Hi Kevin,
On 2013-09-03 11:40:57 -0400, Robert Haas wrote:
> On Fri, Aug 30, 2013 at 11:19 AM, Andres Freund <and...@2ndquadrant.com>
> wrote:
> > 0007 wal_decoding: Add information about a tables primary key to struct
> > RelationData
> > * Could be used in the matview refresh code
> I think you and Kevin should discuss whether this is actually the
> right way to do this. ISTM that if logical replication and
> materialized views end up selecting different approaches to this
> problem, everybody loses.
The patch we're discussion here adds a new struct RelationData field
called 'rd_primary' (should possibly be renamed) which contains
information about the "best" candidate key available for a table.
>From the header comments:
/*
* The 'best' primary or candidate key that has been found, only set
* correctly if RelationGetIndexList has been called/rd_indexvalid > 0.
*
* Indexes are chosen in the following order:
* * Primary Key
* * oid index
* * the first (OID order) unique, immediate, non-partial and
* non-expression index over one or more NOT NULL'ed columns
*/
Oid rd_primary;
I thought we could use that in matview.c:refresh_by_match_merge() to
select a more efficient diff if rd_primary has a valid index. In that
case you only'd need to compare that index's fields which should result
in an more efficient plan.
Maybe it's also useful in other cases for you?
If it's relevant at all, would you like to have a different priority
list than the one above?
Regards,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
>From ee85b3bd8d8cc25fa547c004f6f6ea6bccef7c66 Mon Sep 17 00:00:00 2001
From: Andres Freund <and...@anarazel.de>
Date: Mon, 19 Aug 2013 13:24:30 +0200
Subject: [PATCH 4/9] wal_decoding: Add information about a tables primary key
to struct RelationData
'rd_primary' now contains the Oid of an index over uniquely identifying
columns. Several types of indexes are interesting and are collected in that
order:
* Primary Key
* oid index
* the first (OID order) unique, immediate, non-partial and
non-expression index over one or more NOT NULL'ed columns
To gather rd_primary value RelationGetIndexList() needs to have been called.
This is helpful because for logical replication we frequently - on the sending
and receiving side - need to lookup that index and RelationGetIndexList already
gathers all the necessary information.
This could be used to replace tablecmd.c's transformFkeyGetPrimaryKey, but
would change the meaning of that, so it seems to require additional discussion.
---
src/backend/utils/cache/relcache.c | 52 +++++++++++++++++++++++++++++++++++---
src/include/utils/rel.h | 12 +++++++++
2 files changed, 61 insertions(+), 3 deletions(-)
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 66fb63b..c588c29 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -3465,7 +3465,9 @@ RelationGetIndexList(Relation relation)
ScanKeyData skey;
HeapTuple htup;
List *result;
- Oid oidIndex;
+ Oid oidIndex = InvalidOid;
+ Oid pkeyIndex = InvalidOid;
+ Oid candidateIndex = InvalidOid;
MemoryContext oldcxt;
/* Quick exit if we already computed the list. */
@@ -3522,17 +3524,61 @@ RelationGetIndexList(Relation relation)
Assert(!isnull);
indclass = (oidvector *) DatumGetPointer(indclassDatum);
+ if (!IndexIsValid(index))
+ continue;
+
/* Check to see if it is a unique, non-partial btree index on OID */
- if (IndexIsValid(index) &&
- index->indnatts == 1 &&
+ if (index->indnatts == 1 &&
index->indisunique && index->indimmediate &&
index->indkey.values[0] == ObjectIdAttributeNumber &&
indclass->values[0] == OID_BTREE_OPS_OID &&
heap_attisnull(htup, Anum_pg_index_indpred))
oidIndex = index->indexrelid;
+
+ if (index->indisunique &&
+ index->indimmediate &&
+ heap_attisnull(htup, Anum_pg_index_indpred))
+ {
+ /* always prefer primary keys */
+ if (index->indisprimary)
+ pkeyIndex = index->indexrelid;
+ else if (!OidIsValid(pkeyIndex)
+ && !OidIsValid(oidIndex)
+ && !OidIsValid(candidateIndex))
+ {
+ int key;
+ bool found = true;
+ for (key = 0; key < index->indnatts; key++)
+ {
+ int16 attno = index->indkey.values[key];
+ Form_pg_attribute attr;
+ /* internal column, like oid */
+ if (attno <= 0)
+ continue;
+
+ attr = relation->rd_att->attrs[attno - 1];
+ if (!attr->attnotnull)
+ {
+ found = false;
+ break;
+ }
+ }
+ if (found)
+ candidateIndex = index->indexrelid;
+ }
+ }
}
systable_endscan(indscan);
+
+ if (OidIsValid(pkeyIndex))
+ relation->rd_primary = pkeyIndex;
+ /* prefer oid indexes over normal candidate ones */
+ else if (OidIsValid(oidIndex))
+ relation->rd_primary = oidIndex;
+ else if (OidIsValid(candidateIndex))
+ relation->rd_primary = candidateIndex;
+
heap_close(indrel, AccessShareLock);
/* Now save a copy of the completed list in the relcache entry. */
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index 589c9a8..0281b4b 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -111,6 +111,18 @@ typedef struct RelationData
TriggerDesc *trigdesc; /* Trigger info, or NULL if rel has none */
/*
+ * The 'best' primary or candidate key that has been found, only set
+ * correctly if RelationGetIndexList has been called/rd_indexvalid > 0.
+ *
+ * Indexes are chosen in the following order:
+ * * Primary Key
+ * * oid index
+ * * the first (OID order) unique, immediate, non-partial and
+ * non-expression index over one or more NOT NULL'ed columns
+ */
+ Oid rd_primary;
+
+ /*
* rd_options is set whenever rd_rel is loaded into the relcache entry.
* Note that you can NOT look into rd_rel for this data. NULL means "use
* defaults".
--
1.8.3.251.g1462b67
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
