delta/postgresql.git/src/backend/catalog/information_schema.sql, branch master git.postgresql.org: git/postgresql.git Update information_schema for SQL:2023 2023-04-05T07:57:44+00:00 Peter Eisentraut peter@eisentraut.org 2023-04-05T07:47:07+00:00 f275af8cb88e5a87b01467ad90704c945806d7dd This is mainly a light renumbering to match the sections in the standard. The comments for SQL_IMPLEMENTATION_INFO and SQL_SIZING are no longer applicable because the required information has been moved into part 9. 
This is mainly a light renumbering to match the sections in the
standard.

The comments for SQL_IMPLEMENTATION_INFO and SQL_SIZING are no longer
applicable because the required information has been moved into part
9.
doc: Update SQL features/conformance information to SQL:2023 2023-04-05T07:20:25+00:00 Peter Eisentraut peter@eisentraut.org 2023-04-05T06:55:44+00:00 c9f57541d9705da44b9fbcb6e0c75dec3b41b57c Optional subfeatures have been changed to top-level features, so there is a bit of a churn in the list for that. Some existing functions have been added to the standard, so they are moved from the "other" to the "standard" lists in their sections. Discussion: https://www.postgresql.org/message-id/flat/63f285d9-4ec8-0c9e-4bf5-e76334ddc0af@enterprisedb.com 
Optional subfeatures have been changed to top-level features, so there
is a bit of a churn in the list for that.

Some existing functions have been added to the standard, so they are
moved from the "other" to the "standard" lists in their sections.

Discussion: https://www.postgresql.org/message-id/flat/63f285d9-4ec8-0c9e-4bf5-e76334ddc0af@enterprisedb.com
Fix some section numbers in information_schema.sql 2023-03-29T09:34:37+00:00 Peter Eisentraut peter@eisentraut.org 2023-03-29T09:34:37+00:00 38b7437b9088b4859e4489a1a1a9ab7066f5b320 Some of the section numbers that appeared multiple times were not updated completely by previous changes d61d9aa750 and eb3a1376c9. 
Some of the section numbers that appeared multiple times were not
updated completely by previous changes d61d9aa750 and eb3a1376c9.
Update copyright for 2023 2023-01-02T20:00:37+00:00 Bruce Momjian bruce@momjian.us 2023-01-02T20:00:37+00:00 c8e1ba736b2b9e8c98d37a5b77c4ed31baf94147 Backpatch-through: 11 
Backpatch-through: 11
Non-decimal integer literals 2022-12-14T05:17:07+00:00 Peter Eisentraut peter@eisentraut.org 2022-12-14T04:40:38+00:00 6fcda9aba83449082124825b6d375c0a61e21c42 Add support for hexadecimal, octal, and binary integer literals: 0x42F 0o273 0b100101 per SQL:202x draft. This adds support in the lexer as well as in the integer type input functions. Reviewed-by: John Naylor <john.naylor@enterprisedb.com> Reviewed-by: Zhihong Yu <zyu@yugabyte.com> Reviewed-by: David Rowley <dgrowleyml@gmail.com> Reviewed-by: Dean Rasheed <dean.a.rasheed@gmail.com> Discussion: https://www.postgresql.org/message-id/flat/b239564c-cad0-b23e-c57e-166d883cb97d@enterprisedb.com 
Add support for hexadecimal, octal, and binary integer literals:

    0x42F
    0o273
    0b100101

per SQL:202x draft.

This adds support in the lexer as well as in the integer type input
functions.

Reviewed-by: John Naylor <john.naylor@enterprisedb.com>
Reviewed-by: Zhihong Yu <zyu@yugabyte.com>
Reviewed-by: David Rowley <dgrowleyml@gmail.com>
Reviewed-by: Dean Rasheed <dean.a.rasheed@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/b239564c-cad0-b23e-c57e-166d883cb97d@enterprisedb.com
Fix bogus dependency handling for GENERATED expressions. 2022-03-21T18:58:49+00:00 Tom Lane tgl@sss.pgh.pa.us 2022-03-21T18:58:49+00:00 cb02fcb4c95bae08adaca1202c2081cfc81a28b5 For GENERATED columns, we record all dependencies of the generation expression as AUTO dependencies of the column itself. This means that the generated column is silently dropped if any dependency is removed, even if CASCADE wasn't specified. This is at least a POLA violation, but I think it's actually based on a misreading of the standard. The standard does say that you can't drop a dependent GENERATED column in RESTRICT mode; but that's buried down in a subparagraph, on a different page from some pseudocode that makes it look like an AUTO drop is being suggested. Change this to be more like the way that we handle regular default expressions, ie record the dependencies as NORMAL dependencies of the pg_attrdef entry. Also, make the pg_attrdef entry's dependency on the column itself be INTERNAL not AUTO. That has two effects: * the column will go away, not just lose its default, if any dependency of the expression is dropped with CASCADE. So we don't need any special mechanism to make that happen. * it provides an additional cross-check preventing someone from dropping the default expression without dropping the column. catversion bump because of change in the contents of pg_depend (which also requires a change in one information_schema view). Per bug #17439 from Kevin Humphreys. Although this is a longstanding bug, it seems impractical to back-patch because of the need for catalog contents changes. Discussion: https://postgr.es/m/17439-7df4421197e928f0@postgresql.org 
For GENERATED columns, we record all dependencies of the generation
expression as AUTO dependencies of the column itself.  This means
that the generated column is silently dropped if any dependency
is removed, even if CASCADE wasn't specified.  This is at least
a POLA violation, but I think it's actually based on a misreading
of the standard.  The standard does say that you can't drop a
dependent GENERATED column in RESTRICT mode; but that's buried down
in a subparagraph, on a different page from some pseudocode that
makes it look like an AUTO drop is being suggested.

Change this to be more like the way that we handle regular default
expressions, ie record the dependencies as NORMAL dependencies of
the pg_attrdef entry.  Also, make the pg_attrdef entry's dependency
on the column itself be INTERNAL not AUTO.  That has two effects:

* the column will go away, not just lose its default, if any
dependency of the expression is dropped with CASCADE.  So we
don't need any special mechanism to make that happen.

* it provides an additional cross-check preventing someone from
dropping the default expression without dropping the column.

catversion bump because of change in the contents of pg_depend
(which also requires a change in one information_schema view).

Per bug #17439 from Kevin Humphreys.  Although this is a longstanding
bug, it seems impractical to back-patch because of the need for
catalog contents changes.

Discussion: https://postgr.es/m/17439-7df4421197e928f0@postgresql.org
Add UNIQUE null treatment option 2022-02-03T10:48:21+00:00 Peter Eisentraut peter@eisentraut.org 2022-02-03T10:29:54+00:00 94aa7cc5f707712f592885995a28e018c7c80488 The SQL standard has been ambiguous about whether null values in unique constraints should be considered equal or not. Different implementations have different behaviors. In the SQL:202x draft, this has been formalized by making this implementation-defined and adding an option on unique constraint definitions UNIQUE [ NULLS [NOT] DISTINCT ] to choose a behavior explicitly. This patch adds this option to PostgreSQL. The default behavior remains UNIQUE NULLS DISTINCT. Making this happen in the btree code is pretty easy; most of the patch is just to carry the flag around to all the places that need it. The CREATE UNIQUE INDEX syntax extension is not from the standard, it's my own invention. I named all the internal flags, catalog columns, etc. in the negative ("nulls not distinct") so that the default PostgreSQL behavior is the default if the flag is false. Reviewed-by: Maxim Orlov <orlovmg@gmail.com> Reviewed-by: Pavel Borisov <pashkin.elfe@gmail.com> Discussion: https://www.postgresql.org/message-id/flat/84e5ee1b-387e-9a54-c326-9082674bde78@enterprisedb.com 
The SQL standard has been ambiguous about whether null values in
unique constraints should be considered equal or not.  Different
implementations have different behaviors.  In the SQL:202x draft, this
has been formalized by making this implementation-defined and adding
an option on unique constraint definitions UNIQUE [ NULLS [NOT]
DISTINCT ] to choose a behavior explicitly.

This patch adds this option to PostgreSQL.  The default behavior
remains UNIQUE NULLS DISTINCT.  Making this happen in the btree code
is pretty easy; most of the patch is just to carry the flag around to
all the places that need it.

The CREATE UNIQUE INDEX syntax extension is not from the standard,
it's my own invention.

I named all the internal flags, catalog columns, etc. in the negative
("nulls not distinct") so that the default PostgreSQL behavior is the
default if the flag is false.

Reviewed-by: Maxim Orlov <orlovmg@gmail.com>
Reviewed-by: Pavel Borisov <pashkin.elfe@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/84e5ee1b-387e-9a54-c326-9082674bde78@enterprisedb.com
Update copyright for 2022 2022-01-08T00:04:57+00:00 Bruce Momjian bruce@momjian.us 2022-01-08T00:04:57+00:00 27b77ecf9f4d5be211900eda54d8155ada50d696 Backpatch-through: 10 
Backpatch-through: 10
Add DISTINCT to information schema usage views 2021-04-21T09:54:47+00:00 Peter Eisentraut peter@eisentraut.org 2021-04-21T09:54:47+00:00 d84ffffe582b8e036a14c6bc2378df29167f3a00 Since pg_depend can contain duplicate entries, we need to eliminate those in information schema views that build on pg_depend, using DISTINCT. Some of the older views already did that correctly, but some of the more recently added ones didn't. (In some of these views, it might not be possible to reproduce the issue because of how the implementation happens to deduplicate dependencies while recording them, but it seems better to keep this consistent in all cases.) 
Since pg_depend can contain duplicate entries, we need to eliminate
those in information schema views that build on pg_depend, using
DISTINCT.  Some of the older views already did that correctly, but
some of the more recently added ones didn't.  (In some of these views,
it might not be possible to reproduce the issue because of how the
implementation happens to deduplicate dependencies while recording
them, but it seems better to keep this consistent in all cases.)
Convert built-in SQL-language functions to SQL-standard-body style. 2021-04-16T22:37:02+00:00 Tom Lane tgl@sss.pgh.pa.us 2021-04-16T22:36:45+00:00 767982e36298be4da44a063e36261e9cfdc0bf49 Adopt the new pre-parsed representation for all built-in and information_schema SQL-language functions, except for a small number that can't presently be converted because they have polymorphic arguments. This eliminates residual hazards around search-path safety of these functions, and might provide some small performance benefits by reducing parsing costs. It seems useful also to provide more test coverage for the SQL-standard-body feature. Discussion: https://postgr.es/m/3956760.1618529139@sss.pgh.pa.us 
Adopt the new pre-parsed representation for all built-in and
information_schema SQL-language functions, except for a small
number that can't presently be converted because they have
polymorphic arguments.

This eliminates residual hazards around search-path safety of
these functions, and might provide some small performance benefits
by reducing parsing costs.  It seems useful also to provide more
test coverage for the SQL-standard-body feature.

Discussion: https://postgr.es/m/3956760.1618529139@sss.pgh.pa.us