public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Kirill Reshke <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Cc: jian he <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: CREATE SCHEMA ... CREATE DOMAIN support
Date: Wed, 11 Dec 2024 12:08:36 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <CALdSSPi1eFMhAY6Xc7ZShnU0m5YrswOFLyDKrFzLjYd7wefzQg@mail.gmail.com>
References: <CALdSSPh4jUSDsWu3K58hjO60wnTRR0DuO4CKRcwa8EVuOSfXxg@mail.gmail.com>
	<CACJufxG+mrh2O9RS0gX43gU6sv+CMY847eMjMQpe8t4ou-2ryg@mail.gmail.com>
	<CACJufxFUdgqDiK9B+VNtnAwZOj=O3NqdLtXO_OrOwE5XPdCpBA@mail.gmail.com>
	<CALdSSPggNNvcad69dhUceZ_gPuEYnKNNd=WJ_WnP=YDmh=iwmw@mail.gmail.com>
	<[email protected]>
	<CALdSSPhckRXW+KEvdsUmkQ-ErbrP_vPNjGwgXNdpXDb8xnLEbQ@mail.gmail.com>
	<[email protected]>
	<CALdSSPgftbtV=UyeK_XcLit_mKQZr+g8pCxA0kLLxDi0kKwEGw@mail.gmail.com>
	<CACJufxH5TvkQiMOJ9dpDrDbRtyCa-yu+QOmYGt4WGGYVrN+P8g@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<CALdSSPi1eFMhAY6Xc7ZShnU0m5YrswOFLyDKrFzLjYd7wefzQg@mail.gmail.com>

Kirill Reshke <[email protected]> writes:
> On Wed, 4 Dec 2024 at 01:07, Tom Lane <[email protected]> wrote:
>> 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?

> Im +1 on this, but can you please elaborate, which exact objects
> cannot be created now? What will be expanded after
> v2-0002-Dont_try-to-reoder....?

The problem is not too awful right now, because of the very limited
set of object types that CREATE SCHEMA supports.  The only case
I can think of offhand is a table referencing a view's rowtype,
for example

   create schema s1
	create view v1 as select ...
	create table t1 (compositecol v1, ...);

Since transformCreateSchemaStmtElements re-orders views after
tables, this'll fail, and there is no way to fix that except
by giving up use of the elements-in-CREATE-SCHEMA feature.
Admittedly it's a strange usage, and probably no one has tried it.

However, once we start adding in data types and functions,
the hazard grows substantially, because there are more usage
patterns and they can't all be satisfied by a simple object-type
ordering.  For example, domains are already enough to cause
trouble, because we allow domains over composites:

   create schema s1
	create table t1 (...)
	create domain d1 as t1 check(...);

Re-ordering domains before tables would break this case, but
the other order has other problems.  Looking a bit further
down the road, how would you handle creation of a base type
within CREATE SCHEMA?

   create schema s1
	create type myscalar
	create function myscalar_in(cstring) returns myscalar ...
	create function myscalar_out(myscalar) returns cstring ...
	create type myscalar (input = myscalar_in, ...);

This cannot possibly work if an object-type-based re-ordering
is done to it.

So IMV, we have three possibilities:

1. CREATE SCHEMA's schema-element feature remains forevermore
a sad joke that (a) doesn't cover nearly enough to be useful and
(b) doesn't come close to doing what the spec says it should.

2. We invest an enormous amount of engineering effort on trying
to extract dependencies from not-yet-analyzed parse trees, after
which we invest a bunch more effort figuring out heuristics for
ordering the subcommands in the face of circular dependencies.
(Some of that could be stolen from pg_dump, but not all: pg_dump
only has to resolve a limited set of cases.)

3. We bypass the need for #2 by decreeing that we'll execute
the subcommands in order.


>> 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>.

> That's a nice feature to have by itself?

Not unless we abandon the idea of subcommand reordering, because
where are you going to put the ALTER TABLE subcommands?

			regards, tom lane

view thread (32+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: CREATE SCHEMA ... CREATE DOMAIN support
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox