MichaelDBA/pg_get_tabledef

Missing Contraints...

NathanaelA opened this issue · 8 comments

NathanaelA commented

I have a table defined like so:

CREATE TABLE IF NOT EXISTS public.achievements
(
   achievements_id bigserial ,
   ...
  apps_id bigint NULL,
  communities_id bigint NULL,
  ...
)
...
CONSTRAINT achievements_achievement_identifier_apps_id_key UNIQUE (achievement_identifier, apps_id),
CONSTRAINT chk_only_one_not_null CHECK (num_nonnulls(apps_id, communities_id) = 1)

The other constraints are returned, however these two are not.
The ConType for the first one is 'u' and the second one is 'c'
and pg_get_constraintdef does return the proper definition for these constraints.

I'm calling the pg_get_tabledef with all triggers, external pkey, external foreign keys as I was trying to programatically be able to rebuild the table in a new database...

MichaelDBA commented

I have identified a problem is with using the "PKEY_EXTERNAL" option. Without specifying that option it works OK, Can you verify that it works with the first call below but not the second one?

select * from public.pg_get_tabledef('sample','achievements3', false, 'FKEYS_EXTERNAL', 'INCLUDE_TRIGGERS');
select * from public.pg_get_tabledef('sample','achievements3', false, 'PKEY_EXTERNAL','FKEYS_EXTERNAL', 'INCLUDE_TRIGGERS');

This is the DDL I am testing with to mimic your DDL:

CREATE TABLE IF NOT EXISTS public.achievements2
(
   achievements_id bigserial PRIMARY KEY,
   apps_id bigint NULL,
   communities_id bigint NULL,
   achievement_identifier text NULL,
CONSTRAINT achievements2_achievement_identifier_apps_id_key UNIQUE (achievement_identifier, apps_id),
CONSTRAINT achievements2_chk_only_one_not_null CHECK (num_nonnulls(apps_id, communities_id) = 1) );
  • 👍1
NathanaelA commented

Yes, removing the PKEY_EXTERNAL does cause those constraints to be added as part of the CREATE TABLE .

MichaelDBA commented

Had to rework a bunch of stuff to handle internal and external defs. Get the latest build and let me know how it goes.

Also, added another option to get table and column comments as well. See updated readme.

NathanaelA commented

Thanks for trying to quickly fix it -- unfortunately, you seem to have actually broke the normal FKEY_EXTERNAL (& maybe FKEY_INTERNAL ) with this push. Using or not using PKEY_EXTERNAL doesn't seem to effect the new issue.

Minor issue:

First if I use FKEY_INTERNAL it acts the same as if I used FKEY_EXTERNAL, only if I remove the FKEY_* from the parameters does it default to putting it internal to the create function. I never tested this in an earlier version, so not sure if this just broke, the only reason I tested it was because I swapped the EXTERNAL to INTERNAL to try and help isolate the issue and it didn't change anything.

Major issue,

if I use FKEY_EXTERNAL I get a duplicated key and I'm also missing an foreign key

CREATE  TABLE public.achievements (
  achievements_id bigserial ,
  achievement_identifier character varying(40) NOT NULL,
  apps_id bigint NULL,
  communities_id bigint NULL, 
  CONSTRAINT achievements_achievement_identifier_apps_id_key UNIQUE (achievement_identifier, apps_id),
  CONSTRAINT chk_only_one_not_null CHECK ((num_nonnulls(apps_id, communities_id) = 1))
) TABLESPACE pg_default;
ALTER TABLE ONLY public.achievements ADD CONSTRAINT achievements_pkey PRIMARY KEY (achievements_id);
ALTER TABLE ONLY public.achievements ADD CONSTRAINT achievements_apps_id_fkey FOREIGN KEY (apps_id) REFERENCES apps(apps_id);ALTER TABLE ONLY public.achievements ADD CONSTRAINT achievements_apps_id_fkey FOREIGN KEY (apps_id) REFERENCES apps(apps_id);

If you look closely at the last line, it is duplicated, it is missing the line feed in the output as it is concatenated together -- with a manually added line feed you can see it is the identical line easily:

ALTER TABLE ONLY public.achievements ADD CONSTRAINT achievements_apps_id_fkey FOREIGN KEY (apps_id) REFERENCES apps(apps_id);
ALTER TABLE ONLY public.achievements ADD CONSTRAINT achievements_apps_id_fkey FOREIGN KEY (apps_id) REFERENCES apps(apps_id);

PGAdmin & the prior version of your script does generate this missing foreign key...

    CONSTRAINT achievements_communities_id_fkey FOREIGN KEY (communities_id)
MichaelDBA commented

The dups I don't get. I fixed some other stuff. It looks good to me now. Try the latest build and PLEASE show me your exact syntax for calling pg_get_tabledef().

NathanaelA commented

I wish I had better news, but this version is still broken for the foreign keys.

I'm currently calling it like so (using PKEY or TRIGGERS doesn't seem to affect the FKEYS_EXTERNAL issue.

select public.pg_get_tabledef( 'public', 'achievements', false, 'FKEYS_EXTERNAL' )

and it is generating this (I'm copying/pasting exactly as pgadmin displays it)

CREATE  TABLE public.achievements (
  achievements_id bigserial ,
  achievement_identifier character varying(40) NOT NULL,
  apps_id bigint NULL,
  communities_id bigint NULL,
  name character varying(2048) NOT NULL,
  image character varying(4096) NOT NULL,
  status character varying(20) NOT NULL,
  icon character varying(4096) NULL,
  description character varying(4096) NULL,
  created_at timestamp without time zone NULL DEFAULT now(),
  CONSTRAINT achievements_pkey PRIMARY KEY (achievements_id),
  CONSTRAINT achievements_achievement_identifier_apps_id_key UNIQUE (achievement_identifier, apps_id),
  CONSTRAINT chk_only_one_not_null CHECK ((num_nonnulls(apps_id, communities_id) = 1))
) TABLESPACE pg_default;
ALTER TABLE ONLY public.achievements ADD CONSTRAINT achievements_apps_id_fkey FOREIGN KEY (apps_id) REFERENCES apps(apps_id);ALTER TABLE ONLY public.achievements ADD CONSTRAINT achievements_apps_id_fkey FOREIGN KEY (apps_id) REFERENCES apps(apps_id);

As you can see the ALTER TABLE statement at the bottom is duplicated and its still missing the second foreign key:
ALTER TABLE ONLY public.achievements ADD CONSTRAINT achievements_communities_id_fkey FOREIGN KEY (communities_id) REFERENCES communities(communities_id);
Which is returned by both PGAdmin, and the prior version of the script...

Notes:

  • It looks like you fixed "FKEY_INTERNAL" in this version and it does have the missing achievements_communities_id_fkey key in its output!
  • Using "PKEY_EXTERNAL" is also now working properly, but using or not using it has no effect on the missing & duplicated FKEY.
  • Enabling Debug outputs this and detects the achievements_communities_id_fkey key:
NOTICE:  arguments={FKEYS_EXTERNAL}
INFO:  arg=FKEYS_EXTERNAL
INFO:  (1)tabledef so far: <NULL>
INFO:  (col loop) name=achievements_id type=bigint udt_name=int8 udt_schema=pg_catalog
INFO:  (col loop) name=achievement_identifier type=character varying udt_name=varchar udt_schema=pg_catalog
INFO:  (col loop) name=apps_id type=bigint udt_name=int8 udt_schema=pg_catalog
INFO:  (col loop) name=communities_id type=bigint udt_name=int8 udt_schema=pg_catalog
INFO:  (col loop) name=name type=character varying udt_name=varchar udt_schema=pg_catalog
INFO:  (col loop) name=image type=character varying udt_name=varchar udt_schema=pg_catalog
INFO:  (col loop) name=status type=character varying udt_name=varchar udt_schema=pg_catalog
INFO:  (col loop) name=icon type=character varying udt_name=varchar udt_schema=pg_catalog
INFO:  (col loop) name=description type=character varying udt_name=varchar udt_schema=pg_catalog
INFO:  (col loop) name=created_at type=timestamp without time zone udt_name=timestamp udt_schema=pg_catalog

INFO:  (2)tabledef so far: CREATE  TABLE public.achievements (
  achievements_id bigserial ,
  achievement_identifier character varying(40) NOT NULL,
  apps_id bigint NULL,
  communities_id bigint NULL,
  name character varying(2048) NOT NULL,
  image character varying(4096) NOT NULL,
  status character varying(20) NOT NULL,
  icon character varying(4096) NULL,
  description character varying(4096) NULL,
  created_at timestamp without time zone NULL DEFAULT now(),

INFO:  DEBUG4: constraint name=achievements_pkey constraint_def=PRIMARY KEY (achievements_id)
INFO:  DEBUG4: constraint name=achievements_achievement_identifier_apps_id_key constraint_def=UNIQUE (achievement_identifier, apps_id)
INFO:  DEBUG4: constraint name=achievements_apps_id_fkey constraint_def=FOREIGN KEY (apps_id) REFERENCES apps(apps_id)
INFO:  DEBUG4: constraint name=achievements_communities_id_fkey constraint_def=FOREIGN KEY (communities_id) REFERENCES communities(communities_id)
INFO:  DEBUG4: constraint name=chk_only_one_not_null constraint_def=CHECK ((num_nonnulls(apps_id, communities_id) = 1))
INFO:  (3)tabledef so far: CREATE  TABLE public.achievements (
  achievements_id bigserial ,
  achievement_identifier character varying(40) NOT NULL,
  apps_id bigint NULL,
  communities_id bigint NULL,
  name character varying(2048) NOT NULL,
  image character varying(4096) NOT NULL,
  status character varying(20) NOT NULL,
  icon character varying(4096) NULL,
  description character varying(4096) NULL,
  created_at timestamp without time zone NULL DEFAULT now(),
  CONSTRAINT achievements_pkey PRIMARY KEY (achievements_id),
  CONSTRAINT achievements_achievement_identifier_apps_id_key UNIQUE (achievement_identifier, apps_id),
  CONSTRAINT chk_only_one_not_null CHECK ((num_nonnulls(apps_id, communities_id) = 1)),

INFO:  (4)tabledef so far: CREATE  TABLE public.achievements (
  achievements_id bigserial ,
  achievement_identifier character varying(40) NOT NULL,
  apps_id bigint NULL,
  communities_id bigint NULL,
  name character varying(2048) NOT NULL,
  image character varying(4096) NOT NULL,
  status character varying(20) NOT NULL,
  icon character varying(4096) NULL,
  description character varying(4096) NULL,
  created_at timestamp without time zone NULL DEFAULT now(),
  CONSTRAINT achievements_pkey PRIMARY KEY (achievements_id),
  CONSTRAINT achievements_achievement_identifier_apps_id_key UNIQUE (achievement_identifier, apps_id),
  CONSTRAINT chk_only_one_not_null CHECK ((num_nonnulls(apps_id, communities_id) = 1))

INFO:  (5)tabledef so far: CREATE  TABLE public.achievements (
  achievements_id bigserial ,
  achievement_identifier character varying(40) NOT NULL,
  apps_id bigint NULL,
  communities_id bigint NULL,
  name character varying(2048) NOT NULL,
  image character varying(4096) NOT NULL,
  status character varying(20) NOT NULL,
  icon character varying(4096) NULL,
  description character varying(4096) NULL,
  created_at timestamp without time zone NULL DEFAULT now(),
  CONSTRAINT achievements_pkey PRIMARY KEY (achievements_id),
  CONSTRAINT achievements_achievement_identifier_apps_id_key UNIQUE (achievement_identifier, apps_id),
  CONSTRAINT chk_only_one_not_null CHECK ((num_nonnulls(apps_id, communities_id) = 1))
) TABLESPACE pg_default;

INFO:  (6)tabledef so far: CREATE  TABLE public.achievements (
  achievements_id bigserial ,
  achievement_identifier character varying(40) NOT NULL,
  apps_id bigint NULL,
  communities_id bigint NULL,
  name character varying(2048) NOT NULL,
  image character varying(4096) NOT NULL,
  status character varying(20) NOT NULL,
  icon character varying(4096) NULL,
  description character varying(4096) NULL,
  created_at timestamp without time zone NULL DEFAULT now(),
  CONSTRAINT achievements_pkey PRIMARY KEY (achievements_id),
  CONSTRAINT achievements_achievement_identifier_apps_id_key UNIQUE (achievement_identifier, apps_id),
  CONSTRAINT chk_only_one_not_null CHECK ((num_nonnulls(apps_id, communities_id) = 1))
) TABLESPACE pg_default;
ALTER TABLE ONLY public.achievements ADD CONSTRAINT achievements_apps_id_fkey FOREIGN KEY (apps_id) REFERENCES apps(apps_id);ALTER TABLE ONLY public.achievements ADD CONSTRAINT achievements_apps_id_fkey FOREIGN KEY (apps_id) REFERENCES apps(apps_id);

INFO:  (7)tabledef so far: CREATE  TABLE public.achievements (
  achievements_id bigserial ,
  achievement_identifier character varying(40) NOT NULL,
  apps_id bigint NULL,
  communities_id bigint NULL,
  name character varying(2048) NOT NULL,
  image character varying(4096) NOT NULL,
  status character varying(20) NOT NULL,
  icon character varying(4096) NULL,
  description character varying(4096) NULL,
  created_at timestamp without time zone NULL DEFAULT now(),
  CONSTRAINT achievements_pkey PRIMARY KEY (achievements_id),
  CONSTRAINT achievements_achievement_identifier_apps_id_key UNIQUE (achievement_identifier, apps_id),
  CONSTRAINT chk_only_one_not_null CHECK ((num_nonnulls(apps_id, communities_id) = 1))
) TABLESPACE pg_default;
ALTER TABLE ONLY public.achievements ADD CONSTRAINT achievements_apps_id_fkey FOREIGN KEY (apps_id) REFERENCES apps(apps_id);ALTER TABLE ONLY public.achievements ADD CONSTRAINT achievements_apps_id_fkey FOREIGN KEY (apps_id) REFERENCES apps(apps_id);

INFO:  (8)tabledef so far: CREATE  TABLE public.achievements (
  achievements_id bigserial ,
  achievement_identifier character varying(40) NOT NULL,
  apps_id bigint NULL,
  communities_id bigint NULL,
  name character varying(2048) NOT NULL,
  image character varying(4096) NOT NULL,
  status character varying(20) NOT NULL,
  icon character varying(4096) NULL,
  description character varying(4096) NULL,
  created_at timestamp without time zone NULL DEFAULT now(),
  CONSTRAINT achievements_pkey PRIMARY KEY (achievements_id),
  CONSTRAINT achievements_achievement_identifier_apps_id_key UNIQUE (achievement_identifier, apps_id),
  CONSTRAINT chk_only_one_not_null CHECK ((num_nonnulls(apps_id, communities_id) = 1))
) TABLESPACE pg_default;
ALTER TABLE ONLY public.achievements ADD CONSTRAINT achievements_apps_id_fkey FOREIGN KEY (apps_id) REFERENCES apps(apps_id);ALTER TABLE ONLY public.achievements ADD CONSTRAINT achievements_apps_id_fkey FOREIGN KEY (apps_id) REFERENCES apps(apps_id);


Successfully run. Total query runtime: 80 msec.
1 rows affected.```
MichaelDBA commented

Thanks for hangin' in there with me. I think I fixed it this time. Please try latest build.

NathanaelA commented

Awesome that does seem to fix it. It looks good.