=?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