RE: Hooks for session start and end, take two

From: Michael Paquier <michael(at)paquier(dot)xyz>
> Adding extra custom logging information, or plug that information
> elsewhere than Postgres. I have use cases for that when it comes to
> store external telemetry data or audit things for events happening
> specifically in Postgres.
>
> Well, hook authors can do a lot of stupid things.. Anyway it looks
> that the end hook is out of scope as far as the discussion has gone
> based on the lack of facility, and that there is still interest for
> the start hook.

I've got interested in this. What's the current status of this patch? The CF entry shows it was committed.

https://commitfest.postgresql.org/25/2251/

But I understood not, because the relevant code doesn't appear in HEAD, and Git log shows that it was reverted. Am I correct?

I'm thinking of using this feature to address an issue raised by a user who is considering migrating from Oracle. Oracle caches SQL execution plans in shared memory and enables any session to use them. He wants to hide the plan creation time from end users after database restart (perhaps the plan creation is slow.) To do that in Oracle, he runs possible SQL statements in a session solely for warm-up before resuming service for end users. Then, later new sessions of end users can use the cached execution plans.

Ideally, PostgreSQL should be able to share query plans among sessions without user intervention. But I guess it would be difficult. Instead, with this session start hook and the connection pooling, individual sessions can run possible SQL statements using PREPARE and EXECUTE once (forcing generic plans) at session start.

BTW, the user interface of the feature is unduly difficult for my use case and the use cases you mentioned. I think users should be able to use the feature only with SQL, without creating extensions. That also allows the use of this feature in DBaaS. How about creating a new event trigger type, connect trigger, after Oracle's logon trigger?

CREATE EVENT TRIGGER mytrigger
AFTER CONNECT [ON mydatabase]
EXECUTE {PROCEDURE | FUNCTION} myproc();

If some hacker wants to do what SQL can't, he can specify a UDF in C.

Regards
Takayuki Tsunakawa