Feedback-ID: i76984098:Fastmail
Message-ID: <bda1e3df-b58d-4dea-bbc7-d12d5a79e9c7@aklaver.com>
Date: Mon, 15 Jul 2024 09:47:28 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: How does this FK constraint error happen?
To: Ron Johnson <ronljohnsonjr@gmail.com>,
 "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
References: <CANzqJaBF6mnUy1ZfBbySzSr1AfErnrMEGJaUvdSA1H9ZXkTbEw@mail.gmail.com>
 <20240715143518.3v5xdfuj27ryzuzh@hjp.at>
 <CANzqJaCZkwRon9xDOeFaTd05KZM--Eei1SAiattJXurx_8n0bQ@mail.gmail.com>
 <4ff0dd59-9d7b-4a13-ad75-3627080458e1@aklaver.com>
 <CANzqJaC0EYvpD-452JVS4UNH8D0qFaXygBR5je=_zK2pDziM5w@mail.gmail.com>
 <28c88e41-36bc-4704-9c30-f81986f6cdc8@aklaver.com>
 <CANzqJaCyPpUReKCy7G8mVczApoKq73-zfzbSOvpgpCGJ86igVg@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CANzqJaCyPpUReKCy7G8mVczApoKq73-zfzbSOvpgpCGJ86igVg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/bda1e3df-b58d-4dea-bbc7-d12d5a79e9c7%40aklaver.com>
Precedence: bulk

On 7/15/24 09:21, Ron Johnson wrote:
> On Mon, Jul 15, 2024 at 11:37 AM Adrian Klaver 
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
> 


>     I don't think it is entirely coincidental that 1210 is the only shown
>     user_id with a modified_on value that is in proximity to the delete
>     error.
> 
> 
> I don't think so either.
> 
>     My suspicion is that actions are not happening in the exact order
>     you think they are.
> 
> 
> modified_on is CURRENT_TIMESTAMP or NOW() or somesuch.  I'm not sure, 
> because I'm not privy to the code.
> 
> But I'm printing the system time in bash before every statement.

That is why I wrote 'Time travel?'.

I suspect the modified_on time in the table is not accurately 
representing when the row is modified.

> 
>     I would think that combining DELETE FROM
>     rel_group_user; and DELETE FROM public.access_user; in a single
>     transaction would be a good start to fixing this.
> 
> 
> That is in fact what I'm working on now.  There are 26 tables, and they 
> must be done in a specific order when deleting, and the reverse while 
> inserting.
> 
> postgres_fdw would make this easier...

It can't be installed?


-- 
Adrian Klaver
adrian.klaver@aklaver.com