amigrave commented 4 months ago

Hi Luis,

Thanks a lot for your work on pgxcrown !

I was wondering if pgxcrown could be used --and if it has any advantage to be used-- in the context of a PosgreSQL extension using the hooks pointers ?

Basically I'd like to be able to create a PostgreSQL extension using nim where I can register a pointer to a function. Here's a very short example of an extension which allows to add a delay when a postgresql authentication fails: https://github.com/postgres/postgres/blob/master/contrib/auth_delay/auth_delay.c

Such extensions are supposed to save the current pointer (of NULL if any), install a pointer to our own function to hook on, then restore the previously installed pointer on _PG_fini. For some reasons this auth_delay extension does not restore properly the pointer but here's another example where the extensions properly restore the previous pointer hook: https://github.com/postgres/postgres/blob/master/contrib/passwordcheck/passwordcheck.c#L143-L148

I was struggling converting those simple C examples in nim and my problem started with the function name _PG_init for whose the nim compiler will parse error when I use the exportc pragma. I was losing hope when I stumble upon your project, of course if this use case goes beyond the scope of pgxcrown I would totally understand, but if the first example could be written using pgxcrown as a helper, would you mind providing a small example ?

Cheers !

luisacosta828 commented 4 months ago

Hi Fabien, currently pgxcrown doesn't support postgres hooks, but I'm planning to add that feature.

amigrave commented 4 months ago

Hi Fabien, currently pgxcrown doesn't support postgres hooks, but I'm planning to add that feature.

Good news ! I don't have enough experience with nim at the moment to work with it's metaprograming techniques which I'm currently learning. However I'm willing to help so don't hesitate to tell me if there's anything I can do (or try to do :). I'm currently trying to integrate pgxs.mk (pg_config --pgxs) in compiler.nim

luisacosta828 commented 3 months ago

pgxcrown new commands

Usage

List available hooks


pgxtool available-hooks
    * emit_log
    * post_parse_analyze

Create a hook


# this command will create a template for that hook, you just need to focus on edit the custom proc.

#For example: this hook won't allow you to delete from a table without a where clause 

pgxtool create-hook post_parse_analyze

import
  pgxcrown

PG_MODULE_MAGIC
ActivateHooks
var post_parse_analyze_hook {. original_hook .}: post_parse_analyze_hook_type
var prev_post_parse_analyze {. user_hook .}: post_parse_analyze_hook_type

proc custom_proc(pstate: ptr ParseState; query: ptr Query;
                 jstate: ptr JumbleState) {.cdecl.} =
   if query[].commandType == CMDType.CMD_DELETE:
    report(error, "Cannot execute delete without where", "","")

proc pg_init() {.exportc: "_PG_init", pginitexport.} =
  prev_post_parse_analyze = post_parse_analyze_hook
  post_parse_analyze_hook = custom_proc

proc pg_fini() {.exportc: "_PG_fini".} =
  post_parse_analyze_hook = prev_post_parse_analyze

Compile your hook and install it


# don't need to use pgxs

pgxtool build-extension post_parse_analyze

sudo cp library.so $(pg_config --pkglibdir)

Test your hook


postgres=# insert into dummy select * from generate_series(1, 10000);
INSERT 0 10000
postgres=# delete from dummy;
ERROR:  Cannot execute delete without where
postgres=# select count(*) from dummy;
 count 
-------
 10000
(1 row)

luisacosta828 / pgxcrown