Re: Locking

Simon Attwell <attwell(at)binc(dot)net> writes:
> How does postgresql deal with locking when one has a large select
> query running on a > 700,000 row table, when there are inserts pending
> for that table.

> I have an application that does a _lot_ of inserts, and a frontend that
> makes large long laborious select queries on the same tables.

In Postgres, you don't lock, you just do the operations. The SELECT
query won't see the results of (nor be blocked by) updating transactions
that start after it does. If you need consistency across multiple
SELECTs then wrap them all in a BEGIN/END block. See
http://www.postgresql.org/devel-corner/docs/postgres/mvcc.html
for more detail.

> MySQL has the INSERT DELAYED which allows batch processing of inserts and
> allows clients to receive and instant "OK" when doing inserts.

We think our scheme is a great deal better ;-)

regards, tom lane