wrong output in dump of rules with old values of row type columns
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
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
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