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

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？

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

On Sun, 13 Apr 2025 at 00:33, David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> Kirill's answer is correct; though it is a bit annoying that "create view" cannot detect the infinite recursion
inherentin its body.
 
>
> David J.
>

Hm, if we can detect infinite recursion at runtime, we should also be
able to do it definition-time, on "create or replace". Is there any
reason we omit such a check (for recursive views/relations) when
executing ddl?

-- 
Best regards,
Kirill Reshke

Kirill Reshke <reshkekirill@gmail.com> writes:
> On Sun, 13 Apr 2025 at 00:33, David G. Johnston
> <david.g.johnston@gmail.com> wrote:
>> Kirill's answer is correct; though it is a bit annoying that "create view" cannot detect the infinite recursion
inherentin its body. 

> Hm, if we can detect infinite recursion at runtime, we should also be
> able to do it definition-time, on "create or replace". Is there any
> reason we omit such a check (for recursive views/relations) when
> executing ddl?

I don't think this would be an improvement, because it would cause
errors that need not happen.  For example, if the recursion is through
two views, then altering either one might be enough to remove the
problem.  Refusing to create the second view is pre-judging where
the problem lies.  And it's not like the run-time error is obscure.

I think this suggestion is much like the one we recently
rejected about throwing an error if the user tries to attach a
default to a view column whose underlying column is generated.
In short: "if you were to try to execute this right now, it would
fail" is not a great argument for refusing to create an object
whose behavior is intertwined with other objects.

            regards, tom lane

On Mon, Apr 14, 2025 at 08:55:04AM +0000, Gang Chen wrote:
> Hello,
> 
> But isn't that kind of strange? If it's just a very simple view, first of all,
> referencing itself would result in an infinite recursion error. Also, it
> doesn't seem to have any practical use. Secondly, during pg_upgrade or backup
> and restore processes, it might introduce unnecessary errors and additional
> handling procedures.

You are right, but disallowing only that setup would be confusing. To
keep the user interface simple, we should either allow a general class
of things or disallow them.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Do not let urgent matters crowd out time for investment in the future.