public inbox for [email protected]
help / color / mirror / Atom feed[PATCH 8/8] tweak costing for bloom/minmax-multi indexes
26+ messages / 3 participants
[nested] [flat]
* [PATCH 8/8] tweak costing for bloom/minmax-multi indexes
@ 2020-08-07 13:53 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Tomas Vondra @ 2020-08-07 13:53 UTC (permalink / raw)
---
src/backend/access/brin/brin_bloom.c | 1 +
src/backend/access/brin/brin_minmax_multi.c | 1 +
src/backend/utils/adt/selfuncs.c | 19 ++++++++++++++++++-
src/include/access/brin_internal.h | 3 +++
4 files changed, 23 insertions(+), 1 deletion(-)
diff --git a/src/backend/access/brin/brin_bloom.c b/src/backend/access/brin/brin_bloom.c
index e7ca100821..f64381b657 100644
--- a/src/backend/access/brin/brin_bloom.c
+++ b/src/backend/access/brin/brin_bloom.c
@@ -643,6 +643,7 @@ brin_bloom_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(BloomOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (BloomOpaque *)
diff --git a/src/backend/access/brin/brin_minmax_multi.c b/src/backend/access/brin/brin_minmax_multi.c
index f700700d46..5f637e0629 100644
--- a/src/backend/access/brin/brin_minmax_multi.c
+++ b/src/backend/access/brin/brin_minmax_multi.c
@@ -1301,6 +1301,7 @@ brin_minmax_multi_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(MinmaxMultiOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (MinmaxMultiOpaque *)
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 53d974125f..956d6f0a84 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -98,6 +98,7 @@
#include <math.h>
#include "access/brin.h"
+#include "access/brin_internal.h"
#include "access/brin_page.h"
#include "access/gin.h"
#include "access/table.h"
@@ -7348,7 +7349,8 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
double minimalRanges;
double estimatedRanges;
double selec;
- Relation indexRel;
+ Relation indexRel = NULL;
+ TupleDesc tupdesc = NULL;
ListCell *l;
VariableStatData vardata;
@@ -7370,6 +7372,7 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
*/
indexRel = index_open(index->indexoid, NoLock);
brinGetStats(indexRel, &statsData);
+ tupdesc = RelationGetDescr(indexRel);
index_close(indexRel, NoLock);
/* work out the actual number of ranges in the index */
@@ -7403,6 +7406,17 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
{
IndexClause *iclause = lfirst_node(IndexClause, l);
AttrNumber attnum = index->indexkeys[iclause->indexcol];
+ FmgrInfo *opcInfoFn;
+ BrinOpcInfo *opcInfo;
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, iclause->indexcol);
+ bool ignore_correlation;
+
+ opcInfoFn = index_getprocinfo(indexRel, iclause->indexcol + 1, BRIN_PROCNUM_OPCINFO);
+
+ opcInfo = (BrinOpcInfo *)
+ DatumGetPointer(FunctionCall1(opcInfoFn, attr->atttypid));
+
+ ignore_correlation = opcInfo->oi_ignore_correlation;
/* attempt to lookup stats in relation for this index column */
if (attnum != 0)
@@ -7473,6 +7487,9 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
if (sslot.nnumbers > 0)
varCorrelation = Abs(sslot.numbers[0]);
+ if (ignore_correlation)
+ varCorrelation = 1.0;
+
if (varCorrelation > *indexCorrelation)
*indexCorrelation = varCorrelation;
diff --git a/src/include/access/brin_internal.h b/src/include/access/brin_internal.h
index ee4d0706df..67aea62a02 100644
--- a/src/include/access/brin_internal.h
+++ b/src/include/access/brin_internal.h
@@ -30,6 +30,9 @@ typedef struct BrinOpcInfo
/* Regular processing of NULLs in BrinValues? */
bool oi_regular_nulls;
+ /* Ignore correlation during cost estimation */
+ bool oi_ignore_correlation;
+
/* Opaque pointer for the opclass' private use */
void *oi_opaque;
--
2.25.4
--jsivyprvf2oxfjz3--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH 8/8] tweak costing for bloom/minmax-multi indexes
@ 2020-08-07 13:53 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Tomas Vondra @ 2020-08-07 13:53 UTC (permalink / raw)
---
src/backend/access/brin/brin_bloom.c | 1 +
src/backend/access/brin/brin_minmax_multi.c | 1 +
src/backend/utils/adt/selfuncs.c | 19 ++++++++++++++++++-
src/include/access/brin_internal.h | 3 +++
4 files changed, 23 insertions(+), 1 deletion(-)
diff --git a/src/backend/access/brin/brin_bloom.c b/src/backend/access/brin/brin_bloom.c
index e7ca100821..f64381b657 100644
--- a/src/backend/access/brin/brin_bloom.c
+++ b/src/backend/access/brin/brin_bloom.c
@@ -643,6 +643,7 @@ brin_bloom_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(BloomOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (BloomOpaque *)
diff --git a/src/backend/access/brin/brin_minmax_multi.c b/src/backend/access/brin/brin_minmax_multi.c
index f700700d46..5f637e0629 100644
--- a/src/backend/access/brin/brin_minmax_multi.c
+++ b/src/backend/access/brin/brin_minmax_multi.c
@@ -1301,6 +1301,7 @@ brin_minmax_multi_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(MinmaxMultiOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (MinmaxMultiOpaque *)
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 53d974125f..956d6f0a84 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -98,6 +98,7 @@
#include <math.h>
#include "access/brin.h"
+#include "access/brin_internal.h"
#include "access/brin_page.h"
#include "access/gin.h"
#include "access/table.h"
@@ -7348,7 +7349,8 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
double minimalRanges;
double estimatedRanges;
double selec;
- Relation indexRel;
+ Relation indexRel = NULL;
+ TupleDesc tupdesc = NULL;
ListCell *l;
VariableStatData vardata;
@@ -7370,6 +7372,7 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
*/
indexRel = index_open(index->indexoid, NoLock);
brinGetStats(indexRel, &statsData);
+ tupdesc = RelationGetDescr(indexRel);
index_close(indexRel, NoLock);
/* work out the actual number of ranges in the index */
@@ -7403,6 +7406,17 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
{
IndexClause *iclause = lfirst_node(IndexClause, l);
AttrNumber attnum = index->indexkeys[iclause->indexcol];
+ FmgrInfo *opcInfoFn;
+ BrinOpcInfo *opcInfo;
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, iclause->indexcol);
+ bool ignore_correlation;
+
+ opcInfoFn = index_getprocinfo(indexRel, iclause->indexcol + 1, BRIN_PROCNUM_OPCINFO);
+
+ opcInfo = (BrinOpcInfo *)
+ DatumGetPointer(FunctionCall1(opcInfoFn, attr->atttypid));
+
+ ignore_correlation = opcInfo->oi_ignore_correlation;
/* attempt to lookup stats in relation for this index column */
if (attnum != 0)
@@ -7473,6 +7487,9 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
if (sslot.nnumbers > 0)
varCorrelation = Abs(sslot.numbers[0]);
+ if (ignore_correlation)
+ varCorrelation = 1.0;
+
if (varCorrelation > *indexCorrelation)
*indexCorrelation = varCorrelation;
diff --git a/src/include/access/brin_internal.h b/src/include/access/brin_internal.h
index ee4d0706df..67aea62a02 100644
--- a/src/include/access/brin_internal.h
+++ b/src/include/access/brin_internal.h
@@ -30,6 +30,9 @@ typedef struct BrinOpcInfo
/* Regular processing of NULLs in BrinValues? */
bool oi_regular_nulls;
+ /* Ignore correlation during cost estimation */
+ bool oi_ignore_correlation;
+
/* Opaque pointer for the opclass' private use */
void *oi_opaque;
--
2.25.4
--jsivyprvf2oxfjz3--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH 8/8] tweak costing for bloom/minmax-multi indexes
@ 2020-08-07 13:53 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Tomas Vondra @ 2020-08-07 13:53 UTC (permalink / raw)
---
src/backend/access/brin/brin_bloom.c | 1 +
src/backend/access/brin/brin_minmax_multi.c | 1 +
src/backend/utils/adt/selfuncs.c | 19 ++++++++++++++++++-
src/include/access/brin_internal.h | 3 +++
4 files changed, 23 insertions(+), 1 deletion(-)
diff --git a/src/backend/access/brin/brin_bloom.c b/src/backend/access/brin/brin_bloom.c
index e7ca100821..f64381b657 100644
--- a/src/backend/access/brin/brin_bloom.c
+++ b/src/backend/access/brin/brin_bloom.c
@@ -643,6 +643,7 @@ brin_bloom_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(BloomOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (BloomOpaque *)
diff --git a/src/backend/access/brin/brin_minmax_multi.c b/src/backend/access/brin/brin_minmax_multi.c
index f700700d46..5f637e0629 100644
--- a/src/backend/access/brin/brin_minmax_multi.c
+++ b/src/backend/access/brin/brin_minmax_multi.c
@@ -1301,6 +1301,7 @@ brin_minmax_multi_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(MinmaxMultiOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (MinmaxMultiOpaque *)
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 53d974125f..956d6f0a84 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -98,6 +98,7 @@
#include <math.h>
#include "access/brin.h"
+#include "access/brin_internal.h"
#include "access/brin_page.h"
#include "access/gin.h"
#include "access/table.h"
@@ -7348,7 +7349,8 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
double minimalRanges;
double estimatedRanges;
double selec;
- Relation indexRel;
+ Relation indexRel = NULL;
+ TupleDesc tupdesc = NULL;
ListCell *l;
VariableStatData vardata;
@@ -7370,6 +7372,7 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
*/
indexRel = index_open(index->indexoid, NoLock);
brinGetStats(indexRel, &statsData);
+ tupdesc = RelationGetDescr(indexRel);
index_close(indexRel, NoLock);
/* work out the actual number of ranges in the index */
@@ -7403,6 +7406,17 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
{
IndexClause *iclause = lfirst_node(IndexClause, l);
AttrNumber attnum = index->indexkeys[iclause->indexcol];
+ FmgrInfo *opcInfoFn;
+ BrinOpcInfo *opcInfo;
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, iclause->indexcol);
+ bool ignore_correlation;
+
+ opcInfoFn = index_getprocinfo(indexRel, iclause->indexcol + 1, BRIN_PROCNUM_OPCINFO);
+
+ opcInfo = (BrinOpcInfo *)
+ DatumGetPointer(FunctionCall1(opcInfoFn, attr->atttypid));
+
+ ignore_correlation = opcInfo->oi_ignore_correlation;
/* attempt to lookup stats in relation for this index column */
if (attnum != 0)
@@ -7473,6 +7487,9 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
if (sslot.nnumbers > 0)
varCorrelation = Abs(sslot.numbers[0]);
+ if (ignore_correlation)
+ varCorrelation = 1.0;
+
if (varCorrelation > *indexCorrelation)
*indexCorrelation = varCorrelation;
diff --git a/src/include/access/brin_internal.h b/src/include/access/brin_internal.h
index ee4d0706df..67aea62a02 100644
--- a/src/include/access/brin_internal.h
+++ b/src/include/access/brin_internal.h
@@ -30,6 +30,9 @@ typedef struct BrinOpcInfo
/* Regular processing of NULLs in BrinValues? */
bool oi_regular_nulls;
+ /* Ignore correlation during cost estimation */
+ bool oi_ignore_correlation;
+
/* Opaque pointer for the opclass' private use */
void *oi_opaque;
--
2.25.4
--jsivyprvf2oxfjz3--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH 8/8] tweak costing for bloom/minmax-multi indexes
@ 2020-08-07 13:53 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Tomas Vondra @ 2020-08-07 13:53 UTC (permalink / raw)
---
src/backend/access/brin/brin_bloom.c | 1 +
src/backend/access/brin/brin_minmax_multi.c | 1 +
src/backend/utils/adt/selfuncs.c | 19 ++++++++++++++++++-
src/include/access/brin_internal.h | 3 +++
4 files changed, 23 insertions(+), 1 deletion(-)
diff --git a/src/backend/access/brin/brin_bloom.c b/src/backend/access/brin/brin_bloom.c
index e7ca100821..f64381b657 100644
--- a/src/backend/access/brin/brin_bloom.c
+++ b/src/backend/access/brin/brin_bloom.c
@@ -643,6 +643,7 @@ brin_bloom_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(BloomOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (BloomOpaque *)
diff --git a/src/backend/access/brin/brin_minmax_multi.c b/src/backend/access/brin/brin_minmax_multi.c
index f700700d46..5f637e0629 100644
--- a/src/backend/access/brin/brin_minmax_multi.c
+++ b/src/backend/access/brin/brin_minmax_multi.c
@@ -1301,6 +1301,7 @@ brin_minmax_multi_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(MinmaxMultiOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (MinmaxMultiOpaque *)
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 53d974125f..956d6f0a84 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -98,6 +98,7 @@
#include <math.h>
#include "access/brin.h"
+#include "access/brin_internal.h"
#include "access/brin_page.h"
#include "access/gin.h"
#include "access/table.h"
@@ -7348,7 +7349,8 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
double minimalRanges;
double estimatedRanges;
double selec;
- Relation indexRel;
+ Relation indexRel = NULL;
+ TupleDesc tupdesc = NULL;
ListCell *l;
VariableStatData vardata;
@@ -7370,6 +7372,7 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
*/
indexRel = index_open(index->indexoid, NoLock);
brinGetStats(indexRel, &statsData);
+ tupdesc = RelationGetDescr(indexRel);
index_close(indexRel, NoLock);
/* work out the actual number of ranges in the index */
@@ -7403,6 +7406,17 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
{
IndexClause *iclause = lfirst_node(IndexClause, l);
AttrNumber attnum = index->indexkeys[iclause->indexcol];
+ FmgrInfo *opcInfoFn;
+ BrinOpcInfo *opcInfo;
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, iclause->indexcol);
+ bool ignore_correlation;
+
+ opcInfoFn = index_getprocinfo(indexRel, iclause->indexcol + 1, BRIN_PROCNUM_OPCINFO);
+
+ opcInfo = (BrinOpcInfo *)
+ DatumGetPointer(FunctionCall1(opcInfoFn, attr->atttypid));
+
+ ignore_correlation = opcInfo->oi_ignore_correlation;
/* attempt to lookup stats in relation for this index column */
if (attnum != 0)
@@ -7473,6 +7487,9 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
if (sslot.nnumbers > 0)
varCorrelation = Abs(sslot.numbers[0]);
+ if (ignore_correlation)
+ varCorrelation = 1.0;
+
if (varCorrelation > *indexCorrelation)
*indexCorrelation = varCorrelation;
diff --git a/src/include/access/brin_internal.h b/src/include/access/brin_internal.h
index ee4d0706df..67aea62a02 100644
--- a/src/include/access/brin_internal.h
+++ b/src/include/access/brin_internal.h
@@ -30,6 +30,9 @@ typedef struct BrinOpcInfo
/* Regular processing of NULLs in BrinValues? */
bool oi_regular_nulls;
+ /* Ignore correlation during cost estimation */
+ bool oi_ignore_correlation;
+
/* Opaque pointer for the opclass' private use */
void *oi_opaque;
--
2.25.4
--jsivyprvf2oxfjz3--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH 8/8] tweak costing for bloom/minmax-multi indexes
@ 2020-08-07 13:53 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Tomas Vondra @ 2020-08-07 13:53 UTC (permalink / raw)
---
src/backend/access/brin/brin_bloom.c | 1 +
src/backend/access/brin/brin_minmax_multi.c | 1 +
src/backend/utils/adt/selfuncs.c | 19 ++++++++++++++++++-
src/include/access/brin_internal.h | 3 +++
4 files changed, 23 insertions(+), 1 deletion(-)
diff --git a/src/backend/access/brin/brin_bloom.c b/src/backend/access/brin/brin_bloom.c
index e7ca100821..f64381b657 100644
--- a/src/backend/access/brin/brin_bloom.c
+++ b/src/backend/access/brin/brin_bloom.c
@@ -643,6 +643,7 @@ brin_bloom_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(BloomOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (BloomOpaque *)
diff --git a/src/backend/access/brin/brin_minmax_multi.c b/src/backend/access/brin/brin_minmax_multi.c
index f700700d46..5f637e0629 100644
--- a/src/backend/access/brin/brin_minmax_multi.c
+++ b/src/backend/access/brin/brin_minmax_multi.c
@@ -1301,6 +1301,7 @@ brin_minmax_multi_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(MinmaxMultiOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (MinmaxMultiOpaque *)
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 53d974125f..956d6f0a84 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -98,6 +98,7 @@
#include <math.h>
#include "access/brin.h"
+#include "access/brin_internal.h"
#include "access/brin_page.h"
#include "access/gin.h"
#include "access/table.h"
@@ -7348,7 +7349,8 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
double minimalRanges;
double estimatedRanges;
double selec;
- Relation indexRel;
+ Relation indexRel = NULL;
+ TupleDesc tupdesc = NULL;
ListCell *l;
VariableStatData vardata;
@@ -7370,6 +7372,7 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
*/
indexRel = index_open(index->indexoid, NoLock);
brinGetStats(indexRel, &statsData);
+ tupdesc = RelationGetDescr(indexRel);
index_close(indexRel, NoLock);
/* work out the actual number of ranges in the index */
@@ -7403,6 +7406,17 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
{
IndexClause *iclause = lfirst_node(IndexClause, l);
AttrNumber attnum = index->indexkeys[iclause->indexcol];
+ FmgrInfo *opcInfoFn;
+ BrinOpcInfo *opcInfo;
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, iclause->indexcol);
+ bool ignore_correlation;
+
+ opcInfoFn = index_getprocinfo(indexRel, iclause->indexcol + 1, BRIN_PROCNUM_OPCINFO);
+
+ opcInfo = (BrinOpcInfo *)
+ DatumGetPointer(FunctionCall1(opcInfoFn, attr->atttypid));
+
+ ignore_correlation = opcInfo->oi_ignore_correlation;
/* attempt to lookup stats in relation for this index column */
if (attnum != 0)
@@ -7473,6 +7487,9 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
if (sslot.nnumbers > 0)
varCorrelation = Abs(sslot.numbers[0]);
+ if (ignore_correlation)
+ varCorrelation = 1.0;
+
if (varCorrelation > *indexCorrelation)
*indexCorrelation = varCorrelation;
diff --git a/src/include/access/brin_internal.h b/src/include/access/brin_internal.h
index ee4d0706df..67aea62a02 100644
--- a/src/include/access/brin_internal.h
+++ b/src/include/access/brin_internal.h
@@ -30,6 +30,9 @@ typedef struct BrinOpcInfo
/* Regular processing of NULLs in BrinValues? */
bool oi_regular_nulls;
+ /* Ignore correlation during cost estimation */
+ bool oi_ignore_correlation;
+
/* Opaque pointer for the opclass' private use */
void *oi_opaque;
--
2.25.4
--jsivyprvf2oxfjz3--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH 8/8] tweak costing for bloom/minmax-multi indexes
@ 2020-08-07 13:53 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Tomas Vondra @ 2020-08-07 13:53 UTC (permalink / raw)
---
src/backend/access/brin/brin_bloom.c | 1 +
src/backend/access/brin/brin_minmax_multi.c | 1 +
src/backend/utils/adt/selfuncs.c | 19 ++++++++++++++++++-
src/include/access/brin_internal.h | 3 +++
4 files changed, 23 insertions(+), 1 deletion(-)
diff --git a/src/backend/access/brin/brin_bloom.c b/src/backend/access/brin/brin_bloom.c
index e7ca100821..f64381b657 100644
--- a/src/backend/access/brin/brin_bloom.c
+++ b/src/backend/access/brin/brin_bloom.c
@@ -643,6 +643,7 @@ brin_bloom_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(BloomOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (BloomOpaque *)
diff --git a/src/backend/access/brin/brin_minmax_multi.c b/src/backend/access/brin/brin_minmax_multi.c
index f700700d46..5f637e0629 100644
--- a/src/backend/access/brin/brin_minmax_multi.c
+++ b/src/backend/access/brin/brin_minmax_multi.c
@@ -1301,6 +1301,7 @@ brin_minmax_multi_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(MinmaxMultiOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (MinmaxMultiOpaque *)
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 53d974125f..956d6f0a84 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -98,6 +98,7 @@
#include <math.h>
#include "access/brin.h"
+#include "access/brin_internal.h"
#include "access/brin_page.h"
#include "access/gin.h"
#include "access/table.h"
@@ -7348,7 +7349,8 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
double minimalRanges;
double estimatedRanges;
double selec;
- Relation indexRel;
+ Relation indexRel = NULL;
+ TupleDesc tupdesc = NULL;
ListCell *l;
VariableStatData vardata;
@@ -7370,6 +7372,7 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
*/
indexRel = index_open(index->indexoid, NoLock);
brinGetStats(indexRel, &statsData);
+ tupdesc = RelationGetDescr(indexRel);
index_close(indexRel, NoLock);
/* work out the actual number of ranges in the index */
@@ -7403,6 +7406,17 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
{
IndexClause *iclause = lfirst_node(IndexClause, l);
AttrNumber attnum = index->indexkeys[iclause->indexcol];
+ FmgrInfo *opcInfoFn;
+ BrinOpcInfo *opcInfo;
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, iclause->indexcol);
+ bool ignore_correlation;
+
+ opcInfoFn = index_getprocinfo(indexRel, iclause->indexcol + 1, BRIN_PROCNUM_OPCINFO);
+
+ opcInfo = (BrinOpcInfo *)
+ DatumGetPointer(FunctionCall1(opcInfoFn, attr->atttypid));
+
+ ignore_correlation = opcInfo->oi_ignore_correlation;
/* attempt to lookup stats in relation for this index column */
if (attnum != 0)
@@ -7473,6 +7487,9 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
if (sslot.nnumbers > 0)
varCorrelation = Abs(sslot.numbers[0]);
+ if (ignore_correlation)
+ varCorrelation = 1.0;
+
if (varCorrelation > *indexCorrelation)
*indexCorrelation = varCorrelation;
diff --git a/src/include/access/brin_internal.h b/src/include/access/brin_internal.h
index ee4d0706df..67aea62a02 100644
--- a/src/include/access/brin_internal.h
+++ b/src/include/access/brin_internal.h
@@ -30,6 +30,9 @@ typedef struct BrinOpcInfo
/* Regular processing of NULLs in BrinValues? */
bool oi_regular_nulls;
+ /* Ignore correlation during cost estimation */
+ bool oi_ignore_correlation;
+
/* Opaque pointer for the opclass' private use */
void *oi_opaque;
--
2.25.4
--jsivyprvf2oxfjz3--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH 8/8] tweak costing for bloom/minmax-multi indexes
@ 2020-08-07 13:53 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Tomas Vondra @ 2020-08-07 13:53 UTC (permalink / raw)
---
src/backend/access/brin/brin_bloom.c | 1 +
src/backend/access/brin/brin_minmax_multi.c | 1 +
src/backend/utils/adt/selfuncs.c | 19 ++++++++++++++++++-
src/include/access/brin_internal.h | 3 +++
4 files changed, 23 insertions(+), 1 deletion(-)
diff --git a/src/backend/access/brin/brin_bloom.c b/src/backend/access/brin/brin_bloom.c
index e7ca100821..f64381b657 100644
--- a/src/backend/access/brin/brin_bloom.c
+++ b/src/backend/access/brin/brin_bloom.c
@@ -643,6 +643,7 @@ brin_bloom_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(BloomOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (BloomOpaque *)
diff --git a/src/backend/access/brin/brin_minmax_multi.c b/src/backend/access/brin/brin_minmax_multi.c
index f700700d46..5f637e0629 100644
--- a/src/backend/access/brin/brin_minmax_multi.c
+++ b/src/backend/access/brin/brin_minmax_multi.c
@@ -1301,6 +1301,7 @@ brin_minmax_multi_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(MinmaxMultiOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (MinmaxMultiOpaque *)
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 53d974125f..956d6f0a84 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -98,6 +98,7 @@
#include <math.h>
#include "access/brin.h"
+#include "access/brin_internal.h"
#include "access/brin_page.h"
#include "access/gin.h"
#include "access/table.h"
@@ -7348,7 +7349,8 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
double minimalRanges;
double estimatedRanges;
double selec;
- Relation indexRel;
+ Relation indexRel = NULL;
+ TupleDesc tupdesc = NULL;
ListCell *l;
VariableStatData vardata;
@@ -7370,6 +7372,7 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
*/
indexRel = index_open(index->indexoid, NoLock);
brinGetStats(indexRel, &statsData);
+ tupdesc = RelationGetDescr(indexRel);
index_close(indexRel, NoLock);
/* work out the actual number of ranges in the index */
@@ -7403,6 +7406,17 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
{
IndexClause *iclause = lfirst_node(IndexClause, l);
AttrNumber attnum = index->indexkeys[iclause->indexcol];
+ FmgrInfo *opcInfoFn;
+ BrinOpcInfo *opcInfo;
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, iclause->indexcol);
+ bool ignore_correlation;
+
+ opcInfoFn = index_getprocinfo(indexRel, iclause->indexcol + 1, BRIN_PROCNUM_OPCINFO);
+
+ opcInfo = (BrinOpcInfo *)
+ DatumGetPointer(FunctionCall1(opcInfoFn, attr->atttypid));
+
+ ignore_correlation = opcInfo->oi_ignore_correlation;
/* attempt to lookup stats in relation for this index column */
if (attnum != 0)
@@ -7473,6 +7487,9 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
if (sslot.nnumbers > 0)
varCorrelation = Abs(sslot.numbers[0]);
+ if (ignore_correlation)
+ varCorrelation = 1.0;
+
if (varCorrelation > *indexCorrelation)
*indexCorrelation = varCorrelation;
diff --git a/src/include/access/brin_internal.h b/src/include/access/brin_internal.h
index ee4d0706df..67aea62a02 100644
--- a/src/include/access/brin_internal.h
+++ b/src/include/access/brin_internal.h
@@ -30,6 +30,9 @@ typedef struct BrinOpcInfo
/* Regular processing of NULLs in BrinValues? */
bool oi_regular_nulls;
+ /* Ignore correlation during cost estimation */
+ bool oi_ignore_correlation;
+
/* Opaque pointer for the opclass' private use */
void *oi_opaque;
--
2.25.4
--jsivyprvf2oxfjz3--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH 8/8] tweak costing for bloom/minmax-multi indexes
@ 2020-08-07 13:53 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Tomas Vondra @ 2020-08-07 13:53 UTC (permalink / raw)
---
src/backend/access/brin/brin_bloom.c | 1 +
src/backend/access/brin/brin_minmax_multi.c | 1 +
src/backend/utils/adt/selfuncs.c | 19 ++++++++++++++++++-
src/include/access/brin_internal.h | 3 +++
4 files changed, 23 insertions(+), 1 deletion(-)
diff --git a/src/backend/access/brin/brin_bloom.c b/src/backend/access/brin/brin_bloom.c
index e7ca100821..f64381b657 100644
--- a/src/backend/access/brin/brin_bloom.c
+++ b/src/backend/access/brin/brin_bloom.c
@@ -643,6 +643,7 @@ brin_bloom_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(BloomOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (BloomOpaque *)
diff --git a/src/backend/access/brin/brin_minmax_multi.c b/src/backend/access/brin/brin_minmax_multi.c
index f700700d46..5f637e0629 100644
--- a/src/backend/access/brin/brin_minmax_multi.c
+++ b/src/backend/access/brin/brin_minmax_multi.c
@@ -1301,6 +1301,7 @@ brin_minmax_multi_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(MinmaxMultiOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (MinmaxMultiOpaque *)
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 53d974125f..956d6f0a84 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -98,6 +98,7 @@
#include <math.h>
#include "access/brin.h"
+#include "access/brin_internal.h"
#include "access/brin_page.h"
#include "access/gin.h"
#include "access/table.h"
@@ -7348,7 +7349,8 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
double minimalRanges;
double estimatedRanges;
double selec;
- Relation indexRel;
+ Relation indexRel = NULL;
+ TupleDesc tupdesc = NULL;
ListCell *l;
VariableStatData vardata;
@@ -7370,6 +7372,7 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
*/
indexRel = index_open(index->indexoid, NoLock);
brinGetStats(indexRel, &statsData);
+ tupdesc = RelationGetDescr(indexRel);
index_close(indexRel, NoLock);
/* work out the actual number of ranges in the index */
@@ -7403,6 +7406,17 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
{
IndexClause *iclause = lfirst_node(IndexClause, l);
AttrNumber attnum = index->indexkeys[iclause->indexcol];
+ FmgrInfo *opcInfoFn;
+ BrinOpcInfo *opcInfo;
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, iclause->indexcol);
+ bool ignore_correlation;
+
+ opcInfoFn = index_getprocinfo(indexRel, iclause->indexcol + 1, BRIN_PROCNUM_OPCINFO);
+
+ opcInfo = (BrinOpcInfo *)
+ DatumGetPointer(FunctionCall1(opcInfoFn, attr->atttypid));
+
+ ignore_correlation = opcInfo->oi_ignore_correlation;
/* attempt to lookup stats in relation for this index column */
if (attnum != 0)
@@ -7473,6 +7487,9 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
if (sslot.nnumbers > 0)
varCorrelation = Abs(sslot.numbers[0]);
+ if (ignore_correlation)
+ varCorrelation = 1.0;
+
if (varCorrelation > *indexCorrelation)
*indexCorrelation = varCorrelation;
diff --git a/src/include/access/brin_internal.h b/src/include/access/brin_internal.h
index ee4d0706df..67aea62a02 100644
--- a/src/include/access/brin_internal.h
+++ b/src/include/access/brin_internal.h
@@ -30,6 +30,9 @@ typedef struct BrinOpcInfo
/* Regular processing of NULLs in BrinValues? */
bool oi_regular_nulls;
+ /* Ignore correlation during cost estimation */
+ bool oi_ignore_correlation;
+
/* Opaque pointer for the opclass' private use */
void *oi_opaque;
--
2.25.4
--jsivyprvf2oxfjz3--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH 8/8] tweak costing for bloom/minmax-multi indexes
@ 2020-08-07 13:53 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Tomas Vondra @ 2020-08-07 13:53 UTC (permalink / raw)
---
src/backend/access/brin/brin_bloom.c | 1 +
src/backend/access/brin/brin_minmax_multi.c | 1 +
src/backend/utils/adt/selfuncs.c | 19 ++++++++++++++++++-
src/include/access/brin_internal.h | 3 +++
4 files changed, 23 insertions(+), 1 deletion(-)
diff --git a/src/backend/access/brin/brin_bloom.c b/src/backend/access/brin/brin_bloom.c
index e7ca100821..f64381b657 100644
--- a/src/backend/access/brin/brin_bloom.c
+++ b/src/backend/access/brin/brin_bloom.c
@@ -643,6 +643,7 @@ brin_bloom_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(BloomOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (BloomOpaque *)
diff --git a/src/backend/access/brin/brin_minmax_multi.c b/src/backend/access/brin/brin_minmax_multi.c
index f700700d46..5f637e0629 100644
--- a/src/backend/access/brin/brin_minmax_multi.c
+++ b/src/backend/access/brin/brin_minmax_multi.c
@@ -1301,6 +1301,7 @@ brin_minmax_multi_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(MinmaxMultiOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (MinmaxMultiOpaque *)
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 00c7afc66f..c00265a66d 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -98,6 +98,7 @@
#include <math.h>
#include "access/brin.h"
+#include "access/brin_internal.h"
#include "access/brin_page.h"
#include "access/gin.h"
#include "access/table.h"
@@ -7350,7 +7351,8 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
double minimalRanges;
double estimatedRanges;
double selec;
- Relation indexRel;
+ Relation indexRel = NULL;
+ TupleDesc tupdesc = NULL;
ListCell *l;
VariableStatData vardata;
@@ -7372,6 +7374,7 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
*/
indexRel = index_open(index->indexoid, NoLock);
brinGetStats(indexRel, &statsData);
+ tupdesc = RelationGetDescr(indexRel);
index_close(indexRel, NoLock);
/* work out the actual number of ranges in the index */
@@ -7405,6 +7408,17 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
{
IndexClause *iclause = lfirst_node(IndexClause, l);
AttrNumber attnum = index->indexkeys[iclause->indexcol];
+ FmgrInfo *opcInfoFn;
+ BrinOpcInfo *opcInfo;
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, iclause->indexcol);
+ bool ignore_correlation;
+
+ opcInfoFn = index_getprocinfo(indexRel, iclause->indexcol + 1, BRIN_PROCNUM_OPCINFO);
+
+ opcInfo = (BrinOpcInfo *)
+ DatumGetPointer(FunctionCall1(opcInfoFn, attr->atttypid));
+
+ ignore_correlation = opcInfo->oi_ignore_correlation;
/* attempt to lookup stats in relation for this index column */
if (attnum != 0)
@@ -7475,6 +7489,9 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
if (sslot.nnumbers > 0)
varCorrelation = Abs(sslot.numbers[0]);
+ if (ignore_correlation)
+ varCorrelation = 1.0;
+
if (varCorrelation > *indexCorrelation)
*indexCorrelation = varCorrelation;
diff --git a/src/include/access/brin_internal.h b/src/include/access/brin_internal.h
index ee4d0706df..67aea62a02 100644
--- a/src/include/access/brin_internal.h
+++ b/src/include/access/brin_internal.h
@@ -30,6 +30,9 @@ typedef struct BrinOpcInfo
/* Regular processing of NULLs in BrinValues? */
bool oi_regular_nulls;
+ /* Ignore correlation during cost estimation */
+ bool oi_ignore_correlation;
+
/* Opaque pointer for the opclass' private use */
void *oi_opaque;
--
2.25.4
--vurodj3csurzcvv3--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH 8/8] tweak costing for bloom/minmax-multi indexes
@ 2020-08-07 13:53 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Tomas Vondra @ 2020-08-07 13:53 UTC (permalink / raw)
---
src/backend/access/brin/brin_bloom.c | 1 +
src/backend/access/brin/brin_minmax_multi.c | 1 +
src/backend/utils/adt/selfuncs.c | 19 ++++++++++++++++++-
src/include/access/brin_internal.h | 3 +++
4 files changed, 23 insertions(+), 1 deletion(-)
diff --git a/src/backend/access/brin/brin_bloom.c b/src/backend/access/brin/brin_bloom.c
index e7ca100821..f64381b657 100644
--- a/src/backend/access/brin/brin_bloom.c
+++ b/src/backend/access/brin/brin_bloom.c
@@ -643,6 +643,7 @@ brin_bloom_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(BloomOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (BloomOpaque *)
diff --git a/src/backend/access/brin/brin_minmax_multi.c b/src/backend/access/brin/brin_minmax_multi.c
index f700700d46..5f637e0629 100644
--- a/src/backend/access/brin/brin_minmax_multi.c
+++ b/src/backend/access/brin/brin_minmax_multi.c
@@ -1301,6 +1301,7 @@ brin_minmax_multi_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(MinmaxMultiOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (MinmaxMultiOpaque *)
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 53d974125f..956d6f0a84 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -98,6 +98,7 @@
#include <math.h>
#include "access/brin.h"
+#include "access/brin_internal.h"
#include "access/brin_page.h"
#include "access/gin.h"
#include "access/table.h"
@@ -7348,7 +7349,8 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
double minimalRanges;
double estimatedRanges;
double selec;
- Relation indexRel;
+ Relation indexRel = NULL;
+ TupleDesc tupdesc = NULL;
ListCell *l;
VariableStatData vardata;
@@ -7370,6 +7372,7 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
*/
indexRel = index_open(index->indexoid, NoLock);
brinGetStats(indexRel, &statsData);
+ tupdesc = RelationGetDescr(indexRel);
index_close(indexRel, NoLock);
/* work out the actual number of ranges in the index */
@@ -7403,6 +7406,17 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
{
IndexClause *iclause = lfirst_node(IndexClause, l);
AttrNumber attnum = index->indexkeys[iclause->indexcol];
+ FmgrInfo *opcInfoFn;
+ BrinOpcInfo *opcInfo;
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, iclause->indexcol);
+ bool ignore_correlation;
+
+ opcInfoFn = index_getprocinfo(indexRel, iclause->indexcol + 1, BRIN_PROCNUM_OPCINFO);
+
+ opcInfo = (BrinOpcInfo *)
+ DatumGetPointer(FunctionCall1(opcInfoFn, attr->atttypid));
+
+ ignore_correlation = opcInfo->oi_ignore_correlation;
/* attempt to lookup stats in relation for this index column */
if (attnum != 0)
@@ -7473,6 +7487,9 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
if (sslot.nnumbers > 0)
varCorrelation = Abs(sslot.numbers[0]);
+ if (ignore_correlation)
+ varCorrelation = 1.0;
+
if (varCorrelation > *indexCorrelation)
*indexCorrelation = varCorrelation;
diff --git a/src/include/access/brin_internal.h b/src/include/access/brin_internal.h
index ee4d0706df..67aea62a02 100644
--- a/src/include/access/brin_internal.h
+++ b/src/include/access/brin_internal.h
@@ -30,6 +30,9 @@ typedef struct BrinOpcInfo
/* Regular processing of NULLs in BrinValues? */
bool oi_regular_nulls;
+ /* Ignore correlation during cost estimation */
+ bool oi_ignore_correlation;
+
/* Opaque pointer for the opclass' private use */
void *oi_opaque;
--
2.25.4
--jsivyprvf2oxfjz3--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH 08/10] tweak costing for bloom/minmax-multi indexes
@ 2020-08-07 13:53 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Tomas Vondra @ 2020-08-07 13:53 UTC (permalink / raw)
---
src/backend/access/brin/brin_bloom.c | 1 +
src/backend/access/brin/brin_minmax_multi.c | 1 +
src/backend/utils/adt/selfuncs.c | 19 ++++++++++++++++++-
src/include/access/brin_internal.h | 3 +++
4 files changed, 23 insertions(+), 1 deletion(-)
diff --git a/src/backend/access/brin/brin_bloom.c b/src/backend/access/brin/brin_bloom.c
index e7ca100821..f64381b657 100644
--- a/src/backend/access/brin/brin_bloom.c
+++ b/src/backend/access/brin/brin_bloom.c
@@ -643,6 +643,7 @@ brin_bloom_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(BloomOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (BloomOpaque *)
diff --git a/src/backend/access/brin/brin_minmax_multi.c b/src/backend/access/brin/brin_minmax_multi.c
index 1a1ff47223..23a8751b1f 100644
--- a/src/backend/access/brin/brin_minmax_multi.c
+++ b/src/backend/access/brin/brin_minmax_multi.c
@@ -1295,6 +1295,7 @@ brin_minmax_multi_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(MinmaxMultiOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (MinmaxMultiOpaque *)
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 00c7afc66f..c00265a66d 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -98,6 +98,7 @@
#include <math.h>
#include "access/brin.h"
+#include "access/brin_internal.h"
#include "access/brin_page.h"
#include "access/gin.h"
#include "access/table.h"
@@ -7350,7 +7351,8 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
double minimalRanges;
double estimatedRanges;
double selec;
- Relation indexRel;
+ Relation indexRel = NULL;
+ TupleDesc tupdesc = NULL;
ListCell *l;
VariableStatData vardata;
@@ -7372,6 +7374,7 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
*/
indexRel = index_open(index->indexoid, NoLock);
brinGetStats(indexRel, &statsData);
+ tupdesc = RelationGetDescr(indexRel);
index_close(indexRel, NoLock);
/* work out the actual number of ranges in the index */
@@ -7405,6 +7408,17 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
{
IndexClause *iclause = lfirst_node(IndexClause, l);
AttrNumber attnum = index->indexkeys[iclause->indexcol];
+ FmgrInfo *opcInfoFn;
+ BrinOpcInfo *opcInfo;
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, iclause->indexcol);
+ bool ignore_correlation;
+
+ opcInfoFn = index_getprocinfo(indexRel, iclause->indexcol + 1, BRIN_PROCNUM_OPCINFO);
+
+ opcInfo = (BrinOpcInfo *)
+ DatumGetPointer(FunctionCall1(opcInfoFn, attr->atttypid));
+
+ ignore_correlation = opcInfo->oi_ignore_correlation;
/* attempt to lookup stats in relation for this index column */
if (attnum != 0)
@@ -7475,6 +7489,9 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
if (sslot.nnumbers > 0)
varCorrelation = Abs(sslot.numbers[0]);
+ if (ignore_correlation)
+ varCorrelation = 1.0;
+
if (varCorrelation > *indexCorrelation)
*indexCorrelation = varCorrelation;
diff --git a/src/include/access/brin_internal.h b/src/include/access/brin_internal.h
index ee4d0706df..67aea62a02 100644
--- a/src/include/access/brin_internal.h
+++ b/src/include/access/brin_internal.h
@@ -30,6 +30,9 @@ typedef struct BrinOpcInfo
/* Regular processing of NULLs in BrinValues? */
bool oi_regular_nulls;
+ /* Ignore correlation during cost estimation */
+ bool oi_ignore_correlation;
+
/* Opaque pointer for the opclass' private use */
void *oi_opaque;
--
2.25.4
--6k3bcoookz7x4sns
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment;
filename="0009-WIP-batch-build-20200911.patch"
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH 8/8] tweak costing for bloom/minmax-multi indexes
@ 2020-08-07 13:53 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Tomas Vondra @ 2020-08-07 13:53 UTC (permalink / raw)
---
src/backend/access/brin/brin_bloom.c | 1 +
src/backend/access/brin/brin_minmax_multi.c | 1 +
src/backend/utils/adt/selfuncs.c | 19 ++++++++++++++++++-
src/include/access/brin_internal.h | 3 +++
4 files changed, 23 insertions(+), 1 deletion(-)
diff --git a/src/backend/access/brin/brin_bloom.c b/src/backend/access/brin/brin_bloom.c
index e7ca100821..f64381b657 100644
--- a/src/backend/access/brin/brin_bloom.c
+++ b/src/backend/access/brin/brin_bloom.c
@@ -643,6 +643,7 @@ brin_bloom_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(BloomOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (BloomOpaque *)
diff --git a/src/backend/access/brin/brin_minmax_multi.c b/src/backend/access/brin/brin_minmax_multi.c
index f700700d46..5f637e0629 100644
--- a/src/backend/access/brin/brin_minmax_multi.c
+++ b/src/backend/access/brin/brin_minmax_multi.c
@@ -1301,6 +1301,7 @@ brin_minmax_multi_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(MinmaxMultiOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (MinmaxMultiOpaque *)
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 53d974125f..956d6f0a84 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -98,6 +98,7 @@
#include <math.h>
#include "access/brin.h"
+#include "access/brin_internal.h"
#include "access/brin_page.h"
#include "access/gin.h"
#include "access/table.h"
@@ -7348,7 +7349,8 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
double minimalRanges;
double estimatedRanges;
double selec;
- Relation indexRel;
+ Relation indexRel = NULL;
+ TupleDesc tupdesc = NULL;
ListCell *l;
VariableStatData vardata;
@@ -7370,6 +7372,7 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
*/
indexRel = index_open(index->indexoid, NoLock);
brinGetStats(indexRel, &statsData);
+ tupdesc = RelationGetDescr(indexRel);
index_close(indexRel, NoLock);
/* work out the actual number of ranges in the index */
@@ -7403,6 +7406,17 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
{
IndexClause *iclause = lfirst_node(IndexClause, l);
AttrNumber attnum = index->indexkeys[iclause->indexcol];
+ FmgrInfo *opcInfoFn;
+ BrinOpcInfo *opcInfo;
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, iclause->indexcol);
+ bool ignore_correlation;
+
+ opcInfoFn = index_getprocinfo(indexRel, iclause->indexcol + 1, BRIN_PROCNUM_OPCINFO);
+
+ opcInfo = (BrinOpcInfo *)
+ DatumGetPointer(FunctionCall1(opcInfoFn, attr->atttypid));
+
+ ignore_correlation = opcInfo->oi_ignore_correlation;
/* attempt to lookup stats in relation for this index column */
if (attnum != 0)
@@ -7473,6 +7487,9 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
if (sslot.nnumbers > 0)
varCorrelation = Abs(sslot.numbers[0]);
+ if (ignore_correlation)
+ varCorrelation = 1.0;
+
if (varCorrelation > *indexCorrelation)
*indexCorrelation = varCorrelation;
diff --git a/src/include/access/brin_internal.h b/src/include/access/brin_internal.h
index ee4d0706df..67aea62a02 100644
--- a/src/include/access/brin_internal.h
+++ b/src/include/access/brin_internal.h
@@ -30,6 +30,9 @@ typedef struct BrinOpcInfo
/* Regular processing of NULLs in BrinValues? */
bool oi_regular_nulls;
+ /* Ignore correlation during cost estimation */
+ bool oi_ignore_correlation;
+
/* Opaque pointer for the opclass' private use */
void *oi_opaque;
--
2.25.4
--jsivyprvf2oxfjz3--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH 8/8] tweak costing for bloom/minmax-multi indexes
@ 2020-08-07 13:53 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Tomas Vondra @ 2020-08-07 13:53 UTC (permalink / raw)
---
src/backend/access/brin/brin_bloom.c | 1 +
src/backend/access/brin/brin_minmax_multi.c | 1 +
src/backend/utils/adt/selfuncs.c | 19 ++++++++++++++++++-
src/include/access/brin_internal.h | 3 +++
4 files changed, 23 insertions(+), 1 deletion(-)
diff --git a/src/backend/access/brin/brin_bloom.c b/src/backend/access/brin/brin_bloom.c
index e7ca100821..f64381b657 100644
--- a/src/backend/access/brin/brin_bloom.c
+++ b/src/backend/access/brin/brin_bloom.c
@@ -643,6 +643,7 @@ brin_bloom_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(BloomOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (BloomOpaque *)
diff --git a/src/backend/access/brin/brin_minmax_multi.c b/src/backend/access/brin/brin_minmax_multi.c
index f700700d46..5f637e0629 100644
--- a/src/backend/access/brin/brin_minmax_multi.c
+++ b/src/backend/access/brin/brin_minmax_multi.c
@@ -1301,6 +1301,7 @@ brin_minmax_multi_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(MinmaxMultiOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (MinmaxMultiOpaque *)
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 53d974125f..956d6f0a84 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -98,6 +98,7 @@
#include <math.h>
#include "access/brin.h"
+#include "access/brin_internal.h"
#include "access/brin_page.h"
#include "access/gin.h"
#include "access/table.h"
@@ -7348,7 +7349,8 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
double minimalRanges;
double estimatedRanges;
double selec;
- Relation indexRel;
+ Relation indexRel = NULL;
+ TupleDesc tupdesc = NULL;
ListCell *l;
VariableStatData vardata;
@@ -7370,6 +7372,7 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
*/
indexRel = index_open(index->indexoid, NoLock);
brinGetStats(indexRel, &statsData);
+ tupdesc = RelationGetDescr(indexRel);
index_close(indexRel, NoLock);
/* work out the actual number of ranges in the index */
@@ -7403,6 +7406,17 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
{
IndexClause *iclause = lfirst_node(IndexClause, l);
AttrNumber attnum = index->indexkeys[iclause->indexcol];
+ FmgrInfo *opcInfoFn;
+ BrinOpcInfo *opcInfo;
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, iclause->indexcol);
+ bool ignore_correlation;
+
+ opcInfoFn = index_getprocinfo(indexRel, iclause->indexcol + 1, BRIN_PROCNUM_OPCINFO);
+
+ opcInfo = (BrinOpcInfo *)
+ DatumGetPointer(FunctionCall1(opcInfoFn, attr->atttypid));
+
+ ignore_correlation = opcInfo->oi_ignore_correlation;
/* attempt to lookup stats in relation for this index column */
if (attnum != 0)
@@ -7473,6 +7487,9 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
if (sslot.nnumbers > 0)
varCorrelation = Abs(sslot.numbers[0]);
+ if (ignore_correlation)
+ varCorrelation = 1.0;
+
if (varCorrelation > *indexCorrelation)
*indexCorrelation = varCorrelation;
diff --git a/src/include/access/brin_internal.h b/src/include/access/brin_internal.h
index ee4d0706df..67aea62a02 100644
--- a/src/include/access/brin_internal.h
+++ b/src/include/access/brin_internal.h
@@ -30,6 +30,9 @@ typedef struct BrinOpcInfo
/* Regular processing of NULLs in BrinValues? */
bool oi_regular_nulls;
+ /* Ignore correlation during cost estimation */
+ bool oi_ignore_correlation;
+
/* Opaque pointer for the opclass' private use */
void *oi_opaque;
--
2.25.4
--jsivyprvf2oxfjz3--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH 8/8] tweak costing for bloom/minmax-multi indexes
@ 2020-08-07 13:53 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Tomas Vondra @ 2020-08-07 13:53 UTC (permalink / raw)
---
src/backend/access/brin/brin_bloom.c | 1 +
src/backend/access/brin/brin_minmax_multi.c | 1 +
src/backend/utils/adt/selfuncs.c | 19 ++++++++++++++++++-
src/include/access/brin_internal.h | 3 +++
4 files changed, 23 insertions(+), 1 deletion(-)
diff --git a/src/backend/access/brin/brin_bloom.c b/src/backend/access/brin/brin_bloom.c
index e7ca100821..f64381b657 100644
--- a/src/backend/access/brin/brin_bloom.c
+++ b/src/backend/access/brin/brin_bloom.c
@@ -643,6 +643,7 @@ brin_bloom_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(BloomOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (BloomOpaque *)
diff --git a/src/backend/access/brin/brin_minmax_multi.c b/src/backend/access/brin/brin_minmax_multi.c
index f700700d46..5f637e0629 100644
--- a/src/backend/access/brin/brin_minmax_multi.c
+++ b/src/backend/access/brin/brin_minmax_multi.c
@@ -1301,6 +1301,7 @@ brin_minmax_multi_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(MinmaxMultiOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (MinmaxMultiOpaque *)
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 53d974125f..956d6f0a84 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -98,6 +98,7 @@
#include <math.h>
#include "access/brin.h"
+#include "access/brin_internal.h"
#include "access/brin_page.h"
#include "access/gin.h"
#include "access/table.h"
@@ -7348,7 +7349,8 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
double minimalRanges;
double estimatedRanges;
double selec;
- Relation indexRel;
+ Relation indexRel = NULL;
+ TupleDesc tupdesc = NULL;
ListCell *l;
VariableStatData vardata;
@@ -7370,6 +7372,7 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
*/
indexRel = index_open(index->indexoid, NoLock);
brinGetStats(indexRel, &statsData);
+ tupdesc = RelationGetDescr(indexRel);
index_close(indexRel, NoLock);
/* work out the actual number of ranges in the index */
@@ -7403,6 +7406,17 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
{
IndexClause *iclause = lfirst_node(IndexClause, l);
AttrNumber attnum = index->indexkeys[iclause->indexcol];
+ FmgrInfo *opcInfoFn;
+ BrinOpcInfo *opcInfo;
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, iclause->indexcol);
+ bool ignore_correlation;
+
+ opcInfoFn = index_getprocinfo(indexRel, iclause->indexcol + 1, BRIN_PROCNUM_OPCINFO);
+
+ opcInfo = (BrinOpcInfo *)
+ DatumGetPointer(FunctionCall1(opcInfoFn, attr->atttypid));
+
+ ignore_correlation = opcInfo->oi_ignore_correlation;
/* attempt to lookup stats in relation for this index column */
if (attnum != 0)
@@ -7473,6 +7487,9 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
if (sslot.nnumbers > 0)
varCorrelation = Abs(sslot.numbers[0]);
+ if (ignore_correlation)
+ varCorrelation = 1.0;
+
if (varCorrelation > *indexCorrelation)
*indexCorrelation = varCorrelation;
diff --git a/src/include/access/brin_internal.h b/src/include/access/brin_internal.h
index ee4d0706df..67aea62a02 100644
--- a/src/include/access/brin_internal.h
+++ b/src/include/access/brin_internal.h
@@ -30,6 +30,9 @@ typedef struct BrinOpcInfo
/* Regular processing of NULLs in BrinValues? */
bool oi_regular_nulls;
+ /* Ignore correlation during cost estimation */
+ bool oi_ignore_correlation;
+
/* Opaque pointer for the opclass' private use */
void *oi_opaque;
--
2.25.4
--jsivyprvf2oxfjz3--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH 6/6] tweak costing for bloom/minmax-multi indexes
@ 2020-08-07 13:53 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Tomas Vondra @ 2020-08-07 13:53 UTC (permalink / raw)
---
src/backend/access/brin/brin_bloom.c | 1 +
src/backend/access/brin/brin_minmax_multi.c | 1 +
src/backend/utils/adt/selfuncs.c | 19 ++++++++++++++++++-
src/include/access/brin_internal.h | 3 +++
4 files changed, 23 insertions(+), 1 deletion(-)
diff --git a/src/backend/access/brin/brin_bloom.c b/src/backend/access/brin/brin_bloom.c
index c2cbbd9400..03e9d4b713 100644
--- a/src/backend/access/brin/brin_bloom.c
+++ b/src/backend/access/brin/brin_bloom.c
@@ -681,6 +681,7 @@ brin_bloom_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(BloomOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (BloomOpaque *)
diff --git a/src/backend/access/brin/brin_minmax_multi.c b/src/backend/access/brin/brin_minmax_multi.c
index 227927fcf5..c8f36b0c8a 100644
--- a/src/backend/access/brin/brin_minmax_multi.c
+++ b/src/backend/access/brin/brin_minmax_multi.c
@@ -1306,6 +1306,7 @@ brin_minmax_multi_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(MinmaxMultiOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (MinmaxMultiOpaque *)
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 00c7afc66f..c00265a66d 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -98,6 +98,7 @@
#include <math.h>
#include "access/brin.h"
+#include "access/brin_internal.h"
#include "access/brin_page.h"
#include "access/gin.h"
#include "access/table.h"
@@ -7350,7 +7351,8 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
double minimalRanges;
double estimatedRanges;
double selec;
- Relation indexRel;
+ Relation indexRel = NULL;
+ TupleDesc tupdesc = NULL;
ListCell *l;
VariableStatData vardata;
@@ -7372,6 +7374,7 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
*/
indexRel = index_open(index->indexoid, NoLock);
brinGetStats(indexRel, &statsData);
+ tupdesc = RelationGetDescr(indexRel);
index_close(indexRel, NoLock);
/* work out the actual number of ranges in the index */
@@ -7405,6 +7408,17 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
{
IndexClause *iclause = lfirst_node(IndexClause, l);
AttrNumber attnum = index->indexkeys[iclause->indexcol];
+ FmgrInfo *opcInfoFn;
+ BrinOpcInfo *opcInfo;
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, iclause->indexcol);
+ bool ignore_correlation;
+
+ opcInfoFn = index_getprocinfo(indexRel, iclause->indexcol + 1, BRIN_PROCNUM_OPCINFO);
+
+ opcInfo = (BrinOpcInfo *)
+ DatumGetPointer(FunctionCall1(opcInfoFn, attr->atttypid));
+
+ ignore_correlation = opcInfo->oi_ignore_correlation;
/* attempt to lookup stats in relation for this index column */
if (attnum != 0)
@@ -7475,6 +7489,9 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
if (sslot.nnumbers > 0)
varCorrelation = Abs(sslot.numbers[0]);
+ if (ignore_correlation)
+ varCorrelation = 1.0;
+
if (varCorrelation > *indexCorrelation)
*indexCorrelation = varCorrelation;
diff --git a/src/include/access/brin_internal.h b/src/include/access/brin_internal.h
index ee4d0706df..67aea62a02 100644
--- a/src/include/access/brin_internal.h
+++ b/src/include/access/brin_internal.h
@@ -30,6 +30,9 @@ typedef struct BrinOpcInfo
/* Regular processing of NULLs in BrinValues? */
bool oi_regular_nulls;
+ /* Ignore correlation during cost estimation */
+ bool oi_ignore_correlation;
+
/* Opaque pointer for the opclass' private use */
void *oi_opaque;
--
2.25.4
--2wz2lexbyaxcz4wc--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH 8/8] tweak costing for bloom/minmax-multi indexes
@ 2020-08-07 13:53 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Tomas Vondra @ 2020-08-07 13:53 UTC (permalink / raw)
---
src/backend/access/brin/brin_bloom.c | 1 +
src/backend/access/brin/brin_minmax_multi.c | 1 +
src/backend/utils/adt/selfuncs.c | 19 ++++++++++++++++++-
src/include/access/brin_internal.h | 3 +++
4 files changed, 23 insertions(+), 1 deletion(-)
diff --git a/src/backend/access/brin/brin_bloom.c b/src/backend/access/brin/brin_bloom.c
index e7ca100821..f64381b657 100644
--- a/src/backend/access/brin/brin_bloom.c
+++ b/src/backend/access/brin/brin_bloom.c
@@ -643,6 +643,7 @@ brin_bloom_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(BloomOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (BloomOpaque *)
diff --git a/src/backend/access/brin/brin_minmax_multi.c b/src/backend/access/brin/brin_minmax_multi.c
index f700700d46..5f637e0629 100644
--- a/src/backend/access/brin/brin_minmax_multi.c
+++ b/src/backend/access/brin/brin_minmax_multi.c
@@ -1301,6 +1301,7 @@ brin_minmax_multi_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(MinmaxMultiOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (MinmaxMultiOpaque *)
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 53d974125f..956d6f0a84 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -98,6 +98,7 @@
#include <math.h>
#include "access/brin.h"
+#include "access/brin_internal.h"
#include "access/brin_page.h"
#include "access/gin.h"
#include "access/table.h"
@@ -7348,7 +7349,8 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
double minimalRanges;
double estimatedRanges;
double selec;
- Relation indexRel;
+ Relation indexRel = NULL;
+ TupleDesc tupdesc = NULL;
ListCell *l;
VariableStatData vardata;
@@ -7370,6 +7372,7 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
*/
indexRel = index_open(index->indexoid, NoLock);
brinGetStats(indexRel, &statsData);
+ tupdesc = RelationGetDescr(indexRel);
index_close(indexRel, NoLock);
/* work out the actual number of ranges in the index */
@@ -7403,6 +7406,17 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
{
IndexClause *iclause = lfirst_node(IndexClause, l);
AttrNumber attnum = index->indexkeys[iclause->indexcol];
+ FmgrInfo *opcInfoFn;
+ BrinOpcInfo *opcInfo;
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, iclause->indexcol);
+ bool ignore_correlation;
+
+ opcInfoFn = index_getprocinfo(indexRel, iclause->indexcol + 1, BRIN_PROCNUM_OPCINFO);
+
+ opcInfo = (BrinOpcInfo *)
+ DatumGetPointer(FunctionCall1(opcInfoFn, attr->atttypid));
+
+ ignore_correlation = opcInfo->oi_ignore_correlation;
/* attempt to lookup stats in relation for this index column */
if (attnum != 0)
@@ -7473,6 +7487,9 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
if (sslot.nnumbers > 0)
varCorrelation = Abs(sslot.numbers[0]);
+ if (ignore_correlation)
+ varCorrelation = 1.0;
+
if (varCorrelation > *indexCorrelation)
*indexCorrelation = varCorrelation;
diff --git a/src/include/access/brin_internal.h b/src/include/access/brin_internal.h
index ee4d0706df..67aea62a02 100644
--- a/src/include/access/brin_internal.h
+++ b/src/include/access/brin_internal.h
@@ -30,6 +30,9 @@ typedef struct BrinOpcInfo
/* Regular processing of NULLs in BrinValues? */
bool oi_regular_nulls;
+ /* Ignore correlation during cost estimation */
+ bool oi_ignore_correlation;
+
/* Opaque pointer for the opclass' private use */
void *oi_opaque;
--
2.25.4
--jsivyprvf2oxfjz3--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH 8/8] tweak costing for bloom/minmax-multi indexes
@ 2020-08-07 13:53 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Tomas Vondra @ 2020-08-07 13:53 UTC (permalink / raw)
---
src/backend/access/brin/brin_bloom.c | 1 +
src/backend/access/brin/brin_minmax_multi.c | 1 +
src/backend/utils/adt/selfuncs.c | 19 ++++++++++++++++++-
src/include/access/brin_internal.h | 3 +++
4 files changed, 23 insertions(+), 1 deletion(-)
diff --git a/src/backend/access/brin/brin_bloom.c b/src/backend/access/brin/brin_bloom.c
index e7ca100821..f64381b657 100644
--- a/src/backend/access/brin/brin_bloom.c
+++ b/src/backend/access/brin/brin_bloom.c
@@ -643,6 +643,7 @@ brin_bloom_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(BloomOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (BloomOpaque *)
diff --git a/src/backend/access/brin/brin_minmax_multi.c b/src/backend/access/brin/brin_minmax_multi.c
index f700700d46..5f637e0629 100644
--- a/src/backend/access/brin/brin_minmax_multi.c
+++ b/src/backend/access/brin/brin_minmax_multi.c
@@ -1301,6 +1301,7 @@ brin_minmax_multi_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(MinmaxMultiOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (MinmaxMultiOpaque *)
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 53d974125f..956d6f0a84 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -98,6 +98,7 @@
#include <math.h>
#include "access/brin.h"
+#include "access/brin_internal.h"
#include "access/brin_page.h"
#include "access/gin.h"
#include "access/table.h"
@@ -7348,7 +7349,8 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
double minimalRanges;
double estimatedRanges;
double selec;
- Relation indexRel;
+ Relation indexRel = NULL;
+ TupleDesc tupdesc = NULL;
ListCell *l;
VariableStatData vardata;
@@ -7370,6 +7372,7 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
*/
indexRel = index_open(index->indexoid, NoLock);
brinGetStats(indexRel, &statsData);
+ tupdesc = RelationGetDescr(indexRel);
index_close(indexRel, NoLock);
/* work out the actual number of ranges in the index */
@@ -7403,6 +7406,17 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
{
IndexClause *iclause = lfirst_node(IndexClause, l);
AttrNumber attnum = index->indexkeys[iclause->indexcol];
+ FmgrInfo *opcInfoFn;
+ BrinOpcInfo *opcInfo;
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, iclause->indexcol);
+ bool ignore_correlation;
+
+ opcInfoFn = index_getprocinfo(indexRel, iclause->indexcol + 1, BRIN_PROCNUM_OPCINFO);
+
+ opcInfo = (BrinOpcInfo *)
+ DatumGetPointer(FunctionCall1(opcInfoFn, attr->atttypid));
+
+ ignore_correlation = opcInfo->oi_ignore_correlation;
/* attempt to lookup stats in relation for this index column */
if (attnum != 0)
@@ -7473,6 +7487,9 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
if (sslot.nnumbers > 0)
varCorrelation = Abs(sslot.numbers[0]);
+ if (ignore_correlation)
+ varCorrelation = 1.0;
+
if (varCorrelation > *indexCorrelation)
*indexCorrelation = varCorrelation;
diff --git a/src/include/access/brin_internal.h b/src/include/access/brin_internal.h
index ee4d0706df..67aea62a02 100644
--- a/src/include/access/brin_internal.h
+++ b/src/include/access/brin_internal.h
@@ -30,6 +30,9 @@ typedef struct BrinOpcInfo
/* Regular processing of NULLs in BrinValues? */
bool oi_regular_nulls;
+ /* Ignore correlation during cost estimation */
+ bool oi_ignore_correlation;
+
/* Opaque pointer for the opclass' private use */
void *oi_opaque;
--
2.25.4
--jsivyprvf2oxfjz3--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH 8/8] tweak costing for bloom/minmax-multi indexes
@ 2020-08-07 13:53 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Tomas Vondra @ 2020-08-07 13:53 UTC (permalink / raw)
---
src/backend/access/brin/brin_bloom.c | 1 +
src/backend/access/brin/brin_minmax_multi.c | 1 +
src/backend/utils/adt/selfuncs.c | 19 ++++++++++++++++++-
src/include/access/brin_internal.h | 3 +++
4 files changed, 23 insertions(+), 1 deletion(-)
diff --git a/src/backend/access/brin/brin_bloom.c b/src/backend/access/brin/brin_bloom.c
index e7ca100821..f64381b657 100644
--- a/src/backend/access/brin/brin_bloom.c
+++ b/src/backend/access/brin/brin_bloom.c
@@ -643,6 +643,7 @@ brin_bloom_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(BloomOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (BloomOpaque *)
diff --git a/src/backend/access/brin/brin_minmax_multi.c b/src/backend/access/brin/brin_minmax_multi.c
index f700700d46..5f637e0629 100644
--- a/src/backend/access/brin/brin_minmax_multi.c
+++ b/src/backend/access/brin/brin_minmax_multi.c
@@ -1301,6 +1301,7 @@ brin_minmax_multi_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(MinmaxMultiOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (MinmaxMultiOpaque *)
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 53d974125f..956d6f0a84 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -98,6 +98,7 @@
#include <math.h>
#include "access/brin.h"
+#include "access/brin_internal.h"
#include "access/brin_page.h"
#include "access/gin.h"
#include "access/table.h"
@@ -7348,7 +7349,8 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
double minimalRanges;
double estimatedRanges;
double selec;
- Relation indexRel;
+ Relation indexRel = NULL;
+ TupleDesc tupdesc = NULL;
ListCell *l;
VariableStatData vardata;
@@ -7370,6 +7372,7 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
*/
indexRel = index_open(index->indexoid, NoLock);
brinGetStats(indexRel, &statsData);
+ tupdesc = RelationGetDescr(indexRel);
index_close(indexRel, NoLock);
/* work out the actual number of ranges in the index */
@@ -7403,6 +7406,17 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
{
IndexClause *iclause = lfirst_node(IndexClause, l);
AttrNumber attnum = index->indexkeys[iclause->indexcol];
+ FmgrInfo *opcInfoFn;
+ BrinOpcInfo *opcInfo;
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, iclause->indexcol);
+ bool ignore_correlation;
+
+ opcInfoFn = index_getprocinfo(indexRel, iclause->indexcol + 1, BRIN_PROCNUM_OPCINFO);
+
+ opcInfo = (BrinOpcInfo *)
+ DatumGetPointer(FunctionCall1(opcInfoFn, attr->atttypid));
+
+ ignore_correlation = opcInfo->oi_ignore_correlation;
/* attempt to lookup stats in relation for this index column */
if (attnum != 0)
@@ -7473,6 +7487,9 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
if (sslot.nnumbers > 0)
varCorrelation = Abs(sslot.numbers[0]);
+ if (ignore_correlation)
+ varCorrelation = 1.0;
+
if (varCorrelation > *indexCorrelation)
*indexCorrelation = varCorrelation;
diff --git a/src/include/access/brin_internal.h b/src/include/access/brin_internal.h
index ee4d0706df..67aea62a02 100644
--- a/src/include/access/brin_internal.h
+++ b/src/include/access/brin_internal.h
@@ -30,6 +30,9 @@ typedef struct BrinOpcInfo
/* Regular processing of NULLs in BrinValues? */
bool oi_regular_nulls;
+ /* Ignore correlation during cost estimation */
+ bool oi_ignore_correlation;
+
/* Opaque pointer for the opclass' private use */
void *oi_opaque;
--
2.25.4
--jsivyprvf2oxfjz3--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH 8/8] tweak costing for bloom/minmax-multi indexes
@ 2020-08-07 13:53 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Tomas Vondra @ 2020-08-07 13:53 UTC (permalink / raw)
---
src/backend/access/brin/brin_bloom.c | 1 +
src/backend/access/brin/brin_minmax_multi.c | 1 +
src/backend/utils/adt/selfuncs.c | 19 ++++++++++++++++++-
src/include/access/brin_internal.h | 3 +++
4 files changed, 23 insertions(+), 1 deletion(-)
diff --git a/src/backend/access/brin/brin_bloom.c b/src/backend/access/brin/brin_bloom.c
index e7ca100821..f64381b657 100644
--- a/src/backend/access/brin/brin_bloom.c
+++ b/src/backend/access/brin/brin_bloom.c
@@ -643,6 +643,7 @@ brin_bloom_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(BloomOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (BloomOpaque *)
diff --git a/src/backend/access/brin/brin_minmax_multi.c b/src/backend/access/brin/brin_minmax_multi.c
index f700700d46..5f637e0629 100644
--- a/src/backend/access/brin/brin_minmax_multi.c
+++ b/src/backend/access/brin/brin_minmax_multi.c
@@ -1301,6 +1301,7 @@ brin_minmax_multi_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(MinmaxMultiOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (MinmaxMultiOpaque *)
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 53d974125f..956d6f0a84 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -98,6 +98,7 @@
#include <math.h>
#include "access/brin.h"
+#include "access/brin_internal.h"
#include "access/brin_page.h"
#include "access/gin.h"
#include "access/table.h"
@@ -7348,7 +7349,8 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
double minimalRanges;
double estimatedRanges;
double selec;
- Relation indexRel;
+ Relation indexRel = NULL;
+ TupleDesc tupdesc = NULL;
ListCell *l;
VariableStatData vardata;
@@ -7370,6 +7372,7 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
*/
indexRel = index_open(index->indexoid, NoLock);
brinGetStats(indexRel, &statsData);
+ tupdesc = RelationGetDescr(indexRel);
index_close(indexRel, NoLock);
/* work out the actual number of ranges in the index */
@@ -7403,6 +7406,17 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
{
IndexClause *iclause = lfirst_node(IndexClause, l);
AttrNumber attnum = index->indexkeys[iclause->indexcol];
+ FmgrInfo *opcInfoFn;
+ BrinOpcInfo *opcInfo;
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, iclause->indexcol);
+ bool ignore_correlation;
+
+ opcInfoFn = index_getprocinfo(indexRel, iclause->indexcol + 1, BRIN_PROCNUM_OPCINFO);
+
+ opcInfo = (BrinOpcInfo *)
+ DatumGetPointer(FunctionCall1(opcInfoFn, attr->atttypid));
+
+ ignore_correlation = opcInfo->oi_ignore_correlation;
/* attempt to lookup stats in relation for this index column */
if (attnum != 0)
@@ -7473,6 +7487,9 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
if (sslot.nnumbers > 0)
varCorrelation = Abs(sslot.numbers[0]);
+ if (ignore_correlation)
+ varCorrelation = 1.0;
+
if (varCorrelation > *indexCorrelation)
*indexCorrelation = varCorrelation;
diff --git a/src/include/access/brin_internal.h b/src/include/access/brin_internal.h
index ee4d0706df..67aea62a02 100644
--- a/src/include/access/brin_internal.h
+++ b/src/include/access/brin_internal.h
@@ -30,6 +30,9 @@ typedef struct BrinOpcInfo
/* Regular processing of NULLs in BrinValues? */
bool oi_regular_nulls;
+ /* Ignore correlation during cost estimation */
+ bool oi_ignore_correlation;
+
/* Opaque pointer for the opclass' private use */
void *oi_opaque;
--
2.25.4
--jsivyprvf2oxfjz3--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH 8/8] tweak costing for bloom/minmax-multi indexes
@ 2020-08-07 13:53 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Tomas Vondra @ 2020-08-07 13:53 UTC (permalink / raw)
---
src/backend/access/brin/brin_bloom.c | 1 +
src/backend/access/brin/brin_minmax_multi.c | 1 +
src/backend/utils/adt/selfuncs.c | 19 ++++++++++++++++++-
src/include/access/brin_internal.h | 3 +++
4 files changed, 23 insertions(+), 1 deletion(-)
diff --git a/src/backend/access/brin/brin_bloom.c b/src/backend/access/brin/brin_bloom.c
index e7ca100821..f64381b657 100644
--- a/src/backend/access/brin/brin_bloom.c
+++ b/src/backend/access/brin/brin_bloom.c
@@ -643,6 +643,7 @@ brin_bloom_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(BloomOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (BloomOpaque *)
diff --git a/src/backend/access/brin/brin_minmax_multi.c b/src/backend/access/brin/brin_minmax_multi.c
index f700700d46..5f637e0629 100644
--- a/src/backend/access/brin/brin_minmax_multi.c
+++ b/src/backend/access/brin/brin_minmax_multi.c
@@ -1301,6 +1301,7 @@ brin_minmax_multi_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(MinmaxMultiOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (MinmaxMultiOpaque *)
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 53d974125f..956d6f0a84 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -98,6 +98,7 @@
#include <math.h>
#include "access/brin.h"
+#include "access/brin_internal.h"
#include "access/brin_page.h"
#include "access/gin.h"
#include "access/table.h"
@@ -7348,7 +7349,8 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
double minimalRanges;
double estimatedRanges;
double selec;
- Relation indexRel;
+ Relation indexRel = NULL;
+ TupleDesc tupdesc = NULL;
ListCell *l;
VariableStatData vardata;
@@ -7370,6 +7372,7 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
*/
indexRel = index_open(index->indexoid, NoLock);
brinGetStats(indexRel, &statsData);
+ tupdesc = RelationGetDescr(indexRel);
index_close(indexRel, NoLock);
/* work out the actual number of ranges in the index */
@@ -7403,6 +7406,17 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
{
IndexClause *iclause = lfirst_node(IndexClause, l);
AttrNumber attnum = index->indexkeys[iclause->indexcol];
+ FmgrInfo *opcInfoFn;
+ BrinOpcInfo *opcInfo;
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, iclause->indexcol);
+ bool ignore_correlation;
+
+ opcInfoFn = index_getprocinfo(indexRel, iclause->indexcol + 1, BRIN_PROCNUM_OPCINFO);
+
+ opcInfo = (BrinOpcInfo *)
+ DatumGetPointer(FunctionCall1(opcInfoFn, attr->atttypid));
+
+ ignore_correlation = opcInfo->oi_ignore_correlation;
/* attempt to lookup stats in relation for this index column */
if (attnum != 0)
@@ -7473,6 +7487,9 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
if (sslot.nnumbers > 0)
varCorrelation = Abs(sslot.numbers[0]);
+ if (ignore_correlation)
+ varCorrelation = 1.0;
+
if (varCorrelation > *indexCorrelation)
*indexCorrelation = varCorrelation;
diff --git a/src/include/access/brin_internal.h b/src/include/access/brin_internal.h
index ee4d0706df..67aea62a02 100644
--- a/src/include/access/brin_internal.h
+++ b/src/include/access/brin_internal.h
@@ -30,6 +30,9 @@ typedef struct BrinOpcInfo
/* Regular processing of NULLs in BrinValues? */
bool oi_regular_nulls;
+ /* Ignore correlation during cost estimation */
+ bool oi_ignore_correlation;
+
/* Opaque pointer for the opclass' private use */
void *oi_opaque;
--
2.25.4
--jsivyprvf2oxfjz3--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH 8/8] tweak costing for bloom/minmax-multi indexes
@ 2020-08-07 13:53 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Tomas Vondra @ 2020-08-07 13:53 UTC (permalink / raw)
---
src/backend/access/brin/brin_bloom.c | 1 +
src/backend/access/brin/brin_minmax_multi.c | 1 +
src/backend/utils/adt/selfuncs.c | 19 ++++++++++++++++++-
src/include/access/brin_internal.h | 3 +++
4 files changed, 23 insertions(+), 1 deletion(-)
diff --git a/src/backend/access/brin/brin_bloom.c b/src/backend/access/brin/brin_bloom.c
index e7ca100821..f64381b657 100644
--- a/src/backend/access/brin/brin_bloom.c
+++ b/src/backend/access/brin/brin_bloom.c
@@ -643,6 +643,7 @@ brin_bloom_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(BloomOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (BloomOpaque *)
diff --git a/src/backend/access/brin/brin_minmax_multi.c b/src/backend/access/brin/brin_minmax_multi.c
index f700700d46..5f637e0629 100644
--- a/src/backend/access/brin/brin_minmax_multi.c
+++ b/src/backend/access/brin/brin_minmax_multi.c
@@ -1301,6 +1301,7 @@ brin_minmax_multi_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(MinmaxMultiOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (MinmaxMultiOpaque *)
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 53d974125f..956d6f0a84 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -98,6 +98,7 @@
#include <math.h>
#include "access/brin.h"
+#include "access/brin_internal.h"
#include "access/brin_page.h"
#include "access/gin.h"
#include "access/table.h"
@@ -7348,7 +7349,8 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
double minimalRanges;
double estimatedRanges;
double selec;
- Relation indexRel;
+ Relation indexRel = NULL;
+ TupleDesc tupdesc = NULL;
ListCell *l;
VariableStatData vardata;
@@ -7370,6 +7372,7 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
*/
indexRel = index_open(index->indexoid, NoLock);
brinGetStats(indexRel, &statsData);
+ tupdesc = RelationGetDescr(indexRel);
index_close(indexRel, NoLock);
/* work out the actual number of ranges in the index */
@@ -7403,6 +7406,17 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
{
IndexClause *iclause = lfirst_node(IndexClause, l);
AttrNumber attnum = index->indexkeys[iclause->indexcol];
+ FmgrInfo *opcInfoFn;
+ BrinOpcInfo *opcInfo;
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, iclause->indexcol);
+ bool ignore_correlation;
+
+ opcInfoFn = index_getprocinfo(indexRel, iclause->indexcol + 1, BRIN_PROCNUM_OPCINFO);
+
+ opcInfo = (BrinOpcInfo *)
+ DatumGetPointer(FunctionCall1(opcInfoFn, attr->atttypid));
+
+ ignore_correlation = opcInfo->oi_ignore_correlation;
/* attempt to lookup stats in relation for this index column */
if (attnum != 0)
@@ -7473,6 +7487,9 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
if (sslot.nnumbers > 0)
varCorrelation = Abs(sslot.numbers[0]);
+ if (ignore_correlation)
+ varCorrelation = 1.0;
+
if (varCorrelation > *indexCorrelation)
*indexCorrelation = varCorrelation;
diff --git a/src/include/access/brin_internal.h b/src/include/access/brin_internal.h
index ee4d0706df..67aea62a02 100644
--- a/src/include/access/brin_internal.h
+++ b/src/include/access/brin_internal.h
@@ -30,6 +30,9 @@ typedef struct BrinOpcInfo
/* Regular processing of NULLs in BrinValues? */
bool oi_regular_nulls;
+ /* Ignore correlation during cost estimation */
+ bool oi_ignore_correlation;
+
/* Opaque pointer for the opclass' private use */
void *oi_opaque;
--
2.25.4
--jsivyprvf2oxfjz3--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH 8/8] tweak costing for bloom/minmax-multi indexes
@ 2020-08-07 13:53 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Tomas Vondra @ 2020-08-07 13:53 UTC (permalink / raw)
---
src/backend/access/brin/brin_bloom.c | 1 +
src/backend/access/brin/brin_minmax_multi.c | 1 +
src/backend/utils/adt/selfuncs.c | 19 ++++++++++++++++++-
src/include/access/brin_internal.h | 3 +++
4 files changed, 23 insertions(+), 1 deletion(-)
diff --git a/src/backend/access/brin/brin_bloom.c b/src/backend/access/brin/brin_bloom.c
index e7ca100821..f64381b657 100644
--- a/src/backend/access/brin/brin_bloom.c
+++ b/src/backend/access/brin/brin_bloom.c
@@ -643,6 +643,7 @@ brin_bloom_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(BloomOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (BloomOpaque *)
diff --git a/src/backend/access/brin/brin_minmax_multi.c b/src/backend/access/brin/brin_minmax_multi.c
index f700700d46..5f637e0629 100644
--- a/src/backend/access/brin/brin_minmax_multi.c
+++ b/src/backend/access/brin/brin_minmax_multi.c
@@ -1301,6 +1301,7 @@ brin_minmax_multi_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(MinmaxMultiOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (MinmaxMultiOpaque *)
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 53d974125f..956d6f0a84 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -98,6 +98,7 @@
#include <math.h>
#include "access/brin.h"
+#include "access/brin_internal.h"
#include "access/brin_page.h"
#include "access/gin.h"
#include "access/table.h"
@@ -7348,7 +7349,8 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
double minimalRanges;
double estimatedRanges;
double selec;
- Relation indexRel;
+ Relation indexRel = NULL;
+ TupleDesc tupdesc = NULL;
ListCell *l;
VariableStatData vardata;
@@ -7370,6 +7372,7 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
*/
indexRel = index_open(index->indexoid, NoLock);
brinGetStats(indexRel, &statsData);
+ tupdesc = RelationGetDescr(indexRel);
index_close(indexRel, NoLock);
/* work out the actual number of ranges in the index */
@@ -7403,6 +7406,17 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
{
IndexClause *iclause = lfirst_node(IndexClause, l);
AttrNumber attnum = index->indexkeys[iclause->indexcol];
+ FmgrInfo *opcInfoFn;
+ BrinOpcInfo *opcInfo;
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, iclause->indexcol);
+ bool ignore_correlation;
+
+ opcInfoFn = index_getprocinfo(indexRel, iclause->indexcol + 1, BRIN_PROCNUM_OPCINFO);
+
+ opcInfo = (BrinOpcInfo *)
+ DatumGetPointer(FunctionCall1(opcInfoFn, attr->atttypid));
+
+ ignore_correlation = opcInfo->oi_ignore_correlation;
/* attempt to lookup stats in relation for this index column */
if (attnum != 0)
@@ -7473,6 +7487,9 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
if (sslot.nnumbers > 0)
varCorrelation = Abs(sslot.numbers[0]);
+ if (ignore_correlation)
+ varCorrelation = 1.0;
+
if (varCorrelation > *indexCorrelation)
*indexCorrelation = varCorrelation;
diff --git a/src/include/access/brin_internal.h b/src/include/access/brin_internal.h
index ee4d0706df..67aea62a02 100644
--- a/src/include/access/brin_internal.h
+++ b/src/include/access/brin_internal.h
@@ -30,6 +30,9 @@ typedef struct BrinOpcInfo
/* Regular processing of NULLs in BrinValues? */
bool oi_regular_nulls;
+ /* Ignore correlation during cost estimation */
+ bool oi_ignore_correlation;
+
/* Opaque pointer for the opclass' private use */
void *oi_opaque;
--
2.25.4
--jsivyprvf2oxfjz3--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH 8/8] tweak costing for bloom/minmax-multi indexes
@ 2020-08-07 13:53 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Tomas Vondra @ 2020-08-07 13:53 UTC (permalink / raw)
---
src/backend/access/brin/brin_bloom.c | 1 +
src/backend/access/brin/brin_minmax_multi.c | 1 +
src/backend/utils/adt/selfuncs.c | 19 ++++++++++++++++++-
src/include/access/brin_internal.h | 3 +++
4 files changed, 23 insertions(+), 1 deletion(-)
diff --git a/src/backend/access/brin/brin_bloom.c b/src/backend/access/brin/brin_bloom.c
index e7ca100821..f64381b657 100644
--- a/src/backend/access/brin/brin_bloom.c
+++ b/src/backend/access/brin/brin_bloom.c
@@ -643,6 +643,7 @@ brin_bloom_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(BloomOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (BloomOpaque *)
diff --git a/src/backend/access/brin/brin_minmax_multi.c b/src/backend/access/brin/brin_minmax_multi.c
index f700700d46..5f637e0629 100644
--- a/src/backend/access/brin/brin_minmax_multi.c
+++ b/src/backend/access/brin/brin_minmax_multi.c
@@ -1301,6 +1301,7 @@ brin_minmax_multi_opcinfo(PG_FUNCTION_ARGS)
result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) +
sizeof(MinmaxMultiOpaque));
+ result->oi_ignore_correlation = true;
result->oi_nstored = 1;
result->oi_regular_nulls = true;
result->oi_opaque = (MinmaxMultiOpaque *)
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 53d974125f..956d6f0a84 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -98,6 +98,7 @@
#include <math.h>
#include "access/brin.h"
+#include "access/brin_internal.h"
#include "access/brin_page.h"
#include "access/gin.h"
#include "access/table.h"
@@ -7348,7 +7349,8 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
double minimalRanges;
double estimatedRanges;
double selec;
- Relation indexRel;
+ Relation indexRel = NULL;
+ TupleDesc tupdesc = NULL;
ListCell *l;
VariableStatData vardata;
@@ -7370,6 +7372,7 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
*/
indexRel = index_open(index->indexoid, NoLock);
brinGetStats(indexRel, &statsData);
+ tupdesc = RelationGetDescr(indexRel);
index_close(indexRel, NoLock);
/* work out the actual number of ranges in the index */
@@ -7403,6 +7406,17 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
{
IndexClause *iclause = lfirst_node(IndexClause, l);
AttrNumber attnum = index->indexkeys[iclause->indexcol];
+ FmgrInfo *opcInfoFn;
+ BrinOpcInfo *opcInfo;
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, iclause->indexcol);
+ bool ignore_correlation;
+
+ opcInfoFn = index_getprocinfo(indexRel, iclause->indexcol + 1, BRIN_PROCNUM_OPCINFO);
+
+ opcInfo = (BrinOpcInfo *)
+ DatumGetPointer(FunctionCall1(opcInfoFn, attr->atttypid));
+
+ ignore_correlation = opcInfo->oi_ignore_correlation;
/* attempt to lookup stats in relation for this index column */
if (attnum != 0)
@@ -7473,6 +7487,9 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
if (sslot.nnumbers > 0)
varCorrelation = Abs(sslot.numbers[0]);
+ if (ignore_correlation)
+ varCorrelation = 1.0;
+
if (varCorrelation > *indexCorrelation)
*indexCorrelation = varCorrelation;
diff --git a/src/include/access/brin_internal.h b/src/include/access/brin_internal.h
index ee4d0706df..67aea62a02 100644
--- a/src/include/access/brin_internal.h
+++ b/src/include/access/brin_internal.h
@@ -30,6 +30,9 @@ typedef struct BrinOpcInfo
/* Regular processing of NULLs in BrinValues? */
bool oi_regular_nulls;
+ /* Ignore correlation during cost estimation */
+ bool oi_ignore_correlation;
+
/* Opaque pointer for the opclass' private use */
void *oi_opaque;
--
2.25.4
--jsivyprvf2oxfjz3--
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Support for NO INHERIT to INHERIT state change with named NOT NULL constraints
@ 2025-02-04 18:41 Alvaro Herrera <[email protected]>
2025-02-10 05:13 ` Re: Support for NO INHERIT to INHERIT state change with named NOT NULL constraints Suraj Kharage <[email protected]>
0 siblings, 1 reply; 26+ messages in thread
From: Alvaro Herrera @ 2025-02-04 18:41 UTC (permalink / raw)
To: Suraj Kharage <[email protected]>; +Cc: jian he <[email protected]>; Robert Haas <[email protected]>; pgsql-hackers
On 2025-Jan-13, Suraj Kharage wrote:
> Please find attached revised version of patch which added the INHERIT to NO
> INHERIT state change for not null constraint.
Thanks!
I find the doc changes a little odd. First, you seem to have added a
[INHERIT/NO INHERIT] flag in the wrong place (line 112); that stuff
already has the NO INHERIT flag next to the constraint types that allow
it, so that change should be removed from the patch. I think the
addition in line 62 are sufficient. Second, adding the explanation for
what this does to the existing varlistentry for ALTER CONSTRAINT looks
out of place. I would add a separate one, something like this perhaps:
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index f9576da435e..10614bcdbd6 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -59,6 +59,7 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
ADD <replaceable class="parameter">table_constraint</replaceable> [ NOT VALID ]
ADD <replaceable class="parameter">table_constraint_using_index</replaceable>
ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
+ ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> SET [ NO ] INHERIT
VALIDATE CONSTRAINT <replaceable class="parameter">constraint_name</replaceable>
DROP CONSTRAINT [ IF EXISTS ] <replaceable class="parameter">constraint_name</replaceable> [ RESTRICT | CASCADE ]
DISABLE TRIGGER [ <replaceable class="parameter">trigger_name</replaceable> | ALL | USER ]
@@ -551,7 +552,27 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<listitem>
<para>
This form alters the attributes of a constraint that was previously
- created. Currently only foreign key constraints may be altered.
+ created. Currently only foreign key constraints may be altered in
+ this fashion, but see below.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="sql-altertable-desc-alter-constraint-inherit">
+ <term><literal>ALTER CONSTRAINT ... SET INHERIT</literal></term>
+ <term><literal>ALTER CONSTRAINT ... SET NO INHERIT</literal></term>
+ <listitem>
+ <para>
+ This form modifies a inheritable constraint so that it becomes not
+ inheritable, or vice-versa. Only not-null constraints may be altered
+ in this fashion at present.
+ In addition to changing the inheritability status of the constraint,
+ in the case where a non-inheritable constraint is being marked
+ inheritable, if the table has children, an equivalent constraint
+ is added to them. If marking an inheritable constraint as
+ non-inheritable on a table with children, then the corresponding
+ constraint on children will be marked as no longer inherited,
+ but not removed.
</para>
</listitem>
</varlistentry>
I don't think reusing AT_AlterConstraint for this is a good idea. I
would rather add a new AT_AlterConstraintInherit /
AT_AlterConstraintNoInherit, which takes only a constraint name in
n->name rather than a Constraint in n->def. So gram.y would look like
/*
* ALTER TABLE <name> ALTER CONSTRAINT SET [NO] INHERIT
*/
| ALTER CONSTRAINT name SET INHERIT
{
AlterTableCmd *n = makeNode(AlterTableCmd);
n->subtype = AT_AlterConstraintInherit;
n->name = $3;
$$ = (Node *) n;
}
| ALTER CONSTRAINT name SET NO INHERIT
{
AlterTableCmd *n = makeNode(AlterTableCmd);
n->subtype = AT_AlterConstraintNoInherit;
n->name = $3;
$$ = (Node *) n;
}
This avoids hardcoding in the grammar that we only support this for
not-null constraints -- I'm sure we'll want to implement this for CHECK
constraints later, and at the grammar level there just wouldn't be any
way to implement that the way you have it.
It's a pity that bison doesn't like having unadorned NO INHERIT here.
That would align better with the other use of INHERIT / NO INHERIT we
have in alter table -- requiring a SET there looks ugly. I tried to
change it and the shift/reduce conflict is annoying. I don't have any
bright ideas on fixing that.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"Nunca confiaré en un traidor. Ni siquiera si el traidor lo he creado yo"
(Barón Vladimir Harkonnen)
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Support for NO INHERIT to INHERIT state change with named NOT NULL constraints
2025-02-04 18:41 Re: Support for NO INHERIT to INHERIT state change with named NOT NULL constraints Alvaro Herrera <[email protected]>
@ 2025-02-10 05:13 ` Suraj Kharage <[email protected]>
2025-02-19 15:46 ` Re: Support for NO INHERIT to INHERIT state change with named NOT NULL constraints Alvaro Herrera <[email protected]>
0 siblings, 1 reply; 26+ messages in thread
From: Suraj Kharage @ 2025-02-10 05:13 UTC (permalink / raw)
To: Alvaro Herrera <[email protected]>; +Cc: jian he <[email protected]>; Robert Haas <[email protected]>; pgsql-hackers
Thanks, Alvaro, for the review.
I have addressed your comments per the above suggestions in the attached v4
patch.
--
Thanks & Regards,
Suraj kharage,
enterprisedb.com <https://www.enterprisedb.com/;
On Wed, Feb 5, 2025 at 12:11 AM Alvaro Herrera <[email protected]>
wrote:
> On 2025-Jan-13, Suraj Kharage wrote:
>
> > Please find attached revised version of patch which added the INHERIT to
> NO
> > INHERIT state change for not null constraint.
>
> Thanks!
>
> I find the doc changes a little odd. First, you seem to have added a
> [INHERIT/NO INHERIT] flag in the wrong place (line 112); that stuff
> already has the NO INHERIT flag next to the constraint types that allow
> it, so that change should be removed from the patch. I think the
> addition in line 62 are sufficient. Second, adding the explanation for
> what this does to the existing varlistentry for ALTER CONSTRAINT looks
> out of place. I would add a separate one, something like this perhaps:
>
> diff --git a/doc/src/sgml/ref/alter_table.sgml
> b/doc/src/sgml/ref/alter_table.sgml
> index f9576da435e..10614bcdbd6 100644
> --- a/doc/src/sgml/ref/alter_table.sgml
> +++ b/doc/src/sgml/ref/alter_table.sgml
> @@ -59,6 +59,7 @@ ALTER TABLE [ IF EXISTS ] <replaceable
> class="parameter">name</replaceable>
> ADD <replaceable class="parameter">table_constraint</replaceable> [
> NOT VALID ]
> ADD <replaceable
> class="parameter">table_constraint_using_index</replaceable>
> ALTER CONSTRAINT <replaceable
> class="parameter">constraint_name</replaceable> [ DEFERRABLE | NOT
> DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
> + ALTER CONSTRAINT <replaceable
> class="parameter">constraint_name</replaceable> SET [ NO ] INHERIT
> VALIDATE CONSTRAINT <replaceable
> class="parameter">constraint_name</replaceable>
> DROP CONSTRAINT [ IF EXISTS ] <replaceable
> class="parameter">constraint_name</replaceable> [ RESTRICT | CASCADE ]
> DISABLE TRIGGER [ <replaceable
> class="parameter">trigger_name</replaceable> | ALL | USER ]
> @@ -551,7 +552,27 @@ WITH ( MODULUS <replaceable
> class="parameter">numeric_literal</replaceable>, REM
> <listitem>
> <para>
> This form alters the attributes of a constraint that was previously
> - created. Currently only foreign key constraints may be altered.
> + created. Currently only foreign key constraints may be altered in
> + this fashion, but see below.
> + </para>
> + </listitem>
> + </varlistentry>
> +
> + <varlistentry id="sql-altertable-desc-alter-constraint-inherit">
> + <term><literal>ALTER CONSTRAINT ... SET INHERIT</literal></term>
> + <term><literal>ALTER CONSTRAINT ... SET NO INHERIT</literal></term>
> + <listitem>
> + <para>
> + This form modifies a inheritable constraint so that it becomes not
> + inheritable, or vice-versa. Only not-null constraints may be altered
> + in this fashion at present.
> + In addition to changing the inheritability status of the constraint,
> + in the case where a non-inheritable constraint is being marked
> + inheritable, if the table has children, an equivalent constraint
> + is added to them. If marking an inheritable constraint as
> + non-inheritable on a table with children, then the corresponding
> + constraint on children will be marked as no longer inherited,
> + but not removed.
> </para>
> </listitem>
> </varlistentry>
>
>
> I don't think reusing AT_AlterConstraint for this is a good idea. I
> would rather add a new AT_AlterConstraintInherit /
> AT_AlterConstraintNoInherit, which takes only a constraint name in
> n->name rather than a Constraint in n->def. So gram.y would look like
>
> /*
> * ALTER TABLE <name> ALTER CONSTRAINT SET [NO]
> INHERIT
> */
> | ALTER CONSTRAINT name SET INHERIT
> {
> AlterTableCmd *n =
> makeNode(AlterTableCmd);
>
> n->subtype =
> AT_AlterConstraintInherit;
> n->name = $3;
>
> $$ = (Node *) n;
> }
> | ALTER CONSTRAINT name SET NO INHERIT
> {
> AlterTableCmd *n =
> makeNode(AlterTableCmd);
>
> n->subtype =
> AT_AlterConstraintNoInherit;
> n->name = $3;
>
> $$ = (Node *) n;
> }
>
> This avoids hardcoding in the grammar that we only support this for
> not-null constraints -- I'm sure we'll want to implement this for CHECK
> constraints later, and at the grammar level there just wouldn't be any
> way to implement that the way you have it.
>
>
> It's a pity that bison doesn't like having unadorned NO INHERIT here.
> That would align better with the other use of INHERIT / NO INHERIT we
> have in alter table -- requiring a SET there looks ugly. I tried to
> change it and the shift/reduce conflict is annoying. I don't have any
> bright ideas on fixing that.
>
> --
> Álvaro Herrera 48°01'N 7°57'E —
> https://www.EnterpriseDB.com/
> "Nunca confiaré en un traidor. Ni siquiera si el traidor lo he creado yo"
> (Barón Vladimir Harkonnen)
>
Attachments:
[application/octet-stream] v4-alter_not_null_constraint_to_inherit_no_inherit.patch (52.5K, ../../CAF1DzPX2dTyOyiHfUoszYroa62T6_CXXMqFozcAvwOFCRJKCnA@mail.gmail.com/3-v4-alter_not_null_constraint_to_inherit_no_inherit.patch)
download | inline diff:
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index f9576da..59f939c 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -59,6 +59,7 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
ADD <replaceable class="parameter">table_constraint</replaceable> [ NOT VALID ]
ADD <replaceable class="parameter">table_constraint_using_index</replaceable>
ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
+ ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> SET [INHERIT | NO INHERIT]
VALIDATE CONSTRAINT <replaceable class="parameter">constraint_name</replaceable>
DROP CONSTRAINT [ IF EXISTS ] <replaceable class="parameter">constraint_name</replaceable> [ RESTRICT | CASCADE ]
DISABLE TRIGGER [ <replaceable class="parameter">trigger_name</replaceable> | ALL | USER ]
@@ -551,11 +552,31 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<listitem>
<para>
This form alters the attributes of a constraint that was previously
- created. Currently only foreign key constraints may be altered.
+ created. Currently only foreign key constraints may be altered in
+ this fashion, but see below.
</para>
</listitem>
</varlistentry>
+ <varlistentry id="sql-altertable-desc-alter-constraint-inherit">
+ <term><literal>ALTER CONSTRAINT ... SET INHERIT</literal></term>
+ <term><literal>ALTER CONSTRAINT ... SET NO INHERIT</literal></term>
+ <listitem>
+ <para>
+ This form modifies a inheritable constraint so that it becomes not
+ inheritable, or vice-versa. Only not-null constraints may be altered
+ in this fashion at present.
+ In addition to changing the inheritability status of the constraint,
+ in the case where a non-inheritable constraint is being marked
+ inheritable, if the table has children, an equivalent constraint
+ is added to them. If marking an inheritable constraint as
+ non-inheritable on a table with children, then the corresponding
+ constraint on children will be marked as no longer inherited,
+ but not removed.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="sql-altertable-desc-validate-constraint">
<term><literal>VALIDATE CONSTRAINT</literal></term>
<listitem>
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 18f64db..fa6231a 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -391,6 +391,10 @@ static void AlterSeqNamespaces(Relation classRel, Relation rel,
LOCKMODE lockmode);
static ObjectAddress ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd,
bool recurse, bool recursing, LOCKMODE lockmode);
+static ObjectAddress ATExecAlterConstraintInherit(List **wqueue, Relation rel, AlterTableCmd *cmd,
+ bool recurse, bool recursing, LOCKMODE lockmode);
+static ObjectAddress ATExecAlterConstraintNoInherit(Relation rel, AlterTableCmd *cmd,
+ LOCKMODE lockmode);
static bool ATExecAlterConstrRecurse(Constraint *cmdcon, Relation conrel, Relation tgrel,
Relation rel, HeapTuple contuple, List **otherrelids,
LOCKMODE lockmode);
@@ -476,6 +480,8 @@ static ObjectAddress ATExecSetNotNull(List **wqueue, Relation rel,
char *constrname, char *colName,
bool recurse, bool recursing,
LOCKMODE lockmode);
+static ObjectAddress ATExecSetNotNullNoInherit(Relation rel, char *conName,
+ char *colName, LOCKMODE lockmode);
static bool NotNullImpliedByRelConstraints(Relation rel, Form_pg_attribute attr);
static bool ConstraintImpliedByRelConstraint(Relation scanrel,
List *testConstraint, List *provenConstraint);
@@ -4660,6 +4666,8 @@ AlterTableGetLockLevel(List *cmds)
case AT_SetExpression:
case AT_DropExpression:
case AT_SetCompression:
+ case AT_AlterConstraintInherit:
+ case AT_AlterConstraintNoInherit:
cmd_lockmode = AccessExclusiveLock;
break;
@@ -5154,6 +5162,18 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
/* Recursion occurs during execution phase */
pass = AT_PASS_MISC;
break;
+ case AT_AlterConstraintInherit: /* ALTER CONSTRAINT SET INHERIT*/
+ ATSimplePermissions(cmd->subtype, rel,
+ ATT_TABLE | ATT_PARTITIONED_TABLE);
+ /* Recursion occurs during execution phase */
+ pass = AT_PASS_MISC;
+ break;
+ case AT_AlterConstraintNoInherit: /* ALTER CONSTRAINT SET NO INHERIT */
+ ATSimplePermissions(cmd->subtype, rel,
+ ATT_TABLE | ATT_PARTITIONED_TABLE);
+ /* Recursion occurs during execution phase */
+ pass = AT_PASS_MISC;
+ break;
case AT_ValidateConstraint: /* VALIDATE CONSTRAINT */
ATSimplePermissions(cmd->subtype, rel,
ATT_TABLE | ATT_PARTITIONED_TABLE | ATT_FOREIGN_TABLE);
@@ -5434,6 +5454,13 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab,
case AT_AlterConstraint: /* ALTER CONSTRAINT */
address = ATExecAlterConstraint(rel, cmd, false, false, lockmode);
break;
+ case AT_AlterConstraintInherit: /* ALTER CONSTRAINT SET INHERIT */
+ address = ATExecAlterConstraintInherit(wqueue, rel, cmd,
+ true, true, lockmode);
+ break;
+ case AT_AlterConstraintNoInherit: /* ALTER CONSTRAINT SET NO INHERIT */
+ address = ATExecAlterConstraintNoInherit(rel, cmd, lockmode);
+ break;
case AT_ValidateConstraint: /* VALIDATE CONSTRAINT */
address = ATExecValidateConstraint(wqueue, rel, cmd->name, cmd->recurse,
false, lockmode);
@@ -6518,6 +6545,10 @@ alter_table_type_to_string(AlterTableType cmdtype)
return "ADD CONSTRAINT";
case AT_AlterConstraint:
return "ALTER CONSTRAINT";
+ case AT_AlterConstraintInherit:
+ return "ALTER CONSTRAINT ... SET INHERIT";
+ case AT_AlterConstraintNoInherit:
+ return "ALTER CONSTRAINT ... SET NO INHERIT";
case AT_ValidateConstraint:
return "VALIDATE CONSTRAINT";
case AT_DropConstraint:
@@ -21034,3 +21065,331 @@ GetAttributeStorage(Oid atttypid, const char *storagemode)
return cstorage;
}
+
+/*
+ * ALTER TABLE ALTER CONSTRAINT SET INHERIT
+ *
+ * Make the constraint as inherit and recurse the constraint to childrens if
+ * any.
+ *
+ * Currently only works for not null constraints.
+ *
+ * If the constraint is modified, returns its address; otherwise, return
+ * InvalidObjectAddress.
+ */
+static ObjectAddress
+ATExecAlterConstraintInherit(List **wqueue, Relation rel,
+ AlterTableCmd *cmd, bool recurse,
+ bool recursing, LOCKMODE lockmode)
+{
+ Relation conrel;
+ Relation tgrel;
+ SysScanDesc scan;
+ ScanKeyData skey[3];
+ HeapTuple contuple;
+ Form_pg_constraint currcon;
+ ObjectAddress address = InvalidObjectAddress;
+ AttrNumber colNum;
+ char *colName;
+ List *children;
+
+ conrel = table_open(ConstraintRelationId, RowExclusiveLock);
+ tgrel = table_open(TriggerRelationId, RowExclusiveLock);
+
+ /*
+ * Find and check the target constraint
+ */
+ ScanKeyInit(&skey[0],
+ Anum_pg_constraint_conrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationGetRelid(rel)));
+ ScanKeyInit(&skey[1],
+ Anum_pg_constraint_contypid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(InvalidOid));
+ ScanKeyInit(&skey[2],
+ Anum_pg_constraint_conname,
+ BTEqualStrategyNumber, F_NAMEEQ,
+ CStringGetDatum(cmd->name));
+ scan = systable_beginscan(conrel, ConstraintRelidTypidNameIndexId,
+ true, NULL, 3, skey);
+
+ /* There can be at most one matching row */
+ if (!HeapTupleIsValid(contuple = systable_getnext(scan)))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("constraint \"%s\" of relation \"%s\" does not exist",
+ cmd->name, RelationGetRelationName(rel))));
+
+ contuple = heap_copytuple(contuple);
+
+ currcon = (Form_pg_constraint) GETSTRUCT(contuple);
+
+ if (currcon->contype != CONSTRAINT_NOTNULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("ALTER TABLE \"%s\" ALTER CONSTRAINT \"%s\" SET INHERIT only supports not null constraint",
+ RelationGetRelationName(rel), cmd->name)));
+
+ /* Return if constraint is already marked as INHERIT. */
+ if (!currcon->connoinherit)
+ {
+ systable_endscan(scan);
+
+ table_close(tgrel, RowExclusiveLock);
+ table_close(conrel, RowExclusiveLock);
+ heap_freetuple(contuple);
+
+ return address;
+ }
+
+ /* Update the constraint tuple and mark connoinherit as false. */
+ currcon->connoinherit = false;
+
+ CatalogTupleUpdate(conrel, &contuple->t_self, contuple);
+ ObjectAddressSet(address, ConstraintRelationId, currcon->oid);
+ CommandCounterIncrement();
+
+ systable_endscan(scan);
+
+ table_close(tgrel, RowExclusiveLock);
+ table_close(conrel, RowExclusiveLock);
+
+ /* fetch the column number and name */
+ colNum = extractNotNullColumn(contuple);
+ colName = get_attname(currcon->conrelid, colNum, false);
+
+ /*
+ * Recurse to propagate the constraint to children that don't have one.
+ */
+ children = find_inheritance_children(RelationGetRelid(rel),
+ lockmode);
+
+ foreach_oid(childoid, children)
+ {
+ Relation childrel = table_open(childoid, NoLock);
+ ObjectAddress addr;
+
+ addr = ATExecSetNotNull(wqueue, childrel, NameStr(currcon->conname),
+ colName, recurse, recursing, lockmode);
+ if (OidIsValid(addr.objectId))
+ CommandCounterIncrement();
+
+ table_close(childrel, NoLock);
+ }
+
+ heap_freetuple(contuple);
+
+ return address;
+}
+
+
+/*
+ * ALTER TABLE ALTER CONSTRAINT SET NO INHERIT
+ *
+ * Make the constraint as inherit and recurse to the childrens and decrement
+ * their inheritance count.
+ *
+ * Currently only works for not null constraints.
+ *
+ * If the constraint is modified, returns its address; otherwise, return
+ * InvalidObjectAddress.
+ */
+static ObjectAddress
+ATExecAlterConstraintNoInherit(Relation rel, AlterTableCmd *cmd,
+ LOCKMODE lockmode)
+{
+ Relation conrel;
+ Relation tgrel;
+ SysScanDesc scan;
+ ScanKeyData skey[3];
+ HeapTuple contuple;
+ Form_pg_constraint currcon;
+ ObjectAddress address = InvalidObjectAddress;
+ AttrNumber colNum;
+ char *colName;
+ List *children;
+
+ conrel = table_open(ConstraintRelationId, RowExclusiveLock);
+ tgrel = table_open(TriggerRelationId, RowExclusiveLock);
+
+ /*
+ * Find and check the target constraint
+ */
+ ScanKeyInit(&skey[0],
+ Anum_pg_constraint_conrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationGetRelid(rel)));
+ ScanKeyInit(&skey[1],
+ Anum_pg_constraint_contypid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(InvalidOid));
+ ScanKeyInit(&skey[2],
+ Anum_pg_constraint_conname,
+ BTEqualStrategyNumber, F_NAMEEQ,
+ CStringGetDatum(cmd->name));
+ scan = systable_beginscan(conrel, ConstraintRelidTypidNameIndexId,
+ true, NULL, 3, skey);
+
+ /* There can be at most one matching row */
+ if (!HeapTupleIsValid(contuple = systable_getnext(scan)))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("constraint \"%s\" of relation \"%s\" does not exist",
+ cmd->name, RelationGetRelationName(rel))));
+
+ contuple = heap_copytuple(contuple);
+
+ currcon = (Form_pg_constraint) GETSTRUCT(contuple);
+
+ if (currcon->contype != CONSTRAINT_NOTNULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("ALTER TABLE \"%s\" ALTER CONSTRAINT \"%s\" SET NO INHERIT only supports not null constraint",
+ RelationGetRelationName(rel), cmd->name)));
+
+ /* Return if constraint is already marked as NO INHERIT. */
+ if (currcon->connoinherit)
+ {
+ systable_endscan(scan);
+
+ table_close(tgrel, RowExclusiveLock);
+ table_close(conrel, RowExclusiveLock);
+ heap_freetuple(contuple);
+
+ return address;
+ }
+
+ /* Update the constraint tuple and mark connoinherit as true. */
+ currcon->connoinherit = true;
+
+ CatalogTupleUpdate(conrel, &contuple->t_self, contuple);
+ ObjectAddressSet(address, ConstraintRelationId, currcon->oid);
+ CommandCounterIncrement();
+
+ systable_endscan(scan);
+
+ table_close(tgrel, RowExclusiveLock);
+ table_close(conrel, RowExclusiveLock);
+
+ /* fetch the column number and name */
+ colNum = extractNotNullColumn(contuple);
+ colName = get_attname(currcon->conrelid, colNum, false);
+
+ /*
+ * Recurse to propagate the constraint to children that don't have one.
+ */
+ children = find_inheritance_children(RelationGetRelid(rel),
+ lockmode);
+
+ foreach_oid(childoid, children)
+ {
+ Relation childrel = table_open(childoid, NoLock);
+
+ ATExecSetNotNullNoInherit(childrel, NameStr(currcon->conname),
+ colName, lockmode);
+
+ table_close(childrel, NoLock);
+ }
+
+ heap_freetuple(contuple);
+
+ return address;
+}
+
+/*
+ * Find out the not null constraint from provided relation and decrement the
+ * coninhcount count if constraint exists since the parent constraint marked as
+ * no inherit.
+ *
+ * We must recurse to child tables during execution.
+ */
+static ObjectAddress
+ATExecSetNotNullNoInherit(Relation rel, char *conName,
+ char *colName, LOCKMODE lockmode)
+{
+
+ HeapTuple tuple;
+ AttrNumber attnum;
+ ObjectAddress address;
+ List *children;
+
+ /* Guard against stack overflow due to overly deep inheritance tree. */
+ check_stack_depth();
+
+ ATSimplePermissions(AT_AddConstraint, rel,
+ ATT_PARTITIONED_TABLE | ATT_TABLE | ATT_FOREIGN_TABLE);
+
+ attnum = get_attnum(RelationGetRelid(rel), colName);
+ if (attnum == InvalidAttrNumber)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_COLUMN),
+ errmsg("column \"%s\" of relation \"%s\" does not exist",
+ colName, RelationGetRelationName(rel))));
+
+ /* Prevent them from altering a system attribute */
+ if (attnum <= 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot alter system column \"%s\"",
+ colName)));
+
+ /* See if there's already a constraint */
+ tuple = findNotNullConstraintAttnum(RelationGetRelid(rel), attnum);
+ if (HeapTupleIsValid(tuple))
+ {
+ Form_pg_constraint conForm = (Form_pg_constraint) GETSTRUCT(tuple);
+ bool changed = false;
+
+ /* Decrement the constraint inheritance count */
+ if (conForm->coninhcount > 0)
+ {
+ conForm->coninhcount--;
+ changed = true;
+ }
+ /* Mark the constraint as local defined once coninhcount = 0 */
+ if (conForm->coninhcount == 0)
+ {
+ conForm->conislocal = true;
+ changed = true;
+ }
+
+ if (changed)
+ {
+ Relation constr_rel;
+
+ constr_rel = table_open(ConstraintRelationId, RowExclusiveLock);
+
+ CatalogTupleUpdate(constr_rel, &tuple->t_self, tuple);
+ ObjectAddressSet(address, ConstraintRelationId, conForm->oid);
+ table_close(constr_rel, RowExclusiveLock);
+
+ /* Make update visible */
+ CommandCounterIncrement();
+ }
+ }
+ else
+ elog(ERROR, "cache lookup failed for not-null constraint on column \"%s\" of relation %u",
+ colName, RelationGetRelid(rel));
+
+ if (tuple)
+ heap_freetuple(tuple);
+
+ InvokeObjectPostAlterHook(RelationRelationId,
+ RelationGetRelid(rel), attnum);
+
+ /*
+ * Recurse to child tables.
+ */
+ children = find_inheritance_children(RelationGetRelid(rel), lockmode);
+
+ foreach_oid(childoid, children)
+ {
+ Relation childrel = table_open(childoid, NoLock);
+
+ ATExecSetNotNullNoInherit(childrel, conName, colName, lockmode);
+ table_close(childrel, NoLock);
+ }
+
+ return address;
+}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index d7f9c00..d96989a 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -2669,6 +2669,26 @@ alter_table_cmd:
NULL, NULL, NULL, yyscanner);
$$ = (Node *) n;
}
+ /* ALTER TABLE <name> ALTER CONSTRAINT INHERIT */
+ | ALTER CONSTRAINT name SET INHERIT
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+
+ n->subtype = AT_AlterConstraintInherit;
+ n->name = $3;
+
+ $$ = (Node *) n;
+ }
+ /* ALTER TABLE <name> ALTER CONSTRAINT SET NO INHERIT */
+ | ALTER CONSTRAINT name SET NO INHERIT
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+
+ n->subtype = AT_AlterConstraintNoInherit;
+ n->name = $3;
+
+ $$ = (Node *) n;
+ }
/* ALTER TABLE <name> VALIDATE CONSTRAINT ... */
| VALIDATE CONSTRAINT name
{
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index ffe155e..6ba542b 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2467,6 +2467,8 @@ typedef enum AlterTableType
AT_SetIdentity, /* SET identity column options */
AT_DropIdentity, /* DROP IDENTITY */
AT_ReAddStatistics, /* internal to commands/tablecmds.c */
+ AT_AlterConstraintInherit, /* alter constraint set inherit */
+ AT_AlterConstraintNoInherit, /* alter constraint set no inherit */
} AlterTableType;
typedef struct ReplicaIdentityStmt
diff --git a/src/test/modules/test_ddl_deparse/test_ddl_deparse.c b/src/test/modules/test_ddl_deparse/test_ddl_deparse.c
index 193669f..46281ec 100644
--- a/src/test/modules/test_ddl_deparse/test_ddl_deparse.c
+++ b/src/test/modules/test_ddl_deparse/test_ddl_deparse.c
@@ -168,6 +168,8 @@ get_altertable_subcmdinfo(PG_FUNCTION_ARGS)
strtype = "(re) ADD DOMAIN CONSTRAINT";
break;
case AT_AlterConstraint:
+ case AT_AlterConstraintInherit:
+ case AT_AlterConstraintNoInherit:
strtype = "ALTER CONSTRAINT";
break;
case AT_ValidateConstraint:
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index dbf3835..2d76aba 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -2744,6 +2744,587 @@ NOTICE: drop cascades to 2 other objects
DETAIL: drop cascades to table inh_multiparent
drop cascades to table inh_multiparent2
--
+-- Test - alter constraint inherit/no inherit for not null.
+--
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+NOTICE: merging multiple inherited definitions of column "f1"
+create table ch3 () inherits (part1, ch1, ch2);
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f2"
+NOTICE: merging multiple inherited definitions of column "f3"
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 2 | f
+ ch3 | part1_f1_not_null | n | 3 | f
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1
+Child tables: ch2,
+ ch3
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1,
+ ch2
+
+alter table part1 alter constraint part1_f1_not_null set no inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 0 | t
+ ch2 | part1_f1_not_null | n | 0 | t
+ ch3 | part1_f1_not_null | n | 0 | t
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1"
+Inherits: part1
+Child tables: ch2,
+ ch3
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1"
+Inherits: part1,
+ ch1
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1"
+Inherits: part1,
+ ch1,
+ ch2
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+-- Test inherit constraint and make sure it validates.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+NOTICE: merging multiple inherited definitions of column "f1"
+create table ch3 () inherits (part1, ch1, ch2);
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f2"
+NOTICE: merging multiple inherited definitions of column "f3"
+insert into ch1 values(NULL, 'sample', 1);
+alter table part1 alter constraint part1_f1_not_null set inherit;
+ERROR: column "f1" of relation "ch1" contains null values
+delete from ch1;
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 2 | f
+ ch3 | part1_f1_not_null | n | 3 | f
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1
+Child tables: ch2,
+ ch3
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1,
+ ch2
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+-- Test not null inherit constraint which already exists on child table.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+NOTICE: merging multiple inherited definitions of column "f1"
+alter table ch1 alter f1 set not null;
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "ch1_f1_not_null" NOT NULL "f1"
+Inherits: part1
+Child tables: ch2
+
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ ch1 | ch1_f1_not_null | n | 1 | t
+ ch2 | ch1_f1_not_null | n | 2 | f
+ part1 | part1_f1_not_null | n | 0 | t
+(3 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "ch1_f1_not_null" NOT NULL "f1" (local, inherited)
+Inherits: part1
+Child tables: ch2
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "ch1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1
+
+drop table part1 cascade;
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int not null no inherit) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+NOTICE: merging multiple inherited definitions of column "f1"
+alter table ch1 alter constraint ch1_f3_not_null set inherit;
+create table ch3 () inherits (part1, ch1, ch2);
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f2"
+NOTICE: merging multiple inherited definitions of column "f3"
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ ch1 | ch1_f3_not_null | n | 0 | t
+ ch2 | ch1_f3_not_null | n | 1 | f
+ ch3 | ch1_f3_not_null | n | 2 | f
+ part1 | part1_f1_not_null | n | 0 | t
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | not null | | plain | |
+Not-null constraints:
+ "ch1_f3_not_null" NOT NULL "f3"
+Inherits: part1
+Child tables: ch2,
+ ch3
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | not null | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "ch1_f3_not_null" NOT NULL "f3" (inherited)
+Inherits: part1,
+ ch1
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | not null | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "ch1_f3_not_null" NOT NULL "f3" (inherited)
+Inherits: part1,
+ ch1,
+ ch2
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+-- Multilevel inheritance.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (ch1);
+create table ch3 () inherits (ch2);
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 1 | f
+ ch3 | part1_f1_not_null | n | 1 | f
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1
+Child tables: ch2
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: ch1
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: ch2
+
+-- Test no inherit.
+alter table part1 alter constraint part1_f1_not_null set no inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 0 | t
+ ch2 | part1_f1_not_null | n | 0 | t
+ ch3 | part1_f1_not_null | n | 0 | t
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1"
+Inherits: part1
+Child tables: ch2
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1"
+Inherits: ch1
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1"
+Inherits: ch2
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+-- If existing behavior is INHERIT.
+create table part1 (f1 int not null);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (ch1);
+create table ch3 () inherits (ch2);
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 1 | f
+ ch3 | part1_f1_not_null | n | 1 | f
+(4 rows)
+
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 1 | f
+ ch3 | part1_f1_not_null | n | 1 | f
+(4 rows)
+
+-- Set to no inherit.
+alter table part1 alter constraint part1_f1_not_null set no inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 0 | t
+ ch2 | part1_f1_not_null | n | 0 | t
+ ch3 | part1_f1_not_null | n | 0 | t
+(4 rows)
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+-- Test no inherit when child has inherited constraint from multiple parents.
+create table part1 (f1 int not null);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+NOTICE: merging multiple inherited definitions of column "f1"
+create table ch3 () inherits (part1, ch1, ch2);
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f2"
+NOTICE: merging multiple inherited definitions of column "f3"
+create table part2(f1 int not null);
+alter table ch2 inherit part2;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 3 | f
+ ch3 | part1_f1_not_null | n | 3 | f
+(4 rows)
+
+alter table part1 alter constraint part1_f1_not_null set no inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 0 | t
+ ch2 | part1_f1_not_null | n | 1 | f
+ ch3 | part1_f1_not_null | n | 0 | t
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1"
+Inherits: part1
+Child tables: ch2,
+ ch3
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1,
+ part2
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1"
+Inherits: part1,
+ ch1,
+ ch2
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+drop table part2 cascade;
+-- Negative scenarios for alter constraint .. set inherit.
+-- Other than not null constraints are not allowed to inherit.
+create table part1 (f1 int check(f1 > 5));
+alter table part1 alter constraint part1_f1_check set inherit;
+ERROR: ALTER TABLE "part1" ALTER CONSTRAINT "part1_f1_check" SET INHERIT only supports not null constraint
+drop table part1;
+-- error out when provided not null constarint does not exists.
+create table part1(f1 int not null no inherit);
+alter table part1 alter constraint foo set inherit;
+ERROR: constraint "foo" of relation "part1" does not exist
+drop table part1;
+--
-- Mixed ownership inheritance tree
--
create role regress_alice;
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index 49aae42..a4e7cb7 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -1091,6 +1091,167 @@ select conrelid::regclass, contype, conname,
drop table inh_p1, inh_p2, inh_p3, inh_p4 cascade;
--
+-- Test - alter constraint inherit/no inherit for not null.
+--
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+create table ch3 () inherits (part1, ch1, ch2);
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+alter table part1 alter constraint part1_f1_not_null set no inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+drop table part1 cascade;
+
+-- Test inherit constraint and make sure it validates.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+create table ch3 () inherits (part1, ch1, ch2);
+insert into ch1 values(NULL, 'sample', 1);
+alter table part1 alter constraint part1_f1_not_null set inherit;
+delete from ch1;
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+drop table part1 cascade;
+
+-- Test not null inherit constraint which already exists on child table.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+alter table ch1 alter f1 set not null;
+\d+ ch1
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+
+drop table part1 cascade;
+
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int not null no inherit) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+alter table ch1 alter constraint ch1_f3_not_null set inherit;
+create table ch3 () inherits (part1, ch1, ch2);
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+drop table part1 cascade;
+
+-- Multilevel inheritance.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (ch1);
+create table ch3 () inherits (ch2);
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+-- Test no inherit.
+alter table part1 alter constraint part1_f1_not_null set no inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+drop table part1 cascade;
+
+-- If existing behavior is INHERIT.
+create table part1 (f1 int not null);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (ch1);
+create table ch3 () inherits (ch2);
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+
+-- Set to no inherit.
+alter table part1 alter constraint part1_f1_not_null set no inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+
+drop table part1 cascade;
+
+-- Test no inherit when child has inherited constraint from multiple parents.
+create table part1 (f1 int not null);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+create table ch3 () inherits (part1, ch1, ch2);
+create table part2(f1 int not null);
+alter table ch2 inherit part2;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+alter table part1 alter constraint part1_f1_not_null set no inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+drop table part1 cascade;
+drop table part2 cascade;
+
+-- Negative scenarios for alter constraint .. set inherit.
+-- Other than not null constraints are not allowed to inherit.
+create table part1 (f1 int check(f1 > 5));
+alter table part1 alter constraint part1_f1_check set inherit;
+drop table part1;
+
+-- error out when provided not null constarint does not exists.
+create table part1(f1 int not null no inherit);
+alter table part1 alter constraint foo set inherit;
+drop table part1;
+
+--
-- Mixed ownership inheritance tree
--
create role regress_alice;
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Support for NO INHERIT to INHERIT state change with named NOT NULL constraints
2025-02-04 18:41 Re: Support for NO INHERIT to INHERIT state change with named NOT NULL constraints Alvaro Herrera <[email protected]>
2025-02-10 05:13 ` Re: Support for NO INHERIT to INHERIT state change with named NOT NULL constraints Suraj Kharage <[email protected]>
@ 2025-02-19 15:46 ` Alvaro Herrera <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Alvaro Herrera @ 2025-02-19 15:46 UTC (permalink / raw)
To: Suraj Kharage <[email protected]>; +Cc: jian he <[email protected]>; Robert Haas <[email protected]>; pgsql-hackers
On 2025-Feb-10, Suraj Kharage wrote:
> Thanks, Alvaro, for the review.
>
> I have addressed your comments per the above suggestions in the attached v4
> patch.
Okay, thanks. It looks good to me, but I realized a few days ago that
this patch affects the same code as the patch from Amul Sul to change
enforcedness of constraints[1], and it would be good to structure all
these in a sensible manner to avoid creating a mess of routines that
work against each other.
So I have pushed patch 0001 from Amul, which restructures the way ALTER
TABLE .. ALTER CONSTRAINT works. This should make it possible to use
the same infrastructure for his NOT ENFORCED constraint change as well
as NO INHERIT. The way I see this working for your patch is that you'd
remove the new AT_AlterConstraintInherit code I had suggested
previously, and instead add a new flag to the ATAlterConstraint struct
to carry the information of the state change we want; then the state
change would actually be implemented inside ATExecAlterConstraintInternal.
I think you'll also need a new member in ATAlterConstraint to carry the
column name that's being modified.
One detail about that is that the recursion model would have to be
different, I think. In the existing code for DEFERRED we simply walk
down the hierarchy using the 'conparentid' field to find children. That
won't work for INHERIT / NO INHERIT -- for this we need to use normal
find_inheritance_children-based recursion.
One thing to keep in mind is what happens with
ALTER TABLE ONLY parent ALTER CONSTRAINT zzz;
ensuring that it operates without recursing for legacy inheritance, and
throwing an error for partitioned tables.
Thanks!
[1] https://postgr.es/m/CAAJ_b96gEVfK5MVe5YRVwBuobMFr_CKGvz683zFLNeF8gAN5_Q@mail.gmail.com
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"Digital and video cameras have this adjustment and film cameras don't for the
same reason dogs and cats lick themselves: because they can." (Ken Rockwell)
^ permalink raw reply [nested|flat] 26+ messages in thread
end of thread, other threads:[~2025-02-19 15:46 UTC | newest]
Thread overview: 26+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-08-07 13:53 [PATCH 8/8] tweak costing for bloom/minmax-multi indexes Tomas Vondra <[email protected]>
2020-08-07 13:53 [PATCH 8/8] tweak costing for bloom/minmax-multi indexes Tomas Vondra <[email protected]>
2020-08-07 13:53 [PATCH 8/8] tweak costing for bloom/minmax-multi indexes Tomas Vondra <[email protected]>
2020-08-07 13:53 [PATCH 8/8] tweak costing for bloom/minmax-multi indexes Tomas Vondra <[email protected]>
2020-08-07 13:53 [PATCH 8/8] tweak costing for bloom/minmax-multi indexes Tomas Vondra <[email protected]>
2020-08-07 13:53 [PATCH 8/8] tweak costing for bloom/minmax-multi indexes Tomas Vondra <[email protected]>
2020-08-07 13:53 [PATCH 8/8] tweak costing for bloom/minmax-multi indexes Tomas Vondra <[email protected]>
2020-08-07 13:53 [PATCH 8/8] tweak costing for bloom/minmax-multi indexes Tomas Vondra <[email protected]>
2020-08-07 13:53 [PATCH 8/8] tweak costing for bloom/minmax-multi indexes Tomas Vondra <[email protected]>
2020-08-07 13:53 [PATCH 8/8] tweak costing for bloom/minmax-multi indexes Tomas Vondra <[email protected]>
2020-08-07 13:53 [PATCH 08/10] tweak costing for bloom/minmax-multi indexes Tomas Vondra <[email protected]>
2020-08-07 13:53 [PATCH 8/8] tweak costing for bloom/minmax-multi indexes Tomas Vondra <[email protected]>
2020-08-07 13:53 [PATCH 8/8] tweak costing for bloom/minmax-multi indexes Tomas Vondra <[email protected]>
2020-08-07 13:53 [PATCH 8/8] tweak costing for bloom/minmax-multi indexes Tomas Vondra <[email protected]>
2020-08-07 13:53 [PATCH 6/6] tweak costing for bloom/minmax-multi indexes Tomas Vondra <[email protected]>
2020-08-07 13:53 [PATCH 8/8] tweak costing for bloom/minmax-multi indexes Tomas Vondra <[email protected]>
2020-08-07 13:53 [PATCH 8/8] tweak costing for bloom/minmax-multi indexes Tomas Vondra <[email protected]>
2020-08-07 13:53 [PATCH 8/8] tweak costing for bloom/minmax-multi indexes Tomas Vondra <[email protected]>
2020-08-07 13:53 [PATCH 8/8] tweak costing for bloom/minmax-multi indexes Tomas Vondra <[email protected]>
2020-08-07 13:53 [PATCH 8/8] tweak costing for bloom/minmax-multi indexes Tomas Vondra <[email protected]>
2020-08-07 13:53 [PATCH 8/8] tweak costing for bloom/minmax-multi indexes Tomas Vondra <[email protected]>
2020-08-07 13:53 [PATCH 8/8] tweak costing for bloom/minmax-multi indexes Tomas Vondra <[email protected]>
2020-08-07 13:53 [PATCH 8/8] tweak costing for bloom/minmax-multi indexes Tomas Vondra <[email protected]>
2025-02-04 18:41 Re: Support for NO INHERIT to INHERIT state change with named NOT NULL constraints Alvaro Herrera <[email protected]>
2025-02-10 05:13 ` Re: Support for NO INHERIT to INHERIT state change with named NOT NULL constraints Suraj Kharage <[email protected]>
2025-02-19 15:46 ` Re: Support for NO INHERIT to INHERIT state change with named NOT NULL constraints Alvaro Herrera <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox