Skip site navigation (1) Skip section navigation (2)

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
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 16:22:53
Message-ID: 15946.1194711773@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-committers
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.

			regards, tom lane

In response to

Responses

pgsql-committers by date

Next:From: User JurkaDate: 2007-11-10 17:52:59
Subject: jdbc - CVSROOT: Attempt to point commit messages to our own mailing
Previous:From: Bruce MomjianDate: 2007-11-10 16:15:24
Subject: pgsql: Use strip -x on OS/X-darwin because non-"-x" causes link

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group