Postgres Pro
Downloads
Home   >   mailing lists

Thread: New SET/autocommit problem

New SET/autocommit problem

From
Bruce Momjian
Date:
Remember how we make SET/SHOW/RESET _not_ be part of a multi-statement
transaction when it is at the start of a transaction and autocommit is
off?

Well, look at this:test=> SET random_page_cost = 2;SETtest=> COMMIT;WARNING:  COMMIT: no transaction in progressCOMMIT

The WARNING happens with SHOW and RESET too.  I wonder if we should
suppress the WARNING of a COMMIT with no statements when autocommit is
off.  This will probably be better for portability, though again, it is
confusing.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073

Re: New SET/autocommit problem

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>     WARNING:  COMMIT: no transaction in progress
>     COMMIT

> The WARNING happens with SHOW and RESET too.  I wonder if we should
> suppress the WARNING of a COMMIT with no statements when autocommit is
> off.

I don't think so; that will make it quite hard to check whether a
transaction is open.
        regards, tom lane

Re: New SET/autocommit problem

From
Sean Chittenden
Date:
> >     WARNING:  COMMIT: no transaction in progress
> >     COMMIT
> 
> > The WARNING happens with SHOW and RESET too.  I wonder if we should
> > suppress the WARNING of a COMMIT with no statements when autocommit is
> > off.
> 
> I don't think so; that will make it quite hard to check whether a
> transaction is open.

I've got tons of these warnings in my logs... is there a programmatic
way of determining if the current session is in a transaction?  Unless
I misunderstood the fix and the commit message, I'm pretty sure that
most of my problem has been fixed in CVS with SET's auto-committing if
it's not in a transaction, but now that there are some intricate rules
with regards to starting transactions, I'd love to provide a DBI
interface into a call that returns whether or not we're in a
transaction to prevent millions of these:

NOTICE:  ROLLBACK: no transaction in progress

-sc

-- 
Sean Chittenden

Re: New SET/autocommit problem

From
Tom Lane
Date:
Sean Chittenden <sean@chittenden.org> writes:
> WARNING:  COMMIT: no transaction in progress

> I've got tons of these warnings in my logs... is there a programmatic
> way of determining if the current session is in a transaction?

Not at present: you have to track it for yourself.  One of the
suggestions on the list for the next frontend/backend protocol revision
(probably in 7.4) is to add a way for the backend to signal its
transaction state: no transaction, in transaction, or in failed
transaction seems like the set of possible states.
        regards, tom lane

Re: New SET/autocommit problem

From
Sean Chittenden
Date:
> > WARNING:  COMMIT: no transaction in progress
> 
> > I've got tons of these warnings in my logs... is there a programmatic
> > way of determining if the current session is in a transaction?
> 
> Not at present: you have to track it for yourself.  One of the
> suggestions on the list for the next frontend/backend protocol revision
> (probably in 7.4) is to add a way for the backend to signal its
> transaction state: no transaction, in transaction, or in failed
> transaction seems like the set of possible states.

That would be fabulous because with autocommit set to off, the
complexity for tracking that in application is getting pretty
gnarly. -sc

-- 
Sean Chittenden