Thread: infinite recursion detected in rules for relation
I wanted to set a rule:
View this message in context: infinite recursion detected in rules for relation
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
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: 42P17Is 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.
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: 42P17Is 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
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.
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.
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.
View this message in context: Re: infinite recursion detected in rules for relation
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
David J.
On Friday, February 13, 2015, pinker [via PostgreSQL] <[hidden email]> wrote:
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
View this message in context: Re: infinite recursion detected in rules for relation
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.
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.