pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed8.4.1 distinct query WITHOUT order by
8+ messages / 4 participants
[nested] [flat]
* 8.4.1 distinct query WITHOUT order by
@ 2009-12-21 22:15 Gary Chambers <gwchamb@gmail.com>
0 siblings, 2 replies; 8+ messages in thread
From: Gary Chambers @ 2009-12-21 22:15 UTC (permalink / raw)
To: pgsql-sql
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! */
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: 8.4.1 distinct query WITHOUT order by
@ 2009-12-21 22:38 Thomas Kellerer <spam_eater@gmx.net>
parent: Gary Chambers <gwchamb@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Thomas Kellerer @ 2009-12-21 22:38 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: 8.4.1 distinct query WITHOUT order by
@ 2009-12-21 22:53 Scott Marlowe <scott.marlowe@gmail.com>
parent: Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 1 reply; 8+ messages in thread
From: Scott Marlowe @ 2009-12-21 22:53 UTC (permalink / raw)
To: Thomas Kellerer <spam_eater@gmx.net>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: 8.4.1 distinct query WITHOUT order by
@ 2009-12-22 03:45 Gary Chambers <gwchamb@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Gary Chambers @ 2009-12-22 03:45 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Thomas Kellerer <spam_eater@gmx.net>; pgsql-sql
> 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! */
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: 8.4.1 distinct query WITHOUT order by
@ 2009-12-22 05:18 Scott Marlowe <scott.marlowe@gmail.com>
parent: Gary Chambers <gwchamb@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Scott Marlowe @ 2009-12-22 05:18 UTC (permalink / raw)
To: Gary Chambers <gwchamb@gmail.com>; +Cc: Thomas Kellerer <spam_eater@gmx.net>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: 8.4.1 distinct query WITHOUT order by
@ 2009-12-22 05:23 Scott Marlowe <scott.marlowe@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Scott Marlowe @ 2009-12-22 05:23 UTC (permalink / raw)
To: Gary Chambers <gwchamb@gmail.com>; +Cc: Thomas Kellerer <spam_eater@gmx.net>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: 8.4.1 distinct query WITHOUT order by
@ 2009-12-22 07:11 msi77 <msi77@yandex.ru>
parent: Gary Chambers <gwchamb@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: msi77 @ 2009-12-22 07:11 UTC (permalink / raw)
To: Gary Chambers <gwchamb@gmail.com>; pgsql-sql
> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: 8.4.1 distinct query WITHOUT order by
@ 2009-12-22 09:22 Scott Marlowe <scott.marlowe@gmail.com>
parent: msi77 <msi77@yandex.ru>
0 siblings, 0 replies; 8+ messages in thread
From: Scott Marlowe @ 2009-12-22 09:22 UTC (permalink / raw)
To: msi77 <msi77@yandex.ru>; +Cc: Gary Chambers <gwchamb@gmail.com>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2009-12-22 09:22 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-12-21 22:15 8.4.1 distinct query WITHOUT order by Gary Chambers <gwchamb@gmail.com>
2009-12-21 22:38 ` Thomas Kellerer <spam_eater@gmx.net>
2009-12-21 22:53 ` Scott Marlowe <scott.marlowe@gmail.com>
2009-12-22 03:45 ` Gary Chambers <gwchamb@gmail.com>
2009-12-22 05:18 ` Scott Marlowe <scott.marlowe@gmail.com>
2009-12-22 05:23 ` Scott Marlowe <scott.marlowe@gmail.com>
2009-12-22 07:11 ` msi77 <msi77@yandex.ru>
2009-12-22 09:22 ` Scott Marlowe <scott.marlowe@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox