From: "Gabriel E. Sánchez Martínez" <gabrielesanchez@gmail.com>
To: pgsql-admin@postgresql.org
Subject: tables mysteriously truncated
Date: Wed, 13 Mar 2013 13:36:23 -0400
Message-ID: <5140B917.5060405@gmail.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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
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: <5140B917.5060405@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