Fwd: REWRITE_INVOKE_MAX and "query may contain cycles" - Mailing list pgsql-general

(I have the worst problems posting to this list.  If this comes across
twice, I'm very sorry)

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