> 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.
