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

Commitdfb75e4

Browse files
committed
Add primary keys and unique constraints to system catalogs
For those system catalogs that have a unique indexes, make a primarykey and unique constraint, using ALTER TABLE ... PRIMARY KEY/UNIQUEUSING INDEX.This can be helpful for GUI tools that look for a primary key, and itmight in the future allow declaring foreign keys, for making schemadiagrams.The constraint creation statements are automatically created bygenbki.pl from DECLARE_UNIQUE_INDEX directives. To specify which oneof the available unique indexes is the primary key, use the newdirective DECLARE_UNIQUE_INDEX_PKEY instead. By convention, weusually make a catalog's OID column its primary key, if it has one.Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>Discussion:https://www.postgresql.org/message-id/flat/dc5f44d9-5ec1-a596-0251-dadadcdede98@2ndquadrant.com
1 parent6533062 commitdfb75e4

File tree

71 files changed

+177
-126
lines changed

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

71 files changed

+177
-126
lines changed

‎src/backend/catalog/.gitignore‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,5 @@
11
/postgres.bki
22
/schemapg.h
3+
/system_constraints.sql
34
/pg_*_d.h
45
/bki-stamp

‎src/backend/catalog/Catalog.pm‎

Lines changed: 6 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -94,14 +94,15 @@ sub ParseHeader
9494
push @{$catalog{toasting} },
9595
{parent_table=>$1,toast_oid=>$2,toast_index_oid=>$3 };
9696
}
97-
elsif (/^DECLARE_(UNIQUE_)?INDEX\(\s*(\w+),\s*(\d+),\s*(.+)\)/)
97+
elsif (/^DECLARE_(UNIQUE_)?INDEX(_PKEY)?\(\s*(\w+),\s*(\d+),\s*(.+)\)/)
9898
{
9999
push @{$catalog{indexing} },
100-
{
100+
{
101101
is_unique=>$1 ? 1 : 0,
102-
index_name=>$2,
103-
index_oid=>$3,
104-
index_decl=>$4
102+
is_pkey=>$2 ? 1 : 0,
103+
index_name=>$3,
104+
index_oid=>$4,
105+
index_decl=>$5
105106
};
106107
}
107108
elsif (/^CATALOG\((\w+),(\d+),(\w+)\)/)

‎src/backend/catalog/Makefile‎

Lines changed: 5 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -121,6 +121,7 @@ $(top_builddir)/src/include/catalog/header-stamp: bki-stamp
121121
.PHONY: install-data
122122
install-data: bki-stamp installdirs
123123
$(INSTALL_DATA)$(call vpathsearch,postgres.bki)'$(DESTDIR)$(datadir)/postgres.bki'
124+
$(INSTALL_DATA)$(call vpathsearch,system_constraints.sql)'$(DESTDIR)$(datadir)/system_constraints.sql'
124125
$(INSTALL_DATA)$(srcdir)/system_views.sql'$(DESTDIR)$(datadir)/system_views.sql'
125126
$(INSTALL_DATA)$(srcdir)/information_schema.sql'$(DESTDIR)$(datadir)/information_schema.sql'
126127
$(INSTALL_DATA)$(srcdir)/sql_features.txt'$(DESTDIR)$(datadir)/sql_features.txt'
@@ -130,11 +131,11 @@ installdirs:
130131

131132
.PHONY: uninstall-data
132133
uninstall-data:
133-
rm -f$(addprefix '$(DESTDIR)$(datadir)'/, postgres.bki system_views.sql information_schema.sql sql_features.txt)
134+
rm -f$(addprefix '$(DESTDIR)$(datadir)'/, postgres.bkisystem_constraints.sqlsystem_views.sql information_schema.sql sql_features.txt)
134135

135-
# postgres.bkiand the generated headers are in the distribution tarball,
136-
# so they are not cleaned here.
136+
# postgres.bki, system_constraints.sql,and the generated headers are
137+
#in the distribution tarball,so they are not cleaned here.
137138
clean:
138139

139140
maintainer-clean: clean
140-
rm -f bki-stamp postgres.bki$(GENERATED_HEADERS)
141+
rm -f bki-stamp postgres.bkisystem_constraints.sql$(GENERATED_HEADERS)

‎src/backend/catalog/genbki.pl‎

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -55,6 +55,7 @@
5555
my@toast_decls;
5656
my@index_decls;
5757
my%oidcounts;
58+
my@system_constraints;
5859

5960
foreachmy$header (@ARGV)
6061
{
@@ -137,6 +138,17 @@
137138
$index->{index_name},$index->{index_oid},
138139
$index->{index_decl};
139140
$oidcounts{$index->{index_oid} }++;
141+
142+
if ($index->{is_unique})
143+
{
144+
$index->{index_decl} =~/on (\w+) using/;
145+
my$tblname =$1;
146+
push@system_constraints,
147+
sprintf"ALTER TABLE%s ADD%s USING INDEX%s;",
148+
$tblname,
149+
$index->{is_pkey} ?"PRIMARY KEY" :"UNIQUE",
150+
$index->{index_name};
151+
}
140152
}
141153
}
142154

@@ -388,6 +400,9 @@
388400
my$schemafile =$output_path . 'schemapg.h';
389401
open my$schemapg, '>',$schemafile .$tmpext
390402
or die "can't open$schemafile$tmpext:$!";
403+
my$constraints_file =$output_path . 'system_constraints.sql';
404+
open my$constraints, '>',$constraints_file .$tmpext
405+
or die "can't open$constraints_file$tmpext:$!";
391406
392407
# Generate postgres.bki and pg_*_d.h headers.
393408
@@ -648,6 +663,12 @@
648663
"genbki OID counter reached$GenbkiNextOid, overrunning FirstBootstrapObjectId\n"
649664
if$GenbkiNextOid >$FirstBootstrapObjectId;
650665

666+
# Now generate system_constraints.sql
667+
668+
foreachmy$c (@system_constraints)
669+
{
670+
print$constraints$c,"\n";
671+
}
651672

652673
# Now generate schemapg.h
653674

@@ -688,10 +709,12 @@
688709
# We're done emitting data
689710
close$bki;
690711
close$schemapg;
712+
close$constraints;
691713

692714
# Finally, rename the completed files into place.
693715
Catalog::RenameTempFile($bkifile,$tmpext);
694716
Catalog::RenameTempFile($schemafile,$tmpext);
717+
Catalog::RenameTempFile($constraints_file,$tmpext);
695718

696719
exit 0;
697720

‎src/bin/initdb/initdb.c‎

Lines changed: 14 additions & 46 deletions
Original file line numberDiff line numberDiff line change
@@ -159,6 +159,7 @@ static char *conf_file;
159159
staticchar*dictionary_file;
160160
staticchar*info_schema_file;
161161
staticchar*features_file;
162+
staticchar*system_constraints_file;
162163
staticchar*system_views_file;
163164
staticboolsuccess= false;
164165
staticboolmade_new_pgdata= false;
@@ -251,10 +252,9 @@ static void bootstrap_template1(void);
251252
staticvoidsetup_auth(FILE*cmdfd);
252253
staticvoidget_su_pwd(void);
253254
staticvoidsetup_depend(FILE*cmdfd);
254-
staticvoidsetup_sysviews(FILE*cmdfd);
255+
staticvoidsetup_run_file(FILE*cmdfd,constchar*filename);
255256
staticvoidsetup_description(FILE*cmdfd);
256257
staticvoidsetup_collation(FILE*cmdfd);
257-
staticvoidsetup_dictionary(FILE*cmdfd);
258258
staticvoidsetup_privileges(FILE*cmdfd);
259259
staticvoidset_info_version(void);
260260
staticvoidsetup_schema(FILE*cmdfd);
@@ -1600,25 +1600,24 @@ setup_depend(FILE *cmdfd)
16001600
}
16011601

16021602
/*
1603-
*set up system views
1603+
*Run external file
16041604
*/
16051605
staticvoid
1606-
setup_sysviews(FILE*cmdfd)
1606+
setup_run_file(FILE*cmdfd,constchar*filename)
16071607
{
1608-
char**line;
1609-
char**sysviews_setup;
1608+
char**lines;
16101609

1611-
sysviews_setup=readfile(system_views_file);
1610+
lines=readfile(filename);
16121611

1613-
for (line=sysviews_setup;*line!=NULL;line++)
1612+
for (char**line=lines;*line!=NULL;line++)
16141613
{
16151614
PG_CMD_PUTS(*line);
16161615
free(*line);
16171616
}
16181617

16191618
PG_CMD_PUTS("\n\n");
16201619

1621-
free(sysviews_setup);
1620+
free(lines);
16221621
}
16231622

16241623
/*
@@ -1661,27 +1660,6 @@ setup_collation(FILE *cmdfd)
16611660
PG_CMD_PUTS("SELECT pg_import_system_collations('pg_catalog');\n\n");
16621661
}
16631662

1664-
/*
1665-
* load extra dictionaries (Snowball stemmers)
1666-
*/
1667-
staticvoid
1668-
setup_dictionary(FILE*cmdfd)
1669-
{
1670-
char**line;
1671-
char**conv_lines;
1672-
1673-
conv_lines=readfile(dictionary_file);
1674-
for (line=conv_lines;*line!=NULL;line++)
1675-
{
1676-
PG_CMD_PUTS(*line);
1677-
free(*line);
1678-
}
1679-
1680-
PG_CMD_PUTS("\n\n");
1681-
1682-
free(conv_lines);
1683-
}
1684-
16851663
/*
16861664
* Set up privileges
16871665
*
@@ -1882,20 +1860,7 @@ set_info_version(void)
18821860
staticvoid
18831861
setup_schema(FILE*cmdfd)
18841862
{
1885-
char**line;
1886-
char**lines;
1887-
1888-
lines=readfile(info_schema_file);
1889-
1890-
for (line=lines;*line!=NULL;line++)
1891-
{
1892-
PG_CMD_PUTS(*line);
1893-
free(*line);
1894-
}
1895-
1896-
PG_CMD_PUTS("\n\n");
1897-
1898-
free(lines);
1863+
setup_run_file(cmdfd,info_schema_file);
18991864

19001865
PG_CMD_PRINTF("UPDATE information_schema.sql_implementation_info "
19011866
" SET character_value = '%s' "
@@ -2534,6 +2499,7 @@ setup_data_file_paths(void)
25342499
set_input(&dictionary_file,"snowball_create.sql");
25352500
set_input(&info_schema_file,"information_schema.sql");
25362501
set_input(&features_file,"sql_features.txt");
2502+
set_input(&system_constraints_file,"system_constraints.sql");
25372503
set_input(&system_views_file,"system_views.sql");
25382504

25392505
if (show_setting||debug)
@@ -2895,20 +2861,22 @@ initialize_data_directory(void)
28952861

28962862
setup_auth(cmdfd);
28972863

2864+
setup_run_file(cmdfd,system_constraints_file);
2865+
28982866
setup_depend(cmdfd);
28992867

29002868
/*
29012869
* Note that no objects created after setup_depend() will be "pinned".
29022870
* They are all droppable at the whim of the DBA.
29032871
*/
29042872

2905-
setup_sysviews(cmdfd);
2873+
setup_run_file(cmdfd,system_views_file);
29062874

29072875
setup_description(cmdfd);
29082876

29092877
setup_collation(cmdfd);
29102878

2911-
setup_dictionary(cmdfd);
2879+
setup_run_file(cmdfd,dictionary_file);
29122880

29132881
setup_privileges(cmdfd);
29142882

‎src/include/catalog/genbki.h‎

Lines changed: 8 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -55,12 +55,15 @@
5555
#defineDECLARE_TOAST(name,toastoid,indexoid) extern int no_such_variable
5656

5757
/*
58-
* These lines processed by genbki.pl to create the statements
58+
* These linesareprocessed by genbki.pl to create the statements
5959
* the bootstrap parser will turn into DefineIndex calls.
6060
*
61-
* The keyword is DECLARE_INDEX or DECLARE_UNIQUE_INDEX. The first two
62-
* arguments are the index name and OID, the rest is much like a standard
63-
* 'create index' SQL command.
61+
* The keyword is DECLARE_INDEX or DECLARE_UNIQUE_INDEX or
62+
* DECLARE_UNIQUE_INDEX_PKEY. ("PKEY" marks the index as being the catalog's
63+
* primary key; currently this is only cosmetically different from a regular
64+
* unique index. By convention, we usually make a catalog's OID column its
65+
* pkey, if it has one.) The first two arguments are the index's name and
66+
* OID, the rest is much like a standard 'create index' SQL command.
6467
*
6568
* For each index, we also provide a #define for its OID. References to
6669
* the index in the C code should always use these #defines, not the actual
@@ -70,6 +73,7 @@
7073
*/
7174
#defineDECLARE_INDEX(name,oid,decl) extern int no_such_variable
7275
#defineDECLARE_UNIQUE_INDEX(name,oid,decl) extern int no_such_variable
76+
#defineDECLARE_UNIQUE_INDEX_PKEY(name,oid,decl) extern int no_such_variable
7377

7478
/* The following are never defined; they are here only for documentation. */
7579

‎src/include/catalog/pg_aggregate.h‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -110,7 +110,7 @@ typedef FormData_pg_aggregate *Form_pg_aggregate;
110110

111111
DECLARE_TOAST(pg_aggregate,4159,4160);
112112

113-
DECLARE_UNIQUE_INDEX(pg_aggregate_fnoid_index,2650,onpg_aggregateusingbtree(aggfnoidoid_ops));
113+
DECLARE_UNIQUE_INDEX_PKEY(pg_aggregate_fnoid_index,2650,onpg_aggregateusingbtree(aggfnoidoid_ops));
114114
#defineAggregateFnoidIndexId 2650
115115

116116
#ifdefEXPOSE_TO_CLIENT_CODE

‎src/include/catalog/pg_am.h‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -49,7 +49,7 @@ typedef FormData_pg_am *Form_pg_am;
4949

5050
DECLARE_UNIQUE_INDEX(pg_am_name_index,2651,onpg_amusingbtree(amnamename_ops));
5151
#defineAmNameIndexId 2651
52-
DECLARE_UNIQUE_INDEX(pg_am_oid_index,2652,onpg_amusingbtree(oidoid_ops));
52+
DECLARE_UNIQUE_INDEX_PKEY(pg_am_oid_index,2652,onpg_amusingbtree(oidoid_ops));
5353
#defineAmOidIndexId 2652
5454

5555
#ifdefEXPOSE_TO_CLIENT_CODE

‎src/include/catalog/pg_amop.h‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -91,7 +91,7 @@ DECLARE_UNIQUE_INDEX(pg_amop_fam_strat_index, 2653, on pg_amop using btree(amopf
9191
#defineAccessMethodStrategyIndexId 2653
9292
DECLARE_UNIQUE_INDEX(pg_amop_opr_fam_index,2654,onpg_amopusingbtree(amopoproid_ops,amoppurposechar_ops,amopfamilyoid_ops));
9393
#defineAccessMethodOperatorIndexId 2654
94-
DECLARE_UNIQUE_INDEX(pg_amop_oid_index,2756,onpg_amopusingbtree(oidoid_ops));
94+
DECLARE_UNIQUE_INDEX_PKEY(pg_amop_oid_index,2756,onpg_amopusingbtree(oidoid_ops));
9595
#defineAccessMethodOperatorOidIndexId2756
9696

9797
#ifdefEXPOSE_TO_CLIENT_CODE

‎src/include/catalog/pg_amproc.h‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -69,7 +69,7 @@ typedef FormData_pg_amproc *Form_pg_amproc;
6969

7070
DECLARE_UNIQUE_INDEX(pg_amproc_fam_proc_index,2655,onpg_amprocusingbtree(amprocfamilyoid_ops,amproclefttypeoid_ops,amprocrighttypeoid_ops,amprocnumint2_ops));
7171
#defineAccessMethodProcedureIndexId 2655
72-
DECLARE_UNIQUE_INDEX(pg_amproc_oid_index,2757,onpg_amprocusingbtree(oidoid_ops));
72+
DECLARE_UNIQUE_INDEX_PKEY(pg_amproc_oid_index,2757,onpg_amprocusingbtree(oidoid_ops));
7373
#defineAccessMethodProcedureOidIndexId 2757
7474

7575
#endif/* PG_AMPROC_H */

0 commit comments

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