Deleting, indexes and transactions - pgsql.general

The old Google Groups will be going away soon, but your browser is incompatible with the new version.
Message from discussion Deleting, indexes and transactions
Path: pr3ni59315pbb.0!nntp.google.com!news2.google.com!npeer01.iad.highwinds-media.com!news.highwinds-media.com!feed-me.highwinds-media.com!nx01.iad01.newshosting.com!newshosting.com!novia!news-out.readnews.com!transit4.readnews.com!216.40.28.145.MISMATCH!novia!proxad.net!feeder1-1.proxad.net!nntpfeed.proxad.net!78.192.0.157.MISMATCH!news.le-studio75.com!news.hub.org!postgresql.org!pgsql-general-owner+M187680
From: adrian.kla...@gmail.com (Adrian Klaver)
Newsgroups: pgsql.general
Subject: Re: [GENERAL] Deleting, indexes and transactions
Date: Mon, 28 May 2012 10:41:55 -0700
Organization: Hub.Org Networking Services
Lines: 62
Sender: n...@news.hub.org
Message-ID: <4FC3B8E3.8010907@gmail.com>
References: <CAAUywg__VyUuE+_Z8MMgEKzraGH=soHapOOcNoBgPWPxRBV82Q@mail.gmail.com> <4FC38E40.2040608@gmail.com> <CAAUywg9LSZAc1O6O7Kr9Z7LJHQtd++5S8qF4YwNrEExdOWs53A@mail.gmail.com>
NNTP-Posting-Host: news.hub.org
Mime-Version: 1.0
NNTP-Posting-Date: Mon, 28 May 2012 17:43:32 +0000 (UTC)
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

On 05/28/2012 08:46 AM, Stevo SlaviÄ‡ wrote:
> Hello Adrian,
>
> Thanks for replying!
>
> I guess in this case, referential action, from your quote, on deleting
> As is check that there are no Bs referencing to-be-deleted A row. But
> since all Bs are deleted (not committed yet though) prior to deleting
> As, I don't understand why is this check taking that long time. Doesn't
> this transaction, that both deleting Bs and As belong to, have enough
> "knowledge" if not to skip this check then to at least have it executed
> faster? It seems, in case without index, that postgres is executing this
> referential integrity check sequentially over B data as if they were not
> deleted, it just skips raising error because it ultimately after long
> time finds B records are about to be deleted. It would be faster if
> postgres had a structure/info on transaction level which would allow it
> to execute following (sequential) queries/checks only over rows which
> haven't been marked for deletion - I guess that would add complexity.
> With index I guess postgres does same logic just uses index to lookup Bs
> referencing to-be-deleted A much faster, and then determines Bs have
> been marked for deletion and doesn't raise error. I wonder how other
> RDBMS behave in this case.

FKs, as I understand it, are basically system triggers. The exact method 
by which they work and the effect of indexes on that are beyond me at 
this point. Others may have more insight.

>
> Anyway, regarding your second question, cascade delete hasn't been
> applied or tried yet. Case I've initially explained is one subcase of
> actual case that needs to be supported which is to sync As with an
> external source, which unfortunatelly doesn't provide info whether Bs
> have been changed or not for given A. So, there are two subcases, one
> where almost all data is dropped (As and Bs) and replaced with new,
> while in other subcase just some As data gets added while some As are
> deleted. In either case, we need to drop all Bs and add them because of
> lack of information of changes in Bs. Will check and see how that
> performs for both scenarios.

Well the issue seems to be with what you do to A not B. The FK is on B 
but the reference is to A and when you do an action on A in it needs to 
verify the state of the referring rows in B. By explicitly specifying a 
course of action (ON DELETE CASCADE) you streamline the process in the 
first case. In the second case it not as big an issue because you are 
only changing a small subset of A.

>
> Kind regards,
> Stevo.
>



-- 
Adrian Klaver
adrian.kla...@gmail.com

-- 
Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Create a group - Google Groups - Google Home - Terms of Service - Privacy Policy
Account Options
