I've never used PGSQL in a production environment, so forgive me if I'm
missing some of the common nuances assosciated with PGSQL. I have
burrowed through the dox, but havn't found anything which goes into the
detail I need.
I was trying to import a database into PGSQL, I have a file which
contains a long series of create table and insert clauses. I was using
the command:
isql DB_NAME < FILENAME
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.
I tried to restart, but errors started flying every which way.
Investigating using psql yielded illogical results:
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
TT_fcgi=# create table answer (i int);
ERROR: cannot create answer
ERROR: cannot create answer
PG gives no usuable error message as to why the table cannot be created,
and I'm simply nolonger able to create a table named "answer".
I terminated the PGserver, and restarted it. Obviously that would
-force- a rollback of any inprogress transactions. Still, same results,
my database is hosed.
From my experiences with Interbase this should simply never happen.
Here's the obvious questions stemming from this problem:
Why was my transaction never rolled back?
How do you determine what the outstanding transactions are?
How do you remotely roll back pending transactions?
How do you determine who is currently connected to a database?
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
T1: insert a into x
T2: insert b into x
T2: rollback
T1: commit
Does the existance of a table in one transaction context cross over to
other transactions?
How do I query my PG server and determine the version? The PGSQL client
version reports 7.0.2; but in the future I would like to query the
server and not some client application assosciated with PG.
FYI, deleting the answer file in the db directory seemed to alleviate my
problem. But obviously that is highly highly highly unacceptable.
Thanks,
-Joe
