Unique key violation on changes.script_hash not handled

[maudrid]

Hi.
Having an issue when I try to deploy and I am able to recreate it on all our environments.
When I run sqitch deploy I get this error in the database:

2022-05-17 07:12:38.045 UTC [172] ERROR:  duplicate key value violates unique constraint "tags_project_tag_key"
2022-05-17 07:12:38.045 UTC [172] DETAIL:  Key (project, tag)=(affectli, @V1.6.0) already exists.
2022-05-17 07:12:38.045 UTC [172] STATEMENT:
                   INSERT INTO tags (
                          tag_id
                        , tag
                        , project
                        , change_id
                        , note
                        , committer_name
                        , committer_email
                        , planned_at
                        , planner_name
                        , planner_email
                   )
                   SELECT tid, tg, proj, chid, n, name, email, at, pname, pemail FROM ( VALUES
               ($1, $2, $3, $4, $5, $6, $7, $8::timestamptz, $9, $10)
                   ) i(tid, tg, proj, chid, n, name, email, at, pname, pemail)
                     LEFT JOIN tags ON i.tid = tags.tag_id
                    WHERE tags.tag_id IS NULL

The steps I followed to create this problem:

1. Have some existing sqitch project
2. Add a new tag
3. Run sqitch deploy without adding any other changes
4. Now do a sqitch rework
5. Then do a sqitch deploy again:

$ sqitch deploy
ERROR:  duplicate key value violates unique constraint "tags_project_tag_key"
DETAIL:  Key (project, tag)=(myProject, @V1.6.0) already exists.

This is how I worked around this problem:

1. After getting this error: sqitch revert @HEAD^

Revert changes to schemas/myProject/publications/iam from db:pg://postgres:@localhost:15432/myProject? [Yes]
  - schemas/myProject/tables/myTable @V1.6.0 .. ok

2. Then Deploy again:

$ sqitch deploy
Deploying changes to db:pg://postgres:@localhost:15432/myProject
  + schemas/myProject/tables/myTable @V1.6.0 .. ok
  + schemas/myProject/tables/myTable .......... ok

[theory]

Odd, I am not able to replicate this issue. I

1. Deployed a plan
2. Added a tag
3. deployed again (it says everything is up-to-date, but adds a tag record)
4. Reworked the most recent change
5. Deployed again and it deployed successfully

Out of curiosity, I diffed a dump of a database where the tag was deployed at the same time as the last change, and another where it was deployed after the last change, as in your example. The only difference was that the sqitch.events record for that last change did not have the tag. This is purely informational and for display, and clearly not related to the issue you're seeing.

Would you mind sharing an example of

1. A plan without the tag
2. The plan with the tag added
3. The plan with the reworked change

[maudrid]

Hi. Please see this project on how to recreate the issue:
https://github.com/maudrid/sqitch-bug-630
Read the code and then execute the test.sh to see the problem reproduced.

[theory]

Thanks for that. This is because there is no change to the deploy script. It works with this change:

--- a/test.sh
+++ b/test.sh
@@ -7,4 +7,5 @@ docker exec -ti -u999 sqitch-test bash -c 'cd /tmp/s630/ && sqitch tag myTag -m
 docker exec -ti -u999 sqitch-test bash -c 'cd /tmp/s630/ && sqitch deploy'
 docker exec -ti -u999 sqitch-test bash -c 'cd /tmp/s630/ && sqitch status'
 docker exec -ti -u999 sqitch-test bash -c 'cd /tmp/s630/ && sqitch rework test1 -m "reworking"'
+docker exec -ti -u999 sqitch-test bash -c 'cd /tmp/s630/ && echo >> deploy/test1.sql'
 docker exec -ti -u999 sqitch-test bash -c 'cd /tmp/s630/ && sqitch deploy'

This is on the assumption that you will change the deploy script — because otherwise why rework it?

[theory]

Mean to say thanks a million for taking the time to build such a terrific test case. Made it easy for me to replicate and see what was going on --- even if I did first go down a rabbit hole to install v1.3.1 to make sure the issue remained (I think the apt repo installs v1.1.0).

[maudrid]

So just for me to understand, the issue happens because the hash of the file did not change?
The new file is automatically created, but since the contents is the same, it is giving an issue?

[theory]

Right, it creates the script as a copy of the old one, but expects you to modify it — otherwise why bother reworking it if you don't rework it? :-)

[maudrid]

Hi. I found another case that triggers the same error message:

1. Create a plan that contains a tag.
2. Deploy all changes
3. Revert changes until just before the tag (so also revert the tag)
4. Change the commit message of the tag
5. Try to deploy all changes.
   Result is the same error message.
   If I then change the commit message back to the original commit message, I can deploy the changes again.
   Also, if I revert to the change that is two lines up from the tag, then the tag is actually reverted. Is this by design?

[theory]

I'm not able to replicate that issue, either :-( Here's what I did:

sqitch init s630 --engine pg --target postgres
sqitch add test1 -m "make a new version"
add test2 -m "make another change version"
sqitch deploy db:pg:try
sqitch status  db:pg:try
sqitch revert --to test1 db:pg:try
# edit sqitch.conf, append ` (edited)` to the `@myTag` line
sqitch deploy db:pg:try

[theory]

Re-opening, because I now realize that the raw SQL error is a bug. The code needs to catch unique key violations and return a more meaningful error.

[maudrid]

I will try to recreate this using the project submitted before, but I need a bit of time.