CODEWITHSUNDEEP
postgresqltriggersplpgsqldynamic-sql
user706794
user706794

Reputation: 211

Postgres trigger issue "ERROR: query "SELECT NEW.*" returned 12 columns"

I'm on Postgres 9.2 and I'm having issues with a trigger inserting into partitions. It was working for quite awhile, where i was running this .sql script for almost a year, where it was successful all the way to as of last week. Today, I tried to run it, and I was getting an error from the trigger.

ERROR: query "SELECT NEW.*" returned 12 columns
CONTEXT: PL/pgSQL function ft_test_insert_trigger() line 3 at EXECUTE statement.

Here's what my base(parent) table looks like:

CREATE TABLE public.test (
   id            bigint         NOT NULL PRIMARY KEY,
   name          varchar(16)    NOT NULL,
   geometry      geometry       NOT NULL,
   scale         integer        NOT NULL,
   zone          smallint       NOT NULL,
   band          char(1)        NOT NULL,
   easting       integer,
   northing      integer,
   ancestry      text,
   display_name  varchar(16),
   created_at    timestamp      DEFAULT now() NOT NULL,
   updated_at    timestamp      DEFAULT now() NOT NULL,
   CONSTRAINT enforce_dims_geometry CHECK (ndims(geometry) = 2),
   CONSTRAINT enforce_srid_geometry CHECK (srid(geometry) = 4326),
   CONSTRAINT enforce_geotype_geometry CHECK ((geometrytype(geometry) = 'POLYGON'::text) OR (geometry IS NULL))
);

And this one just one of its child tables:

CREATE TABLE zone.test_zone_6v (
   id            bigserial      NOT NULL PRIMARY KEY,
   name          varchar(16)    NOT NULL,
   geometry      geometry       NOT NULL,
   scale         integer        NOT NULL,
   zone          smallint       NOT NULL,
   band          char(1)        NOT NULL,
   easting       integer,
   northing      integer,
   ancestry      text,
   display_name  varchar(16),
   created_at    timestamp      DEFAULT now() NOT NULL,
   updated_at    timestamp      DEFAULT now() NOT NULL,
   CONSTRAINT enforce_srid_geometry CHECK (srid(geometry) = 4326),
   CONSTRAINT enforce_geotype_geometry CHECK ((geometrytype(geometry) = 'POLYGON'::text) OR (geometry IS NULL)),
   CONSTRAINT enforce_dims_geometry CHECK (ndims(geometry) = 2),
   CONSTRAINT test_zone_6v_check CHECK ((zone = 6) AND (band = 'V'::bpchar))
)
INHERITS (public.test)
TABLESPACE compressed;

ALTER TABLE zone.test_zone_6v ADD CONSTRAINT uq_test_zone_6v_on_name UNIQUE (name);

And this is the trigger:

CREATE OR REPLACE FUNCTION public.ft_test_insert_trigger()
  RETURNS trigger
  LANGUAGE plpgsql
AS
$body$
BEGIN
   EXECUTE format('INSERT INTO zone.test_zone_%s%s VALUES($1)', NEW.zone, NEW.band) USING NEW.*;
RETURN NULL;
END;
$body$
 VOLATILE
 COST 100;

CREATE TRIGGER trg_insert_test_trigger BEFORE INSERT
  ON test FOR EACH ROW
  EXECUTE PROCEDURE ft_test_insert_trigger();

I have been copying data from a file w/ x amount of records into a temp table to filter / clean out some data:

CREATE TEMPORARY TABLE import_test (
 name          TEXT     NOT NULL
,display_name  TEXT     
,scale         INTEGER  NOT NULL
,zone          INTEGER  NOT NULL
,band          CHAR     NOT NULL
,easting       INTEGER  NOT NULL
,northing      INTEGER  NOT NULL
,geometry      GEOMETRY NOT NULL
);

When I do an insert into the table from the temporary table

INSERT INTO test (name, geometry, scale, zone, band, easting, northing, display_name) 
SELECT name, geometry, scale, zone, band, easting, northing, display_name FROM import_test;

I get the error quoted above.

I'm not sure what the issue is exactly. The trigger never changed. The data seems valid. I did a 

INSERT INTO zone.test_zone_6v (name, geometry, scale, zone, band, easting, northing, display_name) 
SELECT name, geometry, scale, zone, band, easting, northing, display_name FROM import_test;

with no issues. In zone schema, I have a large number of children table for it. I can't figure out why it's not working now, all of a sudden.

Upvotes: 1

Views: 3346

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657202

The problem is USING NEW.*, which is invalid syntax (for tables with more than one column).

The USING clause of plpgsql's EXECUTE statement expects simple expressions. You cannot decompose a row type into n values in this position. Each expression must return a single value.

Pass the whole row instead:

CREATE OR REPLACE FUNCTION public.ft_test_insert_trigger()
  RETURNS trigger AS
$func$
BEGIN
   EXECUTE format('INSERT INTO zone.%I SELECT ($1).*'
                , 'test_zone_' || NEW.zone || NEW.band)
   USING NEW;
   RETURN NULL;
END
$func$  LANGUAGE plpgsql;

While being at it, I fixed a potential SQL injection hole.
A smallint (zone) and a char(1) (band) don't look dangerous. But even a single character can kill your statement. Think of ' or ';'. And if you later forget about the hole and change the type of the column to text or varchar you are wide open to any mischief.
Never just concatenate user input into code.
Details:

If your old trigger function "was successful all the way" and it "never changed", then it was either

  • only used with tables holding a singe column or
  • never actually executed.
  • (Or maybe some old version silently took the first column and discarded the rest? But you did not mention a version upgrade, and I tested with 9.1 and it fails the same, and I don't recall issues with older versions.)

PL/pgSQL only superficially verifies the syntax of SQL commands. Your code would pass the test. It even works for tables with a single column. The exception won't show up until executed with a wider table.

Related:

Upvotes: 1

Related Questions