I have been working on a new PostgreSQL feature here:
https://commitfest.postgresql.org/action/patch_view?id=132
The idea is that you can specify index-enforced constraints other than
the simple UNIQUE constraint.
The original motivation for the patch was to allow "temporal keys" (i.e.
enforced non-overlapping time periods), but I would like it to be more
generally useful than that. It supports any constraint as long as:
(a) You have some commutative boolean operator that tells you if two
values conflict
(b) That operator can be used as a strategy to search the index.
So, UNIQUE is a special case, where the operator is "=". For a temporal
key, the operator would be "&&" on a time period type.
I would like this feature to be useful to PostGIS users, as well. I
think that a non-overlapping constraint (constraint operator: &&) would
be natural for many GIS applications, particularly for data integration.
My questions are:
1. What do people do now to enforce these types of constraints?
2. My feature is mostly useful for concurrent operations. If you're just
doing a bulk load, it would probably be just as fast to verify by using
a join (or self join). How much demand is there among PostGIS people to
enforce interesting constraints (like non-overlapping) in an environment
with concurrent insert/update/delete transactions (usually small-size
transactions, not bulk loads)?
3. Does anyone have a use for a constraint other than non-overlapping?
The feature would support it -- but it's hard for me to think of a
use-case. Perhaps "not entirely within some other geometry" would be a
useful constraint in some cases?
4. Is there anything that you can think of that this feature might do
better that would make it more useful to PostGIS users?
Right now, the patch works, but it's not really done. The language right
now is in the CREATE INDEX command, but I'm going to change that to be a
part of the CREATE/ALTER TABLE commands, so that one index can be used
to enforce multiple constraints. Also, it doesn't check the current
contents of the table when building an index over a table with data
already in it.
So, it's a bit rough around the edges, but I'd like some input from
potential users in the PostGIS community before I finalize the work and
submit it for the September commit fest (for PostgreSQL).
Regards,
Jeff Davis
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
