Thread: infinite recursion detected in rules for relation

infinite recursion detected in rules for relation

From
pinker
Date:
I wanted to set a rule:
CREATE RULE "_RETURN" AS   ON SELECT * from backend.test   DO INSTEAD       SELECT * FROM backend.test WHERE who='Me';
When I'm trying to do anything on the table I get following error:
ERROR:  infinite recursion detected in rules for relation "backend.test"

********** Błąd **********

ERROR: infinite recursion detected in rules for relation "backend.test"
Stan SQL: 42P17
Is there any way to avoid that? Maybe there exist some other approaches that could be useful ?

View this message in context: infinite recursion detected in rules for relation
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: infinite recursion detected in rules for relation

From
John McKown
Date:
On Thu, Feb 12, 2015 at 10:48 AM, pinker <pinker@onet.eu> wrote:
I wanted to set a rule:
CREATE RULE "_RETURN" AS   ON SELECT * from backend.test   DO INSTEAD       SELECT * FROM backend.test WHERE who='Me';
When I'm trying to do anything on the table I get following error:
ERROR:  infinite recursion detected in rules for relation "backend.test"

********** Błąd **********

ERROR: infinite recursion detected in rules for relation "backend.test"
Stan SQL: 42P17
Is there any way to avoid that? Maybe there exist some other approaches that could be useful ?

​I'm not totally sure why you want to do the above. If I needed such a thing and "backend.test" already exists, I would rename "backend.test" to something like "backend.test__x" and then create a view like:

CREATE VIEW backend.test AS
SELECT * FROM bachend.test__x WHERE who='Me'
WITH CHECK OPTION.

The WITH CHECK OPTION will stop people from doing a INSERT or UPDATE which did not have "who" equal to 'Me'. I don't know if you would need this.

I would then GRANT appropriate authority to the VIEW and remove it from "backend.test__x"​.

 

--
He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

Re: infinite recursion detected in rules for relation

From
David G Johnston
Date:
pinker wrote
> I wanted to set a rule:
> <pre>
> CREATE RULE "_RETURN" AS
>     ON SELECT * from backend.test
>     DO INSTEAD
>         SELECT * FROM backend.test WHERE who='Me';
> </pre>
> When I'm trying to do anything on the table I get following error:
> <pre>
> ERROR:  infinite recursion detected in rules for relation "backend.test"

Not surprising...


> Is there any way to avoid that? Maybe there exist some other approaches
> that could be useful ?

CREATE VIEW test_me AS
SELECT * FROM backend.test WHERE who = 'Me'
;

David J.




--
View this message in context:
http://postgresql.nabble.com/infinite-recursion-detected-in-rules-for-relation-tp5837697p5837700.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


Re: infinite recursion detected in rules for relation

From
pinker
Date:
hmm I wanted to show only rows that was inserted today, so instead of
who='me' wanted to filter for instance where timestamp_column=CURRENT_DATE.

Yes, a view would be a solution but I thouhgt that's the case rules were
made for? Isn't it?



--
View this message in context:
http://postgresql.nabble.com/infinite-recursion-detected-in-rules-for-relation-tp5837697p5837822.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


Re: infinite recursion detected in rules for relation

From
David G Johnston
Date:
User created rules are almost never the correct solution.  There are too many cavets and views can accomplish nearly everything that a user might want.

David J.

On Friday, February 13, 2015, pinker [via PostgreSQL] <[hidden email]> wrote:
hmm I wanted to show only rows that was inserted today, so instead of who='me' wanted to filter for instance where timestamp_column=CURRENT_DATE.

Yes, a view would be a solution but I thouhgt that's the case rules were made for? Isn't it?


If you reply to this email, your message will be added to the discussion below:
http://postgresql.nabble.com/infinite-recursion-detected-in-rules-for-relation-tp5837697p5837822.html
To unsubscribe from infinite recursion detected in rules for relation, click here.
NAML


View this message in context: Re: infinite recursion detected in rules for relation
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: infinite recursion detected in rules for relation

From
pinker
Date:
Ok, but in this particular case I don't see any caveats and think that could
be classic case for rule to be used.
If it is "almost never the correct solution" why rules still exists at all?



--
View this message in context:
http://postgresql.nabble.com/infinite-recursion-detected-in-rules-for-relation-tp5837697p5837867.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


Re: infinite recursion detected in rules for relation

From
David G Johnston
Date:
On Fri, Feb 13, 2015 at 8:24 AM, pinker [via PostgreSQL] <[hidden email]> wrote:
Ok, but in this particular case I don't see any caveats

​You mean other than the infinite recursion, right?​

and think that could be classic case for rule to be used.
If it is "almost never the correct solution" why rules still exists at all? 

​Backward compatibility, the "almost", and ​the fact that views use rules as an implementation mechanism.

David J.




View this message in context: Re: infinite recursion detected in rules for relation
Sent from the PostgreSQL - general mailing list archive at Nabble.com.