Re: [GENERAL] [Q] rollback after select?

[Martijn van Oosterhout]

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   klep...@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.


signature.asc
Description: Digital signature

[GENERAL] [Q] rollback after select?

[V S P]

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
  torea...@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

Re: [GENERAL] [Q] rollback after select?

[V S P]

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 torea...@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
   torea...@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
  torea...@fastmail.fm

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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general