SQL exception for an invalid query is thrown during pact broker data purge
sosnowst opened this issue · 4 comments
Introduction
I'm facing issue with the broker data purge feature, trying to apply given version selector:
"[{\"max_age\": 15}, {\"max_age\": 30, \"branch\": \"main\"}]"
but as a result in broker logs I got: ERROR: each UNION query must have the same number of columns.
I used tags before and this selector worked fine, it looks like it stopped after changing to branches.
I have already:
- Upgraded to the latest Pact Broker
- Checked the CHANGELOG to see if the issue I am about to raise has been fixed
Expected behaviour
Data that is not included in the selector will be cleared
Actual behaviour
SQL exception for an invalid query is thrown and the data is not cleared:
ERROR: each UNION query must have the same number of columns
Steps to reproduce
- Enable pact broker data purge
- Add one of the below selectors
"[{\"max_age\": 15}, {\"max_age\": 30, \"branch\": \"main\"}]"
"[{\"branch\": \"main\", \"latest\": true}, {\"max_age\": 15}]"
Relevant log files
selector logs:
pactdbcleanup-pact-broker-1 | 2022-11-08 09:51:00.718470 I [23:4420] pact-broker -- Connected to database pack_broker pactdbcleanup-pact-broker-1 | 2022-11-08 09:51:00.722276 I [23:4420] pact-broker -- Deleting oldest 500 versions, keeping versions that match the configured selectors -- [#<PactBroker::DB::Clean::Selector:0x00007efc058d21a8 @source_hash={"max_age"=>15}, @max_age=15>, #<PactBroker::DB::Clean::Selector:0x00007efc058d0c90 @source_hash={"max_age"=>30, "branch"=>"main"}, @branch="main", @max_age=30>, #<PactBroker::DB::Clean::Selector:0x00007efc058726e0 @source_hash=nil, @deployed=true>, #<PactBroker::DB::Clean::Selector:0x00007efc05872618 @source_hash=nil, @released=true>]
sql query
2022-11-08 09:51:01.099 UTC [31] STATEMENT: SELECT * FROM "versions" WHERE ("id" NOT IN (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT "id" FROM "versions" WHERE ("versions"."created_at" >= '2022-10-24') UNION (SELECT "id", "branch_name" FROM "versions" INNER JOIN (SELECT "version_id", "branch_name" FROM "branch_versions" WHERE ("branch_id" IN (SELECT "id" FROM "branches" WHERE ("name" = 'main')))) AS "bv" ON ("versions"."id" = "bv"."version_id") WHERE ("versions"."created_at" >= '2022-10-09'))) AS "t1" UNION (SELECT "id" FROM "versions" WHERE ("id" IN (SELECT "version_id" FROM "deployed_versions" WHERE ("id" IN (SELECT "deployed_version_id" FROM "currently_deployed_version_ids")))))) AS "t1" UNION (SELECT "id" FROM "versions" WHERE ("id" IN (SELECT "version_id" FROM "released_versions" WHERE ("support_ended_at" IS NULL))))) AS "t1")) ORDER BY "id" ASC LIMIT 500
error:
2022-11-08 09:51:01.099 UTC [31] ERROR: each UNION query must have the same number of columns at character 181
Thanks Tomasz. Which version are you running?
When it comes to pact-broker, the newest one:
image: pactfoundation/pact-broker:latest
Apologies that it has taken this long to fix.