Skip to content

2.25.1.0-b312

2.25.1.0-b312: [#25901] YSQL: create or replace view should increment catalog version
@myang2021 myang2021 tagged this 06 Feb 23:24 
Summary:
In native PG 15.2:
(1) In session 1:
```
postgres=# create table foo(a int, b int);
CREATE TABLE
postgres=# insert into foo values (1, 2);
INSERT 0 1
postgres=# create view v as select a, b from foo;
CREATE VIEW
```
(2) In session 2:
```
postgres=# select * from v;
 a | b
---+---
 1 | 2
(1 row)
```
(3) Back to session 1:
```
postgres=# create or replace view v as select b as a, a as b from foo;
CREATE VIEW
```
(4) Back to session 2:
```
postgres=# select * from v;
 a | b
---+---
 2 | 1
(1 row)
```
We can see step 4 just see the new result.

In YB (master branch)

(1) In session 1:
```
yugabyte=# create table foo(a int, b int);
CREATE TABLE
yugabyte=# insert into foo values (1, 2);
INSERT 0 1
yugabyte=# create view v as select a, b from foo;
CREATE VIEW
```
(2) In session 2:
```
yugabyte=# select * from v;
 a | b
---+---
 1 | 2
(1 row)
```
(3) Back to session 1:
```
yugabyte=# create or replace view v as select b as a, a as b from foo;
CREATE VIEW
```
(4) Back to session 2:
```
yugabyte=# select * from v;
 a | b
---+---
 1 | 2
(1 row)
```

We can see step 4 still show the old result.

This is because the stale view definition must be cached in session 2's PG
backend catalog cache, which still has (a, b), not (b, a) in the view definition.

To fix this bug, I made a change so that create or replace view DDL statement is
going to increment catalog version.

New unit test added that would fail before this diff.
Jira: DB-15213

Test Plan: ./yb_build.sh --cxx-test pg_catalog_version-test --gtest_filter PgCatalogVersionTest.CreateOrReplaceView

Reviewers: kfranz, sanketh, mihnea

Reviewed By: kfranz

Subscribers: jason, yql

Differential Revision: https://phorge.dev.yugabyte.com/D41728
Assets 2
Loading