FAQ

On 5/8/13 2:17 PM, Martijn van Oosterhout wrote:
On Wed, May 08, 2013 at 10:19:08AM +0200, Vincenzo Melandri wrote:
On Tue, May 7, 2013 at 11:55 PM, Robert Haas wrote:
This is a really hard problem. If you pick this as your first project
hacking on PostgreSQL, you will almost certainly fail.
Thank you very much, i guessed that already -.-
Still, I needed that at my office for a long time, struggled with it many
times and had to come out with some "exotic" solutions...
Now I have spare time between projects, so I can work on it full-time. At
least it's worth a try, isn't it?
Well, you can work on it but I think it will be less programming and
more coming up with a feasable solution.
Anyway, I'm working to better understand the problem, trying to identify at
least the main involved points.
At the moment I'm figuring out how the inherit mechanism works for
relations (in tablecmds.c).. Then I'll figure out about how indexes work..
While there are probably old threads in the archives, I find the
easiest way to look at the problem is in the locking. In particular, I
think if you can get unique indexes to work then the rest will follow.

Consider the case of an inheritence hierarchy and you want a unique
index on a column. Since you want to be able to create and drop
children easily, each childs need to have an index just for them. But
if you insert a row into one child you need to, somehow, prevent other
people also inserting the same value in a different child. Efficiently
and deadlock-free. This is hard, though we're up for crazy,
out-of-the-box ideas.

Note, there is one very special case, namely:

- The children are used for partitioning.

- The unique index you want is on the partition key.

Since each value can only possibly appear in one table your locking
problems vanish. The question is: how often does this happen?
I would also consider indexes that span multiple tables that are do NOT involve inheritance. That's the most generic case, so if you can make that work everything else should fall into place. The only caveat is that UPDATE and DELETE in an inheritance tree could produce unique challenges since they would start off by reading from more than one table.
--
Jim C. Nasby, Data Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net

Search Discussions

Discussion Posts

Previous

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 5 of 5 | next ›
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedMay 6, '13 at 1:30p
activeMay 8, '13 at 7:32p
posts5
users5
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase