Thread

1. Re: [BUGS] int2 unique index malfunction (btree corrupt)

   Tom Lane <tgl@sss.pgh.pa.us> — 1999-08-22T20:57:46Z

   Christof Petig <christof.petig@wtal.de> writes:
   > During development of a CIM program I frequently updated a table by its
   > primary key (int2 or numeric(3)). A lot of strange messages
   > 'NOTICE:  (transaction aborted): queries ignored until END' alerted me
   > that something is going wrong.
   > [ details snipped ]
   
   FWIW, the test program you supplied seems to run without errors for me.
   I'm using current CVS sources on an HPUX box.
   
   There was a fix applied on 8/8 to clean up a problem with btrees not
   recovering from an aborted transaction properly, but I'm not sure
   whether that has anything to do with your example...
   
   			regards, tom lane
   
   

2. Re: [BUGS] int2 unique index malfunction (btree corrupt)

   Christof Petig <christof.petig@wtal.de> — 1999-08-23T12:22:00Z

   Tom Lane wrote:
   
   > Christof Petig <christof.petig@wtal.de> writes:
   > > During development of a CIM program I frequently updated a table by its
   > > primary key (int2 or numeric(3)). A lot of strange messages
   > > 'NOTICE:  (transaction aborted): queries ignored until END' alerted me
   > > that something is going wrong.
   > > [ details snipped ]
   >
   > FWIW, the test program you supplied seems to run without errors for me.
   > I'm using current CVS sources on an HPUX box.
   >
   > There was a fix applied on 8/8 to clean up a problem with btrees not
   > recovering from an aborted transaction properly, but I'm not sure
   > whether that has anything to do with your example...
   
   My example fails desperately (within one to two seconds) on 6.5.1, however I
   tested it with today's CVS sources and it runs cleanly (disable the
   debugging output for testing at full speed).
   So the bugfix seems to cover my problem.
   
   However ...
   - if I vacuum the database while my test program runs all sorts of strange
   things happen:
      -- all goes well (90% chance, better if database had recently shrunk)
   
      -- the vacuum backend crashes:
   /home/christof> vacuumdb test
   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.
   vacuumdb: database vacuum failed on test.
   
      -- see yourself:
   home/christof> vacuumdb test
   ERROR:  Cannot insert a duplicate key into a unique index
   vacuumdb: database vacuum failed on test.
   
      -- postmaster goes into an endless loop, you can't kill test nor vacuumdb
   (happened once after a long run, test (the table's file) had reached about
   4MB.) Killing postmaster helps ...
   
   - vacuum never shrinks primary indices, and the index' file continues to
   grow (even at 7MB+).
     Seems the only choice for long running databases is either (drop
   index/create index) or     (dump/delete/restore).
   
   Regards
        Christof
   
   PS: Besides these issues Postgres works rather well!
     I like datetime_part('epoch', ...) and timespan_part('epoch', ...) which
   cover a functionality not available on our closed source (aka commercial)
   database.
   Calculating the speed of a running machine in SQL is nearly trivial
   (start_time, current_time, produced_amount).
   
   PPS: I modified the test program to not drop the table and recreate it on
   start. This allows many runs (event concurrent) on the same tables.
   Simly invoke as
   ./test something_which_doesnt_matter
   
   

3. Re: [BUGS] int2 unique index malfunction (btree corrupt)

   Christof Petig <christof.petig@wtal.de> — 1999-08-23T12:39:28Z

   As a side note:
   
   once test_pkey is beyond 13MB in size a failure of a vacuumdb is nearly
   certain.
   the size of a clean (!) test increases steadily (due to a bigger pkey file?)
   
   Regards
        Christof