Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitd832611

Browse files
committed
Fix my oversight in enabling domains-of-domains: ALTER DOMAIN ADD CONSTRAINT
needs to check the new constraint against columns of derived domains too.Also, make it error out if the domain to be modified is used within anycomposite-type columns. Eventually we should support that case, but it seemsa bit painful, and not suitable for a back-patch. For the moment just let theuser know we can't do it.Backpatch to 8.2, which is the only released version that allows nesteddomains. Possibly the other part should be back-patched further.
1 parent3b6afdd commitd832611

File tree

6 files changed

+143
-23
lines changed

6 files changed

+143
-23
lines changed

‎doc/src/sgml/ref/alter_domain.sgml

Lines changed: 14 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/alter_domain.sgml,v 1.21 2007/01/31 23:26:02 momjian Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/alter_domain.sgml,v 1.22 2007/05/11 20:16:32 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -197,6 +197,19 @@ ALTER DOMAIN <replaceable class="PARAMETER">name</replaceable>
197197
</para>
198198
</refsect1>
199199

200+
<refsect1>
201+
<title>Notes</title>
202+
203+
<para>
204+
Currently, <command>ALTER DOMAIN ADD CONSTRAINT</> and
205+
<command>ALTER DOMAIN SET NOT NULL</> will fail if the named domain or
206+
any derived domain is used within a composite-type column of any
207+
table in the database. They should eventually be improved to be
208+
able to verify the new constraint for such nested columns.
209+
</para>
210+
211+
</refsect1>
212+
200213
<refsect1>
201214
<title>Examples</title>
202215

‎src/backend/commands/tablecmds.c

Lines changed: 27 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/commands/tablecmds.c,v 1.220 2007/05/1117:57:12 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/commands/tablecmds.c,v 1.221 2007/05/1120:16:36 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -206,8 +206,6 @@ static void ATSimpleRecursion(List **wqueue, Relation rel,
206206
AlterTableCmd*cmd,boolrecurse);
207207
staticvoidATOneLevelRecursion(List**wqueue,Relationrel,
208208
AlterTableCmd*cmd);
209-
staticvoidfind_composite_type_dependencies(OidtypeOid,
210-
constchar*origTblName);
211209
staticvoidATPrepAddColumn(List**wqueue,Relationrel,boolrecurse,
212210
AlterTableCmd*cmd);
213211
staticvoidATExecAddColumn(AlteredTableInfo*tab,Relationrel,
@@ -2410,7 +2408,8 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap)
24102408
*/
24112409
if (newrel)
24122410
find_composite_type_dependencies(oldrel->rd_rel->reltype,
2413-
RelationGetRelationName(oldrel));
2411+
RelationGetRelationName(oldrel),
2412+
NULL);
24142413

24152414
/*
24162415
* Generate the constraint and default execution states
@@ -2812,16 +2811,21 @@ ATOneLevelRecursion(List **wqueue, Relation rel,
28122811
/*
28132812
* find_composite_type_dependencies
28142813
*
2815-
* Check to see if atable's rowtype is being used as a column in some
2814+
* Check to see if acomposite type is being used as a column in some
28162815
* other table (possibly nested several levels deep in composite types!).
28172816
* Eventually, we'd like to propagate the check or rewrite operation
28182817
* into other such tables, but for now, just error out if we find any.
28192818
*
2819+
* Caller should provide either a table name or a type name (not both) to
2820+
* report in the error message, if any.
2821+
*
28202822
* We assume that functions and views depending on the type are not reasons
28212823
* to reject the ALTER. (How safe is this really?)
28222824
*/
2823-
staticvoid
2824-
find_composite_type_dependencies(OidtypeOid,constchar*origTblName)
2825+
void
2826+
find_composite_type_dependencies(OidtypeOid,
2827+
constchar*origTblName,
2828+
constchar*origTypeName)
28252829
{
28262830
RelationdepRel;
28272831
ScanKeyDatakey[2];
@@ -2864,12 +2868,20 @@ find_composite_type_dependencies(Oid typeOid, const char *origTblName)
28642868

28652869
if (rel->rd_rel->relkind==RELKIND_RELATION)
28662870
{
2867-
ereport(ERROR,
2868-
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
2869-
errmsg("cannot alter table \"%s\" because column \"%s\".\"%s\" uses its rowtype",
2870-
origTblName,
2871-
RelationGetRelationName(rel),
2872-
NameStr(att->attname))));
2871+
if (origTblName)
2872+
ereport(ERROR,
2873+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
2874+
errmsg("cannot alter table \"%s\" because column \"%s\".\"%s\" uses its rowtype",
2875+
origTblName,
2876+
RelationGetRelationName(rel),
2877+
NameStr(att->attname))));
2878+
else
2879+
ereport(ERROR,
2880+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
2881+
errmsg("cannot alter type \"%s\" because column \"%s\".\"%s\" uses it",
2882+
origTypeName,
2883+
RelationGetRelationName(rel),
2884+
NameStr(att->attname))));
28732885
}
28742886
elseif (OidIsValid(rel->rd_rel->reltype))
28752887
{
@@ -2878,7 +2890,7 @@ find_composite_type_dependencies(Oid typeOid, const char *origTblName)
28782890
* recursively check for indirect dependencies via its rowtype.
28792891
*/
28802892
find_composite_type_dependencies(rel->rd_rel->reltype,
2881-
origTblName);
2893+
origTblName,origTypeName);
28822894
}
28832895

28842896
relation_close(rel,AccessShareLock);
@@ -2894,7 +2906,7 @@ find_composite_type_dependencies(Oid typeOid, const char *origTblName)
28942906
*/
28952907
arrayOid=get_array_type(typeOid);
28962908
if (OidIsValid(arrayOid))
2897-
find_composite_type_dependencies(arrayOid,origTblName);
2909+
find_composite_type_dependencies(arrayOid,origTblName,origTypeName);
28982910
}
28992911

29002912

‎src/backend/commands/typecmds.c

Lines changed: 37 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/commands/typecmds.c,v 1.102 2007/05/1117:57:12 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/commands/typecmds.c,v 1.103 2007/05/1120:16:54 tgl Exp $
1212
*
1313
* DESCRIPTION
1414
* The "DefineFoo" routines take the parse tree and pick out the
@@ -1805,6 +1805,10 @@ AlterDomainAddConstraint(List *names, Node *newConstraint)
18051805
* the domain type. We have opened each rel and acquired the specified lock
18061806
* type on it.
18071807
*
1808+
* We support nested domains by including attributes that are of derived
1809+
* domain types. Current callers do not need to distinguish between attributes
1810+
* that are of exactly the given domain and those that are of derived domains.
1811+
*
18081812
* XXX this is completely broken because there is no way to lock the domain
18091813
* to prevent columns from being added or dropped while our command runs.
18101814
* We can partially protect against column drops by locking relations as we
@@ -1814,9 +1818,11 @@ AlterDomainAddConstraint(List *names, Node *newConstraint)
18141818
* trivial risk of deadlock. We can minimize but not eliminate the deadlock
18151819
* risk by using the weakest suitable lock (ShareLock for most callers).
18161820
*
1817-
* XXX to support domains over domains, we'd need to make this smarter,
1818-
* or make its callers smarter, so that we could find columns of derived
1819-
* domains. Arrays of domains would be a problem too.
1821+
* XXX the API for this is not sufficient to support checking domain values
1822+
* that are inside composite types or arrays. Currently we just error out
1823+
* if a composite type containing the target domain is stored anywhere.
1824+
* There are not currently arrays of domains; if there were, we could take
1825+
* the same approach, but it'd be nicer to fix it properly.
18201826
*
18211827
* Generally used for retrieving a list of tests when adding
18221828
* new constraints to a domain.
@@ -1858,7 +1864,23 @@ get_rels_with_domain(Oid domainOid, LOCKMODE lockmode)
18581864
Form_pg_attributepg_att;
18591865
intptr;
18601866

1861-
/* Ignore dependees that aren't user columns of relations */
1867+
/* Check for directly dependent types --- must be domains */
1868+
if (pg_depend->classid==TypeRelationId)
1869+
{
1870+
Assert(get_typtype(pg_depend->objid)==TYPTYPE_DOMAIN);
1871+
/*
1872+
* Recursively add dependent columns to the output list. This
1873+
* is a bit inefficient since we may fail to combine RelToCheck
1874+
* entries when attributes of the same rel have different derived
1875+
* domain types, but it's probably not worth improving.
1876+
*/
1877+
result=list_concat(result,
1878+
get_rels_with_domain(pg_depend->objid,
1879+
lockmode));
1880+
continue;
1881+
}
1882+
1883+
/* Else, ignore dependees that aren't user columns of relations */
18621884
/* (we assume system columns are never of domain types) */
18631885
if (pg_depend->classid!=RelationRelationId||
18641886
pg_depend->objsubid <=0)
@@ -1884,7 +1906,16 @@ get_rels_with_domain(Oid domainOid, LOCKMODE lockmode)
18841906
/* Acquire requested lock on relation */
18851907
rel=relation_open(pg_depend->objid,lockmode);
18861908

1887-
/* It could be a view or composite type; if so ignore it */
1909+
/*
1910+
* Check to see if rowtype is stored anyplace as a composite-type
1911+
* column; if so we have to fail, for now anyway.
1912+
*/
1913+
if (OidIsValid(rel->rd_rel->reltype))
1914+
find_composite_type_dependencies(rel->rd_rel->reltype,
1915+
NULL,
1916+
format_type_be(domainOid));
1917+
1918+
/* Otherwise we can ignore views, composite types, etc */
18881919
if (rel->rd_rel->relkind!=RELKIND_RELATION)
18891920
{
18901921
relation_close(rel,lockmode);

‎src/include/commands/tablecmds.h

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/commands/tablecmds.h,v 1.32 2007/01/05 22:19:54 momjian Exp $
10+
* $PostgreSQL: pgsql/src/include/commands/tablecmds.h,v 1.33 2007/05/11 20:17:10 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -45,6 +45,10 @@ extern void renameatt(Oid myrelid,
4545
externvoidrenamerel(Oidmyrelid,
4646
constchar*newrelname);
4747

48+
externvoidfind_composite_type_dependencies(OidtypeOid,
49+
constchar*origTblName,
50+
constchar*origTypeName);
51+
4852
externAttrNumber*varattnos_map(TupleDescold,TupleDescnew);
4953
externAttrNumber*varattnos_map_schema(TupleDescold,List*schema);
5054
externvoidchange_varattnos_of_a_node(Node*node,constAttrNumber*newattno);

‎src/test/regress/expected/domain.out

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -438,3 +438,32 @@ select doubledecrement(3); -- good
438438
1
439439
(1 row)
440440

441+
-- Check that ALTER DOMAIN tests columns of derived types
442+
create domain posint as int4;
443+
-- Currently, this doesn't work for composite types, but verify it complains
444+
create type ddtest1 as (f1 posint);
445+
create table ddtest2(f1 ddtest1);
446+
insert into ddtest2 values(row(-1));
447+
alter domain posint add constraint c1 check(value >= 0);
448+
ERROR: cannot alter type "posint" because column "ddtest2"."f1" uses it
449+
drop table ddtest2;
450+
create table ddtest2(f1 ddtest1[]);
451+
insert into ddtest2 values('{(-1)}');
452+
alter domain posint add constraint c1 check(value >= 0);
453+
ERROR: cannot alter type "posint" because column "ddtest2"."f1" uses it
454+
drop table ddtest2;
455+
alter domain posint add constraint c1 check(value >= 0);
456+
create domain posint2 as posint check (value % 2 = 0);
457+
create table ddtest2(f1 posint2);
458+
insert into ddtest2 values(11); -- fail
459+
ERROR: value for domain posint2 violates check constraint "posint2_check"
460+
insert into ddtest2 values(-2); -- fail
461+
ERROR: value for domain posint2 violates check constraint "c1"
462+
insert into ddtest2 values(2);
463+
alter domain posint add constraint c2 check(value >= 10); -- fail
464+
ERROR: column "f1" of table "ddtest2" contains values that violate the new constraint
465+
alter domain posint add constraint c2 check(value > 0); -- OK
466+
drop table ddtest2;
467+
drop type ddtest1;
468+
drop domain posint cascade;
469+
NOTICE: drop cascades to type posint2

‎src/test/regress/sql/domain.sql

Lines changed: 31 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -351,3 +351,34 @@ select doubledecrement(0); -- fail before call
351351
select doubledecrement(1);-- fail at assignment to v
352352
select doubledecrement(2);-- fail at return
353353
select doubledecrement(3);-- good
354+
355+
-- Check that ALTER DOMAIN tests columns of derived types
356+
357+
createdomainposintas int4;
358+
359+
-- Currently, this doesn't work for composite types, but verify it complains
360+
createtypeddtest1as (f1 posint);
361+
createtableddtest2(f1 ddtest1);
362+
insert into ddtest2values(row(-1));
363+
alterdomain posint addconstraint c1check(value>=0);
364+
droptable ddtest2;
365+
366+
createtableddtest2(f1 ddtest1[]);
367+
insert into ddtest2values('{(-1)}');
368+
alterdomain posint addconstraint c1check(value>=0);
369+
droptable ddtest2;
370+
371+
alterdomain posint addconstraint c1check(value>=0);
372+
373+
createdomainposint2as posintcheck (value %2=0);
374+
createtableddtest2(f1 posint2);
375+
insert into ddtest2values(11);-- fail
376+
insert into ddtest2values(-2);-- fail
377+
insert into ddtest2values(2);
378+
379+
alterdomain posint addconstraint c2check(value>=10);-- fail
380+
alterdomain posint addconstraint c2check(value>0);-- OK
381+
382+
droptable ddtest2;
383+
droptype ddtest1;
384+
dropdomain posint cascade;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp