On Fri, Jan 09, 2004 at 08:02:16PM -0500, Tom Lane wrote:
> Kragen Sitaker <kragen+pgsql@airwave.com> writes:
> > We'll run the experiment again. Should we try 7.3.3 too?
>
> No, I don't think 7.3.3 is likely to behave differently from 7.3.4
> as far as this goes. What would actually be interesting is whether
> you can make 7.4 fail.
We'll do our best.
> > Well, it's possible the daemon could have gotten killed while it was
> > inside the transaction, followed shortly by a shutdown of postgres ---
> > a dozen times or more --- and during development, we frequently kill
> > the daemon so that it will restart with new code.
>
> But you're seeing these errors in production, on a machine where you're
> not doing that, no? In any case there is code in place to clean out
> a temp schema of any pre-existing junk when a new backend starts to use
> it ... perhaps there's a bug in that, but that code was not changed
> since 7.3.2 ...
I'm not sure what kind of pre-existing junk could cause this problem,
anyway. Leftover ordinary rows in pg_class would cause the daemon to
fail as soon as it started, not after running for hours, and as you
pointed out, would normally give us a higher-level error message that
didn't mention relations from pg_catalog.
> Another question: are you fairly confident that if the same bug had been
> in 7.3.2, you would have found it? Were there any changes in your usage
> patterns around the time you adopted 7.3.4?
Actually, yes and yes; we revamped the transaction handling in the rest
of the system, using autocommit most of the time and wrapping various
chunks of the system in transactions. The particular query that's at
fault didn't change noticeably (we added another column to it, from
a table it was already selecting half a dozen or so columns from),
but it's running inside of a transaction all the time, same as before.
The only relevant difference in this daemon is that its transaction used
to start as soon as it had committed its previous changes, followed by
a 15-second sleep; now it starts after the end of the 15-second sleep,
just before we start doing database actions.
The other tables it's selecting from didn't change much, but all the
processes updating them changed their transactional style.
Of course, my ideas about what could affect this problem are pretty fuzzy.
I'll retest again against 7.3.2 to make sure it's not a change we
introduced into our code around the same time.
Oh, by the way, we aren't aware of any production machines that don't
have this problem.
> > For our application, we shut down and restart Postgres every night
> > because it seems to make VACUUM FULL work better.
>
> [ itch... ] Let's not discuss the wisdom of that just now, but ...
We started doing it on Postgres 7.2, where we determined empirically
that not doing it made the database a little slower every day. We may
not need it anymore with pg_autovacuum and 7.3.
> If you're not planning to go to 7.4 soon, you might want to think about
> an update to 7.3.5, just on general principles.
We did test on 7.3.5, with the same database contents as on 7.3.4.
Didn't help. But of course updating to 7.3.5 is probably a good idea
anyway.
Thank you very much for your help.
-Kragen
