This post will only focus on creating and debugging a c function that you can create in pg.
To read up on what has already been typed up on the nitty gritty of pg check out these links.
General all encompassing ->http://www.postgresql.org/docs/8.3/interactive/index.html
Stored Functions in c ->http://www.postgresql.org/docs/8.3/interactive/xfunc-c.html
I run my pg server on old desktop pc hardware. The CPU is 800MHZ and there is one gig of ram. The box is running FreeBSD 7.0 32-bit. Pg is version 8.3.5.
Here is my super simple and almost pointless C function that I used to grasp the basics creating and debugging a c function in pg.
/* foo.c*/To create a c function and compile it follow the steps I have outlined bellow.
int32 arg = PG_GETARG_INT32(0);
while (arg >= 0)
Prerequisites for how I did this:
- Pg server compiled with --enable-debug (NOT 100% sure if this is needed yet need to double check)
- The pg server up and running and you are able to connect to it.
- Make sure you have the pg source code on the same machine you are compiling on. To compile your c function you need some of the pg source code. I'm doing all my compiles on FreeBSD 7.0 so if you are using another OS look here http://www.postgresql.org/docs/8.3/interactive/xfunc-c.html in section titled 34.9.6. Compiling and Linking Dynamically-Loaded Functions.
- I'm logged in as the postgres UNIX account when creating my file and compiling. I'm also logged in as the postgres account when connecting to the database via psql command line tool.
- You have a c compiler and gdb installed.
- Copy the code above into file foo.c and save it.
- Compile foo.c with debug symbols by adding the -g option. For example on FreeBSD you do this gcc -g -fpic -c foo.c -I/usr/home/postgres/src/include and then gcc -g -shared -o foo.so foo.o. You shouldn't get any errors if all goes well.
- Make sure your foo.so is in a location that the pg server can see. If postgres is the account running the server which most likely it will be make sure you put the file in a location that postgres has access to. I put mine in a directory that postgres owns full rights to.
- Execute the following from psql.
CREATE FUNCTION public.c_loop(integer) RETURNS integerThis creates a stored function in the database that we can call via SQL to invoke our C function.
AS '/usr/home/postgres/foo', 'c_loop' /*Change to point to your foo file location*/
LANGUAGE C STRICT;
- To make sure all is well with our new database function execute select public.c_loop(10); from psql. It should execute to completion and return -1.
- Now onto the debugging. Log into psql and LOAD the C function like this -> load '/usr/home/postgres/foo'; This will cause the pg server to load the object so we can debug it.
- Find the back end process that you are connected to in psql by running select pg_backend_pid(); while in the same psql session.
- Open another terminal session and run gdb for the pid returned from step 7 like this -> gdb /usr/local/pgsql/bin/postgres 27069
- Now from the gdb session you started in step 8 set a break point in the c function by typing -> b c_loop
- Now type continue or cont
- Go back to your psql session and execute the function via SQL like this -> SELECT public.c_loop(10);
- Go back to your gdb session and check that you hit the break point. You can now step through the C function in gdb. Type s and hit enter and you should be stepping through the code.