Thread: Schema problems RedHat / SuSE 9.3 for version 7.4.8
Hi list,
we are using schema's to separate different administrations, and are using
Zope/Plone as a front-end portal to the database.
In this installation almost the first statement is:
cvix=# SELECT set_config('search_path', '"' || t2.schema || '"', true)
cvix-# FROM "Lindix"."Gebruikers" as t1, "Lindix"."Administratie" as t2
cvix-# WHERE uid = 'zon0023'
cvix-# AND t1.administratie_id = t2.administratie_id;
set_config
------------------
"testschema"
(1 row)
As you can see, I have a fixed schema called "Lindix" where the actual
destination schema is in a table. Depending on the user, the search_path is
set and it should be able to find the table.
Now we have installed a new server, with the same db version, the same content
(a restore from the original db) and the same coding.
After setting the search path the query
Select * from vwexternetoegang
produces the required results in the first installation, but in the new
installation, it cannot find the view. However, if I do an explicit
Set search_path to "testschema";
it works as expected.
I can reproduce the result not only from my own code, but also from psql.
The only real difference between the 2 installations I see is that the working
installation has a RedHat Enterprise Linux ES Release 4 (Nahant update1)
version versus the new (not working) a SuSE 9.3 installation.
For both installations I have compiled Postgresql from the standard source as
is delivered from www.postgresql.org (or one of the mirrors). Difference is
that the first installation dates back to june 27th and the new installation
from august 20th.
Other difference is that in the new situation, the postgres database is on the
same machine as the Zope application is.
Hope someone has a solution for this, because the new machine should be
launched monday.
On Thu, Sep 01, 2005 at 06:13:13PM +0200, Dick Kniep wrote:
>
> cvix=# SELECT set_config('search_path', '"' || t2.schema || '"', true)
> cvix-# FROM "Lindix"."Gebruikers" as t1, "Lindix"."Administratie" as t2
> cvix-# WHERE uid = 'zon0023'
> cvix-# AND t1.administratie_id = t2.administratie_id;
> set_config
> ------------------
> "testschema"
> (1 row)
Using quote_ident(t2.schema) would be cleaner and more reliable
than quoting t2.schema yourself, and the final "true" argument to
set_config() means that the setting is local to the current
transaction.
> Now we have installed a new server, with the same db version, the same content
> (a restore from the original db) and the same coding.
>
> After setting the search path the query
>
> Select * from vwexternetoegang
>
> produces the required results in the first installation, but in the new
> installation, it cannot find the view.
Have you used "SHOW search_path" to see what the setting is after
executing the above command? Is the new installation using autocommit
mode? If so then the setting won't take effect because you've told
set_config() that the setting is local to the current transaction,
and as soon as the SELECT completes that transaction is done. If
you want the setting to persist then pass "false" as the last argument
to set_config().
> I can reproduce the result not only from my own code, but also from psql.
Are you saying that the query works as expected with psql on the
old server, but not on the new server? If you run "\set" in psql,
what are the two servers' AUTOCOMMIT settings? If this is an
autocommit issue then it must be on the client side, because 7.4
removed support for server-side autocommit and your Subject header
says you're running 7.4.8.
--
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?
--
Michael Fuhr
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?
Hi list,
Again after reconsidering the way this problem came to be, it struck me that I
used a pg_dump -f /tmp/dumpdb and restored with psql. How can it be that not
all grants of the schema's, views and tables are restored? There were no
messages during the restore that could be interpreted as an indication that
the grants were not restored.
Op vrijdag 2 september 2005 14:35, schreef Dick Kniep:
> 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?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
On Sat, Sep 03, 2005 at 04:07:34PM +0200, Dick Kniep wrote: > Again after reconsidering the way this problem came to be, it struck me that I > used a pg_dump -f /tmp/dumpdb and restored with psql. How can it be that not > all grants of the schema's, views and tables are restored? There were no > messages during the restore that could be interpreted as an indication that > the grants were not restored. Do the necessary GRANT statements appear in the dump? What permissions do you see if you restore the dump again into a newly-created database? Among other ways, you can use psql's \z command to see permissions (you might need use "\z schemaname." or set your search_path to see what you're interested in). -- Michael Fuhr