Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitf566515

Browse files
committed
Add record_image_ops opclass for matview concurrent refresh.
REFRESH MATERIALIZED VIEW CONCURRENTLY was broken for any matviewcontaining a column of a type without a default btree operatorclass. It also did not produce results consistent with a non-concurrent REFRESH or a normal view if any column was of a typewhich allowed user-visible differences between values whichcompared as equal according to the type's default btree opclass.Concurrent matview refresh was modified to use the new operatorsto solve these problems.Documentation was added for record comparison, both for thedefault btree operator class for record, and the newly addedoperators. Regression tests now check for proper behavior bothfor a matview with a box column and a matview containing a citextcolumn.Reviewed by Steve Singer, who suggested some of the doc language.
1 parent1cccce5 commitf566515

File tree

17 files changed

+775
-37
lines changed

17 files changed

+775
-37
lines changed

‎contrib/citext/expected/citext.out

Lines changed: 41 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2276,3 +2276,44 @@ SELECT like_escape( name::text, ''::citext ) = like_escape( name::text, '' ) AS
22762276
t
22772277
(5 rows)
22782278

2279+
-- Ensure correct behavior for citext with materialized views.
2280+
CREATE TABLE citext_table (
2281+
id serial primary key,
2282+
name citext
2283+
);
2284+
INSERT INTO citext_table (name)
2285+
VALUES ('one'), ('two'), ('three'), (NULL), (NULL);
2286+
CREATE MATERIALIZED VIEW citext_matview AS
2287+
SELECT * FROM citext_table;
2288+
CREATE UNIQUE INDEX citext_matview_id
2289+
ON citext_matview (id);
2290+
SELECT *
2291+
FROM citext_matview m
2292+
FULL JOIN citext_table t ON (t.id = m.id AND t *= m)
2293+
WHERE t.id IS NULL OR m.id IS NULL;
2294+
id | name | id | name
2295+
----+------+----+------
2296+
(0 rows)
2297+
2298+
UPDATE citext_table SET name = 'Two' WHERE name = 'TWO';
2299+
SELECT *
2300+
FROM citext_matview m
2301+
FULL JOIN citext_table t ON (t.id = m.id AND t *= m)
2302+
WHERE t.id IS NULL OR m.id IS NULL;
2303+
id | name | id | name
2304+
----+------+----+------
2305+
| | 2 | Two
2306+
2 | two | |
2307+
(2 rows)
2308+
2309+
REFRESH MATERIALIZED VIEW CONCURRENTLY citext_matview;
2310+
SELECT * FROM citext_matview ORDER BY id;
2311+
id | name
2312+
----+-------
2313+
1 | one
2314+
2 | Two
2315+
3 | three
2316+
4 |
2317+
5 |
2318+
(5 rows)
2319+

‎contrib/citext/expected/citext_1.out

Lines changed: 41 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2276,3 +2276,44 @@ SELECT like_escape( name::text, ''::citext ) = like_escape( name::text, '' ) AS
22762276
t
22772277
(5 rows)
22782278

2279+
-- Ensure correct behavior for citext with materialized views.
2280+
CREATE TABLE citext_table (
2281+
id serial primary key,
2282+
name citext
2283+
);
2284+
INSERT INTO citext_table (name)
2285+
VALUES ('one'), ('two'), ('three'), (NULL), (NULL);
2286+
CREATE MATERIALIZED VIEW citext_matview AS
2287+
SELECT * FROM citext_table;
2288+
CREATE UNIQUE INDEX citext_matview_id
2289+
ON citext_matview (id);
2290+
SELECT *
2291+
FROM citext_matview m
2292+
FULL JOIN citext_table t ON (t.id = m.id AND t *= m)
2293+
WHERE t.id IS NULL OR m.id IS NULL;
2294+
id | name | id | name
2295+
----+------+----+------
2296+
(0 rows)
2297+
2298+
UPDATE citext_table SET name = 'Two' WHERE name = 'TWO';
2299+
SELECT *
2300+
FROM citext_matview m
2301+
FULL JOIN citext_table t ON (t.id = m.id AND t *= m)
2302+
WHERE t.id IS NULL OR m.id IS NULL;
2303+
id | name | id | name
2304+
----+------+----+------
2305+
| | 2 | Two
2306+
2 | two | |
2307+
(2 rows)
2308+
2309+
REFRESH MATERIALIZED VIEW CONCURRENTLY citext_matview;
2310+
SELECT * FROM citext_matview ORDER BY id;
2311+
id | name
2312+
----+-------
2313+
1 | one
2314+
2 | Two
2315+
3 | three
2316+
4 |
2317+
5 |
2318+
(5 rows)
2319+

‎contrib/citext/sql/citext.sql

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -711,3 +711,26 @@ SELECT COUNT(*) = 19::bigint AS t FROM try;
711711

712712
SELECT like_escape( name,'' )= like_escape( name::text,'' )AS tFROM srt;
713713
SELECT like_escape( name::text,''::citext )= like_escape( name::text,'' )AS tFROM srt;
714+
715+
-- Ensure correct behavior for citext with materialized views.
716+
CREATETABLEcitext_table (
717+
idserialprimary key,
718+
name citext
719+
);
720+
INSERT INTO citext_table (name)
721+
VALUES ('one'), ('two'), ('three'), (NULL), (NULL);
722+
CREATE MATERIALIZED VIEW citext_matviewAS
723+
SELECT*FROM citext_table;
724+
CREATEUNIQUE INDEXcitext_matview_id
725+
ON citext_matview (id);
726+
SELECT*
727+
FROM citext_matview m
728+
FULLJOIN citext_table tON (t.id=m.idAND t*= m)
729+
WHEREt.id ISNULLORm.id ISNULL;
730+
UPDATE citext_tableSET name='Two'WHERE name='TWO';
731+
SELECT*
732+
FROM citext_matview m
733+
FULLJOIN citext_table tON (t.id=m.idAND t*= m)
734+
WHEREt.id ISNULLORm.id ISNULL;
735+
REFRESH MATERIALIZED VIEW CONCURRENTLY citext_matview;
736+
SELECT*FROM citext_matviewORDER BY id;

‎doc/src/sgml/func.sgml

Lines changed: 85 additions & 26 deletions
Original file line numberDiff line numberDiff line change
@@ -12739,7 +12739,7 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
1273912739

1274012740
<para>
1274112741
See <xref linkend="row-wise-comparison"> for details about the meaning
12742-
of a row-wise comparison.
12742+
of a row constructor comparison.
1274312743
</para>
1274412744
</sect2>
1274512745

@@ -12795,12 +12795,12 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
1279512795

1279612796
<para>
1279712797
See <xref linkend="row-wise-comparison"> for details about the meaning
12798-
of a row-wise comparison.
12798+
of a row constructor comparison.
1279912799
</para>
1280012800
</sect2>
1280112801

1280212802
<sect2>
12803-
<title>Row-wise Comparison</title>
12803+
<title>Single-row Comparison</title>
1280412804

1280512805
<indexterm zone="functions-subquery">
1280612806
<primary>comparison</primary>
@@ -12823,7 +12823,7 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
1282312823

1282412824
<para>
1282512825
See <xref linkend="row-wise-comparison"> for details about the meaning
12826-
of a row-wise comparison.
12826+
of a row constructor comparison.
1282712827
</para>
1282812828
</sect2>
1282912829
</sect1>
@@ -12852,13 +12852,23 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
1285212852
<primary>SOME</primary>
1285312853
</indexterm>
1285412854

12855+
<indexterm>
12856+
<primary>composite type</primary>
12857+
<secondary>comparison</secondary>
12858+
</indexterm>
12859+
1285512860
<indexterm>
1285612861
<primary>row-wise comparison</primary>
1285712862
</indexterm>
1285812863

1285912864
<indexterm>
1286012865
<primary>comparison</primary>
12861-
<secondary>row-wise</secondary>
12866+
<secondary>composite type</secondary>
12867+
</indexterm>
12868+
12869+
<indexterm>
12870+
<primary>comparison</primary>
12871+
<secondary>row constructor</secondary>
1286212872
</indexterm>
1286312873

1286412874
<indexterm>
@@ -13023,7 +13033,7 @@ AND
1302313033
</sect2>
1302413034

1302513035
<sect2 id="row-wise-comparison">
13026-
<title>Row-wise Comparison</title>
13036+
<title>Row Constructor Comparison</title>
1302713037

1302813038
<synopsis>
1302913039
<replaceable>row_constructor</replaceable> <replaceable>operator</replaceable> <replaceable>row_constructor</replaceable>
@@ -13033,20 +13043,25 @@ AND
1303313043
Each side is a row constructor,
1303413044
as described in <xref linkend="sql-syntax-row-constructors">.
1303513045
The two row values must have the same number of fields.
13036-
Each side is evaluated and they are compared row-wise. Rowcomparisons
13037-
are allowed when the <replaceable>operator</replaceable> is
13046+
Each side is evaluated and they are compared row-wise. Rowconstructor
13047+
comparisonsare allowed when the <replaceable>operator</replaceable> is
1303813048
<literal>=</>,
1303913049
<literal>&lt;&gt;</>,
1304013050
<literal>&lt;</>,
1304113051
<literal>&lt;=</>,
1304213052
<literal>&gt;</> or
13043-
<literal>&gt;=</>,
13044-
or has semantics similar to one of these. (To be specific, an operator
13045-
can be a row comparison operator if it is a member of a B-tree operator
13046-
class, or is the negator of the <literal>=</> member of a B-tree operator
13047-
class.)
13053+
<literal>&gt;=</>.
13054+
Every row element must be of a type which has a default B-tree operator
13055+
class or the attempted comparison may generate an error.
1304813056
</para>
1304913057

13058+
<note>
13059+
<para>
13060+
Errors related to the number or types of elements might not occur if
13061+
the comparison is resolved using earlier columns.
13062+
</para>
13063+
</note>
13064+
1305013065
<para>
1305113066
The <literal>=</> and <literal>&lt;&gt;</> cases work slightly differently
1305213067
from the others. Two rows are considered
@@ -13104,20 +13119,64 @@ AND
1310413119
be either true or false, never null.
1310513120
</para>
1310613121

13107-
<note>
13108-
<para>
13109-
The SQL specification requires row-wise comparison to return NULL if the
13110-
result depends on comparing two NULL values or a NULL and a non-NULL.
13111-
<productname>PostgreSQL</productname> does this only when comparing the
13112-
results of two row constructors or comparing a row constructor to the
13113-
output of a subquery (as in <xref linkend="functions-subquery">).
13114-
In other contexts where two composite-type values are compared, two
13115-
NULL field values are considered equal, and a NULL is considered larger
13116-
than a non-NULL. This is necessary in order to have consistent sorting
13117-
and indexing behavior for composite types.
13118-
</para>
13119-
</note>
13122+
</sect2>
1312013123

13124+
<sect2 id="composite-type-comparison">
13125+
<title>Composite Type Comparison</title>
13126+
13127+
<synopsis>
13128+
<replaceable>record</replaceable> <replaceable>operator</replaceable> <replaceable>record</replaceable>
13129+
</synopsis>
13130+
13131+
<para>
13132+
The SQL specification requires row-wise comparison to return NULL if the
13133+
result depends on comparing two NULL values or a NULL and a non-NULL.
13134+
<productname>PostgreSQL</productname> does this only when comparing the
13135+
results of two row constructors (as in
13136+
<xref linkend="row-wise-comparison">) or comparing a row constructor
13137+
to the output of a subquery (as in <xref linkend="functions-subquery">).
13138+
In other contexts where two composite-type values are compared, two
13139+
NULL field values are considered equal, and a NULL is considered larger
13140+
than a non-NULL. This is necessary in order to have consistent sorting
13141+
and indexing behavior for composite types.
13142+
</para>
13143+
13144+
<para>
13145+
Each side is evaluated and they are compared row-wise. Composite type
13146+
comparisons are allowed when the <replaceable>operator</replaceable> is
13147+
<literal>=</>,
13148+
<literal>&lt;&gt;</>,
13149+
<literal>&lt;</>,
13150+
<literal>&lt;=</>,
13151+
<literal>&gt;</> or
13152+
<literal>&gt;=</>,
13153+
or has semantics similar to one of these. (To be specific, an operator
13154+
can be a row comparison operator if it is a member of a B-tree operator
13155+
class, or is the negator of the <literal>=</> member of a B-tree operator
13156+
class.) The default behavior of the above operators is the same as for
13157+
<literal>IS [ NOT ] DISTINCT FROM</literal> for row constructors (see
13158+
<xref linkend="row-wise-comparison">).
13159+
</para>
13160+
13161+
<para>
13162+
To support matching of rows which include elements without a default
13163+
B-tree operator class, the following operators are defined for composite
13164+
type comparison:
13165+
<literal>*=</>,
13166+
<literal>*&lt;&gt;</>,
13167+
<literal>*&lt;</>,
13168+
<literal>*&lt;=</>,
13169+
<literal>*&gt;</>, and
13170+
<literal>*&gt;=</>.
13171+
These operators compare the internal binary representation of the two
13172+
rows. Two rows might have a different binary representation even
13173+
though comparisons of the two rows with the equality operator is true.
13174+
The ordering of rows under these comparision operators is deterministic
13175+
but not otherwise meaningful. These operators are used internally for
13176+
materialized views and might be useful for other specialized purposes
13177+
such as replication but are not intended to be generally useful for
13178+
writing queries.
13179+
</para>
1312113180
</sect2>
1312213181
</sect1>
1312313182

‎src/backend/commands/matview.c

Lines changed: 4 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -562,7 +562,7 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid)
562562
"SELECT newdata FROM %s newdata "
563563
"WHERE newdata IS NOT NULL AND EXISTS "
564564
"(SELECT * FROM %s newdata2 WHERE newdata2 IS NOT NULL "
565-
"AND newdata2 OPERATOR(pg_catalog.=) newdata "
565+
"AND newdata2 OPERATOR(pg_catalog.*=) newdata "
566566
"AND newdata2.ctid OPERATOR(pg_catalog.<>) "
567567
"newdata.ctid) LIMIT 1",
568568
tempname,tempname);
@@ -645,9 +645,6 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid)
645645
/*
646646
* Only include the column once regardless of how many times
647647
* it shows up in how many indexes.
648-
*
649-
* This is also useful later to omit columns which can not
650-
* have changed from the SET clause of the UPDATE statement.
651648
*/
652649
if (usedForQual[attnum-1])
653650
continue;
@@ -682,8 +679,9 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid)
682679
errhint("Create a UNIQUE index with no WHERE clause on one or more columns of the materialized view.")));
683680

684681
appendStringInfoString(&querybuf,
685-
" AND newdata = mv) WHERE newdata IS NULL OR mv IS NULL"
686-
" ORDER BY tid");
682+
" AND newdata OPERATOR(pg_catalog.*=) mv) "
683+
"WHERE newdata IS NULL OR mv IS NULL "
684+
"ORDER BY tid");
687685

688686
/* Create the temporary "diff" table. */
689687
if (SPI_exec(querybuf.data,0)!=SPI_OK_UTILITY)

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp