9

From docs - 37.3.1.1. "A First Rule Step by Step"

CREATE TABLE shoelace_log (
    sl_name    text,          -- shoelace changed
    sl_avail   integer,       -- new available value
    log_who    text,          -- who did it
    log_when   timestamp      -- when
);

CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data
    WHERE NEW.sl_avail <> OLD.sl_avail
    DO INSERT INTO shoelace_log VALUES (
                                    NEW.sl_name,
                                    NEW.sl_avail,
                                    current_user,
                                    current_timestamp
                                );

Now someone does:

(1) UPDATE shoelace_data SET sl_avail = 6 WHERE sl_name = 'sl7';

And the parser generates this additional query

(2) INSERT INTO shoelace_log VALUES (
       shoelace_data.sl_name, 6,
       current_user, current_timestamp )
  FROM shoelace_data
 WHERE 6 <> shoelace_data.sl_avail
   AND shoelace_data.sl_name = 'sl7';

The question is: Are there any tools to tell how the query (1) gets rewritten into (1) + (2)?

5

There is no direct way to see an SQL representation of the rewritten query, because the rewriting happens on an internal tree representation, and it's not easy to turn this back into SQL. The closest thing is turning on the configuration parameter debug_print_rewritten, which prints a representation of that internal tree format to the server log. If you use this in conjunction with the settings debug_print_parse and debug_print_plan (and possibly debug_pretty_print), you can see how the query is transformed through the various stages. The format isn't easy to read, but if you are interested in learning the details of this, it will probably be worth it.

  • I've also learned that EXPLAIN gives information on the number and type of queries involved (and is far more readable than database's debug log :). – hegemon Nov 17 '11 at 20:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.