Обсуждение: 7.3.3 behaving differently on OS X 10.2.6 and FreeBSD 4.8-STABLE
I don't know if this is a postgresql bug or a problem with my architecture but I thought I would post here about a strange bug I just came across in my application. I use OS X 10.2.6 as my development machine and FreeBSD 4.8 for my production machines. All systems are running postgresql 7.3.3. I just published some code to production and when testing the production results it blew up with a sql parsing error. The following sql worked fine on my OS X development machine: select u.user_id, u.first_name, u.last_name, u.email_address, w.w9, pm.description as payment_method, count(s.user_id) as documents, sum(s.payment_amount) as amt_sum from ht_user u inner join writer w on u.user_id = w.user_id inner join payment_method pm on w.payment_method_id = pm.payment_method_id left join submission s on u.user_id = s.user_id group by u.user_id, u.first_name, u.last_name, u.email_address, w.w9, pm.description order by lower(last_name) asc But on my production machine postgresql complained about the order by clause-- it wanted the table alias to be on last_name. culley
Culley, > But on my production machine postgresql complained about the order by > clause-- it wanted the table alias to be on last_name. I believe this is because you used "u.last_name" earlier in the statement, and the ORDER BY clause doesn't know that's whatyou mean. That's a guess that doesn't really explain why it'd work under one OS and not under another. Are the two versions of Postgresconfigured the same? -------------------------- David Olbersen iGuard Engineer St. Bernard Software > -----Original Message----- > From: culley harrelson [mailto:culley@fastmail.fm] > Sent: Friday, August 08, 2003 10:48 AM > To: pgsql-general@postgresql.org > Subject: [GENERAL] 7.3.3 behaving differently on OS X 10.2.6 > and FreeBSD > 4.8-STABLE > > > I don't know if this is a postgresql bug or a problem with my > architecture but I thought I would post here about a strange > bug I just > came across in my application. > > I use OS X 10.2.6 as my development machine and FreeBSD 4.8 for my > production machines. All systems are running postgresql > 7.3.3. I just > published some code to production and when testing the production > results it blew up with a sql parsing error. The following > sql worked > fine on my OS X development machine: > > select u.user_id, u.first_name, u.last_name, u.email_address, w.w9, > pm.description as payment_method, count(s.user_id) as documents, > sum(s.payment_amount) as amt_sum from ht_user u inner join > writer w on > u.user_id = w.user_id inner join payment_method pm on > w.payment_method_id = pm.payment_method_id left join submission s on > u.user_id = s.user_id group by u.user_id, u.first_name, u.last_name, > u.email_address, w.w9, pm.description order by lower(last_name) asc > > But on my production machine postgresql complained about the order by > clause-- it wanted the table alias to be on last_name. > > culley > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings >
"David Olbersen" <DOlbersen@stbernard.com> writes:
> That's a guess that
> doesn't really explain why it'd work under one OS and not under
> another. Are the two versions of Postgres configured the same?
It seems pretty weird to me too. I'd bet that the table declarations
are not really quite the same on both boxes.
regards, tom lane
I have a suspicion that the version might be different. I have the same symptom here on two different RH 7.3 boxes one running 7.3.2 and the other running 7.3.3
It would appear 7.3.2 is more strict about the naming of the GROUP BY fields.
David Olbersen wrote:
It would appear 7.3.2 is more strict about the naming of the GROUP BY fields.
David Olbersen wrote:
Culley,But on my production machine postgresql complained about the order by clause-- it wanted the table alias to be on last_name.I believe this is because you used "u.last_name" earlier in the statement, and the ORDER BY clause doesn't know that's what you mean. That's a guess that doesn't really explain why it'd work under one OS and not under another. Are the two versions of Postgres configured the same? -------------------------- David Olbersen iGuard Engineer St. Bernard Software-----Original Message----- From: culley harrelson [mailto:culley@fastmail.fm] Sent: Friday, August 08, 2003 10:48 AM To: pgsql-general@postgresql.org Subject: [GENERAL] 7.3.3 behaving differently on OS X 10.2.6 and FreeBSD 4.8-STABLE I don't know if this is a postgresql bug or a problem with my architecture but I thought I would post here about a strange bug I just came across in my application. I use OS X 10.2.6 as my development machine and FreeBSD 4.8 for my production machines. All systems are running postgresql 7.3.3. I just published some code to production and when testing the production results it blew up with a sql parsing error. The following sql worked fine on my OS X development machine: select u.user_id, u.first_name, u.last_name, u.email_address, w.w9, pm.description as payment_method, count(s.user_id) as documents, sum(s.payment_amount) as amt_sum from ht_user u inner join writer w on u.user_id = w.user_id inner join payment_method pm on w.payment_method_id = pm.payment_method_id left join submission s on u.user_id = s.user_id group by u.user_id, u.first_name, u.last_name, u.email_address, w.w9, pm.description order by lower(last_name) asc But on my production machine postgresql complained about the order by clause-- it wanted the table alias to be on last_name. culley ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
DeJuan Jackson <djackson@speedfc.com> writes:
> I have a suspicion that the version might be different. I have the same
> symptom here on two different RH 7.3 boxes one running 7.3.2 and the
> other running 7.3.3
> It would appear 7.3.2 is more strict about the naming of the GROUP BY
> fields.
Hmm ... digging in the CVS logs, I see just two potentially-relevant
changes between 7.3.2 and 7.3.3.
2003-04-03 13:04 tgl
* src/backend/parser/parse_agg.c (REL7_3_STABLE): Repair incorrect
checking of grouped/ungrouped variables in the presence of unnamed
joins; per pghackers discussion 31-Mar-03.
(the discussion in question is at
http://archives.postgresql.org/pgsql-hackers/2003-03/msg01230.php
2003-03-13 11:58 tgl
* src/backend/optimizer/plan/planner.c (REL7_3_STABLE): GROUP BY
got confused if there were multiple equal() GROUP BY items. This
bug has been latent since 7.0 or maybe even further back, but it
was only exposed when parse_clause.c stopped suppressing duplicate
items (see its rev 1.96 of 18-Aug-02).
The second one doesn't seem to apply, but maybe the first does.
regards, tom lane
DeJuan Jackson wrote: > I have a suspicion that the version might be different. I have the same > symptom here on two different RH 7.3 boxes one running 7.3.2 and the > other running 7.3.3 > It would appear 7.3.2 is more strict about the naming of the GROUP BY > fields. > They really are the same versions. For the OS X machine I installed from source downloaded from the postgresql ftp site. FreeBSD was installed from the port but my ports tree is up to date. On freebsd: 501 $ pg_ctl --version pg_ctl (PostgreSQL) 7.3.3 On OS X: 516 $ pg_ctl --version pg_ctl (PostgreSQL) 7.3.3
> That's a guess that doesn't really explain why it'd work under one OS > and not under another. Are the two versions of Postgres configured > the same? > I suppose they could be configured differently. I don't know how to investigate this. It isn't really a problem for me-- just strange. culley
culley harrelson <culley@fastmail.fm> writes:
> DeJuan Jackson wrote:
>> I have a suspicion that the version might be different.
> They really are the same versions.
Either they're not the same version, or the table schemas are different
between the two installations, or there's a bug that we need to fix.
There is no fourth possibility. Accordingly, I want to pursue this
until we understand it. For starters, what was the *exact* error
message you got, from exactly what input query, and how did you change
the query to avoid it? And please show us \d info for all the relevant
tables from both installations.
regards, tom lane