Re: WIP: URI connection string support for libpq

This submission has turned into a bit of a mess.  I did the closest 
thing to a review the day after it was submitted; follow-up review 
attempts had issues applying the patch.  And it's been stuck there.  The 
patch is still fine, I just tested it out to pick this back up myself 
again.  I think this one is a good advocacy feature, and most of the 
hard work is done already.  Smooth some edge cases and this will be 
ready to go.

First thing:  the URI prefix.  It is possible to connect using a URI in 
Python+SQL Alchemy, which was mentioned before as not too relevant due 
to their also requiring a driver name.  As documented at 
http://docs.sqlalchemy.org/en/latest/core/engines.html and demonstrated 
at http://packages.python.org/Flask-SQLAlchemy/config.html , it is 
possible to leave off the driver part of the connection string.  That 
assumes the default driver, such that postgresql:// does the same as 
postgresql+psycopg2:// , sensibly.  That means we absolutely have an 
installed base of URI speaking developers split between postgresql:// 
(Python) and postgres:// (Ruby).  Given that, there really isn't a 
useful path forward that helps out all those developers without 
supporting both prefixes.  That's where this left off before, I just 
wanted to emphasize how clear that need seems now.

Next thing, also mentioned at that Flask page.  SQLite has standardized 
the idea that sqlite:////absolute/path/to/foo.db is a URI pointing to a 
file.  Given that, I wonder if Alex's syntax for specifying a socket 
file name might adopt that syntax, rather than requiring the hex 
encoding:  postgresql://%2Fvar%2Fpgsql%2Ftmp/mydb  It's not a big deal, 
but it would smooth another rough edge toward making the Postgres URI 
implementation look as close as possible to others.

So far I've found only one syntax that I expected this to handle that it 
rejects:

psql -d postgresql://gsmith@localhost

It's picky about needing that third slash, but that shouldn't be hard to 
fix.  I started collecting up all the variants that do work as an 
initial shell script regression test, so that changes don't break 
something that already works.  Here are all the variations that already 
work, setup so that a series of "1" outputs is passing:

psql -d postgresql://gsmith@localhost:5432/gsmith -At -c "SELECT 1"
psql -d postgresql://gsmith@localhost/gsmith -At -c "SELECT 1"
psql -d postgresql://localhost:5432/gsmith -At -c "SELECT 1"
psql -d postgresql://localhost/gsmith -At -c "SELECT 1"
psql -d postgresql://gsmith@localhost:5432/ -At -c "SELECT 1"
psql -d postgresql://gsmith@localhost/ -At -c "SELECT 1"
psql -d postgresql://localhost:5432/ -At -c "SELECT 1"
psql -d postgresql://localhost/gsmith -At -c "SELECT 1"
psql -d postgresql://localhost/ -At -c "SELECT 1"
psql -d postgresql:/// -At -c "SELECT 1"
psql -d postgresql://%6Cocalhost/ -At -c "SELECT 1"
psql -d postgresql://localhost/gsmith?user=gsmith -At -c "SELECT 1"
psql -d postgresql://localhost/gsmith?user=gsmith&port=5432 -At -c 
"SELECT 1"
psql -d postgresql://localhost/gsmith?user=gsmith\&port=5432 -At -c 
"SELECT 1"

Replace all the "gsmith" with $USER to make this usable for others.

My eyes are starting to cross when I look at URI now, so that's enough 
for today.  If Alex wants to rev this soon, great; if not I have a good 
idea what I'd like to do with this next, regardless of that.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com