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@postgresql.org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference:      18892
> Logged by:          Gavin
> Email address:      gchen@s2now.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