Hi all,
On a Debian Buster/64 Bit I run Postgres 11 (apt-cache info):
Package: postgresql-11
Version: 11.7-0+deb10u1
Maintainer: Debian PostgreSQL Maintainers <team+postgresql@tracker.debian.org>
I tried to update the running server by executing the following SQL update script using psql:
---8<-----------------------------------------------
BEGIN;
-- add a column to an existing table
-- add a new table
-- add several db functions
-- replace a DB function:
DROP FUNCTION public.get_result2(mytaskid bigint, OUT data bytea, OUT metadata jsonb, OUT errortext text, OUT vanished
boolean);
CREATE FUNCTION public.get_result2(mytaskid bigint, OUT data bytea, OUT metadata jsonb, OUT errortext text, OUT
vanishedboolean) RETURNS record
[…]
COMMIT;
---8<-----------------------------------------------
About ~350 clients were connected to the server when I ran the above script, a few of them using, inter alia, the
functionget_result2() which ought to be replaced.
Immediately after running the script, the log was filled with errors
---8<-----------------------------------------------
ERROR: cache lookup failed for function 1821571
CONTEXT: PL/pgSQL function get_result2(bigint) while casting return value to function's return type
STATEMENT: SELECT data, metadata, errortext, vanished FROM get_result2(26671107)
---8<-----------------------------------------------
Interestingly, some clients were reporting errors while trying to call a /different/ DB function which had not been
touchedby the above script.
According to some older reports I found searching the internet for the error, the pg_catalog was probably damaged. As
arestart of the daemon didn't help, and following the recommendations in the older reports, I dumped the data (no idea
ifit was damaged, too, though) and re-installed the cluster form scratch. It now again works as expected using the
modifiedschema.
I would agree that updating the schema of a database server under considerable load is not the best idea, but it should
*never*damage the database itself (it would be acceptable if the transaction just fails, though).
Any idea what happened here, i.e. what caused the error, and how I can avoid it?
Thanks in advance,
Albrecht.