Something wrong with pool. Got "Connection is already closed None None" with copy_from and big file.

[sector119]

I use sqlalchemy 0.6 and psycopg2 2.0.13.

When I try to copy from some little file it's ok, when from some
bigger one I got error and log posted below.
When I use pgbouncer and pass poolclass=pool.NullPool to create_engine
everything wor perfect with large file too.

My query is:

session.connection().connection.cursor().cursor.copy_from(f, table,
sep=chr(int(conf['import.separator'])), columns=map(str, fields.split
(',')))

Log:

2009-11-04 18:43:30 EET LOG: statement: COPY people
(id,street_id,building,corpus,appartment,block,last_name,hd,residents,space,rooms,privilege,comment)
FROM stdin WITH DELIMITER AS E' '
2009-11-04 18:44:08 EET LOG: could not receive data from client:
Connection reset by peer
2009-11-04 18:44:08 EET CONTEXT: COPY people, line 6146
2009-11-04 18:44:08 EET STATEMENT: COPY people
(id,street_id,building,corpus,appartment,block,last_name,hd,residents,space,rooms,privilege,comment)
FROM stdin WITH DELIMITER AS E''
2009-11-04 18:44:08 EET LOG: incomplete message from client
2009-11-04 18:44:08 EET CONTEXT: COPY people, line 6146
2009-11-04 18:44:08 EET STATEMENT: COPY people
(id,street_id,building,corpus,appartment,block,last_name,hd,residents,space,rooms,privilege,comment)
FROM stdin WITH DELIMITER AS E''
2009-11-04 18:44:08 EET ERROR: unexpected EOF on client connection
2009-11-04 18:44:08 EET CONTEXT: COPY people, line 6146
2009-11-04 18:44:08 EET STATEMENT: COPY people
(id,street_id,building,corpus,appartment,block,last_name,hd,residents,space,rooms,privilege,comment)
FROM stdin WITH DELIMITER AS E''
2009-11-04 18:44:08 EET LOG: could not send data to client: Broken
pipe
2009-11-04 18:44:08 EET LOG: could not receive data from client:
Connection reset by peer
2009-11-04 18:44:08 EET LOG: unexpected EOF on client connection
2009-11-04 18:44:08 EET LOG: disconnection: session time: 0:00:44.763
user=eps database=eps host=localhost port=44257

[Michael Bayer]

sector119 wrote:
>
> I use sqlalchemy 0.6 and psycopg2 2.0.13.
>
> When I try to copy from some little file it's ok, when from some
> bigger one I got error and log posted below.
> When I use pgbouncer and pass poolclass=pool.NullPool to create_engine
> everything wor perfect with large file too.
>
> My query is:
>
> session.connection().connection.cursor().cursor.copy_from(f, table,
> sep=chr(int(conf['import.separator'])), columns=map(str, fields.split
> (',')))

copy_from() probably creates some state that is not compatible with the
connection being used afterwards for subsequent operations, or
alternatively copy_from() is not compatible with some previous state.
The pool does nothing special to the connections which it stores except
calling rollback() when they are returned.

If you can try to isolate the issue to an exact sequence of events (i.e.,
don't use a Session or ORM - just use an engine and connect()) that would
reveal more about what's going on.

[sector119]

On Nov 4, 7:13 pm, "Michael Bayer" <mike...@zzzcomputing.com> wrote:
> copy_from() probably creates some state that is not compatible with the
> connection being used afterwards for subsequent operations, or
> alternatively copy_from() is not compatible with some previous state.  
> The pool does nothing special to the connections which it stores except
> calling rollback() when they are returned.
>
> If you can try to isolate the issue to an exact sequence of events (i.e.,
> don't use a Session or ORM - just use an engine and connect()) that would
> reveal more about what's going on.

Now I try copy_from without Session or ORM, use engine only and
everything is ok :) What does it mean? :)

engine = create_engine(conf['sqlalchemy.url'])
connection = engine.raw_connection()
connection.cursor().cursor.copy_from(f, i.table, sep=chr(int(conf
['import.separator'])), columns=map(str, i.fields.split(',')))

[Michael Bayer]

do some addtional things on the connection ? commit/rollback etc. ? bind it to a Session and do some stuff ? not sure.



>
>
>
> --
>
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=.
>
>

[Michael Bayer]

On Nov 21, 2009, at 8:51 AM, sector119 wrote:

>
>

if you want a connection from the pool, and then just throw it away afterwards, call detach() on it. then you can do whatever weird things with it and it will be thrown away when you close() it (i.e. not returned to the pool or anything).