On 21 Jul. 2017 21:58, "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. Don't we have that timestamp already? What practical use cases are there for acting post-auth but that can't wait until the user tries to do something? Can a user do anything remotely interesting or useful without hitting either ExecutorStart_hook or ProcessUtility_hook? They can parse queries I guess but you could just set your hook up in the parser instead. If you hook the parser all they can do is open an idle session and sit there... So given that you can effectively do it already at the C hook level, if you're going to do it at all I guess it it'd be more interesting to expose a convenient event trigger for session start. As others suggested upthread. So it's easy for DBAs and devs who won't have any idea where to start writing extensions that register hooks. But... I think you need a good use case. Such a trigger would have no way to receive parameters from the user (except custom GUCs) or report any sort of result other than an error/warning/notice. So what's it going to do that can't already be decided by pg_hba.cond, pg_authid etc?
