From d08889aa8b462befe4d494d01ed4cc81644c1d22 Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Sun, 23 Jan 2005 00:30:59 +0000 Subject: [PATCH] Add tools/find_gt_lt to find < and > in SGML source. Lowercase some uppercase tags so tools is more reliable at finding problems. --- doc/src/sgml/client-auth.sgml | 18 +- doc/src/sgml/contacts.sgml | 40 +- doc/src/sgml/installation.sgml | 4 +- doc/src/sgml/protocol.sgml | 4386 +++++++++++++++--------------- doc/src/sgml/ref/pg_restore.sgml | 6 +- doc/src/sgml/release.sgml | 44 +- doc/src/sgml/rules.sgml | 864 +++--- doc/src/sgml/runtime.sgml | 12 +- doc/src/sgml/xindex.sgml | 4 +- doc/src/sgml/xoper.sgml | 50 +- src/tools/RELEASE_CHANGES | 2 +- src/tools/find_gt_lt | 2 + 12 files changed, 2717 insertions(+), 2715 deletions(-) create mode 100755 src/tools/find_gt_lt diff --git a/doc/src/sgml/client-auth.sgml b/doc/src/sgml/client-auth.sgml index edcefa6903..75a0381921 100644 --- a/doc/src/sgml/client-auth.sgml +++ b/doc/src/sgml/client-auth.sgml @@ -1,5 +1,5 @@ @@ -892,9 +892,9 @@ omicron bryanh guest1 - + FATAL: no pg_hba.conf entry for host "123.123.123.123", user "andym", database "testdb" - + This is what you are most likely to get if you succeed in contacting the server, but it does not want to talk to you. As the message suggests, the server refused the connection request because it found @@ -903,9 +903,9 @@ FATAL: no pg_hba.conf entry for host "123.123.123.123", user "andym", database - + FATAL: Password authentication failed for user "andym" - + Messages like this indicate that you contacted the server, and it is willing to talk to you, but not until you pass the authorization method specified in the pg_hba.conf file. Check @@ -915,16 +915,16 @@ FATAL: Password authentication failed for user "andym" - + FATAL: user "andym" does not exist - + The indicated user name was not found. - + FATAL: database "testdb" does not exist - + The database you are trying to connect to does not exist. Note that if you do not specify a database name, it defaults to the database user name, which may or may not be the right thing. diff --git a/doc/src/sgml/contacts.sgml b/doc/src/sgml/contacts.sgml index e20698f001..b4a5c5fd0b 100644 --- a/doc/src/sgml/contacts.sgml +++ b/doc/src/sgml/contacts.sgml @@ -1,35 +1,35 @@ - -Contacts + +Contacts - - - - -Thomas Lockhart + + + + +Thomas Lockhart works on SQL standards compliance and documentation. - - - - + + + + - + diff --git a/doc/src/sgml/installation.sgml b/doc/src/sgml/installation.sgml index 86b8aaa76a..867e74596d 100644 --- a/doc/src/sgml/installation.sgml +++ b/doc/src/sgml/installation.sgml @@ -1,4 +1,4 @@ - + <![%standalone-include[<productname>PostgreSQL</>]]> @@ -249,7 +249,7 @@ su - postgres class="osname">Linux</>, <systemitem class="osname">NetBSD</>, <systemitem class="osname">Solaris</>), for other systems you can download an add-on package from here: <ulink - url="http://developer.postgresql.org/~petere/bsd-gettext/" ></ulink>. + url="http://developer.postgresql.org/~petere/bsd-gettext/"></ulink>. If you are using the <application>Gettext</> implementation in the <acronym>GNU</acronym> C library then you will additionally need the <productname>GNU Gettext</productname> package for some diff --git a/doc/src/sgml/protocol.sgml b/doc/src/sgml/protocol.sgml index e93d5e4aa2..a3d445e436 100644 --- a/doc/src/sgml/protocol.sgml +++ b/doc/src/sgml/protocol.sgml @@ -1,4 +1,4 @@ -<!-- $PostgreSQL: pgsql/doc/src/sgml/protocol.sgml,v 1.57 2004/12/20 18:15:05 tgl Exp $ --> +<!-- $PostgreSQL: pgsql/doc/src/sgml/protocol.sgml,v 1.58 2005/01/23 00:30:18 momjian Exp $ --> <chapter id="protocol"> <title>Frontend/Backend Protocol @@ -210,7 +210,7 @@ - Start-Up + Start-Up To begin a session, a frontend opens a connection to the server and sends @@ -265,7 +265,7 @@ - AuthenticationKerberosV4 + AuthenticationKerberosV4 The frontend must now take part in a Kerberos V4 @@ -278,60 +278,60 @@ - AuthenticationKerberosV5 - - + AuthenticationKerberosV5 + + The frontend must now take part in a Kerberos V5 authentication dialog (not described here, part of the Kerberos specification) with the server. If this is successful, the server responds with an AuthenticationOk, otherwise it responds with an ErrorResponse. - - - - - - AuthenticationCleartextPassword - - + + + + + + AuthenticationCleartextPassword + + The frontend must now send a PasswordMessage containing the password in clear-text form. If this is the correct password, the server responds with an AuthenticationOk, otherwise it responds with an ErrorResponse. - - - - - - AuthenticationCryptPassword - - + + + + + + AuthenticationCryptPassword + + The frontend must now send a PasswordMessage containing the password encrypted via crypt(3), using the 2-character salt specified in the AuthenticationCryptPassword message. If this is the correct password, the server responds with an AuthenticationOk, otherwise it responds with an ErrorResponse. - - - - - - AuthenticationMD5Password - - + + + + + + AuthenticationMD5Password + + The frontend must now send a PasswordMessage containing the password encrypted via MD5, using the 4-character salt specified in the AuthenticationMD5Password message. If this is the correct password, the server responds with an AuthenticationOk, otherwise it responds with an ErrorResponse. - - - - - - AuthenticationSCMCredential - - + + + + + + AuthenticationSCMCredential + + This response is only possible for local Unix-domain connections on platforms that support SCM credential messages. The frontend must issue an SCM credential message and then send a single data @@ -340,12 +340,12 @@ the credential message.) If the credential is acceptable, the server responds with an AuthenticationOk, otherwise it responds with an ErrorResponse. - - - + + + - - + + If the frontend does not support the authentication method @@ -372,23 +372,23 @@ The possible messages from the backend in this phase are: - - - BackendKeyData - - + + + BackendKeyData + + This message provides secret-key data that the frontend must save if it wants to be able to issue cancel requests later. The frontend should not respond to this message, but should continue listening for a ReadyForQuery message. - - - - - - ParameterStatus - - + + + + + + ParameterStatus + + This message informs the frontend about the current (initial) setting of backend parameters, such as or . @@ -397,41 +397,41 @@ more details. The frontend should not respond to this message, but should continue listening for a ReadyForQuery message. - - - - - - ReadyForQuery - - + + + + + + ReadyForQuery + + Start-up is completed. The frontend may now issue commands. - - - - - - ErrorResponse - - + + + + + + ErrorResponse + + Start-up failed. The connection is closed after sending this message. - - - - - - NoticeResponse - - + + + + + + NoticeResponse + + A warning message has been issued. The frontend should display the message but continue listening for ReadyForQuery or ErrorResponse. - - - - - + + + + + The ReadyForQuery message is the same one that the backend will @@ -442,10 +442,10 @@ - - Simple Query + + Simple Query - + A simple query cycle is initiated by the frontend sending a Query message to the backend. The message includes an SQL command (or commands) expressed as a text string. @@ -459,109 +459,109 @@ command fails and already-issued later commands succeed.) - + The possible response messages from the backend are: - - - CommandComplete - - + + + CommandComplete + + An SQL command completed normally. - - - - - - CopyInResponse - - + + + + + + CopyInResponse + + The backend is ready to copy data from the frontend to a table; see . - - - - - - CopyOutResponse - - + + + + + + CopyOutResponse + + The backend is ready to copy data from a table to the frontend; see . - - - - - - RowDescription - - + + + + + + RowDescription + + Indicates that rows are about to be returned in response to a SELECT, FETCH, etc query. The contents of this message describe the column layout of the rows. This will be followed by a DataRow message for each row being returned to the frontend. - - - - - - DataRow - - + + + + + + DataRow + + One of the set of rows returned by a SELECT, FETCH, etc query. - - - - - - EmptyQueryResponse - - + + + + + + EmptyQueryResponse + + An empty query string was recognized. - - - - - - ErrorResponse - - + + + + + + ErrorResponse + + An error has occurred. - - - - - - ReadyForQuery - - + + + + + + ReadyForQuery + + Processing of the query string is complete. A separate message is sent to indicate this because the query string may contain multiple SQL commands. (CommandComplete marks the end of processing one SQL command, not the whole string.) ReadyForQuery will always be sent, whether processing terminates successfully or with an error. - - - - - - NoticeResponse - - + + + + + + NoticeResponse + + A warning message has been issued in relation to the query. Notices are in addition to other responses, i.e., the backend will continue processing the command. - - - + + + - - + + - + The response to a SELECT query (or other queries that return row sets, such as EXPLAIN or SHOW) normally consists of RowDescription, zero or more @@ -570,28 +570,28 @@ as described in . All other query types normally produce only a CommandComplete message. - + - + Since a query string could contain several queries (separated by semicolons), there might be several such response sequences before the backend finishes processing the query string. ReadyForQuery is issued when the entire string has been processed and the backend is ready to accept a new query string. - + - + If a completely empty (no contents other than whitespace) query string is received, the response is EmptyQueryResponse followed by ReadyForQuery. - + - + In the event of an error, ErrorResponse is issued followed by ReadyForQuery. All further processing of the query string is aborted by ErrorResponse (even if more queries remained in it). Note that this may occur partway through the sequence of messages generated by an individual query. - + In simple Query mode, the format of retrieved values is always text, @@ -615,8 +615,8 @@ - - Extended Query + + Extended Query The extended query protocol breaks down the above-described simple @@ -854,8 +854,8 @@ - - Function Call + + Function Call The Function Call sub-protocol allows the client to request a direct @@ -885,51 +885,51 @@ The possible response messages from the backend are: - - - ErrorResponse - - + + + ErrorResponse + + An error has occurred. - - - - - - FunctionCallResponse - - + + + + + + FunctionCallResponse + + The function call was completed and returned the result given in the message. (Note that the Function Call protocol can only handle a single scalar result, not a row type or set of results.) - - - - - - ReadyForQuery - - + + + + + + ReadyForQuery + + Processing of the function call is complete. ReadyForQuery will always be sent, whether processing terminates successfully or with an error. - - - - - - NoticeResponse - - + + + + + + NoticeResponse + + A warning message has been issued in relation to the function call. Notices are in addition to other responses, i.e., the backend will continue processing the command. - - - - - + + + + + @@ -1086,10 +1086,10 @@ - - Cancelling Requests in Progress + + Cancelling Requests in Progress - + During the processing of a query, the frontend may request cancellation of the query. The cancel request is not sent directly on the open connection to the backend for reasons of @@ -1100,7 +1100,7 @@ the normal case. - + To issue a cancel request, the frontend opens a new connection to the server and sends a CancelRequest message, rather than the StartupMessage message that would ordinarily be sent across a new @@ -1109,7 +1109,7 @@ the cancel request message. - + A CancelRequest message will be ignored unless it contains the same key data (PID and secret key) passed to the frontend during connection start-up. If the request matches the PID and secret @@ -1119,7 +1119,7 @@ processing the query.) - + The cancellation signal may or may not have any effect — for example, if it arrives after the backend has finished processing the query, then it will have no effect. If the cancellation is @@ -1127,7 +1127,7 @@ early with an error message. - + The upshot of all this is that for reasons of both security and efficiency, the frontend has no direct way to tell whether a cancel request has succeeded. It must continue to wait for the @@ -1137,7 +1137,7 @@ succeeding. - + Since the cancel request is sent across a new connection to the server and not across the regular frontend/backend communication link, it is possible for the cancel request to be issued by any @@ -1150,8 +1150,8 @@ - - Termination + + Termination The normal, graceful termination procedure is that the frontend @@ -1190,10 +1190,10 @@ - - <acronym>SSL</acronym> Session Encryption + + <acronym>SSL</acronym> Session Encryption - + If PostgreSQL was built with SSL support, frontend/backend communications can be encrypted using SSL. This provides @@ -1244,92 +1244,92 @@ - -Message Data Types + +Message Data Types - + This section describes the base data types used in messages. - + - - - Intn(i) - - - - An n-bit integer in network byte + + + Intn(i) + + + + An n-bit integer in network byte order (most significant byte first). - If i is specified it + If i is specified it is the exact value that will appear, otherwise the value is variable. Eg. Int16, Int32(42). - - - - - - - Intn[k] - - - - An array of k - n-bit integers, each in network - byte order. The array length k + + + + + + + Intn[k] + + + + An array of k + n-bit integers, each in network + byte order. The array length k is always determined by an earlier field in the message. Eg. Int16[M]. - - - - - - - String(s) - - - + + + + + + + String(s) + + + A null-terminated string (C-style string). There is no specific length limitation on strings. - If s is specified it is the exact + If s is specified it is the exact value that will appear, otherwise the value is variable. Eg. String, String("user"). - + - - -There is no predefined limit on the length of a string + + +There is no predefined limit on the length of a string that can be returned by the backend. Good coding strategy for a frontend is to use an expandable buffer so that anything that fits in memory can be accepted. If that's not feasible, read the full string and discard trailing characters that don't fit into your fixed-size buffer. - - - - - - - - Byten(c) - - - - Exactly n bytes. If the field - width n is not a constant, it is + + + + + + + + Byten(c) + + + + Exactly n bytes. If the field + width n is not a constant, it is always determinable from an earlier field in the message. - If c is specified it is the exact + If c is specified it is the exact value. Eg. Byte2, Byte1('\n'). - - - + + + - - + + - -Message Formats + +Message Formats - + This section describes the detailed format of each message. Each is marked to indicate that it may be sent by a frontend (F), a backend (B), or both (F & B). @@ -1340,454 +1340,454 @@ message is an exception, because it forms part of a data stream; the contents of any individual CopyData message may not be interpretable on their own.) - + - - + + AuthenticationOk (B) - - - + + + - - - + + + Byte1('R') - - - + + + Identifies the message as an authentication request. - - - - - + + + + + Int32(8) - - - + + + Length of message contents in bytes, including self. - - - - - + + + + + Int32(0) - - - + + + Specifies that the authentication was successful. - - - - + + + + - - - + + + - - + + AuthenticationKerberosV4 (B) - - - + + + - - - + + + Byte1('R') - - - + + + Identifies the message as an authentication request. - - - - - + + + + + Int32(8) - - - + + + Length of message contents in bytes, including self. - - - - - + + + + + Int32(1) - - - + + + Specifies that Kerberos V4 authentication is required. - - - - - - - + + + + + + + - - + + AuthenticationKerberosV5 (B) - - - + + + - - - + + + Byte1('R') - - - + + + Identifies the message as an authentication request. - - - - - + + + + + Int32(8) - - - + + + Length of message contents in bytes, including self. - - - - - + + + + + Int32(2) - - - + + + Specifies that Kerberos V5 authentication is required. - - - - - - - + + + + + + + - - + + AuthenticationCleartextPassword (B) - - - + + + - - - + + + Byte1('R') - - - + + + Identifies the message as an authentication request. - - - - - + + + + + Int32(8) - - - + + + Length of message contents in bytes, including self. - - - - - + + + + + Int32(3) - - - + + + Specifies that a clear-text password is required. - - - - - - - + + + + + + + - - + + AuthenticationCryptPassword (B) - - - + + + - - - + + + Byte1('R') - - - + + + Identifies the message as an authentication request. - - - - - + + + + + Int32(10) - - - + + + Length of message contents in bytes, including self. - - - - - + + + + + Int32(4) - - - + + + Specifies that a crypt()-encrypted password is required. - - - - - + + + + + Byte2 - - - + + + The salt to use when encrypting the password. - - - - + + + + - - - + + + - - + + AuthenticationMD5Password (B) - - - + + + - - - + + + Byte1('R') - - - + + + Identifies the message as an authentication request. - - - - - + + + + + Int32(12) - - - + + + Length of message contents in bytes, including self. - - - - - + + + + + Int32(5) - - - + + + Specifies that an MD5-encrypted password is required. - - - - - + + + + + Byte4 - - - + + + The salt to use when encrypting the password. - - - - + + + + - - - + + + - - + + AuthenticationSCMCredential (B) - - - + + + - - - + + + Byte1('R') - - - + + + Identifies the message as an authentication request. - - - - - + + + + + Int32(8) - - - + + + Length of message contents in bytes, including self. - - - - - + + + + + Int32(6) - - - + + + Specifies that an SCM credentials message is required. - - - - + + + + - - - + + + - - + + BackendKeyData (B) - - - + + + - - - + + + Byte1('K') - - - + + + Identifies the message as cancellation key data. The frontend must save these values if it wishes to be able to issue CancelRequest messages later. - - - - - + + + + + Int32(12) - - - + + + Length of message contents in bytes, including self. - - - - - + + + + + Int32 - - - + + + The process ID of this backend. - - - - - + + + + + Int32 - - - + + + The secret key of this backend. - - - - + + + + - - - + + + - - + + Bind (F) - - - + + + - - - + + + Byte1('B') - - - + + + Identifies the message as a Bind command. - - - - - + + + + + Int32 - - - + + + Length of message contents in bytes, including self. - - - - - + + + + + String - - - + + + The name of the destination portal (an empty string selects the unnamed portal). - - - - - + + + + + String - - - + + + The name of the source prepared statement (an empty string selects the unnamed prepared statement). - - - - - + + + + + Int16 - - - + + + The number of parameter format codes that follow (denoted C below). This can be zero to indicate that there are no parameters @@ -1795,68 +1795,68 @@ Bind (F) or one, in which case the specified format code is applied to all parameters; or it can equal the actual number of parameters. - - - - - + + + + + Int16[C] - - - + + + The parameter format codes. Each must presently be zero (text) or one (binary). - - - - - + + + + + Int16 - - - + + + The number of parameter values that follow (possibly zero). This must match the number of parameters needed by the query. - - - - + + + + Next, the following pair of fields appear for each parameter: - - - + + + Int32 - - - + + + The length of the parameter value, in bytes (this count does not include itself). Can be zero. As a special case, -1 indicates a NULL parameter value. No value bytes follow in the NULL case. - - - - - - Byten - - - + + + + + + Byten + + + The value of the parameter, in the format indicated by the associated format code. - n is the above length. - - - - + n is the above length. + + + + After the last parameter, the following fields appear: - - - + + + Int16 - - - + + + The number of result-column format codes that follow (denoted R below). This can be zero to indicate that there are no result columns @@ -1865,271 +1865,271 @@ Bind (F) or one, in which case the specified format code is applied to all result columns (if any); or it can equal the actual number of result columns of the query. - - - - - + + + + + Int16[R] - - - + + + The result-column format codes. Each must presently be zero (text) or one (binary). - - - - - - - + + + + + + + - - + + BindComplete (B) - - - + + + - - - + + + Byte1('2') - - - + + + Identifies the message as a Bind-complete indicator. - - - - - + + + + + Int32(4) - - - + + + Length of message contents in bytes, including self. - - - - + + + + - - - + + + - - + + CancelRequest (F) - - - + + + - - - + + + Int32(16) - - - + + + Length of message contents in bytes, including self. - - - - - + + + + + Int32(80877102) - - - + + + The cancel request code. The value is chosen to contain 1234 in the most significant 16 bits, and 5678 in the least 16 significant bits. (To avoid confusion, this code must not be the same as any protocol version number.) - - - - - + + + + + Int32 - - - + + + The process ID of the target backend. - - - - - + + + + + Int32 - - - + + + The secret key for the target backend. - - - - + + + + - - - + + + - - + + Close (F) - - - + + + - - - + + + Byte1('C') - - - + + + Identifies the message as a Close command. - - - - - + + + + + Int32 - - - + + + Length of message contents in bytes, including self. - - - - - + + + + + Byte1 - - - + + + 'S' to close a prepared statement; or 'P' to close a portal. - - - - - + + + + + String - - - + + + The name of the prepared statement or portal to close (an empty string selects the unnamed prepared statement or portal). - - - - - - - + + + + + + + - - + + CloseComplete (B) - - - + + + - - - + + + Byte1('3') - - - + + + Identifies the message as a Close-complete indicator. - - - - - + + + + + Int32(4) - - - + + + Length of message contents in bytes, including self. - - - - + + + + - - - + + + - - + + CommandComplete (B) - - - + + + - - - + + + Byte1('C') - - - + + + Identifies the message as a command-completed response. - - - - - + + + + + Int32 - - - + + + Length of message contents in bytes, including self. - - - - - + + + + + String - - - + + + The command tag. This is usually a single word that identifies which SQL command was completed. - + - + For an INSERT command, the tag is INSERT oid rows, where rows is the number of rows inserted. oid is the object ID - of the inserted row if rows is 1 + of the inserted row if rows is 1 and the target table has OIDs; - otherwise oid is 0. - + otherwise oid is 0. + - + For a DELETE command, the tag is - DELETE rows where - rows is the number of rows deleted. - + DELETE rows where + rows is the number of rows deleted. + - + For an UPDATE command, the tag is - UPDATE rows where - rows is the number of rows updated. - + UPDATE rows where + rows is the number of rows updated. + For a MOVE command, the tag is @@ -2143,178 +2143,178 @@ CommandComplete (B) FETCH rows where rows is the number of rows that have been retrieved from the cursor. - - - - + + + + - - - + + + - - + + CopyData (F & B) - - - - - - + + + + + + Byte1('d') - - - + + + Identifies the message as COPY data. - - - - - + + + + + Int32 - - - + + + Length of message contents in bytes, including self. - - - - - - Byten - - - + + + + + + Byten + + + Data that forms part of a COPY data stream. Messages sent from the backend will always correspond to single data rows, but messages sent by frontends may divide the data stream arbitrarily. - - - - - - - + + + + + + + - - + + CopyDone (F & B) - - - + + + - - - + + + Byte1('c') - - - + + + Identifies the message as a COPY-complete indicator. - - - - - + + + + + Int32(4) - - - + + + Length of message contents in bytes, including self. - - - - + + + + - - - + + + - - + + CopyFail (F) - - - + + + - - - + + + Byte1('f') - - - + + + Identifies the message as a COPY-failure indicator. - - - - - + + + + + Int32 - - - + + + Length of message contents in bytes, including self. - - - - - + + + + + String - - - + + + An error message to report as the cause of failure. - - - - + + + + - - - + + + - - + + CopyInResponse (B) - - - + + + - - - + + + Byte1('G') - - - + + + Identifies the message as a Start Copy In response. The frontend must now send copy-in data (if not prepared to do so, send a CopyFail message). - - - - - + + + + + Int32 - - - + + + Length of message contents in bytes, including self. - - - - - + + + + + Int8 - - - + + + 0 indicates the overall COPY format is textual (rows separated by newlines, columns separated by separator characters, etc). @@ -2322,317 +2322,317 @@ CopyInResponse (B) to DataRow format). See for more information. - - - - - + + + + + Int16 - - - + + + The number of columns in the data to be copied (denoted N below). - - - - - + + + + + Int16[N] - - - + + + The format codes to be used for each column. Each must presently be zero (text) or one (binary). All must be zero if the overall copy format is textual. - - - - + + + + - - - + + + - - + + CopyOutResponse (B) - - - + + + - - - + + + Byte1('H') - - - + + + Identifies the message as a Start Copy Out response. This message will be followed by copy-out data. - - - - - + + + + + Int32 - - - + + + Length of message contents in bytes, including self. - - - - - + + + + + Int8 - - - + + + 0 indicates the overall COPY format is textual (rows separated by newlines, columns separated by separator characters, etc). 1 indicates the overall copy format is binary (similar to DataRow format). See for more information. - - - - - + + + + + Int16 - - - + + + The number of columns in the data to be copied (denoted N below). - - - - - + + + + + Int16[N] - - - + + + The format codes to be used for each column. Each must presently be zero (text) or one (binary). All must be zero if the overall copy format is textual. - - - - + + + + - - - + + + - - + + DataRow (B) - - - - - - + + + + + + Byte1('D') - - - + + + Identifies the message as a data row. - - - - - + + + + + Int32 - - - + + + Length of message contents in bytes, including self. - - - - - + + + + + Int16 - - - + + + The number of column values that follow (possibly zero). - - - - + + + + Next, the following pair of fields appear for each column: - - - + + + Int32 - - - + + + The length of the column value, in bytes (this count does not include itself). Can be zero. As a special case, -1 indicates a NULL column value. No value bytes follow in the NULL case. - - - - - - Byten - - - + + + + + + Byten + + + The value of the column, in the format indicated by the associated format code. - n is the above length. - - - - + n is the above length. + + + + - - - + + + - - + + Describe (F) - - - + + + - - - + + + Byte1('D') - - - + + + Identifies the message as a Describe command. - - - - - + + + + + Int32 - - - + + + Length of message contents in bytes, including self. - - - - - + + + + + Byte1 - - - + + + 'S' to describe a prepared statement; or 'P' to describe a portal. - - - - - + + + + + String - - - + + + The name of the prepared statement or portal to describe (an empty string selects the unnamed prepared statement or portal). - - - - - - - + + + + + + + - - + + EmptyQueryResponse (B) - - - + + + - - - + + + Byte1('I') - - - + + + Identifies the message as a response to an empty query string. (This substitutes for CommandComplete.) - - - - - + + + + + Int32(4) - - - + + + Length of message contents in bytes, including self. - - - - + + + + - - - + + + - - + + ErrorResponse (B) - - - + + + - - - + + + Byte1('E') - - - + + + Identifies the message as an error. - - - - - + + + + + Int32 - - - + + + Length of message contents in bytes, including self. - - - - + + + + The message body consists of one or more identified fields, followed by a zero byte as a terminator. Fields may appear in any order. For each field there is the following: - - - + + + Byte1 - - - + + + A code identifying the field type; if zero, this is the message terminator and no string follows. The presently defined field types are listed in @@ -2640,162 +2640,162 @@ ErrorResponse (B) Since more field types may be added in future, frontends should silently ignore fields of unrecognized type. - - - - - + + + + + String - - - + + + The field value. - - - - + + + + - - - + + + - - + + Execute (F) - - - + + + - - - + + + Byte1('E') - - - + + + Identifies the message as an Execute command. - - - - - + + + + + Int32 - - - + + + Length of message contents in bytes, including self. - - - - - + + + + + String - - - + + + The name of the portal to execute (an empty string selects the unnamed portal). - - - - - + + + + + Int32 - - - + + + Maximum number of rows to return, if portal contains a query that returns rows (ignored otherwise). Zero denotes no limit. - - - - - - - + + + + + + + - - + + Flush (F) - - - + + + - - - + + + Byte1('H') - - - + + + Identifies the message as a Flush command. - - - - - + + + + + Int32(4) - - - + + + Length of message contents in bytes, including self. - - - - + + + + - - - + + + - - + + FunctionCall (F) - - - + + + - - - + + + Byte1('F') - - - + + + Identifies the message as a function call. - - - - - + + + + + Int32 - - - + + + Length of message contents in bytes, including self. - - - - - + + + + + Int32 - - - + + + Specifies the object ID of the function to call. - - - - - + + + + + Int16 - - - + + + The number of argument format codes that follow (denoted C below). This can be zero to indicate that there are no arguments @@ -2803,214 +2803,214 @@ FunctionCall (F) or one, in which case the specified format code is applied to all arguments; or it can equal the actual number of arguments. - - - - - + + + + + Int16[C] - - - + + + The argument format codes. Each must presently be zero (text) or one (binary). - - - - - + + + + + Int16 - - - + + + Specifies the number of arguments being supplied to the function. - - - - + + + + Next, the following pair of fields appear for each argument: - - - + + + Int32 - - - + + + The length of the argument value, in bytes (this count does not include itself). Can be zero. As a special case, -1 indicates a NULL argument value. No value bytes follow in the NULL case. - - - - - - Byten - - - + + + + + + Byten + + + The value of the argument, in the format indicated by the associated format code. - n is the above length. - - - - + n is the above length. + + + + After the last argument, the following field appears: - - - + + + Int16 - - - + + + The format code for the function result. Must presently be zero (text) or one (binary). - - - - + + + + - - - + + + - - + + FunctionCallResponse (B) - - - + + + - - - + + + Byte1('V') - - - + + + Identifies the message as a function call result. - - - - - + + + + + Int32 - - - + + + Length of message contents in bytes, including self. - - - - - + + + + + Int32 - - - + + + The length of the function result value, in bytes (this count does not include itself). Can be zero. As a special case, -1 indicates a NULL function result. No value bytes follow in the NULL case. - - - - - - Byten - - - + + + + + + Byten + + + The value of the function result, in the format indicated by the associated format code. - n is the above length. - - - - + n is the above length. + + + + - - - + + + - - + + NoData (B) - - - + + + - - - + + + Byte1('n') - - - + + + Identifies the message as a no-data indicator. - - - - - + + + + + Int32(4) - - - + + + Length of message contents in bytes, including self. - - - - + + + + - - - + + + - - + + NoticeResponse (B) - - - + + + - - - + + + Byte1('N') - - - + + + Identifies the message as a notice. - - - - - + + + + + Int32 - - - + + + Length of message contents in bytes, including self. - - - - + + + + The message body consists of one or more identified fields, followed by a zero byte as a terminator. Fields may appear in any order. For each field there is the following: - - - + + + Byte1 - - - + + + A code identifying the field type; if zero, this is the message terminator and no string follows. The presently defined field types are listed in @@ -3018,848 +3018,848 @@ NoticeResponse (B) Since more field types may be added in future, frontends should silently ignore fields of unrecognized type. - - - - - + + + + + String - - - + + + The field value. - - - - + + + + - - - + + + - - + + NotificationResponse (B) - - - + + + - - - + + + Byte1('A') - - - + + + Identifies the message as a notification response. - - - - - + + + + + Int32 - - - + + + Length of message contents in bytes, including self. - - - - - + + + + + Int32 - - - + + + The process ID of the notifying backend process. - - - - - + + + + + String - - - + + + The name of the condition that the notify has been raised on. - - - - - + + + + + String - - - + + + Additional information passed from the notifying process. (Currently, this feature is unimplemented so the field is always an empty string.) - - - - + + + + - - - + + + - - + + ParameterDescription (B) - - - + + + - - - + + + Byte1('t') - - - + + + Identifies the message as a parameter description. - - - - - + + + + + Int32 - - - + + + Length of message contents in bytes, including self. - - - - - + + + + + Int16 - - - + + + The number of parameters used by the statement (may be zero). - - - - + + + + Then, for each parameter, there is the following: - - - + + + Int32 - - - + + + Specifies the object ID of the parameter data type. - - - - - - - + + + + + + + - - + + ParameterStatus (B) - - - + + + - - - + + + Byte1('S') - - - + + + Identifies the message as a run-time parameter status report. - - - - - + + + + + Int32 - - - + + + Length of message contents in bytes, including self. - - - - - + + + + + String - - - + + + The name of the run-time parameter being reported. - - - - - + + + + + String - - - + + + The current value of the parameter. - - - - - - - + + + + + + + - - + + Parse (F) - - - + + + - - - + + + Byte1('P') - - - + + + Identifies the message as a Parse command. - - - - - + + + + + Int32 - - - + + + Length of message contents in bytes, including self. - - - - - + + + + + String - - - + + + The name of the destination prepared statement (an empty string selects the unnamed prepared statement). - - - - - + + + + + String - - - + + + The query string to be parsed. - - - - - + + + + + Int16 - - - + + + The number of parameter data types specified (may be zero). Note that this is not an indication of the number of parameters that might appear in the query string, only the number that the frontend wants to prespecify types for. - - - - + + + + Then, for each parameter, there is the following: - - - + + + Int32 - - - + + + Specifies the object ID of the parameter data type. Placing a zero here is equivalent to leaving the type unspecified. - - - - - - - + + + + + + + - - + + ParseComplete (B) - - - + + + - - - + + + Byte1('1') - - - + + + Identifies the message as a Parse-complete indicator. - - - - - + + + + + Int32(4) - - - + + + Length of message contents in bytes, including self. - - - - + + + + - - - + + + - - + + PasswordMessage (F) - - - + + + - - - + + + Byte1('p') - - - + + + Identifies the message as a password response. - - - - - + + + + + Int32 - - - + + + Length of message contents in bytes, including self. - - - - - + + + + + String - - - + + + The password (encrypted, if requested). - - - - - - - + + + + + + + - - + + PortalSuspended (B) - - - + + + - - - + + + Byte1('s') - - - + + + Identifies the message as a portal-suspended indicator. Note this only appears if an Execute message's row-count limit was reached. - - - - - + + + + + Int32(4) - - - + + + Length of message contents in bytes, including self. - - - - + + + + - - - + + + - - + + Query (F) - - - + + + - - - + + + Byte1('Q') - - - + + + Identifies the message as a simple query. - - - - - + + + + + Int32 - - - + + + Length of message contents in bytes, including self. - - - - - + + + + + String - - - + + + The query string itself. - - - - + + + + - - - + + + - - + + ReadyForQuery (B) - - - + + + - - - + + + Byte1('Z') - - - + + + Identifies the message type. ReadyForQuery is sent whenever the backend is ready for a new query cycle. - - - - - + + + + + Int32(5) - - - + + + Length of message contents in bytes, including self. - - - - - + + + + + Byte1 - - - + + + Current backend transaction status indicator. Possible values are 'I' if idle (not in a transaction block); 'T' if in a transaction block; or 'E' if in a failed transaction block (queries will be rejected until block is ended). - - - - + + + + - - - + + + - - + + RowDescription (B) - - - + + + - - - + + + Byte1('T') - - - + + + Identifies the message as a row description. - - - - - + + + + + Int32 - - - + + + Length of message contents in bytes, including self. - - - - - + + + + + Int16 - - - + + + Specifies the number of fields in a row (may be zero). - - - - + + + + Then, for each field, there is the following: - - - + + + String - - - + + + The field name. - - - - - + + + + + Int32 - - - + + + If the field can be identified as a column of a specific table, the object ID of the table; otherwise zero. - - - - - + + + + + Int16 - - - + + + If the field can be identified as a column of a specific table, the attribute number of the column; otherwise zero. - - - - - + + + + + Int32 - - - + + + The object ID of the field's data type. - - - - - + + + + + Int16 - - - + + + The data type size (see pg_type.typlen). Note that negative values denote variable-width types. - - - - - + + + + + Int32 - - - + + + The type modifier (see pg_attribute.atttypmod). The meaning of the modifier is type-specific. - - - - - + + + + + Int16 - - - + + + The format code being used for the field. Currently will be zero (text) or one (binary). In a RowDescription returned from the statement variant of Describe, the format code is not yet known and will always be zero. - - - - + + + + - - - + + + - - + + SSLRequest (F) - - - + + + - - - + + + Int32(8) - - - + + + Length of message contents in bytes, including self. - - - - - + + + + + Int32(80877103) - - - + + + The SSL request code. The value is chosen to contain 1234 in the most significant 16 bits, and 5679 in the least 16 significant bits. (To avoid confusion, this code must not be the same as any protocol version number.) - - - - + + + + - - - + + + - - + + StartupMessage (F) - - - + + + - - - + + + Int32 - - - + + + Length of message contents in bytes, including self. - - - - - + + + + + Int32(196608) - - - + + + The protocol version number. The most significant 16 bits are the major version number (3 for the protocol described here). The least significant 16 bits are the minor version number (0 for the protocol described here). - - - - + + + + The protocol version number is followed by one or more pairs of parameter name and value strings. A zero byte is required as a terminator after the last name/value pair. Parameters can appear in any order. user is required, others are optional. Each parameter is specified as: - - - + + + String - - - + + + The parameter name. Currently recognized names are: - - - + + + user - - - + + + The database user name to connect as. Required; there is no default. - - - - - + + + + + database - - - + + + The database to connect to. Defaults to the user name. - - - - - + + + + + options - - - + + + Command-line arguments for the backend. (This is deprecated in favor of setting individual run-time parameters.) - - - - + + + + In addition to the above, any run-time parameter that can be set at backend start time may be listed. Such settings will be applied during backend start (after parsing the command-line options if any). The values will act as session defaults. - - - - - + + + + + String - - - + + + The parameter value. - - - - + + + + - - - + + + - - + + Sync (F) - - - + + + - - - + + + Byte1('S') - - - + + + Identifies the message as a Sync command. - - - - - + + + + + Int32(4) - - - + + + Length of message contents in bytes, including self. - - - - + + + + - - - + + + - - + + Terminate (F) - - - + + + - - - + + + Byte1('X') - - - + + + Identifies the message as a termination. - - - - - + + + + + Int32(4) - - - + + + Length of message contents in bytes, including self. - - - - + + + + - - - + + + - + - -Error and Notice Message Fields + +Error and Notice Message Fields This section describes the fields that may appear in ErrorResponse and @@ -3868,165 +3868,165 @@ token. Note that any given field type should appear at most once per message. - + - - + + S - - - + + + Severity: the field contents are ERROR, FATAL, or PANIC (in an error message), or WARNING, NOTICE, DEBUG, INFO, or LOG (in a notice message), or a localized translation of one of these. Always present. - - - + + + - - + + C - - - + + + Code: the SQLSTATE code for the error (see ). Not localizable. Always present. - - - + + + - - + + M - - - + + + Message: the primary human-readable error message. This should be accurate but terse (typically one line). Always present. - - - + + + - - + + D - - - + + + Detail: an optional secondary error message carrying more detail about the problem. May run to multiple lines. - - - + + + - - + + H - - - + + + Hint: an optional suggestion what to do about the problem. This is intended to differ from Detail in that it offers advice (potentially inappropriate) rather than hard facts. May run to multiple lines. - - - + + + - - + + P - - - + + + Position: the field value is a decimal ASCII integer, indicating an error cursor position as an index into the original query string. The first character has index 1, and positions are measured in characters not bytes. - - - + + + - - + + p - - - + + + Internal position: this is defined the same as the P field, but it is used when the cursor position refers to an internally generated command rather than the one submitted by the client. The q field will always appear when this field appears. - - - + + + - - + + q - - - + + + Internal query: the text of a failed internally-generated command. This could be, for example, a SQL query issued by a PL/pgSQL function. - - - + + + - - + + W - - - + + + Where: an indication of the context in which the error occurred. Presently this includes a call stack traceback of active procedural language functions and internally-generated queries. The trace is one entry per line, most recent first. - - - + + + - - + + F - - - + + + File: the file name of the source-code location where the error was reported. - - - + + + - - + + L - - - + + + Line: the line number of the source-code location where the error was reported. - - - + + + - - + + R - - - + + + Routine: the name of the source-code routine reporting the error. - - - + + + - + The client is responsible for formatting displayed information to meet its @@ -4038,8 +4038,8 @@ not line breaks. - -Summary of Changes since Protocol 2.0 + +Summary of Changes since Protocol 2.0 This section provides a quick checklist of changes, for the benefit of @@ -4143,4 +4143,4 @@ string parameter; this has been removed. - + diff --git a/doc/src/sgml/ref/pg_restore.sgml b/doc/src/sgml/ref/pg_restore.sgml index db731ca9ee..5e7ff3240b 100644 --- a/doc/src/sgml/ref/pg_restore.sgml +++ b/doc/src/sgml/ref/pg_restore.sgml @@ -1,4 +1,4 @@ - + @@ -230,8 +230,8 @@ - Restore elements in list-file only, and in the + Restore elements in + list-file only, and in the order they appear in the file. Lines can be moved and may also be commented out by placing a ; at the start of the line. (See below for examples.) diff --git a/doc/src/sgml/release.sgml b/doc/src/sgml/release.sgml index 96f260c5a4..1421fdfa5b 100644 --- a/doc/src/sgml/release.sgml +++ b/doc/src/sgml/release.sgml @@ -1,5 +1,5 @@ @@ -9777,31 +9777,31 @@ New Ports diff --git a/doc/src/sgml/rules.sgml b/doc/src/sgml/rules.sgml index 513b5f28d6..eaad266f36 100644 --- a/doc/src/sgml/rules.sgml +++ b/doc/src/sgml/rules.sgml @@ -1,27 +1,27 @@ - + - -The Rule System + +The Rule System rule - + This chapter discusses the rule system in PostgreSQL. Production rule systems are conceptually simple, but there are many subtle points involved in actually using them. - + - + Some other database systems define active database rules, which are usually stored procedures and triggers. In PostgreSQL, these can be implemented using functions and triggers as well. - + - + The rule system (more precisely speaking, the query rewrite rule system) is totally different from stored procedures and triggers. It modifies queries to take rules into consideration, and then @@ -33,31 +33,31 @@ linkend="ONG90">. - -The Query Tree + +The Query Tree query tree - + To understand how the rule system works it is necessary to know when it is invoked and what its input and results are. - + - + The rule system is located between the parser and the planner. It takes the output of the parser, one query tree, and the user-defined rewrite rules, which are also query trees with some extra information, and creates zero or more query trees as result. So its input and output are always things the parser itself could have produced and thus, anything it sees - is basically representable as an SQL statement. - + is basically representable as an SQL statement. + - + Now what is a query tree? It is an internal representation of an - SQL statement where the single parts that it is + SQL statement where the single parts that it is built from are stored separately. These query trees can be shown in the server log if you set the configuration parameters debug_print_parse, @@ -66,94 +66,94 @@ stored as query trees, in the system catalog pg_rewrite. They are not formatted like the log output, but they contain exactly the same information. - + - + Reading a raw query tree requires some experience. But since - SQL representations of query trees are + SQL representations of query trees are sufficient to understand the rule system, this chapter will not teach how to read them. - + - - When reading the SQL representations of the + + When reading the SQL representations of the query trees in this chapter it is necessary to be able to identify the parts the statement is broken into when it is in the query tree structure. The parts of a query tree are - - - + + + the command type - - - + + + This is a simple value telling which command (SELECT, INSERT, UPDATE, DELETE) produced the query tree. - - - + + + - - + + the range table - + range table - - + + The range table is a list of relations that are used in the query. In a SELECT statement these are the relations given after the FROM key word. - + - + Every range table entry identifies a table or view and tells by which name it is called in the other parts of the query. In the query tree, the range table entries are referenced by number rather than by name, so here it doesn't matter if there - are duplicate names as it would in an SQL + are duplicate names as it would in an SQL statement. This can happen after the range tables of rules have been merged in. The examples in this chapter will not have this situation. - - - + + + - - + + the result relation - - - + + + This is an index into the range table that identifies the relation where the results of the query go. - + - + SELECT queries normally don't have a result relation. The special case of a SELECT INTO is mostly identical to a CREATE TABLE followed by a INSERT ... SELECT and is not discussed separately here. - + - + For INSERT, UPDATE, and DELETE commands, the result relation is the table (or view!) where the changes are to take effect. - - - + + + - - + + the target list - + target list - - + + The target list is a list of expressions that define the result of the query. In the case of a SELECT, these expressions are the ones that @@ -163,17 +163,17 @@ abbreviation for all the column names of a relation. It is expanded by the parser into the individual columns, so the rule system never sees it.) - + - + DELETE commands don't need a target list because they don't produce any result. In fact, the planner will add a special CTID entry to the empty target list, but this is after the rule system and will be discussed later; for the rule system, the target list is empty. - + - + For INSERT commands, the target list describes the new rows that should go into the result relation. It consists of the expressions in the VALUES clause or the ones from the @@ -183,9 +183,9 @@ the original command but have defaults. Any remaining columns (with neither a given value nor a default) will be filled in by the planner with a constant null expression. - + - + For UPDATE commands, the target list describes the new rows that should replace the old ones. In the rule system, it contains just the expressions from the SET @@ -193,40 +193,40 @@ missing columns by inserting expressions that copy the values from the old row into the new one. And it will add the special CTID entry just as for DELETE, too. - + - + Every entry in the target list contains an expression that can be a constant value, a variable pointing to a column of one of the relations in the range table, a parameter, or an expression tree made of function calls, constants, variables, operators, etc. - - - + + + - - + + the qualification - - - + + + The query's qualification is an expression much like one of those contained in the target list entries. The result value of this expression is a Boolean that tells whether the operation (INSERT, UPDATE, DELETE, or SELECT) for the final result row should be executed or not. It corresponds to the WHERE clause - of an SQL statement. - - - + of an SQL statement. + + + - - + + the join tree - - - + + + The query's join tree shows the structure of the FROM clause. For a simple query like SELECT ... FROM a, b, c, the join tree is just a list of the FROM items, because we are allowed to join them in @@ -239,31 +239,31 @@ the top-level WHERE expression as a qualification attached to the top-level join-tree item, too. So really the join tree represents both the FROM and WHERE clauses of a SELECT. - - - + + + - - + + the others - - - + + + The other parts of the query tree like the ORDER BY clause aren't of interest here. The rule system substitutes some entries there while applying rules, but that doesn't have much to do with the fundamentals of the rule system. - - - + + + - + - + - -Views and the Rule System + +Views and the Rule System rule @@ -275,57 +275,57 @@ implementation through rules - - Views in PostgreSQL are implemented + + Views in PostgreSQL are implemented using the rule system. In fact, there is essentially no difference between - + CREATE VIEW myview AS SELECT * FROM mytab; - + compared against the two commands - -CREATE TABLE myview (same column list as mytab); + +CREATE TABLE myview (same column list as mytab); CREATE RULE "_RETURN" AS ON SELECT TO myview DO INSTEAD SELECT * FROM mytab; - + because this is exactly what the CREATE VIEW command does internally. This has some side effects. One of them is that the information about a view in the - PostgreSQL system catalogs is exactly + PostgreSQL system catalogs is exactly the same as it is for a table. So for the parser, there is absolutely no difference between a table and a view. They are the same thing: relations. - + - -How <command>SELECT</command> Rules Work + +How <command>SELECT</command> Rules Work rule for SELECT - + Rules ON SELECT are applied to all queries as the last step, even if the command given is an INSERT, UPDATE or DELETE. And they have different semantics from rules on the other command types in that they modify the query tree in place instead of creating a new one. So SELECT rules are described first. - + - + Currently, there can be only one action in an ON SELECT rule, and it must be an unconditional SELECT action that is INSTEAD. This restriction was required to make rules safe enough to open them for ordinary users, and it restricts ON SELECT rules to act like views. - + - + The examples for this chapter are two join views that do some calculations and some more views using them in turn. One of the two first views is customized later by adding rules for @@ -336,24 +336,24 @@ CREATE RULE "_RETURN" AS ON SELECT TO myview DO INSTEAD this makes things harder to get into. But it's better to have one example that covers all the points discussed step by step rather than having many different ones that might mix up in mind. - + - + For the example, we need a little min function that returns the lower of 2 integer values. We create that as - + CREATE FUNCTION min(integer, integer) RETURNS integer AS $$ SELECT CASE WHEN $1 < $2 THEN $1 ELSE $2 END $$ LANGUAGE SQL STRICT; - - + + - + The real tables we need in the first two rule system descriptions are these: - + CREATE TABLE shoe_data ( shoename text, -- primary key sh_avail integer, -- available number of pairs @@ -375,15 +375,15 @@ CREATE TABLE unit ( un_name text, -- primary key un_fact real -- factor to transform to cm ); - + As you can see, they represent shoe-store data. - + - + The views are created as - + CREATE VIEW shoe AS SELECT sh.shoename, sh.sh_avail, @@ -416,7 +416,7 @@ CREATE VIEW shoe_ready AS WHERE rsl.sl_color = rsh.slcolor AND rsl.sl_len_cm >= rsh.slminlen_cm AND rsl.sl_len_cm <= rsh.slmaxlen_cm; - + The CREATE VIEW command for the shoelace view (which is the simplest one we @@ -430,23 +430,23 @@ CREATE VIEW shoe_ready AS The action of our rule has a query qualification. The action of the rule is one query tree that is a copy of the SELECT statement in the view creation command. - + - - + + The two extra range table entries for NEW and OLD (named *NEW* and *OLD* for historical reasons in the printed query tree) you can see in the pg_rewrite entry aren't of interest for SELECT rules. - - + + - + Now we populate unit, shoe_data and shoelace_data and run a simple query on a view: - + INSERT INTO unit VALUES ('cm', 1.0); INSERT INTO unit VALUES ('m', 100.0); INSERT INTO unit VALUES ('inch', 2.54); @@ -478,7 +478,7 @@ SELECT * FROM shoelace; sl5 | 4 | brown | 1 | m | 100 sl6 | 0 | brown | 0.9 | m | 90 (8 rows) - + @@ -487,12 +487,12 @@ SELECT * FROM shoelace; rules. The SELECT * FROM shoelace was interpreted by the parser and produced the query tree - + SELECT shoelace.sl_name, shoelace.sl_avail, shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM shoelace shoelace; - + and this is given to the rule system. The rule system walks through the range table and checks if there are rules @@ -500,23 +500,23 @@ SELECT shoelace.sl_name, shoelace.sl_avail, shoelace (the only one up to now) it finds the _RETURN rule with the query tree - + SELECT s.sl_name, s.sl_avail, s.sl_color, s.sl_len, s.sl_unit, s.sl_len * u.un_fact AS sl_len_cm FROM shoelace *OLD*, shoelace *NEW*, shoelace_data s, unit u WHERE s.sl_unit = u.un_name; - - + + - + To expand the view, the rewriter simply creates a subquery range-table entry containing the rule's action query tree, and substitutes this range table entry for the original one that referenced the view. The resulting rewritten query tree is almost the same as if you had typed - + SELECT shoelace.sl_name, shoelace.sl_avail, shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm @@ -528,7 +528,7 @@ SELECT shoelace.sl_name, shoelace.sl_avail, s.sl_len * u.un_fact AS sl_len_cm FROM shoelace_data s, unit u WHERE s.sl_unit = u.un_name) shoelace; - + There is one difference however: the subquery's range table has two extra entries shoelace *OLD* and shoelace *NEW*. These entries don't @@ -539,9 +539,9 @@ SELECT shoelace.sl_name, shoelace.sl_avail, executor will still check that the user has proper privileges to access the view, even though there's no direct use of the view in the rewritten query. - + - + That was the first rule applied. The rule system will continue checking the remaining range-table entries in the top query (in this example there are no more), and it will recursively check the range-table entries in @@ -550,14 +550,14 @@ SELECT shoelace.sl_name, shoelace.sl_avail, In this example, there are no rewrite rules for shoelace_data or unit, so rewriting is complete and the above is the final result given to the planner. - + - + No we want to write a query that finds out for which shoes currently in the store we have the matching shoelaces (color and length) and where the total number of exactly matching pairs is greater or equal to two. - + SELECT * FROM shoe_ready WHERE total_avail >= 2; shoename | sh_avail | sl_name | sl_avail | total_avail @@ -565,25 +565,25 @@ SELECT * FROM shoe_ready WHERE total_avail >= 2; sh1 | 2 | sl1 | 5 | 2 sh3 | 4 | sl7 | 7 | 4 (2 rows) - - + + - + The output of the parser this time is the query tree - + SELECT shoe_ready.shoename, shoe_ready.sh_avail, shoe_ready.sl_name, shoe_ready.sl_avail, shoe_ready.total_avail FROM shoe_ready shoe_ready WHERE shoe_ready.total_avail >= 2; - + The first rule applied will be the one for the shoe_ready view and it results in the query tree - + SELECT shoe_ready.shoename, shoe_ready.sh_avail, shoe_ready.sl_name, shoe_ready.sl_avail, shoe_ready.total_avail @@ -597,13 +597,13 @@ SELECT shoe_ready.shoename, shoe_ready.sh_avail, AND rsl.sl_len_cm >= rsh.slminlen_cm AND rsl.sl_len_cm <= rsh.slmaxlen_cm) shoe_ready WHERE shoe_ready.total_avail >= 2; - + Similarly, the rules for shoe and shoelace are substituted into the range table of the subquery, leading to a three-level final query tree: - + SELECT shoe_ready.shoename, shoe_ready.sh_avail, shoe_ready.sl_name, shoe_ready.sl_avail, shoe_ready.total_avail @@ -634,7 +634,7 @@ SELECT shoe_ready.shoename, shoe_ready.sh_avail, AND rsl.sl_len_cm >= rsh.slminlen_cm AND rsl.sl_len_cm <= rsh.slmaxlen_cm) shoe_ready WHERE shoe_ready.total_avail > 2; - + @@ -650,8 +650,8 @@ SELECT shoe_ready.shoename, shoe_ready.sh_avail, system doesn't have to concern itself with. - - + + There is currently no recursion stopping mechanism for view rules in the rule system (only for the other kinds of rules). This doesn't hurt much, because the only way to push this into an @@ -662,20 +662,20 @@ SELECT shoe_ready.shoename, shoe_ready.sh_avail, never happen if CREATE VIEW is used because for the first CREATE VIEW, the second relation does not exist and thus the first view cannot select from the second. - - - + + + - -View Rules in Non-<command>SELECT</command> Statements + +View Rules in Non-<command>SELECT</command> Statements - + Two details of the query tree aren't touched in the description of view rules above. These are the command type and the result relation. In fact, view rules don't need this information. - + - + There are only a few differences between a query tree for a SELECT and one for any other command. Obviously, they have a different command type and for a @@ -685,41 +685,41 @@ SELECT shoe_ready.shoename, shoe_ready.sh_avail, t1 and t2 with columns a and b, the query trees for the two statements - + SELECT t2.b FROM t1, t2 WHERE t1.a = t2.a; UPDATE t1 SET b = t2.b WHERE t1.a = t2.a; - + are nearly identical. In particular: - - - + + + The range tables contain entries for the tables t1 and t2. - - + + - - + + The target lists contain one variable that points to column b of the range table entry for table t2. - - + + - - + + The qualification expressions compare the columns a of both range-table entries for equality. - - + + - - + + The join trees show a simple join between t1 and t2. - - - + + + @@ -729,16 +729,16 @@ UPDATE t1 SET b = t2.b WHERE t1.a = t2.a; the target list by the planner and the final query tree will read as - + UPDATE t1 SET a = t1.a, b = t2.b WHERE t1.a = t2.a; - + and thus the executor run over the join will produce exactly the same result set as a - + SELECT t1.a, t2.b FROM t1, t2 WHERE t1.a = t2.a; - + will do. But there is a little problem in UPDATE: The executor does not care what the @@ -750,9 +750,9 @@ SELECT t1.a, t2.b FROM t1, t2 WHERE t1.a = t2.a; this is an UPDATE, and it knows that this result should go into table t1. But which of the rows that are there has to be replaced by the new row? - + - + To resolve this problem, another entry is added to the target list in UPDATE (and also in DELETE) statements: the current tuple ID @@ -762,11 +762,11 @@ SELECT t1.a, t2.b FROM t1, t2 WHERE t1.a = t2.a; original row of t1 to be updated. After adding the CTID to the target list, the query actually looks like - + SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a; - + - Now another detail of PostgreSQL enters + Now another detail of PostgreSQL enters the stage. Old table rows aren't overwritten, and this is why ROLLBACK is fast. In an UPDATE, the new result row is inserted into the table (after stripping the @@ -776,26 +776,26 @@ SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a; and current transaction ID. Thus the old row is hidden, and after the transaction committed the vacuum cleaner can really move it out. - + - + Knowing all that, we can simply apply view rules in absolutely the same way to any command. There is no difference. - - + + - -The Power of Views in <ProductName>PostgreSQL</ProductName> + +The Power of Views in <productname>PostgreSQL</productname> - + The above demonstrates how the rule system incorporates view definitions into the original query tree. In the second example, a simple SELECT from one view created a final query tree that is a join of 4 tables (unit was used twice with different names). - + - + The benefit of implementing views with the rule system is, that the planner has all the information about which tables have to be scanned plus the @@ -807,16 +807,16 @@ SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a; The planner has to decide which is the best path to execute the query, and the more information the planner has, the better this decision can be. And - the rule system as implemented in PostgreSQL + the rule system as implemented in PostgreSQL ensures, that this is all information available about the query up to that point. - - + + - -Updating a View + +Updating a View - + What happens if a view is named as the target relation for an INSERT, UPDATE, or DELETE? After doing the substitutions @@ -824,18 +824,18 @@ SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a; relation points at a subquery range-table entry. This will not work, so the rewriter throws an error if it sees it has produced such a thing. - + - + To change this, we can define rules that modify the behavior of these kinds of commands. This is the topic of the next section. - - + + - + - -Rules on <command>INSERT</>, <command>UPDATE</>, and <command>DELETE</> + +Rules on <command>INSERT</>, <command>UPDATE</>, and <command>DELETE</> rule @@ -852,67 +852,67 @@ SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a; for DELETE - + Rules that are defined on INSERT, UPDATE, and DELETE are significantly different from the view rules described in the previous section. First, their CREATE RULE command allows more: - - - + + + They are allowed to have no action. - - + + - - + + They can have multiple actions. - - + + - - + + They can be INSTEAD or ALSO (default). - - + + - - + + The pseudorelations NEW and OLD become useful. - - + + - - + + They can have rule qualifications. - - - + + + Second, they don't modify the query tree in place. Instead they create zero or more new query trees and can throw away the original one. - + - -How Update Rules Work + +How Update Rules Work - + Keep the syntax - + CREATE RULE rule_name AS ON event TO object [WHERE rule_qualification] DO [ALSO|INSTEAD] [action | (actions) | NOTHING]; - + in mind. In the following, update rules means rules that are defined on INSERT, UPDATE, or DELETE. - + - + Update rules get applied by the rule system when the result relation and the command type of a query tree are equal to the object and event given in the CREATE RULE command. @@ -921,15 +921,15 @@ CREATE RULE rule_name AS ON event There can be zero (NOTHING key word), one, or multiple actions. To simplify, we will look at a rule with one action. This rule can have a qualification or not and it can be INSTEAD or ALSO (default). - + - + What is a rule qualification? It is a restriction that tells when the actions of the rule should be done and when not. This qualification can only reference the pseudorelations NEW and/or OLD, which basically represent the relation that was given as object (but with a special meaning). - + So we have four cases that produce the following query trees for @@ -984,9 +984,9 @@ CREATE RULE rule_name AS ON event added to the list. Since only qualified INSTEAD rules already add the original query tree, we end up with either one or two output query trees for a rule with one action. - + - + For ON INSERT rules, the original query (if not suppressed by INSTEAD) is done before any actions added by rules. This allows the actions to see the inserted row(s). But for ON UPDATE and ON @@ -994,9 +994,9 @@ CREATE RULE rule_name AS ON event This ensures that the actions can see the to-be-updated or to-be-deleted rows; otherwise, the actions might do nothing because they find no rows matching their qualifications. - + - + The query trees generated from rule actions are thrown into the rewrite system again, and maybe more rules get applied resulting in more or less query trees. @@ -1006,9 +1006,9 @@ CREATE RULE rule_name AS ON event If after 100 iterations there are still update rules to apply, the rule system assumes a loop over multiple rule definitions and reports an error. - + - + The query trees found in the actions of the pg_rewrite system catalog are only templates. Since they can reference the range-table entries for @@ -1020,25 +1020,25 @@ CREATE RULE rule_name AS ON event a null value (for an INSERT). Any reference to OLD is replaced by a reference to the range-table entry that is the result relation. - + - + After the system is done applying update rules, it applies view rules to the produced query tree(s). Views cannot insert new update actions so there is no need to apply update rules to the output of view rewriting. - + - -A First Rule Step by Step + +A First Rule Step by Step - + Say we want to trace changes to the sl_avail column in the shoelace_data relation. So we set up a log table and a rule that conditionally writes a log entry when an UPDATE is performed on shoelace_data. - + CREATE TABLE shoelace_log ( sl_name text, -- shoelace changed sl_avail integer, -- new available value @@ -1054,53 +1054,53 @@ CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data current_user, current_timestamp ); - - + + - + Now someone does: - + UPDATE shoelace_data SET sl_avail = 6 WHERE sl_name = 'sl7'; - + and we look at the log table: - + SELECT * FROM shoelace_log; sl_name | sl_avail | log_who | log_when ---------+----------+---------+---------------------------------- sl7 | 6 | Al | Tue Oct 20 16:14:45 1998 MET DST (1 row) - + That's what we expected. What happened in the background is the following. The parser created the query tree - + UPDATE shoelace_data SET sl_avail = 6 FROM shoelace_data shoelace_data WHERE shoelace_data.sl_name = 'sl7'; - + There is a rule log_shoelace that is ON UPDATE with the rule qualification expression - + NEW.sl_avail <> OLD.sl_avail - + and the action - + INSERT INTO shoelace_log VALUES ( *NEW*.sl_name, *NEW*.sl_avail, current_user, current_timestamp ) FROM shoelace_data *NEW*, shoelace_data *OLD*; - + (This looks a little strange since you can't normally write INSERT ... VALUES ... FROM. The FROM @@ -1108,33 +1108,33 @@ INSERT INTO shoelace_log VALUES ( in the query tree for *NEW* and *OLD*. These are needed so that they can be referenced by variables in the INSERT command's query tree.) - + - + The rule is a qualified ALSO rule, so the rule system has to return two query trees: the modified rule action and the original query tree. In step 1, the range table of the original query is incorporated into the rule's action query tree. This results in: - + INSERT INTO shoelace_log VALUES ( *NEW*.sl_name, *NEW*.sl_avail, current_user, current_timestamp ) FROM shoelace_data *NEW*, shoelace_data *OLD*, shoelace_data shoelace_data; - + In step 2, the rule qualification is added to it, so the result set is restricted to rows where sl_avail changes: - + INSERT INTO shoelace_log VALUES ( *NEW*.sl_name, *NEW*.sl_avail, current_user, current_timestamp ) FROM shoelace_data *NEW*, shoelace_data *OLD*, shoelace_data shoelace_data WHERE *NEW*.sl_avail <> *OLD*.sl_avail; - + (This looks even stranger, since INSERT ... VALUES doesn't have a WHERE clause either, but the planner and executor will have no @@ -1147,7 +1147,7 @@ INSERT INTO shoelace_log VALUES ( restricting the result set further to only the rows that would have been touched by the original query: - + INSERT INTO shoelace_log VALUES ( *NEW*.sl_name, *NEW*.sl_avail, current_user, current_timestamp ) @@ -1155,7 +1155,7 @@ INSERT INTO shoelace_log VALUES ( shoelace_data shoelace_data WHERE *NEW*.sl_avail <> *OLD*.sl_avail AND shoelace_data.sl_name = 'sl7'; - + @@ -1163,7 +1163,7 @@ INSERT INTO shoelace_log VALUES ( original query tree or by the matching variable references from the result relation: - + INSERT INTO shoelace_log VALUES ( shoelace_data.sl_name, 6, current_user, current_timestamp ) @@ -1171,14 +1171,14 @@ INSERT INTO shoelace_log VALUES ( shoelace_data shoelace_data WHERE 6 <> *OLD*.sl_avail AND shoelace_data.sl_name = 'sl7'; - + Step 5 changes OLD references into result relation references: - + INSERT INTO shoelace_log VALUES ( shoelace_data.sl_name, 6, current_user, current_timestamp ) @@ -1186,7 +1186,7 @@ INSERT INTO shoelace_log VALUES ( shoelace_data shoelace_data WHERE 6 <> shoelace_data.sl_avail AND shoelace_data.sl_name = 'sl7'; - + @@ -1194,7 +1194,7 @@ INSERT INTO shoelace_log VALUES ( original query tree. In short, the output from the rule system is a list of two query trees that correspond to these statements: - + INSERT INTO shoelace_log VALUES ( shoelace_data.sl_name, 6, current_user, current_timestamp ) @@ -1204,7 +1204,7 @@ INSERT INTO shoelace_log VALUES ( UPDATE shoelace_data SET sl_avail = 6 WHERE sl_name = 'sl7'; - + These are executed in this order, and that is exactly what the rule was meant to do. @@ -1214,10 +1214,10 @@ UPDATE shoelace_data SET sl_avail = 6 The substitutions and the added qualifications ensure that, if the original query would be, say, - + UPDATE shoelace_data SET sl_color = 'green' WHERE sl_name = 'sl7'; - + no log entry would get written. In that case, the original query tree does not contain a target list entry for @@ -1225,14 +1225,14 @@ UPDATE shoelace_data SET sl_color = 'green' replaced by shoelace_data.sl_avail. Thus, the extra command generated by the rule is - + INSERT INTO shoelace_log VALUES ( shoelace_data.sl_name, shoelace_data.sl_avail, current_user, current_timestamp ) FROM shoelace_data WHERE shoelace_data.sl_avail <> shoelace_data.sl_avail AND shoelace_data.sl_name = 'sl7'; - + and that qualification will never be true. @@ -1241,59 +1241,59 @@ INSERT INTO shoelace_log VALUES ( It will also work if the original query modifies multiple rows. So if someone issued the command - + UPDATE shoelace_data SET sl_avail = 0 WHERE sl_color = 'black'; - + four rows in fact get updated (sl1, sl2, sl3, and sl4). But sl3 already has sl_avail = 0. In this case, the original query trees qualification is different and that results in the extra query tree - + INSERT INTO shoelace_log SELECT shoelace_data.sl_name, 0, current_user, current_timestamp FROM shoelace_data WHERE 0 <> shoelace_data.sl_avail AND shoelace_data.sl_color = 'black'; - + being generated by the rule. This query tree will surely insert three new log entries. And that's absolutely correct. - + - + Here we can see why it is important that the original query tree is executed last. If the UPDATE had been executed first, all the rows would have already been set to zero, so the logging INSERT would not find any row where 0 <> shoelace_data.sl_avail. - - + + - + - -Cooperation with Views + +Cooperation with Views viewupdating - + A simple way to protect view relations from the mentioned possibility that someone can try to run INSERT, UPDATE, or DELETE on them is to let those query trees get thrown away. So we create the rules - + CREATE RULE shoe_ins_protect AS ON INSERT TO shoe DO INSTEAD NOTHING; CREATE RULE shoe_upd_protect AS ON UPDATE TO shoe DO INSTEAD NOTHING; CREATE RULE shoe_del_protect AS ON DELETE TO shoe DO INSTEAD NOTHING; - + If someone now tries to do any of these operations on the view relation shoe, the rule system will @@ -1302,16 +1302,16 @@ CREATE RULE shoe_del_protect AS ON DELETE TO shoe query trees will be empty and the whole query will become nothing because there is nothing left to be optimized or executed after the rule system is done with it. - + - + A more sophisticated way to use the rule system is to create rules that rewrite the query tree into one that does the right operation on the real tables. To do that on the shoelace view, we create the following rules: - + CREATE RULE shoelace_ins AS ON INSERT TO shoelace DO INSTEAD INSERT INTO shoelace_data VALUES ( @@ -1336,7 +1336,7 @@ CREATE RULE shoelace_del AS ON DELETE TO shoelace DO INSTEAD DELETE FROM shoelace_data WHERE sl_name = OLD.sl_name; - + @@ -1347,7 +1347,7 @@ CREATE RULE shoelace_del AS ON DELETE TO shoelace insert the items from the part list, and one with a special trick. The creation commands for these are: - + CREATE TABLE shoelace_arrive ( arr_name text, arr_quant integer @@ -1363,12 +1363,12 @@ CREATE RULE shoelace_ok_ins AS ON INSERT TO shoelace_ok UPDATE shoelace SET sl_avail = sl_avail + NEW.ok_quant WHERE sl_name = NEW.ok_name; - + Now you can fill the table shoelace_arrive with the data from the parts list: - + SELECT * FROM shoelace_arrive; arr_name | arr_quant @@ -1377,11 +1377,11 @@ SELECT * FROM shoelace_arrive; sl6 | 20 sl8 | 20 (3 rows) - + Take a quick look at the current data: - + SELECT * FROM shoelace; sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm @@ -1395,17 +1395,17 @@ SELECT * FROM shoelace; sl5 | 4 | brown | 1 | m | 100 sl6 | 0 | brown | 0.9 | m | 90 (8 rows) - + Now move the arrived shoelaces in: - + INSERT INTO shoelace_ok SELECT * FROM shoelace_arrive; - + and check the results: - + SELECT * FROM shoelace ORDER BY sl_name; sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm @@ -1429,7 +1429,7 @@ SELECT * FROM shoelace_log; sl6 | 20 | Al | Tue Oct 20 19:25:16 1998 MET DST sl8 | 21 | Al | Tue Oct 20 19:25:16 1998 MET DST (4 rows) - + @@ -1438,30 +1438,30 @@ SELECT * FROM shoelace_log; transformation will be the last in this chapter. First, there is the parser's output - + INSERT INTO shoelace_ok SELECT shoelace_arrive.arr_name, shoelace_arrive.arr_quant FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok; - + Now the first rule shoelace_ok_ins is applied and turns this into - + UPDATE shoelace SET sl_avail = shoelace.sl_avail + shoelace_arrive.arr_quant FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok, shoelace_ok *OLD*, shoelace_ok *NEW*, shoelace shoelace WHERE shoelace.sl_name = shoelace_arrive.arr_name; - + and throws away the original INSERT on shoelace_ok. This rewritten query is passed to the rule system again, and the second applied rule shoelace_upd produces - + UPDATE shoelace_data SET sl_name = shoelace.sl_name, sl_avail = shoelace.sl_avail + shoelace_arrive.arr_quant, @@ -1474,14 +1474,14 @@ UPDATE shoelace_data shoelace *NEW*, shoelace_data shoelace_data WHERE shoelace.sl_name = shoelace_arrive.arr_name AND shoelace_data.sl_name = shoelace.sl_name; - + Again it's an INSTEAD rule and the previous query tree is trashed. Note that this query still uses the view shoelace. But the rule system isn't finished with this step, so it continues and applies the _RETURN rule on it, and we get - + UPDATE shoelace_data SET sl_name = s.sl_name, sl_avail = s.sl_avail + shoelace_arrive.arr_quant, @@ -1496,12 +1496,12 @@ UPDATE shoelace_data shoelace_data s, unit u WHERE s.sl_name = shoelace_arrive.arr_name AND shoelace_data.sl_name = s.sl_name; - + Finally, the rule log_shoelace gets applied, producing the extra query tree - + INSERT INTO shoelace_log SELECT s.sl_name, s.sl_avail + shoelace_arrive.arr_quant, @@ -1518,7 +1518,7 @@ SELECT s.sl_name, WHERE s.sl_name = shoelace_arrive.arr_name AND shoelace_data.sl_name = s.sl_name AND (s.sl_avail + shoelace_arrive.arr_quant) <> s.sl_avail; - + After that the rule system runs out of rules and returns the generated query trees. @@ -1526,9 +1526,9 @@ SELECT s.sl_name, So we end up with two final query trees that are equivalent to the - SQL statements + SQL statements - + INSERT INTO shoelace_log SELECT s.sl_name, s.sl_avail + shoelace_arrive.arr_quant, @@ -1547,15 +1547,15 @@ UPDATE shoelace_data shoelace_data s WHERE s.sl_name = shoelace_arrive.sl_name AND shoelace_data.sl_name = s.sl_name; - + The result is that data coming from one relation inserted into another, changed into updates on a third, changed into updating a fourth plus logging that final update in a fifth gets reduced into two queries. - + - + There is a little detail that's a bit ugly. Looking at the two queries, it turns out that the shoelace_data relation appears twice in the range table where it could @@ -1593,63 +1593,63 @@ Merge Join necessary. And the same redundant scan is done once more in the UPDATE. But it was a really hard job to make that all possible at all. - + - + Now we make a final demonstration of the - PostgreSQL rule system and its power. + PostgreSQL rule system and its power. Say you add some shoelaces with extraordinary colors to your database: - + INSERT INTO shoelace VALUES ('sl9', 0, 'pink', 35.0, 'inch', 0.0); INSERT INTO shoelace VALUES ('sl10', 1000, 'magenta', 40.0, 'inch', 0.0); - + We would like to make a view to check which shoelace entries do not fit any shoe in color. The view for this is - + CREATE VIEW shoelace_mismatch AS SELECT * FROM shoelace WHERE NOT EXISTS (SELECT shoename FROM shoe WHERE slcolor = sl_color); - + Its output is - + SELECT * FROM shoelace_mismatch; sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm ---------+----------+----------+--------+---------+----------- sl9 | 0 | pink | 35 | inch | 88.9 sl10 | 1000 | magenta | 40 | inch | 101.6 - + Now we want to set it up so that mismatching shoelaces that are not in stock are deleted from the database. - To make it a little harder for PostgreSQL, + To make it a little harder for PostgreSQL, we don't delete it directly. Instead we create one more view - + CREATE VIEW shoelace_can_delete AS SELECT * FROM shoelace_mismatch WHERE sl_avail = 0; - + and do it this way: - + DELETE FROM shoelace WHERE EXISTS (SELECT * FROM shoelace_can_delete WHERE sl_name = shoelace.sl_name); - + Voilà: - + SELECT * FROM shoelace; sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm @@ -1664,7 +1664,7 @@ SELECT * FROM shoelace; sl5 | 4 | brown | 1 | m | 100 sl6 | 20 | brown | 0.9 | m | 90 (9 rows) - + @@ -1675,19 +1675,19 @@ SELECT * FROM shoelace; gets rewritten into one single query tree that deletes the requested data from a real table. - + - + There are probably only a few situations out in the real world where such a construct is necessary. But it makes you feel comfortable that it works. - - + + - + - -Rules and Privileges + +Rules and Privileges privilege @@ -1699,36 +1699,36 @@ SELECT * FROM shoelace; with views - - Due to rewriting of queries by the PostgreSQL + + Due to rewriting of queries by the PostgreSQL rule system, other tables/views than those used in the original query get accessed. When update rules are used, this can include write access to tables. - + - + Rewrite rules don't have a separate owner. The owner of a relation (table or view) is automatically the owner of the rewrite rules that are defined for it. - The PostgreSQL rule system changes the + The PostgreSQL rule system changes the behavior of the default access control system. Relations that are used due to rules get checked against the privileges of the rule owner, not the user invoking the rule. This means that a user only needs the required privileges for the tables/views that he names explicitly in his queries. - + - + For example: A user has a list of phone numbers where some of them are private, the others are of interest for the secretary of the office. He can construct the following: - + CREATE TABLE phone_data (person text, phone text, private boolean); CREATE VIEW phone_number AS SELECT person, phone FROM phone_data WHERE NOT private; GRANT SELECT ON phone_number TO secretary; - + Nobody except him (and the database superusers) can access the phone_data table. But because of the GRANT, @@ -1744,9 +1744,9 @@ GRANT SELECT ON phone_number TO secretary; phone_number is also performed, but this is done against the invoking user, so nobody but the user and the secretary can use it. - + - + The privileges are checked rule by rule. So the secretary is for now the only one who can see the public phone numbers. But the secretary can setup another view and grant access to that to the public. Then, anyone @@ -1757,9 +1757,9 @@ GRANT SELECT ON phone_number TO secretary; And as soon as the user will notice, that the secretary opened his phone_number view, he can revoke his access. Immediately, any access to the secretary's view would fail. - + - + One might think that this rule-by-rule checking is a security hole, but in fact it isn't. But if it did not work this way, the secretary could set up a table with the same columns as phone_number and @@ -1768,9 +1768,9 @@ GRANT SELECT ON phone_number TO secretary; GRANT command means, I trust you. If someone you trust does the thing above, it's time to think it over and then use REVOKE. - + - + This mechanism also works for update rules. In the examples of the previous section, the owner of the tables in the example database could grant the privileges SELECT, @@ -1780,20 +1780,20 @@ GRANT SELECT ON phone_number TO secretary; write log entries will still be executed successfully, and that other user could see the log entries. But he cannot create fake entries, nor could he manipulate or remove existing ones. - - + + - -Rules and Command Status + +Rules and Command Status - - The PostgreSQL server returns a command + + The PostgreSQL server returns a command status string, such as INSERT 149592 1, for each command it receives. This is simple enough when there are no rules involved, but what happens when the query is rewritten by rules? - + - + Rules affect the command status as follows: @@ -1828,18 +1828,18 @@ GRANT SELECT ON phone_number TO secretary; (This system was established in PostgreSQL 7.3. In versions before that, the command status might show different results when rules exist.) - + - + The programmer can ensure that any desired INSTEAD rule is the one that sets the command status in the second case, by giving it the alphabetically last rule name among the active rules, so that it gets applied last. - - + + - -Rules versus Triggers + +Rules versus Triggers rule @@ -1851,9 +1851,9 @@ GRANT SELECT ON phone_number TO secretary; compared with rules - + Many things that can be done using triggers can also be - implemented using the PostgreSQL + implemented using the PostgreSQL rule system. One of the things that cannot be implemented by rules are some kinds of constraints, especially foreign keys. It is possible to place a qualified rule that rewrites a command to NOTHING @@ -1862,9 +1862,9 @@ GRANT SELECT ON phone_number TO secretary; not a good idea. If checks for valid values are required, and in the case of an invalid value an error message should be generated, it must be done by a trigger. - + - + On the other hand, a trigger that is fired on INSERT on a view can do the same as a rule: put the data somewhere else and suppress the insert in the view. But @@ -1872,9 +1872,9 @@ GRANT SELECT ON phone_number TO secretary; DELETE, because there is no real data in the view relation that could be scanned, and thus the trigger would never get called. Only a rule will help. - + - + For the things that can be implemented by both, which is best depends on the usage of the database. A trigger is fired for any affected row once. A rule manipulates @@ -1884,13 +1884,13 @@ GRANT SELECT ON phone_number TO secretary; called for every single row and must execute its operations many times. However, the trigger approach is conceptually far simpler than the rule approach, and is easier for novices to get right. - + - + Here we show an example of how the choice of rules versus triggers plays out in one situation. There are two tables: - + CREATE TABLE computer ( hostname text, -- indexed manufacturer text -- indexed @@ -1900,43 +1900,43 @@ CREATE TABLE software ( software text, -- indexed hostname text -- indexed ); - + Both tables have many thousands of rows and the indexes on hostname are unique. The rule or trigger should implement a constraint that deletes rows from software that reference a deleted computer. The trigger would use this command: - + DELETE FROM software WHERE hostname = $1; - + Since the trigger is called for each individual row deleted from computer, it can prepare and save the plan for this command and pass the hostname value in the parameter. The rule would be written as - + CREATE RULE computer_del AS ON DELETE TO computer DO DELETE FROM software WHERE hostname = OLD.hostname; - + Now we look at different types of deletes. In the case of a - + DELETE FROM computer WHERE hostname = 'mypc.local.net'; - + the table computer is scanned by index (fast), and the command issued by the trigger would also use an index scan (also fast). The extra command from the rule would be - + DELETE FROM software WHERE computer.hostname = 'mypc.local.net' AND software.hostname = computer.hostname; - + Since there are appropriate indexes setup, the planner will create a plan of @@ -1957,17 +1957,17 @@ Nestloop old. There are two possible commands to do that. One is - + DELETE FROM computer WHERE hostname >= 'old' AND hostname < 'ole' - + The command added by the rule will be - + DELETE FROM software WHERE computer.hostname >= 'old' AND computer.hostname < 'ole' AND software.hostname = computer.hostname; - + with the plan @@ -1980,9 +1980,9 @@ Hash Join The other possible command is - + DELETE FROM computer WHERE hostname ~ '^old'; - + which results in the following executing plan for the command added by the rule: @@ -2007,44 +2007,44 @@ Nestloop the table software whether the rule will still be faster in the sequential scan situation. 2000 command executions from the trigger over the SPI manager take some time, even if all the index blocks will soon be in the cache. - + - + The last command we look at is - + DELETE FROM computer WHERE manufacurer = 'bim'; - + Again this could result in many rows to be deleted from computer. So the trigger will again run many commands through the executor. The command generated by the rule will be - + DELETE FROM software WHERE computer.manufacurer = 'bim' AND software.hostname = computer.hostname; - + The plan for that command will again be the nested loop over two index scans, only using a different index on computer: - + Nestloop -> Index Scan using comp_manufidx on computer -> Index Scan using soft_hostidx on software - + In any of these cases, the extra commands from the rule system will be more or less independent from the number of affected rows in a command. - + - + Another situation is cases on UPDATE where it depends on the change of an attribute if an action should be performed or - not. In PostgreSQL version 6.4, the + not. In PostgreSQL version 6.4, the attribute specification for rule events is disabled (it will have its comeback latest in 6.5, maybe earlier - stay tuned). So for now the only way to @@ -2063,17 +2063,17 @@ Nestloop target list and will suppress the additional query completely if the attribute isn't touched. So the rule, qualified or not, will only do its scans if there ever could be something to do. - + ]]> - + The summary is, rules will only be significantly slower than triggers if their actions result in large and badly qualified joins, a situation where the planner fails. - - + + - + - - Server Run-time Environment + + Server Run-time Environment - + This chapter discusses how to set up and run the database server and its interactions with the operating system. @@ -447,7 +447,7 @@ psql: could not connect to server: No such file or directory - Run-time Configuration + Run-time Configuration configuration @@ -4981,7 +4981,7 @@ psql -h localhost -p 3333 template1 - + @@ -700,7 +700,7 @@ DEFAULT FOR TYPE int8 USING btree AS - In PostgreSQL versions before 7.4, + In PostgreSQL versions before 7.4, sorting and grouping operations would implicitly use operators named =, <, and >. The new behavior of relying on default operator classes avoids having to make diff --git a/doc/src/sgml/xoper.sgml b/doc/src/sgml/xoper.sgml index a35d0c93c5..6c4874d93f 100644 --- a/doc/src/sgml/xoper.sgml +++ b/doc/src/sgml/xoper.sgml @@ -1,5 +1,5 @@ @@ -10,7 +10,7 @@ $PostgreSQL: pgsql/doc/src/sgml/xoper.sgml,v 1.32 2004/11/15 06:32:14 neilc Exp user-defined - + Every operator is syntactic sugar for a call to an underlying function that does the real work; so you must first create the underlying function before you can create @@ -19,9 +19,9 @@ $PostgreSQL: pgsql/doc/src/sgml/xoper.sgml,v 1.32 2004/11/15 06:32:14 neilc Exp that helps the query planner optimize queries that use the operator. The next section will be devoted to explaining that additional information. - + - + PostgreSQL supports left unary, right unary, and binary operators. Operators can be overloaded;overloadingoperators @@ -29,15 +29,15 @@ $PostgreSQL: pgsql/doc/src/sgml/xoper.sgml,v 1.32 2004/11/15 06:32:14 neilc Exp that have different numbers and types of operands. When a query is executed, the system determines the operator to call from the number and types of the provided operands. - + - + Here is an example of creating an operator for adding two complex numbers. We assume we've already created the definition of type complex (see ). First we need a function that does the work, then we can define the operator: - + CREATE FUNCTION complex_add(complex, complex) RETURNS complex AS 'filename', 'complex_add' @@ -49,10 +49,10 @@ CREATE OPERATOR + ( procedure = complex_add, commutator = + ); - - + + - + Now we could execute a query like this: @@ -63,9 +63,9 @@ SELECT (a + b) AS c FROM test_complex; (5.2,6.05) (133.42,144.95) - + - + We've shown how to create a binary operator here. To create unary operators, just omit one of leftarg (for left unary) or rightarg (for right unary). The procedure @@ -74,14 +74,14 @@ SELECT (a + b) AS c FROM test_complex; clause shown in the example is an optional hint to the query optimizer. Further details about commutator and other optimizer hints appear in the next section. - + Operator Optimization Information - A PostgreSQL operator definition can include + A PostgreSQL operator definition can include several optional clauses that tell the system useful things about how the operator behaves. These clauses should be provided whenever appropriate, because they can make for considerable speedups in execution @@ -95,7 +95,7 @@ SELECT (a + b) AS c FROM test_complex; Additional optimization clauses might be added in future versions of - PostgreSQL. The ones described here are all + PostgreSQL. The ones described here are all the ones that release &version; understands. @@ -115,7 +115,7 @@ SELECT (a + b) AS c FROM test_complex; The left operand type of a commutable operator is the same as the right operand type of its commutator, and vice versa. So the name of - the commutator operator is all that PostgreSQL + the commutator operator is all that PostgreSQL needs to be given to look up the commutator, and that's all that needs to be provided in the COMMUTATOR clause. @@ -131,7 +131,7 @@ SELECT (a + b) AS c FROM test_complex; index scan unless it can determine how to flip the clause around to tab2.y = tab1.x, because the index-scan machinery expects to see the indexed column on the left of the operator it is given. - PostgreSQL will not simply + PostgreSQL will not simply assume that this is a valid transformation — the creator of the = operator must specify that it is valid, by marking the operator with commutator information. @@ -149,7 +149,7 @@ SELECT (a + b) AS c FROM test_complex; One way is to omit the COMMUTATOR clause in the first operator that you define, and then provide one in the second operator's definition. - Since PostgreSQL knows that commutative + Since PostgreSQL knows that commutative operators come in pairs, when it sees the second definition it will automatically go back and fill in the missing COMMUTATOR clause in the first definition. @@ -159,12 +159,12 @@ SELECT (a + b) AS c FROM test_complex; The other, more straightforward way is just to include COMMUTATOR clauses - in both definitions. When PostgreSQL processes + in both definitions. When PostgreSQL processes the first definition and realizes that COMMUTATOR refers to a nonexistent operator, the system will make a dummy entry for that operator in the system catalog. This dummy entry will have valid data only for the operator name, left and right operand types, and result type, - since that's all that PostgreSQL can deduce + since that's all that PostgreSQL can deduce at this point. The first operator's catalog entry will link to this dummy entry. Later, when you define the second operator, the system updates the dummy entry with the additional information from the second @@ -225,9 +225,9 @@ SELECT (a + b) AS c FROM test_complex; binary operators that return boolean. The idea behind a restriction selectivity estimator is to guess what fraction of the rows in a table will satisfy a WHERE-clause condition of the form - + column OP constant - + for the current operator and a particular constant value. This assists the optimizer by giving it some idea of how many rows will be eliminated by WHERE @@ -297,9 +297,9 @@ column OP constant binary operators that return boolean. The idea behind a join selectivity estimator is to guess what fraction of the rows in a pair of tables will satisfy a WHERE-clause condition of the form - + table1.column1 OP table2.column2 - + for the current operator. As with the RESTRICT clause, this helps the optimizer very substantially by letting it figure out which of several possible join sequences is likely to take the least work. @@ -496,7 +496,7 @@ table1.column1 OP table2.column2 - In PostgreSQL versions before 7.3, + In PostgreSQL versions before 7.3, the MERGES shorthand was not available: to make a merge-joinable operator one had to write both SORT1 and SORT2 explicitly. Also, the LTCMP and diff --git a/src/tools/RELEASE_CHANGES b/src/tools/RELEASE_CHANGES index 149e67f4bc..a087d0c85a 100644 --- a/src/tools/RELEASE_CHANGES +++ b/src/tools/RELEASE_CHANGES @@ -29,7 +29,7 @@ document all new features update help output from inside the programs doc/src/sgml/ref manual pages - convert any literal "<" and ">" characters + convert any literal "<" and ">" characters, use tools/find_gt_lt * Ports update config.guess and config.sub at the start of beta diff --git a/src/tools/find_gt_lt b/src/tools/find_gt_lt new file mode 100755 index 0000000000..66919dd7ae --- /dev/null +++ b/src/tools/find_gt_lt @@ -0,0 +1,2 @@ +grep '[^]a-z0-9"/!-]>' *.sgml ref/*.sgml +grep '<[^]a-z0-9"/!-]' *.sgml ref/*.sgml -- 2.40.0