Collected from the PG bugs email list.
|Reported by||Taylor Brown|
Body of first available message related to this bug follows.
The following bug has been logged on the website: Bug reference: 15544 Logged by: Taylor Brown Email address: (redacted) 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://email@example.com 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
|2018-12-09 02:30:07+00||=?utf-8?q?PG_Bug_reporting_form?=||BUG #15544: Unexpected: "Returned record type does not match expected record type" after ALTER TABLE|
|2018-12-09 15:52:25+00||Tom Lane||Re: BUG #15544: Unexpected: "Returned record type does not match expected record type" after ALTER TABLE|