On Mon, 30 Nov 2015 23:07:36 -0500, "Steve Petrie, P.Eng." <apetrie@xxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
