Thread: ERROR: could not open file "pg_subtrans...

ERROR: could not open file "pg_subtrans...

From
Warren
Date:
I have Postgres 8.1.3 running on Windows XP. I keep seeing this error in the log files:

2007-09-29 10:11:29 ERROR:  could not access status of transaction 5745045
2007-09-29 10:11:29 DETAIL:  could not open file "pg_subtrans/0057": Invalid argument

I am also getting the following in the Windows Applcation event log:

FATAL:  could not reattach to shared memory (key=5432001, addr=01C80000): Invalid argument

It is causing quite a few problems. I have searched around and have not found to much on it. I have
the same database/hardware set-up on two other machines and they work fine. I have not tried to
reinstall postgres yet. I know it only happens when three particular tables are being used. I have
droped these tables and created new ones and I am still geting the error. These tables get a lot of
insert, updates and deletes. The three table schemas are:


CREATE TABLE scanorder
(
   scn_pk int4 NOT NULL DEFAULT nextval(('"scanorder_scn_pk_seq"'::text)::regclass),
   scn_usr_fk int4,
   scn_str_fk int4,
   scn_ogp_fk int4,
   scn_date timestamp,
   scn_processed_date timestamp,
   scn_report bool NOT NULL DEFAULT false,
   scn_processed bool NOT NULL DEFAULT false,
   scn_sent bool NOT NULL DEFAULT false,
   scn_sending bool NOT NULL DEFAULT false,
   scn_total float4,
   scn_time_changed timestamp,
   CONSTRAINT scanorder_pkey PRIMARY KEY (scn_pk)
)

CREATE TABLE order_
(
   ord_pk int4 NOT NULL DEFAULT nextval(('"order_ord_pk_seq"'::text)::regclass),
   ord_usr_fk int4,
   ord_str_fk int4,
   ord_scn_fk int4,
   ord_ven_fk int4,
   ord_ogp_fk int4,
   ord_date timestamp,
   ord_processed_date timestamp,
   ord_delivered_date timestamp,
   ord_received_date timestamp,
   ord_po_number char(25),
   ord_invoice_number char(25),
   ord_account_number char(30),
   ord_vendor_code char(10),
   ord_vendor_name char(25),
   ord_report bool NOT NULL DEFAULT false,
   ord_processed bool NOT NULL DEFAULT false,
   ord_delivered bool NOT NULL DEFAULT false,
   ord_received bool NOT NULL DEFAULT false,
   ord_time_changed timestamp,
   ord_memo char(30),
   ord_sent_to char(25),
   CONSTRAINT order_pkey PRIMARY KEY (ord_pk),
   CONSTRAINT order_scanorder_ord_scn_fk FOREIGN KEY (ord_scn_fk)
       REFERENCES scanorder (scn_pk) MATCH SIMPLE
       ON UPDATE RESTRICT ON DELETE CASCADE
)

CREATE TABLE order_
(
   ord_pk int4 NOT NULL DEFAULT nextval(('"order_ord_pk_seq"'::text)::regclass),
   ord_usr_fk int4,
   ord_str_fk int4,
   ord_scn_fk int4,
   ord_ven_fk int4,
   ord_ogp_fk int4,
   ord_date timestamp,
   ord_processed_date timestamp,
   ord_delivered_date timestamp,
   ord_received_date timestamp,
   ord_po_number char(25),
   ord_invoice_number char(25),
   ord_account_number char(30),
   ord_vendor_code char(10),
   ord_vendor_name char(25),
   ord_report bool NOT NULL DEFAULT false,
   ord_processed bool NOT NULL DEFAULT false,
   ord_delivered bool NOT NULL DEFAULT false,
   ord_received bool NOT NULL DEFAULT false,
   ord_time_changed timestamp,
   ord_memo char(30),
   ord_sent_to char(25),
   CONSTRAINT order_pkey PRIMARY KEY (ord_pk),
   CONSTRAINT order_scanorder_ord_scn_fk FOREIGN KEY (ord_scn_fk)
       REFERENCES scanorder (scn_pk) MATCH SIMPLE
       ON UPDATE RESTRICT ON DELETE CASCADE
)

I am a complete novice with postgres so bare with me.

--
Thanks,

Warren Bell

Re: ERROR: could not open file "pg_subtrans...

From
Alvaro Herrera
Date:
Warren wrote:
> I have Postgres 8.1.3 running on Windows XP. I keep seeing this error in
> the log files:

Update to 8.1.3.

Better yet, update to 8.2.5, as 8.1 is unsupported on Windows.

http://www.postgresql.org/about/news.865


--
Alvaro Herrera       Valdivia, Chile   ICBM: S 39º 49' 18.1", W 73º 13' 56.4"
"La gente vulgar solo piensa en pasar el tiempo;
el que tiene talento, en aprovecharlo"

Re: ERROR: could not open file "pg_subtrans...

From
"Magnus Hagander"
Date:
> > I have Postgres 8.1.3 running on Windows XP. I keep seeing this error in
> > the log files:
>
> Update to 8.1.3.

I'm sure Alvaro means 8.1.10..

> Better yet, update to 8.2.5, as 8.1 is unsupported on Windows.
>
> http://www.postgresql.org/about/news.865

Since we haven't released 8.3 yet, it's still suported. That said, it's still good advice since there's a reason we're
droppingthat support soon. 

The first thing to do with an error like this, though, is to look for and remove any antivirus, antispyware or such
programs.

/Magnus
>
> --
> Alvaro Herrera       Valdivia, Chile   ICBM: S 39º 49' 18.1", W 73º 13' 56.4"
> "La gente vulgar solo piensa en pasar el tiempo;
> el que tiene talento, en aprovecharlo"
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>