Обсуждение: [BUGS] BUG #14799: SELECT * FROM transition_table in a statement-leveltrigger
[BUGS] BUG #14799: SELECT * FROM transition_table in a statement-leveltrigger
От
phb07@apra.asso.fr
Дата:
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
phb07@apra.asso.fr writes: > 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. > ... > 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). Yeah. The RTE_NAMEDTUPLESTORE patch seems to have piggybacked on the code for RTE_CTE and friends, none of which could have dropped columns so the case wasn't considered. I think the immediate problem is in expandRTE() but I have zero faith that there aren't comparable bugs elsewhere. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #14799: SELECT * FROM transition_table in astatement-level trigger
От
Thomas Munro
Дата:
On Thu, Sep 7, 2017 at 1:32 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > phb07@apra.asso.fr writes: >> 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. >> ... >> 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). > > Yeah. The RTE_NAMEDTUPLESTORE patch seems to have piggybacked on the code > for RTE_CTE and friends, none of which could have dropped columns so the > case wasn't considered. I think the immediate problem is in expandRTE() > but I have zero faith that there aren't comparable bugs elsewhere. Ouch. Thanks for fixing this bug, which I would have picked up if I'd been awake at the time. -- Thomas Munro http://www.enterprisedb.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #14799: SELECT * FROM transition_table in astatement-level trigger
От
Peter Geoghegan
Дата:
On Wed, Sep 6, 2017 at 2:20 PM, Thomas Munro <thomas.munro@enterprisedb.com> wrote: > Ouch. Thanks for fixing this bug, which I would have picked up if I'd > been awake at the time. Forgetting about support for dropped columns seems to be a mistake that almost everyone makes at least once. -- Peter Geoghegan -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs