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

Commit62f3409

Browse files
committed
Build in some knowledge about foreign-key relationships in the catalogs.
This follows in the spirit of commitdfb75e4, which created primarykey and uniqueness constraints to improve the visibility of constraintsimposed on the system catalogs. While our catalogs contain manyforeign-key-like relationships, they don't quite follow SQL semantics,in that the convention for an omitted reference is to write zero notNULL. Plus, we have some cases in which there are arrays each of whoseelements is supposed to be an FK reference; SQL has no way to model that.So we can't create actual foreign key constraints to describe thesituation. Nonetheless, we can collect and use knowledge about theserelationships.This patch therefore adds annotations to the catalog header files todeclare foreign-key relationships. (The BKI_LOOKUP annotations coversimple cases, but we weren't previously distinguishing which suchcolumns are allowed to contain zeroes; we also need new markings formulti-column FK references.) Then, Catalog.pm and genbki.pl aretaught to collect this information into a table in a new generatedheader "system_fk_info.h". The only user of that at the moment isa new SQL function pg_get_catalog_foreign_keys(), which exposes thetable to SQL. The oidjoins regression test is rewritten to usepg_get_catalog_foreign_keys() to find out which columns to check.Aside from removing the need for manual maintenance of that testscript, this allows it to cover numerous relationships that were notchecked by the old implementation based on findoidjoins. (As of thiscommit, 217 relationships are checked by the test, versus 181 before.)Discussion:https://postgr.es/m/3240355.1612129197@sss.pgh.pa.us
1 parent4793314 commit62f3409

File tree

78 files changed

+901
-2402
lines changed

Some content is hidden

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

78 files changed

+901
-2402
lines changed

‎doc/src/sgml/bki.sgml

Lines changed: 23 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -474,10 +474,15 @@
474474

475475
<listitem>
476476
<para>
477-
In such a column, all entries must use the symbolic format except
478-
when writing <literal>0</literal> for InvalidOid. (If the column is
477+
In some catalog columns, it's allowed for entries to be zero instead
478+
of a valid reference. If this is allowed, write
479+
<literal>BKI_LOOKUP_OPT</literal> instead
480+
of <literal>BKI_LOOKUP</literal>. Then you can
481+
write <literal>0</literal> for an entry. (If the column is
479482
declared <type>regproc</type>, you can optionally
480483
write <literal>-</literal> instead of <literal>0</literal>.)
484+
Except for this special case, all entries in
485+
a <literal>BKI_LOOKUP</literal> column must be symbolic references.
481486
<filename>genbki.pl</filename> will warn about unrecognized names.
482487
</para>
483488
</listitem>
@@ -554,6 +559,22 @@
554559
therefore no need for the bootstrap backend to deal with symbolic
555560
references.
556561
</para>
562+
563+
<para>
564+
It's desirable to mark OID reference columns
565+
with <literal>BKI_LOOKUP</literal> or <literal>BKI_LOOKUP_OPT</literal>
566+
even if the catalog has no initial data that requires lookup. This
567+
allows <filename>genbki.pl</filename> to record the foreign key
568+
relationships that exist in the system catalogs. That information is
569+
used in the regression tests to check for incorrect entries. See also
570+
the macros <literal>DECLARE_FOREIGN_KEY</literal>,
571+
<literal>DECLARE_FOREIGN_KEY_OPT</literal>,
572+
<literal>DECLARE_ARRAY_FOREIGN_KEY</literal>,
573+
and <literal>DECLARE_ARRAY_FOREIGN_KEY_OPT</literal>, which are
574+
used to declare foreign key relationships that are too complex
575+
for <literal>BKI_LOOKUP</literal> (typically, multi-column foreign
576+
keys).
577+
</para>
557578
</sect2>
558579

559580
<sect2 id="system-catalog-auto-array-types">

‎doc/src/sgml/func.sgml

Lines changed: 32 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -22789,6 +22789,38 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
2278922789
</para></entry>
2279022790
</row>
2279122791

22792+
<row>
22793+
<entry role="func_table_entry"><para role="func_signature">
22794+
<indexterm>
22795+
<primary>pg_get_catalog_foreign_keys</primary>
22796+
</indexterm>
22797+
<function>pg_get_catalog_foreign_keys</function> ()
22798+
<returnvalue>setof record</returnvalue>
22799+
( <parameter>fktable</parameter> <type>regclass</type>,
22800+
<parameter>fkcols</parameter> <type>text[]</type>,
22801+
<parameter>pktable</parameter> <type>regclass</type>,
22802+
<parameter>pkcols</parameter> <type>text[]</type>,
22803+
<parameter>is_array</parameter> <type>boolean</type>,
22804+
<parameter>is_opt</parameter> <type>boolean</type> )
22805+
</para>
22806+
<para>
22807+
Returns a set of records describing the foreign key relationships
22808+
that exist within the <productname>PostgreSQL</productname> system
22809+
catalogs.
22810+
The <parameter>fktable</parameter> column contains the name of the
22811+
referencing catalog, and the <parameter>fkcols</parameter> column
22812+
contains the name(s) of the referencing column(s). Similarly,
22813+
the <parameter>pktable</parameter> column contains the name of the
22814+
referenced catalog, and the <parameter>pkcols</parameter> column
22815+
contains the name(s) of the referenced column(s).
22816+
If <parameter>is_array</parameter> is true, the last referencing
22817+
column is an array, each of whose elements should match some entry
22818+
in the referenced catalog.
22819+
If <parameter>is_opt</parameter> is true, the referencing column(s)
22820+
are allowed to contain zeroes instead of a valid reference.
22821+
</para></entry>
22822+
</row>
22823+
2279222824
<row>
2279322825
<entry role="func_table_entry"><para role="func_signature">
2279422826
<indexterm>

‎src/backend/catalog/.gitignore

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

‎src/backend/catalog/Catalog.pm

Lines changed: 26 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -105,6 +105,17 @@ sub ParseHeader
105105
index_decl=>$5
106106
};
107107
}
108+
elsif (/^DECLARE_(ARRAY_)?FOREIGN_KEY(_OPT)?\(\s*\(([^)]+)\),\s*(\w+),\s*\(([^)]+)\)\)/)
109+
{
110+
push @{$catalog{foreign_keys} },
111+
{
112+
is_array=>$1 ? 1 : 0,
113+
is_opt=>$2 ? 1 : 0,
114+
fk_cols=>$3,
115+
pk_table=>$4,
116+
pk_cols=>$5
117+
};
118+
}
108119
elsif (/^CATALOG\((\w+),(\d+),(\w+)\)/)
109120
{
110121
$catalog{catname} =$1;
@@ -197,9 +208,22 @@ sub ParseHeader
197208
{
198209
$column{array_default} =$1;
199210
}
200-
elsif ($attopt =~/BKI_LOOKUP\((\w+)\)/)
211+
elsif ($attopt =~/BKI_LOOKUP(_OPT)?\((\w+)\)/)
201212
{
202-
$column{lookup} =$1;
213+
$column{lookup} =$2;
214+
$column{lookup_opt} =$1 ? 1 : 0;
215+
# BKI_LOOKUP implicitly makes an FK reference
216+
push @{$catalog{foreign_keys} },
217+
{
218+
is_array=>
219+
($atttypeeq'oidvector' ||$atttypeeq'_oid')
220+
? 1
221+
: 0,
222+
is_opt=>$column{lookup_opt},
223+
fk_cols=>$attname,
224+
pk_table=>$column{lookup},
225+
pk_cols=>'oid'
226+
};
203227
}
204228
else
205229
{

‎src/backend/catalog/Makefile

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -70,7 +70,7 @@ CATALOG_HEADERS := \
7070
pg_sequence.h pg_publication.h pg_publication_rel.h pg_subscription.h\
7171
pg_subscription_rel.h
7272

73-
GENERATED_HEADERS :=$(CATALOG_HEADERS:%.h=%_d.h) schemapg.h
73+
GENERATED_HEADERS :=$(CATALOG_HEADERS:%.h=%_d.h) schemapg.h system_fk_info.h
7474

7575
POSTGRES_BKI_SRCS :=$(addprefix$(top_srcdir)/src/include/catalog/,$(CATALOG_HEADERS))
7676

‎src/backend/catalog/genbki.pl

Lines changed: 106 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -213,6 +213,12 @@
213213
$amoids{$row->{amname} } =$row->{oid};
214214
}
215215

216+
# There is only one authid at bootstrap time, and we handle it specially:
217+
# the usually-defaulted symbol PGUID becomes the bootstrap superuser's OID.
218+
# (We could drop this in favor of writing out BKI_DEFAULT(POSTGRES) ...)
219+
my%authidoids;
220+
$authidoids{'PGUID'} =$BOOTSTRAP_SUPERUSERID;
221+
216222
# class (relation) OID lookup (note this only covers bootstrap catalogs!)
217223
my%classoids;
218224
foreachmy$row (@{$catalog_data{pg_class} })
@@ -234,6 +240,12 @@
234240
$langoids{$row->{lanname} } =$row->{oid};
235241
}
236242

243+
# There is only one namespace at bootstrap time, and we handle it specially:
244+
# the usually-defaulted symbol PGNSP becomes the pg_catalog namespace's OID.
245+
# (We could drop this in favor of writing out BKI_DEFAULT(pg_catalog) ...)
246+
my%namespaceoids;
247+
$namespaceoids{'PGNSP'} =$PG_CATALOG_NAMESPACE;
248+
237249
# opclass OID lookup
238250
my%opcoids;
239251
foreachmy$row (@{$catalog_data{pg_opclass} })
@@ -376,9 +388,11 @@
376388
# Map lookup name to the corresponding hash table.
377389
my%lookup_kind = (
378390
pg_am =>\%amoids,
391+
pg_authid =>\%authidoids,
379392
pg_class =>\%classoids,
380393
pg_collation =>\%collationoids,
381394
pg_language =>\%langoids,
395+
pg_namespace =>\%namespaceoids,
382396
pg_opclass =>\%opcoids,
383397
pg_operator =>\%operoids,
384398
pg_opfamily =>\%opfoids,
@@ -400,6 +414,9 @@
400414
my$schemafile =$output_path . 'schemapg.h';
401415
open my$schemapg, '>',$schemafile .$tmpext
402416
or die "can't open$schemafile$tmpext:$!";
417+
my$fk_info_file =$output_path . 'system_fk_info.h';
418+
open my$fk_info, '>',$fk_info_file .$tmpext
419+
or die "can't open$fk_info_file$tmpext:$!";
403420
my$constraints_file =$output_path . 'system_constraints.sql';
404421
open my$constraints, '>',$constraints_file .$tmpext
405422
or die "can't open$constraints_file$tmpext:$!";
@@ -554,18 +571,14 @@
554571
$GenbkiNextOid++;
555572
}
556573
557-
# Substitute constant values we acquired above.
558-
# (It's intentional that this can apply to parts of a field).
559-
$bki_values{$attname} =~ s/\bPGUID\b/$BOOTSTRAP_SUPERUSERID/g;
560-
$bki_values{$attname} =~ s/\bPGNSP\b/$PG_CATALOG_NAMESPACE/g;
561-
562574
# Replace OID synonyms with OIDs per the appropriate lookup rule.
563575
#
564576
# If the column type is oidvector or _oid, we have to replace
565577
# each element of the array as per the lookup rule.
566578
if ($column->{lookup})
567579
{
568-
my$lookup =$lookup_kind{$column->{lookup} };
580+
my$lookup =$lookup_kind{$column->{lookup} };
581+
my$lookup_opt =$column->{lookup_opt};
569582
my@lookupnames;
570583
my@lookupoids;
571584
@@ -575,8 +588,9 @@
575588
if ($atttype eq 'oidvector')
576589
{
577590
@lookupnames = split /\s+/,$bki_values{$attname};
578-
@lookupoids = lookup_oids($lookup,$catname,\%bki_values,
579-
@lookupnames);
591+
@lookupoids =
592+
lookup_oids($lookup,$catname,$attname,$lookup_opt,
593+
\%bki_values,@lookupnames);
580594
$bki_values{$attname} = join(' ',@lookupoids);
581595
}
582596
elsif ($atttype eq '_oid')
@@ -586,17 +600,18 @@
586600
$bki_values{$attname} =~ s/[{}]//g;
587601
@lookupnames =split /,/,$bki_values{$attname};
588602
@lookupoids =
589-
lookup_oids($lookup,$catname,\%bki_values,
590-
@lookupnames);
603+
lookup_oids($lookup,$catname,$attname,
604+
$lookup_opt, \%bki_values,@lookupnames);
591605
$bki_values{$attname} =sprintf"{%s}",
592606
join(',',@lookupoids);
593607
}
594608
}
595609
else
596610
{
597611
$lookupnames[0] =$bki_values{$attname};
598-
@lookupoids = lookup_oids($lookup,$catname, \%bki_values,
599-
@lookupnames);
612+
@lookupoids =
613+
lookup_oids($lookup,$catname,$attname,$lookup_opt,
614+
\%bki_values,@lookupnames);
600615
$bki_values{$attname} =$lookupoids[0];
601616
}
602617
}
@@ -706,14 +721,78 @@
706721
# Closing boilerplate for schemapg.h
707722
print$schemapg"\n#endif\t\t\t\t\t\t\t/* SCHEMAPG_H */\n";
708723

724+
# Now generate system_fk_info.h
725+
726+
# Opening boilerplate for system_fk_info.h
727+
print$fk_info<<EOM;
728+
/*-------------------------------------------------------------------------
729+
*
730+
* system_fk_info.h
731+
* Data about the foreign-key relationships in the system catalogs
732+
*
733+
* Portions Copyright (c) 1996-2021, PostgreSQL Global Development Group
734+
* Portions Copyright (c) 1994, Regents of the University of California
735+
*
736+
* NOTES
737+
* ******************************
738+
* *** DO NOT EDIT THIS FILE! ***
739+
* ******************************
740+
*
741+
* It has been GENERATED by src/backend/catalog/genbki.pl
742+
*
743+
*-------------------------------------------------------------------------
744+
*/
745+
#ifndef SYSTEM_FK_INFO_H
746+
#define SYSTEM_FK_INFO_H
747+
748+
typedef struct SysFKRelationship
749+
{
750+
Oidfk_table;/* referencing catalog */
751+
Oidpk_table;/* referenced catalog */
752+
const char *fk_columns;/* referencing column name(s) */
753+
const char *pk_columns;/* referenced column name(s) */
754+
boolis_array;/* if true, last fk_column is an array */
755+
boolis_opt;/* if true, fk_column can be zero */
756+
} SysFKRelationship;
757+
758+
static const SysFKRelationship sys_fk_relationships[] = {
759+
EOM
760+
761+
# Emit system_fk_info data
762+
foreachmy$catname (@catnames)
763+
{
764+
my$catalog =$catalogs{$catname};
765+
foreachmy$fkinfo (@{$catalog->{foreign_keys} })
766+
{
767+
my$pktabname =$fkinfo->{pk_table};
768+
769+
# We use BKI_LOOKUP for encodings, but there's no real catalog there
770+
nextif$pktabnameeq'encoding';
771+
772+
printf$fk_info
773+
"\t{ /*%s */%s, /*%s */%s,\"{%s}\",\"{%s}\",%s,%s},\n",
774+
$catname,$catalog->{relation_oid},
775+
$pktabname,$catalogs{$pktabname}->{relation_oid},
776+
$fkinfo->{fk_cols},
777+
$fkinfo->{pk_cols},
778+
($fkinfo->{is_array} ?"true" :"false"),
779+
($fkinfo->{is_opt} ?"true" :"false");
780+
}
781+
}
782+
783+
# Closing boilerplate for system_fk_info.h
784+
print$fk_info"};\n\n#endif\t\t\t\t\t\t\t/* SYSTEM_FK_INFO_H */\n";
785+
709786
# We're done emitting data
710787
close$bki;
711788
close$schemapg;
789+
close$fk_info;
712790
close$constraints;
713791

714792
# Finally, rename the completed files into place.
715793
Catalog::RenameTempFile($bkifile,$tmpext);
716794
Catalog::RenameTempFile($schemafile,$tmpext);
795+
Catalog::RenameTempFile($fk_info_file,$tmpext);
717796
Catalog::RenameTempFile($constraints_file,$tmpext);
718797

719798
exit 0;
@@ -948,7 +1027,8 @@ sub morph_row_for_schemapg
9481027
# within this genbki.pl run.)
9491028
sublookup_oids
9501029
{
951-
my ($lookup,$catname,$bki_values,@lookupnames) =@_;
1030+
my ($lookup,$catname,$attname,$lookup_opt,$bki_values,@lookupnames)
1031+
=@_;
9521032

9531033
my@lookupoids;
9541034
foreachmy$lookupname (@lookupnames)
@@ -961,10 +1041,19 @@ sub lookup_oids
9611041
else
9621042
{
9631043
push@lookupoids,$lookupname;
964-
warnsprintf
965-
"unresolved OID reference\"%s\" in%s.dat line%s\n",
966-
$lookupname,$catname,$bki_values->{line_number}
967-
if$lookupnamene'-'and$lookupnamene'0';
1044+
if ($lookupnameeq'-'or$lookupnameeq'0')
1045+
{
1046+
warnsprintf
1047+
"invalid zero OID reference in%s.dat field%s line%s\n",
1048+
$catname,$attname,$bki_values->{line_number}
1049+
if !$lookup_opt;
1050+
}
1051+
else
1052+
{
1053+
warnsprintf
1054+
"unresolved OID reference\"%s\" in%s.dat field%s line%s\n",
1055+
$lookupname,$catname,$attname,$bki_values->{line_number};
1056+
}
9681057
}
9691058
}
9701059
return@lookupoids;

0 commit comments

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