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 branch of Postgres that better supports (Postgres forks, but initially) Redshift.

The 'redshift' branch of this Official Postgres Repository clone, is aimed at supporting Redshift in many ways:

  • Support Redshift related artifacts
    • Redshift specific SQL Commands / variations
    • Redshift Libraries
  • Support AWS specific artifacts

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 ;)
  • Further, as of now, I don't expect to remove 'Unsupported Postgres features'
  • Additionally, as far as possible:
    • Keep submitting Postgres related patches back to Postgres master
    • Keep this branch up to date with Postgres master

Update (19th August 2017)

  • Currently this branch supports multiple Redshift specific commands such as
    • CREATE LIBRARY
    • CREATE TABLE (DISTKEY / DISTSTYLE / ...)
    • Returns non-SQL items like
      • ENCODINGs (essentially Compression)
      • REGIONs (for e.g. US-EAST-1 etc.)
  • With these commands fully supported (per specification) my current understanding is that it is (near) pointless to support non-functional keywords like LOCAL (in CREATE TABLE), which are probably just baggage from history.


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

    3 Aug 2017

    Reducing Wires

    Recently got an additional monitor for my workstation@home and found that the following wires were indispensable:

    • USB Mouse
    • Monitor VGA / HDMI / DVI cable
    • USB Hub cable (Pen Drive etc.)
    I was lucky that this ($20 + used) Dell monitor was an awesome buy since it came with a Monitor USB Hub (besides other goodies such as vertical rotate etc).

    After a bit of rejigging, this is how things finally panned-out:
    • 1 USB Wire (from the laptop) for the MUH (Monitor USB Hub)
      • This is usually something like this.
    • Use a USB->DVI converter and use that to connect MUH -> Monitor DVI port
      • This is usually something like this.
    • Plug USB Mouse to MUH
    • With things working so well, I also plugged a Wireless Touchpad dongle to the MUH
    So now when I need to do some office work, connecting 1 USB wire gets me up and running!

    #LoveOneWires :)

    Now only if I could find a stable / foolproof Wireless solution here ;)

    29 Jul 2017

    Symbols in Redshift User Passwords work just fine


    Recently read a few posts / discussions about people doubting Redshift not accepting (working-well with) ASCII symbols in User-Passwords.

    It felt like a good time to write this short post showing that Redshift (Engine) seems to work fine with (non-alphanumeric) (printable) ASCII symbols.

    You can see a few things (in the sample output given below):

    • All non-alphanumeric printable ASCII characters worked fine (at least all that my US-International / QWERTY keyboard could throw at it)
    • For those who also need ' (single-quote) and " (double-quote) you could always use $$ as quote-delimiters
    • You still need at least One of each of the following:
      • Upper-Case English-Letter
      • Lower-Case English-Letter
      • One Digit / Numeral


    ------------------------------------------------------------
    # psql -U adminuser -h  rs_cluster -p 5439 db

    psql (9.6.3, server 8.0.2)
    Type "help" for help.

    rs_cluster adminuser@db-# alter user userb with password 'Aa1~!@#$%^&*()_+-`{}[]|";:,<.>/?';
    ALTER USER
    Time: 237.012 ms
    rs_cluster adminuser@db-# \q

    # psql -U userb -h  rs_cluster -p 5439 db
    Password for user userb:

    psql (9.6.3, server 8.0.2)
    Type "help" for help.

    rs_cluster userb@db-# alter user userb with password $$Aa1~!@#$%^&*()_+-`{}[]|";:,<.>/?'"$$;
    ALTER USER
    Time: 191.505 ms
    rs_cluster adminuser@db-# \q

    # psql -U userb -h  rs_cluster -p 5439 db
    Password for user userb:

    psql (9.6.3, server 8.0.2)
    Type "help" for help.

    rs_cluster userb@db-#
    ------------------------------------------------------------

    20 Jul 2017

    Using generate_series() in Redshift

    Considering that Redshift clearly states that it doesn't support (the commonly used postgres function) generate_series(), it gets very frustrating if you just want to fill a table with a lot of rows and can't without a valid data-source.

    Solution (Generates a billion integers on my test-cluster):

    --INSERT INTO tbl
    WITH x AS (
      SELECT 1 
      FROM stl_connection_log a, stl_connection_log b, stl_connection_log c
      -- LIMIT 100
    )
      SELECT row_number() over (order by 1) FROM x;

    For a Redshift server with even a basic level of login activity, this should generate enough rows. For e.g. On my test cluster, where I am the only user, this currently generates 4034866688 (4 billion) rows :) !

    Interestingly, irrespective of the document, generate_series() actually does work on Redshift:

    # select b from generate_series(1,3) as a(b);
    ┌───┐
    │ b │
    ├───┤
    │ 1 │
    │ 2 │
    │ 3 │
    └───┘
    (3 rows)

    The reason why this wouldn't let you insert any rows to your table though, is that this is a Leader-Node-Only function, whereas INSERTs (on any non-single Redshift Cluster) are run on the Compute Nodes (which don't know about this function).

    The reason why the above works, is ROW_NUMBER() and CROSS JOIN allow us to generate a large number of rows, but for that, the initial data-set (here the STL_CONNECTION_LOG System Table) should have at least some rows to multiply on! You could use any other system table (that is available on Compute Nodes) if required, for some other purpose.

    Play On!

    31 May 2017

    Patch: Using --no-comments with pg_dump

    Recently I submitted a patch for review that allows a non-superuser to practically use a backup taken from pg_dump.

    Currently it is a kludge (and well known at that - Ref 1 / Ref 2 / Ref 3 / Ref 4) but since it's (the ideal solution) too big a byte to chew and not in high-demand, it has seen little to no traction in the past decade.

    This patch should allow the above. But more importantly, it should also allow regular users of AWS RDS Postgres as well as Google Cloud Postgres databases (who do not get SuperUser access by-design) to reliably use the backups, instead of tinkering with the backup SQLs and remove things like COMMENT ON EXTENSION for it to even run during restoration.

    The bad news is that since Postgres 10 has already branched off, I doubt this would see the light of the day (if at all) any time before Postgres 11 (unless there is consensus that it's helpful enough and gets 'back-patched' to Postgres 10 to be released around September 2017).