Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 94F579F9D65 for ; Sun, 7 Oct 2007 06:41:20 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 13998-06 for ; Sun, 7 Oct 2007 06:41:11 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from out1.smtp.messagingengine.com (out1.smtp.messagingengine.com [66.111.4.25]) by postgresql.org (Postfix) with ESMTP id 2BB6D9F9A79 for ; Sun, 7 Oct 2007 06:41:15 -0300 (ADT) Received: from compute2.internal (compute2.internal [10.202.2.42]) by out1.messagingengine.com (Postfix) with ESMTP id 1E87F2F74E; Sun, 7 Oct 2007 05:41:14 -0400 (EDT) Received: from heartbeat2.messagingengine.com ([10.202.2.161]) by compute2.internal (MEProxy); Sun, 07 Oct 2007 05:41:15 -0400 X-Sasl-enc: Zt+gj4zdDbdeoyEmOUriQPjq2iLV7xPIzsrukNlM+nyt 1191750074 Received: from [192.168.1.235] (64-142-85-108.dsl.dynamic.sonic.net [64.142.85.108]) by mail.messagingengine.com (Postfix) with ESMTP id C4C142087E; Sun, 7 Oct 2007 05:41:13 -0400 (EDT) Message-ID: <4708A9DC.5060306@fastmail.net> Date: Sun, 07 Oct 2007 02:41:48 -0700 From: Darren Reed User-Agent: Thunderbird 2.0.0.0 (Windows/20070326) MIME-Version: 1.0 To: Scott Marlowe CC: Darren Reed , "Joshua D. Drake" , pgsql-admin@postgresql.org Subject: Re: Is my database now too big? References: <1191730382.18880.1214536197@webmail.messagingengine.com> <47087723.7040403@commandprompt.com> <1191744007.14918.1214548241@webmail.messagingengine.com> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/48 X-Sequence-Number: 26962 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