Skip site navigation (1) Skip section navigation (2)

Re: improving foreign key locks

From: Florian Pflug <fgp(at)phlo(dot)org>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Alvaro Herrera <alvherre(at)commandprompt(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: improving foreign key locks
Date: 2010-12-01 21:57:18
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
On Dec1, 2010, at 18:44 , Tom Lane wrote:
> Florian Pflug <fgp(at)phlo(dot)org> writes:
>> On Dec1, 2010, at 17:17 , Tom Lane wrote:
>>> There's not enough space in the infomask to record which columns (or
>>> which unique index) are involved.  And if you're talking about data that
>>> could remain on disk long after the unique index is gone, that's not
>>> going to be good enough.
>> We'd distinguish two cases
>>  A) The set of locked columns is a subset of the set of columns mentioned in
>>     *any* unique index. (In other words, for every locked column there is a
>>     unique index which includes that column, though not necessarily one index
>>     which includes them all)
>>  B) The set of locked columns does not satisfy (A)
>> Creating indices shouldn't pose a problem, since it would only enlarge the set of locked columns for rows with HEAP_XMAX_SHARED_LOCK_KEY set.
> Not with that definition.  I could create a unique index that doesn't
> contain some column that every previous unique index contained.
Hm, I seem to be unable to put the definition into unambiguous words. Let me try again.

Set A contains all columns for which an unique index exist which includes said column. More formally, if C_i is the set of columns included in the index I, then A is the union over all C_i with I ranging over the unique indices.

If for example index unique index I1 contains the columns (a,b) and unique index I2 contains the columns (b,c) (and no other unique indices exist) then the set contains the columns a,b,c. If there is an additional index I3 on (d), then A = {a,b,c,d}. If a row is SHARE locked and HEAP_XMAX_SHARED_LOCK_KEY is set, only these columns are considered to be locked. If the flag is not set, all columns are considered locked.

best regards,
Florian Pflug

In response to

pgsql-hackers by date

Next:From: Dimitri FontaineDate: 2010-12-01 22:00:48
Subject: Re: pg_execute_from_file review
Previous:From: Greg SmithDate: 2010-12-01 21:30:07
Subject: Re: Spread checkpoint sync

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group