Обсуждение: 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! */
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
On Mon, Dec 21, 2009 at 3:38 PM, Thomas Kellerer <spam_eater@gmx.net> 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.
> 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! */
On Mon, Dec 21, 2009 at 8:45 PM, Gary Chambers <gwchamb@gmail.com> 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.
On Mon, Dec 21, 2009 at 10:18 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote: > On Mon, Dec 21, 2009 at 8:45 PM, Gary Chambers <gwchamb@gmail.com> 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.
> 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
On Tue, Dec 22, 2009 at 12:11 AM, msi77 <msi77@yandex.ru> wrote: >> 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. Postgres certainly allows it, but I don't think it will help in this case.