0

I have a strange error when importing a backuped database from my prod environment to my dev environment.

the scenario is as follows:

  • Postgres 13.x
  • PGAdmin 4
  • migration files are generated by dbmate and create the very same tables in both environments. At least thats what I think and hope.
  • the backup is done via PGAdmin exporting ONLY the data (no Schema no nothing)

OK that was the easy part.

My import using PgAdmin works almost fine with 1 single exception in 1 particular table. That table uses a user-defined type for one of its columns and it defines a constraint that uses that user-defined type within a function when the constraint is checked.

The whole thing looks roughly like this:

CREATE TYPE status AS ENUM ('ACTIVE','DEACTIVE');

CREATE TABLE my_table (
    id          varchar(36) NOT NULL PRIMARY KEY,
    created_at  timestamp NOT NULL,
    updated_at  timestamp NOT NULL,

    ...

    total       decimal NOT NULL,
    value       JSONB NOT NULL,
    state       status
);

CREATE OR REPLACE FUNCTION complex_constraint_function(value JSONB, total decimal, state status)
RETURNS boolean AS $$
DECLARE
    result boolean default false;
BEGIN
    IF state = 'ACTIVE'::status THEN
        BEGIN
            --do some complex JSONB stuff
            -- return true if everything is fine
            RETURN true;
        END;
    ELSE
        RETURN false;
    END IF;
END;
$$ LANGUAGE plpgsql;

ALTER TABLE my_table 
    ADD CONSTRAINT check_complex_constraint
        CHECK (complex_constraint_function(value, total, state));

I am using multiple user-defined types in my database and they all work fine. And I am using multiple constraints that use functions and they all work fine. But this one particular table is giving me a headache.

On importing the backup via PGAdmin into the dev environment I get the following error when the constraint is being checked

pg_restore: error: COPY failed for table "my_table": ERROR: type "status" does not exist
LINE 1: state = 'ACTIVE'::status
^
QUERY: state = 'ACTIVE'::status
CONTEXT: PL/pgSQL function public.complex_constraint_function(jsonb,numeric,public.status) line 6 at IF

I double and tripple checked if that ENUM type does exists in both DBs (and there is honestly no other way as the dev DB is regularily rebuild using dbmate). I have honestly no clue how this can even be. As when runnign my test cases with the dev-db this constraint is there (I am explictly testing for this).

Has anybody seen anything like this? Or any idea on how to debug this (except for dropping the constraint)?

Thanks

1 Answer 1

2

Usage should be with schema-qualified identity.

IF state = 'ACTIVE'::public.status THEN

pg_restore is intentionally executed with a search_path that includes only the system catalog, but not any user namespaces (for example default public schema).

It is necessary because of CVE-2018-1058 class exploits. This is indeed inconvenient at times, but the postgresql developers have no good ideas on how to fix this inconsistency without reintroducing the vulnerability to such CVE.

1
  • wow! excellent. And the reference to using the namespace helped with the next "bug" too. Thank you very much
    – robkuz
    Apr 4 at 8:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.