RE: Foreign Key locking / deadlock issue.... v2 - Mailing list pgsql-general

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.

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

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