Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UFqDt-0006ZM-7a for pgsql-admin@arkaria.postgresql.org; Wed, 13 Mar 2013 18:16:21 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1UFqDs-0005uc-N4 for pgsql-admin@arkaria.postgresql.org; Wed, 13 Mar 2013 18:16:20 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UFqDq-0005tL-Vo for pgsql-admin@postgresql.org; Wed, 13 Mar 2013 18:16:19 +0000 Received: from mail-vc0-f182.google.com ([209.85.220.182]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UFqDo-0003oO-QW for pgsql-admin@postgresql.org; Wed, 13 Mar 2013 18:16:18 +0000 Received: by mail-vc0-f182.google.com with SMTP id fl11so709994vcb.41 for ; Wed, 13 Mar 2013 11:16:16 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=x-received:message-id:date:from:user-agent:mime-version:to:subject :references:in-reply-to:content-type; bh=MxU/IPMahzJZOJ9EgoUQ/Zq0HQ1GbPjZpC11EhXukDY=; b=BJDXGWlWzpmbslkQKHRPn16kHvYe9uD/hBRi9Hc/jCiJlGXK607ktaaK47GkrHoEcf HUko4Wz9t3+AT4unC3A7/FzO2xXWtNVruSqmeHAfZ7Ecjx+hxMh01HFc87WnsFtX50Zp b3vYuVdSEhLgVjuEq2x0wLkq88RPUw5+x2aqVANvtf1ROn5zJ960ZeCDhIeOW6SZEc5I mm8KOVLb2P6fpLO4JQsAJz/6VldiJeoKQognobtN5yxYV3ajLv4AmUxNq4dCtjeCwbCu MbDeZDzu4DUgp3QCgkgdrxuBdTiNHSCX2F4xqcTLfX77nAd7AGyJXU3aHNfff7mZTM13 tYRQ== X-Received: by 10.220.223.80 with SMTP id ij16mr6625531vcb.28.1363198576103; Wed, 13 Mar 2013 11:16:16 -0700 (PDT) Received: from [18.58.4.81] (TRANSIT1.MIT.EDU. [18.58.4.81]) by mx.google.com with ESMTPS id dh7sm5634097vdb.0.2013.03.13.11.16.14 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Wed, 13 Mar 2013 11:16:15 -0700 (PDT) Message-ID: <5140C268.9080802@gmail.com> Date: Wed, 13 Mar 2013 14:16:08 -0400 From: =?ISO-8859-1?Q?=22Gabriel_E=2E_S=E1nchez_Mart=EDnez=22?= User-Agent: Mozilla/5.0 (Windows NT 6.2; WOW64; rv:17.0) Gecko/20130307 Thunderbird/17.0.4 MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Re: tables mysteriously truncated References: <5140B917.5060405@gmail.com> In-Reply-To: Content-Type: multipart/alternative; boundary="------------050607040308080903060403" X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org This is a multi-part message in MIME format. --------------050607040308080903060403 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit I will try to make these tables logged and see if they withstand the test of time. Are crashes and unclean shutdowns logged anywhere? I would like to see why I might be getting them. I have not had to manually restart the database, if that were an indication of a crash. -Gabriel On 2013-03-13 14:02, Vasilis Ventirozos wrote: > an unlogged table will be truncated at database restart in case of a > crash or unclean shutdown, could that be the case ? > > Vasilis Ventirozos > > On Wed, Mar 13, 2013 at 7:36 PM, "Gabriel E. Sánchez Martínez" > > wrote: > > I have run into an annoying issue several times. After > successfully populating a table with the COPY command, letting the > server run auto-vacuum analyze, and being able to query the table > without any issues for several days, the table suddenly appears to > be empty. In pgAdmin I see a large estimated number of rows, as > it should be, but any SELECT to that table returns no rows, and > SELECT COUNT(*) returns 0. If I run an ANALYZE on that table and > refresh pgAdmin, the estimated number of rows is reset to 0. It > is as if someone had run TRUNCATE on the table, but I cannot find > TRUNCATE or DELETE statements on the logs, and the only users with > the privileges to do so (my colleague and I) have not executed > such statements. The rest of the users can only select and reference. > > I am running PostgreSQL 9.1.8 on Ubuntu 12.10 x86_64 server. I > have a master unlogged table with all the columns, no primary key, > and no index. This master_table has no data per se. Then I have > several partitions, created as follows: > > CREATE UNLOGGED TABLE partition_1 () > INHERITS (master_table) > WITH (OIDS = FALSE); > > Each partition contains up to 20 million rows, and I have about 30 > partitions. > > I have three such sets of tables on the same database, and the > issue has happened multiple times with two of the three sets of > tables. Curiously, the one that has never been affected is a bit > different: the master table contains a column id bigserial, which > is a primary key. Also, this table is logged. > > Has anyone run into similar issues in the past? Could this be a bug? > > Thanks in advance. > > -Gabriel > > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org > ) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin > > --------------050607040308080903060403 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit I will try to make these tables logged and see if they withstand the test of time.  Are crashes and unclean shutdowns logged anywhere?  I would like to see why I might be getting them.  I have not had to manually restart the database, if that were an indication of a crash.

-Gabriel

On 2013-03-13 14:02, Vasilis Ventirozos wrote:
an unlogged table will be truncated at database restart in case of a crash or unclean shutdown, could that be the case ?

Vasilis Ventirozos

On Wed, Mar 13, 2013 at 7:36 PM, "Gabriel E. Sánchez Martínez" <gabrielesanchez@gmail.com> wrote:
I have run into an annoying issue several times.  After successfully populating a table with the COPY command, letting the server run auto-vacuum analyze, and being able to query the table without any issues for several days, the table suddenly appears to be empty.  In pgAdmin I see a large estimated number of rows, as it should be, but any SELECT to that table returns no rows, and SELECT COUNT(*) returns 0.  If I run an ANALYZE on that table and refresh pgAdmin, the estimated number of rows is reset to 0.  It is as if someone had run TRUNCATE on the table, but I cannot find TRUNCATE or DELETE statements on the logs, and the only users with the privileges to do so (my colleague and I) have not executed such statements.  The rest of the users can only select and reference.

I am running PostgreSQL 9.1.8 on Ubuntu 12.10 x86_64 server.  I have a master unlogged table with all the columns, no primary key, and no index.  This master_table has no data per se.  Then I have several partitions, created as follows:

CREATE UNLOGGED TABLE partition_1 ()
INHERITS (master_table)
WITH (OIDS = FALSE);

Each partition contains up to 20 million rows, and I have about 30 partitions.

I have three such sets of tables on the same database, and the issue has happened multiple times with two of the three sets of tables. Curiously, the one that has never been affected is a bit different: the master table contains a column id bigserial, which is a primary key.  Also, this table is logged.

Has anyone run into similar issues in the past?  Could this be a bug?

Thanks in advance.

-Gabriel


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin


--------------050607040308080903060403--