Quick Links

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

From:	Benedikt Grundmann <bgrundmann(at)janestreet(dot)com>
To:	Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc:	Bruce Momjian <bruce(at)momjian(dot)us>, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>, pgsql-general(at)postgresql(dot)org
Subject:	Re: Problems with pg_upgrade after change of unix user running db.
Date:	2016-10-03 14:58:26
Message-ID:	CADbMkNPhMJdAU9L4eWNFXurE8pn6u6d8_20E-EjuQyoDhBd7jQ@mail.gmail.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Thread:
Lists:	pgsql-general

On 3 October 2016 at 15:54, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Benedikt Grundmann <bgrundmann(at)janestreet(dot)com> writes:
> > And it looks like now I'm back to the error that stopped me last time:
> > pg_restore: [archiver (db)] Error from TOC entry 8425; 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_20110101" already exists
> > Command was: ALTER TABLE "js_activity_20110101"
> > ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT
> VALID;
>
> Hm. I'm guessing that table is a child table, and this has something to
> do with improper constraint inheritance. Could we see psql \d+ output
> for both this table and its parent? And for good measure, maybe the
> output of
>
> SELECT conname,convalidated,conislocal,coninhcount,connoinherit
> FROM pg_constraint WHERE conrelid = 'js_activity_20110101'::regclass;
>
> and likewise for the parent table.
>
> regards, tom lane
>

Sure this is not going to be very pretty but here you are:

| extended | |
Check constraints:
"at_most_one_clears_on_bk" CHECK (at_most_one_key_like(tags,
'bk:Clears_on_%'::text)) NOT VALID
"at_most_one_riskless_principal_for_bk" CHECK
(at_most_one_key_like(tags, 'bk:Riskless_principal_for_%'::text)) NOT
VALID
"cancel_consistency" CHECK ((cancel_username IS NULL) =
(cancel_entry_time IS NULL) AND (cancel_entry_time IS NULL)
= (cancel_narrative IS NULL) AND (cancel_narrative IS NULL) =
(cancel_seqno IS NULL))
"capture_date_matches_date_in_tid" CHECK (NOT tid ~~ '(2%)'::text OR
"substring"(tid, 2, 10)::date = capture_date)
NOT VALID
"contracts_is_an_integer" CHECK ((tags -> 'contracts'::text) ~
'^[0-9]+$'::text)
"flow_tag" CHECK ((tags -> 'flow'::text) ~ '^[0-9]+$'::text) NOT VALID
"js_activity_booking_type_check" CHECK (booking_type = 'B'::bpchar OR
booking_type = 'A'::bpchar)
"js_activity_check" CHECK (is_canceled = id)
"js_activity_discretionary_agent_matches_tag" CHECK
(discretionary_agent = (tags -> 'agent'::text))
"js_activity_no_suspense_accounts_check" CHECK (account !~
'^SUSPENSE-.+$'::text)
"js_activity_pnl_date_on_weekday" CHECK (date_part('dow'::text,
pnl_date) >= 1::double precision AND date_part('dow
'::text, pnl_date) <= 5::double precision)
"js_activity_security_code_non_blank" CHECK (security_code <> ''::text)
"nothing_to_see_here" CHECK (pnl_date = '1000-01-01'::date) NO INHERIT
"otc_settlement_system_consistency" CHECK (NOT executing_exchange ~~
'OTC_%'::text OR settlement_system = "substrin
g"(executing_exchange, 5)) NOT VALID
"price_extra_info_tags_come_together" CHECK ((NOT tags ?
'not_final_price'::text OR (tags -> 'not_final_price'::tex
t) = ''::text) AND (tags ? 'price_extra_data'::text) = (tags ?
'price_kind'::text) AND (NOT tags ? 'not_final_price'::t
ext OR tags ? 'price_kind'::text)) NOT VALID
"security_code_not_blank_on_either_end" CHECK (security_code !~~ '%
'::text AND security_code !~~ ' %'::text) NOT V
ALID
"seqno_not_null" CHECK (seqno IS NOT NULL) NOT VALID
"trades_have_times" CHECK (trade_type <> 'Trade'::text OR activity_time
IS NOT NULL) NOT VALID
"valid_counterparty" CHECK (counterparty !~~ '% '::text AND
counterparty !~~ ' %'::text) NOT VALID
"valid_executing_broker" CHECK (executing_broker !~~ '% '::text AND
executing_broker !~~ ' %'::text) NOT VALID
"valid_order_mark" CHECK (order_mark = ANY (ARRAY['T'::text, 'X'::text,
'S'::text])) NOT VALID
"valid_settlement_system" CHECK (settlement_system ~ '^[A-Z]+$'::text)
NOT VALID
"valid_terms_tag" CHECK ((tags -> 'terms'::text) ~
'^[A-Z0-9]{4}$'::text) NOT VALID
Foreign-key constraints:
"js_activity_clearing_firm_fkey1" FOREIGN KEY (clearing_firm)
REFERENCES lu_clearing_firms(mnemonic)
"js_activity_currency_fkey1" FOREIGN KEY (currency) REFERENCES
lu_currencies(currency_code)
"js_activity_no_rows" FOREIGN KEY (id) REFERENCES
js_activity_no_rows_constraint(id)
"js_activity_trade_type_fkey1" FOREIGN KEY (trade_type) REFERENCES
lu_trade_types(mnemonic)
Triggers:
insert_js_activity BEFORE INSERT ON js_activity FOR EACH ROW EXECUTE
PROCEDURE js_activity_insert_trigger()
mv_js_equity AFTER UPDATE ON js_activity FOR EACH STATEMENT EXECUTE
PROCEDURE mv_js_equity_js_activity_was_changed(
)
Child tables: js_activity_20090101,
js_activity_20100101,
js_activity_20110101,
js_activity_20120101,
js_activity_20130101,
js_activity_20140101,
js_activity_20150101,
js_activity_20150701,
js_activity_20160101,
js_activity_tip
Has OIDs: no

| extended | |
Indexes:
"js_activity_2011_cancel_seqno" UNIQUE, btree (cancel_seqno) WHERE
cancel_seqno IS NOT NULL
"js_activity_2011_id" UNIQUE, btree (id)
"js_activity_2011_seqno" UNIQUE, btree (seqno)
"js_activity_2011_activity_date" btree (activity_date)
"js_activity_2011_cancel_entry_time" btree (cancel_entry_time) WHERE
cancel_entry_time IS NOT NULL
"js_activity_2011_discretionary_agent" btree (discretionary_agent)
"js_activity_2011_entry_time" btree (entry_time)
"js_activity_2011_pnl_date" btree (pnl_date)
"js_activity_2011_projected_settlement_date" btree
(projected_settlement_date)
"js_activity_2011_shape_id" btree (shape_id)
"js_activity_2011_tid" btree (tid)
"js_activity_2011_ts" btree ((activity_date + activity_time)) WHERE
activity_time IS NOT NULL
Check constraints:
"activity_date_lower_bound" CHECK (activity_date >= '0111-12-28'::date)
"activity_date_upper_bound" CHECK (activity_date <= '2012-02-10'::date)
"activity_time_lower_bound" CHECK ((activity_date + activity_time) >=
'2010-01-05 10:44:48'::timestamp without time
zone)
"activity_time_upper_bound" CHECK ((activity_date + activity_time) <=
'2011-12-30 23:59:59'::timestamp without time
zone)
"at_most_one_clears_on_bk" CHECK (at_most_one_key_like(tags,
'bk:Clears_on_%'::text)) NOT VALID
"at_most_one_riskless_principal_for_bk" CHECK
(at_most_one_key_like(tags, 'bk:Riskless_principal_for_%'::text)) NOT
VALID
"cancel_consistency" CHECK ((cancel_username IS NULL) =
(cancel_entry_time IS NULL) AND (cancel_entry_time IS NULL)
= (cancel_narrative IS NULL) AND (cancel_narrative IS NULL) =
(cancel_seqno IS NULL))
"cancel_entry_time_lower_bound" CHECK (cancel_entry_time >= '2011-01-04
05:47:48.463365'::timestamp without time zo
ne)
"cancel_entry_time_upper_bound" CHECK (cancel_entry_time <= '2012-02-08
16:07:37.714466'::timestamp without time zo
ne)
"cancel_seqno_lower_bound" CHECK (cancel_seqno >= 80417910::bigint)
"cancel_seqno_upper_bound" CHECK (cancel_seqno <= 291703135::bigint)
"capture_date_matches_date_in_tid" CHECK (NOT tid ~~ '(2%)'::text OR
"substring"(tid, 2, 10)::date = capture_date)
NOT VALID
"capture_date_not_populated" CHECK (capture_date IS NULL)
"contracts_is_an_integer" CHECK ((tags -> 'contracts'::text) ~
'^[0-9]+$'::text)
"entry_time_lower_bound" CHECK (entry_time >= '2011-01-03
11:40:34.825558'::timestamp without time zone)
"entry_time_upper_bound" CHECK (entry_time <= '2012-02-08
16:07:37.714466'::timestamp without time zone)
"flow_tag" CHECK ((tags -> 'flow'::text) ~ '^[0-9]+$'::text) NOT VALID
"js_activity_2011_partition_constraint" CHECK (pnl_date >=
'2011-01-01'::date AND pnl_date <= '2011-12-31'::date)
"js_activity_booking_type_check" CHECK (booking_type = 'B'::bpchar OR
booking_type = 'A'::bpchar)
"js_activity_check" CHECK (is_canceled = id)
"js_activity_discretionary_agent_matches_tag" CHECK
(discretionary_agent = (tags -> 'agent'::text))
"js_activity_no_suspense_accounts_check" CHECK (account !~
'^SUSPENSE-.+$'::text)
"js_activity_pnl_date_on_weekday" CHECK (date_part('dow'::text,
pnl_date) >= 1::double precision AND date_part('dow
'::text, pnl_date) <= 5::double precision)
"js_activity_security_code_non_blank" CHECK (security_code <> ''::text)
"otc_settlement_system_consistency" CHECK (NOT executing_exchange ~~
'OTC_%'::text OR settlement_system = "substrin
g"(executing_exchange, 5)) NOT VALID
"price_extra_info_tags_come_together" CHECK ((NOT tags ?
'not_final_price'::text OR (tags -> 'not_final_price'::tex
t) = ''::text) AND (tags ? 'price_extra_data'::text) = (tags ?
'price_kind'::text) AND (NOT tags ? 'not_final_price'::t
ext OR tags ? 'price_kind'::text)) NOT VALID
"security_code_not_blank_on_either_end" CHECK (security_code !~~ '%
'::text AND security_code !~~ ' %'::text) NOT V
ALID
"seqno_lower_bound" CHECK (seqno >= 79792315::bigint)
"seqno_not_null" CHECK (seqno IS NOT NULL) NOT VALID
"seqno_upper_bound" CHECK (seqno <= 291695496::bigint)
"trades_have_times" CHECK (trade_type <> 'Trade'::text OR activity_time
IS NOT NULL) NOT VALID
"valid_counterparty" CHECK (counterparty !~~ '% '::text AND
counterparty !~~ ' %'::text) NOT VALID
"valid_executing_broker" CHECK (executing_broker !~~ '% '::text AND
executing_broker !~~ ' %'::text) NOT VALID
"valid_order_mark" CHECK (order_mark = ANY (ARRAY['T'::text, 'X'::text,
'S'::text])) NOT VALID
"valid_settlement_system" CHECK (settlement_system ~ '^[A-Z]+$'::text)
NOT VALID
"valid_terms_tag" CHECK ((tags -> 'terms'::text) ~
'^[A-Z0-9]{4}$'::text) NOT VALID
Foreign-key constraints:
"js_activity_2011_account_matches_cf" FOREIGN KEY (account,
clearing_firm) REFERENCES lu_pnl_fr_accounts(account_co
de, clearing_firm)
Triggers:
js_activity_is_frozen AFTER INSERT OR DELETE OR UPDATE ON
js_activity_20110101 FOR EACH ROW EXECUTE PROCEDURE js_ac
tivity_is_frozen()
Inherits: js_activity
Has OIDs: no

proddb_testing=# SELECT
conname,convalidated,conislocal,coninhcount,connoinherit
proddb_testing-# FROM pg_constraint WHERE conrelid =
'js_activity_20110101'::regclass;
conname | convalidated | conislocal |
coninhcount | connoinherit
---------------------------------------------+--------------+------------+-------------+--------------
price_extra_info_tags_come_together | f | f |
1 | f
cancel_entry_time_upper_bound | t | t |
0 | f
cancel_seqno_upper_bound | t | t |
0 | f
activity_time_upper_bound | t | t |
0 | f
seqno_upper_bound | t | t |
0 | f
entry_time_upper_bound | t | t |
0 | f
activity_date_upper_bound | t | t |
0 | f
cancel_entry_time_lower_bound | t | t |
0 | f
cancel_seqno_lower_bound | t | t |
0 | f
activity_time_lower_bound | t | t |
0 | f
seqno_lower_bound | t | t |
0 | f
entry_time_lower_bound | t | t |
0 | f
activity_date_lower_bound | t | t |
0 | f
capture_date_not_populated | t | t |
0 | f
capture_date_matches_date_in_tid | f | f |
1 | f
flow_tag | f | f |
1 | f
at_most_one_riskless_principal_for_bk | f | f |
1 | f
at_most_one_clears_on_bk | f | f |
1 | f
valid_terms_tag | f | f |
1 | f
valid_settlement_system | f | f |
1 | f
otc_settlement_system_consistency | f | f |
1 | f
security_code_not_blank_on_either_end | f | t |
1 | f
trades_have_times | f | t |
1 | f
js_activity_pnl_date_on_weekday | t | t |
1 | f
js_activity_no_suspense_accounts_check | t | t |
1 | f
js_activity_discretionary_agent_matches_tag | t | t |
1 | f
js_activity_check | t | t |
1 | f
js_activity_booking_type_check | t | t |
1 | f
contracts_is_an_integer | t | t |
1 | f
valid_order_mark | f | t |
1 | f
valid_executing_broker | f | t |
1 | f
valid_counterparty | f | t |
1 | f
cancel_consistency | t | t |
1 | f
js_activity_security_code_non_blank | t | t |
1 | f
seqno_not_null | f | t |
1 | f
js_activity_2011_partition_constraint | t | t |
0 | f
js_activity_2011_account_matches_cf | t | t |
0 | t
(37 rows)

proddb_testing=# SELECT
conname,convalidated,conislocal,coninhcount,connoinherit
FROM pg_constraint WHERE conrelid = 'js_activity'::regclass;
conname | convalidated | conislocal |
coninhcount | connoinherit
---------------------------------------------+--------------+------------+-------------+--------------
price_extra_info_tags_come_together | f | t |
0 | f
capture_date_matches_date_in_tid | f | t |
0 | f
flow_tag | f | t |
0 | f
at_most_one_riskless_principal_for_bk | f | t |
0 | f
at_most_one_clears_on_bk | f | t |
0 | f
valid_terms_tag | f | t |
0 | f
valid_settlement_system | f | t |
0 | f
otc_settlement_system_consistency | f | t |
0 | f
trades_have_times | f | t |
0 | f
security_code_not_blank_on_either_end | f | t |
0 | f
cancel_consistency | t | t |
0 | f
valid_counterparty | f | t |
0 | f
valid_executing_broker | f | t |
0 | f
valid_order_mark | f | t |
0 | f
seqno_not_null | f | t |
0 | f
nothing_to_see_here | t | t |
0 | t
contracts_is_an_integer | t | t |
0 | f
js_activity_booking_type_check | t | t |
0 | f
js_activity_check | t | t |
0 | f
js_activity_discretionary_agent_matches_tag | t | t |
0 | f
js_activity_no_suspense_accounts_check | t | t |
0 | f
js_activity_pnl_date_on_weekday | t | t |
0 | f
js_activity_security_code_non_blank | t | t |
0 | f
js_activity_clearing_firm_fkey1 | t | t |
0 | t
js_activity_currency_fkey1 | t | t |
0 | t
js_activity_no_rows | t | t |
0 | t
js_activity_trade_type_fkey1 | t | t |
0 | t
(27 rows)

In response to

Re: Problems with pg_upgrade after change of unix user running db. at 2016-10-03 14:54:01 from Tom Lane

Responses

Re: Problems with pg_upgrade after change of unix user running db. at 2016-10-03 20:01:03 from Tom Lane

Browse pgsql-general by date

	From	Date	Subject
Next Message	Edson Richter	2016-10-03 16:24:49	Re: Installing pgAdmin 4 in Oracle Enterprise Linux 7
Previous Message	Tom Lane	2016-10-03 14:54:01	Re: Problems with pg_upgrade after change of unix user running db.