Re: Transaction, Rollback and Database Corruption question,

Joe Kislo <kislo(at)athenium(dot)com> writes:
> At the top of the import file there is a BEGIN;
> and at the bottom there is a commit. This should make this entire
> import a single transaction.

> During this process I accidentially hit control-C, canceling the whole
> shabang. The transaction obviously was never commited, and since the
> connection to the database was terminated, a rollback should have
> occoured.

Hm. There are some longstanding problems in 7.0.* and before if you try
to roll back a DROP TABLE (finally fixed for 7.1, but that doesn't help
you now). I'm not sure if that explains your results, however. What
is the structure of DROP and CREATE commands in your script?

> TT_fcgi=# create table answer (i int);
> ERROR: cannot create answer
> ERROR: cannot create answer
> TT_fcgi=# \d
> No relations found.
> TT_fcgi=# drop table answer;
> ERROR: Relation 'answer' does not exist
> ERROR: Relation 'answer' does not exist

This looks a lot like there is a physical file
$PGDATA/base/TT_fcgi/answer, but no pg_class entry for "answer".
I'm not sure how you could've gotten to that state based on what
you said above, but you should be able to restore the database to
usability by reaching in and deleting the orphaned file.

> PG gives no usuable error message as to why the table cannot be created,

7.0.3 fixes the lack of kernel error messages when reporting failure
to create/delete a physical file.

> What happens in this case of two transactions:

> T1: begin
> T2: begin
> T1: Create table x with schema y
> T2: create table x with schema z

T2 will wait to see if T1 commits, and if so fail with a duplicate-key
error...

> How do I query my PG server and determine the version?

select version();

regards, tom lane