Thread: Inheritence issue scheme advice?
I'm obviously new. But making great progress in PostgreSQL with my new application... Setup: I'm running on MAC. Postgre 9.0.4 Virtual Machine with application dev in Linux. Problem: I like many other have come across the inherit issues. I found the thread here about such issue... http://postgresql.1045698.n5.nabble.com/FK-s-to-refer-to-rows-in-inheritance-child-td3287684.html I grabbed the "fk_inheritance.v1.patch" file and have been trying to install it for the last two hours. -- Got some help in hackers space so figured this out. BUT was it was recommended not to utilize a hacked version of PostgreSQL. Also person said that partitioning might be a way to go... BUT Partitioning becomes impossible as I'd have to hunt down every single row from every table within the hierarchy when needed. I've got an object driven system with permissions for users so I'll easily have thousands of rows to manage across 100's of tables. For inheritance I'm using it for the following. ONLY on/with UNIQUE CONSTRAINTS and FOREIGN KEYS with OIDS enabled - which from my understanding that shouldn't be an issues as there shouldn't any duplicate entries that cause a deadlock? -- So I would think this patch would be ok? Is there another way that won't be such a headache - cost tons of man hours - and still be efficient? I'm open for anything as I haven't been able to make any progress today :(. ----- Warmest regards, Casey Havenor -- View this message in context: http://postgresql.1045698.n5.nabble.com/Inheritence-issue-scheme-advice-tp4536626p4536626.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 2011-06-29 22:54, Casey Havenor wrote: > Problem: > I like many other have come across the inherit issues. > > I found the thread here about such issue... > http://postgresql.1045698.n5.nabble.com/FK-s-to-refer-to-rows-in-inheritance-child-td3287684.html > > I grabbed the "fk_inheritance.v1.patch" file and have been trying to install > it for the last two hours. -- Got some help in hackers space so figured this > out. BUT was it was recommended not to utilize a hacked version of > PostgreSQL. > > For inheritance I'm using it for the following. ONLY on/with UNIQUE > CONSTRAINTS and FOREIGN KEYS with OIDS enabled - which from my understanding > that shouldn't be an issues as there shouldn't any duplicate entries that > cause a deadlock? -- So I would think this patch would be ok? There are currently two caveats with the patch you mention 1: the user has to ensure global uniqueness of all pk's in an inheritance hierarchy. This is not a problem at all if e.g. your root relation has a pk with e.g. a default value from a sequence. The childs will inherit that default value from the same sequence. 2: the patch enables inserting rows in a relation that has a fk to a inheritance parent/root, with an fk key value that is found in one of the child relations of the refered relation. The patch as is fails to block deleting the referred record in the child relation: that check currently only works for referred records in actual relation the fk points to, not it's childs. It is not impossible to add this, but it just hasn't been programmed yet. So a trigger check to prevent these deletions also has to be made in user space. I'd very much appreciate any feedback you have on the patch, if it matches your usecase. regards, -- Yeb Havinga http://www.mgrid.net/ Mastering Medical Data
What has been your take on the patch - any long term drawbacks or any other functions / triggers that I'll have to stay away from when using this patch within PostgreSQL? Also any tutorials on how to apply the patch under windows/Linux/mac would be appreciated? ----- Warmest regards, Casey Havenor -- View this message in context: http://postgresql.1045698.n5.nabble.com/Inheritence-issue-scheme-advice-tp4536626p4537668.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Ok - loaded up Linux - fun stuff. Figured out how to get the code PostgreSQL version 9.0.4 - Have that in a directory. Got the patch from above... Placed into same directory. Loaded the dependents for the final compile and install with .configure. BUT When I run the patch I'm getting some Hunk fails? Is this because I'm not using the same version that the author intended the patch for? From the output it looks to be going through the files properly but just no able to inset the new code? I've tried ignoring white space and even tried a force - of course I backed everything up prior to this. :) Do you happen to know what version will work with this? Do - I need to get the tree via git? Or can I download the .gz extract the code - add the patched code - then recompile - and install? Thanks! ----- Warmest regards, Casey Havenor -- View this message in context: http://postgresql.1045698.n5.nabble.com/Inheritence-issue-scheme-advice-tp4536626p4540448.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Never-mind I'll just do it by hand. ----- Warmest regards, Casey Havenor -- View this message in context: http://postgresql.1045698.n5.nabble.com/Inheritence-issue-scheme-advice-tp4536626p4540532.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.