Skip to content

Commit dfb75e4

Browse files
peterepetere
committed
Add primary keys and unique constraints to system catalogs
For those system catalogs that have a unique indexes, make a primary key and unique constraint, using ALTER TABLE ... PRIMARY KEY/UNIQUE USING INDEX. This can be helpful for GUI tools that look for a primary key, and it might in the future allow declaring foreign keys, for making schema diagrams. The constraint creation statements are automatically created by genbki.pl from DECLARE_UNIQUE_INDEX directives. To specify which one of the available unique indexes is the primary key, use the new directive DECLARE_UNIQUE_INDEX_PKEY instead. By convention, we usually 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 parent 6533062 commit dfb75e4

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

+1
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

+6-5
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

+5-4
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.bki system_constraints.sql system_views.sql information_schema.sql sql_features.txt)
134135

135-
# postgres.bki and 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.bki system_constraints.sql $(GENERATED_HEADERS)

‎src/backend/catalog/genbki.pl

+23
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
foreach my $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+
foreach my $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

+14-46
Original file line numberDiff line numberDiff line change
@@ -159,6 +159,7 @@ static char *conf_file;
159159
static char *dictionary_file;
160160
static char *info_schema_file;
161161
static char *features_file;
162+
static char *system_constraints_file;
162163
static char *system_views_file;
163164
static bool success = false;
164165
static bool made_new_pgdata = false;
@@ -251,10 +252,9 @@ static void bootstrap_template1(void);
251252
static void setup_auth(FILE *cmdfd);
252253
static void get_su_pwd(void);
253254
static void setup_depend(FILE *cmdfd);
254-
static void setup_sysviews(FILE *cmdfd);
255+
static void setup_run_file(FILE *cmdfd, const char *filename);
255256
static void setup_description(FILE *cmdfd);
256257
static void setup_collation(FILE *cmdfd);
257-
static void setup_dictionary(FILE *cmdfd);
258258
static void setup_privileges(FILE *cmdfd);
259259
static void set_info_version(void);
260260
static void setup_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
static void
1606-
setup_sysviews(FILE *cmdfd)
1606+
setup_run_file(FILE *cmdfd, const char *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-
static void
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
static void
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

+8-4
Original file line numberDiff line numberDiff line change
@@ -55,12 +55,15 @@
5555
#define DECLARE_TOAST(name,toastoid,indexoid) extern int no_such_variable
5656

5757
/*
58-
* These lines processed by genbki.pl to create the statements
58+
* These lines are processed 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
#define DECLARE_INDEX(name,oid,decl) extern int no_such_variable
7275
#define DECLARE_UNIQUE_INDEX(name,oid,decl) extern int no_such_variable
76+
#define DECLARE_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

+1-1
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, on pg_aggregate using btree(aggfnoid oid_ops));
113+
DECLARE_UNIQUE_INDEX_PKEY(pg_aggregate_fnoid_index, 2650, on pg_aggregate using btree(aggfnoid oid_ops));
114114
#define AggregateFnoidIndexId 2650
115115

116116
#ifdef EXPOSE_TO_CLIENT_CODE

‎src/include/catalog/pg_am.h

+1-1
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, on pg_am using btree(amname name_ops));
5151
#define AmNameIndexId 2651
52-
DECLARE_UNIQUE_INDEX(pg_am_oid_index, 2652, on pg_am using btree(oid oid_ops));
52+
DECLARE_UNIQUE_INDEX_PKEY(pg_am_oid_index, 2652, on pg_am using btree(oid oid_ops));
5353
#define AmOidIndexId 2652
5454

5555
#ifdef EXPOSE_TO_CLIENT_CODE

‎src/include/catalog/pg_amop.h

+1-1
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
#define AccessMethodStrategyIndexId 2653
9292
DECLARE_UNIQUE_INDEX(pg_amop_opr_fam_index, 2654, on pg_amop using btree(amopopr oid_ops, amoppurpose char_ops, amopfamily oid_ops));
9393
#define AccessMethodOperatorIndexId 2654
94-
DECLARE_UNIQUE_INDEX(pg_amop_oid_index, 2756, on pg_amop using btree(oid oid_ops));
94+
DECLARE_UNIQUE_INDEX_PKEY(pg_amop_oid_index, 2756, on pg_amop using btree(oid oid_ops));
9595
#define AccessMethodOperatorOidIndexId 2756
9696

9797
#ifdef EXPOSE_TO_CLIENT_CODE

‎src/include/catalog/pg_amproc.h

+1-1
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, on pg_amproc using btree(amprocfamily oid_ops, amproclefttype oid_ops, amprocrighttype oid_ops, amprocnum int2_ops));
7171
#define AccessMethodProcedureIndexId 2655
72-
DECLARE_UNIQUE_INDEX(pg_amproc_oid_index, 2757, on pg_amproc using btree(oid oid_ops));
72+
DECLARE_UNIQUE_INDEX_PKEY(pg_amproc_oid_index, 2757, on pg_amproc using btree(oid oid_ops));
7373
#define AccessMethodProcedureOidIndexId 2757
7474

7575
#endif /* PG_AMPROC_H */

‎src/include/catalog/pg_attrdef.h

+1-1
Original file line numberDiff line numberDiff line change
@@ -50,7 +50,7 @@ DECLARE_TOAST(pg_attrdef, 2830, 2831);
5050

5151
DECLARE_UNIQUE_INDEX(pg_attrdef_adrelid_adnum_index, 2656, on pg_attrdef using btree(adrelid oid_ops, adnum int2_ops));
5252
#define AttrDefaultIndexId 2656
53-
DECLARE_UNIQUE_INDEX(pg_attrdef_oid_index, 2657, on pg_attrdef using btree(oid oid_ops));
53+
DECLARE_UNIQUE_INDEX_PKEY(pg_attrdef_oid_index, 2657, on pg_attrdef using btree(oid oid_ops));
5454
#define AttrDefaultOidIndexId 2657
5555

5656
#endif /* PG_ATTRDEF_H */

‎src/include/catalog/pg_attribute.h

+1-1
Original file line numberDiff line numberDiff line change
@@ -194,7 +194,7 @@ typedef FormData_pg_attribute *Form_pg_attribute;
194194

195195
DECLARE_UNIQUE_INDEX(pg_attribute_relid_attnam_index, 2658, on pg_attribute using btree(attrelid oid_ops, attname name_ops));
196196
#define AttributeRelidNameIndexId 2658
197-
DECLARE_UNIQUE_INDEX(pg_attribute_relid_attnum_index, 2659, on pg_attribute using btree(attrelid oid_ops, attnum int2_ops));
197+
DECLARE_UNIQUE_INDEX_PKEY(pg_attribute_relid_attnum_index, 2659, on pg_attribute using btree(attrelid oid_ops, attnum int2_ops));
198198
#define AttributeRelidNumIndexId 2659
199199

200200
#ifdef EXPOSE_TO_CLIENT_CODE

‎src/include/catalog/pg_auth_members.h

+1-1
Original file line numberDiff line numberDiff line change
@@ -42,7 +42,7 @@ CATALOG(pg_auth_members,1261,AuthMemRelationId) BKI_SHARED_RELATION BKI_ROWTYPE_
4242
*/
4343
typedef FormData_pg_auth_members *Form_pg_auth_members;
4444

45-
DECLARE_UNIQUE_INDEX(pg_auth_members_role_member_index, 2694, on pg_auth_members using btree(roleid oid_ops, member oid_ops));
45+
DECLARE_UNIQUE_INDEX_PKEY(pg_auth_members_role_member_index, 2694, on pg_auth_members using btree(roleid oid_ops, member oid_ops));
4646
#define AuthMemRoleMemIndexId 2694
4747
DECLARE_UNIQUE_INDEX(pg_auth_members_member_role_index, 2695, on pg_auth_members using btree(member oid_ops, roleid oid_ops));
4848
#define AuthMemMemRoleIndexId 2695

‎src/include/catalog/pg_authid.h

+1-1
Original file line numberDiff line numberDiff line change
@@ -61,7 +61,7 @@ DECLARE_TOAST(pg_authid, 4175, 4176);
6161

6262
DECLARE_UNIQUE_INDEX(pg_authid_rolname_index, 2676, on pg_authid using btree(rolname name_ops));
6363
#define AuthIdRolnameIndexId 2676
64-
DECLARE_UNIQUE_INDEX(pg_authid_oid_index, 2677, on pg_authid using btree(oid oid_ops));
64+
DECLARE_UNIQUE_INDEX_PKEY(pg_authid_oid_index, 2677, on pg_authid using btree(oid oid_ops));
6565
#define AuthIdOidIndexId 2677
6666

6767
#endif /* PG_AUTHID_H */

‎src/include/catalog/pg_cast.h

+1-1
Original file line numberDiff line numberDiff line change
@@ -56,7 +56,7 @@ CATALOG(pg_cast,2605,CastRelationId)
5656
*/
5757
typedef FormData_pg_cast *Form_pg_cast;
5858

59-
DECLARE_UNIQUE_INDEX(pg_cast_oid_index, 2660, on pg_cast using btree(oid oid_ops));
59+
DECLARE_UNIQUE_INDEX_PKEY(pg_cast_oid_index, 2660, on pg_cast using btree(oid oid_ops));
6060
#define CastOidIndexId 2660
6161
DECLARE_UNIQUE_INDEX(pg_cast_source_target_index, 2661, on pg_cast using btree(castsource oid_ops, casttarget oid_ops));
6262
#define CastSourceTargetIndexId 2661

‎src/include/catalog/pg_class.h

+1-1
Original file line numberDiff line numberDiff line change
@@ -152,7 +152,7 @@ CATALOG(pg_class,1259,RelationRelationId) BKI_BOOTSTRAP BKI_ROWTYPE_OID(83,Relat
152152
*/
153153
typedef FormData_pg_class *Form_pg_class;
154154

155-
DECLARE_UNIQUE_INDEX(pg_class_oid_index, 2662, on pg_class using btree(oid oid_ops));
155+
DECLARE_UNIQUE_INDEX_PKEY(pg_class_oid_index, 2662, on pg_class using btree(oid oid_ops));
156156
#define ClassOidIndexId 2662
157157
DECLARE_UNIQUE_INDEX(pg_class_relname_nsp_index, 2663, on pg_class using btree(relname name_ops, relnamespace oid_ops));
158158
#define ClassNameNspIndexId 2663

‎src/include/catalog/pg_collation.h

+1-1
Original file line numberDiff line numberDiff line change
@@ -48,7 +48,7 @@ typedef FormData_pg_collation *Form_pg_collation;
4848

4949
DECLARE_UNIQUE_INDEX(pg_collation_name_enc_nsp_index, 3164, on pg_collation using btree(collname name_ops, collencoding int4_ops, collnamespace oid_ops));
5050
#define CollationNameEncNspIndexId 3164
51-
DECLARE_UNIQUE_INDEX(pg_collation_oid_index, 3085, on pg_collation using btree(oid oid_ops));
51+
DECLARE_UNIQUE_INDEX_PKEY(pg_collation_oid_index, 3085, on pg_collation using btree(oid oid_ops));
5252
#define CollationOidIndexId 3085
5353

5454
#ifdef EXPOSE_TO_CLIENT_CODE

‎src/include/catalog/pg_constraint.h

+1-1
Original file line numberDiff line numberDiff line change
@@ -161,7 +161,7 @@ DECLARE_UNIQUE_INDEX(pg_constraint_conrelid_contypid_conname_index, 2665, on pg_
161161
#define ConstraintRelidTypidNameIndexId 2665
162162
DECLARE_INDEX(pg_constraint_contypid_index, 2666, on pg_constraint using btree(contypid oid_ops));
163163
#define ConstraintTypidIndexId 2666
164-
DECLARE_UNIQUE_INDEX(pg_constraint_oid_index, 2667, on pg_constraint using btree(oid oid_ops));
164+
DECLARE_UNIQUE_INDEX_PKEY(pg_constraint_oid_index, 2667, on pg_constraint using btree(oid oid_ops));
165165
#define ConstraintOidIndexId 2667
166166
DECLARE_INDEX(pg_constraint_conparentid_index, 2579, on pg_constraint using btree(conparentid oid_ops));
167167
#define ConstraintParentIndexId 2579

‎src/include/catalog/pg_conversion.h

+1-1
Original file line numberDiff line numberDiff line change
@@ -64,7 +64,7 @@ DECLARE_UNIQUE_INDEX(pg_conversion_default_index, 2668, on pg_conversion using b
6464
#define ConversionDefaultIndexId 2668
6565
DECLARE_UNIQUE_INDEX(pg_conversion_name_nsp_index, 2669, on pg_conversion using btree(conname name_ops, connamespace oid_ops));
6666
#define ConversionNameNspIndexId 2669
67-
DECLARE_UNIQUE_INDEX(pg_conversion_oid_index, 2670, on pg_conversion using btree(oid oid_ops));
67+
DECLARE_UNIQUE_INDEX_PKEY(pg_conversion_oid_index, 2670, on pg_conversion using btree(oid oid_ops));
6868
#define ConversionOidIndexId 2670
6969

7070

0 commit comments

Comments
 (0)