Re: Issues with patitionning and triggers

Hello everyone,

Here is a complete example demonstrating the issue I am encountering :

CREATE TABLE parent (
   split INTEGER NOT NULL,
   happiness INTEGER NOT NULL,
   modificationDate TIMESTAMP NOT NULL
);

CREATE TABLE child_split1 ( CHECK (split = 1) ) INHERITS (parent);
CREATE TABLE child_split2 ( CHECK (split = 2) ) INHERITS (parent);

CREATE OR REPLACE FUNCTION parent_dispatcher_trigger()
RETURNS TRIGGER AS $$
BEGIN
   CASE NEW.split
      WHEN 1 THEN INSERT INTO child_split1 VALUES (NEW.*);
      WHEN 2 THEN INSERT INTO child_split2 VALUES (NEW.*);
      ELSE RAISE EXCEPTION 'Partition for % does not exist!',NEW.split;
   END CASE;
   RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_insert_00
   BEFORE INSERT ON parent
   FOR EACH ROW EXECUTE PROCEDURE parent_dispatcher_trigger();

CREATE OR REPLACE FUNCTION set_modificationDate_debug() RETURNS TRIGGER AS $$
BEGIN
   NEW.modificationDate := now();
   RAISE NOTICE 'NEW row is now : (%, %, %)',
      NEW.split, NEW.happiness, NEW.modificationDate;
   RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER trigger_insert_00
   BEFORE INSERT ON child_split1
   FOR EACH ROW EXECUTE PROCEDURE set_modificationdate_debug();

CREATE TRIGGER trigger_insert_00
   BEFORE INSERT ON child_split2
   FOR EACH ROW EXECUTE PROCEDURE set_modificationdate_debug();

INSERT INTO parent (split, happiness) VALUES (1, 42);

-- NOTICE:  NEW row is now : (1, 42, 2014-02-19 16:31:07.384151)
-- CONTEXT:  SQL statement "INSERT INTO child_split1 VALUES (NEW.*)"
-- PL/pgSQL function parent_dispatcher_trigger() line 4 at SQL statement
-- ERROR:  null value in column "modificationdate" violates not-null
constraint
-- DETAIL:  Failing row contains (1, 42, null).

INSERT INTO child_split1 (split, happiness) VALUES (1, 42);

-- NOTICE:  NEW row is now : (1, 42, 2014-02-19 16:37:27.134194)
-- INSERT 0 1


So, we clearly see that trigger_insert_00 is called in both cases.  I don't
understand why the query fails on the parent, but works when the INSERT
targets the child table directly.

Regards,
Samuel Gilbert


On 2014-02-18 23:16:31 Samuel Gilbert wrote:
> On 2014-02-18 17:59:35 Tom Lane wrote:
> > Samuel Gilbert <samuel.gilbert@ec.gc.ca> writes:
> > > All of this was done on PostgreSQL 9.2.0 64-bit compiled from the
> > > official
> >
> > > source.  Significant changes in postgresql.conf :
> > Why in the world are you using 9.2.0?  You're missing a year and a half
> > worth of bug fixes, some of them quite serious.
>
> Yes.  I know and I bear the pain and shame not running at least the latest
> revision of the 9.2 branch.  Unfortunately, it's hard to get my manager to
> view the update of software that "just works" as something to prioritize.
>
> The good news is that your reply is a good argument to do so!  :)
>
> Cheers!
>
> > > INSERT ... RETURNING does not work with partitioned tables, since the
> > > trigger function on the parent that dispatches new rows to the children
> > > tables, must return NULL.  If the trigger function on the parent ends
> > > with "RETURN NEW", INSERT ... RETURNING works, but new rows are
> > > duplicated; they are inserted both in the parent and child tables.
> > >
> > > Is there a way to make INSERT ... RETURNING work without duplicating the
> > > rows?
> >
> > Fraid not --- it only shows what got inserted into the parent table, which
> > is nothing if you're using this technique.
> >
> > > The modification date must be updated if any row is modified in any way.
> > > I
> > > first tried to define the triggers on the parent table.  This worked,
> > > but
> > > I
> > > realized that if a queries targets explicitly a child table, it could
> > > modify a row without the date being updated.  I therefore dropped the
> > > triggers on the parent table and defined them for every child.  To my
> > > great surprise, the insert below failed with a message saying that NULLs
> > > are not allowed in the modificationdate column.
> >
> > You'd have to provide a self-contained example for anyone to help you with
> > that.  The most obvious explanation is that you forgot to attach the
> > trigger to the specific child table ...
> >
> >             regards, tom lane