pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedtables mysteriously truncated
4+ messages / 3 participants
[nested] [flat]
* tables mysteriously truncated
@ 2013-03-13 17:36 "Gabriel E. Sánchez Martínez" <gabrielesanchez@gmail.com>
2013-03-13 18:02 ` Re: tables mysteriously truncated Vasilis Ventirozos <v.ventirozos@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: "Gabriel E. Sánchez Martínez" @ 2013-03-13 17:36 UTC (permalink / raw)
To: pgsql-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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: tables mysteriously truncated
2013-03-13 17:36 tables mysteriously truncated "Gabriel E. Sánchez Martínez" <gabrielesanchez@gmail.com>
@ 2013-03-13 18:02 ` Vasilis Ventirozos <v.ventirozos@gmail.com>
2013-03-13 18:16 ` Re: tables mysteriously truncated "Gabriel E. Sánchez Martínez" <gabrielesanchez@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Vasilis Ventirozos @ 2013-03-13 18:02 UTC (permalink / raw)
To: pgsql-admin
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> 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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: tables mysteriously truncated
2013-03-13 17:36 tables mysteriously truncated "Gabriel E. Sánchez Martínez" <gabrielesanchez@gmail.com>
2013-03-13 18:02 ` Re: tables mysteriously truncated Vasilis Ventirozos <v.ventirozos@gmail.com>
@ 2013-03-13 18:16 ` "Gabriel E. Sánchez Martínez" <gabrielesanchez@gmail.com>
2013-03-13 21:26 ` Re: tables mysteriously truncated Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 1 reply; 4+ messages in thread
From: "Gabriel E. Sánchez Martínez" @ 2013-03-13 18:16 UTC (permalink / raw)
To: pgsql-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
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: tables mysteriously truncated
2013-03-13 17:36 tables mysteriously truncated "Gabriel E. Sánchez Martínez" <gabrielesanchez@gmail.com>
2013-03-13 18:02 ` Re: tables mysteriously truncated Vasilis Ventirozos <v.ventirozos@gmail.com>
2013-03-13 18:16 ` Re: tables mysteriously truncated "Gabriel E. Sánchez Martínez" <gabrielesanchez@gmail.com>
@ 2013-03-13 21:26 ` Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 0 replies; 4+ messages in thread
From: Alvaro Herrera @ 2013-03-13 21:26 UTC (permalink / raw)
To: Gabriel E. Sánchez Martínez <gabrielesanchez@gmail.com>; +Cc: pgsql-admin
"Gabriel E. Sánchez Martínez" escribió:
> 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.
Yes, crashes are logged in the server log. There's support for
automatic recovery and restart after a crash, so it's quite plausible
that you're not seeing anything if you're not paying attention.
Sessions that were running prior to the crash are terminated, though, so
if you keep connections open, you'd notice because they would no longer
work.
Crashes should certainly not happen routinely. If they are, you have a
problem that's worth investigating.
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2013-03-13 21:26 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-03-13 17:36 tables mysteriously truncated "Gabriel E. Sánchez Martínez" <gabrielesanchez@gmail.com>
2013-03-13 18:02 ` Vasilis Ventirozos <v.ventirozos@gmail.com>
2013-03-13 18:16 ` "Gabriel E. Sánchez Martínez" <gabrielesanchez@gmail.com>
2013-03-13 21:26 ` Alvaro Herrera <alvherre@2ndquadrant.com>
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