Re: Schema problems RedHat / SuSE 9.3 for version 7.4.8

[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