pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedwrong output in dump of rules with old values of row type columns
3+ messages / 2 participants
[nested] [flat]
Commits from this thread:
2022-01-13 45a3cefad6 3a1bfe2565 3433a1fc76 ca14c4184b 4aee39ddb8 43c2175121 Fix ruleutils.c's dumping of whole-row Vars in more contexts.
* wrong output in dump of rules with old values of row type columns
@ 2022-01-12 08:04 Timur Khanjanov <intel@intrans.baku.az>
2022-01-12 15:08 ` Re: wrong output in dump of rules with old values of row type columns Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Timur Khanjanov @ 2022-01-12 08:04 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: wrong output in dump of rules with old values of row type columns
2022-01-12 08:04 wrong output in dump of rules with old values of row type columns Timur Khanjanov <intel@intrans.baku.az>
@ 2022-01-12 15:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-01-12 16:59 ` Re: wrong output in dump of rules with old values of row type columns Timur Khanjanov <intel@intrans.baku.az>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2022-01-12 15:08 UTC (permalink / raw)
To: Timur Khanjanov <intel@intrans.baku.az>; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: wrong output in dump of rules with old values of row type columns
2022-01-12 08:04 wrong output in dump of rules with old values of row type columns Timur Khanjanov <intel@intrans.baku.az>
2022-01-12 15:08 ` Re: wrong output in dump of rules with old values of row type columns Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-01-12 16:59 ` Timur Khanjanov <intel@intrans.baku.az>
0 siblings, 0 replies; 3+ messages in thread
From: Timur Khanjanov @ 2022-01-12 16:59 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2022-01-12 16:59 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-01-12 08:04 wrong output in dump of rules with old values of row type columns Timur Khanjanov <intel@intrans.baku.az>
2022-01-12 15:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-01-12 16:59 ` Timur Khanjanov <intel@intrans.baku.az>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox