Re: Embarassing GROUP question - Mailing list pgsql-general

Most Database Administrators dont allow jpg/png/gifs into BLOB columns simply because its Run-length
encoding and MUCH easier to store the picture's link e.g. http://www.mywebsite.com/PictureOfFido.jpg

Oracle on the other hand can store multi-gb images into blobs then again you're paying for that 'luxury'

Also keep in mind Postgres is under BSD license so you're getting what you pay for
<LegalStuff/>
PostgreSQL Database Management System
(formerly known as Postgres, then as Postgres95)

Portions Copyright (c) 1996-2009, The PostgreSQL Global Development Group

Portions Copyright (c) 1994, The Regents of the University of California

Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.

IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
</LegalStuff>

what types of caching issues are you experencing?
Martin Gainty
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.

> From: corey@eyewantmedia.com
> To: tgl@sss.pgh.pa.us
> Subject: Re: [GENERAL] Embarassing GROUP question
> Date: Sat, 3 Oct 2009 16:56:02 -0500
> CC: sam@samason.me.uk; pgsql-general@postgresql.org
>
> We're coming from mysql 4, and changing the sort order changes the
> values of all columns as you would expect, given that you would expect
> a sort statement to affect grouping. This certainly isn't the only
> time I've used this syntax. I've been mysql user for ten years, and
> the outcome has been consistant across hundreds of tables and millions
> of rows and thousands of queries. If you ever have to use or modify a
> mysql db, just keep this in mind in case it saves you some time.
>
> That being said, we've discovered a few instances where docs were
> wrong, found numerous bugs with bitshifting and blob objects and cache
> usage and io buffering. We even sarted working on our own storage
> engine until we came to our senses and switched RDBMSeses.
>
> 5.1 has chased more than a few folks off, and rather than upgrade to
> it, we started porting to postgres. I didn't mean for my comparison to
> appearas a knock against postgres, merely to explain why I was having
> such a problem with such a simple issue. Thanks again for the help.
>
> Corey Tisdale
>
> On Oct 3, 2009, at 3:39 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> > Sam Mason <sam@samason.me.uk> writes:
> >> On Sat, Oct 03, 2009 at 01:05:49PM -0400, Tom Lane wrote:
> >>> but looking at this example, and presuming that you find that
> >>> it actually does something useful, I wonder whether they interpret
> >>> the combination of GROUP BY and ambiguous-per-spec ORDER BY
> >>> in some fashion similar to DISTINCT ON.
> >
> >> Yup, does look that way doesn't it. It's still a weird pair of
> >> semantics to conflate.
> >
> > I poked around in the MySQL 5.1 manual to see if this is true.
> > I think it isn't --- it says very clearly here:
> > http://dev.mysql.com/doc/refman/5.1/en/group-by-hidden-columns.html
> > that you simply get an arbitrary choice among the possible values
> > when you reference an ambiguous column. It's possible that Corey's
> > query actually does give him the answers he wants, but apparently
> > it would be an implementation artifact that they're not promising
> > to maintain.
> >
> >> Hum, if they were assuming that you'd always have to implement
> >> GROUP BY
> >> by doing a sort step first then I can see why they'd end up with
> >> this.
> >
> > It's worse than that --- they actually are promising that GROUP BY
> > orders the results! In
> > http://dev.mysql.com/doc/refman/5.1/en/select.html
> > I find
> >
> > If you use GROUP BY, output rows are sorted according to the
> > GROUP BY columns as if you had an ORDER BY for the same
> > columns. To avoid the overhead of sorting that GROUP BY
> > produces, add ORDER BY NULL:
> >
> > SELECT a, COUNT(b) FROM test_table GROUP BY a ORDER BY NULL;
> >
> > MySQL extends the GROUP BY clause so that you can also specify
> > ASC and DESC after columns named in the clause:
> >
> > SELECT a, COUNT(b) FROM test_table GROUP BY a DESC;
> >
> > The first of these examples implies that they allow ORDER BY to
> > override
> > the default GROUP BY sorting, which would mean that the ORDER BY sort
> > has to happen after the GROUP BY operation, unlike the approach we
> > take
> > for DISTINCT ON. So that means the ORDER BY *isn't* going to affect
> > which row gets chosen out of each event_type group.
> >
> > What I am currently betting is that Corey's query does not really do
> > what he thinks it does in MySQL. It probably is selecting a random
> > representative row in each group and then sorting on the basis of the
> > event_dates in those rows.
> >
> > regards, tom lane
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

---

Hotmail: Trusted email with powerful SPAM protection. Sign up now.