PostgreSQL – Does CREATE TABLE AS Lock the Table?

lockingpostgresqltable

In Postgres, does this query lock films for reads and/or writes?

CREATE TABLE films2 AS
  TABLE films;

Best Answer

No it does not create a read or write lock on films table as this creates ACCESS SHARE LOCK;

to test this open two sessions

in session one run this command

  begin;
  create table films2 as 
     table films;

then in the second session run this command

Select * from films;
Update films set something = 0

If you need to lock films table you have to issue a Lock like so

begin ;
  Lock table films in exclusive mode;
  create table films2 as 
     table films;
commit;

this will block the table from updates but not reads...

NOTE Access Share Locks block any transaction that try to acquire an ACCESS EXCLUSIVE locks such as DROP Films etc...

Related Question