Re: tables mysteriously truncated

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