Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Table comments are not displayed in postgresql #859

Closed
halmai opened this issue Jan 20, 2020 · 7 comments
Closed

Table comments are not displayed in postgresql #859

halmai opened this issue Jan 20, 2020 · 7 comments

Comments

@halmai
Copy link

@halmai halmai commented Jan 20, 2020

Steps to reproduce this issue

  1. Step 1; connect to an existing postgres database
  2. Step 2; check the structure of a table (Alt+Enter on the table name)
  3. Step 3; define a comment for a column with clicking the Comment column of the row of the field (or you can do it with the COMMENT ON COLUMN <table_name> IS 'whatever'; command as well)
  4. Then HeidiSql doesn't show the comment. Nor in this screen we opened with Alt+Enter nor on the "</> CREATE code" tab. All the comments are empty strings.

Current behavior

Column comments are not shown.

Expected behavior

Column comments are shown in both places.

  • HeidiSQL version:
    Version 10.3.0.5837 (64 Bit)
    Compiled on: 2020-01-19 19:55:50
    Environment: Windows v10.0

  • Database system and version:
    PostgreSQL 11.5 (Debian 11.5-1+deb10u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

  • Operating system:
    Window 10 Pro
    Version 10.0.18362 Build 18362

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

@ansgarbecker ansgarbecker commented Jan 20, 2020

Yes, I used to retrieve PG table columns with their comments from pg_description.description, before replacing the following query in 1344c10 by a simpler query on IS.columns:

SELECT 
    DISTINCT a.attname AS column_name,
  a.attnum,
  a.atttypid,
  FORMAT_TYPE(a.atttypid, a.atttypmod) AS data_type,
  CASE a.attnotnull WHEN false THEN 'YES' ELSE 'NO' END AS IS_NULLABLE,
  com.description AS column_comment,
  pg_get_expr(def.adbin, def.adrelid) AS column_default,
  NULL AS character_maximum_length
FROM pg_attribute AS a
JOIN pg_class AS pgc ON pgc.oid = a.attrelid
LEFT JOIN pg_description AS com ON (pgc.oid = com.objoid AND a.attnum = com.objsubid)
LEFT JOIN pg_attrdef AS def ON (a.attrelid = def.adrelid AND a.attnum = def.adnum)
WHERE
  a.attnum > 0
  AND pgc.oid = a.attrelid
  AND pg_table_is_visible(pgc.oid)
  AND NOT a.attisdropped
  AND pgc.relname = 'mytable'
ORDER BY a.attnum

I will have to override GetTableColumns now to get the comments back.

@halmai

This comment has been minimized.

Copy link
Author

@halmai halmai commented Jan 20, 2020

It would be awesome if you could put it back. Thanks. :)

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

@ansgarbecker ansgarbecker commented Jan 20, 2020

I just simplified the above query and used that to retrieve column comments. I just wonder if I need some "schema='currentschema'" WHERE filter?

SELECT a.attname AS colname, des.description
FROM pg_attribute AS a, pg_description AS des, pg_class AS pgc
WHERE
    pgc.oid = a.attrelid
    AND des.objoid = pgc.oid
    AND pg_table_is_visible(pgc.oid)
    AND pgc.relname = 'mytable'
    AND a.attnum = des.objsubid
@halmai

This comment has been minimized.

Copy link
Author

@halmai halmai commented Jan 21, 2020

Hi Angsar, thanks a lot for the quick response. :)

I created a one-column table in the public schema and another one in a second schema. I added comment to the only column of both of the tables.

Unfortunately, none of your queries returns the comment of the column of the second table. Both queries work in the current schema only. I am not sure whether this is an issue or not, maybe HeidiSQL sets the schema accordingly, in which case this is exactly the desired behavior.

Here is what I did:

CREATE SCHEMA other_schema;
CREATE TABLE public.mytable (x INT);
CREATE TABLE other_schema.mytable (x INT);
COMMENT ON COLUMN public.mytable.x IS 'foo';
COMMENT ON COLUMN other_schema.mytable.x IS 'bar';

then executed both your shorter and longer queries. Both returned foo. But after set search_path = other_schema;, both queries returned the comment bar.

If HeidiSQL sets the search path accordingly for each schema then this will be fine.

@KohlerCia

This comment has been minimized.

Copy link

@KohlerCia KohlerCia commented Jan 21, 2020

Hi halmai and Angsar.

I tested the small sql because i have the same problem here.
the small sql will work fine i believe
the line that identifies the current schema is AND pg_table_is_visible(pg_class.oid).
i sugested this change on my issue as well #842

@halmai

This comment has been minimized.

Copy link
Author

@halmai halmai commented Jan 21, 2020

Thanks, @KohlerCia for your feedback. Thank you both of you, guys. :)

@KohlerCia

This comment has been minimized.

Copy link

@KohlerCia KohlerCia commented Jan 21, 2020

Thanks, @KohlerCia for your feedback. Thank you both of you, guys. :)

The coments are working fine here on the latest compilation 5838

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
3 participants
You can’t perform that action at this time.