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.