Обсуждение: BUG #3628: Wrong schema picked

The following bug has been logged online:

Bug reference:      3628
Logged by:          Pedro Gimeno
Email address:      pgsql-001@personal.formauri.es
PostgreSQL version: 8.2.4
Operating system:   Linux (Debian stable + backports)
Description:        Wrong schema picked
Details:

When a function has a SQL statement to execute that has an unqualified
table, that SQL statement doesn't always pick the table from a schema in the
search_path. The following script is an example:

-- begin script
create database test2;
\c test2
create language 'plpgsql';
create schema schema1;
create schema schema2;
create table schema1.table1 (column1 varchar(10));
create table schema2.table1 (column1 varchar(10));
create function public.f1() returns void as $$
begin
  insert into table1 values (current_schema());
end;
$$ language plpgsql;
set search_path=schema1,public;
select f1();
set search_path=schema2,public;
select f1();
select * from schema1.table1;
select * from schema2.table1;
-- end script

The output of the last couple of SELECT statements is:

 column1
---------
 schema1
 schema2
(2 rows)

 column1
---------
(0 rows)

while the expected result would be:

 column1
---------
 schema1
(1 row)

 column1
---------
 schema2
(1 row)

-- Pedro Gimeno

Pedro Gimeno wrote:
> When a function has a SQL statement to execute that has an unqualified
> table, that SQL statement doesn't always pick the table from a schema in the
> search_path.

The first time the function is run, all the statements in it are planned
and the schema used for the insert is resolved. Subsequent calls will
reuse the same plan. The obvious fix is to always explicitly specify the
schema in the INSERT. Or if if you want it to depend on the search_path,
you can execute it dynamically using EXECUTE
(http://www.postgresql.org/docs/8.2/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN).

--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

Heikki Linnakangas wrote:

> Pedro Gimeno wrote:
> > When a function has a SQL statement to execute that has an
> > unqualified table, that SQL statement doesn't always pick the table
> > from a schema in the search_path.
>=20
> The first time the function is run, all the statements in it are
> planned and the schema used for the insert is resolved. Subsequent=20=20
> calls will reuse the same plan. The obvious fix is to always=20=20
> explicitly specify the schema in the INSERT. Or if if you want it to=20=
=20
> depend on the search_path, you can execute it dynamically using=20=20
> EXECUTE=20=20
> (http://www.postgresql.org/docs/8.2/static/plpgsql-statements.html#PLPGSQ=
L-STATEMENTS-EXECUTING-DYN).

That's the workaround I'm using, yet I find this behaviour quite=20=20
unexpected. Using EXECUTE prevents the benefit of using prepared
statements until search_path changes or a schema is deleted or renamed,
not to mention the impact on readability.

What I expect is that when search_path changes, either explicitly or=20=20
implicitly (e.g. by creating a temporary table), or a schema is deleted=20=
=20
or renamed, the affected prepared queries are parsed again so that the=20=
=20
right schema is picked.

This would allow using a common schema for functions instead of=20=20
defining them once in every schema in which to apply them, in cases=20=20
(like mine) where the same operations have to be applied to different=20=20
schemas.

-- Pedro Gimeno

Heikki Linnakangas wrote:

> The first time the function is run, all the statements in it are=20=20
> planned and the schema used for the insert is resolved. Subsequent=20=20
> calls will reuse the same plan.

This post is apparently related:

http://archives.postgresql.org/pgsql-hackers/2007-02/msg00933.php

-- Pedro Gimeno