Defined C function gives nondeterministic results - Mailing list 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                                       *