Inexplicable UPDATE...RETURNING behaviour

Поиск
Список
Период
Сортировка
От Joe Wildish
Тема Inexplicable UPDATE...RETURNING behaviour
Дата
Msg-id 9C47F3F5-D646-4A81-A8CC-FA8FD40EFF07@elusive.cx
обсуждение исходный текст
Ответы Re: Inexplicable UPDATE...RETURNING behaviour
Список pgsql-general
Hello all,

We are seeing an inexplicable behaviour when issuing an "UPDATE..RETURNING" statement. I am unsure if it is a Postgres
bug.Additional eyes-on would be much appreicated. 

When issuing the following statement we are seeing multiple rows UPDATE'd despite the use of LIMIT 1 and despite the
"uid"column in the "some_queue" table having a PRIMARY KEY constraint on it: 

UPDATE queue.some_queue AS q
   SET (state, awaiting) = ('executing', FALSE)
  FROM (SELECT uid
          FROM queue.some_queue
         WHERE awaiting
           AND process_after <= CURRENT_TIMESTAMP AT TIME ZONE 'UTC'
         ORDER BY process_after ASC
           FOR UPDATE SKIP LOCKED
         LIMIT 1)
    AS dq(uid)
 WHERE q.uid = dq.uid
 RETURNING q.uid;

However, when using the following statement, which (AFAIK) is semantically equivalent, we see only a single row being
updated/dequeued:

UPDATE queue.some_queue AS q
   SET (state, awaiting) = ('executing', FALSE)
 WHERE uid = (SELECT uid
                FROM queue.some_queue
               WHERE awaiting
                 AND process_after <= CURRENT_TIMESTAMP AT TIME ZONE 'UTC'
               ORDER BY process_after ASC
                 FOR UPDATE SKIP LOCKED
               LIMIT 1)
 RETURNING uid;

IMO the two statements should yield the same result. But, we see the first one updating multiple rows and therefore
dequeingmultiple uids, yet the second one functions as intended (ie. single item is dequeued). 

We can replicate this locally in tests but I can't explain it. Is this a bug, or am I overlooking something?

Cheers,
-Joe

PG. Postgres 10.6 in production, and the same behaviour with 10.5 + 11.2 in dev.


В списке pgsql-general по дате отправления:

Предыдущее
От: Andreas Kretschmer
Дата:
Сообщение: Re: SQLSTATE when PostgreSQL crashes during COMMIT statement
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Inexplicable UPDATE...RETURNING behaviour