pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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




view thread (27+ messages)  latest in thread

Message-ID: <4708A9DC.5060306@fastmail.net>
Permalink:  ../4708A9DC.5060306@fastmail.net/
Also on:    postgresql.org/message-id/4708A9DC.5060306@fastmail.net

 · 

reply

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