From:
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To:
"Matthew T(dot) O'Connor" <matthew(at)zeut(dot)net>
Cc:
Simon Riggs <simon(at)2ndquadrant(dot)com>,
Alvaro Herrera <alvherre(at)commandprompt(dot)com>,
Heikki Linnakangas <heikki(at)enterprisedb(dot)com>,
Gregory Stark <stark(at)enterprisedb(dot)com>,
Stefan Kaltenbrunner <stefan(at)kaltenbrunner(dot)cc>,
Guillaume Smet <guillaume(dot)smet(at)gmail(dot)com>,
PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject:
Re: First steps with 8.3 and autovacuum launcher
Date:
2007-10-02 14:55:00
Message-ID:
14375.1191336900@sss.pgh.pa.us (view raw or flat )
Thread:
2007-09-18 16:51:04 from "Guillaume Smet" <guillaume(dot)smet(at)gmail(dot)com>
2007-09-19 03:30:05 from Decibel! <decibel(at)decibel(dot)org>
2007-09-19 07:08:58 from "Guillaume Smet" <guillaume(dot)smet(at)gmail(dot)com>
2007-09-19 14:53:29 from Decibel! <decibel(at)decibel(dot)org>
2007-09-22 10:06:57 from "Guillaume Smet" <guillaume(dot)smet(at)gmail(dot)com>
2007-09-22 15:38:52 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2007-09-22 16:17:37 from "Guillaume Smet" <guillaume(dot)smet(at)gmail(dot)com>
2007-09-30 18:26:46 from Stefan Kaltenbrunner <stefan(at)kaltenbrunner(dot)cc>
2007-10-01 13:58:44 from "Guillaume Smet" <guillaume(dot)smet(at)gmail(dot)com>
2007-10-01 15:44:02 from Stefan Kaltenbrunner <stefan(at)kaltenbrunner(dot)cc>
2007-10-01 17:15:08 from Gregory Stark <stark(at)enterprisedb(dot)com>
2007-10-01 17:35:41 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2007-10-01 17:42:41 from Heikki Linnakangas <heikki(at)enterprisedb(dot)com>
2007-10-01 19:59:16 from Alvaro Herrera <alvherre(at)commandprompt(dot)com>
2007-10-01 20:26:00 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2007-10-01 20:40:21 from "Matthew T(dot) O'Connor" <matthew(at)zeut(dot)net>
2007-10-01 20:50:07 from Alvaro Herrera <alvherre(at)commandprompt(dot)com>
2007-10-01 21:33:42 from Simon Riggs <simon(at)2ndquadrant(dot)com>
2007-10-01 22:53:40 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2007-10-01 22:56:55 from Alvaro Herrera <alvherre(at)commandprompt(dot)com>
2007-10-01 23:14:21 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2007-10-02 09:23:12 from Michael Paesold <mpaesold(at)gmx(dot)at>
2007-10-01 23:04:39 from "Matthew T(dot) O'Connor" <matthew(at)zeut(dot)net>
2007-10-01 23:28:02 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2007-10-01 23:35:36 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2007-10-01 23:43:45 from Alvaro Herrera <alvherre(at)commandprompt(dot)com>
2007-10-01 23:48:27 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2007-10-02 09:19:54 from Michael Paesold <mpaesold(at)gmx(dot)at>
2007-10-02 14:55:00 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2007-10-02 14:58:22 from Alvaro Herrera <alvherre(at)commandprompt(dot)com>
2007-10-02 15:17:47 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2007-10-03 14:02:28 from Alvaro Herrera <alvherre(at)commandprompt(dot)com>
2007-10-03 16:42:30 from Alvaro Herrera <alvherre(at)commandprompt(dot)com>
2007-10-03 23:21:09 from Alvaro Herrera <alvherre(at)commandprompt(dot)com>
2007-10-04 09:01:41 from Heikki Linnakangas <heikki(at)enterprisedb(dot)com>
2007-10-04 12:53:16 from "Guillaume Smet" <guillaume(dot)smet(at)gmail(dot)com>
2007-10-04 13:04:06 from Alvaro Herrera <alvherre(at)commandprompt(dot)com>
2007-10-04 15:07:02 from Gregory Stark <stark(at)enterprisedb(dot)com>
2007-10-04 15:10:31 from Simon Riggs <simon(at)2ndquadrant(dot)com>
2007-10-04 15:45:46 from Heikki Linnakangas <heikki(at)enterprisedb(dot)com>
2007-10-04 16:55:57 from Alvaro Herrera <alvherre(at)commandprompt(dot)com>
2007-10-04 17:30:55 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2007-10-04 18:33:16 from Alvaro Herrera <alvherre(at)commandprompt(dot)com>
2007-10-04 18:40:46 from Simon Riggs <simon(at)2ndquadrant(dot)com>
2007-10-04 19:32:07 from Simon Riggs <simon(at)2ndquadrant(dot)com>
2007-10-04 21:33:42 from Alvaro Herrera <alvherre(at)commandprompt(dot)com>
2007-10-05 12:27:54 from Simon Riggs <simon(at)2ndquadrant(dot)com>
2007-10-09 20:10:01 from Simon Riggs <simon(at)2ndquadrant(dot)com>
2007-10-10 03:52:15 from Alvaro Herrera <alvherre(at)commandprompt(dot)com>
2007-10-10 09:04:34 from Michael Paesold <mpaesold(at)gmx(dot)at>
2007-10-10 09:53:47 from Simon Riggs <simon(at)2ndquadrant(dot)com>
2007-10-10 10:17:43 from "Heikki Linnakangas" <heikki(at)enterprisedb(dot)com>
2007-10-10 10:48:56 from Simon Riggs <simon(at)2ndquadrant(dot)com>
2007-10-11 19:59:08 from Michael Paesold <mpaesold(at)gmx(dot)at>
2007-10-11 20:22:46 from Simon Riggs <simon(at)2ndquadrant(dot)com>
2007-10-12 05:24:29 from Alvaro Herrera <alvherre(at)commandprompt(dot)com>
2007-10-12 06:17:51 from Simon Riggs <simon(at)2ndquadrant(dot)com>
2007-10-12 06:40:25 from Simon Riggs <simon(at)2ndquadrant(dot)com>
2007-10-12 07:23:57 from Deblauwe Gino <gino(at)useitgroup(dot)com>
2007-10-12 09:08:21 from Michael Paesold <mpaesold(at)gmx(dot)at>
2007-10-12 14:19:57 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2007-10-12 15:02:02 from Simon Riggs <simon(at)2ndquadrant(dot)com>
2007-10-12 15:26:20 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2007-10-12 16:32:47 from Simon Riggs <simon(at)2ndquadrant(dot)com>
2007-10-12 16:42:48 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2007-10-12 17:30:30 from Simon Riggs <simon(at)2ndquadrant(dot)com>
2007-10-12 17:51:42 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2007-10-12 18:16:55 from Simon Riggs <simon(at)2ndquadrant(dot)com>
2007-10-12 09:44:20 from Michael Paesold <mpaesold(at)gmx(dot)at>
2007-10-12 11:00:54 from Simon Riggs <simon(at)2ndquadrant(dot)com>
2007-10-12 12:27:31 from Deblauwe Gino <gino(at)useitgroup(dot)com>
2007-10-04 18:42:10 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2007-10-11 13:57:22 from Gregory Stark <stark(at)enterprisedb(dot)com>
2007-10-04 14:27:52 from Simon Riggs <simon(at)2ndquadrant(dot)com>
2007-10-04 14:43:16 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2007-10-04 15:03:00 from Simon Riggs <simon(at)2ndquadrant(dot)com>
2007-10-01 15:57:03 from "Guillaume Smet" <guillaume(dot)smet(at)gmail(dot)com>
Lists:
pgsql-hackers
I wrote:
> Actually, if you wanted a low-hanging solution to that, it would
> probably be to revert this 8.2 patch:
> http://archives.postgresql.org/pgsql-committers/2006-09/msg00284.php
Uh ... never mind, that won't help at all. Reducing the lock taken for
ANALYZE could allow CREATE INDEX (ShareLock) to run concurrently. But
ALTER TABLE wants AccessExclusive lock, and no amount of fine-tuning of
the conflicting lock will help.
It might be possible to solve this if we reduce the strength of the lock
used for ALTER TABLE. We'd have to go through all the commands
potentially issued by a pg_dump script and see if they could all be made
to run concurrently with autovac, which is a bit nervous-making but
might be feasible; and I'm afraid tablecmds.c would need some
restructuring to not use the same lock type for every variant of ALTER.
But it seems like a path worth investigating.
regards, tom lane
In response to
Responses
pgsql-hackers by date
Next :From: Alvaro HerreraDate: 2007-10-02 14:58:22Subject : Re: First steps with 8.3 and autovacuum launcherPrevious :From : Michael MeskesDate : 2007-10-02 14:11:20Subject : Re: pgsql: ITAGAKI Takahiro<itagaki.takahiro@oss.ntt.co.jp> added
