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