Re: [HACKERS] Problems on NUMERIC

From: jwieck(at)debis(dot)com (Jan Wieck)
To: lockhart(at)alumni(dot)caltech(dot)edu (Thomas G(dot) Lockhart)
Cc: jwieck(at)debis(dot)com, pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: [HACKERS] Problems on NUMERIC
Date: 1998-12-22 16:47:19
Message-ID: m0zsUxs-000EBUC@orion.SAPserv.Hamburg.dsh.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

>
> > First I wonder why the can_coerce... stuff is #if'd out of
> > parse_relation.c? For the NUMERIC type the
> > numeric(num,typmod) must be called if someone does an
> >
> > INSERT INTO ... SELECT * FROM ...
> >
> > But it isn't. It is only called when there are calculations
> > done on the columns. I also checked that for BPCHAR type and
> > it simply throws an ERROR if the target's length doesn't
> > match.
>
> Sorry, I'm having trouble thinking of a case which does not behave
> properly with the existing types. I've tried inserting varchar(10)
> columns into a varchar(1) column, I've tried inserting int columns into
> float columns, etc etc. How are you getting handleTargetColname() /
> checkTargetTypes() called where it is rejecting things?

pgsql=> create table t1 (a char(10));
CREATE
pgsql=> create table t2 (a char(4));
CREATE
pgsql=> insert into t2 select * from t1;
ERROR: Length of a is not equal to the length of target column a
pgsql=>

>
> It may be that splitting that attribute field into two pieces for
> NUMERIC is opening a can of worms, since there are specific assumptions
> about what that field means throughout the code :(

It doesn't produce any problems so far, only that the
function numeric(num,typmod) isn't called when doing a plain
INSERT ... SELECT. It is only called when comparisions where
performed in the SELECT clause of the INSERT on the numeric
attributes. But I need that call to force the rounding and
range check at INSERT time. Otherwise, the values in the
target table will output later with the scale of their
original source table, and that's wrong. Also it would be
possible to insert 1000.0 into a numeric(5,2) attribute, and
that shouldn't be.

Maybe I have to hook for NUMERIC there in parse_relation too.
Up to now I'm compiling the whole thing as loadable module.
I'll check it that's possible when moving it to the builtins.

But in general I think if there is a function with the same
name as a type, that take this type plus another int4
argument, this must be a range checker/padder/truncator or
the like and it should be called before values are assigned
to attributes.

>
> Maybe we should think about how to isolate the type-specific
> interpretation of that attribute field into a type-specific handler
> routine? Ooh, that sounds like a pain...

Noooooooooo

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck(at)debis(dot)com (Jan Wieck) #

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message The Hermit Hacker 1998-12-22 18:50:58 Re: [HACKERS] ecpg patches
Previous Message Thomas G. Lockhart 1998-12-22 16:16:23 Re: [HACKERS] Problems on NUMERIC