In PostgreSQL, CREATE INDEX CONCURRENTLY lets you build an index while writes continue. If the build fails during a table scan, for example because of a deadlock or unique violation, PostgreSQL can leave behind an invalid index that queries cannot use. Even after a successful build, a transaction that predates it may still be unable to use the new index.
We treat the validity of a concurrently built index as a release decision. The index must be marked valid in pg_index and cover all eligible rows. For a partial index, eligibility is defined by its predicate. A disposable PostgreSQL 18.x fixture uses synthetic data to compare unique and non-unique builds, exercise the partitioned-table restriction, and test recovery. For each case, an independently written manifest specifies the expected outcome. A ledger ties that expectation to catalog flags, query plans, and error output.
The approach follows Refonte Learning’s SQLite UPSERT partial-index validation playbook. A successful command tag alone cannot establish that the intended index covers the intended rows. If the build leaves an invalid index, the documented recovery is to drop it, possibly with DROP INDEX CONCURRENTLY, correct the cause, and retry. The decision matrix shows when to accept, repair, rerun, or hold. All fixture outcomes in this article are EXPECTED / NOT EXECUTED.
1. Define the operational contract
First, we explicitly state what “index built concurrently, valid for queries” means. The acceptance contract is:
· Completeness: The index must include all eligible rows. For the non-null keys in the unique-index cases, no two rows may have the same indexed value.
· Catalog Flags: In pg_index, indisvalid must be true (meaning the index is safe for queries), and indisready must be true (meaning it is used for new inserts/updates).
· Queryability: The index should be used by the optimizer where appropriate (when no older transactions block it). If a long-running transaction predates the build, the index may not yet appear to it, but that is not a build error.
· Uniqueness enforcement: After a successful unique build, the non-null indexed values in the fixture must be unique. A failure during the second scan can leave an index that still rejects duplicate writes, even though queries cannot use it.
· Error handling: A concurrent build that fails while scanning the table can leave an invalid index. Preserve the error and check for the index before dropping it and retrying. An error that rejects the command before the build starts has a different expected catalog state.
The fixture has six cases.
· Case 1: Unique index on a table with duplicates. We insert duplicate keys and run CREATE UNIQUE INDEX CONCURRENTLY. Expect a unique violation, an index with indisvalid = false, and both original rows still present. The planner must ignore the index. Capture stderr and read indisready separately to determine whether writes maintain the index. Whether new duplicates are rejected depends on the build phase reached.
· Case 2: Unique index on a table without duplicates. Expect the command tag “CREATE INDEX” and a catalog entry with indisvalid = true and indisready = true. Capture an EXPLAIN plan for a lookup on the indexed column. A valid index is eligible for that lookup, although the optimizer may choose a sequential scan on a small table.
· Case 3: Normal (non-unique) index. Ensure that no error occurs even with identical data, and that indisvalid=true. (A normal index doesn’t enforce uniqueness, so duplicates shouldn’t cause failure.)
· Case 4: Create an index concurrently inside a transaction. Expect an error because concurrent builds cannot run inside an explicit transaction block. This tests the documented restriction.
· Case 5: Partitioned table with concurrent index build. The docs state that “concurrent builds for indexes on partitioned tables are currently not supported”; we expect an error.
· Case 6: DROP INDEX CONCURRENTLY recovery. After a failed build leaves an invalid standalone index, run DROP INDEX CONCURRENTLY idx1 outside a transaction block. The command can wait for conflicting transactions. Capture the command tag and confirm the index is absent from the catalog.
Each case has an ID (C1 through C6), SQL steps, expected outputs, and evidence to check in the manifest below. For example, if CREATE INDEX CONCURRENTLY returns success but the same index has pg_index.indisvalid = false, the command tag and catalog disagree. Preserve stdout, stderr, and catalog snapshots before cleanup. The same acceptance test applies to a SQLite partial-index UPSERT.
2. Record the documented baseline and scope
Before running tests, we confirm the documented behavior of concurrent index builds. The PostgreSQL manual explains that a concurrent build does not block writes but works in phases: first, it inserts an invalid index entry, then does two table scans, waiting for preexisting transactions to finish, then marks the index valid. Crucially:
· Invalid index on failure: If a scan fails, PostgreSQL can leave an invalid index behind. Queries ignore it. If the index reached the stage where writes maintain it, it can still add update overhead until it is dropped.
· Uniqueness caveats: Once the second scan begins, a concurrent unique index already enforces uniqueness against other transactions. If this scan fails, the invalid index can continue to reject duplicate inserts.
· Catalog flags: In pg_index, a false indisvalid means the index may be incomplete and cannot safely serve queries. If it is unique, the flag also means the table’s existing rows are not guaranteed to satisfy uniqueness. Read indisready separately. When it is false, inserts and updates ignore the index. When it is true, they maintain it.
· Concurrent drop restrictions: DROP INDEX CONCURRENTLY waits for conflicting transactions while allowing reads and writes on the table. It accepts one index name, disallows CASCADE, and must run outside an explicit transaction block. An index supporting a primary-key or unique constraint cannot be removed independently of that constraint. The recovery example uses a standalone index.
Our scope is concurrency-related failure and recovery. The examples use B-tree indexes on simple columns. They exclude partition-level recursion and index expressions. We separate the command tag from runtime visibility, since a transaction that predates the build may be unable to use the new index.
Version and environment fingerprint
The fixture targets the PostgreSQL 18 branch, specifically 18.7 for the release decision below. Record the server’s full version and confirm the exact build before comparing outcomes.
# EXPECTED / NOT EXECUTED
psql -d testdb -c "SELECT version();"
psql -d testdb -Atc "SHOW server_version_num;" | grep -x 180007
Record the version string, including the exact patch release and platform, in evidence/version.txt. Keep the fixture on a single Linux node with a PostgreSQL 18.x psql client and no background traffic. Include psql --version and uname -a in the environment record. Refonte’s row-level security guide gives “PostgreSQL 18.1 on Linux” as an example of this fingerprint. A different server build needs its own evidence.
All operations use a fresh database cluster, initialized with initdb and started with pg_ctl. We connect over a local Unix socket and create a separate database for the fixture. An administrator creates the database and login. The test user creates the fixture objects and uses separate psql sessions for the cases.
3. Pin versions and build an isolated fixture
Use the PostgreSQL 18.7 binaries in a fresh working directory. The following commands set up the fixture. Run the version checks above after the database is available.
# Setup (EXPECTED / NOT EXECUTED)
mkdir -p socket evidence
export PGHOST="$PWD/socket" PGPORT=55432
initdb -D pgdata --no-locale --encoding=UTF8
pg_ctl -D pgdata -o "-F -p $PGPORT -k '$PGHOST' -c listen_addresses=''" \
-l serverlog start
psql -v ON_ERROR_STOP=1 -d postgres -c "CREATE ROLE testuser LOGIN;"
psql -v ON_ERROR_STOP=1 -d postgres \
-c "CREATE DATABASE testdb OWNER testuser;"
export PGDATABASE=testdb PGUSER=testuser
This creates a fresh PostgreSQL cluster with a private socket directory. Record the outputs of psql --version and uname -a in evidence/environment.txt, as in Refonte’s Kubernetes environment capture.
Then we define our schema in schema.sql:
-- schema.sql (EXPECTED / NOT EXECUTED)
CREATE TABLE items (
id SERIAL PRIMARY KEY,
tenant TEXT NOT NULL,
val INT
);
-- Table has no duplicates initially; "tenant" might be used for partial indexes.
The fixture keeps tenant and val separate so the index definition is explicit. A compound index on (tenant, val) would test uniqueness of the pair. The unique indexes in C1, C2, and C6 test val alone.
Run each independent case in a clean session with a fresh copy of the fixture table, or remove case-specific indexes before truncating and reseeding the table. Truncation alone retains the indexes. Preserve the failed C1 state for C6, which starts from that index and its two duplicate rows. Run all catalog queries and plans in testdb.
4. Write an independent expected-case manifest
Before running anything, write down the expected outcome for each case. This independent manifest gives us something to check the actual outputs against. Every row below is EXPECTED / NOT EXECUTED.
Case | Scenario | Steps (SQL) | Expected outcome | Evidence to verify |
C1 | Unique index on duplicates | Insert two rows with the same val. Run CREATE UNIQUE INDEX CONCURRENTLY idx1 ON items(val). | Unique violation. The index stays invalid and both duplicate rows remain. | Capture stderr. Check indisvalid and indisready for idx1, and count the duplicate rows. |
C2 | Unique index, no duplicates | Ensure val has no duplicate non-null keys. Run CREATE UNIQUE INDEX CONCURRENTLY idx2 ON items(val). | CREATE INDEX returns successfully. Both indisvalid and indisready are true. | Check both flags. Save the EXPLAIN plan for a lookup on val and identify the scan chosen. |
C3 | Non-unique index with duplicates | Run CREATE INDEX CONCURRENTLY idx3 ON items(val). | The build succeeds. The index is valid and eligible for queries on val. An index-only scan is possible for a suitable query. | Verify indisvalid. Capture the plan and identify idx3 if the optimizer selects it. |
C4 | Explicit transaction block | Issue BEGIN, then CREATE INDEX CONCURRENTLY idx4 ON items(val). End the failed transaction. | PostgreSQL rejects the concurrent build inside a transaction block. No index is created. | Capture stderr. In psql, check that \di idx4 finds no index. |
C5 | Partitioned parent table | Create the parent and two partitions. Issue CREATE INDEX CONCURRENTLY on the parent. | PostgreSQL rejects the concurrent build on the parent. Builds on individual partitions are supported. | Capture the error from the parent index command. |
C6 | Concurrent drop and recovery | Start from C1. Drop idx1 concurrently, remove the extra duplicate row, and retry the unique build. | The old index disappears. The rebuilt index succeeds under the same name. | Check the old index is absent after the drop. Record the new OID, command tag, and validity flag. |
C1 tests the error path. The two fixture rows have the same non-null key, so our assertion is:
“CREATE UNIQUE INDEX CONCURRENTLY idx1 ON items(val) on a table containing duplicates must return an error.”
We capture stderr and check for a uniqueness violation, for example “could not create unique index” with a detail identifying the duplicated key. Run the following as a script with psql -v ON_ERROR_STOP=1.
-- Case C1 (EXPECTED / NOT EXECUTED)
INSERT INTO items (tenant,val) VALUES ('a', 100), ('a', 100);
CREATE UNIQUE INDEX CONCURRENTLY idx1 ON items(val);
We expect a nonzero exit status and an error identifying the duplicate. This check verifies that PostgreSQL reports the conflict and leaves the duplicate rows in the table.
5. Reproduce the misleading success signal
The PostgreSQL manual explains that an index can still be unavailable to some transactions that predate a successful concurrent build. A related timing issue arises earlier, while the build is still waiting for an old snapshot. The following two-session example distinguishes the two stages.
1. Open transaction. In session T1, run BEGIN ISOLATION LEVEL REPEATABLE READ; and SELECT COUNT(*) FROM items;. Leave the transaction open so it retains its snapshot.
2. Concurrent build. In session T2, run CREATE INDEX CONCURRENTLY idx_valid ON items(val); outside a transaction block. Let the command run while T1 remains open. The build must wait for a snapshot that predates the second scan before it can finish.
3. Test in T1. While T2 is running, use T1 to run EXPLAIN SELECT * FROM items WHERE val = 123;. Record the plan before releasing the snapshot with COMMIT;. At this point, the absence of idx_valid is consistent with a build that has yet to finish.
4. Test in a new transaction. After T2 returns successfully, start a new session, check the flags for idx_valid and repeat the same EXPLAIN. The new index is eligible for this query. The optimizer chooses a scan according to its estimated cost.
5. Interpretation. Compare each plan with the time at which T2 finished. T1’s plan was captured before completion. A sequential scan in the fresh session may simply be cheaper on a small table.
Check the success signal (“CREATE INDEX” returned without error) against pg_index.indisvalid in a fresh transaction. Record the plan separately to show how the query was planned. The assertion for this fixture is:
“If the concurrent build completes successfully, the new session should find the intended index marked valid. An index scan in its plan is additional evidence that the planner selected that index for this query.”
We capture evidence:
· Before completion. Save the EXPLAIN plan from T1 and note when the transaction released its snapshot.
· After completion. Save the catalog flags and the EXPLAIN plan from the fresh session. Record the scan that PostgreSQL chose. The PostgreSQL documentation describes the timing caveat for transactions that predate the build.
6. Run a controlled comparison case
To clarify the above, we run a “control” without the long-transaction interference:
· Base case. Start a new session in the clean fixture and ensure no other sessions are reading items. Run CREATE INDEX CONCURRENTLY idx_ctrl ON items(val);. After it returns, check the catalog entry and run the same EXPLAIN in a fresh transaction. Record whether the planner chose the new index.
· We also compare a non-concurrent build, CREATE INDEX idx_norm ON items(val);, on a separate copy of the same small table. It blocks writes while it runs. Both methods should leave a valid index. Keeping the two indexes in separate fixtures lets us identify the index selected in each plan.
Distinguish absent evidence from failure
This section’s key falsifiable assertion is:
“After a successful build on this fixture, a fresh connection should find the new index marked valid and ready. Whether the connection’s query uses it can be checked independently.”
For the control case, we capture:
-- Controlled build (EXPECTED / NOT EXECUTED)
CREATE INDEX CONCURRENTLY idx_ctrl ON items(val);
-- Record the plan chosen after the build.
EXPLAIN SELECT * FROM items WHERE val = 123;
An “Index Scan using idx_ctrl” or a “Bitmap Index Scan” on that index shows that the planner chose it. A sequential scan is also plausible on this small table. If the chosen plan differs from the expectation, check the catalog flags, query predicate and table statistics before attributing the difference to the build.
For completeness, record when each catalog query ran. T1 retained an older snapshot and may see different catalog information. The check in a fresh transaction establishes which flags PostgreSQL reports after the build completes.
Refonte Learning’s SQLite WAL playbook also distinguishes missing evidence from a bug by comparing counter values with concrete database states.
7. Test negative controls and boundary conditions
We include tests that should definitely fail or behave in a constrained way:
· C4 (inside a transaction block). Running BEGIN; followed by CREATE INDEX CONCURRENTLY should return an error because concurrent builds must run outside an explicit transaction block. Capture stderr, then roll back if the session is still open.
· C5 (partitioned table). Create a separate parent table with two partitions. A concurrent index build on the parent should fail. Capture the actual error. PostgreSQL supports building the indexes concurrently on the individual partitions.
· Concurrent drop inside a transaction block. As a side check, begin an explicit transaction and try DROP INDEX CONCURRENTLY idx3; on the existing C3 index. An error about the transaction block is expected under the rules for concurrent drops. End the failed transaction with ROLLBACK;.
· Index supporting a constraint. The index supporting the primary key on items provides another negative control. Trying to remove it with DROP INDEX CONCURRENTLY should produce an error. Concurrent drops do not support CASCADE.
Each negative control tests a documented restriction. We assert:
“Attempting an unsupported concurrency action results in an explicit error, not silent behavior.”
Evidence: stderr in each control. For example:
-- C4 negative control (EXPECTED / NOT EXECUTED)
BEGIN;
CREATE INDEX CONCURRENTLY idx4 ON items(val);
Expect: psql error about transaction block.
Another control:
-- C5 negative control (EXPECTED / NOT EXECUTED)
CREATE TABLE items_part (tenant text NOT NULL, val int)
PARTITION BY LIST (tenant);
CREATE TABLE items_a PARTITION OF items_part FOR VALUES IN ('x');
CREATE TABLE items_b PARTITION OF items_part FOR VALUES IN ('y');
CREATE INDEX CONCURRENTLY idx_part ON items_part(val);
Expected result: the parent index command returns an error about concurrent builds on a partitioned table.
8. Capture raw evidence before cleanup
Before we remove or alter anything for recovery, we snapshot the evidence for each case. This means saving:
· SQL output: The full stdout/stderr of the psql session for each test (including EXPLAIN output). We label them per case (e.g. evidence/C1_stderr.txt, evidence/C2_out.txt).
· Catalog dumps: The relevant system catalog queries. In particular:
-- Catalog snapshot (EXPECTED / NOT EXECUTED)
SELECT indexrelid AS index_oid, indexrelid::regclass AS index_name,
indisvalid, indisready
FROM pg_index WHERE indrelid = 'public.items'::regclass;
This (in a table or CSV form) shows all indexes on items. For each test, we save before/after snapshots (in TSV or text).
· Row snapshots (if needed). For unique-index tests, use the psql command \copy (SELECT * FROM items ORDER BY id) TO 'before.tsv'. Repeat it after the operation with 'after.tsv' as the destination. Ordering by the primary key makes it possible to compare the two files and identify any intended changes.
· EXPLAIN plans. Record the plan for each query, including whether the planner selected the index.
For example, after C1 fails, we would run:
-- After C1 (EXPECTED / NOT EXECUTED)
SELECT indexrelid::regclass, indisvalid, indisready
FROM pg_index WHERE indexrelid = to_regclass('public.idx1');
Save the returned row, including both flags. For C1, preexisting duplicates are expected to make the first scan fail before the index becomes ready for writes. A later failure can leave an index invalid but ready. Record the actual value of indisready to distinguish the cases. For the query plan, run the following statement.
-- Plan check (EXPECTED / NOT EXECUTED)
EXPLAIN SELECT * FROM items WHERE val = 100;
Check whether the plan mentions idx1. An invalid index should be ignored for querying. If the plan names it, preserve the contradictory observation along with the catalog snapshot. Save these outputs so they remain available after the session ends.
Preserve contradictory observations
If any test yields an unexpected state, we keep that raw evidence. For instance, if C2 (which should succeed) somehow left indisvalid=false, that contradiction between “CREATE INDEX returned success” and catalogs must be retained. Even contradictory logs (e.g. a success message alongside an error code) go into evidence/.
The audit compares each case with the independent manifest. The following illustrative entries show how to record a discrepancy for investigation. All entries are hypothetical (EXPECTED / NOT EXECUTED).
Case | Expected indisvalid | Example value | Interpretation |
C1 | false | false | Matches |
C2 | true | false | Contradiction |
C3 | true | true | Matches |
Every such discrepancy is documented. (This “ledger” is discussed below.) In short, all raw data (stderr, pg_index queries, etc.) is our ground truth and is labeled “raw” or “untouched” in ledger.tsv for human review.
9. Reconcile state, identity and completeness
With raw evidence captured, we reconcile each case’s actual state against the manifest. We verify:
· Index presence or absence. If C1 fails during the scan, we expect pg_index to list the index with indisvalid=false. An unexpected absence warrants investigation.
· Flag correctness. Check both indisvalid and indisready. C1 should leave an invalid index. For this first-scan failure, expect indisready=false. A failure during the second scan can instead leave it true. A successful C2 build should show both flags as true.
· Table content consistency: The table should not have changed except what we intended (we never delete data except duplicates in controlled repair). We ensure COPY or SELECT of rows matches initial data plus any inserts.
· Uniqueness after failure. The two preexisting duplicate rows in C1 should remain. Record separately whether later writes encounter a uniqueness check. For C6, remove the extra fixture row between attempts and verify that the remaining non-null values are unique after the successful retry.
· Log and plan alignment. If an EXPLAIN plan omits an index we expected it to use, check when the query ran and which snapshot it used. In a fresh transaction, consider the table size, statistics and estimated scan costs before interpreting the plan.
An evidence ledger in TSV or Markdown can record each case’s flags, error codes and notes. The following excerpt illustrates the format (EXPECTED / NOT EXECUTED).
Case | Error | indisvalid | indisready | Index used? | Disposition |
C1 | Duplicate key | false | false | No | REPAIR |
C2 | None | true | true | Yes, in this example | ACCEPT |
C4 | Transaction block | N/A | N/A | N/A | REJECT operation |
To verify identity, record the OID of idx1 and compare it across queries within the same build. Dropping and rebuilding the index gives it a new OID even if its name is reused. Check that the evidence covers every required observation. If a required plan or catalog snapshot is missing, mark the case HOLD.
This step parallels the state reconciliation in the Kubernetes ConfigMap example: each surface (catalog, storage, running process) must match the intended contract. Here, the “state” comprises the pg_index flags, actual data rows, and query results.
10. Separate normative guarantees from observations
Now we interpret our evidence in light of documented guarantees vs actual outcomes:
· Documented behavior. If a concurrent build fails while scanning the table, PostgreSQL leaves an invalid index. A successful build marks the index valid. Commands rejected before the build begins, such as C4, do not create an index.
· Observation. In a completed run, the catalog row for C1 would establish which flags the server actually reported. All fixture outcomes here are EXPECTED / NOT EXECUTED.
If documentation and evidence agree, we ACCEPT that behavior. If they conflict, we trust evidence and mark an anomaly. For example, suppose (hypothetically) we observed C2’s index with indisready=false (contrary to doc that it should be ready). That would trigger deeper investigation. But suppose everything aligns: then we note “docs said invalid index left; indeed we see an invalid index”.
For example, the documented restrictions prohibit dropping a primary-key index concurrently. If DROP INDEX CONCURRENTLY pk_idx returns the expected dependency error for that index, the negative control passes. If it succeeds, preserve the evidence for investigation.
PostgreSQL documents a specific uniqueness side effect: if a unique concurrent build fails during its second scan, the invalid index continues to enforce uniqueness. C1 seeds duplicates before the build, so its first scan can fail earlier. A follow-up duplicate insert in the disposable fixture tests enforcement at that stage. Record the outcome and roll back the probe to preserve the two original rows.
Flag any inference about indisready for a particular failure stage. Compare it with the catalog snapshot and the duplicate-insert result before drawing a conclusion about that run.
Bound the repair and its risks
When a scan fails and leaves the standalone index invalid, limit the repair to the affected index and the known duplicate fixture row.
· If the standalone index is invalid (indisvalid = false), the smallest repair is DROP INDEX idx1 or DROP INDEX CONCURRENTLY idx1, depending on the acceptable impact. A concurrent drop allows reads and writes on the table while it waits for conflicting transactions.
“If pg_index shows an invalid index, then running DROP INDEX CONCURRENTLY should remove it; afterward, the table should be in its original state.”
Expected evidence: After the drop, SELECT to_regclass('public.idx1'); returns NULL. Compare the ordered table rows before and after the drop to verify that the operation preserved them.
· For a unique index with duplicates, dropping the index is only half the fix; we also must remove duplicates before retrying. We frame the risk: fixing data (deleting duplicates) is outside the DB engine’s automated fix. If the contract demands the index be built, we must accept manual remediation.
We bound the repair in three ways.
· Drop only the affected index, using an operation compatible with the acceptable lock impact.
· Treat deletion of duplicate data as a separate repair decision.
· Check for dependent objects before dropping the index.
Before dropping idx1 in C1, check its dependencies. The example creates a standalone index. If an index backs a primary-key or unique constraint, stop and review the dependency with the table owner. A plain index drop cannot remove that dependency, and a change to the constraint has different locking and integrity consequences.
Capture evidence around the repair itself. Starting from the failed C1 build, run:
-- Case C6 recovery (EXPECTED / NOT EXECUTED)
DROP INDEX CONCURRENTLY public.idx1;
-- Confirm that no catalog row resolves to the old name.
SELECT * FROM pg_index
WHERE indexrelid = to_regclass('public.idx1');
The query should return no rows. After fixing the duplicate fixture data, retry the build and check the new catalog entry.
“After dropping an invalid index (concurrently), retrying CREATE INDEX CONCURRENTLY with no duplicates will succeed and mark the index valid.”
Record the version and cluster identity around the repair to rule out a connection to a different server or an older transaction. Refonte’s topology spread constraints playbook similarly records the exact cluster version and node inventory.
11. Apply the smallest safe repair
Based on the above, for any failed case we apply only the targeted repair:
· Index drop: If C1 left idx1 invalid, run DROP INDEX CONCURRENTLY public.idx1 outside a transaction block and capture the “DROP INDEX” command tag. If a concurrent drop is unavailable, review the restrictions and the table lock required for an ordinary drop before choosing that operation.
· Data fix: C1 deliberately inserted two rows for tenant a with val = 100. Remove the extra fixture row by its primary key, keeping the earlier row. A grouped check on the indexed column must then show no duplicate non-null values across the table. This step is a separate data repair.
· Rerun the build: Run CREATE UNIQUE INDEX CONCURRENTLY idx1 ON items(val) again. After the duplicate is removed, the expected result is a successful build and a valid index.
The complete C6 repair below starts from the failed C1 state. Run it in psql with autocommit enabled and ON_ERROR_STOP set to 1.
-- Case C6 repair (EXPECTED / NOT EXECUTED)
DROP INDEX CONCURRENTLY public.idx1;
DELETE FROM items
WHERE id = (
SELECT max(id) FROM items
WHERE tenant = 'a' AND val = 100
HAVING count(*) = 2
);
SELECT val, count(*) FROM items WHERE val IS NOT NULL
GROUP BY val HAVING count(*) > 1;
CREATE UNIQUE INDEX CONCURRENTLY idx1 ON items(val);
After the create, we again check pg_index. The index should now appear as valid (indisvalid=true). If any step fails (e.g. if drop deadlocks), we capture that too.
Reconnect as the index owner and verify that the session is outside an explicit transaction block. Record the role and connection context with the repair evidence so that an ownership error can be distinguished from a build failure.
Finally, record the repaired index’s identity and validity flags before deciding whether to accept the result.
12. Test recovery, rollback and rerun safety
After repairing, we validate that our recovery process itself is safe and idempotent:
· Partial side effects: Test a repeated drop while the index is still absent and before rebuilding it. With DROP INDEX CONCURRENTLY IF EXISTS public.idx1, the second attempt should report that the index does not exist.
“Running the drop step again does nothing harmful.”
Expected evidence from the second drop is a notice, with the index still absent from pg_index.
· Rollback Safety: Each SQL script is designed to be run with proper transaction boundaries. We never leave a partially created index (except what PG already did) before drop. If a drop fails midway (unlikely, but say the server crashes), our test would mark HOLD due to incomplete cleanup (this is a planning scenario, not executed here).
· Rerun with different conditions: After recording the successful rebuild, recreate the original fixture on a fresh table. Seed the same duplicate rows, run the unique build again, and repeat the repair. Reusing the successfully indexed table would cause the duplicate insert itself to fail.
“After repair, the table contains the intended rows and the rebuilt index is valid. Repeating the exercise from the same starting data should produce the same result.”
· Transaction limits: Run DROP INDEX CONCURRENTLY outside an explicit transaction block. Keep that boundary visible in the repair script.
Assign ownership and rerun triggers
We assign final ownership of the issue: after repair, the new index is owned by the table owner (e.g. testuser) and acts as normal. If an engineer wants to rerun this test (e.g. in a later environment or after an application change), they need to trigger it by any event that changes the relevant state (for example, if new duplicates are inserted or if the index-definition changes).
In practice, we might set a flag or ticket: “When a CREATE INDEX CONCURRENTLY happens on production, rerun these checks.” The evidence artifact here is simply the repaired state itself: a listing of pg_index and table rows after repair, saved for record.
We also log that the next steps (if still failing) would involve a HOLD and possibly manual data reconciliation (“RECONCILE” in Refonte terms), but that’s beyond what our test must do.
13. Make the ACCEPT / REPAIR / RERUN / HOLD decision
The matrix shows how to classify each case if its expected evidence is collected. All six outcomes are EXPECTED / NOT EXECUTED.
Case | Expected outcome | Decision if confirmed | Justification |
C1 | Unique build fails and leaves an invalid index. | REPAIR | Drop the invalid idx1 and remove the known duplicate fixture row. |
C2 | Unique build succeeds. The index is valid and eligible for queries. | ACCEPT | The index and the row checks meet the contract. Save the actual plan. |
C3 | Non-unique build succeeds with duplicate values. | ACCEPT | The index is valid. The definition permits duplicate keys. |
C4 | Build is rejected inside an explicit transaction block. | REJECT operation | The negative control passes if PostgreSQL reports the restriction and creates no index. |
C5 | Concurrent build on the partitioned parent is rejected. | REJECT operation | The error confirms the documented restriction on the parent. |
C6 | Invalid index is removed, fixture data repaired, and index rebuilt. | ACCEPT | The replacement has a new OID, the expected row set, and valid and ready flags. |
For PostgreSQL 18.7, the expected outcome is ACCEPT for C2 and C3 when the valid index and row checks agree. C1 requires REPAIR if the scan leaves an invalid index and duplicate rows. C4 and C5 reject unsupported operations. Their expected errors count as passing negative controls when the recorded catalog state also matches the manifest.
A real release decision requires the evidence ledger from that run. Choose RERUN when a corrected fixture or a fresh session can supply a missing check. Keep the decision on HOLD while a contradiction or incomplete evidence prevents verification.
14. Database administration skills at Refonte Learning
Refonte Learning’s Database Administrator Essentials program covers database design, query optimization, backup and recovery, performance tuning, and security. The three-month program requires 12–14 hours per week and emphasizes hands-on validation, including the kinds of checks used here to verify data integrity and reliability. On completion, students earn a Training Certificate and an Internship Certificate.
