From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
Cc: | Noah Misch <noah(at)leadboat(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: foreign key locks, 2nd attempt |
Date: | 2012-01-31 17:12:10 |
Message-ID: | CA+TgmoYprk=6VJ-tDVgafYg1Nkz-CExxChmiX1R4ojm7Z00=Dg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Tue, Jan 31, 2012 at 11:58 AM, Alvaro Herrera
<alvherre(at)commandprompt(dot)com> wrote:
> Well, we're already storing a multixact in Xmax, so it's not like we
> don't assume that we can store multis in space normally reserved for
> Xids. What I've been wondering is not how ugly it is, but rather of the
> fact that we're bloating pg_class some more.
I don't think another 4 bytes in pg_class is that big a deal. We
don't do relcache rebuilds frequently enough for that to really matter
much. The bigger cost of this patch seems to me to be that we're
going to have to carry around multi-xact IDs for a long time, and
probably fsync and/or WAL-log them moreso than now. I'm not sure how
much you've worried about that, but a new column in pg_class seems
like line noise by comparison.
>> What about SELECT FOR UPDATE? That's a pretty common case, I think.
>> If that's now going to force a multixact to get created and
>> additionally force multixact lookups when the row is subsequently
>> examined, that seems, well, actually pretty scary at first glance.
>> SELECT FOR UPDATE is fairly expensive as it stands, and is commonly
>> used.
>
> SELECT FOR UPDATE is still going to work without a multi in the simple
> cases. The case where it's different is when somebody else grabs a KEY
> SHARE lock on the same tuple; it's now going to get a multi, where it
> previously blocked. So other transactions later checking the tuple will
> have a bit of a larger cost. That's okay considering that it meant
> the other transaction did not have to wait anymore.
OK. I assume that the different treatment of SELECT FOR SHARE is due
to lack of bit space?
>> >> 2. What algorithm did we end up using do fix the set of key columns,
>> >> and is there any user configuration that can or needs to happen there?
>> >
>> > Currently we just use all columns indexed by unique indexes (excluding
>> > expressional and partial ones). Furthermore we consider "key column"
>> > all columns in a table without unique indexes. Noah disagrees with this
>> > choice; he says we should drop this last point, and that we should relax
>> > the first to "columns actually used by foreign key constraints". I
>> > expect that this is a rather simple change.
>>
>> Why the special case for tables without unique indexes? Like Noah, I
>> don't see the point. Unless there's some trade-off I'm not seeing, we
>> should want the number of key columns to be as minimal as possible, so
>> that as many updates as possible can use the "cheap" path that doesn't
>> involve locking the whole tuple.
>
> No trade-off. I just thought it was safer: my thought was that if
> there's no nominated key column, the safer bet was that any of them
> could have been. But then, in reality there cannot be any foreign key
> here anyway. I'll revert that bit.
OK.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2012-01-31 17:35:58 | Re: Index-only scan performance regression |
Previous Message | Lionel Elie Mamane | 2012-01-31 17:11:00 | Re: information schema/aclexplode doesn't know about default privileges |