Peter Eisentraut <peter@eisentraut.org> writes:
> On 30.11.24 20:08, Tom Lane wrote:
>> ... I think we ought to nuke
>> that concept from orbit and just execute the schema elements in the
>> order presented. I looked at several iterations of the SQL standard
>> and cannot find any support for the idea that CREATE SCHEMA needs to
>> be any smarter than that.
> SQL/Framework subclause "Descriptors" says:
> """
> The execution of an SQL-statement may result in the creation of many
> descriptors. An SQL object that is created as a result of an
> SQL-statement may depend on other descriptors that are only created as a
> result of the execution of that SQL statement.
> NOTE 8 — This is particularly relevant in the case of the <schema
> definition> SQL-statement. A <schema definition> can, for example,
> contain many <table definition>s that in turn contain <table
> constraint>s. A single <table constraint> in one <table definition> can
> reference a second table being created by a separate <table definition>
> which itself is able to contain a reference to the first table. The
> dependencies of each table on the descriptors of the other are valid
> provided that all necessary descriptors are created during the execution
> of the complete <schema definition>.
> """
Ah, thanks for the pointer.
> So this says effectively that forward references are allowed. Whether
> reordering the statements is a good way to implement that is dubious, as
> we are discovering.
Yeah, I think it's a long way from this text to the conclusion that
the implementation is responsible for reordering the subcommands
to remove forward references. And it really offers no help at all
for the ensuing problem of distinguishing forward references from
external references.
The one aspect of the spec's definition that seems useful to me in
practice is the ability to create quasi-circular foreign keys (that
is, t1 has an FK to t2 and t2 has an FK to t1). But that is something
we have never implemented in 22 years and nobody has complained of
the lack. I'm totally willing to throw that possibility overboard
permanently in order to expand the set of creatable object types
without introducing a ton of restrictions and weird behaviors.
What do you think?
regards, tom lane
PS: if we were really excited about allowing circular FKs to be
made within CREATE SCHEMA, a possible though non-standard answer
would be to allow ALTER TABLE ADD CONSTRAINT as a <schema element>.
