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(dot)gilbert(at)ec(dot)gc(dot)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