Thread: How does Delete Query work ??

sri harsha <sriharsha9992@gmail.com> wrote:

> I want to know about the flow in which delete query is executed
> in postgres .
>
> Assume i have a following query.
>
> Delete from table_a * where column_a = 'something'
>
> How is this query executed ? Are the rows first filtered out and
> then deleted one by one ?? Or is the row deleted as and when it
> is filtered ??

This is not as simple as you might think.  Since this really only
matters if there is some sort of concurrent operation, you might
want to review the documentation on that:

http://www.postgresql.org/docs/9.2/interactive/mvcc.html

To simplify (ignoring rule rewrites, security, constraints,
triggers, row level locking, etc.), what happens is that the table
is scanned (possibly through an index or other indirect means) and
tuples are checked for MVCC visibility and the conditions on your
WHERE clause, setting the xmax to indicate what transaction is
deleting the tuple.  Until the deleting transaction commits, no
other transaction is blocked from reading the tuple, and even after
the transaction commits reads may be using snapshots which include
the tuple.  If the deleting transaction is rolled back, the delete
effectively "never happened"; if it commits, queries using new
snapshots will ignore the tuple even though they may need to read
it and it is still present in the table.  Once there are no
remaining snapshots which would allow a query to see the tuple it
may be pruned or vacuumed to actually remove the tuple and free the
space it is taking for a new tuple.  At a later point a vacuum
process will remove any old index entries pointing to the tuple.

Like I said, this is simplified quite a bit, so without knowing why
you are asking it is possible I left out something you would want
to know about; but hopefully if you read the documentation you can
pick up on any such details.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company