On Wed, Nov 4, 2015 at 1:31 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Victor Blomqvist <v...@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 >
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
