public inbox for [email protected]help / color / mirror / Atom feed
Re: EXCLUDE COLLATE in CREATE/ALTER TABLE document 5+ messages / 5 participants [nested] [flat]
* Re: EXCLUDE COLLATE in CREATE/ALTER TABLE document @ 2023-11-10 14:59 jian he <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: jian he @ 2023-11-10 14:59 UTC (permalink / raw) To: shihao zhong <[email protected]>; +Cc: Tom Lane <[email protected]>; [email protected] On Wed, Nov 1, 2023 at 10:30 AM shihao zhong <[email protected]> wrote: > > Thank you for your feedback on my previous patch. I have fixed the issue and attached a new patch for your review. Could you please take a look for it if you have a sec? Thanks > Your patch works fine. you can see it here: https://cirrus-ci.com/task/6481922939944960 in an ideal world, since the doc is already built, we can probably view it as a plain html file just click the ci test result. in src/sgml/ref/create_table.sgml: "Each exclude_element can optionally specify an operator class and/or ordering options; these are described fully under CREATE INDEX." You may need to update this sentence to reflect that exclude_element can also optionally specify collation. ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: EXCLUDE COLLATE in CREATE/ALTER TABLE document @ 2023-11-16 23:25 shihao zhong <[email protected]> parent: jian he <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: shihao zhong @ 2023-11-16 23:25 UTC (permalink / raw) To: jian he <[email protected]>; +Cc: Tom Lane <[email protected]>; [email protected] Hi Jian, Thanks for your comments, a new version is attached. Thanks, Shihao On Fri, Nov 10, 2023 at 9:59 AM jian he <[email protected]> wrote: > On Wed, Nov 1, 2023 at 10:30 AM shihao zhong <[email protected]> > wrote: > > > > Thank you for your feedback on my previous patch. I have fixed the issue > and attached a new patch for your review. Could you please take a look for > it if you have a sec? Thanks > > > > Your patch works fine. you can see it here: > https://cirrus-ci.com/task/6481922939944960 > in an ideal world, since the doc is already built, we can probably > view it as a plain html file just click the ci test result. > > in src/sgml/ref/create_table.sgml: > "Each exclude_element can optionally specify an operator class and/or > ordering options; these are described fully under CREATE INDEX." > > You may need to update this sentence to reflect that exclude_element > can also optionally specify collation. > Attachments: [application/octet-stream] update_document_exclude_with_collate_v3.patch (2.8K, ../../CAGRkXqRs+szVwOjSKF71QGnZDQ62-Z=RqbHqT6QTR1o=ueLjVg@mail.gmail.com/3-update_document_exclude_with_collate_v3.patch) download | inline diff: diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 2c4138e4e9..43f5f0873e 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -135,7 +135,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM <phrase><replaceable class="parameter">exclude_element</replaceable> in an <literal>EXCLUDE</literal> constraint is:</phrase> -{ <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ <replaceable class="parameter">opclass</replaceable> ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] +{ <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] <phrase><replaceable class="parameter">referential_action</replaceable> in a <literal>FOREIGN KEY</literal>/<literal>REFERENCES</literal> constraint is:</phrase> diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index e04a0692c4..a5e768352f 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -105,7 +105,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM <phrase><replaceable class="parameter">exclude_element</replaceable> in an <literal>EXCLUDE</literal> constraint is:</phrase> -{ <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ <replaceable class="parameter">opclass</replaceable> ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] +{ <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] <phrase><replaceable class="parameter">referential_action</replaceable> in a <literal>FOREIGN KEY</literal>/<literal>REFERENCES</literal> constraint is:</phrase> @@ -1097,8 +1097,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM method <replaceable>index_method</replaceable>. The operators are required to be commutative. Each <replaceable class="parameter">exclude_element</replaceable> - can optionally specify an operator class and/or ordering options; - these are described fully under + can optionally specify any of the following: a collation, a + operator class, or ordering options; these are described fully under <xref linkend="sql-createindex"/>. </para> ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: EXCLUDE COLLATE in CREATE/ALTER TABLE document @ 2023-12-01 14:59 Daniel Verite <[email protected]> parent: shihao zhong <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Daniel Verite @ 2023-12-01 14:59 UTC (permalink / raw) To: shihao zhong <[email protected]>; +Cc: Jian He <[email protected]>; Tom Lane <[email protected]>; [email protected] shihao zhong wrote: > Thanks for your comments, a new version is attached. In this hunk: @@ -1097,8 +1097,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM method <replaceable>index_method</replaceable>. The operators are required to be commutative. Each <replaceable class="parameter">exclude_element</replaceable> - can optionally specify an operator class and/or ordering options; - these are described fully under + can optionally specify any of the following: a collation, a + operator class, or ordering options; these are described fully under <xref linkend="sql-createindex"/>. </para> "a" should be "an" as it's followed by "operator class". Also the use of "and/or" in the previous version conveys the fact that operator class and ordering options are not mutually exclusive. But when using "any of the following" in the new text, doesn't it loose that meaning? In case it does, I would suggest the attached diff. Best regards, -- Daniel Vérité https://postgresql.verite.pro/ Twitter: @DanielVerite diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 2c4138e4e9..43f5f0873e 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -135,7 +135,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM <phrase><replaceable class="parameter">exclude_element</replaceable> in an <literal>EXCLUDE</literal> constraint is:</phrase> -{ <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ <replaceable class="parameter">opclass</replaceable> ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] +{ <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] <phrase><replaceable class="parameter">referential_action</replaceable> in a <literal>FOREIGN KEY</literal>/<literal>REFERENCES</literal> constraint is:</phrase> diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index e04a0692c4..277d292aac 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -105,7 +105,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM <phrase><replaceable class="parameter">exclude_element</replaceable> in an <literal>EXCLUDE</literal> constraint is:</phrase> -{ <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ <replaceable class="parameter">opclass</replaceable> ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] +{ <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] <phrase><replaceable class="parameter">referential_action</replaceable> in a <literal>FOREIGN KEY</literal>/<literal>REFERENCES</literal> constraint is:</phrase> @@ -1097,9 +1097,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM method <replaceable>index_method</replaceable>. The operators are required to be commutative. Each <replaceable class="parameter">exclude_element</replaceable> - can optionally specify an operator class and/or ordering options; - these are described fully under - <xref linkend="sql-createindex"/>. + can optionally specify a collation, an operator class, and ordering options; + these are described fully under <xref linkend="sql-createindex"/>. </para> <para> Attachments: [text/plain] update_document_exclude_with_collate_v3-bis.patch (2.8K, ../../[email protected]/2-update_document_exclude_with_collate_v3-bis.patch) download | inline diff: diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 2c4138e4e9..43f5f0873e 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -135,7 +135,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM <phrase><replaceable class="parameter">exclude_element</replaceable> in an <literal>EXCLUDE</literal> constraint is:</phrase> -{ <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ <replaceable class="parameter">opclass</replaceable> ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] +{ <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] <phrase><replaceable class="parameter">referential_action</replaceable> in a <literal>FOREIGN KEY</literal>/<literal>REFERENCES</literal> constraint is:</phrase> diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index e04a0692c4..277d292aac 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -105,7 +105,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM <phrase><replaceable class="parameter">exclude_element</replaceable> in an <literal>EXCLUDE</literal> constraint is:</phrase> -{ <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ <replaceable class="parameter">opclass</replaceable> ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] +{ <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] <phrase><replaceable class="parameter">referential_action</replaceable> in a <literal>FOREIGN KEY</literal>/<literal>REFERENCES</literal> constraint is:</phrase> @@ -1097,9 +1097,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM method <replaceable>index_method</replaceable>. The operators are required to be commutative. Each <replaceable class="parameter">exclude_element</replaceable> - can optionally specify an operator class and/or ordering options; - these are described fully under - <xref linkend="sql-createindex"/>. + can optionally specify a collation, an operator class, and ordering options; + these are described fully under <xref linkend="sql-createindex"/>. </para> <para> ^ permalink raw reply [nested|flat] 5+ messages in thread
* [PATCH v3 10/17] Inline heap_freeze_execute_prepared() @ 2024-01-07 22:03 Melanie Plageman <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Melanie Plageman @ 2024-01-07 22:03 UTC (permalink / raw) In order to merge freeze and prune records, the execution of tuple freezing and the WAL logging of the changes to the page must be separated so that the WAL logging can be combined with prune WAL logging. This commit makes a helper for the tuple freezing and then inlines the contents of heap_freeze_execute_prepared() where it is called in heap_page_prune(). The original function, heap_freeze_execute_prepared() is retained because the "no prune" case in heap_page_prune() must still be able to emit a freeze record. --- src/backend/access/heap/heapam.c | 61 +++++++++++++++++------------ src/backend/access/heap/pruneheap.c | 51 ++++++++++++++++++++++-- src/include/access/heapam.h | 8 ++++ 3 files changed, 90 insertions(+), 30 deletions(-) diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c index 16e3f2520a4..a3691584c55 100644 --- a/src/backend/access/heap/heapam.c +++ b/src/backend/access/heap/heapam.c @@ -91,9 +91,6 @@ static void compute_new_xmax_infomask(TransactionId xmax, uint16 old_infomask, static TM_Result heap_lock_updated_tuple(Relation rel, HeapTuple tuple, ItemPointer ctid, TransactionId xid, LockTupleMode mode); -static int heap_log_freeze_plan(HeapTupleFreeze *tuples, int ntuples, - xl_heap_freeze_plan *plans_out, - OffsetNumber *offsets_out); static void GetMultiXactIdHintBits(MultiXactId multi, uint16 *new_infomask, uint16 *new_infomask2); static TransactionId MultiXactIdGetUpdateXid(TransactionId xmax, @@ -6713,30 +6710,17 @@ heap_pre_freeze_checks(Buffer buffer, } /* - * heap_freeze_execute_prepared - * - * Executes freezing of one or more heap tuples on a page on behalf of caller. - * Caller passes an array of tuple plans from heap_prepare_freeze_tuple. - * Caller must set 'offset' in each plan for us. Note that we destructively - * sort caller's tuples array in-place, so caller had better be done with it. - * - * WAL-logs the changes so that VACUUM can advance the rel's relfrozenxid - * later on without any risk of unsafe pg_xact lookups, even following a hard - * crash (or when querying from a standby). We represent freezing by setting - * infomask bits in tuple headers, but this shouldn't be thought of as a hint. - * See section on buffer access rules in src/backend/storage/buffer/README. + * Helper which executes freezing of one or more heap tuples on a page on + * behalf of caller. Caller passes an array of tuple plans from + * heap_prepare_freeze_tuple. Caller must set 'offset' in each plan for us. + * Must be called in a critical section that also marks the buffer dirty and, + * if needed, emits WAL. */ void -heap_freeze_execute_prepared(Relation rel, Buffer buffer, - TransactionId snapshotConflictHorizon, - HeapTupleFreeze *tuples, int ntuples) +heap_freeze_prepared_tuples(Buffer buffer, HeapTupleFreeze *tuples, int ntuples) { Page page = BufferGetPage(buffer); - Assert(ntuples > 0); - - START_CRIT_SECTION(); - for (int i = 0; i < ntuples; i++) { HeapTupleFreeze *frz = tuples + i; @@ -6746,6 +6730,29 @@ heap_freeze_execute_prepared(Relation rel, Buffer buffer, htup = (HeapTupleHeader) PageGetItem(page, itemid); heap_execute_freeze_tuple(htup, frz); } +} + +/* + * heap_freeze_execute_prepared + * + * Execute freezing of prepared tuples and WAL-logs the changes so that VACUUM + * can advance the rel's relfrozenxid later on without any risk of unsafe + * pg_xact lookups, even following a hard crash (or when querying from a + * standby). We represent freezing by setting infomask bits in tuple headers, + * but this shouldn't be thought of as a hint. See section on buffer access + * rules in src/backend/storage/buffer/README. Must be called from within a + * critical section. + */ +void +heap_freeze_execute_prepared(Relation rel, Buffer buffer, + TransactionId snapshotConflictHorizon, + HeapTupleFreeze *tuples, int ntuples) +{ + Page page = BufferGetPage(buffer); + + Assert(ntuples > 0); + + heap_freeze_prepared_tuples(buffer, tuples, ntuples); MarkBufferDirty(buffer); @@ -6758,7 +6765,11 @@ heap_freeze_execute_prepared(Relation rel, Buffer buffer, xl_heap_freeze_page xlrec; XLogRecPtr recptr; - /* Prepare deduplicated representation for use in WAL record */ + /* + * Prepare deduplicated representation for use in WAL record + * Destructively sorts tuples array in-place, so caller had better be + * done with it. + */ nplans = heap_log_freeze_plan(tuples, ntuples, plans, offsets); xlrec.snapshotConflictHorizon = snapshotConflictHorizon; @@ -6783,8 +6794,6 @@ heap_freeze_execute_prepared(Relation rel, Buffer buffer, PageSetLSN(page, recptr); } - - END_CRIT_SECTION(); } /* @@ -6874,7 +6883,7 @@ heap_log_freeze_new_plan(xl_heap_freeze_plan *plan, HeapTupleFreeze *frz) * (actually there is one array per freeze plan, but that's not of immediate * concern to our caller). */ -static int +int heap_log_freeze_plan(HeapTupleFreeze *tuples, int ntuples, xl_heap_freeze_plan *plans_out, OffsetNumber *offsets_out) diff --git a/src/backend/access/heap/pruneheap.c b/src/backend/access/heap/pruneheap.c index bc0a23da61b..d4356e0bce9 100644 --- a/src/backend/access/heap/pruneheap.c +++ b/src/backend/access/heap/pruneheap.c @@ -627,10 +627,53 @@ heap_page_prune_and_freeze(Relation relation, Buffer buffer, if (do_freeze) { - /* Execute all freeze plans for page as a single atomic action */ - heap_freeze_execute_prepared(relation, buffer, - frz_conflict_horizon, - frozen, presult->nfrozen); + START_CRIT_SECTION(); + + Assert(presult->nfrozen > 0); + + heap_freeze_prepared_tuples(buffer, frozen, presult->nfrozen); + + MarkBufferDirty(buffer); + + /* Now WAL-log freezing if necessary */ + if (RelationNeedsWAL(relation)) + { + xl_heap_freeze_plan plans[MaxHeapTuplesPerPage]; + OffsetNumber offsets[MaxHeapTuplesPerPage]; + int nplans; + xl_heap_freeze_page xlrec; + XLogRecPtr recptr; + + /* + * Prepare deduplicated representation for use in WAL record + * Destructively sorts tuples array in-place. + */ + nplans = heap_log_freeze_plan(frozen, presult->nfrozen, plans, offsets); + + xlrec.snapshotConflictHorizon = frz_conflict_horizon; + xlrec.isCatalogRel = RelationIsAccessibleInLogicalDecoding(relation); + xlrec.nplans = nplans; + + XLogBeginInsert(); + XLogRegisterData((char *) &xlrec, SizeOfHeapFreezePage); + + /* + * The freeze plan array and offset array are not actually in the + * buffer, but pretend that they are. When XLogInsert stores the + * whole buffer, the arrays need not be stored too. + */ + XLogRegisterBuffer(0, buffer, REGBUF_STANDARD); + XLogRegisterBufData(0, (char *) plans, + nplans * sizeof(xl_heap_freeze_plan)); + XLogRegisterBufData(0, (char *) offsets, + presult->nfrozen * sizeof(OffsetNumber)); + + recptr = XLogInsert(RM_HEAP2_ID, XLOG_HEAP2_FREEZE_PAGE); + + PageSetLSN(page, recptr); + } + + END_CRIT_SECTION(); } else if (!pagefrz || !presult->all_frozen || presult->nfrozen > 0) { diff --git a/src/include/access/heapam.h b/src/include/access/heapam.h index dffbbd3cd3e..8a6bc071345 100644 --- a/src/include/access/heapam.h +++ b/src/include/access/heapam.h @@ -14,6 +14,7 @@ #ifndef HEAPAM_H #define HEAPAM_H +#include "access/heapam_xlog.h" #include "access/relation.h" /* for backward compatibility */ #include "access/relscan.h" #include "access/sdir.h" @@ -320,9 +321,16 @@ extern void heap_pre_freeze_checks(Buffer buffer, extern void heap_freeze_execute_prepared(Relation rel, Buffer buffer, TransactionId snapshotConflictHorizon, HeapTupleFreeze *tuples, int ntuples); + +extern void heap_freeze_prepared_tuples(Buffer buffer, + HeapTupleFreeze *tuples, int ntuples); extern bool heap_freeze_tuple(HeapTupleHeader tuple, TransactionId relfrozenxid, TransactionId relminmxid, TransactionId FreezeLimit, TransactionId MultiXactCutoff); + +extern int heap_log_freeze_plan(HeapTupleFreeze *tuples, int ntuples, + xl_heap_freeze_plan *plans_out, + OffsetNumber *offsets_out); extern bool heap_tuple_should_freeze(HeapTupleHeader tuple, const struct VacuumCutoffs *cutoffs, TransactionId *NoFreezePageRelfrozenXid, -- 2.40.1 --racicctn4wry6xe5 Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v3-0011-Exit-heap_page_prune-early-if-no-prune.patch" ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: EXCLUDE COLLATE in CREATE/ALTER TABLE document @ 2024-04-07 19:38 Tom Lane <[email protected]> parent: Daniel Verite <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Tom Lane @ 2024-04-07 19:38 UTC (permalink / raw) To: Daniel Verite <[email protected]>; +Cc: shihao zhong <[email protected]>; Jian He <[email protected]>; [email protected] "Daniel Verite" <[email protected]> writes: > Also the use of "and/or" in the previous version conveys the fact > that operator class and ordering options are not mutually > exclusive. But when using "any of the following" in the new text, > doesn't it loose that meaning? Yeah; and/or is perfectly fine here and doesn't need to be improved on. There's a bigger problem though, which is that these bits are *also* missing any reference to opclass parameters. I fixed that and pushed it. regards, tom lane ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2024-04-07 19:38 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2023-11-10 14:59 Re: EXCLUDE COLLATE in CREATE/ALTER TABLE document jian he <[email protected]> 2023-11-16 23:25 ` shihao zhong <[email protected]> 2023-12-01 14:59 ` Daniel Verite <[email protected]> 2024-04-07 19:38 ` Tom Lane <[email protected]> 2024-01-07 22:03 [PATCH v3 10/17] Inline heap_freeze_execute_prepared() Melanie Plageman <[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