Thread: Session variables and C functions

I'm writing a custom C function and one of the things it needs to do is
to be configured from the SQL-land, per user session (different users
have different configurations in different sessions).

I have found (and have used) the SET SESSION command and the
current_setting() function for use with custom_variable_classes
configuration in postgresql.conf, but I'm not sure how to achieve the
same effect from C.

Ideally, the C module would create its own "custom variable class,"
named e.g. "module", then define some setting, e.g. "module.setting".
The users would then execute an SQL command such as "SET SESSION
module.setting='something'", and the module would need to pick this up
in the C function.

Any pointers to how this would be done?

Ivan Voras <ivoras@freebsd.org> writes:
> Ideally, the C module would create its own "custom variable class,"
> named e.g. "module", then define some setting, e.g. "module.setting".
> The users would then execute an SQL command such as "SET SESSION
> module.setting='something'", and the module would need to pick this up
> in the C function.

Plenty of examples of that in contrib/ ...

            regards, tom lane

On 17 November 2011 19:02, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Ivan Voras <ivoras@freebsd.org> writes:
>> Ideally, the C module would create its own "custom variable class,"
>> named e.g. "module", then define some setting, e.g. "module.setting".
>> The users would then execute an SQL command such as "SET SESSION
>> module.setting='something'", and the module would need to pick this up
>> in the C function.
>
> Plenty of examples of that in contrib/ ...

Ok, I found DefineCustom*() and _PG_init() but there's a small
problem: the functions I'm writing are for a user defined typed and
apparently _PG_init() is not called until some operation with the type
is done (e.g. anything with the input/output functions).

This means that DefineCustom*() is not called and PG doesn't know
about the variable until it's too late - I need the session variable
to be settable by the user before input/output - relying on the
"default value" is not enough.

Is there any way to make _PG_init() called earlier, e.g. as soon as
the session is established or at database connection time, something
like that?

Ivan Voras <ivoras@freebsd.org> writes:
> Is there any way to make _PG_init() called earlier, e.g. as soon as
> the session is established or at database connection time, something
> like that?

Preload the library --- see shared/local_preload_libraries configuration
settings.

            regards, tom lane

On 18 November 2011 01:20, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Ivan Voras <ivoras@freebsd.org> writes:
>> Is there any way to make _PG_init() called earlier, e.g. as soon as
>> the session is established or at database connection time, something
>> like that?
>
> Preload the library --- see shared/local_preload_libraries configuration
> settings.

Ok, thanks!