X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7009C52A20 for ; Tue, 9 Aug 2005 16:03:11 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 43125-07 for ; Tue, 9 Aug 2005 19:03:05 +0000 (GMT) Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.196]) by svr1.postgresql.org (Postfix) with ESMTP id 17892529F6 for ; Tue, 9 Aug 2005 16:03:02 -0300 (ADT) Received: by zproxy.gmail.com with SMTP id o37so207232nzf for ; Tue, 09 Aug 2005 12:03:03 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=bzN4m8AvsPIDFCzODvXgrUoHoaIKvRT/ummkApgVo5/hne11DYks/RtltD7pYL3eRv6gCBnTvQajXX5Xw5nxvX+5O53ZOTAkA2kAHv9EMhl4FTsHAYR1jTjzh+2Gs2Fmme0hof3XRB+zg+HJEdl0otxoUNO+BZ9PnWX2wKc4iyc= Received: by 10.36.8.6 with SMTP id 6mr1266094nzh; Tue, 09 Aug 2005 12:03:03 -0700 (PDT) Received: by 10.36.24.2 with HTTP; Tue, 9 Aug 2005 12:03:03 -0700 (PDT) Message-ID: <1d219a6f050809120320cb1acb@mail.gmail.com> Date: Tue, 9 Aug 2005 15:03:03 -0400 From: Chris Hoover To: Michael Fuhr Subject: Re: PG 7.3.4 VS PG 8.0.3 Problem Cc: pgsql-admin@postgresql.org In-Reply-To: <20050809183443.GA25232@winnie.fuhr.org> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <1d219a6f05080910277cbf10b2@mail.gmail.com> <20050809183443.GA25232@winnie.fuhr.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.024 tagged_above=0 required=5 tests=RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200508/116 X-Sequence-Number: 18435 Sorry, I should have given some setup details. First off, we are running 8.0.3 from the postgresql.org rpms on RHEL AS 4. 7.3.4 is running on RHEL AS 2.1 installed from source. What is ECPG? Anyway, I'm not sure how to give you a simple example. We are running a cobol application that is connecting to PostgreSQL via odbc. The application runs on one linux box and the database on a seperate one. The application generates a sql like this (ugly I know): SELECT *=20 FROM CLMHDR=20 WHERE=20 (HDR_CLM_STATUS =3D 'L ' OR=20 HDR_CLM_STATUS =3D 'E ' OR=20 HDR_CLM_STATUS =3D 'V ' OR=20 HDR_CLM_STATUS =3D 'P ' OR=20 HDR_CLM_STATUS =3D 'H ' OR=20 HDR_CLM_STATUS =3D 'A ' OR=20 HDR_CLM_STATUS =3D 'R ' OR=20 HDR_CLM_STATUS =3D 'T ' OR=20 HDR_CLM_STATUS =3D '* ' OR=20 HDR_CLM_STATUS =3D '* ') AND=20 (HDR_CREATE_DT >=3D '' AND HDR_CREATE_DT <=3D '') AND=20 (HDR_ATCH_IND =3D 'A' OR=20 HDR_ATCH_IND =3D 'E' OR=20 HDR_ATCH_IND =3D 'U' OR=20 HDR_ATCH_IND =3D 'C' OR=20 HDR_ATCH_IND =3D '') AND=20 HDR_USER_ID =3D 'some_user'=20 ORDER BY HDR_INV_NBR, HDR_INV_QFR; When this query is run against either version of PG, it does not return any rows (obviously due to the issue with hdr_create_dt). I actually walked through the code using our cobol debugger to find this issue. When it checks the sqlca.sqlcode record returned by this query in 7.3.4, it has a 0. In 8.0.3, it has a 100. I know that we are going to have to get this bad query fixed in our code, but I am very curious why the same query generating the same results would return different sqlca records. I am really hoping there might be a way to make it behave like 7.3.4 does since this problem sql is imbedded quite deeply in our application and the fix will not be trivial. thanks for your assistance, Chris On 8/9/05, Michael Fuhr wrote: > On Tue, Aug 09, 2005 at 01:27:55PM -0400, Chris Hoover wrote: > > When I connect to a 7.3.4 database, the query returns 0 rows and a > > sqlca.sqlcode of 0 > > > > However, when I connect to an 8.0.3 database, the query returns 0 rows > > and a sqlca.sqlcode of 100. >=20 > You don't mention it but it sounds like you're using ECPG. Can you > post a simple but complete example that works differently in 7.3.4 > and 8.0.3? >=20 > I just did some tests with 7.3.10 and 8.0.3 and both returned a > sqlca.sqlcode of 100 for a query that returned no rows. Either > something changed between 7.3.4 and 7.3.10 or my test didn't > duplicate what you're doing. >=20 > -- > Michael Fuhr >