pg7.3.4: pg_atoi: zero-length string

[Rob Fielding]

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Andrew Sullivan]

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
<an****@liberty rms.info> M2P 2A8
[phone] x110
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Rob Fielding]

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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Bruno Wolff III]

On Tue, Nov 04, 2003 at 12:19:58 +0000,
Rob Fielding <ro*@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.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Rob Fielding]

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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

[Andrew Sullivan]

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
<an****@liberty rms.info> M2P 2A8
[phone] x110
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Rob Fielding]

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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html