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

Thanks Rob,

The security policy in the example was reduced to the bare minimum, to demonstrate the issue was with the existence of the policy, not what it did.
Obviously my real-application policy does much more, and uses other columns to do both read & write checks.

Adding the write check as suggested makes no difference to the demo - the blocking lock still occurs.

Phil Horder
Database Mechanic

-----Original Message-----
From: rob stone [mailto:floriparob(at)gmail(dot)com]
Sent: 22 March 2018 11:11
To: HORDER Phil; Adrian Klaver
Cc: pgsql-general
Subject: Re: Foreign Key locking / deadlock issue.... v2

Hello Phil,

On Wed, 2018-03-21 at 15:26 +0000, HORDER Phil wrote:
> OK,
> Let's try again, with a full script, and including the bit that makes
> the difference…
>
> Hi,
> I’m trying to understand why I’m getting a deadlock issue, and how to
> work around it.
>
> At base, I think the problem is:
> 1. Updates to a parent table are creating row level write
> locks.
> 2. updates to a child table set the foreign key value to
> the parent table, which are then blocked – because there is Row Level
> Security on the parent table.
>
>
>
> -- The setup
> ------------
>
> 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;
>
> -- A dummy security policy, that allows everybody access to everything
> drop policy if exists security_policy on PL ;

I don't use row level security but after reading the documentation, I'd alter this:-

> create policy security_policy on PL TO public using (true); -- (1)

to

CREATE POLICY security_policy on PL FOR ALL TO PUBLIC USING (true) WITH CHECK (true);

and trying again.

HTH,
Rob