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

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