Re: Prepared statements fail after schema changes with surprising error

Peter van Hardenberg <pvh(at)pvh(dot)ca> writes:
> A user reported an interesting issue today. After restoring a dump created
> with --clean on a running application in his development environment his
> application started complaining of missing tables despite those tables very
> clearly existing.

> After a little thinking, we determined that this was due to the now-default
> behaviour of Rails to create prepared statements for most queries. The
> prepared statements error out because the old relation they point to is
> missing, but this gives a misleading report thus:

> PG::Error: ERROR: relation "xxx" does not exist

> I'm not sure what the best outcome here would be. A very simple solution
> might be to expand the error message or add a hint to make it descriptive
> enough that a user might be able to figure out the cause on their own
> without happening to have the unusual intersection of Rails and Postgres
> internals knowlege I (unfortunately) possess. A better solution might be to
> attempt to re-prepare the statement before throwing an error.

Works for me ...

regression=# create table z1 (f1 int , f2 int);
CREATE TABLE
regression=# prepare sz1 as select * from z1;
PREPARE
regression=# insert into z1 values(1,2);
INSERT 0 1
regression=# execute sz1;
f1 | f2
----+----
1 | 2
(1 row)

regression=# drop table z1;
DROP TABLE
regression=# create table z1 (f1 int , f2 int);
CREATE TABLE
regression=# insert into z1 values(3,4);
INSERT 0 1
regression=# execute sz1;
f1 | f2
----+----
3 | 4
(1 row)

regards, tom lane