pg12 partitions question

From: Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com>
To: PostgreSQL mailing lists <pgsql-performance(at)postgresql(dot)org>
Subject: pg12 partitions question
Date: 2019-09-23 10:59:40
Message-ID: CA+t6e1no0d-1iOCvUhbLYjEyHcX=Ep6XmeCoKuYtc6RvTdmnZg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hey,
I got the following partitions structure in pg12 beta 3 version :

postgres=# \d+ students
Partitioned table "public.students"
Column | Type | Collation | Nullable | Default | Storage | Stats
target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
id | integer | | not null | | plain |
|
name | text | | | | extended |
|
class | integer | | | | plain |
|
Partition key: HASH (id)
Indexes:
"students_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"students_class_fkey" FOREIGN KEY (class) REFERENCES class(id)
Partitions: students_00 FOR VALUES WITH (modulus 20, remainder 0),
students_01 FOR VALUES WITH (modulus 20, remainder 1),
students_02 FOR VALUES WITH (modulus 20, remainder 2),
students_03 FOR VALUES WITH (modulus 20, remainder 3),
students_04 FOR VALUES WITH (modulus 20, remainder 4)

postgres=# insert into students values(20,'a',1);
ERROR: no partition of relation "students" found for row
DETAIL: Partition key of the failing row contains (id) = (20).

I'm trying to insert a few rows but some of them fail on the following
error : no partition of relation "sutdents" found for row ...

for example :
postgres=# insert into students values(20,'a',1);
ERROR: no partition of relation "students" found for row
DETAIL: Partition key of the failing row contains (id) = (20).
postgres=# insert into students values(2,'a',1);
ERROR: no partition of relation "students" found for row
DETAIL: Partition key of the failing row contains (id) = (2).
postgres=# insert into students values(1,'a',1);
ERROR: duplicate key value violates unique constraint "students_00_pkey"
DETAIL: Key (id)=(1) already exists.
postgres=# insert into students values(2,'a',1);
ERROR: no partition of relation "students" found for row
DETAIL: Partition key of the failing row contains (id) = (2).
postgres=# insert into students values(3,'a',1);
INSERT 0 1
postgres=# insert into students values(4,'a',1);
ERROR: no partition of relation "students" found for row
DETAIL: Partition key of the failing row contains (id) = (4).

The current content of the table :
postgres=# select * from students;
id | name | class
----+------+-------
1 | a | 1
3 | a | 1
(2 rows)

what am I missing ?

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Mariel Cherkassky 2019-09-23 11:02:10 Re: pg12 partitions question
Previous Message MichaelDBA 2019-09-20 13:09:35 Re: Slow query on a one-tuple table