Re: [GENERAL] vacuumdb problem

On Sun, 2 Jul 2000, Tom Lane wrote:

> Date: Sun, 02 Jul 2000 12:47:55 -0400
> From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> To: Marcin Inkielman <marn(at)wsisiz(dot)edu(dot)pl>
> Cc: pgsql-general(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org
> Subject: Re: [GENERAL] vacuumdb problem
>
> Marcin Inkielman <marn(at)wsisiz(dot)edu(dot)pl> writes:
> > NOTICE: FlushRelationBuffers(osoby, 228): block 223 is referenced
> > (private 0, global 1)
> > FATAL 1: VACUUM (vc_repair_frag): FlushRelationBuffers returned -2
>
> > this table is referenced in my db by a tree of FOREIGN KEYs.
>
> Hmm, I wonder whether there is a buffer-refcount leak in the foreign
> key stuff.
>
> > however my db seems to work and I am able to do pg_dump
> > Rescently, I dumped and restored it and for a few days I was able to
> > do vacuumdb. Today, the problem is here again.
>
> You will probably find that stopping and restarting the postmaster
> will make the problem go away (until it comes back again). Might
> be an acceptable workaround to let you vacuum, until we can find
> the bug.

thanks, it worked.

>
> Do you think you can extract a reproducible example? Short of that,
> it'd be helpful to at least see the declarations of "osoby" and all
> associated tables.
>
> regards, tom lane
>

sure..

CREATE TABLE "osoby" (
"Nazwisko" text NOT NULL,
"Imię" text NOT NULL,
"Drugie imię" text,
"Data urodzenia" date NOT NULL,
"Miejsce urodzenia" text,
"Nazwisko rodowe" text,
"Imię ojca" text,
"Imię matki" text,
"Nazwisko rodowe matki" text,
"Płeć" int2,
"Stan cywilny" int2,
"Numer dowodu osobistego" text,
"PESEL" text,
"NIP" text,
"Nazwa szkoły" text,
"Nr świadectwa" text,
"Data wystawienia świad" date,
"Miejsce wyst śwaid" text,
"ulica" text,
"kod" text,
"poczta" text,
"miejscowość" text,
"gmina" text,
"wojew" text,
"telefon" text,
"Adres koresp - ulica" text,
"Adres koresp - kod" text,
"Adres koresp - poczta" text,
"Adres koresp - miejscowość" text,
"Adres koresp - gmina" text,
"Adres koresp - wojew" text,
"Adres koresp - telefon" text,
"ob wojskowy" int2,
"WKU" int4,
"WKU - kod" text,
"WKU - miejscowość" text,
"WKU - ulica" text,
"osoba_id" int4 NOT NULL);

CREATE TABLE "teczki_t" (
"osoba_id" int4 NOT NULL,
"tr_wdz" int4 NOT NULL,
"dyplom_id" int2 NOT NULL,
"termin_egzaminu_id" int2 NOT NULL,
"teczka_id" int4 NOT NULL,
"pracownik_id" int4,
"stan_id" int4,
"data" datetime,
"pracownik_id_r" int4,
"data_r" datetime,
"uwagi" text,
"poz_kurs" int2,
"kurs_komp" int2,
FOREIGN KEY (osoba_id) REFERENCES "osoby"(osoba_id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (dyplom_id) REFERENCES "Dyplom"(symbol) ON UPDATE CASCADE,
FOREIGN KEY (stan_id) REFERENCES "stany"(stan_id) ON UPDATE CASCADE
);

CREATE TABLE "studenci" (
"numer_albumu" int4 NOT NULL,
"osoba_id" int4 NOT NULL,
"tr_wdz_id" int4 NOT NULL,
"grupa_id" int4,
"grupa_jez_id" int4,
"semestr" int4 NOT NULL,
"repeta" bool,
"warunki" int4,
"znacznik" int4,
FOREIGN KEY (tr_wdz_id) REFERENCES tr_wdz(tr_wdz_id) ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY (osoba_id) REFERENCES osoby(osoba_id) ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY (grupa_id) REFERENCES grupy_stud(grupa_id) ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY (grupa_jez_id) REFERENCES grupy_jez_stud(grupa_jez_id) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE "rejestr_decyzji" (
"decyzja_id" int4 NOT NULL,
"osoba_id" int4,
"numer_albumu" int4,
"data_pocz" date,
"data_konca" date,
"typ_decyzji_id" int4,
"podjeta_przez" int4,
"podjeta_dnia" datetime,
"anulowana_przez" int4,
"anulowana_dnia" datetime,
"uwagi" text,
FOREIGN KEY (osoba_id) REFERENCES "osoby"(osoba_id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (numer_albumu) REFERENCES album(numer_albumu) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE "konto" (
"osoba_id" int4 NOT NULL,
"login" text,
"gr_id" int4,
"alias" text,
"pass" text,
"homedir" text,
"ma_konto" bool,
FOREIGN KEY (osoba_id) REFERENCES "osoby"(osoba_id) ON DELETE CASCADE ON UPDATE CASCADE
);

/*only 2 indexes on osoby*/

CREATE UNIQUE INDEX "osoby_pkey" on "osoby" using btree ("osoba_id" "int4_ops" );
CREATE UNIQUE INDEX "osoby_Imię_key" on "osoby" using btree (
"Imię" "text_ops", "Nazwisko" "text_ops", "Data urodzenia" "date_ops" );

almost all this tables are referenced by other tables (however this
references are not cyclic). the db is not very large from the point of
view of data but it contains >200 tables.

I hope it will help a little...

regards

--
mi