Sending SQL statement as it is

I found another situation.

I want to use FME to perfom a VACUUM FULL after a set of operations.

But I can’t with FME because there is no transformer that allows me to send SQL command without being in a transaction block.

It could be great to have an advanced option in the SQLExecutor to avoid packaging our SQL into a transaction block an to sending it just as it is.

It could allow us to perform VACUUM.

You can send a VACUUM Statement to PostgreSQL in the “SQL to Run After Write” parameter of a featureWriter.

Another option would be to run the VACUUM within a “Shutdown Python Script”

Hi ​@rlagarde ,

Thanks for the advice but unfortunetly it is impossible in my context. I use SQLExecutor to achieve some updates into the database with complexe functions and procedures.

I cannot use a FeatureWriter as it is not the transformer used at this stage of my translation (there is no need to write or update feature into the workspace into the DB).

I cannot use the Shutdown Python Script because there are other operation after that.

I currently use PythonCaller with psycopg to connect to the db and run the VACCUM FULL which works but is unsatisfiying in my opinion.

Pay attention, VACUUM ANALYZE can be run into a translation but VACUUM FULL not ;-)

You could put it on the initiator output port of your SQLExecutor

Instead of the featureWriter you could even have a FeatureReader connecting to your postgres database and do the VACUUM FULL in the “run before/after“ of a dummy read on any table.

psycopg will work fine in any case.

Also note that VACUUM ANALYZE won’t run in a transaction. A simple ANALYZE will

Yeah, my mistake, VACUUM alwas run outside of a transaction, regardless of the specified options.

That’s another possibility but like the PythonCaller and the FeatureWriter, those are bypasses and I like clean workspaces ;-)

By the way, thanks for spending time on this subject ! I hope Safe will add this little option for managing the transaction encapsulation.

It could be great to start a transaction inside a transformer, commit it inside another and having plenty of SQL request between these two… Many ideas about this subject 😅