Feature request: Connection string parsing for postgres_fdw
I'm trying to store connection to postgres_fdw in the database I want to
be able to store the full breadth of connection styles and all the
different types of connections that libpq supports. But having some
troubles.
Postgres_fdw wants options passed into CREATE SERVER, all broken out into
separate variables, but this format is neither a connection URI, nor a
keyword/value string. One could imagine writing some parser that extracts
all the variables and honors collisions in the same way libpq does (like
when both the path and the query string specify a port), but I really don't
want to recreate that.
It would be really nice if I could tap into libpq's connection string
parser from SQL and use it to extract all the variables in a given
connection string, so that I can then pipe those into CREATE SERVER
options. Either that, or teach postgres_fdw how to consume standard
connection strings.
Suggestions? Is this worthy of a feature request?
Thanks,
Eric
--
http://aquameta.org/
On Wed, Dec 23, 2020 at 6:35 PM Eric Hanson <eric@aquameta.com> wrote:
I'm trying to store connection to postgres_fdw in the database I want to be able to store the full breadth of connection styles and all the different types of connections that libpq supports. But having some troubles.
Postgres_fdw wants options passed into CREATE SERVER, all broken out into separate variables, but this format is neither a connection URI, nor a keyword/value string. One could imagine writing some parser that extracts all the variables and honors collisions in the same way libpq does (like when both the path and the query string specify a port), but I really don't want to recreate that.
It would be really nice if I could tap into libpq's connection string parser from SQL and use it to extract all the variables in a given connection string, so that I can then pipe those into CREATE SERVER options. Either that, or teach postgres_fdw how to consume standard connection strings.
Suggestions? Is this worthy of a feature request?
https://www.postgresql.org/docs/13/libpq-connect.html#LIBPQ-PARAMKEYWORDS
lists the parameters that postgres_fdw accepts. "dbname" can be more
than just dbname. See
https://www.postgresql.org/docs/13/libpq-connect.html#LIBPQ-CONNSTRING.
And "dbname" is not in the list of exception paramters in section
"F.33.1.1. Connection Options" at
https://www.postgresql.org/docs/13/postgres-fdw.html#id-1.11.7.42.11.
I haven't tried this myself. But this might help you.
--
Best Wishes,
Ashutosh Bapat
On Wed, Dec 23, 2020 at 5:39 AM Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
wrote:
https://www.postgresql.org/docs/13/libpq-connect.html#LIBPQ-PARAMKEYWORDS
lists the parameters that postgres_fdw accepts. "dbname" can be more
than just dbname. See
https://www.postgresql.org/docs/13/libpq-connect.html#LIBPQ-CONNSTRING.
And "dbname" is not in the list of exception paramters in section
"F.33.1.1. Connection Options" at
https://www.postgresql.org/docs/13/postgres-fdw.html#id-1.11.7.42.11.I haven't tried this myself. But this might help you.
Good idea, but according to this thread:
/messages/by-id/878tjcbbgb.fsf@ars-thinkpad
"postgres_fdw forbids usage of connection strings by passing expand_dbname
= false to PQconnectdbParams"
They discuss the reasoning here: If it were to allow expand_dbname, people
could override username etc, variables that need to be fixed, by setting
them in the dbname connection string. But this just seems like a bug. It
should prioritize non-expanded variables over expanded ones.
Eric
--
http://aquameta.org/
On Wed, Dec 23, 2020 at 7:42 PM Eric Hanson <eric@aquameta.com> wrote:
On Wed, Dec 23, 2020 at 5:39 AM Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> wrote:
https://www.postgresql.org/docs/13/libpq-connect.html#LIBPQ-PARAMKEYWORDS
lists the parameters that postgres_fdw accepts. "dbname" can be more
than just dbname. See
https://www.postgresql.org/docs/13/libpq-connect.html#LIBPQ-CONNSTRING.
And "dbname" is not in the list of exception paramters in section
"F.33.1.1. Connection Options" at
https://www.postgresql.org/docs/13/postgres-fdw.html#id-1.11.7.42.11.I haven't tried this myself. But this might help you.
Good idea, but according to this thread:
/messages/by-id/878tjcbbgb.fsf@ars-thinkpad
"postgres_fdw forbids usage of connection strings by passing expand_dbname = false to PQconnectdbParams"
Looks like the documentation needs an update here.
They discuss the reasoning here: If it were to allow expand_dbname, people could override username etc, variables that need to be fixed, by setting them in the dbname connection string. But this just seems like a bug. It should prioritize non-expanded variables over expanded ones.
Yeah. That might be what the feature should implement.
--
Best Wishes,
Ashutosh Bapat
2020年12月23日(水) 22:05 Eric Hanson <eric@aquameta.com>:
I'm trying to store connection to postgres_fdw in the database I want to be able to store the full breadth of connection styles and all the different types of connections that libpq supports. But having some troubles.
Postgres_fdw wants options passed into CREATE SERVER, all broken out into separate variables, but this format is neither a connection URI, nor a keyword/value string. One could imagine writing some parser that extracts all the variables and honors collisions in the same way libpq does (like when both the path and the query string specify a port), but I really don't want to recreate that.
It would be really nice if I could tap into libpq's connection string parser from SQL and use it to extract all the variables in a given connection string, so that I can then pipe those into CREATE SERVER options. Either that, or teach postgres_fdw how to consume standard connection strings.
Does something like this do what you're looking for?
postgres=# SELECT * FROM conninfo_parse('host=node1 user=foo');
keyword | value
---------+-------
user | foo
host | node1
(2 rows)
postgres=# SELECT * FROM conninfo_parse('postgresql://localhost:5433');
keyword | value
---------+-----------
host | localhost
port | 5433
(2 rows)
Basically a wrapper around PQconninfoParse(), I've had the code knocking around
for a while now and finally got round to packaging it into an
extension [1]https://github.com/ibarwick/conninfo. It's
on my todo list to submit a patch based on this to core, as it seems Generally
Useful To Have.
[1]: https://github.com/ibarwick/conninfo
Regards
Ian Barwick
--
EnterpriseDB: https://www.enterprisedb.com
On Fri, Dec 25, 2020 at 09:59:09AM +0900, Ian Lawrence Barwick wrote:
Basically a wrapper around PQconninfoParse(), I've had the code knocking around
for a while now and finally got round to packaging it into an
extension [1]. It's
on my todo list to submit a patch based on this to core, as it seems Generally
Useful To Have.
I think that this would be useful. If you send a patch to core, be
careful that the backend code should not directly link to libpq. This
is of course fine if done from an extension, though this is not really
something related to postgres_fdw. Or just refactor the code into
src/common/ and then consume the refactored pieces from libpq and the
backend.
--
Michael
Whoa that's perfect! Thank you so much.
On Thu, Dec 24, 2020 at 4:59 PM Ian Lawrence Barwick <barwick@gmail.com>
wrote:
Show quoted text
2020年12月23日(水) 22:05 Eric Hanson <eric@aquameta.com>:
I'm trying to store connection to postgres_fdw in the database I want
to be able to store the full breadth of connection styles and all the
different types of connections that libpq supports. But having some
troubles.Postgres_fdw wants options passed into CREATE SERVER, all broken out
into separate variables, but this format is neither a connection URI, nor a
keyword/value string. One could imagine writing some parser that extracts
all the variables and honors collisions in the same way libpq does (like
when both the path and the query string specify a port), but I really don't
want to recreate that.It would be really nice if I could tap into libpq's connection string
parser from SQL and use it to extract all the variables in a given
connection string, so that I can then pipe those into CREATE SERVER
options. Either that, or teach postgres_fdw how to consume standard
connection strings.Does something like this do what you're looking for?
postgres=# SELECT * FROM conninfo_parse('host=node1 user=foo');
keyword | value
---------+-------
user | foo
host | node1
(2 rows)postgres=# SELECT * FROM conninfo_parse('postgresql://localhost:5433');
keyword | value
---------+-----------
host | localhost
port | 5433
(2 rows)Basically a wrapper around PQconninfoParse(), I've had the code knocking
around
for a while now and finally got round to packaging it into an
extension [1]. It's
on my todo list to submit a patch based on this to core, as it seems
Generally
Useful To Have.[1] https://github.com/ibarwick/conninfo
Regards
Ian Barwick
--
EnterpriseDB: https://www.enterprisedb.com