Thread: Grouping Sets
Hi,<br /><br />I tried to apply the Grouping Sets Patch to 8.4, but received several Hunks failed messages, does anyone knowif the failing hunks can be applied manually? Or what version they were applied to specifically? <br clear="all" /><br/>-- <br />Regards <br /><br />David<br />
On Sun, Sep 18, 2011 at 02:08:01PM -0500, David Rinaldi wrote: > I tried to apply the Grouping Sets Patch to 8.4, but received several Hunks > failed messages, does anyone know if the failing hunks can be applied > manually? Or what version they were applied to specifically? Your best bet is probably to get the code from approximately the date of the patch. As far as I know it hasn't been touched in a while, and didn't work well back when it was being actively developed. -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com
Paul, I was able to apply the patch to 9.0.4 and so far looks good. My Oracle results match. Nice. But, when trying to calculate some percentages and control some rounding, the results are coming back as null for some reason. I have tried casting, to_char, etc to try to get them to show up..no love ensued. I was wondering if you have any idea what could by happening. I have attached some test results based on the grouping sets wiki. One of the examples is just using group by, as a sanity check. Any ideas or help would be much appreciated. CREATE TABLE cars2 (name CHARACTER VARYING , place CHARACTER VARYING , sales real, cost real ); insert into cars2 values('skoda', 'czech rep.', 10000, 8000); insert into cars2 values('skoda', 'germany', 5000, 6000); insert into cars2 values('bmw', 'czech rep.', 6000, 4000); insert into cars2 values('bmw', 'germany', 18000, 15000); insert into cars2 values('opel', 'czech rep.', 7000, 5000); insert into cars2 values('opel', 'germany', 7000, 5000); --grouping sets test-- select name, place, sum(sales) as sales, sum(cost) as cost, sum(cost) / sum(sales) as cost_sales_ratio, (sum(cost) / sum(sales)) * 100 as cost_sales_ratio_per, round(((sum(cost) / sum(sales)) * 100),0) as cost_sales_ratio_per_rnd from cars2 group by rollup(name, place); name place sales cost cost_sales_ratio cost_sales_ratio_per cost_sales_ratio_per_rnd bmw czech rep. 6000 4000 0.6667 (null) (null) skoda germany 5000 6000 1.2 (null) (null) opel czech rep. 7000 5000 0.7143 (null) (null) opel germany 7000 5000 0.7143 (null) (null) skoda czech rep. 10000 8000 0.8 (null) (null) bmw germany 18000 15000 0.8333 (null) (null) bmw (null) 24000 19000 0.7917 (null) (null) skoda (null) 15000 14000 0.9333 (null) (null) opel (null) 14000 10000 0.7143 (null) (null) (null) (null) 53000 43000 0.8113 (null) (null) --group by sanity test-- select name, place, sum(sales) as sales, sum(cost) as cost, sum(cost) / sum(sales) as cost_sales_ratio, (sum(cost) / sum(sales)) * 100 as cost_sales_ratio_per, round(((sum(cost) / sum(sales)) * 100),0) as cost_sales_ratio_per_rnd from cars2 group by name, place; name place sales cost cost_sales_ratio cost_sales_ratio_per cost_sales_ratio_per_rnd bmw czech rep. 6000 4000 0.6667 66.6667 67 skoda germany 5000 6000 1.2 120 120 opel czech rep. 7000 5000 0.7143 71.4286 71 opel germany 7000 5000 0.7143 71.4286 71 skoda czech rep. 10000 8000 0.8 80 80 bmw germany 18000 15000 0.8333 83.3333 83 Thanks -- Regards David -----Original Message----- From: Pavel Stehule [mailto:pavel.stehule@gmail.com] Sent: Sunday, September 18, 2011 2:34 PM To: David Rinaldi Subject: Re: [HACKERS] Grouping Sets Hello A last patch should be applied on 8.4 or 9.0 - should to try it. I worked with developer version. http://archives.postgresql.org/pgsql-hackers/2010-08/msg00647.php Regards Pavel Stehule 2011/9/18 David Rinaldi <edwbroker@gmail.com>: > Hi, > > I tried to apply the Grouping Sets Patch to 8.4, but received several Hunks > failed messages, does anyone know if the failing hunks can be applied > manually? Or what version they were applied to specifically? > > -- > Regards > > David >
Hello 2011/9/20 David Rinaldi <edwbroker@gmail.com>: > Paul, > > I was able to apply the patch to 9.0.4 and so far looks good. My Oracle > results match. Nice. > > But, when trying to calculate some percentages and control some rounding, > the results are coming back as null for some reason. I have tried casting, > to_char, etc to try to get them to show up..no love ensued. I was wondering > if you have any idea what could by happening. I have attached some test > results based on the grouping sets wiki. One of the examples is just using > group by, as a sanity check. Any ideas or help would be much appreciated. > sorry, I have not any useful idea. This work was a concept and it is probable, so there will be some corner issues :(. This feature needs more love and some more significant changes in planner and executor. Regards Pavel > > CREATE TABLE cars2 (name CHARACTER VARYING , place CHARACTER VARYING , > sales real, cost real ); > > insert into cars2 values('skoda', 'czech rep.', 10000, 8000); > insert into cars2 values('skoda', 'germany', 5000, 6000); > insert into cars2 values('bmw', 'czech rep.', 6000, 4000); > insert into cars2 values('bmw', 'germany', 18000, 15000); > insert into cars2 values('opel', 'czech rep.', 7000, 5000); > insert into cars2 values('opel', 'germany', 7000, 5000); > > --grouping sets test-- > > select name, place, > sum(sales) as sales, > sum(cost) as cost, > sum(cost) / sum(sales) as cost_sales_ratio, > (sum(cost) / sum(sales)) * 100 as cost_sales_ratio_per, > round(((sum(cost) / sum(sales)) * 100),0) as cost_sales_ratio_per_rnd > from cars2 group by rollup(name, place); > > > name place sales cost cost_sales_ratio cost_sales_ratio_per > cost_sales_ratio_per_rnd > bmw czech rep. 6000 4000 0.6667 (null) (null) > skoda germany 5000 6000 1.2 (null) (null) > opel czech rep. 7000 5000 0.7143 (null) (null) > opel germany 7000 5000 0.7143 (null) (null) > skoda czech rep. 10000 8000 0.8 (null) (null) > bmw germany 18000 15000 0.8333 (null) (null) > bmw (null) 24000 19000 0.7917 (null) (null) > skoda (null) 15000 14000 0.9333 (null) (null) > opel (null) 14000 10000 0.7143 (null) (null) > (null) (null) 53000 43000 0.8113 (null) (null) > > --group by sanity test-- > > select name, place, > sum(sales) as sales, > sum(cost) as cost, > sum(cost) / sum(sales) as cost_sales_ratio, > (sum(cost) / sum(sales)) * 100 as cost_sales_ratio_per, > round(((sum(cost) / sum(sales)) * 100),0) as cost_sales_ratio_per_rnd > from cars2 group by name, place; > > name place sales cost cost_sales_ratio cost_sales_ratio_per > cost_sales_ratio_per_rnd > bmw czech rep. 6000 4000 0.6667 66.6667 67 > skoda germany 5000 6000 1.2 120 120 > opel czech rep. 7000 5000 0.7143 71.4286 71 > opel germany 7000 5000 0.7143 71.4286 71 > skoda czech rep. 10000 8000 0.8 80 80 > bmw germany 18000 15000 0.8333 83.3333 83 > > > > Thanks > > > > > -- > Regards > > David > > > -----Original Message----- > From: Pavel Stehule [mailto:pavel.stehule@gmail.com] > Sent: Sunday, September 18, 2011 2:34 PM > To: David Rinaldi > Subject: Re: [HACKERS] Grouping Sets > > Hello > > A last patch should be applied on 8.4 or 9.0 - should to try it. I > worked with developer version. > > http://archives.postgresql.org/pgsql-hackers/2010-08/msg00647.php > > Regards > > Pavel Stehule > > 2011/9/18 David Rinaldi <edwbroker@gmail.com>: >> Hi, >> >> I tried to apply the Grouping Sets Patch to 8.4, but received several > Hunks >> failed messages, does anyone know if the failing hunks can be applied >> manually? Or what version they were applied to specifically? >> >> -- >> Regards >> >> David >> > >
Since it seems that you have spent some considerable time investigating and producing a working concept, what would your best guess time estimate be, assuming the requisite skills/talent/will in (planner/executor/etc.), to have a solid working module put together? Are we looking at something like 40 hours or more like 5000 hours, in your estimate? Thanks. -- Regards David -----Original Message----- From: Pavel Stehule [mailto:pavel.stehule@gmail.com] Sent: Monday, September 19, 2011 10:45 PM To: edwbroker@gmail.com Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Grouping Sets Hello 2011/9/20 David Rinaldi <edwbroker@gmail.com>: > Paul, > > I was able to apply the patch to 9.0.4 and so far looks good. My Oracle > results match. Nice. > > But, when trying to calculate some percentages and control some rounding, > the results are coming back as null for some reason. I have tried casting, > to_char, etc to try to get them to show up..no love ensued. I was wondering > if you have any idea what could by happening. I have attached some test > results based on the grouping sets wiki. One of the examples is just using > group by, as a sanity check. Any ideas or help would be much appreciated. > sorry, I have not any useful idea. This work was a concept and it is probable, so there will be some corner issues :(. This feature needs more love and some more significant changes in planner and executor. Regards Pavel > > CREATE TABLE cars2 (name CHARACTER VARYING , place CHARACTER VARYING , > sales real, cost real ); > > insert into cars2 values('skoda', 'czech rep.', 10000, 8000); > insert into cars2 values('skoda', 'germany', 5000, 6000); > insert into cars2 values('bmw', 'czech rep.', 6000, 4000); > insert into cars2 values('bmw', 'germany', 18000, 15000); > insert into cars2 values('opel', 'czech rep.', 7000, 5000); > insert into cars2 values('opel', 'germany', 7000, 5000); > > --grouping sets test-- > > select name, place, > sum(sales) as sales, > sum(cost) as cost, > sum(cost) / sum(sales) as cost_sales_ratio, > (sum(cost) / sum(sales)) * 100 as cost_sales_ratio_per, > round(((sum(cost) / sum(sales)) * 100),0) as cost_sales_ratio_per_rnd > from cars2 group by rollup(name, place); > > > name place sales cost cost_sales_ratio cost_sales_ratio_per > cost_sales_ratio_per_rnd > bmw czech rep. 6000 4000 0.6667 (null) (null) > skoda germany 5000 6000 1.2 (null) (null) > opel czech rep. 7000 5000 0.7143 (null) (null) > opel germany 7000 5000 0.7143 (null) (null) > skoda czech rep. 10000 8000 0.8 (null) (null) > bmw germany 18000 15000 0.8333 (null) (null) > bmw (null) 24000 19000 0.7917 (null) (null) > skoda (null) 15000 14000 0.9333 (null) (null) > opel (null) 14000 10000 0.7143 (null) (null) > (null) (null) 53000 43000 0.8113 (null) (null) > > --group by sanity test-- > > select name, place, > sum(sales) as sales, > sum(cost) as cost, > sum(cost) / sum(sales) as cost_sales_ratio, > (sum(cost) / sum(sales)) * 100 as cost_sales_ratio_per, > round(((sum(cost) / sum(sales)) * 100),0) as cost_sales_ratio_per_rnd > from cars2 group by name, place; > > name place sales cost cost_sales_ratio cost_sales_ratio_per > cost_sales_ratio_per_rnd > bmw czech rep. 6000 4000 0.6667 66.6667 67 > skoda germany 5000 6000 1.2 120 120 > opel czech rep. 7000 5000 0.7143 71.4286 71 > opel germany 7000 5000 0.7143 71.4286 71 > skoda czech rep. 10000 8000 0.8 80 80 > bmw germany 18000 15000 0.8333 83.3333 83 > > > > Thanks > > > > > -- > Regards > > David > > > -----Original Message----- > From: Pavel Stehule [mailto:pavel.stehule@gmail.com] > Sent: Sunday, September 18, 2011 2:34 PM > To: David Rinaldi > Subject: Re: [HACKERS] Grouping Sets > > Hello > > A last patch should be applied on 8.4 or 9.0 - should to try it. I > worked with developer version. > > http://archives.postgresql.org/pgsql-hackers/2010-08/msg00647.php > > Regards > > Pavel Stehule > > 2011/9/18 David Rinaldi <edwbroker@gmail.com>: >> Hi, >> >> I tried to apply the Grouping Sets Patch to 8.4, but received several > Hunks >> failed messages, does anyone know if the failing hunks can be applied >> manually? Or what version they were applied to specifically? >> >> -- >> Regards >> >> David >> > >
Hello 2011/9/20 David Rinaldi <edwbroker@gmail.com>: > Since it seems that you have spent some considerable time investigating and > producing a working concept, what would your best guess time estimate be, > assuming the requisite skills/talent/will in (planner/executor/etc.), to > have a solid working module put together? Are we looking at something like > 40 hours or more like 5000 hours, in your estimate? > it depends on your knowledge of pg internals and your motivation :). I thing so it can be less than 40 hours for elimination of these issues and next 40 hours for some finalisation. If I remember well, I had a prototype after one week of hacking, and I am not a strong programmer. Regards Pavel Stehule > Thanks. > > -- > Regards > > David > > -----Original Message----- > From: Pavel Stehule [mailto:pavel.stehule@gmail.com] > Sent: Monday, September 19, 2011 10:45 PM > To: edwbroker@gmail.com > Cc: pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] Grouping Sets > > Hello > > 2011/9/20 David Rinaldi <edwbroker@gmail.com>: >> Paul, >> >> I was able to apply the patch to 9.0.4 and so far looks good. My Oracle >> results match. Nice. >> >> But, when trying to calculate some percentages and control some rounding, >> the results are coming back as null for some reason. I have tried > casting, >> to_char, etc to try to get them to show up..no love ensued. I was > wondering >> if you have any idea what could by happening. I have attached some test >> results based on the grouping sets wiki. One of the examples is just using >> group by, as a sanity check. Any ideas or help would be much appreciated. >> > > sorry, I have not any useful idea. This work was a concept and it is > probable, so there will be some corner issues :(. > > This feature needs more love and some more significant changes in > planner and executor. > > Regards > > Pavel > > >> >> CREATE TABLE cars2 (name CHARACTER VARYING , place CHARACTER VARYING , >> sales real, cost real ); >> >> insert into cars2 values('skoda', 'czech rep.', 10000, 8000); >> insert into cars2 values('skoda', 'germany', 5000, 6000); >> insert into cars2 values('bmw', 'czech rep.', 6000, 4000); >> insert into cars2 values('bmw', 'germany', 18000, 15000); >> insert into cars2 values('opel', 'czech rep.', 7000, 5000); >> insert into cars2 values('opel', 'germany', 7000, 5000); >> >> --grouping sets test-- >> >> select name, place, >> sum(sales) as sales, >> sum(cost) as cost, >> sum(cost) / sum(sales) as cost_sales_ratio, >> (sum(cost) / sum(sales)) * 100 as cost_sales_ratio_per, >> round(((sum(cost) / sum(sales)) * 100),0) as cost_sales_ratio_per_rnd >> from cars2 group by rollup(name, place); >> >> >> name place sales cost cost_sales_ratio > cost_sales_ratio_per >> cost_sales_ratio_per_rnd >> bmw czech rep. 6000 4000 0.6667 (null) (null) >> skoda germany 5000 6000 1.2 (null) (null) >> opel czech rep. 7000 5000 0.7143 (null) (null) >> opel germany 7000 5000 0.7143 (null) (null) >> skoda czech rep. 10000 8000 0.8 (null) (null) >> bmw germany 18000 15000 0.8333 (null) (null) >> bmw (null) 24000 19000 0.7917 (null) (null) >> skoda (null) 15000 14000 0.9333 (null) (null) >> opel (null) 14000 10000 0.7143 (null) (null) >> (null) (null) 53000 43000 0.8113 (null) (null) >> >> --group by sanity test-- >> >> select name, place, >> sum(sales) as sales, >> sum(cost) as cost, >> sum(cost) / sum(sales) as cost_sales_ratio, >> (sum(cost) / sum(sales)) * 100 as cost_sales_ratio_per, >> round(((sum(cost) / sum(sales)) * 100),0) as cost_sales_ratio_per_rnd >> from cars2 group by name, place; >> >> name place sales cost cost_sales_ratio > cost_sales_ratio_per >> cost_sales_ratio_per_rnd >> bmw czech rep. 6000 4000 0.6667 66.6667 67 >> skoda germany 5000 6000 1.2 120 120 >> opel czech rep. 7000 5000 0.7143 71.4286 71 >> opel germany 7000 5000 0.7143 71.4286 71 >> skoda czech rep. 10000 8000 0.8 80 80 >> bmw germany 18000 15000 0.8333 83.3333 83 >> >> >> >> Thanks >> >> >> >> >> -- >> Regards >> >> David >> >> >> -----Original Message----- >> From: Pavel Stehule [mailto:pavel.stehule@gmail.com] >> Sent: Sunday, September 18, 2011 2:34 PM >> To: David Rinaldi >> Subject: Re: [HACKERS] Grouping Sets >> >> Hello >> >> A last patch should be applied on 8.4 or 9.0 - should to try it. I >> worked with developer version. >> >> http://archives.postgresql.org/pgsql-hackers/2010-08/msg00647.php >> >> Regards >> >> Pavel Stehule >> >> 2011/9/18 David Rinaldi <edwbroker@gmail.com>: >>> Hi, >>> >>> I tried to apply the Grouping Sets Patch to 8.4, but received several >> Hunks >>> failed messages, does anyone know if the failing hunks can be applied >>> manually? Or what version they were applied to specifically? >>> >>> -- >>> Regards >>> >>> David >>> >> >> > >