Subject: Re: infinite loop in an update statement

From: Fabrice Chapuis <[email-masked]>
Date: Wed, 11 Sep 2024 12:14:19 +0200

On Tue, Sep 10, 2024 at 4:49 PM Fabrice Chapuis <[email-masked]>
wrote:

> no lock, in view pg_stat_activity
>
> status = active
> wait event = NULL
> wait event type = NULL
>
> On Mon, Sep 9, 2024 at 5:00 PM Tom Lane <[email-masked]> wrote:
>
>> Fabrice Chapuis <[email-masked]> writes:
>> > why this update does not return instantly?
>>
>> > UPDATE table_a a
>> >  SET col1 = (SELECT MIN(b.col1)
>> >                     FROM table_b b
>> >                     WHERE b.col2 = a.col2)
>>
>> Maybe query is waiting for a lock on one of those tables?
>>
>>                         regards, tom lane
>>
>

---

From: Greg Sabino Mullane <[email-masked]>
Date: Wed, 11 Sep 2024 08:29:40 -0400

>> wait event type = NULL
That seems quite unlikely. Perhaps you are seeing the pg_stat_activity
query itself? Try this:

select state, now()-state_change, wait_event_type, wait_event, query
from pg_stat_activity where query ~ 'SELECT MIN' and pg_backend_pid() <>
pid;

Cheers,
Greg