Thread: Patch to add support for partial indices
[Note to list maintainer: I sent this yesterday but it got stuck. I've resent it now. If you find it in the queue, you may safely delete it.] This patch re-enables support for partial indices. This includes: * Updates to documentation * Added pg_dump support * Corrections to the grammer to accept the CREATE statement * Update old broken code each index type * Fixes to VACUUM so that it handles indices in general better * Updates to the optimiser and cost estimators to handle them * Initial steps to removal of EXTEND INDEX statement * Patches the regression tests Note that Tom Lane has already taken the portion that deals with vacuum as part of his new vacuum code. As for the extend index statement, it was decided to remove it since it was not really flexible enough, so in general you'd be better off just dropping the index and recreating it. For further discussion, see the pgsql-general list. The patch against anonymous CVS as of a day ago or so. Many thanks to Tom Lane for tips and pointers during the initial revisions of this patch. http://svana.org/kleptog/pgsql/partial-indices-7.2.patch -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > It would be nice if someone came up with a certification system that > actually separated those who can barely regurgitate what they crammed over > the last few weeks from those who command secret ninja networking powers.
Attachment
Your patch has been added to the PostgreSQL unapplied patches list at:
http://candle.pha.pa.us/cgi-bin/pgpatches
I will try to apply it within the next 48 hours.
> [Note to list maintainer: I sent this yesterday but it got stuck. I've
> resent it now. If you find it in the queue, you may safely delete it.]
>
> This patch re-enables support for partial indices. This includes:
>
> * Updates to documentation
> * Added pg_dump support
> * Corrections to the grammer to accept the CREATE statement
> * Update old broken code each index type
> * Fixes to VACUUM so that it handles indices in general better
> * Updates to the optimiser and cost estimators to handle them
> * Initial steps to removal of EXTEND INDEX statement
> * Patches the regression tests
>
> Note that Tom Lane has already taken the portion that deals with vacuum as
> part of his new vacuum code.
>
> As for the extend index statement, it was decided to remove it since it was
> not really flexible enough, so in general you'd be better off just dropping
> the index and recreating it.
>
> For further discussion, see the pgsql-general list. The patch against
> anonymous CVS as of a day ago or so.
>
> Many thanks to Tom Lane for tips and pointers during the initial revisions
> of this patch.
>
> http://svana.org/kleptog/pgsql/partial-indices-7.2.patch
> --
> Martijn van Oosterhout <kleptog@svana.org>
> http://svana.org/kleptog/
> > It would be nice if someone came up with a certification system that
> > actually separated those who can barely regurgitate what they crammed over
> > the last few weeks from those who command secret ninja networking powers.
[ Attachment, skipping... ]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
I am having trouble applying this patch. 8 of the 9 changes to vacuum.c failed to apply. > [Note to list maintainer: I sent this yesterday but it got stuck. I've > resent it now. If you find it in the queue, you may safely delete it.] > > This patch re-enables support for partial indices. This includes: > > * Updates to documentation > * Added pg_dump support > * Corrections to the grammer to accept the CREATE statement > * Update old broken code each index type > * Fixes to VACUUM so that it handles indices in general better > * Updates to the optimiser and cost estimators to handle them > * Initial steps to removal of EXTEND INDEX statement > * Patches the regression tests > > Note that Tom Lane has already taken the portion that deals with vacuum as > part of his new vacuum code. > > As for the extend index statement, it was decided to remove it since it was > not really flexible enough, so in general you'd be better off just dropping > the index and recreating it. > > For further discussion, see the pgsql-general list. The patch against > anonymous CVS as of a day ago or so. > > Many thanks to Tom Lane for tips and pointers during the initial revisions > of this patch. > > http://svana.org/kleptog/pgsql/partial-indices-7.2.patch > -- > Martijn van Oosterhout <kleptog@svana.org> > http://svana.org/kleptog/ > > It would be nice if someone came up with a certification system that > > actually separated those who can barely regurgitate what they crammed over > > the last few weeks from those who command secret ninja networking powers. [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Martijn van Oosterhout <kleptog@svana.org> writes:
> This patch re-enables support for partial indices.
Applied, with some further changes by myself. Many thanks for tackling
this ancient TODO item!
A few comments for future reference:
1. Apparently you didn't run the regression tests; if you had, you'd
have found at least one more bug (transformIndexStmt was broken for
indexes generated implicitly by CREATE TABLE). The regression tests
are a good thing to use for backend code development.
2. I didn't like the way you did the SELECT of the partial index
expression in pg_dump; it'd break down the first time a quote mark
showed up in a relation name or compiled qual expression. What I
did instead was
SELECT pg_get_expr(indpred,indrelid) as pred FROM pg_index WHERE oid = %s
which required changing the second parameter of pg_get_expr from text to
oid --- but that's more robust anyway. The text version would've had
issues as soon as we implement schemas.
3. A needed feature not yet implemented is for psql's \d commands to
show the predicate when displaying info about a partial index.
regards, tom lane
On Mon, Jul 16, 2001 at 01:20:01AM -0400, Tom Lane wrote: > Martijn van Oosterhout <kleptog@svana.org> writes: > > This patch re-enables support for partial indices. > > Applied, with some further changes by myself. Many thanks for tackling > this ancient TODO item! > > A few comments for future reference: > > 1. Apparently you didn't run the regression tests; if you had, you'd > have found at least one more bug (transformIndexStmt was broken for > indexes generated implicitly by CREATE TABLE). The regression tests > are a good thing to use for backend code development. Oops. I kept thinking that I should but somehow never actually did it. It's make regress, right? > 2. I didn't like the way you did the SELECT of the partial index > expression in pg_dump; it'd break down the first time a quote mark > showed up in a relation name or compiled qual expression. What I > did instead was True. I guess I was naively assuming that the compiled form wouldn't have any quotes in it. The compiled form doesn't actually show the actual relation names. > 3. A needed feature not yet implemented is for psql's \d commands to > show the predicate when displaying info about a partial index. I'll look at that. Can't be too difficult with the new get_expr function :) Now, if that's all in CVS, I can rediff my remove-extend patch and submit it, unless you beat me to it :) -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > It would be nice if someone came up with a certification system that > actually separated those who can barely regurgitate what they crammed over > the last few weeks from those who command secret ninja networking powers.
> > 3. A needed feature not yet implemented is for psql's \d commands to > > show the predicate when displaying info about a partial index. > > I'll look at that. Can't be too difficult with the new get_expr > function :) If you're in there - might be useful if normal indices were shown with the keys they're defined over, rather than just the index name. Chris
On Mon, Jul 16, 2001 at 05:06:44PM +0800, Christopher Kings-Lynne wrote: > > > 3. A needed feature not yet implemented is for psql's \d commands to > > > show the predicate when displaying info about a partial index. > > > > I'll look at that. Can't be too difficult with the new get_expr > > function :) > > If you're in there - might be useful if normal indices were shown with the > keys they're defined over, rather than just the index name. Well, I thought it'd be easy but a whole bunch of commands use the listTables function which is great if they all output the same. However, to add one column is not so easy. I mean, while I'm at it I could show the query defining a view. As for showing column names, I think you'd need a function to take a list of names returned by a query and turn it into an array. Maybe there is such a beast, but I havn't seen it yet. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > It would be nice if someone came up with a certification system that > actually separated those who can barely regurgitate what they crammed over > the last few weeks from those who command secret ninja networking powers.
On Mon, Jul 16, 2001 at 09:50:30PM +1000, Martijn van Oosterhout wrote:
> Well, I thought it'd be easy but a whole bunch of commands use the
> listTables function which is great if they all output the same. However, to
> add one column is not so easy. I mean, while I'm at it I could show the
> query defining a view.
Still not sure about that.
> As for showing column names, I think you'd need a function to take a list of
> names returned by a query and turn it into an array. Maybe there is such a
> beast, but I havn't seen it yet.
Well, what about this:
CREATE FUNCTION textjoin(text,text)
RETURNS text
AS 'SELECT CASE WHEN $1 IS NULL THEN $2 ELSE $1 || \', \' || $2 END;'
LANGUAGE 'sql';
CREATE AGGREGATE joinlist ( basetype = text, sfunc = textjoin, stype = text );
SELECT c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table'
WHEN 'v' THEN 'view'
WHEN 'i' THEN 'index'
WHEN 'S' THEN 'sequence'
WHEN 's' THEN 'special'
END as "Type",
u.usename as "Owner",
joinlist(attname::text) as "Attributes",
pg_get_expr(indpred,indrelid) as "Predicate"
FROM pg_class c LEFT JOIN pg_user u ON c.relowner = u.usesysid,
pg_index i, pg_attribute a
WHERE c.oid = i.indexrelid
AND i.indexrelid = a.attrelid
GROUP BY "Name", "Type", "Owner", "Predicate";
Produces an output like:
Name | Type | Owner | Attributes | Predicate
---------------------------------+-------+----------+-------------------------------------+----------------------
pg_aggregate_name_type_index | index | postgres | aggname, aggbasetype |
pg_am_name_index | index | postgres | amname |
pg_operator_oprname_l_r_k_index | index | postgres | oprname, oprleft, oprright, oprkind |
test2 | index | kleptog | clid | (billid < '3'::text)
Only much longer ofcourse. And with a bit more work you can also show if
it's a unique index or not. Where do you draw the line?
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
> It would be nice if someone came up with a certification system that
> actually separated those who can barely regurgitate what they crammed over
> the last few weeks from those who command secret ninja networking powers.
Martijn van Oosterhout <kleptog@svana.org> writes:
> Now, if that's all in CVS, I can rediff my remove-extend patch and submit
> it, unless you beat me to it :)
A lot of the EXTEND code is gone, but there are some loose ends still
(the ExtendStmt node type is still there, for example).
regards, tom lane
Martijn van Oosterhout <kleptog@svana.org> writes:
> Well, I thought it'd be easy but a whole bunch of commands use the
> listTables function which is great if they all output the same. However, to
> add one column is not so easy.
I was thinking of adding a line to the output for "\d index".
> I mean, while I'm at it I could show the
> query defining a view.
We already do -- try "\d view".
regards, tom lane
Patch applied by Tom. > [Note to list maintainer: I sent this yesterday but it got stuck. I've > resent it now. If you find it in the queue, you may safely delete it.] > > This patch re-enables support for partial indices. This includes: > > * Updates to documentation > * Added pg_dump support > * Corrections to the grammer to accept the CREATE statement > * Update old broken code each index type > * Fixes to VACUUM so that it handles indices in general better > * Updates to the optimiser and cost estimators to handle them > * Initial steps to removal of EXTEND INDEX statement > * Patches the regression tests > > Note that Tom Lane has already taken the portion that deals with vacuum as > part of his new vacuum code. > > As for the extend index statement, it was decided to remove it since it was > not really flexible enough, so in general you'd be better off just dropping > the index and recreating it. > > For further discussion, see the pgsql-general list. The patch against > anonymous CVS as of a day ago or so. > > Many thanks to Tom Lane for tips and pointers during the initial revisions > of this patch. > > http://svana.org/kleptog/pgsql/partial-indices-7.2.patch > -- > Martijn van Oosterhout <kleptog@svana.org> > http://svana.org/kleptog/ > > It would be nice if someone came up with a certification system that > > actually separated those who can barely regurgitate what they crammed over > > the last few weeks from those who command secret ninja networking powers. [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026