infinite recursion detected in rules for relation

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

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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] <
ml-node+s1045698n5837822h6@n5.nabble.com> 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
<http://postgresql.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&amp;node=5837697&amp;code=ZGF2aWQuZy5qb2huc3RvbkBnbWFpbC5jb218NTgzNzY5N3wtMzI2NTA0MzIx&gt;
.
NAML
<http://postgresql.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&amp;id=instant_html%21nabble%3Aemail.naml&amp;base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&amp;breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml&gt;

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

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, Feb 13, 2015 at 8:24 AM, pinker [via PostgreSQL] <
ml-node+s1045698n5837867h1@n5.nabble.com> 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: http://postgresql.nabble.com/infinite-recursion-detected-in-rules-for-relation-tp5837697p5837871.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.