Postgres Rules CTE Request Prevention

Question

Using Postgres 9.3:

I am trying to automatically populate a table when an insert is performed in another table. This seems like using the rules correctly, but after adding the rule to the first table, I can no longer insert into the second table using a writable CTE. Here is an example:

CREATE TABLE foo ( id INT PRIMARY KEY ); CREATE TABLE bar ( id INT PRIMARY KEY REFERENCES foo ); CREATE RULE insertFoo AS ON INSERT TO foo DO INSERT INTO bar VALUES (NEW.id); WITH a AS (SELECT * FROM (VALUES (1), (2)) b) INSERT INTO foo SELECT * FROM a 

When this is done, I get an error

"ERROR: WITH cannot be used in a request that is rewritten by rules into multiple requests."

I searched for this error line, but I can only find links to the source code. I know that I can accomplish the above using row level triggers, but it looks like I should do this at the instruction level. Why can't I use a writable CTE when such requests can (in this case) be easily rewritten as:

 INSERT INTO foo SELECT * FROM (VALUES (1), (2)) a 

Does anyone know of a different way that will accomplish what I'm trying to do except 1) use rules, which prevents the use of "c" requests or 2) using row level triggers? Thanks

Answer 1

TL DR: use triggers, not rules.

Generally speaking, prefer triggers over rules, unless the rules are absolutely necessary. (In practice, they never happen.)

Using rules is a bunch of problems that unnecessarily complicate your life in the future. You come across this here. Another (main) is, for example, the number of rows affected will correspond to the number of the most recent request - if you rely on FOUND somewhere, and your request incorrectly reports that none of the lines were affected by the request, you will experience painful errors.

In addition, they sometimes talk about obsolete Postgres rules:

http://postgresql.nabble.com/Deprecating-RULES-td5727689.html