Don't Have Flyway Teams? Roll Your Own Version of Output Query Results
Published: 2021-02-11 • Last updated: 2021-05-03
Flyway is a popular database migration tool. They offer two versions, a Free edition, and a Teams edition.
I was using the Free edition on a project. When creating the migration files, I did a
pg_dump of an existing database. This worked out fine and dandy, except for one thing…
pg_dump extracts data, it generates
INSERT statements with the
OVERRIDING SYSTEM VALUE clause, which allows explicit values into an identity column (as opposed to letting the column auto-increment).
This means statements like
SELECT pg_catalog.setval('foo', 42, false) are then needed to set the identity column sequence to its correct value.
SELECT statements were outputting query results, and making the logs quite noisy. Turns out, there’s a configuration parameter in the Teams edition called
outputQueryResults. Setting this parameter to
false would have solved the issue. But I was on the Free edition, so I had to get creative.
The solution was to create a temporary table from the
SELECT statement, then immediately drop it, so that the pattern could be repeated.
This will output query results:
SELECT pg_catalog.setval('foo', 42, false);
And this will not:
CREATE TEMP TABLE temp_silent AS SELECT pg_catalog.setval('foo', 42, false); DROP TABLE IF EXISTS temp_silent;