Small TRUNCATE glitch
Just noticed that TRUNCATE fails to clear the stats collector's counts
for the table. I am not sure if it should reset the event counts or
not (any thoughts?) but surely it is wrong to not zero the live/dead
tuple counts.
regards, tom lane
On Thu, Apr 03, 2008 at 11:58:11AM -0400, Tom Lane wrote:
Just noticed that TRUNCATE fails to clear the stats collector's counts
for the table. I am not sure if it should reset the event counts or
not (any thoughts?) but surely it is wrong to not zero the live/dead
tuple counts.
Wern't there complaints from people regularly truncating and
refilling tables getting bad plans because they lost the statistics?
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
Please line up in a tree and maintain the heap invariant while
boarding. Thank you for flying nlogn airlines.
Martijn van Oosterhout <kleptog@svana.org> writes:
On Thu, Apr 03, 2008 at 11:58:11AM -0400, Tom Lane wrote:
Just noticed that TRUNCATE fails to clear the stats collector's counts
for the table. I am not sure if it should reset the event counts or
not (any thoughts?) but surely it is wrong to not zero the live/dead
tuple counts.
Wern't there complaints from people regularly truncating and
refilling tables getting bad plans because they lost the statistics?
Not related --- the planner doesn't look at pgstats data.
regards, tom lane
Tom Lane wrote:
Just noticed that TRUNCATE fails to clear the stats collector's counts
for the table. I am not sure if it should reset the event counts or
not (any thoughts?) but surely it is wrong to not zero the live/dead
tuple counts.
Agreed, the live/dead counters should be reset. Regarding event counts,
my take is that we should have a separate statement count for truncate
(obviously not a tuple count), and the others should be left alone.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera <alvherre@commandprompt.com> writes:
Tom Lane wrote:
Just noticed that TRUNCATE fails to clear the stats collector's counts
for the table. I am not sure if it should reset the event counts or
not (any thoughts?) but surely it is wrong to not zero the live/dead
tuple counts.
Agreed, the live/dead counters should be reset. Regarding event counts,
my take is that we should have a separate statement count for truncate
(obviously not a tuple count), and the others should be left alone.
I thought some more about how to do it, and stumbled over how to cope
with TRUNCATE being rolled back. That nixed my first idea of just
having TRUNCATE send a zero-the-counters-now message.
regards, tom lane
Added to TODO:
o Clear table counters on TRUNCATE
http://archives.postgresql.org/pgsql-hackers/2008-04/msg00169.php
---------------------------------------------------------------------------
Tom Lane wrote:
Alvaro Herrera <alvherre@commandprompt.com> writes:
Tom Lane wrote:
Just noticed that TRUNCATE fails to clear the stats collector's counts
for the table. I am not sure if it should reset the event counts or
not (any thoughts?) but surely it is wrong to not zero the live/dead
tuple counts.Agreed, the live/dead counters should be reset. Regarding event counts,
my take is that we should have a separate statement count for truncate
(obviously not a tuple count), and the others should be left alone.I thought some more about how to do it, and stumbled over how to cope
with TRUNCATE being rolled back. That nixed my first idea of just
having TRUNCATE send a zero-the-counters-now message.regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Bruce Momjian <bruce@momjian.us> writes:
Added to TODO:
o Clear table counters on TRUNCATE
http://archives.postgresql.org/pgsql-hackers/2008-04/msg00169.php
Hello,
Attached is a WIP patch for this TODO.
Attachments:
0001-WIP-track-TRUNCATEs-in-pgstat-transaction-stats.patchtext/x-diffDownload
>From 97665ef1ca7d1847e90d4dfab38562135f01fb2b Mon Sep 17 00:00:00 2001
From: Alex Shulgin <ash@commandprompt.com>
Date: Tue, 9 Dec 2014 16:35:14 +0300
Subject: [PATCH] WIP: track TRUNCATEs in pgstat transaction stats.
The n_live_tup and n_dead_tup counters need to be set to 0 after a
TRUNCATE on the relation. We can't issue a special message to the stats
collector because the xact might be later aborted, so we track the fact
that the relation was truncated during the xact (and reset this xact's
insert/update/delete counters). When xact is committed, we use the
`truncated' flag to reset the n_live_tup and n_dead_tup counters.
---
src/backend/commands/tablecmds.c | 2 ++
src/backend/postmaster/pgstat.c | 70 ++++++++++++++++++++++++++++++++++++----
src/include/pgstat.h | 3 ++
3 files changed, 68 insertions(+), 7 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
new file mode 100644
index 1e737a0..192d033
*** a/src/backend/commands/tablecmds.c
--- b/src/backend/commands/tablecmds.c
*************** ExecuteTruncate(TruncateStmt *stmt)
*** 1224,1229 ****
--- 1224,1231 ----
*/
reindex_relation(heap_relid, REINDEX_REL_PROCESS_TOAST);
}
+
+ pgstat_count_heap_truncate(rel);
}
/*
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
new file mode 100644
index c7f41a5..7ff66b5
*** a/src/backend/postmaster/pgstat.c
--- b/src/backend/postmaster/pgstat.c
*************** typedef struct TwoPhasePgStatRecord
*** 200,208 ****
PgStat_Counter tuples_updated; /* tuples updated in xact */
PgStat_Counter tuples_deleted; /* tuples deleted in xact */
Oid t_id; /* table's OID */
! bool t_shared; /* is it a shared catalog? */
} TwoPhasePgStatRecord;
/*
* Info about current "snapshot" of stats file
*/
--- 200,211 ----
PgStat_Counter tuples_updated; /* tuples updated in xact */
PgStat_Counter tuples_deleted; /* tuples deleted in xact */
Oid t_id; /* table's OID */
! char t_flags; /* see TWOPHASE_PGSTAT_RECORD_*_FLAGs */
} TwoPhasePgStatRecord;
+ #define TWOPHASE_PGSTAT_RECORD_SHARED_FLAG 0x01 /* is it a shared catalog? */
+ #define TWOPHASE_PGSTAT_RECORD_TRUNC_FLAG 0x02 /* was the relation truncated? */
+
/*
* Info about current "snapshot" of stats file
*/
*************** pgstat_count_heap_delete(Relation rel)
*** 1864,1869 ****
--- 1867,1896 ----
}
/*
+ * pgstat_count_heap_truncate - update tuple counters due to truncate
+ */
+ void
+ pgstat_count_heap_truncate(Relation rel)
+ {
+ PgStat_TableStatus *pgstat_info = rel->pgstat_info;
+
+ if (pgstat_info != NULL)
+ {
+ /* We have to log the effect at the proper transactional level */
+ int nest_level = GetCurrentTransactionNestLevel();
+
+ if (pgstat_info->trans == NULL ||
+ pgstat_info->trans->nest_level != nest_level)
+ add_tabstat_xact_level(pgstat_info, nest_level);
+
+ pgstat_info->trans->tuples_inserted = 0;
+ pgstat_info->trans->tuples_updated = 0;
+ pgstat_info->trans->tuples_deleted = 0;
+ pgstat_info->trans->truncated = true;
+ }
+ }
+
+ /*
* pgstat_update_heap_dead_tuples - update dead-tuples count
*
* The semantics of this are that we are reporting the nontransactional
*************** AtEOXact_PgStat(bool isCommit)
*** 1927,1932 ****
--- 1954,1961 ----
tabstat->t_counts.t_tuples_deleted += trans->tuples_deleted;
if (isCommit)
{
+ tabstat->t_counts.t_truncated = trans->truncated;
+
/* insert adds a live tuple, delete removes one */
tabstat->t_counts.t_delta_live_tuples +=
trans->tuples_inserted - trans->tuples_deleted;
*************** AtEOSubXact_PgStat(bool isCommit, int ne
*** 1991,1999 ****
{
if (trans->upper && trans->upper->nest_level == nestDepth - 1)
{
! trans->upper->tuples_inserted += trans->tuples_inserted;
! trans->upper->tuples_updated += trans->tuples_updated;
! trans->upper->tuples_deleted += trans->tuples_deleted;
tabstat->trans = trans->upper;
pfree(trans);
}
--- 2020,2039 ----
{
if (trans->upper && trans->upper->nest_level == nestDepth - 1)
{
! if (trans->truncated)
! {
! trans->upper->truncated = true;
! /* replace upper xact stats with ours */
! trans->upper->tuples_inserted = trans->tuples_inserted;
! trans->upper->tuples_updated = trans->tuples_updated;
! trans->upper->tuples_deleted = trans->tuples_deleted;
! }
! else
! {
! trans->upper->tuples_inserted += trans->tuples_inserted;
! trans->upper->tuples_updated += trans->tuples_updated;
! trans->upper->tuples_deleted += trans->tuples_deleted;
! }
tabstat->trans = trans->upper;
pfree(trans);
}
*************** AtPrepare_PgStat(void)
*** 2071,2077 ****
record.tuples_updated = trans->tuples_updated;
record.tuples_deleted = trans->tuples_deleted;
record.t_id = tabstat->t_id;
! record.t_shared = tabstat->t_shared;
RegisterTwoPhaseRecord(TWOPHASE_RM_PGSTAT_ID, 0,
&record, sizeof(TwoPhasePgStatRecord));
--- 2111,2120 ----
record.tuples_updated = trans->tuples_updated;
record.tuples_deleted = trans->tuples_deleted;
record.t_id = tabstat->t_id;
! if (tabstat->t_shared)
! record.t_flags |= TWOPHASE_PGSTAT_RECORD_SHARED_FLAG;
! if (trans->truncated)
! record.t_flags |= TWOPHASE_PGSTAT_RECORD_TRUNC_FLAG;
RegisterTwoPhaseRecord(TWOPHASE_RM_PGSTAT_ID, 0,
&record, sizeof(TwoPhasePgStatRecord));
*************** pgstat_twophase_postcommit(TransactionId
*** 2131,2142 ****
PgStat_TableStatus *pgstat_info;
/* Find or create a tabstat entry for the rel */
! pgstat_info = get_tabstat_entry(rec->t_id, rec->t_shared);
/* Same math as in AtEOXact_PgStat, commit case */
pgstat_info->t_counts.t_tuples_inserted += rec->tuples_inserted;
pgstat_info->t_counts.t_tuples_updated += rec->tuples_updated;
pgstat_info->t_counts.t_tuples_deleted += rec->tuples_deleted;
pgstat_info->t_counts.t_delta_live_tuples +=
rec->tuples_inserted - rec->tuples_deleted;
pgstat_info->t_counts.t_delta_dead_tuples +=
--- 2174,2190 ----
PgStat_TableStatus *pgstat_info;
/* Find or create a tabstat entry for the rel */
! pgstat_info =
! get_tabstat_entry(rec->t_id,
! (rec->t_flags & TWOPHASE_PGSTAT_RECORD_SHARED_FLAG) != 0);
/* Same math as in AtEOXact_PgStat, commit case */
pgstat_info->t_counts.t_tuples_inserted += rec->tuples_inserted;
pgstat_info->t_counts.t_tuples_updated += rec->tuples_updated;
pgstat_info->t_counts.t_tuples_deleted += rec->tuples_deleted;
+ pgstat_info->t_counts.t_truncated =
+ ((rec->t_flags & TWOPHASE_PGSTAT_RECORD_TRUNC_FLAG) != 0);
+
pgstat_info->t_counts.t_delta_live_tuples +=
rec->tuples_inserted - rec->tuples_deleted;
pgstat_info->t_counts.t_delta_dead_tuples +=
*************** pgstat_twophase_postabort(TransactionId
*** 2160,2166 ****
PgStat_TableStatus *pgstat_info;
/* Find or create a tabstat entry for the rel */
! pgstat_info = get_tabstat_entry(rec->t_id, rec->t_shared);
/* Same math as in AtEOXact_PgStat, abort case */
pgstat_info->t_counts.t_tuples_inserted += rec->tuples_inserted;
--- 2208,2217 ----
PgStat_TableStatus *pgstat_info;
/* Find or create a tabstat entry for the rel */
! pgstat_info =
! get_tabstat_entry(rec->t_id,
! (rec->t_flags & TWOPHASE_PGSTAT_RECORD_SHARED_FLAG) != 0);
!
/* Same math as in AtEOXact_PgStat, abort case */
pgstat_info->t_counts.t_tuples_inserted += rec->tuples_inserted;
*************** pgstat_recv_tabstat(PgStat_MsgTabstat *m
*** 4685,4690 ****
--- 4736,4746 ----
tabentry->tuples_updated += tabmsg->t_counts.t_tuples_updated;
tabentry->tuples_deleted += tabmsg->t_counts.t_tuples_deleted;
tabentry->tuples_hot_updated += tabmsg->t_counts.t_tuples_hot_updated;
+ if (tabmsg->t_counts.t_truncated)
+ {
+ tabentry->n_live_tuples = 0;
+ tabentry->n_dead_tuples = 0;
+ }
tabentry->n_live_tuples += tabmsg->t_counts.t_delta_live_tuples;
tabentry->n_dead_tuples += tabmsg->t_counts.t_delta_dead_tuples;
tabentry->changes_since_analyze += tabmsg->t_counts.t_changed_tuples;
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
new file mode 100644
index 0892533..5107f48
*** a/src/include/pgstat.h
--- b/src/include/pgstat.h
*************** typedef struct PgStat_TableCounts
*** 103,108 ****
--- 103,109 ----
PgStat_Counter t_tuples_updated;
PgStat_Counter t_tuples_deleted;
PgStat_Counter t_tuples_hot_updated;
+ bool t_truncated;
PgStat_Counter t_delta_live_tuples;
PgStat_Counter t_delta_dead_tuples;
*************** typedef struct PgStat_TableXactStatus
*** 164,169 ****
--- 165,171 ----
PgStat_Counter tuples_inserted; /* tuples inserted in (sub)xact */
PgStat_Counter tuples_updated; /* tuples updated in (sub)xact */
PgStat_Counter tuples_deleted; /* tuples deleted in (sub)xact */
+ bool truncated; /* relation got truncated in this (sub)xact */
int nest_level; /* subtransaction nest level */
/* links to other structs for same relation: */
struct PgStat_TableXactStatus *upper; /* next higher subxact if any */
*************** extern void pgstat_initstats(Relation re
*** 916,921 ****
--- 918,924 ----
extern void pgstat_count_heap_insert(Relation rel, int n);
extern void pgstat_count_heap_update(Relation rel, bool hot);
extern void pgstat_count_heap_delete(Relation rel);
+ extern void pgstat_count_heap_truncate(Relation rel);
extern void pgstat_update_heap_dead_tuples(Relation rel, int delta);
extern void pgstat_init_function_usage(FunctionCallInfoData *fcinfo,
--
2.1.0
Alex Shulgin <ash@commandprompt.com> writes:
Bruce Momjian <bruce@momjian.us> writes:
Added to TODO:
o Clear table counters on TRUNCATE
http://archives.postgresql.org/pgsql-hackers/2008-04/msg00169.php
Hello,
Attached is a WIP patch for this TODO.
This part went as an attachment, which wasn't my intent:
========================================================
It does the trick by tracking if a TRUNCATE command was issued under a
(sub)transaction and uses this knowledge to reset the live/dead tuple
counters later if the transaction was committed. Testing in simple
cases shows that this clears the counters correctly, including use of
savepoints.
The 2PC part requires extending bool flag to fit the trunc flag, is this
approach sane? Given that 2PC transaction should survive server
restart, it's reasonable to expect it to also survive the upgrade, so I
see no clean way of adding another bool field to the
TwoPhasePgStatRecord struct (unless some would like to add checks on
record length, etc.).
I'm going to add some regression tests, but not sure what would be the
best location for this. The truncate.sql seems like natural choice, but
stats are not updating realtime, so I'd need to borrow some tricks from
stats.sql or better put the new tests in the stats.sql itself?
--
Alex
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Alex Shulgin wrote:
The 2PC part requires extending bool flag to fit the trunc flag, is this
approach sane? Given that 2PC transaction should survive server
restart, it's reasonable to expect it to also survive the upgrade, so I
see no clean way of adding another bool field to the
TwoPhasePgStatRecord struct (unless some would like to add checks on
record length, etc.).
I don't think we need to have 2PC files survive a pg_upgrade. It seems
perfectly okay to remove them from the new cluster at some appropriate
time, *if* they are copied from the old cluster at all (I don't think
they should be.)
--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 12/10/2014 03:04 AM, Alvaro Herrera wrote:
Alex Shulgin wrote:
The 2PC part requires extending bool flag to fit the trunc flag, is this
approach sane? Given that 2PC transaction should survive server
restart, it's reasonable to expect it to also survive the upgrade, so I
see no clean way of adding another bool field to the
TwoPhasePgStatRecord struct (unless some would like to add checks on
record length, etc.).I don't think we need to have 2PC files survive a pg_upgrade. It seems
perfectly okay to remove them from the new cluster at some appropriate
time, *if* they are copied from the old cluster at all (I don't think
they should be.)
I think pg_upgrade should check if there are any prepared transactions
pending, and refuse to upgrade if there are. It could be made to work,
but it's really not worth the trouble. If there are any pending prepared
transactions in the system when you run pg_upgrade, it's more likely to
be a mistake or oversight in the first place, than on purpose.
- Heikki
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Wed, Dec 10, 2014 at 10:32:42AM +0200, Heikki Linnakangas wrote:
I don't think we need to have 2PC files survive a pg_upgrade. It seems
perfectly okay to remove them from the new cluster at some appropriate
time, *if* they are copied from the old cluster at all (I don't think
they should be.)I think pg_upgrade should check if there are any prepared
transactions pending, and refuse to upgrade if there are. It could
be made to work, but it's really not worth the trouble. If there are
any pending prepared transactions in the system when you run
pg_upgrade, it's more likely to be a mistake or oversight in the
first place, than on purpose.
pg_upgrade already checks for prepared transactions and errors out if
they exist; see check_for_prepared_transactions().
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Bruce Momjian <bruce@momjian.us> writes:
On Wed, Dec 10, 2014 at 10:32:42AM +0200, Heikki Linnakangas wrote:
I don't think we need to have 2PC files survive a pg_upgrade. It seems
perfectly okay to remove them from the new cluster at some appropriate
time, *if* they are copied from the old cluster at all (I don't think
they should be.)I think pg_upgrade should check if there are any prepared
transactions pending, and refuse to upgrade if there are. It could
be made to work, but it's really not worth the trouble. If there are
any pending prepared transactions in the system when you run
pg_upgrade, it's more likely to be a mistake or oversight in the
first place, than on purpose.pg_upgrade already checks for prepared transactions and errors out if
they exist; see check_for_prepared_transactions().
Alright, that's good to know. So I'm just adding a new bool field to
the 2PC pgstat record instead of the bit magic.
Attached is v0.2, now with a regression test included.
--
Alex
Attachments:
truncate-and-pgstat-v0.2.patchtext/x-diffDownload
>From 4c8fae27ecd9c94e7c3da0997f03099045a152d9 Mon Sep 17 00:00:00 2001
From: Alex Shulgin <ash@commandprompt.com>
Date: Tue, 9 Dec 2014 16:35:14 +0300
Subject: [PATCH] WIP: track TRUNCATEs in pgstat transaction stats.
The n_live_tup and n_dead_tup counters need to be set to 0 after a
TRUNCATE on the relation. We can't issue a special message to the stats
collector because the xact might be later aborted, so we track the fact
that the relation was truncated during the xact (and reset this xact's
insert/update/delete counters). When xact is committed, we use the
`truncated' flag to reset the n_live_tup and n_dead_tup counters.
---
src/backend/commands/tablecmds.c | 2 +
src/backend/postmaster/pgstat.c | 52 ++++++++++++-
src/include/pgstat.h | 3 +
src/test/regress/expected/truncate.out | 136 +++++++++++++++++++++++++++++++++
src/test/regress/sql/truncate.sql | 98 ++++++++++++++++++++++++
5 files changed, 288 insertions(+), 3 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
new file mode 100644
index 1e737a0..192d033
*** a/src/backend/commands/tablecmds.c
--- b/src/backend/commands/tablecmds.c
*************** ExecuteTruncate(TruncateStmt *stmt)
*** 1224,1229 ****
--- 1224,1231 ----
*/
reindex_relation(heap_relid, REINDEX_REL_PROCESS_TOAST);
}
+
+ pgstat_count_heap_truncate(rel);
}
/*
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
new file mode 100644
index c7f41a5..88c83d2
*** a/src/backend/postmaster/pgstat.c
--- b/src/backend/postmaster/pgstat.c
*************** typedef struct TwoPhasePgStatRecord
*** 201,206 ****
--- 201,207 ----
PgStat_Counter tuples_deleted; /* tuples deleted in xact */
Oid t_id; /* table's OID */
bool t_shared; /* is it a shared catalog? */
+ bool t_truncated; /* was the relation truncated? */
} TwoPhasePgStatRecord;
/*
*************** pgstat_count_heap_delete(Relation rel)
*** 1864,1869 ****
--- 1865,1894 ----
}
/*
+ * pgstat_count_heap_truncate - update tuple counters due to truncate
+ */
+ void
+ pgstat_count_heap_truncate(Relation rel)
+ {
+ PgStat_TableStatus *pgstat_info = rel->pgstat_info;
+
+ if (pgstat_info != NULL)
+ {
+ /* We have to log the effect at the proper transactional level */
+ int nest_level = GetCurrentTransactionNestLevel();
+
+ if (pgstat_info->trans == NULL ||
+ pgstat_info->trans->nest_level != nest_level)
+ add_tabstat_xact_level(pgstat_info, nest_level);
+
+ pgstat_info->trans->tuples_inserted = 0;
+ pgstat_info->trans->tuples_updated = 0;
+ pgstat_info->trans->tuples_deleted = 0;
+ pgstat_info->trans->truncated = true;
+ }
+ }
+
+ /*
* pgstat_update_heap_dead_tuples - update dead-tuples count
*
* The semantics of this are that we are reporting the nontransactional
*************** AtEOXact_PgStat(bool isCommit)
*** 1927,1932 ****
--- 1952,1959 ----
tabstat->t_counts.t_tuples_deleted += trans->tuples_deleted;
if (isCommit)
{
+ tabstat->t_counts.t_truncated = trans->truncated;
+
/* insert adds a live tuple, delete removes one */
tabstat->t_counts.t_delta_live_tuples +=
trans->tuples_inserted - trans->tuples_deleted;
*************** AtEOSubXact_PgStat(bool isCommit, int ne
*** 1991,1999 ****
{
if (trans->upper && trans->upper->nest_level == nestDepth - 1)
{
! trans->upper->tuples_inserted += trans->tuples_inserted;
! trans->upper->tuples_updated += trans->tuples_updated;
! trans->upper->tuples_deleted += trans->tuples_deleted;
tabstat->trans = trans->upper;
pfree(trans);
}
--- 2018,2037 ----
{
if (trans->upper && trans->upper->nest_level == nestDepth - 1)
{
! if (trans->truncated)
! {
! trans->upper->truncated = true;
! /* replace upper xact stats with ours */
! trans->upper->tuples_inserted = trans->tuples_inserted;
! trans->upper->tuples_updated = trans->tuples_updated;
! trans->upper->tuples_deleted = trans->tuples_deleted;
! }
! else
! {
! trans->upper->tuples_inserted += trans->tuples_inserted;
! trans->upper->tuples_updated += trans->tuples_updated;
! trans->upper->tuples_deleted += trans->tuples_deleted;
! }
tabstat->trans = trans->upper;
pfree(trans);
}
*************** AtPrepare_PgStat(void)
*** 2072,2077 ****
--- 2110,2116 ----
record.tuples_deleted = trans->tuples_deleted;
record.t_id = tabstat->t_id;
record.t_shared = tabstat->t_shared;
+ record.t_truncated = trans->truncated;
RegisterTwoPhaseRecord(TWOPHASE_RM_PGSTAT_ID, 0,
&record, sizeof(TwoPhasePgStatRecord));
*************** pgstat_twophase_postcommit(TransactionId
*** 2137,2142 ****
--- 2176,2183 ----
pgstat_info->t_counts.t_tuples_inserted += rec->tuples_inserted;
pgstat_info->t_counts.t_tuples_updated += rec->tuples_updated;
pgstat_info->t_counts.t_tuples_deleted += rec->tuples_deleted;
+ pgstat_info->t_counts.t_truncated = rec->t_truncated;
+
pgstat_info->t_counts.t_delta_live_tuples +=
rec->tuples_inserted - rec->tuples_deleted;
pgstat_info->t_counts.t_delta_dead_tuples +=
*************** pgstat_recv_tabstat(PgStat_MsgTabstat *m
*** 4685,4690 ****
--- 4726,4736 ----
tabentry->tuples_updated += tabmsg->t_counts.t_tuples_updated;
tabentry->tuples_deleted += tabmsg->t_counts.t_tuples_deleted;
tabentry->tuples_hot_updated += tabmsg->t_counts.t_tuples_hot_updated;
+ if (tabmsg->t_counts.t_truncated)
+ {
+ tabentry->n_live_tuples = 0;
+ tabentry->n_dead_tuples = 0;
+ }
tabentry->n_live_tuples += tabmsg->t_counts.t_delta_live_tuples;
tabentry->n_dead_tuples += tabmsg->t_counts.t_delta_dead_tuples;
tabentry->changes_since_analyze += tabmsg->t_counts.t_changed_tuples;
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
new file mode 100644
index 0892533..5107f48
*** a/src/include/pgstat.h
--- b/src/include/pgstat.h
*************** typedef struct PgStat_TableCounts
*** 103,108 ****
--- 103,109 ----
PgStat_Counter t_tuples_updated;
PgStat_Counter t_tuples_deleted;
PgStat_Counter t_tuples_hot_updated;
+ bool t_truncated;
PgStat_Counter t_delta_live_tuples;
PgStat_Counter t_delta_dead_tuples;
*************** typedef struct PgStat_TableXactStatus
*** 164,169 ****
--- 165,171 ----
PgStat_Counter tuples_inserted; /* tuples inserted in (sub)xact */
PgStat_Counter tuples_updated; /* tuples updated in (sub)xact */
PgStat_Counter tuples_deleted; /* tuples deleted in (sub)xact */
+ bool truncated; /* relation got truncated in this (sub)xact */
int nest_level; /* subtransaction nest level */
/* links to other structs for same relation: */
struct PgStat_TableXactStatus *upper; /* next higher subxact if any */
*************** extern void pgstat_initstats(Relation re
*** 916,921 ****
--- 918,924 ----
extern void pgstat_count_heap_insert(Relation rel, int n);
extern void pgstat_count_heap_update(Relation rel, bool hot);
extern void pgstat_count_heap_delete(Relation rel);
+ extern void pgstat_count_heap_truncate(Relation rel);
extern void pgstat_update_heap_dead_tuples(Relation rel, int delta);
extern void pgstat_init_function_usage(FunctionCallInfoData *fcinfo,
diff --git a/src/test/regress/expected/truncate.out b/src/test/regress/expected/truncate.out
new file mode 100644
index 5c5277e..9a961f7
*** a/src/test/regress/expected/truncate.out
--- b/src/test/regress/expected/truncate.out
*************** SELECT nextval('truncate_a_id1'); -- fai
*** 420,422 ****
--- 420,558 ----
ERROR: relation "truncate_a_id1" does not exist
LINE 1: SELECT nextval('truncate_a_id1');
^
+ -- test effects of TRUNCATE on pgstat n_live_tup/n_dead_tup counters
+ CREATE TABLE trunc_stats_test(id serial);
+ CREATE TEMP TABLE prevstats AS
+ SELECT n_tup_ins, n_tup_upd, n_tup_del, n_live_tup, n_dead_tup
+ FROM pg_stat_user_tables
+ WHERE relname='trunc_stats_test';
+ create function wait_for_trunc_test_stats() returns prevstats as $$
+ declare
+ start_time timestamptz := clock_timestamp();
+ newstats prevstats;
+ updated bool;
+ begin
+ -- we don't want to wait forever; loop will exit after 30 seconds
+ for i in 1 .. 300 loop
+
+ SELECT INTO newstats
+ n_tup_ins, n_tup_upd, n_tup_del, n_live_tup, n_dead_tup
+ FROM pg_stat_user_tables
+ WHERE relname='trunc_stats_test';
+
+ SELECT INTO updated
+ row(p.*) <> newstats
+ FROM prevstats p;
+
+ exit when updated;
+
+ -- wait a little
+ perform pg_sleep(0.1);
+
+ -- reset stats snapshot so we can test again
+ perform pg_stat_clear_snapshot();
+
+ end loop;
+
+ TRUNCATE prevstats; -- what a pun
+ INSERT INTO prevstats SELECT newstats.*;
+
+ -- report time waited in postmaster log (where it won't change test output)
+ raise log 'wait_for_stats delayed % seconds',
+ extract(epoch from clock_timestamp() - start_time);
+
+ RETURN newstats;
+ end
+ $$ language plpgsql;
+ -- populate the table so we can check that n_live_tup is reset to 0
+ -- after truncate
+ INSERT INTO trunc_stats_test DEFAULT VALUES;
+ INSERT INTO trunc_stats_test DEFAULT VALUES;
+ INSERT INTO trunc_stats_test DEFAULT VALUES;
+ -- wait for stats collector to update
+ SELECT pg_sleep(0.5);
+ pg_sleep
+ ----------
+
+ (1 row)
+
+ SELECT * FROM wait_for_trunc_test_stats();
+ n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup
+ -----------+-----------+-----------+------------+------------
+ 3 | 0 | 0 | 3 | 0
+ (1 row)
+
+ TRUNCATE trunc_stats_test;
+ SELECT pg_sleep(0.5);
+ pg_sleep
+ ----------
+
+ (1 row)
+
+ SELECT * FROM wait_for_trunc_test_stats();
+ n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup
+ -----------+-----------+-----------+------------+------------
+ 3 | 0 | 0 | 0 | 0
+ (1 row)
+
+ -- repopulate the table
+ INSERT INTO trunc_stats_test DEFAULT VALUES;
+ INSERT INTO trunc_stats_test DEFAULT VALUES;
+ INSERT INTO trunc_stats_test DEFAULT VALUES;
+ UPDATE trunc_stats_test SET id = id + 10 WHERE id < 6; -- UPDATE 2
+ DELETE FROM trunc_stats_test WHERE id = 6; -- DELETE 1
+ SELECT pg_sleep(0.5);
+ pg_sleep
+ ----------
+
+ (1 row)
+
+ SELECT * FROM wait_for_trunc_test_stats();
+ n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup
+ -----------+-----------+-----------+------------+------------
+ 6 | 2 | 1 | 2 | 3
+ (1 row)
+
+ BEGIN;
+ UPDATE trunc_stats_test SET id = id + 100; -- UPDATE 2
+ TRUNCATE trunc_stats_test;
+ INSERT INTO trunc_stats_test DEFAULT VALUES;
+ COMMIT;
+ SELECT pg_sleep(0.5);
+ pg_sleep
+ ----------
+
+ (1 row)
+
+ SELECT * FROM wait_for_trunc_test_stats();
+ n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup
+ -----------+-----------+-----------+------------+------------
+ 7 | 2 | 1 | 1 | 0
+ (1 row)
+
+ -- now to use a savepoint: this should only count 1 insert and have 1
+ -- live tuple after commit
+ BEGIN;
+ INSERT INTO trunc_stats_test DEFAULT VALUES;
+ INSERT INTO trunc_stats_test DEFAULT VALUES;
+ SAVEPOINT p1;
+ INSERT INTO trunc_stats_test DEFAULT VALUES;
+ TRUNCATE trunc_stats_test;
+ INSERT INTO trunc_stats_test DEFAULT VALUES;
+ RELEASE SAVEPOINT p1;
+ COMMIT;
+ SELECT pg_sleep(0.5);
+ pg_sleep
+ ----------
+
+ (1 row)
+
+ SELECT * FROM wait_for_trunc_test_stats();
+ n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup
+ -----------+-----------+-----------+------------+------------
+ 8 | 2 | 1 | 1 | 0
+ (1 row)
+
+ DROP TABLE prevstats CASCADE;
+ NOTICE: drop cascades to function wait_for_trunc_test_stats()
+ DROP TABLE trunc_stats_test;
diff --git a/src/test/regress/sql/truncate.sql b/src/test/regress/sql/truncate.sql
new file mode 100644
index a3d6f53..c912345
*** a/src/test/regress/sql/truncate.sql
--- b/src/test/regress/sql/truncate.sql
*************** SELECT * FROM truncate_a;
*** 215,217 ****
--- 215,315 ----
DROP TABLE truncate_a;
SELECT nextval('truncate_a_id1'); -- fail, seq should have been dropped
+
+ -- test effects of TRUNCATE on pgstat n_live_tup/n_dead_tup counters
+ CREATE TABLE trunc_stats_test(id serial);
+
+ CREATE TEMP TABLE prevstats AS
+ SELECT n_tup_ins, n_tup_upd, n_tup_del, n_live_tup, n_dead_tup
+ FROM pg_stat_user_tables
+ WHERE relname='trunc_stats_test';
+
+ create function wait_for_trunc_test_stats() returns prevstats as $$
+ declare
+ start_time timestamptz := clock_timestamp();
+ newstats prevstats;
+ updated bool;
+ begin
+ -- we don't want to wait forever; loop will exit after 30 seconds
+ for i in 1 .. 300 loop
+
+ SELECT INTO newstats
+ n_tup_ins, n_tup_upd, n_tup_del, n_live_tup, n_dead_tup
+ FROM pg_stat_user_tables
+ WHERE relname='trunc_stats_test';
+
+ SELECT INTO updated
+ row(p.*) <> newstats
+ FROM prevstats p;
+
+ exit when updated;
+
+ -- wait a little
+ perform pg_sleep(0.1);
+
+ -- reset stats snapshot so we can test again
+ perform pg_stat_clear_snapshot();
+
+ end loop;
+
+ TRUNCATE prevstats; -- what a pun
+ INSERT INTO prevstats SELECT newstats.*;
+
+ -- report time waited in postmaster log (where it won't change test output)
+ raise log 'wait_for_stats delayed % seconds',
+ extract(epoch from clock_timestamp() - start_time);
+
+ RETURN newstats;
+ end
+ $$ language plpgsql;
+
+ -- populate the table so we can check that n_live_tup is reset to 0
+ -- after truncate
+ INSERT INTO trunc_stats_test DEFAULT VALUES;
+ INSERT INTO trunc_stats_test DEFAULT VALUES;
+ INSERT INTO trunc_stats_test DEFAULT VALUES;
+
+ -- wait for stats collector to update
+ SELECT pg_sleep(0.5);
+ SELECT * FROM wait_for_trunc_test_stats();
+
+ TRUNCATE trunc_stats_test;
+ SELECT pg_sleep(0.5);
+ SELECT * FROM wait_for_trunc_test_stats();
+
+ -- repopulate the table
+ INSERT INTO trunc_stats_test DEFAULT VALUES;
+ INSERT INTO trunc_stats_test DEFAULT VALUES;
+ INSERT INTO trunc_stats_test DEFAULT VALUES;
+ UPDATE trunc_stats_test SET id = id + 10 WHERE id < 6; -- UPDATE 2
+ DELETE FROM trunc_stats_test WHERE id = 6; -- DELETE 1
+
+ SELECT pg_sleep(0.5);
+ SELECT * FROM wait_for_trunc_test_stats();
+
+ BEGIN;
+ UPDATE trunc_stats_test SET id = id + 100; -- UPDATE 2
+ TRUNCATE trunc_stats_test;
+ INSERT INTO trunc_stats_test DEFAULT VALUES;
+ COMMIT;
+
+ SELECT pg_sleep(0.5);
+ SELECT * FROM wait_for_trunc_test_stats();
+
+ -- now to use a savepoint: this should only count 1 insert and have 1
+ -- live tuple after commit
+ BEGIN;
+ INSERT INTO trunc_stats_test DEFAULT VALUES;
+ INSERT INTO trunc_stats_test DEFAULT VALUES;
+ SAVEPOINT p1;
+ INSERT INTO trunc_stats_test DEFAULT VALUES;
+ TRUNCATE trunc_stats_test;
+ INSERT INTO trunc_stats_test DEFAULT VALUES;
+ RELEASE SAVEPOINT p1;
+ COMMIT;
+
+ SELECT pg_sleep(0.5);
+ SELECT * FROM wait_for_trunc_test_stats();
+
+ DROP TABLE prevstats CASCADE;
+ DROP TABLE trunc_stats_test;
--
2.1.0