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

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

Yes, two processes are required to get a deadlock.

> If that is true, why don't you commit the updates to table pl and release the locks?

It's a long story... but I can't change it at the moment, the PL update is a single long-running batch process, which
shouldn'tget in the way of the ELN process.
 

> 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
inpl.pl_id will cause an exception to occur due to the constraint.
 

I don't see how this is irrelevant, it's the FK check against the PL table that is causing the lock contention.
We're not getting FK violations - the data is being constructed correctly.

The issue I have is that the FK check is being blocked by an apparently upgraded lock on the PL table, caused by the
RLSpolicy.
 

I'm running Postgres 9.6.1 (I think)


Phil Horder
Database Mechanic


-----Original Message-----
From: rob stone [mailto:floriparob@gmail.com] 
Sent: 24 March 2018 11:30
To: HORDER Phil; pgsql-general
Subject: 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
inpl.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@gmail.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
>  
>