Re: Rows removed on child table when updating parent partitioned table.

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.

- Jon

<https://www.linkedin.com/in/jonstrong/>
<https://www.jonathanrstrong.com>

*Jonathan Strong*

CIO / CTO / Consultant

*P:* 609-532-1715 *E:* jonathanrstrong(at)gmail(dot)com

*Quora Top Writer <https://www.quora.com/profile/Jonathan-R-Strong>*

On Thu, Oct 1, 2020 at 1:00 PM Eduard Català <eduard(dot)catala(at)gmail(dot)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 12*
> Given 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!
>
>
>
>
>