Postgresql – With Postgres, can I make a rule that will not recurse

postgresql

The rule is already in place, and I'm hoping to fix the rule itself rather than change the whole process on how this works due to some bureaucracy I'd rather not deal with. Or in other words, I know there are other ways to achieve my goal, but I specifically want to know if it can be done somehow with a rule.

The current rule is that when an update occurs, another update fires to concatenate a few fields together. That update performed by the rule seems to call the rule again and generates an "infinite recursion" error.

Rule example:

ON UPDATE TO people DO 
UPDATE people 
SET "fullname" = concat(new."firstName", ' ', new."lastName") 
WHERE people.id = new.id

Is there any way to tell the update that the rule does to not then call the rule again? Or to flag the rule with some sort of runOnce? It's my understanding rules alter the query, so I can't do something simple like AND people.fullname != new.fullname

Best Answer

If fullname column is never altered manually (i.e. it must match firstname and lastname values always) then simply use generated column. It will be (re)calculated automatically during insert/update:

ALTER TABLE people
    DROP COLUMN fullName,
    ADD COLUMN fullName VARCHAR(200) GENERATED ALWAYS AS (firstName || ' ' || lastName) STORED;

DEMO

Related Solutions

Postgresql – How to make Postgres autovacuum not impact performance

Few basic points could alleviate your fear.

Running autovaccum will eventually improve the performance of queries. It is not something to harm the performance. This is because autovaccum does the garbage collection of dead tuples in table. so queries need to scan though less number of tuples. In your question, it is not clear how you analysed that autovaccum is creating performance problem. if possible please provide details.
Autovaccum algorithms/logic in Postgres is continuouly improved in new versions. for example, Version 9.2 received a patch for skipping a table if there is lock. so autovaccum will do better job in handling bloat. This logic is further improved to skip blocks which are having locks. in latest version 9.6 (which is in development) received a patch to completely skip all the blocks where all tuples are already freezed. Robert Hass posted about this recently: http://rhaas.blogspot.in/2016/03/no-more-full-table-vacuums.html
You may want to fine tune autovaccum related parameters to suit your system. if possible take the help of expert DBA. Each parameter is documented very well in Postgres documentation : http://www.postgresql.org/docs/9.5/static/runtime-config-autovacuum.html

So Regarding your question : "Is there a way to make it not consume as much memory/CPU so that client queries can run more efficiently?"

Answer is yes. by fine tuning the parameters mentioned in the above link you can achieve the best results for your system

A general advice will be to always upgrade to latest stable version of PostgreSQL to take advantage of all the autovaccum improvements. Always we should keep in mind that autovaccum is very necessary for keeping the performance of the database. If there is CPU or Memory starvation in your system, consider getting better machine. otherwise tables will become bloated and performance goes down over a period of time.

Postgresql – Postgres create an insert return rule

RETURN isn't a valid command in a rule definition; you can only use SELECT, INSERT, UPDATE, DELETE, and NOTIFY.

You can create an ALSO rule to return the value of the new row, something like this:

> CREATE RULE return_data AS ON INSERT TO FOO DO ALSO SELECT NEW.*;
CREATE RULE

> INSERT INTO foo VALUES (1, 'test');
 a |  b
---+------
 1 | test
(1 row)

INSERT 0 1

Note that you can do something identical by just adding the RETURNING clause to the INSERT statement:

> INSERT INTO foo VALUES (2, 'test 123') RETURNING *;
 a |    b
---+----------
 2 | test 123
(1 row)


INSERT 0 1

Best Answer

Related Solutions

Postgresql – How to make Postgres autovacuum not impact performance

Postgresql – Postgres create an insert return rule

Related Question