From jscalia@cityblueprinting.com Fri Oct  3 13:19:32 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id E5B77D1B508
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri,  3 Oct 2003 13:19:32 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 48392-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri,  3 Oct 2003 10:18:46 -0300 (ADT)
Received: from chimta05.algx.net (mta6.algx.net [67.92.168.235])
	by svr1.postgresql.org (Postfix) with ESMTP id B2E0ED1B506
	for <pgsql-sql@postgresql.org>; Fri,  3 Oct 2003 10:18:42 -0300 (ADT)
Received: from John
	(ip216-203-212-194.z212-203-216.customer.algx.net [216.203.212.194])
	by chimmx05.algx.net
	(iPlanet Messaging Server 5.2 HotFix 1.16 (built May 14 2003))
	with ESMTP id <0HM600JHUNN9VK@chimmx05.algx.net> for
	pgsql-sql@postgresql.org; Fri, 03 Oct 2003 08:18:46 -0500 (CDT)
Date: Fri, 03 Oct 2003 09:18:44 -0400
From: "John B. Scalia" <jscalia@cityblueprinting.com>
Subject: Bug in psql - Postgresql 7.3.1?
To: pgsql-sql@postgresql.org
Message-id: <001a01c389b0$e00773a0$2501a8c0@John>
MIME-version: 1.0
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Mailer: Microsoft Outlook, Build 10.0.4024
Content-type: multipart/alternative;
	boundary="Boundary_(ID_GPvWkl3PNtmCGVQBWMcQtQ)"
Importance: Normal
X-Priority: 3 (Normal)
X-MSMail-priority: Normal
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=HTML_50_60
X-Spam-Level: 
X-Archive-Number: 200310/53
X-Sequence-Number: 15453

This is a multi-part message in MIME format.

--Boundary_(ID_GPvWkl3PNtmCGVQBWMcQtQ)
Content-type: text/plain; charset=us-ascii
Content-transfer-encoding: 7BIT

All,

 

I'm not certain if what I'm trying to do is legal, but if I execute a
statement like:

 

UPDATE my_table SET field1='new_value' AND SET field2='different_value'
WHERE my_table_id = 'key';

 

in psql, it reports that it has successfully updated one record.
However, the record does not appear to be updated when I subsequently
issue a SELECT for that record. I'll buy the fact that I may be using
illegal syntax - I'm not SQL expert, but the update says it worked when
in fact it did not. Should this have worked or should it have reported
an error?

 

For record, this is Postgresql 7.3.1 on Solaris 9. Problem originally
seen during a php4 script, but has been duplicated on the psql command
line.

 

TIA,

John


--Boundary_(ID_GPvWkl3PNtmCGVQBWMcQtQ)
Content-type: text/html; charset=us-ascii
Content-transfer-encoding: 7BIT

<html>

<head>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">


<meta name=Generator content="Microsoft Word 10 (filtered)">

<style>
<!--
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	margin-bottom:.0001pt;
	font-size:12.0pt;
	font-family:"Times New Roman";}
a:link, span.MsoHyperlink
	{color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{color:purple;
	text-decoration:underline;}
span.EmailStyle17
	{font-family:Arial;
	color:windowtext;}
@page Section1
	{size:8.5in 11.0in;
	margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
	{page:Section1;}
-->
</style>

</head>

<body lang=EN-US link=blue vlink=purple>

<div class=Section1>

<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>All,</span></font></p>

<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>&nbsp;</span></font></p>

<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>I&#8217;m not certain if what I&#8217;m trying to do is
legal, but if I execute a statement like:</span></font></p>

<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>&nbsp;</span></font></p>

<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>UPDATE my_table SET field1=&#8217;new_value&#8217; AND SET
field2=&#8217;different_value&#8217; WHERE my_table_id = &#8216;key&#8217;;</span></font></p>

<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>&nbsp;</span></font></p>

<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>in psql, it reports that it has successfully updated one
record. However, the record does not appear to be updated when I subsequently
issue a SELECT for that record. I&#8217;ll buy the fact that I may be using
illegal syntax &#8211; I&#8217;m not SQL expert, but the update says it worked
when in fact it did not. Should this have worked or should it have reported an
error?</span></font></p>

<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>&nbsp;</span></font></p>

<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>For record, this is Postgresql 7.3.1 on Solaris 9. Problem
originally seen during a php4 script, but has been duplicated on the psql
command line.</span></font></p>

<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>&nbsp;</span></font></p>

<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>TIA,</span></font></p>

<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>John</span></font></p>

</div>

</body>

</html>

--Boundary_(ID_GPvWkl3PNtmCGVQBWMcQtQ)--



From wweng@kencast.com Fri Oct  3 20:01:32 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 3C012D1CB17
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri,  3 Oct 2003 20:01:32 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 28624-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri,  3 Oct 2003 17:00:44 -0300 (ADT)
Received: from mta3.snet.net (mta3.snet.net [204.60.203.69])
	by svr1.postgresql.org (Postfix) with ESMTP id 97BCBD1C9EB
	for <pgsql-sql@postgresql.org>; Fri,  3 Oct 2003 16:42:34 -0300 (ADT)
Received: from [192.168.1.203] (167.95.252.64.snet.net [64.252.95.167])
	by mta3.snet.net (8.12.3/8.12.3/SNET-smtp-1.2/D-1.1.1.1/O-1.1.1.1) with
	ESMTP id h93JgHIJ010138; Fri, 3 Oct 2003 15:42:18 -0400 (EDT)
Date: Fri, 3 Oct 2003 15:37:33 -0400 (EDT)
From: Wei Weng <wweng@kencast.com>
X-X-Sender: wweng@localhost.localdomain
To: "John B. Scalia" <jscalia@cityblueprinting.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Bug in psql - Postgresql 7.3.1?
In-Reply-To: <001a01c389b0$e00773a0$2501a8c0@John>
Message-ID: <Pine.LNX.4.56.0310031536520.6639@localhost.localdomain>
References: <001a01c389b0$e00773a0$2501a8c0@John>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200310/56
X-Sequence-Number: 15456

Shouldn't that be "UPDATE my_table SET field1 = 'new_value', field2 = 
'different_value' WHERE my_table_id = 'key';"?

Wei

On Fri, 3 Oct 2003, John B. Scalia wrote:

> All,
> 
>  
> 
> I'm not certain if what I'm trying to do is legal, but if I execute a
> statement like:
> 
>  
> 
> UPDATE my_table SET field1='new_value' AND SET field2='different_value'
> WHERE my_table_id = 'key';
> 
>  
> 
> in psql, it reports that it has successfully updated one record.
> However, the record does not appear to be updated when I subsequently
> issue a SELECT for that record. I'll buy the fact that I may be using
> illegal syntax - I'm not SQL expert, but the update says it worked when
> in fact it did not. Should this have worked or should it have reported
> an error?
> 
>  
> 
> For record, this is Postgresql 7.3.1 on Solaris 9. Problem originally
> seen during a php4 script, but has been duplicated on the psql command
> line.
> 
>  
> 
> TIA,
> 
> John
> 
> 



From josh@agliodbs.com Fri Oct  3 20:11:20 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 915E5D1B4FC
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri,  3 Oct 2003 20:11:20 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 34823-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri,  3 Oct 2003 17:10:33 -0300 (ADT)
Received: from davinci.ethosmedia.com (unknown [209.128.84.228])
	by svr1.postgresql.org (Postfix) with ESMTP id 72E57D1CC27
	for <pgsql-sql@postgresql.org>; Fri,  3 Oct 2003 16:47:00 -0300 (ADT)
Received: from [66.219.92.2] (HELO temoku)
	by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2)
	with ESMTP id 3719209; Fri, 03 Oct 2003 12:47:36 -0700
Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: "John B. Scalia" <jscalia@cityblueprinting.com>, pgsql-sql@postgresql.org
Subject: Re: Bug in psql - Postgresql 7.3.1?
Date: Fri, 3 Oct 2003 12:45:07 -0700
User-Agent: KMail/1.4.3
References: <001a01c389b0$e00773a0$2501a8c0@John>
In-Reply-To: <001a01c389b0$e00773a0$2501a8c0@John>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200310031245.08177.josh@agliodbs.com>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200310/57
X-Sequence-Number: 15457

John,

>=20
> UPDATE my_table SET field1=3D'new_value' AND SET field2=3D'different_valu=
e'
> WHERE my_table_id =3D 'key';

Well, your SQL is bad:

UPDATE my_table SET field1=3D'new_value, field2=3D'different_value'
WHERE my_table_id =3D 'key';

> in psql, it reports that it has successfully updated one record.
> However, the record does not appear to be updated when I subsequently
> issue a SELECT for that record. I'll buy the fact that I may be using
> illegal syntax - I'm not SQL expert, but the update says it worked when
> in fact it did not. Should this have worked or should it have reported
> an error?

It's an error if it actually occurred that way.  Can you cut-and-paste your=
=20
actual PSQL session?

> For record, this is Postgresql 7.3.1 on Solaris 9. Problem originally
> seen during a php4 script, but has been duplicated on the psql command
> line.

BTW, there are known bugs in 7.3.1; you should upgrade to 7.3.4.

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco




From bruno@wolff.to Fri Oct  3 19:51:18 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 470F4D1CC78
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri,  3 Oct 2003 19:51:18 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 27011-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri,  3 Oct 2003 16:50:29 -0300 (ADT)
Received: from wolff.to (wolff.to [66.93.249.74])
	by svr1.postgresql.org (Postfix) with SMTP id E8EB6D1B52E
	for <pgsql-sql@postgresql.org>; Fri,  3 Oct 2003 16:39:56 -0300 (ADT)
Received: (qmail 23165 invoked by uid 500); 3 Oct 2003 19:47:16 -0000
Date: Fri, 3 Oct 2003 14:47:16 -0500
From: Bruno Wolff III <bruno@wolff.to>
To: "John B. Scalia" <jscalia@cityblueprinting.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Bug in psql - Postgresql 7.3.1?
Message-ID: <20031003194716.GB23110@wolff.to>
Mail-Followup-To: "John B. Scalia" <jscalia@cityblueprinting.com>,
	pgsql-sql@postgresql.org
References: <001a01c389b0$e00773a0$2501a8c0@John>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <001a01c389b0$e00773a0$2501a8c0@John>
User-Agent: Mutt/1.5.4i
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200310/55
X-Sequence-Number: 15455

On Fri, Oct 03, 2003 at 09:18:44 -0400,
  "John B. Scalia" <jscalia@cityblueprinting.com> wrote:
> All,
> 
>  
> 
> I'm not certain if what I'm trying to do is legal, but if I execute a
> statement like:
> 
>  
> 
> UPDATE my_table SET field1='new_value' AND SET field2='different_value'
> WHERE my_table_id = 'key';

It should be:
UPDATE my_table SET field1='new_value', field2='different_value'
WHERE my_table_id = 'key';

> in psql, it reports that it has successfully updated one record.

I am not sure why you aren't getting a syntax error.

> However, the record does not appear to be updated when I subsequently
> issue a SELECT for that record. I'll buy the fact that I may be using
> illegal syntax - I'm not SQL expert, but the update says it worked when
> in fact it did not. Should this have worked or should it have reported
> an error?



From josh@agliodbs.com Fri Oct  3 20:38:58 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 0E899D1B568
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri,  3 Oct 2003 20:38:58 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 39190-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri,  3 Oct 2003 17:38:10 -0300 (ADT)
Received: from davinci.ethosmedia.com (unknown [209.128.84.228])
	by svr1.postgresql.org (Postfix) with ESMTP id 9A50DD1CB8D
	for <pgsql-sql@postgresql.org>; Fri,  3 Oct 2003 17:18:02 -0300 (ADT)
Received: from [66.219.92.2] (HELO temoku)
	by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2)
	with ESMTP id 3719388; Fri, 03 Oct 2003 13:18:38 -0700
Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: "John B. Scalia" <jscalia@cityblueprinting.com>
Subject: Re: Bug in psql - Postgresql 7.3.1?
Date: Fri, 3 Oct 2003 13:16:10 -0700
User-Agent: KMail/1.4.3
References: <004401c389e9$ac7acc60$2501a8c0@John>
In-Reply-To: <004401c389e9$ac7acc60$2501a8c0@John>
Cc: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200310031315.52146.josh@agliodbs.com>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200310/58
X-Sequence-Number: 15458

John,

> Yeah, I figured out my SQL was bad and had switched to the comma
> separated version, instead. In my mind, the first form should have
> caused an error. I've attached a cut-and-pasted session from psql where
> I used this syntax on a test table. While edited for brevity and to
> obscure passwords, this is how the output appeared.

Here's your problem:

accounting=3D# update all_user set usr_current =3D True AND usr_location =
=3D 1002;
UPDATE 3

PostgreSQL interpreted the expression "True AND usr_location =3D 1002" as a=
=20
single, unitary, boolean expression.   AND is the boolean AND operator.=20=
=20
Since none of the users on your list had "usr_location =3D 1002", you got:

user_current =3D (True AND (usr_location =3D 1002))
user_current =3D (True AND False)
user_current =3D False

Since all 3 rows already had false, they did not appear to get updated, but=
 in=20
fact they were.

Time to look up your order of operations!

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco




From tgl@sss.pgh.pa.us Fri Oct  3 20:45:07 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id EDF8FD1B53E
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri,  3 Oct 2003 20:45:07 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 39839-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri,  3 Oct 2003 17:44:23 -0300 (ADT)
Received: from sss.pgh.pa.us (unknown [192.204.191.242])
	by svr1.postgresql.org (Postfix) with ESMTP id 41299D1B58E
	for <pgsql-sql@postgresql.org>; Fri,  3 Oct 2003 17:27:28 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id h93KRChn000730;
	Fri, 3 Oct 2003 16:27:12 -0400 (EDT)
To: "John B. Scalia" <jscalia@cityblueprinting.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Bug in psql - Postgresql 7.3.1? 
In-reply-to: <001a01c389b0$e00773a0$2501a8c0@John> 
References: <001a01c389b0$e00773a0$2501a8c0@John>
Comments: In-reply-to "John B. Scalia" <jscalia@cityblueprinting.com>
	message dated "Fri, 03 Oct 2003 09:18:44 -0400"
Date: Fri, 03 Oct 2003 16:27:12 -0400
Message-ID: <729.1065212832@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200310/59
X-Sequence-Number: 15459

"John B. Scalia" <jscalia@cityblueprinting.com> writes:
> UPDATE my_table SET field1='new_value' AND SET field2='different_value'
> WHERE my_table_id = 'key';

The other responses have focused on your obvious syntax error, but I'm
assuming you didn't actually cut-and-paste that from your psql session.

> in psql, it reports that it has successfully updated one record.
> However, the record does not appear to be updated when I subsequently
> issue a SELECT for that record.

I'm wondering if you are checking from another transaction that doesn't
think the first one is committed yet.  Are you using BEGIN/END or
autocommit-off mode?

			regards, tom lane