Postgres Pro
Downloads
Home   >   mailing lists

Thread: why are my SELECTs in transaction?

why are my SELECTs in transaction?

From
Travis Hume
Date:
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?

Re: why are my SELECTs in transaction?

From
Peter Eisentraut
Date:
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

Re: why are my SELECTs in transaction?

From
"Nigel J. Andrews"
Date:
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

Re: why are my SELECTs in transaction?

From
"Nigel J. Andrews"
Date:
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