Bruce Momjian <br...@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
>From 4c8fae27ecd9c94e7c3da0997f03099045a152d9 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 | 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
