Обсуждение: [Q] rollback after select?

I have read that
if a given connection has an error,
it first must be rolledback (before any other statement on that
connection can
be executed).

Is this true for Selects as well?
In other words if select fails, should the connection be 'rolledback'?

I am trying to test my error handing
and having problems:

In am multithreaded ODBC c++ app, I do select, it is successful,
 then 'update'

update fails (because I misspelled 'WHERE'), I rollback
but then when I try the same select, the DB says
relation XYZ does not exist (where XYZ is my table)

I do not understand how it is happening.

all my tables are in a new schema I call 'prod', when I connect to the
database
I set right away 'set search_path to prod', assuming that all of the
SQLs now
will be going to 'prod'.

But may be this somehow gets screwed up after a rollback or any other
error?


thanks,
Vlad
--
  V S P
  toreason@fastmail.fm

--
http://www.fastmail.fm - One of many happy users:
  http://www.fastmail.fm/docs/quotes.html

Hi,
I think I resolved my second question (about rollback modifing the
search_path)

I did not realize that I had to issue a commit
after set search_path

so what was happening is that when I did the rollback, the search path
was also gone.

The same problem was with my set client encoding (my other email)
both were related problem to the fact that PG I have to issue commits
even for the 'non sql' statements.


sorry I did not figure this out before posting,
Vlad





On Sun, 29 Mar 2009 20:20 -0400, "V S P" <toreason@fastmail.fm> wrote:
> I have read that
> if a given connection has an error,
> it first must be rolledback (before any other statement on that
> connection can
> be executed).
>
> Is this true for Selects as well?
> In other words if select fails, should the connection be 'rolledback'?
>
> I am trying to test my error handing
> and having problems:
>
> In am multithreaded ODBC c++ app, I do select, it is successful,
>  then 'update'
>
> update fails (because I misspelled 'WHERE'), I rollback
> but then when I try the same select, the DB says
> relation XYZ does not exist (where XYZ is my table)
>
> I do not understand how it is happening.
>
> all my tables are in a new schema I call 'prod', when I connect to the
> database
> I set right away 'set search_path to prod', assuming that all of the
> SQLs now
> will be going to 'prod'.
>
> But may be this somehow gets screwed up after a rollback or any other
> error?
>
>
> thanks,
> Vlad
> --
>   V S P
>   toreason@fastmail.fm
>
> --
> http://www.fastmail.fm - One of many happy users:
>   http://www.fastmail.fm/docs/quotes.html
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
--
  V S P
  toreason@fastmail.fm

--
http://www.fastmail.fm - And now for something completely different…

On Sun, Mar 29, 2009 at 08:44:59PM -0400, V S P wrote:
> The same problem was with my set client encoding (my other email)
> both were related problem to the fact that PG I have to issue commits
> even for the 'non sql' statements.

That's something between you and your database driver. PostgreSQL by
default commits between each statement unless you explicitly start a
transaction.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.