On 23/10/2018 19:48, Daniel Gustafsson wrote:
>> On 23 Oct 2018, at 15:17, Peter Eisentraut
>> <peter.eisentr...@2ndquadrant.com> wrote:
>>
>> I propose the attached patch to remove the long-unused catalog column
>> pg_attrdef.adsrc.
>
> +1, I ran into a bug in an app as recently as today where adsrc was used
> instead of pg_get_expr().
>
> Patch looks good. I probably would’ve opted for mentioning how to get a human
> readable version on the page, along the lines of the attached version,
Agreed. I have integrated your suggestion.
Also, let's go nuts and remove pg_constraint.consrc as well.
Updated patches attached.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From 8f433168a441fb21ad29a45dead842504eb257ad Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pete...@gmx.net>
Date: Sat, 27 Oct 2018 11:25:20 +0100
Subject: [PATCH v2 1/2] Remove obsolete pg_attrdef.adsrc column
This has been deprecated and effectively unused for a long time.
---
doc/src/sgml/catalogs.sgml | 20 +++-----------------
src/backend/catalog/heap.c | 12 ------------
src/include/catalog/pg_attrdef.h | 1 -
3 files changed, 3 insertions(+), 30 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 9edba96fab..08cc0ea062 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -947,27 +947,13 @@ <title><structname>pg_attrdef</structname> Columns</title>
<entry><structfield>adbin</structfield></entry>
<entry><type>pg_node_tree</type></entry>
<entry></entry>
- <entry>The internal representation of the column default value</entry>
- </row>
-
- <row>
- <entry><structfield>adsrc</structfield></entry>
- <entry><type>text</type></entry>
- <entry></entry>
- <entry>A human-readable representation of the default value</entry>
+ <entry>The column default value, in <function>nodeToString()</function>
+ representation. Use <literal>pg_get_expr(adbin, adrelid)</literal> to
+ convert it to an SQL expression.</entry>
</row>
</tbody>
</tgroup>
</table>
-
- <para>
- The <structfield>adsrc</structfield> field is historical, and is best
- not used, because it does not track outside changes that might affect
- the representation of the default value. Reverse-compiling the
- <structfield>adbin</structfield> field (with
<function>pg_get_expr</function> for
- example) is a better way to display the default value.
- </para>
-
</sect1>
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 3c9c03c997..640e283688 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -2152,7 +2152,6 @@ StoreAttrDefault(Relation rel, AttrNumber attnum,
Node *expr, bool is_internal, bool
add_column_mode)
{
char *adbin;
- char *adsrc;
Relation adrel;
HeapTuple tuple;
Datum values[4];
@@ -2169,21 +2168,12 @@ StoreAttrDefault(Relation rel, AttrNumber attnum,
*/
adbin = nodeToString(expr);
- /*
- * Also deparse it to form the mostly-obsolete adsrc field.
- */
- adsrc = deparse_expression(expr,
-
deparse_context_for(RelationGetRelationName(rel),
-
RelationGetRelid(rel)),
- false, false);
-
/*
* Make the pg_attrdef entry.
*/
values[Anum_pg_attrdef_adrelid - 1] = RelationGetRelid(rel);
values[Anum_pg_attrdef_adnum - 1] = attnum;
values[Anum_pg_attrdef_adbin - 1] = CStringGetTextDatum(adbin);
- values[Anum_pg_attrdef_adsrc - 1] = CStringGetTextDatum(adsrc);
adrel = heap_open(AttrDefaultRelationId, RowExclusiveLock);
@@ -2198,10 +2188,8 @@ StoreAttrDefault(Relation rel, AttrNumber attnum,
/* now can free some of the stuff allocated above */
pfree(DatumGetPointer(values[Anum_pg_attrdef_adbin - 1]));
- pfree(DatumGetPointer(values[Anum_pg_attrdef_adsrc - 1]));
heap_freetuple(tuple);
pfree(adbin);
- pfree(adsrc);
/*
* Update the pg_attribute entry for the column to show that a default
diff --git a/src/include/catalog/pg_attrdef.h b/src/include/catalog/pg_attrdef.h
index e4a37ec326..a9a2351efd 100644
--- a/src/include/catalog/pg_attrdef.h
+++ b/src/include/catalog/pg_attrdef.h
@@ -33,7 +33,6 @@ CATALOG(pg_attrdef,2604,AttrDefaultRelationId)
#ifdef CATALOG_VARLEN /* variable-length fields start here */
pg_node_tree adbin BKI_FORCE_NOT_NULL; /* nodeToString
representation of default */
- text adsrc BKI_FORCE_NOT_NULL; /*
human-readable representation of default */
#endif
} FormData_pg_attrdef;
base-commit: 5953c99697621174f50aa219a3cd457212968268
--
2.19.1
From de19c7b319416e384e1c514bb7f35804e0c616c4 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pete...@gmx.net>
Date: Sat, 27 Oct 2018 11:45:50 +0100
Subject: [PATCH v2 2/2] Remove obsolete pg_constraint.consrc column
This has been deprecated and effectively unused for a long time.
---
doc/src/sgml/catalogs.sgml | 21 ++++-----------------
src/backend/catalog/heap.c | 11 -----------
src/backend/catalog/index.c | 1 -
src/backend/catalog/pg_constraint.c | 13 -------------
src/backend/commands/tablecmds.c | 1 -
src/backend/commands/trigger.c | 1 -
src/backend/commands/typecmds.c | 8 --------
src/include/catalog/pg_constraint.h | 6 ------
src/test/regress/expected/inherit.out | 20 ++++++++++----------
src/test/regress/sql/inherit.sql | 20 ++++++++++----------
10 files changed, 24 insertions(+), 78 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 08cc0ea062..7f2981ac85 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -2400,14 +2400,10 @@ <title><structname>pg_constraint</structname>
Columns</title>
<entry><structfield>conbin</structfield></entry>
<entry><type>pg_node_tree</type></entry>
<entry></entry>
- <entry>If a check constraint, an internal representation of the
expression</entry>
- </row>
-
- <row>
- <entry><structfield>consrc</structfield></entry>
- <entry><type>text</type></entry>
- <entry></entry>
- <entry>If a check constraint, a human-readable representation of the
expression</entry>
+ <entry>If a check constraint, an internal representation of the
+ expression. (It's recommended to use
+ <function>pg_get_constraintdef()</function> to extract the definition of
+ a check constraint.)</entry>
</row>
</tbody>
</tgroup>
@@ -2423,15 +2419,6 @@ <title><structname>pg_constraint</structname>
Columns</title>
index.)
</para>
- <note>
- <para>
- <structfield>consrc</structfield> is not updated when referenced objects
- change; for example, it won't track renaming of columns. Rather than
- relying on this field, it's best to use
<function>pg_get_constraintdef()</function>
- to extract the definition of a check constraint.
- </para>
- </note>
-
<note>
<para>
<literal>pg_class.relchecks</literal> needs to agree with the
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 640e283688..064cf9dbf6 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -2315,7 +2315,6 @@ StoreRelCheck(Relation rel, const char *ccname, Node
*expr,
bool is_no_inherit, bool is_internal)
{
char *ccbin;
- char *ccsrc;
List *varList;
int keycount;
int16 *attNos;
@@ -2326,14 +2325,6 @@ StoreRelCheck(Relation rel, const char *ccname, Node
*expr,
*/
ccbin = nodeToString(expr);
- /*
- * Also deparse it to form the mostly-obsolete consrc field.
- */
- ccsrc = deparse_expression(expr,
-
deparse_context_for(RelationGetRelationName(rel),
-
RelationGetRelid(rel)),
- false, false);
-
/*
* Find columns of rel that are used in expr
*
@@ -2406,14 +2397,12 @@ StoreRelCheck(Relation rel, const char *ccname, Node
*expr,
NULL, /* not an
exclusion constraint */
expr, /* Tree form of
check constraint */
ccbin, /*
Binary form of check constraint */
- ccsrc, /*
Source form of check constraint */
is_local, /*
conislocal */
inhcount, /*
coninhcount */
is_no_inherit,
/* connoinherit */
is_internal); /*
internally constructed? */
pfree(ccbin);
- pfree(ccsrc);
return constrOid;
}
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 5885899c9b..4088286151 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -1336,7 +1336,6 @@ index_constraint_create(Relation heapRelation,
indexInfo->ii_ExclusionOps,
NULL,
/* no check constraint */
NULL,
- NULL,
islocal,
inhcount,
noinherit,
diff --git a/src/backend/catalog/pg_constraint.c
b/src/backend/catalog/pg_constraint.c
index f4057a9f15..1c235b4b29 100644
--- a/src/backend/catalog/pg_constraint.c
+++ b/src/backend/catalog/pg_constraint.c
@@ -78,7 +78,6 @@ CreateConstraintEntry(const char *constraintName,
const Oid *exclOp,
Node *conExpr,
const char *conBin,
- const char *conSrc,
bool conIsLocal,
int conInhCount,
bool conNoInherit,
@@ -218,22 +217,11 @@ CreateConstraintEntry(const char *constraintName,
else
nulls[Anum_pg_constraint_conexclop - 1] = true;
- /*
- * initialize the binary form of the check constraint.
- */
if (conBin)
values[Anum_pg_constraint_conbin - 1] =
CStringGetTextDatum(conBin);
else
nulls[Anum_pg_constraint_conbin - 1] = true;
- /*
- * initialize the text form of the check constraint
- */
- if (conSrc)
- values[Anum_pg_constraint_consrc - 1] =
CStringGetTextDatum(conSrc);
- else
- nulls[Anum_pg_constraint_consrc - 1] = true;
-
tup = heap_form_tuple(RelationGetDescr(conDesc), values, nulls);
conOid = CatalogTupleInsert(conDesc, tup);
@@ -703,7 +691,6 @@ clone_fk_constraints(Relation pg_constraint, Relation
parentRel,
NULL,
NULL,
NULL,
- NULL,
false,
1, false,
true);
subclone = lappend_oid(subclone, constrOid);
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 153aec263e..ba76c3f9b9 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -7704,7 +7704,6 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo
*tab, Relation rel,
NULL,
/* no exclusion constraint */
NULL,
/* no check constraint */
NULL,
- NULL,
true,
/* islocal */
0,
/* inhcount */
true,
/* isnoinherit */
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index 240e85e391..0bd847cd19 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -752,7 +752,6 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
NULL, /* no exclusion */
NULL, /* no check constraint */
NULL,
-
NULL,
true, /* islocal */
0, /* inhcount */
true, /* isnoinherit */
diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c
index 3271962a7a..12388004d9 100644
--- a/src/backend/commands/typecmds.c
+++ b/src/backend/commands/typecmds.c
@@ -3042,7 +3042,6 @@ domainAddConstraint(Oid domainOid, Oid domainNamespace,
Oid baseTypeOid,
const char *domainName, ObjectAddress
*constrAddr)
{
Node *expr;
- char *ccsrc;
char *ccbin;
ParseState *pstate;
CoerceToDomainValue *domVal;
@@ -3116,12 +3115,6 @@ domainAddConstraint(Oid domainOid, Oid domainNamespace,
Oid baseTypeOid,
*/
ccbin = nodeToString(expr);
- /*
- * Deparse it to produce text for consrc.
- */
- ccsrc = deparse_expression(expr,
- NIL, false, false);
-
/*
* Store the constraint in pg_constraint
*/
@@ -3151,7 +3144,6 @@ domainAddConstraint(Oid domainOid, Oid domainNamespace,
Oid baseTypeOid,
NULL, /* not an
exclusion constraint */
expr, /* Tree form of
check constraint */
ccbin, /*
Binary form of check constraint */
- ccsrc, /*
Source form of check constraint */
true, /* is local */
0, /* inhcount */
false, /*
connoinherit */
diff --git a/src/include/catalog/pg_constraint.h
b/src/include/catalog/pg_constraint.h
index 66b3f13f74..630cabe0b8 100644
--- a/src/include/catalog/pg_constraint.h
+++ b/src/include/catalog/pg_constraint.h
@@ -142,11 +142,6 @@ CATALOG(pg_constraint,2606,ConstraintRelationId)
* If a check constraint, nodeToString representation of expression
*/
pg_node_tree conbin;
-
- /*
- * If a check constraint, source-text representation of expression
- */
- text consrc;
#endif
} FormData_pg_constraint;
@@ -224,7 +219,6 @@ extern Oid CreateConstraintEntry(const char *constraintName,
const Oid *exclOp,
Node *conExpr,
const char *conBin,
- const char *conSrc,
bool conIsLocal,
int conInhCount,
bool conNoInherit,
diff --git a/src/test/regress/expected/inherit.out
b/src/test/regress/expected/inherit.out
index 4f29d9f891..d768e5df2c 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -797,7 +797,7 @@ drop table p1;
CREATE TABLE ac (aa TEXT);
alter table ac add constraint ac_check check (aa is not null);
CREATE TABLE bc (bb TEXT) INHERITS (ac);
-select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount,
pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid
= pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount,
pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join
pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac',
'bc') order by 1,2;
relname | conname | contype | conislocal | coninhcount | consrc
---------+----------+---------+------------+-------------+------------------
ac | ac_check | c | t | 0 | (aa IS NOT NULL)
@@ -813,14 +813,14 @@ DETAIL: Failing row contains (null, null).
alter table bc drop constraint ac_check; -- fail, disallowed
ERROR: cannot drop inherited constraint "ac_check" of relation "bc"
alter table ac drop constraint ac_check;
-select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount,
pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid
= pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount,
pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join
pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac',
'bc') order by 1,2;
relname | conname | contype | conislocal | coninhcount | consrc
---------+---------+---------+------------+-------------+--------
(0 rows)
-- try the unnamed-constraint case
alter table ac add check (aa is not null);
-select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount,
pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid
= pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount,
pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join
pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac',
'bc') order by 1,2;
relname | conname | contype | conislocal | coninhcount | consrc
---------+-------------+---------+------------+-------------+------------------
ac | ac_aa_check | c | t | 0 | (aa IS NOT NULL)
@@ -836,14 +836,14 @@ DETAIL: Failing row contains (null, null).
alter table bc drop constraint ac_aa_check; -- fail, disallowed
ERROR: cannot drop inherited constraint "ac_aa_check" of relation "bc"
alter table ac drop constraint ac_aa_check;
-select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount,
pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid
= pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount,
pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join
pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac',
'bc') order by 1,2;
relname | conname | contype | conislocal | coninhcount | consrc
---------+---------+---------+------------+-------------+--------
(0 rows)
alter table ac add constraint ac_check check (aa is not null);
alter table bc no inherit ac;
-select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount,
pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid
= pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount,
pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join
pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac',
'bc') order by 1,2;
relname | conname | contype | conislocal | coninhcount | consrc
---------+----------+---------+------------+-------------+------------------
ac | ac_check | c | t | 0 | (aa IS NOT NULL)
@@ -851,14 +851,14 @@ select pc.relname, pgc.conname, pgc.contype,
pgc.conislocal, pgc.coninhcount, pg
(2 rows)
alter table bc drop constraint ac_check;
-select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount,
pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid
= pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount,
pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join
pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac',
'bc') order by 1,2;
relname | conname | contype | conislocal | coninhcount | consrc
---------+----------+---------+------------+-------------+------------------
ac | ac_check | c | t | 0 | (aa IS NOT NULL)
(1 row)
alter table ac drop constraint ac_check;
-select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount,
pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid
= pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount,
pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join
pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac',
'bc') order by 1,2;
relname | conname | contype | conislocal | coninhcount | consrc
---------+---------+---------+------------+-------------+--------
(0 rows)
@@ -869,7 +869,7 @@ create table ac (a int constraint check_a check (a <> 0));
create table bc (a int constraint check_a check (a <> 0), b int constraint
check_b check (b <> 0)) inherits (ac);
NOTICE: merging column "a" with inherited definition
NOTICE: merging constraint "check_a" with inherited definition
-select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount,
pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid
= pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount,
pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join
pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac',
'bc') order by 1,2;
relname | conname | contype | conislocal | coninhcount | consrc
---------+---------+---------+------------+-------------+----------
ac | check_a | c | t | 0 | (a <> 0)
@@ -882,7 +882,7 @@ drop table ac;
create table ac (a int constraint check_a check (a <> 0));
create table bc (b int constraint check_b check (b <> 0));
create table cc (c int constraint check_c check (c <> 0)) inherits (ac, bc);
-select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount,
pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid
= pc.oid) where pc.relname in ('ac', 'bc', 'cc') order by 1,2;
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount,
pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join
pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac',
'bc', 'cc') order by 1,2;
relname | conname | contype | conislocal | coninhcount | consrc
---------+---------+---------+------------+-------------+----------
ac | check_a | c | t | 0 | (a <> 0)
@@ -893,7 +893,7 @@ select pc.relname, pgc.conname, pgc.contype,
pgc.conislocal, pgc.coninhcount, pg
(5 rows)
alter table cc no inherit bc;
-select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount,
pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid
= pc.oid) where pc.relname in ('ac', 'bc', 'cc') order by 1,2;
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount,
pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join
pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac',
'bc', 'cc') order by 1,2;
relname | conname | contype | conislocal | coninhcount | consrc
---------+---------+---------+------------+-------------+----------
ac | check_a | c | t | 0 | (a <> 0)
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index a6e541d4da..e8b6448f3c 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -257,40 +257,40 @@ CREATE TABLE otherchild (tomorrow date default now())
CREATE TABLE ac (aa TEXT);
alter table ac add constraint ac_check check (aa is not null);
CREATE TABLE bc (bb TEXT) INHERITS (ac);
-select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount,
pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid
= pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount,
pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join
pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac',
'bc') order by 1,2;
insert into ac (aa) values (NULL);
insert into bc (aa) values (NULL);
alter table bc drop constraint ac_check; -- fail, disallowed
alter table ac drop constraint ac_check;
-select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount,
pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid
= pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount,
pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join
pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac',
'bc') order by 1,2;
-- try the unnamed-constraint case
alter table ac add check (aa is not null);
-select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount,
pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid
= pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount,
pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join
pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac',
'bc') order by 1,2;
insert into ac (aa) values (NULL);
insert into bc (aa) values (NULL);
alter table bc drop constraint ac_aa_check; -- fail, disallowed
alter table ac drop constraint ac_aa_check;
-select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount,
pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid
= pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount,
pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join
pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac',
'bc') order by 1,2;
alter table ac add constraint ac_check check (aa is not null);
alter table bc no inherit ac;
-select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount,
pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid
= pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount,
pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join
pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac',
'bc') order by 1,2;
alter table bc drop constraint ac_check;
-select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount,
pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid
= pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount,
pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join
pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac',
'bc') order by 1,2;
alter table ac drop constraint ac_check;
-select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount,
pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid
= pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount,
pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join
pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac',
'bc') order by 1,2;
drop table bc;
drop table ac;
create table ac (a int constraint check_a check (a <> 0));
create table bc (a int constraint check_a check (a <> 0), b int constraint
check_b check (b <> 0)) inherits (ac);
-select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount,
pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid
= pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount,
pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join
pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac',
'bc') order by 1,2;
drop table bc;
drop table ac;
@@ -298,10 +298,10 @@ CREATE TABLE bc (bb TEXT) INHERITS (ac);
create table ac (a int constraint check_a check (a <> 0));
create table bc (b int constraint check_b check (b <> 0));
create table cc (c int constraint check_c check (c <> 0)) inherits (ac, bc);
-select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount,
pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid
= pc.oid) where pc.relname in ('ac', 'bc', 'cc') order by 1,2;
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount,
pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join
pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac',
'bc', 'cc') order by 1,2;
alter table cc no inherit bc;
-select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount,
pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid
= pc.oid) where pc.relname in ('ac', 'bc', 'cc') order by 1,2;
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount,
pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join
pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac',
'bc', 'cc') order by 1,2;
drop table cc;
drop table bc;
--
2.19.1
