Re: [HACKERS] Proposal: Local indexes for partitioned table

Hi Alvaro,

On 01/04/2018 09:30 AM, Alvaro Herrera wrote:
> v11 fixes the dependency problem I mentioned in
> https://postgr.es/m/20171229203818.pqxf2cyl4g2wre6h@alvherre.pgsql
> and adds some test to cover that stuff.
>

Thanks, make check-world passes.

Maybe a warning for existing indexes on the same column(s), but with a
different type, should be emitted during ATTACH, e.g.

-- test.sql --
CREATE TABLE test (a integer NOT NULL) PARTITION BY HASH(a);
CREATE TABLE test_p00 PARTITION OF test FOR VALUES WITH (MODULUS 2,
REMAINDER 0);
CREATE TABLE test_p01 PARTITION OF test FOR VALUES WITH (MODULUS 2,
REMAINDER 1);
CREATE INDEX idx_test_a ON test (a);

INSERT INTO test (SELECT i FROM generate_series(1, 1000000) AS i);

ANALYZE;

ALTER TABLE test DETACH PARTITION test_p00;
DROP INDEX test_p00_a_idx;
CREATE INDEX test_p00_a_idx ON test_p00 USING hash (a);
ALTER TABLE test ATTACH PARTITION test_p00 FOR VALUES WITH (MODULUS 2,
REMAINDER 0);

-- test.sql --

Of course, this could also fall under index maintenance and no warning
emitted. Docs have "Each partition is first checked to determine whether
an equivalent index already exists," so it is covered.

Best regards,
Jesper