Обсуждение: 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