Thread: dblink not returning result

I am using Postgresql 9.1. I found that dblink is not returning result for BEGIN transaction.


------------------------------------------------------------
select new_conn('conn1');
select new_conn('conn2');

select dblink_send_query('conn1','begin;  update t2 set i=10 where nam=''a1'';');
select dblink_send_query('conn2','begin;  delete from t2 where nam=''a1'';');

pgdb=# select * from lock_vw;
 datname | relname | transactionid |       mode       | granted | usename  |         current_query          |          query_start          |       age       | procpid
---------+---------+---------------+------------------+---------+----------+--------------------------------+-------------------------------+-----------------+---------
 pgdb    | t2      |               | RowExclusiveLock | t       | postgres | <IDLE> in transaction          | 2011-11-03 12:26:17.175681+06 | 00:00:04.05115  |    9414
 pgdb    | t2      |               | ExclusiveLock    | t       | postgres | begin;  delete from t2 where n | 2011-11-03 12:26:17.180294+06 | 00:00:04.046537 |    9415
 pgdb    | t2      |               | RowExclusiveLock | t       | postgres | begin;  delete from t2 where n | 2011-11-03 12:26:17.180294+06 | 00:00:04.046537 |    9415
(3 rows)


select dblink_send_query('conn1','select i,nam from t2')

select * from dblink_get_result('conn1') as t2(i int, nam text);
ERROR:  function return row and query-specified return row do not match
DETAIL:  Returned row contains 1 attribute, but query expects 2.
select dblink_send_query('conn2','rollback');

-- Supposed to send result here
pgdb=# select * from dblink_get_result('conn1') as t2(i int, nam text);
 i | nam
---+-----
(0 rows)

pgdb=# select * from t2;
 i |  nam 
---+-------
 1 | asdas
 2 | a1
(2 rows)
------------------------------------------------------------------

Could any one please tell me why?

Hi,

 From reading your email and from the URL below, it seems that the error
is because it's returning the status of the first call (where you are
beginning the transaction and making the first query).

http://www.postgresql.org/docs/9.1/static/contrib-dblink-get-result.

Have you tried doing a simple SELECT expecting a one-row-one-column
after each call ? (before you make the next SELECT) ?

--
Robins Tharakan

On 11/03/2011 12:56 PM, AI Rumman wrote:
> ------------------------------------------------------------
> select new_conn('conn1');
> select dblink_send_query('conn1','begin;  update t2 set i=10 where
> nam=''a1'';');
> select dblink_send_query('conn1','select i,nam from t2')
>
> select * from dblink_get_result('conn1') as t2(i int, nam text);
> ERROR:  function return row and query-specified return row do not match
> DETAIL:  Returned row contains 1 attribute, but query expects 2.
> select dblink_send_query('conn2','rollback');
>
> *-- Supposed to send result here*
> pgdb=# select * from dblink_get_result('conn1') as t2(i int, nam text);
>   i | nam
> ---+-----
> (0 rows)
>
> pgdb=# select * from t2;
>   i |  nam
> ---+-------
>   1 | asdas
>   2 | a1
> (2 rows)
> ------------------------------------------------------------------
>
> Could any one please tell me why?

This message has been digitally signed by the sender.