I think the newC function idea is pretty good, however, what would be
great is just one more step of protocol, perhaps an API verson 2 or 3:

One thing than makes writing a non-trivial function a bit problematic,
and perhaps even less efficient, is that the function does not know when
it is first run and when it is finished, and there is no facility to
manage contextual information. This limits external functons having to
be  fairly simple, or overly complex.

I propose that when the newC structure is allocated that a function
specific "Init" function be called, and when the structure is being
freed, calling a "Exit" function. The new C structure should also have a
void pointer that allows persistent information to be passed around.

typedef struct
{
    FmgrInfo   *flinfo;         /* ptr to lookup info used for this call
*/
    Node       *context;        /* pass info about context of call */
    Node       *resultinfo;     /* pass or return extra info about
result */
    bool        isnull;         /* function must set true if result is
NULL */
    short       nargs;          /* # arguments actually passed */
    Datum       arg[FUNC_MAX_ARGS];  /* Arguments passed to function */
    bool        argnull[FUNC_MAX_ARGS];  /* T if arg[i] is actually NULL
*/

    void *    userparam;    /* to be used by he function */

} FunctionCallInfoData;
typedef FunctionCallInfoData* FunctionCallInfo;

The userparam can be used to store data, or a count, or whatever.

Datum function(PG_FUNCTION_ARGS) ;
bool function_Init(PG_FUNCTION_ARGS);
void function_Exit(PG_FUNCTION_ARGS);

This protocol would make writing some really cool features much easier.
As a C++ guy, I could execute "new" at Init and "delete" at Exit. ;-)


Mark.


Tue, 12 Dec 2000 11:27:33 -0500
mlw <markw@mohawksoft.com>
external function proposal for 7.2

As a lurker on the list this post caught my eye somewhat.  I think this
would be excellent functionality to have in postgres, i was considering
doing something like this in a non intruse manner, by manipulating
_init() and _fini functions of shared libraries.  But what you have
described below is a much better interface.  In particular i was looking
at a way of getting async notifications when a row had been inserted, and
pasing out to my other applications enough data, to be able to query back
in for the complete row.

The ability to have an init/exit for an external function would be a big
win, you could even have the init() create a thread for passing results
to, and performing what ever voodoo magic you wanted.

i'll go back to lurking and listening now.



On Tue, 12 Dec 2000, mlw wrote:

> I think the newC function idea is pretty good, however, what would be
> great is just one more step of protocol, perhaps an API verson 2 or 3:
> 
> One thing than makes writing a non-trivial function a bit problematic,
> and perhaps even less efficient, is that the function does not know when
> it is first run and when it is finished, and there is no facility to
> manage contextual information. This limits external functons having to
> be  fairly simple, or overly complex.
> 
> I propose that when the newC structure is allocated that a function
> specific "Init" function be called, and when the structure is being
> freed, calling a "Exit" function. The new C structure should also have a
> void pointer that allows persistent information to be passed around.
> 
> typedef struct
> {
>     FmgrInfo   *flinfo;         /* ptr to lookup info used for this call
> */
>     Node       *context;        /* pass info about context of call */
>     Node       *resultinfo;     /* pass or return extra info about
> result */
>     bool        isnull;         /* function must set true if result is
> NULL */
>     short       nargs;          /* # arguments actually passed */
>     Datum       arg[FUNC_MAX_ARGS];  /* Arguments passed to function */
>     bool        argnull[FUNC_MAX_ARGS];  /* T if arg[i] is actually NULL
> */
> 
>     void *    userparam;    /* to be used by he function */
> 
> } FunctionCallInfoData;
> typedef FunctionCallInfoData* FunctionCallInfo;
> 
> The userparam can be used to store data, or a count, or whatever.
> 
> Datum function(PG_FUNCTION_ARGS) ;
> bool function_Init(PG_FUNCTION_ARGS);
> void function_Exit(PG_FUNCTION_ARGS);
> 
> This protocol would make writing some really cool features much easier.
> As a C++ guy, I could execute "new" at Init and "delete" at Exit. ;-)
> 
> 
> Mark.
> 



PGP key:  http://codex.net/pgp/pgp.asc


Tue, 12 Dec 2000 23:48:50 +0000 (GMT)
Vincent AE Scott <vince@codex.net>
Re: external function proposal for 7.2
Vincent AE Scott wrote:
> 
> As a lurker on the list this post caught my eye somewhat.  I think this
> would be excellent functionality to have in postgres, i was considering
> doing something like this in a non intruse manner, by manipulating
> _init() and _fini functions of shared libraries.  But what you have
> described below is a much better interface.  In particular i was looking
> at a way of getting async notifications when a row had been inserted, and
> pasing out to my other applications enough data, to be able to query back
> in for the complete row.
> 
> The ability to have an init/exit for an external function would be a big
> win, you could even have the init() create a thread for passing results
> to, and performing what ever voodoo magic you wanted.
> 
> i'll go back to lurking and listening now.

I did some code spelunking today. It will not be easy, but I think it is
quite doable. Currently, in the code, a function pointer is passed
around. If I resurrect some of the "old" C code a bit, and do some
merging with the new code we could do it. I just have to find where I
call the exit function.

As far as I can see, the code passes around a function pointer, but
seems to mostly call a small number of localized functions to dispatch
the call. So, I was thinking, rather than pass the function, why not
pass the structure? The old C code stuff does this, why not keep it
around, and pass around the finfo struct instead? and call
(*finfo->funct)(args)?

> 
> On Tue, 12 Dec 2000, mlw wrote:
> 
> > I think the newC function idea is pretty good, however, what would be
> > great is just one more step of protocol, perhaps an API verson 2 or 3:
> >
> > One thing than makes writing a non-trivial function a bit problematic,
> > and perhaps even less efficient, is that the function does not know when
> > it is first run and when it is finished, and there is no facility to
> > manage contextual information. This limits external functons having to
> > be  fairly simple, or overly complex.
> >
> > I propose that when the newC structure is allocated that a function
> > specific "Init" function be called, and when the structure is being
> > freed, calling a "Exit" function. The new C structure should also have a
> > void pointer that allows persistent information to be passed around.
> >
> > typedef struct
> > {
> >     FmgrInfo   *flinfo;         /* ptr to lookup info used for this call
> > */
> >     Node       *context;        /* pass info about context of call */
> >     Node       *resultinfo;     /* pass or return extra info about
> > result */
> >     bool        isnull;         /* function must set true if result is
> > NULL */
> >     short       nargs;          /* # arguments actually passed */
> >     Datum       arg[FUNC_MAX_ARGS];  /* Arguments passed to function */
> >     bool        argnull[FUNC_MAX_ARGS];  /* T if arg[i] is actually NULL
> > */
> >
> >     void *    userparam;    /* to be used by he function */
> >
> > } FunctionCallInfoData;
> > typedef FunctionCallInfoData* FunctionCallInfo;
> >
> > The userparam can be used to store data, or a count, or whatever.
> >
> > Datum function(PG_FUNCTION_ARGS) ;
> > bool function_Init(PG_FUNCTION_ARGS);
> > void function_Exit(PG_FUNCTION_ARGS);
> >
> > This protocol would make writing some really cool features much easier.
> > As a C++ guy, I could execute "new" at Init and "delete" at Exit. ;-)
> >
> >
> > Mark.
> >
> 
> PGP key:  http://codex.net/pgp/pgp.asc

-- 
http://www.mohawksoft.com

Tue, 12 Dec 2000 19:41:36 -0500
mlw <markw@mohawksoft.com>
Re: external function proposal for 7.2
mlw <markw@mohawksoft.com> writes:
> I just have to find where I call the exit function.

That will be the hard part.

FmgrInfo is not currently considered a durable data structure, and I
think you will be in for grief if you try to make any guarantees about
what will happen when one disappears.  If you need a cleanup proc to
be called, I'd suggest looking into registering it to be called at
query completion and/or transaction cleanup/abort, as needed.

Most of the sorts of resources you might need to clean up already have
cleanup mechanisms, so it's not entirely clear that you even *need*
a cleanup proc.  Maybe a different way to say that is that Postgres
already has a pretty well-defined cleanup philosophy, and it's geared
to particular resources (memory, open files, etc) not to individual
called functions.  You should consider swimming with that tide rather
than against it.

I have no objection to adding another field to FmgrInfo for the callee's
use, if you can show an example or two where it'd be useful.  I'm only
concerned about the callback-on-delete part.  That sounds like a recipe
for fragility...

			regards, tom lane

Wed, 13 Dec 2000 00:20:20 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: external function proposal for 7.2 
Tom Lane wrote:
> 
> mlw <markw@mohawksoft.com> writes:
> > I just have to find where I call the exit function.
> 
> That will be the hard part.
> 
> FmgrInfo is not currently considered a durable data structure, and I
> think you will be in for grief if you try to make any guarantees about
> what will happen when one disappears.  If you need a cleanup proc to
> be called, I'd suggest looking into registering it to be called at
> query completion and/or transaction cleanup/abort, as needed.

I think making this structure durable with be fairly 'easy' assuming
that fmgr_info(...) is called only once prior to operations which
requires the function. If this is not the case, then you are 100% right. 

If my assumption is correct, and please correct me if I am wrong, then
all that should be needed to be done is allocate the structure at this
point, and pass it around as the function pointer, and just make sure
that it is always 'FunctionCallInvoke' that calls the function. 

Assuming all my assumptions are correct, (and I can't see how that is
possible ;-), I should also call the Init function at this time.

The big problem is calling the "Exit" function. I am sure that will not
be easily done, or even doable, but we can dream.

> 
> Most of the sorts of resources you might need to clean up already have
> cleanup mechanisms, so it's not entirely clear that you even *need*
> a cleanup proc.  Maybe a different way to say that is that Postgres
> already has a pretty well-defined cleanup philosophy, and it's geared
> to particular resources (memory, open files, etc) not to individual
> called functions.  You should consider swimming with that tide rather
> than against it.

Believe me I understand what you are saying, but, I think Postgres, with
a few tweaks here and there, targeted at efficient extension mechanisms,
could blow away the DB market. I have harped on my text search engine, I
know, but I am not the only one that wants to do these sorts of things,
and it is discouraging how little information is available.

Making it easy for guys like me, to bring functionality into Posgres,
will make Postgres the hands down winner for so many projects that
otherwise would have to resort to using some crappy db library.

Postgres has it all, it has query language, presentation mechanisms,
ODBC, tools, etc. Rather than having to write an application around some
crappy db library, we could write a few neat functions in a powerful SQL
database.

I think a little focus on this area will pay off hugely.

> 
> I have no objection to adding another field to FmgrInfo for the callee's
> use, if you can show an example or two where it'd be useful.  I'm only
> concerned about the callback-on-delete part.  That sounds like a recipe
> for fragility...

Yes, this is a concern for sure, if it is a problem, then, absolutely,
it should be dropped.

-- 
http://www.mohawksoft.com

Wed, 13 Dec 2000 08:11:41 -0500
mlw <markw@mohawksoft.com>
Re: external function proposal for 7.2
<HIGH HORSE>

Let me explain why I think the changes I mentioned are a good thing.

(BTW gateway.mohawksoft.com seems to going to an old IP address that I
haven't had for years, something is strange.)

So, using the IP address, go to this web site. 
	http://216.41.12.226/search.php3

This is a test page, not a production page. I'll leave it up for a few
days barring power outages and other such non-sense.

I have harped about it before, it is a music search system. There is
based on an external daemon which does the full text searching. The
search is completely independent of Postgres, but I use Postgres as the
data source and the presentation system. I use PHP/Apache to interface
with Postgres and display data.

(One added goody about the design is that the text search engine can be
run on a different machine than the Postgres DB, this allows better
scalability with common hardware.)

The code looks like http://216.41.12.226/testmuze.html (please look at
page source, the table strings screw up the page)

It takes three select statements and a temp table, to do what one should
be able to do with a single select statement and good function support.

Please don't get me wrong, I'm not dumping on Postgres at all, but it
would be nice to be able to create this sort of application much easier.
Support for these sorts of constructs will put Postgres in the real
"world class" database category, not just a very strong contender.

It has been suggested that I create a Postgres Index, but that is a lot
of code that many would not be able to justify to use Postgres. If the
function mechanisms were just a little more powerful, this sort of
application would be much easier and more efficient, thus a better
choice.

</HIGH HORSE>      

-- 
http://www.mohawksoft.com

Wed, 13 Dec 2000 09:25:40 -0500
mlw <markw@mohawksoft.com>
Re: external function proposal for 7.2
On Wed, 13 Dec 2000, mlw wrote:

> Assuming all my assumptions are correct, (and I can't see how that is
> possible ;-), I should also call the Init function at this time.
> 
> The big problem is calling the "Exit" function. I am sure that will not
> be easily done, or even doable, but we can dream.


Ok, i don't know the complete syntax of the 'load external function'
stuff, but how about something like :

... ON LOAD CALL 'init()' on UNLOAD CALL 'fini()' ...

when the functions is loaded, you specify a setup function and when it's
unloaded( im not actually sure if this exists) call the finish function.

sorry, if any of that sounds dumb.

-vince
(going back to lurk mode)


PGP key:  http://codex.net/pgp/pgp.asc


Wed, 13 Dec 2000 19:11:12 +0000 (GMT)
Vincent AE Scott <vince@codex.net>
Re: external function proposal for 7.2
mlw wrote:
> 
> Let me explain why I think the changes I mentioned are a good thing.
> 
> (BTW gateway.mohawksoft.com seems to going to an old IP address that I
> haven't had for years, something is strange.)
> 
> So, using the IP address, go to this web site.
>         http://216.41.12.226/search.php3
> 
> This is a test page, not a production page. I'll leave it up for a few
> days barring power outages and other such non-sense.

Does it search from some hidden fields too ?

When I searched for "allison", I got lot of allisons, but also a lot of 
lines with no allison in them, like "The Janet Lawson Quintet: Sunday
Afternoon"

----------
Hannu

Thu, 14 Dec 2000 00:35:56 +0200
Hannu Krosing <hannu@tm.ee>
Re: external function proposal for 7.2
Hannu Krosing wrote:
> 
> mlw wrote:
> >
> > Let me explain why I think the changes I mentioned are a good thing.
> >
> > (BTW gateway.mohawksoft.com seems to going to an old IP address that I
> > haven't had for years, something is strange.)
> >
> > So, using the IP address, go to this web site.
> >         http://216.41.12.226/search.php3
> >
> > This is a test page, not a production page. I'll leave it up for a few
> > days barring power outages and other such non-sense.
> 
> Does it search from some hidden fields too ?
> 
> When I searched for "allison", I got lot of allisons, but also a lot of
> lines with no allison in them, like "The Janet Lawson Quintet: Sunday
> Afternoon"

Actually, that's a metaphone search. "Lawson" metaphones to "lsn" and
allison will also metaphone to "lsn." The metaphone is optional, but
works well when at least two words are specified.

A search of "costello allison" will find exactly what you want.

-- 
http://www.mohawksoft.com

Wed, 13 Dec 2000 17:50:34 -0500
mlw <markw@mohawksoft.com>
Re: external function proposal for 7.2