Arrays and FFTW
This is a repost. I originally posted to the novice list,
but it seems to be a very low traffic list, and no one seems
to have noticed my message. Then I posted to the General
List, where I was kindly advised to try the hackers list. So
here I am.
------------------------------------------------------------
Hello! I'm a PGSQL newbie. I have installed postgres only a
few days ago in the attempt to use it to solve a specific
problem.
I am using (trying to...) PGSQL to store a database of
digital signals. Each signal is a sequence of (signal_id,
timestamp, double) tuples. I've managed to write resampling
alogrithms in pl/pgsql, and I don't think it would be hard
to write autoregressive filters. However, now I'm confronted
with the need to compute the power spectra of my signals. I
would like to use FFTW, which is lightning fast on my
machine. Has anyone already written FFTW bindings for
PostgreSQL?
If I have to write the code myself, I would need to create a
database function calling code from a C module. Such code
would have to operate on real and complex float arrays. I
understand how I could use a pl/pgsql function to create a
new table where each signal is stored as a (signal_id,
double array) tuple, but how am I supposed to pass such
arrays to a C function? How are postgres arrays actually
implemented in memory? In short, I need someone to get me
started on writing an FFTW binding for pgsql, in none is
already available.
Thank you in advance for any help you can give me. And
double thumbs up to the developers: running PostgreSQL for
the first time is an epiphanic experience. I want to study
the ins and outs of it rapidly so that, hopefully, in a
while, I will be able to contribute to the pgsql project.
Alex Baretta
Alessandro Baretta <alex@baretta.com> writes:
If I have to write the code myself, I would need to create a
database function calling code from a C module. Such code
would have to operate on real and complex float arrays. I
understand how I could use a pl/pgsql function to create a
new table where each signal is stored as a (signal_id,
double array) tuple, but how am I supposed to pass such
arrays to a C function? How are postgres arrays actually
implemented in memory? In short, I need someone to get me
started on writing an FFTW binding for pgsql, in none is
already available.
You're intending to store each complete signal as a big array in one
row? That could get a bit ugly if the signals are very large (many
megabytes). But if you want to do it that way, I think the coding
would be pretty straightforward. See src/backend/utils/adt/float.c
for some examples of C functions that process arrays of floats ---
the "FLOAT AGGREGATE OPERATORS" section is relevant.
src/include/utils/array.h is relevant reading as well.
regards, tom lane
Tom Lane wrote:
You're intending to store each complete signal as a big array in one
row? That could get a bit ugly if the signals are very large (many
megabytes). But if you want to do it that way, I think the coding
would be pretty straightforward. See src/backend/utils/adt/float.c
for some examples of C functions that process arrays of floats ---
the "FLOAT AGGREGATE OPERATORS" section is relevant.
src/include/utils/array.h is relevant reading as well.
Ok. I'll take a look at them.
What is going to be ugly, exactly? To be precise, my
application requires storing samples obtained at random
intervals, and resampling them a 1Hz. My pgsql installation
is already managing 3 months worth of data (1.8 Mrows),
imported from MS/Excel x-( . But power spectra are only
intersting for a moving window about 8-12 hours long (9.1h
yields an array of 2**15 samples, whose FFT can be computed
in about 2ms on an Athlon 1200).
My problem is that I need to know how the arrays are
represented in memory, and how they are passed to my
function. Also, I'll need to define an abstract type for the
execution plan of the dft alorithm--yes, fftw uses execution
plans just like PostgreSQL :-) --and I will need to store
them in the database. This seems a little tricky to me. As I
stated earlier, I have already learned SQL and PL/pgSQL
functions, but I still have to learn how to create a C
function. Anyway, I'm working on it, and I hope to be able
to share my code with you guys, for possible inclusion in
the distribution.
Alex
Alessandro Baretta wrote:
My problem is that I need to know how the arrays are represented in
memory, and how they are passed to my function. Also, I'll need to
define an abstract type for the execution plan of the dft alorithm--yes,
fftw uses execution plans just like PostgreSQL :-) --and I will need to
store them in the database. This seems a little tricky to me. As I
stated earlier, I have already learned SQL and PL/pgSQL functions, but I
still have to learn how to create a C function. Anyway, I'm working on
it, and I hope to be able to share my code with you guys, for possible
inclusion in the distribution.
Also see contrib/array, contrib/dblink, and contrib/intarray (and
probably others under contrib) for user function examples which handle
arrays. And be sure to see:
http://www.postgresql.org/idocs/index.php?xfunc-c.html
for general information on user C language functions.
Joe
Joe Conway wrote:
Also see contrib/array, contrib/dblink, and contrib/intarray (and
probably others under contrib) for user function examples which handle
arrays. And be sure to see:
http://www.postgresql.org/idocs/index.php?xfunc-c.html
for general information on user C language functions.Joe
I read that already, but it does not mention how arrays are
passed to a C function. I need some examples. Now I have
some places to look for. Now, I'll download the source and
take a look at it. Hopefully, it will not be too difficult
to figure out how arrays are passed to C functions. The docs
*seem* to imply that I will get a blob of memory where the
first 4 bytes are the size_t of the array, and the rest is
the array itself. However, this is not clearly stated, so I
could have misunderstood this point. PostgreSQL is a great
thing, but the documentation is a little too tight in a few
spots. I managed to wade through most of it--I'd hate to get
an RTFM from you guys--but I wish it were a little more
explicative.
Thanks to everybody for the kind help. Good work. I'll get
back to mine momentarily.
Alex
Matthew T. O'Connor wrote:
Why are you using plpgsql for this?
Since I prefer to store my data in a database than in a
file, because this allows me to handle concurrency in a very
natural way, I want to keep my application code, insofar as
possible together with my data.
You can write it in C.
I could use a Turing Machine if a cared to. The fact is that
C is not my favorite language. I do not feel compelled to
use C, in this case, except to allow me to interface pgsql
with FFTW, the Fastest Fourier Transform in the West.
Alex