Re: tables mysteriously truncated

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(at)gmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>