New SET/autocommit problem

Started by Bruce Momjianabout 23 years ago5 messages

pgman@candle.pha.pa.us

about 23 years ago

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;
SET
test=> COMMIT;
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. 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, Pennsylvania 19073

Tom Lane

tgl@sss.pgh.pa.us

about 23 years ago

In reply to: Bruce Momjian (#1)

Re: New SET/autocommit problem

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

Sean Chittenden

sean@chittenden.org

about 23 years ago

In reply to: Tom Lane (#2)

Re: New SET/autocommit problem

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

Tom Lane

tgl@sss.pgh.pa.us

about 23 years ago

In reply to: Sean Chittenden (#3)

Re: New SET/autocommit problem

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

Sean Chittenden

sean@chittenden.org

about 23 years ago

In reply to: Tom Lane (#4)

Re: New SET/autocommit problem

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