Re: Drop or alter column under load give ERROR #42804 structure of query does not match function result type: - Mailing list pgsql-general
From | Adrian Klaver |
---|---|
Subject | Re: Drop or alter column under load give ERROR #42804 structure of query does not match function result type: |
Date | |
Msg-id | 561D6F35.9020709@aklaver.com Whole thread Raw |
In response to | Re: Drop or alter column under load give ERROR #42804 structure of query does not match function result type: (Victor Blomqvist <vb@viblo.se>) |
List | pgsql-general |
On 10/12/2015 07:53 PM, Victor Blomqvist wrote: > 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. Might want to investigate the record return type: http://www.postgresql.org/docs/9.4/interactive/plpgsql-overview.html#PLPGSQL-ARGS-RESULTS 40.1.2. Supported Argument and Result Data Types "It is also possible to declare a PL/pgSQL function as returning record, which means that the result is a row type whose columns are determined by specification in the calling query, as discussed in Section 7.2.1.4." The section that explains difference between declared type record and returned type record: http://www.postgresql.org/docs/9.4/interactive/plpgsql-declarations.html#PLPGSQL-DECLARATION-RECORDS How to use a returned record in query: http://www.postgresql.org/docs/9.4/interactive/queries-table-expressions.html#QUERIES-TABLEFUNCTIONS See bottom of section. Basically all the above leaves it up to the calling query to 'shape' the output. Not sure if that will work for you. > > 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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote: > > On 10/12/2015 06:53 AM, Tom Lane wrote: > > Andres Freund <andres@anarazel.de <mailto: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 <mailto:adrian.klaver@aklaver.com> > > -- Adrian Klaver adrian.klaver@aklaver.com
pgsql-general by date: