From: Darren Reed <darrenr@fastmail.net>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: Darren Reed <darrenr+postgres@fastmail.net>
Cc: Joshua D. Drake <jd@commandprompt.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Is my database now too big?
Date: Sun, 07 Oct 2007 02:41:48 -0700
Message-ID: <4708A9DC.5060306@fastmail.net> (raw)
In-Reply-To: <dcc563d10710070114m35df7741tcaa01700b21baec@mail.gmail.com>
References: <1191730382.18880.1214536197@webmail.messagingengine.com>
<47087723.7040403@commandprompt.com>
<1191744007.14918.1214548241@webmail.messagingengine.com>
<dcc563d10710070114m35df7741tcaa01700b21baec@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: darrenr@fastmail.net, scott.marlowe@gmail.com, darrenr+postgres@fastmail.net, jd@commandprompt.com
Subject: Re: Is my database now too big?
In-Reply-To: <4708A9DC.5060306@fastmail.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox