Re: pg_restore restores out of order - Mailing list pgsql-general

On 3/11/20 4:11 PM, Kevin Brannen wrote:
>> Adrian Klaver wrote:
>> On 3/11/20 2:46 PM, Kevin Brannen wrote:
>>> I'm working thru our system to upgrade from 9.6.5 to 12.2. One of the last things I'm verifying is backup/restore
andit's failing for no reason I can figure out. So I'm looking for pointers on this.
 
>>>
>>> If it matters, the code is from the version 12.2 from the Pg site, RPMs for Centos 6 (.10).
>>>
>>> The backup is made like:
>>>
>>> # $PGPATH/pg_dump --format=d --jobs=2 --file=$exp --dbname=nms
>>> --schema=public
>>
>> Which version of pg_dump 9.6.5 or 12.2?
> 
> Both pg_dump and pg_restore are with 12.2, on an already converted 12.2 DB.
> So I'm 12.2 all the way on my test system by this point. :)
> 
>>> ...
>>> So we move the current schema to the side just in case something goes wrong and we need to move it back, create an
emptyschema for it, then restore into that schema. Then it goes bad...
 
>>>
>>> pg_restore: while PROCESSING TOC:
>>> pg_restore: from TOC entry 6356; 0 23653 TABLE DATA subscribers
>>> nmsroot
>>> pg_restore: error: COPY failed for table "subscribers": ERROR:  relation "su_profiles" does not exist
>>> LINE 1: SELECT srvc_data           FROM su_profiles WHERE su_profile...
>>>                                           ^
>>> QUERY:  SELECT srvc_data           FROM su_profiles WHERE su_profile_pk = p_profile_fk
>>> CONTEXT:  PL/pgSQL function public.check_data_ip(integer,integer,character varying) line 7 at SQL statement
>>> COPY subscribers, line 1: "61   1002    \N      SU_1002 t       \N      4       \N      1       250     0
2015-06-2216:56:27.79333+00     nmsmgr  \N      \N      \N"
 
>>
>> What is in public.check_data_ip(integer,integer,character varying)?
>>
>> Is it a trigger function on subscribers?
> 
> Not quite...
> 
> nms=# \d subscribers
>                         Table "public.subscribers"
> ...
> Check constraints:
>      "chk_su_data_ip" CHECK (check_data_ip(profile_fk, unit_id, data_ip))
> 
> 
> The first line of the check_data_ip() function is the offender:
> 
> SELECT srvc_data INTO data
> FROM su_profiles
> WHERE su_profile_pk = p_profile_fk;
> 
> Hmm, why is this a problem now and not before?
> 
> (Probably the usual reason of code "tightening" as we go forward, or so I'd guess.)
> 
> I don't see any option that mentions "CHECK" in the pg_restore doc in regards to
> possibly turning it off...more research...
> 
> And oh, this hurts. :( From the docs on CHECK constraints:
> 
> PostgreSQL does not support CHECK constraints that reference table data other
> than the new or updated row being checked. While a CHECK constraint that
> violates this rule may appear to work in simple tests, it cannot guarantee
> that the database will not reach a state in which the constraint condition
> is false (due to subsequent changes of the other row(s) involved). This
> would cause a database dump and reload to fail. ...
> 
> It goes on to say a trigger is the right way to do this. So yeah, a "tightening
> of the code" seems to be hitting me because of bad code from the past.
> 
> Problem identified and I'll solve it tomorrow.

Glad it was solved.

However, looking a gift horse in the mouth, did this not error when 
moving from the 9.6.5 instance to the first 12.2 instance?

Or

Was the constraint added in the first 12.2 instance?

> 
> Thank you so much Adrian for helping me to figure this out!
> 
> Kevin


-- 
Adrian Klaver
adrian.klaver@aklaver.com