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 and it'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 empty schema 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-22 16: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@xxxxxxxxxxx
