Defined C function gives nondeterministic results
От | Patrick L. Nolan |
---|---|
Тема | Defined C function gives nondeterministic results |
Дата | |
Msg-id | 200203020755.g227tc215486@razzle.Stanford.EDU обсуждение исходный текст |
Ответы |
Re: Defined C function gives nondeterministic results
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-general |
We just installed Postgresql 7.2 on Redhat Linux 7.1. I'm trying to write my first function extension in C. I think I'm following the rules, but it gives nonsense results. The extension is added by this statement: create or replace function distf (float4, float4, float4, float4) returns float4 as '/home/pln/pg/mylib.so', 'distf' language C; Here's the C code: #include "postgres.h" #include "fmgr.h" #define fac 57.2957795 /* pi/180 */ PG_FUNCTION_INFO_V1(distf); Datum distf(PG_FUNCTION_ARGS) { float4 ra1 = PG_GETARG_FLOAT4(0); /* Extract the 4 arguments */ float4 dec1 = PG_GETARG_FLOAT4(1); float4 ra2 = PG_GETARG_FLOAT4(2); float4 dec2 = PG_GETARG_FLOAT4(3); double dot; float4 angle; dot = cos((double)(ra1/fac))*cos((double)(ra2/fac))+ sin((double)(ra1/fac))*sin((double)(ra2/fac))*cos((double)((dec1-dec2)/fac)); angle = fac * acos(dot); PG_RETURN_FLOAT4(angle); } There are a bunch of extra casts in there just to make sure that there is no ambiguity about types. The function is to calculate the arc distance between two points on a sphere specified by latitude and longitude. It compiles into the .so file with these commands: gcc -fpic -I/usr/local/pgsql/include/server -c distf.c gcc -shared -o mylib.so distf.o The CREATE OR REPLACE goes cleanly. To test it there's a table like this: pln=# \d foo Table "foo" Column | Type | Modifiers --------+------+----------- a | real | b | real | c | real | d | real | pln=# select * from foo; a | b | c | d ---+---+---+--- 1 | 2 | 1 | 2 1 | 2 | 1 | 2 (2 rows) Here comes the test: pln=# select a,b,c,d,distf(a,b,c,d) from foo; a | b | c | d | distf ---+---+---+---+-------- 1 | 2 | 1 | 2 | 837894 1 | 2 | 1 | 2 | 720552 (2 rows) pln=# select a,b,c,d,distf(a,b,c,d) from foo; a | b | c | d | distf ---+---+---+---+-------- 1 | 2 | 1 | 2 | 603210 1 | 2 | 1 | 2 | 485868 There are three or four impossible things there. The distf numbers are way out of range. In this case they should be zero. The two rows in each test should return the same distf. Running the same query twice should give the same answer. To save space I didn't show any more output, but when I keep running the same query it settles into a pattern. The distf values repeat themselves with a period of 8 rows. That's just too weird for me. I must be missing something really basic here. Go ahead, tell me how dumb I am. --------------------------------- By the way, I could mention some other stuff I did and how it frustrated me. 1. I thought there might be an error log file with some useful information. Couldn't find one. There was some information about turning on a syslog facility. I copied the example right out of the official web page, and it told me I had the wrong syntax in postgresql.conf. 2. I tried using the old version-0 C syntax, treating the arguments and return value as pointers. The results were about the same. 3. I made the function return 0 instead of the calculated value. The results were still bogus in a similar way. 4. I wondered where standard output would go, so I put in a printf statement. It crashed the postmaster. 5. I tried to log some debugging information to my own file by putting in an fprintf statement. That crashed the postmaster too. 6. I got really paranoid about whether or not my changes to the shared library were getting noticed. The documents seem to indicate that all it takes is a LOAD. I'm not so sure. I used this procedure: LOAD CREATE OR REPLACE LOAD I'm still not sure that did the job. There seemed to be some non- reproducible behavior. I ended up using a separate psql session for each test. That seemed to smooth things out. Aieee! * Patrick L. Nolan * * W. W. Hansen Experimental Physics Laboratory (HEPL) * * Stanford University *
В списке pgsql-general по дате отправления: