Re: Table partionning : INSERT with inconsistent return ligne inserted.

On 10/18/2017 10:24 AM, STERBECQ Didier wrote:
> Hi,
>
> I use PostgreSQL 9.6 (9.6.3)  with table partitioning, when I use INSERT
> order psql, it does not show the number of lines inserted.
>
> I do not see any information in the version notes from the PostgreSQL
> documentation, even with the 9.6.5 update, is it some bug ?

It's not really a bug, but it is quite annoying.

> CREATE FUNCTION t1_part_test_trigger()
> RETURNS TRIGGER AS $$
> BEGIN
>     IF NEW.id >= 1 AND NEW.id <= 1000 THEN
>         INSERT INTO t1_part_01 VALUES (NEW.*);
>     ELSIF NEW.id > 1000 AND NEW.id <= 22000000 THEN
>         INSERT INTO t1_part_02 VALUES (NEW.*);
>     END IF ;
>     RETURN NULL;
> END;
> $$
> LANGUAGE plpgsql;

The problem is here. You returned null in a BEFORE trigger which
cancels the insert. As far as PostgreSQL is concerned, you didn't
insert anything into this table (because you didn't) and so it correctly
but annoyingly returns 0.

> hba=> insert into t1_part values (3000001, '3000001' ) ;
> INSERT 0 0                                    -- should be “INSERT 0 1”

To get this effect, which I believe is required for Hibernate and some
other frameworks, you need to create a view with an INSTEAD OF trigger
that inserts into the table, which then get rerouted with your BEFORE
trigger. Then you insert into the view and get the desired result.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support