From: | STERBECQ Didier <didier(dot)sterbecq(at)ratp(dot)fr> |
---|---|
To: | Vik Fearing <vik(dot)fearing(at)2ndquadrant(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Table partionning : INSERT with inconsistent return ligne inserted. |
Date: | 2017-10-19 14:57:14 |
Message-ID: | C0685475FF6E374E989150721491820A13FB4C64@SV003314.info.ratp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi Vik,
Thanks for that, it is working.
Didier.
-----Message d'origine-----
De : Vik Fearing [mailto:vik(dot)fearing(at)2ndquadrant(dot)com]
Envoyé : mercredi 18 octobre 2017 19:30
À : STERBECQ Didier; pgsql-general(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Igal @ Lucee.org | 2017-10-19 15:21:36 | Using Variables in Queries |
Previous Message | américo bravo astroña | 2017-10-19 14:28:22 | Re: Problems with the time in data type timestamp without time zone |