0

I have a simple table like this :

CREATE TABLE gateway_text (
  id         BIGSERIAL                                  NOT NULL PRIMARY KEY,
  text       TEXT                                       NOT NULL,
  hash       char(40)                                   UNIQUE,
  created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT now()  NOT NULL
);

CREATE RULE "gateway_text_on_duplicate_ignore" AS ON INSERT TO "gateway_text"
  WHERE EXISTS(SELECT 1 FROM gateway_text WHERE hash=NEW.hash)
  DO INSTEAD SELECT * FROM gateway_text WHERE hash=NEW.hash;

SQL Fiddle

As you can see, the rule is for when the first select is passed (the hash is already in database) so the first insert, is not the one DO INSTEAD supposed to run for. But the problem is, every time I run insert, I get the select result, even for the first insert of a hash. In my case this is OK, very OK actually, but I don't know if this is correct or this is a bug? can I simply accept this as normal and relay on this?

1

Yes, the SELECT executing in addition to the INSERT is the behavior that should be expected.

Based on the qualification (the where exists()... clause) and the DO INSTEAD clause, the RULE produces a sequence of queries looking like this, in pseudo-code:

IF NOT EXISTS (SELECT 1 FROM gateway_text WHERE hash=NEW.hash)
 THEN INSERT...

IF EXISTS (SELECT 1 FROM gateway_text WHERE hash=NEW.hash)
 THEN SELECT * FROM gateway_text WHERE hash=NEW.hash;

In the first step, NEW.hash is not in the table yet so the INSERT is executed (or if it's in the table already, then it's not executed, but that's not the case you're asking about).

In the second step, NEW.hash is now in the table so the SELECT is executed too.

See Rules on INSERT, UPDATE, and DELETE in PostgreSQL documentation for more details.

The (wrong) idea that the SELECT should not be executed when the INSERT gets executed is a common misinterpretation of how RULEs work, because intuition is misleading here. When looking at the rule, we're tempted to think that it does this:

IF EXISTS (SELECT 1 FROM gateway_text WHERE hash=NEW.hash) THEN SELECT * FROM gateway_text WHERE hash=NEW.hash ELSE INSERT...

But it doesn't. To achieve this, a procedural language like plpgsql should be used.

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.