Re: BUG #17042: Concurrent Modifications of PostgreSQL Function Can Corrupt Database

PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> My co-worker Martin von Wittich came up with a set of minimal SQL statements
> to trigger the issue:

> bug.sql:

> DROP TYPE foo CASCADE;
> CREATE TYPE foo AS (foo INTEGER);

> CREATE OR REPLACE FUNCTION foobar() RETURNS foo AS $$
> BEGIN
>   RETURN ROW(1)::foo;
> END
> $$
> LANGUAGE plpgsql;

> SELECT foobar();

> In order to trigger the issue, we need to run our statements in parallel:
> psql -f test.sql & psql -f test.sql

This doesn't seem terribly surprising. Occasionally one session's
foobar() will run at an instant where type foo doesn't exist, thanks
to the other session's script having dropped the type and not yet
recreated it. The "cache lookup failed" messages are a bit scary,
but they arise in situations where the type did exist a moment ago
but now it's gone.

There's been occasional proposals to prevent this sort of thing by
obtaining a lock on every type name mentioned in a function and
holding it till (probably) end of transaction. The cost of that,
compared to the value, has dissuaded us from doing it.

regards, tom lane