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

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