Thread: Fwd: REWRITE_INVOKE_MAX and "query may contain cycles"

I've run into a situation where I need a RULE to execute more than 10
(guaranteed non-cyclical) operations.

Outside of modifying the source every time I compile/install postgres
(which is what I've done now), how can I redefine REWRITE_INVOKE_MAX?

Also, is there any possibility of making REWRITE_INVOKE_MAX a
configurable number in some future version of postgres?

FWIW, I'm (still) using 7.2.3 on unix.

thanks!

eric

On Thu, Feb 20, 2003 at 12:43:21 -0500,
  "Eric B. Ridge" <ebr@tcdi.com> wrote:
> I've run into a situation where I need a RULE to execute more than 10
> (guaranteed non-cyclical) operations.
>
> Outside of modifying the source every time I compile/install postgres
> (which is what I've done now), how can I redefine REWRITE_INVOKE_MAX?
>
> Also, is there any possibility of making REWRITE_INVOKE_MAX a
> configurable number in some future version of postgres?

I think in 7.4 it is getting raised to 100. There was a message about it
(from Tom Lane I think) within the last couple of weeks.

On Thursday, February 20, 2003, at 02:23  PM, Bruno Wolff III wrote:
>
> I think in 7.4 it is getting raised to 100. There was a message about
> it
> (from Tom Lane I think) within the last couple of weeks.

I've been looking through the archives for this (and similar) messages
and am coming up empty handed.  I'd love to know what the plans are for
this little issue.

eric

Bruno Wolff III <bruno@wolff.to> writes:
>   "Eric B. Ridge" <ebr@tcdi.com> wrote:
>> Outside of modifying the source every time I compile/install postgres
>> (which is what I've done now), how can I redefine REWRITE_INVOKE_MAX?

> I think in 7.4 it is getting raised to 100.

It is already 100 in 7.3.

            regards, tom lane

On Thursday, February 20, 2003, at 05:58  PM, Tom Lane wrote:

> Bruno Wolff III <bruno@wolff.to> writes:
>>   "Eric B. Ridge" <ebr@tcdi.com> wrote:
>>> Outside of modifying the source every time I compile/install postgres
>>> (which is what I've done now), how can I redefine REWRITE_INVOKE_MAX?
>
>> I think in 7.4 it is getting raised to 100.
>
> It is already 100 in 7.3.

That's better.  I've set mine (in 7.2.3) to 1024.  I'm at 50ish
operations right now and that number has the potential to grow, perhaps
even past 100.  That's why I was asking if it could become a
configuration setting.

Is making it configurable technically possible (ie, can rewriteHandler
access GUC parameters?), and if I invested the time to make it happen,
would a patch be accepted?  I really don't want to continue to modify
the source every time I install postgres.

eric

"Eric B. Ridge" <ebr@tcdi.com> writes:
> That's better.  I've set mine (in 7.2.3) to 1024.  I'm at 50ish
> operations right now and that number has the potential to grow, perhaps
> even past 100.  That's why I was asking if it could become a
> configuration setting.

Hm, I had figured "100 is plenty".  But maybe not.

> Is making it configurable technically possible (ie, can rewriteHandler
> access GUC parameters?), and if I invested the time to make it happen,
> would a patch be accepted?

Yes, and yes IMHO.  Even better would be to detect loops directly and
eliminate this kluge entirely, but I'm not sure if you want to get into
that ... whereas a GUC parameter is pretty trivial.  Don't forget the
documentation updates (which you will find is a bigger patch than the
actual code change).

            regards, tom lane

On Thursday, February 20, 2003, at 07:11  PM, Tom Lane wrote:

> "Eric B. Ridge" <ebr@tcdi.com> writes:
>> That's better.  I've set mine (in 7.2.3) to 1024.  I'm at 50ish
>> operations right now and that number has the potential to grow,
>> perhaps
>> even past 100.  That's why I was asking if it could become a
>> configuration setting.
>
> Hm, I had figured "100 is plenty".  But maybe not.

We're using views and rules to emulate table inheritance.  We've found
that when you get about 50 tables that all inherit from a single (or
more) base table, SELECT performance really starts to suck ass... even
with zero records in the database.

So with views and rules (combined with our code/sql generator that has
intimate knowledge of the database schema) we can pretty easily (and
transparently) copy field values around to the other tables in the
inheritance tree.  Duplicating data up and down, but performance has
skyrocketed.

Also, this reminds me of something else....

create table foo (
      id int8 default nextval('seq_foo_id'),
      title text,
      type default 'foo'
);

INSERT INTO foo (id, title, type) VALUES (<default>, 'whatever',
<default>);

Is there no keyword for getting the DEFAULT value of a column when
doing an INSERT?  I know, just don't specify the columns, but with
RULEs (and how we're using 'em), we could really use something like
this.  How hard would this be to implement?  And where would one do it?

Alternatively, it would be really sweet if ON INSERT RULEs would
provide the DEFAULT values in NEW for those fields that weren't
specified in the triggering INSERT statement.  In other words:

CREATE VIEW foo_view AS SELECT * FROM foo;
CREATE RULE foo_rule ON INSERT TO foo_view DO INSTEAD (
    INSERT INTO some_other_table (id, title, type) values (NEW.id,
NEW.title, NEW.type)
);
INSERT INTO foo_view (title) values ('whatever');
SELECT * FROM foo_view

id  |  title          | type
----------------------
      | whatever |

id and type end up as null, and if id is defined as NOT NULL PRIMARY
KEY the whole thing fails.  So...

...had to write the RULE like this:
CREATE RULE foo_rule ON INSERT TO foo_view DO INSTEAD (
    INSERT INTO some_other_table (id, title, type)
          values  (CASE WHEN NEW.id IS NULL THEN nextval('seq_foo_id')
ELSE NEW.id END,
                         NEW.title,
                         CASE WHEN NEW.type IS NULL THEN 'foo' ELSE
NEW.type END)
);

Hardcoding the default values like this really stinks because now
there's no way to actually insert a NULL into a column with a default
value.

I even played around with getting the DEFAULT 'clause' from pg_attrdef
but soon realized that I couldn't actually expand the ::text form of
the value into the real, typed value.

Our little schema.sql file, including minimal comments and whitespace,
is almost 700k, mostly due to the CASE statements illustrated above.
Thank goodness it's auto-generated.

>> Is making it configurable technically possible (ie, can rewriteHandler
>> access GUC parameters?), and if I invested the time to make it happen,
>> would a patch be accepted?
>
> Yes, and yes IMHO.  Even better would be to detect loops directly and
> eliminate this kluge entirely, but I'm not sure if you want to get into
> that ... whereas a GUC parameter is pretty trivial.  Don't forget the
> documentation updates (which you will find is a bigger patch than the
> actual code change).

Sign me up for this.  It's gunna take me a bit to figure things out,
but I'm committed to doing it.  Oh, and thanks for reminding me 'bout
the documentation....

eric

"Eric B. Ridge" <ebr@tcdi.com> writes:
> INSERT INTO foo (id, title, type) VALUES (<default>, 'whatever',
> <default>);

> Is there no keyword for getting the DEFAULT value of a column when
> doing an INSERT?

Not sure how far back this goes, but in 7.3 you can say DEFAULT:

  INSERT INTO foo (id, title, type) VALUES (default, 'whatever',
  default);

which is per SQL spec.

> Alternatively, it would be really sweet if ON INSERT RULEs would
> provide the DEFAULT values in NEW for those fields that weren't
> specified in the triggering INSERT statement.

Again, I don't recall when we implemented this, but you can attach
column default values to views in 7.3, and maybe before.  In your
example:

> CREATE VIEW foo_view AS SELECT * FROM foo;
> CREATE RULE foo_rule ON INSERT TO foo_view DO INSTEAD (
>     INSERT INTO some_other_table (id, title, type) values (NEW.id,
> NEW.title, NEW.type)
> );
> INSERT INTO foo_view (title) values ('whatever');

the unspecified columns will be driven by any column defaults attached
to foo_view (not foo).  You say, eg,

  alter table foo_view alter column id set default nextval('seq_foo_id');

and away you go.

            regards, tom lane

On Thursday, February 20, 2003, at 10:20  PM, Tom Lane wrote:
> "Eric B. Ridge" <ebr@tcdi.com> writes:
>> INSERT INTO foo (id, title, type) VALUES (<default>, 'whatever',
>> <default>);
>
>> Is there no keyword for getting the DEFAULT value of a column when
>> doing an INSERT?
>
> Not sure how far back this goes, but in 7.3 you can say DEFAULT:
<snip>
>   alter table foo_view alter column id set default
> nextval('seq_foo_id');
>
> and away you go.

Thanks for this information.  Y'all rock.  These two things are enough
to convince me to upgrade to 7.3 immediately.

eric

On Friday, February 21, 2003, at 11:11  AM, Eric B.Ridge wrote:
> Thanks for this information.  Y'all rock.  These two things are enough
> to convince me to upgrade to 7.3 immediately.

So after upgrading from 7.2.3 to 7.3.2, I've got some interesting
problems.

The backend simply saysr:
    FATAL:  SPI: improper call to spi_dest_setup

And psql says basically the same thing:
    FATAL:  SPI: improper call to spi_dest_setup
    server closed the connection unexpectedly
            This probably means the server terminated abnormally
            before or while processing the request.
    The connection to the server was lost. Attempting reset: Succeeded.

And the jdbc drivers just go nuts.  But since I can duplicate this in
psql, I'm sure it's not a JDBC specific issue.


Rather than try to explain what is happening, please checkout the
attached testcase.   However, I believe I've narrowed it down to (at
least) an after-insert trigger (on a table w/ a view/rule facade) that
then tries to do an insert into a different (and mostly unrelated) view.

The expected results (as are produced by 7.2.3 are attached) as well.

If more information is necessary, please let me know.

thanks in advance!

eric

"Eric B.Ridge" <ebr@tcdi.com> writes:
> So after upgrading from 7.2.3 to 7.3.2, I've got some interesting
> problems.
> The backend simply saysr:
>     FATAL:  SPI: improper call to spi_dest_setup

Yeah, 7.3.2 has some problems with rules that issue multiple commands of
the same type (like your INSERTs) :-(.  This was just noticed and fixed
about a week ago:

2003-02-14 16:12  tgl

    * src/backend/executor/: spi.c (REL7_3_STABLE), spi.c: Fix SPI
    result logic for case where there are multiple statements of the
    same type in a rule.  Per bug report from Pavel Hanak.

If you pull down the latest 7.3 branch version of spi.c from
http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/executor/spi.c

things should work again for you.

Sorry about that :-( ... this was an unexpected side effect of a bug fix
back in January ...

            regards, tom lane

On Friday, February 21, 2003, at 09:51  PM, Tom Lane wrote:

> If you pull down the latest 7.3 branch version of spi.c

Thanks again!  I'll give it a shot on Monday.

Is there a 7.3.3 release in the works?

eric

"Eric B. Ridge" <ebr@tcdi.com> writes:
> Is there a 7.3.3 release in the works?

We've got enough bugs accumulated that there clearly will need to be
a 7.3.3.  There's been no discussion yet about when to do it.  I'm
inclined to wait awhile longer, see what else gets reported ...

            regards, tom lane

On Saturday, February 22, 2003, at 01:13  PM, Eric B.Ridge wrote:

> On Friday, February 21, 2003, at 09:51  PM, Tom Lane wrote:
>
>> If you pull down the latest 7.3 branch version of spi.c
>
> Thanks again!  I'll give it a shot on Monday.

I just wanted to follow up and say that between this fix to spi.c,
rewrite_invoke_max limit of 100, and the ALTER TABLE <view name> ALTER
COLUMN foo SET DEFAULT feature, 7.3.2 is great.  Thanks!

eric

On Monday, February 24, 2003, at 11:55  AM, Eric B.Ridge wrote:

> On Saturday, February 22, 2003, at 01:13  PM, Eric B.Ridge wrote:
>
>> On Friday, February 21, 2003, at 09:51  PM, Tom Lane wrote:
>>
>>> If you pull down the latest 7.3 branch version of spi.c
>>
>> Thanks again!  I'll give it a shot on Monday.
>
> I just wanted to follow up and say that between this fix to spi.c,
> rewrite_invoke_max limit of 100, and the ALTER TABLE <view name> ALTER
> COLUMN foo SET DEFAULT feature, 7.3.2 is great.  Thanks!

I hate to bring this subject up again, but I'm confused about when
default (view) values (via NEW) are evaluated when used in a
multi-statement insert rule.

xxx=# create table foo (id int8);
CREATE TABLE
xxx=# create sequence seq_foo_id;
CREATE SEQUENCE
xxx=# create view foo_view as select * from foo;
CREATE VIEW
xxx=# alter table foo_view alter column id set default
nextval('seq_foo_id');
ALTER TABLE
xxx=# create rule foo_insert_rule as on insert to foo_view do instead (
xxx(# insert into foo (id) values (NEW.id);
xxx(# insert into foo (id) values (NEW.id);
xxx(# );
CREATE RULE
xxx=# insert into foo_view default values;
INSERT 273920 1
xxx=# select * from foo;
  id
----
   1
   2
(2 rows)

I would have expected "id" to be '1' in both rows.  In fact, for what
I'm trying to do, it's a requirement.

Can anyone explain why NEW.id is being re-evaluated for each statement
in the rule?  Is this by design?

eric

"Eric B. Ridge" <ebr@tcdi.com> writes:
> I would have expected "id" to be '1' in both rows.  In fact, for what
> I'm trying to do, it's a requirement.

Then use a trigger to capture the inserted row...

> Can anyone explain why NEW.id is being re-evaluated for each statement
> in the rule?  Is this by design?

Because the rule is a macro.  Yes, it's by design.

            regards, tom lane

On Thursday, March 13, 2003, at 12:51  PM, Tom Lane wrote:
> "Eric B. Ridge" <ebr@tcdi.com> writes:
>> I would have expected "id" to be '1' in both rows.  In fact, for what
>> I'm trying to do, it's a requirement.
>
> Then use a trigger to capture the inserted row...

I was afraid you'd say that.

>> Can anyone explain why NEW.id is being re-evaluated for each statement
>> in the rule?  Is this by design?
>
> Because the rule is a macro.  Yes, it's by design.

Makes sense,  but it's counter-intuitive.  Only 1 INSERT is being
issued against the view.

eric