Constraints not visible in latest migration schema

[AA-morganh]

I have a raw sql migration that creates a unique constraint across a few columns. I want to use this constraint to enforce an upsert workflow. However, in the schema from that migration (public_MIGRATION_NAME) the constraint isn't visible. If I switch to the public schema, it is visible and working, so I can see that it's getting created correctly. Is there something I need to do to get my constraint into my migration schema's view, or should I run another migration, or do something else?

Example Migration:

{
    "name": "my_migration",
    "operations": [
        {
            "sql": {
                "up":"ALTER TABLE inventory ADD CONSTRAINT unique_entitlement_ids_per_user UNIQUE (user_id, entitlement_id);",
                "down":"ALTER TABLE inventory DROP CONSTRAINT unique_entitlement_ids_per_user "
            }
        }] 
}

SET search_path=public_my_migration;

INSERT INTO inventory(user_id, entitlement_id, quantity)
	VALUES ('Some User Id', 'Some Entitlement Id', 100)
	ON CONFLICT ON CONSTRAINT unique_entitlement_ids_per_user 
    	DO UPDATE SET
			quantity = inventory.quantity + EXCLUDED.quantity
RETURNING *

This fails with "constraint unique_entitlement_ids_per_user does not exist." However, if I use the public schema instead, the query works.

[exekias]

aah, good catch! a couple of things that come to mind you could try:

• Adding public to the search path so the constraint is found: SET search_path= public_my_migration,public;

• Fully namespacing the constraint from the query:

INSERT INTO inventory(user_id, entitlement_id, quantity)
	VALUES ('Some User Id', 'Some Entitlement Id', 100)
	ON CONFLICT ON CONSTRAINT public.unique_entitlement_ids_per_user 
    	DO UPDATE SET
			quantity = inventory.quantity + EXCLUDED.quantity
RETURNING *

Let us know if any of these work! I'm wondering if we could have ways of making the constraint visible without these changes

[AA-morganh]

Hi @exekias

Unfortunately those aren't working for me. I'm setting the search path as a parameter to my postgres client as it connects (-c search_path=SEARCH PATH) and when I add the public schema to that path, it's not finding any of my relations anymore.

I tried fully namespacing the constraint as you have here and that's also failing, complaining that the "." is invalid syntax.

One advantage I do have is that I'm running this specific query in a larger transaction, so I can do

SET search_path=public;

MY QUERY

SET search_path=SCHEMA;

I did try using SET search_path=SCHEMA,public in the transaction to see what would happen, and it also failed to find the constraint. I think it might be because it's finding the inventory table in the view, so it doesn't look at the public schema for the constraint even if it's on the path. Could be wrong about that though, I'm far from a postgres expert.

[kvch]

Unfortunately, you cannot access the constraint like this because it is defined on the table. But public_my_migration.inventory is a view. In order to support testing constraints by running multiple schemas, we must reconsider our view usage. I am keeping this issue open. We can only address this problem if we abandon views.