Changing PostgreSQL s Rule System
Changing PostgreSQL's Rule SystemRules are another fundamental component of PostgreSQL. Rules define the behavior of PostgreSQL and are a very powerful tool. Using Rules for Changing PostgreSQL's BehaviorIn this section, you look at PostgreSQL's rule system and see what can be done with the help of rules. We will not cover all technical details, because this is very complicated and requires a lot of knowledge about PostgreSQL's internals (parse tree, and so forth) The focus will be on aspects that are interesting for users and programmers who want to build more sophisticated PostgreSQL applications. Let's get right to some practical stuff. To add a rule to the database, the CREATE RULE command has to be used; here is an overview of the command's syntax: mydata=# \ h CREATE RULE Command: CREATE RULE Description: Defines a new rule Syntax: CREATE RULE name AS ON event TO object [ WHERE condition ] DO [ INSTEAD ] action where action can be: NOTHING query ( query ; query ... ) [ query ; query ... ] To remove a rule from the database, we use the DROP RULE command: mydata=# \ h DROP RULE Command: DROP RULE Description: Removes existing rules from the database Syntax: DROP RULE name [, ...] The next example is a prototype of a logging function. The function is simple so that it can easily be understood : #include <stdio.h> #include <pgsql/postgres.h> #include <pgsql/fmgr.h> PG_FUNCTION_INFO_V1(myelog); Datum myelog(PG_FUNCTION_ARGS) { text *arg1 = PG_GETARG_TEXT_P(0); elog(NOTICE, "%s", VARDATA(arg1)); PG_RETURN_INT16(1); } You can see how the logging function has been implemented. First we extract the input and pass the data to the elog function used to add an entry to the logfile. Finally, 1 is returned. After compiling the code, we add the function to the database and test it: mydata=# CREATE FUNCTION myelog(text) RETURNS int4 AS '/mnt/data/c/postgres_extpack/libfoo.so' LANGUAGE 'c'; CREATE mydata=# SELECT myelog('my notice function'); NOTICE: my notice function myelog -------- 1 (1 row) One line is added to PostgreSQL's logfile: 2001-07-02 16:12:40 [4241] NOTICE: my notice function We have defined a logging function that can be used in combination with PostgreSQL 7.1 or later. For users of 7.0, this function will not work, because fmgr.h and postgres.h were both installed in the include directory but not in 7.1 and up. Now we create a table called mynumber , using a simple SQL statement: mydata=# CREATE TABLE mydata(a int4, b int4); CREATE In the next step, we want to define a rule that tells the database to call the logging function every time a value is inserted into table mynumber . The rule for that is very simple; here is the SQL code to create the rule: CREATE RULE notice_insert AS ON INSERT TO mynumber DO SELECT myelog('Values inserted into mynumber'); If we insert a row into table mynumber , the NOTICE message is displayed: mydata=# INSERT INTO mynumber VALUES('12', '23'); NOTICE: Values inserted into mynumber INSERT 120348 1 The value has been inserted successfully into the table. PostgreSQL stores information about rules in a system table called pg_rewrite . We query the table to find out what has been added for the rule that we have just defined, using the following SQL statement: SELECT * FROM pg_rewrite WHERE rulename='notice_insert'; The data structure of table pg_rewrite can easily be extracted from the database by using the \d command: mydata=# \ d pg_rewrite Table "pg_rewrite" Attribute Type Modifier ------------+----------+---------- rulename name ev_type "char" ev_class oid ev_attr smallint is_instead boolean ev_qual text ev_action text Indices: pg_rewrite_oid_index, pg_rewrite_rulename_index Here is the result of the query: notice_insert 3 120251 -1 f <> ({ QUERY :command 1 :utility <> :resultRelation 0 :into <> :isPortal false :isBinary false :isTemp false :hasAggs false :hasSubLinks false :rtable ({ RTE :relname mynumber :relid 120251 :subquery <> :alias { ATTR :relname *OLD* :attrs <>} :eref { ATTR :relname *OLD* :attrs ( "a" "b" )} :inh false :inFromCl false :checkForRead false :checkForWrite false :checkAsUser 500} { RTE :relname mynumber :relid 120251 :subquery <> :alias { ATTR :relname *NEW* :attrs <>} :eref { ATTR :relname *NEW* :attrs ( "a" "b" )} :inh false :inFromCl false :checkForRead false :checkForWrite false :checkAsUser 500} ) :jointree { FROMEXPR : fromlist <> :quals <>} :rowMarks () :targetList ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname myelog :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { EXPR :typeOid 23 :opType func :oper { FUNC :funcid 120346 :functype 23 } :args ({ CONST :consttype 25 :constlen -1 :constbyval false :constisnull false :constvalue 33 [ 33 0 0 0 86 97 108 117 101 115 32 105 110 115 101 114 116 101 100 32 105 110 116 111 32 109 121 110 117 109 98 101 114 ] })}}) :groupClause <> :havingQual <> :distinctClause <> :sortClause <> :limitOffset <> :limitCount <> :setOperations <> :resultRelations ()} ) (1 row) The result of the query shows quite well that rules are a very complex topic in PostgreSQL and are a fundamental and powerful component of the database server. But rules can be used for other purposes. The following is a script called a.sql , which defines two rules. The first rule tells the server to call the logging function. The second rule changes the behavior of the server again and tells the database to do nothing instead of inserting the values defined in the INSERT statement: CREATE RULE notice_insert AS ON INSERT TO mynumber DO SELECT myelog('Values inserted into mynumber'); INSERT INTO mynumber VALUES('12', '23'); CREATE RULE notice_redefine AS ON INSERT TO mynumber DO INSTEAD NOTHING; INSERT INTO mynumber VALUES('13', '24'); The two rules defined here do the opposite of each other. Let's execute the script: [hs@duron postgres_extpack]$ psql mydata < /tmp/a.sql CREATE NOTICE: Values inserted into mynumber INSERT 120356 1 CREATE NOTICE: Values inserted into mynumber myelog -------- 1 (1 row) The first INSERT statement is executed and the NOTICE message is displayed on the screen. Then we add the second rule to the database to make sure that the INSERT statement is not executed. The NOTICE message is displayed because only the INSERT statement is replaced . If we query table mynumber , we can see that the table contains only one value: mydata=# SELECT * FROM mynumber; a b ----+---- 12 23 (1 row) Until now, we have defined rules that call a certain function, such as our logging function. Now we define a rule used to execute an SQL statement: CREATE RULE notice_insert AS ON INSERT TO mynumber DO INSERT INTO mynumber VALUES('1', '2'); INSERT INTO mynumber VALUES('12', '23'); The problem with this SQL code is that the rule causes an INSERT statement to be performed, which also causes an INSERT statement to be performed. The result is an endless recursion. Luckily, PostgreSQL has an emergency brake implemented, which quits a recursion after 10 steps (10 has been compiled into the binaries). If we execute the previous script, the database stops the execution of the code: [hs@duron postgres_extpack]$ psql mydata < /tmp/a.sql CREATE ERROR: query rewritten 10 times, may contain cycles Because the entire process is executed in one transaction, mynumber does not contain 10 values. As you can see in the following, the table is still empty: mydata=# SELECT * FROM mynumber; a b ---+--- (0 rows) Using Rules in Combination with ViewsWhen dealing with views, PostgreSQL's rule system might be essential, because without it some basic operations cannot be performed. In this section, you will see how views can be used efficiently in combination with views. Let's insert a value into mynumber : mydata=# INSERT INTO mynumber VALUES('1', '2'); INSERT 120373 1 As you might expect, the record has been inserted into the table successfully: mydata=# SELECT * FROM mynumber; a b ---+--- 1 2 (1 row) Now we create a view that multiplies the values in the table by 2 : CREATE VIEW my_view AS SELECT a*2 AS c, b*2 AS d FROM mynumber; If we query the table, we receive the result: mydata=# SELECT * FROM my_view; c d ---+--- 2 4 (1 row) Now we want to update the record in the view by using a simple UPDATE statement: mydata=# UPDATE my_view SET c=6 WHERE c=2; ERROR: Cannot update a view without an appropriate rule The UPDATE statement fails, because the database does not know how to treat the tables the view is defined on. A view can be updated only when the user defines a set of rules. Then the database will know what to do when an UPDATE operation has to be performed. A rule can be defined like this: CREATE RULE view_update AS ON UPDATE TO my_view DO INSTEAD UPDATE mynumber SET a=NEW.c/2, b=NEW.d/2; We tell the database to perform an UPDATE query on table mynumber instead of the UPDATE statement on the view. We set the values in the table to the half of the values that we pass to the UPDATE statement for the view, because the view displays the values in the table multiplied by 2 . NEW tells the database to use the value that the view should contain after the UPDATE operation. If we want the old value to be used, we use OLD instead. Let's perform the UPDATE operation to see whether it works: mydata=# UPDATE my_view SET c=6 WHERE c=2; UPDATE 1 The UPDATE operation has been executed successfully. Let's see what can be retrieved from the view: mydata=# SELECT * FROM my_view; c d ---+--- 6 4 (1 row) The values in the view have been changed correctly. Because the view does not contain the data, we query the table that the view is defined on: mydata=# SELECT * FROM mynumber; a b ---+--- 3 2 (1 row) The UPDATE query has changed the value in the first column from 1 to 3 , which is correct. Because we do not want to perform only UPDATE operations, we also define rules for INSERT and DELETE statements: CREATE RULE view_insert AS ON INSERT TO my_view DO INSTEAD INSERT INTO mynumber VALUES( (NEW.c/2)::int4, (NEW.d/2)::int4); CREATE RULE view_delete AS ON DELETE TO my_view DO INSTEAD DELETE FROM mynumber WHERE a=(OLD.c/2)::int4 AND b=(OLD.d/2)::int4; The rule for INSERT statements is easy. We simply tell the database to insert the values passed to the INSERT statement into the table that the view is defined on. We have to make sure that the result of the division is still an integer value; otherwise , we can't insert the value into the table, because both columns of the table ( a and b ) are integer fields. In the example, we solved the problem by using a simple cast. A more sophisticated solution would be to use a function that checks whether the result of c/2 or d/2 is a valid integer number. The rule that we have defined for DELETE statements divides the values of the two columns by 2 , casts the result to integer, and deletes the values from the table that the view is defined on. Let's try to insert a value into a view now: mydata=# INSERT INTO my_view VALUES('12', '10'); INSERT 120400 1 mydata=# SELECT * FROM mynumber; a b ---+--- 3 2 6 5 (2 rows) The correct value has successfully been inserted into the table that we are using for our view. Logically, the correct value can also be found when querying the view itself: mydata=# SELECT * FROM my_view; c d ----+---- 6 4 12 10 (2 rows) Let's try to delete records from the table: mydata=# DELETE FROM my_view WHERE c=12; DELETE 1 mydata=# SELECT * FROM my_view; c d ---+--- 6 4 (1 row) The value has successfully been deleted. To make sure, that the rule works correctly, we try to delete records that don't exist in the table: mydata=# DELETE FROM my_view WHERE c=6 AND d=3; DELETE 0 Nothing has been deleted, so we have proved that the rule does exactly what we want it to do.  |
