Обсуждение: A select with aggretion is failing, still subtle problems with ag gregation

The following select fails:

>    select invoiceid + 3 as type, memberid, 1, max(TotShippingHandling) 
>          from InvoiceLineDetails 
>          where TotShippingHandling <> 0 
>          group by type, memberid limit 10;
ERROR:  replace_agg_clause: variable not in target list

The following select works (the + 3 has been eliminated):

>    select invoiceid as type, memberid, 1, max(TotShippingHandling) 
>          from InvoiceLineDetails 
>          where TotShippingHandling <> 0 
>          group by type, memberid limit 10;type|memberid|?column?|  max
-----+--------+--------+-----
15499|    1626|       1| 6.00
15524|     138|       1| 3.00
15647|    1083|       1|20.00
15653|    1230|       1| 4.00
15659|    1600|       1| 3.00
15671|    1276|       1| 3.50
15672|    1494|       1| 3.00
15673|    1653|       1| 4.50
15674|    1624|       1| 6.00
15675|    1406|       1| 7.00
(10 rows)

Here is a description of the view InvoiceLineDetails:

> \d InvoiceLineDetails
View    = invoicelinedetails
Query   = Not a view
+----------------------------------+----------------------------------+-----
--+
|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-----
--+
| invoicelinesid                   | int4                             |
4 |
| invoiceid                        | int4                             |
4 |
| dateprinted                      | datetime                         |
8 |
| ordersid                         | int4                             |
4 |
| ordertypeid                      | int4                             |
4 |
| totshippinghandling              | numeric                          |
var |
| shippeddate                      | datetime                         |
8 |
| memberid                         | int4                             |
4 |
| gift                             | numeric                          |
var |
| shippinghandling                 | numeric                          |
var |
| unitcost                         | numeric                          |
var |
| unitprice                        | numeric                          |
var |
| quantity                         | int4                             |
4 |
| invamount                        | numeric                          |
var |
| inventoryid                      | int4                             |
4 |
| inventoryname                    | varchar()                        |
0 |
| inventorytypeid                  | int4                             |
4 |
| inventorytypename                | varchar()                        |
32 |
| categoriesid                     | int4                             |
4 |
| tapenum                          | int4                             |
4 |
+----------------------------------+----------------------------------+-----
--+

Michael J Davis <michael.j.davis@tvguide.com> writes:
> The following select fails:
>> select invoiceid + 3 as type, memberid, 1, max(TotShippingHandling) 
>> from InvoiceLineDetails 
>> where TotShippingHandling <> 0 
>> group by type, memberid limit 10;
> ERROR:  replace_agg_clause: variable not in target list

Yeah, "GROUP BY" on anything but a primitive column is still pretty
hosed.  I'm going to try to work on it this weekend.
        regards, tom lane

Just one more question.  If you remove the cache file so the next
backend creates it, could their be problems if another backend starts
while the file is being created by another backend?

--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

Bruce Momjian <maillist@candle.pha.pa.us> writes:
> That sounds like a big win.  1/3 second is large.  If they vacuum a
> single table, and it is not a system table, can the removal be
> skipped?

I didn't do that; I just put an unconditional remove into vac_shutdown.
If you want to improve on that, be my guest ;-).

> Just one more question.  If you remove the cache file so the next
> backend creates it, could their be problems if another backend starts
> while the file is being created by another backend?

The code in relcache.c looks to be fairly robust --- if the file seems
to be broken (ie, ends early) it will go off and rebuild the file.
So I suppose you could get an extra rebuild in that scenario.

If you wanted to be really paranoid you could have the writing code
create the file under a temporary name (using the backend's PID) and
rename it into place when done; that'd prevent any kind of worry about
the wrong things happening if two backends write the file at the same
time.  But really, it shouldn't matter.
        regards, tom lane

Is this done?  I have added it to the list.



> Michael J Davis <michael.j.davis@tvguide.com> writes:
> > The following select fails:
> >> select invoiceid + 3 as type, memberid, 1, max(TotShippingHandling) 
> >> from InvoiceLineDetails 
> >> where TotShippingHandling <> 0 
> >> group by type, memberid limit 10;
> > ERROR:  replace_agg_clause: variable not in target list
> 
> Yeah, "GROUP BY" on anything but a primitive column is still pretty
> hosed.  I'm going to try to work on it this weekend.
> 
>             regards, tom lane
> 
> 


--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026