Re: [HACKERS] Bug in prepared statement cache invalidation? - Mailing list pgsql-hackers

On Fri, Apr 28, 2017 at 3:01 AM, Konstantin Knizhnik
<k.knizhnik@postgrespro.ru> wrote:
> I find out that now Postgres correctly invalidates prepared plans which
> directly depend on altered relation, but doesn't invalidate plans having
> transitive (indirect) dependencies.

I think the problem here is that replanning and recompiling are two
different things.  Here's a slightly different example:

rhaas=# create table foo (a int, b text);
CREATE TABLE
rhaas=# create table bar (a text, b int);
CREATE TABLE
rhaas=# create function quux() returns void as $$declare f foo; begin
select * from foo into f limit 1; raise notice '%', f; end$$ language
plpgsql;
CREATE FUNCTION
rhaas=# insert into foo values (1, 'one');
INSERT 0 1
rhaas=# insert into bar values ('two', 2);
INSERT 0 1
rhaas=# select quux();
NOTICE:  (1,one)quux
------

(1 row)

rhaas=# begin;
BEGIN
rhaas=# alter table foo rename to snazzy;
ALTER TABLE
rhaas=# alter table bar rename to foo;
ALTER TABLE
rhaas=# select quux();
ERROR:  invalid input syntax for integer: "two"
CONTEXT:  PL/pgSQL function quux() line 1 at SQL statement

So what has happened here is that the query "SELECT * FROM foo" inside
of quux() got replanned, but the variable f declared by quux() as
being type foo did not get changed to match the new definition of type
foo.  The plancache is working just fine here; it correctly identifies
each statement that needs re-planning and performs that re-planning as
required.  The problem is that this only affects the statements
themselves, not other PL-created data structures like local variables.
The PL's cache of compiled functions is the issue, not the plancache.
I think that's also the problem in your test case.  Your test case
doesn't explicitly involve a variable but I think there must be
something like that under the hood.

This problem has been discussed before but nobody's done anything
about it.  The problem is a bit tricky because the core system doesn't
know anything about the function caches maintained by individual PLs.
I suppose ideally there'd be a way for a PL to say "if the definition
of X changes, please tell me to recompile function Y".  That probably
wouldn't be perfect because the PL might not be able to figure out
everything on which they actually depend; that might be
Turing-complete in some cases.  But even a partial solution would
probably be welcomed by users.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company