Обсуждение: could not open relation - why?
Hello,
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"
now there are a number of possibilites, why PostgreSQL could not open:
a) the file is gone
b) no access priv
c) file is open locked for single use
d) ???
Is there any way I can get more detailed information, why this happened?
Harald
--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
Let's set so double the killer delete select all.
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"
now there are a number of possibilites, why PostgreSQL could not open:
a) the file is gone
b) no access priv
c) file is open locked for single use
d) ???
Is there any way I can get more detailed information, why this happened?
Harald
--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
Let's set so double the killer delete select all.
"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
> 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.
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 :)
you're actively changing template1, which is probably not good
practice ...
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)
Is there a way to learn to which dropped table OIDs belong, or is all gone after dropping and autovacuum ?
Harald
--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
Let's set so double the killer delete select all.
"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
> 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.
Surely my mistake in error reporting. Just cut out the "surrounding lines" from the log; like in contextual diff :(
"exactly" is a difficult question: he was creating a form. This form is build out of 5-7 main queries, which touch around 60% of the tables in the database. And one of these queries failed with this "cannot access" message appearing.
My reason for suspecting the temp tables is the coded process of:
1) connect to database
2) on this connection, create a temp table carrying the user-id
3) on this connection, create a temp table carrying a quarant-level
4) prepare a hanfull of quereies for this connection
5) query lots of tables and views
the views are something like
select col1, col2, col3
from
mastertable
where now() >= validfrom and now() <= validuntill
and mastertable.quarantlevel=get_quarant()
(with get_quarant() a function looking up the current value within the connections temp_table)
As the connections are handled in a pool, and the runtimesystem is able to close them (together with the temp tables), those where my suspects.
I put in > autovacuum. It could easily have been the same situation except two
> unrelated processes.
Surely my mistake in error reporting. Just cut out the "surrounding lines" from the log; like in contextual diff :(
> 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?
"exactly" is a difficult question: he was creating a form. This form is build out of 5-7 main queries, which touch around 60% of the tables in the database. And one of these queries failed with this "cannot access" message appearing.
My reason for suspecting the temp tables is the coded process of:
1) connect to database
2) on this connection, create a temp table carrying the user-id
3) on this connection, create a temp table carrying a quarant-level
4) prepare a hanfull of quereies for this connection
5) query lots of tables and views
the views are something like
select col1, col2, col3
from
mastertable
where now() >= validfrom and now() <= validuntill
and mastertable.quarantlevel=get_quarant()
(with get_quarant() a function looking up the current value within the connections temp_table)
As the connections are handled in a pool, and the runtimesystem is able to close them (together with the temp tables), those where my suspects.
>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.
log_min_error_statement = error
and feel better prepared for the next appearing error. This error occurred with a frequency of aroound once every month; so it will be some time until I see it again.
Thanks for all the information and help,
Harald
--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
Let's set so double the killer delete select all.
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