Re: WIP: generalized index constraints

On Thu, 2009-07-16 at 15:22 +1000, Brendan Jurd wrote:
> I had a play around with the feature in psql. I think the syntax is
> okay, but using "ALTER TABLE ... ADD" as you mentioned upthread could
> be a better option.

Ok, I think we're pretty much settled on that option then.

Another idea that I thought about is that:

ALTER TABLE foo ADD UNIQUE (a, b) USING foo_idx;

could be a shorthand for:

ALTER TABLE foo ADD INDEX CONSTRAINT (a =, b =) USING foo_idx;

The benefit is that it could go over GiST indexes or hash indexes, not
just btrees. The syntax could also be useful to turn an existing btree
into a unique btree.

> I noticed that there's no change to the output of \d in psql to show
> the constraint, so when I do a \d on my test table, I can see that
> there's a gist index there, but I can't tell that there is also a
> constraint on it. This seems like a pretty significant shortcoming.
> Essentially once you've created one of these index constraints, it
> vanishes into the catalogs and becomes invisible to the user. This
> might call for a modification of pg_get_indexdef()?

I agree, that's important. Psql support, regression tests, and docs are
all intertwined somewhat with the syntax, so I held off on that work
until I got a little feedback. I will get to work and see if I can put
together a more complete version in the next few days.

If you happen to have time, you can see if you can break my current
patch. I expect the basic algorithm to remain about the same for my next
version, so if you see any problems with that, please let me know. Also,
if you see any possible improvements that could make it useful for more
situations, that would be helpful, too.

But I think I have enough information to move forward, so if you want to
move on to a more complete patch, feel free.

Thanks for the review!

Regards,
Jeff Davis