Re: BUG #8673: Could not open file "pg_multixact/members/xxxx" on slave during hot_standby

2014-06-02 17:10 GMT+03:00 Alvaro Herrera <alvherre@2ndquadrant.com>:

> Serge Negodyuck wrote:
> > Hello,
> >
> > I've upgraded postgresql to version 9.3.4 and did fresh initdb and
> restored
> > database from sql backup.
> > According to 9.4.3 changelog issue with multixact wraparound was fixed.
>
> Ouch.  This is rather strange.  First I see the failing multixact has
> 8684 members, which is totally unusual.  My guess is that you have code
> that creates lots of subtransactions, and perhaps does something to one
> tuple in a different subtransaction; doing sometihng like that would be,
> I think, the only way to get subxacts that large.  Does that sound
> right?
>
> It sounds like you are right. I've found a lot of inserts in logs. Each
insert cause trigger to be performed. This  trigger updates counter in
other table.
It is very possible this tirgger tries to update the same counter for
different inserts.



> > An ugly hack "cp pg_multixact/members/14077 pg_multixact/members/14078"
> > helped me to start master server in replica.
>
> This is the second weird thing.  How come you needed to create 14078
> when you already had that file, according to the listing you pasted
> above?  Or is the 14078 file present in the listing only because you
> copied it?
>
14078 file present in the listing only because I  copied it.
It was the only corrupt file on master server.



>
> > Then, did pg_basebackup to slave database. It does not help
> > 2014-06-02 09:58:49 EEST 172.18.10.17 db2 DETAIL: Could not open file
> > "pg_multixact/members/1112D": No such file or directory.
> > 2014-06-02 09:58:49 EEST 172.18.10.18 db2 DETAIL: Could not open file
> > "pg_multixact/members/11130": No such file or directory.
> > 2014-06-02 09:58:51 EEST 172.18.10.34 db2 DETAIL: Could not open file
> > "pg_multixact/members/11145": No such file or directory.
> > 2014-06-02 09:58:51 EEST 172.18.10.38 db2 DETAIL: Could not open file
> > "pg_multixact/members/13F76": No such file or directory
>
> Are these the only files missing?  Are intermediate files there?
>


Only 0000 - 001E files were present on slave server.



>
> > What additional information should I provide?
> > If I will increase autovacuum_multixact_freeze_max_age will it help?
> (Now I
> > have default value)
>
> No, you'd need to *decrease* the freezing age, so that things are
> cleaned up sooner.  If you increase the freezing age, the problematic
> multis would persist longer, causing more trouble.
>
>
> I think I will have to reproduce your scenario to be able to understand
> what is going on.  If you can describe it in detail, that would make it
> easier for me.
>
Try this simplifiled schema:

CREATE TABLE company (
id integer NOT NULL,
num_products integer );

CREATE TABLE product (
id integer NOT NULL,
company_id integer );

ALTER TABLE ONLY company ADD CONSTRAINT company_pkey PRIMARY KEY (id);
ALTER TABLE ONLY product ADD CONSTRAINT product_pkey PRIMARY KEY (id);
ALTER TABLE ONLY product ADD CONSTRAINT product_company_id_fk FOREIGN KEY
(company_id) REFERENCES company(id) ON DELETE CASCADE;

CREATE FUNCTION update_num_products() RETURNS trigger
LANGUAGE plpythonu
AS $$

old, new, event, when, table_name = TD["old"], TD["new"], TD["event"],
TD["when"], TD["table_name"]

def update_company(company_id, delta):
plpy.execute("""
UPDATE company
SET num_products = coalesce(num_products, 0) + %(delta)s
WHERE id = %(company_id)s
""" % {'company_id': company_id, 'delta': delta})

if table_name.lower() == 'product':
if when == 'AFTER' and event == 'INSERT':
if new['status'] == 0 and new['visible_on_site'] is not False:
update_company(new['company_id'], 1)
return 'OK'
$$;

CREATE TRIGGER update_num_products AFTER INSERT OR UPDATE ON product FOR
EACH ROW EXECUTE PROCEDURE update_num_products();
INSERT into company VALUES (0,1);

Do a lot of inserts into product with the same product.company_id;