pg_indexes doesn't show indexes for partitioned tables - bug or intended?

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

pg_indexes doesn't show indexes for partitioned tables - bug or intended?

Thomas Kellerer
In Postgres 11.2, indexes defined on partitioned tables do not show up in pg_indexes (the actual indexes for the partitions however do show up).

E.g.:

    CREATE TABLE base_table
    (
      column1 varchar(50) NOT NULL,
      column2 integer NOT NULL,
      column3 integer not null,
      part_key bigint NOT NULL
    )
    PARTITION BY HASH (part_key);

    CREATE UNIQUE INDEX idx_one ON base_table (column1, column2, part_key);

The following select returns nothing:

  select *
  from pg_indexes
  where tablename = 'base_table';

This is caused by the fact that pg_indexes only returns information for regular tables and materialized views ("relkind in ('r','m')") and regular indexes (relkind = 'i')

If the conditions on the relkind for the "table class" to include 'p' as well, and the relkind for the "index class" is changed to return 'i' and 'I', then those indexes are listed in pg_indexes as well:

 SELECT n.nspname AS schemaname,
    c.relname AS tablename,
    i.relname AS indexname,
    t.spcname AS tablespace,
    pg_get_indexdef(i.oid) AS indexdef
   FROM pg_index x
     JOIN pg_class c ON c.oid = x.indrelid
     JOIN pg_class i ON i.oid = x.indexrelid
     LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_tablespace t ON t.oid = i.reltablespace
  WHERE (c.relkind in ('r','m','p')) --<< add 'p' to the list
    AND i.relkind in ('i', 'I') --<< add 'I' to the list



Is leaving out the indexes defined on the partitioned table intended or a bug?

Regards
Thomas



Reply | Threaded
Open this post in threaded view
|

Re: pg_indexes doesn't show indexes for partitioned tables - bug or intended?

David Rowley-3
On Thu, 11 Apr 2019 at 00:39, Thomas Kellerer <[hidden email]> wrote:
>
> In Postgres 11.2, indexes defined on partitioned tables do not show up in pg_indexes (the actual indexes for the partitions however do show up).

> Is leaving out the indexes defined on the partitioned table intended or a bug?

Overlooked for PG11. You'll see them in PG12 per [1]. It's not really
possible to backpatch a fix for that since these views are created
during initdb.

[1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f94cec64476f2752e91b10d7928a2fcd105e9fc3

--
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


Reply | Threaded
Open this post in threaded view
|

Re: pg_indexes doesn't show indexes for partitioned tables - bug or intended?

Alvaro Herrera-9
On 2019-Apr-11, David Rowley wrote:

> On Thu, 11 Apr 2019 at 00:39, Thomas Kellerer <[hidden email]> wrote:
> >
> > In Postgres 11.2, indexes defined on partitioned tables do not show up in pg_indexes (the actual indexes for the partitions however do show up).
>
> > Is leaving out the indexes defined on the partitioned table intended or a bug?
>
> Overlooked for PG11. You'll see them in PG12 per [1]. It's not really
> possible to backpatch a fix for that since these views are created
> during initdb.

(You can, of course, adjust the view definition yourself.)

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


Reply | Threaded
Open this post in threaded view
|

Re: pg_indexes doesn't show indexes for partitioned tables - bug or intended?

Thomas Kellerer
In reply to this post by David Rowley-3
David Rowley schrieb am 10.04.2019 um 17:57:
>> In Postgres 11.2, indexes defined on partitioned tables do not show up in pg_indexes (the actual indexes for the partitions however do show up).
>
>> Is leaving out the indexes defined on the partitioned table intended or a bug?
>
> Overlooked for PG11. You'll see them in PG12 per [1]. It's not really
> possible to backpatch a fix for that since these views are created
> during initdb.

Thanks.

I don't undertand though why it's not back patched - at least that would fix the bug for new installations

Thomas


Reply | Threaded
Open this post in threaded view
|

Re: pg_indexes doesn't show indexes for partitioned tables - bug or intended?

Tom Lane-2
Thomas Kellerer <[hidden email]> writes:
> David Rowley schrieb am 10.04.2019 um 17:57:
>> Overlooked for PG11. You'll see them in PG12 per [1]. It's not really
>> possible to backpatch a fix for that since these views are created
>> during initdb.

> I don't undertand though why it's not back patched - at least that would fix the bug for new installations

David overstated the situation --- it'd be *possible* to back-patch a
fix for that, if we thought that the bug was of sufficient importance.
But the costs of such changes are way way higher than "change a couple
of lines in system_views.sql".  We would also have to write documentation
about how to fix it manually, along the lines of the first bullet point in
[1], and then users would either have to jump through that hoop or decide
that it wasn't worth it to them (which it wouldn't be, for most).  And,
having different behaviors in different "v11" installations is not really
all that nice, especially for something that's only debatably a bug.
So I concur with the decision not to back-patch.

                        regards, tom lane

[1] https://www.postgresql.org/docs/9.6/release-9-6-5.html