Postgresql – Partition table behavior for Postgres

partitioningpostgresql

I have created a partitioned table with 1 partition. below is the script for the same:

CREATE TABLE public.ms (
id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
a int8 NOT NULL,
b int4 NOT NULL,
c varchar(2000) NULL,
d int2 NOT NULL,
e bool NOT NULL DEFAULT false,
f timestamp NOT NULL DEFAULT timezone('UTC'::text, CURRENT_TIMESTAMP),
g jsonb NULL,
h int2 NOT NULL DEFAULT 1,
i bool NOT NULL DEFAULT true,
j int2 NULL,
CONSTRAINT me_pkey PRIMARY KEY (id)
)PARTITION BY RANGE (id) ; 


CREATE TABLE ms3_0  PARTITION OF public.msg
FOR VALUES FROM (1) to (1000);

Now, when I'm running insert statement for this via below loop, I'm getting below error message:

do $$
begin
for i in 1..1000
loop
INSERT INTO public.ms
(a, b, c, d, e, f,  h, i , j)
VALUES(2, 0, '', 0, false, '2019-01-28 23:59:51', 1, true, 0);
end loop;
end;$$

SQL Error [23514]: ERROR: no partition of relation "ms" found for row
Detail: Partition key of the failing row contains (id) = (1001).

However, till id '1000', partition has been created already. After that, I dropped the existing table and recreated the same table and partition again. Now, I started inserting records manually via a single 'insert' statement. Then, I could insert into them.

After, this, I have created 2nd partition like below:

CREATE TABLE ms3_1  PARTITION OF public.ms
FOR VALUES FROM (1001) to (2000);

And, again executed the same loop for 1000 entries and got below error:

SQL Error [23514]: ERROR: no partition of relation "ms" found for row
Detail: Partition key of the failing row contains (id) = (1000)

However, when I insert in the batch of 100 , like below: I could insert that:

do $$
begin
for i in 0..100
loop
INSERT INTO public.ms
(a, b, c, d, e, f,  h, i, j)
VALUES(2, 0, '', 0, false, '2019-01-28 23:59:51', 1, true, 0);
end loop;
end;$$

From the above whole scenario , I have a few queries:

  1. In the first case, why it wasn't inserting data (1000 values) into first partition when partition for first 1000 values was created already?
  2. After inserting 3-4 records into first partition and then After creating again another partition (ms3_1) with 1000 records, when I try to perform bulk insert of 1000 records into the same ms table, why I'm not able to make that?

Any help/reason for this will be appreciated here. Since, it will help me to configure partition as per my business requirement.

Best Answer

I can answer the first question, the rest of your description is too confused for me to follow.

The first 1000 rows were inserted successfully into the existing partition, but inserting the row with id = 1000 failed. This error caused the whole transaction to be rolled back, including the previously inserted rows.

One thing that may also confuse you is that with range partitioning, the upper end of the range is excluded. So if you create a partition

FOR VALUES FROM (1) to (1000)

it can only accommodate values up to 999. This looks surprising with discrete data types like integer, but makes life much easier when using other data types:

FOR VALUES FROM ('2019-01-01 00:00:00') TO ('2020-01-01 00:00:00')

is so much nicer than having to use an upper limit of 2019-12-31 23:59:59.999999.

Related Question