Thread

1. Table corruption on drop

   Robert Weiler <rweiler@perfectsense.com> — 2000-12-22T20:26:52Z

   I'm using version 7.0.3 on Red Hat 7.0. I built the package using the
   defaults. I have also experienced the problem on Red Hat 6.2 Here are
   the deatils of the systems:
   
   System 1:
   Red Hat 6.2, Dell  Intel PIII/I810  700/133MHz, 128MB, 10GB IDE
   Postgresql 7.0.3 built from .tgz using ./configure && make
   
   System 2:
   Red Hat 7.0 Homebuilt Dual PIII800/133 512MB,  1x20GB IDE, 1x30gb IDE
   Postgresql 7.0.3 built from .tgz using ./configure && make
   
   To reproduce the problem, write a simple sql file with 2 tables liek
   this:
   
   drop table1;
   create table1 (
       v1 integer,
       v2 integer
   );
   
   create unique index  table1_index on table1(v1,v2);
   
   drop table2;
   create table2 (
       vi intteger,
       v2 varhcar(128),
       primary key (v2)
   );
   
   Run the script by redirecting from stdin to psql  - you should get a
   delayed error like
   
   ERROR: relation table1 does not exist.
   
   Start psql and create the first table by hand, rerun the script.
   Eventually you should get 'file not found' errors on the first table.
   
   I've attached a sample .sql file and transcript.
   
   If I create the tables before I drop them, everything seems to work
   fine.
   
   Bob Weiler
   Perfect Sense Software
   

2. Re: Table corruption on drop

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-01-01T23:56:15Z

   "Robert A. Weiler" <rweiler@perfectsense.com> writes:
   > [rotweiler@pss5 schema]$ psql < postgresql.sql
   > NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'domain_pkey' for table 'domain'
   > NOTICE:  mdopen: couldn't open unique_id: No such file or directory
   > NOTICE:  RelationIdBuildRelation: smgropen(unique_id): No such file or directory
   > NOTICE:  mdopen: couldn't open unique_id: No such file or directory
   > ERROR:  Relation 'domain' does not exist
   
   Hmm.  This looks suspiciously like the sort of problems that arise if
   you try to roll back a DROP TABLE, ie,
   
   	begin;
   	drop table foo;
   	abort;
   
   which leaves the catalog rows for 'foo' still valid, but the physical
   file for it has already been deleted.
   
   The script you show doesn't seem to do that, but I wonder whether it's
   not related somehow.
   
   This class of problems is (at long last) fixed for 7.1, but in prior
   releases all we can say is "don't do that" :-(
   
   			regards, tom lane