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(at)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(at)localhost:5432/gsmith -At -c "SELECT 1"
psql -d postgresql://gsmith(at)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(at)localhost:5432/ -At -c "SELECT 1"
psql -d postgresql://gsmith(at)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(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com