Re: I do not get the point of the information_schema

On 2018-02-13 16:06:43 -0700, David G. Johnston wrote:
> On Tue, Feb 13, 2018 at 3:57 PM, Peter J. Holzer <hjp-pgsql(at)hjp(dot)at> wrote:
>
> (That said, it looks like both PostgreSQL and MariaDB include additional
> columns beyond those mandated by the standard - you can't rely on those,
> of course. And some databases like Oracle don't even have an information
> schema.)
>
>
> Given the documented charter of information_schema I'd present your conclusion
> and evidence to pgsql-bugs...while I suppose extra columns are not inherently
> harmful at minimum they would need to be documented if kept.

I don't have evidence, as I don't have access to a recent SQL standard.
But I noticed that for example information_schema.tables have only a few
columns in common between PostgreSQL and MariaDB:

hjp=> select * from information_schema.tables where table_type='BASE TABLE' limit 1;
─[ RECORD 1 ]────────────────┬───────────
table_catalog │ hjp
table_schema │ public
table_name │ hjpnet
table_type │ BASE TABLE
self_referencing_column_name │ (∅)
reference_generation │ (∅)
user_defined_type_catalog │ (∅)
user_defined_type_schema │ (∅)
user_defined_type_name │ (∅)
is_insertable_into │ YES
is_typed │ NO
commit_action │ (∅)

MariaDB [simba]> select * from information_schema.tables where table_type='BASE TABLE' limit 1\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: simba
TABLE_NAME: archived_versions
TABLE_TYPE: BASE TABLE
ENGINE: MyISAM
VERSION: 10
ROW_FORMAT: Fixed
TABLE_ROWS: 0
AVG_ROW_LENGTH: 0
DATA_LENGTH: 0
MAX_DATA_LENGTH: 7036874417766399
INDEX_LENGTH: 1024
DATA_FREE: 0
AUTO_INCREMENT: 1
CREATE_TIME: 2008-01-28 01:24:48
UPDATE_TIME: 2008-01-28 01:24:48
CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
CHECKSUM: NULL
CREATE_OPTIONS:
TABLE_COMMENT:
1 row in set (0.01 sec)

The first 4 are the same, all others are different.
It is possible that all the columns that PostgreSQL has are required by
the standard and that MariaDB is non-conforming by omitting them, but at
least some of the names look quite PostgreSQL-specific to me. So my
guess is that the standard only requires the first 4 and the rest are
RDBMS-specific.

hp

--
_ | Peter J. Holzer | we build much bigger, better disasters now
|_|_) | | because we have much more sophisticated
| | | hjp(at)hjp(dot)at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>