Thread: pgsql: If an index depends on no columns of its table, give it a
pgsql: If an index depends on no columns of its table, give it a
From
tgl@postgresql.org (Tom Lane)
Date:
Log Message:
-----------
If an index depends on no columns of its table, give it a dependency on the
whole table instead, to ensure that it goes away when the table is dropped.
Per bug #3723 from Sam Mason.
Backpatch as far as 7.4; AFAICT 7.3 does not have the issue, because it doesn't
have general-purpose expression indexes and so there must be at least one
column referenced by an index.
Modified Files:
--------------
pgsql/src/backend/catalog:
index.c (r1.286 -> r1.287)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/catalog/index.c?r1=1.286&r2=1.287)
On Thu, 2007-11-08 at 23:22 +0000, Tom Lane wrote: > Log Message: > ----------- > If an index depends on no columns of its table, give it a dependency on the > whole table instead, to ensure that it goes away when the table is dropped. > Per bug #3723 from Sam Mason. > > Backpatch as far as 7.4; AFAICT 7.3 does not have the issue, because it doesn't > have general-purpose expression indexes and so there must be at least one > column referenced by an index. > I had understood the discussion to conclude that indexes that do not depend on any column of the table to not be allowed at all. Why would anyone want this? (There was a workaround to the original thought). What value is there in allowing such strange cases? -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
Simon Riggs <simon@2ndquadrant.com> writes:
> I had understood the discussion to conclude that indexes that do not
> depend on any column of the table to not be allowed at all.
That was my first reaction too, but the point about unique-index behavior
refutes it. Constraining a table to have at most one row is useful.
regards, tom lane
On Fri, 2007-11-09 at 04:05 -0500, Tom Lane wrote: > Simon Riggs <simon@2ndquadrant.com> writes: > > I had understood the discussion to conclude that indexes that do not > > depend on any column of the table to not be allowed at all. > > That was my first reaction too, but the point about unique-index behavior > refutes it. Constraining a table to have at most one row is useful. Sure is, and I've done it just a few days ago. This SQL does it using standard syntax: create table foo (handle integer primary key check (handle = 1)); It's also a lot more obvious than creating an index on a constant, which seems like a wrinkle that we should disallow. It just sounds to me like something that will break again in the future, so I'd rather disallow it now rather than here "but we need to support zero column indexes too". -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
Simon Riggs <simon@2ndquadrant.com> writes:
> On Fri, 2007-11-09 at 04:05 -0500, Tom Lane wrote:
>> That was my first reaction too, but the point about unique-index behavior
>> refutes it. Constraining a table to have at most one row is useful.
> Sure is, and I've done it just a few days ago.
> This SQL does it using standard syntax:
> create table foo (handle integer primary key check (handle = 1));
That does not constrain the table to have only one row. It constrains
it to have only one value of the handle field (thereby making the field
useless). The fact that there are workarounds isn't a reason to not
support the index option.
regards, tom lane
"Tom Lane" <tgl@sss.pgh.pa.us> writes: > Simon Riggs <simon@2ndquadrant.com> writes: > >> Sure is, and I've done it just a few days ago. > >> This SQL does it using standard syntax: > >> create table foo (handle integer primary key check (handle = 1)); > > That does not constrain the table to have only one row. It constrains > it to have only one value of the handle field (thereby making the field > useless). And that field is the primary key so... I think the point is that both of these solutions are reasonable solutions to the stated problem. If we can there's no reason to make only one of them legal. Actually there is one reason to prefer Simon's solution (which was the only approach I've seen before) -- it's standard SQL and should work on any database with check constraints and unique constraints. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning
On Fri, 2007-11-09 at 11:28 -0500, Tom Lane wrote: > Simon Riggs <simon@2ndquadrant.com> writes: > > On Fri, 2007-11-09 at 04:05 -0500, Tom Lane wrote: > >> That was my first reaction too, but the point about unique-index behavior > >> refutes it. Constraining a table to have at most one row is useful. > > > Sure is, and I've done it just a few days ago. > > > This SQL does it using standard syntax: > > > create table foo (handle integer primary key check (handle = 1)); > > That does not constrain the table to have only one row. It constrains > it to have only one value of the handle field (thereby making the field > useless). It works, sure you need another column to put data in. > The fact that there are workarounds isn't a reason to not > support the index option. The above is not a workaround. It is the SQL Standard way of solving the problem, so why support another non-standard way? Constants in indexes are just a strangeness we don't need. Supporting weird syntax because one person wants it has never been anything you've advocated before, so I'm surprised to see that argument deployed here. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
Simon Riggs <simon@2ndquadrant.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
On Sat, 2007-11-10 at 11:22 -0500, Tom Lane wrote: > Simon Riggs <simon@2ndquadrant.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
Simon Riggs <simon@2ndquadrant.com> writes:
> 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.
Well, if there's actually a reason to forbid it at some point, we can
reconsider. Right now there is no such reason. Furthermore, I rather
imagine that if there were a reason, the restriction it would require
would be a bit different than the one under discussion here. If there's
something we want to do that cannot work with index expressions that
happen to be constants, it seems unlikely that adding a partial index
predicate would suddenly make it start to work.
regards, tom lane
On Sat, Nov 10, 2007 at 07:31:39PM +0000, Simon Riggs wrote: > On Sat, 2007-11-10 at 11:22 -0500, Tom Lane wrote: > > Simon Riggs <simon@2ndquadrant.com> writes: > > > Constants in indexes are just a strangeness we don't need. > > > If I invented zero-column tables You don't have to. CREATE TABLE foo(); already works. As I understand it, this is there to allow people to rearrange tables completely--drop all the columns and replace them, for example--and not have that fail. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate