Thread: BUG #15544: Unexpected: "Returned record type does not match expectedrecord type" after ALTER TABLE

The following bug has been logged on the website:

Bug reference:      15544
Logged by:          Taylor Brown
Email address:      taylor@youneedabudget.com
PostgreSQL version: 10.6
Operating system:   Ubuntu 18
Description:

If you define a function to return a type of "<some table name>", and then
alter the table to add a column, that function will immediately begin to
throw an error: "Returned record type does not match expected record type".
This error is not present on any new connections to the database.

Minimal repro follows:
---------
CREATE TABLE foo (    
    col1 int
);

CREATE OR REPLACE FUNCTION get_foo_object (p_entity foo)
  RETURNS "foo"
AS $$
BEGIN
  RETURN p_entity;
END;
$$
LANGUAGE plpgsql;

SELECT get_foo_object((SELECT NULL::foo));

ALTER TABLE foo add column col2 int;

SELECT get_foo_object((SELECT NULL::foo));

-- Results in this error:
-- ERROR:  42804: returned record type does not match expected record type
DETAIL:  Number of returned columns (1) does not match expected column count
(2). CONTEXT:  PL/pgSQL function get_foo_object(foo) while casting return
value to function's return type LOCATION:  convert_tuples_by_position,
tupconvert.c:138

------------------

The only way to fix the issue and use the function again is disconnect and
reconnect to the database (new sessions work fine). This leads me to believe
that there is a cached definition of this table type that is not being
refreshed when the table is altered. It's worth noting that when this
happened to us, not even dropping and recreating the function appeared to be
enough to fix the issue on existing connections. In our (harrowing)
experience, we had to kill and recreate our connection to Postgres to get
the issue fixed.

In our Googling, this bug appeared similar to this report:
https://www.postgresql.org/message-id/20161110161247.8769.64528@wrigleys.postgresql.org

Lastly, a bit of very sincere flattery: Thank you, thank you, thank you for
Postgres. The fact that this is the first bug we've ever run into with this
database, especially considering how much we've used it, is staggering, and
quite the compliment to the Postgres devs.

Thanks,

Taylor Brown
CTO
YouNeedABudget.com

=?utf-8?q?PG_Bug_reporting_form?= <noreply@postgresql.org> writes:
> If you define a function to return a type of "<some table name>", and then
> alter the table to add a column, that function will immediately begin to
> throw an error: "Returned record type does not match expected record type".
> This error is not present on any new connections to the database.

Yeah, plpgsql has historically been unfriendly to intrasession changes
in the rowtypes of composite-typed variables or parameters, because
it sets up internal data structures that depend on those details and
doesn't have a way to update them.  This is improved in v11 --- I won't
claim it's perfect yet, but at least it seems to handle this example
without problems.  Prior to v11, you'd have to start a new session or
do CREATE OR REPLACE FUNCTION to make this work.

> The only way to fix the issue and use the function again is disconnect and
> reconnect to the database (new sessions work fine). This leads me to believe
> that there is a cached definition of this table type that is not being
> refreshed when the table is altered. It's worth noting that when this
> happened to us, not even dropping and recreating the function appeared to be
> enough to fix the issue on existing connections.

Really?  Replacing the function ought to be enough, I should think
(and it is, when I try your example on v10).  I'd be interested to
see a fleshed-out example where it isn't.

            regards, tom lane