Обсуждение: pg7.3.4: pg_atoi: zero-length string
Hi,
We're currently experiencing a problem where SQL statements are failing
when entring a '' for not not-null integer columns:
ERROR: pg_atoi: zero-length string
This was discovered just after a database migration from 7.2 to 7.3.4.
Example:
insert into renewal_cache
(dom, expiry, issued, aid) values
('data','2004-03-05','19980305','')
The above example in just one case where 'aid' can accept a null value.
The use of quotes around all values was established as IIRC pg7.2
wouldn't accept statements without them. The use of this convention is
extensive.
Cheers,
Rob Fielding
Development
Designer Servers Ltd
On Tue, Nov 04, 2003 at 11:21:35AM +0000, Rob Fielding wrote:
>
>
> Hi,
>
> We're currently experiencing a problem where SQL statements are failing
> when entring a '' for not not-null integer columns:
Yes. This behaviour was made more compliant in 7.3. It's in the
release notes.
> The above example in just one case where 'aid' can accept a null value.
That's not a null. It's a zero-length string.
> The use of quotes around all values was established as IIRC pg7.2
> wouldn't accept statements without them. The use of this convention is
> extensive.
You could probably put in a rewrite rule to convert '' to NULL and
allow nulls on the column. It's the only suggestion I can think of,
short of going back to 7.2.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Afilias Canada Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
> -----Ursprüngliche Nachricht----- > Von: Andrew Sullivan [mailto:andrew@libertyrms.info] > Gesendet: Dienstag, 4. November 2003 12:32 > An: pgsql-general@postgresql.org > Betreff: Re: [GENERAL] pg7.3.4: pg_atoi: zero-length string > > > On Tue, Nov 04, 2003 at 11:21:35AM +0000, Rob Fielding wrote: > > We're currently experiencing a problem where SQL statements > are failing > > when entring a '' for not not-null integer columns: > > Yes. This behaviour was made more compliant in 7.3. It's in the > release notes. [snip] > You could probably put in a rewrite rule to convert '' to NULL and > allow nulls on the column. It's the only suggestion I can think of, > short of going back to 7.2. No, there's actually another option. Bruce posted a patch for reverting to 7.2-behaviour (well, sort of...); I currently cannot find the original message, but here's the relevant bit: -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 --ELM1040320327-20624-0_ Content-Transfer-Encoding: 7bit Content-Type: text/plain Content-Disposition: inline; filename="/bjm/diff" Index: src/backend/utils/adt/numutils.c =================================================================== RCS file: /cvsroot/pgsql-server/src/backend/utils/adt/numutils.c,v retrieving revision 1.54 diff -c -c -r1.54 numutils.c *** src/backend/utils/adt/numutils.c 4 Sep 2002 20:31:28 -0000 1.54 --- src/backend/utils/adt/numutils.c 19 Dec 2002 17:10:56 -0000 *************** *** 70,76 **** if (s == (char *) NULL) elog(ERROR, "pg_atoi: NULL pointer"); else if (*s == 0) ! elog(ERROR, "pg_atoi: zero-length string"); else l = strtol(s, &badp, 10); --- 70,80 ---- if (s == (char *) NULL) elog(ERROR, "pg_atoi: NULL pointer"); else if (*s == 0) ! { ! /* 7.3.X workaround for broken apps, bjm 2002-12-19 */ ! elog(WARNING, "pg_atoi: zero-length string"); ! l = (long) 0; ! } else l = strtol(s, &badp, 10); --ELM1040320327-20624-0_ Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 8bit MIME-Version: 1.0 Note however, that regression tests for 7.3.x will fail, as there are explicit tests for zero-length strings to cause an error. That need not worry you, though. We're currently running 7.3.4 with this patch and it works like a charm. Regards, Markus >
Andrew Sullivan wrote: > On Tue, Nov 04, 2003 at 11:21:35AM +0000, Rob Fielding wrote: > >> >>Hi, >> >>We're currently experiencing a problem where SQL statements are failing >>when entring a '' for not not-null integer columns: > > > Yes. This behaviour was made more compliant in 7.3. It's in the > release notes. > > >>The above example in just one case where 'aid' can accept a null value. I've found this is a feature of 7.3 to not treat a empty string as a NULL integer type. Silly lazy me ;) As it turned out it relatively trivial to fix the offending statements on the few occasions where it has been valid to do this. Consider this a non-issue. Cheers, -- Rob Fielding Development Designer Servers Ltd
On Tue, Nov 04, 2003 at 12:19:58 +0000, Rob Fielding <rob@dsvr.net> wrote: > > I've found this is a feature of 7.3 to not treat a empty string as a > NULL integer type. Silly lazy me ;) It didn't even then. It was treated as 0. Oracle is the DB that treats empty strings as null values.
>>The above example in just one case where 'aid' can accept a null value. > > That's not a null. It's a zero-length string. I've found this is a feature of 7.3 to not treat a empty string as a NULL integer type. Silly lazy me. As it turned out it relatively trivial to fix the offending statements on the few occasions where it has been valid to do this. Consider this a non-issue. Cheers, -- Rob Fielding Development Designer Servers Ltd
On Tue, Nov 04, 2003 at 12:19:58PM +0000, Rob Fielding wrote:
> I've found this is a feature of 7.3 to not treat a empty string as a
> NULL integer type. Silly lazy me ;)
For the record, it _never_ treated it as NULL. It treated it as
"empty string". '' != NULL. In fact, !(NULL=NULL) & !(NULL!=NULL).
SQL uses three-valued logic.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Afilias Canada Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
> For the record, it _never_ treated it as NULL. It treated it as > "empty string". '' != NULL. In fact, !(NULL=NULL) & !(NULL!=NULL). > SQL uses three-valued logic. You're absolutely right. That explains why, when I quickly looked, some are zero's and some are NULLs - the NULLs where NULLs and the zeros where empty strings. Two different bad-programming examples. If I actually used these columns for anything whenever they didn't have non zero or null data in them then I'd have probably been alot more careful about what went in them. I presume that an 32bit integer of zero and a NULL are represented differently in the database ? I suppose internally you aren't representing a NULL within the context of a 32bit integer field and it would just probably be magic pointer to the next field - some sort of exercise in space squashing? I don't know anything about the internal stucture of the tuples. Dependant on the above, it would probably make sense to clean up the database, especially considering these columns are also indexed. Cheers -- Rob Fielding Development Designer Servers Ltd