From 4996eea81cd52833cb8413c5105aba6937971748 Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Wed, 19 Feb 2003 03:13:25 +0000 Subject: [PATCH] This patch includes more SGML markup fixes as well as a few minor additions to the docs. Neil Conway --- doc/src/sgml/ddl.sgml | 10 +- doc/src/sgml/ref/create_table.sgml | 11 +- doc/src/sgml/ref/create_type.sgml | 9 +- doc/src/sgml/rules.sgml | 450 ++++++++++++++++++++----------------- 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 6c58bfeb81..01ea0488df 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -1,4 +1,4 @@ - + Data Definition @@ -189,7 +189,7 @@ DROP TABLE products; individual table a row came from. The tableoid can be joined against the oid column of - pg_class to obtain the table name. + pg_class to obtain the table name. @@ -904,9 +904,9 @@ WHERE c.altitude > 500; 139798 | Madison | 845 - (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 + pg_class you can see the actual table names: 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 3439c73115..3c0a9fc3a8 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -1,5 +1,5 @@ @@ -230,7 +230,10 @@ and table_constraint 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 + WITHOUT OIDS also reduces the space required + to store the table on disk by 4 bytes per row of the table, + thereby improving performance. @@ -881,8 +884,8 @@ CREATE ASSERTION name CHECK ( condition< @@ -218,7 +218,8 @@ CREATE TYPE The first form of CREATE TYPE 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 CREATE + FUNCTION) before defining the type. The representation of a new base type is determined by input_function, which converts the type's external representation to an internal @@ -400,8 +401,8 @@ CREATE TYPE User-defined type names cannot begin with the underscore character (_) and can only be 62 - characters long (or in general NAMEDATALEN-2, rather than - the NAMEDATALEN-1 characters allowed for other names). + characters long (or in general NAMEDATALEN - 2, rather than + the NAMEDATALEN - 1 characters allowed for other names). Type names beginning with underscore are reserved for internally-created array type names. diff --git a/doc/src/sgml/rules.sgml b/doc/src/sgml/rules.sgml index 4b72d5de3b..25c78cce88 100644 --- a/doc/src/sgml/rules.sgml +++ b/doc/src/sgml/rules.sgml @@ -1,4 +1,4 @@ - + The Rule System @@ -59,7 +59,7 @@ 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 pg_rewrite catalog, which are + rules from the pg_rewrite 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 PostgreSQL backend with debug level 4 and typing queries into the interactive backend interface. The rule - actions in the pg_rewrite system catalog are + actions in the pg_rewrite system catalog are also stored as query trees. They are not formatted like the debug output, but they contain exactly the same information. @@ -107,7 +107,9 @@ This is a simple value telling which command - (SELECT, INSERT, UPDATE, DELETE) produced the parse tree. + (SELECT, INSERT, + UPDATE, DELETE) produced + the parse tree. @@ -120,7 +122,7 @@ 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 SELECT statement these are the relations given after the FROM keyword. @@ -148,16 +150,17 @@ - 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. + 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. - On INSERT, UPDATE and DELETE queries the result relation - is the table (or view!) where the changes take effect. + On INSERT, UPDATE and + DELETE queries the result relation is the table + (or view!) where the changes take effect. @@ -168,41 +171,44 @@ - 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 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.) - DELETE queries 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. + DELETE queries 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. - 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 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 planner with a constant NULL expression. - 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 CTID entry just as for DELETE too. + 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 + CTID entry just as for DELETE too. @@ -220,12 +226,13 @@ - 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 - SQL 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 + (INSERT, UPDATE, + DELETE or SELECT) for the + final result row should be executed or not. It is the WHERE clause + of an SQL statement. @@ -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 SELECT. @@ -296,26 +303,26 @@ 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 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 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 + 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. -How SELECT Rules Work +How <command>SELECT</command> Rules Work - 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 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. @@ -326,15 +333,16 @@ CREATE RULE "_RETURN" AS ON SELECT TO myview DO INSTEAD - 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 + 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. @@ -428,21 +436,21 @@ CREATE VIEW shoe_ready AS AND rsl.sl_len_cm <= rsh.slmaxlen_cm; - The CREATE VIEW command for the shoelace - view (which is the simplest one we have) - will create a relation shoelace and an entry - in pg_rewrite - 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 CREATE VIEW command for the + shoelace view (which is the simplest one we + have) will create a relation shoelace and an entry in + pg_rewrite 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 rule's action is one query tree that is a copy of the - SELECT statement in the view creation command. + SELECT statement in the view creation command. Note @@ -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 pg_rewrite entry aren't of interest + the pg_rewrite entry aren't of interest for SELECT rules. - Now we populate unit, shoe_data - and shoelace_data and Al types the first - SELECT in his life: + Now we populate unit, shoe_data + and shoelace_data and Al types the first + SELECT in his life: al_bundy=> INSERT INTO unit VALUES ('cm', 1.0); @@ -504,10 +512,10 @@ sl6 | 0|brown | 0.9|m | 90 (8 rows) - It's the simplest SELECT Al can do on our views, so we take this - to explain the basics of view rules. - The SELECT * FROM shoelace was interpreted by the parser and - produced the parse tree + It's the simplest SELECT Al can do on our + views, so we take this opportunity to explain the basics of view + rules. The SELECT * FROM shoelace was + interpreted by the parser and produced the parse tree SELECT shoelace.sl_name, shoelace.sl_avail, @@ -517,9 +525,9 @@ SELECT shoelace.sl_name, shoelace.sl_avail, and this is given to the rule system. The rule system walks through the - range table and checks if there are rules in pg_rewrite + range table and checks if there are rules in pg_rewrite for any relation. When processing the range table entry for - shoelace (the only one up to now) it finds the + shoelace (the only one up to now) it finds the _RETURN rule with the parse tree @@ -618,7 +626,7 @@ SELECT shoe_ready.shoename, shoe_ready.sh_avail, The first rule applied will be the one for the - shoe_ready view and it results in the + shoe_ready view and it results in the parse tree @@ -637,8 +645,8 @@ SELECT shoe_ready.shoename, shoe_ready.sh_avail, WHERE int4ge(shoe_ready.total_avail, 2); - Similarly, the rules for shoe and - shoelace are substituted into the range table of + Similarly, the rules for shoe and + shoelace are substituted into the range table of the sub-query, leading to a three-level final query tree: @@ -686,17 +694,16 @@ SELECT shoe_ready.shoename, shoe_ready.sh_avail, Note - 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 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. @@ -713,12 +720,13 @@ SELECT shoe_ready.shoename, shoe_ready.sh_avail, - 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 + 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 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; - 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 + UPDATE the missing columns from t1 are added to + the target list by the planner and the final parse tree will read + as 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; - 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 + 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? To resolve this problem, another entry is added to the target list - in UPDATE (and also in DELETE) statements: the current tuple ID (CTID). - This is a system attribute containing the file - block number and position in the block for the row. Knowing the table, - the CTID can be used to retrieve the original t1 row to be updated. - After adding the CTID to the target list, the query actually looks like + in UPDATE (and also in + DELETE) statements: the current tuple ID + (CTID). This is a system attribute containing the + file block number and position in the block for the row. Knowing + the table, the CTID can be used to retrieve the + original t1 row 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; @@ -796,13 +809,14 @@ SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a; Now another detail of PostgreSQL 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 CTID) and - in the tuple header of the row that CTID pointed to - the cmax and 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 UPDATE, + the new result row is inserted into the table (after stripping + CTID) and in the tuple header of the row that + CTID pointed to the cmax and + 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. @@ -815,10 +829,11 @@ SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a; The Power of Views in <ProductName>PostgreSQL</ProductName> - 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 SELECT from one view created a final + parse tree that is a join of 4 tables (unit is used twice with + different names). @@ -848,16 +863,19 @@ SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a; viewsupdating - 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 + 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. - 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-SELECT queries. This is the topic of the + next section. @@ -870,10 +888,10 @@ SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a; Differences from View Rules - 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 CREATE RULE command + allows more: @@ -932,7 +950,7 @@ CREATE RULE rule_name AS ON event 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 CREATE RULE 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 - The parse trees found in the actions of the pg_rewrite - 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 + pg_rewrite 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. @@ -1072,9 +1092,10 @@ CREATE RULE rule_name AS ON event We want to trace changes to the sl_avail column in the - shoelace_data relation. So we setup a - log table and a rule that conditionally writes a log entry when - an UPDATE is performed on shoelace_data. + shoelace_data relation. So we setup a log table + and a rule that conditionally writes a log entry when an + UPDATE is performed on + shoelace_data. 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. + INSERT command's query tree. @@ -1287,11 +1308,11 @@ INSERT INTO shoelace_log SELECT - 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 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. @@ -1302,9 +1323,9 @@ INSERT INTO shoelace_log SELECT 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 INSERT, + UPDATE and DELETE on them is + to let those parse trees get thrown away. We create the rules CREATE RULE shoe_ins_protect AS ON INSERT TO shoe @@ -1316,7 +1337,7 @@ CREATE RULE shoe_del_protect AS ON DELETE TO shoe If Al now tries to do any of these operations on the view - relation shoe, the rule system will + relation shoe, 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 shoelace view, we create + on the shoelace view, we create the following rules: @@ -1466,9 +1487,10 @@ UPDATE shoelace SET WHERE bpchareq(shoelace.sl_name, showlace_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 produced + 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 produced UPDATE shoelace_data SET @@ -1486,7 +1508,7 @@ UPDATE shoelace_data SET Again it's an INSTEAD rule and the previous parse tree is trashed. - Note that this query still uses the view shoelace. + Note that this query still uses the view shoelace. But the rule system isn't finished with this loop so it continues and applies the _RETURN rule on it and we get @@ -1564,11 +1586,12 @@ UPDATE shoelace_data SET - There is a little detail that's a bit ugly. Looking at - the two queries turns out, that the shoelace_data - 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 shoelace_data + 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 Nested Loop @@ -1594,12 +1617,12 @@ Merge Join -> Seq Scan on shoelace_arrive - that totally produces the same entries in the log relation. - Thus, the rule system caused one extra scan on the - shoelace_data 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 + shoelace_data 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. @@ -1738,15 +1761,17 @@ GRANT SELECT ON phone_number TO secretary; 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 + 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. @@ -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 I trust you. + he can grant access to everyone he wants. A + GRANT means I trust you. If someone you trust does the thing above, it's time to - think it over and then REVOKE. + think it over and then REVOKE. @@ -1823,12 +1849,14 @@ GRANT SELECT ON phone_number TO secretary; 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 + (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. @@ -1859,13 +1887,13 @@ GRANT SELECT ON phone_number TO secretary; - 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 + 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. @@ -2018,7 +2046,7 @@ DELETE FROM software WHERE computer.manufacurer = 'bim' - Another situation is cases on UPDATE where it depends on the + 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 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 6a54e12118..887c0dc1d2 100644 --- a/doc/src/sgml/syntax.sgml +++ b/doc/src/sgml/syntax.sgml @@ -1,5 +1,5 @@ @@ -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, NAMEDATALEN is 64 so the maximum identifier - length is 63 (but at the time PostgreSQL is built, - NAMEDATALEN can be changed in - src/include/postgres_ext.h). + length is 63. If this limit is problematic, it can be raised by + changing the NAMEDATALEN constant in + src/include/postgres_ext.h. @@ -541,8 +541,8 @@ CAST ( 'string' AS type ) A dollar sign ($) 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. -- 2.11.0