Thread: [HACKERS] float4 confused as int??
Can someone enlighten me why this error is occurring in which a float value is apparently being interpreted as an integer? Known workarounds?? emsdb=> UPDATE contract_activity_type SET duration = 3.5, updater_id = 1, last_update = now() WHERE id = 72; ERROR: pg_atoi: error in "3.5": can't parse ".5" emsdb=> \d contract_activity_type Table = contract_activity_type +----------------------------------+----------------------------------+-------+ | Field | Type | Length| +----------------------------------+----------------------------------+-------+ | id | int4 not null default nextval ( | 4 | | contract_id | int4 not null | 4 | | activity_type_id | int4 not null | 4 | | travel_required | int4 not null | 4 | | billable | int4 not null | 4 | | duration | float4 not null | 4 | ... Thanks in advance. Cheers, Ed Loehr
Ed Loehr <eloehr@austin.rr.com> writes:
> emsdb=> UPDATE contract_activity_type SET duration = 3.5, updater_id
> = 1, last_update = now() WHERE id = 72;
> ERROR: pg_atoi: error in "3.5": can't parse ".5"
?? What version are you running? I can't reproduce that with either
6.5.3 or current sources.
regards, tom lane
Tom Lane wrote: > > Ed Loehr <eloehr@austin.rr.com> writes: > > emsdb=> UPDATE contract_activity_type SET duration = 3.5, updater_id > > = 1, last_update = now() WHERE id = 72; > > ERROR: pg_atoi: error in "3.5": can't parse ".5" > > ?? What version are you running? I can't reproduce that with either > 6.5.3 or current sources. 6.5.2
Ed Loehr wrote: > > Tom Lane wrote: > > > > Ed Loehr <eloehr@austin.rr.com> writes: > > > emsdb=> UPDATE contract_activity_type SET duration = 3.5, updater_id > > > = 1, last_update = now() WHERE id = 72; > > > ERROR: pg_atoi: error in "3.5": can't parse ".5" > > > > ?? What version are you running? I can't reproduce that with either > > 6.5.3 or current sources. > > 6.5.2 This happens only via DBI/DBD::Pg. I can't reproduce via psql. Cheers, Ed Loehr
Ed Loehr wrote: > > Ed Loehr wrote: > > > > Tom Lane wrote: > > > > > > Ed Loehr <eloehr@austin.rr.com> writes: > > > > emsdb=> UPDATE contract_activity_type SET duration = 3.5, updater_id > > > > = 1, last_update = now() WHERE id = 72; > > > > ERROR: pg_atoi: error in "3.5": can't parse ".5" > > > > > > ?? What version are you running? I can't reproduce that with either > > > 6.5.3 or current sources. > > > > 6.5.2 > > This happens only via DBI/DBD::Pg. I can't reproduce via psql. Not getting enough sleep... Correction: DBI or not makes no difference. I can reproduce it on *this* table via psql, but I cannot reproduce on a newly created table with a float column.
The problem is occurring with a database that was restored with psql from a pg_dump (6.5.2). If I dump the problem table, then drop/recreate it with my original table creation commands, and then reload with only the insert commands from the pg_dump'ed file, it reloads and allows updating of the float field without a problem. Testing my entire DB to find other problem float fields is painful; doing this drop/recreate process would be even more so. As you can see below, this makes no sense given '\d' shows the field is a float4 yet the code is trying to call pg_atoi on the new value... I'm still looking into this more to understand what hoops I may have to jump through to salvage/reuse pg_dump output...any tips would be appreciated. Ed Loehr wrote: > > Can someone enlighten me why this error is occurring in which a float > value is apparently being interpreted as an integer? Known > workarounds?? > > emsdb=> UPDATE contract_activity_type SET duration = 3.5, updater_id > = 1, last_update = now() WHERE id = 72; > ERROR: pg_atoi: error in "3.5": can't parse ".5" > emsdb=> \d contract_activity_type > Table = contract_activity_type > +----------------------------------+----------------------------------+-------+ > | Field | Type > | Length| > +----------------------------------+----------------------------------+-------+ > | id | int4 not null default nextval ( > | 4 | > | contract_id | int4 not null > | 4 | > | activity_type_id | int4 not null > | 4 | > | travel_required | int4 not null > | 4 | > | billable | int4 not null > | 4 | > | duration | float4 not null > | 4 | > ... > > Thanks in advance. > > Cheers, > Ed Loehr
Ed Loehr <eloehr@austin.rr.com> writes:
> The problem is occurring with a database that was restored with psql
> from a pg_dump (6.5.2).
> If I dump the problem table, then drop/recreate it with my original
> table creation commands, and then reload with only the insert commands
> from the pg_dump'ed file, it reloads and allows updating of the float
> field without a problem.
OK. Almost certainly, the data in the table is of no consequence;
the table schema is what matters. What are your original table
creation commands, and what does pg_dump emit?
regards, tom lane
Tom Lane wrote:
>
> Ed Loehr <eloehr@austin.rr.com> writes:
> > The problem is occurring with a database that was restored with psql
> > from a pg_dump (6.5.2).
>
> > If I dump the problem table, then drop/recreate it with my original
> > table creation commands, and then reload with only the insert commands
> > from the pg_dump'ed file, it reloads and allows updating of the float
> > field without a problem.
>
> OK. Almost certainly, the data in the table is of no consequence;
> the table schema is what matters. What are your original table
> creation commands, and what does pg_dump emit?
I'd hoped to spot an erroneous integer type redefinition of the column
in the pg_dump output, but no such luck.
------------ Original table creation commands: ----------------------
CREATE TABLE contract_activity_type ( id SERIAL, contract_id INTEGER NOT NULL, -- default
frequencyfor
planning activity_type_id INTEGER NOT NULL,-- Ex: "Interviews",
"Coaching", ... travel_required INTEGER NOT NULL, -- bool: yes/no billable INTEGER NOT NULL, --
bool:yes/no duration FLOAT4 NOT NULL, -- how long is the activity
in days participants INTEGER NOT NULL, -- # of expected
participants frequency_id INTEGER NOT NULL, -- default frequency for
planning cloned INTEGER NOT NULL DEFAULT 0, -- bool: yes/no creator_id INTEGER NOT NULL
DEFAULT0, -- person id creation_time DATETIME NOT NULL DEFAULT now(), updater_id INTEGER NOT NULL
DEFAULT0, -- person id last_update DATETIME NOT NULL DEFAULT now(), record_status INTEGER NOT NULL
DEFAULT1, PRIMARY KEY (contract_id,activity_type_id)
);
CREATE INDEX contract_activity_type_aid ON
contract_activity_type(activity_type_id);
CREATE INDEX contract_activity_type_cid ON
contract_activity_type(contract_id);
------------ pg_dump output: -----------------------------------------
CREATE SEQUENCE "contract_activity_type_id_seq" start 214 increment 1
maxvalue 2147483647 minvalue 1 cache 1 ;
SELECT nextval ('contract_activity_type_id_seq');
CREATE SEQUENCE "contract_activity_type_e_id_seq" start 1386 increment
1 maxvalue 2147483647 minvalue 1 cache 1 ;
SELECT nextval ('contract_activity_type_e_id_seq');
CREATE TABLE "contract_activity_type" ( "id" int4 DEFAULT nextval ( '"contract_activity_type_id_seq"'
) NOT NULL, "contract_id" int4 NOT NULL, "activity_type_id" int4 NOT NULL, "travel_required" int4 NOT
NULL, "billable" int4 NOT NULL, "duration" float4 NOT NULL, "participants" int4 NOT NULL,
"frequency_id"int4 NOT NULL, "cloned" int4 DEFAULT 0 NOT NULL, "creator_id" int4 DEFAULT 0 NOT NULL,
"creation_time"datetime DEFAULT now ( ) NOT NULL, "updater_id" int4 DEFAULT 0 NOT NULL, "last_update"
datetimeDEFAULT now ( ) NOT NULL, "record_status" int4 DEFAULT 1 NOT NULL);