[NCLUG] Annoying post-meeting SQL curiosities

[Michael Milligan]

Aaron D. Johnson wrote:
> During the very pleasant post-meeting dinner discussions last night, I
> mentioned I had been let down by a number of SQL databases over the
> last week.  Paul suggested I share with the list, so here's my cause
> of consternation.  Consider the following:
> 
>     CREATE TABLE test (a INTEGER);
>     CREATE UNIQUE INDEX test_a_idx ON test (a);
>     INSERT INTO test (a) VALUES (1);
>     INSERT INTO test (a) VALUES (2);
> 
> So far, all is good.  I'll skip the SELECT showing that it worked as
> expected.
> 
> Here's where the fun starts:
> 
>     UPDATE test SET a = a + 1;
> 
> This should leave us with two rows, with values 2 and 3, right?  Well,

Yes, unless you have table constraints, triggers, or rules that dictate
otherwise...  this is a somewhat ambiguous and contrived situation.

> not so fast.  Some databases do work that way, and others report that
> the unique index constraint is being violated.

I would expect that behavior for an ACID-compliant database (such as
PostgreSQL) given your example.  It updates the index after each row is
updated as a result of your UPDATE statement within the transaction, but
because of your insert order (you put in 1 before 2), the 1 row will be
incremented before the 2 row (e.g., PostgreSQL does sequential table
scans based on insert, or OID, order) and thus you are violating the
index constraint on the table.

The fix?  Drop the index before you do this operation.  Or don't use an
index at all.  The index is not useful for this transaction anyway since
you are altering every row in the table but uniqueness is provably
maintained.  The update operation is also a lot faster without the index
in the way.

I.e., the correct handling of this is:

BEGIN;
DROP INDEX test_a_idx;
UPDATE test SET a = a + 1;
CREATE UNIQUE INDEX test_a_idx ON test (a);
COMMIT;

Regards,
Mike

-- 
Michael Milligan                                   -> milli at acmeps.com