Thread: postgres server failure during some database opearion

Surabhi Ahuja wrote:

> I have a query regarding, the postgres Sever failure, for eg say  i am
> parsng a file and inserting rows into a table. Now while this
> opeartion is taking place the server crashes, or say the power goes
> off, or say someone kills the postmaster demon.
>
> what happens to the rows that have been inserted till now? can we get
> a status of how many rows have been installed or not?

I highly suggest you read about "transactions". That's exactly what they
are here for.

If you opened a transaction for the entire insert operation, or if it is
done in a single operation (say - insert ... from select ... ), then
none of the rows are in the database, and you can start the entire
operation over with no risk for data integrity.

If each operation is in a different transaction then it is up to you to
put some mechanism in place to count how many rows got in. For example,
assuming you don't need to support multiple clients simultaneously and
you can figure out by the last row inserted which is the next one, you
can can open a transaction, insert a row, update a second table with the
identifier of the last row successfully inserted, and flush the
transaction. This way, you can always know which is the last row
inserted in a reliable way.

 From the sound of it, you really just want to perform the entire insert
operation inside one transaction.

> regards
> surabhi

          Shachar

--
Shachar Shemesh
Lingnu Open Source Consulting ltd.
Have you backed up today's work? http://www.lingnu.com/backup.html

Surabhi Ahuja wrote:
> I have a query regarding, the postgres Sever failure, for eg say  i
> am parsng a file and inserting rows into a table. Now while this
> opeartion is taking place the server crashes, or say the power goes
> off, or say someone kills the postmaster demon.

Any rows you have committed (and received a successful result code for)
should still be there. Any rows you haven't committed will not be.

> what happens to the rows that have been inserted till now? can we get
> a status of how many rows have been installed or not?

You can always do SELECT count(*) FROM my_table to see how many rows are
in it.

--
   Richard Huxton
   Archonet Ltd