Re: BUG #18892: When the view already exists, CREATE OR REPLACE VIEW does not check whether the table exists.

On Sun, 13 Apr 2025 at 00:00, PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 18892
> Logged by: Gavin
> Email address: gchen(at)s2now(dot)com
> PostgreSQL version: 16.8
> Operating system: Red Hat Enterprise Linux release 9.5
> Description:
>
> The SQL for testing the demo is as follows:
> postgres=# create database test1;
> CREATE DATABASE
> postgres=# \c test1
> You are now connected to database "test1" as user "postgres".
> test1=# create schema test;
> CREATE SCHEMA
> test1=# create table test.t1 (id int);
> CREATE TABLE
> test1=# create view t1 as select * from t1;
> ERROR: relation "t1" does not exist
> LINE 1: create view t1 as select * from t1;
> ^
> test1=# create view t1 as select * from test.t1;
> CREATE VIEW
> test1=# create or replace view t1 as select * from t1;
> CREATE VIEW
> test1=# \d
> List of relations
> Schema | Name | Type | Owner
> --------+------+------+----------
> public | t1 | view | postgres
> (1 row)
>
> We know that in PostgreSQL (PG), it is not allowed to create tables and
> views with the same name under the same schema in the database. When
> creating a view for the first time (when the view does not exist), if the
> table does not exist or has the same name as the view, the database will
> throw an error as expected. However, as demonstrated in my test demo, when
> the view already exists, running CREATE OR REPLACE VIEW does not check
> whether the table exists. Although I understand that this might be an issue
> with operational practices, as everyone knows, in a production environment,
> our application development team might unintentionally or inadvertently
> cause the same problem. The design might have been intended for quick
> response without checking if the table exists, but rather modifying the
> system table (my guess). If such an issue exists, should it be considered a
> bug？
>

Did you try to select from this view?

```
reshke=# table t1;
ERROR: infinite recursion detected in rules for relation "t1"
```

" create or replace view t1 as select * from t1;" creates a view that
references itself, there is nothing wrong with it.

--
Best regards,
Kirill Reshke