From: "Josh Berkus" <josh@agliodbs.com>
Subject: Re: Misunderstanding about your article entitled: Episode 2:
To: Mike Papper <mike@digitalpipe.net>
Cc: pgsql-sql@postgresql.org
Date: Tue, 23 Jul 2002 14:47:32 -0700
Message-ID: <web-1587626@davinci.ethosmedia.com>
In-Reply-To: <200207232008.g6NK8wc27201@mike.digitalpipe.net>
MIME-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: 8bit

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