Skip site navigation (1) Skip section navigation (2)

Re: BUG #3628: Wrong schema picked

From: Pedro Gimeno <pgsql-001(at)personal(dot)formauri(dot)es>
To: Heikki Linnakangas <heikki(at)enterprisedb(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #3628: Wrong schema picked
Date: 2007-09-24 16:50:21
Message-ID: 1190652621l.4923l.6l@dirtecnica.formauri.es (view raw or flat)
Thread:
Lists: pgsql-bugs
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.
> 
> 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).

That's the workaround I'm using, yet I find this behaviour quite  
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  
implicitly (e.g. by creating a temporary table), or a schema is deleted  
or renamed, the affected prepared queries are parsed again so that the  
right schema is picked.

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

-- Pedro Gimeno

In response to

pgsql-bugs by date

Next:From: Robert TesslerDate: 2007-09-24 19:13:39
Subject: BUG #3631: cant unsubscribe
Previous:From: Tom LaneDate: 2007-09-24 14:49:44
Subject: Re: Memory Allocation Error

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group