12 Aug 2017

Redshift support for psql

Am sure you know that psql doesn't go out of it's way to support Postgres' forks natively. I obviously understand the reasoning, which allowed me to find a gap that I could fill here.

The existing features (in psql) that work with any Postgres fork (like Redshift) are entirely because it is a fork of Postgres. Since I use psql heavily at work, last week I decided to begin maintaining a Postgres fork that better supports (Postgres forks, but initially) Redshift. As always, unless explicitly mentioned, this is entirely an unofficial effort.

The 'redshift' branch of this Postgres code-base, is aimed at supporting Redshift in many ways:
  • Support Redshift related artifacts
    • Redshift specific SQL Commands / variations
    • Redshift Libraries
  • Support AWS specific artifacts
  • Support Redshift specific changes
    • For e.g. "/d table" etc.

The idea is:
  • Maintain this branch for the long-term
    • At least as long as I have an accessible Redshift cluster
  • Down the line look at whether other Postgres forks (for e.g. RDS Postgres) need such special attention
    • Although nothing much stands out yet
      • Except some rare exceptions like this or this, which do need to go through an arduous long wait / process of refinement.
  • Change the default port to 5439 (or whatever the flavour supports)
    • ...with an evil grin ;)
  • Additionally, as far as possible:
    • Keep submitting Postgres related patches back to Postgres master
    • Keep this branch up to date with Postgres master

Update (31st August 2017)
  • Currently this branch supports most Redshift specific SQL commands such as
    • Returns non-SQL items like
      • ENCODINGs (a.k.a. Compressions like ZSTD / LZO etc )
      • REGIONs (for e.g. US-EAST-1 etc.)
  • Of course some complex variants (for e.g. GRANT SELECT, UPDATE ON ALL TABLES IN SCHEMA TO GROUP xxx ) don't automatically come up with tab-complete feature. This is primarily because psql's tab-complete feature isn't very powerful to cater to all such scenarios which in turn is because psql's auto-complete isn't a full-fledged parser to begin with.
  • In a nutshell, this branch is now in a pretty good shape to auto-complete the most common Redshift specific SQL Syntax.
  • The best part is that this still merges perfectly with Postgres mainline!

    Let me know if you find anything that needs inclusion, or if I missed something.


    $  psql -U redshift_user -h localhost -E -p 5439 db
    psql (client-version:11devel, server-version:8.0.2, engine:redshift)
    Type "help" for help.


    No comments:

    Find Database DNS / Endpoint via SQL

    How to get Database identifier using SQL Often there is a need for client programs to find "where am I logged into?". This blog po...