Scott Marlowe wrote:
...
Any reasonably modern version of pgsql should simply stop accepting
requests rather than suffering loss due to txid wraparound.So,I can
think of two possibilities here. Bad hardware or operator error.
Assuming you've checked out your machine thoroughly for bad hardware,
I can see a scenario where one does something like:
begin;
create table xyz;
load 10,000,000 rows
manipulate rows
shutdown db without committing
start database
voila, table xyz is gone, and rightly so.
Got more detailed info on what you're doing?
That does describe what was happening (I haven't used BEGIN/COMMIT.)
Nothing very special, I thought...
But, doing "SELECT * FROM ifl LIMIT 1;" causes postgres to grow its
process to 2GB and then die because the OS ran out of swap!
Actually, this is a table that sees a lot of INSERT/DELETE (it's a place to
store work to be done and bits get removed when completed) and I haven't
been using BEGIN/COMMIT. This is how postgres currently handles it:
LOG: database system was not properly shut down; automatic recovery in
progress
LOG: record with zero length at 0/891157C8
LOG: redo is not required
LOG: database system is ready
LOG: transaction ID wrap limit is 2147484146, limited by database
"postgres"
LOG: unexpected EOF on client connection
LOG: server process (PID 7212) was terminated by signal 9
LOG: terminating any other active server processes
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back
the current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and
repeat your command.
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back
the current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and
repeat your command.
LOG: all server processes terminated; reinitializing
LOG: database system was interrupted at 2007-10-07 02:22:05 PDT
LOG: checkpoint record is at 0/891157C8
LOG: redo record is at 0/891157C8; undo record is at 0/0; shutdown TRUE
LOG: next transaction ID: 2715; next OID: 24576
LOG: next MultiXactId: 1; next MultiXactOffset: 0
LOG: database system was not properly shut down; automatic recovery in
progress
LOG: record with zero length at 0/8911580C
LOG: redo is not required
LOG: database system is ready
LOG: transaction ID wrap limit is 2147484146, limited by database
"postgres"
I'm modifying the work to use BEGIN/COMMIT, but the ifl table worries me...
I can't seem to do anything with it that doesn't cause postgres to crap
out ;(
Darren
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
