Re: Misunderstanding about your article entitled: Episode 2:

Mike,

> "Technical Note: as of PostgreSQL 7.2.1, DEFERRABLE UNIQUE
> constraints are
> not supported -- which means that you should not declare unique
> constraints
> on columns whose values will be frequently re-arranged, like custom
> sort
> order columns, even if the values in these columns are unique."
>
> Perhaps you mean for columns that are part of an order by clause Or
> you mean
> something much more subtle...where the value for a column is set to A
> then to
> B then to C... all the time?

The latter. For example, if you have a table like:

create table articles (
article_id INT,
title VARCHAR(100),
summary varchar(300),
content TEXT,
sort_order INT );

... where sort_order is a user-maintained custom sort order which is
different from the alpha sort.

In this case, the values in sort_order will be unique whenever
re-ordering is complete, and this is fairly easy to ensure via
triggers. However, while the re-ordering is in process, there will
temporarily be some non-unique values while, for example, the
article.sort_order #122 is being swapped with article.sort_order #123.

When deferrable unique constraints are implemented (maybe Postgres
8.0?) then the uniqueness check will happen only at the *end* of a
transaction, and a unique index on this kind of a column will be
permitted. Currently, it raises an error preventing column
re-ordering.

-Josh Berkus