Home > mailing lists

Re: Problems with pg_upgrade after change of unix user running db. - Mailing list pgsql-general

From	Benedikt Grundmann
Subject	Re: Problems with pg_upgrade after change of unix user running db.
Date	October 3, 2016 17:58:37
Msg-id	CADbMkNPhMJdAU9L4eWNFXurE8pn6u6d8_20E-EjuQyoDhBd7jQ@mail.gmail.com Whole thread Raw
In response to	Re: Problems with pg_upgrade after change of unix user running db. (Tom Lane <tgl@sss.pgh.pa.us>)
Responses	Re: Problems with pg_upgrade after change of unix user running db.
List	pgsql-general

Tree view

On 3 October 2016 at 15:54, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Benedikt Grundmann <bgrundmann@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)

pgsql-general by date:

From: Tom Lane
Date: 03 October 2016, 17:54:13
Subject: Re: Problems with pg_upgrade after change of unix user running db.

From: Edson Richter
Date: 03 October 2016, 19:24:57
Subject: Re: Installing pgAdmin 4 in Oracle Enterprise Linux 7

Re: Problems with pg_upgrade after change of unix user running db. - Mailing list pgsql-general

Previous

Next