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