Re: Is my database now too big?

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