Обсуждение: why are my SELECTs in transaction?
Inflight=# select usename from pg_user; usename --------- fc luser ping (3 rows) Inflight=# select * from badtable; ERROR: Relation "badtable" does not exist Inflight=# select usename from pg_user; ERROR: current transaction is aborted, queries ignored until end of transaction block So at this point all selects are screwed unless I issue a ROLLBACK; Why? Is there a way for SELECTs (or all SQL) statement that fail to NOT have this behaviour?
Travis Hume writes: > So at this point all selects are screwed unless I issue a ROLLBACK; > Why? Is there a way for SELECTs (or all SQL) statement that fail to NOT > have this behaviour? You probably set autocommit to off. Turn it on. -- Peter Eisentraut peter_e@gmx.net
On Thu, 19 Jun 2003, Peter Eisentraut wrote: > Travis Hume writes: > > > So at this point all selects are screwed unless I issue a ROLLBACK; > > Why? Is there a way for SELECTs (or all SQL) statement that fail to NOT > > have this behaviour? > > You probably set autocommit to off. Turn it on. Actually, I've been having a oddity along those lines on 7.3.3 Running a script as from a shell scipt as: psql blah <<EOF create function ... select myfunction... drop function... EOF Was running the function, as evidenced by the logs and the output tuples from the select and yet the changes the (grant all on table <table|seq|view> to another) where not being commited. This was without changing from the default autocommit is off mode. I also had the the similar situation where I had a request to allow something to be tested which meant I ran an update hitting 5 tuples in a table containing approximately 120. Having done that I then get the request _again_. I look back at the data and find the update wasn't commited. This instance was in an interactive psql session and I believe I managed to repeat the samething (update .... then \q) a second time with the same [lack of] effect. Third time it worked. I'm a little vague on that particular example though as there's been so much water under the bridge since Tuesday when it was. However, as the first example is in a script I will be running again and it turned up pretty consistently before, I'm sure that that will be reproducable. That is unless the '\q' sequences I've added to the end don't make it work. -- Nigel J. Andrews
On Thu, 19 Jun 2003, Nigel J. Andrews wrote: > On Thu, 19 Jun 2003, Peter Eisentraut wrote: > > > Travis Hume writes: > > > > > So at this point all selects are screwed unless I issue a ROLLBACK; > > > Why? Is there a way for SELECTs (or all SQL) statement that fail to NOT > > > have this behaviour? > > > > You probably set autocommit to off. Turn it on. > > Actually, I've been having a oddity along those lines on 7.3.3 > > Running a script as from a shell scipt as: > > ... Ah, sorry, I think I might have got confused with an email I saw yesterday. I only glanced at the content of this one before replying making what appear to be some wrong assumptions about the subject matter from the subject line. -- Nigel J. Andrews