Re: PostgreSQL vs SQL Standard

>>>>> "Tom" == Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

>> I think I got all the issues I currently know of, but there may be
>> more, and others may disagree with my classification of issues or the
>> rationales for violating the spec. Any feedback?

Tom> WRT 1.1 ... I doubt that redefining DROP DOMAIN as you describe
Tom> has "no major issues". It sounds to me like an incredibly ugly
Tom> wart on the cascaded dependency logic. Quite aside from wartiness,
Tom> adding new objects/dependencies as part of a DROP is broken by
Tom> design. What if the domain drop has cascaded from something the
Tom> domain's constraints themselves depend on? I'd put this as a "has
Tom> design-level problems" item.

Oh wow, I hadn't noticed that dropping a function referenced from a
domain's default or constraint drops the whole domain rather than just
removing the default or constraint the way it would with a table.

That seems pretty bad to me, in the sense of being potentially a nasty
footgun for anyone using domains, but certainly you are correct on the
effect on how we categorize the problem.

(If it were not the case, then the only way we'd end up cascading to
dropping a domain would be if we dropped the base type, in which case
the columns are going to go away anyway)

Tom> WRT 3.2 on select-list aliases, the postfix-operator issue is only
Tom> one of several reasons why we can't support that. There was some
Tom> more-detailed discussion about that awhile back,

Tom> https://www.postgresql.org/message-id/flat/99ad0450-b1ab-702f-48ef-6972b630bc87%40BlueTreble.com

OK, so to summarize, we'd also have to remove ISNULL or make it a
reserved word, and also make VARYING reserved (as it is in the spec)?

The spec doesn't allow "SELECT col AS reservedword", even though we do,
so we don't really have to support "SELECT col reservedword". i.e. we
don't need to get all the way to allowing a_expr ColLabel production, it
would suffice to get to a_expr ColId.

Tom> Constraint name scope: I think it's an overstatement to say that
Tom> this makes some info-schema views "useless". "Under-determined"
Tom> might be an appropriate word.

But in practice that makes it useless except in cases where you
generally don't care about i_s anyway.

Tom> Or you could say "useless unless the application limits itself to
Tom> follow the SQL spec's restriction on names".

I'm not sure any applications use i_s to introspect on their own foreign
key constraints; every time I've had to give the "that doesn't work
because PG's constraint name scope differs from the standard" speech to
someone it's because they've been trying to write something more generic
than a single application.

(Though that could be selection bias I guess.)

Someone also pointed out the last time this came up that handling of
constraint names on inherited tables means that an application may be
unable to avoid using duplicate names.

Tom> Object ownership scope: I have not really dug into the spec on
Tom> this point, but I recall from our own docs that "schema owner owns
Tom> all contained objects too" is true only in DBs that implement some
Tom> minimal subset of the standard.

The spec literally does not use the term "owns" or "owned by" (in the
sense of an authorization identifier owning an object) anywhere except
for schemas.

If you look at <table definition>, you'll see that the authorization
identifier A which is the recipient of the table's initial GRANTs is
defined as being that of the schema.

Tom> So that might need more explanation. In any case, we can surely
Tom> mount a very strong defense in terms of usability/flexibility
Tom> here, we don't need to say it's just historical.

Sure.

--
Andrew (irc:RhodiumToad)