Обсуждение: BUG #15100: sequence behavior on failed insert to a partitioned table
BUG #15100: sequence behavior on failed insert to a partitioned table
От
PG Bug reporting form
Дата:
The following bug has been logged on the website: Bug reference: 15100 Logged by: Istvan Kassai Email address: kassai1972@gmail.com PostgreSQL version: 10.3 Operating system: linux Description: Hi! I started to deal with the new declarative table partitioning in 10. I found that a failed insert (due to the field I've tried to insert is out of partition range) also increments the sequence of the base table, even if the insert is rejected. A simple sample: Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ----------+------------------------+-----------+----------+-----------------------------------------+----------+--------------+------------- sor_azon | integer | | not null | nextval('teszt_sor_azon_seq'::regclass) | plain | | szoveg | character varying(100) | | | | extended | | Partition key: RANGE (szoveg) Partitions: teszt_a FOR VALUES FROM ('a%') TO ('b%'), teszt_b FOR VALUES FROM ('b%') TO ('c%') I've inserted two rows to test if it is working well: o=# insert into teszt (szoveg) values('aladár'); INSERT 0 1 o=# insert into teszt (szoveg) values('béla'); INSERT 0 1 then I inserted an out-of-range value: o=# insert into teszt (szoveg) values('cicu'); ERROR: no partition of relation "teszt" found for row DETAIL: Partition key of the failing row contains (szoveg) = (cicu). It is working as expected. After it I queried the content for the table: o=# select * from teszt; sor_azon | szoveg ----------+-------- 1 | aladár 2 | béla (2 rows) Seems all is good. BUT! After it I created a new partition: o=# CREATE TABLE teszt_c PARTITION OF teszt FOR VALUES FROM ('c%') TO ('z%'); CREATE TABLE Then I tried to insert the last recently rejected value again: o=# insert into teszt (szoveg) values('cicu'); INSERT 0 1 And a query again: o=# select * from teszt; sor_azon | szoveg ----------+-------- 1 | aladár 2 | béla 4 | cicu (3 rows) As you can see the sor_azon isn't continous. The #3 is missing. As I think the sequence hasn't rolled back after a failed insert. I'm not sure is it a bug, but it is better you know about it. bye Istvan