On 2019-10-17 09:44, Michael Paquier wrote: > I have a question here. Per the notes in information_schema.sql, > SQL_SIZING_PROFILES has been removed in SQL:2011,
OK, we can remove that one as well. New patch attached. > attributes.isnullable and DOMAIN_UDT_USAGE in SQL:2003~. Would it > make sense to cleanup those ones? OK, I'll look into those, but it seems like a separate undertaking. We don't always remove things just because they were dropped by the SQL standard. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>From aec53faf22966ee56ccd812996e40527cc2cea49 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut <pe...@eisentraut.org> Date: Sat, 19 Oct 2019 22:56:18 +0200 Subject: [PATCH v2] Remove obsolete information schema tables Remove SQL_LANGUAGES, which was eliminated in SQL:2008, and SQL_PACKAGES and SQL_SIZING_PROFILES, which were eliminated in SQL:2011. Since they were dropped by the SQL standard, the information in them was no longer updated and therefore no longer useful. This also removes the feature-package association information in sql_feature_packages.txt, but for the time begin we are keeping the information which features are in the Core package (that is, mandatory SQL features). Maybe at some point someone wants to invent a way to store that that does not involve using the "package" mechanism anymore. Discussion https://www.postgresql.org/message-id/flat/91334220-7900-071b-9327-0c6ecd012017%402ndquadrant.com --- doc/src/sgml/features.sgml | 9 +- doc/src/sgml/information_schema.sgml | 216 ------------------- src/backend/catalog/information_schema.sql | 70 ------ src/backend/catalog/sql_feature_packages.txt | 29 --- src/backend/catalog/sql_features.txt | 4 - src/test/regress/expected/sanity_check.out | 3 - 6 files changed, 3 insertions(+), 328 deletions(-) diff --git a/doc/src/sgml/features.sgml b/doc/src/sgml/features.sgml index f767bee46e..2c5a7e5d0c 100644 --- a/doc/src/sgml/features.sgml +++ b/doc/src/sgml/features.sgml @@ -44,10 +44,7 @@ <title>SQL Conformance</title> broad three levels found in <acronym>SQL-92</acronym>. A large subset of these features represents the <quote>Core</quote> features, which every conforming SQL implementation must supply. - The rest of the features are purely optional. Some optional - features are grouped together to form <quote>packages</quote>, which - SQL implementations can claim conformance to, thus claiming - conformance to particular groups of features. + The rest of the features are purely optional. </para> <para> @@ -116,7 +113,7 @@ <title>Supported Features</title> <thead> <row> <entry>Identifier</entry> - <entry>Package</entry> + <entry>Core?</entry> <entry>Description</entry> <entry>Comment</entry> </row> @@ -143,7 +140,7 @@ <title>Unsupported Features</title> <thead> <row> <entry>Identifier</entry> - <entry>Package</entry> + <entry>Core?</entry> <entry>Description</entry> <entry>Comment</entry> </row> diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml index 906fe7819f..7a995a1b64 100644 --- a/doc/src/sgml/information_schema.sgml +++ b/doc/src/sgml/information_schema.sgml @@ -4963,160 +4963,6 @@ <title><literal>sql_implementation_info</literal> Columns</title> </table> </sect1> - <sect1 id="infoschema-sql-languages"> - <title><literal>sql_languages</literal></title> - - <para> - The table <literal>sql_languages</literal> contains one row for - each SQL language binding that is supported by - <productname>PostgreSQL</productname>. - <productname>PostgreSQL</productname> supports direct SQL and - embedded SQL in C; that is all you will learn from this table. - </para> - - <para> - This table was removed from the SQL standard in SQL:2008, so there - are no entries referring to standards later than SQL:2003. - </para> - - <table> - <title><literal>sql_languages</literal> Columns</title> - - <tgroup cols="3"> - <thead> - <row> - <entry>Name</entry> - <entry>Data Type</entry> - <entry>Description</entry> - </row> - </thead> - - <tbody> - <row> - <entry><literal>sql_language_source</literal></entry> - <entry><type>character_data</type></entry> - <entry> - The name of the source of the language definition; always - <literal>ISO 9075</literal>, that is, the SQL standard - </entry> - </row> - - <row> - <entry><literal>sql_language_year</literal></entry> - <entry><type>character_data</type></entry> - <entry> - The year the standard referenced in - <literal>sql_language_source</literal> was approved. - </entry> - </row> - - <row> - <entry><literal>sql_language_conformance</literal></entry> - <entry><type>character_data</type></entry> - <entry> - The standard conformance level for the language binding. For - ISO 9075:2003 this is always <literal>CORE</literal>. - </entry> - </row> - - <row> - <entry><literal>sql_language_integrity</literal></entry> - <entry><type>character_data</type></entry> - <entry>Always null (This value is relevant to an earlier version of the SQL standard.)</entry> - </row> - - <row> - <entry><literal>sql_language_implementation</literal></entry> - <entry><type>character_data</type></entry> - <entry>Always null</entry> - </row> - - <row> - <entry><literal>sql_language_binding_style</literal></entry> - <entry><type>character_data</type></entry> - <entry> - The language binding style, either <literal>DIRECT</literal> or - <literal>EMBEDDED</literal> - </entry> - </row> - - <row> - <entry><literal>sql_language_programming_language</literal></entry> - <entry><type>character_data</type></entry> - <entry> - The programming language, if the binding style is - <literal>EMBEDDED</literal>, else null. <productname>PostgreSQL</productname> only - supports the language C. - </entry> - </row> - </tbody> - </tgroup> - </table> - </sect1> - - <sect1 id="infoschema-sql-packages"> - <title><literal>sql_packages</literal></title> - - <para> - The table <literal>sql_packages</literal> contains information - about which feature packages defined in the SQL standard are - supported by <productname>PostgreSQL</productname>. Refer to <xref - linkend="features"/> for background information on feature packages. - </para> - - <table> - <title><literal>sql_packages</literal> Columns</title> - - <tgroup cols="3"> - <thead> - <row> - <entry>Name</entry> - <entry>Data Type</entry> - <entry>Description</entry> - </row> - </thead> - - <tbody> - <row> - <entry><literal>feature_id</literal></entry> - <entry><type>character_data</type></entry> - <entry>Identifier string of the package</entry> - </row> - - <row> - <entry><literal>feature_name</literal></entry> - <entry><type>character_data</type></entry> - <entry>Descriptive name of the package</entry> - </row> - - <row> - <entry><literal>is_supported</literal></entry> - <entry><type>yes_or_no</type></entry> - <entry> - <literal>YES</literal> if the package is fully supported by the - current version of <productname>PostgreSQL</productname>, <literal>NO</literal> if not - </entry> - </row> - - <row> - <entry><literal>is_verified_by</literal></entry> - <entry><type>character_data</type></entry> - <entry> - Always null, since the <productname>PostgreSQL</productname> development group does not - perform formal testing of feature conformance - </entry> - </row> - - <row> - <entry><literal>comments</literal></entry> - <entry><type>character_data</type></entry> - <entry>Possibly a comment about the supported status of the package</entry> - </row> - </tbody> - </tgroup> - </table> - </sect1> - <sect1 id="infoschema-sql-parts"> <title><literal>sql_parts</literal></title> @@ -5239,68 +5085,6 @@ <title><literal>sql_sizing</literal> Columns</title> </table> </sect1> - <sect1 id="infoschema-sql-sizing-profiles"> - <title><literal>sql_sizing_profiles</literal></title> - - <para> - The table <literal>sql_sizing_profiles</literal> contains - information about the <literal>sql_sizing</literal> values that are - required by various profiles of the SQL standard. <productname>PostgreSQL</productname> does - not track any SQL profiles, so this table is empty. - </para> - - <table> - <title><literal>sql_sizing_profiles</literal> Columns</title> - - <tgroup cols="3"> - <thead> - <row> - <entry>Name</entry> - <entry>Data Type</entry> - <entry>Description</entry> - </row> - </thead> - - <tbody> - <row> - <entry><literal>sizing_id</literal></entry> - <entry><type>cardinal_number</type></entry> - <entry>Identifier of the sizing item</entry> - </row> - - <row> - <entry><literal>sizing_name</literal></entry> - <entry><type>character_data</type></entry> - <entry>Descriptive name of the sizing item</entry> - </row> - - <row> - <entry><literal>profile_id</literal></entry> - <entry><type>character_data</type></entry> - <entry>Identifier string of a profile</entry> - </row> - - <row> - <entry><literal>required_value</literal></entry> - <entry><type>cardinal_number</type></entry> - <entry> - The value required by the SQL profile for the sizing item, or 0 - if the profile places no limit on the sizing item, or null if - the profile does not require any of the features for which the - sizing item is applicable - </entry> - </row> - - <row> - <entry><literal>comments</literal></entry> - <entry><type>character_data</type></entry> - <entry>Possibly a comment pertaining to the sizing item within the profile</entry> - </row> - </tbody> - </tgroup> - </table> - </sect1> - <sect1 id="infoschema-table-constraints"> <title><literal>table_constraints</literal></title> diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql index c42e76ea89..5d64791c5d 100644 --- a/src/backend/catalog/information_schema.sql +++ b/src/backend/catalog/information_schema.sql @@ -1648,56 +1648,6 @@ CREATE TABLE sql_implementation_info ( GRANT SELECT ON sql_implementation_info TO PUBLIC; -/* - * SQL_LANGUAGES table - * apparently removed in SQL:2008 - */ - -CREATE TABLE sql_languages ( - sql_language_source character_data, - sql_language_year character_data, - sql_language_conformance character_data, - sql_language_integrity character_data, - sql_language_implementation character_data, - sql_language_binding_style character_data, - sql_language_programming_language character_data -); - -INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'DIRECT', NULL); -INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'EMBEDDED', 'C'); -INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'DIRECT', NULL); -INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'EMBEDDED', 'C'); - -GRANT SELECT ON sql_languages TO PUBLIC; - - -/* - * SQL_PACKAGES table - * removed in SQL:2011 - */ - -CREATE TABLE sql_packages ( - feature_id character_data, - feature_name character_data, - is_supported yes_or_no, - is_verified_by character_data, - comments character_data -); - -INSERT INTO sql_packages VALUES ('PKG000', 'Core', 'NO', NULL, ''); -INSERT INTO sql_packages VALUES ('PKG001', 'Enhanced datetime facilities', 'YES', NULL, ''); -INSERT INTO sql_packages VALUES ('PKG002', 'Enhanced integrity management', 'NO', NULL, ''); -INSERT INTO sql_packages VALUES ('PKG003', 'OLAP facilities', 'NO', NULL, ''); -INSERT INTO sql_packages VALUES ('PKG004', 'PSM', 'NO', NULL, 'PL/pgSQL is similar.'); -INSERT INTO sql_packages VALUES ('PKG005', 'CLI', 'NO', NULL, 'ODBC is similar.'); -INSERT INTO sql_packages VALUES ('PKG006', 'Basic object support', 'NO', NULL, ''); -INSERT INTO sql_packages VALUES ('PKG007', 'Enhanced object support', 'NO', NULL, ''); -INSERT INTO sql_packages VALUES ('PKG008', 'Active database', 'NO', NULL, ''); -INSERT INTO sql_packages VALUES ('PKG010', 'OLAP', 'NO', NULL, 'NO'); - -GRANT SELECT ON sql_packages TO PUBLIC; - - /* * 5.59 * SQL_PARTS table @@ -1768,26 +1718,6 @@ CREATE TABLE sql_sizing ( GRANT SELECT ON sql_sizing TO PUBLIC; -/* - * SQL_SIZING_PROFILES table - * removed in SQL:2011 - */ - --- The data in this table are defined by various profiles of SQL. --- Since we don't have any information about such profiles, we provide --- an empty table. - -CREATE TABLE sql_sizing_profiles ( - sizing_id cardinal_number, - sizing_name character_data, - profile_id character_data, - required_value cardinal_number, - comments character_data -); - -GRANT SELECT ON sql_sizing_profiles TO PUBLIC; - - /* * 5.61 * TABLE_CONSTRAINTS view diff --git a/src/backend/catalog/sql_feature_packages.txt b/src/backend/catalog/sql_feature_packages.txt index 382e4aea5c..1d87a3e31e 100644 --- a/src/backend/catalog/sql_feature_packages.txt +++ b/src/backend/catalog/sql_feature_packages.txt @@ -22,46 +22,17 @@ F021 Core F031 Core F041 Core F051 Core -F052 Enhanced datetime facilities F081 Core F131 Core F181 Core -F191 Enhanced integrity management F201 Core F221 Core F261 Core F311 Core -F411 Enhanced datetime facilities F471 Core F481 Core -F491 Enhanced integrity management F501 Core -F521 Enhanced integrity management -F555 Enhanced datetime facilities -F671 Enhanced integrity management -F701 Enhanced integrity management F812 Core S011 Core -S023 Basic object support -S024 Enhanced object support -S041 Basic object support -S043 Enhanced object support -S051 Basic object support -S071 Enhanced object support -S081 Enhanced object support -S111 Enhanced object support -S151 Basic object support -S161 Enhanced object support -S211 Enhanced object support -S231 Enhanced object support -T041 Basic object support -T191 Enhanced integrity management -T201 Enhanced integrity management -T211 Active database -T211 Enhanced integrity management -T212 Enhanced integrity management T321 Core -T322 PSM -T431 OLAP -T611 OLAP T631 Core diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index 9c68292a54..ab3e381cff 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -285,11 +285,7 @@ F492 Optional table constraint enforcement NO F501 Features and conformance views YES F501 Features and conformance views 01 SQL_FEATURES view YES F501 Features and conformance views 02 SQL_SIZING view YES -F501 Features and conformance views 03 SQL_LANGUAGES view YES F502 Enhanced documentation tables YES -F502 Enhanced documentation tables 01 SQL_SIZING_PROFILES view YES -F502 Enhanced documentation tables 02 SQL_IMPLEMENTATION_INFO view YES -F502 Enhanced documentation tables 03 SQL_PACKAGES view YES F521 Assertions NO F531 Temporary tables YES F555 Enhanced seconds precision YES diff --git a/src/test/regress/expected/sanity_check.out b/src/test/regress/expected/sanity_check.out index d6e75ffce6..070de78e85 100644 --- a/src/test/regress/expected/sanity_check.out +++ b/src/test/regress/expected/sanity_check.out @@ -177,11 +177,8 @@ shighway|t slow_emp4000|f sql_features|f sql_implementation_info|f -sql_languages|f -sql_packages|f sql_parts|f sql_sizing|f -sql_sizing_profiles|f stud_emp|f student|f tableam_parted_a_heap2|f -- 2.23.0
