Thread: pg7.3.4: pg_atoi: zero-length string

pg7.3.4: pg_atoi: zero-length string

From
Rob Fielding
Date:

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



Re: pg7.3.4: pg_atoi: zero-length string

From
Andrew Sullivan
Date:
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


Re: pg7.3.4: pg_atoi: zero-length string

From
"Markus Wollny"
Date:
> -----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

>

Re: pg7.3.4: pg_atoi: zero-length string

From
Rob Fielding
Date:

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


Re: pg7.3.4: pg_atoi: zero-length string

From
Bruno Wolff III
Date:
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.

Re: pg7.3.4: pg_atoi: zero-length string

From
Rob Fielding
Date:

>>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


Re: pg7.3.4: pg_atoi: zero-length string

From
Andrew Sullivan
Date:
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


Re: pg7.3.4: pg_atoi: zero-length string

From
Rob Fielding
Date:
> 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