diff options
| -rw-r--r-- | doc/src/sgml/ddl.sgml | 10 | ||||
| -rw-r--r-- | doc/src/sgml/ref/create_table.sgml | 11 | ||||
| -rw-r--r-- | doc/src/sgml/ref/create_type.sgml | 9 | ||||
| -rw-r--r-- | doc/src/sgml/rules.sgml | 450 | ||||
| -rw-r--r-- | doc/src/sgml/syntax.sgml | 12 |
5 files changed, 262 insertions, 230 deletions
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 6c58bfeb818..01ea0488df3 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -1,4 +1,4 @@ -<!-- $Header: /cvsroot/pgsql/doc/src/sgml/ddl.sgml,v 1.10 2002/11/15 03:11:15 momjian Exp $ --> +<!-- $Header: /cvsroot/pgsql/doc/src/sgml/ddl.sgml,v 1.11 2003/02/19 03:13:24 momjian Exp $ --> <chapter id="ddl"> <title>Data Definition</title> @@ -189,7 +189,7 @@ DROP TABLE products; individual table a row came from. The <structfield>tableoid</structfield> can be joined against the <structfield>oid</structfield> column of - <classname>pg_class</classname> to obtain the table name. + <structname>pg_class</structname> to obtain the table name. </para> </listitem> </varlistentry> @@ -904,9 +904,9 @@ WHERE c.altitude > 500; 139798 | Madison | 845 </programlisting> - (If you try to reproduce this example, you will probably get different - numeric OIDs.) By doing a join with pg_class you can see the actual table - names: + (If you try to reproduce this example, you will probably get + different numeric OIDs.) By doing a join with + <structname>pg_class</> you can see the actual table names: <programlisting> SELECT p.relname, c.name, c.altitude diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 3439c731150..3c0a9fc3a8b 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -1,5 +1,5 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_table.sgml,v 1.61 2003/01/19 00:13:29 momjian Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_table.sgml,v 1.62 2003/02/19 03:13:25 momjian Exp $ PostgreSQL documentation --> @@ -230,7 +230,10 @@ and <replaceable class="PARAMETER">table_constraint</replaceable> is: for large tables, since it will reduce OID consumption and thereby postpone wraparound of the 32-bit OID counter. Once the counter wraps around, uniqueness of OIDs can no longer be - assumed, which considerably reduces their usefulness. + assumed, which considerably reduces their usefulness. Specifying + <literal>WITHOUT OIDS</literal> also reduces the space required + to store the table on disk by 4 bytes per row of the table, + thereby improving performance. </para> </listitem> </varlistentry> @@ -881,8 +884,8 @@ CREATE ASSERTION <replaceable>name</replaceable> CHECK ( <replaceable>condition< <!-- <para> - Domain constraints are defined by CREATE DOMAIN or ALTER DOMAIN - statements: + Domain constraints are defined by <command>CREATE + DOMAIN</command> or <command>ALTER DOMAIN</command> statements: </para> <para> Domain constraint: diff --git a/doc/src/sgml/ref/create_type.sgml b/doc/src/sgml/ref/create_type.sgml index 651409b2455..a8e8c592917 100644 --- a/doc/src/sgml/ref/create_type.sgml +++ b/doc/src/sgml/ref/create_type.sgml @@ -1,5 +1,5 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_type.sgml,v 1.38 2003/01/19 00:13:29 momjian Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_type.sgml,v 1.39 2003/02/19 03:13:25 momjian Exp $ PostgreSQL documentation --> @@ -218,7 +218,8 @@ CREATE TYPE <para> The first form of <command>CREATE TYPE</command> creates a new base type (scalar type). It requires the - registration of two functions (using CREATE FUNCTION) before defining the + registration of two functions (using <command>CREATE + FUNCTION</command>) before defining the type. The representation of a new base type is determined by <replaceable class="parameter">input_function</replaceable>, which converts the type's external representation to an internal @@ -400,8 +401,8 @@ CREATE TYPE <para> User-defined type names cannot begin with the underscore character (<quote><literal>_</literal></quote>) and can only be 62 - characters long (or in general <literal>NAMEDATALEN-2</>, rather than - the <literal>NAMEDATALEN-1</> characters allowed for other names). + characters long (or in general <symbol>NAMEDATALEN</symbol> - 2, rather than + the <symbol>NAMEDATALEN</symbol> - 1 characters allowed for other names). Type names beginning with underscore are reserved for internally-created array type names. </para> diff --git a/doc/src/sgml/rules.sgml b/doc/src/sgml/rules.sgml index 4b72d5de3b2..25c78cce885 100644 --- a/doc/src/sgml/rules.sgml +++ b/doc/src/sgml/rules.sgml @@ -1,4 +1,4 @@ -<!-- $Header: /cvsroot/pgsql/doc/src/sgml/rules.sgml,v 1.26 2002/10/19 19:00:47 tgl Exp $ --> +<!-- $Header: /cvsroot/pgsql/doc/src/sgml/rules.sgml,v 1.27 2003/02/19 03:13:24 momjian Exp $ --> <Chapter Id="rules"> <Title>The Rule System</Title> @@ -59,7 +59,7 @@ <Para> The rule system is located between the query parser and the planner. It takes the output of the parser, one query tree, and the rewrite - rules from the <FileName>pg_rewrite</FileName> catalog, which are + rules from the <structname>pg_rewrite</structname> catalog, which are query trees too with some extra information, and creates zero or many query trees as result. So its input and output are always things the parser itself could have produced and thus, anything it sees @@ -72,7 +72,7 @@ it are stored separately. These query trees are visible when starting the <ProductName>PostgreSQL</ProductName> backend with debug level 4 and typing queries into the interactive backend interface. The rule - actions in the <FileName>pg_rewrite</FileName> system catalog are + actions in the <structname>pg_rewrite</structname> system catalog are also stored as query trees. They are not formatted like the debug output, but they contain exactly the same information. </Para> @@ -107,7 +107,9 @@ <ListItem> <Para> This is a simple value telling which command - (SELECT, INSERT, UPDATE, DELETE) produced the parse tree. + (<command>SELECT</command>, <command>INSERT</command>, + <command>UPDATE</command>, <command>DELETE</command>) produced + the parse tree. </Para> </ListItem> </VarListEntry> @@ -120,7 +122,7 @@ <ListItem> <Para> The range table is a list of relations that are used in the query. - In a SELECT statement these are the relations given after + In a <command>SELECT</command> statement these are the relations given after the FROM keyword. </Para> @@ -148,16 +150,17 @@ </Para> <Para> - SELECT queries - normally don't have a result relation. The special case - of a SELECT INTO is mostly identical to a CREATE TABLE, - INSERT ... SELECT sequence and is not discussed separately - here. + <command>SELECT</command> queries normally don't have a result + relation. The special case of a <command>SELECT INTO</command> is + mostly identical to a <command>CREATE TABLE</command>, + <literal>INSERT ... SELECT</literal> sequence and is not discussed + separately here. </Para> <Para> - On INSERT, UPDATE and DELETE queries the result relation - is the table (or view!) where the changes take effect. + On <command>INSERT</command>, <command>UPDATE</command> and + <command>DELETE</command> queries the result relation is the table + (or view!) where the changes take effect. </Para> </ListItem> </VarListEntry> @@ -168,41 +171,44 @@ </Term> <ListItem> <Para> - The target list is a list of expressions that define the result - of the query. In the case of a SELECT, the expressions are what - builds the final output of the query. They are the expressions - between the SELECT and the FROM keywords. (* is just an - abbreviation for all the attribute names of a relation. It is - expanded by the parser into the individual attributes, so the - rule system never sees it.) + The target list is a list of expressions that define the + result of the query. In the case of a <command>SELECT</command>, + the expressions are what builds the final output of the + query. They are the expressions between the + <command>SELECT</command> and the FROM keywords. + (<literal>*</literal> is just an abbreviation for all the + attribute names of a relation. It is expanded by the parser into + the individual attributes, so the rule system never sees it.) </Para> <Para> - DELETE queries don't need a target list because they don't - produce any result. In fact the planner will add a special <acronym>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. + <command>DELETE</command> queries don't need a target list + because they don't produce any result. In fact the planner will + add a special <acronym>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. </Para> <Para> - In INSERT queries the target list describes the new rows that - should go into the result relation. It is the expressions in the VALUES - clause or the ones from the SELECT clause in INSERT ... SELECT. - The first step of the rewrite process adds target list entries - for any columns that were not assigned to by the original query - and have defaults. Any remaining columns (with neither a given - value nor a default) will be filled in by the + In <command>INSERT</command> queries the target list describes + the new rows that should go into the result relation. It is the + expressions in the VALUES clause or the ones from the + <command>SELECT</command> clause in <literal>INSERT + ... SELECT</literal>. The first step of the rewrite process adds + target list entries for any columns that were not assigned to by + the original query and 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. </Para> <Para> - In UPDATE queries, 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 attribute = expression part of the query. - The planner will handle missing columns by inserting expressions that - copy the values from the old row into the new one. And it will add - the special <acronym>CTID</> entry just as for DELETE too. + In <command>UPDATE</command> queries, 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 + attribute = expression part of the query. The planner will handle + missing columns by inserting expressions that copy the values from + the old row into the new one. And it will add the special + <acronym>CTID</> entry just as for <command>DELETE</command> too. </Para> <Para> @@ -220,12 +226,13 @@ </Term> <ListItem> <Para> - 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 if the operation - (INSERT, UPDATE, DELETE or SELECT) for the final result row should be - executed or not. It is the WHERE clause of an - <Acronym>SQL</Acronym> statement. + 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 if the operation + (<command>INSERT</command>, <command>UPDATE</command>, + <command>DELETE</command> or <command>SELECT</command>) for the + final result row should be executed or not. It is the WHERE clause + of an <Acronym>SQL</Acronym> statement. </Para> </ListItem> </VarListEntry> @@ -247,7 +254,7 @@ to those join tree nodes. It turns out to be convenient to store 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. + both the FROM and WHERE clauses of a <command>SELECT</command>. </Para> </ListItem> </VarListEntry> @@ -296,26 +303,26 @@ CREATE RULE "_RETURN" AS ON SELECT TO myview DO INSTEAD SELECT * FROM mytab; </ProgramListing> - 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 <ProductName>PostgreSQL</ProductName> - system catalogs is exactly the same as it is for a table. So for the - query parser, there is absolutely no difference between - a table and a view. They are the same thing - relations. That is the - important one for now. + because this is exactly what the <command>CREATE VIEW</command> + command does internally. This has some side effects. One of them + is that the information about a view in the + <ProductName>PostgreSQL</ProductName> system catalogs is exactly + the same as it is for a table. So for the query parser, there is + absolutely no difference between a table and a view. They are the + same thing - relations. That is the important one for now. </Para> </Sect2> <Sect2> -<Title>How SELECT Rules Work</Title> +<Title>How <command>SELECT</command> Rules Work</Title> <Para> - 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 the others in that they modify the parse tree in - place instead of creating a new one. - So SELECT rules are described first. + Rules ON SELECT are applied to all queries as the last step, even + if the command given is an <command>INSERT</command>, + <command>UPDATE</command> or <command>DELETE</command>. And they + have different semantics from the others in that they modify the + parse tree in place instead of creating a new one. So + <command>SELECT</command> rules are described first. </Para> <Para> @@ -326,15 +333,16 @@ CREATE RULE "_RETURN" AS ON SELECT TO myview DO INSTEAD </Para> <Para> - The examples for this document 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 - INSERT, UPDATE and DELETE operations so that the final result will - be a view that behaves like a real table with some magic functionality. - It is not such a simple example to start from and 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. + The examples for this document 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 + <command>INSERT</command>, <command>UPDATE</command> and + <command>DELETE</command> operations so that the final result will + be a view that behaves like a real table with some magic + functionality. It is not such a simple example to start from and + 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. </Para> <Para> @@ -428,21 +436,21 @@ CREATE VIEW shoe_ready AS AND rsl.sl_len_cm <= rsh.slmaxlen_cm; </ProgramListing> - The CREATE VIEW command for the <Filename>shoelace</Filename> - view (which is the simplest one we have) - will create a relation shoelace and an entry - in <FileName>pg_rewrite</FileName> - that tells that there is a rewrite rule that must be applied - whenever the relation shoelace is referenced in a query's range table. - The rule has no rule qualification (discussed later, with the - non SELECT rules, since SELECT rules currently cannot have them) and - it is INSTEAD. Note that rule qualifications are not the same as - query qualifications! The rule's action has a query qualification. + The <command>CREATE VIEW</command> command for the + <literal>shoelace</literal> view (which is the simplest one we + have) will create a relation shoelace and an entry in + <structname>pg_rewrite</structname> that tells that there is a + rewrite rule that must be applied whenever the relation shoelace + is referenced in a query's range table. The rule has no rule + qualification (discussed later, with the non SELECT rules, since + SELECT rules currently cannot have them) and it is INSTEAD. Note + that rule qualifications are not the same as query qualifications! + The rule's action has a query qualification. </Para> <Para> The rule's action is one query tree that is a copy of the - SELECT statement in the view creation command. + <command>SELECT</command> statement in the view creation command. <Note> <Title>Note</Title> @@ -450,14 +458,14 @@ CREATE VIEW shoe_ready AS The two extra range table entries for NEW and OLD (named *NEW* and *CURRENT* for historical reasons in the printed query tree) you can see in - the <Filename>pg_rewrite</Filename> entry aren't of interest + the <structname>pg_rewrite</structname> entry aren't of interest for SELECT rules. </Para> </Note> - Now we populate <Filename>unit</Filename>, <Filename>shoe_data</Filename> - and <Filename>shoelace_data</Filename> and Al types the first - SELECT in his life: + Now we populate <literal>unit</literal>, <literal>shoe_data</literal> + and <literal>shoelace_data</literal> and Al types the first + <command>SELECT</command> in his life: <ProgramListing> al_bundy=> INSERT INTO unit VALUES ('cm', 1.0); @@ -504,10 +512,10 @@ sl6 | 0|brown | 0.9|m | 90 (8 rows) </ProgramListing> - It's the simplest SELECT Al can do on our views, so we take this - to explain the basics of view rules. - The <literal>SELECT * FROM shoelace</literal> was interpreted by the parser and - produced the parse tree + It's the simplest <command>SELECT</command> Al can do on our + views, so we take this opportunity to explain the basics of view + rules. The <literal>SELECT * FROM shoelace</literal> was + interpreted by the parser and produced the parse tree <ProgramListing> SELECT shoelace.sl_name, shoelace.sl_avail, @@ -517,9 +525,9 @@ SELECT shoelace.sl_name, shoelace.sl_avail, </ProgramListing> and this is given to the rule system. The rule system walks through the - range table and checks if there are rules in <Filename>pg_rewrite</Filename> + range table and checks if there are rules in <structname>pg_rewrite</structname> for any relation. When processing the range table entry for - <Filename>shoelace</Filename> (the only one up to now) it finds the + <literal>shoelace</literal> (the only one up to now) it finds the <literal>_RETURN</literal> rule with the parse tree <ProgramListing> @@ -618,7 +626,7 @@ SELECT shoe_ready.shoename, shoe_ready.sh_avail, </ProgramListing> The first rule applied will be the one for the - <Filename>shoe_ready</Filename> view and it results in the + <literal>shoe_ready</literal> view and it results in the parse tree <ProgramListing> @@ -637,8 +645,8 @@ SELECT shoe_ready.shoename, shoe_ready.sh_avail, WHERE int4ge(shoe_ready.total_avail, 2); </ProgramListing> - Similarly, the rules for <Filename>shoe</Filename> and - <Filename>shoelace</Filename> are substituted into the range table of + Similarly, the rules for <literal>shoe</literal> and + <literal>shoelace</literal> are substituted into the range table of the sub-query, leading to a three-level final query tree: <ProgramListing> @@ -686,17 +694,16 @@ SELECT shoe_ready.shoename, shoe_ready.sh_avail, <Note> <Title>Note</Title> <Para> - 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 endless loop (blowing up the - backend until it reaches the memory limit) - is to create tables and then setup the - view rules by hand with CREATE RULE in such a way, that - one selects from the other that selects from the one. - This could 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. + 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 + endless loop (blowing up the backend until it reaches the memory + limit) is to create tables and then setup the view rules by hand + with <command>CREATE RULE</command> in such a way, that one + selects from the other that selects from the one. This could + never happen if <command>CREATE VIEW</command> is used because for + the first <command>CREATE VIEW</command>, the second relation does + not exist and thus the first view cannot select from the second. </Para> </Note> </Para> @@ -713,12 +720,13 @@ SELECT shoe_ready.shoename, shoe_ready.sh_avail, </Para> <Para> - There are only a few differences between a parse tree for a SELECT - and one for any other command. Obviously they have another command type - and this time the result relation points to the range table entry where - the result should go. Everything else is absolutely the same. - So having two tables t1 and t2 with attributes - a and b, the parse trees for the two statements + There are only a few differences between a parse tree for a + <command>SELECT</command> and one for any other command. Obviously + they have another command type and this time the result relation + points to the range table entry where the result should + go. Everything else is absolutely the same. So having two tables + t1 and t2 with attributes a and b, the parse trees for the two + statements <ProgramListing> SELECT t2.b FROM t1, t2 WHERE t1.a = t2.a; @@ -756,10 +764,11 @@ UPDATE t1 SET b = t2.b WHERE t1.a = t2.a; </ListItem> </ItemizedList> - The consequence is, that both parse trees result in similar execution - plans. They are both joins over the two tables. For the UPDATE - the missing columns from t1 are added to the target list by the planner - and the final parse tree will read as + The consequence is, that both parse trees result in similar + execution plans. They are both joins over the two tables. For the + <command>UPDATE</command> the missing columns from t1 are added to + the target list by the planner and the final parse tree will read + as <ProgramListing> UPDATE t1 SET a = t1.a, b = t2.b WHERE t1.a = t2.a; @@ -772,23 +781,27 @@ UPDATE t1 SET a = t1.a, b = t2.b WHERE t1.a = t2.a; SELECT t1.a, t2.b FROM t1, t2 WHERE t1.a = t2.a; </ProgramListing> - will do. But there is a little problem in UPDATE. The executor does - not care what the results from the join it is doing are meant - for. It just produces a result set of rows. The difference that one - is a SELECT command and the other is an UPDATE is handled in the - caller of the executor. The caller still knows (looking at the - parse tree) that this is an UPDATE, and he 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? + will do. But there is a little problem in + <command>UPDATE</command>. The executor does not care what the + results from the join it is doing are meant for. It just produces + a result set of rows. The difference that one is a + <command>SELECT</command> command and the other is an + <command>UPDATE</command> is handled in the caller of the + executor. The caller still knows (looking at the parse tree) that + this is an <command>UPDATE</command>, and he 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? </Para> <Para> To resolve this problem, another entry is added to the target list - in UPDATE (and also in DELETE) statements: the current tuple ID (<acronym>CTID</>). - This is a system attribute containing the file - block number and position in the block for the row. Knowing the table, - the <acronym>CTID</> can be used to retrieve the original t1 row to be updated. - After adding the <acronym>CTID</> to the target list, the query actually looks like + in <command>UPDATE</command> (and also in + <command>DELETE</command>) statements: the current tuple ID + (<acronym>CTID</>). This is a system attribute containing the + file block number and position in the block for the row. Knowing + the table, the <acronym>CTID</> can be used to retrieve the + original t1 row to be updated. After adding the <acronym>CTID</> + to the target list, the query actually looks like <ProgramListing> SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a; @@ -796,13 +809,14 @@ SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a; Now another detail of <ProductName>PostgreSQL</ProductName> enters the stage. At this moment, table rows aren't overwritten and this - is why ABORT TRANSACTION is fast. In an UPDATE, the new result row - is inserted into the table (after stripping <acronym>CTID</>) and - in the tuple header of the row that <acronym>CTID</> pointed to - the <literal>cmax</> and <literal>xmax</> entries are set to the - current command counter and current transaction ID. Thus the old - row is hidden and after the transaction committed the vacuum - cleaner can really move it out. + is why ABORT TRANSACTION is fast. In an <command>UPDATE</command>, + the new result row is inserted into the table (after stripping + <acronym>CTID</>) and in the tuple header of the row that + <acronym>CTID</> pointed to the <literal>cmax</> and + <literal>xmax</> entries are set to the current command counter + and current transaction ID. Thus the old row is hidden and after + the transaction committed the vacuum cleaner can really move it + out. </Para> <Para> @@ -815,10 +829,11 @@ SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a; <Title>The Power of Views in <ProductName>PostgreSQL</ProductName></Title> <Para> - The above demonstrates how the rule system incorporates - view definitions into the original parse tree. In the second example - a simple SELECT from one view created a final parse tree that is - a join of 4 tables (unit is used twice with different names). + The above demonstrates how the rule system incorporates view + definitions into the original parse tree. In the second example a + simple <command>SELECT</command> from one view created a final + parse tree that is a join of 4 tables (unit is used twice with + different names). </Para> <Sect3> @@ -848,16 +863,19 @@ SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a; <indexterm zone="rules-views-update"><primary>views</><secondary>updating</></> <Para> - What happens if a view is named as the target relation for an INSERT, - UPDATE, or DELETE? After doing the substitutions described above, - we will have a query tree in which the result 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. + What happens if a view is named as the target relation for an + <command>INSERT</command>, <command>UPDATE</command>, or + <command>DELETE</command>? After doing the substitutions + described above, we will have a query tree in which the result + 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. </Para> <Para> - To change this we can define rules that modify the behavior - of non-SELECT queries. This is the topic of the next section. + To change this we can define rules that modify the behavior of + non-<command>SELECT</command> queries. This is the topic of the + next section. </Para> </Sect2> @@ -870,10 +888,10 @@ SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a; <Title>Differences from View Rules</Title> <Para> - Rules that are defined ON INSERT, UPDATE and DELETE are - totally different from the view rules described - in the previous section. First, their CREATE RULE - command allows more: + Rules that are defined ON INSERT, UPDATE and DELETE are totally + different from the view rules described in the previous + section. First, their <command>CREATE RULE</command> command + allows more: <ItemizedList> <ListItem> @@ -932,7 +950,7 @@ CREATE RULE rule_name AS ON event <Para> Update rules get applied by the rule system when the result relation and the command type of a parse tree are equal to the - object and event given in the CREATE RULE command. + object and event given in the <command>CREATE RULE</command> command. For update rules, the rule system creates a list of parse trees. Initially the parse tree list is empty. There can be zero (NOTHING keyword), one or multiple actions. @@ -1050,15 +1068,17 @@ CREATE RULE rule_name AS ON event </Para> <Para> - The parse trees found in the actions of the <Filename>pg_rewrite</Filename> - system catalog are only templates. Since they can reference the - range-table entries for NEW and OLD, some substitutions have to be made - before they can be used. For any reference to NEW, the target list of - the original query is searched for a corresponding entry. If found, - that entry's expression replaces the reference. Otherwise - NEW means the same as OLD (for an UPDATE) or is replaced by NULL - (for an INSERT). Any reference to OLD is replaced by a - reference to the range-table entry which is the result relation. + The parse trees found in the actions of the + <structname>pg_rewrite</structname> system catalog are only + templates. Since they can reference the range-table entries for + NEW and OLD, some substitutions have to be made before they can be + used. For any reference to NEW, the target list of the original + query is searched for a corresponding entry. If found, that + entry's expression replaces the reference. Otherwise NEW means the + same as OLD (for an <command>UPDATE</command>) or is replaced by + NULL (for an <command>INSERT</command>). Any reference to OLD is + replaced by a reference to the range-table entry which is the + result relation. </Para> <Para> @@ -1072,9 +1092,10 @@ CREATE RULE rule_name AS ON event <Para> We want to trace changes to the sl_avail column in the - <Filename>shoelace_data</Filename> relation. So we setup a - log table and a rule that conditionally writes a log entry when - an UPDATE is performed on <Filename>shoelace_data</Filename>. + <literal>shoelace_data</literal> relation. So we setup a log table + and a rule that conditionally writes a log entry when an + <command>UPDATE</command> is performed on + <literal>shoelace_data</literal>. <ProgramListing> CREATE TABLE shoelace_log ( @@ -1144,7 +1165,7 @@ INSERT INTO shoelace_log VALUES( INSERT ... VALUES ... FROM. The FROM clause here is just to indicate that there are range-table entries in the parse tree for *NEW* and *OLD*. These are needed so that they can be referenced by variables in the - INSERT command's query tree. + <command>INSERT</command> command's query tree. </Para> <Para> @@ -1287,11 +1308,11 @@ INSERT INTO shoelace_log SELECT </Para> <Para> - Here we can see why it is important that the original parse tree is - executed last. - If the UPDATE would have been executed first, all the rows - are already set to zero, so the logging INSERT - would not find any row where 0 != shoelace_data.sl_avail. + Here we can see why it is important that the original parse tree + is executed last. If the <command>UPDATE</command> would have + been executed first, all the rows are already set to zero, so the + logging <command>INSERT</command> would not find any row where 0 + != shoelace_data.sl_avail. </Para> </Sect3> @@ -1302,9 +1323,9 @@ INSERT INTO shoelace_log SELECT <Para> A simple way to protect view relations from the mentioned - possibility that someone can try to INSERT, UPDATE and DELETE - on them is to let those parse trees get - thrown away. We create the rules + possibility that someone can try to <command>INSERT</command>, + <command>UPDATE</command> and <command>DELETE</command> on them is + to let those parse trees get thrown away. We create the rules <ProgramListing> CREATE RULE shoe_ins_protect AS ON INSERT TO shoe @@ -1316,7 +1337,7 @@ CREATE RULE shoe_del_protect AS ON DELETE TO shoe </ProgramListing> If Al now tries to do any of these operations on the view - relation <Filename>shoe</Filename>, the rule system will + relation <literal>shoe</literal>, the rule system will apply the rules. Since the rules have no actions and are INSTEAD, the resulting list of parse trees will be empty and the whole query will become @@ -1328,7 +1349,7 @@ CREATE RULE shoe_del_protect AS ON DELETE TO shoe A more sophisticated way to use the rule system is to create rules that rewrite the parse tree into one that does the right operation on the real tables. To do that - on the <Filename>shoelace</Filename> view, we create + on the <literal>shoelace</literal> view, we create the following rules: <ProgramListing> @@ -1466,9 +1487,10 @@ UPDATE shoelace SET WHERE bpchareq(shoelace.sl_name, showlace_arrive.arr_name); </ProgramListing> - and throws away the original INSERT on <Filename>shoelace_ok</Filename>. - This rewritten query is passed to the rule system again and - the second applied rule <literal>shoelace_upd</literal> produced + and throws away the original <command>INSERT</command> on + <literal>shoelace_ok</literal>. This rewritten query is passed to + the rule system again and the second applied rule + <literal>shoelace_upd</literal> produced <ProgramListing> UPDATE shoelace_data SET @@ -1486,7 +1508,7 @@ UPDATE shoelace_data SET </ProgramListing> Again it's an INSTEAD rule and the previous parse tree is trashed. - Note that this query still uses the view <Filename>shoelace</Filename>. + Note that this query still uses the view <literal>shoelace</literal>. But the rule system isn't finished with this loop so it continues and applies the <literal>_RETURN</literal> rule on it and we get @@ -1564,11 +1586,12 @@ UPDATE shoelace_data SET </Para> <Para> - There is a little detail that's a bit ugly. Looking at - the two queries turns out, that the <Filename>shoelace_data</Filename> - relation appears twice in the range table where it could definitely - be reduced to one. The planner does not handle it and so the - execution plan for the rule systems output of the INSERT will be + There is a little detail that's a bit ugly. Looking at the two + queries turns out, that the <literal>shoelace_data</literal> + relation appears twice in the range table where it could + definitely be reduced to one. The planner does not handle it and + so the execution plan for the rule systems output of the + <command>INSERT</command> will be <ProgramListing> Nested Loop @@ -1594,12 +1617,12 @@ Merge Join -> Seq Scan on shoelace_arrive </ProgramListing> - that totally produces the same entries in the log relation. - Thus, the rule system caused one extra scan on the - <Filename>shoelace_data</Filename> relation that is - absolutely not necessary. And the same obsolete scan - is done once more in the UPDATE. But it was a really hard - job to make that all possible at all. + that totally produces the same entries in the log relation. Thus, + the rule system caused one extra scan on the + <literal>shoelace_data</literal> relation that is absolutely not + necessary. And the same obsolete scan is done once more in the + <command>UPDATE</command>. But it was a really hard job to make + that all possible at all. </Para> <Para> @@ -1738,15 +1761,17 @@ GRANT SELECT ON phone_number TO secretary; </ProgramListing> Nobody except him (and the database superusers) can access the - phone_data table. But due to the GRANT, the secretary can SELECT from the - phone_number view. The rule system will rewrite - the SELECT from phone_number into a SELECT from phone_data and add the qualification - that only entries where private is false are wanted. Since the - user is the owner of phone_number, the read access to phone_data - is now checked against his permissions and the query is considered - granted. The check for accessing phone_number is also performed, - but this is done against the invoking user, so nobody but the user and the - secretary can use it. + phone_data table. But due to the GRANT, the secretary can + <command>SELECT</command> from the phone_number view. The rule + system will rewrite the <command>SELECT</command> from + phone_number into a <command>SELECT</command> from phone_data and + add the qualification that only entries where private is false are + wanted. Since the user is the owner of phone_number, the read + access to phone_data is now checked against his permissions and + the query is considered granted. The check for accessing + phone_number is also performed, but this is done against the + invoking user, so nobody but the user and the secretary can use + it. </Para> <Para> @@ -1767,9 +1792,10 @@ GRANT SELECT ON phone_number TO secretary; hole, but in fact it isn't. If this would not work, the secretary could setup a table with the same columns as phone_number and copy the data to there once per day. Then it's his own data and - he can grant access to everyone he wants. A GRANT means <quote>I trust you</quote>. + he can grant access to everyone he wants. A + <command>GRANT</command> means <quote>I trust you</quote>. If someone you trust does the thing above, it's time to - think it over and then REVOKE. + think it over and then <command>REVOKE</command>. </Para> <Para> @@ -1823,12 +1849,14 @@ GRANT SELECT ON phone_number TO secretary; <para> If there is any unconditional INSTEAD rule for the query, then the original query will not be executed at all. In this case, - the server will return the command status for the last query that - was inserted by an INSTEAD rule (conditional or unconditional) - and is of the same type (INSERT, UPDATE, or DELETE) as the original - query. If no query meeting those requirements is added by any - rule, then the returned command status shows the original query - type and zeroes for the tuple-count and OID fields. + the server will return the command status for the last query + that was inserted by an INSTEAD rule (conditional or + unconditional) and is of the same type + (<command>INSERT</command>, <command>UPDATE</command>, or + <command>DELETE</command>) as the original query. If no query + meeting those requirements is added by any rule, then the + returned command status shows the original query type and + zeroes for the tuple-count and OID fields. </para> </listitem> </orderedlist> @@ -1859,13 +1887,13 @@ GRANT SELECT ON phone_number TO secretary; </Para> <Para> - 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 it cannot - do the same thing on UPDATE or 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. + On the other hand a trigger that is fired on + <command>INSERT</command> on a view can do the same as a rule, put + the data somewhere else and suppress the insert in the view. But + it cannot do the same thing on <command>UPDATE</command> or + <command>DELETE</command>, 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. </Para> <Para> @@ -2018,7 +2046,7 @@ DELETE FROM software WHERE computer.manufacurer = 'bim' </Para> <Para> - Another situation is cases on UPDATE where it depends on the + Another situation is cases on <command>UPDATE</command> where it depends on the change of an attribute if an action should be performed or not. In <ProductName>PostgreSQL</ProductName> version 6.4, the attribute specification for rule events is disabled (it will have diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml index 6a54e121185..887c0dc1d2b 100644 --- a/doc/src/sgml/syntax.sgml +++ b/doc/src/sgml/syntax.sgml @@ -1,5 +1,5 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/syntax.sgml,v 1.74 2002/11/15 03:11:17 momjian Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/syntax.sgml,v 1.75 2003/02/19 03:13:24 momjian Exp $ --> <chapter id="sql-syntax"> @@ -121,9 +121,9 @@ INSERT INTO MY_TABLE VALUES (3, 'hi there'); characters of an identifier; longer names can be written in commands, but they will be truncated. By default, <symbol>NAMEDATALEN</symbol> is 64 so the maximum identifier - length is 63 (but at the time <productname>PostgreSQL</> is built, - <symbol>NAMEDATALEN</symbol> can be changed in - <filename>src/include/postgres_ext.h</filename>). + length is 63. If this limit is problematic, it can be raised by + changing the <symbol>NAMEDATALEN</symbol> constant in + <filename>src/include/postgres_ext.h</filename>. </para> <para> @@ -541,8 +541,8 @@ CAST ( '<replaceable>string</replaceable>' AS <replaceable>type</replaceable> ) <para> A dollar sign (<literal>$</literal>) followed by digits is used to represent the positional parameters in the body of a function - definition. In other contexts the dollar sign may be part of an - operator name. + definition or a prepared statement. In other contexts the + dollar sign may be part of an operator name. </para> </listitem> |