4 ms·

This is not actually compatible with SQL semantics. An important constraint on SQL is that a query must run, and produce correct results, relying only on the s

This is not actually compatible with SQL semantics. An important constraint on SQL is that a query must run, and produce correct results, relying only on the structure and content of tables. Indexes (can) make queries faster but must not inhibit, or be required for, correctness. The same is true of primary key constraints, foreign key constraints, check constraints, defaults, triggers, partitioning, whether a table is heap/clustered, and literally every other implementation detail of the RDBMS. This proposal would break that constraint, by elevating the _name_ of a foreign key constraint to an identifier usable in a DQL statement (as distinct from DDL statements where such names can already appear). That's not allowed. More importantly, it's _not a good idea_, because that semantic separation between data on one side, and the machinery of acceleration and validation on the other side, is critical to the value prop of the relational model and a big reason why it's been so hyper-successful. So no, let's not do this.

JoelJacobson 5y ago
> This proposal would break that constraint, by elevating the _name_ of a foreign key constraint to an identifier usable in a DQL statement (as distinct from DDL statements where such names can already appear). That's not allowed. Actually, constraint names already do appear in some DQL statements, such as the quite recently added INSERT INTO ... ON CONFLICT in PostgreSQL [1] INSERT INTO ... ON CONFLICT [ conflict_target ] conflict_action where conflict_target can be one of: ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ] ON CONSTRAINT constraint_name [1] https://www.postgresql.org/docs/current/sql-insert.html https://www.postgresql.org/docs/current/sql-insert.html
- bradleyjg 5y ago
  PostgresSQL isn’t SQL though. Vendor extensions have done all kinds of inelegant or even outright ill advised things from the beginning.
  - JoelJacobson 5y ago
    The INSERT ... ON CONFLICT UPDATE patch has been documented here: https://wiki.postgresql.org/wiki/UPSERT#UPSERT_as_implemented_in_practice https://wiki.postgresql.org/wiki/UPSERT#UPSERT_as_implemente... There are some clear benefits possible only thanks to specifying constraint names. Quoted from the link: * The SQL-standard MERGE doesn't provide for choosing an index, so use of a unique index would need to be conditioned on equality quals against indexed columns in order to provide the behavior being discussed for UPSERT. * The ON expression will need to be evaluated to see whether it properly compares to a unique index on the target table. Initially this will need to be done to determine whether the MERGE is allowed at all; later it will determine which sort of plan is allowed. It would be easier to match an index name, provided the index name is known and stable.
    - deleted 5y ago
      [deleted]
  - yunohn 5y ago
    > PostgresSQL isn’t SQL though What is SQL acc. to you? MS-SQL, MySQL, SQLITE, Bigquery “Standard SQL”?
    - bradleyjg 5y ago
      Standards published by ISO/IEC.
      - yunohn 5y ago
        Sure, but where is that pure standard implemented?
        bradleyjg 5y ago
        Why does it need to be implemented somewhere?
        5y ago
- deleted 5y ago
  [deleted]
- cryptonector 5y ago
  That's DML, not DQL. Still, I disagree with GP's arguments (see separate comment).
  - JoelJacobson 5y ago
    > That's DML, not DQL. Thanks. I incorrectly assumed there were only two sublanguages. But now when you say it, I've heard both DML and DDL before, but never DQL. Curious if there were even more categories, I found this Wikipedia article: https://en.wikipedia.org/wiki/Data_query_language https://en.wikipedia.org/wiki/Data_query_language Interesting to read, what a SELECT statement is, depends on if having FROM or WHERE "data manipulators". Quote from Wikipedia: "Although often considered part of DML, the SQL SELECT statement is strictly speaking an example of DQL. When adding FROM or WHERE data manipulators to the SELECT statement the statement is then considered part of the DML."
deleted 5y ago
[deleted]
cryptonector 5y ago
> and produce correct results This doesn't interfere with that. > relying only on the structure and content of tables Constraints are part of the table schema, not index schema. > his proposal would break that constraint, by elevating the _name_ of a foreign key constraint to an identifier usable in a DQL statement (as distinct from DDL statements where such names can already appear). That's not allowed. Not allowed says who? The standard? I doubt it, and anyways, it can be changed. "That's not allowed" is not a good argument. A better argument is that this is the first time a constraint can change the meaning of a query -- that is a good argument, but it would be better if the constraint could change existing queries, which it does not do. Because this would only affect queries that refer to the constraint, this seems quite allowable to me. > More importantly, it's _not a good idea_, because that semantic separation between data on one side, and the machinery of acceleration and validation on the other side, ... This is not about optimization, ergo this argument is out. I'm not sure I want this particular extension, but I don't buy your arguments against it.
jpz 5y ago
I agree with all the points you make here, we’ll said.
lolive 5y ago
To me, making the data relationships implicit (and making them explicit again in the DAO layer of your app) is a nightmare from a data design point of view. In real life, a DB is often tied to the app that was built on top of it. Making data reuse, data discovery and data understanding VERY hard.
goto11 5y ago
> More importantly, it's _not a good idea_, because that semantic separation between data on one side, and the machinery of acceleration and validation on the other side, is critical to the value prop of the relational model and a big reason why it's been so hyper-successful. Constraints are part of the logical model just like the types of columns are. Indexes on the other hand is part of the physical model and should be transparent to the logical model. Database engines tend to couple foreign-key constraints with indexes, since you usually want an index on a foreign-key. But in principle they are separate. So I don't see any violation of the relational model in this proposal. I do like the general idea of extending SQL with metadata-aware abstractions, although I'm not a fan of this particular syntax.
dragonwriter 5y ago
> This proposal would break that constraint, by elevating the _name_ of a foreign key constraint to an identifier usable in a DQL statement This doesn't break that constraint in an SQL RDBMS that also implements the relational model, since it is a fundamental element of the relational model that schema metadata is stored as data, and therefore constraint specifications, including names, are included within “content of tables”.

JoelJacobson 5y ago

> This proposal would break that constraint, by elevating the _name_ of a foreign key constraint to an identifier usable in a DQL statement (as distinct from DDL statements where such names can already appear). That's not allowed. Actually, constraint names already do appear in some DQL statements, such as the quite recently added INSERT INTO ... ON CONFLICT in PostgreSQL [1] INSERT INTO ... ON CONFLICT [ conflict_target ] conflict_action where conflict_target can be one of: ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ] ON CONSTRAINT constraint_name [1] https://www.postgresql.org/docs/current/sql-insert.html https://www.postgresql.org/docs/current/sql-insert.html

bradleyjg 5y ago
PostgresSQL isn’t SQL though. Vendor extensions have done all kinds of inelegant or even outright ill advised things from the beginning.
- JoelJacobson 5y ago
  The INSERT ... ON CONFLICT UPDATE patch has been documented here: https://wiki.postgresql.org/wiki/UPSERT#UPSERT_as_implemented_in_practice https://wiki.postgresql.org/wiki/UPSERT#UPSERT_as_implemente... There are some clear benefits possible only thanks to specifying constraint names. Quoted from the link: * The SQL-standard MERGE doesn't provide for choosing an index, so use of a unique index would need to be conditioned on equality quals against indexed columns in order to provide the behavior being discussed for UPSERT. * The ON expression will need to be evaluated to see whether it properly compares to a unique index on the target table. Initially this will need to be done to determine whether the MERGE is allowed at all; later it will determine which sort of plan is allowed. It would be easier to match an index name, provided the index name is known and stable.
  - deleted 5y ago
    [deleted]
- yunohn 5y ago
  > PostgresSQL isn’t SQL though What is SQL acc. to you? MS-SQL, MySQL, SQLITE, Bigquery “Standard SQL”?
  - bradleyjg 5y ago
    Standards published by ISO/IEC.
    - yunohn 5y ago
      Sure, but where is that pure standard implemented?
      - bradleyjg 5y ago
        Why does it need to be implemented somewhere?
        5y ago
deleted 5y ago
[deleted]
cryptonector 5y ago
That's DML, not DQL. Still, I disagree with GP's arguments (see separate comment).
- JoelJacobson 5y ago
  > That's DML, not DQL. Thanks. I incorrectly assumed there were only two sublanguages. But now when you say it, I've heard both DML and DDL before, but never DQL. Curious if there were even more categories, I found this Wikipedia article: https://en.wikipedia.org/wiki/Data_query_language https://en.wikipedia.org/wiki/Data_query_language Interesting to read, what a SELECT statement is, depends on if having FROM or WHERE "data manipulators". Quote from Wikipedia: "Although often considered part of DML, the SQL SELECT statement is strictly speaking an example of DQL. When adding FROM or WHERE data manipulators to the SELECT statement the statement is then considered part of the DML."

deleted 5y ago

[deleted]

cryptonector 5y ago

> and produce correct results This doesn't interfere with that. > relying only on the structure and content of tables Constraints are part of the table schema, not index schema. > his proposal would break that constraint, by elevating the _name_ of a foreign key constraint to an identifier usable in a DQL statement (as distinct from DDL statements where such names can already appear). That's not allowed. Not allowed says who? The standard? I doubt it, and anyways, it can be changed. "That's not allowed" is not a good argument. A better argument is that this is the first time a constraint can change the meaning of a query -- that is a good argument, but it would be better if the constraint could change existing queries, which it does not do. Because this would only affect queries that refer to the constraint, this seems quite allowable to me. > More importantly, it's _not a good idea_, because that semantic separation between data on one side, and the machinery of acceleration and validation on the other side, ... This is not about optimization, ergo this argument is out. I'm not sure I want this particular extension, but I don't buy your arguments against it.

jpz 5y ago

I agree with all the points you make here, we’ll said.

lolive 5y ago

To me, making the data relationships implicit (and making them explicit again in the DAO layer of your app) is a nightmare from a data design point of view. In real life, a DB is often tied to the app that was built on top of it. Making data reuse, data discovery and data understanding VERY hard.

goto11 5y ago

> More importantly, it's _not a good idea_, because that semantic separation between data on one side, and the machinery of acceleration and validation on the other side, is critical to the value prop of the relational model and a big reason why it's been so hyper-successful. Constraints are part of the logical model just like the types of columns are. Indexes on the other hand is part of the physical model and should be transparent to the logical model. Database engines tend to couple foreign-key constraints with indexes, since you usually want an index on a foreign-key. But in principle they are separate. So I don't see any violation of the relational model in this proposal. I do like the general idea of extending SQL with metadata-aware abstractions, although I'm not a fan of this particular syntax.

dragonwriter 5y ago

> This proposal would break that constraint, by elevating the _name_ of a foreign key constraint to an identifier usable in a DQL statement This doesn't break that constraint in an SQL RDBMS that also implements the relational model, since it is a fundamental element of the relational model that schema metadata is stored as data, and therefore constraint specifications, including names, are included within “content of tables”.