[Please copy the mailing list on replies so others can contribute to and learn from the discussion. I've quoted more of your message than I ordinarily would because other people won't have seen it and they won't find it in the list archives.]
On Thu, Sep 01, 2005 at 11:35:43PM +0200, Dick Kniep wrote: > After starting psql, and executing the query, without a begin, after the > query > there is no search path > > SELECT set_config('search_path', '"' || t2.schema || '"', true) FROM > "Lindix"."Gebruikers" as t1, "Lindix"."Administratie" as t2 WHERE uid = > 'zon0023' AND t1.administratie_id = t2.administratie_id; > set_config > ------------------ > "adeuxproductie" > (1 row) > > cvix=# SHOW search_path; > search_path > -------------- > $user,public > (1 row) Apparently you're in autocommit mode, which is the default for psql. Each statement is its own transaction, so you won't see the effects of set_config() when the third argument is true. > Executed with third parameter false: > > cvix=# SELECT set_config('search_path', '"' || t2.schema || '"', false) FROM > "Lindix"."Gebruikers" as t1, "Lindix"."Administratie" as t2 WHERE uid = > 'zon0023' AND t1.administratie_id = t2.administratie_id; > set_config > ------------------ > "adeuxproductie" > (1 row) > > cvix=# SHOW search_path; > search_path > ------------------ > "adeuxproductie" > (1 row) > > Also the same result when I have a "begin" before the first statement. Which > means that it seems to work correctly! Yep. If you're in a transaction block, or if you tell set_config() not to make the change local to the transaction, then you see the new setting take effect. > Also, a thing I hadn't checked before, is that the psql results on the 2 > servers are the same. Which leads to my conclusion that the autocommit > settings are indeed different on the 2 servers. What do "SELECT version()" and "SHOW autocommit" show on both servers? If both servers are running 7.4 then they can't have different autocommit settings because 7.4 and later don't support server-side autocommit (it always shows "on" and you can't change it). Unless one of the servers is running 7.3, the autocommit settings must be on the client side. Are you using the same instance of the client to connect to both servers? > OK, next question, how do I get rid of the autocommit in my application? I > tried set autocommit to off; but that is deprecated. Using "SET autocommit" attempts to change the server-side setting, which was only supported in 7.3 (the developers removed it after deciding it had been a bad idea). How to disable autocommit on the client side depends on your client interface. What language and API are you using? -- Michael Fuhr ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster