+18Hi there.
I was wondering which type of parameter is used when enableing vaccum analyze tabel in a Postgis writer feature type (https://www.postgresql.org/docs/current/sql-vacuum.html) . Is it a FULL vacuum? all I see in the logfile is: Vacuuming and analyzing table ....
Any tips on how to query that information from the database?
Thanks in advance.
Itay
Best answer by danminneyatsaf
Hi @Sigbjørn Herstad thanks for the tip, still seeing only VACUUM ANALYZE so I guess this is just plain vaccum.
I was able to run the SQL in "SQL to Run Before Read" on the Reader. However I know you're trying to use this on the writer, so I'm not sure if this works for your scenario. Either way let me know if this helps at all. 😀
Additionally, I was curious which version of Postgresql you are running? This probably isn't causing the issue but is useful to know anyways.
+25Can you see the SQL on the Postgresql side by examining running queries while running the workspace @itay
+18Can 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.
+13Hi @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.
+18Hi @Sigbjørn Herstad thanks for the tip, still seeing only VACUUM ANALYZE so I guess this is just plain vaccum.
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?
+13Hi @Sigbjørn Herstad thanks for the tip, still seeing only VACUUM ANALYZE so I guess this is just plain vaccum.
@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?
+18Hi @Sigbjørn Herstad thanks for the tip, still seeing only VACUUM ANALYZE so I guess this is just plain vaccum.
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:
+13Hi @Sigbjørn Herstad thanks for the tip, still seeing only VACUUM ANALYZE so I guess this is just plain vaccum.
I was able to run the SQL in "SQL to Run Before Read" on the Reader. However I know you're trying to use this on the writer, so I'm not sure if this works for your scenario. Either way let me know if this helps at all. 😀
Additionally, I was curious which version of Postgresql you are running? This probably isn't causing the issue but is useful to know anyways.
+18Hi @Sigbjørn Herstad thanks for the tip, still seeing only VACUUM ANALYZE so I guess this is just plain vaccum.
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👍
+18Just to let people know that I created an idée to avoid using a transaction block in the SQLExecutor :
