This is an automated email from the ASF dual-hosted git repository.
maxyang pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry-backup.git
The following commit(s) were added to refs/heads/main by this push:
new b60130e8 fix(relations): Ensure materialized view access method is
restored
b60130e8 is described below
commit b60130e8062e0b2a0a031b959bc742869ceedf5d
Author: Robert Mu <[email protected]>
AuthorDate: Wed Oct 15 17:44:05 2025 +0800
fix(relations): Ensure materialized view access method is restored
On Cloudberry, Greenplum 7+, materialized views must be created with
a specific access method (e.g., 'heap', 'ao_row') and associated storage
options in the 'WITH' clause (e.g., 'compresstype=zstd').
The prior implementation did not back up the 'USING' clause.
During restore, the database would default to another access method
(e.g., 'heap') which might not support the view's storage options.
This incompatibility caused the 'CREATE MATERIALIZED VIEW' command
to fail validation.
This commit corrects the failure by:
- Updating the view query for Cloudberry, GPDB 7+ to fetch the access
method.
- Modifying DDL generation to include the 'USING' clause.
- Updating integration tests to verify the fix.
Fixes #61
---
backup/predata_relations.go | 8 +++--
backup/queries_relations.go | 45 ++++++++++++++++++++-------
integration/predata_relations_create_test.go | 7 ++++-
integration/predata_relations_queries_test.go | 11 +++++--
4 files changed, 54 insertions(+), 17 deletions(-)
diff --git a/backup/predata_relations.go b/backup/predata_relations.go
index 8f42a8a2..ead394ab 100644
--- a/backup/predata_relations.go
+++ b/backup/predata_relations.go
@@ -444,8 +444,12 @@ func PrintCreateViewStatement(metadataFile
*utils.FileWithByteCount, objToc *toc
if !view.IsMaterialized {
metadataFile.MustPrintf("\n\nCREATE VIEW %s%s AS %s\n",
view.FQN(), view.Options, view.Definition.String)
} else {
- metadataFile.MustPrintf("\n\nCREATE MATERIALIZED VIEW %s%s%s AS
%s\nWITH NO DATA\n%s;\n",
- view.FQN(), view.Options, tablespaceClause,
view.Definition.String[:len(view.Definition.String)-1], view.DistPolicy.Policy)
+ accessMethodClause := ""
+ if view.AccessMethodName != "" {
+ accessMethodClause = fmt.Sprintf(" USING %s",
view.AccessMethodName)
+ }
+ metadataFile.MustPrintf("\n\nCREATE MATERIALIZED VIEW %s%s%s%s
AS %s\nWITH NO DATA\n%s;\n",
+ view.FQN(), accessMethodClause, view.Options,
tablespaceClause, view.Definition.String[:len(view.Definition.String)-1],
view.DistPolicy.Policy)
}
section, entry := view.GetMetadataEntry()
tier := globalTierMap[view.GetUniqueID()]
diff --git a/backup/queries_relations.go b/backup/queries_relations.go
index ed16db98..34652a84 100644
--- a/backup/queries_relations.go
+++ b/backup/queries_relations.go
@@ -344,16 +344,17 @@ func GetSequenceDefinition(connectionPool *dbconn.DBConn,
seqName string) Sequen
}
type View struct {
- Oid uint32
- Schema string
- Name string
- Options string
- Definition sql.NullString
- Tablespace string
- IsMaterialized bool
- DistPolicy DistPolicy
- NeedsDummy bool
- ColumnDefs []ColumnDefinition
+ Oid uint32
+ Schema string
+ Name string
+ Options string
+ Definition sql.NullString
+ Tablespace string
+ IsMaterialized bool
+ DistPolicy DistPolicy
+ NeedsDummy bool
+ ColumnDefs []ColumnDefinition
+ AccessMethodName string
}
func (v View) GetMetadataEntry() (string, toc.MetadataEntry) {
@@ -412,7 +413,7 @@ func GetAllViews(connectionPool *dbconn.DBConn) []View {
// Materialized views were introduced in GPDB 7 and backported to GPDB
6.2.
// Reloptions and tablespace added to pg_class in GPDB 6
- atLeast6Query := fmt.Sprintf(`
+ version6Query := fmt.Sprintf(`
SELECT
c.oid AS oid,
quote_ident(n.nspname) AS schema,
@@ -428,11 +429,31 @@ func GetAllViews(connectionPool *dbconn.DBConn) []View {
AND %s
AND %s`, relationAndSchemaFilterClause(),
ExtensionFilterClause("c"))
+ atLeast7Query := fmt.Sprintf(`
+ SELECT
+ c.oid AS oid,
+ quote_ident(n.nspname) AS schema,
+ quote_ident(c.relname) AS name,
+ pg_get_viewdef(c.oid) AS definition,
+ coalesce(' WITH (' || array_to_string(c.reloptions, ', ') ||
')', '') AS options,
+ coalesce(quote_ident(t.spcname), '') AS tablespace,
+ c.relkind='m' AS ismaterialized,
+ coalesce(quote_ident(am.amname), '') AS accessmethodname
+ FROM pg_class c
+ LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
+ LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
+ LEFT JOIN pg_am am ON am.oid = c.relam
+ WHERE c.relkind IN ('m', 'v')
+ AND %s
+ AND %s`, relationAndSchemaFilterClause(),
ExtensionFilterClause("c"))
+
query := ""
if connectionPool.Version.IsGPDB() &&
connectionPool.Version.Before("6") {
query = before6Query
+ } else if connectionPool.Version.IsGPDB() &&
connectionPool.Version.Is("6") {
+ query = version6Query
} else {
- query = atLeast6Query
+ query = atLeast7Query
}
results := make([]View, 0)
diff --git a/integration/predata_relations_create_test.go
b/integration/predata_relations_create_test.go
index 3d825f11..5a92c391 100644
--- a/integration/predata_relations_create_test.go
+++ b/integration/predata_relations_create_test.go
@@ -576,6 +576,9 @@ SET SUBPARTITION TEMPLATE ` + `
})
It("creates a view with privileges, owner, security label, and
comment", func() {
view := backup.View{Oid: 1, Schema: "public", Name:
"simplemview", Definition: sql.NullString{String: " SELECT 1 AS a;", Valid:
true}, IsMaterialized: true, DistPolicy: backup.DistPolicy{Policy: "DISTRIBUTED
BY (a)"}}
+ if (connectionPool.Version.IsGPDB() &&
connectionPool.Version.AtLeast("7")) || connectionPool.Version.IsCBDB() {
+ view.AccessMethodName = "heap"
+ }
viewMetadata :=
testutils.DefaultMetadata(toc.OBJ_MATERIALIZED_VIEW, true, true, true,
includeSecurityLabels)
backup.PrintCreateViewStatement(backupfile, tocfile,
view, viewMetadata)
@@ -594,7 +597,9 @@ SET SUBPARTITION TEMPLATE ` + `
})
It("creates a materialized view with options", func() {
view := backup.View{Oid: 1, Schema: "public", Name:
"simplemview", Options: " WITH (fillfactor=10)", Definition:
sql.NullString{String: " SELECT 1 AS a;", Valid: true}, IsMaterialized: true,
DistPolicy: backup.DistPolicy{Policy: "DISTRIBUTED BY (a)"}}
-
+ if (connectionPool.Version.IsGPDB() &&
connectionPool.Version.AtLeast("7")) || connectionPool.Version.IsCBDB() {
+ view.AccessMethodName = "heap"
+ }
backup.PrintCreateViewStatement(backupfile, tocfile,
view, backup.ObjectMetadata{})
testhelper.AssertQueryRuns(connectionPool,
buffer.String())
diff --git a/integration/predata_relations_queries_test.go
b/integration/predata_relations_queries_test.go
index af97c5c9..a0fc446e 100644
--- a/integration/predata_relations_queries_test.go
+++ b/integration/predata_relations_queries_test.go
@@ -512,6 +512,9 @@ PARTITION BY LIST (gender)
results := backup.GetAllViews(connectionPool)
materialView := backup.View{Oid: 1, Schema: "public",
Name: "simplematerialview", Definition: sql.NullString{String: " SELECT 1 AS
a;", Valid: true}, IsMaterialized: true, DistPolicy: backup.DistPolicy{Policy:
"DISTRIBUTED BY (a)"}}
+ if (connectionPool.Version.IsGPDB() &&
connectionPool.Version.AtLeast("7")) || connectionPool.Version.IsCBDB() {
+ materialView.AccessMethodName = "heap"
+ }
materialView.Oid =
testutils.OidFromObjectName(connectionPool, "public", "simplematerialview",
backup.TYPE_RELATION)
Expect(results).To(HaveLen(1))
@@ -526,7 +529,9 @@ PARTITION BY LIST (gender)
results := backup.GetAllViews(connectionPool)
materialView := backup.View{Oid: 1, Schema: "public",
Name: "simplematerialview", Definition: sql.NullString{String: " SELECT 1 AS
a;", Valid: true}, Options: " WITH (fillfactor=50, autovacuum_enabled=false)",
IsMaterialized: true, DistPolicy: backup.DistPolicy{Policy: "DISTRIBUTED BY
(a)"}}
-
+ if (connectionPool.Version.IsGPDB() &&
connectionPool.Version.AtLeast("7")) || connectionPool.Version.IsCBDB() {
+ materialView.AccessMethodName = "heap"
+ }
materialView.Oid =
testutils.OidFromObjectName(connectionPool, "public", "simplematerialview",
backup.TYPE_RELATION)
Expect(results).To(HaveLen(1))
structmatcher.ExpectStructsToMatchExcluding(&materialView, &results[0],
"ColumnDefs", "DistPolicy.Oid")
@@ -542,7 +547,9 @@ PARTITION BY LIST (gender)
results := backup.GetAllViews(connectionPool)
materialView := backup.View{Oid: 1, Schema: "public",
Name: "simplematerialview", Definition: sql.NullString{String: " SELECT 1 AS
a;", Valid: true}, Tablespace: "test_tablespace", IsMaterialized: true,
DistPolicy: backup.DistPolicy{Policy: "DISTRIBUTED BY (a)"}}
-
+ if (connectionPool.Version.IsGPDB() &&
connectionPool.Version.AtLeast("7")) || connectionPool.Version.IsCBDB() {
+ materialView.AccessMethodName = "heap"
+ }
Expect(results).To(HaveLen(1))
structmatcher.ExpectStructsToMatchExcluding(&materialView, &results[0], "Oid",
"ColumnDefs", "DistPolicy.Oid")
})
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
