psycopg2 craziness

[andydtaylor]

Hi,

I'm trying to use psycopg2 but having some issues. Would it be possible to get some pointers? I seem unable to execute any SQL statements.


So here are my database tables before (and after) I try to do anything:

postgres=# \c
You are now connected to database "postgres" as user "postgres".
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+-------------------------+----------+------------
public | loc_all | table | django_dev
public | loc_all_unique | table | django_dev
public | loc_dlr | table | django_dev
public | loc_londonbuses | table | django_dev
public | loc_londonriverservices | table | django_dev
public | loc_londonunderground | table | django_dev
public | loc_tramlink | table | django_dev
public | lu_stations_id_seq | sequence | postgres
public | postcode_input | table | postgres
public | postcode_lat_long | table | django_dev
public | test_foo | table | django_dev
public | tubecross | table | django_dev


Here's what I've been trying to execute in the python shell:
.... host = 'localhost',
.... database = 'postgres',
.... user = 'postgres',
.... password = 'password'
.... )Traceback (most recent call last):
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
psycopg2.InternalError: current transaction is aborted, commands ignored until end of transaction block


Here's the postgres log:

2013-02-21 22:22:20 GMT LOG: database system was shut down at 2013-02-21 22:21:41 GMT
2013-02-21 22:22:20 GMT LOG: autovacuum launcher started
2013-02-21 22:22:20 GMT LOG: database system is ready to accept connections
2013-02-21 22:22:20 GMT LOG: incomplete startup packet
2013-02-21 22:22:21 GMT FATAL: password authentication failed for user "postgres"
2013-02-21 22:22:22 GMT FATAL: password authentication failed for user "postgres"
2013-02-21 22:22:22 GMT FATAL: password authentication failed for user "postgres"
2013-02-21 22:22:23 GMT FATAL: password authentication failed for user "postgres"
2013-02-21 22:22:23 GMT FATAL: password authentication failed for user "postgres"
2013-02-21 22:22:24 GMT FATAL: password authentication failed for user "postgres"
2013-02-21 22:22:24 GMT FATAL: password authentication failed for user "postgres"
2013-02-21 22:22:25 GMT FATAL: password authentication failed for user "postgres"
2013-02-21 22:22:25 GMT FATAL: password authentication failed for user "postgres"
2013-02-21 22:22:26 GMT FATAL: password authentication failed for user "postgres"
2013-02-21 22:22:26 GMT FATAL: password authentication failed for user "postgres"
2013-02-21 22:22:26 GMT LOG: incomplete startup packet
2013-02-21 22:28:52 GMT ERROR: relation "foo" already exists
2013-02-21 22:28:52 GMT STATEMENT: CREATE TABLE foo (id serial PRIMARY KEY);
2013-02-21 22:34:53 GMT ERROR: current transaction is aborted, commands ignored until end of transaction block
2013-02-21 22:34:53 GMT STATEMENT: CREATE TABLE foo (id serial PRIMARY KEY);




Further example from python shell:

andyt@andyt-ThinkPad-X61:~$ python
Python 2.7.3 (default, Sep 26 2012, 21:51:14)
[GCC 4.7.2] on linux2
Type "help", "copyright", "credits" or "license" for more information..... host = 'localhost',
.... database = 'postgres',
.... user = 'postgres',
.... password = 'password'
.... )Traceback (most recent call last):
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
psycopg2.InternalError: current transaction is aborted, commands ignored until end of transaction block


Postgres log:

2013-02-21 23:08:18 GMT LOG: database system was shut down at 2013-02-21 23:07:40 GMT
2013-02-21 23:08:18 GMT LOG: autovacuum launcher started
2013-02-21 23:08:18 GMT LOG: database system is ready to accept connections
2013-02-21 23:08:18 GMT LOG: incomplete startup packet
2013-02-21 23:08:19 GMT FATAL: password authentication failed for user "postgres"
2013-02-21 23:08:19 GMT FATAL: password authentication failed for user "postgres"
2013-02-21 23:08:20 GMT FATAL: password authentication failed for user "postgres"
2013-02-21 23:08:20 GMT FATAL: password authentication failed for user "postgres"
2013-02-21 23:08:21 GMT FATAL: password authentication failed for user "postgres"
2013-02-21 23:08:21 GMT FATAL: password authentication failed for user "postgres"
2013-02-21 23:08:22 GMT FATAL: password authentication failed for user "postgres"
2013-02-21 23:08:22 GMT FATAL: password authentication failed for user "postgres"
2013-02-21 23:08:23 GMT FATAL: password authentication failed for user "postgres"
2013-02-21 23:08:23 GMT FATAL: password authentication failed for user "postgres"
2013-02-21 23:08:24 GMT FATAL: password authentication failed for user "postgres"
2013-02-21 23:08:24 GMT LOG: incomplete startup packet
2013-02-21 23:19:23 GMT ERROR: table "tubecross" does not exist
2013-02-21 23:19:23 GMT STATEMENT: DROP TABLE tubecross
2013-02-21 23:22:05 GMT ERROR: current transaction is aborted, commands ignored until end of transaction block
2013-02-21 23:22:05 GMT STATEMENT: SELECT * FROM loc_tramlink


Thanks,



Andy

 

[MRAB]

Hi,

I'm trying to use psycopg2 but having some issues. Would it be possible to get some pointers? I seem unable to execute any SQL statements.


So here are my database tables before (and after) I try to do anything:

postgres=# \c
You are now connected to database "postgres" as user "postgres".
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+-------------------------+----------+------------
public | loc_all | table | django_dev
public | loc_all_unique | table | django_dev
public | loc_dlr | table | django_dev
public | loc_londonbuses | table | django_dev
public | loc_londonriverservices | table | django_dev
public | loc_londonunderground | table | django_dev
public | loc_tramlink | table | django_dev
public | lu_stations_id_seq | sequence | postgres
public | postcode_input | table | postgres
public | postcode_lat_long | table | django_dev
public | test_foo | table | django_dev
public | tubecross | table | django_dev


Here's what I've been trying to execute in the python shell:

... host = 'localhost',
... database = 'postgres',
... user = 'postgres',
... password = 'password'
... )

You ask it to create a table "foo":

Done. The table "foo" has been created.

You ask it to create a table "foo" again:

It complains because it already exists. No surprise there, you've
already created it.

Traceback (most recent call last):

Traceback (most recent call last):
File "<stdin>", line 1, in <module>
psycopg2.InternalError: current transaction is aborted, commands ignored until end of transaction block

Maybe you need to commit the change?

Here's the postgres log:

2013-02-21 22:22:20 GMT LOG: database system was shut down at 2013-02-21 22:21:41 GMT
2013-02-21 22:22:20 GMT LOG: autovacuum launcher started
2013-02-21 22:22:20 GMT LOG: database system is ready to accept connections
2013-02-21 22:22:20 GMT LOG: incomplete startup packet
2013-02-21 22:22:21 GMT FATAL: password authentication failed for user "postgres"
2013-02-21 22:22:22 GMT FATAL: password authentication failed for user "postgres"
2013-02-21 22:22:22 GMT FATAL: password authentication failed for user "postgres"
2013-02-21 22:22:23 GMT FATAL: password authentication failed for user "postgres"
2013-02-21 22:22:23 GMT FATAL: password authentication failed for user "postgres"
2013-02-21 22:22:24 GMT FATAL: password authentication failed for user "postgres"
2013-02-21 22:22:24 GMT FATAL: password authentication failed for user "postgres"
2013-02-21 22:22:25 GMT FATAL: password authentication failed for user "postgres"
2013-02-21 22:22:25 GMT FATAL: password authentication failed for user "postgres"
2013-02-21 22:22:26 GMT FATAL: password authentication failed for user "postgres"
2013-02-21 22:22:26 GMT FATAL: password authentication failed for user "postgres"
2013-02-21 22:22:26 GMT LOG: incomplete startup packet
2013-02-21 22:28:52 GMT ERROR: relation "foo" already exists
2013-02-21 22:28:52 GMT STATEMENT: CREATE TABLE foo (id serial PRIMARY KEY);
2013-02-21 22:34:53 GMT ERROR: current transaction is aborted, commands ignored until end of transaction block
2013-02-21 22:34:53 GMT STATEMENT: CREATE TABLE foo (id serial PRIMARY KEY);




Further example from python shell:

andyt@andyt-ThinkPad-X61:~$ python
Python 2.7.3 (default, Sep 26 2012, 21:51:14)
[GCC 4.7.2] on linux2
Type "help", "copyright", "credits" or "license" for more information.... host = 'localhost',
... database = 'postgres',
... user = 'postgres',
... password = 'password'
... )

You ask it to drop the table "tubecross" if it exists.

Done. The table "done", if it ever existed, has been dropped.

You ask it to drop the table "tubecross" again.

It complains because it doesn't exist. No surprise there, you've
already dropped it.

Traceback (most recent call last):

Traceback (most recent call last):
File "<stdin>", line 1, in <module>
psycopg2.InternalError: current transaction is aborted, commands ignored until end of transaction block

Maybe you need to commit the change?

 

[andydtaylor]

I'd actually forgotten about commit, thanks!

I'll have another go with this in mind.

 

[andydtaylor]

I'd actually forgotten about commit, thanks!

I'll have another go with this in mind.

 

[Chris Angelico]

Like many things, it's silent when everything works. As MRAB
suggested, you probably need to commit before the changes become
visible; unlike certain other database engines, PostgreSQL actually
provides transactional integrity for DDL queries (CREATE/DROP TABLE
etc) as well as DML queries (INSERT/UPDATE etc). This is incredibly
handy, but can be surprising if you're accustomed to them being
immediately visible to other sessions.

But next time you ask for help, don't say that you're "having issues"
- say *what* issues you're having! It makes helping you so much easier
if we know up front what you need help with, rather than being forced
to guess

(By the way, does anyone else see irony in there being "craziness" in
a module that's pretty much called "psycho"?)

ChrisA