Running Vacuum query for Redshift in Talend.

[tYrannoSaurusRex_1]

I am working on Amazon Redshift SQL database. When working on this database, I usually run following query,
vacuum full <table_Name>
Now this query runs properly using Workbench. I want to run this query from tRedshiftRow. I put this same query in tRedshiftRow and run and I get following error,
connecting to socket on port 3754
connected
ERROR: VACUUM cannot run inside a transaction block
disconnected
Anybody have idea how to solve this??
Thanks in advance.

[Anonymous]

Hi,
Could you please set commit every in Advanced settings of TalendHelpCenter:tRedshfitRow ="0" to see if it is working?
Best regards
Sabrina

[tYrannoSaurusRex_1]

Hi xdshi,
Thanks for the help, it worked.
Can you provide me description why it happens with vacuum query? I mean I know commit every does is to run commit for 'n' no. of rows inserted.
From my opinion, vacuum does not insert or update any row. But just clears the rows remaining data or entries in table which stay back after removing old entries. Kind of cleaning unwanted rows. But commit every works for insert and update, thus issue with vacuum.

[Anonymous]

Hi,
It is not a bug in talend.
We have faced similar issue in Greenplum https://jira.talendforge.org/browse/TDI-29246 and find a solution from http://www.postgresql.org/message-id/20050506215248.E1711@bacon.
Hope it will be useful for you.
Best regards
Sabrina

[Anonymous]

Sorry,
I have suffered from the same probleam.
But I can not find "commit every in Advanced settings " our talend studio.
My studio is Bigdata 5.6.2
How should i handle it?
Thanks in advance.

[Anonymous]

Hi nishiokya,
Please see my screenshot. Let us know if it is Ok with you.
Best regards
Sabrina

[Anonymous]

Hi Sabrina.
Thank you for your reply .
I can not find "commit every in Advanced settings " yet.
Plaease see my tRedshiftRow screensot.
Thanks in advance.

[Anonymous]

Hi nishiokya,

Can you upload again the screenshots you wanted to show, please? For some reason it didn't make it to your post. I tested it on bigdata 5.6.2, everything is Ok.
Please see my screenshot.

Best regards
Sabrina

[Anonymous]

                                                 Hi Sabrina.
Thank you for your reply .
I'm sorry, I could not upload my screenshot at previous post.
I try again.

Thanks in advance.

[Anonymous]

I know this is an old post but since I had a similar issue I want to say that if you are "reusing a connection" that you lose the option for the "commit every".  You need to make it a built in or repository connection of its own so it can have different values than the shared connection.