Multiple foreign keys issue

[dustinsmith1024]

Just trying this out for the first time. I have a table that has multiple foreign keys on id and uuid to the same target table.

The first ID related queries seem to work and then it fails while trying to do a select a subset based on the wrong key type. See the second/failed query.

Is there a way to break this foreign key? I am thinking about just dropping the uuid foreign key before running the tool. Messing with dependency breaks and fk_augmentation hasn't seemed to work at all.

Thanks for the help.

    SELECT "market_id" FROM "calc"."settings" WHERE ("market_id") NOT IN (SELECT "id" FROM "public"."market")
    Query completed in 0.0010530948638916016s
Beginning query @ 2021-01-14 21:14:09.559303:
    SELECT ty.typname
                        FROM pg_attribute att
                        JOIN pg_class cl ON cl.oid = att.attrelid
                        JOIN pg_type ty ON ty.oid = att.atttypid
                        JOIN pg_namespace ns ON ns.oid = cl.relnamespace
                        WHERE cl.relname = 'tonic_subset_59dd3165-8a06-4460-9b84-554f34af116a' AND att.attnum > 0 AND
                        NOT att.attisdropped
                        ORDER BY att.attnum;

    Query completed in 0.002404928207397461s
Beginning query @ 2021-01-14 21:14:09.561779:
    SELECT "market_uuid" FROM "calc"."settings" WHERE ("market_uuid") NOT IN (SELECT "id" FROM "public"."market")
Traceback (most recent call last):
  File "direct_subset.py", line 43, in <module>
    subsetter.run_middle_out()
  File "/Users/dustinsmith/Development/work/condenser/condenser/subset.py", line 84, in run_middle_out
    self.subset_downstream(t, relationships)
  File "/Users/dustinsmith/Development/work/condenser/condenser/subset.py", line 177, in subset_downstream
    self.__db_helper.copy_rows(self.__destination_conn, self.__destination_conn, q, temp_table)
  File "/Users/dustinsmith/Development/work/condenser/condenser/psql_database_helper.py", line 36, in copy_rows
    cursor.execute(query)
  File "/Users/dustinsmith/Development/work/condenser/condenser/db_connect.py", line 58, in execute
    retval = self.inner_cursor.execute(query)
psycopg2.errors.UndefinedFunction: operator does not exist: uuid = bigint
LINE 1: ...calc"."settings" WHERE ("market_uuid") NOT IN (SE...
                                                             ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

[theaeolianmachine]

Hi Dustin,

Are you writing your foreign keys from scratch in the FK Augmentation section? As I understand, calc.settings.market_id and calc.settings.market_uuid both point to public.market.id, where public.market.id is a bigint type. Do you do explicit type casts when normally querying between these columns? Since not all bigints can be proper UUID types in Postgres, we don't do an explicit cast here.

However, I would expect a dependency break (notably from calc.settings.market__uuid to public.market.id) to allow you to work around this. Would you mind sharing your configuration (connection details redacted?)

[dustinsmith1024]

calc.settings.market_uuid does not point to public.market.id it points to public.market.uuid. So no we don't have to do any typecasting because they are different links.

It seems like the tool is assuming UUID is linked to ID though.

[theaeolianmachine]

Hmmm, might be worth taking a look at your config and perhaps a schema dump of these two tables, something seems off.