The following bug has been logged on the website:
Bug reference: 14799
Logged by: Philippe BEAUDOIN
Email address: phb07@apra.asso.fr
PostgreSQL version: 10beta4
Operating system: Linux
Description:
Hi all,
I am playing a bit with transition tables in statement-level triggers, using
the postgres V10 beta 4 version. I am facing an issue that I suspect to be a
bug (unless it is a design limitation). I have built a small test case to
reproduce what I have discovered.
Here it is inline as it is really short.
-- test case start
-- create an application table
DROP TABLE IF EXISTS mytbl1;
CREATE TABLE mytbl1 ( col11 INT NOT NULL, col12 TEXT NOT NULL, extracol INT
);
-- the extra column is now dropped
ALTER TABLE mytbl1 DROP COLUMN extracol;
select attname, attnum, attisdropped from pg_class, pg_namespace,
pg_attribute where attrelid = pg_class.oid and relnamespace = pg_namespace.oid and
relname = 'mytbl1' and nspname = 'public';
-- create a log table with the same structure + 2 technical columns
DROP TABLE IF EXISTS mytbl1_log;
CREATE TABLE mytbl1_log ( col11 INT NOT NULL, col12 TEXT NOT NULL, verb TEXT ,
tuple TEXT
);
-- create a log function using transition tables
CREATE OR REPLACE FUNCTION log() RETURNS TRIGGER LANGUAGE plpgsql
AS $$
BEGIN IF TG_OP = 'INSERT' THEN INSERT INTO mytbl1_log SELECT new_table.*, 'INS', 'NEW' FROM
new_table;
-- if one explicitely list the column, it works
-- INSERT INTO mytbl1_log SELECT col11, col12, 'INS', 'NEW' FROM
new_table; END IF; RETURN NULL;
END; $$;
-- create the triggers
CREATE TRIGGER log_insert_mytbl1 AFTER INSERT ON mytbl1 REFERENCING NEW TABLE AS new_table FOR EACH STATEMENT
EXECUTEPROCEDURE log();
-- now use the infrastructure
insert into mytbl1 values (1001,'a'),(1000,'a');
-- test case end
As a result, one gets an error on the INSERT into the log table with a
message:
ERROR: INSERT has more expressions than target columns
LINE 1: INSERT INTO mytbl1_log SELECT new_table.*, 'INS', 'NEW'
^
QUERY: INSERT INTO mytbl1_log SELECT new_table.*, 'INS', 'NEW' FROM new_table
CONTEXT: PL/pgSQL function log() line 5 at SQL statement
The problem also exists with DELETE triggers, while not demonstrated here.
In some other cases, I had an error on a column type unconsistency.
If one uses a row_to_json() function in a SELECT * loop inside the same
trigger function, like: FOR r IN SELECT * FROM new_table LOOP RAISE WARNING 'New row = %',row_to_json(r); END
LOOP;
one gets another error message:
psql:test_transition_tables3.sql:60: ERROR: cache lookup failed for type
0
CONTEXT: SQL statement "SELECT * FROM new_table"
PL/pgSQL function log() line 10 at FOR over SELECT rows
It looks like the resolution of the column list has not taken into account a
"NOT attisdropped" condition when scanning the pg_attribute table (or an
equivalent in memory structure).
Best regards.
Philippe.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs