[SQL] 8.4.1 distinct query WITHOUT order by
All... One of the groups where I work uses a homegrown C/C++ application that was written back in 7.3 days which is now running on an 8.4.1 server (which, until very recently, was running on an 8.1 server). Unfortunately, they did not use an order by in the query and are now dealing with a return dataset that is unsorted and difficult to use. The current maintainer is unsure about being able to do the right thing and recompile the code after fixing the query. Aside from disabling enable_hashagg (which, according to the documentation, is performance-expensive), what other options do I have? What are the ramifications of renaming the table (containing 8000 rows) and creating a view of the same name? Assuming it's possible, would the efficiency of a rule to rewrite the query be an acceptable alternative? Thanks in advance for any insight and suggestions! -- Gary Chambers /* Nothing fancy and nothing Microsoft! */ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] 8.4.1 distinct query WITHOUT order by
Gary Chambers wrote on 21.12.2009 23:15: The current maintainer is unsure about being able to do the right thing and recompile the code after fixing the query. Why not simply add the necessary GROUP BY? Thomas -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] 8.4.1 distinct query WITHOUT order by
On Mon, Dec 21, 2009 at 3:38 PM, Thomas Kellerer wrote: > Gary Chambers wrote on 21.12.2009 23:15: >> >> The current maintainer is unsure about being able to do the right >> thing and recompile the code after fixing the query. > > Why not simply add the necessary GROUP BY? Yeah, if you're code base is that fragile, bandaging it up by jumping through hoops in pgsql is just putting off the inevitable when it (the code base) has to get recompiled someday anyway. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] 8.4.1 distinct query WITHOUT order by
> Yeah, if you're code base is that fragile, bandaging it up by jumping > through hoops in pgsql is just putting off the inevitable when it (the > code base) has to get recompiled someday anyway. I appreciate (and agree with) the concern about the fragility of the codebase. The maintainer knows that anything except adding ORDER BY is a kludge. Now, the aforementioned notwithstanding... Aside from disabling enable_hashagg (which, according to the documentation, is performance-expensive), what other options do I have? What are the ramifications of renaming the table (containing 8000 rows) and creating a view of the same name? Assuming it's possible, would the efficiency of a rule to rewrite the query be an acceptable alternative? Thanks in advance for any insight and suggestions! -- Gary Chambers /* Nothing fancy and nothing Microsoft! */ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] 8.4.1 distinct query WITHOUT order by
On Mon, Dec 21, 2009 at 8:45 PM, Gary Chambers wrote: >> Yeah, if you're code base is that fragile, bandaging it up by jumping >> through hoops in pgsql is just putting off the inevitable when it (the >> code base) has to get recompiled someday anyway. > > I appreciate (and agree with) the concern about the fragility of the > codebase. The maintainer knows that anything except adding ORDER BY > is a kludge. > > Now, the aforementioned notwithstanding... Replacing the table with a view should work. Just be sure to make insert rules if you need it to be insertable by other parts of the app I guess. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] 8.4.1 distinct query WITHOUT order by
On Mon, Dec 21, 2009 at 10:18 PM, Scott Marlowe wrote: > On Mon, Dec 21, 2009 at 8:45 PM, Gary Chambers wrote: >>> Yeah, if you're code base is that fragile, bandaging it up by jumping >>> through hoops in pgsql is just putting off the inevitable when it (the >>> code base) has to get recompiled someday anyway. >> >> I appreciate (and agree with) the concern about the fragility of the >> codebase. The maintainer knows that anything except adding ORDER BY >> is a kludge. >> >> Now, the aforementioned notwithstanding... > > Replacing the table with a view should work. Just be sure to make > insert rules if you need it to be insertable by other parts of the app > I guess. Actually, since the order by in the view will be applied before the hash_agg and stuff, I don't think it will work. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] 8.4.1 distinct query WITHOUT order by
> What are the ramifications of renaming the table (containing 8000 > rows) and creating a view of the same name? View does not admit ORDER BY clause, at least, Standard does not. Sergey -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
