wrong output in dump of rules with old values of row type columns

Started by Timur Khanjanovover 4 years ago3 messagesbugs
Jump to latest
#1Timur Khanjanov
intel@intrans.baku.az

for example

create table test(a int);

create table test_log(old test);
create rule del as on delete to test do insert into test_log values(old);

it works as intended

postgres=# insert into test values(1);
INSERT 0 1
postgres=# delete from test;
DELETE 1
postgres=# select * from test_log
postgres-# ;
old
-----
(1)
(1 row)

BUT
\d test shows

postgres=# \d test
Table "public.test"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
Rules:
del AS
ON DELETE TO test DO INSERT INTO test_log (old)
VALUES (old.*)

and pg_dump makes wrong rule

--
-- Name: test del; Type: RULE; Schema: public; Owner: postgres
--

CREATE RULE del AS
ON DELETE TO public.test DO INSERT INTO public.test_log (old)
VALUES (old.*);

when i try to recreate it from dump

drop rule del on test ;

CREATE RULE del AS
ON DELETE TO public.test DO INSERT INTO public.test_log (old)
VALUES (old.*);

ERROR: column "old" is of type test but expression is of type integer
LINE 3: VALUES (old.*);
^
HINT: You will need to rewrite or cast the expression.

if i remove .* part - all OK

postgres=# CREATE RULE del AS
ON DELETE TO public.test DO INSERT INTO public.test_log (old)
VALUES (old);
CREATE RULE

So i think it's bug, and both \d and pg_dump should return

VALUES (old) instead of VALUES (old.*) in this case

'new' instead of 'old' makes same result

postgres=# CREATE RULE ins AS
ON INSERT TO public.test DO INSERT INTO public.test_log (old)
VALUES (new);
CREATE RULE
postgres=# \d test
Table "public.test"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
Rules:
del AS
ON DELETE TO test DO INSERT INTO test_log (old)
VALUES (old.*)
ins AS
ON INSERT TO test DO INSERT INTO test_log (old)
VALUES (new.*)

it same on ver 11 and 14, don't tried other versions

--
Homo Homini Dominus est

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Timur Khanjanov (#1)
Re: wrong output in dump of rules with old values of row type columns

Timur Khanjanov <intel@intrans.baku.az> writes:

create table test(a int);
create table test_log(old test);
create rule del as on delete to test do insert into test_log values(old);

As a workaround you could write it as

create rule del as on delete to test do insert into test_log select old;

which will reverse-list as

ON DELETE TO test DO INSERT INTO test_log (old) SELECT old.*::test AS old

Looks like we need to apply the same hack in VALUES lists.

regards, tom lane

#3Timur Khanjanov
intel@intrans.baku.az
In reply to: Tom Lane (#2)
Re: wrong output in dump of rules with old values of row type columns

On 12.01.2022 19:08, Tom Lane wrote:

Timur Khanjanov <intel@intrans.baku.az> writes:

create table test(a int);
create table test_log(old test);
create rule del as on delete to test do insert into test_log values(old);

As a workaround you could write it as

create rule del as on delete to test do insert into test_log select old;

which will reverse-list as

ON DELETE TO test DO INSERT INTO test_log (old) SELECT old.*::test AS old

Looks like we need to apply the same hack in VALUES lists.

regards, tom lane

thank you for workaround,
I'm already find another workaround
(just script, modyfing dump, not universal, but works for me ;)

Hope that in next version it 'll be fixed.

--
Homo Homini Dominus est