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" < gabrielesanc...@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 >
