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.
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 b8d860e..8b5e408 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 end of session */
+session_end_hook_type session_end_hook = NULL;
+
/* ----------------------------------------------------------------
* decls for routines only used in this file
* ----------------------------------------------------------------
@@ -182,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);
/* ----------------------------------------------------------------
@@ -3809,6 +3813,12 @@ PostgresMain(int argc, char *argv[],
PgStartTime = GetCurrentTimestamp();
/*
+ * 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
@@ -4514,3 +4524,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 f8c535c..6d38d0b 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 end of session */
+typedef void (*session_end_hook_type) (const char *dbname,
+ const char *username);
+extern PGDLLIMPORT session_end_hook_type session_end_hook;
+
/* GUC-configurable parameters */
typedef enum
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
