postgres - cannot drop database using psycopg2

Question

So I simply trying to drop and recreate my database using Python's psycopg2. Here is my code:

    with psycopg2.connect(database="postgres", user="postgres", password="****") as conn:
    with conn.cursor() as cur:
        conn.autocommit = True   #  Explains why we do this - we cannot drop or create from within a DB transaction. http://initd.org/psycopg/docs/connection.html#connection.autocommit
        cur.execute("DROP DATABASE crowdsurfer;")
        cur.execute("CREATE DATABASE crowdsurfer;")

When I run this code, I get

PS C:\Users\Nick\Documents\GitHub\CrowdSurfer\CrowdSurfer> python utils/sqlInit.py
Traceback (most recent call last):

  File "utils/sqlInit.py", line 70, in <module>
    run()

  File "utils/sqlInit.py", line 21, in run
recreate_empty_database()

  File "utils/sqlInit.py", line 40, in recreate_empty_database
    cur.execute("DROP DATABASE crowdsurfer;")

psycopg2.OperationalError: database "crowdsurfer" is being accessed by other users
DETAIL:  There is 1 other session using the database.

Alright, fair enough. So I opened a connection to postgres and took a peek at the existing processed before and while my code was running. Before my code started, we get this:

postgres=# select pid from pg_stat_activity

This command returns a single PID, PID 6052

This process is me, so that's good. Now here is what I get when query running processes while my python code is running:

 postgres=# select * from pg_stat_activity;
 datid  |   datname   | pid  | usesysid | usename  | application_name | client_addr | client_hostname | client_port |       backend_start        |         xact_start         |        query_start         |
       state_change        | waiting | state  |                         query

  12029 | postgres    | 6052 |       10 | postgres | psql             | ::1         |                 |       49842 | 2014-03-11 23:14:34.049-06 | 2014-03-11 23:14:58.938-06 | 2014-03-11 23:14:58.938-06 |

2014-03-11 23:14:58.938-06 | f       | active | select * from pg_stat_activity;
 142547 | crowdsurfer | 3952 |       10 | postgres |                  | 127.0.0.1   |                 |       49849 | 2014-03-11 23:14:57.489-06 |                            | 2014-03-11 23:14:57.491-06 |
2014-03-11 23:14:57.491-06 | f       | idle   | SET default_transaction_isolation TO 'read committed'

  12029 | postgres    | 7908 |       10 | postgres |                  | ::1         |                 |       49851 | 2014-03-11 23:14:57.556-06 | 2014-03-11 23:14:57.559-06 | 2014-03-11 23:14:57.559-06 |
2014-03-11 23:14:57.559-06 | f       | active | DROP DATABASE crowdsurfer;
(3 rows)

The python code started 2 processes! One connects to the postgres DB, which I did explicitly. The other connects to the DB I want to delete (crowdsurfer). Note that it is idle, and the query it ran was SET default_transaction_isolation TO 'read committed'

So it seems like setting conn.autocommit equal to true is creating a new process??? Any thoughts on what to do here to make drop this DB?

psycopg2 doesn't open any extra connection and creates exactly one connection for every connect() call. Make sure you're not accessing crowdsurfer from other parts of the code or from the command-line using psql. — fog
– fog, Commented Mar 12, 2014 at 16:53
You're right. I set a couple breakpoints and then looked at the active processes. When I set a breakpoint immediately after if name == "main": (the first line of code in the script), the second process is already created. Does this mean an import is creating this sql process? — Nick
– Nick, Commented Mar 12, 2014 at 17:12
yep, just verified that is what is happening... Now I need to understand how — Nick
– Nick, Commented Mar 12, 2014 at 17:16
figured it out: one of the imported modules had a function with a decorator that was opening the connection. I never knew decorators were executed during import. Thank you for the help! — Nick
– Nick, Commented Mar 12, 2014 at 17:26
I came here looking for the solution to psycopg2.InternalError: DROP DATABASE cannot run inside a transaction block. conn.autocommit = True fixed my problem. Thanks! — Chuck Wilbur
– Chuck Wilbur, Commented Dec 18, 2015 at 18:15

Nick · Accepted Answer · 2014-03-13 21:34:21Z

5

Here is what happened. One of the imported classes had a decorator that was opening the connection. This is a standard Django decorator transaction.atomic (I actually incorrectly applied it to a class as opposed to a method). Apparently it is executed during the import process, opening a connection to the postgres DB.

answered Mar 13, 2014 at 21:34

Nick

1,2683 gold badges15 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgres - cannot drop database using psycopg2

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related