A View That Refuses Updates, a Slow Lookup and a Gapless Sequence Request

Cause. WITH CHECK OPTION means: any row inserted or updated through this view must still be visible through the view afterwards. The view's predicate is status = 'active'. Changing the status to 'cancelled' produces a row that fails that predicate, so PostgreSQL rejects the statement with a check-option violation. The base-table update works because it bypasses the view's predicate entirely. The view is doing exactly what it was told.

Options.

1. Write to the base table for lifecycle changes. Keep v_active_subs as a read-optimised view for the BI tool's lists, and let the cancel action target subscriptions directly. Simplest and safest; requires a second grant.
2. Create a second view without the filter clause, e.g. v_all_subs over the same columns with no WHERE and no check option, and point the write path at it. The view stays auto-updatable because it is a simple single-table select with no aggregates, DISTINCT, GROUP BY, UNION or window functions.
3. Drop WITH CHECK OPTION. The UPDATE then succeeds - and the row silently disappears from the view, which users read as 'the row was deleted'. Every read after the write must go to the base table. Acceptable only if the team understands this.
4. INSTEAD OF trigger on the view to route writes explicitly. Maximum control, maximum maintenance; overkill for one status change.

Recommendation: option 1, with option 2 if the BI tool must write through a view. Never widen the check option just to make an error message go away.

Cause. The predicate is on lower(email), not on email. A B-tree index on email is ordered by the raw stored value, so the optimizer has no way to seek on a computed value; it falls back to a sequential scan, evaluating lower() on all 220,000 rows. The index is not broken, it is simply irrelevant to this query. (ILIKE, date(created_at) and amount + 0 columns cause the same class of problem.)

Fix - expression index that matches the predicate exactly:

CREATE INDEX CONCURRENTLY idx_customers_lower_email
  ON customers (lower(email));
ANALYZE customers;

The expression in the index definition must match the one in the query literally; lower(email) and LOWER(TRIM(email)) are different expressions and only the second would use an index on the second.

Alternatives worth weighing.

• Store a normalised email_lower column maintained by a trigger or generated column, and index that. More storage, but readable in every client and indexable in any DBMS.
• Use the citext extension so = is case-insensitive by default. Convenient, but changes equality semantics everywhere the type is used.
• Use a case-insensitive collation, keeping in mind it affects ordering as well as matching.

Operational caveats. CREATE INDEX takes a lock that blocks writes for the whole build; on a live 220,000-row table use CREATE INDEX CONCURRENTLY, which cannot run inside a transaction block and can leave an INVALID index if it fails - check pg_index.indisvalid and drop and retry if so. Finally, confirm the plan actually changes with EXPLAIN (ANALYZE, BUFFERS); 900 ms on 220,000 rows suggests the table has far more dead tuples than live ones.

Why the gaps exist. A sequence is built for uniqueness and concurrency, not for contiguity. Several normal events burn a number:

• nextval() is non-transactional: the number is consumed even if the transaction later rolls back. A failed insert leaves a hole by design.
• The default cache is 10 per session; if a session exits without using its cached block, those numbers are discarded. A restart discards the rest.
• Concurrent sessions draw from the same sequence, so numbers are handed out in arrival order, not in commit order - a row with 1047 can commit before 1046.
• Failed uniqueness checks and retried jobs each consume a number.

Numbers 1041–1046 missing is therefore normal behaviour, not corruption. Gaps are a symptom of the design working correctly.

Advice.

1. Reframe the requirement. Auditors need unique, non-reusable, traceable invoice numbers, not contiguous ones; gaps are commonly acceptable and sometimes legally required to preserve an audit trail of voided invoices. Confirm the legal requirement before building anything.
2. If true contiguity is mandatory, sequences cannot deliver it. Use a counter table updated inside the same transaction as the invoice:

UPDATE number_counters SET last_value = last_value + 1
WHERE name = 'invoice' RETURNING last_value;

This serialises invoice creation - one number per transaction, in commit order - so throughput drops and lock waits appear under concurrency. Accept that trade explicitly. 3. Never reuse a burned number, and never renumber issued invoices; that breaks every downstream reference and any audit trail. 4. Keep invoice_seq for surrogate keys and, if needed, add a human-facing number assigned at posting time by the counter table.