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 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
Show quoted text
-----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-STABLEI 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) ascBut 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
Import Notes
Resolved by subject fallback
"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:
Show quoted text
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-STABLEI 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) ascBut 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