Table partionning : INSERT with inconsistent return ligne inserted.

[STERBECQ Didier <didier.sterbecq@xxxxxxx>]

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