Do you have some advice how to design my functions to work around this problem?
If I understand your conversation correct the problem is returning the rowtype users from the function. If so, I can think of two workarounds (both quite inconvenient and complex): 1. Use RETURNS TABLE(...) together with not selecting * in the functions. 2. Use RETURNS <custom type> also without select * in the functions. What do other people do in this situation? For our system the lowest load is in the late night, 04 - 06, which might have sufficiently low load to avoid the issue, but I would much prefer to run schema changes when there are people in the office. /Victor On Mon, Oct 12, 2015 at 10:15 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 10/12/2015 06:53 AM, Tom Lane wrote: > >> Andres Freund <and...@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.kla...@aklaver.com >
