Mailing List Archive: Working with ZPsycopgDA (or any DA)

Working with ZPsycopgDA (or any DA) - commiting

Aug 11, 2006, 7:56 AM

Post #1 of 3 (2503 views)

Hi :-)

I'm working with a Postgresql database which I have to insert a lot of data to... I'm using the ZPsycopgDA database adapter, and ZSQL methods.

I have noticed that though I am calling the zsql method no data gets really commited to the database. It all gets entered to database at the very end.

This behavior seems reasonable for keeping database consistent, but in my situation it is really not so good - I have couple houndreds megabytes of data and as I believe it all gets stored in memory before being sent to the database causing serious system lags. I could be wrong though - this is only my observation... What could be another reason for slooow inserts?

Either way, it would be far better for me if every "INSERT" would be commited immediatelly or better yet if I could force a "commit" within my code. Is there some way I can do it?

Please excuse my poor English :-)

Best regards,
Maciej

_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Re: Working with ZPsycopgDA (or any DA) - commiting [ In reply to ]

lists at zopyx

Aug 11, 2006, 8:17 AM

Post #2 of 3 (2382 views)

Permalink

--On 11. August 2006 16:56:59 +0000 krokodylek@tenbit.pl wrote:

>
> Hi :-)
>
> I'm working with a Postgresql database which I have to insert a lot of
> data to... I'm using the ZPsycopgDA database adapter, and ZSQL methods.
>
> I have noticed that though I am calling the zsql method no data gets
> really commited to the database. It all gets entered to database at the
> very end.

The DA is usually tied to the transaction system of Zope. So this behaviour
is totally perfect and usually highly desirable.

> This behavior seems reasonable for keeping database consistent, but in my
> situation it is really not so good - I have couple houndreds megabytes of
> data and as I believe it all gets stored in memory

ZSQL methods are executed at the time they are called and immediately
executed on the server. THat does not mean that the every ZSQL method
causes
an automatic commit after execution (see above).

>before being sent to
> the database causing serious system lags.

You might google for "postgres autocommit mode".

> I could be wrong though - this
> is only my observation... What could be another reason for slooow inserts?

What means slow inserts? Depending on the isolation level, changes are
visible to other apps only after a commit..that's how databases usually
work.
>
> Either way, it would be far better for me if every "INSERT" would be
> commited immediatelly or better yet if I could force a "commit" within my
> code. Is there some way I can do it?
>

See above. You can configure autocommit mode either on the server side or
on the client side with psycopg (check the autocommit() method of the
connection object as returned through psycopg.connect()). You might extend
the ZPsycopgDA sources in order to create a DB connection either with or
without autocommit mode.

-aj

Re: Working with ZPsycopgDA (or any DA) - commiting [ In reply to ]

charlie at egenix

Aug 11, 2006, 8:52 AM

Post #3 of 3 (2378 views)

Permalink

Am 11.08.2006, 16:56 Uhr, schrieb <krokodylek@tenbit.pl>:

> Hi
> I'm working with a Postgresql database which I have to insert a lot of
> data to... I'm using the ZPsycopgDA database adapter, and ZSQL methods.
> I have noticed that though I am calling the zsql method no data gets
> really commited to the database. It all gets entered to database at the
> very end.
> This behavior seems reasonable for keeping database consistent, but in
> my situation it is really not so good - I have couple houndreds
> megabytes of data and as I believe it all gets stored in memory before
> being sent to the database causing serious system lags. I could be wrong
> though - this is only my observation... What could be another reason for
> slooow inserts?
> Either way, it would be far better for me if every "INSERT" would be
> commited immediatelly or better yet if I could force a "commit" within
> my code. Is there some way I can do it?

Maciej,

if you want transactional integrity then be prepared to pay the price: do
not adjust Zope's transaction settings. As Andreas says you can make some
adjustments on the server to speed up the process but please note that
Zope is infamously slow for SQL statements although this usually does not
play an important part. If you are doing a one off import then you are
better working directly with Python. If you do need to work within Zope
then note that using bound parameters should give you an additional
performance boost over anything you can set on the server.

Charlie
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db