BUG #17646: create rule named "_RETURN" will cause pg core

The following bug has been logged on the website:

Bug reference: 17646
Logged by: kui liu
Email address: liukui(at)kingbase(dot)com(dot)cn
PostgreSQL version: 15.0
Operating system: linux
Description:

Hi, I encounter an error, it may be a bug, thx.

here is a test case

create table t (id integer);
create view v1 as select * from t;
create or replace rule "_RETURN" as on UPDATE to v1 do instead select * from
t;
select * from v1;

execute up sql, will get this error

server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
The connection to the server was lost. Attempting reset: Failed.
!?>

PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> here is a test case

> create table t (id integer);
> create view v1 as select * from t;
> create or replace rule "_RETURN" as on UPDATE to v1 do instead select * from
> t;

Meh. We should be preventing you from doing that.

(Although the core dump is also not great --- something should've noticed
the bogosity of the plan earlier than this.)

regards, tom lane

On Mon, Oct 17, 2022 at 10:17:55AM -0400, Tom Lane wrote:
> PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> > here is a test case
>
> > create table t (id integer);
> > create view v1 as select * from t;
> > create or replace rule "_RETURN" as on UPDATE to v1 do instead select * from
> > t;
>
> Meh. We should be preventing you from doing that.
>
> (Although the core dump is also not great --- something should've noticed
> the bogosity of the plan earlier than this.)

Id doesn't look much of a plan bogosity,
but rather implicit deletion of the "_RETURN" on select rule.

Consider the behaviour of the direct drop rule:

ilan=*> create table t (id integer);
CREATE TABLE
ilan=*> create view v1 as select * from t;
CREATE VIEW
ilan=*> DROP RULE "_RETURN" ON v1;
ERROR: cannot drop rule _RETURN on view v1 because view v1 requires it
ПОДСКАЗКА: You can drop view v1 instead.

>
> regards, tom lane
>

On Mon, Oct 17, 2022 at 12:03:15PM +0000, PG Bug reporting form wrote:
> The following bug has been logged on the website:
>
> Bug reference: 17646
> Logged by: kui liu
> Email address: liukui(at)kingbase(dot)com(dot)cn
> PostgreSQL version: 15.0
> Operating system: linux
> Description:
>
> Hi, I encounter an error, it may be a bug, thx.
>
> here is a test case
>
> create table t (id integer);
> create view v1 as select * from t;
> create or replace rule "_RETURN" as on UPDATE to v1 do instead select * from
> t;
> select * from v1;
>
> execute up sql, will get this error
>
> server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
> The connection to the server was lost. Attempting reset: Failed.
> The connection to the server was lost. Attempting reset: Failed.
> !?>

btw, I'm confirming the bug for postgres from at least 8.4 to 14.

Version 11 and less instead of segfault prints someting like:

ilan=*# select * from v1;
ERROR: could not open file "base/24576/57373": No such file or directory

>

Ilya Anfimov <ilan(at)tzirechnoy(dot)com> writes:
> On Mon, Oct 17, 2022 at 10:17:55AM -0400, Tom Lane wrote:
>> (Although the core dump is also not great --- something should've noticed
>> the bogosity of the plan earlier than this.)

> Id doesn't look much of a plan bogosity,
> but rather implicit deletion of the "_RETURN" on select rule.

Right, but then since there's no applicable rule, we end up generating
a plan that tries to do a seqscan directly on the view relation,
which of course lacks storage. Something should notice that a little
sooner than segfaulting because of rel->rd_tableam being NULL --- it's
not like we don't have hundreds of other sanity checks for not-really-
supposed-to-happen catalog corruption.

In versions before v12, I get something like

regression=# select * from v1;
ERROR: could not open file "base/16384/49209": No such file or directory

which is a shade less bad, but still not great.

regards, tom lane

On Mon, Oct 17, 2022 at 10:41:35AM -0400, Tom Lane wrote:
> Ilya Anfimov <ilan(at)tzirechnoy(dot)com> writes:
> > On Mon, Oct 17, 2022 at 10:17:55AM -0400, Tom Lane wrote:
> >> (Although the core dump is also not great --- something should've noticed
> >> the bogosity of the plan earlier than this.)
>
> > Id doesn't look much of a plan bogosity,
> > but rather implicit deletion of the "_RETURN" on select rule.
>
> Right, but then since there's no applicable rule, we end up generating
> a plan that tries to do a seqscan directly on the view relation,
> which of course lacks storage. Something should notice that a little
> sooner than segfaulting because of rel->rd_tableam being NULL --- it's
> not like we don't have hundreds of other sanity checks for not-really-
> supposed-to-happen catalog corruption.
>
> In versions before v12, I get something like
>
> regression=# select * from v1;
> ERROR: could not open file "base/16384/49209": No such file or directory
>
> which is a shade less bad, but still not great.

Another solution would be to always create a real heap file and let
anyone dropped to that state select it, if he wishes.

btw, the documentation clearly says that it does so:
/docs/14/rules-views.html

<<In fact, there is essentially no difference between:

CREATE VIEW myview AS SELECT * FROM mytab;
compared against the two commands:

CREATE TABLE myview (same column list as mytab);
CREATE RULE "_RETURN" AS ON SELECT TO myview DO INSTEAD
SELECT * FROM mytab;
because this is exactly what the CREATE VIEW command does internally. >>