DEV Community

Peter Timoshevsky
Peter Timoshevsky

Posted on

Rule on insert

#help #postgres

Hello!

There is a bug in one of the database modules I use and sometimes it inserts 'undefined' into the database. I want to create a rule that will replace one of the values in the row with another. I've tried this:

CREATE RULE replace_undefined AS ON INSERT TO telegraf_session
    WHERE NEW.session = 'undefined'
    DO INSTEAD INSERT INTO telegraf_session VALUES(NEW.id, REPLACE(NEW.session, 'undefined', '{}');
Enter fullscreen mode Exit fullscreen mode

But on inserting with this rule I get ERROR: infinite recursion detected in rules for relation "telegraf_session"

What am I doing wrong? I thing the condition isn't respected, but I'm not sure why.

Thanks!

Top comments (3)

Collapse
 
rhymes profile image
rhymes • • Edited

Your rule on insert triggers a rule on insert which triggers a rule on insert which triggers a rule on insert :-)

You might want to use a view if the goal is to have the app see {} instead of undefined or you could look into a trigger.

This works on PostgreSQL 11:

create table telegraf_session (id bigserial primary key, session text);

CREATE OR REPLACE FUNCTION telegraf_session_replace_undefined()
  RETURNS trigger AS $$
BEGIN
   IF NEW.session = 'undefined' THEN
     NEW.session := '{}';
   END IF;
   RETURN NEW;
END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER telegraf_session_replace_undefined_trigger
BEFORE INSERT ON telegraf_session
FOR EACH ROW
EXECUTE PROCEDURE telegraf_session_replace_undefined();

insert into telegraf_session (session) values ('valid');
insert into telegraf_session (session) values ('undefined');
Enter fullscreen mode Exit fullscreen mode

and this is the output:

test=# select * from telegraf_session;
 id | session
----+---------
  4 | valid
  5 | {}
(2 rows)
Enter fullscreen mode Exit fullscreen mode
Collapse
 
fcfn profile image
Peter Timoshevsky •

Thanks a ton!

Do I understand correctly that it's impossible to have a rule on insert that does an insert to the same table as no matter the condition it will still trigger itself before the condition is checked thus giving the infinite recursion error?

Collapse
 
rhymes profile image
rhymes •

I'm not 100% certain but as far as I know, yes it's not possible. The purpose of rules is to rewrite queries, sort of a macro system: "you write X and I write y". If the source and the target are the same, the rule itself gets re-activated.

A trigger instead is a precondition (or post condition) to a query.

Read next

spurgeon_prakash profile image

MERN (Full Stack Neon Starter kit with complete authentication) Submission for Neon Open Source Starter Kit Challenge

Spurgeon Gnan Prakasham -

ayoaduwo profile image

Real-Time Chat Application Starter kit with Neon Postgres, NestJS, and WebSocket.

Fola -

sam_shalemrajtara_8bda2 profile image

Full Stack Auth Implementation using Next js and Neon DB for Neon Challenge

Sam Shalem Raj Tara -

king_triton profile image

Help Needed with Creating a Console Bot Using OpenAI Assistants API

King Triton -