Re: Drop or alter column under load give ERROR #42804 structure of query does not match function result type:
От | Adrian Klaver |
---|---|
Тема | Re: Drop or alter column under load give ERROR #42804 structure of query does not match function result type: |
Дата | |
Msg-id | 561BC074.9040707@aklaver.com обсуждение исходный текст |
Ответ на | Re: Drop or alter column under load give ERROR #42804 structure of query does not match function result type: (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: Drop or alter column under load give ERROR #42804
structure of query does not match function result type:
(Victor Blomqvist <vb@viblo.se>)
Re: Drop or alter column under load give ERROR #42804 structure of query does not match function result type: (Victor Blomqvist <vb@viblo.se>) |
Список | pgsql-general |
On 10/12/2015 06:53 AM, Tom Lane wrote: > Andres Freund <andres@anarazel.de> writes: >> On 2015-10-09 14:32:44 +0800, Victor Blomqvist wrote: >>> CREATE FUNCTION select_users(id_ integer) RETURNS SETOF users AS >>> $$ >>> BEGIN >>> RETURN QUERY SELECT * FROM users WHERE id = id_; >>> END; >>> $$ LANGUAGE plpgsql; > >> My guess is that the problem here is that table level locking prevents >> modification of the "users" type when the table is used, but there's no >> locking preventing the columns to be dropped while the function is >> used. So what happens is that 1) the function is parsed & planned 2) >> DROP COLUMN is executed 3) the contained statement is executed 4) a >> mismatch between the contained statement and the function definition is >> detected. > > The query plan as such does get refreshed, I believe. The problem is that > plpgsql has no provision for the definition of a named composite type to > change after a function's been parsed. This applies to variables of named > composite types for sure, and based on this example I think it must apply > to the function result type as well, though I'm too lazy to go check the > code right now. That makes sense. The problem is that I cannot square that with Albe's example, which I tested also: " Session 1: test=> CREATE TABLE users (id integer PRIMARY KEY, name varchar NOT NULL, to_be_removed integer NOT NULL); CREATE TABLE test=> CREATE FUNCTION select_users(id_ integer) RETURNS SETOF users AS $$BEGIN RETURN QUERY SELECT * FROM users WHERE id = id_; END;$$ LANGUAGE plpgsql; CREATE FUNCTION Session 2: test=> SELECT id, name FROM select_users(18); id | name ----+------ (0 rows) Ok, now the plan is cached. Now in Session 1: test=> ALTER TABLE users DROP COLUMN to_be_removed; ALTER TABLE Session2: test=> SELECT id, name FROM select_users(18); id | name ----+------ (0 rows) No error. This is 9.4.4. " > > We have had past discussions about fixing this. I believe it would > require getting rid of use of plpgsql's "row" infrastructure for named > composites, at least in most cases, and going over to the "record" > infrastructure instead. In the past the conversations have stalled as > soon as somebody complained that that would probably make some operations > slower. I don't entirely understand that objection, since (a) some other > operations would probably get faster, and (b) performance does not trump > correctness. But that's where the discussion stands at the moment. > > regards, tom lane > > -- Adrian Klaver adrian.klaver@aklaver.com
В списке pgsql-general по дате отправления: