[PATCH] A hook for session start

On Thu, Jul 20, 2017 at 8:47 AM, Yugo Nagata <nagata@sraoss.co.jp> wrote:

Hi,

Currently, PostgreSQL doen't have a hook triggered at session
start. Although we already have ClientAuthentication_hook,
this is triggered during authentication, so we can not
access the database.

If we have a hook triggerd only once at session start, we may
do something useful on the session for certain database or user.

For example, one of our clients wanted such feature. He wanted
to handle encription for specific users, though I don't know
the detail.

The attached patch (session_start_hook.patch) implements such
hook.

Another patch, session_start_sample.patch, is a very simple
example of this hook that changes work_mem values for sessions
of a specific database.

I would appreciate hearing your opinion on this hook.

I'm not sure your real needs but doesn't it material for improve Event
Triggers???

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

Show quoted text

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

On Thu, Jul 20, 2017 at 8:27 AM, Fabrízio de Royes Mello
<fabriziomello@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.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 21 July 2017 at 08:42, Robert Haas <robertmhaas@gmail.com> wrote:

On Thu, Jul 20, 2017 at 8:27 AM, Fabrízio de Royes Mello
<fabriziomello@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&gt;

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).

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.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Fri, 21 Jul 2017 09:53:19 +0800
Craig Ringer <craig@2ndquadrant.com> wrote:

On 21 July 2017 at 08:42, Robert Haas <robertmhaas@gmail.com> wrote:

On Thu, Jul 20, 2017 at 8:27 AM, Fabrízio de Royes Mello
<fabriziomello@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&gt;

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).

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.

Thanks,

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Yugo Nagata <nagata@sraoss.co.jp>

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, Jul 21, 2017 at 9:35 AM, Yugo Nagata <nagata@sraoss.co.jp> wrote:

On Fri, 21 Jul 2017 09:53:19 +0800
Craig Ringer <craig@2ndquadrant.com> wrote:

On 21 July 2017 at 08:42, Robert Haas <robertmhaas@gmail.com> wrote:

On Thu, Jul 20, 2017 at 8:27 AM, Fabrízio de Royes Mello
<fabriziomello@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&gt;

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?

If we're going to add this hook what about add a session end hook also?

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

Show quoted text

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

On Fri, 21 Jul 2017 10:31:57 -0300
Fabrízio de Royes Mello <fabriziomello@gmail.com> wrote:

On Fri, Jul 21, 2017 at 9:35 AM, Yugo Nagata <nagata@sraoss.co.jp> wrote:

On Fri, 21 Jul 2017 09:53:19 +0800
Craig Ringer <craig@2ndquadrant.com> wrote:

On 21 July 2017 at 08:42, Robert Haas <robertmhaas@gmail.com> wrote:

On Thu, Jul 20, 2017 at 8:27 AM, Fabrízio de Royes Mello
<fabriziomello@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&gt;

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.

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

--
Yugo Nagata <nagata@sraoss.co.jp>

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 21 Jul. 2017 21:58, "Yugo Nagata" <nagata@sraoss.co.jp> wrote:

On Fri, 21 Jul 2017 10:31:57 -0300
Fabrízio de Royes Mello <fabriziomello@gmail.com> wrote:

On Fri, Jul 21, 2017 at 9:35 AM, Yugo Nagata <nagata@sraoss.co.jp> wrote:

On Fri, 21 Jul 2017 09:53:19 +0800
Craig Ringer <craig@2ndquadrant.com> wrote:

On 21 July 2017 at 08:42, Robert Haas <robertmhaas@gmail.com> wrote:

On Thu, Jul 20, 2017 at 8:27 AM, Fabrízio de Royes Mello
<fabriziomello@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&gt;

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?

On Fri, Jul 21, 2017 at 10:58 AM, Yugo Nagata <nagata@sraoss.co.jp> wrote:

On Fri, 21 Jul 2017 10:31:57 -0300
Fabrízio de Royes Mello <fabriziomello@gmail.com> wrote:

On Fri, Jul 21, 2017 at 9:35 AM, Yugo Nagata <nagata@sraoss.co.jp>

wrote:

On Fri, 21 Jul 2017 09:53:19 +0800
Craig Ringer <craig@2ndquadrant.com> wrote:

On 21 July 2017 at 08:42, Robert Haas <robertmhaas@gmail.com> wrote:

On Thu, Jul 20, 2017 at 8:27 AM, Fabrízio de Royes Mello
<fabriziomello@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&gt;

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

Show quoted text

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

On Fri, Jul 21, 2017 at 11:10 AM, Craig Ringer <craig@2ndquadrant.com> wrote:

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?

Have, yes; record, no.

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...

That's an exceedingly-weak argument for rejecting this patch. The
fact that you can probably hack around the lack of a hook for most
reasonable use cases is not an argument for having a hook that does
what people actually want to do.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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...

That's an exceedingly-weak argument for rejecting this patch. The
fact that you can probably hack around the lack of a hook for most
reasonable use cases is not an argument for having a hook that does
what people actually want to do.

When I first saw this thread, my initial thought of a use case is to
prepare some key application queries so they are there and ready to go.
That would need to be before the ExecutorStart_hook or ProcessUtility_hook
if an app would just want to execute the prepared statement.

On Fri, Jul 21, 2017 at 12:19 PM, Fabrízio de Royes Mello <
fabriziomello@gmail.com> wrote:

On Fri, Jul 21, 2017 at 10:58 AM, Yugo Nagata <nagata@sraoss.co.jp> wrote:

On Fri, 21 Jul 2017 10:31:57 -0300
Fabrízio de Royes Mello <fabriziomello@gmail.com> wrote:

On Fri, Jul 21, 2017 at 9:35 AM, Yugo Nagata <nagata@sraoss.co.jp>

wrote:

On Fri, 21 Jul 2017 09:53:19 +0800
Craig Ringer <craig@2ndquadrant.com> wrote:

On 21 July 2017 at 08:42, Robert Haas <robertmhaas@gmail.com>

wrote:

On Thu, Jul 20, 2017 at 8:27 AM, Fabrízio de Royes Mello
<fabriziomello@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&gt;

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

Show quoted text

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

On 7/21/17 13:14, Jim Mlodgenski wrote:

When I first saw this thread, my initial thought of a use case is to
prepare some key application queries so they are there and ready to go.
That would need to be before the ExecutorStart_hook or
ProcessUtility_hook if an app would just want to execute the prepared
statement.

Isn't that what the preprepare extension does already?

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 7/20/17 07:47, Yugo Nagata wrote:

Another patch, session_start_sample.patch, is a very simple
example of this hook that changes work_mem values for sessions
of a specific database.

I think test modules should go into src/test/modules/ instead of contrib.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 7/21/17 12:59, Robert Haas wrote:

That's an exceedingly-weak argument for rejecting this patch. The
fact that you can probably hack around the lack of a hook for most
reasonable use cases is not an argument for having a hook that does
what people actually want to do.

Still nobody has presented a concrete use case so far.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2017-08-01 15:37:40 -0400, Peter Eisentraut wrote:

On 7/21/17 12:59, Robert Haas wrote:

That's an exceedingly-weak argument for rejecting this patch. The
fact that you can probably hack around the lack of a hook for most
reasonable use cases is not an argument for having a hook that does
what people actually want to do.

Still nobody has presented a concrete use case so far.

Citus for example starts a background worker (performing
e.g. distributed deadlock detection) if the citus extension exists. We
atm need annoying hacks to do so when the first query is executed.

- Andres

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, Aug 1, 2017 at 3:37 PM, Peter Eisentraut
<peter.eisentraut@2ndquadrant.com> wrote:

On 7/21/17 12:59, Robert Haas wrote:

That's an exceedingly-weak argument for rejecting this patch. The
fact that you can probably hack around the lack of a hook for most
reasonable use cases is not an argument for having a hook that does
what people actually want to do.

Still nobody has presented a concrete use case so far.

I've been asked for this at EDB, too. Inserting a row into some table
on each logon, for example.

A quick Google search found 6 previous requests for this feature, some
of which describe intended use cases:

/messages/by-id/4EBC6852.5030605@fuzzy.cz (2011)
/messages/by-id/CAHyXU0wrsYShxmwBxZSGYoiBJa=gzEJ17iAeRvaf_vA+coH_qA@mail.gmail.com
(2011)
/messages/by-id/BAY104-W513CF26C0046C9D28747B8D1DD0@phx.gbl
(2009, in Spanish)
/messages/by-id/758d5e7f0803130227m558d32cdl7159bed00d21f084@mail.gmail.com
(2008)
/messages/by-id/001a01c48077$0b118e60$0200030a@gendron.ca
(2004)
/messages/by-id/F96SgcOrBLSAQV6uPDV00000a2b@hotmail.com
(2000)

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, Aug 1, 2017 at 4:55 PM, Robert Haas <robertmhaas@gmail.com> wrote:

On Tue, Aug 1, 2017 at 3:37 PM, Peter Eisentraut
<peter.eisentraut@2ndquadrant.com> wrote:

On 7/21/17 12:59, Robert Haas wrote:

That's an exceedingly-weak argument for rejecting this patch. The
fact that you can probably hack around the lack of a hook for most
reasonable use cases is not an argument for having a hook that does
what people actually want to do.

Still nobody has presented a concrete use case so far.

I've been asked for this at EDB, too. Inserting a row into some table
on each logon, for example.

A quick Google search found 6 previous requests for this feature, some
of which describe intended use cases:

/messages/by-id/4EBC6852.5030605@fuzzy.cz (2011)

/messages/by-id/CAHyXU0wrsYShxmwBxZSGYoiBJa=gzEJ17iAeRvaf_vA+coH_qA@mail.gmail.com

(2011)

/messages/by-id/BAY104-W513CF26C0046C9D28747B8D1DD0@phx.gbl

(2009, in Spanish)

/messages/by-id/758d5e7f0803130227m558d32cdl7159bed00d21f084@mail.gmail.com

(2008)

/messages/by-id/001a01c48077$0b118e60$0200030a@gendron.ca

(2004)

/messages/by-id/F96SgcOrBLSAQV6uPDV00000a2b@hotmail.com

(2000)

Hi all,

I'm sending a new rebased patches and added tests to src/tests/modules as
suggested before.

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

Show quoted text

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

On Thu, Oct 5, 2017 at 4:14 PM, Fabrízio de Royes Mello <
fabriziomello@gmail.com> wrote:

On Tue, Aug 1, 2017 at 4:55 PM, Robert Haas <robertmhaas@gmail.com> wrote:

On Tue, Aug 1, 2017 at 3:37 PM, Peter Eisentraut
<peter.eisentraut@2ndquadrant.com> wrote:

On 7/21/17 12:59, Robert Haas wrote:

That's an exceedingly-weak argument for rejecting this patch. The
fact that you can probably hack around the lack of a hook for most
reasonable use cases is not an argument for having a hook that does
what people actually want to do.

Still nobody has presented a concrete use case so far.

I've been asked for this at EDB, too. Inserting a row into some table
on each logon, for example.

A quick Google search found 6 previous requests for this feature, some
of which describe intended use cases:

/messages/by-id/4EBC6852.5030605@fuzzy.cz (2011)

/messages/by-id/CAHyXU0wrsYShxmwBxZSGYoiBJa=gzEJ17iAeRvaf_vA+coH_qA@mail.gmail.com

(2011)

/messages/by-id/BAY104-W513CF26C0046C9D28747B8D1DD0@phx.gbl

(2009, in Spanish)

/messages/by-id/758d5e7f0803130227m558d32cdl7159bed00d21f084@mail.gmail.com

(2008)

/messages/by-id/001a01c48077$0b118e60$0200030a@gendron.ca

(2004)

/messages/by-id/F96SgcOrBLSAQV6uPDV00000a2b@hotmail.com

(2000)

Hi all,

I'm sending a new rebased patches and added tests to src/tests/modules as

suggested before.

Also added for the next commitfest:

https://commitfest.postgresql.org/15/1318/

Att,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

Show quoted text

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

On Fri, Jul 21, 2017 at 11:10:52PM +0800, Craig Ringer wrote:

What practical use cases are there for acting post-auth but that can't wait
until the user tries to do something?

Creating TEMP schema that triggers and functions might need.

Doing CREATE TEMP TABLE IF NOT EXISTS in triggers slows things down.

It'd be super nice if PostgreSQL had some sort of persistent TEMP
schema option, where you can have schema elements that are persistent
in that they're always there, but where the data is all TEMP. Oracle
has this and they call it GLOBAL TEMP IIRC. There would be some
caveats, such as not being able to have FKs between these sorts of
persistent temp tables and persistent tables.

In the absence of such a feature, a session hook/trigger is a great
workaround.

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...

In any other hook you'd have to check whether the session setup work you
wanted to do has been done. That could be potentially slow.

I actually have an all SQL implementation of session/begin/commit
triggers. The session triggers in that implementation only run on the
first DML statement, which could be too late for OP's purpose.

Nico
--

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers