public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v18 04/18] tableam: Add fetch_row_version.
8+ messages / 4 participants
[nested] [flat]
* [PATCH v18 04/18] tableam: Add fetch_row_version.
@ 2019-03-08 00:30 Andres Freund <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Andres Freund @ 2019-03-08 00:30 UTC (permalink / raw)
Author:
Reviewed-By:
Discussion: https://postgr.es/m/
Backpatch:
---
src/backend/access/heap/heapam_handler.c | 28 ++++++++++
src/backend/commands/trigger.c | 70 ++++--------------------
src/backend/executor/execMain.c | 12 +---
src/backend/executor/nodeLockRows.c | 10 +---
src/backend/executor/nodeModifyTable.c | 23 ++------
src/backend/executor/nodeTidscan.c | 15 +----
src/include/access/tableam.h | 25 +++++++++
7 files changed, 73 insertions(+), 110 deletions(-)
diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c
index 3285197c558..318e393dbde 100644
--- a/src/backend/access/heap/heapam_handler.c
+++ b/src/backend/access/heap/heapam_handler.c
@@ -148,6 +148,33 @@ heapam_index_fetch_tuple(struct IndexFetchTableData *scan,
* ------------------------------------------------------------------------
*/
+static bool
+heapam_fetch_row_version(Relation relation,
+ ItemPointer tid,
+ Snapshot snapshot,
+ TupleTableSlot *slot,
+ Relation stats_relation)
+{
+ BufferHeapTupleTableSlot *bslot = (BufferHeapTupleTableSlot *) slot;
+ Buffer buffer;
+
+ Assert(TTS_IS_BUFFERTUPLE(slot));
+
+ if (heap_fetch(relation, tid, snapshot, &bslot->base.tupdata, &buffer, stats_relation))
+ {
+ /* store in slot, transferring existing pin */
+ ExecStorePinnedBufferHeapTuple(&bslot->base.tupdata, slot, buffer);
+
+ slot->tts_tableOid = RelationGetRelid(relation);
+
+ return true;
+ }
+
+ slot->tts_tableOid = RelationGetRelid(relation);
+
+ return false;
+}
+
static bool
heapam_tuple_satisfies_snapshot(Relation rel, TupleTableSlot *slot,
Snapshot snapshot)
@@ -546,6 +573,7 @@ static const TableAmRoutine heapam_methods = {
.tuple_update = heapam_heap_update,
.tuple_lock = heapam_lock_tuple,
+ .tuple_fetch_row_version = heapam_fetch_row_version,
.tuple_satisfies_snapshot = heapam_tuple_satisfies_snapshot,
};
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index 5cc15bcfef0..6fbf0c2b81e 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -14,10 +14,11 @@
#include "postgres.h"
#include "access/genam.h"
-#include "access/heapam.h"
-#include "access/tableam.h"
-#include "access/sysattr.h"
#include "access/htup_details.h"
+#include "access/relation.h"
+#include "access/sysattr.h"
+#include "access/table.h"
+#include "access/tableam.h"
#include "access/xact.h"
#include "catalog/catalog.h"
#include "catalog/dependency.h"
@@ -3382,43 +3383,8 @@ GetTupleForTrigger(EState *estate,
}
else
{
-
- Page page;
- ItemId lp;
- Buffer buffer;
- BufferHeapTupleTableSlot *boldslot;
- HeapTuple tuple;
-
- Assert(TTS_IS_BUFFERTUPLE(oldslot));
- ExecClearTuple(oldslot);
- boldslot = (BufferHeapTupleTableSlot *) oldslot;
- tuple = &boldslot->base.tupdata;
-
- buffer = ReadBuffer(relation, ItemPointerGetBlockNumber(tid));
-
- /*
- * Although we already know this tuple is valid, we must lock the
- * buffer to ensure that no one has a buffer cleanup lock; otherwise
- * they might move the tuple while we try to copy it. But we can
- * release the lock before actually doing the heap_copytuple call,
- * since holding pin is sufficient to prevent anyone from getting a
- * cleanup lock they don't already hold.
- */
- LockBuffer(buffer, BUFFER_LOCK_SHARE);
-
- page = BufferGetPage(buffer);
- lp = PageGetItemId(page, ItemPointerGetOffsetNumber(tid));
-
- Assert(ItemIdIsNormal(lp));
-
- tuple->t_data = (HeapTupleHeader) PageGetItem(page, lp);
- tuple->t_len = ItemIdGetLength(lp);
- tuple->t_self = *tid;
- tuple->t_tableOid = RelationGetRelid(relation);
-
- LockBuffer(buffer, BUFFER_LOCK_UNLOCK);
-
- ExecStorePinnedBufferHeapTuple(tuple, oldslot, buffer);
+ if (!table_fetch_row_version(relation, tid, SnapshotAny, oldslot, NULL))
+ elog(ERROR, "couldn't fetch tuple");
}
return true;
@@ -4197,8 +4163,6 @@ AfterTriggerExecute(EState *estate,
AfterTriggerShared evtshared = GetTriggerSharedData(event);
Oid tgoid = evtshared->ats_tgoid;
TriggerData LocTriggerData;
- HeapTupleData tuple1;
- HeapTupleData tuple2;
HeapTuple rettuple;
int tgindx;
bool should_free_trig = false;
@@ -4275,19 +4239,12 @@ AfterTriggerExecute(EState *estate,
default:
if (ItemPointerIsValid(&(event->ate_ctid1)))
{
- Buffer buffer;
-
LocTriggerData.tg_trigslot = ExecGetTriggerOldSlot(estate, relInfo);
- ItemPointerCopy(&(event->ate_ctid1), &(tuple1.t_self));
- if (!heap_fetch(rel, &(tuple1.t_self), SnapshotAny, &tuple1, &buffer, NULL))
+ if (!table_fetch_row_version(rel, &(event->ate_ctid1), SnapshotAny, LocTriggerData.tg_trigslot, NULL))
elog(ERROR, "failed to fetch tuple1 for AFTER trigger");
- ExecStorePinnedBufferHeapTuple(&tuple1,
- LocTriggerData.tg_trigslot,
- buffer);
LocTriggerData.tg_trigtuple =
- ExecFetchSlotHeapTuple(LocTriggerData.tg_trigslot, false,
- &should_free_trig);
+ ExecFetchSlotHeapTuple(LocTriggerData.tg_trigslot, false, &should_free_trig);
}
else
{
@@ -4299,19 +4256,12 @@ AfterTriggerExecute(EState *estate,
AFTER_TRIGGER_2CTID &&
ItemPointerIsValid(&(event->ate_ctid2)))
{
- Buffer buffer;
-
LocTriggerData.tg_newslot = ExecGetTriggerNewSlot(estate, relInfo);
- ItemPointerCopy(&(event->ate_ctid2), &(tuple2.t_self));
- if (!heap_fetch(rel, &(tuple2.t_self), SnapshotAny, &tuple2, &buffer, NULL))
+ if (!table_fetch_row_version(rel, &(event->ate_ctid2), SnapshotAny, LocTriggerData.tg_newslot, NULL))
elog(ERROR, "failed to fetch tuple2 for AFTER trigger");
- ExecStorePinnedBufferHeapTuple(&tuple2,
- LocTriggerData.tg_newslot,
- buffer);
LocTriggerData.tg_newtuple =
- ExecFetchSlotHeapTuple(LocTriggerData.tg_newslot, false,
- &should_free_new);
+ ExecFetchSlotHeapTuple(LocTriggerData.tg_newslot, false, &should_free_new);
}
else
{
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 8723e32dd58..e70e9f08e42 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -2638,17 +2638,9 @@ EvalPlanQualFetchRowMarks(EPQState *epqstate)
else
{
/* ordinary table, fetch the tuple */
- HeapTupleData tuple;
- Buffer buffer;
-
- tuple.t_self = *((ItemPointer) DatumGetPointer(datum));
- if (!heap_fetch(erm->relation, &tuple.t_self, SnapshotAny,
- &tuple, &buffer, NULL))
+ if (!table_fetch_row_version(erm->relation, (ItemPointer) DatumGetPointer(datum),
+ SnapshotAny, slot, NULL))
elog(ERROR, "failed to fetch tuple for EvalPlanQual recheck");
-
- /* successful, store tuple */
- ExecStorePinnedBufferHeapTuple(&tuple, slot, buffer);
- ExecMaterializeSlot(slot);
}
}
else
diff --git a/src/backend/executor/nodeLockRows.c b/src/backend/executor/nodeLockRows.c
index 91f46b88ed8..aedc5297e3b 100644
--- a/src/backend/executor/nodeLockRows.c
+++ b/src/backend/executor/nodeLockRows.c
@@ -21,7 +21,6 @@
#include "postgres.h"
-#include "access/heapam.h"
#include "access/htup_details.h"
#include "access/tableam.h"
#include "access/xact.h"
@@ -275,8 +274,6 @@ lnext:
ExecAuxRowMark *aerm = (ExecAuxRowMark *) lfirst(lc);
ExecRowMark *erm = aerm->rowmark;
TupleTableSlot *markSlot;
- HeapTupleData tuple;
- Buffer buffer;
markSlot = EvalPlanQualSlot(&node->lr_epqstate, erm->relation, erm->rti);
@@ -297,12 +294,9 @@ lnext:
Assert(ItemPointerIsValid(&(erm->curCtid)));
/* okay, fetch the tuple */
- tuple.t_self = erm->curCtid;
- if (!heap_fetch(erm->relation, &tuple.t_self, SnapshotAny, &tuple, &buffer,
- NULL))
+ if (!table_fetch_row_version(erm->relation, &erm->curCtid, SnapshotAny, markSlot,
+ NULL))
elog(ERROR, "failed to fetch tuple for EvalPlanQual recheck");
- ExecStorePinnedBufferHeapTuple(&tuple, markSlot, buffer);
- ExecMaterializeSlot(markSlot);
/* successful, use tuple in slot */
}
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index 5b079d8302a..b4247ec33b4 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -230,18 +230,15 @@ ExecCheckTIDVisible(EState *estate,
TupleTableSlot *tempSlot)
{
Relation rel = relinfo->ri_RelationDesc;
- Buffer buffer;
- HeapTupleData tuple;
/* Redundantly check isolation level */
if (!IsolationUsesXactSnapshot())
return;
- tuple.t_self = *tid;
- if (!heap_fetch(rel, &tuple.t_self, SnapshotAny, &tuple, &buffer, NULL))
+ if (!table_fetch_row_version(rel, tid, SnapshotAny, tempSlot, NULL))
elog(ERROR, "failed to fetch conflicting tuple for ON CONFLICT");
- ExecStorePinnedBufferHeapTuple(&tuple, tempSlot, buffer);
ExecCheckHeapTupleVisible(estate, rel, tempSlot);
+ ExecClearTuple(tempSlot);
}
/* ----------------------------------------------------------------
@@ -851,21 +848,9 @@ ldelete:;
}
else
{
- BufferHeapTupleTableSlot *bslot;
- HeapTuple deltuple;
- Buffer buffer;
-
- Assert(TTS_IS_BUFFERTUPLE(slot));
- ExecClearTuple(slot);
- bslot = (BufferHeapTupleTableSlot *) slot;
- deltuple = &bslot->base.tupdata;
-
- deltuple->t_self = *tupleid;
- if (!heap_fetch(resultRelationDesc, &deltuple->t_self, SnapshotAny,
- deltuple, &buffer, NULL))
+ if (!table_fetch_row_version(resultRelationDesc, tupleid, SnapshotAny,
+ slot, NULL))
elog(ERROR, "failed to fetch deleted tuple for DELETE RETURNING");
-
- ExecStorePinnedBufferHeapTuple(deltuple, slot, buffer);
}
}
diff --git a/src/backend/executor/nodeTidscan.c b/src/backend/executor/nodeTidscan.c
index b819cf2383e..7d496cc4105 100644
--- a/src/backend/executor/nodeTidscan.c
+++ b/src/backend/executor/nodeTidscan.c
@@ -310,7 +310,6 @@ TidNext(TidScanState *node)
Relation heapRelation;
HeapTuple tuple;
TupleTableSlot *slot;
- Buffer buffer = InvalidBuffer;
ItemPointerData *tidList;
int numTids;
bool bBackward;
@@ -376,19 +375,9 @@ TidNext(TidScanState *node)
if (node->tss_isCurrentOf)
heap_get_latest_tid(heapRelation, snapshot, &tuple->t_self);
- if (heap_fetch(heapRelation, &tuple->t_self, snapshot, tuple, &buffer, NULL))
- {
- /*
- * Store the scanned tuple in the scan tuple slot of the scan
- * state, transferring the pin to the slot.
- */
- ExecStorePinnedBufferHeapTuple(tuple, /* tuple to store */
- slot, /* slot to store in */
- buffer); /* buffer associated with
- * tuple */
-
+ if (table_fetch_row_version(heapRelation, &tuple->t_self, snapshot, slot, NULL))
return slot;
- }
+
/* Bad TID or failed snapshot qual; try next */
if (bBackward)
node->tss_TidPtr--;
diff --git a/src/include/access/tableam.h b/src/include/access/tableam.h
index b34e903d84d..abbd7f63385 100644
--- a/src/include/access/tableam.h
+++ b/src/include/access/tableam.h
@@ -218,6 +218,13 @@ typedef struct TableAmRoutine
* ------------------------------------------------------------------------
*/
+
+ bool (*tuple_fetch_row_version) (Relation rel,
+ ItemPointer tid,
+ Snapshot snapshot,
+ TupleTableSlot *slot,
+ Relation stats_relation);
+
/*
* Does the tuple in `slot` satisfy `snapshot`? The slot needs to be of
* the appropriate type for the AM.
@@ -542,6 +549,24 @@ table_index_fetch_tuple(struct IndexFetchTableData *scan,
* ------------------------------------------------------------------------
*/
+
+/*
+ * table_fetch_row_version - retrieve tuple with given tid
+ *
+ * XXX: This shouldn't just take a tid, but tid + additional information
+ */
+static inline bool
+table_fetch_row_version(Relation rel,
+ ItemPointer tid,
+ Snapshot snapshot,
+ TupleTableSlot *slot,
+ Relation stats_relation)
+{
+ return rel->rd_tableam->tuple_fetch_row_version(rel, tid,
+ snapshot, slot,
+ stats_relation);
+}
+
/*
* Return true iff tuple in slot satisfies the snapshot.
*
--
2.21.0.dirty
--yvn3crbc4qf4vymf
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="v18-0005-tableam-Add-use-tableam_fetch_follow_check.patch"
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_dump versus hash partitioning
@ 2023-03-13 23:39 Tom Lane <[email protected]>
2023-03-16 09:03 ` Re: pg_dump versus hash partitioning Julien Rouhaud <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2023-03-13 23:39 UTC (permalink / raw)
To: Julien Rouhaud <[email protected]>; +Cc: Andrew Dunstan <[email protected]>; Alvaro Herrera <[email protected]>; David Rowley <[email protected]>; Peter Geoghegan <[email protected]>; Robert Haas <[email protected]>; [email protected]; Andrew <[email protected]>
Julien Rouhaud <[email protected]> writes:
> On Sun, Mar 12, 2023 at 03:46:52PM -0400, Tom Lane wrote:
>> The trick is to detect in pg_restore whether pg_dump chose to do
>> load-via-partition-root.
> Given that this approach wouldn't help with existing dump files (at least if
> using COPY, in any case the one using INSERT are doomed), so I'm slightly in
> favor of the first approach, and later add an easy and non magic incantation
> way to produce dumps that don't depend on partitioning.
Yeah, we need to do both. Attached find an updated patch series:
0001: TAP test that exhibits both this deadlock problem and the
different-hash-codes problem. I'm not sure if we want to commit
this, or if it should be in exactly this form --- the second set
of tests with a manual --load-via-partition-root switch will be
pretty redundant after this patch series.
0002: Make pg_restore detect load-via-partition-root by examining the
COPY commands embedded in the dump, and skip the TRUNCATE if so,
thereby fixing the deadlock issue. This is the best we can do for
legacy dump files, I think, but it should be good enough.
0003: Also detect load-via-partition-root by adding a label in the
dump. This is a more bulletproof solution going forward.
0004-0006: same as previous patches, but rebased over these.
This gets us to a place where the new TAP test passes.
I've not done anything about modifying the documentation, but I still
think we could remove the warning label on --load-via-partition-root.
regards, tom lane
Attachments:
[text/x-diff] v2-0001-Create-a-test-case-for-dump-and-restore-of-hashed.patch (2.9K, ../../[email protected]/2-v2-0001-Create-a-test-case-for-dump-and-restore-of-hashed.patch)
download | inline diff:
From 33acd81f90ccd1ebdd75fbdf58024edb8f10f6a1 Mon Sep 17 00:00:00 2001
From: Tom Lane <[email protected]>
Date: Mon, 13 Mar 2023 19:01:42 -0400
Subject: [PATCH v2 1/6] Create a test case for dump-and-restore of hashed-enum
partitioning.
I'm not sure we want to commit this, at least not in this form,
but it's able to demonstrate both the deadlock problem (when using
--load-via-partition-root) and the change-of-hash-code problem
(when not).
---
src/bin/pg_dump/meson.build | 1 +
src/bin/pg_dump/t/004_pg_dump_parallel.pl | 66 +++++++++++++++++++++++
2 files changed, 67 insertions(+)
create mode 100644 src/bin/pg_dump/t/004_pg_dump_parallel.pl
diff --git a/src/bin/pg_dump/meson.build b/src/bin/pg_dump/meson.build
index ab4c25c781..b2fb7ac77f 100644
--- a/src/bin/pg_dump/meson.build
+++ b/src/bin/pg_dump/meson.build
@@ -96,6 +96,7 @@ tests += {
't/001_basic.pl',
't/002_pg_dump.pl',
't/003_pg_dump_with_server.pl',
+ 't/004_pg_dump_parallel.pl',
't/010_dump_connstr.pl',
],
},
diff --git a/src/bin/pg_dump/t/004_pg_dump_parallel.pl b/src/bin/pg_dump/t/004_pg_dump_parallel.pl
new file mode 100644
index 0000000000..c3f7d20b13
--- /dev/null
+++ b/src/bin/pg_dump/t/004_pg_dump_parallel.pl
@@ -0,0 +1,66 @@
+
+# Copyright (c) 2021-2023, PostgreSQL Global Development Group
+
+use strict;
+use warnings;
+
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+my $dbname1 = 'regression_src';
+my $dbname2 = 'regression_dest1';
+my $dbname3 = 'regression_dest2';
+
+my $node = PostgreSQL::Test::Cluster->new('main');
+$node->init;
+$node->start;
+
+my $backupdir = $node->backup_dir;
+
+$node->run_log([ 'createdb', $dbname1 ]);
+$node->run_log([ 'createdb', $dbname2 ]);
+$node->run_log([ 'createdb', $dbname3 ]);
+
+$node->safe_psql(
+ $dbname1,
+ qq{
+create type digit as enum ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9');
+create table t0 (en digit, data int) partition by hash(en);
+create table t0_p1 partition of t0 for values with (modulus 3, remainder 0);
+create table t0_p2 partition of t0 for values with (modulus 3, remainder 1);
+create table t0_p3 partition of t0 for values with (modulus 3, remainder 2);
+insert into t0 select (x%10)::text::digit, x from generate_series(1,1000) x;
+ });
+
+$node->command_ok(
+ [
+ 'pg_dump', '-Fd', '--no-sync', '-j2', '-f', "$backupdir/dump1",
+ $node->connstr($dbname1)
+ ],
+ 'parallel dump');
+
+$node->command_ok(
+ [
+ 'pg_restore', '-v',
+ '-d', $node->connstr($dbname2),
+ '-j3', "$backupdir/dump1"
+ ],
+ 'parallel restore');
+
+$node->command_ok(
+ [
+ 'pg_dump', '-Fd', '--no-sync', '-j2', '-f', "$backupdir/dump2",
+ '--load-via-partition-root', $node->connstr($dbname1)
+ ],
+ 'parallel dump via partition root');
+
+$node->command_ok(
+ [
+ 'pg_restore', '-v',
+ '-d', $node->connstr($dbname3),
+ '-j3', "$backupdir/dump2"
+ ],
+ 'parallel restore via partition root');
+
+done_testing();
--
2.31.1
[text/x-diff] v2-0002-Avoid-TRUNCATE-when-restoring-load-via-partition-.patch (4.5K, ../../[email protected]/3-v2-0002-Avoid-TRUNCATE-when-restoring-load-via-partition-.patch)
download | inline diff:
From 685b4a1c0aaa59b2b6047051d082838b88528587 Mon Sep 17 00:00:00 2001
From: Tom Lane <[email protected]>
Date: Mon, 13 Mar 2023 19:05:05 -0400
Subject: [PATCH v2 2/6] Avoid TRUNCATE when restoring load-via-partition-root
data.
This fixes the deadlock problem, allowing 004_pg_dump_parallel.pl to
succeed in the test where there's a manual --load-via-partition-root
switch. It relies on the dump having used COPY commands, though.
---
src/bin/pg_dump/pg_backup_archiver.c | 61 ++++++++++++++++++++++++----
1 file changed, 54 insertions(+), 7 deletions(-)
diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c
index 61ebb8fe85..cb3a2c21f5 100644
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -86,6 +86,7 @@ static RestorePass _tocEntryRestorePass(TocEntry *te);
static bool _tocEntryIsACL(TocEntry *te);
static void _disableTriggersIfNecessary(ArchiveHandle *AH, TocEntry *te);
static void _enableTriggersIfNecessary(ArchiveHandle *AH, TocEntry *te);
+static bool is_load_via_partition_root(TocEntry *te);
static void buildTocEntryArrays(ArchiveHandle *AH);
static void _moveBefore(TocEntry *pos, TocEntry *te);
static int _discoverArchiveFormat(ArchiveHandle *AH);
@@ -887,6 +888,8 @@ restore_toc_entry(ArchiveHandle *AH, TocEntry *te, bool is_parallel)
}
else
{
+ bool use_truncate;
+
_disableTriggersIfNecessary(AH, te);
/* Select owner and schema as necessary */
@@ -898,13 +901,23 @@ restore_toc_entry(ArchiveHandle *AH, TocEntry *te, bool is_parallel)
/*
* In parallel restore, if we created the table earlier in
- * the run then we wrap the COPY in a transaction and
- * precede it with a TRUNCATE. If archiving is not on
- * this prevents WAL-logging the COPY. This obtains a
- * speedup similar to that from using single_txn mode in
- * non-parallel restores.
+ * the run and we are not restoring a
+ * load-via-partition-root data item then we wrap the COPY
+ * in a transaction and precede it with a TRUNCATE. If
+ * archiving is not on this prevents WAL-logging the COPY.
+ * This obtains a speedup similar to that from using
+ * single_txn mode in non-parallel restores.
+ *
+ * We mustn't do this for load-via-partition-root cases
+ * because some data might get moved across partition
+ * boundaries, risking deadlock and/or loss of previously
+ * loaded data. (We assume that all partitions of a
+ * partitioned table will be treated the same way.)
*/
- if (is_parallel && te->created)
+ use_truncate = is_parallel && te->created &&
+ !is_load_via_partition_root(te);
+
+ if (use_truncate)
{
/*
* Parallel restore is always talking directly to a
@@ -942,7 +955,7 @@ restore_toc_entry(ArchiveHandle *AH, TocEntry *te, bool is_parallel)
AH->outputKind = OUTPUT_SQLCMDS;
/* close out the transaction started above */
- if (is_parallel && te->created)
+ if (use_truncate)
CommitTransaction(&AH->public);
_enableTriggersIfNecessary(AH, te);
@@ -1036,6 +1049,40 @@ _enableTriggersIfNecessary(ArchiveHandle *AH, TocEntry *te)
fmtQualifiedId(te->namespace, te->tag));
}
+/*
+ * Detect whether a TABLE DATA TOC item is performing "load via partition
+ * root", that is the target table is an ancestor partition rather than the
+ * table the TOC item is nominally for.
+ *
+ * In newer archive files this can be detected by checking for a special
+ * comment placed in te->defn. In older files we have to fall back to seeing
+ * if the COPY statement targets the named table or some other one. This
+ * will not work for data dumped as INSERT commands, so we could give a false
+ * negative in that case; fortunately, that's a rarely-used option.
+ */
+static bool
+is_load_via_partition_root(TocEntry *te)
+{
+ if (te->copyStmt && *te->copyStmt)
+ {
+ PQExpBuffer copyStmt = createPQExpBuffer();
+ bool result;
+
+ /*
+ * Build the initial part of the COPY as it would appear if the
+ * nominal target table is the actual target. If we see anything
+ * else, it must be a load-via-partition-root case.
+ */
+ appendPQExpBuffer(copyStmt, "COPY %s ",
+ fmtQualifiedId(te->namespace, te->tag));
+ result = strncmp(te->copyStmt, copyStmt->data, copyStmt->len) != 0;
+ destroyPQExpBuffer(copyStmt);
+ return result;
+ }
+ /* Assume it's not load-via-partition-root */
+ return false;
+}
+
/*
* This is a routine that is part of the dumper interface, hence the 'Archive*' parameter.
*/
--
2.31.1
[text/x-diff] v2-0003-Add-a-marker-to-TABLE-DATA-items-using-load-via-p.patch (4.0K, ../../[email protected]/4-v2-0003-Add-a-marker-to-TABLE-DATA-items-using-load-via-p.patch)
download | inline diff:
From 28b122cb9be438e08e3754672559a9e59ea39711 Mon Sep 17 00:00:00 2001
From: Tom Lane <[email protected]>
Date: Mon, 13 Mar 2023 19:08:40 -0400
Subject: [PATCH v2 3/6] Add a marker to TABLE DATA items using
load-via-partition-root.
Set the te->defn field of such an item to a comment like
-- load via partition root <table name>
This provides a mechanism for load-via-partition-root detection
that works even with --inserts mode, and as a side benefit the
dump contents are a bit less confusing. We still need the COPY
test to work with old dump files, though.
---
src/bin/pg_dump/pg_backup_archiver.c | 11 ++++++--
src/bin/pg_dump/pg_dump.c | 39 ++++++++++++++++------------
2 files changed, 31 insertions(+), 19 deletions(-)
diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c
index cb3a2c21f5..1ecd719db2 100644
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -1063,6 +1063,9 @@ _enableTriggersIfNecessary(ArchiveHandle *AH, TocEntry *te)
static bool
is_load_via_partition_root(TocEntry *te)
{
+ if (te->defn &&
+ strncmp(te->defn, "-- load via partition root ", 27) == 0)
+ return true;
if (te->copyStmt && *te->copyStmt)
{
PQExpBuffer copyStmt = createPQExpBuffer();
@@ -3002,8 +3005,12 @@ _tocEntryRequired(TocEntry *te, teSection curSection, ArchiveHandle *AH)
res = res & ~REQ_DATA;
}
- /* If there's no definition command, there's no schema component */
- if (!te->defn || !te->defn[0])
+ /*
+ * If there's no definition command, there's no schema component. Treat
+ * "load via partition root" comments as not schema.
+ */
+ if (!te->defn || !te->defn[0] ||
+ strncmp(te->defn, "-- load via partition root ", 27) == 0)
res = res & ~REQ_SCHEMA;
/*
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 4217908f84..b0376ee56c 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -2443,34 +2443,38 @@ dumpTableData(Archive *fout, const TableDataInfo *tdinfo)
PQExpBuffer copyBuf = createPQExpBuffer();
PQExpBuffer clistBuf = createPQExpBuffer();
DataDumperPtr dumpFn;
+ char *tdDefn = NULL;
char *copyStmt;
const char *copyFrom;
/* We had better have loaded per-column details about this table */
Assert(tbinfo->interesting);
+ /*
+ * When load-via-partition-root is set, get the root table name for the
+ * partition table, so that we can reload data through the root table.
+ * Then construct a comment to be inserted into the TOC entry's defn
+ * field, so that such cases can be identified reliably.
+ */
+ if (dopt->load_via_partition_root && tbinfo->ispartition)
+ {
+ TableInfo *parentTbinfo;
+
+ parentTbinfo = getRootTableInfo(tbinfo);
+ copyFrom = fmtQualifiedDumpable(parentTbinfo);
+ printfPQExpBuffer(copyBuf, "-- load via partition root %s",
+ copyFrom);
+ tdDefn = pg_strdup(copyBuf->data);
+ }
+ else
+ copyFrom = fmtQualifiedDumpable(tbinfo);
+
if (dopt->dump_inserts == 0)
{
/* Dump/restore using COPY */
dumpFn = dumpTableData_copy;
-
- /*
- * When load-via-partition-root is set, get the root table name for
- * the partition table, so that we can reload data through the root
- * table.
- */
- if (dopt->load_via_partition_root && tbinfo->ispartition)
- {
- TableInfo *parentTbinfo;
-
- parentTbinfo = getRootTableInfo(tbinfo);
- copyFrom = fmtQualifiedDumpable(parentTbinfo);
- }
- else
- copyFrom = fmtQualifiedDumpable(tbinfo);
-
/* must use 2 steps here 'cause fmtId is nonreentrant */
- appendPQExpBuffer(copyBuf, "COPY %s ",
+ printfPQExpBuffer(copyBuf, "COPY %s ",
copyFrom);
appendPQExpBuffer(copyBuf, "%s FROM stdin;\n",
fmtCopyColumnList(tbinfo, clistBuf));
@@ -2498,6 +2502,7 @@ dumpTableData(Archive *fout, const TableDataInfo *tdinfo)
.owner = tbinfo->rolname,
.description = "TABLE DATA",
.section = SECTION_DATA,
+ .createStmt = tdDefn,
.copyStmt = copyStmt,
.deps = &(tbinfo->dobj.dumpId),
.nDeps = 1,
--
2.31.1
[text/x-diff] v2-0004-Force-load-via-partition-root-for-hash-partitioni.patch (9.0K, ../../[email protected]/5-v2-0004-Force-load-via-partition-root-for-hash-partitioni.patch)
download | inline diff:
From e68c58928285679663dcea32ea98409a31f15a5a Mon Sep 17 00:00:00 2001
From: Tom Lane <[email protected]>
Date: Mon, 13 Mar 2023 19:11:44 -0400
Subject: [PATCH v2 4/6] Force load-via-partition-root for hash partitioning on
an enum column.
Without this, dump and restore will almost always fail because the
enum values will receive different OIDs in the destination database,
and their hash codes will therefore be different. (Improving the
hash algorithm would not make this situation better, and would indeed
break other cases as well.) This allows 004_pg_dump_parallel.pl to
pass in full.
Discussion: https://postgr.es/m/[email protected]
---
src/bin/pg_dump/common.c | 18 +++---
src/bin/pg_dump/pg_dump.c | 122 +++++++++++++++++++++++++++++++++++---
src/bin/pg_dump/pg_dump.h | 2 +
3 files changed, 125 insertions(+), 17 deletions(-)
diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c
index a2b74901e4..44558b60e8 100644
--- a/src/bin/pg_dump/common.c
+++ b/src/bin/pg_dump/common.c
@@ -230,6 +230,9 @@ getSchemaData(Archive *fout, int *numTablesPtr)
pg_log_info("flagging inherited columns in subtables");
flagInhAttrs(fout->dopt, tblinfo, numTables);
+ pg_log_info("reading partitioning data");
+ getPartitioningInfo(fout);
+
pg_log_info("reading indexes");
getIndexes(fout, tblinfo, numTables);
@@ -285,7 +288,6 @@ static void
flagInhTables(Archive *fout, TableInfo *tblinfo, int numTables,
InhInfo *inhinfo, int numInherits)
{
- DumpOptions *dopt = fout->dopt;
int i,
j;
@@ -301,18 +303,18 @@ flagInhTables(Archive *fout, TableInfo *tblinfo, int numTables,
continue;
/*
- * Normally, we don't bother computing anything for non-target tables,
- * but if load-via-partition-root is specified, we gather information
- * on every partition in the system so that getRootTableInfo can trace
- * from any given to leaf partition all the way up to the root. (We
- * don't need to mark them as interesting for getTableAttrs, though.)
+ * Normally, we don't bother computing anything for non-target tables.
+ * However, we must find the parents of non-root partitioned tables in
+ * any case, so that we can trace from leaf partitions up to the root
+ * (in case a leaf is to be dumped but its parents are not). We need
+ * not mark such parents interesting for getTableAttrs, though.
*/
if (!tblinfo[i].dobj.dump)
{
mark_parents = false;
- if (!dopt->load_via_partition_root ||
- !tblinfo[i].ispartition)
+ if (!(tblinfo[i].relkind == RELKIND_PARTITIONED_TABLE &&
+ tblinfo[i].ispartition))
find_parents = false;
}
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index b0376ee56c..7f40b455af 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -320,6 +320,7 @@ static void appendReloptionsArrayAH(PQExpBuffer buffer, const char *reloptions,
static char *get_synchronized_snapshot(Archive *fout);
static void setupDumpWorker(Archive *AH);
static TableInfo *getRootTableInfo(const TableInfo *tbinfo);
+static bool forcePartitionRootLoad(const TableInfo *tbinfo);
int
@@ -2227,11 +2228,13 @@ dumpTableData_insert(Archive *fout, const void *dcontext)
insertStmt = createPQExpBuffer();
/*
- * When load-via-partition-root is set, get the root table name
- * for the partition table, so that we can reload data through the
- * root table.
+ * When load-via-partition-root is set or forced, get the root
+ * table name for the partition table, so that we can reload data
+ * through the root table.
*/
- if (dopt->load_via_partition_root && tbinfo->ispartition)
+ if (tbinfo->ispartition &&
+ (dopt->load_via_partition_root ||
+ forcePartitionRootLoad(tbinfo)))
targettab = getRootTableInfo(tbinfo);
else
targettab = tbinfo;
@@ -2429,6 +2432,35 @@ getRootTableInfo(const TableInfo *tbinfo)
return parentTbinfo;
}
+/*
+ * forcePartitionRootLoad
+ * Check if we must force load_via_partition_root for this partition.
+ *
+ * This is required if any level of ancestral partitioned table has an
+ * unsafe partitioning scheme.
+ */
+static bool
+forcePartitionRootLoad(const TableInfo *tbinfo)
+{
+ TableInfo *parentTbinfo;
+
+ Assert(tbinfo->ispartition);
+ Assert(tbinfo->numParents == 1);
+
+ parentTbinfo = tbinfo->parents[0];
+ if (parentTbinfo->unsafe_partitions)
+ return true;
+ while (parentTbinfo->ispartition)
+ {
+ Assert(parentTbinfo->numParents == 1);
+ parentTbinfo = parentTbinfo->parents[0];
+ if (parentTbinfo->unsafe_partitions)
+ return true;
+ }
+
+ return false;
+}
+
/*
* dumpTableData -
* dump the contents of a single table
@@ -2451,12 +2483,14 @@ dumpTableData(Archive *fout, const TableDataInfo *tdinfo)
Assert(tbinfo->interesting);
/*
- * When load-via-partition-root is set, get the root table name for the
- * partition table, so that we can reload data through the root table.
- * Then construct a comment to be inserted into the TOC entry's defn
- * field, so that such cases can be identified reliably.
+ * When load-via-partition-root is set or forced, get the root table name
+ * for the partition table, so that we can reload data through the root
+ * table. Then construct a comment to be inserted into the TOC entry's
+ * defn field, so that such cases can be identified reliably.
*/
- if (dopt->load_via_partition_root && tbinfo->ispartition)
+ if (tbinfo->ispartition &&
+ (dopt->load_via_partition_root ||
+ forcePartitionRootLoad(tbinfo)))
{
TableInfo *parentTbinfo;
@@ -6764,6 +6798,76 @@ getInherits(Archive *fout, int *numInherits)
return inhinfo;
}
+/*
+ * getPartitioningInfo
+ * get information about partitioning
+ *
+ * For the most part, we only collect partitioning info about tables we
+ * intend to dump. However, this function has to consider all partitioned
+ * tables in the database, because we need to know about parents of partitions
+ * we are going to dump even if the parents themselves won't be dumped.
+ *
+ * Specifically, what we need to know is whether each partitioned table
+ * has an "unsafe" partitioning scheme that requires us to force
+ * load-via-partition-root mode for its children. Currently the only case
+ * for which we force that is hash partitioning on enum columns, since the
+ * hash codes depend on enum value OIDs which won't be replicated across
+ * dump-and-reload. There are other cases in which load-via-partition-root
+ * might be necessary, but we expect users to cope with them.
+ */
+void
+getPartitioningInfo(Archive *fout)
+{
+ PQExpBuffer query;
+ PGresult *res;
+ int ntups;
+
+ /* no partitions before v10 */
+ if (fout->remoteVersion < 100000)
+ return;
+ /* needn't bother if schema-only dump */
+ if (fout->dopt->schemaOnly)
+ return;
+
+ query = createPQExpBuffer();
+
+ /*
+ * Unsafe partitioning schemes are exactly those for which hash enum_ops
+ * appears among the partition opclasses. We needn't check partstrat.
+ *
+ * Note that this query may well retrieve info about tables we aren't
+ * going to dump and hence have no lock on. That's okay since we need not
+ * invoke any unsafe server-side functions.
+ */
+ appendPQExpBufferStr(query,
+ "SELECT partrelid FROM pg_partitioned_table WHERE\n"
+ "(SELECT c.oid FROM pg_opclass c JOIN pg_am a "
+ "ON c.opcmethod = a.oid\n"
+ "WHERE opcname = 'enum_ops' "
+ "AND opcnamespace = 'pg_catalog'::regnamespace "
+ "AND amname = 'hash') = ANY(partclass)");
+
+ res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
+
+ ntups = PQntuples(res);
+
+ for (int i = 0; i < ntups; i++)
+ {
+ Oid tabrelid = atooid(PQgetvalue(res, i, 0));
+ TableInfo *tbinfo;
+
+ tbinfo = findTableByOid(tabrelid);
+ if (tbinfo == NULL)
+ pg_fatal("failed sanity check, table OID %u appearing in pg_partitioned_table not found",
+ tabrelid);
+ tbinfo->unsafe_partitions = true;
+ }
+
+ PQclear(res);
+
+ destroyPQExpBuffer(query);
+}
+
/*
* getIndexes
* get information about every index on a dumpable table
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index cdca0b993d..ffa37265c8 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -318,6 +318,7 @@ typedef struct _tableInfo
bool dummy_view; /* view's real definition must be postponed */
bool postponed_def; /* matview must be postponed into post-data */
bool ispartition; /* is table a partition? */
+ bool unsafe_partitions; /* is it an unsafe partitioned table? */
/*
* These fields are computed only if we decide the table is interesting
@@ -714,6 +715,7 @@ extern ConvInfo *getConversions(Archive *fout, int *numConversions);
extern TableInfo *getTables(Archive *fout, int *numTables);
extern void getOwnedSeqs(Archive *fout, TableInfo tblinfo[], int numTables);
extern InhInfo *getInherits(Archive *fout, int *numInherits);
+extern void getPartitioningInfo(Archive *fout);
extern void getIndexes(Archive *fout, TableInfo tblinfo[], int numTables);
extern void getExtendedStatistics(Archive *fout);
extern void getConstraints(Archive *fout, TableInfo tblinfo[], int numTables);
--
2.31.1
[text/x-diff] v2-0005-Don-t-create-useless-TableAttachInfo-objects.patch (1.5K, ../../[email protected]/6-v2-0005-Don-t-create-useless-TableAttachInfo-objects.patch)
download | inline diff:
From 5836a653373a382ee3d3b287ab5377230852a554 Mon Sep 17 00:00:00 2001
From: Tom Lane <[email protected]>
Date: Mon, 13 Mar 2023 19:13:35 -0400
Subject: [PATCH v2 5/6] Don't create useless TableAttachInfo objects.
It's silly to create a TableAttachInfo object that we're not
going to dump, when we know perfectly well at creation time
that it won't be dumped.
Discussion: https://postgr.es/m/[email protected]
---
src/bin/pg_dump/common.c | 3 ++-
src/bin/pg_dump/pg_dump.c | 3 ---
2 files changed, 2 insertions(+), 4 deletions(-)
diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c
index 44558b60e8..df48d0bb17 100644
--- a/src/bin/pg_dump/common.c
+++ b/src/bin/pg_dump/common.c
@@ -336,7 +336,8 @@ flagInhTables(Archive *fout, TableInfo *tblinfo, int numTables,
}
/* Create TableAttachInfo object if needed */
- if (tblinfo[i].dobj.dump && tblinfo[i].ispartition)
+ if ((tblinfo[i].dobj.dump & DUMP_COMPONENT_DEFINITION) &&
+ tblinfo[i].ispartition)
{
TableAttachInfo *attachinfo;
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 7f40b455af..a4a06b4a20 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -16120,9 +16120,6 @@ dumpTableAttach(Archive *fout, const TableAttachInfo *attachinfo)
if (dopt->dataOnly)
return;
- if (!(attachinfo->partitionTbl->dobj.dump & DUMP_COMPONENT_DEFINITION))
- return;
-
q = createPQExpBuffer();
if (!fout->is_prepared[PREPQUERY_DUMPTABLEATTACH])
--
2.31.1
[text/x-diff] v2-0006-Simplify-pg_dump-s-creation-of-parent-table-links.patch (6.7K, ../../[email protected]/7-v2-0006-Simplify-pg_dump-s-creation-of-parent-table-links.patch)
download | inline diff:
From dcdf1c516d06afa75759d38e0903b8585b95ef72 Mon Sep 17 00:00:00 2001
From: Tom Lane <[email protected]>
Date: Mon, 13 Mar 2023 19:16:48 -0400
Subject: [PATCH v2 6/6] Simplify pg_dump's creation of parent-table links.
Instead of trying to optimize this by skipping creation of the
links for tables we don't plan to dump, just create them all in
bulk with a single scan over the pg_inherits data. The previous
approach was more or less O(N^2) in the number of pg_inherits
entries, not to mention being way too complicated.
Discussion: https://postgr.es/m/[email protected]
---
src/bin/pg_dump/common.c | 125 ++++++++++++++++----------------------
src/bin/pg_dump/pg_dump.h | 5 +-
2 files changed, 54 insertions(+), 76 deletions(-)
diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c
index df48d0bb17..5d988986ed 100644
--- a/src/bin/pg_dump/common.c
+++ b/src/bin/pg_dump/common.c
@@ -83,8 +83,6 @@ static void flagInhTables(Archive *fout, TableInfo *tblinfo, int numTables,
InhInfo *inhinfo, int numInherits);
static void flagInhIndexes(Archive *fout, TableInfo *tblinfo, int numTables);
static void flagInhAttrs(DumpOptions *dopt, TableInfo *tblinfo, int numTables);
-static void findParentsByOid(TableInfo *self,
- InhInfo *inhinfo, int numInherits);
static int strInArray(const char *pattern, char **arr, int arr_size);
static IndxInfo *findIndexByOid(Oid oid);
@@ -288,45 +286,70 @@ static void
flagInhTables(Archive *fout, TableInfo *tblinfo, int numTables,
InhInfo *inhinfo, int numInherits)
{
+ TableInfo *child = NULL;
+ TableInfo *parent = NULL;
int i,
j;
- for (i = 0; i < numTables; i++)
+ /*
+ * Set up links from child tables to their parents.
+ *
+ * We used to attempt to skip this work for tables that are not to be
+ * dumped; but the optimizable cases are rare in practice, and setting up
+ * these links in bulk is cheaper than the old way. (Note in particular
+ * that it's very rare for a child to have more than one parent.)
+ */
+ for (i = 0; i < numInherits; i++)
{
- bool find_parents = true;
- bool mark_parents = true;
-
- /* Some kinds never have parents */
- if (tblinfo[i].relkind == RELKIND_SEQUENCE ||
- tblinfo[i].relkind == RELKIND_VIEW ||
- tblinfo[i].relkind == RELKIND_MATVIEW)
- continue;
-
/*
- * Normally, we don't bother computing anything for non-target tables.
- * However, we must find the parents of non-root partitioned tables in
- * any case, so that we can trace from leaf partitions up to the root
- * (in case a leaf is to be dumped but its parents are not). We need
- * not mark such parents interesting for getTableAttrs, though.
+ * Skip a hashtable lookup if it's same table as last time. This is
+ * unlikely for the child, but less so for the parent. (Maybe we
+ * should ask the backend for a sorted array to make it more likely?
+ * Not clear the sorting effort would be repaid, though.)
*/
- if (!tblinfo[i].dobj.dump)
+ if (child == NULL ||
+ child->dobj.catId.oid != inhinfo[i].inhrelid)
{
- mark_parents = false;
+ child = findTableByOid(inhinfo[i].inhrelid);
- if (!(tblinfo[i].relkind == RELKIND_PARTITIONED_TABLE &&
- tblinfo[i].ispartition))
- find_parents = false;
+ /*
+ * If we find no TableInfo, assume the pg_inherits entry is for a
+ * partitioned index, which we don't need to track.
+ */
+ if (child == NULL)
+ continue;
}
+ if (parent == NULL ||
+ parent->dobj.catId.oid != inhinfo[i].inhparent)
+ {
+ parent = findTableByOid(inhinfo[i].inhparent);
+ if (parent == NULL)
+ pg_fatal("failed sanity check, parent OID %u of table \"%s\" (OID %u) not found",
+ inhinfo[i].inhparent,
+ child->dobj.name,
+ child->dobj.catId.oid);
+ }
+ /* Add this parent to the child's list of parents. */
+ if (child->numParents > 0)
+ child->parents = pg_realloc_array(child->parents,
+ TableInfo *,
+ child->numParents + 1);
+ else
+ child->parents = pg_malloc_array(TableInfo *, 1);
+ child->parents[child->numParents++] = parent;
+ }
- /* If needed, find all the immediate parent tables. */
- if (find_parents)
- findParentsByOid(&tblinfo[i], inhinfo, numInherits);
-
+ /*
+ * Now consider all child tables and mark parents interesting as needed.
+ */
+ for (i = 0; i < numTables; i++)
+ {
/*
* If needed, mark the parents as interesting for getTableAttrs and
- * getIndexes.
+ * getIndexes. We only need this for direct parents of dumpable
+ * tables.
*/
- if (mark_parents)
+ if (tblinfo[i].dobj.dump)
{
int numParents = tblinfo[i].numParents;
TableInfo **parents = tblinfo[i].parents;
@@ -996,52 +1019,6 @@ findOwningExtension(CatalogId catalogId)
}
-/*
- * findParentsByOid
- * find a table's parents in tblinfo[]
- */
-static void
-findParentsByOid(TableInfo *self,
- InhInfo *inhinfo, int numInherits)
-{
- Oid oid = self->dobj.catId.oid;
- int i,
- j;
- int numParents;
-
- numParents = 0;
- for (i = 0; i < numInherits; i++)
- {
- if (inhinfo[i].inhrelid == oid)
- numParents++;
- }
-
- self->numParents = numParents;
-
- if (numParents > 0)
- {
- self->parents = pg_malloc_array(TableInfo *, numParents);
- j = 0;
- for (i = 0; i < numInherits; i++)
- {
- if (inhinfo[i].inhrelid == oid)
- {
- TableInfo *parent;
-
- parent = findTableByOid(inhinfo[i].inhparent);
- if (parent == NULL)
- pg_fatal("failed sanity check, parent OID %u of table \"%s\" (OID %u) not found",
- inhinfo[i].inhparent,
- self->dobj.name,
- oid);
- self->parents[j++] = parent;
- }
- }
- }
- else
- self->parents = NULL;
-}
-
/*
* parseOidArray
* parse a string of numbers delimited by spaces into a character array
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index ffa37265c8..283cd1a602 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -320,6 +320,9 @@ typedef struct _tableInfo
bool ispartition; /* is table a partition? */
bool unsafe_partitions; /* is it an unsafe partitioned table? */
+ int numParents; /* number of (immediate) parent tables */
+ struct _tableInfo **parents; /* TableInfos of immediate parents */
+
/*
* These fields are computed only if we decide the table is interesting
* (it's either a table to dump, or a direct parent of a dumpable table).
@@ -351,8 +354,6 @@ typedef struct _tableInfo
/*
* Stuff computed only for dumpable tables.
*/
- int numParents; /* number of (immediate) parent tables */
- struct _tableInfo **parents; /* TableInfos of immediate parents */
int numIndexes; /* number of indexes */
struct _indxInfo *indexes; /* indexes */
struct _tableDataInfo *dataObj; /* TableDataInfo, if dumping its data */
--
2.31.1
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_dump versus hash partitioning
2023-03-13 23:39 Re: pg_dump versus hash partitioning Tom Lane <[email protected]>
@ 2023-03-16 09:03 ` Julien Rouhaud <[email protected]>
2023-03-16 12:43 ` Re: pg_dump versus hash partitioning Tom Lane <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Julien Rouhaud @ 2023-03-16 09:03 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Andrew Dunstan <[email protected]>; Alvaro Herrera <[email protected]>; David Rowley <[email protected]>; Peter Geoghegan <[email protected]>; Robert Haas <[email protected]>; [email protected]; Andrew <[email protected]>
On Mon, Mar 13, 2023 at 07:39:12PM -0400, Tom Lane wrote:
> Julien Rouhaud <[email protected]> writes:
> > On Sun, Mar 12, 2023 at 03:46:52PM -0400, Tom Lane wrote:
> >> The trick is to detect in pg_restore whether pg_dump chose to do
> >> load-via-partition-root.
>
> > Given that this approach wouldn't help with existing dump files (at least if
> > using COPY, in any case the one using INSERT are doomed), so I'm slightly in
> > favor of the first approach, and later add an easy and non magic incantation
> > way to produce dumps that don't depend on partitioning.
>
> Yeah, we need to do both. Attached find an updated patch series:
I didn't find a CF entry, is it intended?
> 0001: TAP test that exhibits both this deadlock problem and the
> different-hash-codes problem. I'm not sure if we want to commit
> this, or if it should be in exactly this form --- the second set
> of tests with a manual --load-via-partition-root switch will be
> pretty redundant after this patch series.
I think there should be at least the first set of tests committed. I would
also be happy to see some test with the --insert case, even if those are
technically redundant too, as it's quite cheap to do once you setup the
cluster.
> 0002: Make pg_restore detect load-via-partition-root by examining the
> COPY commands embedded in the dump, and skip the TRUNCATE if so,
> thereby fixing the deadlock issue. This is the best we can do for
> legacy dump files, I think, but it should be good enough.
is_load_via_partition_root():
+ * In newer archive files this can be detected by checking for a special
+ * comment placed in te->defn. In older files we have to fall back to seeing
+ * if the COPY statement targets the named table or some other one. This
+ * will not work for data dumped as INSERT commands, so we could give a false
+ * negative in that case; fortunately, that's a rarely-used option.
I'm not sure if you intend to keep the current 0002 - 0003 separation, but if
yes the part about te->defn and possible fallback should be moved to 0003. In
0002 we're only looking at the COPY statement.
- * the run then we wrap the COPY in a transaction and
- * precede it with a TRUNCATE. If archiving is not on
- * this prevents WAL-logging the COPY. This obtains a
- * speedup similar to that from using single_txn mode in
- * non-parallel restores.
+ * the run and we are not restoring a
+ * load-via-partition-root data item then we wrap the COPY
+ * in a transaction and precede it with a TRUNCATE. If
+ * archiving is not on this prevents WAL-logging the COPY.
+ * This obtains a speedup similar to that from using
+ * single_txn mode in non-parallel restores.
I know you're only inheriting this comment, but isn't it well outdated and not
accurate anymore? I'm assuming that "archiving is not on" was an acceptable
way to mean "wal_level < archive" at some point, but it's now completely
misleading.
Minor nitpicking:
- should the function name prefixed with a "_" like almost all nearby code?
- should there be an assert that the given toc entry is indeed a TABLE DATA?
FWIW it unsurprisingly fixes the problem on my original use case.
> 0003: Also detect load-via-partition-root by adding a label in the
> dump. This is a more bulletproof solution going forward.
>
> 0004-0006: same as previous patches, but rebased over these.
> This gets us to a place where the new TAP test passes.
+getPartitioningInfo(Archive *fout)
+{
+ PQExpBuffer query;
+ PGresult *res;
+ int ntups;
+
+ /* no partitions before v10 */
+ if (fout->remoteVersion < 100000)
+ return;
+ [...]
+ /*
+ * Unsafe partitioning schemes are exactly those for which hash enum_ops
+ * appears among the partition opclasses. We needn't check partstrat.
+ *
+ * Note that this query may well retrieve info about tables we aren't
+ * going to dump and hence have no lock on. That's okay since we need not
+ * invoke any unsafe server-side functions.
+ */
+ appendPQExpBufferStr(query,
+ "SELECT partrelid FROM pg_partitioned_table WHERE\n"
+ "(SELECT c.oid FROM pg_opclass c JOIN pg_am a "
+ "ON c.opcmethod = a.oid\n"
+ "WHERE opcname = 'enum_ops' "
+ "AND opcnamespace = 'pg_catalog'::regnamespace "
+ "AND amname = 'hash') = ANY(partclass)");
Hash partitioning was added with pg11, should we bypass pg10 too with a comment
saying that we only care about hash, at least for the forseeable future?
Other than that, the patchset looks quite good to me, modulo the upcoming doc
changes.
More generally, I also think that forcing --load-via-partition-root for
known unsafe partitioning seems like the best compromise. I'm not sure if we
should have an option to turn it off though.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_dump versus hash partitioning
2023-03-13 23:39 Re: pg_dump versus hash partitioning Tom Lane <[email protected]>
2023-03-16 09:03 ` Re: pg_dump versus hash partitioning Julien Rouhaud <[email protected]>
@ 2023-03-16 12:43 ` Tom Lane <[email protected]>
2023-03-17 02:07 ` Re: pg_dump versus hash partitioning Julien Rouhaud <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2023-03-16 12:43 UTC (permalink / raw)
To: Julien Rouhaud <[email protected]>; +Cc: Andrew Dunstan <[email protected]>; Alvaro Herrera <[email protected]>; David Rowley <[email protected]>; Peter Geoghegan <[email protected]>; Robert Haas <[email protected]>; [email protected]; Andrew <[email protected]>
Julien Rouhaud <[email protected]> writes:
> On Mon, Mar 13, 2023 at 07:39:12PM -0400, Tom Lane wrote:
>> Yeah, we need to do both. Attached find an updated patch series:
> I didn't find a CF entry, is it intended?
Yeah, it's there:
https://commitfest.postgresql.org/42/4226/
> I'm not sure if you intend to keep the current 0002 - 0003 separation, but if
> yes the part about te->defn and possible fallback should be moved to 0003. In
> 0002 we're only looking at the COPY statement.
I was intending to smash it all into one commit --- the separation is
just to ease review.
> I know you're only inheriting this comment, but isn't it well outdated and not
> accurate anymore? I'm assuming that "archiving is not on" was an acceptable
> way to mean "wal_level < archive" at some point, but it's now completely
> misleading.
Sure, want to propose wording?
> Hash partitioning was added with pg11, should we bypass pg10 too with a comment
> saying that we only care about hash, at least for the forseeable future?
Good point. With v10 already out of support, it hardly matters in the
real world, but we might as well not expend cycles when we clearly
needn't.
> More generally, I also think that forcing --load-via-partition-root for
> known unsafe partitioning seems like the best compromise. I'm not sure if we
> should have an option to turn it off though.
I think the odds of that yielding a usable dump are nil, so I don't
see why we should bother.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_dump versus hash partitioning
2023-03-13 23:39 Re: pg_dump versus hash partitioning Tom Lane <[email protected]>
2023-03-16 09:03 ` Re: pg_dump versus hash partitioning Julien Rouhaud <[email protected]>
2023-03-16 12:43 ` Re: pg_dump versus hash partitioning Tom Lane <[email protected]>
@ 2023-03-17 02:07 ` Julien Rouhaud <[email protected]>
2023-03-17 17:44 ` Re: pg_dump versus hash partitioning Tom Lane <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Julien Rouhaud @ 2023-03-17 02:07 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Andrew Dunstan <[email protected]>; Alvaro Herrera <[email protected]>; David Rowley <[email protected]>; Peter Geoghegan <[email protected]>; Robert Haas <[email protected]>; [email protected]; Andrew <[email protected]>
On Thu, Mar 16, 2023 at 08:43:56AM -0400, Tom Lane wrote:
> Julien Rouhaud <[email protected]> writes:
> > On Mon, Mar 13, 2023 at 07:39:12PM -0400, Tom Lane wrote:
> >> Yeah, we need to do both. Attached find an updated patch series:
>
> > I didn't find a CF entry, is it intended?
>
> Yeah, it's there:
>
> https://commitfest.postgresql.org/42/4226/
Ah thanks! I was looking for "pg_dump" in the page.
> > I'm not sure if you intend to keep the current 0002 - 0003 separation, but if
> > yes the part about te->defn and possible fallback should be moved to 0003. In
> > 0002 we're only looking at the COPY statement.
>
> I was intending to smash it all into one commit --- the separation is
> just to ease review.
Ok, no problem then and I agree it's better to squash them.
> > I know you're only inheriting this comment, but isn't it well outdated and not
> > accurate anymore? I'm assuming that "archiving is not on" was an acceptable
> > way to mean "wal_level < archive" at some point, but it's now completely
> > misleading.
>
> Sure, want to propose wording?
Just mentioning the exact wal_level, something like
* [...]. If wal_level is set to minimal this prevents
* WAL-logging the COPY. This obtains a speedup similar to
* [...]
> > More generally, I also think that forcing --load-via-partition-root for
> > known unsafe partitioning seems like the best compromise. I'm not sure if we
> > should have an option to turn it off though.
>
> I think the odds of that yielding a usable dump are nil, so I don't
> see why we should bother.
No objection from me.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_dump versus hash partitioning
2023-03-13 23:39 Re: pg_dump versus hash partitioning Tom Lane <[email protected]>
2023-03-16 09:03 ` Re: pg_dump versus hash partitioning Julien Rouhaud <[email protected]>
2023-03-16 12:43 ` Re: pg_dump versus hash partitioning Tom Lane <[email protected]>
2023-03-17 02:07 ` Re: pg_dump versus hash partitioning Julien Rouhaud <[email protected]>
@ 2023-03-17 17:44 ` Tom Lane <[email protected]>
2023-03-18 01:06 ` Re: pg_dump versus hash partitioning Julien Rouhaud <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2023-03-17 17:44 UTC (permalink / raw)
To: Julien Rouhaud <[email protected]>; +Cc: Andrew Dunstan <[email protected]>; Alvaro Herrera <[email protected]>; David Rowley <[email protected]>; Peter Geoghegan <[email protected]>; Robert Haas <[email protected]>; [email protected]; Andrew <[email protected]>
Julien Rouhaud <[email protected]> writes:
> On Thu, Mar 16, 2023 at 08:43:56AM -0400, Tom Lane wrote:
>> I think the odds of that yielding a usable dump are nil, so I don't
>> see why we should bother.
> No objection from me.
OK, pushed with the discussed changes.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_dump versus hash partitioning
2023-03-13 23:39 Re: pg_dump versus hash partitioning Tom Lane <[email protected]>
2023-03-16 09:03 ` Re: pg_dump versus hash partitioning Julien Rouhaud <[email protected]>
2023-03-16 12:43 ` Re: pg_dump versus hash partitioning Tom Lane <[email protected]>
2023-03-17 02:07 ` Re: pg_dump versus hash partitioning Julien Rouhaud <[email protected]>
2023-03-17 17:44 ` Re: pg_dump versus hash partitioning Tom Lane <[email protected]>
@ 2023-03-18 01:06 ` Julien Rouhaud <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Julien Rouhaud @ 2023-03-18 01:06 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Andrew Dunstan <[email protected]>; Alvaro Herrera <[email protected]>; David Rowley <[email protected]>; Peter Geoghegan <[email protected]>; Robert Haas <[email protected]>; [email protected]; Andrew <[email protected]>
On Fri, Mar 17, 2023 at 01:44:12PM -0400, Tom Lane wrote:
> Julien Rouhaud <[email protected]> writes:
> > On Thu, Mar 16, 2023 at 08:43:56AM -0400, Tom Lane wrote:
> >> I think the odds of that yielding a usable dump are nil, so I don't
> >> see why we should bother.
>
> > No objection from me.
>
> OK, pushed with the discussed changes.
Great news, thanks a lot!
^ permalink raw reply [nested|flat] 8+ messages in thread
* [PATCH v14 6/8] Row pattern recognition patch (docs).
@ 2024-02-28 13:59 Tatsuo Ishii <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Tatsuo Ishii @ 2024-02-28 13:59 UTC (permalink / raw)
---
doc/src/sgml/advanced.sgml | 80 ++++++++++++++++++++++++++++++++++++
doc/src/sgml/func.sgml | 54 ++++++++++++++++++++++++
doc/src/sgml/ref/select.sgml | 38 ++++++++++++++++-
3 files changed, 170 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/advanced.sgml b/doc/src/sgml/advanced.sgml
index 755c9f1485..cf18dd887e 100644
--- a/doc/src/sgml/advanced.sgml
+++ b/doc/src/sgml/advanced.sgml
@@ -537,6 +537,86 @@ WHERE pos < 3;
<literal>rank</literal> less than 3.
</para>
+ <para>
+ Row pattern common syntax can be used to perform row pattern recognition
+ in a query. Row pattern common syntax includes two sub
+ clauses: <literal>DEFINE</literal>
+ and <literal>PATTERN</literal>. <literal>DEFINE</literal> defines
+ definition variables along with an expression. The expression must be a
+ logical expression, which means it must
+ return <literal>TRUE</literal>, <literal>FALSE</literal>
+ or <literal>NULL</literal>. The expression may comprise column references
+ and functions. Window functions, aggregate functions and subqueries are
+ not allowed. An example of <literal>DEFINE</literal> is as follows.
+
+<programlisting>
+DEFINE
+ LOWPRICE AS price <= 100,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+</programlisting>
+
+ Note that <function>PREV</function> returns the price column in the
+ previous row if it's called in a context of row pattern recognition. So in
+ the second line the definition variable "UP" is <literal>TRUE</literal>
+ when the price column in the current row is greater than the price column
+ in the previous row. Likewise, "DOWN" is <literal>TRUE</literal> when when
+ the price column in the current row is lower than the price column in the
+ previous row.
+ </para>
+ <para>
+ Once <literal>DEFINE</literal> exists, <literal>PATTERN</literal> can be
+ used. <literal>PATTERN</literal> defines a sequence of rows that satisfies
+ certain conditions. For example following <literal>PATTERN</literal>
+ defines that a row starts with the condition "LOWPRICE", then one or more
+ rows satisfy "UP" and finally one or more rows satisfy "DOWN". Note that
+ "+" means one or more matches. Also you can use "*", which means zero or
+ more matches. If a sequence of rows which satisfies the PATTERN is found,
+ in the starting row of the sequence of rows all window functions and
+ aggregates are shown in the target list. Note that aggregations only look
+ into the matched rows, rather than whole frame. In the second or
+ subsequent rows all window functions and aggregates are NULL. For rows
+ that do not match the PATTERN, all window functions and aggregates are
+ shown AS NULL too, except count which shows 0. This is because the
+ unmatched rows are in an empty frame. Example of
+ a <literal>SELECT</literal> using the <literal>DEFINE</literal>
+ and <literal>PATTERN</literal> clause is as follows.
+
+<programlisting>
+SELECT company, tdate, price,
+ first_value(price) OVER w,
+ max(price) OVER w,
+ count(price) OVER w
+FROM stock,
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ INITIAL
+ PATTERN (LOWPRICE UP+ DOWN+)
+ DEFINE
+ LOWPRICE AS price <= 100,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+</programlisting>
+<screen>
+ company | tdate | price | first_value | max | count
+----------+------------+-------+-------------+-----+-------
+ company1 | 2023-07-01 | 100 | 100 | 200 | 4
+ company1 | 2023-07-02 | 200 | | |
+ company1 | 2023-07-03 | 150 | | |
+ company1 | 2023-07-04 | 140 | | |
+ company1 | 2023-07-05 | 150 | | | 0
+ company1 | 2023-07-06 | 90 | 90 | 130 | 4
+ company1 | 2023-07-07 | 110 | | |
+ company1 | 2023-07-08 | 130 | | |
+ company1 | 2023-07-09 | 120 | | |
+ company1 | 2023-07-10 | 130 | | | 0
+</screen>
+ </para>
+
<para>
When a query involves multiple window functions, it is possible to write
out each one with a separate <literal>OVER</literal> clause, but this is
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index e5fa82c161..8dd97501a7 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -22364,6 +22364,7 @@ SELECT count(*) FROM sometable;
returns <literal>NULL</literal> if there is no such row.
</para></entry>
</row>
+
</tbody>
</tgroup>
</table>
@@ -22403,6 +22404,59 @@ SELECT count(*) FROM sometable;
Other frame specifications can be used to obtain other effects.
</para>
+ <para>
+ Row pattern recognition navigation functions are listed in
+ <xref linkend="functions-rpr-navigation-table"/>. These functions
+ can be used to describe DEFINE clause of Row pattern recognition.
+ </para>
+
+ <table id="functions-rpr-navigation-table">
+ <title>Row Pattern Navigation Functions</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ Function
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>prev</primary>
+ </indexterm>
+ <function>prev</function> ( <parameter>value</parameter> <type>anyelement</type> )
+ <returnvalue>anyelement</returnvalue>
+ </para>
+ <para>
+ Returns the column value at the previous row;
+ returns NULL if there is no previous row in the window frame.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>next</primary>
+ </indexterm>
+ <function>next</function> ( <parameter>value</parameter> <type>anyelement</type> )
+ <returnvalue>anyelement</returnvalue>
+ </para>
+ <para>
+ Returns the column value at the next row;
+ returns NULL if there is no next row in the window frame.
+ </para></entry>
+ </row>
+
+ </tbody>
+ </tgroup>
+ </table>
+
<note>
<para>
The SQL standard defines a <literal>RESPECT NULLS</literal> or
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 9917df7839..1575fc2167 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -969,8 +969,8 @@ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceabl
The <replaceable class="parameter">frame_clause</replaceable> can be one of
<synopsis>
-{ RANGE | ROWS | GROUPS } <replaceable>frame_start</replaceable> [ <replaceable>frame_exclusion</replaceable> ]
-{ RANGE | ROWS | GROUPS } BETWEEN <replaceable>frame_start</replaceable> AND <replaceable>frame_end</replaceable> [ <replaceable>frame_exclusion</replaceable> ]
+{ RANGE | ROWS | GROUPS } <replaceable>frame_start</replaceable> [ <replaceable>frame_exclusion</replaceable> ] [row_pattern_common_syntax]
+{ RANGE | ROWS | GROUPS } BETWEEN <replaceable>frame_start</replaceable> AND <replaceable>frame_end</replaceable> [ <replaceable>frame_exclusion</replaceable> ] [row_pattern_common_syntax]
</synopsis>
where <replaceable>frame_start</replaceable>
@@ -1077,6 +1077,40 @@ EXCLUDE NO OTHERS
a given peer group will be in the frame or excluded from it.
</para>
+ <para>
+ The
+ optional <replaceable class="parameter">row_pattern_common_syntax</replaceable>
+ defines the <firstterm>row pattern recognition condition</firstterm> for
+ this
+ window. <replaceable class="parameter">row_pattern_common_syntax</replaceable>
+ includes following subclauses. <literal>AFTER MATCH SKIP PAST LAST
+ ROW</literal> or <literal>AFTER MATCH SKIP TO NEXT ROW</literal> controls
+ how to proceed to next row position after a match
+ found. With <literal>AFTER MATCH SKIP PAST LAST ROW</literal> (the
+ default) next row position is next to the last row of previous match. On
+ the other hand, with <literal>AFTER MATCH SKIP TO NEXT ROW</literal> next
+ row position is always next to the last row of previous
+ match. <literal>DEFINE</literal> defines definition variables along with a
+ boolean expression. <literal>PATTERN</literal> defines a sequence of rows
+ that satisfies certain conditions using variables defined
+ in <literal>DEFINE</literal> clause. If the variable is not defined in
+ the <literal>DEFINE</literal> clause, it is implicitly assumed
+ following is defined in the <literal>DEFINE</literal> clause.
+
+<synopsis>
+<literal>variable_name</literal> AS TRUE
+</synopsis>
+
+ Note that the maximu number of variables defined
+ in <literal>DEFINE</literal> clause is 26.
+
+<synopsis>
+[ AFTER MATCH SKIP PAST LAST ROW | AFTER MATCH SKIP TO NEXT ROW ]
+PATTERN <replaceable class="parameter">pattern_variable_name</replaceable>[+] [, ...]
+DEFINE <replaceable class="parameter">definition_varible_name</replaceable> AS <replaceable class="parameter">expression</replaceable> [, ...]
+</synopsis>
+ </para>
+
<para>
The purpose of a <literal>WINDOW</literal> clause is to specify the
behavior of <firstterm>window functions</firstterm> appearing in the query's
--
2.25.1
----Next_Part(Thu_Feb_29_09_19_54_2024_640)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="v14-0007-Row-pattern-recognition-patch-tests.patch"
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2024-02-28 13:59 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-03-08 00:30 [PATCH v18 04/18] tableam: Add fetch_row_version. Andres Freund <[email protected]>
2023-03-13 23:39 Re: pg_dump versus hash partitioning Tom Lane <[email protected]>
2023-03-16 09:03 ` Re: pg_dump versus hash partitioning Julien Rouhaud <[email protected]>
2023-03-16 12:43 ` Re: pg_dump versus hash partitioning Tom Lane <[email protected]>
2023-03-17 02:07 ` Re: pg_dump versus hash partitioning Julien Rouhaud <[email protected]>
2023-03-17 17:44 ` Re: pg_dump versus hash partitioning Tom Lane <[email protected]>
2023-03-18 01:06 ` Re: pg_dump versus hash partitioning Julien Rouhaud <[email protected]>
2024-02-28 13:59 [PATCH v14 6/8] Row pattern recognition patch (docs). Tatsuo Ishii <[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