Thread: A bug or a feature?

We have a table with ~30 columns, named 'people' and we were going to
create a view for all record with 'relationship' equal to 1. The
database complains where using the '*' placeholder:

albourne=> CREATE VIEW employees AS SELECT * FROM people WHERE
relationship = 1; 
ERROR:  DefineQueryRewrite: rule plan string too big.

but accepts the same 30 columns on the command:

albourne=> create view employees as select

id,title,first_name,middle_name,last_name,suffix,company,job_title,address,city,zipcode,country,home_phone,home_fax,mobile,bus_phone,bus_fax,other_phone,e_mail_1,e_mail_2,url,birthday,christmas,brochure,golf,croquet,comment
from people where relationship=1;
CREATE

'*' is SQL92 (I think) so is this a bug or a known limitation?

The system is PostgreSQL 6.5.2 on alphaev6-dec-osf4.0f, compiled by cc.

Thanks
Alessio

-- 
Alessio F. Bragadini        alessio@albourne.com
APL Financial Services        http://www.sevenseas.org/~alessio
Nicosia, Cyprus             phone: +357-2-750652

You are welcome, sir, to Cyprus. -- Shakespeare's "Othello"

Alessio F. Bragadini wrote:

> We have a table with ~30 columns, named 'people' and we were going to
> create a view for all record with 'relationship' equal to 1. The
> database complains where using the '*' placeholder:
>
> albourne=> CREATE VIEW employees AS SELECT * FROM people WHERE
> relationship = 1;
> ERROR:  DefineQueryRewrite: rule plan string too big.
>
> but accepts the same 30 columns on the command:
>
> [...]
>
> '*' is SQL92 (I think) so is this a bug or a known limitation?
>
> The system is PostgreSQL 6.5.2 on alphaev6-dec-osf4.0f, compiled by cc.

    It's a well known limitation in versions up to 6.5.*.

    I've  lowered  the problem in the 7.0 tree by compressing the
    rule plan string, using a new data type.  A 'SELECT  *'  view
    from  a  table  with  54  fields  uses  only about 25% of the
    available space then.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #

Alessio Bragadini <alessio@albourne.com> writes:
> We have a table with ~30 columns, named 'people' and we were going to
> create a view for all record with 'relationship' equal to 1. The
> database complains where using the '*' placeholder:
> albourne=> CREATE VIEW employees AS SELECT * FROM people WHERE
> relationship = 1; 
> ERROR:  DefineQueryRewrite: rule plan string too big.
> but accepts the same 30 columns on the command:

There is a limit on the length of rule plans :-(.  Jan has implemented
compression of rule plan strings as a partial workaround for 7.0, and
the final solution will come when we eliminate tuple length limits.

In the meantime, the interesting question is why two apparently
equivalent queries yield rule plans of different lengths.  As far as
I can tell, '*' and explicitly listing the fields *do* yield exactly
the same results.  My guess is that your query is right at the hairy
edge of the length limit, such that one or two characters more or less
make the difference.  The rule plan does include a couple of instances
of the name of the view, so if you used a longer view name in one case
than the other, that could explain why one worked and the other didn't.

If that's not it then I'm baffled...
        regards, tom lane