Thread: A select with aggretion is failing, still subtle problems with ag gregation
A select with aggretion is failing, still subtle problems with ag gregation
From
Michael J Davis
Date:
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 | +----------------------------------+----------------------------------+----- --+
Re: [HACKERS] A select with aggretion is failing, still subtle problems with aggregation
From
Tom Lane
Date:
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
From
Bruce Momjian
Date:
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