psql - better support pipe line
Hi
I found so a set "psql" and "xargs" is pretty strong. But I miss a psql
option for simple returning current database and continuing in pipeline.
What I am doing:
psql postgres -At -c "select datname from pg_database" |
xargs -P 3 -I % psql % -At -c "select current_databe() from
pg_stat_all_tables
where relname = 'pg_attribute' and n_dead_tup > 100000" |
xargs -P 3 -I % sh -c "psql % -q -c 'analyze pg_attribute'; echo %" |
xargs -P 3 -I % psql % -At -c "select curren_database() from ..."
it works perfectly - but the line
xargs -P 3 -I % sh -c "psql % -q -c 'analyze pg_attribute'; echo %"
is little bit ugly - with some psql option it can be cleaned to
xargs -P3 -I % psql % -q --echo-db -c "analyze pg_attribute" | ...
--echo-db requires -q option
What are you thinking about this idea?
Regards
Pavel
On 08/24/2015 08:06 AM, Pavel Stehule wrote:
Hi
I found so a set "psql" and "xargs" is pretty strong. But I miss a psql
option for simple returning current database and continuing in pipeline.What I am doing:
psql postgres -At -c "select datname from pg_database" |
xargs -P 3 -I % psql % -At -c "select current_databe() from
pg_stat_all_tables
where relname = 'pg_attribute' and n_dead_tup > 100000" |
xargs -P 3 -I % sh -c "psql % -q -c 'analyze pg_attribute'; echo %" |
xargs -P 3 -I % psql % -At -c "select curren_database() from ..."it works perfectly - but the line
xargs -P 3 -I % sh -c "psql % -q -c 'analyze pg_attribute'; echo %"
is little bit ugly - with some psql option it can be cleaned to
xargs -P3 -I % psql % -q --echo-db -c "analyze pg_attribute" | ...
--echo-db requires -q option
What are you thinking about this idea?
Seems like a one-tricky-pony to me. You're just as likely to need to
print a relation name or something else, as the current database.
I don't actually understandu what you'd put in the "..." above. One
you've analyze'd the table, what more do you want to do?
Overall, once your pipeline gets that complicated, I'd rather write a
little bash or perl script with for-loops and variables.
- Heikki
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
2015-08-24 12:49 GMT+02:00 Heikki Linnakangas <hlinnaka@iki.fi>:
On 08/24/2015 08:06 AM, Pavel Stehule wrote:
Hi
I found so a set "psql" and "xargs" is pretty strong. But I miss a psql
option for simple returning current database and continuing in pipeline.What I am doing:
psql postgres -At -c "select datname from pg_database" |
xargs -P 3 -I % psql % -At -c "select current_databe() from
pg_stat_all_tables
where relname = 'pg_attribute' and n_dead_tup > 100000" |
xargs -P 3 -I % sh -c "psql % -q -c 'analyze pg_attribute'; echo %" |
xargs -P 3 -I % psql % -At -c "select curren_database() from ..."it works perfectly - but the line
xargs -P 3 -I % sh -c "psql % -q -c 'analyze pg_attribute'; echo %"
is little bit ugly - with some psql option it can be cleaned to
xargs -P3 -I % psql % -q --echo-db -c "analyze pg_attribute" | ...
--echo-db requires -q option
What are you thinking about this idea?
Seems like a one-tricky-pony to me. You're just as likely to need to print
a relation name or something else, as the current database.
yes, you have true
I don't actually understandu what you'd put in the "..." above. One you've
analyze'd the table, what more do you want to do?
do other filtering and for example run VACUUM FULL when it can have a
significant effect
Overall, once your pipeline gets that complicated, I'd rather write a
little bash or perl script with for-loops and variables.
It is pipeline of trivial statements - so still it is pretty simple - and
with multiprocess suppport
Regards
Pavel
Show quoted text
- Heikki
On 08/24/2015 06:49 AM, Heikki Linnakangas wrote:
On 08/24/2015 08:06 AM, Pavel Stehule wrote:
Hi
I found so a set "psql" and "xargs" is pretty strong. But I miss a psql
option for simple returning current database and continuing in pipeline.What I am doing:
psql postgres -At -c "select datname from pg_database" |
xargs -P 3 -I % psql % -At -c "select current_databe() from
pg_stat_all_tables
where relname = 'pg_attribute' and n_dead_tup > 100000" |
xargs -P 3 -I % sh -c "psql % -q -c 'analyze pg_attribute'; echo %" |
xargs -P 3 -I % psql % -At -c "select curren_database() from ..."it works perfectly - but the line
xargs -P 3 -I % sh -c "psql % -q -c 'analyze pg_attribute'; echo %"
is little bit ugly - with some psql option it can be cleaned to
xargs -P3 -I % psql % -q --echo-db -c "analyze pg_attribute" | ...
--echo-db requires -q option
What are you thinking about this idea?
Seems like a one-tricky-pony to me. You're just as likely to need to
print a relation name or something else, as the current database.I don't actually understandu what you'd put in the "..." above. One
you've analyze'd the table, what more do you want to do?Overall, once your pipeline gets that complicated, I'd rather write a
little bash or perl script with for-loops and variables.
Yes, the use case for this is way too narrow.
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Heikki Linnakangas <hlinnaka@iki.fi> writes:
On 08/24/2015 08:06 AM, Pavel Stehule wrote:
it works perfectly - but the line
xargs -P 3 -I % sh -c "psql % -q -c 'analyze pg_attribute'; echo %"
is little bit ugly - with some psql option it can be cleaned to
xargs -P3 -I % psql % -q --echo-db -c "analyze pg_attribute" | ...
--echo-db requires -q option
What are you thinking about this idea?
Seems like a one-tricky-pony to me. You're just as likely to need to
print a relation name or something else, as the current database.
Not only that, but:
(1) there is no reason to believe that the db name and only the db name
is needed to do another connection; what about port, host, user, etc?
(2) this commandeers the pipe connection to transmit out-of-band data,
making it impossible to use the pipe for its natural function, viz
transmitting ordinary data from one processing step to the next. Sure,
there are use-cases where there's no such data and you can repurpose the
pipe like that, but that's an enormous limitation.
Overall, once your pipeline gets that complicated, I'd rather write a
little bash or perl script with for-loops and variables.
Yeah, on the whole this seems like a band-aid to let a bad scripting
approach limp a few steps further before it collapses completely.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
2015-08-24 16:02 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us>:
Heikki Linnakangas <hlinnaka@iki.fi> writes:
On 08/24/2015 08:06 AM, Pavel Stehule wrote:
it works perfectly - but the line
xargs -P 3 -I % sh -c "psql % -q -c 'analyze pg_attribute'; echo %"
is little bit ugly - with some psql option it can be cleaned to
xargs -P3 -I % psql % -q --echo-db -c "analyze pg_attribute" | ...
--echo-db requires -q option
What are you thinking about this idea?Seems like a one-tricky-pony to me. You're just as likely to need to
print a relation name or something else, as the current database.Not only that, but:
(1) there is no reason to believe that the db name and only the db name
is needed to do another connection; what about port, host, user, etc?
I have to agree - the possibilities is much more than database name - so
one option is not good idea.
(2) this commandeers the pipe connection to transmit out-of-band data,
making it impossible to use the pipe for its natural function, viz
transmitting ordinary data from one processing step to the next. Sure,
there are use-cases where there's no such data and you can repurpose the
pipe like that, but that's an enormous limitation.
I wrote some bash or perl scripts and I don't think so described style is
less readable than other.
But it has one pretty advantage - paralelism without any line more, without
higher complexity.
Regards
Pavel
Show quoted text
Overall, once your pipeline gets that complicated, I'd rather write a
little bash or perl script with for-loops and variables.Yeah, on the whole this seems like a band-aid to let a bad scripting
approach limp a few steps further before it collapses completely.
regards, tom lane
On 8/24/15 3:04 PM, Pavel Stehule wrote:
(1) there is no reason to believe that the db name and only the db name
is needed to do another connection; what about port, host, user, etc?I have to agree - the possibilities is much more than database name - so
one option is not good idea.
What I've had problems with is trying to correlate psql specified
connection attributes with things like DBI. It would be nice if there
was a way to get a fully formed connection URI for the current connection.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Jim Nasby <Jim.Nasby@bluetreble.com> writes:
What I've had problems with is trying to correlate psql specified
connection attributes with things like DBI. It would be nice if there
was a way to get a fully formed connection URI for the current connection.
Yeah, although I'd think the capability to create such a URI is libpq's
province not psql's. Maybe a PQgetConnectionURI(PGConn) function in
libpq, and some psql backslash command to access that? Or maybe a nicer
API would be that there's a magic psql variable containing the URI; not
sure.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Hi
2015-08-25 17:21 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us>:
Jim Nasby <Jim.Nasby@bluetreble.com> writes:
What I've had problems with is trying to correlate psql specified
connection attributes with things like DBI. It would be nice if there
was a way to get a fully formed connection URI for the currentconnection.
Yeah, although I'd think the capability to create such a URI is libpq's
province not psql's. Maybe a PQgetConnectionURI(PGConn) function in
libpq, and some psql backslash command to access that? Or maybe a nicer
API would be that there's a magic psql variable containing the URI; not
sure.
proof concept of PQGetConnectionUri and \uri command.
missing:
connection options
uri encoding
Show quoted text
regards, tom lane
Attachments:
PQgetConnectionUri.patchtext/x-patch; charset=US-ASCII; name=PQgetConnectionUri.patchDownload
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
new file mode 100644
index 6181a61..47e27cd
*** a/src/bin/psql/command.c
--- b/src/bin/psql/command.c
*************** exec_command(const char *cmd,
*** 1505,1510 ****
--- 1505,1530 ----
free(opt);
}
+ /* \uri */
+ else if (strcmp(cmd, "uri") == 0)
+ {
+ char *db = PQdb(pset.db);
+
+ if (db == NULL)
+ printf(_("You are currently not connected to a database.\n"));
+ else
+ {
+ char *uri = PQgetConnectionUri(pset.db);
+ if (uri == NULL)
+ {
+ psql_error("out of memory\n");
+ exit(EXIT_FAILURE);
+ }
+ printf("%s\n", uri);
+ free(uri);
+ }
+ }
+
/* \w -- write query buffer to file */
else if (strcmp(cmd, "w") == 0 || strcmp(cmd, "write") == 0)
{
diff --git a/src/interfaces/libpq/exports.txt b/src/interfaces/libpq/exports.txt
new file mode 100644
index 4a21bf1..c1165c9
*** a/src/interfaces/libpq/exports.txt
--- b/src/interfaces/libpq/exports.txt
*************** PQsslInUse 166
*** 169,171 ****
--- 169,172 ----
PQsslStruct 167
PQsslAttributes 168
PQsslAttribute 169
+ PQgetConnectionUri 170
diff --git a/src/interfaces/libpq/fe-connect.c b/src/interfaces/libpq/fe-connect.c
new file mode 100644
index a45f4cb..c70e25e
*** a/src/interfaces/libpq/fe-connect.c
--- b/src/interfaces/libpq/fe-connect.c
*************** PQport(const PGconn *conn)
*** 5361,5366 ****
--- 5361,5402 ----
return conn->pgport;
}
+ #define isdef(strptr) ((strptr) && (strptr[0] != '\0'))
+
+ /*
+ * Returns string uri - returned string should be released
+ */
+ char *
+ PQgetConnectionUri(const PGconn *conn)
+ {
+ PQExpBufferData buf;
+ char *host;
+
+ if (!conn)
+ return NULL;
+
+ host = PQhost(conn);
+
+ initPQExpBuffer(&buf);
+
+ appendPQExpBuffer(&buf, "postgresql://%s", conn->pguser);
+
+ if (isdef(conn->pgpass))
+ appendPQExpBuffer(&buf, ":%s", conn->pgpass);
+ appendPQExpBufferStr(&buf, "@");
+ if (isdef(host))
+ appendPQExpBufferStr(&buf, host);
+ if (isdef(conn->pgport))
+ appendPQExpBuffer(&buf, ":%s", conn->pgport);
+ if (isdef(conn->dbName))
+ appendPQExpBuffer(&buf, "/%s", conn->dbName);
+
+ if (isdef(conn->pgoptions))
+ appendPQExpBuffer(&buf, "?%s", conn->pgoptions);
+
+ return buf.data;
+ }
+
char *
PQtty(const PGconn *conn)
{
diff --git a/src/interfaces/libpq/libpq-fe.h b/src/interfaces/libpq/libpq-fe.h
new file mode 100644
index a73eae2..7c8a212
*** a/src/interfaces/libpq/libpq-fe.h
--- b/src/interfaces/libpq/libpq-fe.h
*************** extern char *PQhost(const PGconn *conn);
*** 304,309 ****
--- 304,310 ----
extern char *PQport(const PGconn *conn);
extern char *PQtty(const PGconn *conn);
extern char *PQoptions(const PGconn *conn);
+ extern char *PQgetConnectionUri(const PGconn *conn);
extern ConnStatusType PQstatus(const PGconn *conn);
extern PGTransactionStatusType PQtransactionStatus(const PGconn *conn);
extern const char *PQparameterStatus(const PGconn *conn,
On Wed, Aug 26, 2015 at 5:44 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:
Hi
2015-08-25 17:21 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us>:
Jim Nasby <Jim.Nasby@bluetreble.com> writes:
What I've had problems with is trying to correlate psql specified
connection attributes with things like DBI. It would be nice if there
was a way to get a fully formed connection URI for the currentconnection.
Yeah, although I'd think the capability to create such a URI is libpq's
province not psql's. Maybe a PQgetConnectionURI(PGConn) function in
libpq, and some psql backslash command to access that? Or maybe a nicer
API would be that there's a magic psql variable containing the URI; not
sure.proof concept of PQGetConnectionUri and \uri command.
I like the idea, thanks!
missing:
connection options
uri encoding
Attached adds implementation of both. Still missing:
- documentation
Maybe we should provide a bool parameter to this new function so that
additional parameters could be ignored. Right now it will print a few
default values, that are of no great use anyway:
$ ./bin/psql -c '\uri' 'postgresql://username@/postgres'
postgresql:/username@
:5432/postgres?client_encoding=UTF8&fallback_application_name=psql&sslmode=disable
I don't think we can detect and remove the default values from this output
in a reliable way?
--
Alex
Attachments:
PQgetConnectionUri-v2.patchtext/x-patch; charset=US-ASCII; name=PQgetConnectionUri-v2.patchDownload
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 6181a61..47e27cd 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -1505,6 +1505,26 @@ exec_command(const char *cmd,
free(opt);
}
+ /* \uri */
+ else if (strcmp(cmd, "uri") == 0)
+ {
+ char *db = PQdb(pset.db);
+
+ if (db == NULL)
+ printf(_("You are currently not connected to a database.\n"));
+ else
+ {
+ char *uri = PQgetConnectionUri(pset.db);
+ if (uri == NULL)
+ {
+ psql_error("out of memory\n");
+ exit(EXIT_FAILURE);
+ }
+ printf("%s\n", uri);
+ free(uri);
+ }
+ }
+
/* \w -- write query buffer to file */
else if (strcmp(cmd, "w") == 0 || strcmp(cmd, "write") == 0)
{
diff --git a/src/interfaces/libpq/exports.txt b/src/interfaces/libpq/exports.txt
index 4a21bf1..c1165c9 100644
--- a/src/interfaces/libpq/exports.txt
+++ b/src/interfaces/libpq/exports.txt
@@ -169,3 +169,4 @@ PQsslInUse 166
PQsslStruct 167
PQsslAttributes 168
PQsslAttribute 169
+PQgetConnectionUri 170
diff --git a/src/interfaces/libpq/fe-connect.c b/src/interfaces/libpq/fe-connect.c
index a45f4cb..561dee3 100644
--- a/src/interfaces/libpq/fe-connect.c
+++ b/src/interfaces/libpq/fe-connect.c
@@ -5361,6 +5361,118 @@ PQport(const PGconn *conn)
return conn->pgport;
}
+#define isdef(strptr) ((strptr) && (strptr[0] != '\0'))
+
+#define APPEND_URI_FIELD(key, fieldname) \
+ \
+ if (isdef(conn->fieldname)) \
+ { \
+ if (qs) \
+ appendPQExpBufferChar(&buf, '&'); \
+ else \
+ appendPQExpBufferChar(&buf, '?'); \
+ qs = true; \
+ \
+ appendPQExpBufferStr(&buf, #key "="); \
+ escape_uri(&buf, conn->fieldname); \
+ } \
+
+
+static void
+escape_uri(PQExpBuffer buf, const char *str)
+{
+ static const char hextbl[] = "0123456789ABCDEF";
+ const char *p;
+
+ for (p = str; *p; p++)
+ {
+ if (*p == '-' || *p == '_' || (isgraph(*p) && !ispunct(*p)))
+ appendPQExpBufferChar(buf, *p);
+ else
+ {
+ appendPQExpBufferChar(buf, '%');
+ appendPQExpBufferChar(buf, hextbl[(*p >> 4) & 0x0F]);
+ appendPQExpBufferChar(buf, hextbl[*p & 0x0F]);
+ }
+ }
+}
+
+/*
+ * Returns string uri - returned string should be released
+ */
+char *
+PQgetConnectionUri(const PGconn *conn)
+{
+ PQExpBufferData buf;
+ const char *host;
+ bool qs;
+
+ if (!conn)
+ return NULL;
+
+ host = PQhost(conn);
+
+ initPQExpBuffer(&buf);
+
+ /* build the main uri part */
+ appendPQExpBufferStr(&buf, "postgresql://");
+ escape_uri(&buf, conn->pguser);
+
+ if (isdef(conn->pgpass))
+ {
+ appendPQExpBufferChar(&buf, ':');
+ escape_uri(&buf, conn->pgpass);
+ }
+ appendPQExpBufferChar(&buf, '@');
+ if (isdef(host))
+ escape_uri(&buf, host);
+
+ if (isdef(conn->pgport))
+ {
+ appendPQExpBufferChar(&buf, ':');
+ escape_uri(&buf, conn->pgport);
+ }
+ if (isdef(conn->dbName))
+ {
+ appendPQExpBufferChar(&buf, '/');
+ escape_uri(&buf, conn->dbName);
+ }
+
+ /* optional query string parameters follow */
+ qs = false;
+ APPEND_URI_FIELD(connect_timeout, connect_timeout);
+ APPEND_URI_FIELD(client_encoding, client_encoding_initial);
+ APPEND_URI_FIELD(options, pgoptions);
+ APPEND_URI_FIELD(application_name, appname);
+ APPEND_URI_FIELD(fallback_application_name, fbappname);
+ APPEND_URI_FIELD(keepalives, keepalives);
+ APPEND_URI_FIELD(keepalives_idle, keepalives_idle);
+ APPEND_URI_FIELD(keepalives_interval, keepalives_interval);
+ APPEND_URI_FIELD(keepalives_count, keepalives_count);
+ APPEND_URI_FIELD(sslmode, sslmode);
+ if (isdef(conn->sslmode) && strcmp(conn->sslmode, "disable"))
+ {
+ APPEND_URI_FIELD(sslcompression, sslcompression);
+ APPEND_URI_FIELD(sslcert, sslcert);
+ APPEND_URI_FIELD(sslkey, sslkey);
+ APPEND_URI_FIELD(sslrootcert, sslrootcert);
+ APPEND_URI_FIELD(sslcrl, sslcrl);
+ }
+ APPEND_URI_FIELD(requirepeer, requirepeer);
+#if defined(ENABLE_GSS) || defined(ENABLE_SSPI)
+ APPEND_URI_FIELD(krbsrvname, krbsrvname);
+#endif
+#if defined(ENABLE_SSPI) && defined(ENABLE_GSS)
+ APPEND_URI_FIELD(gsslib, gsslib);
+#endif
+
+ return buf.data;
+}
+
+#undef isdef
+#undef APPEND_URI_FIELD
+
+
char *
PQtty(const PGconn *conn)
{
diff --git a/src/interfaces/libpq/libpq-fe.h b/src/interfaces/libpq/libpq-fe.h
index a73eae2..7c8a212 100644
--- a/src/interfaces/libpq/libpq-fe.h
+++ b/src/interfaces/libpq/libpq-fe.h
@@ -304,6 +304,7 @@ extern char *PQhost(const PGconn *conn);
extern char *PQport(const PGconn *conn);
extern char *PQtty(const PGconn *conn);
extern char *PQoptions(const PGconn *conn);
+extern char *PQgetConnectionUri(const PGconn *conn);
extern ConnStatusType PQstatus(const PGconn *conn);
extern PGTransactionStatusType PQtransactionStatus(const PGconn *conn);
extern const char *PQparameterStatus(const PGconn *conn,
2015-08-27 11:54 GMT+02:00 Shulgin, Oleksandr <oleksandr.shulgin@zalando.de>
:
On Wed, Aug 26, 2015 at 5:44 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:Hi
2015-08-25 17:21 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us>:
Jim Nasby <Jim.Nasby@bluetreble.com> writes:
What I've had problems with is trying to correlate psql specified
connection attributes with things like DBI. It would be nice if there
was a way to get a fully formed connection URI for the currentconnection.
Yeah, although I'd think the capability to create such a URI is libpq's
province not psql's. Maybe a PQgetConnectionURI(PGConn) function in
libpq, and some psql backslash command to access that? Or maybe a nicer
API would be that there's a magic psql variable containing the URI; not
sure.proof concept of PQGetConnectionUri and \uri command.
I like the idea, thanks!
missing:
connection options
uri encodingAttached adds implementation of both. Still missing:
- documentation
Maybe we should provide a bool parameter to this new function so that
additional parameters could be ignored. Right now it will print a few
default values, that are of no great use anyway:$ ./bin/psql -c '\uri' 'postgresql://username@/postgres'
postgresql:/username@
:5432/postgres?client_encoding=UTF8&fallback_application_name=psql&sslmode=disableI don't think we can detect and remove the default values from this output
in a reliable way?
This is pretty difficult - any parameter can be important, and hard to
identify default values on client side without connect to server side. I
don't see any other way, than hold bitmap for identification entered values
and default values from the input.
Pavel
Show quoted text
--
Alex
2015-08-27 11:54 GMT+02:00 Shulgin, Oleksandr <oleksandr.shulgin@zalando.de>
:
On Wed, Aug 26, 2015 at 5:44 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:Hi
2015-08-25 17:21 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us>:
Jim Nasby <Jim.Nasby@bluetreble.com> writes:
What I've had problems with is trying to correlate psql specified
connection attributes with things like DBI. It would be nice if there
was a way to get a fully formed connection URI for the currentconnection.
Yeah, although I'd think the capability to create such a URI is libpq's
province not psql's. Maybe a PQgetConnectionURI(PGConn) function in
libpq, and some psql backslash command to access that? Or maybe a nicer
API would be that there's a magic psql variable containing the URI; not
sure.proof concept of PQGetConnectionUri and \uri command.
I like the idea, thanks!
missing:
connection options
uri encodingAttached adds implementation of both. Still missing:
thank you for enhancing this patch
Pavel
Show quoted text
- documentation
Maybe we should provide a bool parameter to this new function so that
additional parameters could be ignored. Right now it will print a few
default values, that are of no great use anyway:$ ./bin/psql -c '\uri' 'postgresql://username@/postgres'
postgresql:/username@
:5432/postgres?client_encoding=UTF8&fallback_application_name=psql&sslmode=disableI don't think we can detect and remove the default values from this output
in a reliable way?--
Alex
On 8/27/15 8:37 AM, Pavel Stehule wrote:
I don't think we can detect and remove the default values from this
output in a reliable way?This is pretty difficult - any parameter can be important, and hard to
identify default values on client side without connect to server side. I
don't see any other way, than hold bitmap for identification entered
values and default values from the input.
That would be nice, but probably not that big a deal.
What I think would be more useful is a way to exclude the password if
you didn't need it.
It occurs to me the most flexible thing that could be done here would be
providing a libpq function that spits out JSON connection parameters and
have psql turn that into a variable. It would be easy to feed that to a
SQL statement and do whatever you want with it at that point, including
format it to a connection URI.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Aug 28, 2015 at 3:29 AM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
On 8/27/15 8:37 AM, Pavel Stehule wrote:
I don't think we can detect and remove the default values from this
output in a reliable way?This is pretty difficult - any parameter can be important, and hard to
identify default values on client side without connect to server side. I
don't see any other way, than hold bitmap for identification entered
values and default values from the input.That would be nice, but probably not that big a deal.
What I think would be more useful is a way to exclude the password if you
didn't need it.
Good point, and maybe even exclude it by default and provide only if asked
for that explicitly.
It occurs to me the most flexible thing that could be done here would be
providing a libpq function that spits out JSON connection parameters and
have psql turn that into a variable. It would be easy to feed that to a SQL
statement and do whatever you want with it at that point, including format
it to a connection URI.
Hm... but that would mean that suddenly psql would need JSON parsing
capabilities and URI escaping code would have to be moved there too? So
every client that links to libpq and wants to use this feature going as far
as reconstructing an URI would need both of the capabilities.
Why instead of JSON not spit conninfo format, with proper escaping? That
could be a separate library call, e.g. PGgetConnectionString() and a
separate backslash command: \conninfo
--
Alex
On 8/28/15 3:58 AM, Shulgin, Oleksandr wrote:
It occurs to me the most flexible thing that could be done here
would be providing a libpq function that spits out JSON connection
parameters and have psql turn that into a variable. It would be easy
to feed that to a SQL statement and do whatever you want with it at
that point, including format it to a connection URI.Hm... but that would mean that suddenly psql would need JSON parsing
capabilities and URI escaping code would have to be moved there too? So
every client that links to libpq and wants to use this feature going as
far as reconstructing an URI would need both of the capabilities.
Anything that's doing this presumably has connected to the database,
which on any recent version means you have plenty of ability to process
JSON at the SQL layer.
Why instead of JSON not spit conninfo format, with proper escaping?
That could be a separate library call, e.g. PGgetConnectionString() and
a separate backslash command: \conninfo
Do you mean as a URI? The downside to that it's it's more difficult to
parse than JSON. Another option might be an array.
The other issue is there's no way to capture \conninfo inside of psql
and do something with it. If instead this was exposed as a variable, you
could handle it in SQL if you wanted to.
All that said, the patch already adds significant value and you could
always parse the URI if you really needed to.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Aug 28, 2015 at 9:52 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
On 8/28/15 3:58 AM, Shulgin, Oleksandr wrote:
It occurs to me the most flexible thing that could be done here
would be providing a libpq function that spits out JSON connection
parameters and have psql turn that into a variable. It would be easy
to feed that to a SQL statement and do whatever you want with it at
that point, including format it to a connection URI.Hm... but that would mean that suddenly psql would need JSON parsing
capabilities and URI escaping code would have to be moved there too? So
every client that links to libpq and wants to use this feature going as
far as reconstructing an URI would need both of the capabilities.Anything that's doing this presumably has connected to the database, which
on any recent version means you have plenty of ability to process JSON at
the SQL layer.
*Cough*... Well, the fact that it's technically not impossible, doesn't
mean it's the right way to do it. By the same reasoning we can also ask
the server to calculate 1+1 for us in SQL. :-)
And that will work even with a 9.0 server, while parsing JSON -- not
really. Another point is that you don't need an *alive* connection to be
able to extract its URI/conninfo string, while when offloading JSON parsing
part to the server you suddenly do. Bottom line for me: while still
possible, this can't be portable.
Why instead of JSON not spit conninfo format, with proper escaping?
That could be a separate library call, e.g. PGgetConnectionString() and
a separate backslash command: \conninfoDo you mean as a URI? The downside to that it's it's more difficult to
parse than JSON. Another option might be an array.
Hm... actually why not just use the existing call:
PQconninfoOption *PQconninfo(PGconn *conn);
and move whatever code is needed to form an URI or conninfo string to psql
itself?
The other issue is there's no way to capture \conninfo inside of psql and
do something with it. If instead this was exposed as a variable, you could
handle it in SQL if you wanted to.
Yeah, I forgot about the variable proposal, that would be a more useful way
to expose it for sure.
--
Alex
On 8/29/15 8:10 AM, Shulgin, Oleksandr wrote:
The other issue is there's no way to capture \conninfo inside of
psql and do something with it. If instead this was exposed as a
variable, you could handle it in SQL if you wanted to.Yeah, I forgot about the variable proposal, that would be a more useful
way to expose it for sure.
Right. My only other point is it would be nice if what we exposed there
could be easily parsed in SQL. But as I said, having *anything* would be
an improvement.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers