CODEWITHSUNDEEP
sqlpostgresqlliquibase
Antony Vimal Raj
Antony Vimal Raj

Reputation: 384

Large query execution in liquibase

I am trying to update a column in one of my tables through an update query something like the below:

update table_name set field='some jsonb operation()';

This table has 300k+ rows. I need to update all the rows. I have added a changeset in liquibase with this update query. But when I try to execute, I am getting connection timeout error because the update operation takes 2-3 minutes. This is a onetime activity. How can I effectively execute the query without timeout error. I thought of splitting the data and apply the update. Will it resolve my issue? or any other better way to do this?

Upvotes: 0

Views: 1605

Answers (1)

SteveDonie
SteveDonie

Reputation: 9016

I am not a postgresql expert, but if you are doing this with a <sql> or <sqlFile> change type, it appears that you could prepend the actual query with SET statement_timeout = 600000 to set the statement timeout for that session. The timeout is in milliseconds, so the example given would be for 10 minutes.

I think the changeset should look something like this:

<changeSet id="update-the-things" author="antony">
  <sql splitStatements="true" endDelimiter=";">
    SET statement_timeout = 600000;
    update table_name set field='some jsonb operation()';
  </sql>
</changeSet>

Upvotes: 1

Related Questions