Обсуждение: unique constraint

Поиск
Список
Период
Сортировка

unique constraint

От
Jamie Kahgee
Дата:
I have a table of paragraphs for pages that are in a specific order (1st, 2nd, 3rd, etc...).

demo=# \d paragraphs
                           Table "toolbox.paragraphs"
   Column    |  Type   |                        Modifiers                        
-------------+---------+---------------------------------------------------------
 ...
 page        | integer | not null
 pos         | integer | not null default 1
 ...

Is there a good way to ensure these paragraphs order can't get all out of whack?  what I mean is - we had a slight hiccup in a query and when paragraph positions were moved it sometimes messed up the order of other paragraph positions.  for example, some paragraph positions for a page might end up like (1st, 2nd, 2nd, 4th, 5th) or some other random list w/ duplicate positions

I've fixed the incorrect query, but would like to know if there is a better constraint that I could use to ensure this can't happen besides spectacular bug-free programming

I tried using a unique constraint on the page/pos columns, but was running into constraint errors when I did an update to move positions - in a transaction, there might be two pages at the same position for an instance while they are getting shuffled around.

to fix this I tried deferring the constraints, but as we're using version 8.2.5, realized this isn't supported and might not be the best approach?

Any ideas from the community that might be usefull?

Thanks,
Jamie K.

Re: unique constraint

От
Michael Glaesemann
Дата:
On Oct 20, 2010, at 15:58 , Jamie Kahgee wrote:

> I have a table of paragraphs for pages that are in a specific order (1st,
> 2nd, 3rd, etc...).

<snip />

> I tried using a unique constraint on the page/pos columns, but was running
> into constraint errors when I did an update to move positions - in a
> transaction, there might be two pages at the same position for an instance
> while they are getting shuffled around.
>
> to fix this I tried deferring the constraints, but as we're using version
> 8.2.5, realized this isn't supported and might not be the best approach?

Upgrade if you can. Otherwise you can do two updates when rearranging paragraphs. The first updates them to an
"invalid"range which isn't used in "correct" data and so won't conflict with other values, and the second moves them
backto the valid range of positions. 
Negative numbers are often used for this middle range.

This is basically a hierarchy problem. Take a gander around the web for nested sets for examples.

Michael Glaesemann
grzm seespotcode net

Re: unique constraint

От
Jeff Davis
Дата:
On Wed, 2010-10-20 at 16:10 -0400, Michael Glaesemann wrote:
> Upgrade if you can. Otherwise you can do two updates when rearranging paragraphs. The first updates them to an
"invalid"range which isn't used in "correct" data and so won't conflict with other values, and the second moves them
backto the valid range of positions. 
> Negative numbers are often used for this middle range.

The reason for the upgrade suggestion I believe was DEFERRABLE unique
constraints. These allow you to temporarily violate a unique constraint,
as long as it's correct at the end.

See:

  http://www.postgresql.org/docs/9.0/static/sql-createtable.html

and look for "DEFERRABLE".

You can also consider a constraint trigger:

  http://www.postgresql.org/docs/9.0/static/sql-createconstraint.html

Be careful to lock appropriately, however.

Regards,
    Jeff Davis