Re: Minmax indexes

Erik Rijkers wrote:
> On Tue, September 17, 2013 23:03, Alvaro Herrera wrote:
>
> > [minmax-1.patch. + minmax-2-incr.patch. (and initdb)]
>
>
> The patches apply and compile OK.
>
> I've not yet really tested; I just wanted to mention that make check gives the following differences:

Oops, I forgot to update the expected file. I had to comment on this
when submitting minmax-2-incr.patch and forgot. First, those extra five
operators are supposed to be there; expected file needs an update. As
for this:

> --- 1277,1288 ----
> WHERE am.amname <> 'btree' AND am.amname <> 'gist' AND am.amname <> 'gin'
> GROUP BY amname, amsupport, opcname, amprocfamily
> HAVING count(*) != amsupport OR amprocfamily IS NULL;
> ! amname | opcname | count
> ! --------+-------------+-------
> ! minmax | int4_ops | 1
> ! minmax | text_ops | 1
> ! minmax | numeric_ops | 1
> ! (3 rows)

I think the problem is that the query is wrong. This is the complete query:

SELECT amname, opcname, count(*)
FROM pg_am am JOIN pg_opclass op ON opcmethod = am.oid
LEFT JOIN pg_amproc p ON amprocfamily = opcfamily AND
amproclefttype = amprocrighttype AND amproclefttype = opcintype
WHERE am.amname <> 'btree' AND am.amname <> 'gist' AND am.amname <> 'gin'
GROUP BY amname, amsupport, opcname, amprocfamily
HAVING count(*) != amsupport OR amprocfamily IS NULL;

I should be, instead, this:

SELECT amname, opcname, count(*)
FROM pg_am am JOIN pg_opclass op ON opcmethod = am.oid
LEFT JOIN pg_amproc p ON amprocfamily = opcfamily AND
amproclefttype = amprocrighttype AND amproclefttype = opcintype
WHERE am.amname <> 'btree' AND am.amname <> 'gist' AND am.amname <> 'gin'
GROUP BY amname, amsupport, opcname, amprocfamily
HAVING count(*) != amsupport AND (amprocfamily IS NOT NULL);

This query is supposed to check that there are no opclasses with
mismatching number of support procedures; but if the left join returns a
null-extended row for pg_amproc, that means there is no support proc,
yet count(*) will return 1. So count(*) will not match amsupport, and
the row is supposed to be excluded by the amprocfamily IS NULL clause in
HAVING.

Both queries return empty in HEAD, but only the second one correctly
returns empty with the patch applied.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services