Re: Deleting orphan records

Does anybody have a method for deleting orphan (unreferenced) records? Is it possible to set up some sort of constraint or trigger (that performs well when lots of records are deleted ;) )?

---------------------------------
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now

Does ON DELETE CASCADE not work for you?

Other than that you would have do do outer join acrobatics ;-)

Best Regards;
Chris Travers
----- Original Message -----
From: John Smith
To: pgsql-general(at)postgresql(dot)org
Sent: Wednesday, February 05, 2003 12:55 PM
Subject: [GENERAL] Deleting orphan records

Does anybody have a method for deleting orphan (unreferenced) records? Is it possible to set up some sort of constraint or trigger (that performs well when lots of records are deleted ;) )?

------------------------------------------------------------------------------
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now

I was referring to parent records with no children ;).
John
Chris Travers <chris(at)travelamericas(dot)com> wrote:Does ON DELETE CASCADE not work for you? Other than that you would have do do outer join acrobatics ;-) Best Regards;Chris Travers----- Original Message ----- From: John Smith To: pgsql-general(at)postgresql(dot)org Sent: Wednesday, February 05, 2003 12:55 PMSubject: [GENERAL] Deleting orphan records
Does anybody have a method for deleting orphan (unreferenced) records? Is it possible to set up some sort of constraint or trigger (that performs well when lots of records are deleted ;) )?

---------------------------------
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now

---------------------------------
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now

On Wed, 5 Feb 2003, John Smith wrote:

>
> I was referring to parent records with no children ;).
> John
> Chris Travers <chris(at)travelamericas(dot)com> wrote:Does ON DELETE CASCADE not work for you? Other than that you would have do do outer join acrobatics ;-) Best Regards;Chris Travers----- Original Message ----- From: John Smith To: pgsql-general(at)postgresql(dot)org Sent: Wednesday, February 05, 2003 12:55 PMSubject: [GENERAL] Deleting orphan records
> Does anybody have a method for deleting orphan (unreferenced) records? Is it possible to set up some sort of constraint or trigger (that performs well when lots of records are deleted ;) )?
>

I'm was sure I'd seen mention of using an extra table in the delete statement
in the docs. However, I can't see it in 7.4dev. Going by that short description
though the following might be possible:

DELETE FROM table1
WHERE
NOT EXISTS ( SELECT FROM table2 WHERE table2.forkeycol = table1.forkeycol )

According the the docs the reader is directed to the SELECT page for details of
the WHERE clause and the above would be valid for a select.

--
Nigel J. Andrews

On Thu, Feb 06, 2003 at 07:36:47 +0000,
"Nigel J. Andrews" <nandrews(at)investsystems(dot)co(dot)uk> wrote:
>
> I'm was sure I'd seen mention of using an extra table in the delete statement
> in the docs. However, I can't see it in 7.4dev. Going by that short description

I was wondering about that. When you mentioned it I went and checked and didn't
find it so I thought maybe the extra tables were getting included automatically
which think select will do.

There is an extension to the update command to reference additional tables
and that might be what you were thinking of.

John Smith wrote:
> I was referring to parent records with no children ;).
>
> John

I think this sort of reference requirement is meant by the SQL keyword
PENDANT. Not sure what PENDANT ON DELETE CASCADE then is supposed to do,
but removing parents that became childless would be one plausible
interpretation.

PENDANT is not yet implemented in PostgreSQL.

And please, John, can you stop sending your plain text messages as
multipart mime encoded?

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

Are there plans to implement PENDANT?
http://www.ca.postgresql.org/projects/devel-refint.html
http://www.dbazine.com/mullins_triggers.html
http://developer.postgresql.org/cvsweb.cgi/pgsql-server/doc/TODO.detail/foreign.diff?r1=1.1&r2=1.2

Sorry about the MIME emails - can't figure out how to get Yahoo to send text (I see you use Yahoo - how's it done? ;) ).

John

Jan Wieck <JanWieck(at)Yahoo(dot)com> wrote:
John Smith wrote:
> I was referring to parent records with no children ;).
>
> John

I think this sort of reference requirement is meant by the SQL keyword
PENDANT. Not sure what PENDANT ON DELETE CASCADE then is supposed to do,
but removing parents that became childless would be one plausible
interpretation.

PENDANT is not yet implemented in PostgreSQL.

And please, John, can you stop sending your plain text messages as
multipart mime encoded?

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

---------------------------------
Do you Yahoo!?
Yahoo! Shopping - Send Flowers for Valentine's Day

John Smith wrote:
> Are there plans to implement PENDANT?

Not that I know of. I think you can work around it with a custom
triggers on UPDATE and DELETE on the child rows, that then do a DELETE
on the parent table using OLD.fkey (if OLD.fkey != NEW.fkey in the
UPDATE case of course).

> Sorry about the MIME emails - can't figure out how to get Yahoo to
> send text (I see you use Yahoo - how's it done? ;) ).

By having your own IMAP server on a Linux box at home, polling the mail
from Yahoo! and other accounts with fetchmail, filtering it for SPAM
with procmail and some Tcl scripts and then using Netscape with roaming
profile and SSL IMAP connections to access your mailboxes from wherever
you are. Since you need Apache for the roaming profile server anyway,
you may also install SquirrelMail so you can get at you mail from any
browser via https.

Sorry, you asked the wrong guy for this ;-)

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #