Re: Foreign Key locking / deadlock issue.... v2

Hello Phil,

On Fri, 2018-03-23 at 15:42 +0000, HORDER Phil wrote:
> Rob, thanks for looking.
>
> The "pause" is only to not-do-the-commit yet, so that the child
> process can then try and access the record - I've not left anything
> out.
> This code is my own demo, not a cut from our production code.
>
> Did you run this as the 'postgres' superuser? That would bypass the
> RLS, and probably avoid the problem.

No. Never use super user to test scripts.

>
> I checked by creating a new user, and ran my code in that:
>
> Sql> create user test password 'password';
>
> After running my test script, psql \dp shows:
>
> Schema | Name | Type | Access privileges
> | Column privileges | Policies
> --------+-------------------+-------+------------------------------
> ---+-------------------+------------------
> public | eln | table |
> | |
> public | pl | table |
> | | security_policy:+
> | | |
> | | (u): true +
> | | |
> | | (c): true
>
> (plus some other stuff for postGIS)
>
> Here’s my code again:
>
> drop table if exists ELN;
> drop table if exists PL;
>
> Create table PL
> (pl_id integer,
> m_number text
> );
>
> alter table PL ENABLE row level security;
> alter table PL FORCE row level security;
>
> drop policy if exists security_policy on PL ;
> CREATE POLICY security_policy on PL FOR ALL TO PUBLIC USING (true)
> WITH CHECK (true);
>
> Alter table PL add constraint PL_PK primary key (pl_id);
> Insert into PL values (1, null);
> Insert into PL values (2, null);
> Insert into PL values (3, null);
>
> Create table ELN
> (event_id integer,
> pl_id integer
> );
>
>
> Alter table ELN add constraint ELN_PK primary key (event_id);
> Alter table ELN add constraint ELN_PL_FK foreign key (pl_id)
> references PL (pl_id);
>
> Insert into ELN values (301, null);
> Insert into ELN values (302, null);
> Insert into ELN values (303, null);
>
> commit;
>
> -- process 1:
> start transaction;
> update pl set m_number = '234' where pl_id = 2;
> update pl set m_number = '345' where pl_id = 3;
>
> -- wait here, and run process 2

This was never explained. You are running "process 2" in an entirely
different session????

If that is true, why don't you commit the updates to table pl and
release the locks?
The fact that eln.pl_id is a foreign key is irrelevant. If you tried to
alter eln.pl_id to a value that doesn't exist in pl.pl_id will cause an
exception to occur due to the constraint.

Cheers,
Rob

> commit;
>
>
> -- process 2:
> start transaction;
> update eln set pl_id = 3 where event_id = 303;
> update eln set pl_id = 2 where event_id = 302;
> -- Gets blocked by process 1
> commit;
>
>
> Phil Horder
> Database Mechanic
>
> -----Original Message-----
> From: rob stone [mailto:floriparob(at)gmail(dot)com]
> Sent: 23 March 2018 11:43
> To: HORDER Phil; pgsql-general
> Subject: Re: Foreign Key locking / deadlock issue.... v2
>
> Hello Phil,
>
> I've run your sample script on 9.6.5 and 10.3.
> The only thing that I added was a commit; after the initial inserts
> just to ensure the rows were saved.
> No errors were reported for either version.
>
> The output of \dp after running was:-
>
> Access privileges Schema | Name |
> Type | Access privileges | Column privileges
> | Policies
> --------+------+-------+-------------------+-------------------+-----
> --
> -----------
> public | eln | table | | |
> public | pl | table | | |
> security_policy:+
> | | | | |
> (u):
> true
>
>
> --> including the FOR ALL in the create policy statement as well as
> WITH CHECK(true).
>
> Access privileges Schema | Name |
> Type | Access privileges | Column privileges
> | Policies
> --------+------+-------+-------------------+-------------------+-----
> --
> -----------
> public | eln | table | | |
> public | pl | table | | |
> security_policy:+
> | | | | |
> (u):
> true +
> | | | | |
> (c):
> true
>
>
> The only mystery is what happens here:-
>
> <snip>
>
> -- …. Pause while other processing happens …..
> (commit;)
>
> -- Child table processing – occurs often & quickly. Starts after
> parent update.
>
> <\snip>
>
>
> I'd like to know more about RLS and trying to de-bug your script.
>
> On a production application you'd be testing for errors and raising
> exceptions so as to inform users that a problem occurred.
>
> So, without knowing what occurs during "Pause while other processing
> happens" I can't help any further.
>
> Cheers,
> Rob
>
>