From: "Gabriel E. Sánchez Martínez" <gabrielesanchez@gmail.com>
To: pgsql-admin@postgresql.org
Subject: Re: tables mysteriously truncated
Date: Wed, 13 Mar 2013 14:16:08 -0400
Message-ID: <5140C268.9080802@gmail.com> (raw)
In-Reply-To: <CAF8jcqo+eqEuBrqp6=QhFrTDhg17He9hqiCMOhaMh9TfoYO5_w@mail.gmail.com>
References: <5140B917.5060405@gmail.com>
<CAF8jcqo+eqEuBrqp6=QhFrTDhg17He9hqiCMOhaMh9TfoYO5_w@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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 <mailto: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
> <mailto:pgsql-admin@postgresql.org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
>
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: gabrielesanchez@gmail.com
Subject: Re: tables mysteriously truncated
In-Reply-To: <5140C268.9080802@gmail.com>
* 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