BUG #18763: pg_get_viewdef returns an old def with new relation name

On Thu, 2 Jan 2025 at 15:19, PG Bug reporting form
<noreply@postgresql.org> wrote:

The following bug has been logged on the website:

Bug reference: 18763
Logged by: lyz
Email address: linyuzhe.lyz@gmail.com
PostgreSQL version: 13.7
Operating system: Linux
Description:

in first connection

test5=# create table a1(c int);
CREATE TABLE
test5=# create table a2(c int);
CREATE TABLE
test5=# create view v as select * from a1;
CREATE VIEW

then rename relation names and replace view in one txn, but not commit.

test5=# begin;
BEGIN
test5=*# alter table a1 rename to a3;
ALTER TABLE
test5=*# alter table a2 rename to a1;
ALTER TABLE
test5=*# create or replace view v as select * from a1;
CREATE VIEW

in second connection:

test5=# select pg_get_viewdef('v');

it will be stuck by AccessShareLock of 'v'.

Then we commit txn in first connection,
the result of second connection wii be:

pg_get_viewdef
----------------
SELECT a3.c +
FROM a3;
(1 row)

old definition but using new relation name.

Reproduced here on HEAD. I also reproduce this with `begin transaction
isolation level serializable;`, so this is likely not an isolation
issue...

--
Best regards,
Kirill Reshke

It seems like an old, raw viewdef has already been obtained from pg_rewrite
before it was blocked by first connection.
Then in `get_query_def`, it tries to parse the raw viewdef.
Before finishing its work, `get_query_def` will acquire locks and wait
until first connection finishes.
After that, during the parsing process, relation names are finally
generated by `generate_relation_name` in ruleutils.c using
`SearchSysCache1`.
And `SearchSysCache1` will use the catalog snapshot to get the latest
relname.
Perhaps we should refer to the code of `pg_get_constraintdef_worker`,
directly using `systable_beginscan` with transaction snapshot?

Regards
Lin Yuzhe