[HACKERS] MemoryContextSwitchTo() confusion

[Dan Searle]

Hi,

I've written a custom C-language function that takes a SQL select 
statement as it's input parameter, runs the query using the SPI 
interface, iterates over all the results of the select using a cursor 
and collates the data using a tsearch type binary tree, then walks the 
tree to create a flat vector of ordered nodes all in the first call 
(i.e. SRF_IS_FIRSTCALL()). It then contructs tuples, turns them into 
Datums and returns all it's records with subsequent calls.


I've used the example code in the documentation, and used the doxygen 
docs for tablefunc.c as guidelines.


Everything seems to work fine, except on the final call it has no more 
data to return so cleans up all it's internal data structures and 
returns with SRF_RETURN_DONE(funcctx).


I had to fiddle about with switching memory contexts rather a lot to 
make it work this far, but I'm only guessing as to when it's appropriate 
to call MemoryContextSwitchTo(), and to which context to switch to. I 
had a few SEGV's and had to add a few MemoryContextSwitchTo() calls in 
various places, however, can someone please explain the need for 
MemoryContextSwitchTo() and when it's needed, as I'm basically guessing 
and I think it's the reason for the SEGV after the final call to 
SRF_RETURN_DONE() after it's returned all it's tuples.


I have verified that all the code seems to work, and that it is after 
the final call to SRF_RETURN_DONE() that the SEGV happens by writing 
debug strings to a file handle.


I can provide a copy of the sourece code, but I thought it best to ask 
first.


Regards, Dan...



Scanned for viruses, spam and offensive content by CensorNet MailSafe

Professional Web  E-mail Filtering from www.censornet.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Collating records based on a custom group by (aggregate like) function

[Dan Searle]

Hi,

I want to create a custom function/query (using the C API?) that
collates records from a record set (takes multiple records as input)
and produces a set of records (returns multiple records).

The Postgres docs only give examples of how to create a custom
function that returns multiple records (sets of tuples), but does not
show how you can create a function that can take a set of tuples as
input and scan them.

This requirement has come about because I want to scan an event log
table and collate individual log entries (group them) depending on a
custom notion of equality.

For instance, if we had the table events:

timestamp, event
00:00:00, a
00:00:01, b
00:00:02, a
00:10:00, b
00:10:01, b

The custom function/query should output:

event, start, end
a, 00:00:00, 00:00:02
b, 00:00:01, 00:00:01
b, 00:10:00, 00:10:01

It's like grouping the events in the log table by name and timestamp,
however the timestamp grouping is not based simply on equality, but
the notion that if two events happen within 5 minutes of each other
they are collated into the same event bucket.

Another way to look at the problem would be to create a special
grouping function in a GROUP BY clause, e.g.

SELECT name, min(timestamp), max(timestamp) FROM
events GROUP BY name, enumerate_event(name, timestamp);

Where enumerate_event would take the name (a or b as in the
example previously), and the timestamp for the event, then return a
unique collated event ID for each distinct event bucket. The
problem here is that the enumerate_event function would need to save
it's state (a temporary list of active events) between calls.

I can't see an easy way to make a custom function using the Postgres C
API that can (for each transaction) setup an internal state,
repeatedly use the state during the execution of a query, and then
free it's state when the query finishes.

I've racked my brain about this but can't think of a simple solution,
even though this appears to be a simple problem, any suggestions much
appreciated.

Regards, Dan...

--

Dan Searle
Adelix Ltd
[EMAIL PROTECTED] web: www.adelix.com
tel: 0845 230 9590 / fax: 0845 230 9591 / support: 0845 230 9592
snail: The Old Post Office, Bristol Rd, Hambrook, Bristol BS16 1RY. UK.

Adelix Ltd is a registered company in England  Wales No. 4232156
VAT registration number 779 4232 91
Adelix Ltd is BS EN ISO 9001:2000 Certified (No. GB 12763)

Any views expressed in this email communication are those
of the individual sender, except where the sender specifically states
them to be the views of a member of Adelix Ltd.  Adelix Ltd. does not
represent, warrant or guarantee that the integrity of this communication
has been maintained nor that the communication is free of errors or
interference.



Scanned for viruses, spam and offensive content by CensorNet MailSafe

Professional Web  E-mail Filtering from www.censornet.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers