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
