11

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?

Nick
  • 1,148
  • 2
  • 14
  • 28
  • 1
    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 Mar 12 '14 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 Mar 12 '14 at 17:12
  • yep, just verified that is what is happening... Now I need to understand how – Nick Mar 12 '14 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 Mar 12 '14 at 17:26
  • Please post an answer to your own question with that info. – Craig Ringer Mar 12 '14 at 23:35
  • Instead of editing the answer into your question, can you please post it as an answer and accept it? That will let us know the problem is solved and also allow others to find the solution easily – Basic Mar 12 '14 at 23:50
  • 9
    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 Dec 18 '15 at 18:15
  • conn.autocommit = True is raising "AttributeError: 'psycopg2.extensions.connection' object attribute 'commit' is read-only" – vladimir.gorea Nov 14 '20 at 07:31

1 Answers1

4

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.

Nick
  • 1,148
  • 2
  • 14
  • 28