[BUGS] BUG #14799: SELECT * FROM transition_table in a statement-leveltrigger

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