Hi,

I was just going thr. dblink code and noticed that dblink cursors are wrapped 
in their own transaction.

If an application instantiates a transaction to  do 10 things, one of which is 
to fetch a cursor over dblink, how will it work?

IMO the instantiation of transaction block should be left calling application, 
as done with normal cursors. DBLink would have to detect whether or not they 
are in a transaction block and abort accordingly.

Or I misunderstood something?

 Shridhar


Wed, 16 Apr 2003 18:30:33 +0530
Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
DBLink cursors
Shridhar Daithankar wrote:
> I was just going thr. dblink code and noticed that dblink cursors are wrapped 
> in their own transaction.

yup -- been that way since 7.3 was released.

> If an application instantiates a transaction to  do 10 things, one of which is 
> to fetch a cursor over dblink, how will it work?

I guess it won't -- you're the first person who ever complained, so 
quite possibly you're the first who's needed it.

> IMO the instantiation of transaction block should be left calling application, 
> as done with normal cursors. DBLink would have to detect whether or not they 
> are in a transaction block and abort accordingly.
> 
> Or I misunderstood something?

No, you seem to understand correctly. Patches gratefully accepted ;-)

I think someone is working on nested transactions, which IIRC may make 
it into 7.4. In this case, the issue will be moot, no? If not, I will 
take a look at fixing it for 7.4. If you want to ensure that, submit a 
patch. I'd think the best thing to do would be the following:

- detect transaction status
- if not in an explicit transaction block, start one

Joe


Wed, 16 Apr 2003 09:12:51 -0700
Joe Conway <mail@joeconway.com>
Re: DBLink cursors
Joe Conway wrote:
> Shridhar Daithankar wrote:
> >I was just going thr. dblink code and noticed that dblink cursors are 
> >wrapped in their own transaction.
> 
> yup -- been that way since 7.3 was released.
> 
> >If an application instantiates a transaction to  do 10 things, one of 
> >which is to fetch a cursor over dblink, how will it work?
> 
> I guess it won't -- you're the first person who ever complained, so 
> quite possibly you're the first who's needed it.

Won't dblink's attempt to initiate a transaction simply return with a
warning if a transaction is already in progress?  This is what psql
does, and it's also what happens with Perl DBI, so it's only if dblink
freaks out that there will be a problem.

I wouldn't expect it to abort the entire (already running)
transaction upon receipt of a warning...


-- 
Kevin Brown					      kevin@sysexperts.com


Wed, 16 Apr 2003 19:03:42 -0700
Kevin Brown <kevin@sysexperts.com>
Re: DBLink cursors
Kevin Brown wrote:
> Won't dblink's attempt to initiate a transaction simply return with a
> warning if a transaction is already in progress?  This is what psql
> does, and it's also what happens with Perl DBI, so it's only if dblink
> freaks out that there will be a problem.
> 
> I wouldn't expect it to abort the entire (already running)
> transaction upon receipt of a warning...
> 
> 

  It's been a while since I even looked at that code very closely, so I 
took it for granted that Shridhar had tried it and it failed. But I 
guess not; you're right (works on 7.3.2 and 7.4devel):

regression=# begin;
BEGIN
regression=# SELECT dblink_open('rmt_foo_cursor','SELECT * FROM foo');
  dblink_open
-------------
  OK
(1 row)

regression=# SELECT *
regression-# FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c 
text[]);
  a | b |     c
---+---+------------
  0 | a | {a0,b0,c0}
  1 | b | {a1,b1,c1}
  2 | c | {a2,b2,c2}
  3 | d | {a3,b3,c3}
(4 rows)

regression=# SELECT dblink_close('rmt_foo_cursor');
  dblink_close
--------------
  OK
(1 row)

regression=# commit;
COMMIT

Joe


Wed, 16 Apr 2003 19:19:50 -0700
Joe Conway <mail@joeconway.com>
Re: DBLink cursors
Joe Conway wrote:
> Kevin Brown wrote:
> 
>> Won't dblink's attempt to initiate a transaction simply return with a
>> warning if a transaction is already in progress?  This is what psql
>> does, and it's also what happens with Perl DBI, so it's only if dblink
>> freaks out that there will be a problem.
>>
>> I wouldn't expect it to abort the entire (already running)
>> transaction upon receipt of a warning...
> 

[...snip...]

> 
> regression=# SELECT dblink_close('rmt_foo_cursor');

But then again, dblink_close issues a COMMIT, so beware...

Joe


Wed, 16 Apr 2003 19:26:56 -0700
Joe Conway <mail@joeconway.com>
Re: DBLink cursors
Joe Conway wrote:
> Joe Conway wrote:
> >Kevin Brown wrote:
> >
> >>Won't dblink's attempt to initiate a transaction simply return with a
> >>warning if a transaction is already in progress?  This is what psql
> >>does, and it's also what happens with Perl DBI, so it's only if dblink
> >>freaks out that there will be a problem.
> >>
> >>I wouldn't expect it to abort the entire (already running)
> >>transaction upon receipt of a warning...
> >
> 
> [...snip...]
> 
> >
> >regression=# SELECT dblink_close('rmt_foo_cursor');
> 
> But then again, dblink_close issues a COMMIT, so beware...

Ooops.  Forgot about that.

The code change I'd make is to not issue the COMMIT if the previous
BEGIN generated a warning.  Is there any way for dblink to detect
that?



-- 
Kevin Brown					      kevin@sysexperts.com


Wed, 16 Apr 2003 19:39:27 -0700
Kevin Brown <kevin@sysexperts.com>
Re: DBLink cursors
On 16 Apr 2003 at 9:12, Joe Conway wrote:

> Shridhar Daithankar wrote:
> > I was just going thr. dblink code and noticed that dblink cursors are wrapped 
> > in their own transaction.
> 
> yup -- been that way since 7.3 was released.
> 
> > If an application instantiates a transaction to  do 10 things, one of which is 
> > to fetch a cursor over dblink, how will it work?
> 
> I guess it won't -- you're the first person who ever complained, so 
> quite possibly you're the first who's needed it.

Well, actually I am not doing anything with it right now. Just happened to 
notice it.
> 
> > IMO the instantiation of transaction block should be left calling application, 
> > as done with normal cursors. DBLink would have to detect whether or not they 
> > are in a transaction block and abort accordingly.
> > 
> > Or I misunderstood something?
> 
> No, you seem to understand correctly. Patches gratefully accepted ;-)

I'll send later. The postgresql CVS tree is on linux partition..
 
> I think someone is working on nested transactions, which IIRC may make 
> it into 7.4. In this case, the issue will be moot, no? If not, I will 
> take a look at fixing it for 7.4. If you want to ensure that, submit a 
> patch. I'd think the best thing to do would be the following:
> 
> - detect transaction status
> - if not in an explicit transaction block, start one

Any idea how do you detect transaction status? I don't have tried it out but 
any info. to start with would be good.

That is fine. But I would like to put out a huge warning board in dblink 
documentation stating which function triggers a transaction and which function 
commits it.

if a module starts a transaction without knowledge to calling application, that 
is not good. It does not matter whether or not postresql supports nested 
transactions. If it does it will work but that would still be an unforeseen 
situation for the application.

Bye
 Shridhar

--
Brogan's Constant:	People tend to congregate in the back of the church and the	
front of the bus.


Thu, 17 Apr 2003 13:30:13 +0530
"Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
Re: DBLink cursors