On Fri, Jul 21, 2017 at 12:19 PM, Fabrízio de Royes Mello < fabriziome...@gmail.com> wrote: > > > On Fri, Jul 21, 2017 at 10:58 AM, Yugo Nagata <nag...@sraoss.co.jp> wrote: > > > > On Fri, 21 Jul 2017 10:31:57 -0300 > > Fabrízio de Royes Mello <fabriziome...@gmail.com> wrote: > > > > > On Fri, Jul 21, 2017 at 9:35 AM, Yugo Nagata <nag...@sraoss.co.jp> wrote: > > > > > > > > On Fri, 21 Jul 2017 09:53:19 +0800 > > > > Craig Ringer <cr...@2ndquadrant.com> wrote: > > > > > > > > > On 21 July 2017 at 08:42, Robert Haas <robertmh...@gmail.com> wrote: > > > > > > > > > > > On Thu, Jul 20, 2017 at 8:27 AM, Fabrízio de Royes Mello > > > > > > <fabriziome...@gmail.com> wrote: > > > > > > > I'm not sure your real needs but doesn't it material for improve > > > Event > > > > > > > Triggers??? > > > > > > > > > > > > I've thought about that, too. One problem is what to do if the user > > > > > > hits ^C while the event trigger procedure is running. If you respond > > > > > > to that by killing the event trigger and letting the user issue > > > > > > commands, then the event trigger can't be used for security or > > > > > > auditing purposes because the user might prevent it from doing > > > > > > whatever it's intended to do with a well-timed interrupt. If you > > > > > > ignore ^C or make it turn into FATAL, then a poorly-crafted trigger > > > > > > can lock users out of the database. Maybe that's OK. We could say > > > > > > "well, if you lock yourself out of the database with your logon > > > > > > trigger, you get to shut down the database and restart in single user > > > > > > mode to recover". > > > > > > > > > > > > A hook, as proposed here, is a lot simpler and lacks these concerns. > > > > > > Installing code in C into the database is intrinsically risky > > > > > > anywhere, and not any moreso here than elsewhere. But it's also less > > > > > > accessible to the average user. > > > > > > <http://www.postgresql.org/mailpref/pgsql-hackers> > > > > > > > > > > > > > > > I'd favour the c hook personally. It's a lot more flexible, and can be > > > used > > > > > by an extension to implement trigger-like behaviour if anyone wants it, > > > > > including the extension's choice of error handling decisions. > > > > > > > > > > It's also a lot simpler and less intrusive for core. Which is nice > > > where we > > > > > don't have something that we don't have anything compelling destined for > > > > > core that needs it. (I want to add a bunch of hooks in the logical > > > > > replication code in pg11 for similar reasons, and so features like DDL > > > > > replication can be prototyped as extensions more practically). > > > > > > > > > > > I agree with you both... > > > > > > > > > > > That said, isn't ExecutorStart_hook + ProcessUtility_hook able to serve > > > the > > > > > same job as a session-start hook, albeit at slightly higher overhead? > > > You > > > > > can just test to see if your initial tasks have run yet. > > > > > > > > Thank you for your suggestion. Certainly, we can do the similar job of a > > > > session-start hook using these existing hooks, although these hooks are > > > > triggered when the first query is executed not when the session is > > > started. > > > > Now I come to think that an additional hook is not need. > > > > > > > > > > As Nagata said hooks proposed by Craing will happens only when the first > > > query is called so I don't know how it works for session start... are we > > > missing something? > > > > Yes, ExecutorStart_hook + ProcessUtility_hook is not strictly same as > > session_start hook. If a query is issued a long time since the session start, > > the timing the hook happens is largely deviated. It is no problem if we only > > want do something once at the session start, but it might be problem if > > we want to record the timestamp of the session start, for example. > > > > > > > > If we're going to add this hook what about add a session end hook also? > > > > If someone want the session-start hook, he might want this too. > > > > Well if someone wants here are the patches... I just did a minor fix and cleanup in your previous session_start sample and provide both samples into the same patch. >
I made a mistake on previous patch... now the attached three patches in their correct orders. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL >> Timbira: http://www.timbira.com.br >> Blog: http://fabriziomello.github.io >> Linkedin: http://br.linkedin.com/in/fabriziomello >> Twitter: http://twitter.com/fabriziomello >> Github: http://github.com/fabriziomello
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c index b8d860e..7a1fa3b 100644 --- a/src/backend/tcop/postgres.c +++ b/src/backend/tcop/postgres.c @@ -160,6 +160,9 @@ static bool RecoveryConflictPending = false; static bool RecoveryConflictRetryable = true; static ProcSignalReason RecoveryConflictReason; +/* Hook for plugins to get control at start of session */ +session_start_hook_type session_start_hook = NULL; + /* ---------------------------------------------------------------- * decls for routines only used in this file * ---------------------------------------------------------------- @@ -3808,6 +3811,9 @@ PostgresMain(int argc, char *argv[], if (!IsUnderPostmaster) PgStartTime = GetCurrentTimestamp(); + if (session_start_hook) + (*session_start_hook) (dbname, username); + /* * POSTGRES main processing loop begins here * diff --git a/src/include/tcop/tcopprot.h b/src/include/tcop/tcopprot.h index f8c535c..d349592 100644 --- a/src/include/tcop/tcopprot.h +++ b/src/include/tcop/tcopprot.h @@ -35,6 +35,11 @@ extern PGDLLIMPORT const char *debug_query_string; extern int max_stack_depth; extern int PostAuthDelay; +/* Hook for plugins to get control at start of session */ +typedef void (*session_start_hook_type) (const char *dbname, + const char *username); +extern PGDLLIMPORT session_start_hook_type session_start_hook; + /* GUC-configurable parameters */ typedef enum
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c index 7a1fa3b..ba04036 100644 --- a/src/backend/tcop/postgres.c +++ b/src/backend/tcop/postgres.c @@ -160,9 +160,9 @@ static bool RecoveryConflictPending = false; static bool RecoveryConflictRetryable = true; static ProcSignalReason RecoveryConflictReason; -/* Hook for plugins to get control at start of session */ +/* Hook for plugins to get control at start or end of session */ session_start_hook_type session_start_hook = NULL; - +session_end_hook_type session_end_hook = NULL; /* ---------------------------------------------------------------- * decls for routines only used in this file * ---------------------------------------------------------------- @@ -185,6 +185,7 @@ static bool IsTransactionExitStmtList(List *pstmts); static bool IsTransactionStmtList(List *pstmts); static void drop_unnamed_stmt(void); static void log_disconnections(int code, Datum arg); +static void do_session_end_hook(int code, Datum arg); /* ---------------------------------------------------------------- @@ -3815,6 +3816,12 @@ PostgresMain(int argc, char *argv[], (*session_start_hook) (dbname, username); /* + * Setup handler to session end hook + */ + if (IsUnderPostmaster) + on_proc_exit(do_session_end_hook, 0); + + /* * POSTGRES main processing loop begins here * * If an exception is encountered, processing resumes here so we abort the @@ -4520,3 +4527,15 @@ log_disconnections(int code, Datum arg) port->user_name, port->database_name, port->remote_host, port->remote_port[0] ? " port=" : "", port->remote_port))); } + +/* + * on_proc_exit handler to call session end hook + */ +static void +do_session_end_hook(int code, Datum arg) +{ + Port *port = MyProcPort; + + if (session_end_hook) + (*session_end_hook) (port->database_name, port->user_name); +} diff --git a/src/include/tcop/tcopprot.h b/src/include/tcop/tcopprot.h index d349592..b7fb8c3 100644 --- a/src/include/tcop/tcopprot.h +++ b/src/include/tcop/tcopprot.h @@ -35,10 +35,14 @@ extern PGDLLIMPORT const char *debug_query_string; extern int max_stack_depth; extern int PostAuthDelay; -/* Hook for plugins to get control at start of session */ +/* Hook for plugins to get control at start and end of session */ typedef void (*session_start_hook_type) (const char *dbname, const char *username); +typedef void (*session_end_hook_type) (const char *dbname, + const char *username); + extern PGDLLIMPORT session_start_hook_type session_start_hook; +extern PGDLLIMPORT session_end_hook_type session_end_hook; /* GUC-configurable parameters */
diff --git a/contrib/session_end/Makefile b/contrib/session_end/Makefile new file mode 100644 index 0000000..a2c0a72 --- /dev/null +++ b/contrib/session_end/Makefile @@ -0,0 +1,15 @@ +# contrib/session_start/Makefile + +MODULES = session_end +PGFILEDESC = "session_end - sample for session end hook" + +ifdef USE_PGXS +PG_CONFIG = pg_config +PGXS := $(shell $(PG_CONFIG) --pgxs) +include $(PGXS) +else +subdir = contrib/session_end +top_builddir = ../.. +include $(top_builddir)/src/Makefile.global +include $(top_srcdir)/contrib/contrib-global.mk +endif diff --git a/contrib/session_end/session_end.c b/contrib/session_end/session_end.c new file mode 100644 index 0000000..c979b40 --- /dev/null +++ b/contrib/session_end/session_end.c @@ -0,0 +1,45 @@ +/* ------------------------------------------------------------------------- + * + * session_end.c + * + * Copyright (c) 2010-2017, PostgreSQL Global Development Group + * + * IDENTIFICATION + * contrib/session_end/session_end.c + * + * ------------------------------------------------------------------------- + */ +#include "postgres.h" + +#include "tcop/tcopprot.h" + +PG_MODULE_MAGIC; + +void _PG_init(void); + +/* Original Hook */ +static session_end_hook_type original_session_end_hook = NULL; + +/* sample hook function */ +static void +sample_session_end_hook(const char *dbname, const char *username) +{ + if (original_session_end_hook) + original_session_end_hook(dbname, username); + + if (!strcmp(dbname, "test")) + elog(LOG, "end session hooked at '%s' database for user '%s'", + dbname, username); +} + +/* + * Module Load Callback + */ +void +_PG_init(void) +{ + /* Install Hooks */ + + original_session_end_hook = session_end_hook; + session_end_hook = sample_session_end_hook; +} diff --git a/contrib/session_start/Makefile b/contrib/session_start/Makefile new file mode 100644 index 0000000..f94355b --- /dev/null +++ b/contrib/session_start/Makefile @@ -0,0 +1,15 @@ +# contrib/session_start/Makefile + +MODULES = session_start +PGFILEDESC = "session_start - sample for session start hook" + +ifdef USE_PGXS +PG_CONFIG = pg_config +PGXS := $(shell $(PG_CONFIG) --pgxs) +include $(PGXS) +else +subdir = contrib/session_start +top_builddir = ../.. +include $(top_builddir)/src/Makefile.global +include $(top_srcdir)/contrib/contrib-global.mk +endif diff --git a/contrib/session_start/session_start.c b/contrib/session_start/session_start.c new file mode 100644 index 0000000..7070242 --- /dev/null +++ b/contrib/session_start/session_start.c @@ -0,0 +1,52 @@ +/* ------------------------------------------------------------------------- + * + * session_start.c + * + * Copyright (c) 2010-2017, PostgreSQL Global Development Group + * + * IDENTIFICATION + * contrib/session_start/session_start.c + * + * ------------------------------------------------------------------------- + */ +#include "postgres.h" + +#include "access/xact.h" +#include "executor/spi.h" +#include "tcop/tcopprot.h" + +PG_MODULE_MAGIC; + +void _PG_init(void); + +/* Original Hook */ +static session_start_hook_type original_session_start_hook = NULL; + +/* sample hook function */ +static void +sample_session_start_hook(const char *dbname, const char *username) +{ + if (original_session_start_hook) + original_session_start_hook(dbname, username); + + if (!strcmp(dbname, "test")) + { + StartTransactionCommand(); + SPI_connect(); + SPI_exec("set work_mem to 10240", 1); + SPI_finish(); + CommitTransactionCommand(); + } +} + +/* + * Module Load Callback + */ +void +_PG_init(void) +{ + /* Install Hooks */ + + original_session_start_hook = session_start_hook; + session_start_hook = sample_session_start_hook; +}
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers