From: Andrew Sullivan <andrew@libertyrms.info>
To: PgSQL Performance ML <pgsql-performance@postgresql.org>
Subject: Re: schema/db design wrt performance
Date: Thu, 16 Jan 2003 11:46:18 -0500
Message-ID: <20030116114618.G32288@mail.libertyrms.com> (raw)
In-Reply-To: <1042734336.892.42.camel@haggis>
References: <20030116080132.A4962-100000@megazone23.bigpanda.com>
<1042734336.892.42.camel@haggis>
On Thu, Jan 16, 2003 at 10:25:36AM -0600, Ron Johnson wrote:
>
> 2 points:
>
> 1. Don't you *want* TXN2 to wait on TXN1?
Not really. You really just want a tag which prevents TXN2 from
committing when its reference data might go away. So what you want
is a lock which says "don't delete, no matter what", until TXN2
commits. Then TXN1 could fail or not, depending on what it's trying
to do. The problem is that there isn't a lock of the right strength
to do that.
> 2. In an OLTP environment (heck, in *any* environment), the goal
> is to minimize txn length, so TXN2 shouldn't be waiting on
> TXN1 for more than a fraction of a second anyway.
Right. But it's possible to have multiple REFERENCES constraints
to the same table; that's why I picked an account table, for
instance, because you might have a large number of different kinds of
things that the same account can do. So while you're correct that
one wants to minimize txn length, it's also true that, when the
effects are followed across a large system, you can easily start
tripping over the FKs. The real problem, then, only shows up on a
busy system with a table which gets referenced a lot.
I should note, by the way, that the tremendous performance
improvements available in 7.2.x have reduced the problem considerably
from 7.1.x, at least in my experience.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: andrew@libertyrms.info
Subject: Re: schema/db design wrt performance
In-Reply-To: <20030116114618.G32288@mail.libertyrms.com>
References: <20030116080132.A4962-100000@megazone23.bigpanda.com> <1042734336.892.42.camel@haggis> <20030116114618.G32288@mail.libertyrms.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox