infinite loop in an update statement

Fabrice Chapuis <fabrice636861@gmail.com> 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

On 9/9/24 07:55, Fabrice Chapuis wrote:

Hi,

table a and b are empty, this query does not return. It seems we enter
in infinite loop.
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)

Do you have an UPDATE trigger on table_a?

Regards,

Fabrice

--
Adrian Klaver
adrian.klaver@aklaver.com

On 9/10/24 07:47, Fabrice Chapuis wrote:
Reply to list also
Ccing list

no trigger on theses tables

On Mon, Sep 9, 2024 at 5:00 PM Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> wrote:

On 9/9/24 07:55, Fabrice Chapuis wrote:

Hi,

table a and b are empty, this query does not return. It seems we

enter

in infinite loop.
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)

Do you have an UPDATE trigger on table_a?

Regards,

Fabrice

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

On Tue, Sep 10, 2024 at 4:49 PM Fabrice Chapuis <fabrice636861@gmail.com>
wrote:

On Wed, Sep 11, 2024 at 6:14 AM Fabrice Chapuis <fabrice636861@gmail.com>
wrote:

status = active

wait event = NULL
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