Обсуждение: could not open relation - why?

"Harald Armin Massa" <haraldarminmassa@gmail.com> writes:
> within my log there is the following snippet:

> 2006-08-07 13:20:27 LOG:  autovacuum: processing database "template1"
> 2006-08-07 13:20:47 ERROR:  could not open relation with OID 311438
> 2006-08-07 13:21:52 LOG:  autovacuum: processing database "postgres"

The easiest explanation is that someone dropped a table just as
autovacuum was trying to open it.  The above trace suggests that
you're actively changing template1, which is probably not good
practice ...

            regards, tom lane

"Harald Armin Massa" <haraldarminmassa@gmail.com> writes:
>> The easiest explanation is that someone dropped a table just as
>> autovacuum was trying to open it.

> I am not quite sure that "autovacuum" was trying to open, as some user
> reported the same error on his system ( and he is definitely not mr.
> Autovacuum :)

Oh, I assumed you had reason to think that the error message came from
autovacuum.  It could easily have been the same situation except two
unrelated processes.

> What indeed happens alot in this database is the creation and the dropping
> of temp tables (the later automagically at the end of a connection, I
> assume)

Hmm ... but why would one process be trying to open another one's temp
table?  The built-in stuff tries to avoid that, for the most part.
What was that user doing, exactly, when he got the error?

> Is there a way to learn to which dropped table OIDs belong, or is all gone
> after dropping and autovacuum ?

No, not easily --- once the table is dropped the info is gone.  You
could try turning on log_error_statement so you could see what SQL
operation is provoking the error; that might help figure it out.

            regards, tom lane

I had the same problem reported by VACUUM, and I traced it down to an
individual table, for which SELECT * would return the exact same
message.

As far as I know, no process or person has tried to drop the table at
any point. Luckily it's a table populated by an importer every day, and
I have a copy of the schema, so no harm done. But what should I look for
to avoid this happening again?

Also, I've been looking, and recently VACUUM has been reporting that
max_fsm_pages needed to be increased, which I have just done. What can
happen is max_fsm_pages is not high enough? Could it be responsible for
this corruption? What problems could it cause?

And a final question. I've taken over the administration of a Postgres
server. I use the user manual and in general go online for info. But can
someone recommend a good book (or a good source) with the nuts and bolts
of administering and explaining Postgres?

Many thanks
Jaime


-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Monday, August 07, 2006 9:58 AM
To: Harald Armin Massa
Cc: pgsql-general
Subject: Re: [GENERAL] could not open relation - why?

"Harald Armin Massa" <haraldarminmassa@gmail.com> writes:
>> The easiest explanation is that someone dropped a table just as
>> autovacuum was trying to open it.

> I am not quite sure that "autovacuum" was trying to open, as some user
> reported the same error on his system ( and he is definitely not mr.
> Autovacuum :)

Oh, I assumed you had reason to think that the error message came from
autovacuum.  It could easily have been the same situation except two
unrelated processes.

> What indeed happens alot in this database is the creation and the
dropping
> of temp tables (the later automagically at the end of a connection, I
> assume)

Hmm ... but why would one process be trying to open another one's temp
table?  The built-in stuff tries to avoid that, for the most part.
What was that user doing, exactly, when he got the error?

> Is there a way to learn to which dropped table OIDs belong, or is all
gone
> after dropping and autovacuum ?

No, not easily --- once the table is dropped the info is gone.  You
could try turning on log_error_statement so you could see what SQL
operation is provoking the error; that might help figure it out.

            regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq



***********************************************************************
Bear Stearns is not responsible for any recommendation, solicitation,
offer or agreement or any information about any transaction, customer
account or account activity contained in this communication.

Bear Stearns does not provide tax, legal or accounting advice.  You
should consult your own tax, legal and accounting advisors before
engaging in any transaction. In order for Bear Stearns to comply with
Internal Revenue Service Circular 230 (if applicable), you are notified
that any discussion of U.S. federal tax issues contained or referred to
herein is not intended or written to be used, and cannot be used, for
the purpose of:  (A) avoiding penalties that may be imposed under the
Internal Revenue Code; nor (B) promoting, marketing or recommending to
another party any transaction or matter addressed herein.
***********************************************************************

Hi,

I've been given a dataset (from Sybase) which has the timestamps
expressed as
"Jun 26 2002 12:18:56:903PM"

I'd like to use copy in Postgres to load these data, but can't see in
the (v8.1) docs
how to specify this format for Postgresql.

Can anyone help with this?


Thanks,

   Brent Wood

Brent Wood <b.wood@niwa.co.nz> writes:
> I've been given a dataset (from Sybase) which has the timestamps
> expressed as
> "Jun 26 2002 12:18:56:903PM"

That's pretty bizarre.  My advice is to run a quick perl or sed script
over the data and change the (I assume) fractional seconds delimiter
to a dot.  PG will take timestamps like
  "Jun 26 2002 12:18:56.903PM"

            regards, tom lane