sqitch check reveals divergence after fresh database deploy, sha1 hashes match

[the-noob-of-northrose]

Good afternoon,

My team has a sqitch plan with, at the time of writing, 542 changes.

A colleague edited a recent change during development, and as I have been doing for a while now, I pulled the changes down and ran sqitch rebase --modified. However, I noticed that it wanted to revert to a change waaaay further back, one that hasn't been modified in many months, if ever.

After confirming the divergence with sqitch check, I calculated the sha1 hash (using my OS's built-in sha1sum tool) of the deploy script it claims to diverge at, as well as the hashes of the scripts for the previous and next changes, and compared these to what is in the sqitch.changes table:

They match (wtf?)

I deleted the database and did a fresh sqitch deploy. sqitch check told me that there was a divergence at a different change (...wtf??)

I deleted and redeployed again, several times, in fact, and sqitch check reported divergences at different changes each time (WTF???)

I had not manually edited any deploy files during this testing, nor did I pull any new changes from our git repo.

This issue has apparently not been affecting my coworkers. I am so, so confused. I'm not sure if this is a sqitch bug or if I just don't understand how check actually works and I'm therefore not barking up the right tree for troubleshooting.

System info:

• My OS: Windows 10, Ubuntu 22.04 LTS and Debian 12 (Bookworm), both through WSL.
• postgres server Docker image: postgres:14-bullseye
• sqitch version: 1.3.1 on Debian, 1.2.1 on Ubuntu, same results with each.

What I have tried:

• Uninstalling/reinstalling sqitch on my primary WSL distribution (Ubuntu)
• Deleting and recomposing my postgres Docker container and its storage volume
• Using a different WSL distribution (Debian) to clone our repo and do the sqitch deploy/check
• Politely asking my computer to do what I want it to do
• Swearing loudly at my computer

[the-noob-of-northrose]

I noticed that sqitch checks (even after deploys) are not failing on databases on our production/test servers, so I guess it's somehow related to my local dev environment, but I'm at a loss as to what could be going on or how to fix it.

[theory]

My team has a sqitch plan with, at the time of writing, 542 changes.

Damn.

I deleted and redeployed again, several times, in fact, and sqitch check reported divergences at different changes each time (WTF???)

WTF indeed.

This issue has apparently not been affecting my coworkers.

That's the good news. Did you try a different computer by chance?

sqitch version: 1.3.1 on Debian, 1.2.1 on Ubuntu, same results with each.

That's a few years old. Looking through the changes, the closest match I can find is #630, but that's about unique violations in the database. The only other wildcard I can think of is if you have cloned your repo of Sqitch changes on Windows and line endings are different, but that really shouldn't matter, honestly.

Can you try a newer version?

I noticed that sqitch checks (even after deploys) are not failing on databases on our production/test servers, so I guess it's somehow related to my local dev environment, but I'm at a loss as to what could be going on or how to fix it.

Yeah, this is pretty weird!

[the-noob-of-northrose]

Confirmed that updating committed_at in sqitch.changes so things are in plan-order results in a successful check 👍

[theory]

This is very odd. The value of committed_at defaults to clock_timestamp(), here. And when Sqitch [inserts into the changes table], it does not override that default.

From the docs:

clock_timestamp() returns the actual current time, and therefore its value changes even within a single SQL command.

It also uses microsecond resolution by default:

david=# select clock_timestamp();
        clock_timestamp        
-------------------------------
 2025-01-22 18:29:51.004126+00
(1 row)

It should be impossible for two scripts to finish in the same microsecond; Sqitch and DML just isn't that fast. You're not using some sort of parallel load, I assume (Sqitch doesn't offer it), and haven't done anything funky to lower the precision of clock_timestamp(), right?

What is the value of committed_at in the impacted rows?

[the-noob-of-northrose]

No parallel stuff that we're aware of—I asked our db guy, who is just as perplexed about this situation.

(FYI, this is a fresh deploy so the data presented here doesn't match that on my previous comments.)

clock_timestamp() returns 2025-01-23 14:01:28.713269+00

The changes table has the same precision.

tl;dr direct answer to your final question:

6586... 2025-01-22 19:52:44.475037+00 (Last change before things get weird)
f49a... 2025-01-22 19:52:44.540459+00 (The out-of-place change)
3e57... 2025-01-22 19:52:44.551137+00 (Back to normal...)
daf5... 2025-01-22 19:52:44.612815+00
2763... 2025-01-22 19:52:44.678552+00
[This is where the out-of-place change f49a... SHOULD be, according to the plan]
edec... 2025-01-22 19:52:44.694312+00
e375... 2025-01-22 19:52:44.865811+00

---

More data...

sqitch check results:

The order of things in my sqitch.plan:

So everything went fine for the first 16 changes, but then somehow, the change with script_hash f49af... (which comes few changes later) got committed next, before the rest of the changes got committed in plan-order.

Unsure how relevant it is, but the fresh deploy I did before this one got through about 120 changes before something weird happened. The one before that (the data included in my previous comment) got through 299.

EDIT: I manually adjusted that committed_at timestamp for script_hash f49a... and re-ran the check. Turns out it actually happened a few more times in this particular deploy.

[the-noob-of-northrose]

Just for kicks, I initialized a new sqitch project, put in 500 changes (without doing anything to the auto-generated scripts), deployed, and this problem still happens for me. So that rules out issues with our product's scripts, at least...

[theory]

tl;dr direct answer to your final question:

This is handy. The committed_at resolution is as it should be. It looks like the plan is applied out-of-order. What's the output of the deploy for that bit? Does it in fact deploy them out of order?