Error when execute the query to extract pk violators: ERROR #42601 syntax error at or near \"FROM\"

janpio commented 4 years ago

mock-windows-amd64-v2.4.exe database -a localhost -w foo -f -v -d discourse
...
Fixing PRIMARY KEYS violation  93% [=============================================>    ] (123/131) [8s:0s]time="2020-03-08 16:43:17" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing PRIMARY KEYS violation  94% [==============================================>   ] (124/131) [8s:0s]time="2020-03-08 16:43:17" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing PRIMARY KEYS violation  95% [==============================================>   ] (125/131) [8s:0s]time="2020-03-08 16:43:17" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing PRIMARY KEYS violation  96% [===============================================>  ] (126/131) [8s:0s]time="2020-03-08 16:43:17" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing PRIMARY KEYS violation  96% [===============================================>  ] (127/131) [8s:0s]time="2020-03-08 16:43:17" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing PRIMARY KEYS violation  97% [===============================================>  ] (128/131) [8s:0s]time="2020-03-08 16:43:17" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing PRIMARY KEYS violation  98% [================================================> ] (129/131) [8s:0s]time="2020-03-08 16:43:17" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing PRIMARY KEYS violation  99% [================================================> ] (130/131) [8s:0s]time="2020-03-08 16:43:17" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing PRIMARY KEYS violation 100% [==================================================] (131/131) [8s:0s]
time="2020-03-08 16:43:17" level=info msg="Found 238 violation of UNIQUE KEYS, attempting to fix them" file="constraintsRestore.go:26"
time="2020-03-08 16:43:17" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing UNIQUE KEYS violation   0% [                                                  ] (1/238) [0s:8s]time="2020-03-08 16:43:17" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing UNIQUE KEYS violation   0% [                                                  ] (2/238) [0s:10s]time="2020-03-08 16:43:18" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing UNIQUE KEYS violation   1% [                                                  ] (3/238) [0s:11s]time="2020-03-08 16:43:18" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing UNIQUE KEYS violation   1% [                                                  ] (4/238) [0s:11s]time="2020-03-08 16:43:18" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing UNIQUE KEYS violation   2% [>                                                 ] (5/238) [0s:11s]time="2020-03-08 16:43:18" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing UNIQUE KEYS violation   2% [>                                                 ] (6/238) [0s:11s]time="2020-03-08 16:43:18" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing UNIQUE KEYS violation   2% [>                                                 ] (7/238) [0s:11s]time="2020-03-08 16:43:18" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
time="2020-03-08 16:43:18" level=debug msg="Extracting constraint column data type info for table: \"public\".\"categories\"" file="sql.go:371"
time="2020-03-08 16:43:18" level=debug msg="Extracting the unique violations for table \"public\".\"categories\" and column email_in" file="sql.go:424"
Fixing UNIQUE KEYS violation   3% [>                                                 ] (8/238) [0s:16s]time="2020-03-08 16:43:18" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"

time="2020-03-08 16:43:18" level=debug msg="query: SELECT COUNT(*) FROM ( SELECT COALESCE(parent_category_id, '-1'::integer FROM \"public\".\"categories\" GROUP BY COALESCE(parent_category_id, '-1'::integer HAVING COUNT(*) > 1 ) a" file="sql.go:410"
time="2020-03-08 16:43:18" level=fatal msg="Error when execute the query to extract pk violators: ERROR #42601 syntax error at or near \"FROM\"" file="sql.go:411"

Schema is https://github.com/prisma/database-schema-examples/blob/master/postgres/discourse/schema.sql

janpio commented 4 years ago

Another one:

mock-windows-amd64-v2.4.exe database -a localhost -w foo -f -v -d mastodon
...
Fixing PRIMARY KEYS violation  92% [=============================================>    ] (61/66) [4s:0s]time="2020-03-08 16:58:38" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing PRIMARY KEYS violation  93% [=============================================>    ] (62/66) [4s:0s]time="2020-03-08 16:58:38" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing PRIMARY KEYS violation  95% [==============================================>   ] (63/66) [4s:0s]time="2020-03-08 16:58:38" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing PRIMARY KEYS violation  96% [===============================================>  ] (64/66) [4s:0s]time="2020-03-08 16:58:38" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing PRIMARY KEYS violation  98% [================================================> ] (65/66) [4s:0s]time="2020-03-08 16:58:39" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing PRIMARY KEYS violation 100% [==================================================] (66/66) [4s:0s]
time="2020-03-08 16:58:39" level=info msg="Found 113 violation of UNIQUE KEYS, attempting to fix them" file="constraintsRestore.go:26"
time="2020-03-08 16:58:39" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing UNIQUE KEYS violation   0% [                                                  ] (1/113) [0s:3s]time="2020-03-08 16:58:39" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing UNIQUE KEYS violation   1% [                                                  ] (2/113) [0s:5s]time="2020-03-08 16:58:39" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing UNIQUE KEYS violation   2% [>                                                 ] (3/113) [0s:5s]time="2020-03-08 16:58:39" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing UNIQUE KEYS violation   3% [>                                                 ] (4/113) [0s:8s]time="2020-03-08 16:58:39" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing UNIQUE KEYS violation   4% [=>                                                ] (5/113) [0s:8s]time="2020-03-08 16:58:39" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing UNIQUE KEYS violation   5% [=>                                                ] (6/113) [0s:7s]time="2020-03-08 16:58:39" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing UNIQUE KEYS violation   6% [==>                                               ] (7/113) [0s:7s]time="2020-03-08 16:58:39" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing UNIQUE KEYS violation   7% [==>                                               ] (8/113) [0s:7s]time="2020-03-08 16:58:39" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing UNIQUE KEYS violation   7% [==>                                               ] (9/113) [0s:7s]time="2020-03-08 16:58:39" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing UNIQUE KEYS violation   8% [===>                                              ] (10/113) [0s:7s]time="2020-03-08 16:58:39" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing UNIQUE KEYS violation   9% [===>                                              ] (11/113) [0s:7s]time="2020-03-08 16:58:39" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing UNIQUE KEYS violation  10% [====>                                             ] (12/113) [0s:6s]time="2020-03-08 16:58:39" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing UNIQUE KEYS violation  11% [====>                                             ] (13/113) [0s:6s]time="2020-03-08 16:58:39" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing UNIQUE KEYS violation  12% [=====>                                            ] (14/113) [1s:6s]time="2020-03-08 16:58:40" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing UNIQUE KEYS violation  13% [=====>                                            ] (15/113) [1s:7s]time="2020-03-08 16:58:40" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing UNIQUE KEYS violation  14% [======>                                           ] (16/113) [1s:6s]time="2020-03-08 16:58:40" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing UNIQUE KEYS violation  15% [======>                                           ] (17/113) [1s:6s]time="2020-03-08 16:58:40" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing UNIQUE KEYS violation  15% [======>                                           ] (18/113) [1s:6s]time="2020-03-08 16:58:40" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"

time="2020-03-08 16:58:40" level=debug msg="query: SELECT COUNT(*) FROM ( SELECT lower((username FROM \"public\".\"accounts\" GROUP BY lower((username HAVING COUNT(*) > 1 ) a" file="sql.go:410"
time="2020-03-08 16:58:40" level=fatal msg="Error when execute the query to extract pk violators: ERROR #42601 syntax error at or near \"FROM\"" file="sql.go:411"

Schema: https://github.com/prisma/database-schema-examples/blob/master/postgres/mastodon/schema.sql

janpio commented 4 years ago

And one more:

mock-windows-amd64-v2.4.exe database -a localhost -w foo -f -v -d odoo
...
Fixing UNIQUE KEYS violation  94% [==============================================>   ] (246/261) [14s:0s]time="2020-03-08 19:06:29" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing UNIQUE KEYS violation  94% [==============================================>   ] (247/261) [14s:0s]time="2020-03-08 19:06:29" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing UNIQUE KEYS violation  95% [==============================================>   ] (248/261) [14s:0s]time="2020-03-08 19:06:29" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing UNIQUE KEYS violation  95% [==============================================>   ] (249/261) [14s:0s]time="2020-03-08 19:06:29" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing UNIQUE KEYS violation  95% [==============================================>   ] (250/261) [15s:0s]time="2020-03-08 19:06:29" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing UNIQUE KEYS violation  96% [===============================================>  ] (251/261) [15s:0s]time="2020-03-08 19:06:29" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing UNIQUE KEYS violation  96% [===============================================>  ] (252/261) [15s:0s]time="2020-03-08 19:06:29" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing UNIQUE KEYS violation  96% [===============================================>  ] (253/261) [15s:0s]time="2020-03-08 19:06:29" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
Fixing UNIQUE KEYS violation  97% [===============================================>  ] (254/261) [15s:0s]time="2020-03-08 19:06:29" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"

time="2020-03-08 19:06:29" level=debug msg="query: SELECT COUNT(*) FROM ( SELECT type, lang, md5(src FROM \"public\".\"ir_translation\" GROUP BY type, lang, md5(src HAVING COUNT(*) > 1 ) a" file="sql.go:410"
time="2020-03-08 19:06:29" level=fatal msg="Error when execute the query to extract pk violators: ERROR #42601 syntax error at or near \"FROM\"" file="sql.go:411"

Schema: https://github.com/prisma/database-schema-examples/blob/master/postgres/odoo/schema.sql

janpio commented 4 years ago

For discourse the ) of the coalesce is missing.
For mastodon, the lower has two ( and is also missing the ).
For odoo, it's the md5 that is missing the ).

I'll try to create a PR for this.

janpio commented 4 years ago

Ok, I think I found out where it is coming from at least:

https://github.com/pivotal-gss/mock-data/blob/ac23cf13044b2d697a9fa411cc6c18132c6002db/constraintsRestore.go#L53-L61

cols := strings.Trim(keys, "()")´ seems to remove too many)` here that are required for the cols. It should probably just remove a pair of brackets at the start and end of the string - but I don't know golang, so have no idea how to write that.

faisaltheparttimecoder commented 4 years ago

The unqiue index DDL is here

https://github.com/prisma/database-schema-examples/blob/master/postgres/odoo/schema.sql#L19884

CREATE UNIQUE INDEX ir_translation_code_unique ON public.ir_translation USING btree (type, lang, md5(src)) WHERE ((type)::text = 'code'::text);

You are right about the line, but it actually fails at the regex

https://github.com/pivotal-gss/mock-data/blob/ac23cf13044b2d697a9fa411cc6c18132c6002db/constraintsRestore.go#L53

The regex tells to find everything that starts with ( & ends with ), i.e. it did start of correctly but it ends as soon as it found ) i.e the o/p was (type, lang, name, md5(src), so at the end the trim ended with type, lang, name, md5(src which is wrong, need to fix this.

need to fix the regex to find the matching end brackets )

faisaltheparttimecoder commented 4 years ago

Updated the wrong issues, the above commit was for the issue (https://github.com/pivotal-gss/mock-data/issues/26) , Reopening this to fix the issue

faisaltheparttimecoder commented 4 years ago

Updated the regex to this

    // Extract the columns from the list that was collected during backup
    keys, err := ColExtractor(pk.column, ` \((?:[^()]+|(?R))*+\)`)

seems like golang doesn't support look around, I end up with error

 error parsing regexp: invalid or unsupported Perl syntax: `(?R`

Going to keep this under pending for another approach

janpio commented 4 years ago

Yep, canvas-lms is now also affected by this after removing the other blocker:

mock-windows-amd64-v2.5.exe database -a localhost -w foo -f -v -d canvas-lms
...
time="2020-03-10 20:15:43" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
time="2020-03-10 20:15:43" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
time="2020-03-10 20:15:43" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
time="2020-03-10 20:15:43" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
time="2020-03-10 20:15:43" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
time="2020-03-10 20:15:43" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
time="2020-03-10 20:15:43" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
time="2020-03-10 20:15:43" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
time="2020-03-10 20:15:43" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
time="2020-03-10 20:15:43" level=debug msg="query: SELECT COUNT(*) FROM ( SELECT lower((unique_id FROM \"public\".\"pseudonyms\" GROUP BY lower((unique_id HAVING COUNT(*) > 1 ) a" file="sql.go:415"
time="2020-03-10 20:15:43" level=fatal msg="Error when executing the query to extract pk violators: ERROR #42601 syntax error at or near \"FROM\"" file="sql.go:416"

https://github.com/prisma/database-schema-examples/blob/master/postgres/canvas-lms/schema.sql

janpio commented 4 years ago

And exo as well:

time="2020-03-10 20:34:38" level=debug msg="Checking / Fixing FOREIGN KEY Violation table: \"public\".\"portal_navigation_nodes\", column: page_id, reference: portal_pages(id)" file="constraintsRestore.go:109"
time="2020-03-10 20:34:38" level=debug msg="Extracting the foreign violations for table \"public\".\"portal_navigation_nodes\" and column portal_pages" file="sql.go:520"
time="2020-03-10 20:34:39" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:99"
time="2020-03-10 20:34:39" level=debug msg="Checking / Fixing FOREIGN KEY Violation table: \"public\".\"portal_navigations\", column: node_id, reference: portal_navigation_nodes(node_id)" file="constraintsRestore.go:109"
time="2020-03-10 20:34:39" level=debug msg="Extracting the foreign violations for table \"public\".\"portal_navigations\" and column portal_navigation_nodes" file="sql.go:520"
time="2020-03-10 20:34:40" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:99"
time="2020-03-10 20:34:40" level=debug msg="Checking / Fixing FOREIGN KEY Violation table: \"public\".\"portal_navigations\", column: site_id, reference: portal_sites(id)" file="constraintsRestore.go:109"
time="2020-03-10 20:34:40" level=debug msg="Extracting the foreign violations for table \"public\".\"portal_navigations\" and column portal_sites" file="sql.go:520"
time="2020-03-10 20:34:40" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:99"
time="2020-03-10 20:34:40" level=debug msg="Checking / Fixing FOREIGN KEY Violation table: \"public\".\"portal_pages\", column: site_id, reference: portal_sites(id)" file="constraintsRestore.go:109"
time="2020-03-10 20:34:40" level=debug msg="Extracting the foreign violations for table \"public\".\"portal_pages\" and column portal_sites" file="sql.go:520"
time="2020-03-10 20:34:41" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:99"
time="2020-03-10 20:34:41" level=debug msg="Checking / Fixing FOREIGN KEY Violation table: \"public\".\"qrtz_blob_triggers\", column: sched_name, trigger_name, trigger_group, reference: qrtz_triggers(sched_name, trigger_name, trigger_group)" file="constraintsRestore.go:109"
time="2020-03-10 20:34:41" level=debug msg="Query: SELECT COUNT(*) FROM (\nSELECT sched_name, trigger_name, trigger_group \nFROM   \"public\".\"qrtz_blob_triggers\" \nWHERE sched_name, trigger_name, trigger_group  NOT IN \n       ( \n              SELECT sched_name, trigger_name, trigger_group \n              FROM   qrtz_triggers )\n) a" file="sql.go:492"
time="2020-03-10 20:34:41" level=fatal msg="Error when executing the query to total rows of foreign keys for table \"public\".\"qrtz_blob_triggers\": ERROR #42601 syntax error at or near \",\"" file="sql.go:493"

https://github.com/prisma/database-schema-examples/blob/master/postgres/exo/schema.sql

And Gitlab:

time="2020-03-10 20:39:44" level=debug msg="Extracting the unique violations for table \"public\".\"pool_repositories\" and column disk_path" file="sql.go:429"
time="2020-03-10 20:39:44" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
time="2020-03-10 20:39:44" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
time="2020-03-10 20:39:45" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
time="2020-03-10 20:39:45" level=debug msg="Extracting constraint column data type info for table: \"public\".\"programming_languages\"" file="sql.go:377"
time="2020-03-10 20:39:45" level=debug msg="Extracting the unique violations for table \"public\".\"programming_languages\" and column name" file="sql.go:429"
time="2020-03-10 20:39:45" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
time="2020-03-10 20:39:45" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
time="2020-03-10 20:39:45" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
time="2020-03-10 20:39:45" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
time="2020-03-10 20:39:45" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
time="2020-03-10 20:39:45" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
time="2020-03-10 20:39:45" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
time="2020-03-10 20:39:45" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
time="2020-03-10 20:39:45" level=debug msg="Fixing the Primary / Unique Key" file="constraintsRestore.go:50"
time="2020-03-10 20:39:45" level=debug msg="query: SELECT COUNT(*) FROM ( SELECT project_id, date DESC FROM \"public\".\"project_daily_statistics\" GROUP BY project_id, date DESC HAVING COUNT(*) > 1 ) a" file="sql.go:415"
time="2020-03-10 20:39:45" level=fatal msg="Error when executing the query to extract pk violators: ERROR #42601 syntax error at or near \"DESC\"" file="sql.go:416"

https://github.com/prisma/database-schema-examples/blob/master/postgres/gitlab/schema.sql

faisaltheparttimecoder commented 4 years ago

Thanks, ya I figured that out during testing so I need a good regex or I will need to create on my own.

faisaltheparttimecoder commented 4 years ago

Alright got a regex \(([^\[\]]*)\) that works in golang and this seems to fix the issue we are having here.

Here is the Summary of the test

discourse: Works
mastodon: Works but there was an issue with public.accounts index when we tried to delete the pk data via deleteViolatingPkOrUkConstraints since there is another table that has built a fk on these data.
```
ALTER TABLE ONLY public.account_moderation_notes
    ADD CONSTRAINT fk_rails_dd62ed5ac3 FOREIGN KEY (target_account_id) REFERENCES public.accounts(id);
```
This becomes complicated now to solve due to nested constraints, we leave it to user to fix the data manually and recreate the constraints (we did our max to fix it)

odoo: Works but a lot of index creation issues due to the same issue with nested fk mentioned above and the clause ON DELETE SET NULL|RESTRICT on lot of them.

I.e when we tried to delete the pk row, we got the error

ERROR:  null value in column "user_id" violates not-null constraint
DETAIL:  Failing row contains (97, 166, M, null, t, 5747506, h, 2161672, t, 2020-11-11 05:46:08, -199450, 24, 2027-08-07 08:58:20, 91, 2029-03-30 01:25:55).
CONTEXT:  SQL statement "UPDATE ONLY "public"."ir_cron" SET "user_id" = NULL WHERE $1 OPERATOR(pg_catalog.=) "user_id""

the FK here has an ON DELETE SET NULL clause

testme=# \d ir_cron
                                             Table "public.ir_cron"
        Column        |            Type             | Collation | Nullable |               Default
----------------------+-----------------------------+-----------+----------+-------------------------------------
 id                   | integer                     |           | not null | nextval('ir_cron_id_seq'::regclass)
 ir_actions_server_id | integer                     |           | not null |
 cron_name            | character varying           |           |          |
 user_id              | integer                     |           | not null |
 active               | boolean                     |           |          |
 interval_number      | integer                     |           |          |
 interval_type        | character varying           |           |          |
 numbercall           | integer                     |           |          |
 doall                | boolean                     |           |          |
 nextcall             | timestamp without time zone |           | not null |
 priority             | integer                     |           |          |
 create_uid           | integer                     |           |          |
 create_date          | timestamp without time zone |           |          |
 write_uid            | integer                     |           |          |
 write_date           | timestamp without time zone |           |          |
Indexes:
    "ir_cron_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "ir_cron_create_uid_fkey" FOREIGN KEY (create_uid) REFERENCES res_users(id) ON DELETE SET NULL
    "ir_cron_ir_actions_server_id_fkey" FOREIGN KEY (ir_actions_server_id) REFERENCES ir_act_server(id) ON DELETE RESTRICT
    "ir_cron_user_id_fkey" FOREIGN KEY (user_id) REFERENCES res_users(id) ON DELETE SET NULL
    "ir_cron_write_uid_fkey" FOREIGN KEY (write_uid) REFERENCES res_users(id) ON DELETE SET NULL

but it fails because there is a another constraint of not null attached to it ( this conflicts with each other :) )

user_id              | integer                     |           | not null |

canvas-lms: Works, this has the outstanding issue due to trigger on the table (https://github.com/pivotal-gss/mock-data/issues/23) which cannot be fixed via mock-data also Index on a Foriegn Key issue.
exo: Works, Fails on composite FK

ALTER TABLE "public"."qrtz_blob_triggers" ADD CONSTRAINT qrtz_blob_triggers_sched_name_fkey FOREIGN KEY (sched_name, trigger_name, trigger_group) REFERENCES qrtz_triggers(sched_name, trigger_name, trigger_group);
ALTER TABLE "public"."qrtz_cron_triggers" ADD CONSTRAINT qrtz_cron_triggers_sched_name_fkey FOREIGN KEY (sched_name, trigger_name, trigger_group) REFERENCES qrtz_triggers(sched_name, trigger_name, trigger_group);
ALTER TABLE "public"."qrtz_simple_triggers" ADD CONSTRAINT qrtz_simple_triggers_sched_name_fkey FOREIGN KEY (sched_name, trigger_name, trigger_group) REFERENCES qrtz_triggers(sched_name, trigger_name, trigger_group);
ALTER TABLE "public"."qrtz_simprop_triggers" ADD CONSTRAINT qrtz_simprop_triggers_sched_name_fkey FOREIGN KEY (sched_name, trigger_name, trigger_group) REFERENCES qrtz_triggers(sched_name, trigger_name, trigger_group);
ALTER TABLE "public"."qrtz_triggers" ADD CONSTRAINT qrtz_triggers_sched_name_fkey FOREIGN KEY (sched_name, job_name, job_group) REFERENCES qrtz_job_details(sched_name, job_name, job_group);

We have not implemented any logic to work with composite FKey (This would take time)

gitlab: Works

CREATE UNIQUE INDEX index_project_daily_statistics_on_project_id_and_date ON public.project_daily_statistics USING btree (project_id, date DESC);

but fails here due to DESC extracting this condition from brackets results in wrong sql like

DEBU[2020-03-20 19:58:01] query: SELECT COUNT(*) FROM ( SELECT project_id, date desc FROM "public"."project_daily_statistics" GROUP BY project_id, date desc HAVING COUNT(*) > 1 ) a  file="sql.go:415"

faisaltheparttimecoder commented 4 years ago

Im closing this issue, since the original issue reported has been fixed.

janpio commented 4 years ago

Thanks!

faisaltheparttimecoder / mock-data

Error when execute the query to extract pk violators: ERROR #42601 syntax error at or near \"FROM\" #24