Thread: Can VACUUM, but cannot do VACUUM ANALYZE
Hi! Every couple of days I run "vacuum analyze" on every db on my system (from a shell script). Now there is one small database (two tables, one SQL-function) where I get the following (in psql): > albert@frithjof:/home/albert > psql > Welcome to the POSTGRESQL interactive sql monitor: > Please read the file COPYRIGHT for copyright terms of POSTGRESQL > [PostgreSQL 6.5.1 on i586-pc-linux-gnulibc1, compiled by gcc 2.7.2.1] > > type \? for help on slash commands > type \q to quit > type \g or terminate with semicolon to execute query > You are currently connected to the database: albert > > albert=> vacuum; > VACUUM > albert=> vacuum analyze; > NOTICE: AbortTransaction and not in in-progress state > pqReadData() -- backend closed the channel unexpectedly. > This probably means the backend terminated abnormally > before or while processing the request. > We have lost the connection to the backend, so further processing is impossible. Terminating. > I.e., I can VACUUM, but I cannot do VACUUM ANALYZE. Why should analyze do an ABORT? By the way, I do not notice any strange behavior with this database, and pg_dump works fine on it. Any ideas what might be at fault and how to fix this? Thanks in advance, Albert. -- -------------------------------------------------------------------------- Albert Reiner <areiner@tph.tuwien.ac.at> Deutsch * English * Esperanto * Latine --------------------------------------------------------------------------
On Sat, 23 Oct 1999, Albert REINER wrote: > I.e., I can VACUUM, but I cannot do VACUUM ANALYZE. Why should analyze > do an ABORT? By the way, I do not notice any strange behavior with > this database, and pg_dump works fine on it. Once I saw similar behaviour. I found thta the problem was in broken strcoll() function in glibc2; I compiled Postgres with locale support. After upgrading (Debain GNU/Linux 2.0 to 2.1) I got newer libc, and the problem gone away. What system/config you are running? OS, compiler, postgres version, compilation flags? Oleg. ---- Oleg Broytmann http://members.xoom.com/phd2/ phd2@earthling.net Programmers don't die, they justGOSUB without RETURN.
Oleg Broytmann wrote: >On Sat, 23 Oct 1999, Albert REINER wrote: >> I.e., I can VACUUM, but I cannot do VACUUM ANALYZE. Whyshould analyze >> do an ABORT? By the way, I do not notice any strange behavior with >> this database, and pg_dump worksfine on it. > > Once I saw similar behaviour. I found thta the problem was in broken >strcoll() function in glibc2;I compiled Postgres with locale support. >After upgrading (Debain GNU/Linux 2.0 to 2.1) I got newer libc, and the>problem gone away. > What system/config you are running? OS, compiler, postgres version, >compilation flags? There was also a specific vacuum analyze bug in vc_abort, fixed in 6.5.2. The error log showed something like ERROR: vacuum: can't destroy lock file! NOTICE: AbortTransaction and not in in-progress state See the pgsql-hackers archive around 10th and 13th August. -- Vote against SPAM: http://www.politik-digital.de/spam/ ======================================== Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID32B8FAA1 ======================================== "I press toward the mark for the prize of the high calling of God in Christ Jesus." Philippians 3:14
"Albert REINER" <areiner@tph.tuwien.ac.at> writes:
>> albert=> vacuum analyze;
>> NOTICE: AbortTransaction and not in in-progress state
>> pqReadData() -- backend closed the channel unexpectedly.
This unhelpful trace is the result of the interaction of a couple
of different bugs --- first, vacuum is encountering some sort of
error; then error recovery is messing up in the context of vacuum
(leading first to the NOTICE and then to the coredump); and when
libpq observes connection closure, it drops the original error
message (which it has already received!) on the floor in its haste
to tell you about the closed connection.
The error recovery problem is fixed as of 6.5.2, I believe, so it'd
be worth your while to upgrade. The libpq misbehavior is fixed for
7.0, but it's part of some extensive changes that seemed too risky
to back-patch into 6.5.*. In the meantime, the only way to find out
what that original error message was is to consult the postmaster log
file --- you are running the postmaster with stdout/stderr going to
some logfile, I hope. It should show up right before the NOTICE and
then the postmaster's response to the backend coredump.
Once we have vacuum's error message we can maybe offer some useful
counsel.
regards, tom lane
Thanks for your reply!
On Sat, Oct 23, 1999 at 12:04:38PM +0000, Oleg Broytmann wrote:
> On Sat, 23 Oct 1999, Albert REINER wrote:
> > I.e., I can VACUUM, but I cannot do VACUUM ANALYZE. Why should analyze
...
> Once I saw similar behaviour. I found thta the problem was in broken
> strcoll() function in glibc2; I compiled Postgres with locale support.
> After upgrading (Debain GNU/Linux 2.0 to 2.1) I got newer libc, and the
> problem gone away.
> What system/config you are running? OS, compiler, postgres version,
> compilation flags?
>From my original post:
> > albert@frithjof:/home/albert > psql
> > Welcome to the POSTGRESQL interactive sql monitor:
> > Please read the file COPYRIGHT for copyright terms of POSTGRESQL
> > [PostgreSQL 6.5.1 on i586-pc-linux-gnulibc1, compiled by gcc 2.7.2.1] ^^^^^ ^^^^^^^^^^^^^^^^^^^^^^
^^^^^^^^^^^
I'm still using Linux kernel 2.0.35 (SuSE 5.3), but planning to
upgrade the kernel. I also built Postgres 6.5.1 with locale support.
>
> Oleg.
Thanks,
Albert.
--
--------------------------------------------------------------------------
Albert Reiner <areiner@tph.tuwien.ac.at>
Deutsch * English * Esperanto * Latine
--------------------------------------------------------------------------
"Albert REINER" <areiner@tph.tuwien.ac.at> writes:
> I'm still using Linux kernel 2.0.35 (SuSE 5.3), but planning to
> upgrade the kernel. I also built Postgres 6.5.1 with locale support.
Hmm. I recall several different reports of weird problems seen with
concurrently running backends that went away after an upgrade from
kernel 2.0.35 or earlier to 2.0.36 or later. Apparently, some
significant bugs in Linux's handling of SysV semaphores were corrected
at 2.0.36 --- and Postgres depends heavily on semaphores. However,
I wouldn't really expect that to be a problem unless you were running
several backends concurrently. It wasn't clear if you had other
things going on besides the VACUUM or not.
I forgot to mention yesterday: if you track down the vacuum error
message as I suggested, and it's something about "can't destroy
lock file", then you just need to update to 6.5.2 and the problem
should go away. If it's something else then more investigation
will be needed.
regards, tom lane
On Sun, Oct 24, 1999 at 01:31:06PM -0400, Tom Lane wrote: > "Albert REINER" <areiner@tph.tuwien.ac.at> writes: ... > at 2.0.36 --- and Postgres depends heavily on semaphores. However, > I wouldn't really expect that to be a problem unless you were running > several backends concurrently. It wasn't clear if you had other > things going on besides the VACUUM or not. No, just the vacuum. > I forgot to mention yesterday: if you track down the vacuum error > message as I suggested, and it's something about "can't destroy > lock file", then you just need to update to 6.5.2 and the problem > should go away. If it's something else then more investigation > will be needed. Thank you for this help. I'll try as soon as possible. By the way, is this problem likely to affect data integrity or the basic functioning of the database? As far as I understand, the vacuum-analyze failure only means that some queries are slower than necessary, or not? Bye, Albert. -- -------------------------------------------------------------------------- Albert Reiner <areiner@tph.tuwien.ac.at> Deutsch * English * Esperanto * Latine --------------------------------------------------------------------------