Python & Postgres: would psycopg2.connect lock the table?

Question

I am running a python script to do ETL(Extract, transform, load) and I put all the psql queries in one transaction. Here's the transaction:

conn = psycopg2.connect(...)
try:
    cur = conn.cursor() #q1
    cur.execute("create temp table tt (like t INCLUDING DEFAULTS)") #q2
    cur.execute("copy tt from '/file.csv' DELIMITER ',' CSV HEADER ") #q3
    cur.execute("...") #q4,   update t based on data from tt
    conn.commit()
except:
    conn.rollback()

I know the table will be locked when running q4, but I'm not sure if the table would be locked during the whole transaction(from connect to commit)?

Is there a way to test if the table is locked? I don't have much of data right now (about 100 rows)..

Thanks very much!

Python does not have `catch`. It has `except`. Also I don't think there's a reason to lock the table for each connection. — msvalkon, Apr 25 '14 at 20:37
You should never use unconditional `except`. At minimum, `except Exception`, but preferably `except psycopg2.DatabaseError`. You should also always log or re-throw the exception, never silently swallow it. — Craig Ringer, Apr 26 '14 at 06:43

score 3 · Accepted Answer · answered Apr 26 '14 at 05:48

I know the table will be locked when running q4, but I'm not sure if the table would be locked during the whole transaction(from connect to commit)?

Locks are taken when first required, and released at transaction commit, not before.

So in your case, you don't access t until q4, so that's when the lock is taken. UPDATE takes a ROW EXCLUSIVE lock on the table. Despite the name, it's a table level lock; additionally, row-level locks are taken on rows that're updated.

Is there a way to test if the table is locked? I don't have much of data right now (about 100 rows)..

Query pg_locks. Understand that there are both table- and row-level locks.

Python & Postgres: would psycopg2.connect lock the table?

1 Answers1