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>

 


Thank you. Roger.


Regards.

Hi Wolfgang,


I will look into this but I take your advice and leave it for later.


Best Regards,

Hans

Now that PostgreSQL supports multi-level partitions, would be nice if dbvis could export them.

I'm running dbvis 12.0.4 and when I do a table export ( only allowed at the highest level ), it only exports the first level partitions.


Thanks!

Dennis

Hi Hans,

I really would like to post a screenshot of how pgAdmin4 v.20 handles partitions,
but I do not know if this is legal :)
So here is what we would like to see: let table A be partitioned and B and C be partitions of A.
Then:
- only A shall be listed under "Tables", but B and C may *not*
- for A there is a navigation sub-item "Partitions", because A is partitioned
- Unfolding "Partitions" under the navigation item of A shall show B and C
  as partitions.
- for the partitioned table A you could show interesting things like number of partitions
- for the partitions B and C you could show at least the partition constraints
Furthermore imagine a partioning wizard that helps in creating non-overlapping partitions,
or an analyzer that checks if data are unevenly spread among the partitions ... just be
creative :)
Native Partitions is THE feature of PostgreSQL 10. Thus a good handling of partitions might
be the main thing Postgres 10 users expect from a db management tool.

Best regards
Wolfgang

 

Hi Wolfgang,


Thanks. We will look into it and let you know if we add more support for partitioned tables.


Best Regards,

Hans

Hi Roger,

thank you! I just installed 10.0.8 and it really looks good!

I have seen only a few minor flaws:

- 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 ...

- I can see the new partition view only if I log in to the database as a superuser. If the partitioned table is

  owned by user "test_schema" and inside the schema "test_schema" and I log in as user "test_schema"

  then the partitions are shown as regular tables and the partitioned table (the master of the partitions) is

  missing at all.


Regards,

Wolfgang


Wolfgang,


DbVisualizer 10.0.7 is now released with extended support for partitioned tables.


Open Help->Check for Update in DbVisualizer to perform the upgrade. 

An alternative is that you download from our web site: 


Regards

Roger

Hi Wolfgang,


I finally had some time to look at this. There seems to be some configuration issues, because in your installation, a profile named /opt/DbVisualizer/resources/profiles/postgresql.xml is used, when it instead should use one named /opt/DbVisualizer/resources/profiles/postgresql8.xml (the difference being the "8" in the filename). The one that gets used in your case is a very limited one that is intended for PostgreSQL versions older than version 8. Have you manually changed anything in your DbVisualizer installation? Which installer did you use when you installed DbVisualizer 10.0.8?


The choice of profile is based in the database version returned by the JDBC driver. I see another oddity in your log that indicates that you may have the wrong driver installed, namely that it throws a java.sql.SQLFeatureNotSupportedException for the Statement#getLargeUpdateCount() method. The driver bundled with DbVisualizer does not do this. Have you replaced the driver that comes bundled with DbVisualizer?


If none of this sounds familiar, I suggest that you uninstall DbVisualizer and reinstall the latest version (10.0.10). It is important that you first uninstall the current version, to get rid of potentially old files that have not been replaced when upgrading. Reinstalling DbVisualizer does not affect any user settings.


Best Regards,

Hans

Hi Roger,


thank you very much!


Regards

Wolfgang

Login or Signup to post a comment