Обсуждение: [GENERAL] Table partionning : INSERT with inconsistent return ligne inserted.
[GENERAL] Table partionning : INSERT with inconsistent return ligne inserted.
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.
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.
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