Re: pgsql: If an index depends on no columns of its table, give it a

From: Simon Riggs <simon(at)2ndquadrant(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-committers(at)postgresql(dot)org
Subject: Re: pgsql: If an index depends on no columns of its table, give it a
Date: 2007-11-10 19:31:39
Message-ID: 1194723099.4251.552.camel@ebony.site
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-committers

On Sat, 2007-11-10 at 11:22 -0500, Tom Lane wrote:
> Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> > Constants in indexes are just a strangeness we don't need.
>
> I disagree. Here's an example that I don't think you can do with
> purely SQL-spec syntax:
>
> CREATE UNIQUE INDEX fooi ON foo ((1)) WHERE f1 < 0;
>
> This constrains the table to contain no more than one row with
> negative f1. Now admittedly this index does depend on the column f1
> so it's not directly an example of the case being patched, but I think
> it would be pretty weird to allow this but reject the base case
> without a WHERE clause.
>
> I also think that there's no principled reason to reject
> CREATE INDEX fooi ON foo ((1));
> if we allow
> CREATE INDEX fooi ON foo ((CASE WHEN false THEN f1 ELSE 1 END));
> The second index is certainly without rational use, but on what
> grounds will you argue that it's more valid than the other?
>
> Basically, my view is that this may be an edge case, but it's not
> utterly useless, and throwing an error for it will violate the
> principle of least surprise. It's not hugely different from the
> reasoning that led us to allow zero-column tables.

If I invented zero-column tables or constant indexes your reply would be
swift and unprintable. :-) You definitely can do the above using
multiple partial indexes, if you chose, but if you really want this, and
it looks like you do, that's cool.

My only fear is your reminder at a later date that we can't add feature
X because of constant indexes and 8.4 is going to be all about indexes.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com

In response to

Responses

Browse pgsql-committers by date

  From Date Subject
Next Message Tom Lane 2007-11-10 19:44:42 Re: pgsql: If an index depends on no columns of its table, give it a
Previous Message Tom Lane 2007-11-10 19:29:54 pgsql: Add missing closing / in xsd:restriction, and remove some