Bruce Momjian <br...@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.
>From 97665ef1ca7d1847e90d4dfab38562135f01fb2b Mon Sep 17 00:00:00 2001
From: Alex Shulgin <a...@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
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?
--
Regards,
Alex
> ---------------------------------------------------------------------------
>
> Tom Lane wrote:
>> Alvaro Herrera <alvhe...@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
