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
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
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 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