From: Jim Nasby <Jim.Nasby@BlueTreble.com>
To: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
To: Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: Change in "policy" on dump ordering?
Date: Wed, 22 Feb 2017 17:24:49 -0600
Message-ID: <65049c27-96bf-317b-b435-931f6041f94c@BlueTreble.com> (raw)
In-Reply-To: <75409fb1-bd96-25c2-f6b0-c24e28ed2684@2ndquadrant.com>
References: <87866740-ef55-8a6d-6480-499b92aa23c0@BlueTreble.com>
<8f64786e-824d-7c0d-33a6-3d9e1c8e9fde@2ndquadrant.com>
<edc1dc0d-66ba-0d9e-b0e7-890f064789d7@BlueTreble.com>
<33b25c65-94f1-6ff6-c717-61a56294a255@2ndquadrant.com>
<a537995b-ccdb-f668-5f67-98e9b9b4a99d@BlueTreble.com>
<75409fb1-bd96-25c2-f6b0-c24e28ed2684@2ndquadrant.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
On 2/22/17 12:29 PM, Peter Eisentraut wrote:
> On 2/22/17 10:14, Jim Nasby wrote:
>> CREATE MATERIALIZED VIEW tmv AS SELECT * FROM pg_subscription;
>> SELECT 0
>>
>> IOW, you can create matviews that depend on any other
>> table/view/matview, but right now if the matview includes certain items
>> it will mysteriously end up empty post-restore.
>
> Yes, by that logic matview refresh should always be last.
Patches for head attached.
RLS was the first item added after DO_REFRESH_MATVIEW, which was added
in 9.5. So if we want to treat this as a bug, they'd need to be patched
as well, which is a simple matter of swapping 33 and 34.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)
diff --git a/src/bin/pg_dump/pg_dump_sort.c b/src/bin/pg_dump/pg_dump_sort.cindex ea643397ba..708a47f3cb 100644--- a/src/bin/pg_dump/pg_dump_sort.c+++ b/src/bin/pg_dump/pg_dump_sort.c@@ -26,6 +26,9 @@ static const char *modulename = gettext_noop("sorter");
* Sort priority for database object types.
* Objects are sorted by type, and within a type by name.
*
+ * Because materialized views can potentially reference system views,+ * DO_REFRESH_MATVIEW should always be the last thing on the list.+ *
* NOTE: object-type priorities must match the section assignments made in
* pg_dump.c; that is, PRE_DATA objects must sort before DO_PRE_DATA_BOUNDARY,
* POST_DATA objects must sort after DO_POST_DATA_BOUNDARY, and DATA objects
@@ -70,11 +73,11 @@ static const int dbObjectTypePriority[] =
22, /* DO_PRE_DATA_BOUNDARY */
26, /* DO_POST_DATA_BOUNDARY */
33, /* DO_EVENT_TRIGGER */
- 34, /* DO_REFRESH_MATVIEW */- 35, /* DO_POLICY */- 36, /* DO_PUBLICATION */- 37, /* DO_PUBLICATION_REL */- 38 /* DO_SUBSCRIPTION */+ 38, /* DO_REFRESH_MATVIEW */+ 34, /* DO_POLICY */+ 35, /* DO_PUBLICATION */+ 36, /* DO_PUBLICATION_REL */+ 37 /* DO_SUBSCRIPTION */
};
static DumpId preDataBoundId;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackersAttachments:
[text/plain] fix_dump_ordering.patch (1.3K, ../65049c27-96bf-317b-b435-931f6041f94c@BlueTreble.com/2-fix_dump_ordering.patch)
download | inline diff:diff --git a/src/bin/pg_dump/pg_dump_sort.c b/src/bin/pg_dump/pg_dump_sort.cindex ea643397ba..708a47f3cb 100644--- a/src/bin/pg_dump/pg_dump_sort.c+++ b/src/bin/pg_dump/pg_dump_sort.c@@ -26,6 +26,9 @@ static const char *modulename = gettext_noop("sorter");
* Sort priority for database object types.
* Objects are sorted by type, and within a type by name.
*
+ * Because materialized views can potentially reference system views,+ * DO_REFRESH_MATVIEW should always be the last thing on the list.+ *
* NOTE: object-type priorities must match the section assignments made in
* pg_dump.c; that is, PRE_DATA objects must sort before DO_PRE_DATA_BOUNDARY,
* POST_DATA objects must sort after DO_POST_DATA_BOUNDARY, and DATA objects
@@ -70,11 +73,11 @@ static const int dbObjectTypePriority[] =
22, /* DO_PRE_DATA_BOUNDARY */
26, /* DO_POST_DATA_BOUNDARY */
33, /* DO_EVENT_TRIGGER */
- 34, /* DO_REFRESH_MATVIEW */- 35, /* DO_POLICY */- 36, /* DO_PUBLICATION */- 37, /* DO_PUBLICATION_REL */- 38 /* DO_SUBSCRIPTION */+ 38, /* DO_REFRESH_MATVIEW */+ 34, /* DO_POLICY */+ 35, /* DO_PUBLICATION */+ 36, /* DO_PUBLICATION_REL */+ 37 /* DO_SUBSCRIPTION */
};
static DumpId preDataBoundId;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: Jim.Nasby@BlueTreble.com, peter.eisentraut@2ndquadrant.com
Subject: Re: Change in "policy" on dump ordering?
In-Reply-To: <65049c27-96bf-317b-b435-931f6041f94c@BlueTreble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox