Re: why are my SELECTs in transaction?

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