Обсуждение: user types API wish list

Based on my experience with the 7.1.3 user defined type API, here are 
several wishlist items.  (They don't seem to be in 7.2b4 either.)

1) load/unload functions

Some user-defined types and functions require initialization of
global structures.  This can be handled by code like
 static int initialize() {   static int initialized = 0;
   if (!initialized) {     //... do global initialization     initialized = 1;   } }

but this requires that every user-defined function call this 
routine.

But more importantly, there's no way to free resources when the 
database shuts down or loads the shared library.  A memory leak may 
be acceptable, but it's not acceptable for physical or virtual devices
to remain locked.

Proposed solution: define a new data structure that contains
information about the user-defined types/function module.  This
structure would contain routines that are called when the shared
library is loaded or unloaded.  Something like:
 typedef struct {   char *name;   int (*load)();      // returns nonzero on error   int (*unload)();   ... }
PG_USER_TYPE_INFO;
2) autoconfiguring functions

It should be possible for a user-defined type to define itself,
perhaps via a "bootstrap" procedure in the structure mentioned 
above.  The bootstrap procedure could use SPI calls to define 
its own types, functions, operators and secondary indexes.

All but the last item can be done by a SQL script, of course,
but this provides a cleaner mechanism for complex packages. 
(My libpkixpq script, for instance, is over 1000 lines long!)

This function would probably take one or two single arguments,
the path to the shared library being bootstrapped and possibly
the name of the database

3) context information

The final wishlist item, which may not be doable, is to have
some context for the query.  I know that the fcinfo structure
does have a "context" field, but what I would really like to
have is a clean way to determine:
1) the current database
2) the target relation, or NULL if it's a freestanding select clause.

The purpose is to allow the user-defined functions to pull out
metadata maintained elsewhere.  E.g., I might define a catalogue
containing public encryption encryption keys to use when writing
data into certain tables.  My encryption routines could pull this
information out of the database with SPI... if I knew the name of
the table that was the target of the insert or update.

Bear Giles <bear@coyotesong.com> writes:
> Based on my experience with the 7.1.3 user defined type API, here are 
> several wishlist items.  (They don't seem to be in 7.2b4 either.)

> 1) load/unload functions

> But more importantly, there's no way to free resources when the 
> database shuts down or loads the shared library.

You can add an on_shmem_exit or on_proc_exit callback, though I agree
this doesn't cover the scenario of a forcible LOAD replacing an existing
copy of your shlib.

> It should be possible for a user-defined type to define itself,
> perhaps via a "bootstrap" procedure in the structure mentioned 
> above.  The bootstrap procedure could use SPI calls to define 
> its own types, functions, operators and secondary indexes.

> All but the last item can be done by a SQL script, of course,
> but this provides a cleaner mechanism for complex packages. 

Mph.  I think the script approach is better, mainly because it
seems more configurable for mere-mortal end users.  Stuffing things
down into C code doesn't make life easier for anyone.

> The final wishlist item, which may not be doable, is to have
> some context for the query.  I know that the fcinfo structure
> does have a "context" field, but what I would really like to
> have is a clean way to determine:

>  1) the current database

There's a global variable to tell you that.

>  2) the target relation, or NULL if it's a freestanding select clause.

I do not understand what this means.  What is the "target" of
SELECT ... FROM a JOIN b ?

> The purpose is to allow the user-defined functions to pull out
> metadata maintained elsewhere.  E.g., I might define a catalogue
> containing public encryption encryption keys to use when writing
> data into certain tables.  My encryption routines could pull this
> information out of the database with SPI... if I knew the name of
> the table that was the target of the insert or update.

This strikes me as broken by design.  Why should the datatype depend on
the location in which it is stored?  What happens if the intended target
is diverted by a rule or trigger; does that invalidate what you did?
        regards, tom lane