|
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----- 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 |
