From:
Jochem van Dieten <jochemd(at)gmail(dot)com>
To:
Gregory Maxwell <gmaxwell(at)gmail(dot)com>, Greg Stark <gsstark(at)mit(dot)edu>, Simon Riggs <simon(at)2ndquadrant(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>, pgsql-hackers(at)postgresql(dot)org
Subject:
Re: Reducing relation locking overhead
Date:
2005-12-02 22:41:15
Message-ID:
f96a9b830512021441u130ab9aap27700e287e7f38fc@mail.gmail.com (view raw or flat )
Thread:
2005-12-02 02:37:47 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2005-12-02 02:45:02 from Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>
2005-12-02 03:03:33 from Stephen Frost <sfrost(at)snowman(dot)net>
2005-12-02 06:44:08 from Greg Stark <gsstark(at)mit(dot)edu>
2005-12-02 07:14:37 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2005-12-02 14:53:27 from Simon Riggs <simon(at)2ndquadrant(dot)com>
2005-12-02 20:25:58 from Greg Stark <gsstark(at)mit(dot)edu>
2005-12-02 21:01:39 from Gregory Maxwell <gmaxwell(at)gmail(dot)com>
2005-12-02 22:04:23 from Alvaro Herrera <alvherre(at)commandprompt(dot)com>
2005-12-02 22:34:57 from Simon Riggs <simon(at)2ndquadrant(dot)com>
2005-12-02 22:45:10 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2005-12-03 09:10:54 from Simon Riggs <simon(at)2ndquadrant(dot)com>
2005-12-03 15:15:25 from Greg Stark <gsstark(at)mit(dot)edu>
2005-12-03 15:47:26 from Kevin Brown <kevin(at)sysexperts(dot)com>
2005-12-03 16:57:49 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2005-12-03 17:09:53 from Kevin Brown <kevin(at)sysexperts(dot)com>
2005-12-03 17:53:44 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2005-12-04 03:38:28 from "Qingqing Zhou" <zhouqq(at)cs(dot)toronto(dot)edu>
2005-12-04 03:51:12 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2005-12-04 17:13:28 from Kevin Brown <kevin(at)sysexperts(dot)com>
2005-12-04 18:32:56 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2005-12-04 20:15:37 from Kevin Brown <kevin(at)sysexperts(dot)com>
2005-12-05 03:07:38 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2005-12-05 06:40:55 from Kevin Brown <kevin(at)sysexperts(dot)com>
2005-12-05 13:38:17 from mark(at)mark(dot)mielke(dot)cc
2005-12-05 14:47:43 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2005-12-05 15:08:55 from Alvaro Herrera <alvherre(at)commandprompt(dot)com>
2005-12-05 16:28:43 from Kevin Brown <kevin(at)sysexperts(dot)com>
2005-12-05 01:27:03 from Greg Stark <gsstark(at)mit(dot)edu>
2005-12-03 16:29:43 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2005-12-08 06:16:42 from "Jim C(dot) Nasby" <jim(at)nasby(dot)net>
2005-12-08 06:57:42 from Hannu Krosing <hannu(at)skype(dot)net>
2005-12-08 07:08:10 from "Jim C(dot) Nasby" <jim(at)nasby(dot)net>
2005-12-08 09:58:50 from Hannu Krosing <hannu(at)skype(dot)net>
2005-12-08 18:57:02 from "Jim C(dot) Nasby" <jim(at)nasby(dot)net>
2005-12-08 22:00:14 from Hannu Krosing <hannu(at)skype(dot)net>
2005-12-08 23:03:21 from "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
2006-03-03 03:12:59 from Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
2006-03-03 22:46:39 from "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
2006-03-04 05:02:06 from Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
2005-12-02 22:41:15 from Jochem van Dieten <jochemd(at)gmail(dot)com>
2005-12-02 23:32:57 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2005-12-03 21:45:14 from Jochem van Dieten <jochemd(at)gmail(dot)com>
2005-12-03 15:23:06 from Greg Stark <gsstark(at)mit(dot)edu>
2005-12-03 16:30:31 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2005-12-03 19:01:02 from Greg Stark <gsstark(at)mit(dot)edu>
2005-12-08 06:04:59 from "Jim C(dot) Nasby" <jim(at)nasby(dot)net>
2005-12-03 13:47:36 from Robert Treat <xzilla(at)users(dot)sourceforge(dot)net>
2005-12-03 19:39:53 from Simon Riggs <simon(at)2ndquadrant(dot)com>
2005-12-03 20:16:27 from Alvaro Herrera <alvherre(at)commandprompt(dot)com>
2005-12-03 23:32:17 from Simon Riggs <simon(at)2ndquadrant(dot)com>
2005-12-08 20:34:03 from Ron Mayer <rm_pg(at)cheapcomplexdevices(dot)com>
2005-12-05 14:03:30 from Hannu Krosing <hannu(at)skype(dot)net>
2005-12-06 19:50:45 from Jochem van Dieten <jochemd(at)gmail(dot)com>
2005-12-06 20:12:40 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2005-12-06 20:29:27 from Hannu Krosing <hannu(at)skype(dot)net>
2005-12-06 20:41:28 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2005-12-06 20:58:06 from Hannu Krosing <hannu(at)skype(dot)net>
2005-12-06 21:01:57 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2005-12-06 21:07:45 from Hannu Krosing <hannu(at)skype(dot)net>
2005-12-07 18:01:35 from Jochem van Dieten <jochemd(at)gmail(dot)com>
2005-12-06 20:26:02 from Hannu Krosing <hannu(at)skype(dot)net>
2005-12-06 20:38:38 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2005-12-06 20:45:31 from Hannu Krosing <hannu(at)skype(dot)net>
2005-12-07 00:32:30 from Greg Stark <gsstark(at)mit(dot)edu>
2005-12-07 07:43:08 from Hannu Krosing <hannu(at)skype(dot)net>
2005-12-07 18:36:07 from Greg Stark <gsstark(at)mit(dot)edu>
2005-12-07 20:33:13 from Hannu Krosing <hannu(at)skype(dot)net>
2005-12-08 10:05:17 from Csaba Nagy <nagy(at)ecircle-ag(dot)com>
2005-12-08 09:14:34 from Simon Riggs <simon(at)2ndquadrant(dot)com>
2005-12-08 15:05:15 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2005-12-08 15:23:57 from Csaba Nagy <nagy(at)ecircle-ag(dot)com>
2005-12-08 18:25:26 from Simon Riggs <simon(at)2ndquadrant(dot)com>
2005-12-08 19:53:24 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2005-12-11 01:55:38 from Hannu Krosing <hannu(at)skype(dot)net>
2005-12-11 02:07:44 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2005-12-11 13:28:10 from Simon Riggs <simon(at)2ndquadrant(dot)com>
2005-12-11 18:33:57 from Hannu Krosing <hannu(at)skype(dot)net>
2005-12-12 14:02:50 from Alvaro Herrera <alvherre(at)commandprompt(dot)com>
Lists:
pgsql-hackers
On 12/2/05, Alvaro Herrera wrote:
> Gregory Maxwell wrote:
>>
>> After you're mostly caught up, change locking behavior to block
>> further updates while the final catchup happens. This could be driven
>> by a hurestic that says make up to N attempts to catch up without
>> blocking, after that just take a lock and finish the job. Presumably
>> the catchup would be short compared to the rest of the work.
>
> The problem is that you need to upgrade the lock at the end of the
> operation. This is very deadlock prone, and likely to abort the whole
> operation just when it's going to finish. Is this a showstopper? Tom
> seems to think it is. I'm not sure anyone is going to be happy if they
> find that their two-day reindex was aborted just when it was going to
> finish.
How about the following sceanrio for building a new index:
- create an empty index
- flag it as incomplete
- commit it so it becomes visible to new transactions
- new transactions will update the index when inserting / updating
- the planner will not use it for queries because it is flagged as incomplete
- wait until the the index is visible to all running transactions
- start a new seqscan and insert all records in the index
- commit
- remove the incomplete flag
Wouldn't this overcome the lock upgrade problem?
Jochem
In response to
Responses
pgsql-hackers by date
Next :From: Bruce MomjianDate: 2005-12-02 22:41:34Subject : Re: Numeric 508 datatypePrevious :From : Tom LaneDate : 2005-12-02 22:39:34Subject : Re: Numeric 508 datatype
