Quantcast

dblink_get_result issue

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

dblink_get_result issue

Marc Mamin-2
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

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: dblink_get_result issue

Marc Mamin-2
> (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: '[hidden email]'
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

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: dblink_get_result issue

Tom Lane-2
In reply to this post by Marc Mamin-2
"Marc Mamin" <[hidden email]> 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

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: dblink_get_result issue

Marc Mamin-2
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:[hidden email]]
Sent: Donnerstag, 11. November 2010 00:54
To: Marc Mamin
Cc: [hidden email]; Joe Conway
Subject: Re: [GENERAL] dblink_get_result issue

"Marc Mamin" <[hidden email]> 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

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: dblink_get_result issue

Joe Conway
In reply to this post by Tom Lane-2
On 11/10/2010 03:54 PM, Tom Lane wrote:
> "Marc Mamin" <[hidden email]> 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

dblink.2010.11.13.1.diff (796 bytes) Download Attachment
signature.asc (917 bytes) Download Attachment
Loading...