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?
connect()
call. Make sure you're not accessingcrowdsurfer
from other parts of the code or from the command-line usingpsql
. – fog Mar 12 at 16:53