Re: SQL_SIZING view - Mailing list pgsql-hackers

According to:

http://www.itl.nist.gov/fipspubs/fip127-2.htm#FIPS_TOP


15.2 SQL_Sizing table. The SQL_SIZING table shall consist of exactly one
row for each FIPS SQL database construct defined in Section 16.6 of this
standard. The SIZING_ID and DESCRIPTION columns identify the database
construct by the integer and description assigned to it in Section 16.6.
The ENTRY_VALUE column is equal to the default Entry SQL value defined
for that construct by FIPS SQL in Section 16.6, with "*" converted to a
Null value. The INTERMEDIATE_VALUE column is equal to the default
Intermediate SQL value defined for that construct by FIPS SQL in Section
16.6, with "*" converted to a Null value. The VALUE_SUPPORTED column
indicates a value for the construct that is supported by an
implementation when data in the identified catalog is accessed through
that implementation; if this value is Null, then there is no explicit
restriction on the size of that construct. A user must be able to depend
upon these values when executing SQL-statements against data in the
catalog. If a given catalog spans multiple SQL-Server implementations,
then the VALUE_SUPPORTED shall be valid in all of them. It is important
to recognize that FIPS sizing defaults are not requirements for
conformance to FIPS SQL; instead, they identify a default value that is
assumed to be specified if a Federal SQL procurement is silent on that
topic. For this reason, the VALUE_SUPPORTED may sometimes be less than
the FIPS default for the ENTRY_VALUE or the INTERMEDIATE_VALUE, even for
a FIPS SQL conforming implementation. The SIZING_COMMENTS column is
intended for any vendor comments pertinent to the identified FIPS SQL
database construct.


16.6 Sizing for database constructs. References to this standard in a
procurement should indicate minimum requirements for the precision,
size, or number of occurrences of database constructs. Failure to make
this indication means that the Entry Values detailed below are by
default the minimum requirements for Entry SQL or Transitional SQL
procurements and the Intermediate Values detailed below are by default
the minimum requirements for Intermediate SQL or Full SQL procurements.
  Sizing                                                 Entry    Interm.    Id        Description
         Value    Value 
    1.  Length of an identifier                            18      128    2   CHARACTER max length
       240     1000    3.  CHARACTER VARYING max length                      254     1000    4.  BIT max length in bits
                            *     8000    5.  BIT VARYING max length in bits                      *     8000    6.
NATIONALCHARACTER max length                       *      500    7.  NATIONAL CHAR VARYING max length
*      500    8.  NUMERIC decimal precision                          15       15    9.  DECIMAL decimal precision
                  15       15    10. INTEGER decimal precision                           9        *    11. INTEGER
binaryprecision                            *       31    12. SMALLINT decimal precision                          4
 *    13. SMALLINT binary precision                           *       15    14. FLOAT binary mantissa precision
          20       47    15. FLOAT binary exponent precision                     *        9    16. REAL binary mantissa
precision                    20       23    17. REAL binary exponent precision                      *        7    18.
DOUBLEPRECISION binary mantissa precision         30       47    19. DOUBLE PRECISION binary exponent precision
*        9    20. TIME decimal fractional second precision            *        0    21. TIMESTAMP decimal fractional
secondprecision       *        6    22. INTERVAL decimal fractional second precision        *        6    23. INTERVAL
decimalleading field precision            *        7    24. Columns in a table                                100
250   25. Values in an INSERT statement                     100      250    26. Set clauses in UPDATE statement
          20      250    27. Length of a row (see Note 1)                     2000     8000    28. Columns in UNIQUE
constraint                       6       15    29. Length of UNIQUE columns (Note 1)                 120      750
30.Columns in GROUP BY column list                     6       15    31. Length of GROUP BY column list (Note 1)
  120      750    32. Sort items in ORDER BY clause                       6       15    33. Length of ORDER BY column
list(Note 1)           120      750    34. Referencing columns in FOREIGN KEY                  6       15    35. Length
ofFOREIGN KEY column list (Note 1)        120      750    36. Table references in an SQL statement (Note 3)      15
 50    37. Cursors simultaneously open                        10      100    38. WHEN clauses in a CASE expression
            *       50    39. Columns in a named columns JOIN                     *       15    40. Length of JOIN
columnlist (Note 1)                 *      750    41. Items in a SELECT list                            100      250
42.Length of SQL  (Note 2)                             *    30000    43. Length of  (Note 2)
    *     4000    44. Length of  (Note 2)                                 *     
4000    45. Occurrences in an ALLOCATE DESCRIPTOR               *      100    46. Default occurrences in ALLOCATE
DESCRIPTOR         *      100 

       Note 1: The length of a collection of columns is conservatively       estimated to be no larger than the sum of:
twicethe number of       columns,OCTET_LENGTH of each character or bit column (see       Subclause 6.6, (numeric value
function),of X3.135-1992),       decimal precision plus 1 of each exact numeric column, binary       precision divided
by4 plus 1 of each approximate numeric       column, 10 for each DATE column, 8 for each TIME column, 14 for       each
TIMEWITH TIME ZONE column, 19 for each TIMESTAMP column,       25 for each TIMESTAMP WITH TIME ZONE column, and 20 for
each      INTERVAL column. In addition, if any DATE, TIME, TIMESTAMP, or       INTERVAL column has a non-zero
fractionalseconds precision,       then add that precision plus 1 to the length of the column.
Note2: The length of an SQL statement is defined to be the       result of applying the OCTET_LENGTH function (see
Subclause6.6,       (numeric value function), of X3.135-1992) to the SQL statement       with the SQL statement
consideredto be an instance of a       CHARACTER VARYING data type.                      Note 3: The number of table
referencesin an SQL statement is       the sum of: the number of views and base tables named in the       statement,
thenumber of underlying views and tables (see       Subclause 4.9, "Tables", of X3.135-1992) for each derived table
 or cursor, and the number of ) (either given in the SQL       statement or contained in some view named in the SQL
statement)      not directly associated with a named table or view.         
Some applications may have requirements for CHARACTER VARYING or BIT
VARYING data types with lengths much longer than the Entry SQL or
Intermediate SQL values specified above. This is particularly true for
applications that need to manage large Audio, Graphics, Text, or Video
objects. Some applications have requirements for Audio, Text, or
Graphics objects in excess of 2-3 million bytes, or Video objects in
excess of multiple gigabytes. Implementations that provide such data
types often impose severe restrictions in how these very large objects
can be referenced in SQL definitions and statements. For example, a very
long CHARACTER VARYING data type may not be allowed to participate in a
PRIMARY KEY, a UNIQUE constraint, a REFERENTIAL constraint, a ) a GROUP
BY or HAVING clause, or an ORDER BY in a cursor definition. Applications
that stay within the limits specified above should not encounter any
unexpected restrictions in how these constructs can be used or
referenced in SQL language.

Some implementations address user requirements for very large objects,
with a minimum number of restrictions, by allowing arbitrarily large
maximum length declarations for CHARACTER VARYING or BIT VARYING, with
an internal representation using some sort of indirect addressing
mechanism. In this way they can keep the physical length of the row in
which the object is represented less than the physical page size of the
operating system environment, often necessary for lock management, while
at the same time meet user requirements for storing, retrieving, and
managing large objects. SQL procurements that anticipate requirements
for very long CHARACTER VARYING or BIT VARYING data types should be very
explicit in procurement specifications about additional requirements for
how these large data types interface to external processors or how they
need to be processed by SQL language.

On Mon, 2003-01-06 at 13:48, Peter Eisentraut wrote:
> The information schema is supposed to contain a view SQL_SIZING which is
> defined thus:
>
>          List the sizing items defined in this standard and, for each of
>          these, indicate the size supported by the SQL-implementation.
>
> But the standard does not define any "sizing item" or anything like that
> anywhere.
>
> Unless someone can offer an explanation, I am inclined to just supply an
> empty table and check off this item.
--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc