Thread: 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