0

I just learned about using writable common table expressions in postgres-9.1, specifically from this site. http://vibhorkumar.wordpress.com/2011/10/26/upsertmerge-using-writable-cte-in-postgresql-9-1/

WITH upsert as
(update mytable2 m set sales=m.sales+d.sales, status=d.status from mytable d where m.pid=d.pid
  RETURNING m.*
)
insert into mytable2 select a.pid, a.sales,'NEW' from mytable a where a.pid not in (select b.pid from upsert b);

I mentioned this to some coworkers and I was asked about the concurrency model/safety that postgres using when doing an operation like this. My first thought was that mytable gets locked for the entire execution of the statement so that this should be thread safe in all circumstances.

Is that a correct assumption? I don't know a ton about the internal concurrency model of postgres's statement execution. But if anyone wants to go into as much detail as they'd like, that'd be great =]

Falmarri
  • 47,727
  • 41
  • 151
  • 191

1 Answers1

1

PostgreSQL handles concurrent operations via MVCC, so basic CRUD operations never lock.

The transaction will see only data that has been committed before it started. Whilst it's running it will not get notified about any changes happening outside of it, i.e. it sees only its own changes.

In case you need to make sure that no other process changes data in parallel you'll need to setup explicit locking.

Good read is in the docs: http://www.postgresql.org/docs/current/static/mvcc-intro.html

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
PepperBob
  • 719
  • 3
  • 8
  • 1
    It looks to me like that statement will either perform the desired upsert or fail with a duplicate key error. On error, *none* of the requested activity is persisted, so a retry on a duplicate key error would be safe. – kgrittn Apr 19 '12 at 20:38