Repair memory leaks in plpython.

[pgsql.git] / doc / src / sgml / features.sgml

<!-- doc/src/sgml/features.sgml -->

<appendix id="features">
 <title>SQL Conformance</title>

 <para>
  This section attempts to outline to what extent
  <productname>PostgreSQL</productname> conforms to the current SQL
  standard.  The following information is not a full statement of
  conformance, but it presents the main topics in as much detail as is
  both reasonable and useful for users.
 </para>

 <para>
  The formal name of the SQL standard is ISO/IEC 9075 <quote>Database
  Language SQL</quote>.  A revised version of the standard is released
  from time to time; the most recent update appearing in 2023.
  The 2023 version is referred to as ISO/IEC 9075:2023, or simply as SQL:2023.
  The versions prior to that were SQL:2016, SQL:2011, SQL:2008, SQL:2006, SQL:2003,
  SQL:1999, and SQL-92.  Each version
  replaces the previous one, so claims of conformance to earlier
  versions have no official merit.
  <productname>PostgreSQL</productname> development aims for
  conformance with the latest official version of the standard where
  such conformance does not contradict traditional features or common
  sense.  Many of the features required by the SQL
  standard are supported, though sometimes with slightly differing
  syntax or function.  Further moves towards conformance can be
  expected over time.
 </para>

 <para>
  <acronym>SQL-92</acronym> defined three feature sets for
  conformance: Entry, Intermediate, and Full.  Most database
  management systems claiming <acronym>SQL</acronym> standard
  conformance were conforming at only the Entry level, since the
  entire set of features in the Intermediate and Full levels was
  either too voluminous or in conflict with legacy behaviors.
 </para>

 <para>
  Starting with <acronym>SQL:1999</acronym>, the SQL standard defines
  a large set of individual features rather than the ineffectively
  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.
 </para>

 <para>
  The standard is split into a number of parts, each also known by a shorthand
  name:

  <itemizedlist>
   <listitem><para>ISO/IEC 9075-1 Framework (SQL/Framework)</para><indexterm><primary>SQL/Framework</primary></indexterm></listitem>
   <listitem><para>ISO/IEC 9075-2 Foundation (SQL/Foundation)</para><indexterm><primary>SQL/Foundation</primary></indexterm></listitem>
   <listitem><para>ISO/IEC 9075-3 Call Level Interface (SQL/CLI)</para><indexterm><primary>SQL/CLI</primary></indexterm></listitem>
   <listitem><para>ISO/IEC 9075-4 Persistent Stored Modules (SQL/PSM)</para><indexterm><primary>SQL/PSM</primary></indexterm></listitem>
   <listitem><para>ISO/IEC 9075-9 Management of External Data (SQL/MED)</para><indexterm><primary>SQL/MED</primary></indexterm></listitem>
   <listitem><para>ISO/IEC 9075-10 Object Language Bindings (SQL/OLB)</para><indexterm><primary>SQL/OLB</primary></indexterm></listitem>
   <listitem><para>ISO/IEC 9075-11 Information and Definition Schemas (SQL/Schemata)</para><indexterm><primary>SQL/Schemata</primary></indexterm></listitem>
   <listitem><para>ISO/IEC 9075-13 Routines and Types using the Java Language (SQL/JRT)</para><indexterm><primary>SQL/JRT</primary></indexterm></listitem>
   <listitem><para>ISO/IEC 9075-14 XML-related specifications (SQL/XML)</para><indexterm><primary>SQL/XML</primary></indexterm></listitem>
   <listitem><para>ISO/IEC 9075-15 Multi-dimensional arrays (SQL/MDA)</para><indexterm><primary>SQL/MDA</primary></indexterm></listitem>
   <listitem><para>ISO/IEC 9075-16 Property Graph Queries (SQL/PGQ)</para><indexterm><primary>SQL/PGQ</primary></indexterm></listitem>
  </itemizedlist>

  Note that some part numbers are not (or no longer) used.
 </para>

 <para>
  The <productname>PostgreSQL</productname> core covers parts 1, 2, 9,
  11, and 14.  Part 3 is covered by the ODBC driver, and part 13 is
  covered by the PL/Java plug-in, but exact conformance is currently
  not being verified for these components.  There are currently no
  implementations of parts 4, 10, 15, and 16
  for <productname>PostgreSQL</productname>.
 </para>

 <para>
  PostgreSQL supports most of the major features of SQL:2023.  Out of
  177 mandatory features required for full Core conformance,
  PostgreSQL conforms to at least 170.  In addition, there is a long
  list of supported optional features.  It might be worth noting that at
  the time of writing, no current version of any database management
  system claims full conformance to Core SQL:2023.
 </para>

 <para>
  In the following two sections, we provide a list of those features
  that <productname>PostgreSQL</productname> supports, followed by a
  list of the features defined in <acronym>SQL:2023</acronym> which
  are not yet supported in <productname>PostgreSQL</productname>.
  Both of these lists are approximate: There might be minor details that
  are nonconforming for a feature that is listed as supported, and
  large parts of an unsupported feature might in fact be implemented.
  The main body of the documentation always contains the most accurate
  information about what does and does not work.
 </para>

 <note>
  <para>
   Feature codes containing a hyphen are subfeatures.  Therefore, if a
   particular subfeature is not supported, the main feature is listed
   as unsupported even if some other subfeatures are supported.
  </para>
 </note>

  <sect1 id="features-sql-standard">
   <title>Supported Features</title>

   <para>
    <informaltable>
     <tgroup cols="4">
      <colspec colname="col1" colwidth="1.5*"/>
      <colspec colname="col2" colwidth="1*"/>
      <colspec colname="col3" colwidth="7*"/>
      <colspec colname="col4" colwidth="3*"/>
      <thead>
       <row>
        <entry>Identifier</entry>
        <entry>Core?</entry>
        <entry>Description</entry>
        <entry>Comment</entry>
       </row>
      </thead>

      &features-supported;

     </tgroup>
    </informaltable>
   </para>
  </sect1>

  <sect1 id="unsupported-features-sql-standard">
   <title>Unsupported Features</title>

   <para>
    The following features defined in <acronym>SQL:2023</acronym> are not
    implemented in this release of
    <productname>PostgreSQL</productname>. In a few cases, equivalent
    functionality is available.

    <informaltable>
     <tgroup cols="4">
      <colspec colname="col1" colwidth="1.5*"/>
      <colspec colname="col2" colwidth="1*"/>
      <colspec colname="col3" colwidth="7*"/>
      <colspec colname="col4" colwidth="3*"/>
      <thead>
       <row>
        <entry>Identifier</entry>
        <entry>Core?</entry>
        <entry>Description</entry>
        <entry>Comment</entry>
       </row>
      </thead>

      &features-unsupported;

     </tgroup>
    </informaltable>
   </para>
  </sect1>

  <sect1 id="xml-limits-conformance">
   <title>XML Limits and Conformance to SQL/XML</title>

   <indexterm>
    <primary>SQL/XML</primary>
    <secondary>limits and conformance</secondary>
   </indexterm>

   <para>
    Significant revisions to the XML-related specifications in ISO/IEC 9075-14
    (SQL/XML) were introduced with SQL:2006.
    <productname>PostgreSQL</productname>'s implementation of the XML data
    type and related functions largely follows the earlier 2003 edition,
    with some borrowing from later editions.  In particular:
    <itemizedlist>
     <listitem>
      <para>
       Where the current standard provides a family of XML data types
       to hold <quote>document</quote> or <quote>content</quote> in
       untyped or XML Schema-typed variants, and a type
       <type>XML(SEQUENCE)</type> to hold arbitrary pieces of XML content,
       <productname>PostgreSQL</productname> provides the single
       <type>xml</type> type, which can hold <quote>document</quote> or
       <quote>content</quote>.  There is no equivalent of the
       standard's <quote>sequence</quote> type.
      </para>
     </listitem>

     <listitem>
      <para>
       <productname>PostgreSQL</productname> provides two functions
       introduced in SQL:2006, but in variants that use the XPath 1.0
       language, rather than XML Query as specified for them in the
       standard.
      </para>
     </listitem>

     <listitem>
      <para>
       <productname>PostgreSQL</productname> does not support the
       <literal>RETURNING CONTENT</literal> or <literal>RETURNING SEQUENCE</literal>
       clauses, functions which are defined to have these in the specification
       are implicitly returning content.
      </para>
     </listitem>
    </itemizedlist>
   </para>

   <para>
    This section presents some of the resulting differences you may encounter.
   </para>

   <sect2 id="functions-xml-limits-xpath1">
    <title>Queries Are Restricted to XPath 1.0</title>

    <para>
     The <productname>PostgreSQL</productname>-specific functions
     <function>xpath()</function> and <function>xpath_exists()</function>
     query XML documents using the XPath language.
     <productname>PostgreSQL</productname> also provides XPath-only variants
     of the standard functions <function>XMLEXISTS</function> and
     <function>XMLTABLE</function>, which officially use
     the XQuery language. For all of these functions,
     <productname>PostgreSQL</productname> relies on the
     <application>libxml2</application> library, which provides only XPath 1.0.
    </para>

    <para>
     There is a strong connection between the XQuery language and XPath
     versions 2.0 and later: any expression that is syntactically valid and
     executes successfully in both produces the same result (with a minor
     exception for expressions containing numeric character references or
     predefined entity references, which XQuery replaces with the
     corresponding character while XPath leaves them alone).  But there is
     no such connection between these languages and XPath 1.0; it was an
     earlier language and differs in many respects.
    </para>

    <para>
     There are two categories of limitation to keep in mind: the restriction
     from XQuery to XPath for the functions specified in the SQL standard, and
     the restriction of XPath to version 1.0 for both the standard and the
     <productname>PostgreSQL</productname>-specific functions.
    </para>

    <sect3 id="functions-xml-limits-xpath1-xquery-restriction">
     <title>Restriction of XQuery to XPath</title>

     <para>
      Features of XQuery beyond those of XPath include:

      <itemizedlist>
       <listitem>
        <para>
         XQuery expressions can construct and return new XML nodes, in
         addition to all possible XPath values.  XPath can create and return
         values of the atomic types (numbers, strings, and so on) but can
         only return XML nodes that were already present in documents
         supplied as input to the expression.
        </para>
       </listitem>

       <listitem>
        <para>
         XQuery has control constructs for iteration, sorting, and grouping.
        </para>
       </listitem>

       <listitem>
        <para>
         XQuery allows declaration and use of local functions.
        </para>
       </listitem>
      </itemizedlist>
     </para>

     <para>
      Recent XPath versions begin to offer capabilities overlapping with
      these (such as functional-style <function>for-each</function> and
      <function>sort</function>, anonymous functions, and
      <function>parse-xml</function> to create a node from a string),
      but such features were not available before XPath 3.0.
     </para>
    </sect3>

    <sect3 id="xml-xpath-1-specifics">
     <title>Restriction of XPath to 1.0</title>

     <para>
      For developers familiar with XQuery and XPath 2.0 or later, XPath 1.0
      presents a number of differences to contend with:

      <itemizedlist>
       <listitem>
        <para>
         The fundamental type of an XQuery/XPath expression, the
         <type>sequence</type>, which can contain XML nodes, atomic values,
         or both, does not exist in XPath 1.0. A 1.0 expression can only
         produce a node-set (containing zero or more XML nodes), or a single
         atomic value.
        </para>
       </listitem>

       <listitem>
        <para>
          Unlike an XQuery/XPath sequence, which can contain any desired
          items in any desired order, an XPath 1.0 node-set has no
          guaranteed order and, like any set, does not allow multiple
          appearances of the same item.
         <note>
          <para>
           The <application>libxml2</application> library does seem to
           always return node-sets to <productname>PostgreSQL</productname>
           with their members in the same relative order they had in the
           input document.  Its documentation does not commit to this
           behavior, and an XPath 1.0 expression cannot control it.
          </para>
         </note>
        </para>
       </listitem>

       <listitem>
        <para>
         While XQuery/XPath provides all of the types defined in XML Schema
         and many operators and functions over those types, XPath 1.0 has only
         node-sets and the three atomic types <type>boolean</type>,
         <type>double</type>, and <type>string</type>.
        </para>
       </listitem>

       <listitem>
        <para>
         XPath 1.0 has no conditional operator. An XQuery/XPath expression
         such as <literal>if ( hat ) then hat/@size else "no hat"</literal>
         has no XPath 1.0 equivalent.
        </para>
       </listitem>

       <listitem>
        <para>
         XPath 1.0 has no ordering comparison operator for strings. Both
         <literal>"cat" &lt; "dog"</literal> and
         <literal>"cat" &gt; "dog"</literal> are false, because each is a
         numeric comparison of two <literal>NaN</literal>s. In contrast,
         <literal>=</literal> and <literal>!=</literal> do compare the strings
         as strings.
        </para>
       </listitem>

       <listitem>
        <para>
         XPath 1.0 blurs the distinction between
         <firstterm>value comparisons</firstterm> and
         <firstterm>general comparisons</firstterm> as XQuery/XPath define
         them.  Both <literal>sale/@hatsize = 7</literal> and
         <literal>sale/@customer = "alice"</literal> are existentially
         quantified comparisons, true if there is
         any <literal>sale</literal> with the given value for the
         attribute, but <literal>sale/@taxable = false()</literal> is a
         value comparison to the
         <firstterm>effective boolean value</firstterm> of a whole node-set.
         It is true only if no <literal>sale</literal> has
         a <literal>taxable</literal> attribute at all.
        </para>
       </listitem>

       <listitem>
        <para>
         In the XQuery/XPath data model, a <firstterm>document
         node</firstterm> can have either document form (i.e., exactly one
         top-level element, with only comments and processing instructions
         outside of it) or content form (with those constraints
         relaxed). Its equivalent in XPath 1.0, the
         <firstterm>root node</firstterm>, can only be in document form.
         This is part of the reason an <type>xml</type> value passed as the
         context item to any <productname>PostgreSQL</productname>
         XPath-based function must be in document form.
        </para>
       </listitem>
      </itemizedlist>
     </para>

     <para>
      The differences highlighted here are not all of them. In XQuery and
      the 2.0 and later versions of XPath, there is an XPath 1.0 compatibility
      mode, and the W3C lists of
      <ulink url='https://www.w3.org/TR/2010/REC-xpath-functions-20101214/#xpath1-compatibility'>function library changes</ulink>
      and
      <ulink url='https://www.w3.org/TR/xpath20/#id-backwards-compatibility'>language changes</ulink>
      applied in that mode offer a more complete (but still not exhaustive)
      account of the differences.  The compatibility mode cannot make the
      later languages exactly equivalent to XPath 1.0.
     </para>
    </sect3>

    <sect3 id="functions-xml-limits-casts">
     <title>Mappings between SQL and XML Data Types and Values</title>

     <para>
      In SQL:2006 and later, both directions of conversion between standard SQL
      data types and the XML Schema types are specified precisely. However, the
      rules are expressed using the types and semantics of XQuery/XPath, and
      have no direct application to the different data model of XPath 1.0.
     </para>

     <para>
      When <productname>PostgreSQL</productname> maps SQL data values to XML
      (as in <function>xmlelement</function>), or XML to SQL (as in the output
      columns of <function>xmltable</function>), except for a few cases
      treated specially, <productname>PostgreSQL</productname> simply assumes
      that the XML data type's XPath 1.0 string form will be valid as the
      text-input form of the SQL datatype, and conversely. This rule has the
      virtue of simplicity while producing, for many data types, results similar
      to the mappings specified in the standard.
     </para>

     <para>
      Where interoperability with other systems is a concern, for some data
      types, it may be necessary to use data type formatting functions (such
      as those in <xref linkend="functions-formatting"/>) explicitly to
      produce the standard mappings.
     </para>
    </sect3>
   </sect2>

   <sect2 id="functions-xml-limits-postgresql">
    <title>Incidental Limits of the Implementation</title>

    <para>
     This section concerns limits that are not inherent in the
     <application>libxml2</application> library, but apply to the current
     implementation in <productname>PostgreSQL</productname>.
    </para>

    <sect3 id="functions-xml-limits-postgresql-by-value-only">
     <title>Only <literal>BY VALUE</literal> Passing Mechanism Is Supported</title>

     <para>
      The SQL standard defines two <firstterm>passing mechanisms</firstterm>
      that apply when passing an XML argument from SQL to an XML function or
      receiving a result: <literal>BY REF</literal>, in which a particular XML
      value retains its node identity, and <literal>BY VALUE</literal>, in which
      the content of the XML is passed but node identity is not preserved. A
      mechanism can be specified before a list of parameters, as the default
      mechanism for all of them, or after any parameter, to override the
      default.
     </para>

     <para>
      To illustrate the difference, if
      <replaceable>x</replaceable> is an XML value, these two queries in
      an SQL:2006 environment would produce true and false, respectively:

<programlisting>
SELECT XMLQUERY('$a is $b' PASSING BY REF <replaceable>x</replaceable> AS a, <replaceable>x</replaceable> AS b NULL ON EMPTY);
SELECT XMLQUERY('$a is $b' PASSING BY VALUE <replaceable>x</replaceable> AS a, <replaceable>x</replaceable> AS b NULL ON EMPTY);
</programlisting>
     </para>

     <para>
      <productname>PostgreSQL</productname> will accept
      <literal>BY VALUE</literal> or <literal>BY REF</literal> in an
      <function>XMLEXISTS</function> or <function>XMLTABLE</function>
      construct, but it ignores them.  The <type>xml</type> data type holds
      a character-string serialized representation, so there is no node
      identity to preserve, and passing is always effectively <literal>BY
      VALUE</literal>.
     </para>
    </sect3>

    <sect3 id="functions-xml-limits-postgresql-named-parameters">
     <title>Cannot Pass Named Parameters to Queries</title>

     <para>
      The XPath-based functions support passing one parameter to serve as the
      XPath expression's context item, but do not support passing additional
      values to be available to the expression as named parameters.
     </para>
    </sect3>

    <sect3 id="functions-xml-limits-postgresql-no-xml-sequence">
     <title>No <type>XML(SEQUENCE)</type> Type</title>

     <para>
      The <productname>PostgreSQL</productname> <type>xml</type> data type
      can only hold a value in <literal>DOCUMENT</literal>
      or <literal>CONTENT</literal> form.  An XQuery/XPath expression
      context item must be a single XML node or atomic value, but XPath 1.0
      further restricts it to be only an XML node, and has no node type
      allowing <literal>CONTENT</literal>.  The upshot is that a
      well-formed <literal>DOCUMENT</literal> is the only form of XML value
      that <productname>PostgreSQL</productname> can supply as an XPath
      context item.
     </para>
    </sect3>
   </sect2>
  </sect1>

 </appendix>