synchronous_commit: Developer's View

Async Commit is a useful feature, yet it requires some additional
application code to be added to appropriate transactions. That code is
then clearly version dependent, which may not always be desirable.

It would be good if there was a way to make that a DBA-controllable
setting, much the same as we might execute the following command:

ALTER USER jimbob SET work_mem = ...

The above commmand allows application SQL to be tuned without changes to
the application code itself.

So I'm thinking, is there a way to decorate a transaction in such a way
that only that transaction knows to do
SET LOCAL synchronous_commit = off

Perhaps it would be possible to do this

ALTER FUNCTION fubar SET synchronous_commit = off;

So that any invocation of the function would automatically set all of
the appropriate parameters prior to execution.

Why do I mention this now? Well, on the 8.3 patch status list is the
concept of "per function search_path". search_path is a parameter, so
per function search path would seem to imply setting parameters on a per
function basis, exactly what I'm suggesting for enhancing async commit.

Per function parameters could then also be used for other "Statement
Behaviour" parameters and other related ones, such as ...

default_tablespace, temp_tablespaces, default_transaction_isolation,
statement_timeout, gin_fuzzy_search_limit, standard_conforming_strings,
regex_flavour, xmlbinary and xmloption.

To do this, we would need to add a column to pg_proc named and defined:
procconfig text[]
named similarly to the rolconfig column of pg_authid

This would then give us the flexibility to implement per function
search_path as well as the above mentioned uses.

Thoughts?

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com

"Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:

> Perhaps it would be possible to do this
>
> ALTER FUNCTION fubar SET synchronous_commit = off;
>
> So that any invocation of the function would automatically set all of
> the appropriate parameters prior to execution.

The problem here is that functions don't do commit. So this wouldn't have any
effect unless the setting stayed in effect after the end of the function call.

> Why do I mention this now? Well, on the 8.3 patch status list is the
> concept of "per function search_path". search_path is a parameter, so
> per function search path would seem to imply setting parameters on a per
> function basis, exactly what I'm suggesting for enhancing async commit.

There's a big difference since search_path affects the semantics of the
function itself. The point of having per-function search_path is to ensure
that the function is correctly interpreted in a consistent fashion. Not to
give extra flexibility to change the search_path later.

> Per function parameters could then also be used for other "Statement
> Behaviour" parameters and other related ones, such as ...
>
> default_tablespace, temp_tablespaces, default_transaction_isolation,
> statement_timeout, gin_fuzzy_search_limit, standard_conforming_strings,
> regex_flavour, xmlbinary and xmloption.

Of those standard_conforming_strings and regex_flavour (and no idea about the
xml ones) seem like good matches for binding per-function based on the
defining context.

The others are more DBA-type things which you would expect to be global. If
you want a specific function to create tables in a specific tablespace then
presumably you would just write it to do so. A DBA would be pretty surprised
to find his setting for default_tablespace doesn't have any effect on
pre-existing functions which didn't specify a tablespace.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

Simon Riggs wrote:
> Async Commit is a useful feature, yet it requires some additional
> application code to be added to appropriate transactions. That code is
> then clearly version dependent, which may not always be desirable.
>
> It would be good if there was a way to make that a DBA-controllable
> setting, much the same as we might execute the following command:
>
> ALTER USER jimbob SET work_mem = ...

I don't understand that example -
ALTER USER his_work_is_not_that_important SET synchronous_commit=off
is possible in CVS HEAD...

> The above commmand allows application SQL to be tuned without changes to
> the application code itself.
>
> So I'm thinking, is there a way to decorate a transaction in such a way
> that only that transaction knows to do
> SET LOCAL synchronous_commit = off
>
> Perhaps it would be possible to do this
>
> ALTER FUNCTION fubar SET synchronous_commit = off;
>
> So that any invocation of the function would automatically set all of
> the appropriate parameters prior to execution.
But functions cannot commit toplevel transactions, and subtransaction commits
are not WAL-logged anyway, so what would that do?

> Why do I mention this now? Well, on the 8.3 patch status list is the
> concept of "per function search_path". search_path is a parameter, so
> per function search path would seem to imply setting parameters on a per
> function basis, exactly what I'm suggesting for enhancing async commit.
>
> Per function parameters could then also be used for other "Statement
> Behaviour" parameters and other related ones, such as ...
>
> default_tablespace, temp_tablespaces, default_transaction_isolation,
> statement_timeout, gin_fuzzy_search_limit, standard_conforming_strings,
> regex_flavour, xmlbinary and xmloption.

You can already do
BEGIN;
SET LOCAL myguc=tempvalue
<some query>
END;

in pl/pgsql, and the value will be restored to it's original value
after the END. Or at least this is how I interpret the docs - I can't
remember ever trying that though.

So at least for the pl/pgsql case, it seems easy enough to temporarily
change GUCs already. For other PLs, things might be different though -
I wouldn't know, I have never really used them...

greetings, Florian Pflug

"Florian G. Pflug" <fgp(at)phlo(dot)org> writes:
> ... So at least for the pl/pgsql case, it seems easy enough to temporarily
> change GUCs already. For other PLs, things might be different though -
> I wouldn't know, I have never really used them...

It's definitely possible, but it's inconvenient and slow (slow because
you have to run a subtransaction, which ain't cheap). I think Simon
might have a good point about generalizing the proposed "set the search
path" facility to instead be "set any GUC for the duration of this
function". He's definitely all wet about the usefulness of that for
synchronous_commit, but as Greg pointed out, there are other GUCs
besides search_path that can break a function's expectations.

regards, tom lane

On Thu, Aug 30, 2007 at 10:34:42PM +0100, Simon Riggs wrote:
> Async Commit is a useful feature, yet it requires some additional
> application code to be added to appropriate transactions. That code is
> then clearly version dependent, which may not always be desirable.
>
> It would be good if there was a way to make that a DBA-controllable
> setting, much the same as we might execute the following command:
>
> ALTER USER jimbob SET work_mem = ...
>
> The above commmand allows application SQL to be tuned without changes to
> the application code itself.
>
> So I'm thinking, is there a way to decorate a transaction in such a way
> that only that transaction knows to do
> SET LOCAL synchronous_commit = off
>
> Perhaps it would be possible to do this
>
> ALTER FUNCTION fubar SET synchronous_commit = off;
>
> So that any invocation of the function would automatically set all of
> the appropriate parameters prior to execution.
>
> Why do I mention this now? Well, on the 8.3 patch status list is the
> concept of "per function search_path". search_path is a parameter, so
> per function search path would seem to imply setting parameters on a per
> function basis, exactly what I'm suggesting for enhancing async commit.
>
> Per function parameters could then also be used for other "Statement
> Behaviour" parameters and other related ones, such as ...
>
> default_tablespace, temp_tablespaces, default_transaction_isolation,
> statement_timeout, gin_fuzzy_search_limit, standard_conforming_strings,
> regex_flavour, xmlbinary and xmloption.
>
> To do this, we would need to add a column to pg_proc named and defined:
> procconfig text[]
> named similarly to the rolconfig column of pg_authid
>
> This would then give us the flexibility to implement per function
> search_path as well as the above mentioned uses.
>
> Thoughts?

While we're at it, it would be very nice to be able to set default
per-role, per-database settings. One obvious example of this is
search_path, where in general no two databases in a cluster need share
any common user-defined schema names.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate

David Fetter <david(at)fetter(dot)org> writes:
> While we're at it, it would be very nice to be able to set default
> per-role, per-database settings.

Er ... what deficiency do you see in ALTER ROLE SET and ALTER DATABASE SET?

regards, tom lane

On Sat, Sep 01, 2007 at 10:51:48PM -0400, Tom Lane wrote:
> David Fetter <david(at)fetter(dot)org> writes:
> > While we're at it, it would be very nice to be able to set default
> > per-role, per-database settings.
>
> Er ... what deficiency do you see in ALTER ROLE SET and ALTER DATABASE SET?

You can't set both at once. Let's imagine that you have two databases
in a cluster: foo with schemas foo_1 and foo_2, and bar with schemas
bar_1 and bar_2. There's no way (currently) to set a ROLE's foo
search_path to foo_1,foo_2 and its bar search_path to bar_1, bar_2.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate

David Fetter <david(at)fetter(dot)org> writes:
> On Sat, Sep 01, 2007 at 10:51:48PM -0400, Tom Lane wrote:
>> Er ... what deficiency do you see in ALTER ROLE SET and ALTER DATABASE SET?

> You can't set both at once.

Oh, you mean the cross-product case. Sorry, that was on the wish-list
already, but no one saw it as a security issue, so it's not happening
for 8.3.

regards, tom lane

On Sat, Sep 01, 2007 at 11:07:55PM -0400, Tom Lane wrote:
> David Fetter <david(at)fetter(dot)org> writes:
> > On Sat, Sep 01, 2007 at 10:51:48PM -0400, Tom Lane wrote:
> >> Er ... what deficiency do you see in ALTER ROLE SET and ALTER DATABASE SET?
>
> > You can't set both at once.
>
> Oh, you mean the cross-product case. Sorry, that was on the wish-list
> already, but no one saw it as a security issue, so it's not happening
> for 8.3.

Sorry. I assumed we were talking about 8.4 stuff.

Cheers,
David
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate

On Thu, 2007-08-30 at 21:00 -0400, Tom Lane wrote:
> "Florian G. Pflug" <fgp(at)phlo(dot)org> writes:
> > ... So at least for the pl/pgsql case, it seems easy enough to temporarily
> > change GUCs already. For other PLs, things might be different though -
> > I wouldn't know, I have never really used them...
>
> It's definitely possible, but it's inconvenient and slow (slow because
> you have to run a subtransaction, which ain't cheap). I think Simon
> might have a good point about generalizing the proposed "set the search
> path" facility to instead be "set any GUC for the duration of this
> function".

> He's definitely all wet about the usefulness of that for
> synchronous_commit, but as Greg pointed out, there are other GUCs
> besides search_path that can break a function's expectations.

Not too sure what "all wet" means, but the imagery is great. :-)

As I said, I'm looking for a way to decorate a specific transaction
without changing application code. Setting it on a function works fine
as long as the function was invoked as a top-level procedure call in its
own implicit transaction, which is common usage. Clearly, this doesn't
really make sense for non-procedural functions such as md5(), though it
does for things like record_vehicle_position() or ad_impression().

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com