Обсуждение: Strange Problem

I have an application that parses web server log files, and inserts a
few million rows into a table each month, one table per month. Lately,
I've been getting reports that there is no recent data, so I went
looking.  I'll concentrate on the first table that seems to have an
issue because there are a few, all seemingly the same. \dt+ shows the
table size of 6463 MB.  OK, there's data there. "select count(*) from
table" comes back with 0; "select ctid from table" comes back with 0,
but after a while. pg_dump or pg_dumpall completely skip the table.

I'm not a complete novice, but I'm stumped. No errors in the OS system
logs, postgres starts and stops with no errors. This is running on
ubuntu 14.04.03, with postgres 9.3

On 11 February 2016 at 17:30, Tim Sailer <tps@unslept.org> wrote:
> I have an application that parses web server log files, and inserts a few
> million rows into a table each month, one table per month. Lately, I've been
> getting reports that there is no recent data, so I went looking.  I'll
> concentrate on the first table that seems to have an issue because there are
> a few, all seemingly the same. \dt+ shows the table size of 6463 MB.  OK,
> there's data there. "select count(*) from table" comes back with 0; "select
> ctid from table" comes back with 0, but after a while. pg_dump or pg_dumpall
> completely skip the table.
>
> I'm not a complete novice, but I'm stumped. No errors in the OS system logs,
> postgres starts and stops with no errors. This is running on ubuntu
> 14.04.03, with postgres 9.3

There are 2 possibilities I can think of:

1) The application which inserts the rows still has the transaction
open that inserted all the rows, and hasn't yet committed.

2) A transaction is open, but another transaction has since deleted
all the rows and committed, and the open transaction still has
visibility of the rows, so they can't be cleaned up by a vacuum
process.

I'd suggest checking pg_stat_activity for connections with an old
xact_start and a status of 'idle', and also check pg_locks to see if
the affected table is listed as being locked by another process.

Thom

Thom Brown <thom@linux.com> writes:
> There are 2 possibilities I can think of:

> 1) The application which inserts the rows still has the transaction
> open that inserted all the rows, and hasn't yet committed.

> 2) A transaction is open, but another transaction has since deleted
> all the rows and committed, and the open transaction still has
> visibility of the rows, so they can't be cleaned up by a vacuum
> process.

"VACUUM VERBOSE <table>" would likely be informative.  I don't think
it will distinguish which of those things has happened, but it would
confirm the idea that there are a bunch of not-visible-to-you rows.

It might be a good idea to check for old prepared transactions, as
well as old idle-in-transaction sessions.

            regards, tom lane

On 02/11/2016 09:53 AM, Tom Lane wrote:
> Thom Brown <thom@linux.com> writes:
>> There are 2 possibilities I can think of:
>> 1) The application which inserts the rows still has the transaction
>> open that inserted all the rows, and hasn't yet committed.
>> 2) A transaction is open, but another transaction has since deleted
>> all the rows and committed, and the open transaction still has
>> visibility of the rows, so they can't be cleaned up by a vacuum
>> process.
> "VACUUM VERBOSE <table>" would likely be informative.  I don't think
> it will distinguish which of those things has happened, but it would
> confirm the idea that there are a bunch of not-visible-to-you rows.
>
> It might be a good idea to check for old prepared transactions, as
> well as old idle-in-transaction sessions.
>
>             regards, tom lane

Ugh. I saw a slew of "suspending truncate due to conflicting lock
request" scroll up the screen, along with lines like "truncated 4928 to
1376 pages" all the say down to "truncated 1376 to 0 pages".

I'm guessing that my table is now empty for some reason. Sigh.

Tim

Tim Sailer <tps@unslept.org> writes:
> On 02/11/2016 09:53 AM, Tom Lane wrote:
>> "VACUUM VERBOSE <table>" would likely be informative.

> Ugh. I saw a slew of "suspending truncate due to conflicting lock
> request" scroll up the screen, along with lines like "truncated 4928 to
> 1376 pages" all the say down to "truncated 1376 to 0 pages".

> I'm guessing that my table is now empty for some reason. Sigh.

Well, it was logically empty before, now it's physically empty too.

So what this confirms is that there is *not* a question of uncommitted
transactions or anything like that.  At this point I'd be looking for
client-side problems in your data insertion procedures.

            regards, tom lane