Обсуждение: A select with aggretion is failing, still subtle problems with ag gregation
A select with aggretion is failing, still subtle problems with ag gregation
От
Michael J Davis
Дата:
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
Re: [HACKERS] A select with aggretion is failing, still subtle problems with aggregation
От
Bruce Momjian
Дата:
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