Primary keys not styled differently

[yissachar]

When I generate a diagram there is no indication as to which fields are the primary keys for the tables. As far as I can tell from the examples this should be indicated by the field being bold and italicized but that isn't happening for me.

Here is the command I am using:

./schemacrawler.sh -server=postgresql -url=jdbc:postgresql://localhost:5432/mydb -schemas=public -portablenames -command=schema -outputformat=png -outputfile=database-diagram.png

I'm running this on a Postgres 9.6 database and using version 14.20.05 of SchemaCrawler.

[sualeh]

Please zip and attach the SchemaCrawler debug logs, which can be obtained by running SchemaCrawler with an additional -loglevel=ALL command-line argument.

[yissachar]

Here is an image of the problem:

And the logs: logs.txt.zip

[sualeh]

Please run the following SQL statement, and let me know what it returns. If your schema is not called public, please change the name of the schema it returns.

SELECT
  NULL AS table_cat,
  n.nspname AS table_schem,
  ct.relname AS table_name,
  a.attname AS column_name,
  (i.keys).n AS key_seq,
  ci.relname AS pk_name
FROM pg_catalog.pg_class ct
JOIN pg_catalog.pg_attribute a
  ON (ct.oid = a.attrelid)
JOIN pg_catalog.pg_namespace n
  ON (ct.relnamespace = n.oid)
JOIN (SELECT
  i.indexrelid,
  i.indrelid,
  i.indisprimary,
  information_schema._pg_expandarray(i.indkey) AS keys
FROM pg_catalog.pg_index i) i
  ON (a.attnum = (i.keys).x
    AND a.attrelid = i.indrelid)
JOIN pg_catalog.pg_class ci
  ON (ci.oid = i.indexrelid)
WHERE TRUE
AND n.nspname = 'public'
AND i.indisprimary
ORDER BY
  table_name,
  pk_name,
  key_seq

[sualeh]

Or, you could search for your primary key in this:

SELECT
  *
FROM
  INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
  CONSTRAINT_TYPE = 'PRIMARY KEY'

[sualeh]

@yissachar - did you check your database for primary keys using the SQL I gave you?

[yissachar]

Hi @sualeh, sorry for the delay.

This is the result I got from your first query:

table_cat, table_schem, table_name, column_name, key_seq, pk_name
            "public"        "test"   "id"        1    "test_pkey"

[sualeh]

Let me try to reproduce. It seems like there is a known bug with the Postgres JDBC driver which has still not been fixed. SchemaCrawler may need to work around that.

Meanwhile, please send me the results of:

SELECT
  *
FROM
  INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
  CONSTRAINT_TYPE = 'PRIMARY KEY'

[sualeh]

I cannot reproduce your issue. Please send me your CREATE TABLE statement.

[yissachar]

Constraints:

constraint_catalog, constraint_schema, constraint_name, table_catalog, table_schema, table_name, constraint_type, is_deferrable, initially_deferred
"mydb"                    "public"    "test_pkey"       "mydb"    "public"   "test"   "PRIMARY KEY""NO" "NO"

Create table statement:

CREATE TABLE test
(
    id character varying NOT NULL,
    foo character varying,
    CONSTRAINT test_pkey PRIMARY KEY (id)
)

[sualeh]

I cannot reproduce it. I will put in some more logging into the next version, so you can see what is going on.

I am using:

• SchemaCrawler 14.21.01
• PostgreSQL JDBC Driver 42.2.2
• PostgreSQL 9.6.6

[sualeh]

PS: I also tried with PostgreSQL 9.4.7, and could not reproduce. (You are using PostgreSQL 9.4.4) Please use the next SchemaCrawler version, where I will put in some more logging.

[yissachar]

I wonder if it is an OS issue? I am on Mac, but a coworker on Windows was not able to reproduce. I will try with the next version, and also try to upgrade my DB to 9.6 which is what I was supposed to be using.

[sualeh]

That could well be the case. It may be that Graphviz works differently on a Mac.