agora inbox for [email protected]help / color / mirror / Atom feed
[PATCH] Add a function to reset the cumulative statistics 10+ messages / 5 participants [nested] [flat]
* [PATCH] Add a function to reset the cumulative statistics @ 2019-02-14 13:37 Pierre <[email protected]> 0 siblings, 0 replies; 10+ messages in thread From: Pierre @ 2019-02-14 13:37 UTC (permalink / raw) pg_stat_statements has two parts : raw statistics that are simple 'stable' counters, and computed statistics (averages, min time, max time...) When using pg_stat_statements to find and fix performance issues, being able to reset the computed statistics can help track the issue and the impact of the fixes. This would also make it possible for tools like powa to collect these statistics too and track them over time by resetting them after each collection. --- .../pg_stat_statements--1.7--1.8.sql | 11 ++++ .../pg_stat_statements/pg_stat_statements.c | 52 +++++++++++++++++-- .../pg_stat_statements.control | 2 +- 3 files changed, 61 insertions(+), 4 deletions(-) create mode 100644 contrib/pg_stat_statements/pg_stat_statements--1.7--1.8.sql diff --git a/contrib/pg_stat_statements/pg_stat_statements--1.7--1.8.sql b/contrib/pg_stat_statements/pg_stat_statements--1.7--1.8.sql new file mode 100644 index 0000000000..7690a9ceba --- /dev/null +++ b/contrib/pg_stat_statements/pg_stat_statements--1.7--1.8.sql @@ -0,0 +1,11 @@ +/* contrib/pg_stat_statements/pg_stat_statements--1.7--1.8.sql */ + +-- complain if script is sourced in psql, rather than via ALTER EXTENSION +\echo Use "ALTER EXTENSION pg_stat_statements UPDATE TO '1.8'" to load this file. \quit + +CREATE FUNCTION pg_stat_statements_reset_computed_values() +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C PARALLEL SAFE; + + diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c index 221b47298c..3a6c227a80 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.c +++ b/contrib/pg_stat_statements/pg_stat_statements.c @@ -150,6 +150,7 @@ typedef struct Counters double max_time; /* maximum execution time in msec */ double mean_time; /* mean execution time in msec */ double sum_var_time; /* sum of variances in execution time in msec */ + int64 computed_calls; /* # of times executed considered for the previous computed values */ int64 rows; /* total # of retrieved or affected rows */ int64 shared_blks_hit; /* # of shared buffer hits */ int64 shared_blks_read; /* # of shared disk blocks read */ @@ -289,6 +290,7 @@ static bool pgss_save; /* whether to save stats across shutdown */ void _PG_init(void); void _PG_fini(void); +PG_FUNCTION_INFO_V1(pg_stat_statements_reset_computed_values); PG_FUNCTION_INFO_V1(pg_stat_statements_reset); PG_FUNCTION_INFO_V1(pg_stat_statements_reset_1_7); PG_FUNCTION_INFO_V1(pg_stat_statements_1_2); @@ -1252,8 +1254,9 @@ pgss_store(const char *query, uint64 queryId, e->counters.usage = USAGE_INIT; e->counters.calls += 1; + e->counters.computed_calls += 1; e->counters.total_time += total_time; - if (e->counters.calls == 1) + if (e->counters.computed_calls == 1) { e->counters.min_time = total_time; e->counters.max_time = total_time; @@ -1268,7 +1271,7 @@ pgss_store(const char *query, uint64 queryId, double old_mean = e->counters.mean_time; e->counters.mean_time += - (total_time - old_mean) / e->counters.calls; + (total_time - old_mean) / e->counters.computed_calls; e->counters.sum_var_time += (total_time - old_mean) * (total_time - e->counters.mean_time); @@ -1324,7 +1327,7 @@ pg_stat_statements_reset_1_7(PG_FUNCTION_ARGS) } /* - * Reset statement statistics. + * Reset all statement statistics. */ Datum pg_stat_statements_reset(PG_FUNCTION_ARGS) @@ -1334,6 +1337,49 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS) PG_RETURN_VOID(); } +/* + * Reset computed statistics from all statements. + */ +Datum +pg_stat_statements_reset_computed_values(PG_FUNCTION_ARGS) +{ + if (!pgss || !pgss_hash) + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("pg_stat_statements must be loaded via shared_preload_libraries"))); + entry_reset_computed(); + PG_RETURN_VOID(); +} + + +/* + * Reset statement computed statistics. + * This takes a shared lock only on the hash table, and a lock per entry + */ +static void +entry_reset_computed(void) +{ + HASH_SEQ_STATUS hash_seq; + pgssEntry *entry; + + /* Lookup the hash table entry with shared lock. */ + LWLockAcquire(pgss->lock, LW_SHARED); + + hash_seq_init(&hash_seq, pgss_hash); + while ((entry = hash_seq_search(&hash_seq)) != NULL) + { + SpinLockAcquire(&entry->mutex); + entry->counters.computed_calls = 0; + entry->counters.min_time = 0; + entry->counters.max_time = 0; + entry->counters.mean_time = 0; + entry->counters.sum_var_time = 0; + SpinLockRelease(&entry->mutex); + } + + LWLockRelease(pgss->lock); +} + /* Number of output arguments (columns) for various API versions */ #define PG_STAT_STATEMENTS_COLS_V1_0 14 #define PG_STAT_STATEMENTS_COLS_V1_1 18 diff --git a/contrib/pg_stat_statements/pg_stat_statements.control b/contrib/pg_stat_statements/pg_stat_statements.control index 14cb422354..7fb20df886 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.control +++ b/contrib/pg_stat_statements/pg_stat_statements.control @@ -1,5 +1,5 @@ # pg_stat_statements extension comment = 'track execution statistics of all SQL statements executed' -default_version = '1.7' +default_version = '1.8' module_pathname = '$libdir/pg_stat_statements' relocatable = true -- 2.23.0.rc1 --nextPart54047663.qFHcHZVkGV-- ^ permalink raw reply [nested|flat] 10+ messages in thread
* [PATCH] Improve documentation about our XML functionality. @ 2019-08-03 02:47 nobody <[email protected]> 0 siblings, 0 replies; 10+ messages in thread From: nobody @ 2019-08-03 02:47 UTC (permalink / raw) Add a section explaining how our XML features depart from current versions of the SQL standard. Update and clarify the descriptions of some XML functions. Chapman Flack, reviewed by Ryan Lambert Discussion: https://postgr.es/m/[email protected] Discussion: https://postgr.es/m/[email protected] Discussion: https://postgr.es/m/CAN-V+g-6JqUQEQZ55Q3toXEN6d5Ez5uvzL4VR+8KtvJKj31taw@mail.gmail.com This version for backpatching PG 11 and 10, taken from Tom's commit for 12, then edited to correctly describe behaviors that are fixed in 12 but still broken in 11 and 10. --- doc/src/sgml/datatype.sgml | 5 + doc/src/sgml/features.sgml | 381 ++++++++++++++++++++++++++++++++++- doc/src/sgml/func.sgml | 184 +++++++++-------- src/backend/catalog/sql_features.txt | 6 +- 4 files changed, 490 insertions(+), 86 deletions(-) diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index 401a2f0..fa505e0 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -4228,6 +4228,11 @@ a0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a11 value is a full document or only a content fragment. </para> + <para> + Limits and compatibility notes for the <type>xml</type> data type + can be found in <xref linkend="xml-limits-conformance"/>. + </para> + <sect2> <title>Creating XML Values</title> <para> diff --git a/doc/src/sgml/features.sgml b/doc/src/sgml/features.sgml index 6c22d69..253ec87 100644 --- a/doc/src/sgml/features.sgml +++ b/doc/src/sgml/features.sgml @@ -16,7 +16,8 @@ Language SQL</quote>. A revised version of the standard is released from time to time; the most recent update appearing in 2011. The 2011 version is referred to as ISO/IEC 9075:2011, or simply as SQL:2011. - The versions prior to that were SQL:2008, SQL:2003, SQL:1999, and SQL-92. Each version + The versions prior to that were SQL:2008, SQL:2006, SQL:2003, SQL:1999, + and SQL-92. Each version replaces the previous one, so claims of conformance to earlier versions have no official merit. <productname>PostgreSQL</productname> development aims for @@ -155,4 +156,382 @@ </para> </sect1> + <sect1 id="xml-limits-conformance"> + <title>XML Limits and Conformance to SQL/XML</title> + + <indexterm> + <primary>SQL/XML</primary> + <secondary>limits and conformance</secondary> + </indexterm> + + <para> + Significant revisions to the XML-related specifications in ISO/IEC 9075-14 + (SQL/XML) were introduced with SQL:2006. + <productname>PostgreSQL</productname>'s implementation of the XML data + type and related functions largely follows the earlier 2003 edition, + with some borrowing from later editions. In particular: + <itemizedlist> + <listitem> + <para> + Where the current standard provides a family of XML data types + to hold <quote>document</quote> or <quote>content</quote> in + untyped or XML Schema-typed variants, and a type + <type>XML(SEQUENCE)</type> to hold arbitrary pieces of XML content, + <productname>PostgreSQL</productname> provides the single + <type>xml</type> type, which can hold <quote>document</quote> or + <quote>content</quote>. There is no equivalent of the + standard's <quote>sequence</quote> type. + </para> + </listitem> + + <listitem> + <para> + <productname>PostgreSQL</productname> provides two functions + introduced in SQL:2006, but in variants that use the XPath 1.0 + language, rather than XML Query as specified for them in the + standard. + </para> + </listitem> + </itemizedlist> + </para> + + <para> + This section presents some of the resulting differences you may encounter. + </para> + + <sect2 id="functions-xml-limits-xpath1"> + <title>Queries are restricted to XPath 1.0</title> + + <para> + The <productname>PostgreSQL</productname>-specific functions + <function>xpath()</function> and <function>xpath_exists()</function> + query XML documents using the XPath language. + <productname>PostgreSQL</productname> also provides XPath-only variants + of the standard functions <function>XMLEXISTS</function> and + <function>XMLTABLE</function>, which officially use + the XQuery language. For all of these functions, + <productname>PostgreSQL</productname> relies on the + <application>libxml2</application> library, which provides only XPath 1.0. + </para> + + <para> + There is a strong connection between the XQuery language and XPath + versions 2.0 and later: any expression that is syntactically valid and + executes successfully in both produces the same result (with a minor + exception for expressions containing numeric character references or + predefined entity references, which XQuery replaces with the + corresponding character while XPath leaves them alone). But there is + no such connection between these languages and XPath 1.0; it was an + earlier language and differs in many respects. + </para> + + <para> + There are two categories of limitation to keep in mind: the restriction + from XQuery to XPath for the functions specified in the SQL standard, and + the restriction of XPath to version 1.0 for both the standard and the + <productname>PostgreSQL</productname>-specific functions. + </para> + + <sect3> + <title>Restriction of XQuery to XPath</title> + + <para> + Features of XQuery beyond those of XPath include: + + <itemizedlist> + <listitem> + <para> + XQuery expressions can construct and return new XML nodes, in + addition to all possible XPath values. XPath can create and return + values of the atomic types (numbers, strings, and so on) but can + only return XML nodes that were already present in documents + supplied as input to the expression. + </para> + </listitem> + + <listitem> + <para> + XQuery has control constructs for iteration, sorting, and grouping. + </para> + </listitem> + + <listitem> + <para> + XQuery allows declaration and use of local functions. + </para> + </listitem> + </itemizedlist> + </para> + + <para> + Recent XPath versions begin to offer capabilities overlapping with + these (such as functional-style <function>for-each</function> and + <function>sort</function>, anonymous functions, and + <function>parse-xml</function> to create a node from a string), + but such features were not available before XPath 3.0. + </para> + </sect3> + + <sect3 id="xml-xpath-1-specifics"> + <title>Restriction of XPath to 1.0</title> + + <para> + For developers familiar with XQuery and XPath 2.0 or later, XPath 1.0 + presents a number of differences to contend with: + + <itemizedlist> + <listitem> + <para> + The fundamental type of an XQuery/XPath expression, the + <type>sequence</type>, which can contain XML nodes, atomic values, + or both, does not exist in XPath 1.0. A 1.0 expression can only + produce a node-set (containing zero or more XML nodes), or a single + atomic value. + </para> + </listitem> + + <listitem> + <para> + Unlike an XQuery/XPath sequence, which can contain any desired + items in any desired order, an XPath 1.0 node-set has no + guaranteed order and, like any set, does not allow multiple + appearances of the same item. + <note> + <para> + The <application>libxml2</application> library does seem to + always return node-sets to <productname>PostgreSQL</productname> + with their members in the same relative order they had in the + input document. Its documentation does not commit to this + behavior, and an XPath 1.0 expression cannot control it. + </para> + </note> + </para> + </listitem> + + <listitem> + <para> + While XQuery/XPath provides all of the types defined in XML Schema + and many operators and functions over those types, XPath 1.0 has only + node-sets and the three atomic types <type>boolean</type>, + <type>double</type>, and <type>string</type>. + </para> + </listitem> + + <listitem> + <para> + XPath 1.0 has no conditional operator. An XQuery/XPath expression + such as <literal>if ( hat ) then hat/@size else "no hat"</literal> + has no XPath 1.0 equivalent. + </para> + </listitem> + + <listitem> + <para> + XPath 1.0 has no ordering comparison operator for strings. Both + <literal>"cat" < "dog"</literal> and + <literal>"cat" > "dog"</literal> are false, because each is a + numeric comparison of two <literal>NaN</literal>s. In contrast, + <literal>=</literal> and <literal>!=</literal> do compare the strings + as strings. + </para> + </listitem> + + <listitem> + <para> + XPath 1.0 blurs the distinction between + <firstterm>value comparisons</firstterm> and + <firstterm>general comparisons</firstterm> as XQuery/XPath define + them. Both <literal>sale/@hatsize = 7</literal> and + <literal>sale/@customer = "alice"</literal> are existentially + quantified comparisons, true if there is + any <literal>sale</literal> with the given value for the + attribute, but <literal>sale/@taxable = false()</literal> is a + value comparison to the + <firstterm>effective boolean value</firstterm> of a whole node-set. + It is true only if no <literal>sale</literal> has + a <literal>taxable</literal> attribute at all. + </para> + </listitem> + + <listitem> + <para> + In the XQuery/XPath data model, a <firstterm>document + node</firstterm> can have either document form (i.e., exactly one + top-level element, with only comments and processing instructions + outside of it) or content form (with those constraints + relaxed). Its equivalent in XPath 1.0, the + <firstterm>root node</firstterm>, can only be in document form. + This is part of the reason an <type>xml</type> value passed as the + context item to any <productname>PostgreSQL</productname> + XPath-based function must be in document form. + </para> + </listitem> + </itemizedlist> + </para> + + <para> + The differences highlighted here are not all of them. In XQuery and + the 2.0 and later versions of XPath, there is an XPath 1.0 compatibility + mode, and the W3C lists of + <ulink url='https://www.w3.org/TR/2010/REC-xpath-functions-20101214/#xpath1-compatibility'>function library changes</ulink> + and + <ulink url='https://www.w3.org/TR/xpath20/#id-backwards-compatibility'>language changes</ulink> + applied in that mode offer a more complete (but still not exhaustive) + account of the differences. The compatibility mode cannot make the + later languages exactly equivalent to XPath 1.0. + </para> + </sect3> + + <sect3 id="functions-xml-limits-casts"> + <title>Mappings between SQL and XML data types and values</title> + + <para> + In SQL:2006 and later, both directions of conversion between standard SQL + data types and the XML Schema types are specified precisely. However, the + rules are expressed using the types and semantics of XQuery/XPath, and + have no direct application to the different data model of XPath 1.0. + </para> + + <para> + When <productname>PostgreSQL</productname> maps SQL data values to XML + (as in <function>xmlelement</function>), or XML to SQL (as in the output + columns of <function>xmltable</function>), except for a few cases + treated specially, <productname>PostgreSQL</productname> simply assumes + that the XML data type's XPath 1.0 string form will be valid as the + text-input form of the SQL datatype, and conversely. This rule has the + virtue of simplicity while producing, for many data types, results similar + to the mappings specified in the standard. In this release, + an explicit cast is needed if an <function>xmltable</function> column + expression produces a boolean or double value; see + <xref linkend="functions-xml-limits-postgresql"/>. + </para> + + <para> + Where interoperability with other systems is a concern, for some data + types, it may be necessary to use data type formatting functions (such + as those in <xref linkend="functions-formatting"/>) explicitly to + produce the standard mappings. + </para> + </sect3> + </sect2> + + <sect2 id="functions-xml-limits-postgresql"> + <title> + Incidental limits of the implementation + </title> + + <para> + This section concerns limits that are not inherent in the + <application>libxml2</application> library, but apply to the current + implementation in <productname>PostgreSQL</productname>. + </para> + + <sect3> + <title> + Cast needed for <function>xmltable</function> column + of boolean or double type + </title> + + <para> + An <function>xmltable</function> column expression evaluating to an XPath + boolean or number result will produce an <quote>unexpected XPath object + type</quote> error. The workaround is to rewrite the column expression to + be inside the XPath <function>string</function> function; + <productname>PostgreSQL</productname> will then assign the string value + successfully to an SQL output column of boolean or double type. + </para> + </sect3> + + <sect3> + <title> + Column path result or SQL result column of XML type + </title> + + <para> + In this release, a <function>xmltable</function> column expression + that evaluates to an XML node-set can be assigned to an SQL result + column of XML type, producing a concatenation of: for most types of + node in the node-set, a text node containing the XPath 1.0 + <firstterm>string-value</firstterm> of the node, but for an element node, + a copy of the node itself. Such a node-set may be assigned to an SQL + column of non-XML type only if the node-set has a single node, with the + string-value of most node types replaced with an empty string, the + string-value of an element node replaced with a concatenation of only its + direct text-node children (excluding those of descendants), and the + string-value of a text or attribute node being as defined in XPath 1.0. + An XPath string value assigned to a result column of XML type must be + parsable as XML. + </para> + + <para> + It is best not to develop code that relies on these behaviors, which have + little resemblance to the spec, and are changed in + <productname>PostgreSQL 12</productname>. + </para> + </sect3> + + <sect3> + <title>Only <literal>BY VALUE</literal> passing mechanism is supported</title> + + <para> + The SQL standard defines two <firstterm>passing mechanisms</firstterm> + that apply when passing an XML argument from SQL to an XML function or + receiving a result: <literal>BY REF</literal>, in which a particular XML + value retains its node identity, and <literal>BY VALUE</literal>, in which + the content of the XML is passed but node identity is not preserved. A + mechanism can be specified before a list of parameters, as the default + mechanism for all of them, or after any parameter, to override the + default. + </para> + + <para> + To illustrate the difference, if + <replaceable>x</replaceable> is an XML value, these two queries in + an SQL:2006 environment would produce true and false, respectively: + +<programlisting> +SELECT XMLQUERY('$a is $b' PASSING BY REF <replaceable>x</replaceable> AS a, <replaceable>x</replaceable> AS b NULL ON EMPTY); +SELECT XMLQUERY('$a is $b' PASSING BY VALUE <replaceable>x</replaceable> AS a, <replaceable>x</replaceable> AS b NULL ON EMPTY); +</programlisting> + </para> + + <para> + In this release, <productname>PostgreSQL</productname> will accept + <literal>BY REF</literal> in an + <function>XMLEXISTS</function> or <function>XMLTABLE</function> + construct, but will ignore it. The <type>xml</type> data type holds + a character-string serialized representation, so there is no node + identity to preserve, and passing is always effectively <literal>BY + VALUE</literal>. + </para> + </sect3> + + <sect3> + <title>Cannot pass named parameters to queries</title> + + <para> + The XPath-based functions support passing one parameter to serve as the + XPath expression's context item, but do not support passing additional + values to be available to the expression as named parameters. + </para> + </sect3> + + <sect3> + <title>No <type>XML(SEQUENCE)</type> type</title> + + <para> + The <productname>PostgreSQL</productname> <type>xml</type> data type + can only hold a value in <literal>DOCUMENT</literal> + or <literal>CONTENT</literal> form. An XQuery/XPath expression + context item must be a single XML node or atomic value, but XPath 1.0 + further restricts it to be only an XML node, and has no node type + allowing <literal>CONTENT</literal>. The upshot is that a + well-formed <literal>DOCUMENT</literal> is the only form of XML value + that <productname>PostgreSQL</productname> can supply as an XPath + context item. + </para> + </sect3> + </sect2> + </sect1> + </appendix> diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index cfa1e78..bec1b87 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -10045,16 +10045,25 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple <sect1 id="functions-xml"> + <title>XML Functions</title> + <indexterm> + <primary>XML Functions</primary> + </indexterm> + <para> The functions and function-like expressions described in this - section operate on values of type <type>xml</type>. Check <xref + section operate on values of type <type>xml</type>. See <xref linkend="datatype-xml"/> for information about the <type>xml</type> type. The function-like expressions <function>xmlparse</function> and <function>xmlserialize</function> for converting to and from - type <type>xml</type> are not repeated here. Use of most of these - functions requires the installation to have been built + type <type>xml</type> are documented there, not in this section. + </para> + + <para> + Use of most of these functions + requires <productname>PostgreSQL</productname> to have been built with <command>configure --with-libxml</command>. </para> @@ -10249,8 +10258,8 @@ SELECT xmlelement(name foo, xmlattributes('xyz' as bar), encoding, depending on the setting of the configuration parameter <xref linkend="guc-xmlbinary"/>. The particular behavior for individual data types is expected to evolve in order to align the - SQL and PostgreSQL data types with the XML Schema specification, - at which point a more precise description will appear. + PostgreSQL mappings with those specified in SQL:2006 and later, + as discussed in <xref linkend="functions-xml-limits-casts"/>. </para> </sect3> @@ -10492,10 +10501,13 @@ SELECT xmlagg(x) FROM (SELECT * FROM test ORDER BY y DESC) AS tab; </synopsis> <para> - The function <function>xmlexists</function> returns true if the - XPath expression in the first argument returns any nodes, and - false otherwise. (If either argument is null, the result is - null.) + The function <function>xmlexists</function> evaluates an XPath 1.0 + expression (the first argument), with the passed XML value as its context + item. The function returns false if the result of that evaluation + yields an empty node-set, true if it yields any other value. The + function returns null if any argument is null. A nonnull value + passed as the context item must be an XML document, not a content + fragment or any non-XML value. </para> <para> @@ -10511,14 +10523,14 @@ SELECT xmlexists('//town[text() = ''Toronto'']' PASSING BY REF '<towns><town>Tor </para> <para> - The <literal>BY REF</literal> clauses have no effect in - PostgreSQL, but are allowed for SQL conformance and compatibility - with other implementations. Per SQL standard, the - first <literal>BY REF</literal> is required, the second is - optional. Also note that the SQL standard specifies - the <function>xmlexists</function> construct to take an XQuery - expression as first argument, but PostgreSQL currently only - supports XPath, which is a subset of XQuery. + The <literal>BY REF</literal> clauses + are accepted in <productname>PostgreSQL</productname>, but are ignored, + as discussed in <xref linkend="functions-xml-limits-postgresql"/>. + In the SQL standard, the <function>xmlexists</function> function + evaluates an expression in the XML Query language, + but <productname>PostgreSQL</productname> allows only an XPath 1.0 + expression, as discussed in + <xref linkend="functions-xml-limits-xpath1"/>. </para> </sect3> @@ -10624,12 +10636,12 @@ SELECT xml_is_well_formed_document('<pg:foo xmlns:pg="http://postgresql.org/stuf </synopsis> <para> - The function <function>xpath</function> evaluates the XPath + The function <function>xpath</function> evaluates the XPath 1.0 expression <replaceable>xpath</replaceable> (a <type>text</type> value) against the XML value <replaceable>xml</replaceable>. It returns an array of XML values - corresponding to the node set produced by the XPath expression. - If the XPath expression returns a scalar value rather than a node set, + corresponding to the node-set produced by the XPath expression. + If the XPath expression returns a scalar value rather than a node-set, a single-element array is returned. </para> @@ -10691,9 +10703,10 @@ SELECT xpath('//mydefns:b/text()', '<a xmlns="http://example.com"><b>test</b></a <para> The function <function>xpath_exists</function> is a specialized form of the <function>xpath</function> function. Instead of returning the - individual XML values that satisfy the XPath, this function returns a - Boolean indicating whether the query was satisfied or not. This - function is equivalent to the standard <literal>XMLEXISTS</literal> predicate, + individual XML values that satisfy the XPath 1.0 expression, this function + returns a Boolean indicating whether the query was satisfied or not + (specifically, whether it produced any value other than an empty node-set). + This function is equivalent to the <literal>XMLEXISTS</literal> predicate, except that it also offers support for a namespace mapping argument. </para> @@ -10734,8 +10747,8 @@ SELECT xpath_exists('/my:a/text()', '<my:a xmlns:my="http://example.com">test</m <para> The <function>xmltable</function> function produces a table based - on the given XML value, an XPath filter to extract rows, and an - optional set of column definitions. + on the given XML value, an XPath filter to extract rows, and a + set of column definitions. </para> <para> @@ -10746,30 +10759,34 @@ SELECT xpath_exists('/my:a/text()', '<my:a xmlns:my="http://example.com">test</m </para> <para> - The required <replaceable>row_expression</replaceable> argument is an XPath - expression that is evaluated against the supplied XML document to - obtain an ordered sequence of XML nodes. This sequence is what - <function>xmltable</function> transforms into output rows. + The required <replaceable>row_expression</replaceable> argument is + an XPath 1.0 expression that is evaluated, passing the + <replaceable>document_expression</replaceable> as its context item, to + obtain a set of XML nodes. These nodes are what + <function>xmltable</function> transforms into output rows. No rows + will be produced if the <replaceable>document_expression</replaceable> + is null, nor if the <replaceable>row_expression</replaceable> produces + an empty node-set or any value other than a node-set. </para> <para> - <replaceable>document_expression</replaceable> provides the XML document to - operate on. - The <literal>BY REF</literal> clauses have no effect in PostgreSQL, - but are allowed for SQL conformance and compatibility with other - implementations. - The argument must be a well-formed XML document; fragments/forests - are not accepted. + <replaceable>document_expression</replaceable> provides the context + item for the <replaceable>row_expression</replaceable>. It must be a + well-formed XML document; fragments/forests are not accepted. + The <literal>BY REF</literal> clauses + are accepted but ignored, as discussed in + <xref linkend="functions-xml-limits-postgresql"/>. + In the SQL standard, the <function>xmltable</function> function + evaluates expressions in the XML Query language, + but <productname>PostgreSQL</productname> allows only XPath 1.0 + expressions, as discussed in + <xref linkend="functions-xml-limits-xpath1"/>. </para> <para> The mandatory <literal>COLUMNS</literal> clause specifies the list of columns in the output table. - If the <literal>COLUMNS</literal> clause is omitted, the rows in the result - set contain a single column of type <literal>xml</literal> containing the - data matched by <replaceable>row_expression</replaceable>. - If <literal>COLUMNS</literal> is specified, each entry describes a - single column. + Each entry describes a single column. See the syntax summary above for the format. The column name and type are required; the path, default and nullability clauses are optional. @@ -10777,48 +10794,57 @@ SELECT xpath_exists('/my:a/text()', '<my:a xmlns:my="http://example.com">test</m <para> A column marked <literal>FOR ORDINALITY</literal> will be populated - with row numbers matching the order in which the - output rows appeared in the original input XML document. + with row numbers, starting with 1, in the order of nodes retrieved from + the <replaceable>row_expression</replaceable>'s result node-set. At most one column may be marked <literal>FOR ORDINALITY</literal>. </para> + <note> + <para> + XPath 1.0 does not specify an order for nodes in a node-set, so code + that relies on a particular order of the results will be + implementation-dependent. Details can be found in + <xref linkend="xml-xpath-1-specifics"/>. + </para> + </note> + <para> - The <literal>column_expression</literal> for a column is an XPath expression - that is evaluated for each row, relative to the result of the - <replaceable>row_expression</replaceable>, to find the value of the column. - If no <literal>column_expression</literal> is given, then the column name - is used as an implicit path. + The <replaceable>column_expression</replaceable> for a column is an + XPath 1.0 expression that is evaluated for each row, with the current + node from the <replaceable>row_expression</replaceable> result as its + context item, to find the value of the column. If + no <replaceable>column_expression</replaceable> is given, then the + column name is used as an implicit path. </para> <para> - If a column's XPath expression returns multiple elements, an error - is raised. - If the expression matches an empty tag, the result is an - empty string (not <literal>NULL</literal>). - Any <literal>xsi:nil</literal> attributes are ignored. + If a column's XPath expression returns a non-XML value (limited to + string, boolean, or double in XPath 1.0) and the column has a + PostgreSQL type other than <type>xml</type>, the column will be set + as if by assigning the value's string representation to the PostgreSQL + type. In this release, an XPath boolean or double result must be explicitly + cast to string (that is, the XPath 1.0 <function>string</function> function + wrapped around the original column expression); + <productname>PostgreSQL</productname> can then successfully assign the + string to an SQL result column of boolean or double type. + These conversion rules differ from those of the SQL + standard, as discussed in <xref linkend="functions-xml-limits-casts"/>. </para> <para> - The text body of the XML matched by the <replaceable>column_expression</replaceable> - is used as the column value. Multiple <literal>text()</literal> nodes - within an element are concatenated in order. Any child elements, - processing instructions, and comments are ignored, but the text contents - of child elements are concatenated to the result. - Note that the whitespace-only <literal>text()</literal> node between two non-text - elements is preserved, and that leading whitespace on a <literal>text()</literal> - node is not flattened. + In this release, SQL result columns of <type>xml</type> type, or + column XPath expressions evaluating to an XML type, regardless of the + output column SQL type, are handled as described in + <xref linkend="functions-xml-limits-postgresql"/>; the behavior + changes significantly in <productname>PostgreSQL 12</productname>. </para> <para> - If the path expression does not match for a given row but - <replaceable>default_expression</replaceable> is specified, the value resulting - from evaluating that expression is used. - If no <literal>DEFAULT</literal> clause is given for the column, - the field will be set to <literal>NULL</literal>. - It is possible for a <replaceable>default_expression</replaceable> to reference - the value of output columns that appear prior to it in the column list, - so the default of one column may be based on the value of another - column. + If the path expression returns an empty node-set + (typically, when it does not match) + for a given row, the column will be set to <literal>NULL</literal>, unless + a <replaceable>default_expression</replaceable> is specified; then the + value resulting from evaluating that expression is used. </para> <para> @@ -10830,20 +10856,14 @@ SELECT xpath_exists('/my:a/text()', '<my:a xmlns:my="http://example.com">test</m </para> <para> - Unlike regular PostgreSQL functions, <replaceable>column_expression</replaceable> - and <replaceable>default_expression</replaceable> are not evaluated to a simple - value before calling the function. - <replaceable>column_expression</replaceable> is normally evaluated - exactly once per input row, and <replaceable>default_expression</replaceable> - is evaluated each time a default is needed for a field. - If the expression qualifies as stable or immutable the repeat + A <replaceable>default_expression</replaceable>, rather than being + evaluated immediately when <function>xmltable</function> is called, + is evaluated each time a default is needed for the column. + If the expression qualifies as stable or immutable, the repeat evaluation may be skipped. - Effectively <function>xmltable</function> behaves more like a subquery than a - function call. This means that you can usefully use volatile functions like - <function>nextval</function> in <replaceable>default_expression</replaceable>, and - <replaceable>column_expression</replaceable> may depend on other parts of the - XML document. + <function>nextval</function> in + <replaceable>default_expression</replaceable>. </para> <para> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index aeb262a..915696e 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -593,7 +593,7 @@ X085 Predefined namespace prefixes NO X086 XML namespace declarations in XMLTable NO X090 XML document predicate YES X091 XML content predicate NO -X096 XMLExists NO XPath only +X096 XMLExists NO XPath 1.0 only X100 Host language support for XML: CONTENT option NO X101 Host language support for XML: DOCUMENT option NO X110 Host language support for XML: VARCHAR mapping NO @@ -661,11 +661,11 @@ X282 XMLValidate with CONTENT option NO X283 XMLValidate with SEQUENCE option NO X284 XMLValidate: NAMESPACE without ELEMENT clause NO X286 XMLValidate: NO NAMESPACE with ELEMENT clause NO -X300 XMLTable NO XPath only +X300 XMLTable NO XPath 1.0 only X301 XMLTable: derived column list option YES X302 XMLTable: ordinality column option YES X303 XMLTable: column default option YES -X304 XMLTable: passing a context item YES +X304 XMLTable: passing a context item YES must be XML DOCUMENT X305 XMLTable: initializing an XQuery variable NO X400 Name and identifier mapping YES X410 Alter column data type: XML type YES -- 2.7.3 --------------060806070207090601090509 Content-Type: text/x-patch; name="10.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="10.patch" ^ permalink raw reply [nested|flat] 10+ messages in thread
* [PATCH] Improve documentation about our XML functionality. @ 2019-08-03 02:47 nobody <[email protected]> 0 siblings, 0 replies; 10+ messages in thread From: nobody @ 2019-08-03 02:47 UTC (permalink / raw) Add a section explaining how our XML features depart from current versions of the SQL standard. Update and clarify the descriptions of some XML functions. Chapman Flack, reviewed by Ryan Lambert Discussion: https://postgr.es/m/[email protected] Discussion: https://postgr.es/m/[email protected] Discussion: https://postgr.es/m/CAN-V+g-6JqUQEQZ55Q3toXEN6d5Ez5uvzL4VR+8KtvJKj31taw@mail.gmail.com This version for backpatching PG 10, taken from Tom's commit for 12, then edited to correctly describe behaviors that are fixed in 12 but still broken in 10. --- doc/src/sgml/datatype.sgml | 5 + doc/src/sgml/features.sgml | 381 ++++++++++++++++++++++++++++++++++- doc/src/sgml/func.sgml | 190 +++++++++-------- src/backend/catalog/sql_features.txt | 6 +- 4 files changed, 493 insertions(+), 89 deletions(-) diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index cd5f5f0..6b9010f 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -4228,6 +4228,11 @@ a0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a11 value is a full document or only a content fragment. </para> + <para> + Limits and compatibility notes for the <type>xml</type> data type + can be found in <xref linkend="xml-limits-conformance"/>. + </para> + <sect2> <title>Creating XML Values</title> <para> diff --git a/doc/src/sgml/features.sgml b/doc/src/sgml/features.sgml index 6c22d69..253ec87 100644 --- a/doc/src/sgml/features.sgml +++ b/doc/src/sgml/features.sgml @@ -16,7 +16,8 @@ Language SQL</quote>. A revised version of the standard is released from time to time; the most recent update appearing in 2011. The 2011 version is referred to as ISO/IEC 9075:2011, or simply as SQL:2011. - The versions prior to that were SQL:2008, SQL:2003, SQL:1999, and SQL-92. Each version + The versions prior to that were SQL:2008, SQL:2006, SQL:2003, SQL:1999, + and SQL-92. Each version replaces the previous one, so claims of conformance to earlier versions have no official merit. <productname>PostgreSQL</productname> development aims for @@ -155,4 +156,382 @@ </para> </sect1> + <sect1 id="xml-limits-conformance"> + <title>XML Limits and Conformance to SQL/XML</title> + + <indexterm> + <primary>SQL/XML</primary> + <secondary>limits and conformance</secondary> + </indexterm> + + <para> + Significant revisions to the XML-related specifications in ISO/IEC 9075-14 + (SQL/XML) were introduced with SQL:2006. + <productname>PostgreSQL</productname>'s implementation of the XML data + type and related functions largely follows the earlier 2003 edition, + with some borrowing from later editions. In particular: + <itemizedlist> + <listitem> + <para> + Where the current standard provides a family of XML data types + to hold <quote>document</quote> or <quote>content</quote> in + untyped or XML Schema-typed variants, and a type + <type>XML(SEQUENCE)</type> to hold arbitrary pieces of XML content, + <productname>PostgreSQL</productname> provides the single + <type>xml</type> type, which can hold <quote>document</quote> or + <quote>content</quote>. There is no equivalent of the + standard's <quote>sequence</quote> type. + </para> + </listitem> + + <listitem> + <para> + <productname>PostgreSQL</productname> provides two functions + introduced in SQL:2006, but in variants that use the XPath 1.0 + language, rather than XML Query as specified for them in the + standard. + </para> + </listitem> + </itemizedlist> + </para> + + <para> + This section presents some of the resulting differences you may encounter. + </para> + + <sect2 id="functions-xml-limits-xpath1"> + <title>Queries are restricted to XPath 1.0</title> + + <para> + The <productname>PostgreSQL</productname>-specific functions + <function>xpath()</function> and <function>xpath_exists()</function> + query XML documents using the XPath language. + <productname>PostgreSQL</productname> also provides XPath-only variants + of the standard functions <function>XMLEXISTS</function> and + <function>XMLTABLE</function>, which officially use + the XQuery language. For all of these functions, + <productname>PostgreSQL</productname> relies on the + <application>libxml2</application> library, which provides only XPath 1.0. + </para> + + <para> + There is a strong connection between the XQuery language and XPath + versions 2.0 and later: any expression that is syntactically valid and + executes successfully in both produces the same result (with a minor + exception for expressions containing numeric character references or + predefined entity references, which XQuery replaces with the + corresponding character while XPath leaves them alone). But there is + no such connection between these languages and XPath 1.0; it was an + earlier language and differs in many respects. + </para> + + <para> + There are two categories of limitation to keep in mind: the restriction + from XQuery to XPath for the functions specified in the SQL standard, and + the restriction of XPath to version 1.0 for both the standard and the + <productname>PostgreSQL</productname>-specific functions. + </para> + + <sect3> + <title>Restriction of XQuery to XPath</title> + + <para> + Features of XQuery beyond those of XPath include: + + <itemizedlist> + <listitem> + <para> + XQuery expressions can construct and return new XML nodes, in + addition to all possible XPath values. XPath can create and return + values of the atomic types (numbers, strings, and so on) but can + only return XML nodes that were already present in documents + supplied as input to the expression. + </para> + </listitem> + + <listitem> + <para> + XQuery has control constructs for iteration, sorting, and grouping. + </para> + </listitem> + + <listitem> + <para> + XQuery allows declaration and use of local functions. + </para> + </listitem> + </itemizedlist> + </para> + + <para> + Recent XPath versions begin to offer capabilities overlapping with + these (such as functional-style <function>for-each</function> and + <function>sort</function>, anonymous functions, and + <function>parse-xml</function> to create a node from a string), + but such features were not available before XPath 3.0. + </para> + </sect3> + + <sect3 id="xml-xpath-1-specifics"> + <title>Restriction of XPath to 1.0</title> + + <para> + For developers familiar with XQuery and XPath 2.0 or later, XPath 1.0 + presents a number of differences to contend with: + + <itemizedlist> + <listitem> + <para> + The fundamental type of an XQuery/XPath expression, the + <type>sequence</type>, which can contain XML nodes, atomic values, + or both, does not exist in XPath 1.0. A 1.0 expression can only + produce a node-set (containing zero or more XML nodes), or a single + atomic value. + </para> + </listitem> + + <listitem> + <para> + Unlike an XQuery/XPath sequence, which can contain any desired + items in any desired order, an XPath 1.0 node-set has no + guaranteed order and, like any set, does not allow multiple + appearances of the same item. + <note> + <para> + The <application>libxml2</application> library does seem to + always return node-sets to <productname>PostgreSQL</productname> + with their members in the same relative order they had in the + input document. Its documentation does not commit to this + behavior, and an XPath 1.0 expression cannot control it. + </para> + </note> + </para> + </listitem> + + <listitem> + <para> + While XQuery/XPath provides all of the types defined in XML Schema + and many operators and functions over those types, XPath 1.0 has only + node-sets and the three atomic types <type>boolean</type>, + <type>double</type>, and <type>string</type>. + </para> + </listitem> + + <listitem> + <para> + XPath 1.0 has no conditional operator. An XQuery/XPath expression + such as <literal>if ( hat ) then hat/@size else "no hat"</literal> + has no XPath 1.0 equivalent. + </para> + </listitem> + + <listitem> + <para> + XPath 1.0 has no ordering comparison operator for strings. Both + <literal>"cat" < "dog"</literal> and + <literal>"cat" > "dog"</literal> are false, because each is a + numeric comparison of two <literal>NaN</literal>s. In contrast, + <literal>=</literal> and <literal>!=</literal> do compare the strings + as strings. + </para> + </listitem> + + <listitem> + <para> + XPath 1.0 blurs the distinction between + <firstterm>value comparisons</firstterm> and + <firstterm>general comparisons</firstterm> as XQuery/XPath define + them. Both <literal>sale/@hatsize = 7</literal> and + <literal>sale/@customer = "alice"</literal> are existentially + quantified comparisons, true if there is + any <literal>sale</literal> with the given value for the + attribute, but <literal>sale/@taxable = false()</literal> is a + value comparison to the + <firstterm>effective boolean value</firstterm> of a whole node-set. + It is true only if no <literal>sale</literal> has + a <literal>taxable</literal> attribute at all. + </para> + </listitem> + + <listitem> + <para> + In the XQuery/XPath data model, a <firstterm>document + node</firstterm> can have either document form (i.e., exactly one + top-level element, with only comments and processing instructions + outside of it) or content form (with those constraints + relaxed). Its equivalent in XPath 1.0, the + <firstterm>root node</firstterm>, can only be in document form. + This is part of the reason an <type>xml</type> value passed as the + context item to any <productname>PostgreSQL</productname> + XPath-based function must be in document form. + </para> + </listitem> + </itemizedlist> + </para> + + <para> + The differences highlighted here are not all of them. In XQuery and + the 2.0 and later versions of XPath, there is an XPath 1.0 compatibility + mode, and the W3C lists of + <ulink url='https://www.w3.org/TR/2010/REC-xpath-functions-20101214/#xpath1-compatibility'>function library changes</ulink> + and + <ulink url='https://www.w3.org/TR/xpath20/#id-backwards-compatibility'>language changes</ulink> + applied in that mode offer a more complete (but still not exhaustive) + account of the differences. The compatibility mode cannot make the + later languages exactly equivalent to XPath 1.0. + </para> + </sect3> + + <sect3 id="functions-xml-limits-casts"> + <title>Mappings between SQL and XML data types and values</title> + + <para> + In SQL:2006 and later, both directions of conversion between standard SQL + data types and the XML Schema types are specified precisely. However, the + rules are expressed using the types and semantics of XQuery/XPath, and + have no direct application to the different data model of XPath 1.0. + </para> + + <para> + When <productname>PostgreSQL</productname> maps SQL data values to XML + (as in <function>xmlelement</function>), or XML to SQL (as in the output + columns of <function>xmltable</function>), except for a few cases + treated specially, <productname>PostgreSQL</productname> simply assumes + that the XML data type's XPath 1.0 string form will be valid as the + text-input form of the SQL datatype, and conversely. This rule has the + virtue of simplicity while producing, for many data types, results similar + to the mappings specified in the standard. In this release, + an explicit cast is needed if an <function>xmltable</function> column + expression produces a boolean or double value; see + <xref linkend="functions-xml-limits-postgresql"/>. + </para> + + <para> + Where interoperability with other systems is a concern, for some data + types, it may be necessary to use data type formatting functions (such + as those in <xref linkend="functions-formatting"/>) explicitly to + produce the standard mappings. + </para> + </sect3> + </sect2> + + <sect2 id="functions-xml-limits-postgresql"> + <title> + Incidental limits of the implementation + </title> + + <para> + This section concerns limits that are not inherent in the + <application>libxml2</application> library, but apply to the current + implementation in <productname>PostgreSQL</productname>. + </para> + + <sect3> + <title> + Cast needed for <function>xmltable</function> column + of boolean or double type + </title> + + <para> + An <function>xmltable</function> column expression evaluating to an XPath + boolean or number result will produce an <quote>unexpected XPath object + type</quote> error. The workaround is to rewrite the column expression to + be inside the XPath <function>string</function> function; + <productname>PostgreSQL</productname> will then assign the string value + successfully to an SQL output column of boolean or double type. + </para> + </sect3> + + <sect3> + <title> + Column path result or SQL result column of XML type + </title> + + <para> + In this release, a <function>xmltable</function> column expression + that evaluates to an XML node-set can be assigned to an SQL result + column of XML type, producing a concatenation of: for most types of + node in the node-set, a text node containing the XPath 1.0 + <firstterm>string-value</firstterm> of the node, but for an element node, + a copy of the node itself. Such a node-set may be assigned to an SQL + column of non-XML type only if the node-set has a single node, with the + string-value of most node types replaced with an empty string, the + string-value of an element node replaced with a concatenation of only its + direct text-node children (excluding those of descendants), and the + string-value of a text or attribute node being as defined in XPath 1.0. + An XPath string value assigned to a result column of XML type must be + parsable as XML. + </para> + + <para> + It is best not to develop code that relies on these behaviors, which have + little resemblance to the spec, and are changed in + <productname>PostgreSQL 12</productname>. + </para> + </sect3> + + <sect3> + <title>Only <literal>BY VALUE</literal> passing mechanism is supported</title> + + <para> + The SQL standard defines two <firstterm>passing mechanisms</firstterm> + that apply when passing an XML argument from SQL to an XML function or + receiving a result: <literal>BY REF</literal>, in which a particular XML + value retains its node identity, and <literal>BY VALUE</literal>, in which + the content of the XML is passed but node identity is not preserved. A + mechanism can be specified before a list of parameters, as the default + mechanism for all of them, or after any parameter, to override the + default. + </para> + + <para> + To illustrate the difference, if + <replaceable>x</replaceable> is an XML value, these two queries in + an SQL:2006 environment would produce true and false, respectively: + +<programlisting> +SELECT XMLQUERY('$a is $b' PASSING BY REF <replaceable>x</replaceable> AS a, <replaceable>x</replaceable> AS b NULL ON EMPTY); +SELECT XMLQUERY('$a is $b' PASSING BY VALUE <replaceable>x</replaceable> AS a, <replaceable>x</replaceable> AS b NULL ON EMPTY); +</programlisting> + </para> + + <para> + In this release, <productname>PostgreSQL</productname> will accept + <literal>BY REF</literal> in an + <function>XMLEXISTS</function> or <function>XMLTABLE</function> + construct, but will ignore it. The <type>xml</type> data type holds + a character-string serialized representation, so there is no node + identity to preserve, and passing is always effectively <literal>BY + VALUE</literal>. + </para> + </sect3> + + <sect3> + <title>Cannot pass named parameters to queries</title> + + <para> + The XPath-based functions support passing one parameter to serve as the + XPath expression's context item, but do not support passing additional + values to be available to the expression as named parameters. + </para> + </sect3> + + <sect3> + <title>No <type>XML(SEQUENCE)</type> type</title> + + <para> + The <productname>PostgreSQL</productname> <type>xml</type> data type + can only hold a value in <literal>DOCUMENT</literal> + or <literal>CONTENT</literal> form. An XQuery/XPath expression + context item must be a single XML node or atomic value, but XPath 1.0 + further restricts it to be only an XML node, and has no node type + allowing <literal>CONTENT</literal>. The upshot is that a + well-formed <literal>DOCUMENT</literal> is the only form of XML value + that <productname>PostgreSQL</productname> can supply as an XPath + context item. + </para> + </sect3> + </sect2> + </sect1> + </appendix> diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 198f9c2..a7514c0 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -9903,17 +9903,26 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple <sect1 id="functions-xml"> + <title>XML Functions</title> + <indexterm> + <primary>XML Functions</primary> + </indexterm> + <para> The functions and function-like expressions described in this - section operate on values of type <type>xml</type>. Check <xref - linkend="datatype-xml"> for information about the <type>xml</type> + section operate on values of type <type>xml</type>. See <xref + linkend="datatype-xml"/> for information about the <type>xml</type> type. The function-like expressions <function>xmlparse</function> and <function>xmlserialize</function> for converting to and from - type <type>xml</type> are not repeated here. Use of most of these - functions requires the installation to have been built - with <command>configure --with-libxml</>. + type <type>xml</type> are documented there, not in this section. + </para> + + <para> + Use of most of these functions + requires <productname>PostgreSQL</productname> to have been built + with <command>configure --with-libxml</command>. </para> <sect2 id="functions-producing-xml"> @@ -10107,8 +10116,8 @@ SELECT xmlelement(name foo, xmlattributes('xyz' as bar), encoding, depending on the setting of the configuration parameter <xref linkend="guc-xmlbinary">. The particular behavior for individual data types is expected to evolve in order to align the - SQL and PostgreSQL data types with the XML Schema specification, - at which point a more precise description will appear. + PostgreSQL mappings with those specified in SQL:2006 and later, + as discussed in <xref linkend="functions-xml-limits-casts"/>. </para> </sect3> @@ -10350,10 +10359,13 @@ SELECT xmlagg(x) FROM (SELECT * FROM test ORDER BY y DESC) AS tab; </synopsis> <para> - The function <function>xmlexists</function> returns true if the - XPath expression in the first argument returns any nodes, and - false otherwise. (If either argument is null, the result is - null.) + The function <function>xmlexists</function> evaluates an XPath 1.0 + expression (the first argument), with the passed XML value as its context + item. The function returns false if the result of that evaluation + yields an empty node-set, true if it yields any other value. The + function returns null if any argument is null. A nonnull value + passed as the context item must be an XML document, not a content + fragment or any non-XML value. </para> <para> @@ -10369,14 +10381,14 @@ SELECT xmlexists('//town[text() = ''Toronto'']' PASSING BY REF '<towns><town>Tor </para> <para> - The <literal>BY REF</literal> clauses have no effect in - PostgreSQL, but are allowed for SQL conformance and compatibility - with other implementations. Per SQL standard, the - first <literal>BY REF</literal> is required, the second is - optional. Also note that the SQL standard specifies - the <function>xmlexists</function> construct to take an XQuery - expression as first argument, but PostgreSQL currently only - supports XPath, which is a subset of XQuery. + The <literal>BY REF</literal> clauses + are accepted in <productname>PostgreSQL</productname>, but are ignored, + as discussed in <xref linkend="functions-xml-limits-postgresql"/>. + In the SQL standard, the <function>xmlexists</function> function + evaluates an expression in the XML Query language, + but <productname>PostgreSQL</productname> allows only an XPath 1.0 + expression, as discussed in + <xref linkend="functions-xml-limits-xpath1"/>. </para> </sect3> @@ -10482,12 +10494,12 @@ SELECT xml_is_well_formed_document('<pg:foo xmlns:pg="http://postgresql.org/stuf </synopsis> <para> - The function <function>xpath</function> evaluates the XPath - expression <replaceable>xpath</replaceable> (a <type>text</> value) + The function <function>xpath</function> evaluates the XPath 1.0 + expression <replaceable>xpath</replaceable> (a <type>text</type> value) against the XML value <replaceable>xml</replaceable>. It returns an array of XML values - corresponding to the node set produced by the XPath expression. - If the XPath expression returns a scalar value rather than a node set, + corresponding to the node-set produced by the XPath expression. + If the XPath expression returns a scalar value rather than a node-set, a single-element array is returned. </para> @@ -10549,9 +10561,10 @@ SELECT xpath('//mydefns:b/text()', '<a xmlns="http://example.com"><b>test</b></a <para> The function <function>xpath_exists</function> is a specialized form of the <function>xpath</function> function. Instead of returning the - individual XML values that satisfy the XPath, this function returns a - Boolean indicating whether the query was satisfied or not. This - function is equivalent to the standard <literal>XMLEXISTS</> predicate, + individual XML values that satisfy the XPath 1.0 expression, this function + returns a Boolean indicating whether the query was satisfied or not + (specifically, whether it produced any value other than an empty node-set). + This function is equivalent to the <literal>XMLEXISTS</literal> predicate, except that it also offers support for a namespace mapping argument. </para> @@ -10592,8 +10605,8 @@ SELECT xpath_exists('/my:a/text()', '<my:a xmlns:my="http://example.com">test</m <para> The <function>xmltable</function> function produces a table based - on the given XML value, an XPath filter to extract rows, and an - optional set of column definitions. + on the given XML value, an XPath filter to extract rows, and a + set of column definitions. </para> <para> @@ -10604,30 +10617,34 @@ SELECT xpath_exists('/my:a/text()', '<my:a xmlns:my="http://example.com">test</m </para> <para> - The required <replaceable>row_expression</> argument is an XPath - expression that is evaluated against the supplied XML document to - obtain an ordered sequence of XML nodes. This sequence is what - <function>xmltable</> transforms into output rows. + The required <replaceable>row_expression</replaceable> argument is + an XPath 1.0 expression that is evaluated, passing the + <replaceable>document_expression</replaceable> as its context item, to + obtain a set of XML nodes. These nodes are what + <function>xmltable</function> transforms into output rows. No rows + will be produced if the <replaceable>document_expression</replaceable> + is null, nor if the <replaceable>row_expression</replaceable> produces + an empty node-set or any value other than a node-set. </para> <para> - <replaceable>document_expression</> provides the XML document to - operate on. - The <literal>BY REF</literal> clauses have no effect in PostgreSQL, - but are allowed for SQL conformance and compatibility with other - implementations. - The argument must be a well-formed XML document; fragments/forests - are not accepted. + <replaceable>document_expression</replaceable> provides the context + item for the <replaceable>row_expression</replaceable>. It must be a + well-formed XML document; fragments/forests are not accepted. + The <literal>BY REF</literal> clauses + are accepted but ignored, as discussed in + <xref linkend="functions-xml-limits-postgresql"/>. + In the SQL standard, the <function>xmltable</function> function + evaluates expressions in the XML Query language, + but <productname>PostgreSQL</productname> allows only XPath 1.0 + expressions, as discussed in + <xref linkend="functions-xml-limits-xpath1"/>. </para> <para> The mandatory <literal>COLUMNS</literal> clause specifies the list of columns in the output table. - If the <literal>COLUMNS</> clause is omitted, the rows in the result - set contain a single column of type <literal>xml</> containing the - data matched by <replaceable>row_expression</>. - If <literal>COLUMNS</literal> is specified, each entry describes a - single column. + Each entry describes a single column. See the syntax summary above for the format. The column name and type are required; the path, default and nullability clauses are optional. @@ -10635,48 +10652,57 @@ SELECT xpath_exists('/my:a/text()', '<my:a xmlns:my="http://example.com">test</m <para> A column marked <literal>FOR ORDINALITY</literal> will be populated - with row numbers matching the order in which the - output rows appeared in the original input XML document. + with row numbers, starting with 1, in the order of nodes retrieved from + the <replaceable>row_expression</replaceable>'s result node-set. At most one column may be marked <literal>FOR ORDINALITY</literal>. </para> + <note> + <para> + XPath 1.0 does not specify an order for nodes in a node-set, so code + that relies on a particular order of the results will be + implementation-dependent. Details can be found in + <xref linkend="xml-xpath-1-specifics"/>. + </para> + </note> + <para> - The <literal>column_expression</> for a column is an XPath expression - that is evaluated for each row, relative to the result of the - <replaceable>row_expression</>, to find the value of the column. - If no <literal>column_expression</> is given, then the column name - is used as an implicit path. + The <replaceable>column_expression</replaceable> for a column is an + XPath 1.0 expression that is evaluated for each row, with the current + node from the <replaceable>row_expression</replaceable> result as its + context item, to find the value of the column. If + no <replaceable>column_expression</replaceable> is given, then the + column name is used as an implicit path. </para> <para> - If a column's XPath expression returns multiple elements, an error - is raised. - If the expression matches an empty tag, the result is an - empty string (not <literal>NULL</>). - Any <literal>xsi:nil</> attributes are ignored. + If a column's XPath expression returns a non-XML value (limited to + string, boolean, or double in XPath 1.0) and the column has a + PostgreSQL type other than <type>xml</type>, the column will be set + as if by assigning the value's string representation to the PostgreSQL + type. In this release, an XPath boolean or double result must be explicitly + cast to string (that is, the XPath 1.0 <function>string</function> function + wrapped around the original column expression); + <productname>PostgreSQL</productname> can then successfully assign the + string to an SQL result column of boolean or double type. + These conversion rules differ from those of the SQL + standard, as discussed in <xref linkend="functions-xml-limits-casts"/>. </para> <para> - The text body of the XML matched by the <replaceable>column_expression</> - is used as the column value. Multiple <literal>text()</literal> nodes - within an element are concatenated in order. Any child elements, - processing instructions, and comments are ignored, but the text contents - of child elements are concatenated to the result. - Note that the whitespace-only <literal>text()</> node between two non-text - elements is preserved, and that leading whitespace on a <literal>text()</> - node is not flattened. + In this release, SQL result columns of <type>xml</type> type, or + column XPath expressions evaluating to an XML type, regardless of the + output column SQL type, are handled as described in + <xref linkend="functions-xml-limits-postgresql"/>; the behavior + changes significantly in <productname>PostgreSQL 12</productname>. </para> <para> - If the path expression does not match for a given row but - <replaceable>default_expression</> is specified, the value resulting - from evaluating that expression is used. - If no <literal>DEFAULT</> clause is given for the column, - the field will be set to <literal>NULL</>. - It is possible for a <replaceable>default_expression</> to reference - the value of output columns that appear prior to it in the column list, - so the default of one column may be based on the value of another - column. + If the path expression returns an empty node-set + (typically, when it does not match) + for a given row, the column will be set to <literal>NULL</literal>, unless + a <replaceable>default_expression</replaceable> is specified; then the + value resulting from evaluating that expression is used. </para> <para> @@ -10688,20 +10714,14 @@ SELECT xpath_exists('/my:a/text()', '<my:a xmlns:my="http://example.com">test</m </para> <para> - Unlike regular PostgreSQL functions, <replaceable>column_expression</> - and <replaceable>default_expression</> are not evaluated to a simple - value before calling the function. - <replaceable>column_expression</> is normally evaluated - exactly once per input row, and <replaceable>default_expression</> - is evaluated each time a default is needed for a field. - If the expression qualifies as stable or immutable the repeat + A <replaceable>default_expression</replaceable>, rather than being + evaluated immediately when <function>xmltable</function> is called, + is evaluated each time a default is needed for the column. + If the expression qualifies as stable or immutable, the repeat evaluation may be skipped. - Effectively <function>xmltable</> behaves more like a subquery than a - function call. This means that you can usefully use volatile functions like - <function>nextval</> in <replaceable>default_expression</>, and - <replaceable>column_expression</> may depend on other parts of the - XML document. + <function>nextval</function> in + <replaceable>default_expression</replaceable>. </para> <para> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index 8e746f3..ccd3450 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -593,7 +593,7 @@ X085 Predefined namespace prefixes NO X086 XML namespace declarations in XMLTable NO X090 XML document predicate YES X091 XML content predicate NO -X096 XMLExists NO XPath only +X096 XMLExists NO XPath 1.0 only X100 Host language support for XML: CONTENT option NO X101 Host language support for XML: DOCUMENT option NO X110 Host language support for XML: VARCHAR mapping NO @@ -661,11 +661,11 @@ X282 XMLValidate with CONTENT option NO X283 XMLValidate with SEQUENCE option NO X284 XMLValidate: NAMESPACE without ELEMENT clause NO X286 XMLValidate: NO NAMESPACE with ELEMENT clause NO -X300 XMLTable NO XPath only +X300 XMLTable NO XPath 1.0 only X301 XMLTable: derived column list option YES X302 XMLTable: ordinality column option YES X303 XMLTable: column default option YES -X304 XMLTable: passing a context item YES +X304 XMLTable: passing a context item YES must be XML DOCUMENT X305 XMLTable: initializing an XQuery variable NO X400 Name and identifier mapping YES X410 Alter column data type: XML type YES -- 2.7.3 --------------060806070207090601090509-- ^ permalink raw reply [nested|flat] 10+ messages in thread
* [PATCH v29 10/11] Add regression tests for Incremental View Maintenance @ 2021-03-10 02:11 Takuma Hoshiai <[email protected]> 0 siblings, 0 replies; 10+ messages in thread From: Takuma Hoshiai @ 2021-03-10 02:11 UTC (permalink / raw) --- .../regress/expected/incremental_matview.out | 1030 +++++++++++++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/sql/incremental_matview.sql | 533 +++++++++ 3 files changed, 1564 insertions(+), 1 deletion(-) create mode 100644 src/test/regress/expected/incremental_matview.out create mode 100644 src/test/regress/sql/incremental_matview.sql diff --git a/src/test/regress/expected/incremental_matview.out b/src/test/regress/expected/incremental_matview.out new file mode 100644 index 0000000000..8946d09f5d --- /dev/null +++ b/src/test/regress/expected/incremental_matview.out @@ -0,0 +1,1030 @@ +-- create a table to use as a basis for views and materialized views in various combinations +CREATE TABLE mv_base_a (i int, j int); +INSERT INTO mv_base_a VALUES + (1,10), + (2,20), + (3,30), + (4,40), + (5,50); +CREATE TABLE mv_base_b (i int, k int); +INSERT INTO mv_base_b VALUES + (1,101), + (2,102), + (3,103), + (4,104); +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_1 AS SELECT i,j,k FROM mv_base_a a INNER JOIN mv_base_b b USING(i) WITH NO DATA; +SELECT * FROM mv_ivm_1 ORDER BY 1,2,3; +ERROR: materialized view "mv_ivm_1" has not been populated +HINT: Use the REFRESH MATERIALIZED VIEW command. +REFRESH MATERIALIZED VIEW mv_ivm_1; +NOTICE: could not create an index on materialized view "mv_ivm_1" automatically +DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause. +HINT: Create an index on the materialized view for efficient incremental maintenance. +SELECT * FROM mv_ivm_1 ORDER BY 1,2,3; + i | j | k +---+----+----- + 1 | 10 | 101 + 2 | 20 | 102 + 3 | 30 | 103 + 4 | 40 | 104 +(4 rows) + +-- REFRESH WITH NO DATA +BEGIN; +CREATE FUNCTION dummy_ivm_trigger_func() RETURNS TRIGGER AS $$ + BEGIN + RETURN NULL; + END +$$ language plpgsql; +CREATE CONSTRAINT TRIGGER dummy_ivm_trigger AFTER INSERT +ON mv_base_a FROM mv_ivm_1 FOR EACH ROW +EXECUTE PROCEDURE dummy_ivm_trigger_func(); +SELECT COUNT(*) +FROM pg_depend pd INNER JOIN pg_trigger pt ON pd.objid = pt.oid +WHERE pd.classid = 'pg_trigger'::regclass AND pd.refobjid = 'mv_ivm_1'::regclass; + count +------- + 17 +(1 row) + +REFRESH MATERIALIZED VIEW mv_ivm_1 WITH NO DATA; +SELECT COUNT(*) +FROM pg_depend pd INNER JOIN pg_trigger pt ON pd.objid = pt.oid +WHERE pd.classid = 'pg_trigger'::regclass AND pd.refobjid = 'mv_ivm_1'::regclass; + count +------- + 1 +(1 row) + +ROLLBACK; +-- immediate maintenance +BEGIN; +INSERT INTO mv_base_b VALUES(5,105); +SELECT * FROM mv_ivm_1 ORDER BY 1,2,3; + i | j | k +---+----+----- + 1 | 10 | 101 + 2 | 20 | 102 + 3 | 30 | 103 + 4 | 40 | 104 + 5 | 50 | 105 +(5 rows) + +UPDATE mv_base_a SET j = 0 WHERE i = 1; +SELECT * FROM mv_ivm_1 ORDER BY 1,2,3; + i | j | k +---+----+----- + 1 | 0 | 101 + 2 | 20 | 102 + 3 | 30 | 103 + 4 | 40 | 104 + 5 | 50 | 105 +(5 rows) + +DELETE FROM mv_base_b WHERE (i,k) = (5,105); +SELECT * FROM mv_ivm_1 ORDER BY 1,2,3; + i | j | k +---+----+----- + 1 | 0 | 101 + 2 | 20 | 102 + 3 | 30 | 103 + 4 | 40 | 104 +(4 rows) + +ROLLBACK; +SELECT * FROM mv_ivm_1 ORDER BY 1,2,3; + i | j | k +---+----+----- + 1 | 10 | 101 + 2 | 20 | 102 + 3 | 30 | 103 + 4 | 40 | 104 +(4 rows) + +-- rename of IVM columns +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_rename AS SELECT DISTINCT * FROM mv_base_a; +NOTICE: created index "mv_ivm_rename_index" on materialized view "mv_ivm_rename" +ALTER MATERIALIZED VIEW mv_ivm_rename RENAME COLUMN __ivm_count__ TO xxx; +ERROR: IVM column can not be renamed +DROP MATERIALIZED VIEW mv_ivm_rename; +-- unique index on IVM columns +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_unique AS SELECT DISTINCT * FROM mv_base_a; +NOTICE: created index "mv_ivm_unique_index" on materialized view "mv_ivm_unique" +CREATE UNIQUE INDEX ON mv_ivm_unique(__ivm_count__); +ERROR: unique index creation on IVM columns is not supported +CREATE UNIQUE INDEX ON mv_ivm_unique((__ivm_count__)); +ERROR: unique index creation on IVM columns is not supported +CREATE UNIQUE INDEX ON mv_ivm_unique((__ivm_count__ + 1)); +ERROR: unique index creation on IVM columns is not supported +DROP MATERIALIZED VIEW mv_ivm_unique; +-- TRUNCATE a base table in join views +BEGIN; +TRUNCATE mv_base_a; +SELECT * FROM mv_ivm_1; + i | j | k +---+---+--- +(0 rows) + +ROLLBACK; +BEGIN; +TRUNCATE mv_base_b; +SELECT * FROM mv_ivm_1; + i | j | k +---+---+--- +(0 rows) + +ROLLBACK; +-- some query syntax +BEGIN; +CREATE FUNCTION ivm_func() RETURNS int LANGUAGE 'sql' + AS 'SELECT 1' IMMUTABLE; +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_func AS SELECT * FROM ivm_func(); +NOTICE: could not create an index on materialized view "mv_ivm_func" automatically +DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause. +HINT: Create an index on the materialized view for efficient incremental maintenance. +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_no_tbl AS SELECT 1; +NOTICE: could not create an index on materialized view "mv_ivm_no_tbl" automatically +DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause. +HINT: Create an index on the materialized view for efficient incremental maintenance. +ROLLBACK; +-- result of materialized view have DISTINCT clause or the duplicate result. +BEGIN; +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_duplicate AS SELECT j FROM mv_base_a; +NOTICE: could not create an index on materialized view "mv_ivm_duplicate" automatically +DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause. +HINT: Create an index on the materialized view for efficient incremental maintenance. +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_distinct AS SELECT DISTINCT j FROM mv_base_a; +NOTICE: created index "mv_ivm_distinct_index" on materialized view "mv_ivm_distinct" +INSERT INTO mv_base_a VALUES(6,20); +SELECT * FROM mv_ivm_duplicate ORDER BY 1; + j +---- + 10 + 20 + 20 + 30 + 40 + 50 +(6 rows) + +SELECT * FROM mv_ivm_distinct ORDER BY 1; + j +---- + 10 + 20 + 30 + 40 + 50 +(5 rows) + +DELETE FROM mv_base_a WHERE (i,j) = (2,20); +SELECT * FROM mv_ivm_duplicate ORDER BY 1; + j +---- + 10 + 20 + 30 + 40 + 50 +(5 rows) + +SELECT * FROM mv_ivm_distinct ORDER BY 1; + j +---- + 10 + 20 + 30 + 40 + 50 +(5 rows) + +ROLLBACK; +-- support SUM(), COUNT() and AVG() aggregate functions +BEGIN; +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_agg AS SELECT i, SUM(j), COUNT(i), AVG(j) FROM mv_base_a GROUP BY i; +NOTICE: created index "mv_ivm_agg_index" on materialized view "mv_ivm_agg" +SELECT * FROM mv_ivm_agg ORDER BY 1,2,3,4; + i | sum | count | avg +---+-----+-------+--------------------- + 1 | 10 | 1 | 10.0000000000000000 + 2 | 20 | 1 | 20.0000000000000000 + 3 | 30 | 1 | 30.0000000000000000 + 4 | 40 | 1 | 40.0000000000000000 + 5 | 50 | 1 | 50.0000000000000000 +(5 rows) + +INSERT INTO mv_base_a VALUES(2,100); +SELECT * FROM mv_ivm_agg ORDER BY 1,2,3,4; + i | sum | count | avg +---+-----+-------+--------------------- + 1 | 10 | 1 | 10.0000000000000000 + 2 | 120 | 2 | 60.0000000000000000 + 3 | 30 | 1 | 30.0000000000000000 + 4 | 40 | 1 | 40.0000000000000000 + 5 | 50 | 1 | 50.0000000000000000 +(5 rows) + +UPDATE mv_base_a SET j = 200 WHERE (i,j) = (2,100); +SELECT * FROM mv_ivm_agg ORDER BY 1,2,3,4; + i | sum | count | avg +---+-----+-------+---------------------- + 1 | 10 | 1 | 10.0000000000000000 + 2 | 220 | 2 | 110.0000000000000000 + 3 | 30 | 1 | 30.0000000000000000 + 4 | 40 | 1 | 40.0000000000000000 + 5 | 50 | 1 | 50.0000000000000000 +(5 rows) + +DELETE FROM mv_base_a WHERE (i,j) = (2,200); +SELECT * FROM mv_ivm_agg ORDER BY 1,2,3,4; + i | sum | count | avg +---+-----+-------+--------------------- + 1 | 10 | 1 | 10.0000000000000000 + 2 | 20 | 1 | 20.0000000000000000 + 3 | 30 | 1 | 30.0000000000000000 + 4 | 40 | 1 | 40.0000000000000000 + 5 | 50 | 1 | 50.0000000000000000 +(5 rows) + +ROLLBACK; +-- support COUNT(*) aggregate function +BEGIN; +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_agg AS SELECT i, SUM(j), COUNT(*) FROM mv_base_a GROUP BY i; +NOTICE: created index "mv_ivm_agg_index" on materialized view "mv_ivm_agg" +SELECT * FROM mv_ivm_agg ORDER BY 1,2,3; + i | sum | count +---+-----+------- + 1 | 10 | 1 + 2 | 20 | 1 + 3 | 30 | 1 + 4 | 40 | 1 + 5 | 50 | 1 +(5 rows) + +INSERT INTO mv_base_a VALUES(2,100); +SELECT * FROM mv_ivm_agg ORDER BY 1,2,3; + i | sum | count +---+-----+------- + 1 | 10 | 1 + 2 | 120 | 2 + 3 | 30 | 1 + 4 | 40 | 1 + 5 | 50 | 1 +(5 rows) + +ROLLBACK; +-- TRUNCATE a base table in aggregate views +BEGIN; +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_agg AS SELECT i, SUM(j), COUNT(*) FROM mv_base_a GROUP BY i; +NOTICE: created index "mv_ivm_agg_index" on materialized view "mv_ivm_agg" +TRUNCATE mv_base_a; +SELECT sum, count FROM mv_ivm_agg; + sum | count +-----+------- +(0 rows) + +SELECT i, SUM(j), COUNT(*) FROM mv_base_a GROUP BY i; + i | sum | count +---+-----+------- +(0 rows) + +ROLLBACK; +-- support aggregate functions without GROUP clause +BEGIN; +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_group AS SELECT SUM(j), COUNT(j), AVG(j) FROM mv_base_a; +NOTICE: could not create an index on materialized view "mv_ivm_group" automatically +DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause. +HINT: Create an index on the materialized view for efficient incremental maintenance. +SELECT * FROM mv_ivm_group ORDER BY 1; + sum | count | avg +-----+-------+--------------------- + 150 | 5 | 30.0000000000000000 +(1 row) + +INSERT INTO mv_base_a VALUES(6,60); +SELECT * FROM mv_ivm_group ORDER BY 1; + sum | count | avg +-----+-------+--------------------- + 210 | 6 | 35.0000000000000000 +(1 row) + +DELETE FROM mv_base_a; +SELECT * FROM mv_ivm_group ORDER BY 1; + sum | count | avg +-----+-------+----- + | 0 | +(1 row) + +ROLLBACK; +-- TRUNCATE a base table in aggregate views without GROUP clause +BEGIN; +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_group AS SELECT SUM(j), COUNT(j), AVG(j) FROM mv_base_a; +NOTICE: could not create an index on materialized view "mv_ivm_group" automatically +DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause. +HINT: Create an index on the materialized view for efficient incremental maintenance. +TRUNCATE mv_base_a; +SELECT sum, count, avg FROM mv_ivm_group; + sum | count | avg +-----+-------+----- + | 0 | +(1 row) + +SELECT SUM(j), COUNT(j), AVG(j) FROM mv_base_a; + sum | count | avg +-----+-------+----- + | 0 | +(1 row) + +ROLLBACK; +-- resolved issue: When use AVG() function and values is indivisible, result of AVG() is incorrect. +BEGIN; +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_avg_bug AS SELECT i, SUM(j), COUNT(j), AVG(j) FROM mv_base_A GROUP BY i; +NOTICE: created index "mv_ivm_avg_bug_index" on materialized view "mv_ivm_avg_bug" +SELECT * FROM mv_ivm_avg_bug ORDER BY 1,2,3; + i | sum | count | avg +---+-----+-------+--------------------- + 1 | 10 | 1 | 10.0000000000000000 + 2 | 20 | 1 | 20.0000000000000000 + 3 | 30 | 1 | 30.0000000000000000 + 4 | 40 | 1 | 40.0000000000000000 + 5 | 50 | 1 | 50.0000000000000000 +(5 rows) + +INSERT INTO mv_base_a VALUES + (1,0), + (1,0), + (2,30), + (2,30); +SELECT * FROM mv_ivm_avg_bug ORDER BY 1,2,3; + i | sum | count | avg +---+-----+-------+--------------------- + 1 | 10 | 3 | 3.3333333333333333 + 2 | 80 | 3 | 26.6666666666666667 + 3 | 30 | 1 | 30.0000000000000000 + 4 | 40 | 1 | 40.0000000000000000 + 5 | 50 | 1 | 50.0000000000000000 +(5 rows) + +DELETE FROM mv_base_a WHERE (i,j) = (1,0); +DELETE FROM mv_base_a WHERE (i,j) = (2,30); +SELECT * FROM mv_ivm_avg_bug ORDER BY 1,2,3; + i | sum | count | avg +---+-----+-------+--------------------- + 1 | 10 | 1 | 10.0000000000000000 + 2 | 20 | 1 | 20.0000000000000000 + 3 | 30 | 1 | 30.0000000000000000 + 4 | 40 | 1 | 40.0000000000000000 + 5 | 50 | 1 | 50.0000000000000000 +(5 rows) + +ROLLBACK; +-- support MIN(), MAX() aggregate functions +BEGIN; +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_min_max AS SELECT i, MIN(j), MAX(j) FROM mv_base_a GROUP BY i; +NOTICE: created index "mv_ivm_min_max_index" on materialized view "mv_ivm_min_max" +SELECT * FROM mv_ivm_min_max ORDER BY 1,2,3; + i | min | max +---+-----+----- + 1 | 10 | 10 + 2 | 20 | 20 + 3 | 30 | 30 + 4 | 40 | 40 + 5 | 50 | 50 +(5 rows) + +INSERT INTO mv_base_a VALUES + (1,11), (1,12), + (2,21), (2,22), + (3,31), (3,32), + (4,41), (4,42), + (5,51), (5,52); +SELECT * FROM mv_ivm_min_max ORDER BY 1,2,3; + i | min | max +---+-----+----- + 1 | 10 | 12 + 2 | 20 | 22 + 3 | 30 | 32 + 4 | 40 | 42 + 5 | 50 | 52 +(5 rows) + +DELETE FROM mv_base_a WHERE (i,j) IN ((1,10), (2,21), (3,32)); +SELECT * FROM mv_ivm_min_max ORDER BY 1,2,3; + i | min | max +---+-----+----- + 1 | 11 | 12 + 2 | 20 | 22 + 3 | 30 | 31 + 4 | 40 | 42 + 5 | 50 | 52 +(5 rows) + +ROLLBACK; +-- support MIN(), MAX() aggregate functions without GROUP clause +BEGIN; +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_min_max AS SELECT MIN(j), MAX(j) FROM mv_base_a; +NOTICE: could not create an index on materialized view "mv_ivm_min_max" automatically +DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause. +HINT: Create an index on the materialized view for efficient incremental maintenance. +SELECT * FROM mv_ivm_min_max; + min | max +-----+----- + 10 | 50 +(1 row) + +INSERT INTO mv_base_a VALUES + (0,0), (6,60), (7,70); +SELECT * FROM mv_ivm_min_max; + min | max +-----+----- + 0 | 70 +(1 row) + +DELETE FROM mv_base_a WHERE (i,j) IN ((0,0), (7,70)); +SELECT * FROM mv_ivm_min_max; + min | max +-----+----- + 10 | 60 +(1 row) + +DELETE FROM mv_base_a; +SELECT * FROM mv_ivm_min_max; + min | max +-----+----- + | +(1 row) + +ROLLBACK; +-- Test MIN/MAX after search_path change +BEGIN; +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_min AS SELECT MIN(j) FROM mv_base_a; +NOTICE: could not create an index on materialized view "mv_ivm_min" automatically +DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause. +HINT: Create an index on the materialized view for efficient incremental maintenance. +SELECT * FROM mv_ivm_min; + min +----- + 10 +(1 row) + +CREATE SCHEMA myschema; +GRANT ALL ON SCHEMA myschema TO public; +CREATE TABLE myschema.mv_base_a (j int); +INSERT INTO myschema.mv_base_a VALUES (1); +DELETE FROM mv_base_a WHERE (i,j) = (1,10); +SELECT * FROM mv_ivm_min; + min +----- + 20 +(1 row) + +SET search_path TO myschema,public,pg_catalog; +DELETE FROM public.mv_base_a WHERE (i,j) = (2,20); +SELECT * FROM mv_ivm_min; + min +----- + 30 +(1 row) + +ROLLBACK; +-- aggregate views with column names specified +BEGIN; +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_agg(a) AS SELECT i, SUM(j) FROM mv_base_a GROUP BY i; +NOTICE: created index "mv_ivm_agg_index" on materialized view "mv_ivm_agg" +INSERT INTO mv_base_a VALUES (1,100), (2,200), (3,300); +UPDATE mv_base_a SET j = 2000 WHERE (i,j) = (2,20); +DELETE FROM mv_base_a WHERE (i,j) = (3,30); +SELECT * FROM mv_ivm_agg ORDER BY 1,2; + a | sum +---+------ + 1 | 110 + 2 | 2200 + 3 | 300 + 4 | 40 + 5 | 50 +(5 rows) + +ROLLBACK; +BEGIN; +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_agg(a,b) AS SELECT i, SUM(j) FROM mv_base_a GROUP BY i; +NOTICE: created index "mv_ivm_agg_index" on materialized view "mv_ivm_agg" +INSERT INTO mv_base_a VALUES (1,100), (2,200), (3,300); +UPDATE mv_base_a SET j = 2000 WHERE (i,j) = (2,20); +DELETE FROM mv_base_a WHERE (i,j) = (3,30); +SELECT * FROM mv_ivm_agg ORDER BY 1,2; + a | b +---+------ + 1 | 110 + 2 | 2200 + 3 | 300 + 4 | 40 + 5 | 50 +(5 rows) + +ROLLBACK; +BEGIN; +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_agg(a,b,c) AS SELECT i, SUM(j) FROM mv_base_a GROUP BY i; +ERROR: too many column names were specified +ROLLBACK; +-- support self join view and multiple change on the same table +BEGIN; +CREATE TABLE base_t (i int, v int); +INSERT INTO base_t VALUES (1, 10), (2, 20), (3, 30); +CREATE INCREMENTAL MATERIALIZED VIEW mv_self(v1, v2) AS + SELECT t1.v, t2.v FROM base_t AS t1 JOIN base_t AS t2 ON t1.i = t2.i; +NOTICE: could not create an index on materialized view "mv_self" automatically +DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause. +HINT: Create an index on the materialized view for efficient incremental maintenance. +SELECT * FROM mv_self ORDER BY v1; + v1 | v2 +----+---- + 10 | 10 + 20 | 20 + 30 | 30 +(3 rows) + +INSERT INTO base_t VALUES (4,40); +DELETE FROM base_t WHERE i = 1; +UPDATE base_t SET v = v*10 WHERE i=2; +SELECT * FROM mv_self ORDER BY v1; + v1 | v2 +-----+----- + 30 | 30 + 40 | 40 + 200 | 200 +(3 rows) + +WITH + ins_t1 AS (INSERT INTO base_t VALUES (5,50) RETURNING 1), + ins_t2 AS (INSERT INTO base_t VALUES (6,60) RETURNING 1), + upd_t AS (UPDATE base_t SET v = v + 100 RETURNING 1), + dlt_t AS (DELETE FROM base_t WHERE i IN (4,5) RETURNING 1) +SELECT NULL; + ?column? +---------- + +(1 row) + +SELECT * FROM mv_self ORDER BY v1; + v1 | v2 +-----+----- + 50 | 50 + 60 | 60 + 130 | 130 + 300 | 300 +(4 rows) + +--- with sub-transactions +SAVEPOINT p1; +INSERT INTO base_t VALUES (7,70); +RELEASE SAVEPOINT p1; +INSERT INTO base_t VALUES (7,77); +SELECT * FROM mv_self ORDER BY v1, v2; + v1 | v2 +-----+----- + 50 | 50 + 60 | 60 + 70 | 70 + 70 | 77 + 77 | 70 + 77 | 77 + 130 | 130 + 300 | 300 +(8 rows) + +ROLLBACK; +-- support simultaneous table changes +BEGIN; +CREATE TABLE base_r (i int, v int); +CREATE TABLE base_s (i int, v int); +INSERT INTO base_r VALUES (1, 10), (2, 20), (3, 30); +INSERT INTO base_s VALUES (1, 100), (2, 200), (3, 300); +CREATE INCREMENTAL MATERIALIZED VIEW mv(v1, v2) AS + SELECT r.v, s.v FROM base_r AS r JOIN base_s AS s USING(i); +NOTICE: could not create an index on materialized view "mv" automatically +DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause. +HINT: Create an index on the materialized view for efficient incremental maintenance. +SELECT * FROM mv ORDER BY v1; + v1 | v2 +----+----- + 10 | 100 + 20 | 200 + 30 | 300 +(3 rows) + +WITH + ins_r AS (INSERT INTO base_r VALUES (1,11) RETURNING 1), + ins_r2 AS (INSERT INTO base_r VALUES (3,33) RETURNING 1), + ins_s AS (INSERT INTO base_s VALUES (2,222) RETURNING 1), + upd_r AS (UPDATE base_r SET v = v + 1000 WHERE i = 2 RETURNING 1), + dlt_s AS (DELETE FROM base_s WHERE i = 3 RETURNING 1) +SELECT NULL; + ?column? +---------- + +(1 row) + +SELECT * FROM mv ORDER BY v1; + v1 | v2 +------+----- + 10 | 100 + 11 | 100 + 1020 | 200 + 1020 | 222 +(4 rows) + +ROLLBACK; +-- support foreign reference constraints +BEGIN; +CREATE TABLE ri1 (i int PRIMARY KEY); +CREATE TABLE ri2 (i int PRIMARY KEY REFERENCES ri1(i) ON UPDATE CASCADE ON DELETE CASCADE, v int); +INSERT INTO ri1 VALUES (1),(2),(3); +INSERT INTO ri2 VALUES (1),(2),(3); +CREATE INCREMENTAL MATERIALIZED VIEW mv_ri(i1, i2) AS + SELECT ri1.i, ri2.i FROM ri1 JOIN ri2 USING(i); +NOTICE: created index "mv_ri_index" on materialized view "mv_ri" +SELECT * FROM mv_ri ORDER BY i1; + i1 | i2 +----+---- + 1 | 1 + 2 | 2 + 3 | 3 +(3 rows) + +UPDATE ri1 SET i=10 where i=1; +DELETE FROM ri1 WHERE i=2; +SELECT * FROM mv_ri ORDER BY i2; + i1 | i2 +----+---- + 3 | 3 + 10 | 10 +(2 rows) + +ROLLBACK; +-- views including NULL +BEGIN; +CREATE TABLE base_t (i int, v int); +INSERT INTO base_t VALUES (1,10),(2, NULL); +CREATE INCREMENTAL MATERIALIZED VIEW mv AS SELECT * FROM base_t; +NOTICE: could not create an index on materialized view "mv" automatically +DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause. +HINT: Create an index on the materialized view for efficient incremental maintenance. +SELECT * FROM mv ORDER BY i; + i | v +---+---- + 1 | 10 + 2 | +(2 rows) + +UPDATE base_t SET v = 20 WHERE i = 2; +SELECT * FROM mv ORDER BY i; + i | v +---+---- + 1 | 10 + 2 | 20 +(2 rows) + +ROLLBACK; +BEGIN; +CREATE TABLE base_t (i int); +CREATE INCREMENTAL MATERIALIZED VIEW mv AS SELECT * FROM base_t; +NOTICE: could not create an index on materialized view "mv" automatically +DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause. +HINT: Create an index on the materialized view for efficient incremental maintenance. +SELECT * FROM mv ORDER BY i; + i +--- +(0 rows) + +INSERT INTO base_t VALUES (1),(NULL); +SELECT * FROM mv ORDER BY i; + i +--- + 1 + +(2 rows) + +ROLLBACK; +BEGIN; +CREATE TABLE base_t (i int, v int); +INSERT INTO base_t VALUES (NULL, 1), (NULL, 2), (1, 10), (1, 20); +CREATE INCREMENTAL MATERIALIZED VIEW mv AS SELECT i, sum(v) FROM base_t GROUP BY i; +NOTICE: created index "mv_index" on materialized view "mv" +SELECT * FROM mv ORDER BY i; + i | sum +---+----- + 1 | 30 + | 3 +(2 rows) + +UPDATE base_t SET v = v * 10; +SELECT * FROM mv ORDER BY i; + i | sum +---+----- + 1 | 300 + | 30 +(2 rows) + +ROLLBACK; +BEGIN; +CREATE TABLE base_t (i int, v int); +INSERT INTO base_t VALUES (NULL, 1), (NULL, 2), (NULL, 3), (NULL, 4), (NULL, 5); +CREATE INCREMENTAL MATERIALIZED VIEW mv AS SELECT i, min(v), max(v) FROM base_t GROUP BY i; +NOTICE: created index "mv_index" on materialized view "mv" +SELECT * FROM mv ORDER BY i; + i | min | max +---+-----+----- + | 1 | 5 +(1 row) + +DELETE FROM base_t WHERE v = 1; +SELECT * FROM mv ORDER BY i; + i | min | max +---+-----+----- + | 2 | 5 +(1 row) + +DELETE FROM base_t WHERE v = 3; +SELECT * FROM mv ORDER BY i; + i | min | max +---+-----+----- + | 2 | 5 +(1 row) + +DELETE FROM base_t WHERE v = 5; +SELECT * FROM mv ORDER BY i; + i | min | max +---+-----+----- + | 2 | 4 +(1 row) + +ROLLBACK; +-- IMMV containing user defined type +BEGIN; +CREATE TYPE mytype; +CREATE FUNCTION mytype_in(cstring) + RETURNS mytype AS 'int4in' + LANGUAGE INTERNAL STRICT IMMUTABLE; +NOTICE: return type mytype is only a shell +CREATE FUNCTION mytype_out(mytype) + RETURNS cstring AS 'int4out' + LANGUAGE INTERNAL STRICT IMMUTABLE; +NOTICE: argument type mytype is only a shell +CREATE TYPE mytype ( + LIKE = int4, + INPUT = mytype_in, + OUTPUT = mytype_out +); +CREATE FUNCTION mytype_eq(mytype, mytype) + RETURNS bool AS 'int4eq' + LANGUAGE INTERNAL STRICT IMMUTABLE; +CREATE FUNCTION mytype_lt(mytype, mytype) + RETURNS bool AS 'int4lt' + LANGUAGE INTERNAL STRICT IMMUTABLE; +CREATE FUNCTION mytype_cmp(mytype, mytype) + RETURNS integer AS 'btint4cmp' + LANGUAGE INTERNAL STRICT IMMUTABLE; +CREATE OPERATOR = ( + leftarg = mytype, rightarg = mytype, + procedure = mytype_eq); +CREATE OPERATOR < ( + leftarg = mytype, rightarg = mytype, + procedure = mytype_lt); +CREATE OPERATOR CLASS mytype_ops + DEFAULT FOR TYPE mytype USING btree AS + OPERATOR 1 <, + OPERATOR 3 = , + FUNCTION 1 mytype_cmp(mytype,mytype); +CREATE TABLE t_mytype (x mytype); +CREATE INCREMENTAL MATERIALIZED VIEW mv_mytype AS + SELECT * FROM t_mytype; +NOTICE: could not create an index on materialized view "mv_mytype" automatically +DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause. +HINT: Create an index on the materialized view for efficient incremental maintenance. +INSERT INTO t_mytype VALUES ('1'::mytype); +SELECT * FROM mv_mytype; + x +--- + 1 +(1 row) + +ROLLBACK; +-- outer join is not supported +CREATE INCREMENTAL MATERIALIZED VIEW mv(a,b) AS SELECT a.i, b.i FROM mv_base_a a LEFT JOIN mv_base_b b ON a.i=b.i; +ERROR: OUTER JOIN is not supported on incrementally maintainable materialized view +-- CTE is not supported +CREATE INCREMENTAL MATERIALIZED VIEW mv AS + WITH b AS ( SELECT * FROM mv_base_b) SELECT a.i,a.j FROM mv_base_a a, b WHERE a.i = b.i; +ERROR: CTE is not supported on incrementally maintainable materialized view +-- contain system column +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm01 AS SELECT i,j,xmin FROM mv_base_a; +ERROR: system column is not supported on incrementally maintainable materialized view +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm02 AS SELECT i,j FROM mv_base_a WHERE xmin = '610'; +ERROR: system column is not supported on incrementally maintainable materialized view +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm04 AS SELECT i,j,xmin::text AS x_min FROM mv_base_a; +ERROR: system column is not supported on incrementally maintainable materialized view +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm06 AS SELECT i,j,xidsend(xmin) AS x_min FROM mv_base_a; +ERROR: system column is not supported on incrementally maintainable materialized view +-- contain subquery +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm03 AS SELECT i,j FROM mv_base_a WHERE i IN (SELECT i FROM mv_base_b WHERE k < 103 ); +ERROR: subquery is not supported on incrementally maintainable materialized view +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm04 AS SELECT a.i,a.j FROM mv_base_a a, (SELECT * FROM mv_base_b) b WHERE a.i = b.i; +ERROR: subquery is not supported on incrementally maintainable materialized view +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm05 AS SELECT i,j, (SELECT k FROM mv_base_b b WHERE a.i = b.i) FROM mv_base_a a; +ERROR: subquery is not supported on incrementally maintainable materialized view +-- contain ORDER BY +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm07 AS SELECT i,j,k FROM mv_base_a a INNER JOIN mv_base_b b USING(i) ORDER BY i,j,k; +ERROR: ORDER BY clause is not supported on incrementally maintainable materialized view +-- contain HAVING +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm08 AS SELECT i,j,k FROM mv_base_a a INNER JOIN mv_base_b b USING(i) GROUP BY i,j,k HAVING SUM(i) > 5; +ERROR: HAVING clause is not supported on incrementally maintainable materialized view +-- contain view or materialized view +CREATE VIEW b_view AS SELECT i,k FROM mv_base_b; +CREATE MATERIALIZED VIEW b_mview AS SELECT i,k FROM mv_base_b; +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm07 AS SELECT a.i,a.j FROM mv_base_a a,b_view b WHERE a.i = b.i; +ERROR: VIEW or MATERIALIZED VIEW is not supported on incrementally maintainable materialized view +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm08 AS SELECT a.i,a.j FROM mv_base_a a,b_mview b WHERE a.i = b.i; +ERROR: VIEW or MATERIALIZED VIEW is not supported on incrementally maintainable materialized view +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm09 AS SELECT a.i,a.j FROM mv_base_a a, (SELECT i, COUNT(*) FROM mv_base_b GROUP BY i) b WHERE a.i = b.i; +ERROR: subquery is not supported on incrementally maintainable materialized view +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm10 AS SELECT a.i,a.j FROM mv_base_a a WHERE EXISTS(SELECT 1 FROM mv_base_b b WHERE a.i = b.i) OR a.i > 5; +ERROR: subquery is not supported on incrementally maintainable materialized view +-- contain mutable functions +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm12 AS SELECT i,j FROM mv_base_a WHERE i = random()::int; +ERROR: mutable function is not supported on incrementally maintainable materialized view +HINT: functions must be marked IMMUTABLE +-- LIMIT/OFFSET is not supported +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm13 AS SELECT i,j FROM mv_base_a LIMIT 10 OFFSET 5; +ERROR: LIMIT/OFFSET clause is not supported on incrementally maintainable materialized view +-- DISTINCT ON is not supported +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm14 AS SELECT DISTINCT ON(i) i, j FROM mv_base_a; +ERROR: DISTINCT ON is not supported on incrementally maintainable materialized view +-- TABLESAMPLE clause is not supported +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm15 AS SELECT i, j FROM mv_base_a TABLESAMPLE SYSTEM(50); +ERROR: TABLESAMPLE clause is not supported on incrementally maintainable materialized view +-- window functions are not supported +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm16 AS SELECT *, cume_dist() OVER (ORDER BY i) AS rank FROM mv_base_a; +ERROR: window functions are not supported on incrementally maintainable materialized view +-- aggregate function with some options is not supported +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm17 AS SELECT COUNT(*) FILTER(WHERE i < 3) FROM mv_base_a; +ERROR: aggregate function with FILTER clause is not supported on incrementally maintainable materialized view +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm18 AS SELECT COUNT(DISTINCT i) FROM mv_base_a; +ERROR: aggregate function with DISTINCT arguments is not supported on incrementally maintainable materialized view +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm19 AS SELECT array_agg(j ORDER BY i DESC) FROM mv_base_a; +ERROR: aggregate function with ORDER clause is not supported on incrementally maintainable materialized view +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm20 AS SELECT i,SUM(j) FROM mv_base_a GROUP BY GROUPING SETS((i),()); +ERROR: GROUPING SETS, ROLLUP, or CUBE clauses is not supported on incrementally maintainable materialized view +-- inheritance parent is not supported +BEGIN; +CREATE TABLE parent (i int, v int); +CREATE TABLE child_a(options text) INHERITS(parent); +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm21 AS SELECT * FROM parent; +ERROR: inheritance parent is not supported on incrementally maintainable materialized view +ROLLBACK; +-- UNION statement is not supported +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm22 AS SELECT i,j FROM mv_base_a UNION ALL SELECT i,k FROM mv_base_b;; +ERROR: UNION/INTERSECT/EXCEPT statements are not supported on incrementally maintainable materialized view +-- empty target list is not allowed with IVM +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm25 AS SELECT FROM mv_base_a; +ERROR: empty target list is not supported on incrementally maintainable materialized view +-- FOR UPDATE/SHARE is not supported +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm26 AS SELECT i,j FROM mv_base_a FOR UPDATE; +ERROR: FOR UPDATE/SHARE clause is not supported on incrementally maintainable materialized view +-- tartget list cannot contain ivm column that start with '__ivm' +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm28 AS SELECT i AS "__ivm_count__" FROM mv_base_a; +ERROR: column name __ivm_count__ is not supported on incrementally maintainable materialized view +-- expressions specified in GROUP BY must appear in the target list. +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm29 AS SELECT COUNT(i) FROM mv_base_a GROUP BY i; +ERROR: GROUP BY expression not appearing in select list is not supported on incrementally maintainable materialized view +-- experssions containing an aggregate is not supported +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm30 AS SELECT sum(i)*0.5 FROM mv_base_a; +ERROR: expression containing an aggregate in it is not supported on incrementally maintainable materialized view +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm31 AS SELECT sum(i)/sum(j) FROM mv_base_a; +ERROR: expression containing an aggregate in it is not supported on incrementally maintainable materialized view +-- VALUES is not supported +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_only_values1 AS values(1); +ERROR: VALUES is not supported on incrementally maintainable materialized view +-- views containing base tables with Row Level Security +DROP USER IF EXISTS ivm_admin; +NOTICE: role "ivm_admin" does not exist, skipping +DROP USER IF EXISTS ivm_user; +NOTICE: role "ivm_user" does not exist, skipping +CREATE USER ivm_admin; +CREATE USER ivm_user; +--- create a table with RLS +SET SESSION AUTHORIZATION ivm_admin; +CREATE TABLE rls_tbl(id int, data text, owner name); +INSERT INTO rls_tbl VALUES + (1,'foo','ivm_user'), + (2,'bar','postgres'); +CREATE TABLE num_tbl(id int, num text); +INSERT INTO num_tbl VALUES + (1,'one'), + (2,'two'), + (3,'three'), + (4,'four'), + (5,'five'), + (6,'six'); +--- Users can access only their own rows +CREATE POLICY rls_tbl_policy ON rls_tbl FOR SELECT TO PUBLIC USING(owner = current_user); +ALTER TABLE rls_tbl ENABLE ROW LEVEL SECURITY; +GRANT ALL on rls_tbl TO PUBLIC; +GRANT ALL on num_tbl TO PUBLIC; +--- create a view owned by ivm_user +SET SESSION AUTHORIZATION ivm_user; +CREATE INCREMENTAL MATERIALIZED VIEW ivm_rls AS SELECT * FROM rls_tbl; +NOTICE: could not create an index on materialized view "ivm_rls" automatically +DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause. +HINT: Create an index on the materialized view for efficient incremental maintenance. +SELECT id, data, owner FROM ivm_rls ORDER BY 1,2,3; + id | data | owner +----+------+---------- + 1 | foo | ivm_user +(1 row) + +RESET SESSION AUTHORIZATION; +--- inserts rows owned by different users +INSERT INTO rls_tbl VALUES + (3,'baz','ivm_user'), + (4,'qux','postgres'); +SELECT id, data, owner FROM ivm_rls ORDER BY 1,2,3; + id | data | owner +----+------+---------- + 1 | foo | ivm_user + 3 | baz | ivm_user +(2 rows) + +--- combination of diffent kinds of commands +WITH + i AS (INSERT INTO rls_tbl VALUES(5,'quux','postgres'), (6,'corge','ivm_user')), + u AS (UPDATE rls_tbl SET owner = 'postgres' WHERE id = 1), + u2 AS (UPDATE rls_tbl SET owner = 'ivm_user' WHERE id = 2) +SELECT; +-- +(1 row) + +SELECT id, data, owner FROM ivm_rls ORDER BY 1,2,3; + id | data | owner +----+-------+---------- + 2 | bar | ivm_user + 3 | baz | ivm_user + 6 | corge | ivm_user +(3 rows) + +--- +SET SESSION AUTHORIZATION ivm_user; +CREATE INCREMENTAL MATERIALIZED VIEW ivm_rls2 AS SELECT * FROM rls_tbl JOIN num_tbl USING(id); +NOTICE: could not create an index on materialized view "ivm_rls2" automatically +DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause. +HINT: Create an index on the materialized view for efficient incremental maintenance. +RESET SESSION AUTHORIZATION; +WITH + x AS (UPDATE rls_tbl SET data = data || '_2' where id in (3,4)), + y AS (UPDATE num_tbl SET num = num || '_2' where id in (3,4)) +SELECT; +-- +(1 row) + +SELECT * FROM ivm_rls2 ORDER BY 1,2,3; + id | data | owner | num +----+-------+----------+--------- + 2 | bar | ivm_user | two + 3 | baz_2 | ivm_user | three_2 + 6 | corge | ivm_user | six +(3 rows) + +-- automatic index creation +CREATE TABLE base_a (i int primary key, j int); +CREATE TABLE base_b (i int primary key, j int); +--- group by: create an index +CREATE INCREMENTAL MATERIALIZED VIEW mv_idx1 AS SELECT i, sum(j) FROM base_a GROUP BY i; +NOTICE: created index "mv_idx1_index" on materialized view "mv_idx1" +--- distinct: create an index +CREATE INCREMENTAL MATERIALIZED VIEW mv_idx2 AS SELECT DISTINCT j FROM base_a; +NOTICE: created index "mv_idx2_index" on materialized view "mv_idx2" +--- with all pkey columns: create an index +CREATE INCREMENTAL MATERIALIZED VIEW mv_idx3(i_a, i_b) AS SELECT a.i, b.i FROM base_a a, base_b b; +NOTICE: created index "mv_idx3_index" on materialized view "mv_idx3" +--- missing some pkey columns: no index +CREATE INCREMENTAL MATERIALIZED VIEW mv_idx4 AS SELECT j FROM base_a; +NOTICE: could not create an index on materialized view "mv_idx4" automatically +DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause. +HINT: Create an index on the materialized view for efficient incremental maintenance. +CREATE INCREMENTAL MATERIALIZED VIEW mv_idx5 AS SELECT a.i, b.j FROM base_a a, base_b b; +NOTICE: could not create an index on materialized view "mv_idx5" automatically +DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause. +HINT: Create an index on the materialized view for efficient incremental maintenance. +-- cleanup +DROP TABLE rls_tbl CASCADE; +NOTICE: drop cascades to 2 other objects +DETAIL: drop cascades to materialized view ivm_rls +drop cascades to materialized view ivm_rls2 +DROP TABLE num_tbl CASCADE; +DROP USER ivm_user; +DROP USER ivm_admin; +DROP TABLE mv_base_b CASCADE; +NOTICE: drop cascades to 3 other objects +DETAIL: drop cascades to materialized view mv_ivm_1 +drop cascades to view b_view +drop cascades to materialized view b_mview +DROP TABLE mv_base_a CASCADE; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 4df9d8503b..21f7247a07 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -78,7 +78,7 @@ test: brin_bloom brin_multi # psql depends on create_am # amutils depends on geometry, create_index_spgist, hash_index, brin # ---------- -test: create_table_like alter_generic alter_operator misc async dbsize merge misc_functions sysviews tsrf tid tidscan tidrangescan collate.icu.utf8 incremental_sort create_role +test: create_table_like alter_generic alter_operator misc async dbsize merge misc_functions sysviews tsrf tid tidscan tidrangescan collate.icu.utf8 incremental_sort create_role incremental_matview # collate.*.utf8 tests cannot be run in parallel with each other test: rules psql psql_crosstab amutils stats_ext collate.linux.utf8 collate.windows.win1252 diff --git a/src/test/regress/sql/incremental_matview.sql b/src/test/regress/sql/incremental_matview.sql new file mode 100644 index 0000000000..82686f9324 --- /dev/null +++ b/src/test/regress/sql/incremental_matview.sql @@ -0,0 +1,533 @@ +-- create a table to use as a basis for views and materialized views in various combinations +CREATE TABLE mv_base_a (i int, j int); +INSERT INTO mv_base_a VALUES + (1,10), + (2,20), + (3,30), + (4,40), + (5,50); +CREATE TABLE mv_base_b (i int, k int); +INSERT INTO mv_base_b VALUES + (1,101), + (2,102), + (3,103), + (4,104); + +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_1 AS SELECT i,j,k FROM mv_base_a a INNER JOIN mv_base_b b USING(i) WITH NO DATA; +SELECT * FROM mv_ivm_1 ORDER BY 1,2,3; +REFRESH MATERIALIZED VIEW mv_ivm_1; +SELECT * FROM mv_ivm_1 ORDER BY 1,2,3; + +-- REFRESH WITH NO DATA +BEGIN; +CREATE FUNCTION dummy_ivm_trigger_func() RETURNS TRIGGER AS $$ + BEGIN + RETURN NULL; + END +$$ language plpgsql; + +CREATE CONSTRAINT TRIGGER dummy_ivm_trigger AFTER INSERT +ON mv_base_a FROM mv_ivm_1 FOR EACH ROW +EXECUTE PROCEDURE dummy_ivm_trigger_func(); + +SELECT COUNT(*) +FROM pg_depend pd INNER JOIN pg_trigger pt ON pd.objid = pt.oid +WHERE pd.classid = 'pg_trigger'::regclass AND pd.refobjid = 'mv_ivm_1'::regclass; + +REFRESH MATERIALIZED VIEW mv_ivm_1 WITH NO DATA; + +SELECT COUNT(*) +FROM pg_depend pd INNER JOIN pg_trigger pt ON pd.objid = pt.oid +WHERE pd.classid = 'pg_trigger'::regclass AND pd.refobjid = 'mv_ivm_1'::regclass; +ROLLBACK; + +-- immediate maintenance +BEGIN; +INSERT INTO mv_base_b VALUES(5,105); +SELECT * FROM mv_ivm_1 ORDER BY 1,2,3; +UPDATE mv_base_a SET j = 0 WHERE i = 1; +SELECT * FROM mv_ivm_1 ORDER BY 1,2,3; +DELETE FROM mv_base_b WHERE (i,k) = (5,105); +SELECT * FROM mv_ivm_1 ORDER BY 1,2,3; +ROLLBACK; +SELECT * FROM mv_ivm_1 ORDER BY 1,2,3; + +-- rename of IVM columns +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_rename AS SELECT DISTINCT * FROM mv_base_a; +ALTER MATERIALIZED VIEW mv_ivm_rename RENAME COLUMN __ivm_count__ TO xxx; +DROP MATERIALIZED VIEW mv_ivm_rename; + +-- unique index on IVM columns +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_unique AS SELECT DISTINCT * FROM mv_base_a; +CREATE UNIQUE INDEX ON mv_ivm_unique(__ivm_count__); +CREATE UNIQUE INDEX ON mv_ivm_unique((__ivm_count__)); +CREATE UNIQUE INDEX ON mv_ivm_unique((__ivm_count__ + 1)); +DROP MATERIALIZED VIEW mv_ivm_unique; + +-- TRUNCATE a base table in join views +BEGIN; +TRUNCATE mv_base_a; +SELECT * FROM mv_ivm_1; +ROLLBACK; + +BEGIN; +TRUNCATE mv_base_b; +SELECT * FROM mv_ivm_1; +ROLLBACK; + +-- some query syntax +BEGIN; +CREATE FUNCTION ivm_func() RETURNS int LANGUAGE 'sql' + AS 'SELECT 1' IMMUTABLE; +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_func AS SELECT * FROM ivm_func(); +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_no_tbl AS SELECT 1; +ROLLBACK; + +-- result of materialized view have DISTINCT clause or the duplicate result. +BEGIN; +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_duplicate AS SELECT j FROM mv_base_a; +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_distinct AS SELECT DISTINCT j FROM mv_base_a; +INSERT INTO mv_base_a VALUES(6,20); +SELECT * FROM mv_ivm_duplicate ORDER BY 1; +SELECT * FROM mv_ivm_distinct ORDER BY 1; +DELETE FROM mv_base_a WHERE (i,j) = (2,20); +SELECT * FROM mv_ivm_duplicate ORDER BY 1; +SELECT * FROM mv_ivm_distinct ORDER BY 1; +ROLLBACK; + +-- support SUM(), COUNT() and AVG() aggregate functions +BEGIN; +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_agg AS SELECT i, SUM(j), COUNT(i), AVG(j) FROM mv_base_a GROUP BY i; +SELECT * FROM mv_ivm_agg ORDER BY 1,2,3,4; +INSERT INTO mv_base_a VALUES(2,100); +SELECT * FROM mv_ivm_agg ORDER BY 1,2,3,4; +UPDATE mv_base_a SET j = 200 WHERE (i,j) = (2,100); +SELECT * FROM mv_ivm_agg ORDER BY 1,2,3,4; +DELETE FROM mv_base_a WHERE (i,j) = (2,200); +SELECT * FROM mv_ivm_agg ORDER BY 1,2,3,4; +ROLLBACK; + +-- support COUNT(*) aggregate function +BEGIN; +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_agg AS SELECT i, SUM(j), COUNT(*) FROM mv_base_a GROUP BY i; +SELECT * FROM mv_ivm_agg ORDER BY 1,2,3; +INSERT INTO mv_base_a VALUES(2,100); +SELECT * FROM mv_ivm_agg ORDER BY 1,2,3; +ROLLBACK; + +-- TRUNCATE a base table in aggregate views +BEGIN; +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_agg AS SELECT i, SUM(j), COUNT(*) FROM mv_base_a GROUP BY i; +TRUNCATE mv_base_a; +SELECT sum, count FROM mv_ivm_agg; +SELECT i, SUM(j), COUNT(*) FROM mv_base_a GROUP BY i; +ROLLBACK; + +-- support aggregate functions without GROUP clause +BEGIN; +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_group AS SELECT SUM(j), COUNT(j), AVG(j) FROM mv_base_a; +SELECT * FROM mv_ivm_group ORDER BY 1; +INSERT INTO mv_base_a VALUES(6,60); +SELECT * FROM mv_ivm_group ORDER BY 1; +DELETE FROM mv_base_a; +SELECT * FROM mv_ivm_group ORDER BY 1; +ROLLBACK; + +-- TRUNCATE a base table in aggregate views without GROUP clause +BEGIN; +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_group AS SELECT SUM(j), COUNT(j), AVG(j) FROM mv_base_a; +TRUNCATE mv_base_a; +SELECT sum, count, avg FROM mv_ivm_group; +SELECT SUM(j), COUNT(j), AVG(j) FROM mv_base_a; +ROLLBACK; + +-- resolved issue: When use AVG() function and values is indivisible, result of AVG() is incorrect. +BEGIN; +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_avg_bug AS SELECT i, SUM(j), COUNT(j), AVG(j) FROM mv_base_A GROUP BY i; +SELECT * FROM mv_ivm_avg_bug ORDER BY 1,2,3; +INSERT INTO mv_base_a VALUES + (1,0), + (1,0), + (2,30), + (2,30); +SELECT * FROM mv_ivm_avg_bug ORDER BY 1,2,3; +DELETE FROM mv_base_a WHERE (i,j) = (1,0); +DELETE FROM mv_base_a WHERE (i,j) = (2,30); +SELECT * FROM mv_ivm_avg_bug ORDER BY 1,2,3; +ROLLBACK; + +-- support MIN(), MAX() aggregate functions +BEGIN; +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_min_max AS SELECT i, MIN(j), MAX(j) FROM mv_base_a GROUP BY i; +SELECT * FROM mv_ivm_min_max ORDER BY 1,2,3; +INSERT INTO mv_base_a VALUES + (1,11), (1,12), + (2,21), (2,22), + (3,31), (3,32), + (4,41), (4,42), + (5,51), (5,52); +SELECT * FROM mv_ivm_min_max ORDER BY 1,2,3; +DELETE FROM mv_base_a WHERE (i,j) IN ((1,10), (2,21), (3,32)); +SELECT * FROM mv_ivm_min_max ORDER BY 1,2,3; +ROLLBACK; + +-- support MIN(), MAX() aggregate functions without GROUP clause +BEGIN; +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_min_max AS SELECT MIN(j), MAX(j) FROM mv_base_a; +SELECT * FROM mv_ivm_min_max; +INSERT INTO mv_base_a VALUES + (0,0), (6,60), (7,70); +SELECT * FROM mv_ivm_min_max; +DELETE FROM mv_base_a WHERE (i,j) IN ((0,0), (7,70)); +SELECT * FROM mv_ivm_min_max; +DELETE FROM mv_base_a; +SELECT * FROM mv_ivm_min_max; +ROLLBACK; + +-- Test MIN/MAX after search_path change +BEGIN; +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_min AS SELECT MIN(j) FROM mv_base_a; +SELECT * FROM mv_ivm_min; + +CREATE SCHEMA myschema; +GRANT ALL ON SCHEMA myschema TO public; +CREATE TABLE myschema.mv_base_a (j int); +INSERT INTO myschema.mv_base_a VALUES (1); + +DELETE FROM mv_base_a WHERE (i,j) = (1,10); +SELECT * FROM mv_ivm_min; + +SET search_path TO myschema,public,pg_catalog; +DELETE FROM public.mv_base_a WHERE (i,j) = (2,20); +SELECT * FROM mv_ivm_min; +ROLLBACK; + +-- aggregate views with column names specified +BEGIN; +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_agg(a) AS SELECT i, SUM(j) FROM mv_base_a GROUP BY i; +INSERT INTO mv_base_a VALUES (1,100), (2,200), (3,300); +UPDATE mv_base_a SET j = 2000 WHERE (i,j) = (2,20); +DELETE FROM mv_base_a WHERE (i,j) = (3,30); +SELECT * FROM mv_ivm_agg ORDER BY 1,2; +ROLLBACK; +BEGIN; +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_agg(a,b) AS SELECT i, SUM(j) FROM mv_base_a GROUP BY i; +INSERT INTO mv_base_a VALUES (1,100), (2,200), (3,300); +UPDATE mv_base_a SET j = 2000 WHERE (i,j) = (2,20); +DELETE FROM mv_base_a WHERE (i,j) = (3,30); +SELECT * FROM mv_ivm_agg ORDER BY 1,2; +ROLLBACK; +BEGIN; +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_agg(a,b,c) AS SELECT i, SUM(j) FROM mv_base_a GROUP BY i; +ROLLBACK; + +-- support self join view and multiple change on the same table +BEGIN; +CREATE TABLE base_t (i int, v int); +INSERT INTO base_t VALUES (1, 10), (2, 20), (3, 30); +CREATE INCREMENTAL MATERIALIZED VIEW mv_self(v1, v2) AS + SELECT t1.v, t2.v FROM base_t AS t1 JOIN base_t AS t2 ON t1.i = t2.i; +SELECT * FROM mv_self ORDER BY v1; +INSERT INTO base_t VALUES (4,40); +DELETE FROM base_t WHERE i = 1; +UPDATE base_t SET v = v*10 WHERE i=2; +SELECT * FROM mv_self ORDER BY v1; +WITH + ins_t1 AS (INSERT INTO base_t VALUES (5,50) RETURNING 1), + ins_t2 AS (INSERT INTO base_t VALUES (6,60) RETURNING 1), + upd_t AS (UPDATE base_t SET v = v + 100 RETURNING 1), + dlt_t AS (DELETE FROM base_t WHERE i IN (4,5) RETURNING 1) +SELECT NULL; +SELECT * FROM mv_self ORDER BY v1; + +--- with sub-transactions +SAVEPOINT p1; +INSERT INTO base_t VALUES (7,70); +RELEASE SAVEPOINT p1; +INSERT INTO base_t VALUES (7,77); +SELECT * FROM mv_self ORDER BY v1, v2; + +ROLLBACK; + +-- support simultaneous table changes +BEGIN; +CREATE TABLE base_r (i int, v int); +CREATE TABLE base_s (i int, v int); +INSERT INTO base_r VALUES (1, 10), (2, 20), (3, 30); +INSERT INTO base_s VALUES (1, 100), (2, 200), (3, 300); +CREATE INCREMENTAL MATERIALIZED VIEW mv(v1, v2) AS + SELECT r.v, s.v FROM base_r AS r JOIN base_s AS s USING(i); +SELECT * FROM mv ORDER BY v1; +WITH + ins_r AS (INSERT INTO base_r VALUES (1,11) RETURNING 1), + ins_r2 AS (INSERT INTO base_r VALUES (3,33) RETURNING 1), + ins_s AS (INSERT INTO base_s VALUES (2,222) RETURNING 1), + upd_r AS (UPDATE base_r SET v = v + 1000 WHERE i = 2 RETURNING 1), + dlt_s AS (DELETE FROM base_s WHERE i = 3 RETURNING 1) +SELECT NULL; +SELECT * FROM mv ORDER BY v1; +ROLLBACK; + +-- support foreign reference constraints +BEGIN; +CREATE TABLE ri1 (i int PRIMARY KEY); +CREATE TABLE ri2 (i int PRIMARY KEY REFERENCES ri1(i) ON UPDATE CASCADE ON DELETE CASCADE, v int); +INSERT INTO ri1 VALUES (1),(2),(3); +INSERT INTO ri2 VALUES (1),(2),(3); +CREATE INCREMENTAL MATERIALIZED VIEW mv_ri(i1, i2) AS + SELECT ri1.i, ri2.i FROM ri1 JOIN ri2 USING(i); +SELECT * FROM mv_ri ORDER BY i1; +UPDATE ri1 SET i=10 where i=1; +DELETE FROM ri1 WHERE i=2; +SELECT * FROM mv_ri ORDER BY i2; +ROLLBACK; + +-- views including NULL +BEGIN; +CREATE TABLE base_t (i int, v int); +INSERT INTO base_t VALUES (1,10),(2, NULL); +CREATE INCREMENTAL MATERIALIZED VIEW mv AS SELECT * FROM base_t; +SELECT * FROM mv ORDER BY i; +UPDATE base_t SET v = 20 WHERE i = 2; +SELECT * FROM mv ORDER BY i; +ROLLBACK; + +BEGIN; +CREATE TABLE base_t (i int); +CREATE INCREMENTAL MATERIALIZED VIEW mv AS SELECT * FROM base_t; +SELECT * FROM mv ORDER BY i; +INSERT INTO base_t VALUES (1),(NULL); +SELECT * FROM mv ORDER BY i; +ROLLBACK; + +BEGIN; +CREATE TABLE base_t (i int, v int); +INSERT INTO base_t VALUES (NULL, 1), (NULL, 2), (1, 10), (1, 20); +CREATE INCREMENTAL MATERIALIZED VIEW mv AS SELECT i, sum(v) FROM base_t GROUP BY i; +SELECT * FROM mv ORDER BY i; +UPDATE base_t SET v = v * 10; +SELECT * FROM mv ORDER BY i; +ROLLBACK; + +BEGIN; +CREATE TABLE base_t (i int, v int); +INSERT INTO base_t VALUES (NULL, 1), (NULL, 2), (NULL, 3), (NULL, 4), (NULL, 5); +CREATE INCREMENTAL MATERIALIZED VIEW mv AS SELECT i, min(v), max(v) FROM base_t GROUP BY i; +SELECT * FROM mv ORDER BY i; +DELETE FROM base_t WHERE v = 1; +SELECT * FROM mv ORDER BY i; +DELETE FROM base_t WHERE v = 3; +SELECT * FROM mv ORDER BY i; +DELETE FROM base_t WHERE v = 5; +SELECT * FROM mv ORDER BY i; +ROLLBACK; + +-- IMMV containing user defined type +BEGIN; + +CREATE TYPE mytype; +CREATE FUNCTION mytype_in(cstring) + RETURNS mytype AS 'int4in' + LANGUAGE INTERNAL STRICT IMMUTABLE; +CREATE FUNCTION mytype_out(mytype) + RETURNS cstring AS 'int4out' + LANGUAGE INTERNAL STRICT IMMUTABLE; +CREATE TYPE mytype ( + LIKE = int4, + INPUT = mytype_in, + OUTPUT = mytype_out +); + +CREATE FUNCTION mytype_eq(mytype, mytype) + RETURNS bool AS 'int4eq' + LANGUAGE INTERNAL STRICT IMMUTABLE; +CREATE FUNCTION mytype_lt(mytype, mytype) + RETURNS bool AS 'int4lt' + LANGUAGE INTERNAL STRICT IMMUTABLE; +CREATE FUNCTION mytype_cmp(mytype, mytype) + RETURNS integer AS 'btint4cmp' + LANGUAGE INTERNAL STRICT IMMUTABLE; + +CREATE OPERATOR = ( + leftarg = mytype, rightarg = mytype, + procedure = mytype_eq); +CREATE OPERATOR < ( + leftarg = mytype, rightarg = mytype, + procedure = mytype_lt); + +CREATE OPERATOR CLASS mytype_ops + DEFAULT FOR TYPE mytype USING btree AS + OPERATOR 1 <, + OPERATOR 3 = , + FUNCTION 1 mytype_cmp(mytype,mytype); + +CREATE TABLE t_mytype (x mytype); +CREATE INCREMENTAL MATERIALIZED VIEW mv_mytype AS + SELECT * FROM t_mytype; +INSERT INTO t_mytype VALUES ('1'::mytype); +SELECT * FROM mv_mytype; + +ROLLBACK; + +-- outer join is not supported +CREATE INCREMENTAL MATERIALIZED VIEW mv(a,b) AS SELECT a.i, b.i FROM mv_base_a a LEFT JOIN mv_base_b b ON a.i=b.i; +-- CTE is not supported +CREATE INCREMENTAL MATERIALIZED VIEW mv AS + WITH b AS ( SELECT * FROM mv_base_b) SELECT a.i,a.j FROM mv_base_a a, b WHERE a.i = b.i; +-- contain system column +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm01 AS SELECT i,j,xmin FROM mv_base_a; +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm02 AS SELECT i,j FROM mv_base_a WHERE xmin = '610'; +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm04 AS SELECT i,j,xmin::text AS x_min FROM mv_base_a; +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm06 AS SELECT i,j,xidsend(xmin) AS x_min FROM mv_base_a; +-- contain subquery +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm03 AS SELECT i,j FROM mv_base_a WHERE i IN (SELECT i FROM mv_base_b WHERE k < 103 ); +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm04 AS SELECT a.i,a.j FROM mv_base_a a, (SELECT * FROM mv_base_b) b WHERE a.i = b.i; +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm05 AS SELECT i,j, (SELECT k FROM mv_base_b b WHERE a.i = b.i) FROM mv_base_a a; +-- contain ORDER BY +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm07 AS SELECT i,j,k FROM mv_base_a a INNER JOIN mv_base_b b USING(i) ORDER BY i,j,k; +-- contain HAVING +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm08 AS SELECT i,j,k FROM mv_base_a a INNER JOIN mv_base_b b USING(i) GROUP BY i,j,k HAVING SUM(i) > 5; + +-- contain view or materialized view +CREATE VIEW b_view AS SELECT i,k FROM mv_base_b; +CREATE MATERIALIZED VIEW b_mview AS SELECT i,k FROM mv_base_b; +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm07 AS SELECT a.i,a.j FROM mv_base_a a,b_view b WHERE a.i = b.i; +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm08 AS SELECT a.i,a.j FROM mv_base_a a,b_mview b WHERE a.i = b.i; +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm09 AS SELECT a.i,a.j FROM mv_base_a a, (SELECT i, COUNT(*) FROM mv_base_b GROUP BY i) b WHERE a.i = b.i; +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm10 AS SELECT a.i,a.j FROM mv_base_a a WHERE EXISTS(SELECT 1 FROM mv_base_b b WHERE a.i = b.i) OR a.i > 5; + +-- contain mutable functions +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm12 AS SELECT i,j FROM mv_base_a WHERE i = random()::int; + +-- LIMIT/OFFSET is not supported +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm13 AS SELECT i,j FROM mv_base_a LIMIT 10 OFFSET 5; + +-- DISTINCT ON is not supported +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm14 AS SELECT DISTINCT ON(i) i, j FROM mv_base_a; + +-- TABLESAMPLE clause is not supported +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm15 AS SELECT i, j FROM mv_base_a TABLESAMPLE SYSTEM(50); + +-- window functions are not supported +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm16 AS SELECT *, cume_dist() OVER (ORDER BY i) AS rank FROM mv_base_a; + +-- aggregate function with some options is not supported +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm17 AS SELECT COUNT(*) FILTER(WHERE i < 3) FROM mv_base_a; +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm18 AS SELECT COUNT(DISTINCT i) FROM mv_base_a; +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm19 AS SELECT array_agg(j ORDER BY i DESC) FROM mv_base_a; +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm20 AS SELECT i,SUM(j) FROM mv_base_a GROUP BY GROUPING SETS((i),()); + +-- inheritance parent is not supported +BEGIN; +CREATE TABLE parent (i int, v int); +CREATE TABLE child_a(options text) INHERITS(parent); +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm21 AS SELECT * FROM parent; +ROLLBACK; + +-- UNION statement is not supported +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm22 AS SELECT i,j FROM mv_base_a UNION ALL SELECT i,k FROM mv_base_b;; + +-- empty target list is not allowed with IVM +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm25 AS SELECT FROM mv_base_a; + +-- FOR UPDATE/SHARE is not supported +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm26 AS SELECT i,j FROM mv_base_a FOR UPDATE; + +-- tartget list cannot contain ivm column that start with '__ivm' +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm28 AS SELECT i AS "__ivm_count__" FROM mv_base_a; + +-- expressions specified in GROUP BY must appear in the target list. +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm29 AS SELECT COUNT(i) FROM mv_base_a GROUP BY i; + +-- experssions containing an aggregate is not supported +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm30 AS SELECT sum(i)*0.5 FROM mv_base_a; +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm31 AS SELECT sum(i)/sum(j) FROM mv_base_a; + +-- VALUES is not supported +CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_only_values1 AS values(1); + +-- views containing base tables with Row Level Security +DROP USER IF EXISTS ivm_admin; +DROP USER IF EXISTS ivm_user; +CREATE USER ivm_admin; +CREATE USER ivm_user; + +--- create a table with RLS +SET SESSION AUTHORIZATION ivm_admin; +CREATE TABLE rls_tbl(id int, data text, owner name); +INSERT INTO rls_tbl VALUES + (1,'foo','ivm_user'), + (2,'bar','postgres'); +CREATE TABLE num_tbl(id int, num text); +INSERT INTO num_tbl VALUES + (1,'one'), + (2,'two'), + (3,'three'), + (4,'four'), + (5,'five'), + (6,'six'); + +--- Users can access only their own rows +CREATE POLICY rls_tbl_policy ON rls_tbl FOR SELECT TO PUBLIC USING(owner = current_user); +ALTER TABLE rls_tbl ENABLE ROW LEVEL SECURITY; +GRANT ALL on rls_tbl TO PUBLIC; +GRANT ALL on num_tbl TO PUBLIC; + +--- create a view owned by ivm_user +SET SESSION AUTHORIZATION ivm_user; + +CREATE INCREMENTAL MATERIALIZED VIEW ivm_rls AS SELECT * FROM rls_tbl; +SELECT id, data, owner FROM ivm_rls ORDER BY 1,2,3; +RESET SESSION AUTHORIZATION; + +--- inserts rows owned by different users +INSERT INTO rls_tbl VALUES + (3,'baz','ivm_user'), + (4,'qux','postgres'); +SELECT id, data, owner FROM ivm_rls ORDER BY 1,2,3; + +--- combination of diffent kinds of commands +WITH + i AS (INSERT INTO rls_tbl VALUES(5,'quux','postgres'), (6,'corge','ivm_user')), + u AS (UPDATE rls_tbl SET owner = 'postgres' WHERE id = 1), + u2 AS (UPDATE rls_tbl SET owner = 'ivm_user' WHERE id = 2) +SELECT; +SELECT id, data, owner FROM ivm_rls ORDER BY 1,2,3; + +--- +SET SESSION AUTHORIZATION ivm_user; +CREATE INCREMENTAL MATERIALIZED VIEW ivm_rls2 AS SELECT * FROM rls_tbl JOIN num_tbl USING(id); +RESET SESSION AUTHORIZATION; + +WITH + x AS (UPDATE rls_tbl SET data = data || '_2' where id in (3,4)), + y AS (UPDATE num_tbl SET num = num || '_2' where id in (3,4)) +SELECT; +SELECT * FROM ivm_rls2 ORDER BY 1,2,3; + +-- automatic index creation +CREATE TABLE base_a (i int primary key, j int); +CREATE TABLE base_b (i int primary key, j int); + +--- group by: create an index +CREATE INCREMENTAL MATERIALIZED VIEW mv_idx1 AS SELECT i, sum(j) FROM base_a GROUP BY i; + +--- distinct: create an index +CREATE INCREMENTAL MATERIALIZED VIEW mv_idx2 AS SELECT DISTINCT j FROM base_a; + +--- with all pkey columns: create an index +CREATE INCREMENTAL MATERIALIZED VIEW mv_idx3(i_a, i_b) AS SELECT a.i, b.i FROM base_a a, base_b b; + +--- missing some pkey columns: no index +CREATE INCREMENTAL MATERIALIZED VIEW mv_idx4 AS SELECT j FROM base_a; +CREATE INCREMENTAL MATERIALIZED VIEW mv_idx5 AS SELECT a.i, b.j FROM base_a a, base_b b; + +-- cleanup + +DROP TABLE rls_tbl CASCADE; +DROP TABLE num_tbl CASCADE; +DROP USER ivm_user; +DROP USER ivm_admin; + +DROP TABLE mv_base_b CASCADE; +DROP TABLE mv_base_a CASCADE; -- 2.25.1 --Multipart=_Mon__28_Aug_2023_16_05_30_+0900_b1OvQD_3A3ZMTGvj Content-Type: text/x-diff; name="v29-0011-Add-documentations-about-Incremental-View-Mainte.patch" Content-Disposition: attachment; filename="v29-0011-Add-documentations-about-Incremental-View-Mainte.patch" Content-Transfer-Encoding: 7bit ^ permalink raw reply [nested|flat] 10+ messages in thread
* [PATCH 5/9] document store_change somewhat more @ 2026-03-12 15:10 Álvaro Herrera <[email protected]> 0 siblings, 0 replies; 10+ messages in thread From: Álvaro Herrera @ 2026-03-12 15:10 UTC (permalink / raw) --- .../replication/pgoutput_repack/pgoutput_repack.c | 9 ++++++++- 1 file changed, 8 insertions(+), 1 deletion(-) diff --git a/src/backend/replication/pgoutput_repack/pgoutput_repack.c b/src/backend/replication/pgoutput_repack/pgoutput_repack.c index 90f3a8975b9..79fc611b9ff 100644 --- a/src/backend/replication/pgoutput_repack/pgoutput_repack.c +++ b/src/backend/replication/pgoutput_repack/pgoutput_repack.c @@ -158,7 +158,14 @@ plugin_change(LogicalDecodingContext *ctx, ReorderBufferTXN *txn, } } -/* Store concurrent data change. */ +/* + * For each change affecting the table being repacked, we store enough + * information about each tuple in it, so that it can be replayed in the + * new copy of the table. + * + * XXX for DELETE and the UPDATE OLD tuples, we could store just the + * replication identity instead of the full tuple. + */ static void store_change(LogicalDecodingContext *ctx, Relation relation, ConcurrentChangeKind kind, HeapTuple tuple) -- 2.47.3 --pnppmxqkefjd4hu2 Content-Type: text/plain; charset=utf-8 Content-Disposition: attachment; filename*0="0006-use-int-instead-of-uint32-for-the-result-of-list_len.noc"; filename*1="fbot.txt" ^ permalink raw reply [nested|flat] 10+ messages in thread
* [PATCH 5/9] document store_change somewhat more @ 2026-03-12 15:10 Álvaro Herrera <[email protected]> 0 siblings, 0 replies; 10+ messages in thread From: Álvaro Herrera @ 2026-03-12 15:10 UTC (permalink / raw) --- .../replication/pgoutput_repack/pgoutput_repack.c | 9 ++++++++- 1 file changed, 8 insertions(+), 1 deletion(-) diff --git a/src/backend/replication/pgoutput_repack/pgoutput_repack.c b/src/backend/replication/pgoutput_repack/pgoutput_repack.c index 90f3a8975b9..79fc611b9ff 100644 --- a/src/backend/replication/pgoutput_repack/pgoutput_repack.c +++ b/src/backend/replication/pgoutput_repack/pgoutput_repack.c @@ -158,7 +158,14 @@ plugin_change(LogicalDecodingContext *ctx, ReorderBufferTXN *txn, } } -/* Store concurrent data change. */ +/* + * For each change affecting the table being repacked, we store enough + * information about each tuple in it, so that it can be replayed in the + * new copy of the table. + * + * XXX for DELETE and the UPDATE OLD tuples, we could store just the + * replication identity instead of the full tuple. + */ static void store_change(LogicalDecodingContext *ctx, Relation relation, ConcurrentChangeKind kind, HeapTuple tuple) -- 2.47.3 --pnppmxqkefjd4hu2 Content-Type: text/plain; charset=utf-8 Content-Disposition: attachment; filename*0="0006-use-int-instead-of-uint32-for-the-result-of-list_len.noc"; filename*1="fbot.txt" ^ permalink raw reply [nested|flat] 10+ messages in thread
* [PATCH v1 1/2] Remove fallback declaration for tas(). @ 2026-05-04 21:04 Nathan Bossart <[email protected]> 0 siblings, 0 replies; 10+ messages in thread From: Nathan Bossart @ 2026-05-04 21:04 UTC (permalink / raw) The last definition of tas() in s_lock.c was removed in commit 718aa43a4e, and the last tas.s file was removed in commit 25f36066dd, so this is dead code. --- src/include/storage/s_lock.h | 7 ------- 1 file changed, 7 deletions(-) diff --git a/src/include/storage/s_lock.h b/src/include/storage/s_lock.h index c9e52511990..dcfec8ce2af 100644 --- a/src/include/storage/s_lock.h +++ b/src/include/storage/s_lock.h @@ -697,13 +697,6 @@ extern void s_unlock(volatile slock_t *lock); #define SPIN_DELAY() ((void) 0) #endif /* SPIN_DELAY */ -#if !defined(TAS) -extern int tas(volatile slock_t *lock); /* in port/.../tas.s, or - * s_lock.c */ - -#define TAS(lock) tas(lock) -#endif /* TAS */ - #if !defined(TAS_SPIN) #define TAS_SPIN(lock) TAS(lock) #endif /* TAS_SPIN */ -- 2.50.1 (Apple Git-155) --T3UIK4uPypqlnd5v Content-Type: text/plain; charset=us-ascii Content-Disposition: attachment; filename=v1-0002-Remove-HAS_TEST_AND_SET.patch ^ permalink raw reply [nested|flat] 10+ messages in thread
* [PATCH v3 1/3] Remove fallback declaration for tas(). @ 2026-05-04 21:04 Nathan Bossart <[email protected]> 0 siblings, 0 replies; 10+ messages in thread From: Nathan Bossart @ 2026-05-04 21:04 UTC (permalink / raw) The last definition of tas() in s_lock.c was removed in commit 718aa43a4e, and the last tas.s file was removed in commit 25f36066dd, so this is dead code. --- src/include/storage/s_lock.h | 7 ------- 1 file changed, 7 deletions(-) diff --git a/src/include/storage/s_lock.h b/src/include/storage/s_lock.h index c9e52511990..dcfec8ce2af 100644 --- a/src/include/storage/s_lock.h +++ b/src/include/storage/s_lock.h @@ -697,13 +697,6 @@ extern void s_unlock(volatile slock_t *lock); #define SPIN_DELAY() ((void) 0) #endif /* SPIN_DELAY */ -#if !defined(TAS) -extern int tas(volatile slock_t *lock); /* in port/.../tas.s, or - * s_lock.c */ - -#define TAS(lock) tas(lock) -#endif /* TAS */ - #if !defined(TAS_SPIN) #define TAS_SPIN(lock) TAS(lock) #endif /* TAS_SPIN */ -- 2.50.1 (Apple Git-155) --IHFkEDr8CkxIje3R Content-Type: text/plain; charset=us-ascii Content-Disposition: attachment; filename=v3-0002-Remove-HAS_TEST_AND_SET.patch ^ permalink raw reply [nested|flat] 10+ messages in thread
* [PATCH v4 1/3] Remove fallback declaration for tas(). @ 2026-05-04 21:04 Nathan Bossart <[email protected]> 0 siblings, 0 replies; 10+ messages in thread From: Nathan Bossart @ 2026-05-04 21:04 UTC (permalink / raw) The last definition of tas() in s_lock.c was removed in commit 718aa43a4e, and the last tas.s file was removed in commit 25f36066dd, so this is dead code. --- src/backend/Makefile | 2 +- src/backend/port/.gitignore | 1 - src/backend/port/meson.build | 2 +- src/include/storage/s_lock.h | 7 ------- 4 files changed, 2 insertions(+), 10 deletions(-) diff --git a/src/backend/Makefile b/src/backend/Makefile index 162d3f1f2a9..4bb76d3d397 100644 --- a/src/backend/Makefile +++ b/src/backend/Makefile @@ -301,7 +301,7 @@ endif distclean: clean # generated by configure - rm -f port/tas.s port/pg_sema.c port/pg_shmem.c + rm -f port/pg_sema.c port/pg_shmem.c ########################################################################## diff --git a/src/backend/port/.gitignore b/src/backend/port/.gitignore index 4ef36b82c77..6c5067a4a9f 100644 --- a/src/backend/port/.gitignore +++ b/src/backend/port/.gitignore @@ -1,3 +1,2 @@ /pg_sema.c /pg_shmem.c -/tas.s diff --git a/src/backend/port/meson.build b/src/backend/port/meson.build index e8b7da8d281..29e88ef3541 100644 --- a/src/backend/port/meson.build +++ b/src/backend/port/meson.build @@ -30,4 +30,4 @@ if host_system == 'windows' endif # autoconf generates the file there, ensure we get a conflict -generated_sources_ac += {'src/backend/port': ['pg_sema.c', 'pg_shmem.c', 'tas.s']} +generated_sources_ac += {'src/backend/port': ['pg_sema.c', 'pg_shmem.c']} diff --git a/src/include/storage/s_lock.h b/src/include/storage/s_lock.h index c9e52511990..dcfec8ce2af 100644 --- a/src/include/storage/s_lock.h +++ b/src/include/storage/s_lock.h @@ -697,13 +697,6 @@ extern void s_unlock(volatile slock_t *lock); #define SPIN_DELAY() ((void) 0) #endif /* SPIN_DELAY */ -#if !defined(TAS) -extern int tas(volatile slock_t *lock); /* in port/.../tas.s, or - * s_lock.c */ - -#define TAS(lock) tas(lock) -#endif /* TAS */ - #if !defined(TAS_SPIN) #define TAS_SPIN(lock) TAS(lock) #endif /* TAS_SPIN */ -- 2.50.1 (Apple Git-155) --JmXY/Yn6swRVDyih Content-Type: text/plain; charset=us-ascii Content-Disposition: attachment; filename=v4-0002-Remove-HAS_TEST_AND_SET.patch ^ permalink raw reply [nested|flat] 10+ messages in thread
* [PATCH v5 1/3] Remove fallback declaration for tas(). @ 2026-05-04 21:04 Nathan Bossart <[email protected]> 0 siblings, 0 replies; 10+ messages in thread From: Nathan Bossart @ 2026-05-04 21:04 UTC (permalink / raw) The last definition of tas() in s_lock.c was removed in commit 718aa43a4e, and the last tas.s file was removed in commit 25f36066dd, so this is dead code. --- src/backend/Makefile | 2 +- src/backend/port/.gitignore | 1 - src/backend/port/meson.build | 2 +- src/include/storage/s_lock.h | 7 ------- 4 files changed, 2 insertions(+), 10 deletions(-) diff --git a/src/backend/Makefile b/src/backend/Makefile index 162d3f1f2a9..4bb76d3d397 100644 --- a/src/backend/Makefile +++ b/src/backend/Makefile @@ -301,7 +301,7 @@ endif distclean: clean # generated by configure - rm -f port/tas.s port/pg_sema.c port/pg_shmem.c + rm -f port/pg_sema.c port/pg_shmem.c ########################################################################## diff --git a/src/backend/port/.gitignore b/src/backend/port/.gitignore index 4ef36b82c77..6c5067a4a9f 100644 --- a/src/backend/port/.gitignore +++ b/src/backend/port/.gitignore @@ -1,3 +1,2 @@ /pg_sema.c /pg_shmem.c -/tas.s diff --git a/src/backend/port/meson.build b/src/backend/port/meson.build index e8b7da8d281..29e88ef3541 100644 --- a/src/backend/port/meson.build +++ b/src/backend/port/meson.build @@ -30,4 +30,4 @@ if host_system == 'windows' endif # autoconf generates the file there, ensure we get a conflict -generated_sources_ac += {'src/backend/port': ['pg_sema.c', 'pg_shmem.c', 'tas.s']} +generated_sources_ac += {'src/backend/port': ['pg_sema.c', 'pg_shmem.c']} diff --git a/src/include/storage/s_lock.h b/src/include/storage/s_lock.h index c9e52511990..dcfec8ce2af 100644 --- a/src/include/storage/s_lock.h +++ b/src/include/storage/s_lock.h @@ -697,13 +697,6 @@ extern void s_unlock(volatile slock_t *lock); #define SPIN_DELAY() ((void) 0) #endif /* SPIN_DELAY */ -#if !defined(TAS) -extern int tas(volatile slock_t *lock); /* in port/.../tas.s, or - * s_lock.c */ - -#define TAS(lock) tas(lock) -#endif /* TAS */ - #if !defined(TAS_SPIN) #define TAS_SPIN(lock) TAS(lock) #endif /* TAS_SPIN */ -- 2.50.1 (Apple Git-155) --EzTP03ol5OjsRjZr Content-Type: text/plain; charset=us-ascii Content-Disposition: attachment; filename=v5-0002-Remove-HAS_TEST_AND_SET.patch ^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2026-05-04 21:04 UTC | newest] Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2019-02-14 13:37 [PATCH] Add a function to reset the cumulative statistics Pierre <[email protected]> 2019-08-03 02:47 [PATCH] Improve documentation about our XML functionality. nobody <[email protected]> 2019-08-03 02:47 [PATCH] Improve documentation about our XML functionality. nobody <[email protected]> 2021-03-10 02:11 [PATCH v29 10/11] Add regression tests for Incremental View Maintenance Takuma Hoshiai <[email protected]> 2026-03-12 15:10 [PATCH 5/9] document store_change somewhat more Álvaro Herrera <[email protected]> 2026-03-12 15:10 [PATCH 5/9] document store_change somewhat more Álvaro Herrera <[email protected]> 2026-05-04 21:04 [PATCH v4 1/3] Remove fallback declaration for tas(). Nathan Bossart <[email protected]> 2026-05-04 21:04 [PATCH v5 1/3] Remove fallback declaration for tas(). Nathan Bossart <[email protected]> 2026-05-04 21:04 [PATCH v1 1/2] Remove fallback declaration for tas(). Nathan Bossart <[email protected]> 2026-05-04 21:04 [PATCH v3 1/3] Remove fallback declaration for tas(). 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