Обсуждение: dblink_get_result issue
Hello, I'm experimenting with dblink, and have an issue with async. queries that do not return result sets. (Postgres 8.3.9) --youll need your own connect string to repeat: select dblink_connect_u('myconn1', 'hostaddr=127.0.0.1 port=5432 dbname=postgres user=postgres password=x'); select dblink_send_query('myconn1', 'create temp table bah(s int8);'); select pg_sleep(.3); SELECT * FROM dblink_get_result('myconn1', true ) as x (t text); => ********** Error ********** remote query result rowtype does not match the specified FROM clause rowtype SQL state: 42804 A subsequent call returns an empty result set, as described here: http://www.postgresql.org/docs/8.3/interactive/contrib-dblink-get-result .html Thanks for any help. Marc Mamin
> (Postgres 8.3.9) oops, this is actually 8.4.4 on Windows. -----Original Message----- From: Marc Mamin Sent: Mittwoch, 10. November 2010 18:25 To: 'pgsql-general@postgresql.org' Subject: dblink_get_result issue Hello, I'm experimenting with dblink, and have an issue with async. queries that do not return result sets. (Postgres 8.3.9) --youll need your own connect string to repeat: select dblink_connect_u('myconn1', 'hostaddr=127.0.0.1 port=5432 dbname=postgres user=postgres password=x'); select dblink_send_query('myconn1', 'create temp table bah(s int8);'); select pg_sleep(.3); SELECT * FROM dblink_get_result('myconn1', true ) as x (t text); => ********** Error ********** remote query result rowtype does not match the specified FROM clause rowtype SQL state: 42804 A subsequent call returns an empty result set, as described here: http://www.postgresql.org/docs/8.3/interactive/contrib-dblink-get-result .html Thanks for any help. Marc Mamin
"Marc Mamin" <M.Mamin@intershop.de> writes: > I'm experimenting with dblink, and have an issue with async. queries > that do not return result sets. > (Postgres 8.3.9) > --youll need your own connect string to repeat: > select dblink_connect_u('myconn1', 'hostaddr=127.0.0.1 port=5432 > dbname=postgres user=postgres password=x'); > select dblink_send_query('myconn1', 'create temp table bah(s int8);'); > select pg_sleep(.3); > SELECT * FROM dblink_get_result('myconn1', true ) as x (t text); > => > ********** Error ********** > remote query result rowtype does not match the specified FROM clause > rowtype Hmm. I can reproduce this in 8.4 too, but it seems OK in 9.0. The only one of the 9.0 commits that seems likely to be relevant is http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=56cbb611ec749ba867a4cfc09c8b7df0f4446620 which looks a bit overcomplex for a back-patch. Joe, any thoughts? regards, tom lane
For now I just ignore the first exception. BEGIN PERFORM * from dblink_get_result('myconn')as x (t text); EXCEPTION WHEN datatype_mismatch THEN NULL; END; PERFORM * from dblink_get_result('myconn')as x (t text); thanks, Marc Mamin -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Donnerstag, 11. November 2010 00:54 To: Marc Mamin Cc: pgsql-general@postgresql.org; Joe Conway Subject: Re: [GENERAL] dblink_get_result issue "Marc Mamin" <M.Mamin@intershop.de> writes: > I'm experimenting with dblink, and have an issue with async. queries > that do not return result sets. > (Postgres 8.3.9) > --youll need your own connect string to repeat: > select dblink_connect_u('myconn1', 'hostaddr=127.0.0.1 port=5432 > dbname=postgres user=postgres password=x'); > select dblink_send_query('myconn1', 'create temp table bah(s int8);'); > select pg_sleep(.3); > SELECT * FROM dblink_get_result('myconn1', true ) as x (t text); > => > ********** Error ********** > remote query result rowtype does not match the specified FROM clause > rowtype Hmm. I can reproduce this in 8.4 too, but it seems OK in 9.0. The only one of the 9.0 commits that seems likely to be relevant is http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=56cbb61 1ec749ba867a4cfc09c8b7df0f4446620 which looks a bit overcomplex for a back-patch. Joe, any thoughts? regards, tom lane
On 11/10/2010 03:54 PM, Tom Lane wrote: > "Marc Mamin" <M.Mamin@intershop.de> writes: > >> SELECT * FROM dblink_get_result('myconn1', true ) as x (t text); > >> => >> ********** Error ********** >> remote query result rowtype does not match the specified FROM clause >> rowtype > Hmm. I can reproduce this in 8.4 too, but it seems OK in 9.0. The only > one of the 9.0 commits that seems likely to be relevant is > http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=56cbb611ec749ba867a4cfc09c8b7df0f4446620 > which looks a bit overcomplex for a back-patch. Joe, any thoughts? Sorry for the slow response. I think this issue was fixed by that patch only as a side effect of significant restructuring. The attached one liner against 8.4 seems to fix it. Objections? Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & 24x7 Support