A partial unique index broke our PR previews for four months: Postgres ON CONFLICT and error 42P10

From May 12 to September 24, 2026, opening a pull request on a repository connected to PreviewDrop did not produce a preview. Previews for manual deploys and default-branch pushes kept working. Previews for pull requests, which are the main thing PreviewDrop is for, failed every time.

The cause was one statement, and this is what Postgres said about it:

ERROR:  42P10: there is no unique or exclusion constraint matching the ON CONFLICT specification

If you landed here by searching for that error, the short answer is in the next section. After that we explain why this one bug did more damage than a single failed request, and how we missed it for four months.

The bug

When a PR event arrives, we record which deployment belongs to which pull request. A PR that gets ten pushes should keep one row, updated in place, so the write is an upsert:

INSERT INTO pd_deployment_meta (id, tenant_id, deployment_id, repo_full_name, pr_number, ...)
VALUES (...)
ON CONFLICT (tenant_id, repo_full_name, pr_number) DO UPDATE SET
  deployment_id = EXCLUDED.deployment_id,
  commit_sha    = EXCLUDED.commit_sha;

The unique index on those three columns looks like this:

CREATE UNIQUE INDEX idx_deployments_active_pr
  ON pd_deployment_meta (tenant_id, repo_full_name, pr_number)
  WHERE pr_number IS NOT NULL;

It is a partial index: it only covers rows where pr_number is set, because the same table also holds deployments that have nothing to do with a PR.

Postgres picks the index an ON CONFLICT should use (the "arbiter") by inference. It looks for a unique index whose columns match the conflict target. For a partial index, the columns are not enough. The statement must also imply the index's predicate. If you don't repeat the WHERE, Postgres will not assume that your conflict target lines up with a partial index. It finds no arbiter and raises 42P10 before touching a single row.

Here is a minimal reproduction on PostgreSQL 16:

CREATE TEMP TABLE pr_meta (id serial PRIMARY KEY, repo text NOT NULL, pr_number int, deployment_id text);
CREATE UNIQUE INDEX pr_meta_active ON pr_meta (repo, pr_number) WHERE pr_number IS NOT NULL;

INSERT INTO pr_meta (repo, pr_number, deployment_id) VALUES ('acme/app', 42, 'dep-1')
ON CONFLICT (repo, pr_number) DO UPDATE SET deployment_id = EXCLUDED.deployment_id;
-- ERROR:  42P10: there is no unique or exclusion constraint matching the ON CONFLICT specification

INSERT INTO pr_meta (repo, pr_number, deployment_id) VALUES ('acme/app', 42, 'dep-1')
ON CONFLICT (repo, pr_number) WHERE pr_number IS NOT NULL DO UPDATE SET deployment_id = EXCLUDED.deployment_id;
-- INSERT 0 1

INSERT INTO pr_meta (repo, pr_number, deployment_id) VALUES ('acme/app', 42, 'dep-2')
ON CONFLICT (repo, pr_number) WHERE pr_number IS NOT NULL DO UPDATE SET deployment_id = EXCLUDED.deployment_id;
-- INSERT 0 1   (the existing row now has deployment_id = 'dep-2')

The fix is the WHERE pr_number IS NOT NULL between the conflict target and DO UPDATE. It is one line.

How it got in

On May 12 we moved PreviewDrop's database access from a hosted client library to plain SQL. The old code called the library's upsert() helper, which resolved conflicts on the primary key, so the partial index was never the arbiter and the question never came up. The rewrite spelled out the conflict target by hand, named the right three columns, and left out the predicate.

Nothing about that statement looks wrong in a code review. The columns match the index. You have to know that partial indexes are handled differently to see it.

Why one failed statement stopped every later PR

A single failed webhook would have been bad. This one was worse because of where the statement sat. A PR event did three things, in this order:

1. insert a queued deployment row,
2. run the upsert above,
3. hand the build to a worker.

Step 2 threw, so step 3 never ran. That left a deployment marked queued that nothing would ever build. Queued rows count toward a workspace's concurrent-preview limit. The next push to the PR added another one, and after a few pushes the workspace was at its limit. From then on, every new PR event was rejected with "concurrent limit reached" before it even reached the broken statement. A cleanup job eventually marked the stuck rows as failed, hours later, and the cycle started over.

So one user who opened a pull request and kept pushing to it did not see "an error" 216 times. For about a day and a half they saw nothing: no preview, no useful PR comment.

How we missed it

We did not miss the error because it was quiet. It was loud in the wrong place.

• Our tests mock the database. A mock accepts any SQL string you hand it, so a statement that Postgres rejects at planning time passes every unit test. Nothing in the suite ever ran this statement against a real Postgres, so the suite stayed green the whole time.
• The stack trace went to the container's stdout. Our dashboard reads a structured event log. The exception was thrown past the code that writes to that log, so the admin views showed webhooks received and nothing after them.
• GitHub knew. Every one of those deliveries got an HTTP 500, and the App's delivery log showed them in red. Nobody was looking at it.

We found it while investigating something unrelated: a spike in site traffic that turned out to be a bot farm. Separating real accounts from the bots meant reading their event histories, and one of them led to a PR with hundreds of events and no deployments.

What we changed

• One upsert for all three providers. GitHub, GitLab and Bitbucket each had their own copy of the statement, all with the same omission. They now call one function, and it includes the predicate.
• A failure can no longer leave a stuck row. If linking the deployment to its PR fails for any reason, that function marks the deployment failed with a reason and the webhook stops before calling the worker. A broken step now costs one failed deployment, not the workspace's whole concurrent-preview limit.
• Verified against real Postgres, not a mock. We did add a unit test that asserts the predicate is in the SQL, but that test only checks text. The proof was a real pull request on production: opened, built, preview live and the PR comment posted; a second push updated the same row and replaced the container; closing the PR tore it down.
• Deleting an account now uninstalls the GitHub App. The user above deleted their account. Their App installation kept sending us events for another day and a half, because deleting our record of an installation does not remove it from GitHub. Now it does.

Check your own codebase

This bug fits in one query plus one grep. List every partial unique index:

SELECT indexrelid::regclass AS index,
       pg_get_expr(indpred, indrelid) AS predicate
FROM pg_index
WHERE indisunique AND indpred IS NOT NULL;

Then look at every ON CONFLICT in your code:

grep -rn "ON CONFLICT (" src/

For each conflict target that matches a partial index's columns, the statement needs that index's WHERE clause. While you have the list open, also check that every conflict target has some unique index behind it. Running this check on our own code turned up a second 42P10, in an admin screen whose upsert targeted a column that had no unique index at all. That one is fixed too.

The general lesson is older than this bug: a test suite that never talks to the real database cannot tell you whether your SQL is valid. At minimum, run each write path once against a real Postgres before trusting it.

If you tried PreviewDrop for PR previews and gave up

That was probably us, not you. PR previews work again as of September 24. Connect a repository in the quickstart and open a pull request, and the preview URL appears as a PR comment when the build finishes. Sign up if you want to try again.