PostGIS vaccum and analyze.

Can you see the SQL on the Postgresql side by examining running queries while running the workspace @itay​ 

Hi @Sigbjørn Herstad​ thanks for the tip, still seeing only VACUUM ANALYZE so I guess this is just plain vaccum.

Hi @itay​, I believe you are correct. The vacuum option in the PostGIS writer is a VACUUM ANALYZE.

Hi @danminneyatsaf​, Thanks for confirming this. I have been trying to use the vacuun freeze analyze option via an SQL statement but the database keep complaining that it cannot be carried out in a transaction.

So I tried using:

begin;

VACUUM FREEZE ANALYZE my_table;

end;

But still no joy and ideas on that?

@itay​ I see some documentation mentioning that Postgresql 9.0+ should be formatted as: VACUUM(FREEZE) [tablename]. This may or may not help for your scenario but I just wanted to put that out there.

Additionally, are you able to share the message you are getting back from the database when trying to carry out the transaction?

Yes the message is: ERROR: VACUUM cannot run inside a transaction block

For the following SQL (with or without parenthesis) :

FME_SQL_DELIMITER ;

BEGIN;

VACUUM FREEZE ANALYZE srv_demo.ns_bijlagen;

COMMIT;

END;

If I run:

COMMIT;

VACUUM FREEZE ANALYZE srv_demo.ns_bijlagen;

via DBeaver it works just fine.

Trying it via de SQLExecutor results in the following error:

Hi @danminneyatsaf​ ,

Thanks for the tip, I have added the SQL statement in the SQL to Run After Writing in the Feature Writers I am using. Not much sense doing it on a table without data in it :)

Problem solved👍

Just to let people know that I created an idée to avoid using a transaction block in the SQLExecutor :

• Sending SQL statement as it is Open
• 7 Votes