From: | "Jim Nasby" <jnasby(at)pervasive(dot)com> |
---|---|
To: | "Nathan Clark" <nathanc(at)abcsinc(dot)com> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: ablilty to test record for foreign key before deleting the record? - Found word(s) list error in the Text body |
Date: | 2005-11-15 21:06:00 |
Message-ID: | D1D2D51E3BE3FC4E98598248901F7594027F9061@ausmail2k4.aus.pervasive.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Adding -general back to the email list.
> From: Nathan Clark [mailto:nathanc(at)abcsinc(dot)com]
> The java application we are writing, throws a foreign key
> error, when the
> user tries to delete a record that has a foreign key. If
> there was a way to
> test the record for foreign keys before the user tries to
> delete, we could
> display a properly formatted error for user consumption.
>
> does Postgresql have a facility for this maybe?
The problem with doing what you propose is that it creates a race condition:
-- Check to see if there are any children
SELECT 1 FROM child WHERE parent_id = blah LIMIT 1;
-- Got back an empty set, so we can delete
-- But meanwhile someone else just inserted a child!
DELETE FROM parent WHERE parent_id = blah;
ERROR: Foreign key ...
Rather than doing this, you should just trap the error and handle it accordingly, either in your java or using a function. http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING provides examples of how to trap errors in plpgsql; you'd just need to modify that for the error you're trapping on.
> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Jim C. Nasby
> Sent: Tuesday, November 15, 2005 12:04 PM
> To: Nathan Clark
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] ablilty to test record for foreign key before
> deleting the record?
>
> On Tue, Nov 15, 2005 at 10:52:30AM -0600, Nathan Clark wrote:
> > Is there a way to check to see if a record has a foreign
> key, before I
> > try to delete a record ?
> > To check first to see if this record is tied to a foreign
> key before I
> > try to change it, thus avoiding
> > a foreign key error.
>
> Well, you could always try selecting on the child table...
>
> What are you actually trying to do? If you just want to avoid exposing
> the error you're probably better off just trapping for it in plpgsql.
> --
> Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
> Pervasive Software http://pervasive.com work: 512-231-6117
> vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2005-11-15 22:03:45 | Re: Congratulations on 8.1 |
Previous Message | Magnus Hagander | 2005-11-15 20:44:37 | Re: Permissions required for service user account _after_ installation |