On 3 October 2016 at 15:54, Tom Lane <t...@sss.pgh.pa.us> wrote: > Benedikt Grundmann <bgrundm...@janestreet.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: proddb_testing=# \d+ js_activity Table "public.js_activity" Column | Type | Modifiers | Storage | Stats target | Description ---------------------------+-----------------------------+------------------------------------------------------------- --------+----------+--------------+------------- id | text | not null default nextval('pnl_fr_imported_trades_id_seq'::re gclass) | extended | | tid | text | not null default currval('pnl_fr_imported_trades_id_seq'::re gclass) | extended | | entry_time | timestamp without time zone | not null default now() | plain | | pnl_date | date | not null | plain | | activity_date | date | not null | plain | | activity_time | time without time zone | | plain | | projected_settlement_date | date | not null | plain | | clearing_firm | text | not null | extended | | currency | text | not null | extended | | account | text | not null | extended | | security_code | text | not null | extended | | trade_type | text | not null | extended | | quantity | numeric(28,8) | not null | main | | gross_price | numeric(28,8) | not null default 0 | main | | net_price | numeric(28,8) | not null default 0 | main | | net_cash | numeric(28,8) | not null default 0 | main | | accrual | numeric(28,8) | not null default 0 | main | | commission_amount | numeric(28,8) | not null default 0 | main | | commission_rate | numeric(28,8) | not null default 0 | main | | narrative | text | not null default ''::text | extended | | counterparty | text | not null | extended | | executing_exchange | text | not null default ''::text | extended | | route | text | not null default ''::text | extended | | source | text | not null default ''::text | extended | | tags | hstore | not null default ''::hstore | extended | | booking_type | character(1) | not null default 'B'::bpchar | extended | | is_canceled | text | | extended | | executing_broker | text | not null default ''::text | extended | | version_tags | hstore | not null default ''::hstore | extended | | discretionary_agent | text | not null default ''::text | extended | | shape_id | text | not null default ''::text | extended | | capture_date | date | | plain | | order_id | text | | extended | | order_mark | text | | extended | | seqno | bigint | default strict_sequence() | plain | | system | text | | extended | | tplus | integer | | plain | | trade_date | date | | plain | | trader | text | | extended | | username | text | | extended | | cancel_username | text | | extended | | cancel_entry_time | timestamp without time zone | | plain | | cancel_narrative | text | | extended | | cancel_seqno | bigint | | plain | | settlement_system | text | | 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 proddb_testing=# \d+ js_activity_20110101 Table "public.js_activity_20110101" Column | Type | Modifiers | Storage | Stats target | Description ---------------------------+-----------------------------+------------------------------------------------------------- --------+----------+--------------+------------- id | text | not null default nextval('pnl_fr_imported_trades_id_seq'::re gclass) | extended | | tid | text | not null default currval('pnl_fr_imported_trades_id_seq'::re gclass) | extended | | entry_time | timestamp without time zone | not null default now() | plain | | pnl_date | date | not null | plain | | activity_date | date | not null | plain | | activity_time | time without time zone | | plain | | projected_settlement_date | date | not null | plain | | clearing_firm | text | not null | extended | | currency | text | not null | extended | | account | text | not null | extended | | security_code | text | not null | extended | | trade_type | text | not null | extended | | quantity | numeric(28,8) | not null | main | | gross_price | numeric(28,8) | not null default 0 | main | | net_price | numeric(28,8) | not null default 0 | main | | net_cash | numeric(28,8) | not null default 0 | main | | accrual | numeric(28,8) | not null default 0 | main | | commission_amount | numeric(28,8) | not null default 0 | main | | commission_rate | numeric(28,8) | not null default 0 | main | | narrative | text | not null default ''::text | extended | | counterparty | text | not null | extended | | executing_exchange | text | not null default ''::text | extended | | route | text | not null default ''::text | extended | | source | text | not null default ''::text | extended | | tags | hstore | not null default ''::hstore | extended | | booking_type | character(1) | not null default 'B'::bpchar | extended | | is_canceled | text | | extended | | executing_broker | text | not null default ''::text | extended | | version_tags | hstore | not null default ''::hstore | extended | | discretionary_agent | text | not null default ''::text | extended | | shape_id | text | not null default ''::text | extended | | capture_date | date | | plain | | order_id | text | | extended | | order_mark | text | | extended | | seqno | bigint | default strict_sequence() | plain | | system | text | | extended | | tplus | integer | | plain | | trade_date | date | | plain | | trader | text | | extended | | username | text | | extended | | cancel_username | text | | extended | | cancel_entry_time | timestamp without time zone | | plain | | cancel_narrative | text | | extended | | cancel_seqno | bigint | | plain | | settlement_system | text | | 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)