request for feature: psql "DSN" option

Christopher Browne said:

Per tonight's dinner discussion, it Sure Would Be Nice if psql had an
additional option that allowed passing in a conninfo string.

The notion: Plenty of libraries out there like Pg, DBI::Pg, and such
make you specify connections in the form:
"host=my.db.host.example.org port=5678 dbname=dumb_db user=chewie"

Since those strings need to be kept around for the programs that are
directly using conninfo, it Sure Would Be Nice if those strings could
also be used as arguments to psql.

Peter Eisentraut observed that "-D" is not yet used; that's certainly
an option.

Thus, supposing we kept conninfo strings in a series of files in
/opt/scripts/conninfo, we might run a script via...

#!/bin/sh
CONNINFO=`cat /opt/scripts/conninfo/dumb_db.conninfo`
PERIOD='90 days'
QUERY="delete from my_table where trans_on < now() -
'${PERIOD}'::interval;" QUERY2="vacuum analyze my_table;"
psql -D "${CONNINFO}" -c "${QUERY}"
psql --pqconninfo="${CONNINFO}" -c "${QUERY2}"

I'm not sure those are forcibly the best names for options, but
they're a thought...

Why is this better than using the service file?

cheers

andrew

On Fri, Jul 07, 2006 at 05:33:14AM -0500, Andrew Dunstan wrote:

Christopher Browne said:

The notion: Plenty of libraries out there like Pg, DBI::Pg, and such
make you specify connections in the form:
"host=my.db.host.example.org port=5678 dbname=dumb_db user=chewie"

Why is this better than using the service file?

What Andrew asked.

Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

david@fetter.org (David Fetter) writes:

On Fri, Jul 07, 2006 at 05:33:14AM -0500, Andrew Dunstan wrote:

Christopher Browne said:

The notion: Plenty of libraries out there like Pg, DBI::Pg, and such
make you specify connections in the form:
"host=my.db.host.example.org port=5678 dbname=dumb_db user=chewie"

Why is this better than using the service file?

What Andrew asked.

It's not *better* than PGSERVICE; it's just that this does reflect the
connection representation widely used by every sort of library out
there that uses libpq.

Let's do a quick "guess the language"...

Which language is this?
conn = PGconn.open("host=my.db.host.example.org port=5678 dbname=dumb_db user=chewie");

How about this?
$conn = Pg::connectdb("host=my.db.host.example.org port=5678 dbname=dumb_db user=chewie");

Hmm... This one should be easy to guess...
import psycopg2
dbcon = psycopg2.connect('host=my.db.host.example.org port=5678 dbname=dumb_db user=chewie')

This one might conceivably be easy to mistake for another...
$dbconn = pg_connect("host=my.db.host.example.org port=5678 dbname=dumb_db user=chewie")
or die('Could not connect: ' . pg_last_error());

How about this?
set conres [catch {set newdbc [pg_connect -conninfo
"host=my.db.host.example.org port=5678 dbname=dumb_db user=chewie"]} msg]

This isn't as obvious as it may seem...
(setq P (pq-connectdb "host=my.db.host.example.org port=5678 dbname=dumb_db user=chewie"))

But this probably is...
(setf conn (postgresql::PQconnectdb "host=my.db.host.example.org port=5678 dbname=dumb_db user=chewie"))

Gavin would be extremely disappointed if I had left this one out :-)
let main () =
let c = new connection ~conninfo:"host=my.db.host.example.org port=5678 dbname=dumb_db user=chewie" () in
let _ = c#exec ~expect:[Copy_in] ("copy " ^ Sys.argv.(1) ^ " from stdin") in
c#copy_in_channel stdin;
c#finish

The common thing across all of these is that there's a single string
passed in to request the connection. A string that libpq in effect
requires, but which psql, somewhat oddly, does not allow you to
directly specify.

We've had cases where we needed to store pgconn "connection
signatures", for reports, and then discovered we wanted to use them to
describe psql connections.

A "--pgconn" or "-D" option would allow doing this.

I agree that pg_service.conf is probably cleaner, but it seems
somewhat odd for The Poster Child libpq application, psql, *not* to
have a way of passing in a conninfo value.
--
let name="cbbrowne" and tld="acm.org" in name ^ "@" ^ tld;;
http://cbbrowne.com/info/oses.html
If a mute swears, does his mother wash his hands with soap?

On 7/7/06, Andrew Dunstan <andrew@dunslane.net> wrote:

Why is this better than using the service file?

service file is not well described in manual. quick grep showed that we have
PGSERVICE variable, and pg_service.conf.sample, which doesn't even include
all possible parameter names.
and - comments in this file suggest it is to be used globally only: "Copy
this to your sysconf directory (typically /usr/local/pgsql/etc) and...", and
not "per user".
i would really love to see pg_service.conf extended in such a way that i
could:
1. store it in $HOME/.pg_service.conf
2. use name of service on psql invocation (not as env variable). preferably
using psql service_name. this will conflict with current behaviour (treating
argument as dbname), but with some basic logic, i belive it can be done.

just my $0.02.

depesz

hubert depesz lubaczewski wrote:

On 7/7/06, *Andrew Dunstan* <andrew@dunslane.net
<mailto:andrew@dunslane.net>> wrote:

Why is this better than using the service file?

service file is not well described in manual. quick grep showed that
we have PGSERVICE variable, and pg_service.conf.sample, which doesn't
even include all possible parameter names.
and - comments in this file suggest it is to be used globally only:
"Copy this to your sysconf directory (typically /usr/local/pgsql/etc)
and...", and not "per user".
i would really love to see pg_service.conf extended in such a way that
i could:
1. store it in $HOME/.pg_service.conf
2. use name of service on psql invocation (not as env variable).
preferably using psql service_name. this will conflict with current
behaviour (treating argument as dbname), but with some basic logic, i
belive it can be done.

The service file location is runtime configurable Just set PGSYSCONFDIR.

cheers

andrew