Bug: dblink_send_query not work on 9.2?
I have problem with dblink_send_query function on Postgres 9.2.2
This work
do $$
begin
perform dblink.dblink_connect('internal');
perform dblink.dblink_exec('internal', 'set application_name=''dblink'';',
true);
perform dblink.dblink_disconnect();
end;
$$
But this not
do $$
begin
perform dblink.dblink_connect('internal');
perform dblink.dblink_send_query('internal', 'select dummy();');
perform dblink.dblink_disconnect();
end;
$$
I got error
ERROR: connection "internal" not available
SQL state: 08003
Context: SQL statement "SELECT dblink.dblink_send_query('internal', 'select
dummy();')"
PL/pgSQL function inline_code_block line 5 at PERFORM
It's works without problems on version 9.0
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Bug-dblink-send-query-not-work-on-9-2-tp5739365.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
aasat <satriani@veranet.pl> writes:
This work
do $$
begin
perform dblink.dblink_connect('internal');
I believe that creates an unnamed connection referencing a foreign
server definition named "internal".
perform dblink.dblink_exec('internal', 'set application_name=''dblink'';',
true);
This is going to find that "internal" is not the name of any known
dblink connection (since the one that does exist is unnamed). So it
creates a transient connection of its own using "internal" as a
connection string, ie a fresh reference to the foreign server. For the
duration of this command, there are two connections to the remote
database.
perform dblink.dblink_disconnect();
And that closes the unnamed connection.
perform dblink.dblink_send_query('internal', 'select dummy();');
This is going to fail because there is no dblink connection named
"internal". Unlike dblink_exec, there is no support for making a
transient connection for just the duration of the function call,
since it wouldn't be sensible --- the connection has to persist
so you can get the result back.
Use the two-parameter form of dblink_connect() so you can make a
named connection.
It's works without problems on version 9.0
Consulting the commit logs, I see that that was a bug we fixed
as of 9.1.
Author: Joe Conway <mail@joeconway.com>
Branch: master Release: REL9_2_BR [8af3596d6] 2011-06-25 15:58:07 -0700
Branch: REL9_1_STABLE Release: REL9_1_0 [e4fb58f89] 2011-06-25 15:40:49 -0700
Async dblink functions require a named connection, and therefore should
use DBLINK_GET_NAMED_CONN rather than DBLINK_GET_CONN.
Problem found by Peter Eisentraut and patch by Fujii Masao.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Thank you! It's now works perfectly!
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Bug-dblink-send-query-not-work-on-9-2-tp5739365p5739526.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general