Re: using a postgres table as a multi-writer multi-updater queue

On Mon, 30 Nov 2015 23:07:36 -0500, "Steve Petrie, P.Eng."
<apetrie(at)aspetrie(dot)net> wrote:

>Instead of using a DELETE command to destroy the row (and a
>resource-intensive AUTOVACUUM / VACUUM process to reclaim the storage
>space), why not instead, simply mark that session management row as "free"
>(with an UPDATE command) ??

Alban beat me to the answer 8-)
But to expand a little:

Your plan won't work because Postgresql does not update in place - it
inserts a new changed row and marks the old as deleted. It does not
physically overwrite the old row until the table is vacuumed.
[If even then - a row which lies beyond the logical end-of-table when
vacuum is finished won't be overwritten until its space is recycled.]

This behavior, known as MVCC (multiple version concurrency control),
is integral to transaction isolation: selects which are running
concurrently with the update may already have seen the old row and
must continue to see it until they complete, even if the update
completes first.

Postgresql doesn't support "dirty read" isolation. A row can't be
physically dropped or its space overwritten while any transaction that
can "see" it is still running.

For more:
https://devcenter.heroku.com/articles/postgresql-concurrency
https://momjian.us/main/writings/pgsql/mvcc.pdf

Actually lots of great stuff in the presentation section on Bruce
Momjian's site: https://momjian.us

George