Hi,
While testing event triggers support for CREATE RULE, I noticed that
existing code does not deparse whole-row references correctly:
postgres=# create table f (a int);
CREATE TABLE
postgres=# create table g (other f);
CREATE TABLE
postgres=# create rule f as on insert to f do also (insert into g values (new));
CREATE RULE
postgres=# \d f Tabla «public.f»Columna | Tipo | Modificadores
---------+---------+---------------a | integer |
Reglas: f AS ON INSERT TO f DO INSERT INTO g (other) VALUES (new.*)
Note that my rule had "values (new)" but the deparsed version uses
"VALUES (new.*)" which doesn't work on input: pg_dump will fail with
this output:
$ pg_dump postgres -t f -t g | LC_ALL=C PGOPTIONS="-c lc_messages=C" psql postgres2
SET
SET
SET
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
ERROR: column "other" is of type f but expression is of type integer
LINE 3: VALUES (new.*); ^
HINT: You will need to rewrite or cast the expression.
My sample session with event triggers is a neat example of that
functionality at work BTW (or so I think anyway):
alvherre=# CREATE RULE "F" AS ON INSERT TO public.f DO also (insert into g values (new); insert into h values (new);
insertinto i values (new));
NOTICE: Trigger has been executed. ddl_command_end -- CREATE RULE
NOTICE: JSON blob: { "actions": [ "INSERT INTO public.g (duh) VALUES (new.*)", "INSERT INTO public.h
(duh)VALUES (new.*)", "INSERT INTO public.i (duh) VALUES (new.*)" ], "event": "INSERT", "fmt": "CREATE
%{or_replace}sRULE %{identity}I AS ON %{event}s TO %{table}D %{where_clause}s DO %{instead}s (%{actions:; }s)",
"identity":"F", "instead": "ALSO", "or_replace": "", "table": { "objname": "f", "schemaname":
"public" }, "where_clause": { "clause": null, "fmt": "WHERE %{clause}s", "present": false }
}
NOTICE: expanded: CREATE RULE "F" AS ON INSERT TO public.f DO ALSO (INSERT INTO public.g (duh) VALUES (new.*);
INSERTINTO public.h (duh) VALUES (new.*); INSERT INTO public.i (duh) VALUES (new.*))
CREATE RULE
alvherre=# \d f Tabla «public.f»Columna | Tipo | Modificadores
---------+---------+---------------a | integer |
Reglas: "F" AS ON INSERT TO f DO ( INSERT INTO g (duh) VALUES (new.*);INSERT INTO h (duh) VALUES (new.*);INSERT
INTOi (duh) VALUES (new.*);
)
I'm switching to the multixact bug now, but I wanted to report this
before I forgot it.
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
