Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx]
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

 

 


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux