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

On Wed, Nov 4, 2015 at 1:31 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Victor Blomqvist <vb(at)viblo(dot)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
>

Ok, then I dont hope for a fix in a future Postgres version.

Given this problem it seems like its generally a bad idea to ever ALTER
anything that is returned from a function, unless you want to add a lock
around the function call (which get a bit unpractical if you have many
functions, especially if they are nested). I wonder if it might be good to
mention this in the docs about the different RETURNs? On the other hand
maybe it only affects a very limited amount of users..

Thanks for the input so far!
/Victor