0

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.

  • Postgres does not automatically create partitions if new values for the partition key are inserted, so I don't really understand what your question is. The errors seem pretty obvious. The DO block from the first example is treated as a single transaction. And by definition a transaction is rolled back if one of the statements have an error – a_horse_with_no_name Dec 6 '19 at 10:42
  • @a_horse_with_no_name , Thanks for the response, Please have look at my question again. The first part: I have created a partition already for 1000 records and then after I try to insert 1000 records via a loop and it's throwing : SQL Error [23514]: ERROR: no partition of relation "msg" found for row Detail: Partition key of the failing row contains (id) = (1001). I'm not expecting another partition to get created. But, it should atleast insert into existing partition which is full empty for 1000 records. Also, none of the statement have any error. – Shiwangini Shishulkar Dec 6 '19 at 10:45
  • @a_horse_with_no_name , you can just run the scripts in the order I mentioned and check out the behavior. – Shiwangini Shishulkar Dec 6 '19 at 10:47
  • 1
    As I wrote: the DO block is one single transaction (regardless of the fact that you are using an inefficient LOOP) - either everything or nothing will be inserted. It's essentially the same as insert into ... values (1),(2,),(3),....(1001) – a_horse_with_no_name Dec 6 '19 at 10:49
  • @a_horse_with_no_name , as you suggested, I edited and re- ran my loop (after recreating table) and started my loop for 1..1000, even now also ,I'm getting error: SQL Error [23514]: ERROR: no partition of relation "msg" found for row Detail: Partition key of the failing row contains (id) = (1000). What transaction you consider wrong now? My partition has capacity to insert records till 1000 ids. Please let me know, which statement is incorrect? – Shiwangini Shishulkar Dec 6 '19 at 10:56
0

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.

| improve this answer | |
  • Thanks @Laurenz Albe for the response. The fact, shared by you about the behavior of range partitioning answered my both the question. Thanks, – Shiwangini Shishulkar Dec 6 '19 at 11:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.