BUG #14799: SELECT * FROM transition_table in a statement-level trigger

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 EXECUTE PROCEDURE 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