Postgres Pro
Downloads
Home   >   mailing lists

Thread: infinite loop in an update statement

infinite loop in an update statement

From
Fabrice Chapuis
Date:
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)

Regards,

Fabrice


Re: infinite loop in an update statement

From
Tom Lane
Date:
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

Re: infinite loop in an update statement

From
Adrian Klaver
Date:
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

Re: infinite loop in an update statement

From
Adrian Klaver
Date:
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

Re: infinite loop in an update statement

From
Fabrice Chapuis
Date:


On Tue, Sep 10, 2024 at 4:49 PM Fabrice Chapuis <fabrice636861@gmail.com> 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 <tgl@sss.pgh.pa.us> wrote:
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

Re: infinite loop in an update statement

From
Greg Sabino Mullane
Date:
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