Re: clustering by partial indexes

Quoting Tom Lane <tgl@sss.pgh.pa.us>:

> "Keith C. Perry" <netadmin@vcsn.com> writes:
> > This might have been discussed before but I wanted to know if clustering
> tables
> > by partial indexes will be availble in a later release of pgSQL?
>
> What in the world would it mean to do that?

I'm not sure I understand your question.  Right now you can cluster tables based
on an index but when I tried to do that with a partial index I got an error.
That information was in my first email.  Perhaps a more basic question why can't
I cluster by a partial index.  Here is the information again cut & pasted from
pgadminIII v1.4 (I omitted the table ddl previously).

CREATE TABLE report
(
  "match" int4 NOT NULL,
  "action" varchar(16),
  stamp timestamptz NOT NULL,
  account varchar(32),
  ipaddress inet,
  profile varchar(16),
  rating text,
  url text,
  CONSTRAINT report_pkey PRIMARY KEY ("match", stamp)
)
WITHOUT OIDS;


CREATE INDEX hrs_idx
  ON report
  USING btree
  (stamp)
  WHERE thehour(stamp) >= 0::double precision AND thehour(stamp) <= 23::double
precision;


CREATE OR REPLACE FUNCTION thehour(timestamptz)
  RETURNS float8 AS
$BODY$
begin
  return extract(hour from $1);
end;
$BODY$
  LANGUAGE 'plpgsql' IMMUTABLE;

Now when I go into the database with psql...

Welcome to psql 8.1.0, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

iprism=# \h cluster
Command:     CLUSTER
Description: cluster a table according to an index
Syntax:
CLUSTER indexname ON tablename
CLUSTER tablename
CLUSTER

iprism=# cluster hrs_idx on report;
ERROR:  cannot cluster on partial index "hrs_idx"
iprism=#



--
Keith C. Perry, MS E.E.
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com

____________________________________
This email account is being host by:
VCSN, Inc : http://vcsn.com