Re: Drop or alter column under load give ERROR #42804 structure of query does not match function result type:

Victor Blomqvist <vb@viblo.se> writes:
> In case any of you are interested of recreating this problem, I today had
> the time to create a short example that reproduce the error every time I
> try.

Hmm.  If you just do that serially:

regression=# select * from select_a() ;
 id | x
----+---
(0 rows)

regression=# alter table a add column y text;
ALTER TABLE
regression=# select * from select_a() ;
 id | x | y
----+---+---
(0 rows)

regression=# alter table a drop column y;
ALTER TABLE
regression=# select * from select_a() ;
 id | x
----+---
(0 rows)

So actually, we *are* tracking the change of table rowtype, both at the
level of the query inside the function and at the level of the function
result.  The problem is that the instant at which the result rowtype of
the function is determined (while parsing the outer query) is different
from the instant at which the inner query's result rowtype is determined.

I'm not really sure that there's anything we can, or even should, try
to do about this.  There would be a whole lot of action-at-a-distance
involved and it would be likely to make some other use-cases worse.

A possible user-level answer if you need to make an application robust
against this sort of thing is to take out a low-grade lock on the
table that's determining the function's result type:

begin;
lock table a in access share mode;
select * from select_a();
commit;

Holding the table lock will prevent any other transactions from altering
the table's rowtype while this transaction runs.

                        regards, tom lane