Re: how to vacuum thru psycopg

On Fri, Jun 12, 2009 at 10:46 AM, Al Niessner <[email protected]> wrote:
>
> I am using psycopg 2.0.8 and want to vacuum my postgresql database as I
> fill it. I found out that some of what I am seeing performance wise is
> the database needing to optimize for my queries and insertions. So, I
> was told to vacuum it occasionally. So, I tried to do it in my program
> as it grew. The error says it all:
>
>  File
> "/home/niessner/Projects/PictureLibrary/Source/PictureLibrary/Model/DB/PostgresqlWithPsycopg/actions.py", line 247, in transactionClose
>    if len (ids) == 10**int(log10 (len (ids))): self.__execute("vacuum
> full", close=False, query=False)
>  File
> "/home/niessner/Projects/PictureLibrary/Source/PictureLibrary/Model/DB/PostgresqlWithPsycopg/actions.py", line 92, in __execute
>    self.__cursor.execute (command)
> psycopg2.InternalError: VACUUM cannot run inside a transaction block
>
>
> So, if I cannot do it with a cursor, then how do I do it? I have 20000
> large, complex items to add and time is growing linearly and will take
> years to complete at its growth rate. However, if I can vacuum once in a
> while, then I can keep the time down to finish in a week or two.
>
> As always, thanks in advance for any and all help.

You can set the connection to autocommit isolation like so:

from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT
connection.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)

Note that this will roll back any previous transactions, so make sure
you commit any work you want to keep before hand.  You can then
execute statements that need to happen outside of a transaction block
like VACUUM.  Afterwards, you can switch back to read committed or
serializable isolation (whichever you were using).

I can't say I've had a need to manually vacuum in my scripts though.
Usually PostgreSQL's autovacuum keeps things under control.

James.