Re: Rows removed on child table when updating parent partitioned table. - Mailing list pgsql-general
I've been away from coding for several years, but dusting off my chops and getting back up to speed with PostgreSQL (love it!). So please forgive me if my early answers here come off as naive. But my understanding of this suggests that you shouldn't be using "update" on a serial field. I'm guessing that under the covers things are getting confused because your update doesn't also address the sequence that's implicitly created when you define a field as "serial". If you use "update" I'm guessing that nextval in the corresponding sequence is *not* updated accordingly.
Have you tried this with setval() or nextval() rather than update? You can compare the difference between these and "update" by checking currval() after each. Again - I apologize for incomplete knowledge here, but I'm speculating that use of "update" on an auto-incrementing serial field is outside intended / supported behavior, and it may well just be that it winds up being handled differently under the covers when the data table and/or associated sequence are partitioned.
On Thu, Oct 1, 2020 at 1:00 PM Eduard Català <eduard.catala@gmail.com> wrote:
Hi developers,We have a strange case where some rows are removed. I think it is a bug, but before notifying it I prefer to ask here where I am wrong.Postgres 12Given the following structure:create table parent ( id serial, constraint parent_pkey primary key (id)) partition by range (id);
create table parent_10 partition of parent for values from (0) to (10);
create table parent_20 partition of parent for values from (11) to (20);
create table child (
id serial,
parent_id int constraint parent_id_fk references parent(id) on update cascade on delete cascade);-- Notice the on update cascade on delete cascade.insert into parent values(0);insert into child values(1,0);
-- Here are the rows
postgres=# table parent;
id
----
0
(1 row)
postgres=# table child;
id | parent_id
----+-----------
1 | 0
(1 row)-- Update the parent table id, with a value contained in the same partition
update parent set id = 5;
postgres=# table parent;
id
----
5
(1 row)
postgres=# table child;
id | parent_id
----+-----------
1 | 5
(1 row)
-- Update the parent table, with a value contained into other partition
update parent set id = 15;
postgres=# update parent set id = 15;
UPDATE 1
postgres=# table parent;
id
----
15
(1 row)
postgres=# table child;
id | parent_id
----+-----------
(0 rows)No error or warning was thrown. The rows in the child table were removed.I think what has happened is: The update caused a DELETE in the table parent_10 (removing the rows from child table) and then the INSERT into parent_20.We've checked the documentation but didn't find something about this unexpected behaviour.Trying without "on delete cascade" clause throws a "parent key not found error".Thank you!
pgsql-general by date: