Start a new topic

postgresql 10 beta - partition table

PostgreSQL version 10(beta) supports partitioned tables.

Table attribute 'p' has been added.

The parent table is not visible.


so.


It works after modifying as below.

      <Command id="postgresql8.getTables">
         <SQL>
            <![CDATA[
SELECT c.relname                                     AS "Name",
       n.nspname                                     AS "Schema",
       pg_get_userbyid(c.relowner)                   AS "Owner",
       t.spcname                                     AS "Tablespace",
       c.relhasindex                                 AS "Has Indexes",
       c.relhasrules                                 AS "Has Rules",
       ]]>
            <If test="#util.isDatabaseVersionGTE(8,4)">
               <![CDATA[
       c.relhastriggers                               AS "Has Triggers",
       ]]>
            </If>
            <Else>
               <![CDATA[
       (c.reltriggers > 0)                            AS "Has Triggers",
       ]]>
            </Else>
            <![CDATA[
       c.relisshared                                 AS "Is Shared",
       c.relnatts                                    AS "N Cols",
       pg_catalog.obj_description(c.oid, 'pg_class') AS "Comment"
FROM   pg_class c
   LEFT JOIN pg_namespace n   ON n.oid = c.relnamespace
   LEFT JOIN pg_tablespace t  ON t.oid = c.reltablespace
WHERE 1=1 
]]>
<If test="#util.isDatabaseVersionGTE(10,0)">
AND c.relkind in( 'r'::"char", 'p'::"char")
</If>
 <Else>
AND c.relkind in( 'r'::"char")
 </Else>
<![CDATA[
AND   n.nspname LIKE '${schema}'
AND   c.relname LIKE '${table}'
ORDER BY n.nspname, c.relname
            ]]>
         </SQL>
      </Command>

 


PostgreSQL 10 was released just a week ago, and we have not formally tested with it yet or looked at if there are any new features we should add support for. Once we've done that, the Supported Databases page will be updated to show formal support also for version 10. 


The features developed for earlier versions are more than likely to work fine also with PostgreSQL 10. We provide database-specific support for many different databases and add new features for new database version based on requests from our customers. If you find that we do not support something added in PostgreSQL 10, let us know and we will look into it.


Best Regards,

Hans


1 person likes this

Hi Wolfgang,


I can reproduce the first issue, i.e. that the Columns tab is empty, and will look into that.


However, I'm not able to reproduce the second issue. I have a schema named "test" in a database named "postgres". I connect with a user named "test" and create a partitioned table. Looking at the Info tab for this table I see that it is owned by user "test" and is part of the "test" schema. It shows up in the Databases tree as a table and its Partitions node list the partitions. Am I doing something different than the case you describe?


Best Regards,

Hans

Hi Wolfgang,


The issue with the empty Columns tab is due to the bundled JDBC driver version. It is fixed in the latest version, 42.2.1, available here:


https://jdbc.postgresql.org/download.html


We will look into bundling the latest driver version with the next maintenance release. If you want to upgrade now, create a new Driver Manager Entry for it e.g. named PostgreSQL 42.2.1 and select that driver for your connection, as described here:


http://confluence.dbvis.com/display/UG100/Installing+a+JDBC+Driver


I cannot reproduce the second issue as I said earlier so your input about what I may be missing is welcome.


Best Regards,

Hans

Can I buy dbvis with PostgreSQL 10 support?

 

Hi Konstantin,


The change discussed here is included in DbVisualizer 10.0, available for purchase now. Is there some other PostgreSQL 10 feature you would like to see supported? 


Best Regards,

Hans

Hi!

But on the page http://www.dbvis.com/features/ there is only PostgreSQL 8,9;
at http://www.dbvis.com/doc/postgresql-database-features/ there is no mention of new features. Release Notes 10.0, 10.0.1, 10.0.2 also do not contain PostgreSQL 10. Are they obsolete pages?

 

Thank you.

 

Postgres 10 now allows for native partitioning. We will use this feature within a project in a
big german company. Currently, dbvis 10.0.4 does not recognize a partitioned table and handles
partitions just like regular tables. Thus we currently recommed pgAdmin4 v2.0 which correctly
subsumes all partitions under its parent table and subpartions under their partition and so on.
Maybe you want to add some cool partition features to dbvisualizer :)

Best regards
Wolfgang
 

 

 Hi Hans,


I have produced the debug log you wanted. See attachment.

The log protocols the following actions:

- connect to the database

- open the database navigation item (AMK173 in this case)

- open the "TABLES" item


But do not waste too much time on this - it's not urgent :)


Best regards

Wolfgang

log

Hi Wolfgang,


It would be great if you can briefly describe what features you would find helpful, so we can look into it. We made a small change a few versions back to list the partitioned tables along with regular tables, at the suggestion of another customer. I assume you would like to see them separated from regular tables with different actions and views?


Best Regards,

Hans

Wolfgang,


Just to inform you that the just released DbVisualizer 10.0.14 now fixes this that you reported a while ago:


- if you click on the partitioned table in the navigator, you can see the columns of the table in a tree view.

  But when you open this table in the database window (the big window on the right side), the "Columns"

  tab does not show the columns. No big deal, but this seems to be wrong ...


Regards


Roger

Thanks. We're currently running a beta testing of the upcoming 10.0 version. We'll include this in the next beta version.


Regards


Roger

Hi Hans,

here is how I reproduce the second problem:

CREATE TABLE amk_schema.measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);

CREATE TABLE amk_schema.measurement_01 PARTITION OF amk_schema.measurement
    FOR VALUES FROM ('2017-01-01') TO ('2018-01-01');
   
CREATE TABLE amk_schema.measurement_00 PARTITION OF amk_schema.measurement
    FOR VALUES FROM ('2016-01-01') TO ('2017-01-01');
   
insert into amk_schema.measurement (city_id, logdate, peaktemp, unitsales) values (17, '2017-05-04', 30, 100);


-- I even do that:
grant all on amk_schema.measurement to amk_schema;
grant all on amk_schema.measurement_01 to amk_schema;
grant all on amk_schema.measurement_00 to amk_schema;


After all, I am happy when I open the database as user "postgres", but when I open it as "amk_schema"

I only see the partitions "measurement_00" and "measurement_01" in the navigation pane. But not the

parent table "measurement".


Best regards,

Wolfgang

Hi Wolfgang,


Strange. I'm still not able to reproduce this. I have tested creating the table and partitions both with postgres as the owner and with my test schema as the owner. In both cases, I see the table under the Tables nodes and its partitions under the Partitions node. My test schema, corresponding to your "ask_schema" is named "test" and I have also used this GRANT statement if that makes a difference:


grant all privileges on schema test to test


Please open Tools->Debug Window and enable debugging for DbVisualizer. Click on the garbage can button to remove old output. Then perform this operation again and include the debug output in your reply. 


I did find another bug, namely that the database qualifier in the DDL for the partitions is incorrectly set to the owner instead of the database. This will be fixed in the next maintenance release.


Best Regards,

Hans

Login or Signup to post a comment