Why am I getting this? Error: error scanning foreign key constraint: sql: Scan error on column index 5, name "table_name": converting NULL to string is unsupported

biorisk commented 4 years ago

I have found Gnorm very helpful for generating code that creates a webserver that connects to a gRPC server that connects to postgres. I use Gnorm to generate proto files, go db code, go web server code, and html templates and saved a LOT of time. I finished the first version of my project 6 months ago, but now have a number of db changes and need to update.

I am getting a strange error when I run gnorm gen -c myconfig.toml:

Error: error scanning foreign key constraint: sql: Scan error on column index 5, name "table_name": converting NULL to string is unsupported

I am assuming that table_name is coming from information.schema, since nothing in my schema has that name.

I haven't changed my gnorm config files or template files since the last successful build. My db colleague has updated a few fields and created new tables in my schema as well as adding new schemas in the same postgres instance. However, I do specify only the schema I want in the config: DBType = "postgres" Schemas = ["stsdb"]

Is it possible that Gnorm is getting confused by information.schema or changes in other schemas?

biorisk commented 4 years ago

I pulled my db schema off of the original server and created a local instance. Gnorm worked fine. Just FYI, the original server was a managed CloudSQL instance on GCP. Probably not a problem with Gnorm.

natefinch commented 4 years ago

Hmm that's good to know. CloudSQL, if it's postgres or MySQL should still work, but it does make me wonder if there's something funky in there.

saward commented 4 years ago

I'm having this exact error, and looking at the underlying gnorm query I suspect it's because I've joined to another schema. I have 'created_by' referencing a user id from a different schema.

The reason for hitting it might be different though -- I'm testing on my local machine via Docker, tried postgres 9.6 and 12. Both failed, but I think they will continue to fail because of the query joining ON constraint_schema. I haven't tested, but I'm wondering about something like this (iirc, only change is the fkc join to be on unique_constraint_schema instead of constraint_schema):

SELECT rc.constraint_schema, lkc.table_name, lkc.column_name, lkc.constraint_name, lkc.position_in_unique_constraint, fkc.table_name, fkc.column_name
FROM information_schema.referential_constraints rc
LEFT JOIN information_schema.key_column_usage lkc
    ON lkc.table_schema = rc.constraint_schema
  AND lkc.constraint_name = rc.constraint_name
LEFT JOIN information_schema.key_column_usage fkc
    ON fkc.table_schema = rc.unique_constraint_schema
  AND fkc.ordinal_position = lkc.position_in_unique_constraint
  AND fkc.constraint_name = rc.unique_constraint_name
WHERE rc.constraint_schema IN (<schemas>);

saward commented 4 years ago

A quick test on my own copy of gnorm with this change made, and I can now generate my code via gnorm. I don't understand whether there are any other serious consequences of making this change though. Are you able to advise whether or not you think this will cause issues?