Обсуждение: Bad behaviour when inserting unspecified variable length datatypes

It took me a while to figure out what was going on, but I think I've
figured it out.

Lets say you have your own variable length datatype called
'MY_DATATYPE'.

CREATE TABLE test_table (myint integer, mydata MY_DATATYPE);
INSERT INTO test_table VALUES (1);

At this point, I'd expect there to be one row in test table. The myint
column will have the value one, and the mydata column will have the
value NULL.

This doesnt appear to be the case.  It seems that the mydata column will
have a structure that looks like a '-'::TEXT structure (ie. the first 4
bytes are an int representing 5, and the 5th byte is the ASCII '-').

This is really bad because a "SELECT * FROM test_table" will send this
weird structure to MY_DATATYPE's OUTPUT function.  Since this weird
structure isn't really a MY_DATATYPE structure, it causes problems.

This happens even if you explictly set MY_DATATYPE's DEFAULT to NULL.

dave

Dave Blasby <dblasby@refractions.net> writes:
> CREATE TABLE test_table (myint integer, mydata MY_DATATYPE);
> INSERT INTO test_table VALUES (1);

> At this point, I'd expect there to be one row in test table. The myint
> column will have the value one, and the mydata column will have the
> value NULL.

Check...

> This doesnt appear to be the case.  It seems that the mydata column will
> have a structure that looks like a '-'::TEXT structure (ie. the first 4
> bytes are an int representing 5, and the 5th byte is the ASCII '-').

Uh, what did your CREATE TYPE command look like, exactly?  This sounds
like you specified a default value for the datatype.

Maybe you need to show us your datatype's I/O functions, too.  Since
this works perfectly fine for the standard variable-length datatypes,
it's hard to arrive at any other conclusion than that your custom
datatype code is erroneous.  But there's not enough info here to figure
out just what is wrong with it.
        regards, tom lane

> Uh, what did your CREATE TYPE command look like, exactly?  This sounds
> like you specified a default value for the datatype.

Okay, here's two examples;

CREATE TYPE WKB (internallength = VARIABLE,input =  WKB_in,output =  WKB_out,storage= extended
);


CREATE TYPE GEOMETRY (alignment = double,internallength = VARIABLE,input =  geometry_in,output =  geometry_out,storage
=main
 
);


I've tried the WKB type with a "DEFAULT = NULL" clause and all the
different storage types.  The same problem occurs everytime.

Here's the create function statements for the _in and _out functions;

create function WKB_in(opaque)RETURNS WKB  AS '@MODULE_FILENAME@','WKB_in'  LANGUAGE 'c' with (isstrict);

create function WKB_out(opaque)RETURNS opaque  AS '@MODULE_FILENAME@','WKB_out'  LANGUAGE 'c' with (isstrict);

create function geometry_in(opaque)RETURNS GEOMETRY  AS '@MODULE_FILENAME@'  LANGUAGE 'c' with (isstrict);

create function geometry_out(opaque)RETURNS opaque  AS '@MODULE_FILENAME@'  LANGUAGE 'c' with (isstrict);



> Maybe you need to show us your datatype's I/O functions, too.

I dont thing they're important.  WKB_in and geometry_in are *never*
called, and WKB_out and geometry_out are called with bad values.  Only
one line of my code is executed in the _out functions.  
WellKnownBinary              *WKB = (WellKnownBinary *) 
PG_DETOAST_DATUM(PG_GETARG_DATUM(0));
    or
GEOMETRY           *geom1 = (GEOMETRY *) 
PG_DETOAST_DATUM(PG_GETARG_DATUM(0));

(See below for a simpler example)

> Since
> this works perfectly fine for the standard variable-length datatypes,

Yes, on system (7.1.2 under solaris) the following works;create table ttt (i integer, mytext text);insert into ttt
values(1);select * from ttt;i | mytext 
 
---+----1 | 
(1 row)


Here's the simplest example I can come up with to show the problem;

create function WKB_in2(opaque)       RETURNS WKB  AS
'/data1/Refractions/Projects/PostGIS/work_dave/postgis/libpostgis.so.0.5','WKB_in2'  LANGUAGE 'c' with (isstrict);

create function WKB_out2(opaque)       RETURNS opaque  AS
'/data1/Refractions/Projects/PostGIS/work_dave/postgis/libpostgis.so.0.5','WKB_out2'  LANGUAGE 'c' with (isstrict);


CREATE TYPE WKB (       internallength = VARIABLE,       input =  WKB_in2,       output =  WKB_out2,       storage=
extended
);


dave=#  create table m (i integer, mywkb wkb);
dave=#  insert into m values (1);
dave=#  select * from m;i | mywkb 
---+-------1 | me
(1 row)


You'll also get this output from the printf in WKB_out (ASCII 45 is the
"-" char);
WKB_out2: WKB has length 5 and 1st value 45


typedef struct Well_known_bin {int32 size;    // total size of this structureunsigned char  data[1]; //THIS HOLDS
VARIABLELENGTH DATA
 
} WellKnownBinary;



PG_FUNCTION_INFO_V1(WKB_in2);
Datum WKB_in2(PG_FUNCTION_ARGS)
{char               *str = PG_GETARG_CSTRING(0);printf("I never get here!\n");
PG_RETURN_NULL();
}

PG_FUNCTION_INFO_V1(WKB_out2);
Datum WKB_out2(PG_FUNCTION_ARGS)
{WellKnownBinary              *WKB = (WellKnownBinary *) 
PG_DETOAST_DATUM(PG_GETARG_DATUM(0));char                    *result;
printf("WKB_out2: WKB has length %i and 1st value %i\n", WKB->size, 
(int) ((char *)WKB)[4] );
// return something
result = palloc(3);result[0] = 'm';result[1] = 'e';result[2] = 0;
PG_RETURN_CSTRING(result);
}

>> Uh, what did your CREATE TYPE command look like, exactly?  This sounds
>> like you specified a default value for the datatype.

> [ no, he didn't ]

Now that I look at it, CREATE TYPE is totally whacked out about default
values for user-defined datatypes.  The reason the system-defined types
all behave properly is that they are defined (in pg_type.h) with NULL
entries in the typdefault field of pg_type.  But CREATE TYPE doesn't
fill in a NULL when it sees you haven't specified a default!  Look at
TypeCreate in pg_type.c:
   /*    * initialize the default value for this type.    */   values[i] = DirectFunctionCall1(textin,        /* 17 */
         CStringGetDatum(defaultTypeValue ? defaultTypeValue : "-"));
 

Yech, where'd that come from?

It turns out this doesn't hurt fixed-length types unless their length is
1, because there is a test in get_typdefault to verify that the stored
value is the expected length.  But for var-length types the "-" comes
through.

A short-term workaround for Dave is to explicitly set pg_type.typdefault
to NULL after creating his type, but clearly we gotta clean this up.
ISTM that TypeCreate should set typdefault to NULL if it's passed a null
default-value item.

Another problem here is that there's no mechanism that causes the value
stored in typdefault to be correctly converted to the destination type.
DefineType and TypeCreate act as though the value is just a text string,
but then get_typdefault seems to think that it should find a text object
containing the *internal* representation of the desired value.  Yech.
For example, to make an integer type with a default of 42, I'd have to
writedefault = '\0\0\0\52'
(which might or might not even work because of the nulls, and certainly
would not do what I wanted on a machine of the other endianness).

I propose that we define typdefault as containing the *external*
representation of the desired value, and have get_typdefault apply the
type's input conversion function to produce a Datum.  Any objections?
        regards, tom lane

I wrote:
> I propose that we define typdefault as containing the *external*
> representation of the desired value, and have get_typdefault apply the
> type's input conversion function to produce a Datum.  Any objections?

This change is committed for 7.2.
        regards, tom lane