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

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.

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