Re: [HACKERS] AbortTransaction and not in in-progress state - Mailing list pgsql-hackers

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";