Tom Lane wrote:
> > If you roll back a truncate, do you get the expected state?
I did a number of variations on the test below, with and without "on drop commit",
and similar tests where the "create table" is done before the "begin". After the
checkpoint, the number of files in the database directory always returned to the
value before the "begin" (210 in this case). Everything behaved as expected.
test=# begin;
BEGIN
test=# create temp table t1 (x integer) ;
CREATE TABLE
test=# insert into t1 select s from generate_series(1,1000) s ;
INSERT 0 1000
test=# select count(*) from t1 ; 1000
test=# savepoint s1;
SAVEPOINT
test=# truncate t1;
TRUNCATE TABLE
test=# select count(*) from t1 ; 0
test=# insert into t1 select s from generate_series(10000,11000) s ;
INSERT 0 1001
test=# select count(*) from t1 ; 1001
test=# rollback to savepoint s1 ;
ROLLBACK
test=# select count(*) from t1 ; 1000
test=# commit ;
COMMIT
test=# select count(*) from t1 ; 1000
test=# checkpoint;
CHECKPOINT
test=#
> How about after a database crash?
Repeating the same test as above, after the second insert, I did "killall -9 postgres".
Restarting generated the expected messages in the log:
2009-08-07 13:09:56 EDT LOG: database system was interrupted; last known up at 2009-08-07 13:06:01 EDT
2009-08-07 13:09:56 EDT LOG: database system was not properly shut down; automatic recovery in progress
2009-08-07 13:09:56 EDT LOG: redo starts at 0/1F8D6D0
2009-08-07 13:09:56 EDT LOG: invalid magic number 0000 in log file 0, segment 1, offset 16392192
2009-08-07 13:09:56 EDT LOG: redo done at 0/1F9F3B8
2009-08-07 13:09:56 EDT LOG: autovacuum launcher started
2009-08-07 13:09:56 EDT LOG: database system is ready to accept connections
However, the DB directory now has 214 files (up from 210); I have no idea whether this
means anything or not. Repeating the previous tests gives expected results.
-- todd