[v14beta] RE of system triggers (postgresql)

[MaXyM]

While testing database Reverse Engineering feature called Show Differences in Database Builder against postgresql database, I noticed that it retrieves logical replication triggers to add them to the model.
While replication triggers are system triggers and shouldn't be considered as model part, those should be excluded.

I narrowed the problem down to the SQL query which retrieves trigger data:

Code: [Select]

Select ns.nspname       As SchemaName,   -- 1
         tab.relname      As TableName,   -- 2
         trg.tgname             As TriggerName, -- 3
         pg_get_triggerdef(trg.oid)       As TriggerDef,  -- 4
         COALESCE(pgdesc.description, '') As Remarks,           -- 5
         trg.*
  From pg_trigger trg
  Inner Join pg_class tab ON trg.tgrelid = tab.oid
  Inner Join pg_namespace ns ON (tab.relnamespace = ns.oid)
  Left Join pg_description pgdesc  ON pgdesc.objoid = trg.oid
  Where trg.tgconstrrelid = 0
  And ns.nspname = 'schema_name'
  And tab.relname = 'table_name' 
Please note condition: trg.tgconstrrelid - it excludes other system triggers which plays a role of relation constraint watchers. To exclude all system triggers the condition  should use trg.tgisinternal = FALSE. Like this:

Code: [Select]

Select ns.nspname       As SchemaName,   -- 1
         tab.relname      As TableName,   -- 2
         trg.tgname             As TriggerName, -- 3
         pg_get_triggerdef(trg.oid)       As TriggerDef,  -- 4
         COALESCE(pgdesc.description, '') As Remarks,           -- 5
         trg.*
  From pg_trigger trg
  Inner Join pg_class tab ON trg.tgrelid = tab.oid
  Inner Join pg_namespace ns ON (tab.relnamespace = ns.oid)
  Left Join pg_description pgdesc  ON pgdesc.objoid = trg.oid
  Where NOT trg.tgisinternal
  And ns.nspname = 'schema_name'
  And tab.relname = 'table_name'
Now, I'm going to report the issue official way. But I want to ask, is it possible to improve this SQL query by my self? Or is it hardcoded and I have to wait for fix (or write the script which will remove all unneeded triggers)

with regards

[Geert Bellekens]

I think this query if hardcoded and you won't be able to change it by yourself.

But you could definitely write a script to get rid of the unwanted triggers.

Geert