Thread: [GENERAL] Table partionning : INSERT with inconsistent return ligne inserted.

[GENERAL] Table partionning : INSERT with inconsistent return ligne inserted.

From

STERBECQ Didier

Date:

18 October 2017, 14:24:49

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 ?

Here is a short test case :

-- table

CREATE TABLE t1_part

( id BIGINT ,

libelle VARCHAR(30 )

)

WITH ( FILLFACTOR = 70 )

TABLESPACE data_1

;

-- partitions : 2.

CREATE TABLE t1_part_01

( CHECK ( id >= 1 AND id <= 1000 )

)

INHERITS ( t1_part ) ;

CREATE TABLE t1_part_02

( CHECK ( id > 1000 AND id <= 22000000 )

)

INHERITS ( t1_part ) ;

-- function of partitionning

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;

-- trigger

CREATE TRIGGER insert_t1_part

BEFORE INSERT ON t1_part

FOR EACH ROW EXECUTE PROCEDURE t1_part_test_trigger();

-- Insert

hba=> insert into t1_part values (3000001, '3000001' ) ;

INSERT 0 0 -- should be “INSERT 0 1”

hba=> select * from t1_part ;

id | libelle

---------+----------

3000001 | 3000001

(1 row) -- proof of successful insert.

Thanks by advance.

Didier Sterbecq

Re: [GENERAL] Table partionning : INSERT with inconsistent returnligne inserted.

From

Vik Fearing

Date:

18 October 2017, 23:30:03

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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Table partionning : INSERT with inconsistent returnligne inserted.

From

STERBECQ Didier

Date:

19 October 2017, 20:57:14

Hi Vik,

Thanks for that, it is working.

Didier.
-----Message d'origine-----
De : Vik Fearing [mailto:vik.fearing@2ndquadrant.com]
Envoyé : mercredi 18 octobre 2017 19:30
À : STERBECQ Didier; pgsql-general@postgresql.org
Objet : Re: [GENERAL] 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
anINSTEAD OF trigger that inserts into the table, which then get rerouted with your BEFORE trigger.  Then you insert
intothe view and get the desired result. 
--
Vik Fearing                                          +33 6 46 75 15 36
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general