Re: [PATCH] Implement motd for PostgreSQL

On Sun, Apr 4, 2021, at 09:16, Fabien COELHO wrote:
> If other cases are indeed pointers. For pgbench, all direct "if (xxx &&"
> cases are simple booleans or integers, pointers seem to have "!= NULL".
> While looking quickly at the grep output, ISTM that most obvious pointers
> have "!= NULL" and other cases often look like booleans:
>
> catalog/pg_operator.c: if (isDelete && t->oprcom == baseId)
> catalog/toasting.c: if (check && lockmode != AccessExclusiveLock)
> commands/async.c: if (amRegisteredListener && listenChannels == NIL)
> commands/explain.c: if (es->analyze && es->timing)
> …
>
> I'm sure there are exceptions, but ISTM that the local style is "!= NULL".

Many thanks for explaining.

>
> >> I'm wondering whether it should be possible to designate (1) a file the
> >> content of which would be shown, or (2) a command, the output of which
> >> would be shown [ok, there might be security implications on this one].
> >
> > Can't we just do that via plpgsql and EXECUTE somehow?
>
> Hmmm.
>
> Should we want to execute forcibly some PL/pgSQL on any new connection?

Oh, of course, you want the command to be execute for each new connection.

My idea was to use PL/pgSQL to execute only when you wanted to update the stored motd value,
but of course, if you want a new value from the command for each new connection,
then that doesn't work (and it doesn't work anyway due to not being able to execute ALTER SYSTEM from functions).

> Not sure this is really desirable. I was thinking of something more
> trivial, like the "motd" directeve could designate a file instead of the
> message itself.
>
> There could be a hook system to execute some user code on new connections
> and other events. It could be a new kind of event trigger, eg with
> connection_start, connection_end… That could be nice for other purposes,
> i.e. auditing. Now, event triggers are not global, they work inside a
> database, which would suggest that if extended a new connection event
> would be fired per database connection, not just once per connection. Not
> sure it would be a bad thing.

Such a hook sounds like a good idea.
If we would have such a hook, then another possibility would be to implement motd as an extension, right?

/Joel