Re: Allow DELETE to use ORDER BY and LIMIT/OFFSET

Поиск
Список
Период
Сортировка
От Corey Huinker
Тема Re: Allow DELETE to use ORDER BY and LIMIT/OFFSET
Дата
Msg-id CADkLM=cG9r=A2ZP=YFe6eQbUY12NndLUb0UAA_3qqL4ieifCBA@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Allow DELETE to use ORDER BY and LIMIT/OFFSET  (Yugo NAGATA <nagata@sraoss.co.jp>)
Список pgsql-hackers
Out of curiosity, could you please tell me the concrete situations
where you wanted to delete one of two identical records?

In my case, there is a table with known duplicates, and we would like to delete all but the one with the lowest ctid, and then add a unique index to the table which then allows us to use INSERT ON CONFLICT in a meaningful way.

The other need for a DELETE...LIMIT or UPDATE...LIMIT is when you're worried about flooding a replica, so you parcel out the DML into chunks that don't cause unacceptable lag on the replica.

Both of these can be accomplished via  DELETE FROM foo WHERE ctid IN ( SELECT ... FROM foo ... LIMIT 1000), but until recently such a construct would result in a full table scan, and you'd take the same hit with each subsequent DML.

I believe that the ctid range scan now can limit those scans, especially if you can order the limited set by ctid, but those techniques are not widely known at this time.

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

Предыдущее
От: Robert Haas
Дата:
Сообщение: Re: sqlsmith: ERROR: XX000: bogus varno: 2
Следующее
От: Tom Lane
Дата:
Сообщение: Re: sqlsmith: ERROR: XX000: bogus varno: 2