Re: Schema problems RedHat / SuSE 9.3 for version 7.4.8

Hi list/Michael,

Sorry I forgot "reply to all"

It proved to be a problem with the permissions on the table and view! So, the
error that was reported was completely different from the actual error. I do
not know how this can happen, but by making a direct connection to the
database within Zope, I was able to get the real error message.

I will investigate further how the reporting of the messages got confused. It
could be a problem in Zope or in psycopg. If I find something interesting I
will report back to the list.

Thanks for the help.

Dick

Op vrijdag 2 september 2005 00:04, schreef Michael Fuhr:
> [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?