From: Josh Berkus <josh@agliodbs.com>
To: Mike Papper <mike@digitalpipe.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Misunderstanding about your article entitled: Episode 2:
Date: Tue, 23 Jul 2002 14:47:32 -0700
Message-ID: <web-1587626@davinci.ethosmedia.com> (raw)
In-Reply-To: <200207232008.g6NK8wc27201@mike.digitalpipe.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, mike@digitalpipe.net
Subject: Re: Misunderstanding about your article entitled: Episode 2:
In-Reply-To: <web-1587626@davinci.ethosmedia.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox