Horton

Thread

Commits

  1. Clean up handling of dropped columns in NAMEDTUPLESTORE RTEs.

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

    phb07 <phb07@apra.asso.fr> — 2017-09-06T12:00:05Z

    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.

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2017-09-06T13:32:34Z

    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

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

    Thomas Munro <thomas.munro@enterprisedb.com> — 2017-09-06T21:20:02Z

    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

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

    Peter Geoghegan <pg@bowt.ie> — 2017-09-06T21:22:37Z

    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