Thread: AbortTransaction and not in in-progress state
Hi,
I checked latest cvs and notice when I do 'vacuum analyze'
I got:
flats=> vacuum analyze;
NOTICE: AbortTransaction and not in in-progress state
NOTICE: AbortTransaction and not in in-progress state
23:09[om]:~/mian/flats/sql>psql flats
Connection to database 'flats' failed.
FATAL 1: Database 'flats' does not exist.
We know this because the directory '/usr/local/pgsql/data/base/flats' does not exist.
You can create a database with the SQL command CREATE DATABASE.
To see what databases exist, look at the subdirectories of '/usr/local/pgsql/data/base/'.
23:09[om]:~/mian/flats/sql>psql -l
NOTICE: AbortTransaction and not in in-progress state
NOTICE: AbortTransaction and not in in-progress state
23:10[om]:~/mian/flats/sql>psg post
431 p4 S 0:00 rlogin om -l postgres
6201 ? S 0:00 postmaster -i -B 1024 -S -D/usr/local/pgsql/data/ -o -Fe
Does this is a known problem ? I'm running linux 2.1.117, egcs 1.1.b,
pgsql was compiled with -O2 -mpentium options and --enable-locale.
After restoring database I tried again 'vacuum analyze' and it
produces the same result but I was able to do psql flats.
Nothing could do there though - on any commands I got
flats=> \dt
NOTICE: AbortTransaction and not in in-progress state
NOTICE: AbortTransaction and not in in-progress state
flats=> \q
Restarting postmaster helps a little bit, but after several operations
I got
flats=> \d work_flats
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally before or while processing the request.
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
> I checked latest cvs and notice when I do 'vacuum analyze'
> I got:
>
> flats=> vacuum analyze;
> NOTICE: AbortTransaction and not in in-progress state
> NOTICE: AbortTransaction and not in in-progress state
>
> 23:09[om]:~/mian/flats/sql>psql flats
> Connection to database 'flats' failed.
I see a small problem in running VACUUM ANALYZE on the regression
database which involves an entry in pg_operator left over from the
char16 datatype. If the line containing "1004" is removed and the system
is "make clean install; initdb"'d then vacuum completes successfully on
that database.
Can you try this test on the regression database (after removing that
line from pg_operator)?
make runtest
psql regression
regression=> vacuum analyze;
If that succeeds for you, then you will need to describe your test
scenerio in more detail. If it fails, then perhaps try backing-off of
your bleeding-edge compiler and kernel (maybe start by compiling -m486
instead of -mpentium). btw, I'm running Linux kernel 2.0.30, gcc 2.7.2.1
from RH4.2...
Regards.
- Tom
> > I checked latest cvs and notice when I do 'vacuum analyze' > > I got: > > > > flats=> vacuum analyze; > > NOTICE: AbortTransaction and not in in-progress state > > NOTICE: AbortTransaction and not in in-progress state > > > > 23:09[om]:~/mian/flats/sql>psql flats > > Connection to database 'flats' failed. > > I see a small problem in running VACUUM ANALYZE on the regression > database which involves an entry in pg_operator left over from the > char16 datatype. If the line containing "1004" is removed and the system > is "make clean install; initdb"'d then vacuum completes successfully on > that database. Interesting I missed that one. I am going to add some more *.sql checks into include/catalog to try and catch things like this. -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)
On Sun, 13 Sep 1998, Thomas G. Lockhart wrote:
> Date: Sun, 13 Sep 1998 14:27:12 +0000
> From: "Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
> To: Oleg Bartunov <oleg@sai.msu.su>
> Cc: pgsql-hackers@postgreSQL.org
> Subject: Re: [HACKERS] AbortTransaction and not in in-progress state
>
> > I checked latest cvs and notice when I do 'vacuum analyze'
> > I got:
> >
> > flats=> vacuum analyze;
> > NOTICE: AbortTransaction and not in in-progress state
> > NOTICE: AbortTransaction and not in in-progress state
> >
> > 23:09[om]:~/mian/flats/sql>psql flats
> > Connection to database 'flats' failed.
>
> I see a small problem in running VACUUM ANALYZE on the regression
> database which involves an entry in pg_operator left over from the
> char16 datatype. If the line containing "1004" is removed and the system
> is "make clean install; initdb"'d then vacuum completes successfully on
> that database.
>
> Can you try this test on the regression database (after removing that
> line from pg_operator)?
>
> make runtest
> psql regression
> regression=> vacuum analyze;
>
> If that succeeds for you, then you will need to describe your test
> scenerio in more detail. If it fails, then perhaps try backing-off of
> your bleeding-edge compiler and kernel (maybe start by compiling -m486
> instead of -mpentium). btw, I'm running Linux kernel 2.0.30, gcc 2.7.2.1
> from RH4.2...
Well, I moved to another machine with gcc 2.7.3, Linux 2.0.35
to be not confused with compiler. btw, egcs 1.1b really makes a difference -
latest cvs run all tests ok except geometry and run_ruletest
when compiled with gcc 2.7.3 on Linux 2.0.35 while egcs 1.1b and Linux 2.1.117
produces more failures (in select_having... I don't remember exactly - it's on
my home bleeding edge machine)
Unfortunately, vacuum analyze on my test database produces the same
result:
test=> vacuum analyze;
NOTICE: AbortTransaction and not in in-progress state
NOTICE: AbortTransaction and not in in-progress state
test=> \dt
NOTICE: AbortTransaction and not in in-progress state
NOTICE: AbortTransaction and not in in-progress state
test=> \q
dv:~/cvs/pgsql/src/test/regress$ psql test
Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQL
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: test
test=> \dt
NOTICE: AbortTransaction and not in in-progress state
ERROR: cannot write block -1 of [] blind
test=> \q
I attached perl script which creates this test database. I wrote it to
test how fast join of tables works, btw could you run it on your setup -
1. createdb test
2. mkjoindata.pl | psql test
There are several parameters in the script:
$ntables = 10; - the number of tables to generate
@nitems - array of tables rows ( just leave them 10)
$njoins - number of tables to join. On my machine it runs ok until
$njoins < 6. It seems that postgres is very-very slow
on join queries but this is another story and needs separate
postings.
If you need only create test database just run mkjoindata.pl > test.sql
and delete select statements manually.
3. psql test
4. vaccum analyze;
Also, vacuum analyze; on regression database produces
regression-> vacuum analyze;
ERROR: type id lookup of 1004 failed
I don't know which file I need to edit as you suggested :-)
Regards,
Oleg
>
> Regards.
>
> - Tom
>
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
#!/usr/local/bin/perl -w
#use strict;
# Number of tables - classificators
my $ntables = 10;
# main table, classificators
#my @nitems = ( 10, 50, 50, 50, 50, 50, 80,100,90 );
my @nitems = ( 10, 10, 10, 10, 10, 10, 10,10,10, 10,10);
# Number of tables we're joining
my $njoins = 6;
my $SQL = '';
# classificators
for ( $i = 0; $i <= $ntables; $i++ ) {
my $table = 't'. $i;
$SQL .= qq^drop table $table;\ncreate table $table ( a_id int4 NOT NULL, a varchar(10)^;
if ( $i == 0 ) { # main table
for ( $k = 1; $k <= $ntables; $k++ ) {
$SQL .= ', a_t'.$k.'_id int4';
}
}
$SQL .= qq^);\ncreate index a_id_$table on $table (a_id);\n^;
if ( $i == 0 ) { # main table - create add. indices
for ( $k = 1; $k <= $ntables; $k++ ) {
my $table = 't'.$k;
$SQL .= 'create index a_'.$table.'_id_t0 on t0 (a_'.$table.'_id);';
$SQL .= "\n";
}
}
$SQL .= qq^COPY $table FROM STDIN USING DELIMITERS '|';\n^;
srand($i);
for ( $j = 1; $j <= $nitems[$i]; $j++ ) {
my @cols = ();
# push @cols, (int(rand($nitems[$i])),'a'.$table.$j);
push @cols, ($j,'a'.$table);
if ( $i == 0 ) { # main table - add cols
for ( $k = 1; $k <= $ntables; $k++ ) {
push @cols, int(rand($nitems[$k]));
}
}
$SQL .= join ('|',@cols)."\n";
}
$SQL .= qq^\\.\n^;
}
print "$SQL\n";
# Query with joins
my $SELECT = '';
# Try GEQO
#$SELECT .= qq^set GEQO to 'ON=$njoins';^;
#$SELECT .= 'explain select a.a';
$SELECT .= 'select t0.a';
for ( $i = 1; $i <= $njoins; $i++ ) {
$SELECT .= ',t'.$i.'.a as t'.$i;
}
$SELECT .= "\n".' from t0 ';
for ( $i = 1; $i <= $njoins; $i++ ) {
$SELECT .= ',t'.$i;
}
$SELECT .= "\n".' where t1.a_id = t0.a_t1_id ';
for ( $i = 2; $i <= $njoins; $i++ ) {
$SELECT .= 'and t'.$i.'.a_id=t0.a_t'.$i.'_id ';
}
$SELECT .= ';';
print $SELECT,"\n";
> > I see a small problem in running VACUUM ANALYZE on the regression
> > database which involves an entry in pg_operator left over from the
> > char16 datatype. If the line containing "1004" is removed and the
> > system is "make clean install; initdb"'d then vacuum completes
> > successfully on that database.
> Also, vacuum analyze; on regression database produces
> regression-> vacuum analyze;
> ERROR: type id lookup of 1004 failed
> I don't know which file I need to edit as you suggested :-)
Oh, sorry. src/include/catalog/pg_operator.h is the one to change. I've
already made the change to the development cvs tree, so you could check
out the newest code.
I haven't had a chance to try your test case yet. Let me know how the
regression test goes and then I'll look at your test if you would still
like me to...
- Tom
> > > I see a small problem in running VACUUM ANALYZE on the regression > > > database which involves an entry in pg_operator left over from the > > > char16 datatype. If the line containing "1004" is removed and the > > > system is "make clean install; initdb"'d then vacuum completes > > > successfully on that database. > > Also, vacuum analyze; on regression database produces > > regression-> vacuum analyze; > > ERROR: type id lookup of 1004 failed > > I don't know which file I need to edit as you suggested :-) > > Oh, sorry. src/include/catalog/pg_operator.h is the one to change. I've > already made the change to the development cvs tree, so you could check > out the newest code. > > I haven't had a chance to try your test case yet. Let me know how the > regression test goes and then I'll look at your test if you would still > like me to... I have added contrib/findoidjoins/make_oidjoins_check which will take the output of findoidjoins and create a set of SQL queries that will find any bad oid's in the tables. I have run it on findoidjoins on the template1 database, and have created template1_check.sql in include/catalog to find any bad oid's. I found a few in pg_operator(the oid was '6'), and fixed them too. We can run the query anytime to find any of these problems in the future. -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)
On Mon, 14 Sep 1998, Thomas G. Lockhart wrote:
> Date: Mon, 14 Sep 1998 01:38:54 +0000
> From: "Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
> To: Oleg Bartunov <oleg@sai.msu.su>
> Cc: pgsql-hackers@postgreSQL.org
> Subject: Re: [HACKERS] AbortTransaction and not in in-progress state
>
> > > I see a small problem in running VACUUM ANALYZE on the regression
> > > database which involves an entry in pg_operator left over from the
> > > char16 datatype. If the line containing "1004" is removed and the
> > > system is "make clean install; initdb"'d then vacuum completes
> > > successfully on that database.
> > Also, vacuum analyze; on regression database produces
> > regression-> vacuum analyze;
> > ERROR: type id lookup of 1004 failed
> > I don't know which file I need to edit as you suggested :-)
>
> Oh, sorry. src/include/catalog/pg_operator.h is the one to change. I've
> already made the change to the development cvs tree, so you could check
> out the newest code.
>
Great, checked cvs tree and vacuum analyze works fine on regression database
as well on my test database !
BUT !!!
I just notice, that when I start postmaster from my script i used to
run postgres in production vacuum analyze on my test database produces
the same error as before ! And just checked it fails and on regression
database !
After playing with options I found this happens with -B 1024 !!!
I don't understand why this fails but 6.3.2 works just fine .
Regards,
Oleg
Below is my script:
6:12[dv]:~postgres>cat runpostgres
#!/bin/sh
# remove postgres lock if it was left on the system
if [ -S /tmp/.s.PGSQL.5432 ]; then
/bin/rm -f /tmp/.s.PGSQL.5432
fi
export LC_CTYPE=koi8-r
export LC_COLLATE=koi8-r
/usr/local/pgsql/bin/postmaster -i -B 1024 -S -D/usr/local/pgsql/data/ -o '-Fe'
> I haven't had a chance to try your test case yet. Let me know how the
> regression test goes and then I'll look at your test if you would still
> like me to...
>
> - Tom
>
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
> Great, checked cvs tree and vacuum analyze works fine on regression
> database as well on my test database !
> BUT !!!
> I just notice, that when I start postmaster from my script i used to
> run postgres in production vacuum analyze on my test database produces
> the same error as before ! And just checked it fails and on regression
> database !
> After playing with options I found this happens with -B 1024 !!!
It works fine on my system with "-B 1024". However, I don't usually use
the -F flag. And I don't usually compile with the Russian/Cyrillic
character set :)
> export LC_CTYPE=koi8-r
> export LC_COLLATE=koi8-r
> /usr/local/pgsql/bin/postmaster -i -B 1024 -S -D/usr/local/pgsql/data/ -o '-Fe'
From the man page:
-F Disable automatic fsync() call after each transaction. This
option improves performance, but an operating system crash while
a transaction is in progress will probably cause data loss.
I ran the regression test with the -F flag; I can see why you might be
tempted to take the risk since it speeds the regression test by 50%.
Pretty impressive.
So what we should do is at least have an fsync() happen when a
transaction completes (when an explicit END WORK statement is executed).
But that's a digression...
No problems with -B 1024 -o '-F' on my system, trying to "vacuum
analyze" the regression database. Try running your tests with the -F
flag omitted just to see what happens...
- Tom