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:floripa...@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
