Pg-promise: VACUUM ANALYZE?

[TimJMartin]

Is it possible to get pg-promise to kick off maintenance queries like VACUUM FULL and VACUUM ANALYZE?

[vitaly-t]

This forum is primarily for reporting issues.

Have you tried to execute those queries? Was there any issue executing them?

[TimJMartin]

sorry @vitaly-t I should have included my error

When I have tried to run VACUUM ANALYZE from a pgAdmin SQL window I get

VACUUM cannot be executed from a function or multi-command string

So I wanted to be able to run a VACUUM ANALYZE command from my data loading script.

This is the error when I try to use

db.tx(t => {
        // this.ctx = transaction config + state context;
        return t.batch([
            t.none('VACUUM ANALYZE data1'),
            t.none('VACUUM ANALYZE data2')
        ]);
    })

ERROR: BatchError {
    stat: { total: 2, succeeded: 0, failed: 2, duration: 15 }
    errors: [
        0: { error: VACUUM cannot run inside a transaction block
            at Connection.parseE (E:\Projects\Temp\promise-test\node_modules\pg\lib\connection.js:539:11)
            at Connection.parseMessage (E:\Projects\Temp\promise-test\node_modules\pg\lib\connection.js:366:17)
            at Socket.<anonymous> (E:\Projects\Temp\promise-test\node_modules\pg\lib\connection.js:105:22)
            at emitOne (events.js:96:13)
            at Socket.emit (events.js:188:7)
            at readableAddChunk (_stream_readable.js:176:18)
            at Socket.Readable.push (_stream_readable.js:134:10)
            at TCP.onread (net.js:547:20)
          name: 'error',
          length: 111,
          severity: 'ERROR',
          code: '25001',
          detail: undefined,
          hint: undefined,
          position: undefined,
          internalPosition: undefined,
          internalQuery: undefined,
          where: undefined,
          schema: undefined,
          table: undefined,
          column: undefined,
          dataType: undefined,
          constraint: undefined,
          file: 'xact.c',
          line: '3156',
          routine: 'PreventTransactionChain' }
        1: { error: current transaction is aborted, commands ignored until end of transaction block
            at Connection.parseE (E:\Projects\Temp\promise-test\node_modules\pg\lib\connection.js:539:11)
            at Connection.parseMessage (E:\Projects\Temp\promise-test\node_modules\pg\lib\connection.js:366:17)
            at Socket.<anonymous> (E:\Projects\Temp\promise-test\node_modules\pg\lib\connection.js:105:22)
            at emitOne (events.js:96:13)
            at Socket.emit (events.js:188:7)
            at readableAddChunk (_stream_readable.js:176:18)
            at Socket.Readable.push (_stream_readable.js:134:10)
            at TCP.onread (net.js:547:20)
          name: 'error',
          length: 143,
          severity: 'ERROR',
          code: '25P02',
          detail: undefined,
          hint: undefined,
          position: undefined,
          internalPosition: undefined,
          internalQuery: undefined,
          where: undefined,
          schema: undefined,
          table: undefined,
          column: undefined,
          dataType: undefined,
          constraint: undefined,
          file: 'postgres.c',
          line: '994',
          routine: 'exec_simple_query' }
    ]
}

[vitaly-t]

As per the API, you are only supposed to use a transaction to enforce consistency when changing the data.

VACUMM doesn't change data in your database, plus the operation is irreversible, i.e. you cannot apply ROLLBACK to it in case of failure.

Simply switch from the tx method to task, and that is all.

[TimJMartin]

Is there a different way to run a query like VACUUM ANALYZE?

In pycopg2 you can change the connection to a different isolation level to be able to run that type of query:

old_level = connection.isolation_level
connection.set_isolation_level(0)
vacuum = "VACUUM ANALYZE data1"
cursor.execute(vacuum)
connection.set_isolation_level(old_level)

So I just wondered if there was a similar approach for pg-promise I had not seen in the docs

[vitaly-t]

In pg-promise, isolation level can only be set for a transaction, see Configurable Transactions. I don't think this will help you though with this situation. Simply follow what I wrote just above, as that is all that's needed.

[TimJMartin]

I think our messages crossed so I didn't see you advice to switch to task.

Yes just changed the db.tx to db.task and the SQL ran successfully.

Thanks very much for your help.