public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v2 08/17] Make opp freeze heuristic compatible with prune+freeze record
21+ messages / 3 participants
[nested] [flat]
* [PATCH v2 08/17] Make opp freeze heuristic compatible with prune+freeze record
@ 2024-01-07 21:11 Melanie Plageman <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Melanie Plageman @ 2024-01-07 21:11 UTC (permalink / raw)
Once the prune and freeze records are combined, we will no longer be
able to use a test of whether or not pruning emitted an FPI to decide
whether or not to opportunistically freeze a freezable page.
While this heuristic should be improved, for now, approximate the
previous logic by keeping track of whether or not a hint bit FPI was
emitted during visibility checks (when checksums are on) and combine
that with checking XLogCheckBufferNeedsBackup(). If we just finished
deciding whether or not to prune and the current buffer seems to need an
FPI after modification, it is likely that pruning would have emitted an
FPI.
---
src/backend/access/heap/pruneheap.c | 58 +++++++++++++++++++++--------
1 file changed, 43 insertions(+), 15 deletions(-)
diff --git a/src/backend/access/heap/pruneheap.c b/src/backend/access/heap/pruneheap.c
index 9c709315192..e715fc29a83 100644
--- a/src/backend/access/heap/pruneheap.c
+++ b/src/backend/access/heap/pruneheap.c
@@ -241,6 +241,10 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
PruneState prstate;
HeapTupleData tup;
bool do_freeze;
+ bool do_prune;
+ bool whole_page_freezable;
+ bool hint_bit_fpi;
+ bool prune_fpi = false;
int64 fpi_before = pgWalUsage.wal_fpi;
TransactionId frz_conflict_horizon = InvalidTransactionId;
@@ -410,6 +414,13 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
}
}
+ /*
+ * If checksums are enabled, heap_prune_satisfies_vacuum() may have caused
+ * an FPI to be emitted. Then reset fpi_before for no prune case.
+ */
+ hint_bit_fpi = fpi_before != pgWalUsage.wal_fpi;
+ fpi_before = pgWalUsage.wal_fpi;
+
/*
* For vacuum, if the whole page will become frozen, we consider
* opportunistically freezing tuples. Dead tuples which will be removed by
@@ -467,11 +478,42 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
if (off_loc)
*off_loc = InvalidOffsetNumber;
+ do_prune = prstate.nredirected > 0 ||
+ prstate.ndead > 0 ||
+ prstate.nunused > 0;
+
+ /*
+ * Only incur overhead of checking if we will do an FPI if we might use
+ * the information.
+ */
+ if (do_prune && pagefrz)
+ prune_fpi = XLogCheckBufferNeedsBackup(buffer);
+
+ /* Is the whole page freezable? And is there something to freeze */
+ whole_page_freezable = presult->all_visible_except_removable &&
+ presult->all_frozen;
+
+ /*
+ * Freeze the page when heap_prepare_freeze_tuple indicates that at least
+ * one XID/MXID from before FreezeLimit/MultiXactCutoff is present. Also
+ * freeze when pruning generated an FPI, if doing so means that we set the
+ * page all-frozen afterwards (might not happen until final heap pass).
+ * XXX: Previously, we knew if pruning emitted an FPI by checking
+ * pgWalUsage.wal_fpi before and after pruning. Once the freeze and prune
+ * records are combined, this heuristic couldn't be used anymore. The
+ * opportunistic freeze heuristic must be improved; however, for now, try
+ * to approximate it.
+ */
+
+ do_freeze = pagefrz &&
+ (pagefrz->freeze_required ||
+ (whole_page_freezable && presult->nfrozen > 0 && (prune_fpi || hint_bit_fpi)));
+
/* Any error while applying the changes is critical */
START_CRIT_SECTION();
/* Have we found any prunable items? */
- if (prstate.nredirected > 0 || prstate.ndead > 0 || prstate.nunused > 0)
+ if (do_prune)
{
/*
* Apply the planned item changes, then repair page fragmentation, and
@@ -563,20 +605,6 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
/* Record number of newly-set-LP_DEAD items for caller */
presult->nnewlpdead = prstate.ndead;
- /*
- * Freeze the page when heap_prepare_freeze_tuple indicates that at least
- * one XID/MXID from before FreezeLimit/MultiXactCutoff is present. Also
- * freeze when pruning generated an FPI, if doing so means that we set the
- * page all-frozen afterwards (might not happen until final heap pass).
- */
- if (pagefrz)
- do_freeze = pagefrz->freeze_required ||
- (presult->all_visible_except_removable && presult->all_frozen &&
- presult->nfrozen > 0 &&
- fpi_before != pgWalUsage.wal_fpi);
- else
- do_freeze = false;
-
if (do_freeze)
{
frz_conflict_horizon = heap_frz_conflict_horizon(presult, pagefrz);
--
2.40.1
--rdqtp5puvxqotfdw
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="v2-0009-Separate-tuple-pre-freeze-checks-and-invoke-earli.patch"
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v4 10/19] Make opp freeze heuristic compatible with prune+freeze record
@ 2024-01-07 21:11 Melanie Plageman <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Melanie Plageman @ 2024-01-07 21:11 UTC (permalink / raw)
Once the prune and freeze records are combined, we will no longer be
able to use a test of whether or not pruning emitted an FPI to decide
whether or not to opportunistically freeze a freezable page.
While this heuristic should be improved, for now, approximate the
previous logic by keeping track of whether or not a hint bit FPI was
emitted during visibility checks (when checksums are on) and combine
that with checking XLogCheckBufferNeedsBackup(). If we just finished
deciding whether or not to prune and the current buffer seems to need an
FPI after modification, it is likely that pruning would have emitted an
FPI.
---
src/backend/access/heap/pruneheap.c | 57 +++++++++++++++++++++--------
1 file changed, 42 insertions(+), 15 deletions(-)
diff --git a/src/backend/access/heap/pruneheap.c b/src/backend/access/heap/pruneheap.c
index 20907ba5408..9edf6bf72d7 100644
--- a/src/backend/access/heap/pruneheap.c
+++ b/src/backend/access/heap/pruneheap.c
@@ -246,6 +246,10 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
PruneState prstate;
HeapTupleData tup;
bool do_freeze;
+ bool do_prune;
+ bool whole_page_freezable;
+ bool hint_bit_fpi;
+ bool prune_fpi = false;
int64 fpi_before = pgWalUsage.wal_fpi;
/*
@@ -439,6 +443,13 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
}
}
+ /*
+ * If checksums are enabled, heap_prune_satisfies_vacuum() may have caused
+ * an FPI to be emitted. Then reset fpi_before for no prune case.
+ */
+ hint_bit_fpi = fpi_before != pgWalUsage.wal_fpi;
+ fpi_before = pgWalUsage.wal_fpi;
+
/*
* For vacuum, if the whole page will become frozen, we consider
* opportunistically freezing tuples. Dead tuples which will be removed by
@@ -483,11 +494,41 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
if (off_loc)
*off_loc = InvalidOffsetNumber;
+ do_prune = prstate.nredirected > 0 ||
+ prstate.ndead > 0 ||
+ prstate.nunused > 0;
+
+ /*
+ * Only incur overhead of checking if we will do an FPI if we might use
+ * the information.
+ */
+ if (do_prune && pagefrz)
+ prune_fpi = XLogCheckBufferNeedsBackup(buffer);
+
+ /* Is the whole page freezable? And is there something to freeze */
+ whole_page_freezable = presult->all_visible_except_removable &&
+ presult->all_frozen;
+
+ /*
+ * Freeze the page when heap_prepare_freeze_tuple indicates that at least
+ * one XID/MXID from before FreezeLimit/MultiXactCutoff is present. Also
+ * freeze when pruning generated an FPI, if doing so means that we set the
+ * page all-frozen afterwards (might not happen until final heap pass).
+ * XXX: Previously, we knew if pruning emitted an FPI by checking
+ * pgWalUsage.wal_fpi before and after pruning. Once the freeze and prune
+ * records are combined, this heuristic couldn't be used anymore. The
+ * opportunistic freeze heuristic must be improved; however, for now, try
+ * to approximate it.
+ */
+ do_freeze = pagefrz &&
+ (pagefrz->freeze_required ||
+ (whole_page_freezable && presult->nfrozen > 0 && (prune_fpi || hint_bit_fpi)));
+
/* Any error while applying the changes is critical */
START_CRIT_SECTION();
/* Have we found any prunable items? */
- if (prstate.nredirected > 0 || prstate.ndead > 0 || prstate.nunused > 0)
+ if (do_prune)
{
/*
* Apply the planned item changes, then repair page fragmentation, and
@@ -579,20 +620,6 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
/* Record number of newly-set-LP_DEAD items for caller */
presult->nnewlpdead = prstate.ndead;
- /*
- * Freeze the page when heap_prepare_freeze_tuple indicates that at least
- * one XID/MXID from before FreezeLimit/MultiXactCutoff is present. Also
- * freeze when pruning generated an FPI, if doing so means that we set the
- * page all-frozen afterwards (might not happen until final heap pass).
- */
- if (pagefrz)
- do_freeze = pagefrz->freeze_required ||
- (presult->all_visible_except_removable && presult->all_frozen &&
- presult->nfrozen > 0 &&
- fpi_before != pgWalUsage.wal_fpi);
- else
- do_freeze = false;
-
if (do_freeze)
{
/*
--
2.40.1
--tez7m2a73jtztiij
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="v4-0011-Separate-tuple-pre-freeze-checks-and-invoke-earli.patch"
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v3 08/17] Make opp freeze heuristic compatible with prune+freeze record
@ 2024-01-07 21:11 Melanie Plageman <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Melanie Plageman @ 2024-01-07 21:11 UTC (permalink / raw)
Once the prune and freeze records are combined, we will no longer be
able to use a test of whether or not pruning emitted an FPI to decide
whether or not to opportunistically freeze a freezable page.
While this heuristic should be improved, for now, approximate the
previous logic by keeping track of whether or not a hint bit FPI was
emitted during visibility checks (when checksums are on) and combine
that with checking XLogCheckBufferNeedsBackup(). If we just finished
deciding whether or not to prune and the current buffer seems to need an
FPI after modification, it is likely that pruning would have emitted an
FPI.
---
src/backend/access/heap/pruneheap.c | 58 +++++++++++++++++++++--------
1 file changed, 43 insertions(+), 15 deletions(-)
diff --git a/src/backend/access/heap/pruneheap.c b/src/backend/access/heap/pruneheap.c
index abf6bdb2d99..f164b7957ed 100644
--- a/src/backend/access/heap/pruneheap.c
+++ b/src/backend/access/heap/pruneheap.c
@@ -255,6 +255,10 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
PruneState prstate;
HeapTupleData tup;
bool do_freeze;
+ bool do_prune;
+ bool whole_page_freezable;
+ bool hint_bit_fpi;
+ bool prune_fpi = false;
int64 fpi_before = pgWalUsage.wal_fpi;
TransactionId frz_conflict_horizon = InvalidTransactionId;
@@ -424,6 +428,13 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
}
}
+ /*
+ * If checksums are enabled, heap_prune_satisfies_vacuum() may have caused
+ * an FPI to be emitted. Then reset fpi_before for no prune case.
+ */
+ hint_bit_fpi = fpi_before != pgWalUsage.wal_fpi;
+ fpi_before = pgWalUsage.wal_fpi;
+
/*
* For vacuum, if the whole page will become frozen, we consider
* opportunistically freezing tuples. Dead tuples which will be removed by
@@ -481,11 +492,42 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
if (off_loc)
*off_loc = InvalidOffsetNumber;
+ do_prune = prstate.nredirected > 0 ||
+ prstate.ndead > 0 ||
+ prstate.nunused > 0;
+
+ /*
+ * Only incur overhead of checking if we will do an FPI if we might use
+ * the information.
+ */
+ if (do_prune && pagefrz)
+ prune_fpi = XLogCheckBufferNeedsBackup(buffer);
+
+ /* Is the whole page freezable? And is there something to freeze */
+ whole_page_freezable = presult->all_visible_except_removable &&
+ presult->all_frozen;
+
+ /*
+ * Freeze the page when heap_prepare_freeze_tuple indicates that at least
+ * one XID/MXID from before FreezeLimit/MultiXactCutoff is present. Also
+ * freeze when pruning generated an FPI, if doing so means that we set the
+ * page all-frozen afterwards (might not happen until final heap pass).
+ * XXX: Previously, we knew if pruning emitted an FPI by checking
+ * pgWalUsage.wal_fpi before and after pruning. Once the freeze and prune
+ * records are combined, this heuristic couldn't be used anymore. The
+ * opportunistic freeze heuristic must be improved; however, for now, try
+ * to approximate it.
+ */
+
+ do_freeze = pagefrz &&
+ (pagefrz->freeze_required ||
+ (whole_page_freezable && presult->nfrozen > 0 && (prune_fpi || hint_bit_fpi)));
+
/* Any error while applying the changes is critical */
START_CRIT_SECTION();
/* Have we found any prunable items? */
- if (prstate.nredirected > 0 || prstate.ndead > 0 || prstate.nunused > 0)
+ if (do_prune)
{
/*
* Apply the planned item changes, then repair page fragmentation, and
@@ -577,20 +619,6 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
/* Record number of newly-set-LP_DEAD items for caller */
presult->nnewlpdead = prstate.ndead;
- /*
- * Freeze the page when heap_prepare_freeze_tuple indicates that at least
- * one XID/MXID from before FreezeLimit/MultiXactCutoff is present. Also
- * freeze when pruning generated an FPI, if doing so means that we set the
- * page all-frozen afterwards (might not happen until final heap pass).
- */
- if (pagefrz)
- do_freeze = pagefrz->freeze_required ||
- (presult->all_visible_except_removable && presult->all_frozen &&
- presult->nfrozen > 0 &&
- fpi_before != pgWalUsage.wal_fpi);
- else
- do_freeze = false;
-
if (do_freeze)
{
frz_conflict_horizon = heap_frz_conflict_horizon(presult, pagefrz);
--
2.40.1
--racicctn4wry6xe5
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="v3-0009-Separate-tuple-pre-freeze-checks-and-invoke-earli.patch"
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v2 08/17] Make opp freeze heuristic compatible with prune+freeze record
@ 2024-01-07 21:11 Melanie Plageman <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Melanie Plageman @ 2024-01-07 21:11 UTC (permalink / raw)
Once the prune and freeze records are combined, we will no longer be
able to use a test of whether or not pruning emitted an FPI to decide
whether or not to opportunistically freeze a freezable page.
While this heuristic should be improved, for now, approximate the
previous logic by keeping track of whether or not a hint bit FPI was
emitted during visibility checks (when checksums are on) and combine
that with checking XLogCheckBufferNeedsBackup(). If we just finished
deciding whether or not to prune and the current buffer seems to need an
FPI after modification, it is likely that pruning would have emitted an
FPI.
---
src/backend/access/heap/pruneheap.c | 58 +++++++++++++++++++++--------
1 file changed, 43 insertions(+), 15 deletions(-)
diff --git a/src/backend/access/heap/pruneheap.c b/src/backend/access/heap/pruneheap.c
index 9c709315192..e715fc29a83 100644
--- a/src/backend/access/heap/pruneheap.c
+++ b/src/backend/access/heap/pruneheap.c
@@ -241,6 +241,10 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
PruneState prstate;
HeapTupleData tup;
bool do_freeze;
+ bool do_prune;
+ bool whole_page_freezable;
+ bool hint_bit_fpi;
+ bool prune_fpi = false;
int64 fpi_before = pgWalUsage.wal_fpi;
TransactionId frz_conflict_horizon = InvalidTransactionId;
@@ -410,6 +414,13 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
}
}
+ /*
+ * If checksums are enabled, heap_prune_satisfies_vacuum() may have caused
+ * an FPI to be emitted. Then reset fpi_before for no prune case.
+ */
+ hint_bit_fpi = fpi_before != pgWalUsage.wal_fpi;
+ fpi_before = pgWalUsage.wal_fpi;
+
/*
* For vacuum, if the whole page will become frozen, we consider
* opportunistically freezing tuples. Dead tuples which will be removed by
@@ -467,11 +478,42 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
if (off_loc)
*off_loc = InvalidOffsetNumber;
+ do_prune = prstate.nredirected > 0 ||
+ prstate.ndead > 0 ||
+ prstate.nunused > 0;
+
+ /*
+ * Only incur overhead of checking if we will do an FPI if we might use
+ * the information.
+ */
+ if (do_prune && pagefrz)
+ prune_fpi = XLogCheckBufferNeedsBackup(buffer);
+
+ /* Is the whole page freezable? And is there something to freeze */
+ whole_page_freezable = presult->all_visible_except_removable &&
+ presult->all_frozen;
+
+ /*
+ * Freeze the page when heap_prepare_freeze_tuple indicates that at least
+ * one XID/MXID from before FreezeLimit/MultiXactCutoff is present. Also
+ * freeze when pruning generated an FPI, if doing so means that we set the
+ * page all-frozen afterwards (might not happen until final heap pass).
+ * XXX: Previously, we knew if pruning emitted an FPI by checking
+ * pgWalUsage.wal_fpi before and after pruning. Once the freeze and prune
+ * records are combined, this heuristic couldn't be used anymore. The
+ * opportunistic freeze heuristic must be improved; however, for now, try
+ * to approximate it.
+ */
+
+ do_freeze = pagefrz &&
+ (pagefrz->freeze_required ||
+ (whole_page_freezable && presult->nfrozen > 0 && (prune_fpi || hint_bit_fpi)));
+
/* Any error while applying the changes is critical */
START_CRIT_SECTION();
/* Have we found any prunable items? */
- if (prstate.nredirected > 0 || prstate.ndead > 0 || prstate.nunused > 0)
+ if (do_prune)
{
/*
* Apply the planned item changes, then repair page fragmentation, and
@@ -563,20 +605,6 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
/* Record number of newly-set-LP_DEAD items for caller */
presult->nnewlpdead = prstate.ndead;
- /*
- * Freeze the page when heap_prepare_freeze_tuple indicates that at least
- * one XID/MXID from before FreezeLimit/MultiXactCutoff is present. Also
- * freeze when pruning generated an FPI, if doing so means that we set the
- * page all-frozen afterwards (might not happen until final heap pass).
- */
- if (pagefrz)
- do_freeze = pagefrz->freeze_required ||
- (presult->all_visible_except_removable && presult->all_frozen &&
- presult->nfrozen > 0 &&
- fpi_before != pgWalUsage.wal_fpi);
- else
- do_freeze = false;
-
if (do_freeze)
{
frz_conflict_horizon = heap_frz_conflict_horizon(presult, pagefrz);
--
2.40.1
--rdqtp5puvxqotfdw
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="v2-0009-Separate-tuple-pre-freeze-checks-and-invoke-earli.patch"
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v4 10/19] Make opp freeze heuristic compatible with prune+freeze record
@ 2024-01-07 21:11 Melanie Plageman <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Melanie Plageman @ 2024-01-07 21:11 UTC (permalink / raw)
Once the prune and freeze records are combined, we will no longer be
able to use a test of whether or not pruning emitted an FPI to decide
whether or not to opportunistically freeze a freezable page.
While this heuristic should be improved, for now, approximate the
previous logic by keeping track of whether or not a hint bit FPI was
emitted during visibility checks (when checksums are on) and combine
that with checking XLogCheckBufferNeedsBackup(). If we just finished
deciding whether or not to prune and the current buffer seems to need an
FPI after modification, it is likely that pruning would have emitted an
FPI.
---
src/backend/access/heap/pruneheap.c | 57 +++++++++++++++++++++--------
1 file changed, 42 insertions(+), 15 deletions(-)
diff --git a/src/backend/access/heap/pruneheap.c b/src/backend/access/heap/pruneheap.c
index 20907ba5408..9edf6bf72d7 100644
--- a/src/backend/access/heap/pruneheap.c
+++ b/src/backend/access/heap/pruneheap.c
@@ -246,6 +246,10 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
PruneState prstate;
HeapTupleData tup;
bool do_freeze;
+ bool do_prune;
+ bool whole_page_freezable;
+ bool hint_bit_fpi;
+ bool prune_fpi = false;
int64 fpi_before = pgWalUsage.wal_fpi;
/*
@@ -439,6 +443,13 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
}
}
+ /*
+ * If checksums are enabled, heap_prune_satisfies_vacuum() may have caused
+ * an FPI to be emitted. Then reset fpi_before for no prune case.
+ */
+ hint_bit_fpi = fpi_before != pgWalUsage.wal_fpi;
+ fpi_before = pgWalUsage.wal_fpi;
+
/*
* For vacuum, if the whole page will become frozen, we consider
* opportunistically freezing tuples. Dead tuples which will be removed by
@@ -483,11 +494,41 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
if (off_loc)
*off_loc = InvalidOffsetNumber;
+ do_prune = prstate.nredirected > 0 ||
+ prstate.ndead > 0 ||
+ prstate.nunused > 0;
+
+ /*
+ * Only incur overhead of checking if we will do an FPI if we might use
+ * the information.
+ */
+ if (do_prune && pagefrz)
+ prune_fpi = XLogCheckBufferNeedsBackup(buffer);
+
+ /* Is the whole page freezable? And is there something to freeze */
+ whole_page_freezable = presult->all_visible_except_removable &&
+ presult->all_frozen;
+
+ /*
+ * Freeze the page when heap_prepare_freeze_tuple indicates that at least
+ * one XID/MXID from before FreezeLimit/MultiXactCutoff is present. Also
+ * freeze when pruning generated an FPI, if doing so means that we set the
+ * page all-frozen afterwards (might not happen until final heap pass).
+ * XXX: Previously, we knew if pruning emitted an FPI by checking
+ * pgWalUsage.wal_fpi before and after pruning. Once the freeze and prune
+ * records are combined, this heuristic couldn't be used anymore. The
+ * opportunistic freeze heuristic must be improved; however, for now, try
+ * to approximate it.
+ */
+ do_freeze = pagefrz &&
+ (pagefrz->freeze_required ||
+ (whole_page_freezable && presult->nfrozen > 0 && (prune_fpi || hint_bit_fpi)));
+
/* Any error while applying the changes is critical */
START_CRIT_SECTION();
/* Have we found any prunable items? */
- if (prstate.nredirected > 0 || prstate.ndead > 0 || prstate.nunused > 0)
+ if (do_prune)
{
/*
* Apply the planned item changes, then repair page fragmentation, and
@@ -579,20 +620,6 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
/* Record number of newly-set-LP_DEAD items for caller */
presult->nnewlpdead = prstate.ndead;
- /*
- * Freeze the page when heap_prepare_freeze_tuple indicates that at least
- * one XID/MXID from before FreezeLimit/MultiXactCutoff is present. Also
- * freeze when pruning generated an FPI, if doing so means that we set the
- * page all-frozen afterwards (might not happen until final heap pass).
- */
- if (pagefrz)
- do_freeze = pagefrz->freeze_required ||
- (presult->all_visible_except_removable && presult->all_frozen &&
- presult->nfrozen > 0 &&
- fpi_before != pgWalUsage.wal_fpi);
- else
- do_freeze = false;
-
if (do_freeze)
{
/*
--
2.40.1
--tez7m2a73jtztiij
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="v4-0011-Separate-tuple-pre-freeze-checks-and-invoke-earli.patch"
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v3 08/17] Make opp freeze heuristic compatible with prune+freeze record
@ 2024-01-07 21:11 Melanie Plageman <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Melanie Plageman @ 2024-01-07 21:11 UTC (permalink / raw)
Once the prune and freeze records are combined, we will no longer be
able to use a test of whether or not pruning emitted an FPI to decide
whether or not to opportunistically freeze a freezable page.
While this heuristic should be improved, for now, approximate the
previous logic by keeping track of whether or not a hint bit FPI was
emitted during visibility checks (when checksums are on) and combine
that with checking XLogCheckBufferNeedsBackup(). If we just finished
deciding whether or not to prune and the current buffer seems to need an
FPI after modification, it is likely that pruning would have emitted an
FPI.
---
src/backend/access/heap/pruneheap.c | 58 +++++++++++++++++++++--------
1 file changed, 43 insertions(+), 15 deletions(-)
diff --git a/src/backend/access/heap/pruneheap.c b/src/backend/access/heap/pruneheap.c
index abf6bdb2d99..f164b7957ed 100644
--- a/src/backend/access/heap/pruneheap.c
+++ b/src/backend/access/heap/pruneheap.c
@@ -255,6 +255,10 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
PruneState prstate;
HeapTupleData tup;
bool do_freeze;
+ bool do_prune;
+ bool whole_page_freezable;
+ bool hint_bit_fpi;
+ bool prune_fpi = false;
int64 fpi_before = pgWalUsage.wal_fpi;
TransactionId frz_conflict_horizon = InvalidTransactionId;
@@ -424,6 +428,13 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
}
}
+ /*
+ * If checksums are enabled, heap_prune_satisfies_vacuum() may have caused
+ * an FPI to be emitted. Then reset fpi_before for no prune case.
+ */
+ hint_bit_fpi = fpi_before != pgWalUsage.wal_fpi;
+ fpi_before = pgWalUsage.wal_fpi;
+
/*
* For vacuum, if the whole page will become frozen, we consider
* opportunistically freezing tuples. Dead tuples which will be removed by
@@ -481,11 +492,42 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
if (off_loc)
*off_loc = InvalidOffsetNumber;
+ do_prune = prstate.nredirected > 0 ||
+ prstate.ndead > 0 ||
+ prstate.nunused > 0;
+
+ /*
+ * Only incur overhead of checking if we will do an FPI if we might use
+ * the information.
+ */
+ if (do_prune && pagefrz)
+ prune_fpi = XLogCheckBufferNeedsBackup(buffer);
+
+ /* Is the whole page freezable? And is there something to freeze */
+ whole_page_freezable = presult->all_visible_except_removable &&
+ presult->all_frozen;
+
+ /*
+ * Freeze the page when heap_prepare_freeze_tuple indicates that at least
+ * one XID/MXID from before FreezeLimit/MultiXactCutoff is present. Also
+ * freeze when pruning generated an FPI, if doing so means that we set the
+ * page all-frozen afterwards (might not happen until final heap pass).
+ * XXX: Previously, we knew if pruning emitted an FPI by checking
+ * pgWalUsage.wal_fpi before and after pruning. Once the freeze and prune
+ * records are combined, this heuristic couldn't be used anymore. The
+ * opportunistic freeze heuristic must be improved; however, for now, try
+ * to approximate it.
+ */
+
+ do_freeze = pagefrz &&
+ (pagefrz->freeze_required ||
+ (whole_page_freezable && presult->nfrozen > 0 && (prune_fpi || hint_bit_fpi)));
+
/* Any error while applying the changes is critical */
START_CRIT_SECTION();
/* Have we found any prunable items? */
- if (prstate.nredirected > 0 || prstate.ndead > 0 || prstate.nunused > 0)
+ if (do_prune)
{
/*
* Apply the planned item changes, then repair page fragmentation, and
@@ -577,20 +619,6 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
/* Record number of newly-set-LP_DEAD items for caller */
presult->nnewlpdead = prstate.ndead;
- /*
- * Freeze the page when heap_prepare_freeze_tuple indicates that at least
- * one XID/MXID from before FreezeLimit/MultiXactCutoff is present. Also
- * freeze when pruning generated an FPI, if doing so means that we set the
- * page all-frozen afterwards (might not happen until final heap pass).
- */
- if (pagefrz)
- do_freeze = pagefrz->freeze_required ||
- (presult->all_visible_except_removable && presult->all_frozen &&
- presult->nfrozen > 0 &&
- fpi_before != pgWalUsage.wal_fpi);
- else
- do_freeze = false;
-
if (do_freeze)
{
frz_conflict_horizon = heap_frz_conflict_horizon(presult, pagefrz);
--
2.40.1
--racicctn4wry6xe5
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="v3-0009-Separate-tuple-pre-freeze-checks-and-invoke-earli.patch"
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v2 08/17] Make opp freeze heuristic compatible with prune+freeze record
@ 2024-01-07 21:11 Melanie Plageman <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Melanie Plageman @ 2024-01-07 21:11 UTC (permalink / raw)
Once the prune and freeze records are combined, we will no longer be
able to use a test of whether or not pruning emitted an FPI to decide
whether or not to opportunistically freeze a freezable page.
While this heuristic should be improved, for now, approximate the
previous logic by keeping track of whether or not a hint bit FPI was
emitted during visibility checks (when checksums are on) and combine
that with checking XLogCheckBufferNeedsBackup(). If we just finished
deciding whether or not to prune and the current buffer seems to need an
FPI after modification, it is likely that pruning would have emitted an
FPI.
---
src/backend/access/heap/pruneheap.c | 58 +++++++++++++++++++++--------
1 file changed, 43 insertions(+), 15 deletions(-)
diff --git a/src/backend/access/heap/pruneheap.c b/src/backend/access/heap/pruneheap.c
index 9c709315192..e715fc29a83 100644
--- a/src/backend/access/heap/pruneheap.c
+++ b/src/backend/access/heap/pruneheap.c
@@ -241,6 +241,10 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
PruneState prstate;
HeapTupleData tup;
bool do_freeze;
+ bool do_prune;
+ bool whole_page_freezable;
+ bool hint_bit_fpi;
+ bool prune_fpi = false;
int64 fpi_before = pgWalUsage.wal_fpi;
TransactionId frz_conflict_horizon = InvalidTransactionId;
@@ -410,6 +414,13 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
}
}
+ /*
+ * If checksums are enabled, heap_prune_satisfies_vacuum() may have caused
+ * an FPI to be emitted. Then reset fpi_before for no prune case.
+ */
+ hint_bit_fpi = fpi_before != pgWalUsage.wal_fpi;
+ fpi_before = pgWalUsage.wal_fpi;
+
/*
* For vacuum, if the whole page will become frozen, we consider
* opportunistically freezing tuples. Dead tuples which will be removed by
@@ -467,11 +478,42 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
if (off_loc)
*off_loc = InvalidOffsetNumber;
+ do_prune = prstate.nredirected > 0 ||
+ prstate.ndead > 0 ||
+ prstate.nunused > 0;
+
+ /*
+ * Only incur overhead of checking if we will do an FPI if we might use
+ * the information.
+ */
+ if (do_prune && pagefrz)
+ prune_fpi = XLogCheckBufferNeedsBackup(buffer);
+
+ /* Is the whole page freezable? And is there something to freeze */
+ whole_page_freezable = presult->all_visible_except_removable &&
+ presult->all_frozen;
+
+ /*
+ * Freeze the page when heap_prepare_freeze_tuple indicates that at least
+ * one XID/MXID from before FreezeLimit/MultiXactCutoff is present. Also
+ * freeze when pruning generated an FPI, if doing so means that we set the
+ * page all-frozen afterwards (might not happen until final heap pass).
+ * XXX: Previously, we knew if pruning emitted an FPI by checking
+ * pgWalUsage.wal_fpi before and after pruning. Once the freeze and prune
+ * records are combined, this heuristic couldn't be used anymore. The
+ * opportunistic freeze heuristic must be improved; however, for now, try
+ * to approximate it.
+ */
+
+ do_freeze = pagefrz &&
+ (pagefrz->freeze_required ||
+ (whole_page_freezable && presult->nfrozen > 0 && (prune_fpi || hint_bit_fpi)));
+
/* Any error while applying the changes is critical */
START_CRIT_SECTION();
/* Have we found any prunable items? */
- if (prstate.nredirected > 0 || prstate.ndead > 0 || prstate.nunused > 0)
+ if (do_prune)
{
/*
* Apply the planned item changes, then repair page fragmentation, and
@@ -563,20 +605,6 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
/* Record number of newly-set-LP_DEAD items for caller */
presult->nnewlpdead = prstate.ndead;
- /*
- * Freeze the page when heap_prepare_freeze_tuple indicates that at least
- * one XID/MXID from before FreezeLimit/MultiXactCutoff is present. Also
- * freeze when pruning generated an FPI, if doing so means that we set the
- * page all-frozen afterwards (might not happen until final heap pass).
- */
- if (pagefrz)
- do_freeze = pagefrz->freeze_required ||
- (presult->all_visible_except_removable && presult->all_frozen &&
- presult->nfrozen > 0 &&
- fpi_before != pgWalUsage.wal_fpi);
- else
- do_freeze = false;
-
if (do_freeze)
{
frz_conflict_horizon = heap_frz_conflict_horizon(presult, pagefrz);
--
2.40.1
--rdqtp5puvxqotfdw
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="v2-0009-Separate-tuple-pre-freeze-checks-and-invoke-earli.patch"
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v4 10/19] Make opp freeze heuristic compatible with prune+freeze record
@ 2024-01-07 21:11 Melanie Plageman <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Melanie Plageman @ 2024-01-07 21:11 UTC (permalink / raw)
Once the prune and freeze records are combined, we will no longer be
able to use a test of whether or not pruning emitted an FPI to decide
whether or not to opportunistically freeze a freezable page.
While this heuristic should be improved, for now, approximate the
previous logic by keeping track of whether or not a hint bit FPI was
emitted during visibility checks (when checksums are on) and combine
that with checking XLogCheckBufferNeedsBackup(). If we just finished
deciding whether or not to prune and the current buffer seems to need an
FPI after modification, it is likely that pruning would have emitted an
FPI.
---
src/backend/access/heap/pruneheap.c | 57 +++++++++++++++++++++--------
1 file changed, 42 insertions(+), 15 deletions(-)
diff --git a/src/backend/access/heap/pruneheap.c b/src/backend/access/heap/pruneheap.c
index 20907ba5408..9edf6bf72d7 100644
--- a/src/backend/access/heap/pruneheap.c
+++ b/src/backend/access/heap/pruneheap.c
@@ -246,6 +246,10 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
PruneState prstate;
HeapTupleData tup;
bool do_freeze;
+ bool do_prune;
+ bool whole_page_freezable;
+ bool hint_bit_fpi;
+ bool prune_fpi = false;
int64 fpi_before = pgWalUsage.wal_fpi;
/*
@@ -439,6 +443,13 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
}
}
+ /*
+ * If checksums are enabled, heap_prune_satisfies_vacuum() may have caused
+ * an FPI to be emitted. Then reset fpi_before for no prune case.
+ */
+ hint_bit_fpi = fpi_before != pgWalUsage.wal_fpi;
+ fpi_before = pgWalUsage.wal_fpi;
+
/*
* For vacuum, if the whole page will become frozen, we consider
* opportunistically freezing tuples. Dead tuples which will be removed by
@@ -483,11 +494,41 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
if (off_loc)
*off_loc = InvalidOffsetNumber;
+ do_prune = prstate.nredirected > 0 ||
+ prstate.ndead > 0 ||
+ prstate.nunused > 0;
+
+ /*
+ * Only incur overhead of checking if we will do an FPI if we might use
+ * the information.
+ */
+ if (do_prune && pagefrz)
+ prune_fpi = XLogCheckBufferNeedsBackup(buffer);
+
+ /* Is the whole page freezable? And is there something to freeze */
+ whole_page_freezable = presult->all_visible_except_removable &&
+ presult->all_frozen;
+
+ /*
+ * Freeze the page when heap_prepare_freeze_tuple indicates that at least
+ * one XID/MXID from before FreezeLimit/MultiXactCutoff is present. Also
+ * freeze when pruning generated an FPI, if doing so means that we set the
+ * page all-frozen afterwards (might not happen until final heap pass).
+ * XXX: Previously, we knew if pruning emitted an FPI by checking
+ * pgWalUsage.wal_fpi before and after pruning. Once the freeze and prune
+ * records are combined, this heuristic couldn't be used anymore. The
+ * opportunistic freeze heuristic must be improved; however, for now, try
+ * to approximate it.
+ */
+ do_freeze = pagefrz &&
+ (pagefrz->freeze_required ||
+ (whole_page_freezable && presult->nfrozen > 0 && (prune_fpi || hint_bit_fpi)));
+
/* Any error while applying the changes is critical */
START_CRIT_SECTION();
/* Have we found any prunable items? */
- if (prstate.nredirected > 0 || prstate.ndead > 0 || prstate.nunused > 0)
+ if (do_prune)
{
/*
* Apply the planned item changes, then repair page fragmentation, and
@@ -579,20 +620,6 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
/* Record number of newly-set-LP_DEAD items for caller */
presult->nnewlpdead = prstate.ndead;
- /*
- * Freeze the page when heap_prepare_freeze_tuple indicates that at least
- * one XID/MXID from before FreezeLimit/MultiXactCutoff is present. Also
- * freeze when pruning generated an FPI, if doing so means that we set the
- * page all-frozen afterwards (might not happen until final heap pass).
- */
- if (pagefrz)
- do_freeze = pagefrz->freeze_required ||
- (presult->all_visible_except_removable && presult->all_frozen &&
- presult->nfrozen > 0 &&
- fpi_before != pgWalUsage.wal_fpi);
- else
- do_freeze = false;
-
if (do_freeze)
{
/*
--
2.40.1
--tez7m2a73jtztiij
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="v4-0011-Separate-tuple-pre-freeze-checks-and-invoke-earli.patch"
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v3 08/17] Make opp freeze heuristic compatible with prune+freeze record
@ 2024-01-07 21:11 Melanie Plageman <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Melanie Plageman @ 2024-01-07 21:11 UTC (permalink / raw)
Once the prune and freeze records are combined, we will no longer be
able to use a test of whether or not pruning emitted an FPI to decide
whether or not to opportunistically freeze a freezable page.
While this heuristic should be improved, for now, approximate the
previous logic by keeping track of whether or not a hint bit FPI was
emitted during visibility checks (when checksums are on) and combine
that with checking XLogCheckBufferNeedsBackup(). If we just finished
deciding whether or not to prune and the current buffer seems to need an
FPI after modification, it is likely that pruning would have emitted an
FPI.
---
src/backend/access/heap/pruneheap.c | 58 +++++++++++++++++++++--------
1 file changed, 43 insertions(+), 15 deletions(-)
diff --git a/src/backend/access/heap/pruneheap.c b/src/backend/access/heap/pruneheap.c
index abf6bdb2d99..f164b7957ed 100644
--- a/src/backend/access/heap/pruneheap.c
+++ b/src/backend/access/heap/pruneheap.c
@@ -255,6 +255,10 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
PruneState prstate;
HeapTupleData tup;
bool do_freeze;
+ bool do_prune;
+ bool whole_page_freezable;
+ bool hint_bit_fpi;
+ bool prune_fpi = false;
int64 fpi_before = pgWalUsage.wal_fpi;
TransactionId frz_conflict_horizon = InvalidTransactionId;
@@ -424,6 +428,13 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
}
}
+ /*
+ * If checksums are enabled, heap_prune_satisfies_vacuum() may have caused
+ * an FPI to be emitted. Then reset fpi_before for no prune case.
+ */
+ hint_bit_fpi = fpi_before != pgWalUsage.wal_fpi;
+ fpi_before = pgWalUsage.wal_fpi;
+
/*
* For vacuum, if the whole page will become frozen, we consider
* opportunistically freezing tuples. Dead tuples which will be removed by
@@ -481,11 +492,42 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
if (off_loc)
*off_loc = InvalidOffsetNumber;
+ do_prune = prstate.nredirected > 0 ||
+ prstate.ndead > 0 ||
+ prstate.nunused > 0;
+
+ /*
+ * Only incur overhead of checking if we will do an FPI if we might use
+ * the information.
+ */
+ if (do_prune && pagefrz)
+ prune_fpi = XLogCheckBufferNeedsBackup(buffer);
+
+ /* Is the whole page freezable? And is there something to freeze */
+ whole_page_freezable = presult->all_visible_except_removable &&
+ presult->all_frozen;
+
+ /*
+ * Freeze the page when heap_prepare_freeze_tuple indicates that at least
+ * one XID/MXID from before FreezeLimit/MultiXactCutoff is present. Also
+ * freeze when pruning generated an FPI, if doing so means that we set the
+ * page all-frozen afterwards (might not happen until final heap pass).
+ * XXX: Previously, we knew if pruning emitted an FPI by checking
+ * pgWalUsage.wal_fpi before and after pruning. Once the freeze and prune
+ * records are combined, this heuristic couldn't be used anymore. The
+ * opportunistic freeze heuristic must be improved; however, for now, try
+ * to approximate it.
+ */
+
+ do_freeze = pagefrz &&
+ (pagefrz->freeze_required ||
+ (whole_page_freezable && presult->nfrozen > 0 && (prune_fpi || hint_bit_fpi)));
+
/* Any error while applying the changes is critical */
START_CRIT_SECTION();
/* Have we found any prunable items? */
- if (prstate.nredirected > 0 || prstate.ndead > 0 || prstate.nunused > 0)
+ if (do_prune)
{
/*
* Apply the planned item changes, then repair page fragmentation, and
@@ -577,20 +619,6 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
/* Record number of newly-set-LP_DEAD items for caller */
presult->nnewlpdead = prstate.ndead;
- /*
- * Freeze the page when heap_prepare_freeze_tuple indicates that at least
- * one XID/MXID from before FreezeLimit/MultiXactCutoff is present. Also
- * freeze when pruning generated an FPI, if doing so means that we set the
- * page all-frozen afterwards (might not happen until final heap pass).
- */
- if (pagefrz)
- do_freeze = pagefrz->freeze_required ||
- (presult->all_visible_except_removable && presult->all_frozen &&
- presult->nfrozen > 0 &&
- fpi_before != pgWalUsage.wal_fpi);
- else
- do_freeze = false;
-
if (do_freeze)
{
frz_conflict_horizon = heap_frz_conflict_horizon(presult, pagefrz);
--
2.40.1
--racicctn4wry6xe5
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="v3-0009-Separate-tuple-pre-freeze-checks-and-invoke-earli.patch"
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v9 09/21] Make opp freeze heuristic compatible with prune+freeze record
@ 2024-03-26 00:48 Melanie Plageman <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Melanie Plageman @ 2024-03-26 00:48 UTC (permalink / raw)
Once the prune and freeze records are combined, we will no longer be
able to use a test of whether or not pruning emitted an FPI to decide
whether or not to opportunistically freeze a freezable page.
While this heuristic should be improved, for now, approximate the
previous logic by keeping track of whether or not a hint bit FPI was
emitted during visibility checks (when checksums are on) and combine
that with checking XLogCheckBufferNeedsBackup(). If we just finished
deciding whether or not to prune and the current buffer seems to need an
FPI after modification, it is likely that pruning would have emitted an
FPI.
---
src/backend/access/heap/pruneheap.c | 57 +++++++++++++++++++++--------
1 file changed, 42 insertions(+), 15 deletions(-)
diff --git a/src/backend/access/heap/pruneheap.c b/src/backend/access/heap/pruneheap.c
index 312695f806c..f0decff35dc 100644
--- a/src/backend/access/heap/pruneheap.c
+++ b/src/backend/access/heap/pruneheap.c
@@ -247,6 +247,10 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
TransactionId visibility_cutoff_xid;
bool do_freeze;
bool all_visible_except_removable;
+ bool do_prune;
+ bool whole_page_freezable;
+ bool hint_bit_fpi;
+ bool prune_fpi = false;
int64 fpi_before = pgWalUsage.wal_fpi;
/*
@@ -456,6 +460,13 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
}
}
+ /*
+ * If checksums are enabled, heap_prune_satisfies_vacuum() may have caused
+ * an FPI to be emitted. Then reset fpi_before for no prune case.
+ */
+ hint_bit_fpi = fpi_before != pgWalUsage.wal_fpi;
+ fpi_before = pgWalUsage.wal_fpi;
+
/*
* For vacuum, if the whole page will become frozen, we consider
* opportunistically freezing tuples. Dead tuples which will be removed by
@@ -500,11 +511,41 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
if (off_loc)
*off_loc = InvalidOffsetNumber;
+ do_prune = prstate.nredirected > 0 ||
+ prstate.ndead > 0 ||
+ prstate.nunused > 0;
+
+ /*
+ * Only incur overhead of checking if we will do an FPI if we might use
+ * the information.
+ */
+ if (do_prune && pagefrz)
+ prune_fpi = XLogCheckBufferNeedsBackup(buffer);
+
+ /* Is the whole page freezable? And is there something to freeze */
+ whole_page_freezable = all_visible_except_removable &&
+ presult->all_frozen;
+
+ /*
+ * Freeze the page when heap_prepare_freeze_tuple indicates that at least
+ * one XID/MXID from before FreezeLimit/MultiXactCutoff is present. Also
+ * freeze when pruning generated an FPI, if doing so means that we set the
+ * page all-frozen afterwards (might not happen until final heap pass).
+ * XXX: Previously, we knew if pruning emitted an FPI by checking
+ * pgWalUsage.wal_fpi before and after pruning. Once the freeze and prune
+ * records are combined, this heuristic couldn't be used anymore. The
+ * opportunistic freeze heuristic must be improved; however, for now, try
+ * to approximate it.
+ */
+ do_freeze = pagefrz &&
+ (pagefrz->freeze_required ||
+ (whole_page_freezable && presult->nfrozen > 0 && (prune_fpi || hint_bit_fpi)));
+
/* Any error while applying the changes is critical */
START_CRIT_SECTION();
/* Have we found any prunable items? */
- if (prstate.nredirected > 0 || prstate.ndead > 0 || prstate.nunused > 0)
+ if (do_prune)
{
/*
* Apply the planned item changes, then repair page fragmentation, and
@@ -569,20 +610,6 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
/* Record number of newly-set-LP_DEAD items for caller */
presult->nnewlpdead = prstate.ndead;
- /*
- * Freeze the page when heap_prepare_freeze_tuple indicates that at least
- * one XID/MXID from before FreezeLimit/MultiXactCutoff is present. Also
- * freeze when pruning generated an FPI, if doing so means that we set the
- * page all-frozen afterwards (might not happen until final heap pass).
- */
- if (pagefrz)
- do_freeze = pagefrz->freeze_required ||
- (all_visible_except_removable && presult->all_frozen &&
- presult->nfrozen > 0 &&
- fpi_before != pgWalUsage.wal_fpi);
- else
- do_freeze = false;
-
if (do_freeze)
{
TransactionId frz_conflict_horizon = InvalidTransactionId;
--
2.40.1
--caj67xgx3lukmr5f
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="v9-0010-Separate-tuple-pre-freeze-checks-and-invoke-earli.patch"
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v7 09/16] Make opp freeze heuristic compatible with prune+freeze record
@ 2024-03-26 00:48 Melanie Plageman <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Melanie Plageman @ 2024-03-26 00:48 UTC (permalink / raw)
Once the prune and freeze records are combined, we will no longer be
able to use a test of whether or not pruning emitted an FPI to decide
whether or not to opportunistically freeze a freezable page.
While this heuristic should be improved, for now, approximate the
previous logic by keeping track of whether or not a hint bit FPI was
emitted during visibility checks (when checksums are on) and combine
that with checking XLogCheckBufferNeedsBackup(). If we just finished
deciding whether or not to prune and the current buffer seems to need an
FPI after modification, it is likely that pruning would have emitted an
FPI.
---
src/backend/access/heap/pruneheap.c | 57 +++++++++++++++++++++--------
1 file changed, 42 insertions(+), 15 deletions(-)
diff --git a/src/backend/access/heap/pruneheap.c b/src/backend/access/heap/pruneheap.c
index e009c7579dd..d38de9b063d 100644
--- a/src/backend/access/heap/pruneheap.c
+++ b/src/backend/access/heap/pruneheap.c
@@ -247,6 +247,10 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
TransactionId visibility_cutoff_xid;
bool do_freeze;
bool all_visible_except_removable;
+ bool do_prune;
+ bool whole_page_freezable;
+ bool hint_bit_fpi;
+ bool prune_fpi = false;
int64 fpi_before = pgWalUsage.wal_fpi;
/*
@@ -456,6 +460,13 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
}
}
+ /*
+ * If checksums are enabled, heap_prune_satisfies_vacuum() may have caused
+ * an FPI to be emitted. Then reset fpi_before for no prune case.
+ */
+ hint_bit_fpi = fpi_before != pgWalUsage.wal_fpi;
+ fpi_before = pgWalUsage.wal_fpi;
+
/*
* For vacuum, if the whole page will become frozen, we consider
* opportunistically freezing tuples. Dead tuples which will be removed by
@@ -500,11 +511,41 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
if (off_loc)
*off_loc = InvalidOffsetNumber;
+ do_prune = prstate.nredirected > 0 ||
+ prstate.ndead > 0 ||
+ prstate.nunused > 0;
+
+ /*
+ * Only incur overhead of checking if we will do an FPI if we might use
+ * the information.
+ */
+ if (do_prune && pagefrz)
+ prune_fpi = XLogCheckBufferNeedsBackup(buffer);
+
+ /* Is the whole page freezable? And is there something to freeze */
+ whole_page_freezable = all_visible_except_removable &&
+ presult->all_frozen;
+
+ /*
+ * Freeze the page when heap_prepare_freeze_tuple indicates that at least
+ * one XID/MXID from before FreezeLimit/MultiXactCutoff is present. Also
+ * freeze when pruning generated an FPI, if doing so means that we set the
+ * page all-frozen afterwards (might not happen until final heap pass).
+ * XXX: Previously, we knew if pruning emitted an FPI by checking
+ * pgWalUsage.wal_fpi before and after pruning. Once the freeze and prune
+ * records are combined, this heuristic couldn't be used anymore. The
+ * opportunistic freeze heuristic must be improved; however, for now, try
+ * to approximate it.
+ */
+ do_freeze = pagefrz &&
+ (pagefrz->freeze_required ||
+ (whole_page_freezable && presult->nfrozen > 0 && (prune_fpi || hint_bit_fpi)));
+
/* Any error while applying the changes is critical */
START_CRIT_SECTION();
/* Have we found any prunable items? */
- if (prstate.nredirected > 0 || prstate.ndead > 0 || prstate.nunused > 0)
+ if (do_prune)
{
/*
* Apply the planned item changes, then repair page fragmentation, and
@@ -569,20 +610,6 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
/* Record number of newly-set-LP_DEAD items for caller */
presult->nnewlpdead = prstate.ndead;
- /*
- * Freeze the page when heap_prepare_freeze_tuple indicates that at least
- * one XID/MXID from before FreezeLimit/MultiXactCutoff is present. Also
- * freeze when pruning generated an FPI, if doing so means that we set the
- * page all-frozen afterwards (might not happen until final heap pass).
- */
- if (pagefrz)
- do_freeze = pagefrz->freeze_required ||
- (all_visible_except_removable && presult->all_frozen &&
- presult->nfrozen > 0 &&
- fpi_before != pgWalUsage.wal_fpi);
- else
- do_freeze = false;
-
if (do_freeze)
{
TransactionId frz_conflict_horizon = InvalidTransactionId;
--
2.40.1
--ck6erxojvlx23byk
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="v7-0010-Separate-tuple-pre-freeze-checks-and-invoke-earli.patch"
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH v7 09/16] Make opp freeze heuristic compatible with prune+freeze record
@ 2024-03-26 00:48 Melanie Plageman <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Melanie Plageman @ 2024-03-26 00:48 UTC (permalink / raw)
Once the prune and freeze records are combined, we will no longer be
able to use a test of whether or not pruning emitted an FPI to decide
whether or not to opportunistically freeze a freezable page.
While this heuristic should be improved, for now, approximate the
previous logic by keeping track of whether or not a hint bit FPI was
emitted during visibility checks (when checksums are on) and combine
that with checking XLogCheckBufferNeedsBackup(). If we just finished
deciding whether or not to prune and the current buffer seems to need an
FPI after modification, it is likely that pruning would have emitted an
FPI.
---
src/backend/access/heap/pruneheap.c | 57 +++++++++++++++++++++--------
1 file changed, 42 insertions(+), 15 deletions(-)
diff --git a/src/backend/access/heap/pruneheap.c b/src/backend/access/heap/pruneheap.c
index e009c7579dd..d38de9b063d 100644
--- a/src/backend/access/heap/pruneheap.c
+++ b/src/backend/access/heap/pruneheap.c
@@ -247,6 +247,10 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
TransactionId visibility_cutoff_xid;
bool do_freeze;
bool all_visible_except_removable;
+ bool do_prune;
+ bool whole_page_freezable;
+ bool hint_bit_fpi;
+ bool prune_fpi = false;
int64 fpi_before = pgWalUsage.wal_fpi;
/*
@@ -456,6 +460,13 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
}
}
+ /*
+ * If checksums are enabled, heap_prune_satisfies_vacuum() may have caused
+ * an FPI to be emitted. Then reset fpi_before for no prune case.
+ */
+ hint_bit_fpi = fpi_before != pgWalUsage.wal_fpi;
+ fpi_before = pgWalUsage.wal_fpi;
+
/*
* For vacuum, if the whole page will become frozen, we consider
* opportunistically freezing tuples. Dead tuples which will be removed by
@@ -500,11 +511,41 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
if (off_loc)
*off_loc = InvalidOffsetNumber;
+ do_prune = prstate.nredirected > 0 ||
+ prstate.ndead > 0 ||
+ prstate.nunused > 0;
+
+ /*
+ * Only incur overhead of checking if we will do an FPI if we might use
+ * the information.
+ */
+ if (do_prune && pagefrz)
+ prune_fpi = XLogCheckBufferNeedsBackup(buffer);
+
+ /* Is the whole page freezable? And is there something to freeze */
+ whole_page_freezable = all_visible_except_removable &&
+ presult->all_frozen;
+
+ /*
+ * Freeze the page when heap_prepare_freeze_tuple indicates that at least
+ * one XID/MXID from before FreezeLimit/MultiXactCutoff is present. Also
+ * freeze when pruning generated an FPI, if doing so means that we set the
+ * page all-frozen afterwards (might not happen until final heap pass).
+ * XXX: Previously, we knew if pruning emitted an FPI by checking
+ * pgWalUsage.wal_fpi before and after pruning. Once the freeze and prune
+ * records are combined, this heuristic couldn't be used anymore. The
+ * opportunistic freeze heuristic must be improved; however, for now, try
+ * to approximate it.
+ */
+ do_freeze = pagefrz &&
+ (pagefrz->freeze_required ||
+ (whole_page_freezable && presult->nfrozen > 0 && (prune_fpi || hint_bit_fpi)));
+
/* Any error while applying the changes is critical */
START_CRIT_SECTION();
/* Have we found any prunable items? */
- if (prstate.nredirected > 0 || prstate.ndead > 0 || prstate.nunused > 0)
+ if (do_prune)
{
/*
* Apply the planned item changes, then repair page fragmentation, and
@@ -569,20 +610,6 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer,
/* Record number of newly-set-LP_DEAD items for caller */
presult->nnewlpdead = prstate.ndead;
- /*
- * Freeze the page when heap_prepare_freeze_tuple indicates that at least
- * one XID/MXID from before FreezeLimit/MultiXactCutoff is present. Also
- * freeze when pruning generated an FPI, if doing so means that we set the
- * page all-frozen afterwards (might not happen until final heap pass).
- */
- if (pagefrz)
- do_freeze = pagefrz->freeze_required ||
- (all_visible_except_removable && presult->all_frozen &&
- presult->nfrozen > 0 &&
- fpi_before != pgWalUsage.wal_fpi);
- else
- do_freeze = false;
-
if (do_freeze)
{
TransactionId frz_conflict_horizon = InvalidTransactionId;
--
2.40.1
--ck6erxojvlx23byk
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="v7-0010-Separate-tuple-pre-freeze-checks-and-invoke-earli.patch"
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: improve performance of pg_dump with many sequences
@ 2024-07-17 03:23 Nathan Bossart <[email protected]>
0 siblings, 1 reply; 21+ messages in thread
From: Nathan Bossart @ 2024-07-17 03:23 UTC (permalink / raw)
To: Michael Paquier <[email protected]>; +Cc: Euler Taveira <[email protected]>; pgsql-hackers
On Wed, Jul 17, 2024 at 11:30:04AM +0900, Michael Paquier wrote:
> Yeah, I have bumped on the same issue. In the long term, I also think
> that we'd better have pg_sequence_last_value() return a row with
> is_called and the value scanned. As you say, it won't help except
> when upgrading from versions of Postgres that are at least to v18,
> assuming that this change gets in the tree, but that would be much
> better in the long term and time flies fast.
AFAICT pg_sequence_last_value() is basically an undocumented internal
function only really intended for use by the pg_sequences system view, so
changing the function like this for v18 might not be out of the question.
Otherwise, I think we'd have to create a strikingly similar function with
slightly different behavior, which would be a bizarre place to end up.
--
nathan
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: improve performance of pg_dump with many sequences
@ 2024-07-17 18:48 Nathan Bossart <[email protected]>
parent: Nathan Bossart <[email protected]>
0 siblings, 1 reply; 21+ messages in thread
From: Nathan Bossart @ 2024-07-17 18:48 UTC (permalink / raw)
To: Michael Paquier <[email protected]>; +Cc: Euler Taveira <[email protected]>; pgsql-hackers
On Tue, Jul 16, 2024 at 10:23:08PM -0500, Nathan Bossart wrote:
> On Wed, Jul 17, 2024 at 11:30:04AM +0900, Michael Paquier wrote:
>> Yeah, I have bumped on the same issue. In the long term, I also think
>> that we'd better have pg_sequence_last_value() return a row with
>> is_called and the value scanned. As you say, it won't help except
>> when upgrading from versions of Postgres that are at least to v18,
>> assuming that this change gets in the tree, but that would be much
>> better in the long term and time flies fast.
>
> AFAICT pg_sequence_last_value() is basically an undocumented internal
> function only really intended for use by the pg_sequences system view, so
> changing the function like this for v18 might not be out of the question.
> Otherwise, I think we'd have to create a strikingly similar function with
> slightly different behavior, which would be a bizarre place to end up.
On second thought, I worry that this change might needlessly complicate the
pg_sequences system view. Maybe we should just add a
pg_sequence_get_tuple() function that returns everything in
FormData_pg_sequence_data for a given sequence OID...
--
nathan
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: improve performance of pg_dump with many sequences
@ 2024-07-17 18:59 Tom Lane <[email protected]>
parent: Nathan Bossart <[email protected]>
0 siblings, 1 reply; 21+ messages in thread
From: Tom Lane @ 2024-07-17 18:59 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: Michael Paquier <[email protected]>; Euler Taveira <[email protected]>; pgsql-hackers
Nathan Bossart <[email protected]> writes:
> On second thought, I worry that this change might needlessly complicate the
> pg_sequences system view. Maybe we should just add a
> pg_sequence_get_tuple() function that returns everything in
> FormData_pg_sequence_data for a given sequence OID...
Uh ... why do we need a function, rather than just
select * from pg_sequence
?
regards, tom lane
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: improve performance of pg_dump with many sequences
@ 2024-07-17 19:05 Nathan Bossart <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 1 reply; 21+ messages in thread
From: Nathan Bossart @ 2024-07-17 19:05 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Michael Paquier <[email protected]>; Euler Taveira <[email protected]>; pgsql-hackers
On Wed, Jul 17, 2024 at 02:59:26PM -0400, Tom Lane wrote:
> Nathan Bossart <[email protected]> writes:
>> On second thought, I worry that this change might needlessly complicate the
>> pg_sequences system view. Maybe we should just add a
>> pg_sequence_get_tuple() function that returns everything in
>> FormData_pg_sequence_data for a given sequence OID...
>
> Uh ... why do we need a function, rather than just
>
> select * from pg_sequence
We can use that for dumpSequence(), but dumpSequenceData() requires
information from the sequence tuple itself. Right now, we query each
sequence relation individually for that data, and I'm trying to find a way
to cut down on those round trips.
--
nathan
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: improve performance of pg_dump with many sequences
@ 2024-07-17 19:11 Tom Lane <[email protected]>
parent: Nathan Bossart <[email protected]>
0 siblings, 1 reply; 21+ messages in thread
From: Tom Lane @ 2024-07-17 19:11 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: Michael Paquier <[email protected]>; Euler Taveira <[email protected]>; pgsql-hackers
Nathan Bossart <[email protected]> writes:
> On Wed, Jul 17, 2024 at 02:59:26PM -0400, Tom Lane wrote:
>> Uh ... why do we need a function, rather than just
>> select * from pg_sequence
> We can use that for dumpSequence(), but dumpSequenceData() requires
> information from the sequence tuple itself. Right now, we query each
> sequence relation individually for that data, and I'm trying to find a way
> to cut down on those round trips.
Ah, I confused FormData_pg_sequence_data with FormData_pg_sequence.
Sorry for the noise.
regards, tom lane
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: improve performance of pg_dump with many sequences
@ 2024-07-18 03:45 Nathan Bossart <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 1 reply; 21+ messages in thread
From: Nathan Bossart @ 2024-07-18 03:45 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Michael Paquier <[email protected]>; Euler Taveira <[email protected]>; pgsql-hackers
Here is an attempt at adding a new function that returns the sequence tuple
and using that to avoid querying each sequence relation individually in
dumpSequenceData().
If we instead wanted to change pg_sequence_last_value() to return both
is_called and last_value, I think we could modify the pg_sequences system
view to use a LATERAL subquery, i.e.,
SELECT
...
CASE
WHEN L.is_called THEN L.last_value
ELSE NULL
END AS last_value
FROM pg_sequence S
...
JOIN LATERAL pg_sequence_last_value(S.seqrelid) L ON true
...
That doesn't seem so bad, and it'd avoid an extra pg_proc entry, but it
would probably break anything that calls pg_sequence_last_value() directly.
Thoughts?
--
nathan
From c5e139efdae92c3902daee886075b509a0720368 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Mon, 15 Jul 2024 13:13:05 -0500
Subject: [PATCH v4 1/4] parse sequence information
---
src/bin/pg_dump/pg_dump.c | 64 ++++++++++++++++-----------------------
1 file changed, 26 insertions(+), 38 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index b8b1888bd3..bbcbe581aa 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -17198,18 +17198,16 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
{
DumpOptions *dopt = fout->dopt;
PGresult *res;
- char *startv,
- *incby,
- *maxv,
- *minv,
- *cache,
- *seqtype;
+ char seqtype[10];
bool cycled;
bool is_ascending;
int64 default_minv,
- default_maxv;
- char bufm[32],
- bufx[32];
+ default_maxv,
+ minv,
+ maxv,
+ startv,
+ incby,
+ cache;
PQExpBuffer query = createPQExpBuffer();
PQExpBuffer delqry = createPQExpBuffer();
char *qseqname;
@@ -17251,16 +17249,21 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
PQntuples(res)),
tbinfo->dobj.name, PQntuples(res));
- seqtype = PQgetvalue(res, 0, 0);
- startv = PQgetvalue(res, 0, 1);
- incby = PQgetvalue(res, 0, 2);
- maxv = PQgetvalue(res, 0, 3);
- minv = PQgetvalue(res, 0, 4);
- cache = PQgetvalue(res, 0, 5);
+ Assert(strlen(PQgetvalue(res, 0, 0)) < sizeof(seqtype));
+ strncpy(seqtype, PQgetvalue(res, 0, 0), sizeof(seqtype));
+ seqtype[sizeof(seqtype) - 1] = '\0';
+
+ startv = strtoi64(PQgetvalue(res, 0, 1), NULL, 10);
+ incby = strtoi64(PQgetvalue(res, 0, 2), NULL, 10);
+ maxv = strtoi64(PQgetvalue(res, 0, 3), NULL, 10);
+ minv = strtoi64(PQgetvalue(res, 0, 4), NULL, 10);
+ cache = strtoi64(PQgetvalue(res, 0, 5), NULL, 10);
cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
+ PQclear(res);
+
/* Calculate default limits for a sequence of this type */
- is_ascending = (incby[0] != '-');
+ is_ascending = (incby >= 0);
if (strcmp(seqtype, "smallint") == 0)
{
default_minv = is_ascending ? 1 : PG_INT16_MIN;
@@ -17282,19 +17285,6 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
default_minv = default_maxv = 0; /* keep compiler quiet */
}
- /*
- * 64-bit strtol() isn't very portable, so convert the limits to strings
- * and compare that way.
- */
- snprintf(bufm, sizeof(bufm), INT64_FORMAT, default_minv);
- snprintf(bufx, sizeof(bufx), INT64_FORMAT, default_maxv);
-
- /* Don't print minv/maxv if they match the respective default limit */
- if (strcmp(minv, bufm) == 0)
- minv = NULL;
- if (strcmp(maxv, bufx) == 0)
- maxv = NULL;
-
/*
* Identity sequences are not to be dropped separately.
*/
@@ -17346,22 +17336,22 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
appendPQExpBuffer(query, " AS %s\n", seqtype);
}
- appendPQExpBuffer(query, " START WITH %s\n", startv);
+ appendPQExpBuffer(query, " START WITH " INT64_FORMAT "\n", startv);
- appendPQExpBuffer(query, " INCREMENT BY %s\n", incby);
+ appendPQExpBuffer(query, " INCREMENT BY " INT64_FORMAT "\n", incby);
- if (minv)
- appendPQExpBuffer(query, " MINVALUE %s\n", minv);
+ if (minv != default_minv)
+ appendPQExpBuffer(query, " MINVALUE " INT64_FORMAT "\n", minv);
else
appendPQExpBufferStr(query, " NO MINVALUE\n");
- if (maxv)
- appendPQExpBuffer(query, " MAXVALUE %s\n", maxv);
+ if (maxv != default_maxv)
+ appendPQExpBuffer(query, " MAXVALUE " INT64_FORMAT "\n", maxv);
else
appendPQExpBufferStr(query, " NO MAXVALUE\n");
appendPQExpBuffer(query,
- " CACHE %s%s",
+ " CACHE " INT64_FORMAT "%s",
cache, (cycled ? "\n CYCLE" : ""));
if (tbinfo->is_identity_sequence)
@@ -17448,8 +17438,6 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
tbinfo->dobj.namespace->dobj.name, tbinfo->rolname,
tbinfo->dobj.catId, 0, tbinfo->dobj.dumpId);
- PQclear(res);
-
destroyPQExpBuffer(query);
destroyPQExpBuffer(delqry);
free(qseqname);
--
2.39.3 (Apple Git-146)
From b7fcaba2c5eef8824153c263c2df516783f0225b Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Tue, 9 Jul 2024 14:06:23 -0500
Subject: [PATCH v4 2/4] cache sequence information
---
src/bin/pg_dump/pg_dump.c | 155 +++++++++++++++++++++++++------
src/tools/pgindent/typedefs.list | 1 +
2 files changed, 128 insertions(+), 28 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index bbcbe581aa..a54e32c7be 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -104,6 +104,18 @@ typedef struct
RelFileNumber toast_index_relfilenumber; /* toast table index filenode */
} BinaryUpgradeClassOidItem;
+typedef struct
+{
+ Oid oid; /* sequence OID */
+ char seqtype[10]; /* data type of sequence */
+ bool cycled; /* whether sequence cycles */
+ int64 minv; /* minimum value */
+ int64 maxv; /* maximum value */
+ int64 startv; /* start value */
+ int64 incby; /* increment value */
+ int64 cache; /* cache size */
+} SequenceItem;
+
typedef enum OidOptions
{
zeroIsError = 1,
@@ -173,6 +185,10 @@ static int nseclabels = 0;
static BinaryUpgradeClassOidItem *binaryUpgradeClassOids = NULL;
static int nbinaryUpgradeClassOids = 0;
+/* sorted table of sequences */
+static SequenceItem *sequences = NULL;
+static int nsequences = 0;
+
/*
* The default number of rows per INSERT when
* --inserts is specified without --rows-per-insert
@@ -270,6 +286,7 @@ static void dumpTable(Archive *fout, const TableInfo *tbinfo);
static void dumpTableSchema(Archive *fout, const TableInfo *tbinfo);
static void dumpTableAttach(Archive *fout, const TableAttachInfo *attachinfo);
static void dumpAttrDef(Archive *fout, const AttrDefInfo *adinfo);
+static void collectSequences(Archive *fout);
static void dumpSequence(Archive *fout, const TableInfo *tbinfo);
static void dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo);
static void dumpIndex(Archive *fout, const IndxInfo *indxinfo);
@@ -992,6 +1009,9 @@ main(int argc, char **argv)
if (dopt.binary_upgrade)
collectBinaryUpgradeClassOids(fout);
+ /* Collect sequence information. */
+ collectSequences(fout);
+
/* Lastly, create dummy objects to represent the section boundaries */
boundaryObjs = createBoundaryObjects();
@@ -17189,6 +17209,71 @@ dumpTableConstraintComment(Archive *fout, const ConstraintInfo *coninfo)
free(qtabname);
}
+/*
+ * bsearch() comparator for SequenceItem
+ */
+static int
+SequenceItemCmp(const void *p1, const void *p2)
+{
+ SequenceItem v1 = *((const SequenceItem *) p1);
+ SequenceItem v2 = *((const SequenceItem *) p2);
+
+ return pg_cmp_u32(v1.oid, v2.oid);
+}
+
+/*
+ * collectSequences
+ *
+ * Construct a table of sequence information. This table is sorted by OID for
+ * speed in lookup.
+ */
+static void
+collectSequences(Archive *fout)
+{
+ PGresult *res;
+ const char *query;
+
+ /*
+ * Before Postgres 10, sequence metadata is in the sequence itself. We
+ * could likely make use of the sorted table with some extra effort, but
+ * for now it seems unlikely to be worth it.
+ */
+ if (fout->remoteVersion < 100000)
+ return;
+
+ query = "SELECT seqrelid, format_type(seqtypid, NULL), "
+ "seqstart, seqincrement, "
+ "seqmax, seqmin, "
+ "seqcache, seqcycle "
+ "FROM pg_catalog.pg_sequence "
+ "ORDER BY seqrelid";
+
+ res = ExecuteSqlQuery(fout, query, PGRES_TUPLES_OK);
+
+ nsequences = PQntuples(res);
+ sequences = (SequenceItem *) pg_malloc(nsequences * sizeof(SequenceItem));
+
+ for (int i = 0; i < nsequences; i++)
+ {
+ size_t seqtype_sz = sizeof(((SequenceItem *) 0)->seqtype);
+
+ sequences[i].oid = atooid(PQgetvalue(res, i, 0));
+
+ Assert(strlen(PQgetvalue(res, i, 1)) < seqtype_sz);
+ strncpy(sequences[i].seqtype, PQgetvalue(res, i, 1), seqtype_sz);
+ sequences[i].seqtype[seqtype_sz - 1] = '\0';
+
+ sequences[i].startv = strtoi64(PQgetvalue(res, i, 2), NULL, 10);
+ sequences[i].incby = strtoi64(PQgetvalue(res, i, 3), NULL, 10);
+ sequences[i].maxv = strtoi64(PQgetvalue(res, i, 4), NULL, 10);
+ sequences[i].minv = strtoi64(PQgetvalue(res, i, 5), NULL, 10);
+ sequences[i].cache = strtoi64(PQgetvalue(res, i, 6), NULL, 10);
+ sequences[i].cycled = (strcmp(PQgetvalue(res, i, 7), "t") == 0);
+ }
+
+ PQclear(res);
+}
+
/*
* dumpSequence
* write the declaration (not data) of one user-defined sequence
@@ -17197,7 +17282,6 @@ static void
dumpSequence(Archive *fout, const TableInfo *tbinfo)
{
DumpOptions *dopt = fout->dopt;
- PGresult *res;
char seqtype[10];
bool cycled;
bool is_ascending;
@@ -17215,19 +17299,34 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
qseqname = pg_strdup(fmtId(tbinfo->dobj.name));
+ /*
+ * For versions >= 10, the sequence information is gathered in a sorted
+ * table before any calls to dumpSequence(). See collectSequences() for
+ * more information.
+ */
if (fout->remoteVersion >= 100000)
{
- appendPQExpBuffer(query,
- "SELECT format_type(seqtypid, NULL), "
- "seqstart, seqincrement, "
- "seqmax, seqmin, "
- "seqcache, seqcycle "
- "FROM pg_catalog.pg_sequence "
- "WHERE seqrelid = '%u'::oid",
- tbinfo->dobj.catId.oid);
+ SequenceItem key = {0};
+ SequenceItem *entry;
+
+ Assert(sequences);
+
+ key.oid = tbinfo->dobj.catId.oid;
+ entry = bsearch(&key, sequences, nsequences,
+ sizeof(SequenceItem), SequenceItemCmp);
+
+ strncpy(seqtype, entry->seqtype, sizeof(seqtype));
+ startv = entry->startv;
+ incby = entry->incby;
+ maxv = entry->maxv;
+ minv = entry->minv;
+ cache = entry->cache;
+ cycled = entry->cycled;
}
else
{
+ PGresult *res;
+
/*
* Before PostgreSQL 10, sequence metadata is in the sequence itself.
*
@@ -17239,28 +17338,28 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
"start_value, increment_by, max_value, min_value, "
"cache_value, is_cycled FROM %s",
fmtQualifiedDumpable(tbinfo));
- }
-
- res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
- if (PQntuples(res) != 1)
- pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
- "query to get data of sequence \"%s\" returned %d rows (expected 1)",
- PQntuples(res)),
- tbinfo->dobj.name, PQntuples(res));
-
- Assert(strlen(PQgetvalue(res, 0, 0)) < sizeof(seqtype));
- strncpy(seqtype, PQgetvalue(res, 0, 0), sizeof(seqtype));
- seqtype[sizeof(seqtype) - 1] = '\0';
+ res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
- startv = strtoi64(PQgetvalue(res, 0, 1), NULL, 10);
- incby = strtoi64(PQgetvalue(res, 0, 2), NULL, 10);
- maxv = strtoi64(PQgetvalue(res, 0, 3), NULL, 10);
- minv = strtoi64(PQgetvalue(res, 0, 4), NULL, 10);
- cache = strtoi64(PQgetvalue(res, 0, 5), NULL, 10);
- cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
+ if (PQntuples(res) != 1)
+ pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
+ "query to get data of sequence \"%s\" returned %d rows (expected 1)",
+ PQntuples(res)),
+ tbinfo->dobj.name, PQntuples(res));
+
+ Assert(strlen(PQgetvalue(res, 0, 0)) < sizeof(seqtype));
+ strncpy(seqtype, PQgetvalue(res, 0, 0), sizeof(seqtype));
+ seqtype[sizeof(seqtype) - 1] = '\0';
+
+ startv = strtoi64(PQgetvalue(res, 0, 1), NULL, 10);
+ incby = strtoi64(PQgetvalue(res, 0, 2), NULL, 10);
+ maxv = strtoi64(PQgetvalue(res, 0, 3), NULL, 10);
+ minv = strtoi64(PQgetvalue(res, 0, 4), NULL, 10);
+ cache = strtoi64(PQgetvalue(res, 0, 5), NULL, 10);
+ cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
- PQclear(res);
+ PQclear(res);
+ }
/* Calculate default limits for a sequence of this type */
is_ascending = (incby >= 0);
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index b4d7f9217c..a09adcfb90 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2572,6 +2572,7 @@ SeqScan
SeqScanState
SeqTable
SeqTableData
+SequenceItem
SerCommitSeqNo
SerialControl
SerialIOData
--
2.39.3 (Apple Git-146)
From 86f0b7c26be5bd257a5039bdfbe127983e1228c7 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Wed, 17 Jul 2024 17:09:03 -0500
Subject: [PATCH v4 3/4] introduce pg_sequence_read_tuple
XXX: NEEDS CATVERSION BUMP
---
src/backend/commands/sequence.c | 62 ++++++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 6 +++
src/test/regress/expected/sequence.out | 7 +++
src/test/regress/sql/sequence.sql | 3 ++
4 files changed, 78 insertions(+)
diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c
index 9f28d40466..45c4cb3936 100644
--- a/src/backend/commands/sequence.c
+++ b/src/backend/commands/sequence.c
@@ -1773,6 +1773,68 @@ pg_sequence_parameters(PG_FUNCTION_ARGS)
return HeapTupleGetDatum(heap_form_tuple(tupdesc, values, isnull));
}
+
+/*
+ * Return the sequence tuple.
+ *
+ * This is primarily intended for use by pg_dump to gather sequence data
+ * without needing to individually query each sequence relation.
+ */
+Datum
+pg_sequence_read_tuple(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+ SeqTable elm;
+ Relation seqrel;
+ Datum values[SEQ_COL_LASTCOL];
+ bool isnull[SEQ_COL_LASTCOL];
+ TupleDesc resultTupleDesc;
+ HeapTuple resultHeapTuple;
+ Datum result;
+
+ resultTupleDesc = CreateTemplateTupleDesc(SEQ_COL_LASTCOL);
+ TupleDescInitEntry(resultTupleDesc, (AttrNumber) 1, "last_value",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(resultTupleDesc, (AttrNumber) 2, "log_cnt",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(resultTupleDesc, (AttrNumber) 3, "is_called",
+ BOOLOID, -1, 0);
+ resultTupleDesc = BlessTupleDesc(resultTupleDesc);
+
+ init_sequence(relid, &elm, &seqrel);
+
+ /*
+ * Return all NULLs for sequences for which we lack privileges, other
+ * sessions' temporary sequences, and unlogged sequences on standbys.
+ */
+ if (pg_class_aclcheck(relid, GetUserId(), ACL_SELECT | ACL_USAGE) == ACLCHECK_OK &&
+ !RELATION_IS_OTHER_TEMP(seqrel) &&
+ (RelationIsPermanent(seqrel) || !RecoveryInProgress()))
+ {
+ Buffer buf;
+ HeapTupleData seqtuple;
+ Form_pg_sequence_data seq;
+
+ seq = read_seq_tuple(seqrel, &buf, &seqtuple);
+
+ memset(isnull, false, sizeof(isnull));
+ values[0] = Int64GetDatum(seq->last_value);
+ values[1] = Int64GetDatum(seq->log_cnt);
+ values[2] = BoolGetDatum(seq->is_called);
+
+ UnlockReleaseBuffer(buf);
+ }
+ else
+ memset(isnull, true, sizeof(isnull));
+
+ sequence_close(seqrel, NoLock);
+
+ resultHeapTuple = heap_form_tuple(resultTupleDesc, values, isnull);
+ result = HeapTupleGetDatum(resultHeapTuple);
+ PG_RETURN_DATUM(result);
+}
+
+
/*
* Return the last value from the sequence
*
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 73d9cf8582..a16aec302e 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3329,6 +3329,12 @@
proname => 'pg_sequence_last_value', provolatile => 'v', proparallel => 'u',
prorettype => 'int8', proargtypes => 'regclass',
prosrc => 'pg_sequence_last_value' },
+{ oid => '9876', descr => 'return sequence tuple, for use by pg_dump',
+ proname => 'pg_sequence_read_tuple', provolatile => 'v', proparallel => 'u',
+ prorettype => 'record', proargtypes => 'regclass',
+ proallargtypes => '{regclass,int8,int8,bool}', proargmodes => '{i,o,o,o}',
+ proargnames => '{sequence_oid,last_value,log_cnt,is_called}',
+ prosrc => 'pg_sequence_read_tuple' },
{ oid => '275', descr => 'return the next oid for a system table',
proname => 'pg_nextoid', provolatile => 'v', proparallel => 'u',
diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out
index 2b47b7796b..e749c4574e 100644
--- a/src/test/regress/expected/sequence.out
+++ b/src/test/regress/expected/sequence.out
@@ -839,4 +839,11 @@ SELECT nextval('test_seq1');
3
(1 row)
+-- pg_sequence_read_tuple
+SELECT * FROM pg_sequence_read_tuple('test_seq1');
+ last_value | log_cnt | is_called
+------------+---------+-----------
+ 10 | 32 | t
+(1 row)
+
DROP SEQUENCE test_seq1;
diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sql
index 674f5f1f66..ea447938ae 100644
--- a/src/test/regress/sql/sequence.sql
+++ b/src/test/regress/sql/sequence.sql
@@ -413,4 +413,7 @@ SELECT nextval('test_seq1');
SELECT nextval('test_seq1');
SELECT nextval('test_seq1');
+-- pg_sequence_read_tuple
+SELECT * FROM pg_sequence_read_tuple('test_seq1');
+
DROP SEQUENCE test_seq1;
--
2.39.3 (Apple Git-146)
From d22c7281416280480b91cff02330a858946364ad Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Wed, 17 Jul 2024 22:13:08 -0500
Subject: [PATCH v4 4/4] cache sequence data
---
src/bin/pg_dump/pg_dump.c | 91 +++++++++++++++++++++++++++++----------
1 file changed, 68 insertions(+), 23 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index a54e32c7be..14019907db 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -114,6 +114,8 @@ typedef struct
int64 startv; /* start value */
int64 incby; /* increment value */
int64 cache; /* cache size */
+ int64 last_value; /* last value of sequence */
+ bool is_called; /* whether nextval advances before returning */
} SequenceItem;
typedef enum OidOptions
@@ -17237,16 +17239,30 @@ collectSequences(Archive *fout)
* Before Postgres 10, sequence metadata is in the sequence itself. We
* could likely make use of the sorted table with some extra effort, but
* for now it seems unlikely to be worth it.
+ *
+ * Since version 18, we can gather the sequence data in this query with
+ * pg_sequence_read_tuple(), but we only do so for non-schema-only dumps.
*/
if (fout->remoteVersion < 100000)
return;
-
- query = "SELECT seqrelid, format_type(seqtypid, NULL), "
- "seqstart, seqincrement, "
- "seqmax, seqmin, "
- "seqcache, seqcycle "
- "FROM pg_catalog.pg_sequence "
- "ORDER BY seqrelid";
+ else if (fout->remoteVersion < 180000 ||
+ (fout->dopt->schemaOnly && !fout->dopt->sequence_data))
+ query = "SELECT seqrelid, format_type(seqtypid, NULL), "
+ "seqstart, seqincrement, "
+ "seqmax, seqmin, "
+ "seqcache, seqcycle, "
+ "NULL, 'f' "
+ "FROM pg_catalog.pg_sequence "
+ "ORDER BY seqrelid";
+ else
+ query = "SELECT seqrelid, format_type(seqtypid, NULL), "
+ "seqstart, seqincrement, "
+ "seqmax, seqmin, "
+ "seqcache, seqcycle, "
+ "last_value, is_called "
+ "FROM pg_catalog.pg_sequence, "
+ "pg_sequence_read_tuple(seqrelid) "
+ "ORDER BY seqrelid;";
res = ExecuteSqlQuery(fout, query, PGRES_TUPLES_OK);
@@ -17269,6 +17285,8 @@ collectSequences(Archive *fout)
sequences[i].minv = strtoi64(PQgetvalue(res, i, 5), NULL, 10);
sequences[i].cache = strtoi64(PQgetvalue(res, i, 6), NULL, 10);
sequences[i].cycled = (strcmp(PQgetvalue(res, i, 7), "t") == 0);
+ sequences[i].last_value = strtoi64(PQgetvalue(res, i, 8), NULL, 10);
+ sequences[i].is_called = (strcmp(PQgetvalue(res, i, 9), "t") == 0);
}
PQclear(res);
@@ -17550,30 +17568,59 @@ static void
dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo)
{
TableInfo *tbinfo = tdinfo->tdtable;
- PGresult *res;
- char *last;
+ int64 last;
bool called;
PQExpBuffer query = createPQExpBuffer();
- appendPQExpBuffer(query,
- "SELECT last_value, is_called FROM %s",
- fmtQualifiedDumpable(tbinfo));
+ /*
+ * For versions >= 18, the sequence information is gathered in the sorted
+ * array before any calls to dumpSequenceData(). See collectSequences()
+ * for more information.
+ *
+ * For older versions, we have to query the sequence relations
+ * individually.
+ */
+ if (fout->remoteVersion < 180000)
+ {
+ PGresult *res;
- res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
+ appendPQExpBuffer(query,
+ "SELECT last_value, is_called FROM %s",
+ fmtQualifiedDumpable(tbinfo));
- if (PQntuples(res) != 1)
- pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
- "query to get data of sequence \"%s\" returned %d rows (expected 1)",
- PQntuples(res)),
- tbinfo->dobj.name, PQntuples(res));
+ res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
- last = PQgetvalue(res, 0, 0);
- called = (strcmp(PQgetvalue(res, 0, 1), "t") == 0);
+ if (PQntuples(res) != 1)
+ pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
+ "query to get data of sequence \"%s\" returned %d rows (expected 1)",
+ PQntuples(res)),
+ tbinfo->dobj.name, PQntuples(res));
+
+ last = strtoi64(PQgetvalue(res, 0, 0), NULL, 10);
+ called = (strcmp(PQgetvalue(res, 0, 1), "t") == 0);
+
+ PQclear(res);
+ }
+ else
+ {
+ SequenceItem key = {0};
+ SequenceItem *entry;
+
+ Assert(sequences);
+ Assert(tbinfo->dobj.catId.oid);
+
+ key.oid = tbinfo->dobj.catId.oid;
+ entry = bsearch(&key, sequences, nsequences,
+ sizeof(SequenceItem), SequenceItemCmp);
+
+ last = entry->last_value;
+ called = entry->is_called;
+ }
resetPQExpBuffer(query);
appendPQExpBufferStr(query, "SELECT pg_catalog.setval(");
appendStringLiteralAH(query, fmtQualifiedDumpable(tbinfo), fout);
- appendPQExpBuffer(query, ", %s, %s);\n",
+ appendPQExpBuffer(query, ", " INT64_FORMAT ", %s);\n",
last, (called ? "true" : "false"));
if (tdinfo->dobj.dump & DUMP_COMPONENT_DATA)
@@ -17587,8 +17634,6 @@ dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo)
.deps = &(tbinfo->dobj.dumpId),
.nDeps = 1));
- PQclear(res);
-
destroyPQExpBuffer(query);
}
--
2.39.3 (Apple Git-146)
Attachments:
[text/plain] v4-0001-parse-sequence-information.patch (4.0K, ../../ZpiP3I6UVcgsCCWf@nathan/2-v4-0001-parse-sequence-information.patch)
download | inline diff:
From c5e139efdae92c3902daee886075b509a0720368 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Mon, 15 Jul 2024 13:13:05 -0500
Subject: [PATCH v4 1/4] parse sequence information
---
src/bin/pg_dump/pg_dump.c | 64 ++++++++++++++++-----------------------
1 file changed, 26 insertions(+), 38 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index b8b1888bd3..bbcbe581aa 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -17198,18 +17198,16 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
{
DumpOptions *dopt = fout->dopt;
PGresult *res;
- char *startv,
- *incby,
- *maxv,
- *minv,
- *cache,
- *seqtype;
+ char seqtype[10];
bool cycled;
bool is_ascending;
int64 default_minv,
- default_maxv;
- char bufm[32],
- bufx[32];
+ default_maxv,
+ minv,
+ maxv,
+ startv,
+ incby,
+ cache;
PQExpBuffer query = createPQExpBuffer();
PQExpBuffer delqry = createPQExpBuffer();
char *qseqname;
@@ -17251,16 +17249,21 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
PQntuples(res)),
tbinfo->dobj.name, PQntuples(res));
- seqtype = PQgetvalue(res, 0, 0);
- startv = PQgetvalue(res, 0, 1);
- incby = PQgetvalue(res, 0, 2);
- maxv = PQgetvalue(res, 0, 3);
- minv = PQgetvalue(res, 0, 4);
- cache = PQgetvalue(res, 0, 5);
+ Assert(strlen(PQgetvalue(res, 0, 0)) < sizeof(seqtype));
+ strncpy(seqtype, PQgetvalue(res, 0, 0), sizeof(seqtype));
+ seqtype[sizeof(seqtype) - 1] = '\0';
+
+ startv = strtoi64(PQgetvalue(res, 0, 1), NULL, 10);
+ incby = strtoi64(PQgetvalue(res, 0, 2), NULL, 10);
+ maxv = strtoi64(PQgetvalue(res, 0, 3), NULL, 10);
+ minv = strtoi64(PQgetvalue(res, 0, 4), NULL, 10);
+ cache = strtoi64(PQgetvalue(res, 0, 5), NULL, 10);
cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
+ PQclear(res);
+
/* Calculate default limits for a sequence of this type */
- is_ascending = (incby[0] != '-');
+ is_ascending = (incby >= 0);
if (strcmp(seqtype, "smallint") == 0)
{
default_minv = is_ascending ? 1 : PG_INT16_MIN;
@@ -17282,19 +17285,6 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
default_minv = default_maxv = 0; /* keep compiler quiet */
}
- /*
- * 64-bit strtol() isn't very portable, so convert the limits to strings
- * and compare that way.
- */
- snprintf(bufm, sizeof(bufm), INT64_FORMAT, default_minv);
- snprintf(bufx, sizeof(bufx), INT64_FORMAT, default_maxv);
-
- /* Don't print minv/maxv if they match the respective default limit */
- if (strcmp(minv, bufm) == 0)
- minv = NULL;
- if (strcmp(maxv, bufx) == 0)
- maxv = NULL;
-
/*
* Identity sequences are not to be dropped separately.
*/
@@ -17346,22 +17336,22 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
appendPQExpBuffer(query, " AS %s\n", seqtype);
}
- appendPQExpBuffer(query, " START WITH %s\n", startv);
+ appendPQExpBuffer(query, " START WITH " INT64_FORMAT "\n", startv);
- appendPQExpBuffer(query, " INCREMENT BY %s\n", incby);
+ appendPQExpBuffer(query, " INCREMENT BY " INT64_FORMAT "\n", incby);
- if (minv)
- appendPQExpBuffer(query, " MINVALUE %s\n", minv);
+ if (minv != default_minv)
+ appendPQExpBuffer(query, " MINVALUE " INT64_FORMAT "\n", minv);
else
appendPQExpBufferStr(query, " NO MINVALUE\n");
- if (maxv)
- appendPQExpBuffer(query, " MAXVALUE %s\n", maxv);
+ if (maxv != default_maxv)
+ appendPQExpBuffer(query, " MAXVALUE " INT64_FORMAT "\n", maxv);
else
appendPQExpBufferStr(query, " NO MAXVALUE\n");
appendPQExpBuffer(query,
- " CACHE %s%s",
+ " CACHE " INT64_FORMAT "%s",
cache, (cycled ? "\n CYCLE" : ""));
if (tbinfo->is_identity_sequence)
@@ -17448,8 +17438,6 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
tbinfo->dobj.namespace->dobj.name, tbinfo->rolname,
tbinfo->dobj.catId, 0, tbinfo->dobj.dumpId);
- PQclear(res);
-
destroyPQExpBuffer(query);
destroyPQExpBuffer(delqry);
free(qseqname);
--
2.39.3 (Apple Git-146)
[text/plain] v4-0002-cache-sequence-information.patch (7.9K, ../../ZpiP3I6UVcgsCCWf@nathan/3-v4-0002-cache-sequence-information.patch)
download | inline diff:
From b7fcaba2c5eef8824153c263c2df516783f0225b Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Tue, 9 Jul 2024 14:06:23 -0500
Subject: [PATCH v4 2/4] cache sequence information
---
src/bin/pg_dump/pg_dump.c | 155 +++++++++++++++++++++++++------
src/tools/pgindent/typedefs.list | 1 +
2 files changed, 128 insertions(+), 28 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index bbcbe581aa..a54e32c7be 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -104,6 +104,18 @@ typedef struct
RelFileNumber toast_index_relfilenumber; /* toast table index filenode */
} BinaryUpgradeClassOidItem;
+typedef struct
+{
+ Oid oid; /* sequence OID */
+ char seqtype[10]; /* data type of sequence */
+ bool cycled; /* whether sequence cycles */
+ int64 minv; /* minimum value */
+ int64 maxv; /* maximum value */
+ int64 startv; /* start value */
+ int64 incby; /* increment value */
+ int64 cache; /* cache size */
+} SequenceItem;
+
typedef enum OidOptions
{
zeroIsError = 1,
@@ -173,6 +185,10 @@ static int nseclabels = 0;
static BinaryUpgradeClassOidItem *binaryUpgradeClassOids = NULL;
static int nbinaryUpgradeClassOids = 0;
+/* sorted table of sequences */
+static SequenceItem *sequences = NULL;
+static int nsequences = 0;
+
/*
* The default number of rows per INSERT when
* --inserts is specified without --rows-per-insert
@@ -270,6 +286,7 @@ static void dumpTable(Archive *fout, const TableInfo *tbinfo);
static void dumpTableSchema(Archive *fout, const TableInfo *tbinfo);
static void dumpTableAttach(Archive *fout, const TableAttachInfo *attachinfo);
static void dumpAttrDef(Archive *fout, const AttrDefInfo *adinfo);
+static void collectSequences(Archive *fout);
static void dumpSequence(Archive *fout, const TableInfo *tbinfo);
static void dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo);
static void dumpIndex(Archive *fout, const IndxInfo *indxinfo);
@@ -992,6 +1009,9 @@ main(int argc, char **argv)
if (dopt.binary_upgrade)
collectBinaryUpgradeClassOids(fout);
+ /* Collect sequence information. */
+ collectSequences(fout);
+
/* Lastly, create dummy objects to represent the section boundaries */
boundaryObjs = createBoundaryObjects();
@@ -17189,6 +17209,71 @@ dumpTableConstraintComment(Archive *fout, const ConstraintInfo *coninfo)
free(qtabname);
}
+/*
+ * bsearch() comparator for SequenceItem
+ */
+static int
+SequenceItemCmp(const void *p1, const void *p2)
+{
+ SequenceItem v1 = *((const SequenceItem *) p1);
+ SequenceItem v2 = *((const SequenceItem *) p2);
+
+ return pg_cmp_u32(v1.oid, v2.oid);
+}
+
+/*
+ * collectSequences
+ *
+ * Construct a table of sequence information. This table is sorted by OID for
+ * speed in lookup.
+ */
+static void
+collectSequences(Archive *fout)
+{
+ PGresult *res;
+ const char *query;
+
+ /*
+ * Before Postgres 10, sequence metadata is in the sequence itself. We
+ * could likely make use of the sorted table with some extra effort, but
+ * for now it seems unlikely to be worth it.
+ */
+ if (fout->remoteVersion < 100000)
+ return;
+
+ query = "SELECT seqrelid, format_type(seqtypid, NULL), "
+ "seqstart, seqincrement, "
+ "seqmax, seqmin, "
+ "seqcache, seqcycle "
+ "FROM pg_catalog.pg_sequence "
+ "ORDER BY seqrelid";
+
+ res = ExecuteSqlQuery(fout, query, PGRES_TUPLES_OK);
+
+ nsequences = PQntuples(res);
+ sequences = (SequenceItem *) pg_malloc(nsequences * sizeof(SequenceItem));
+
+ for (int i = 0; i < nsequences; i++)
+ {
+ size_t seqtype_sz = sizeof(((SequenceItem *) 0)->seqtype);
+
+ sequences[i].oid = atooid(PQgetvalue(res, i, 0));
+
+ Assert(strlen(PQgetvalue(res, i, 1)) < seqtype_sz);
+ strncpy(sequences[i].seqtype, PQgetvalue(res, i, 1), seqtype_sz);
+ sequences[i].seqtype[seqtype_sz - 1] = '\0';
+
+ sequences[i].startv = strtoi64(PQgetvalue(res, i, 2), NULL, 10);
+ sequences[i].incby = strtoi64(PQgetvalue(res, i, 3), NULL, 10);
+ sequences[i].maxv = strtoi64(PQgetvalue(res, i, 4), NULL, 10);
+ sequences[i].minv = strtoi64(PQgetvalue(res, i, 5), NULL, 10);
+ sequences[i].cache = strtoi64(PQgetvalue(res, i, 6), NULL, 10);
+ sequences[i].cycled = (strcmp(PQgetvalue(res, i, 7), "t") == 0);
+ }
+
+ PQclear(res);
+}
+
/*
* dumpSequence
* write the declaration (not data) of one user-defined sequence
@@ -17197,7 +17282,6 @@ static void
dumpSequence(Archive *fout, const TableInfo *tbinfo)
{
DumpOptions *dopt = fout->dopt;
- PGresult *res;
char seqtype[10];
bool cycled;
bool is_ascending;
@@ -17215,19 +17299,34 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
qseqname = pg_strdup(fmtId(tbinfo->dobj.name));
+ /*
+ * For versions >= 10, the sequence information is gathered in a sorted
+ * table before any calls to dumpSequence(). See collectSequences() for
+ * more information.
+ */
if (fout->remoteVersion >= 100000)
{
- appendPQExpBuffer(query,
- "SELECT format_type(seqtypid, NULL), "
- "seqstart, seqincrement, "
- "seqmax, seqmin, "
- "seqcache, seqcycle "
- "FROM pg_catalog.pg_sequence "
- "WHERE seqrelid = '%u'::oid",
- tbinfo->dobj.catId.oid);
+ SequenceItem key = {0};
+ SequenceItem *entry;
+
+ Assert(sequences);
+
+ key.oid = tbinfo->dobj.catId.oid;
+ entry = bsearch(&key, sequences, nsequences,
+ sizeof(SequenceItem), SequenceItemCmp);
+
+ strncpy(seqtype, entry->seqtype, sizeof(seqtype));
+ startv = entry->startv;
+ incby = entry->incby;
+ maxv = entry->maxv;
+ minv = entry->minv;
+ cache = entry->cache;
+ cycled = entry->cycled;
}
else
{
+ PGresult *res;
+
/*
* Before PostgreSQL 10, sequence metadata is in the sequence itself.
*
@@ -17239,28 +17338,28 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
"start_value, increment_by, max_value, min_value, "
"cache_value, is_cycled FROM %s",
fmtQualifiedDumpable(tbinfo));
- }
-
- res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
- if (PQntuples(res) != 1)
- pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
- "query to get data of sequence \"%s\" returned %d rows (expected 1)",
- PQntuples(res)),
- tbinfo->dobj.name, PQntuples(res));
-
- Assert(strlen(PQgetvalue(res, 0, 0)) < sizeof(seqtype));
- strncpy(seqtype, PQgetvalue(res, 0, 0), sizeof(seqtype));
- seqtype[sizeof(seqtype) - 1] = '\0';
+ res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
- startv = strtoi64(PQgetvalue(res, 0, 1), NULL, 10);
- incby = strtoi64(PQgetvalue(res, 0, 2), NULL, 10);
- maxv = strtoi64(PQgetvalue(res, 0, 3), NULL, 10);
- minv = strtoi64(PQgetvalue(res, 0, 4), NULL, 10);
- cache = strtoi64(PQgetvalue(res, 0, 5), NULL, 10);
- cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
+ if (PQntuples(res) != 1)
+ pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
+ "query to get data of sequence \"%s\" returned %d rows (expected 1)",
+ PQntuples(res)),
+ tbinfo->dobj.name, PQntuples(res));
+
+ Assert(strlen(PQgetvalue(res, 0, 0)) < sizeof(seqtype));
+ strncpy(seqtype, PQgetvalue(res, 0, 0), sizeof(seqtype));
+ seqtype[sizeof(seqtype) - 1] = '\0';
+
+ startv = strtoi64(PQgetvalue(res, 0, 1), NULL, 10);
+ incby = strtoi64(PQgetvalue(res, 0, 2), NULL, 10);
+ maxv = strtoi64(PQgetvalue(res, 0, 3), NULL, 10);
+ minv = strtoi64(PQgetvalue(res, 0, 4), NULL, 10);
+ cache = strtoi64(PQgetvalue(res, 0, 5), NULL, 10);
+ cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
- PQclear(res);
+ PQclear(res);
+ }
/* Calculate default limits for a sequence of this type */
is_ascending = (incby >= 0);
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index b4d7f9217c..a09adcfb90 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2572,6 +2572,7 @@ SeqScan
SeqScanState
SeqTable
SeqTableData
+SequenceItem
SerCommitSeqNo
SerialControl
SerialIOData
--
2.39.3 (Apple Git-146)
[text/plain] v4-0003-introduce-pg_sequence_read_tuple.patch (4.4K, ../../ZpiP3I6UVcgsCCWf@nathan/4-v4-0003-introduce-pg_sequence_read_tuple.patch)
download | inline diff:
From 86f0b7c26be5bd257a5039bdfbe127983e1228c7 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Wed, 17 Jul 2024 17:09:03 -0500
Subject: [PATCH v4 3/4] introduce pg_sequence_read_tuple
XXX: NEEDS CATVERSION BUMP
---
src/backend/commands/sequence.c | 62 ++++++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 6 +++
src/test/regress/expected/sequence.out | 7 +++
src/test/regress/sql/sequence.sql | 3 ++
4 files changed, 78 insertions(+)
diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c
index 9f28d40466..45c4cb3936 100644
--- a/src/backend/commands/sequence.c
+++ b/src/backend/commands/sequence.c
@@ -1773,6 +1773,68 @@ pg_sequence_parameters(PG_FUNCTION_ARGS)
return HeapTupleGetDatum(heap_form_tuple(tupdesc, values, isnull));
}
+
+/*
+ * Return the sequence tuple.
+ *
+ * This is primarily intended for use by pg_dump to gather sequence data
+ * without needing to individually query each sequence relation.
+ */
+Datum
+pg_sequence_read_tuple(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+ SeqTable elm;
+ Relation seqrel;
+ Datum values[SEQ_COL_LASTCOL];
+ bool isnull[SEQ_COL_LASTCOL];
+ TupleDesc resultTupleDesc;
+ HeapTuple resultHeapTuple;
+ Datum result;
+
+ resultTupleDesc = CreateTemplateTupleDesc(SEQ_COL_LASTCOL);
+ TupleDescInitEntry(resultTupleDesc, (AttrNumber) 1, "last_value",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(resultTupleDesc, (AttrNumber) 2, "log_cnt",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(resultTupleDesc, (AttrNumber) 3, "is_called",
+ BOOLOID, -1, 0);
+ resultTupleDesc = BlessTupleDesc(resultTupleDesc);
+
+ init_sequence(relid, &elm, &seqrel);
+
+ /*
+ * Return all NULLs for sequences for which we lack privileges, other
+ * sessions' temporary sequences, and unlogged sequences on standbys.
+ */
+ if (pg_class_aclcheck(relid, GetUserId(), ACL_SELECT | ACL_USAGE) == ACLCHECK_OK &&
+ !RELATION_IS_OTHER_TEMP(seqrel) &&
+ (RelationIsPermanent(seqrel) || !RecoveryInProgress()))
+ {
+ Buffer buf;
+ HeapTupleData seqtuple;
+ Form_pg_sequence_data seq;
+
+ seq = read_seq_tuple(seqrel, &buf, &seqtuple);
+
+ memset(isnull, false, sizeof(isnull));
+ values[0] = Int64GetDatum(seq->last_value);
+ values[1] = Int64GetDatum(seq->log_cnt);
+ values[2] = BoolGetDatum(seq->is_called);
+
+ UnlockReleaseBuffer(buf);
+ }
+ else
+ memset(isnull, true, sizeof(isnull));
+
+ sequence_close(seqrel, NoLock);
+
+ resultHeapTuple = heap_form_tuple(resultTupleDesc, values, isnull);
+ result = HeapTupleGetDatum(resultHeapTuple);
+ PG_RETURN_DATUM(result);
+}
+
+
/*
* Return the last value from the sequence
*
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 73d9cf8582..a16aec302e 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3329,6 +3329,12 @@
proname => 'pg_sequence_last_value', provolatile => 'v', proparallel => 'u',
prorettype => 'int8', proargtypes => 'regclass',
prosrc => 'pg_sequence_last_value' },
+{ oid => '9876', descr => 'return sequence tuple, for use by pg_dump',
+ proname => 'pg_sequence_read_tuple', provolatile => 'v', proparallel => 'u',
+ prorettype => 'record', proargtypes => 'regclass',
+ proallargtypes => '{regclass,int8,int8,bool}', proargmodes => '{i,o,o,o}',
+ proargnames => '{sequence_oid,last_value,log_cnt,is_called}',
+ prosrc => 'pg_sequence_read_tuple' },
{ oid => '275', descr => 'return the next oid for a system table',
proname => 'pg_nextoid', provolatile => 'v', proparallel => 'u',
diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out
index 2b47b7796b..e749c4574e 100644
--- a/src/test/regress/expected/sequence.out
+++ b/src/test/regress/expected/sequence.out
@@ -839,4 +839,11 @@ SELECT nextval('test_seq1');
3
(1 row)
+-- pg_sequence_read_tuple
+SELECT * FROM pg_sequence_read_tuple('test_seq1');
+ last_value | log_cnt | is_called
+------------+---------+-----------
+ 10 | 32 | t
+(1 row)
+
DROP SEQUENCE test_seq1;
diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sql
index 674f5f1f66..ea447938ae 100644
--- a/src/test/regress/sql/sequence.sql
+++ b/src/test/regress/sql/sequence.sql
@@ -413,4 +413,7 @@ SELECT nextval('test_seq1');
SELECT nextval('test_seq1');
SELECT nextval('test_seq1');
+-- pg_sequence_read_tuple
+SELECT * FROM pg_sequence_read_tuple('test_seq1');
+
DROP SEQUENCE test_seq1;
--
2.39.3 (Apple Git-146)
[text/plain] v4-0004-cache-sequence-data.patch (5.0K, ../../ZpiP3I6UVcgsCCWf@nathan/5-v4-0004-cache-sequence-data.patch)
download | inline diff:
From d22c7281416280480b91cff02330a858946364ad Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Wed, 17 Jul 2024 22:13:08 -0500
Subject: [PATCH v4 4/4] cache sequence data
---
src/bin/pg_dump/pg_dump.c | 91 +++++++++++++++++++++++++++++----------
1 file changed, 68 insertions(+), 23 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index a54e32c7be..14019907db 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -114,6 +114,8 @@ typedef struct
int64 startv; /* start value */
int64 incby; /* increment value */
int64 cache; /* cache size */
+ int64 last_value; /* last value of sequence */
+ bool is_called; /* whether nextval advances before returning */
} SequenceItem;
typedef enum OidOptions
@@ -17237,16 +17239,30 @@ collectSequences(Archive *fout)
* Before Postgres 10, sequence metadata is in the sequence itself. We
* could likely make use of the sorted table with some extra effort, but
* for now it seems unlikely to be worth it.
+ *
+ * Since version 18, we can gather the sequence data in this query with
+ * pg_sequence_read_tuple(), but we only do so for non-schema-only dumps.
*/
if (fout->remoteVersion < 100000)
return;
-
- query = "SELECT seqrelid, format_type(seqtypid, NULL), "
- "seqstart, seqincrement, "
- "seqmax, seqmin, "
- "seqcache, seqcycle "
- "FROM pg_catalog.pg_sequence "
- "ORDER BY seqrelid";
+ else if (fout->remoteVersion < 180000 ||
+ (fout->dopt->schemaOnly && !fout->dopt->sequence_data))
+ query = "SELECT seqrelid, format_type(seqtypid, NULL), "
+ "seqstart, seqincrement, "
+ "seqmax, seqmin, "
+ "seqcache, seqcycle, "
+ "NULL, 'f' "
+ "FROM pg_catalog.pg_sequence "
+ "ORDER BY seqrelid";
+ else
+ query = "SELECT seqrelid, format_type(seqtypid, NULL), "
+ "seqstart, seqincrement, "
+ "seqmax, seqmin, "
+ "seqcache, seqcycle, "
+ "last_value, is_called "
+ "FROM pg_catalog.pg_sequence, "
+ "pg_sequence_read_tuple(seqrelid) "
+ "ORDER BY seqrelid;";
res = ExecuteSqlQuery(fout, query, PGRES_TUPLES_OK);
@@ -17269,6 +17285,8 @@ collectSequences(Archive *fout)
sequences[i].minv = strtoi64(PQgetvalue(res, i, 5), NULL, 10);
sequences[i].cache = strtoi64(PQgetvalue(res, i, 6), NULL, 10);
sequences[i].cycled = (strcmp(PQgetvalue(res, i, 7), "t") == 0);
+ sequences[i].last_value = strtoi64(PQgetvalue(res, i, 8), NULL, 10);
+ sequences[i].is_called = (strcmp(PQgetvalue(res, i, 9), "t") == 0);
}
PQclear(res);
@@ -17550,30 +17568,59 @@ static void
dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo)
{
TableInfo *tbinfo = tdinfo->tdtable;
- PGresult *res;
- char *last;
+ int64 last;
bool called;
PQExpBuffer query = createPQExpBuffer();
- appendPQExpBuffer(query,
- "SELECT last_value, is_called FROM %s",
- fmtQualifiedDumpable(tbinfo));
+ /*
+ * For versions >= 18, the sequence information is gathered in the sorted
+ * array before any calls to dumpSequenceData(). See collectSequences()
+ * for more information.
+ *
+ * For older versions, we have to query the sequence relations
+ * individually.
+ */
+ if (fout->remoteVersion < 180000)
+ {
+ PGresult *res;
- res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
+ appendPQExpBuffer(query,
+ "SELECT last_value, is_called FROM %s",
+ fmtQualifiedDumpable(tbinfo));
- if (PQntuples(res) != 1)
- pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
- "query to get data of sequence \"%s\" returned %d rows (expected 1)",
- PQntuples(res)),
- tbinfo->dobj.name, PQntuples(res));
+ res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
- last = PQgetvalue(res, 0, 0);
- called = (strcmp(PQgetvalue(res, 0, 1), "t") == 0);
+ if (PQntuples(res) != 1)
+ pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
+ "query to get data of sequence \"%s\" returned %d rows (expected 1)",
+ PQntuples(res)),
+ tbinfo->dobj.name, PQntuples(res));
+
+ last = strtoi64(PQgetvalue(res, 0, 0), NULL, 10);
+ called = (strcmp(PQgetvalue(res, 0, 1), "t") == 0);
+
+ PQclear(res);
+ }
+ else
+ {
+ SequenceItem key = {0};
+ SequenceItem *entry;
+
+ Assert(sequences);
+ Assert(tbinfo->dobj.catId.oid);
+
+ key.oid = tbinfo->dobj.catId.oid;
+ entry = bsearch(&key, sequences, nsequences,
+ sizeof(SequenceItem), SequenceItemCmp);
+
+ last = entry->last_value;
+ called = entry->is_called;
+ }
resetPQExpBuffer(query);
appendPQExpBufferStr(query, "SELECT pg_catalog.setval(");
appendStringLiteralAH(query, fmtQualifiedDumpable(tbinfo), fout);
- appendPQExpBuffer(query, ", %s, %s);\n",
+ appendPQExpBuffer(query, ", " INT64_FORMAT ", %s);\n",
last, (called ? "true" : "false"));
if (tdinfo->dobj.dump & DUMP_COMPONENT_DATA)
@@ -17587,8 +17634,6 @@ dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo)
.deps = &(tbinfo->dobj.dumpId),
.nDeps = 1));
- PQclear(res);
-
destroyPQExpBuffer(query);
}
--
2.39.3 (Apple Git-146)
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: improve performance of pg_dump with many sequences
@ 2024-07-18 03:58 Tom Lane <[email protected]>
parent: Nathan Bossart <[email protected]>
0 siblings, 1 reply; 21+ messages in thread
From: Tom Lane @ 2024-07-18 03:58 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: Michael Paquier <[email protected]>; Euler Taveira <[email protected]>; pgsql-hackers
Nathan Bossart <[email protected]> writes:
> Here is an attempt at adding a new function that returns the sequence tuple
> and using that to avoid querying each sequence relation individually in
> dumpSequenceData().
Didn't read the patch yet, but ...
> If we instead wanted to change pg_sequence_last_value() to return both
> is_called and last_value, I think we could modify the pg_sequences system
> view to use a LATERAL subquery, i.e.,
> ...
> That doesn't seem so bad, and it'd avoid an extra pg_proc entry, but it
> would probably break anything that calls pg_sequence_last_value() directly.
> Thoughts?
... one more pg_proc entry is pretty cheap. I think we should leave
pg_sequence_last_value alone. We don't know if anyone is depending
on it, and de-optimizing the pg_sequences view doesn't seem like a
win either.
... okay, I lied, I looked at the patch. Why are you testing
+ if (pg_class_aclcheck(relid, GetUserId(), ACL_SELECT | ACL_USAGE) == ACLCHECK_OK &&
? This is a substitute for a SELECT from the sequence and it seems
like it ought to demand exactly the same privilege as SELECT.
(If you want to get more technical, USAGE allows nextval() which
gives strictly less information than what this exposes; that's why
we're here after all.) So there is a difference in the privilege
levels, which is another reason for not combining this with
pg_sequence_last_value.
regards, tom lane
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: improve performance of pg_dump with many sequences
@ 2024-07-18 18:22 Nathan Bossart <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 1 reply; 21+ messages in thread
From: Nathan Bossart @ 2024-07-18 18:22 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Michael Paquier <[email protected]>; Euler Taveira <[email protected]>; pgsql-hackers
On Wed, Jul 17, 2024 at 11:58:21PM -0400, Tom Lane wrote:
> ... okay, I lied, I looked at the patch. Why are you testing
>
> + if (pg_class_aclcheck(relid, GetUserId(), ACL_SELECT | ACL_USAGE) == ACLCHECK_OK &&
>
> ? This is a substitute for a SELECT from the sequence and it seems
> like it ought to demand exactly the same privilege as SELECT.
> (If you want to get more technical, USAGE allows nextval() which
> gives strictly less information than what this exposes; that's why
> we're here after all.) So there is a difference in the privilege
> levels, which is another reason for not combining this with
> pg_sequence_last_value.
Oh, that's a good point. I wrongly assumed the privilege checks would be
the same as pg_sequence_last_value(). I fixed this in v5.
I also polished the rest of the patches a bit. Among other things, I
created an enum for the sequence data types to avoid the hacky strncpy()
stuff, which was causing weird CI failures [0].
[0] https://cirrus-ci.com/task/4614801962303488
--
nathan
From e98ff5c9a5e8962445de0d5ab68ce50ed40c121b Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Mon, 15 Jul 2024 13:13:05 -0500
Subject: [PATCH v5 1/4] parse sequence information
---
src/bin/pg_dump/pg_dump.c | 102 ++++++++++++++++++-------------
src/tools/pgindent/typedefs.list | 1 +
2 files changed, 59 insertions(+), 44 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index b8b1888bd3..afafd641fe 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -104,6 +104,23 @@ typedef struct
RelFileNumber toast_index_relfilenumber; /* toast table index filenode */
} BinaryUpgradeClassOidItem;
+typedef enum SeqType
+{
+ SEQTYPE_SMALLINT,
+ SEQTYPE_INTEGER,
+ SEQTYPE_BIGINT,
+} SeqType;
+
+const char *const SeqTypeNames[] =
+{
+ [SEQTYPE_SMALLINT] = "smallint",
+ [SEQTYPE_INTEGER] = "integer",
+ [SEQTYPE_BIGINT] = "bigint",
+};
+
+StaticAssertDecl(lengthof(SeqTypeNames) == (SEQTYPE_BIGINT + 1),
+ "array length mismatch");
+
typedef enum OidOptions
{
zeroIsError = 1,
@@ -17189,6 +17206,18 @@ dumpTableConstraintComment(Archive *fout, const ConstraintInfo *coninfo)
free(qtabname);
}
+static inline SeqType
+parse_sequence_type(const char *name)
+{
+ for (int i = 0; i < lengthof(SeqTypeNames); i++)
+ {
+ if (strcmp(SeqTypeNames[i], name) == 0)
+ return (SeqType) i;
+ }
+
+ pg_fatal("unrecognized sequence type: %s", name);
+}
+
/*
* dumpSequence
* write the declaration (not data) of one user-defined sequence
@@ -17198,18 +17227,16 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
{
DumpOptions *dopt = fout->dopt;
PGresult *res;
- char *startv,
- *incby,
- *maxv,
- *minv,
- *cache,
- *seqtype;
+ SeqType seqtype;
bool cycled;
bool is_ascending;
int64 default_minv,
- default_maxv;
- char bufm[32],
- bufx[32];
+ default_maxv,
+ minv,
+ maxv,
+ startv,
+ incby,
+ cache;
PQExpBuffer query = createPQExpBuffer();
PQExpBuffer delqry = createPQExpBuffer();
char *qseqname;
@@ -17251,50 +17278,39 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
PQntuples(res)),
tbinfo->dobj.name, PQntuples(res));
- seqtype = PQgetvalue(res, 0, 0);
- startv = PQgetvalue(res, 0, 1);
- incby = PQgetvalue(res, 0, 2);
- maxv = PQgetvalue(res, 0, 3);
- minv = PQgetvalue(res, 0, 4);
- cache = PQgetvalue(res, 0, 5);
+ seqtype = parse_sequence_type(PQgetvalue(res, 0, 0));
+ startv = strtoi64(PQgetvalue(res, 0, 1), NULL, 10);
+ incby = strtoi64(PQgetvalue(res, 0, 2), NULL, 10);
+ maxv = strtoi64(PQgetvalue(res, 0, 3), NULL, 10);
+ minv = strtoi64(PQgetvalue(res, 0, 4), NULL, 10);
+ cache = strtoi64(PQgetvalue(res, 0, 5), NULL, 10);
cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
+ PQclear(res);
+
/* Calculate default limits for a sequence of this type */
- is_ascending = (incby[0] != '-');
- if (strcmp(seqtype, "smallint") == 0)
+ is_ascending = (incby >= 0);
+ if (seqtype == SEQTYPE_SMALLINT)
{
default_minv = is_ascending ? 1 : PG_INT16_MIN;
default_maxv = is_ascending ? PG_INT16_MAX : -1;
}
- else if (strcmp(seqtype, "integer") == 0)
+ else if (seqtype == SEQTYPE_INTEGER)
{
default_minv = is_ascending ? 1 : PG_INT32_MIN;
default_maxv = is_ascending ? PG_INT32_MAX : -1;
}
- else if (strcmp(seqtype, "bigint") == 0)
+ else if (seqtype == SEQTYPE_BIGINT)
{
default_minv = is_ascending ? 1 : PG_INT64_MIN;
default_maxv = is_ascending ? PG_INT64_MAX : -1;
}
else
{
- pg_fatal("unrecognized sequence type: %s", seqtype);
+ pg_fatal("unrecognized sequence type: %d", seqtype);
default_minv = default_maxv = 0; /* keep compiler quiet */
}
- /*
- * 64-bit strtol() isn't very portable, so convert the limits to strings
- * and compare that way.
- */
- snprintf(bufm, sizeof(bufm), INT64_FORMAT, default_minv);
- snprintf(bufx, sizeof(bufx), INT64_FORMAT, default_maxv);
-
- /* Don't print minv/maxv if they match the respective default limit */
- if (strcmp(minv, bufm) == 0)
- minv = NULL;
- if (strcmp(maxv, bufx) == 0)
- maxv = NULL;
-
/*
* Identity sequences are not to be dropped separately.
*/
@@ -17342,26 +17358,26 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
"UNLOGGED " : "",
fmtQualifiedDumpable(tbinfo));
- if (strcmp(seqtype, "bigint") != 0)
- appendPQExpBuffer(query, " AS %s\n", seqtype);
+ if (seqtype != SEQTYPE_BIGINT)
+ appendPQExpBuffer(query, " AS %s\n", SeqTypeNames[seqtype]);
}
- appendPQExpBuffer(query, " START WITH %s\n", startv);
+ appendPQExpBuffer(query, " START WITH " INT64_FORMAT "\n", startv);
- appendPQExpBuffer(query, " INCREMENT BY %s\n", incby);
+ appendPQExpBuffer(query, " INCREMENT BY " INT64_FORMAT "\n", incby);
- if (minv)
- appendPQExpBuffer(query, " MINVALUE %s\n", minv);
+ if (minv != default_minv)
+ appendPQExpBuffer(query, " MINVALUE " INT64_FORMAT "\n", minv);
else
appendPQExpBufferStr(query, " NO MINVALUE\n");
- if (maxv)
- appendPQExpBuffer(query, " MAXVALUE %s\n", maxv);
+ if (maxv != default_maxv)
+ appendPQExpBuffer(query, " MAXVALUE " INT64_FORMAT "\n", maxv);
else
appendPQExpBufferStr(query, " NO MAXVALUE\n");
appendPQExpBuffer(query,
- " CACHE %s%s",
+ " CACHE " INT64_FORMAT "%s",
cache, (cycled ? "\n CYCLE" : ""));
if (tbinfo->is_identity_sequence)
@@ -17448,8 +17464,6 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
tbinfo->dobj.namespace->dobj.name, tbinfo->rolname,
tbinfo->dobj.catId, 0, tbinfo->dobj.dumpId);
- PQclear(res);
-
destroyPQExpBuffer(query);
destroyPQExpBuffer(delqry);
free(qseqname);
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index b4d7f9217c..974e21155a 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2572,6 +2572,7 @@ SeqScan
SeqScanState
SeqTable
SeqTableData
+SeqType
SerCommitSeqNo
SerialControl
SerialIOData
--
2.39.3 (Apple Git-146)
From 6f0ce8705acf916163f1272bd9e546e683faaa11 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Tue, 9 Jul 2024 14:06:23 -0500
Subject: [PATCH v5 2/4] cache sequence information
---
src/bin/pg_dump/pg_dump.c | 175 ++++++++++++++++++++++---------
src/tools/pgindent/typedefs.list | 1 +
2 files changed, 129 insertions(+), 47 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index afafd641fe..9d1707623b 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -121,6 +121,18 @@ const char *const SeqTypeNames[] =
StaticAssertDecl(lengthof(SeqTypeNames) == (SEQTYPE_BIGINT + 1),
"array length mismatch");
+typedef struct
+{
+ Oid oid; /* sequence OID */
+ SeqType seqtype; /* data type of sequence */
+ bool cycled; /* whether sequence cycles */
+ int64 minv; /* minimum value */
+ int64 maxv; /* maximum value */
+ int64 startv; /* start value */
+ int64 incby; /* increment value */
+ int64 cache; /* cache size */
+} SequenceItem;
+
typedef enum OidOptions
{
zeroIsError = 1,
@@ -190,6 +202,10 @@ static int nseclabels = 0;
static BinaryUpgradeClassOidItem *binaryUpgradeClassOids = NULL;
static int nbinaryUpgradeClassOids = 0;
+/* sorted table of sequences */
+static SequenceItem *sequences = NULL;
+static int nsequences = 0;
+
/*
* The default number of rows per INSERT when
* --inserts is specified without --rows-per-insert
@@ -287,6 +303,7 @@ static void dumpTable(Archive *fout, const TableInfo *tbinfo);
static void dumpTableSchema(Archive *fout, const TableInfo *tbinfo);
static void dumpTableAttach(Archive *fout, const TableAttachInfo *attachinfo);
static void dumpAttrDef(Archive *fout, const AttrDefInfo *adinfo);
+static void collectSequences(Archive *fout);
static void dumpSequence(Archive *fout, const TableInfo *tbinfo);
static void dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo);
static void dumpIndex(Archive *fout, const IndxInfo *indxinfo);
@@ -1009,6 +1026,9 @@ main(int argc, char **argv)
if (dopt.binary_upgrade)
collectBinaryUpgradeClassOids(fout);
+ /* Collect sequence information. */
+ collectSequences(fout);
+
/* Lastly, create dummy objects to represent the section boundaries */
boundaryObjs = createBoundaryObjects();
@@ -17218,6 +17238,65 @@ parse_sequence_type(const char *name)
pg_fatal("unrecognized sequence type: %s", name);
}
+/*
+ * bsearch() comparator for SequenceItem
+ */
+static int
+SequenceItemCmp(const void *p1, const void *p2)
+{
+ SequenceItem v1 = *((const SequenceItem *) p1);
+ SequenceItem v2 = *((const SequenceItem *) p2);
+
+ return pg_cmp_u32(v1.oid, v2.oid);
+}
+
+/*
+ * collectSequences
+ *
+ * Construct a table of sequence information. This table is sorted by OID for
+ * speed in lookup.
+ */
+static void
+collectSequences(Archive *fout)
+{
+ PGresult *res;
+ const char *query;
+
+ /*
+ * Before Postgres 10, sequence metadata is in the sequence itself. We
+ * could likely make use of the sorted table with some extra effort, but
+ * for now it seems unlikely to be worth it.
+ */
+ if (fout->remoteVersion < 100000)
+ return;
+ else
+ query = "SELECT seqrelid, format_type(seqtypid, NULL), "
+ "seqstart, seqincrement, "
+ "seqmax, seqmin, "
+ "seqcache, seqcycle "
+ "FROM pg_catalog.pg_sequence "
+ "ORDER BY seqrelid";
+
+ res = ExecuteSqlQuery(fout, query, PGRES_TUPLES_OK);
+
+ nsequences = PQntuples(res);
+ sequences = (SequenceItem *) pg_malloc(nsequences * sizeof(SequenceItem));
+
+ for (int i = 0; i < nsequences; i++)
+ {
+ sequences[i].oid = atooid(PQgetvalue(res, i, 0));
+ sequences[i].seqtype = parse_sequence_type(PQgetvalue(res, i, 1));
+ sequences[i].startv = strtoi64(PQgetvalue(res, i, 2), NULL, 10);
+ sequences[i].incby = strtoi64(PQgetvalue(res, i, 3), NULL, 10);
+ sequences[i].maxv = strtoi64(PQgetvalue(res, i, 4), NULL, 10);
+ sequences[i].minv = strtoi64(PQgetvalue(res, i, 5), NULL, 10);
+ sequences[i].cache = strtoi64(PQgetvalue(res, i, 6), NULL, 10);
+ sequences[i].cycled = (strcmp(PQgetvalue(res, i, 7), "t") == 0);
+ }
+
+ PQclear(res);
+}
+
/*
* dumpSequence
* write the declaration (not data) of one user-defined sequence
@@ -17226,17 +17305,10 @@ static void
dumpSequence(Archive *fout, const TableInfo *tbinfo)
{
DumpOptions *dopt = fout->dopt;
- PGresult *res;
- SeqType seqtype;
- bool cycled;
+ SequenceItem *seq;
bool is_ascending;
int64 default_minv,
- default_maxv,
- minv,
- maxv,
- startv,
- incby,
- cache;
+ default_maxv;
PQExpBuffer query = createPQExpBuffer();
PQExpBuffer delqry = createPQExpBuffer();
char *qseqname;
@@ -17244,19 +17316,25 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
qseqname = pg_strdup(fmtId(tbinfo->dobj.name));
+ /*
+ * For versions >= 10, the sequence information is gathered in a sorted
+ * table before any calls to dumpSequence(). See collectSequences() for
+ * more information.
+ */
if (fout->remoteVersion >= 100000)
{
- appendPQExpBuffer(query,
- "SELECT format_type(seqtypid, NULL), "
- "seqstart, seqincrement, "
- "seqmax, seqmin, "
- "seqcache, seqcycle "
- "FROM pg_catalog.pg_sequence "
- "WHERE seqrelid = '%u'::oid",
- tbinfo->dobj.catId.oid);
+ SequenceItem key = {0};
+
+ Assert(sequences);
+
+ key.oid = tbinfo->dobj.catId.oid;
+ seq = bsearch(&key, sequences, nsequences,
+ sizeof(SequenceItem), SequenceItemCmp);
}
else
{
+ PGresult *res;
+
/*
* Before PostgreSQL 10, sequence metadata is in the sequence itself.
*
@@ -17268,46 +17346,47 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
"start_value, increment_by, max_value, min_value, "
"cache_value, is_cycled FROM %s",
fmtQualifiedDumpable(tbinfo));
- }
-
- res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
- if (PQntuples(res) != 1)
- pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
- "query to get data of sequence \"%s\" returned %d rows (expected 1)",
- PQntuples(res)),
- tbinfo->dobj.name, PQntuples(res));
+ res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
- seqtype = parse_sequence_type(PQgetvalue(res, 0, 0));
- startv = strtoi64(PQgetvalue(res, 0, 1), NULL, 10);
- incby = strtoi64(PQgetvalue(res, 0, 2), NULL, 10);
- maxv = strtoi64(PQgetvalue(res, 0, 3), NULL, 10);
- minv = strtoi64(PQgetvalue(res, 0, 4), NULL, 10);
- cache = strtoi64(PQgetvalue(res, 0, 5), NULL, 10);
- cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
+ if (PQntuples(res) != 1)
+ pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
+ "query to get data of sequence \"%s\" returned %d rows (expected 1)",
+ PQntuples(res)),
+ tbinfo->dobj.name, PQntuples(res));
+
+ seq = pg_malloc0(sizeof(SequenceItem));
+ seq->seqtype = parse_sequence_type(PQgetvalue(res, 0, 0));
+ seq->startv = strtoi64(PQgetvalue(res, 0, 1), NULL, 10);
+ seq->incby = strtoi64(PQgetvalue(res, 0, 2), NULL, 10);
+ seq->maxv = strtoi64(PQgetvalue(res, 0, 3), NULL, 10);
+ seq->minv = strtoi64(PQgetvalue(res, 0, 4), NULL, 10);
+ seq->cache = strtoi64(PQgetvalue(res, 0, 5), NULL, 10);
+ seq->cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
- PQclear(res);
+ PQclear(res);
+ }
/* Calculate default limits for a sequence of this type */
- is_ascending = (incby >= 0);
- if (seqtype == SEQTYPE_SMALLINT)
+ is_ascending = (seq->incby >= 0);
+ if (seq->seqtype == SEQTYPE_SMALLINT)
{
default_minv = is_ascending ? 1 : PG_INT16_MIN;
default_maxv = is_ascending ? PG_INT16_MAX : -1;
}
- else if (seqtype == SEQTYPE_INTEGER)
+ else if (seq->seqtype == SEQTYPE_INTEGER)
{
default_minv = is_ascending ? 1 : PG_INT32_MIN;
default_maxv = is_ascending ? PG_INT32_MAX : -1;
}
- else if (seqtype == SEQTYPE_BIGINT)
+ else if (seq->seqtype == SEQTYPE_BIGINT)
{
default_minv = is_ascending ? 1 : PG_INT64_MIN;
default_maxv = is_ascending ? PG_INT64_MAX : -1;
}
else
{
- pg_fatal("unrecognized sequence type: %d", seqtype);
+ pg_fatal("unrecognized sequence type: %d", seq->seqtype);
default_minv = default_maxv = 0; /* keep compiler quiet */
}
@@ -17358,27 +17437,27 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
"UNLOGGED " : "",
fmtQualifiedDumpable(tbinfo));
- if (seqtype != SEQTYPE_BIGINT)
- appendPQExpBuffer(query, " AS %s\n", SeqTypeNames[seqtype]);
+ if (seq->seqtype != SEQTYPE_BIGINT)
+ appendPQExpBuffer(query, " AS %s\n", SeqTypeNames[seq->seqtype]);
}
- appendPQExpBuffer(query, " START WITH " INT64_FORMAT "\n", startv);
+ appendPQExpBuffer(query, " START WITH " INT64_FORMAT "\n", seq->startv);
- appendPQExpBuffer(query, " INCREMENT BY " INT64_FORMAT "\n", incby);
+ appendPQExpBuffer(query, " INCREMENT BY " INT64_FORMAT "\n", seq->incby);
- if (minv != default_minv)
- appendPQExpBuffer(query, " MINVALUE " INT64_FORMAT "\n", minv);
+ if (seq->minv != default_minv)
+ appendPQExpBuffer(query, " MINVALUE " INT64_FORMAT "\n", seq->minv);
else
appendPQExpBufferStr(query, " NO MINVALUE\n");
- if (maxv != default_maxv)
- appendPQExpBuffer(query, " MAXVALUE " INT64_FORMAT "\n", maxv);
+ if (seq->maxv != default_maxv)
+ appendPQExpBuffer(query, " MAXVALUE " INT64_FORMAT "\n", seq->maxv);
else
appendPQExpBufferStr(query, " NO MAXVALUE\n");
appendPQExpBuffer(query,
" CACHE " INT64_FORMAT "%s",
- cache, (cycled ? "\n CYCLE" : ""));
+ seq->cache, (seq->cycled ? "\n CYCLE" : ""));
if (tbinfo->is_identity_sequence)
{
@@ -17464,6 +17543,8 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
tbinfo->dobj.namespace->dobj.name, tbinfo->rolname,
tbinfo->dobj.catId, 0, tbinfo->dobj.dumpId);
+ if (fout->remoteVersion < 100000)
+ pg_free(seq);
destroyPQExpBuffer(query);
destroyPQExpBuffer(delqry);
free(qseqname);
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 974e21155a..e1a46b5bf5 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2573,6 +2573,7 @@ SeqScanState
SeqTable
SeqTableData
SeqType
+SequenceItem
SerCommitSeqNo
SerialControl
SerialIOData
--
2.39.3 (Apple Git-146)
From c74861298d44ab37f6b8bf1dd864614b8131dc20 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Wed, 17 Jul 2024 17:09:03 -0500
Subject: [PATCH v5 3/4] introduce pg_sequence_read_tuple
XXX: NEEDS CATVERSION BUMP
---
src/backend/commands/sequence.c | 62 ++++++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 6 +++
src/test/regress/expected/sequence.out | 7 +++
src/test/regress/sql/sequence.sql | 3 ++
4 files changed, 78 insertions(+)
diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c
index 9f28d40466..364097c457 100644
--- a/src/backend/commands/sequence.c
+++ b/src/backend/commands/sequence.c
@@ -1773,6 +1773,68 @@ pg_sequence_parameters(PG_FUNCTION_ARGS)
return HeapTupleGetDatum(heap_form_tuple(tupdesc, values, isnull));
}
+
+/*
+ * Return the sequence tuple.
+ *
+ * This is primarily intended for use by pg_dump to gather sequence data
+ * without needing to individually query each sequence relation.
+ */
+Datum
+pg_sequence_read_tuple(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+ SeqTable elm;
+ Relation seqrel;
+ Datum values[SEQ_COL_LASTCOL];
+ bool isnull[SEQ_COL_LASTCOL];
+ TupleDesc resultTupleDesc;
+ HeapTuple resultHeapTuple;
+ Datum result;
+
+ resultTupleDesc = CreateTemplateTupleDesc(SEQ_COL_LASTCOL);
+ TupleDescInitEntry(resultTupleDesc, (AttrNumber) 1, "last_value",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(resultTupleDesc, (AttrNumber) 2, "log_cnt",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(resultTupleDesc, (AttrNumber) 3, "is_called",
+ BOOLOID, -1, 0);
+ resultTupleDesc = BlessTupleDesc(resultTupleDesc);
+
+ init_sequence(relid, &elm, &seqrel);
+
+ /*
+ * Return all NULLs for sequences for which we lack privileges, other
+ * sessions' temporary sequences, and unlogged sequences on standbys.
+ */
+ if (pg_class_aclcheck(relid, GetUserId(), ACL_SELECT) == ACLCHECK_OK &&
+ !RELATION_IS_OTHER_TEMP(seqrel) &&
+ (RelationIsPermanent(seqrel) || !RecoveryInProgress()))
+ {
+ Buffer buf;
+ HeapTupleData seqtuple;
+ Form_pg_sequence_data seq;
+
+ seq = read_seq_tuple(seqrel, &buf, &seqtuple);
+
+ memset(isnull, false, sizeof(isnull));
+ values[0] = Int64GetDatum(seq->last_value);
+ values[1] = Int64GetDatum(seq->log_cnt);
+ values[2] = BoolGetDatum(seq->is_called);
+
+ UnlockReleaseBuffer(buf);
+ }
+ else
+ memset(isnull, true, sizeof(isnull));
+
+ sequence_close(seqrel, NoLock);
+
+ resultHeapTuple = heap_form_tuple(resultTupleDesc, values, isnull);
+ result = HeapTupleGetDatum(resultHeapTuple);
+ PG_RETURN_DATUM(result);
+}
+
+
/*
* Return the last value from the sequence
*
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 73d9cf8582..a16aec302e 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3329,6 +3329,12 @@
proname => 'pg_sequence_last_value', provolatile => 'v', proparallel => 'u',
prorettype => 'int8', proargtypes => 'regclass',
prosrc => 'pg_sequence_last_value' },
+{ oid => '9876', descr => 'return sequence tuple, for use by pg_dump',
+ proname => 'pg_sequence_read_tuple', provolatile => 'v', proparallel => 'u',
+ prorettype => 'record', proargtypes => 'regclass',
+ proallargtypes => '{regclass,int8,int8,bool}', proargmodes => '{i,o,o,o}',
+ proargnames => '{sequence_oid,last_value,log_cnt,is_called}',
+ prosrc => 'pg_sequence_read_tuple' },
{ oid => '275', descr => 'return the next oid for a system table',
proname => 'pg_nextoid', provolatile => 'v', proparallel => 'u',
diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out
index 2b47b7796b..e749c4574e 100644
--- a/src/test/regress/expected/sequence.out
+++ b/src/test/regress/expected/sequence.out
@@ -839,4 +839,11 @@ SELECT nextval('test_seq1');
3
(1 row)
+-- pg_sequence_read_tuple
+SELECT * FROM pg_sequence_read_tuple('test_seq1');
+ last_value | log_cnt | is_called
+------------+---------+-----------
+ 10 | 32 | t
+(1 row)
+
DROP SEQUENCE test_seq1;
diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sql
index 674f5f1f66..ea447938ae 100644
--- a/src/test/regress/sql/sequence.sql
+++ b/src/test/regress/sql/sequence.sql
@@ -413,4 +413,7 @@ SELECT nextval('test_seq1');
SELECT nextval('test_seq1');
SELECT nextval('test_seq1');
+-- pg_sequence_read_tuple
+SELECT * FROM pg_sequence_read_tuple('test_seq1');
+
DROP SEQUENCE test_seq1;
--
2.39.3 (Apple Git-146)
From 66c8711d0a14db4e418bbf136437568f6b48ce40 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Wed, 17 Jul 2024 22:13:08 -0500
Subject: [PATCH v5 4/4] cache sequence data
---
src/bin/pg_dump/pg_dump.c | 81 ++++++++++++++++++++++++++++++---------
1 file changed, 63 insertions(+), 18 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 9d1707623b..2c5c614abd 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -131,6 +131,8 @@ typedef struct
int64 startv; /* start value */
int64 incby; /* increment value */
int64 cache; /* cache size */
+ int64 last_value; /* last value of sequence */
+ bool is_called; /* whether nextval advances before returning */
} SequenceItem;
typedef enum OidOptions
@@ -17266,16 +17268,30 @@ collectSequences(Archive *fout)
* Before Postgres 10, sequence metadata is in the sequence itself. We
* could likely make use of the sorted table with some extra effort, but
* for now it seems unlikely to be worth it.
+ *
+ * Since version 18, we can gather the sequence data in this query with
+ * pg_sequence_read_tuple(), but we only do so for non-schema-only dumps.
*/
if (fout->remoteVersion < 100000)
return;
- else
+ else if (fout->remoteVersion < 180000 ||
+ (fout->dopt->schemaOnly && !fout->dopt->sequence_data))
query = "SELECT seqrelid, format_type(seqtypid, NULL), "
"seqstart, seqincrement, "
"seqmax, seqmin, "
- "seqcache, seqcycle "
+ "seqcache, seqcycle, "
+ "NULL, 'f' "
"FROM pg_catalog.pg_sequence "
"ORDER BY seqrelid";
+ else
+ query = "SELECT seqrelid, format_type(seqtypid, NULL), "
+ "seqstart, seqincrement, "
+ "seqmax, seqmin, "
+ "seqcache, seqcycle, "
+ "last_value, is_called "
+ "FROM pg_catalog.pg_sequence, "
+ "pg_sequence_read_tuple(seqrelid) "
+ "ORDER BY seqrelid;";
res = ExecuteSqlQuery(fout, query, PGRES_TUPLES_OK);
@@ -17292,6 +17308,8 @@ collectSequences(Archive *fout)
sequences[i].minv = strtoi64(PQgetvalue(res, i, 5), NULL, 10);
sequences[i].cache = strtoi64(PQgetvalue(res, i, 6), NULL, 10);
sequences[i].cycled = (strcmp(PQgetvalue(res, i, 7), "t") == 0);
+ sequences[i].last_value = strtoi64(PQgetvalue(res, i, 8), NULL, 10);
+ sequences[i].is_called = (strcmp(PQgetvalue(res, i, 9), "t") == 0);
}
PQclear(res);
@@ -17558,30 +17576,59 @@ static void
dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo)
{
TableInfo *tbinfo = tdinfo->tdtable;
- PGresult *res;
- char *last;
+ int64 last;
bool called;
PQExpBuffer query = createPQExpBuffer();
- appendPQExpBuffer(query,
- "SELECT last_value, is_called FROM %s",
- fmtQualifiedDumpable(tbinfo));
+ /*
+ * For versions >= 18, the sequence information is gathered in the sorted
+ * array before any calls to dumpSequenceData(). See collectSequences()
+ * for more information.
+ *
+ * For older versions, we have to query the sequence relations
+ * individually.
+ */
+ if (fout->remoteVersion < 180000)
+ {
+ PGresult *res;
- res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
+ appendPQExpBuffer(query,
+ "SELECT last_value, is_called FROM %s",
+ fmtQualifiedDumpable(tbinfo));
- if (PQntuples(res) != 1)
- pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
- "query to get data of sequence \"%s\" returned %d rows (expected 1)",
- PQntuples(res)),
- tbinfo->dobj.name, PQntuples(res));
+ res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
- last = PQgetvalue(res, 0, 0);
- called = (strcmp(PQgetvalue(res, 0, 1), "t") == 0);
+ if (PQntuples(res) != 1)
+ pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
+ "query to get data of sequence \"%s\" returned %d rows (expected 1)",
+ PQntuples(res)),
+ tbinfo->dobj.name, PQntuples(res));
+
+ last = strtoi64(PQgetvalue(res, 0, 0), NULL, 10);
+ called = (strcmp(PQgetvalue(res, 0, 1), "t") == 0);
+
+ PQclear(res);
+ }
+ else
+ {
+ SequenceItem key = {0};
+ SequenceItem *entry;
+
+ Assert(sequences);
+ Assert(tbinfo->dobj.catId.oid);
+
+ key.oid = tbinfo->dobj.catId.oid;
+ entry = bsearch(&key, sequences, nsequences,
+ sizeof(SequenceItem), SequenceItemCmp);
+
+ last = entry->last_value;
+ called = entry->is_called;
+ }
resetPQExpBuffer(query);
appendPQExpBufferStr(query, "SELECT pg_catalog.setval(");
appendStringLiteralAH(query, fmtQualifiedDumpable(tbinfo), fout);
- appendPQExpBuffer(query, ", %s, %s);\n",
+ appendPQExpBuffer(query, ", " INT64_FORMAT ", %s);\n",
last, (called ? "true" : "false"));
if (tdinfo->dobj.dump & DUMP_COMPONENT_DATA)
@@ -17595,8 +17642,6 @@ dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo)
.deps = &(tbinfo->dobj.dumpId),
.nDeps = 1));
- PQclear(res);
-
destroyPQExpBuffer(query);
}
--
2.39.3 (Apple Git-146)
Attachments:
[text/plain] v5-0001-parse-sequence-information.patch (5.9K, ../../ZpldUoP0XjHszExF@nathan/2-v5-0001-parse-sequence-information.patch)
download | inline diff:
From e98ff5c9a5e8962445de0d5ab68ce50ed40c121b Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Mon, 15 Jul 2024 13:13:05 -0500
Subject: [PATCH v5 1/4] parse sequence information
---
src/bin/pg_dump/pg_dump.c | 102 ++++++++++++++++++-------------
src/tools/pgindent/typedefs.list | 1 +
2 files changed, 59 insertions(+), 44 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index b8b1888bd3..afafd641fe 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -104,6 +104,23 @@ typedef struct
RelFileNumber toast_index_relfilenumber; /* toast table index filenode */
} BinaryUpgradeClassOidItem;
+typedef enum SeqType
+{
+ SEQTYPE_SMALLINT,
+ SEQTYPE_INTEGER,
+ SEQTYPE_BIGINT,
+} SeqType;
+
+const char *const SeqTypeNames[] =
+{
+ [SEQTYPE_SMALLINT] = "smallint",
+ [SEQTYPE_INTEGER] = "integer",
+ [SEQTYPE_BIGINT] = "bigint",
+};
+
+StaticAssertDecl(lengthof(SeqTypeNames) == (SEQTYPE_BIGINT + 1),
+ "array length mismatch");
+
typedef enum OidOptions
{
zeroIsError = 1,
@@ -17189,6 +17206,18 @@ dumpTableConstraintComment(Archive *fout, const ConstraintInfo *coninfo)
free(qtabname);
}
+static inline SeqType
+parse_sequence_type(const char *name)
+{
+ for (int i = 0; i < lengthof(SeqTypeNames); i++)
+ {
+ if (strcmp(SeqTypeNames[i], name) == 0)
+ return (SeqType) i;
+ }
+
+ pg_fatal("unrecognized sequence type: %s", name);
+}
+
/*
* dumpSequence
* write the declaration (not data) of one user-defined sequence
@@ -17198,18 +17227,16 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
{
DumpOptions *dopt = fout->dopt;
PGresult *res;
- char *startv,
- *incby,
- *maxv,
- *minv,
- *cache,
- *seqtype;
+ SeqType seqtype;
bool cycled;
bool is_ascending;
int64 default_minv,
- default_maxv;
- char bufm[32],
- bufx[32];
+ default_maxv,
+ minv,
+ maxv,
+ startv,
+ incby,
+ cache;
PQExpBuffer query = createPQExpBuffer();
PQExpBuffer delqry = createPQExpBuffer();
char *qseqname;
@@ -17251,50 +17278,39 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
PQntuples(res)),
tbinfo->dobj.name, PQntuples(res));
- seqtype = PQgetvalue(res, 0, 0);
- startv = PQgetvalue(res, 0, 1);
- incby = PQgetvalue(res, 0, 2);
- maxv = PQgetvalue(res, 0, 3);
- minv = PQgetvalue(res, 0, 4);
- cache = PQgetvalue(res, 0, 5);
+ seqtype = parse_sequence_type(PQgetvalue(res, 0, 0));
+ startv = strtoi64(PQgetvalue(res, 0, 1), NULL, 10);
+ incby = strtoi64(PQgetvalue(res, 0, 2), NULL, 10);
+ maxv = strtoi64(PQgetvalue(res, 0, 3), NULL, 10);
+ minv = strtoi64(PQgetvalue(res, 0, 4), NULL, 10);
+ cache = strtoi64(PQgetvalue(res, 0, 5), NULL, 10);
cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
+ PQclear(res);
+
/* Calculate default limits for a sequence of this type */
- is_ascending = (incby[0] != '-');
- if (strcmp(seqtype, "smallint") == 0)
+ is_ascending = (incby >= 0);
+ if (seqtype == SEQTYPE_SMALLINT)
{
default_minv = is_ascending ? 1 : PG_INT16_MIN;
default_maxv = is_ascending ? PG_INT16_MAX : -1;
}
- else if (strcmp(seqtype, "integer") == 0)
+ else if (seqtype == SEQTYPE_INTEGER)
{
default_minv = is_ascending ? 1 : PG_INT32_MIN;
default_maxv = is_ascending ? PG_INT32_MAX : -1;
}
- else if (strcmp(seqtype, "bigint") == 0)
+ else if (seqtype == SEQTYPE_BIGINT)
{
default_minv = is_ascending ? 1 : PG_INT64_MIN;
default_maxv = is_ascending ? PG_INT64_MAX : -1;
}
else
{
- pg_fatal("unrecognized sequence type: %s", seqtype);
+ pg_fatal("unrecognized sequence type: %d", seqtype);
default_minv = default_maxv = 0; /* keep compiler quiet */
}
- /*
- * 64-bit strtol() isn't very portable, so convert the limits to strings
- * and compare that way.
- */
- snprintf(bufm, sizeof(bufm), INT64_FORMAT, default_minv);
- snprintf(bufx, sizeof(bufx), INT64_FORMAT, default_maxv);
-
- /* Don't print minv/maxv if they match the respective default limit */
- if (strcmp(minv, bufm) == 0)
- minv = NULL;
- if (strcmp(maxv, bufx) == 0)
- maxv = NULL;
-
/*
* Identity sequences are not to be dropped separately.
*/
@@ -17342,26 +17358,26 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
"UNLOGGED " : "",
fmtQualifiedDumpable(tbinfo));
- if (strcmp(seqtype, "bigint") != 0)
- appendPQExpBuffer(query, " AS %s\n", seqtype);
+ if (seqtype != SEQTYPE_BIGINT)
+ appendPQExpBuffer(query, " AS %s\n", SeqTypeNames[seqtype]);
}
- appendPQExpBuffer(query, " START WITH %s\n", startv);
+ appendPQExpBuffer(query, " START WITH " INT64_FORMAT "\n", startv);
- appendPQExpBuffer(query, " INCREMENT BY %s\n", incby);
+ appendPQExpBuffer(query, " INCREMENT BY " INT64_FORMAT "\n", incby);
- if (minv)
- appendPQExpBuffer(query, " MINVALUE %s\n", minv);
+ if (minv != default_minv)
+ appendPQExpBuffer(query, " MINVALUE " INT64_FORMAT "\n", minv);
else
appendPQExpBufferStr(query, " NO MINVALUE\n");
- if (maxv)
- appendPQExpBuffer(query, " MAXVALUE %s\n", maxv);
+ if (maxv != default_maxv)
+ appendPQExpBuffer(query, " MAXVALUE " INT64_FORMAT "\n", maxv);
else
appendPQExpBufferStr(query, " NO MAXVALUE\n");
appendPQExpBuffer(query,
- " CACHE %s%s",
+ " CACHE " INT64_FORMAT "%s",
cache, (cycled ? "\n CYCLE" : ""));
if (tbinfo->is_identity_sequence)
@@ -17448,8 +17464,6 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
tbinfo->dobj.namespace->dobj.name, tbinfo->rolname,
tbinfo->dobj.catId, 0, tbinfo->dobj.dumpId);
- PQclear(res);
-
destroyPQExpBuffer(query);
destroyPQExpBuffer(delqry);
free(qseqname);
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index b4d7f9217c..974e21155a 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2572,6 +2572,7 @@ SeqScan
SeqScanState
SeqTable
SeqTableData
+SeqType
SerCommitSeqNo
SerialControl
SerialIOData
--
2.39.3 (Apple Git-146)
[text/plain] v5-0002-cache-sequence-information.patch (10.2K, ../../ZpldUoP0XjHszExF@nathan/3-v5-0002-cache-sequence-information.patch)
download | inline diff:
From 6f0ce8705acf916163f1272bd9e546e683faaa11 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Tue, 9 Jul 2024 14:06:23 -0500
Subject: [PATCH v5 2/4] cache sequence information
---
src/bin/pg_dump/pg_dump.c | 175 ++++++++++++++++++++++---------
src/tools/pgindent/typedefs.list | 1 +
2 files changed, 129 insertions(+), 47 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index afafd641fe..9d1707623b 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -121,6 +121,18 @@ const char *const SeqTypeNames[] =
StaticAssertDecl(lengthof(SeqTypeNames) == (SEQTYPE_BIGINT + 1),
"array length mismatch");
+typedef struct
+{
+ Oid oid; /* sequence OID */
+ SeqType seqtype; /* data type of sequence */
+ bool cycled; /* whether sequence cycles */
+ int64 minv; /* minimum value */
+ int64 maxv; /* maximum value */
+ int64 startv; /* start value */
+ int64 incby; /* increment value */
+ int64 cache; /* cache size */
+} SequenceItem;
+
typedef enum OidOptions
{
zeroIsError = 1,
@@ -190,6 +202,10 @@ static int nseclabels = 0;
static BinaryUpgradeClassOidItem *binaryUpgradeClassOids = NULL;
static int nbinaryUpgradeClassOids = 0;
+/* sorted table of sequences */
+static SequenceItem *sequences = NULL;
+static int nsequences = 0;
+
/*
* The default number of rows per INSERT when
* --inserts is specified without --rows-per-insert
@@ -287,6 +303,7 @@ static void dumpTable(Archive *fout, const TableInfo *tbinfo);
static void dumpTableSchema(Archive *fout, const TableInfo *tbinfo);
static void dumpTableAttach(Archive *fout, const TableAttachInfo *attachinfo);
static void dumpAttrDef(Archive *fout, const AttrDefInfo *adinfo);
+static void collectSequences(Archive *fout);
static void dumpSequence(Archive *fout, const TableInfo *tbinfo);
static void dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo);
static void dumpIndex(Archive *fout, const IndxInfo *indxinfo);
@@ -1009,6 +1026,9 @@ main(int argc, char **argv)
if (dopt.binary_upgrade)
collectBinaryUpgradeClassOids(fout);
+ /* Collect sequence information. */
+ collectSequences(fout);
+
/* Lastly, create dummy objects to represent the section boundaries */
boundaryObjs = createBoundaryObjects();
@@ -17218,6 +17238,65 @@ parse_sequence_type(const char *name)
pg_fatal("unrecognized sequence type: %s", name);
}
+/*
+ * bsearch() comparator for SequenceItem
+ */
+static int
+SequenceItemCmp(const void *p1, const void *p2)
+{
+ SequenceItem v1 = *((const SequenceItem *) p1);
+ SequenceItem v2 = *((const SequenceItem *) p2);
+
+ return pg_cmp_u32(v1.oid, v2.oid);
+}
+
+/*
+ * collectSequences
+ *
+ * Construct a table of sequence information. This table is sorted by OID for
+ * speed in lookup.
+ */
+static void
+collectSequences(Archive *fout)
+{
+ PGresult *res;
+ const char *query;
+
+ /*
+ * Before Postgres 10, sequence metadata is in the sequence itself. We
+ * could likely make use of the sorted table with some extra effort, but
+ * for now it seems unlikely to be worth it.
+ */
+ if (fout->remoteVersion < 100000)
+ return;
+ else
+ query = "SELECT seqrelid, format_type(seqtypid, NULL), "
+ "seqstart, seqincrement, "
+ "seqmax, seqmin, "
+ "seqcache, seqcycle "
+ "FROM pg_catalog.pg_sequence "
+ "ORDER BY seqrelid";
+
+ res = ExecuteSqlQuery(fout, query, PGRES_TUPLES_OK);
+
+ nsequences = PQntuples(res);
+ sequences = (SequenceItem *) pg_malloc(nsequences * sizeof(SequenceItem));
+
+ for (int i = 0; i < nsequences; i++)
+ {
+ sequences[i].oid = atooid(PQgetvalue(res, i, 0));
+ sequences[i].seqtype = parse_sequence_type(PQgetvalue(res, i, 1));
+ sequences[i].startv = strtoi64(PQgetvalue(res, i, 2), NULL, 10);
+ sequences[i].incby = strtoi64(PQgetvalue(res, i, 3), NULL, 10);
+ sequences[i].maxv = strtoi64(PQgetvalue(res, i, 4), NULL, 10);
+ sequences[i].minv = strtoi64(PQgetvalue(res, i, 5), NULL, 10);
+ sequences[i].cache = strtoi64(PQgetvalue(res, i, 6), NULL, 10);
+ sequences[i].cycled = (strcmp(PQgetvalue(res, i, 7), "t") == 0);
+ }
+
+ PQclear(res);
+}
+
/*
* dumpSequence
* write the declaration (not data) of one user-defined sequence
@@ -17226,17 +17305,10 @@ static void
dumpSequence(Archive *fout, const TableInfo *tbinfo)
{
DumpOptions *dopt = fout->dopt;
- PGresult *res;
- SeqType seqtype;
- bool cycled;
+ SequenceItem *seq;
bool is_ascending;
int64 default_minv,
- default_maxv,
- minv,
- maxv,
- startv,
- incby,
- cache;
+ default_maxv;
PQExpBuffer query = createPQExpBuffer();
PQExpBuffer delqry = createPQExpBuffer();
char *qseqname;
@@ -17244,19 +17316,25 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
qseqname = pg_strdup(fmtId(tbinfo->dobj.name));
+ /*
+ * For versions >= 10, the sequence information is gathered in a sorted
+ * table before any calls to dumpSequence(). See collectSequences() for
+ * more information.
+ */
if (fout->remoteVersion >= 100000)
{
- appendPQExpBuffer(query,
- "SELECT format_type(seqtypid, NULL), "
- "seqstart, seqincrement, "
- "seqmax, seqmin, "
- "seqcache, seqcycle "
- "FROM pg_catalog.pg_sequence "
- "WHERE seqrelid = '%u'::oid",
- tbinfo->dobj.catId.oid);
+ SequenceItem key = {0};
+
+ Assert(sequences);
+
+ key.oid = tbinfo->dobj.catId.oid;
+ seq = bsearch(&key, sequences, nsequences,
+ sizeof(SequenceItem), SequenceItemCmp);
}
else
{
+ PGresult *res;
+
/*
* Before PostgreSQL 10, sequence metadata is in the sequence itself.
*
@@ -17268,46 +17346,47 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
"start_value, increment_by, max_value, min_value, "
"cache_value, is_cycled FROM %s",
fmtQualifiedDumpable(tbinfo));
- }
-
- res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
- if (PQntuples(res) != 1)
- pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
- "query to get data of sequence \"%s\" returned %d rows (expected 1)",
- PQntuples(res)),
- tbinfo->dobj.name, PQntuples(res));
+ res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
- seqtype = parse_sequence_type(PQgetvalue(res, 0, 0));
- startv = strtoi64(PQgetvalue(res, 0, 1), NULL, 10);
- incby = strtoi64(PQgetvalue(res, 0, 2), NULL, 10);
- maxv = strtoi64(PQgetvalue(res, 0, 3), NULL, 10);
- minv = strtoi64(PQgetvalue(res, 0, 4), NULL, 10);
- cache = strtoi64(PQgetvalue(res, 0, 5), NULL, 10);
- cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
+ if (PQntuples(res) != 1)
+ pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
+ "query to get data of sequence \"%s\" returned %d rows (expected 1)",
+ PQntuples(res)),
+ tbinfo->dobj.name, PQntuples(res));
+
+ seq = pg_malloc0(sizeof(SequenceItem));
+ seq->seqtype = parse_sequence_type(PQgetvalue(res, 0, 0));
+ seq->startv = strtoi64(PQgetvalue(res, 0, 1), NULL, 10);
+ seq->incby = strtoi64(PQgetvalue(res, 0, 2), NULL, 10);
+ seq->maxv = strtoi64(PQgetvalue(res, 0, 3), NULL, 10);
+ seq->minv = strtoi64(PQgetvalue(res, 0, 4), NULL, 10);
+ seq->cache = strtoi64(PQgetvalue(res, 0, 5), NULL, 10);
+ seq->cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
- PQclear(res);
+ PQclear(res);
+ }
/* Calculate default limits for a sequence of this type */
- is_ascending = (incby >= 0);
- if (seqtype == SEQTYPE_SMALLINT)
+ is_ascending = (seq->incby >= 0);
+ if (seq->seqtype == SEQTYPE_SMALLINT)
{
default_minv = is_ascending ? 1 : PG_INT16_MIN;
default_maxv = is_ascending ? PG_INT16_MAX : -1;
}
- else if (seqtype == SEQTYPE_INTEGER)
+ else if (seq->seqtype == SEQTYPE_INTEGER)
{
default_minv = is_ascending ? 1 : PG_INT32_MIN;
default_maxv = is_ascending ? PG_INT32_MAX : -1;
}
- else if (seqtype == SEQTYPE_BIGINT)
+ else if (seq->seqtype == SEQTYPE_BIGINT)
{
default_minv = is_ascending ? 1 : PG_INT64_MIN;
default_maxv = is_ascending ? PG_INT64_MAX : -1;
}
else
{
- pg_fatal("unrecognized sequence type: %d", seqtype);
+ pg_fatal("unrecognized sequence type: %d", seq->seqtype);
default_minv = default_maxv = 0; /* keep compiler quiet */
}
@@ -17358,27 +17437,27 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
"UNLOGGED " : "",
fmtQualifiedDumpable(tbinfo));
- if (seqtype != SEQTYPE_BIGINT)
- appendPQExpBuffer(query, " AS %s\n", SeqTypeNames[seqtype]);
+ if (seq->seqtype != SEQTYPE_BIGINT)
+ appendPQExpBuffer(query, " AS %s\n", SeqTypeNames[seq->seqtype]);
}
- appendPQExpBuffer(query, " START WITH " INT64_FORMAT "\n", startv);
+ appendPQExpBuffer(query, " START WITH " INT64_FORMAT "\n", seq->startv);
- appendPQExpBuffer(query, " INCREMENT BY " INT64_FORMAT "\n", incby);
+ appendPQExpBuffer(query, " INCREMENT BY " INT64_FORMAT "\n", seq->incby);
- if (minv != default_minv)
- appendPQExpBuffer(query, " MINVALUE " INT64_FORMAT "\n", minv);
+ if (seq->minv != default_minv)
+ appendPQExpBuffer(query, " MINVALUE " INT64_FORMAT "\n", seq->minv);
else
appendPQExpBufferStr(query, " NO MINVALUE\n");
- if (maxv != default_maxv)
- appendPQExpBuffer(query, " MAXVALUE " INT64_FORMAT "\n", maxv);
+ if (seq->maxv != default_maxv)
+ appendPQExpBuffer(query, " MAXVALUE " INT64_FORMAT "\n", seq->maxv);
else
appendPQExpBufferStr(query, " NO MAXVALUE\n");
appendPQExpBuffer(query,
" CACHE " INT64_FORMAT "%s",
- cache, (cycled ? "\n CYCLE" : ""));
+ seq->cache, (seq->cycled ? "\n CYCLE" : ""));
if (tbinfo->is_identity_sequence)
{
@@ -17464,6 +17543,8 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
tbinfo->dobj.namespace->dobj.name, tbinfo->rolname,
tbinfo->dobj.catId, 0, tbinfo->dobj.dumpId);
+ if (fout->remoteVersion < 100000)
+ pg_free(seq);
destroyPQExpBuffer(query);
destroyPQExpBuffer(delqry);
free(qseqname);
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 974e21155a..e1a46b5bf5 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2573,6 +2573,7 @@ SeqScanState
SeqTable
SeqTableData
SeqType
+SequenceItem
SerCommitSeqNo
SerialControl
SerialIOData
--
2.39.3 (Apple Git-146)
[text/plain] v5-0003-introduce-pg_sequence_read_tuple.patch (4.4K, ../../ZpldUoP0XjHszExF@nathan/4-v5-0003-introduce-pg_sequence_read_tuple.patch)
download | inline diff:
From c74861298d44ab37f6b8bf1dd864614b8131dc20 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Wed, 17 Jul 2024 17:09:03 -0500
Subject: [PATCH v5 3/4] introduce pg_sequence_read_tuple
XXX: NEEDS CATVERSION BUMP
---
src/backend/commands/sequence.c | 62 ++++++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 6 +++
src/test/regress/expected/sequence.out | 7 +++
src/test/regress/sql/sequence.sql | 3 ++
4 files changed, 78 insertions(+)
diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c
index 9f28d40466..364097c457 100644
--- a/src/backend/commands/sequence.c
+++ b/src/backend/commands/sequence.c
@@ -1773,6 +1773,68 @@ pg_sequence_parameters(PG_FUNCTION_ARGS)
return HeapTupleGetDatum(heap_form_tuple(tupdesc, values, isnull));
}
+
+/*
+ * Return the sequence tuple.
+ *
+ * This is primarily intended for use by pg_dump to gather sequence data
+ * without needing to individually query each sequence relation.
+ */
+Datum
+pg_sequence_read_tuple(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+ SeqTable elm;
+ Relation seqrel;
+ Datum values[SEQ_COL_LASTCOL];
+ bool isnull[SEQ_COL_LASTCOL];
+ TupleDesc resultTupleDesc;
+ HeapTuple resultHeapTuple;
+ Datum result;
+
+ resultTupleDesc = CreateTemplateTupleDesc(SEQ_COL_LASTCOL);
+ TupleDescInitEntry(resultTupleDesc, (AttrNumber) 1, "last_value",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(resultTupleDesc, (AttrNumber) 2, "log_cnt",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(resultTupleDesc, (AttrNumber) 3, "is_called",
+ BOOLOID, -1, 0);
+ resultTupleDesc = BlessTupleDesc(resultTupleDesc);
+
+ init_sequence(relid, &elm, &seqrel);
+
+ /*
+ * Return all NULLs for sequences for which we lack privileges, other
+ * sessions' temporary sequences, and unlogged sequences on standbys.
+ */
+ if (pg_class_aclcheck(relid, GetUserId(), ACL_SELECT) == ACLCHECK_OK &&
+ !RELATION_IS_OTHER_TEMP(seqrel) &&
+ (RelationIsPermanent(seqrel) || !RecoveryInProgress()))
+ {
+ Buffer buf;
+ HeapTupleData seqtuple;
+ Form_pg_sequence_data seq;
+
+ seq = read_seq_tuple(seqrel, &buf, &seqtuple);
+
+ memset(isnull, false, sizeof(isnull));
+ values[0] = Int64GetDatum(seq->last_value);
+ values[1] = Int64GetDatum(seq->log_cnt);
+ values[2] = BoolGetDatum(seq->is_called);
+
+ UnlockReleaseBuffer(buf);
+ }
+ else
+ memset(isnull, true, sizeof(isnull));
+
+ sequence_close(seqrel, NoLock);
+
+ resultHeapTuple = heap_form_tuple(resultTupleDesc, values, isnull);
+ result = HeapTupleGetDatum(resultHeapTuple);
+ PG_RETURN_DATUM(result);
+}
+
+
/*
* Return the last value from the sequence
*
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 73d9cf8582..a16aec302e 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3329,6 +3329,12 @@
proname => 'pg_sequence_last_value', provolatile => 'v', proparallel => 'u',
prorettype => 'int8', proargtypes => 'regclass',
prosrc => 'pg_sequence_last_value' },
+{ oid => '9876', descr => 'return sequence tuple, for use by pg_dump',
+ proname => 'pg_sequence_read_tuple', provolatile => 'v', proparallel => 'u',
+ prorettype => 'record', proargtypes => 'regclass',
+ proallargtypes => '{regclass,int8,int8,bool}', proargmodes => '{i,o,o,o}',
+ proargnames => '{sequence_oid,last_value,log_cnt,is_called}',
+ prosrc => 'pg_sequence_read_tuple' },
{ oid => '275', descr => 'return the next oid for a system table',
proname => 'pg_nextoid', provolatile => 'v', proparallel => 'u',
diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out
index 2b47b7796b..e749c4574e 100644
--- a/src/test/regress/expected/sequence.out
+++ b/src/test/regress/expected/sequence.out
@@ -839,4 +839,11 @@ SELECT nextval('test_seq1');
3
(1 row)
+-- pg_sequence_read_tuple
+SELECT * FROM pg_sequence_read_tuple('test_seq1');
+ last_value | log_cnt | is_called
+------------+---------+-----------
+ 10 | 32 | t
+(1 row)
+
DROP SEQUENCE test_seq1;
diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sql
index 674f5f1f66..ea447938ae 100644
--- a/src/test/regress/sql/sequence.sql
+++ b/src/test/regress/sql/sequence.sql
@@ -413,4 +413,7 @@ SELECT nextval('test_seq1');
SELECT nextval('test_seq1');
SELECT nextval('test_seq1');
+-- pg_sequence_read_tuple
+SELECT * FROM pg_sequence_read_tuple('test_seq1');
+
DROP SEQUENCE test_seq1;
--
2.39.3 (Apple Git-146)
[text/plain] v5-0004-cache-sequence-data.patch (4.9K, ../../ZpldUoP0XjHszExF@nathan/5-v5-0004-cache-sequence-data.patch)
download | inline diff:
From 66c8711d0a14db4e418bbf136437568f6b48ce40 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Wed, 17 Jul 2024 22:13:08 -0500
Subject: [PATCH v5 4/4] cache sequence data
---
src/bin/pg_dump/pg_dump.c | 81 ++++++++++++++++++++++++++++++---------
1 file changed, 63 insertions(+), 18 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 9d1707623b..2c5c614abd 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -131,6 +131,8 @@ typedef struct
int64 startv; /* start value */
int64 incby; /* increment value */
int64 cache; /* cache size */
+ int64 last_value; /* last value of sequence */
+ bool is_called; /* whether nextval advances before returning */
} SequenceItem;
typedef enum OidOptions
@@ -17266,16 +17268,30 @@ collectSequences(Archive *fout)
* Before Postgres 10, sequence metadata is in the sequence itself. We
* could likely make use of the sorted table with some extra effort, but
* for now it seems unlikely to be worth it.
+ *
+ * Since version 18, we can gather the sequence data in this query with
+ * pg_sequence_read_tuple(), but we only do so for non-schema-only dumps.
*/
if (fout->remoteVersion < 100000)
return;
- else
+ else if (fout->remoteVersion < 180000 ||
+ (fout->dopt->schemaOnly && !fout->dopt->sequence_data))
query = "SELECT seqrelid, format_type(seqtypid, NULL), "
"seqstart, seqincrement, "
"seqmax, seqmin, "
- "seqcache, seqcycle "
+ "seqcache, seqcycle, "
+ "NULL, 'f' "
"FROM pg_catalog.pg_sequence "
"ORDER BY seqrelid";
+ else
+ query = "SELECT seqrelid, format_type(seqtypid, NULL), "
+ "seqstart, seqincrement, "
+ "seqmax, seqmin, "
+ "seqcache, seqcycle, "
+ "last_value, is_called "
+ "FROM pg_catalog.pg_sequence, "
+ "pg_sequence_read_tuple(seqrelid) "
+ "ORDER BY seqrelid;";
res = ExecuteSqlQuery(fout, query, PGRES_TUPLES_OK);
@@ -17292,6 +17308,8 @@ collectSequences(Archive *fout)
sequences[i].minv = strtoi64(PQgetvalue(res, i, 5), NULL, 10);
sequences[i].cache = strtoi64(PQgetvalue(res, i, 6), NULL, 10);
sequences[i].cycled = (strcmp(PQgetvalue(res, i, 7), "t") == 0);
+ sequences[i].last_value = strtoi64(PQgetvalue(res, i, 8), NULL, 10);
+ sequences[i].is_called = (strcmp(PQgetvalue(res, i, 9), "t") == 0);
}
PQclear(res);
@@ -17558,30 +17576,59 @@ static void
dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo)
{
TableInfo *tbinfo = tdinfo->tdtable;
- PGresult *res;
- char *last;
+ int64 last;
bool called;
PQExpBuffer query = createPQExpBuffer();
- appendPQExpBuffer(query,
- "SELECT last_value, is_called FROM %s",
- fmtQualifiedDumpable(tbinfo));
+ /*
+ * For versions >= 18, the sequence information is gathered in the sorted
+ * array before any calls to dumpSequenceData(). See collectSequences()
+ * for more information.
+ *
+ * For older versions, we have to query the sequence relations
+ * individually.
+ */
+ if (fout->remoteVersion < 180000)
+ {
+ PGresult *res;
- res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
+ appendPQExpBuffer(query,
+ "SELECT last_value, is_called FROM %s",
+ fmtQualifiedDumpable(tbinfo));
- if (PQntuples(res) != 1)
- pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
- "query to get data of sequence \"%s\" returned %d rows (expected 1)",
- PQntuples(res)),
- tbinfo->dobj.name, PQntuples(res));
+ res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
- last = PQgetvalue(res, 0, 0);
- called = (strcmp(PQgetvalue(res, 0, 1), "t") == 0);
+ if (PQntuples(res) != 1)
+ pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
+ "query to get data of sequence \"%s\" returned %d rows (expected 1)",
+ PQntuples(res)),
+ tbinfo->dobj.name, PQntuples(res));
+
+ last = strtoi64(PQgetvalue(res, 0, 0), NULL, 10);
+ called = (strcmp(PQgetvalue(res, 0, 1), "t") == 0);
+
+ PQclear(res);
+ }
+ else
+ {
+ SequenceItem key = {0};
+ SequenceItem *entry;
+
+ Assert(sequences);
+ Assert(tbinfo->dobj.catId.oid);
+
+ key.oid = tbinfo->dobj.catId.oid;
+ entry = bsearch(&key, sequences, nsequences,
+ sizeof(SequenceItem), SequenceItemCmp);
+
+ last = entry->last_value;
+ called = entry->is_called;
+ }
resetPQExpBuffer(query);
appendPQExpBufferStr(query, "SELECT pg_catalog.setval(");
appendStringLiteralAH(query, fmtQualifiedDumpable(tbinfo), fout);
- appendPQExpBuffer(query, ", %s, %s);\n",
+ appendPQExpBuffer(query, ", " INT64_FORMAT ", %s);\n",
last, (called ? "true" : "false"));
if (tdinfo->dobj.dump & DUMP_COMPONENT_DATA)
@@ -17595,8 +17642,6 @@ dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo)
.deps = &(tbinfo->dobj.dumpId),
.nDeps = 1));
- PQclear(res);
-
destroyPQExpBuffer(query);
}
--
2.39.3 (Apple Git-146)
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: improve performance of pg_dump with many sequences
@ 2024-07-18 20:29 Nathan Bossart <[email protected]>
parent: Nathan Bossart <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Nathan Bossart @ 2024-07-18 20:29 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Michael Paquier <[email protected]>; Euler Taveira <[email protected]>; pgsql-hackers
I fixed a compiler warning on Windows in v6 of the patch set. Sorry for
the noise.
--
nathan
From 139826d3b9290d547ae9b3446ad455ba713211e0 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Mon, 15 Jul 2024 13:13:05 -0500
Subject: [PATCH v6 1/4] parse sequence information
---
src/bin/pg_dump/pg_dump.c | 103 ++++++++++++++++++-------------
src/tools/pgindent/typedefs.list | 1 +
2 files changed, 60 insertions(+), 44 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index b8b1888bd3..74e22e5e1e 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -104,6 +104,23 @@ typedef struct
RelFileNumber toast_index_relfilenumber; /* toast table index filenode */
} BinaryUpgradeClassOidItem;
+typedef enum SeqType
+{
+ SEQTYPE_SMALLINT,
+ SEQTYPE_INTEGER,
+ SEQTYPE_BIGINT,
+} SeqType;
+
+const char *const SeqTypeNames[] =
+{
+ [SEQTYPE_SMALLINT] = "smallint",
+ [SEQTYPE_INTEGER] = "integer",
+ [SEQTYPE_BIGINT] = "bigint",
+};
+
+StaticAssertDecl(lengthof(SeqTypeNames) == (SEQTYPE_BIGINT + 1),
+ "array length mismatch");
+
typedef enum OidOptions
{
zeroIsError = 1,
@@ -17189,6 +17206,19 @@ dumpTableConstraintComment(Archive *fout, const ConstraintInfo *coninfo)
free(qtabname);
}
+static inline SeqType
+parse_sequence_type(const char *name)
+{
+ for (int i = 0; i < lengthof(SeqTypeNames); i++)
+ {
+ if (strcmp(SeqTypeNames[i], name) == 0)
+ return (SeqType) i;
+ }
+
+ pg_fatal("unrecognized sequence type: %s", name);
+ return (SeqType) 0; /* keep compiler quiet */
+}
+
/*
* dumpSequence
* write the declaration (not data) of one user-defined sequence
@@ -17198,18 +17228,16 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
{
DumpOptions *dopt = fout->dopt;
PGresult *res;
- char *startv,
- *incby,
- *maxv,
- *minv,
- *cache,
- *seqtype;
+ SeqType seqtype;
bool cycled;
bool is_ascending;
int64 default_minv,
- default_maxv;
- char bufm[32],
- bufx[32];
+ default_maxv,
+ minv,
+ maxv,
+ startv,
+ incby,
+ cache;
PQExpBuffer query = createPQExpBuffer();
PQExpBuffer delqry = createPQExpBuffer();
char *qseqname;
@@ -17251,50 +17279,39 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
PQntuples(res)),
tbinfo->dobj.name, PQntuples(res));
- seqtype = PQgetvalue(res, 0, 0);
- startv = PQgetvalue(res, 0, 1);
- incby = PQgetvalue(res, 0, 2);
- maxv = PQgetvalue(res, 0, 3);
- minv = PQgetvalue(res, 0, 4);
- cache = PQgetvalue(res, 0, 5);
+ seqtype = parse_sequence_type(PQgetvalue(res, 0, 0));
+ startv = strtoi64(PQgetvalue(res, 0, 1), NULL, 10);
+ incby = strtoi64(PQgetvalue(res, 0, 2), NULL, 10);
+ maxv = strtoi64(PQgetvalue(res, 0, 3), NULL, 10);
+ minv = strtoi64(PQgetvalue(res, 0, 4), NULL, 10);
+ cache = strtoi64(PQgetvalue(res, 0, 5), NULL, 10);
cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
+ PQclear(res);
+
/* Calculate default limits for a sequence of this type */
- is_ascending = (incby[0] != '-');
- if (strcmp(seqtype, "smallint") == 0)
+ is_ascending = (incby >= 0);
+ if (seqtype == SEQTYPE_SMALLINT)
{
default_minv = is_ascending ? 1 : PG_INT16_MIN;
default_maxv = is_ascending ? PG_INT16_MAX : -1;
}
- else if (strcmp(seqtype, "integer") == 0)
+ else if (seqtype == SEQTYPE_INTEGER)
{
default_minv = is_ascending ? 1 : PG_INT32_MIN;
default_maxv = is_ascending ? PG_INT32_MAX : -1;
}
- else if (strcmp(seqtype, "bigint") == 0)
+ else if (seqtype == SEQTYPE_BIGINT)
{
default_minv = is_ascending ? 1 : PG_INT64_MIN;
default_maxv = is_ascending ? PG_INT64_MAX : -1;
}
else
{
- pg_fatal("unrecognized sequence type: %s", seqtype);
+ pg_fatal("unrecognized sequence type: %d", seqtype);
default_minv = default_maxv = 0; /* keep compiler quiet */
}
- /*
- * 64-bit strtol() isn't very portable, so convert the limits to strings
- * and compare that way.
- */
- snprintf(bufm, sizeof(bufm), INT64_FORMAT, default_minv);
- snprintf(bufx, sizeof(bufx), INT64_FORMAT, default_maxv);
-
- /* Don't print minv/maxv if they match the respective default limit */
- if (strcmp(minv, bufm) == 0)
- minv = NULL;
- if (strcmp(maxv, bufx) == 0)
- maxv = NULL;
-
/*
* Identity sequences are not to be dropped separately.
*/
@@ -17342,26 +17359,26 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
"UNLOGGED " : "",
fmtQualifiedDumpable(tbinfo));
- if (strcmp(seqtype, "bigint") != 0)
- appendPQExpBuffer(query, " AS %s\n", seqtype);
+ if (seqtype != SEQTYPE_BIGINT)
+ appendPQExpBuffer(query, " AS %s\n", SeqTypeNames[seqtype]);
}
- appendPQExpBuffer(query, " START WITH %s\n", startv);
+ appendPQExpBuffer(query, " START WITH " INT64_FORMAT "\n", startv);
- appendPQExpBuffer(query, " INCREMENT BY %s\n", incby);
+ appendPQExpBuffer(query, " INCREMENT BY " INT64_FORMAT "\n", incby);
- if (minv)
- appendPQExpBuffer(query, " MINVALUE %s\n", minv);
+ if (minv != default_minv)
+ appendPQExpBuffer(query, " MINVALUE " INT64_FORMAT "\n", minv);
else
appendPQExpBufferStr(query, " NO MINVALUE\n");
- if (maxv)
- appendPQExpBuffer(query, " MAXVALUE %s\n", maxv);
+ if (maxv != default_maxv)
+ appendPQExpBuffer(query, " MAXVALUE " INT64_FORMAT "\n", maxv);
else
appendPQExpBufferStr(query, " NO MAXVALUE\n");
appendPQExpBuffer(query,
- " CACHE %s%s",
+ " CACHE " INT64_FORMAT "%s",
cache, (cycled ? "\n CYCLE" : ""));
if (tbinfo->is_identity_sequence)
@@ -17448,8 +17465,6 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
tbinfo->dobj.namespace->dobj.name, tbinfo->rolname,
tbinfo->dobj.catId, 0, tbinfo->dobj.dumpId);
- PQclear(res);
-
destroyPQExpBuffer(query);
destroyPQExpBuffer(delqry);
free(qseqname);
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index b4d7f9217c..974e21155a 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2572,6 +2572,7 @@ SeqScan
SeqScanState
SeqTable
SeqTableData
+SeqType
SerCommitSeqNo
SerialControl
SerialIOData
--
2.39.3 (Apple Git-146)
From 7bf81a53af63c993478bb47fe347deefd1145bb4 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Tue, 9 Jul 2024 14:06:23 -0500
Subject: [PATCH v6 2/4] cache sequence information
---
src/bin/pg_dump/pg_dump.c | 175 ++++++++++++++++++++++---------
src/tools/pgindent/typedefs.list | 1 +
2 files changed, 129 insertions(+), 47 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 74e22e5e1e..8c42fd762d 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -121,6 +121,18 @@ const char *const SeqTypeNames[] =
StaticAssertDecl(lengthof(SeqTypeNames) == (SEQTYPE_BIGINT + 1),
"array length mismatch");
+typedef struct
+{
+ Oid oid; /* sequence OID */
+ SeqType seqtype; /* data type of sequence */
+ bool cycled; /* whether sequence cycles */
+ int64 minv; /* minimum value */
+ int64 maxv; /* maximum value */
+ int64 startv; /* start value */
+ int64 incby; /* increment value */
+ int64 cache; /* cache size */
+} SequenceItem;
+
typedef enum OidOptions
{
zeroIsError = 1,
@@ -190,6 +202,10 @@ static int nseclabels = 0;
static BinaryUpgradeClassOidItem *binaryUpgradeClassOids = NULL;
static int nbinaryUpgradeClassOids = 0;
+/* sorted table of sequences */
+static SequenceItem *sequences = NULL;
+static int nsequences = 0;
+
/*
* The default number of rows per INSERT when
* --inserts is specified without --rows-per-insert
@@ -287,6 +303,7 @@ static void dumpTable(Archive *fout, const TableInfo *tbinfo);
static void dumpTableSchema(Archive *fout, const TableInfo *tbinfo);
static void dumpTableAttach(Archive *fout, const TableAttachInfo *attachinfo);
static void dumpAttrDef(Archive *fout, const AttrDefInfo *adinfo);
+static void collectSequences(Archive *fout);
static void dumpSequence(Archive *fout, const TableInfo *tbinfo);
static void dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo);
static void dumpIndex(Archive *fout, const IndxInfo *indxinfo);
@@ -1009,6 +1026,9 @@ main(int argc, char **argv)
if (dopt.binary_upgrade)
collectBinaryUpgradeClassOids(fout);
+ /* Collect sequence information. */
+ collectSequences(fout);
+
/* Lastly, create dummy objects to represent the section boundaries */
boundaryObjs = createBoundaryObjects();
@@ -17219,6 +17239,65 @@ parse_sequence_type(const char *name)
return (SeqType) 0; /* keep compiler quiet */
}
+/*
+ * bsearch() comparator for SequenceItem
+ */
+static int
+SequenceItemCmp(const void *p1, const void *p2)
+{
+ SequenceItem v1 = *((const SequenceItem *) p1);
+ SequenceItem v2 = *((const SequenceItem *) p2);
+
+ return pg_cmp_u32(v1.oid, v2.oid);
+}
+
+/*
+ * collectSequences
+ *
+ * Construct a table of sequence information. This table is sorted by OID for
+ * speed in lookup.
+ */
+static void
+collectSequences(Archive *fout)
+{
+ PGresult *res;
+ const char *query;
+
+ /*
+ * Before Postgres 10, sequence metadata is in the sequence itself. We
+ * could likely make use of the sorted table with some extra effort, but
+ * for now it seems unlikely to be worth it.
+ */
+ if (fout->remoteVersion < 100000)
+ return;
+ else
+ query = "SELECT seqrelid, format_type(seqtypid, NULL), "
+ "seqstart, seqincrement, "
+ "seqmax, seqmin, "
+ "seqcache, seqcycle "
+ "FROM pg_catalog.pg_sequence "
+ "ORDER BY seqrelid";
+
+ res = ExecuteSqlQuery(fout, query, PGRES_TUPLES_OK);
+
+ nsequences = PQntuples(res);
+ sequences = (SequenceItem *) pg_malloc(nsequences * sizeof(SequenceItem));
+
+ for (int i = 0; i < nsequences; i++)
+ {
+ sequences[i].oid = atooid(PQgetvalue(res, i, 0));
+ sequences[i].seqtype = parse_sequence_type(PQgetvalue(res, i, 1));
+ sequences[i].startv = strtoi64(PQgetvalue(res, i, 2), NULL, 10);
+ sequences[i].incby = strtoi64(PQgetvalue(res, i, 3), NULL, 10);
+ sequences[i].maxv = strtoi64(PQgetvalue(res, i, 4), NULL, 10);
+ sequences[i].minv = strtoi64(PQgetvalue(res, i, 5), NULL, 10);
+ sequences[i].cache = strtoi64(PQgetvalue(res, i, 6), NULL, 10);
+ sequences[i].cycled = (strcmp(PQgetvalue(res, i, 7), "t") == 0);
+ }
+
+ PQclear(res);
+}
+
/*
* dumpSequence
* write the declaration (not data) of one user-defined sequence
@@ -17227,17 +17306,10 @@ static void
dumpSequence(Archive *fout, const TableInfo *tbinfo)
{
DumpOptions *dopt = fout->dopt;
- PGresult *res;
- SeqType seqtype;
- bool cycled;
+ SequenceItem *seq;
bool is_ascending;
int64 default_minv,
- default_maxv,
- minv,
- maxv,
- startv,
- incby,
- cache;
+ default_maxv;
PQExpBuffer query = createPQExpBuffer();
PQExpBuffer delqry = createPQExpBuffer();
char *qseqname;
@@ -17245,19 +17317,25 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
qseqname = pg_strdup(fmtId(tbinfo->dobj.name));
+ /*
+ * For versions >= 10, the sequence information is gathered in a sorted
+ * table before any calls to dumpSequence(). See collectSequences() for
+ * more information.
+ */
if (fout->remoteVersion >= 100000)
{
- appendPQExpBuffer(query,
- "SELECT format_type(seqtypid, NULL), "
- "seqstart, seqincrement, "
- "seqmax, seqmin, "
- "seqcache, seqcycle "
- "FROM pg_catalog.pg_sequence "
- "WHERE seqrelid = '%u'::oid",
- tbinfo->dobj.catId.oid);
+ SequenceItem key = {0};
+
+ Assert(sequences);
+
+ key.oid = tbinfo->dobj.catId.oid;
+ seq = bsearch(&key, sequences, nsequences,
+ sizeof(SequenceItem), SequenceItemCmp);
}
else
{
+ PGresult *res;
+
/*
* Before PostgreSQL 10, sequence metadata is in the sequence itself.
*
@@ -17269,46 +17347,47 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
"start_value, increment_by, max_value, min_value, "
"cache_value, is_cycled FROM %s",
fmtQualifiedDumpable(tbinfo));
- }
-
- res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
- if (PQntuples(res) != 1)
- pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
- "query to get data of sequence \"%s\" returned %d rows (expected 1)",
- PQntuples(res)),
- tbinfo->dobj.name, PQntuples(res));
+ res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
- seqtype = parse_sequence_type(PQgetvalue(res, 0, 0));
- startv = strtoi64(PQgetvalue(res, 0, 1), NULL, 10);
- incby = strtoi64(PQgetvalue(res, 0, 2), NULL, 10);
- maxv = strtoi64(PQgetvalue(res, 0, 3), NULL, 10);
- minv = strtoi64(PQgetvalue(res, 0, 4), NULL, 10);
- cache = strtoi64(PQgetvalue(res, 0, 5), NULL, 10);
- cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
+ if (PQntuples(res) != 1)
+ pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
+ "query to get data of sequence \"%s\" returned %d rows (expected 1)",
+ PQntuples(res)),
+ tbinfo->dobj.name, PQntuples(res));
+
+ seq = pg_malloc0(sizeof(SequenceItem));
+ seq->seqtype = parse_sequence_type(PQgetvalue(res, 0, 0));
+ seq->startv = strtoi64(PQgetvalue(res, 0, 1), NULL, 10);
+ seq->incby = strtoi64(PQgetvalue(res, 0, 2), NULL, 10);
+ seq->maxv = strtoi64(PQgetvalue(res, 0, 3), NULL, 10);
+ seq->minv = strtoi64(PQgetvalue(res, 0, 4), NULL, 10);
+ seq->cache = strtoi64(PQgetvalue(res, 0, 5), NULL, 10);
+ seq->cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
- PQclear(res);
+ PQclear(res);
+ }
/* Calculate default limits for a sequence of this type */
- is_ascending = (incby >= 0);
- if (seqtype == SEQTYPE_SMALLINT)
+ is_ascending = (seq->incby >= 0);
+ if (seq->seqtype == SEQTYPE_SMALLINT)
{
default_minv = is_ascending ? 1 : PG_INT16_MIN;
default_maxv = is_ascending ? PG_INT16_MAX : -1;
}
- else if (seqtype == SEQTYPE_INTEGER)
+ else if (seq->seqtype == SEQTYPE_INTEGER)
{
default_minv = is_ascending ? 1 : PG_INT32_MIN;
default_maxv = is_ascending ? PG_INT32_MAX : -1;
}
- else if (seqtype == SEQTYPE_BIGINT)
+ else if (seq->seqtype == SEQTYPE_BIGINT)
{
default_minv = is_ascending ? 1 : PG_INT64_MIN;
default_maxv = is_ascending ? PG_INT64_MAX : -1;
}
else
{
- pg_fatal("unrecognized sequence type: %d", seqtype);
+ pg_fatal("unrecognized sequence type: %d", seq->seqtype);
default_minv = default_maxv = 0; /* keep compiler quiet */
}
@@ -17359,27 +17438,27 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
"UNLOGGED " : "",
fmtQualifiedDumpable(tbinfo));
- if (seqtype != SEQTYPE_BIGINT)
- appendPQExpBuffer(query, " AS %s\n", SeqTypeNames[seqtype]);
+ if (seq->seqtype != SEQTYPE_BIGINT)
+ appendPQExpBuffer(query, " AS %s\n", SeqTypeNames[seq->seqtype]);
}
- appendPQExpBuffer(query, " START WITH " INT64_FORMAT "\n", startv);
+ appendPQExpBuffer(query, " START WITH " INT64_FORMAT "\n", seq->startv);
- appendPQExpBuffer(query, " INCREMENT BY " INT64_FORMAT "\n", incby);
+ appendPQExpBuffer(query, " INCREMENT BY " INT64_FORMAT "\n", seq->incby);
- if (minv != default_minv)
- appendPQExpBuffer(query, " MINVALUE " INT64_FORMAT "\n", minv);
+ if (seq->minv != default_minv)
+ appendPQExpBuffer(query, " MINVALUE " INT64_FORMAT "\n", seq->minv);
else
appendPQExpBufferStr(query, " NO MINVALUE\n");
- if (maxv != default_maxv)
- appendPQExpBuffer(query, " MAXVALUE " INT64_FORMAT "\n", maxv);
+ if (seq->maxv != default_maxv)
+ appendPQExpBuffer(query, " MAXVALUE " INT64_FORMAT "\n", seq->maxv);
else
appendPQExpBufferStr(query, " NO MAXVALUE\n");
appendPQExpBuffer(query,
" CACHE " INT64_FORMAT "%s",
- cache, (cycled ? "\n CYCLE" : ""));
+ seq->cache, (seq->cycled ? "\n CYCLE" : ""));
if (tbinfo->is_identity_sequence)
{
@@ -17465,6 +17544,8 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
tbinfo->dobj.namespace->dobj.name, tbinfo->rolname,
tbinfo->dobj.catId, 0, tbinfo->dobj.dumpId);
+ if (fout->remoteVersion < 100000)
+ pg_free(seq);
destroyPQExpBuffer(query);
destroyPQExpBuffer(delqry);
free(qseqname);
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 974e21155a..e1a46b5bf5 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2573,6 +2573,7 @@ SeqScanState
SeqTable
SeqTableData
SeqType
+SequenceItem
SerCommitSeqNo
SerialControl
SerialIOData
--
2.39.3 (Apple Git-146)
From e83ca9e2312e31ccf4430e8fd24744eff3d6fe75 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Wed, 17 Jul 2024 17:09:03 -0500
Subject: [PATCH v6 3/4] introduce pg_sequence_read_tuple
XXX: NEEDS CATVERSION BUMP
---
src/backend/commands/sequence.c | 62 ++++++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 6 +++
src/test/regress/expected/sequence.out | 7 +++
src/test/regress/sql/sequence.sql | 3 ++
4 files changed, 78 insertions(+)
diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c
index 9f28d40466..364097c457 100644
--- a/src/backend/commands/sequence.c
+++ b/src/backend/commands/sequence.c
@@ -1773,6 +1773,68 @@ pg_sequence_parameters(PG_FUNCTION_ARGS)
return HeapTupleGetDatum(heap_form_tuple(tupdesc, values, isnull));
}
+
+/*
+ * Return the sequence tuple.
+ *
+ * This is primarily intended for use by pg_dump to gather sequence data
+ * without needing to individually query each sequence relation.
+ */
+Datum
+pg_sequence_read_tuple(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+ SeqTable elm;
+ Relation seqrel;
+ Datum values[SEQ_COL_LASTCOL];
+ bool isnull[SEQ_COL_LASTCOL];
+ TupleDesc resultTupleDesc;
+ HeapTuple resultHeapTuple;
+ Datum result;
+
+ resultTupleDesc = CreateTemplateTupleDesc(SEQ_COL_LASTCOL);
+ TupleDescInitEntry(resultTupleDesc, (AttrNumber) 1, "last_value",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(resultTupleDesc, (AttrNumber) 2, "log_cnt",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(resultTupleDesc, (AttrNumber) 3, "is_called",
+ BOOLOID, -1, 0);
+ resultTupleDesc = BlessTupleDesc(resultTupleDesc);
+
+ init_sequence(relid, &elm, &seqrel);
+
+ /*
+ * Return all NULLs for sequences for which we lack privileges, other
+ * sessions' temporary sequences, and unlogged sequences on standbys.
+ */
+ if (pg_class_aclcheck(relid, GetUserId(), ACL_SELECT) == ACLCHECK_OK &&
+ !RELATION_IS_OTHER_TEMP(seqrel) &&
+ (RelationIsPermanent(seqrel) || !RecoveryInProgress()))
+ {
+ Buffer buf;
+ HeapTupleData seqtuple;
+ Form_pg_sequence_data seq;
+
+ seq = read_seq_tuple(seqrel, &buf, &seqtuple);
+
+ memset(isnull, false, sizeof(isnull));
+ values[0] = Int64GetDatum(seq->last_value);
+ values[1] = Int64GetDatum(seq->log_cnt);
+ values[2] = BoolGetDatum(seq->is_called);
+
+ UnlockReleaseBuffer(buf);
+ }
+ else
+ memset(isnull, true, sizeof(isnull));
+
+ sequence_close(seqrel, NoLock);
+
+ resultHeapTuple = heap_form_tuple(resultTupleDesc, values, isnull);
+ result = HeapTupleGetDatum(resultHeapTuple);
+ PG_RETURN_DATUM(result);
+}
+
+
/*
* Return the last value from the sequence
*
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 73d9cf8582..a16aec302e 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3329,6 +3329,12 @@
proname => 'pg_sequence_last_value', provolatile => 'v', proparallel => 'u',
prorettype => 'int8', proargtypes => 'regclass',
prosrc => 'pg_sequence_last_value' },
+{ oid => '9876', descr => 'return sequence tuple, for use by pg_dump',
+ proname => 'pg_sequence_read_tuple', provolatile => 'v', proparallel => 'u',
+ prorettype => 'record', proargtypes => 'regclass',
+ proallargtypes => '{regclass,int8,int8,bool}', proargmodes => '{i,o,o,o}',
+ proargnames => '{sequence_oid,last_value,log_cnt,is_called}',
+ prosrc => 'pg_sequence_read_tuple' },
{ oid => '275', descr => 'return the next oid for a system table',
proname => 'pg_nextoid', provolatile => 'v', proparallel => 'u',
diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out
index 2b47b7796b..e749c4574e 100644
--- a/src/test/regress/expected/sequence.out
+++ b/src/test/regress/expected/sequence.out
@@ -839,4 +839,11 @@ SELECT nextval('test_seq1');
3
(1 row)
+-- pg_sequence_read_tuple
+SELECT * FROM pg_sequence_read_tuple('test_seq1');
+ last_value | log_cnt | is_called
+------------+---------+-----------
+ 10 | 32 | t
+(1 row)
+
DROP SEQUENCE test_seq1;
diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sql
index 674f5f1f66..ea447938ae 100644
--- a/src/test/regress/sql/sequence.sql
+++ b/src/test/regress/sql/sequence.sql
@@ -413,4 +413,7 @@ SELECT nextval('test_seq1');
SELECT nextval('test_seq1');
SELECT nextval('test_seq1');
+-- pg_sequence_read_tuple
+SELECT * FROM pg_sequence_read_tuple('test_seq1');
+
DROP SEQUENCE test_seq1;
--
2.39.3 (Apple Git-146)
From c339097c8a6be238798d14ec08d157d818bdf3fc Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Wed, 17 Jul 2024 22:13:08 -0500
Subject: [PATCH v6 4/4] cache sequence data
---
src/bin/pg_dump/pg_dump.c | 81 ++++++++++++++++++++++++++++++---------
1 file changed, 63 insertions(+), 18 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 8c42fd762d..78f6f608bc 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -131,6 +131,8 @@ typedef struct
int64 startv; /* start value */
int64 incby; /* increment value */
int64 cache; /* cache size */
+ int64 last_value; /* last value of sequence */
+ bool is_called; /* whether nextval advances before returning */
} SequenceItem;
typedef enum OidOptions
@@ -17267,16 +17269,30 @@ collectSequences(Archive *fout)
* Before Postgres 10, sequence metadata is in the sequence itself. We
* could likely make use of the sorted table with some extra effort, but
* for now it seems unlikely to be worth it.
+ *
+ * Since version 18, we can gather the sequence data in this query with
+ * pg_sequence_read_tuple(), but we only do so for non-schema-only dumps.
*/
if (fout->remoteVersion < 100000)
return;
- else
+ else if (fout->remoteVersion < 180000 ||
+ (fout->dopt->schemaOnly && !fout->dopt->sequence_data))
query = "SELECT seqrelid, format_type(seqtypid, NULL), "
"seqstart, seqincrement, "
"seqmax, seqmin, "
- "seqcache, seqcycle "
+ "seqcache, seqcycle, "
+ "NULL, 'f' "
"FROM pg_catalog.pg_sequence "
"ORDER BY seqrelid";
+ else
+ query = "SELECT seqrelid, format_type(seqtypid, NULL), "
+ "seqstart, seqincrement, "
+ "seqmax, seqmin, "
+ "seqcache, seqcycle, "
+ "last_value, is_called "
+ "FROM pg_catalog.pg_sequence, "
+ "pg_sequence_read_tuple(seqrelid) "
+ "ORDER BY seqrelid;";
res = ExecuteSqlQuery(fout, query, PGRES_TUPLES_OK);
@@ -17293,6 +17309,8 @@ collectSequences(Archive *fout)
sequences[i].minv = strtoi64(PQgetvalue(res, i, 5), NULL, 10);
sequences[i].cache = strtoi64(PQgetvalue(res, i, 6), NULL, 10);
sequences[i].cycled = (strcmp(PQgetvalue(res, i, 7), "t") == 0);
+ sequences[i].last_value = strtoi64(PQgetvalue(res, i, 8), NULL, 10);
+ sequences[i].is_called = (strcmp(PQgetvalue(res, i, 9), "t") == 0);
}
PQclear(res);
@@ -17559,30 +17577,59 @@ static void
dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo)
{
TableInfo *tbinfo = tdinfo->tdtable;
- PGresult *res;
- char *last;
+ int64 last;
bool called;
PQExpBuffer query = createPQExpBuffer();
- appendPQExpBuffer(query,
- "SELECT last_value, is_called FROM %s",
- fmtQualifiedDumpable(tbinfo));
+ /*
+ * For versions >= 18, the sequence information is gathered in the sorted
+ * array before any calls to dumpSequenceData(). See collectSequences()
+ * for more information.
+ *
+ * For older versions, we have to query the sequence relations
+ * individually.
+ */
+ if (fout->remoteVersion < 180000)
+ {
+ PGresult *res;
- res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
+ appendPQExpBuffer(query,
+ "SELECT last_value, is_called FROM %s",
+ fmtQualifiedDumpable(tbinfo));
- if (PQntuples(res) != 1)
- pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
- "query to get data of sequence \"%s\" returned %d rows (expected 1)",
- PQntuples(res)),
- tbinfo->dobj.name, PQntuples(res));
+ res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
- last = PQgetvalue(res, 0, 0);
- called = (strcmp(PQgetvalue(res, 0, 1), "t") == 0);
+ if (PQntuples(res) != 1)
+ pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
+ "query to get data of sequence \"%s\" returned %d rows (expected 1)",
+ PQntuples(res)),
+ tbinfo->dobj.name, PQntuples(res));
+
+ last = strtoi64(PQgetvalue(res, 0, 0), NULL, 10);
+ called = (strcmp(PQgetvalue(res, 0, 1), "t") == 0);
+
+ PQclear(res);
+ }
+ else
+ {
+ SequenceItem key = {0};
+ SequenceItem *entry;
+
+ Assert(sequences);
+ Assert(tbinfo->dobj.catId.oid);
+
+ key.oid = tbinfo->dobj.catId.oid;
+ entry = bsearch(&key, sequences, nsequences,
+ sizeof(SequenceItem), SequenceItemCmp);
+
+ last = entry->last_value;
+ called = entry->is_called;
+ }
resetPQExpBuffer(query);
appendPQExpBufferStr(query, "SELECT pg_catalog.setval(");
appendStringLiteralAH(query, fmtQualifiedDumpable(tbinfo), fout);
- appendPQExpBuffer(query, ", %s, %s);\n",
+ appendPQExpBuffer(query, ", " INT64_FORMAT ", %s);\n",
last, (called ? "true" : "false"));
if (tdinfo->dobj.dump & DUMP_COMPONENT_DATA)
@@ -17596,8 +17643,6 @@ dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo)
.deps = &(tbinfo->dobj.dumpId),
.nDeps = 1));
- PQclear(res);
-
destroyPQExpBuffer(query);
}
--
2.39.3 (Apple Git-146)
Attachments:
[text/plain] v6-0001-parse-sequence-information.patch (6.0K, ../../Zpl7HywbF2qAkTVf@nathan/2-v6-0001-parse-sequence-information.patch)
download | inline diff:
From 139826d3b9290d547ae9b3446ad455ba713211e0 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Mon, 15 Jul 2024 13:13:05 -0500
Subject: [PATCH v6 1/4] parse sequence information
---
src/bin/pg_dump/pg_dump.c | 103 ++++++++++++++++++-------------
src/tools/pgindent/typedefs.list | 1 +
2 files changed, 60 insertions(+), 44 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index b8b1888bd3..74e22e5e1e 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -104,6 +104,23 @@ typedef struct
RelFileNumber toast_index_relfilenumber; /* toast table index filenode */
} BinaryUpgradeClassOidItem;
+typedef enum SeqType
+{
+ SEQTYPE_SMALLINT,
+ SEQTYPE_INTEGER,
+ SEQTYPE_BIGINT,
+} SeqType;
+
+const char *const SeqTypeNames[] =
+{
+ [SEQTYPE_SMALLINT] = "smallint",
+ [SEQTYPE_INTEGER] = "integer",
+ [SEQTYPE_BIGINT] = "bigint",
+};
+
+StaticAssertDecl(lengthof(SeqTypeNames) == (SEQTYPE_BIGINT + 1),
+ "array length mismatch");
+
typedef enum OidOptions
{
zeroIsError = 1,
@@ -17189,6 +17206,19 @@ dumpTableConstraintComment(Archive *fout, const ConstraintInfo *coninfo)
free(qtabname);
}
+static inline SeqType
+parse_sequence_type(const char *name)
+{
+ for (int i = 0; i < lengthof(SeqTypeNames); i++)
+ {
+ if (strcmp(SeqTypeNames[i], name) == 0)
+ return (SeqType) i;
+ }
+
+ pg_fatal("unrecognized sequence type: %s", name);
+ return (SeqType) 0; /* keep compiler quiet */
+}
+
/*
* dumpSequence
* write the declaration (not data) of one user-defined sequence
@@ -17198,18 +17228,16 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
{
DumpOptions *dopt = fout->dopt;
PGresult *res;
- char *startv,
- *incby,
- *maxv,
- *minv,
- *cache,
- *seqtype;
+ SeqType seqtype;
bool cycled;
bool is_ascending;
int64 default_minv,
- default_maxv;
- char bufm[32],
- bufx[32];
+ default_maxv,
+ minv,
+ maxv,
+ startv,
+ incby,
+ cache;
PQExpBuffer query = createPQExpBuffer();
PQExpBuffer delqry = createPQExpBuffer();
char *qseqname;
@@ -17251,50 +17279,39 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
PQntuples(res)),
tbinfo->dobj.name, PQntuples(res));
- seqtype = PQgetvalue(res, 0, 0);
- startv = PQgetvalue(res, 0, 1);
- incby = PQgetvalue(res, 0, 2);
- maxv = PQgetvalue(res, 0, 3);
- minv = PQgetvalue(res, 0, 4);
- cache = PQgetvalue(res, 0, 5);
+ seqtype = parse_sequence_type(PQgetvalue(res, 0, 0));
+ startv = strtoi64(PQgetvalue(res, 0, 1), NULL, 10);
+ incby = strtoi64(PQgetvalue(res, 0, 2), NULL, 10);
+ maxv = strtoi64(PQgetvalue(res, 0, 3), NULL, 10);
+ minv = strtoi64(PQgetvalue(res, 0, 4), NULL, 10);
+ cache = strtoi64(PQgetvalue(res, 0, 5), NULL, 10);
cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
+ PQclear(res);
+
/* Calculate default limits for a sequence of this type */
- is_ascending = (incby[0] != '-');
- if (strcmp(seqtype, "smallint") == 0)
+ is_ascending = (incby >= 0);
+ if (seqtype == SEQTYPE_SMALLINT)
{
default_minv = is_ascending ? 1 : PG_INT16_MIN;
default_maxv = is_ascending ? PG_INT16_MAX : -1;
}
- else if (strcmp(seqtype, "integer") == 0)
+ else if (seqtype == SEQTYPE_INTEGER)
{
default_minv = is_ascending ? 1 : PG_INT32_MIN;
default_maxv = is_ascending ? PG_INT32_MAX : -1;
}
- else if (strcmp(seqtype, "bigint") == 0)
+ else if (seqtype == SEQTYPE_BIGINT)
{
default_minv = is_ascending ? 1 : PG_INT64_MIN;
default_maxv = is_ascending ? PG_INT64_MAX : -1;
}
else
{
- pg_fatal("unrecognized sequence type: %s", seqtype);
+ pg_fatal("unrecognized sequence type: %d", seqtype);
default_minv = default_maxv = 0; /* keep compiler quiet */
}
- /*
- * 64-bit strtol() isn't very portable, so convert the limits to strings
- * and compare that way.
- */
- snprintf(bufm, sizeof(bufm), INT64_FORMAT, default_minv);
- snprintf(bufx, sizeof(bufx), INT64_FORMAT, default_maxv);
-
- /* Don't print minv/maxv if they match the respective default limit */
- if (strcmp(minv, bufm) == 0)
- minv = NULL;
- if (strcmp(maxv, bufx) == 0)
- maxv = NULL;
-
/*
* Identity sequences are not to be dropped separately.
*/
@@ -17342,26 +17359,26 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
"UNLOGGED " : "",
fmtQualifiedDumpable(tbinfo));
- if (strcmp(seqtype, "bigint") != 0)
- appendPQExpBuffer(query, " AS %s\n", seqtype);
+ if (seqtype != SEQTYPE_BIGINT)
+ appendPQExpBuffer(query, " AS %s\n", SeqTypeNames[seqtype]);
}
- appendPQExpBuffer(query, " START WITH %s\n", startv);
+ appendPQExpBuffer(query, " START WITH " INT64_FORMAT "\n", startv);
- appendPQExpBuffer(query, " INCREMENT BY %s\n", incby);
+ appendPQExpBuffer(query, " INCREMENT BY " INT64_FORMAT "\n", incby);
- if (minv)
- appendPQExpBuffer(query, " MINVALUE %s\n", minv);
+ if (minv != default_minv)
+ appendPQExpBuffer(query, " MINVALUE " INT64_FORMAT "\n", minv);
else
appendPQExpBufferStr(query, " NO MINVALUE\n");
- if (maxv)
- appendPQExpBuffer(query, " MAXVALUE %s\n", maxv);
+ if (maxv != default_maxv)
+ appendPQExpBuffer(query, " MAXVALUE " INT64_FORMAT "\n", maxv);
else
appendPQExpBufferStr(query, " NO MAXVALUE\n");
appendPQExpBuffer(query,
- " CACHE %s%s",
+ " CACHE " INT64_FORMAT "%s",
cache, (cycled ? "\n CYCLE" : ""));
if (tbinfo->is_identity_sequence)
@@ -17448,8 +17465,6 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
tbinfo->dobj.namespace->dobj.name, tbinfo->rolname,
tbinfo->dobj.catId, 0, tbinfo->dobj.dumpId);
- PQclear(res);
-
destroyPQExpBuffer(query);
destroyPQExpBuffer(delqry);
free(qseqname);
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index b4d7f9217c..974e21155a 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2572,6 +2572,7 @@ SeqScan
SeqScanState
SeqTable
SeqTableData
+SeqType
SerCommitSeqNo
SerialControl
SerialIOData
--
2.39.3 (Apple Git-146)
[text/plain] v6-0002-cache-sequence-information.patch (10.2K, ../../Zpl7HywbF2qAkTVf@nathan/3-v6-0002-cache-sequence-information.patch)
download | inline diff:
From 7bf81a53af63c993478bb47fe347deefd1145bb4 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Tue, 9 Jul 2024 14:06:23 -0500
Subject: [PATCH v6 2/4] cache sequence information
---
src/bin/pg_dump/pg_dump.c | 175 ++++++++++++++++++++++---------
src/tools/pgindent/typedefs.list | 1 +
2 files changed, 129 insertions(+), 47 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 74e22e5e1e..8c42fd762d 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -121,6 +121,18 @@ const char *const SeqTypeNames[] =
StaticAssertDecl(lengthof(SeqTypeNames) == (SEQTYPE_BIGINT + 1),
"array length mismatch");
+typedef struct
+{
+ Oid oid; /* sequence OID */
+ SeqType seqtype; /* data type of sequence */
+ bool cycled; /* whether sequence cycles */
+ int64 minv; /* minimum value */
+ int64 maxv; /* maximum value */
+ int64 startv; /* start value */
+ int64 incby; /* increment value */
+ int64 cache; /* cache size */
+} SequenceItem;
+
typedef enum OidOptions
{
zeroIsError = 1,
@@ -190,6 +202,10 @@ static int nseclabels = 0;
static BinaryUpgradeClassOidItem *binaryUpgradeClassOids = NULL;
static int nbinaryUpgradeClassOids = 0;
+/* sorted table of sequences */
+static SequenceItem *sequences = NULL;
+static int nsequences = 0;
+
/*
* The default number of rows per INSERT when
* --inserts is specified without --rows-per-insert
@@ -287,6 +303,7 @@ static void dumpTable(Archive *fout, const TableInfo *tbinfo);
static void dumpTableSchema(Archive *fout, const TableInfo *tbinfo);
static void dumpTableAttach(Archive *fout, const TableAttachInfo *attachinfo);
static void dumpAttrDef(Archive *fout, const AttrDefInfo *adinfo);
+static void collectSequences(Archive *fout);
static void dumpSequence(Archive *fout, const TableInfo *tbinfo);
static void dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo);
static void dumpIndex(Archive *fout, const IndxInfo *indxinfo);
@@ -1009,6 +1026,9 @@ main(int argc, char **argv)
if (dopt.binary_upgrade)
collectBinaryUpgradeClassOids(fout);
+ /* Collect sequence information. */
+ collectSequences(fout);
+
/* Lastly, create dummy objects to represent the section boundaries */
boundaryObjs = createBoundaryObjects();
@@ -17219,6 +17239,65 @@ parse_sequence_type(const char *name)
return (SeqType) 0; /* keep compiler quiet */
}
+/*
+ * bsearch() comparator for SequenceItem
+ */
+static int
+SequenceItemCmp(const void *p1, const void *p2)
+{
+ SequenceItem v1 = *((const SequenceItem *) p1);
+ SequenceItem v2 = *((const SequenceItem *) p2);
+
+ return pg_cmp_u32(v1.oid, v2.oid);
+}
+
+/*
+ * collectSequences
+ *
+ * Construct a table of sequence information. This table is sorted by OID for
+ * speed in lookup.
+ */
+static void
+collectSequences(Archive *fout)
+{
+ PGresult *res;
+ const char *query;
+
+ /*
+ * Before Postgres 10, sequence metadata is in the sequence itself. We
+ * could likely make use of the sorted table with some extra effort, but
+ * for now it seems unlikely to be worth it.
+ */
+ if (fout->remoteVersion < 100000)
+ return;
+ else
+ query = "SELECT seqrelid, format_type(seqtypid, NULL), "
+ "seqstart, seqincrement, "
+ "seqmax, seqmin, "
+ "seqcache, seqcycle "
+ "FROM pg_catalog.pg_sequence "
+ "ORDER BY seqrelid";
+
+ res = ExecuteSqlQuery(fout, query, PGRES_TUPLES_OK);
+
+ nsequences = PQntuples(res);
+ sequences = (SequenceItem *) pg_malloc(nsequences * sizeof(SequenceItem));
+
+ for (int i = 0; i < nsequences; i++)
+ {
+ sequences[i].oid = atooid(PQgetvalue(res, i, 0));
+ sequences[i].seqtype = parse_sequence_type(PQgetvalue(res, i, 1));
+ sequences[i].startv = strtoi64(PQgetvalue(res, i, 2), NULL, 10);
+ sequences[i].incby = strtoi64(PQgetvalue(res, i, 3), NULL, 10);
+ sequences[i].maxv = strtoi64(PQgetvalue(res, i, 4), NULL, 10);
+ sequences[i].minv = strtoi64(PQgetvalue(res, i, 5), NULL, 10);
+ sequences[i].cache = strtoi64(PQgetvalue(res, i, 6), NULL, 10);
+ sequences[i].cycled = (strcmp(PQgetvalue(res, i, 7), "t") == 0);
+ }
+
+ PQclear(res);
+}
+
/*
* dumpSequence
* write the declaration (not data) of one user-defined sequence
@@ -17227,17 +17306,10 @@ static void
dumpSequence(Archive *fout, const TableInfo *tbinfo)
{
DumpOptions *dopt = fout->dopt;
- PGresult *res;
- SeqType seqtype;
- bool cycled;
+ SequenceItem *seq;
bool is_ascending;
int64 default_minv,
- default_maxv,
- minv,
- maxv,
- startv,
- incby,
- cache;
+ default_maxv;
PQExpBuffer query = createPQExpBuffer();
PQExpBuffer delqry = createPQExpBuffer();
char *qseqname;
@@ -17245,19 +17317,25 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
qseqname = pg_strdup(fmtId(tbinfo->dobj.name));
+ /*
+ * For versions >= 10, the sequence information is gathered in a sorted
+ * table before any calls to dumpSequence(). See collectSequences() for
+ * more information.
+ */
if (fout->remoteVersion >= 100000)
{
- appendPQExpBuffer(query,
- "SELECT format_type(seqtypid, NULL), "
- "seqstart, seqincrement, "
- "seqmax, seqmin, "
- "seqcache, seqcycle "
- "FROM pg_catalog.pg_sequence "
- "WHERE seqrelid = '%u'::oid",
- tbinfo->dobj.catId.oid);
+ SequenceItem key = {0};
+
+ Assert(sequences);
+
+ key.oid = tbinfo->dobj.catId.oid;
+ seq = bsearch(&key, sequences, nsequences,
+ sizeof(SequenceItem), SequenceItemCmp);
}
else
{
+ PGresult *res;
+
/*
* Before PostgreSQL 10, sequence metadata is in the sequence itself.
*
@@ -17269,46 +17347,47 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
"start_value, increment_by, max_value, min_value, "
"cache_value, is_cycled FROM %s",
fmtQualifiedDumpable(tbinfo));
- }
-
- res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
- if (PQntuples(res) != 1)
- pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
- "query to get data of sequence \"%s\" returned %d rows (expected 1)",
- PQntuples(res)),
- tbinfo->dobj.name, PQntuples(res));
+ res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
- seqtype = parse_sequence_type(PQgetvalue(res, 0, 0));
- startv = strtoi64(PQgetvalue(res, 0, 1), NULL, 10);
- incby = strtoi64(PQgetvalue(res, 0, 2), NULL, 10);
- maxv = strtoi64(PQgetvalue(res, 0, 3), NULL, 10);
- minv = strtoi64(PQgetvalue(res, 0, 4), NULL, 10);
- cache = strtoi64(PQgetvalue(res, 0, 5), NULL, 10);
- cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
+ if (PQntuples(res) != 1)
+ pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
+ "query to get data of sequence \"%s\" returned %d rows (expected 1)",
+ PQntuples(res)),
+ tbinfo->dobj.name, PQntuples(res));
+
+ seq = pg_malloc0(sizeof(SequenceItem));
+ seq->seqtype = parse_sequence_type(PQgetvalue(res, 0, 0));
+ seq->startv = strtoi64(PQgetvalue(res, 0, 1), NULL, 10);
+ seq->incby = strtoi64(PQgetvalue(res, 0, 2), NULL, 10);
+ seq->maxv = strtoi64(PQgetvalue(res, 0, 3), NULL, 10);
+ seq->minv = strtoi64(PQgetvalue(res, 0, 4), NULL, 10);
+ seq->cache = strtoi64(PQgetvalue(res, 0, 5), NULL, 10);
+ seq->cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
- PQclear(res);
+ PQclear(res);
+ }
/* Calculate default limits for a sequence of this type */
- is_ascending = (incby >= 0);
- if (seqtype == SEQTYPE_SMALLINT)
+ is_ascending = (seq->incby >= 0);
+ if (seq->seqtype == SEQTYPE_SMALLINT)
{
default_minv = is_ascending ? 1 : PG_INT16_MIN;
default_maxv = is_ascending ? PG_INT16_MAX : -1;
}
- else if (seqtype == SEQTYPE_INTEGER)
+ else if (seq->seqtype == SEQTYPE_INTEGER)
{
default_minv = is_ascending ? 1 : PG_INT32_MIN;
default_maxv = is_ascending ? PG_INT32_MAX : -1;
}
- else if (seqtype == SEQTYPE_BIGINT)
+ else if (seq->seqtype == SEQTYPE_BIGINT)
{
default_minv = is_ascending ? 1 : PG_INT64_MIN;
default_maxv = is_ascending ? PG_INT64_MAX : -1;
}
else
{
- pg_fatal("unrecognized sequence type: %d", seqtype);
+ pg_fatal("unrecognized sequence type: %d", seq->seqtype);
default_minv = default_maxv = 0; /* keep compiler quiet */
}
@@ -17359,27 +17438,27 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
"UNLOGGED " : "",
fmtQualifiedDumpable(tbinfo));
- if (seqtype != SEQTYPE_BIGINT)
- appendPQExpBuffer(query, " AS %s\n", SeqTypeNames[seqtype]);
+ if (seq->seqtype != SEQTYPE_BIGINT)
+ appendPQExpBuffer(query, " AS %s\n", SeqTypeNames[seq->seqtype]);
}
- appendPQExpBuffer(query, " START WITH " INT64_FORMAT "\n", startv);
+ appendPQExpBuffer(query, " START WITH " INT64_FORMAT "\n", seq->startv);
- appendPQExpBuffer(query, " INCREMENT BY " INT64_FORMAT "\n", incby);
+ appendPQExpBuffer(query, " INCREMENT BY " INT64_FORMAT "\n", seq->incby);
- if (minv != default_minv)
- appendPQExpBuffer(query, " MINVALUE " INT64_FORMAT "\n", minv);
+ if (seq->minv != default_minv)
+ appendPQExpBuffer(query, " MINVALUE " INT64_FORMAT "\n", seq->minv);
else
appendPQExpBufferStr(query, " NO MINVALUE\n");
- if (maxv != default_maxv)
- appendPQExpBuffer(query, " MAXVALUE " INT64_FORMAT "\n", maxv);
+ if (seq->maxv != default_maxv)
+ appendPQExpBuffer(query, " MAXVALUE " INT64_FORMAT "\n", seq->maxv);
else
appendPQExpBufferStr(query, " NO MAXVALUE\n");
appendPQExpBuffer(query,
" CACHE " INT64_FORMAT "%s",
- cache, (cycled ? "\n CYCLE" : ""));
+ seq->cache, (seq->cycled ? "\n CYCLE" : ""));
if (tbinfo->is_identity_sequence)
{
@@ -17465,6 +17544,8 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
tbinfo->dobj.namespace->dobj.name, tbinfo->rolname,
tbinfo->dobj.catId, 0, tbinfo->dobj.dumpId);
+ if (fout->remoteVersion < 100000)
+ pg_free(seq);
destroyPQExpBuffer(query);
destroyPQExpBuffer(delqry);
free(qseqname);
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 974e21155a..e1a46b5bf5 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2573,6 +2573,7 @@ SeqScanState
SeqTable
SeqTableData
SeqType
+SequenceItem
SerCommitSeqNo
SerialControl
SerialIOData
--
2.39.3 (Apple Git-146)
[text/plain] v6-0003-introduce-pg_sequence_read_tuple.patch (4.4K, ../../Zpl7HywbF2qAkTVf@nathan/4-v6-0003-introduce-pg_sequence_read_tuple.patch)
download | inline diff:
From e83ca9e2312e31ccf4430e8fd24744eff3d6fe75 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Wed, 17 Jul 2024 17:09:03 -0500
Subject: [PATCH v6 3/4] introduce pg_sequence_read_tuple
XXX: NEEDS CATVERSION BUMP
---
src/backend/commands/sequence.c | 62 ++++++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 6 +++
src/test/regress/expected/sequence.out | 7 +++
src/test/regress/sql/sequence.sql | 3 ++
4 files changed, 78 insertions(+)
diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c
index 9f28d40466..364097c457 100644
--- a/src/backend/commands/sequence.c
+++ b/src/backend/commands/sequence.c
@@ -1773,6 +1773,68 @@ pg_sequence_parameters(PG_FUNCTION_ARGS)
return HeapTupleGetDatum(heap_form_tuple(tupdesc, values, isnull));
}
+
+/*
+ * Return the sequence tuple.
+ *
+ * This is primarily intended for use by pg_dump to gather sequence data
+ * without needing to individually query each sequence relation.
+ */
+Datum
+pg_sequence_read_tuple(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+ SeqTable elm;
+ Relation seqrel;
+ Datum values[SEQ_COL_LASTCOL];
+ bool isnull[SEQ_COL_LASTCOL];
+ TupleDesc resultTupleDesc;
+ HeapTuple resultHeapTuple;
+ Datum result;
+
+ resultTupleDesc = CreateTemplateTupleDesc(SEQ_COL_LASTCOL);
+ TupleDescInitEntry(resultTupleDesc, (AttrNumber) 1, "last_value",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(resultTupleDesc, (AttrNumber) 2, "log_cnt",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(resultTupleDesc, (AttrNumber) 3, "is_called",
+ BOOLOID, -1, 0);
+ resultTupleDesc = BlessTupleDesc(resultTupleDesc);
+
+ init_sequence(relid, &elm, &seqrel);
+
+ /*
+ * Return all NULLs for sequences for which we lack privileges, other
+ * sessions' temporary sequences, and unlogged sequences on standbys.
+ */
+ if (pg_class_aclcheck(relid, GetUserId(), ACL_SELECT) == ACLCHECK_OK &&
+ !RELATION_IS_OTHER_TEMP(seqrel) &&
+ (RelationIsPermanent(seqrel) || !RecoveryInProgress()))
+ {
+ Buffer buf;
+ HeapTupleData seqtuple;
+ Form_pg_sequence_data seq;
+
+ seq = read_seq_tuple(seqrel, &buf, &seqtuple);
+
+ memset(isnull, false, sizeof(isnull));
+ values[0] = Int64GetDatum(seq->last_value);
+ values[1] = Int64GetDatum(seq->log_cnt);
+ values[2] = BoolGetDatum(seq->is_called);
+
+ UnlockReleaseBuffer(buf);
+ }
+ else
+ memset(isnull, true, sizeof(isnull));
+
+ sequence_close(seqrel, NoLock);
+
+ resultHeapTuple = heap_form_tuple(resultTupleDesc, values, isnull);
+ result = HeapTupleGetDatum(resultHeapTuple);
+ PG_RETURN_DATUM(result);
+}
+
+
/*
* Return the last value from the sequence
*
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 73d9cf8582..a16aec302e 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3329,6 +3329,12 @@
proname => 'pg_sequence_last_value', provolatile => 'v', proparallel => 'u',
prorettype => 'int8', proargtypes => 'regclass',
prosrc => 'pg_sequence_last_value' },
+{ oid => '9876', descr => 'return sequence tuple, for use by pg_dump',
+ proname => 'pg_sequence_read_tuple', provolatile => 'v', proparallel => 'u',
+ prorettype => 'record', proargtypes => 'regclass',
+ proallargtypes => '{regclass,int8,int8,bool}', proargmodes => '{i,o,o,o}',
+ proargnames => '{sequence_oid,last_value,log_cnt,is_called}',
+ prosrc => 'pg_sequence_read_tuple' },
{ oid => '275', descr => 'return the next oid for a system table',
proname => 'pg_nextoid', provolatile => 'v', proparallel => 'u',
diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out
index 2b47b7796b..e749c4574e 100644
--- a/src/test/regress/expected/sequence.out
+++ b/src/test/regress/expected/sequence.out
@@ -839,4 +839,11 @@ SELECT nextval('test_seq1');
3
(1 row)
+-- pg_sequence_read_tuple
+SELECT * FROM pg_sequence_read_tuple('test_seq1');
+ last_value | log_cnt | is_called
+------------+---------+-----------
+ 10 | 32 | t
+(1 row)
+
DROP SEQUENCE test_seq1;
diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sql
index 674f5f1f66..ea447938ae 100644
--- a/src/test/regress/sql/sequence.sql
+++ b/src/test/regress/sql/sequence.sql
@@ -413,4 +413,7 @@ SELECT nextval('test_seq1');
SELECT nextval('test_seq1');
SELECT nextval('test_seq1');
+-- pg_sequence_read_tuple
+SELECT * FROM pg_sequence_read_tuple('test_seq1');
+
DROP SEQUENCE test_seq1;
--
2.39.3 (Apple Git-146)
[text/plain] v6-0004-cache-sequence-data.patch (4.9K, ../../Zpl7HywbF2qAkTVf@nathan/5-v6-0004-cache-sequence-data.patch)
download | inline diff:
From c339097c8a6be238798d14ec08d157d818bdf3fc Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Wed, 17 Jul 2024 22:13:08 -0500
Subject: [PATCH v6 4/4] cache sequence data
---
src/bin/pg_dump/pg_dump.c | 81 ++++++++++++++++++++++++++++++---------
1 file changed, 63 insertions(+), 18 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 8c42fd762d..78f6f608bc 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -131,6 +131,8 @@ typedef struct
int64 startv; /* start value */
int64 incby; /* increment value */
int64 cache; /* cache size */
+ int64 last_value; /* last value of sequence */
+ bool is_called; /* whether nextval advances before returning */
} SequenceItem;
typedef enum OidOptions
@@ -17267,16 +17269,30 @@ collectSequences(Archive *fout)
* Before Postgres 10, sequence metadata is in the sequence itself. We
* could likely make use of the sorted table with some extra effort, but
* for now it seems unlikely to be worth it.
+ *
+ * Since version 18, we can gather the sequence data in this query with
+ * pg_sequence_read_tuple(), but we only do so for non-schema-only dumps.
*/
if (fout->remoteVersion < 100000)
return;
- else
+ else if (fout->remoteVersion < 180000 ||
+ (fout->dopt->schemaOnly && !fout->dopt->sequence_data))
query = "SELECT seqrelid, format_type(seqtypid, NULL), "
"seqstart, seqincrement, "
"seqmax, seqmin, "
- "seqcache, seqcycle "
+ "seqcache, seqcycle, "
+ "NULL, 'f' "
"FROM pg_catalog.pg_sequence "
"ORDER BY seqrelid";
+ else
+ query = "SELECT seqrelid, format_type(seqtypid, NULL), "
+ "seqstart, seqincrement, "
+ "seqmax, seqmin, "
+ "seqcache, seqcycle, "
+ "last_value, is_called "
+ "FROM pg_catalog.pg_sequence, "
+ "pg_sequence_read_tuple(seqrelid) "
+ "ORDER BY seqrelid;";
res = ExecuteSqlQuery(fout, query, PGRES_TUPLES_OK);
@@ -17293,6 +17309,8 @@ collectSequences(Archive *fout)
sequences[i].minv = strtoi64(PQgetvalue(res, i, 5), NULL, 10);
sequences[i].cache = strtoi64(PQgetvalue(res, i, 6), NULL, 10);
sequences[i].cycled = (strcmp(PQgetvalue(res, i, 7), "t") == 0);
+ sequences[i].last_value = strtoi64(PQgetvalue(res, i, 8), NULL, 10);
+ sequences[i].is_called = (strcmp(PQgetvalue(res, i, 9), "t") == 0);
}
PQclear(res);
@@ -17559,30 +17577,59 @@ static void
dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo)
{
TableInfo *tbinfo = tdinfo->tdtable;
- PGresult *res;
- char *last;
+ int64 last;
bool called;
PQExpBuffer query = createPQExpBuffer();
- appendPQExpBuffer(query,
- "SELECT last_value, is_called FROM %s",
- fmtQualifiedDumpable(tbinfo));
+ /*
+ * For versions >= 18, the sequence information is gathered in the sorted
+ * array before any calls to dumpSequenceData(). See collectSequences()
+ * for more information.
+ *
+ * For older versions, we have to query the sequence relations
+ * individually.
+ */
+ if (fout->remoteVersion < 180000)
+ {
+ PGresult *res;
- res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
+ appendPQExpBuffer(query,
+ "SELECT last_value, is_called FROM %s",
+ fmtQualifiedDumpable(tbinfo));
- if (PQntuples(res) != 1)
- pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
- "query to get data of sequence \"%s\" returned %d rows (expected 1)",
- PQntuples(res)),
- tbinfo->dobj.name, PQntuples(res));
+ res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
- last = PQgetvalue(res, 0, 0);
- called = (strcmp(PQgetvalue(res, 0, 1), "t") == 0);
+ if (PQntuples(res) != 1)
+ pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
+ "query to get data of sequence \"%s\" returned %d rows (expected 1)",
+ PQntuples(res)),
+ tbinfo->dobj.name, PQntuples(res));
+
+ last = strtoi64(PQgetvalue(res, 0, 0), NULL, 10);
+ called = (strcmp(PQgetvalue(res, 0, 1), "t") == 0);
+
+ PQclear(res);
+ }
+ else
+ {
+ SequenceItem key = {0};
+ SequenceItem *entry;
+
+ Assert(sequences);
+ Assert(tbinfo->dobj.catId.oid);
+
+ key.oid = tbinfo->dobj.catId.oid;
+ entry = bsearch(&key, sequences, nsequences,
+ sizeof(SequenceItem), SequenceItemCmp);
+
+ last = entry->last_value;
+ called = entry->is_called;
+ }
resetPQExpBuffer(query);
appendPQExpBufferStr(query, "SELECT pg_catalog.setval(");
appendStringLiteralAH(query, fmtQualifiedDumpable(tbinfo), fout);
- appendPQExpBuffer(query, ", %s, %s);\n",
+ appendPQExpBuffer(query, ", " INT64_FORMAT ", %s);\n",
last, (called ? "true" : "false"));
if (tdinfo->dobj.dump & DUMP_COMPONENT_DATA)
@@ -17596,8 +17643,6 @@ dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo)
.deps = &(tbinfo->dobj.dumpId),
.nDeps = 1));
- PQclear(res);
-
destroyPQExpBuffer(query);
}
--
2.39.3 (Apple Git-146)
^ permalink raw reply [nested|flat] 21+ messages in thread
end of thread, other threads:[~2024-07-18 20:29 UTC | newest]
Thread overview: 21+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-01-07 21:11 [PATCH v2 08/17] Make opp freeze heuristic compatible with prune+freeze record Melanie Plageman <[email protected]>
2024-01-07 21:11 [PATCH v3 08/17] Make opp freeze heuristic compatible with prune+freeze record Melanie Plageman <[email protected]>
2024-01-07 21:11 [PATCH v2 08/17] Make opp freeze heuristic compatible with prune+freeze record Melanie Plageman <[email protected]>
2024-01-07 21:11 [PATCH v4 10/19] Make opp freeze heuristic compatible with prune+freeze record Melanie Plageman <[email protected]>
2024-01-07 21:11 [PATCH v4 10/19] Make opp freeze heuristic compatible with prune+freeze record Melanie Plageman <[email protected]>
2024-01-07 21:11 [PATCH v3 08/17] Make opp freeze heuristic compatible with prune+freeze record Melanie Plageman <[email protected]>
2024-01-07 21:11 [PATCH v3 08/17] Make opp freeze heuristic compatible with prune+freeze record Melanie Plageman <[email protected]>
2024-01-07 21:11 [PATCH v4 10/19] Make opp freeze heuristic compatible with prune+freeze record Melanie Plageman <[email protected]>
2024-01-07 21:11 [PATCH v2 08/17] Make opp freeze heuristic compatible with prune+freeze record Melanie Plageman <[email protected]>
2024-03-26 00:48 [PATCH v7 09/16] Make opp freeze heuristic compatible with prune+freeze record Melanie Plageman <[email protected]>
2024-03-26 00:48 [PATCH v9 09/21] Make opp freeze heuristic compatible with prune+freeze record Melanie Plageman <[email protected]>
2024-03-26 00:48 [PATCH v7 09/16] Make opp freeze heuristic compatible with prune+freeze record Melanie Plageman <[email protected]>
2024-07-17 03:23 Re: improve performance of pg_dump with many sequences Nathan Bossart <[email protected]>
2024-07-17 18:48 ` Re: improve performance of pg_dump with many sequences Nathan Bossart <[email protected]>
2024-07-17 18:59 ` Re: improve performance of pg_dump with many sequences Tom Lane <[email protected]>
2024-07-17 19:05 ` Re: improve performance of pg_dump with many sequences Nathan Bossart <[email protected]>
2024-07-17 19:11 ` Re: improve performance of pg_dump with many sequences Tom Lane <[email protected]>
2024-07-18 03:45 ` Re: improve performance of pg_dump with many sequences Nathan Bossart <[email protected]>
2024-07-18 03:58 ` Re: improve performance of pg_dump with many sequences Tom Lane <[email protected]>
2024-07-18 18:22 ` Re: improve performance of pg_dump with many sequences Nathan Bossart <[email protected]>
2024-07-18 20:29 ` Re: improve performance of pg_dump with many sequences Nathan Bossart <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox