Postgres Pro
Downloads
Home   >   mailing lists

Thread: Tools showing table partitions as tables in listings of tables

Tools showing table partitions as tables in listings of tables

From
Niels Jespersen
Date:

Hello all

 

We are very happy with the ongoing work on partitioning i Postgres 11+. We use it in a number of cases.

 

A small irritation point is that some tools decide that partitions under a table are to be shown in a list of tables, sometimes drowning the main table in a sea of partitions.

 

PgAdmin shows partitions below the main table, Datagrip also. Other tools do not. The ones that I have heard about is the SAS System and Enterprise Architect, both connecting through ODBC. I am aware that the issue is really that these tools and/or the ODBC Driver issues queries against the catalog and these queries are naive in respect to table partitions.

 

Any ideas for working around this? Naming partitions in a special manner, something else. It's not the end of the world, but quite irritating for some users.

 

Regards

 

Niels Jespersen

 

 

Re: Tools showing table partitions as tables in listings of tables

From
hubert depesz lubaczewski
Date:
On Wed, Dec 09, 2020 at 12:29:43PM +0000, Niels Jespersen wrote:
> A small irritation point is that some tools decide that partitions under a table are to be shown in a list of tables,
sometimes
> drowning the main table in a sea of partitions.

While this doesn't answer your question directly, but when I had this
problem, I simply moved partitions to separate schema.

So, let's say I have table plans in schema public. All its partitions
are in "plans" schema.

Of course naming can be done differently, but the thing is that you can
do it, and it doesn't cause any issues with queries - as long as you use
main table and not directly partitions.

Best regards,

depesz

SV: Tools showing table partitions as tables in listings of tables

From
Niels Jespersen
Date:
>> A small irritation point is that some tools decide that partitions 
>> under a table are to be shown in a list of tables, sometimes drowning the main table in a sea of partitions.
>
>While this doesn't answer your question directly, but when I had this problem, I simply moved partitions to separate
schema.
>So, let's say I have table plans in schema public. All its partitions are in "plans" schema.
>Of course naming can be done differently, but the thing is that you can do it, and it doesn't cause any issues with
queries- as long as you use main table and not directly partitions.
 
>
>Best regards,
>
>depesz
>
I had not thought of that. Nice trick. I think we have a standard security setup that revolves around schemas, that may
suffermore than this helps. I will think about that some more. 
 

Thank you for the tip. 

Regards Niels