Re: Problems with pg_upgrade after change of unix user running db.

On 11/27/2015 06:07 PM, Tom Lane wrote:
> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>> On 11/27/2015 08:15 AM, Bruce Momjian wrote:
>>> My guess is you are sharing the constraint name "seqno_not_null" with
>>> multiple tables.  I think you are going to have to dig into the system
>>> tables to see where that is referenced and fix it.
>
>> In the post below the OP shows the tables involved(they where inherited):
>> http://www.postgresql.org/message-id/CADbMkNM_y9ewdaWdQ_8DJ1mUC0Z_FGwTyAD2RwCHgExj2jvOHQ@mail.gmail.com
>
> Inherited eh?  Maybe related to 074c5cfbf.

From the OP's post:


The error:

pg_restore: creating CHECK CONSTRAINT seqno_not_null
pg_restore: creating CHECK CONSTRAINT seqno_not_null
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 8359; 2606 416548282 CHECK
CONSTRAINT seqno_not_null postgres_prod
pg_restore: [archiver (db)] could not execute query: ERROR:  constraint
"seqno_not_null" for relation "js_activity_2011" already exists
    Command was: ALTER TABLE "js_activity_2011"
    ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;

The setup:

postgres_prod(at)proddb_testing=# select c.conname, c.conislocal,
c.coninhcount, c.convalidated as valid, (select relname from pg_class where
oid = c.conrelid) from pg_constraint c where conname = 'seqno_not_null'
order by relname;
    conname     │ conislocal │ coninhcount │ valid │     relname
────────────────┼────────────┼─────────────┼───────┼──────────────────
 seqno_not_null │ t          │           0 │ f     │ js_activity
 seqno_not_null │ t          │           1 │ f     │ js_activity_2009
 seqno_not_null │ t          │           1 │ f     │ js_activity_2010
 seqno_not_null │ t          │           1 │ f     │ js_activity_2011
 seqno_not_null │ f          │           1 │ f     │ js_activity_2012
 seqno_not_null │ f          │           1 │ t     │ js_activity_2013
 seqno_not_null │ f          │           1 │ t     │ js_activity_2014
 seqno_not_null │ f          │           1 │ f     │ js_activity_tip



[as-proddb(at)nyc-dbc-001 upgrade-logs]$ pg_restore
pg_upgrade_dump_16416.custom | fgrep seqno_not_null -B 1
-- For binary upgrade, set up inherited constraint.
ALTER TABLE ONLY "js_activity_2013"  ADD CONSTRAINT "seqno_not_null" CHECK
(("seqno" IS NOT NULL));
--
SET conislocal = false
WHERE contype = 'c' AND conname = 'seqno_not_null'
--
-- For binary upgrade, set up inherited constraint.
ALTER TABLE ONLY "js_activity_2014"  ADD CONSTRAINT "seqno_not_null" CHECK
(("seqno" IS NOT NULL));
--
SET conislocal = false
WHERE contype = 'c' AND conname = 'seqno_not_null'
--
--
-- Name: seqno_not_null; Type: CHECK CONSTRAINT; Schema: public; Owner:
postgres_prod
--
ALTER TABLE "js_activity"
    ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;
--
--
-- Name: seqno_not_null; Type: CHECK CONSTRAINT; Schema: public; Owner:
postgres_prod
--
ALTER TABLE "js_activity_2011"
    ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;
--
--
-- Name: seqno_not_null; Type: CHECK CONSTRAINT; Schema: public; Owner:
postgres_prod
--
ALTER TABLE "js_activity_2010"
    ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;
--
--
-- Name: seqno_not_null; Type: CHECK CONSTRAINT; Schema: public; Owner:
postgres_prod
--
ALTER TABLE "js_activity_2009"
    ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;





>
>                       regards, tom lane
>


--
Adrian Klaver
adrian.klaver@aklaver.com