Обсуждение: BUG #1858: setting search path in select doesn't (always) work

The following bug has been logged online:

Bug reference:      1858
Logged by:          D.J. Kniep
Email address:      dick.kniep@lindix.nl
PostgreSQL version: 7.4.8
Operating system:   Linux (SuSE 9.3) /RedHat ES 4
Description:        setting search path in select doesn't (always) work
Details:

we have been running a Zope site with Postgresql 7.4.8 for a while. 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
------------------
 "adeuxproductie"
(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 will 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.

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

"D.J. Kniep" <dick.kniep@lindix.nl> writes:
> 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.

What does "show search_path" report in the failing and non-failing states?
Also try "select current_schemas(true)".

            regards, tom lane

On Fri, Sep 02, 2005 at 10:58:12AM -0400, Tom Lane wrote:
> "D.J. Kniep" <dick.kniep@lindix.nl> writes:
> > 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.
>
> What does "show search_path" report in the failing and non-failing states?
> Also try "select current_schemas(true)".

This thread came up in pgsql-general yesterday; Dick says the real
problem was something else.

http://archives.postgresql.org/pgsql-general/2005-09/msg00074.php

--
Michael Fuhr

Hi list,

I reported the bug because I was convinced it was something fishy, however,
the bug I reported proved to be something completely else. I don't even know
if it is a bug at all.  If you do a set search_path; the path is set
correctly according to the rules of autocommit and everything else.

If however you are not authorized to use the schema, the next query on a table
within the requested schema results in a table not found condition, which
makes sense, because it actually cannot find the table, because it cannot
enter the schema.

I have checked the way psycopg handles this, and if a select is done on the
table this is working correctly,

 File "/home/dick/CVix/CVix/src/ObjectBuilder.py", line 516, in __BldTabDef
    cur.execute(Sqlstmt)
psycopg.ProgrammingError: ERROR:  permission denied for schema adeuxproductie

Select * from "adeuxproductie"."parameters" WHERE 1=0

so the problem must be within Zope and I will report this problem to the Zope
community.

Thanks for all your help.

Op vrijdag 2 september 2005 17:12, schreef Michael Fuhr:
> On Fri, Sep 02, 2005 at 10:58:12AM -0400, Tom Lane wrote:
> > "D.J. Kniep" <dick.kniep@lindix.nl> writes:
> > > 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.
> >
> > What does "show search_path" report in the failing and non-failing
> > states? Also try "select current_schemas(true)".
>
> This thread came up in pgsql-general yesterday; Dick says the real
> problem was something else.
>
> http://archives.postgresql.org/pgsql-general/2005-09/msg00074.php

Cheers,
Dick Kniep