8000 Add support for REFRESH MATERIALIZED VIEW CONCURRENTLY. · postgrespro/postgres@cc1965a · GitHub
[go: up one dir, main page]
Skip to content

    • Commit cc1965a

    Browse files
    kgrittnkgrittn
    committed
    Add support for REFRESH MATERIALIZED VIEW CONCURRENTLY.
    This allows reads to continue without any blocking while a REFRESH runs. The new data appears atomically as part of transaction commit. Review questioned the Assert that a matview was not a system relation. This will be addressed separately. Reviewed by Hitoshi Harada, Robert Haas, Andres Freund. Merged after review with security patch f3ab5d4.
    1 parent 7f7485a commit cc1965a

    File tree

    16 files changed

    +646
    -59
    lines changed

    16 files changed

    +646
    -59
    lines changed

    ‎doc/src/sgml/mvcc.sgml

    Lines changed: 1 addition & 2 deletions
    Original file line numberDiff line numberDiff line change
    @@ -928,8 +928,7 @@ ERROR: could not serialize access due to read/write dependencies among transact
    928928
    </para>
    929929

    930930
    <para>
    931-
    This lock mode is not automatically acquired on tables by any
    932-
    <productname>PostgreSQL</productname> command.
    931+
    Acquired by <command>REFRESH MATERIALIZED VIEW CONCURRENTLY</command>.
    933932
    </para>
    934933
    </listitem>
    935934
    </varlistentry>

    ‎doc/src/sgml/ref/refresh_materialized_view.sgml

    Lines changed: 33 additions & 1 deletion
    Original file line numberDiff line numberDiff line change
    @@ -21,7 +21,7 @@ PostgreSQL documentation
    2121

    2222
    <refsynopsisdiv>
    2323
    <synopsis>
    24-
    REFRESH MATERIALIZED VIEW <replaceable class="PARAMETER">name</replaceable>
    24+
    REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] <replaceable class="PARAMETER">name</replaceable>
    2525
    [ WITH [ NO ] DATA ]
    2626
    </synopsis>
    2727
    </refsynopsisdiv>
    @@ -38,12 +38,44 @@ REFRESH MATERIALIZED VIEW <replaceable class="PARAMETER">name</replaceable>
    3838
    data is generated and the materialized view is left in an unscannable
    3939
    state.
    4040
    </para>
    41+
    <para>
    42+
    <literal>CONCURRENTLY</literal> and <literal>WITH NO DATA</literal> may not
    43+
    be specified together.
    44+
    </para>
    4145
    </refsect1>
    4246

    4347
    <refsect1>
    4448
    <title>Parameters</title>
    4549

    4650
    <variablelist>
    51+
    <varlistentry>
    52+
    <term><literal>CONCURRENTLY</literal></term>
    53+
    <listitem>
    54+
    <para>
    55+
    Refresh the materialized view without locking out concurrent selects on
    56+
    the materialized view. Without this option a refresh which affects a
    57+
    lot of rows will tend to use fewer resources and complete more quickly,
    58+
    but could block other connections which are trying to read from the
    59+
    materialized view. This option may be faster in cases where a small
    60+
    number of rows are affected.
    61+
    </para>
    62+
    <para>
    63+
    This option is only allowed if there is at least one
    64+
    <literal>UNIQUE</literal> index on the materialized view which uses only
    65+
    column names and includes all rows; that is, it must not index on any
    66+
    expressions nor include a <literal>WHERE</literal> clause.
    67+
    </para>
    68+
    <para>
    69+
    This option may not be used when the materialized view is not already
    70+
    populated.
    71+
    </para>
    72+
    <para>
    73+
    Even with this option only one <literal>REFRESH</literal> at a time may
    74+
    run against any one materialized view.
    75+
    </para>
    76+
    </listitem>
    77+
    </varlistentry>
    78+
    4779
    <varlistentry>
    4880
    <term><replaceable class="PARAMETER">name</replaceable></term>
    4981
    <listitem>

    ‎src/backend/commands/cluster.c

    Lines changed: 21 additions & 6 deletions
    Original file line numberDiff line numberDiff line change
    @@ -589,7 +589,8 @@ rebuild_relation(Relation OldHeap, Oid indexOid,
    589589
    heap_close(OldHeap, NoLock);
    590590

    591591
    /* Create the transient table that will receive the re-ordered data */
    592-
    OIDNewHeap = make_new_heap(tableOid, tableSpace);
    592+
    OIDNewHeap = make_new_heap(tableOid, tableSpace, false,
    593+
    AccessExclusiveLock);
    593594

    594595
    /* Copy the heap data into the new table in the desired order */
    595596
    copy_heap_data(OIDNewHeap, tableOid, indexOid,
    @@ -616,7 +617,8 @@ rebuild_relation(Relation OldHeap, Oid indexOid,
    616617
    * data, then call finish_heap_swap to complete the operation.
    617618
    */
    618619
    Oid
    619-
    make_new_heap(Oid OIDOldHeap, Oid NewTableSpace)
    620+
    make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, bool forcetemp,
    621+
    LOCKMODE lockmode)
    620622
    {
    621623
    TupleDesc OldHeapDesc;
    622624
    char NewHeapName[NAMEDATALEN];
    @@ -626,8 +628,10 @@ make_new_heap(Oid OIDOldHeap, Oid NewTableSpace)
    626628
    HeapTuple tuple;
    627629
    Datum reloptions;
    628630
    bool isNull;
    631+
    Oid namespaceid;
    632+
    char relpersistence;
    629633

    630-
    OldHeap = heap_open(OIDOldHeap, AccessExclusiveLock);
    634+
    OldHeap = heap_open(OIDOldHeap, lockmode);
    631635
    OldHeapDesc = RelationGetDescr(OldHeap);
    632636

    633637
    /*
    @@ -648,6 +652,17 @@ make_new_heap(Oid OIDOldHeap, Oid NewTableSpace)
    648652
    if (isNull)
    649653
    reloptions = (Datum) 0;
    650654

    655+
    if (forcetemp)
    656+
    {
    657+
    namespaceid = LookupCreationNamespace("pg_temp");
    658+
    relpersistence = RELPERSISTENCE_TEMP;
    659+
    }
    660+
    else
    661+
    {
    662+
    namespaceid = RelationGetNamespace(OldHeap);
    663+
    relpersistence = OldHeap->rd_rel->relpersistence;
    664+
    }
    665+
    651666
    /*
    652667
    * Create the new heap, using a temporary name in the same namespace as
    653668
    * the existing table. NOTE: there is some risk of collision with user
    @@ -663,16 +678,16 @@ make_new_heap(Oid OIDOldHeap, Oid NewTableSpace)
    663678
    snprintf(NewHeapName, sizeof(NewHeapName), "pg_temp_%u", OIDOldHeap);
    664679

    665680
    OIDNewHeap = heap_create_with_catalog(NewHeapName,
    666-
    RelationGetNamespace(OldHeap),
    681+
    namespaceid,
    667682
    NewTableSpace,
    668683
    InvalidOid,
    669684
    InvalidOid,
    670685
    InvalidOid,
    671686
    OldHeap->rd_rel->relowner,
    672687
    OldHeapDesc,
    673688
    NIL,
    674-
    OldHeap->rd_rel->relkind,
    675-
    OldHeap->rd_rel->relpersistence,
    689+
    RELKIND_RELATION,
    690+
    relpersistence,
    676691
    false,
    677692
    RelationIsMapped(OldHeap),
    678693
    true,

    0 commit comments

    Comments
     (0)
    0