Re: [Dbdpg-general] Re: 'prepare' is not quite schema-safe

David Wheeler <david(at)kineticode(dot)com> writes:
> On May 1, 2005, at 21:30 , Neil Conway wrote:
>> An alternative would be to flush dependent plans when the schema
>> search path is changed. In effect this would mean flushing *all*
>> prepared plans whenever the search path changes: we could perhaps
>> keep plans that only contain explicit namespace references, but
>> that seems fragile.

> Yes, but this would be invisible to DBD::Pg and other clients, no?

Depends what you call "invisible" --- Neil is suggesting automatic
replanning of already-prepared queries. To the extent that that
causes behavioral changes (like following a new search path) it
wouldn't be invisible to applications.

On the whole I think that the correct semantics of PREPARE is that
the objects referred to by the query are determined when the PREPARE
is executed, and don't change later on. Compare the following
example:

PREPARE foobar AS SELECT * FROM foo;

EXECUTE foobar;

ALTER TABLE foo RENAME TO bar;

EXECUTE foobar;

ALTER TABLE baz RENAME TO foo;

EXECUTE foobar;

Should the second EXECUTE fail entirely? Should the third select a
perhaps completely different set of columns from the formerly-named baz?
I don't think so. But this is exactly equivalent to the idea that
already-prepared statements should track later changes in search_path.

Here's an even nastier example:

SET search_path = s1, s2;

CREATE TABLE s2.foo ( ... );

PREPARE foobar AS SELECT * FROM foo;

EXECUTE foobar; -- shows contents of s2.foo

CREATE TABLE s1.foo ( ... );

EXECUTE foobar; -- shows contents of ??

I think you could demonstrate that if the spec is "make it look like the
original query was retyped as source each time", then *every* DDL change
in the database potentially requires invalidating every cached plan.
I don't find that a desirable spec.

regards, tom lane