Re: [HACKERS] background sessions

[Peter Eisentraut]

On 4/4/17 10:28, Robert Haas wrote:
> So is this patch going anywhere?

Not right now.  It will take some time to sort out your feedback and do
some refactoring.  I will close the patch for now.

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

Re: [HACKERS] background sessions

[Robert Haas]

On Sat, Mar 18, 2017 at 1:10 PM, Robert Haas  wrote:
> On Sat, Mar 18, 2017 at 10:59 AM, Petr Jelinek
>  wrote:
>>> shm_redirect_to_shm_mq() wasn't really designed to be used this way;
>>> it's designed for use by the worker, not the process that launched it.
>>> If an error occurs while output is redirected, bad things will happen.
>>> I think it would be better to find a way of sending that message to
>>> the queue without doing this.
>>
>> Couldn't we just create special version of pq_endmessage that sends to
>> shm_mq?
>
> Yes, I think that sounds good.
>
>>> Also, I suspect this is deadlock-prone.  If we get stuck trying to
>>> send a message to the background session while the queue is full, and
>>> at the same time the session is stuck trying to send us a long error
>>> message, we will have an undetected deadlock.  That's why
>>> pg_background() puts the string being passed to the worker into the
>>> DSM segment in its entirety, rather than sending it through a shm_mq.
>>
>> Yeah I think this will need to use the nowait = true when sending to
>> shm_mq and chunk the message if necessary...
>
> Hmm, yeah.  If you take breaks while sending to check for data that
> you need to receive, then you should be fine.

So is this patch going anywhere?

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

Re: [HACKERS] background sessions

[Robert Haas]

On Sat, Mar 18, 2017 at 10:59 AM, Petr Jelinek
 wrote:
>> shm_redirect_to_shm_mq() wasn't really designed to be used this way;
>> it's designed for use by the worker, not the process that launched it.
>> If an error occurs while output is redirected, bad things will happen.
>> I think it would be better to find a way of sending that message to
>> the queue without doing this.
>
> Couldn't we just create special version of pq_endmessage that sends to
> shm_mq?

Yes, I think that sounds good.

>> Also, I suspect this is deadlock-prone.  If we get stuck trying to
>> send a message to the background session while the queue is full, and
>> at the same time the session is stuck trying to send us a long error
>> message, we will have an undetected deadlock.  That's why
>> pg_background() puts the string being passed to the worker into the
>> DSM segment in its entirety, rather than sending it through a shm_mq.
>
> Yeah I think this will need to use the nowait = true when sending to
> shm_mq and chunk the message if necessary...

Hmm, yeah.  If you take breaks while sending to check for data that
you need to receive, then you should be fine.

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

Re: [HACKERS] background sessions

[Petr Jelinek]

On 15/03/17 17:58, Robert Haas wrote:
> On Wed, Mar 15, 2017 at 6:43 AM, Pavel Stehule  
> wrote:
>> I don't understand - CHECK_FOR_INTERRUPTS called from executor implicitly.
> 
> True.  So there shouldn't be any problem here.  I'm confused as can be
> about what you want changed.
> 
> Some review of the patch itself:
> 
> +pq_redirect_to_shm_mq(session->seg, session->command_qh);
> +pq_beginmessage(, 'X');
> +pq_endmessage();
> +pq_stop_redirect_to_shm_mq();
> 
> shm_redirect_to_shm_mq() wasn't really designed to be used this way;
> it's designed for use by the worker, not the process that launched it.
> If an error occurs while output is redirected, bad things will happen.
> I think it would be better to find a way of sending that message to
> the queue without doing this.

Couldn't we just create special version of pq_endmessage that sends to
shm_mq?

> 
> Also, I suspect this is deadlock-prone.  If we get stuck trying to
> send a message to the background session while the queue is full, and
> at the same time the session is stuck trying to send us a long error
> message, we will have an undetected deadlock.  That's why
> pg_background() puts the string being passed to the worker into the
> DSM segment in its entirety, rather than sending it through a shm_mq.
> 

Yeah I think this will need to use the nowait = true when sending to
shm_mq and chunk the message if necessary...

-- 
  Petr Jelinek  http://www.2ndQuadrant.com/
  PostgreSQL Development, 24x7 Support, 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

Re: [HACKERS] background sessions

[Robert Haas]

On Wed, Mar 15, 2017 at 6:43 AM, Pavel Stehule  wrote:
> I don't understand - CHECK_FOR_INTERRUPTS called from executor implicitly.

True.  So there shouldn't be any problem here.  I'm confused as can be
about what you want changed.

Some review of the patch itself:

+pq_redirect_to_shm_mq(session->seg, session->command_qh);
+pq_beginmessage(, 'X');
+pq_endmessage();
+pq_stop_redirect_to_shm_mq();

shm_redirect_to_shm_mq() wasn't really designed to be used this way;
it's designed for use by the worker, not the process that launched it.
If an error occurs while output is redirected, bad things will happen.
I think it would be better to find a way of sending that message to
the queue without doing this.

Also, I suspect this is deadlock-prone.  If we get stuck trying to
send a message to the background session while the queue is full, and
at the same time the session is stuck trying to send us a long error
message, we will have an undetected deadlock.  That's why
pg_background() puts the string being passed to the worker into the
DSM segment in its entirety, rather than sending it through a shm_mq.

+elog(ERROR, "no T before D");

That's not much of an error message, even for an elog.

+elog(ERROR, "already received a T message");

Nor that.

None of these functions have function header comments.  Or much in the
way of internal comments.  For example:

+case '1':
+break;
+case 'E':
+rethrow_errornotice();
+break;

That's really not clear without more commentary.

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

Re: [HACKERS] background sessions

[Pavel Stehule]

2017-03-15 0:44 GMT+01:00 Robert Haas :

> On Tue, Mar 14, 2017 at 4:54 PM, Pavel Stehule 
> wrote:
> >> I don't understand.  The only way you'd need a server restart is if a
> >> background process wasn't responding to SIGTERM, and that's a bug
> >> independent of anything this patch does.  It would be cause by the
> >> background process not doing CHECK_FOR_INTERRUPTS() or the moral
> >> equivalent regularly.
> >
> > It is bug, and I don't know if it s this extension bug or general bug.
> >
> > There is not adequate cleaning after killing.
> >
> > How can be implemented pg_cancel_backend on background process if there
> are
> > not CHECK_FOR_INTERRUPTS?
>
> You can't.  But what does that have to do with this patch?
>

I don't understand - CHECK_FOR_INTERRUPTS called from executor implicitly.

Pavel


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

Re: [HACKERS] background sessions

[Robert Haas]

On Tue, Mar 14, 2017 at 4:54 PM, Pavel Stehule  wrote:
>> I don't understand.  The only way you'd need a server restart is if a
>> background process wasn't responding to SIGTERM, and that's a bug
>> independent of anything this patch does.  It would be cause by the
>> background process not doing CHECK_FOR_INTERRUPTS() or the moral
>> equivalent regularly.
>
> It is bug, and I don't know if it s this extension bug or general bug.
>
> There is not adequate cleaning after killing.
>
> How can be implemented pg_cancel_backend on background process if there are
> not CHECK_FOR_INTERRUPTS?

You can't.  But what does that have to do with this patch?

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

Re: [HACKERS] background sessions

[Pavel Stehule]

2017-03-14 19:08 GMT+01:00 Robert Haas :

> On Tue, Mar 14, 2017 at 3:31 AM, Pavel Stehule 
> wrote:
> > Very often strategy can be recheck of parent process  in some waiting
> > cycles. It should not to impact performance.
>
> I think that's going to be hard to arrange, and I think it isn't
> necessary.  If the leader wants to arrange for the worker to die when
> it exits, it can use TerminateBackgroundWorker() from a
> PG_ENSURE_ERROR_CLEANUP block or on_shmem_exit hook.
>
> > I afraid so some waiting times in bg process can be high probable with
> this
> > patch - and then is probable so somebody use pg_terminate_backend. This
> > situation should not to finish by server restart.
>
> I don't understand.  The only way you'd need a server restart is if a
> background process wasn't responding to SIGTERM, and that's a bug
> independent of anything this patch does.  It would be cause by the
> background process not doing CHECK_FOR_INTERRUPTS() or the moral
> equivalent regularly.
>

It is bug, and I don't know if it s this extension bug or general bug.

There is not adequate cleaning after killing.

How can be implemented pg_cancel_backend on background process if there are
not CHECK_FOR_INTERRUPTS?

Regards

Pavel

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

Re: [HACKERS] background sessions

[Robert Haas]

On Tue, Mar 14, 2017 at 3:31 AM, Pavel Stehule  wrote:
> Very often strategy can be recheck of parent process  in some waiting
> cycles. It should not to impact performance.

I think that's going to be hard to arrange, and I think it isn't
necessary.  If the leader wants to arrange for the worker to die when
it exits, it can use TerminateBackgroundWorker() from a
PG_ENSURE_ERROR_CLEANUP block or on_shmem_exit hook.

> I afraid so some waiting times in bg process can be high probable with this
> patch - and then is probable so somebody use pg_terminate_backend. This
> situation should not to finish by server restart.

I don't understand.  The only way you'd need a server restart is if a
background process wasn't responding to SIGTERM, and that's a bug
independent of anything this patch does.  It would be cause by the
background process not doing CHECK_FOR_INTERRUPTS() or the moral
equivalent regularly.

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

Re: [HACKERS] background sessions

[Pavel Stehule]

2017-03-13 21:22 GMT+01:00 Robert Haas :

> On Sat, Mar 11, 2017 at 10:11 AM, Pavel Stehule 
> wrote:
> > 2017-03-09 14:52 GMT+01:00 Peter Eisentraut
> > :
> >>
> >> On 3/8/17 14:22, Pavel Stehule wrote:
> >> > 1. will be background session process closed automatically when parent
> >> > process is closed?
> >>
> >> If the communications queue goes away the process will eventually die.
> >> This is similar to how a backend process will eventually die if the
> >> client goes away.  Some more testing would be good here.
> >
> >
> > what means "eventually die"?
> >
> > I called pg_sleep() in called subprocess.
> >
> > Cancel, terminating parent process has not any effect. It is maybe
> > artificial test.
> >
> > Little bit more realistic - waiting on table lock in background worker
> was
> > successful - and when parent was cancelled, then worker process was
> > destroyed too.
> >
> > But when parent was terminated, then background worker process continued.
> >
> > What is worse - the background worker had 100% CPU and I had to restart
> > notebook.
> >
> > CREATE OR REPLACE FUNCTION public.foo()
> >  RETURNS void
> >  LANGUAGE plpythonu
> > AS $function$
> > with plpy.BackgroundSession() as a:
> >   a.execute('update foo2 set a = 30')
> >   a.execute('insert into foo2 values(10)')
> > $function$
> > postgres=#
> >
> >
> > I blocked foo2 in another session.
>
> I'm not sure what's going on with this patch set, but in general a
> background process can't just go away when the foreground process goes
> away.  We could arrange to kill it, a la pg_terminate_backend(), or we
> can let it keep running, and either of those things might be what
> somebody wants, depending on the situation.  But it can't just vanish
> into thin air.
>

I understand, so there are not one solution only - and process maintenance
is hard.

Very often strategy can be recheck of parent process  in some waiting
cycles. It should not to impact performance.

I afraid so some waiting times in bg process can be high probable with this
patch - and then is probable so somebody use pg_terminate_backend. This
situation should not to finish by server restart.

Regards

Pavel


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

Re: [HACKERS] background sessions

[Robert Haas]

On Sat, Mar 11, 2017 at 10:11 AM, Pavel Stehule  wrote:
> 2017-03-09 14:52 GMT+01:00 Peter Eisentraut
> :
>>
>> On 3/8/17 14:22, Pavel Stehule wrote:
>> > 1. will be background session process closed automatically when parent
>> > process is closed?
>>
>> If the communications queue goes away the process will eventually die.
>> This is similar to how a backend process will eventually die if the
>> client goes away.  Some more testing would be good here.
>
>
> what means "eventually die"?
>
> I called pg_sleep() in called subprocess.
>
> Cancel, terminating parent process has not any effect. It is maybe
> artificial test.
>
> Little bit more realistic - waiting on table lock in background worker was
> successful - and when parent was cancelled, then worker process was
> destroyed too.
>
> But when parent was terminated, then background worker process continued.
>
> What is worse - the background worker had 100% CPU and I had to restart
> notebook.
>
> CREATE OR REPLACE FUNCTION public.foo()
>  RETURNS void
>  LANGUAGE plpythonu
> AS $function$
> with plpy.BackgroundSession() as a:
>   a.execute('update foo2 set a = 30')
>   a.execute('insert into foo2 values(10)')
> $function$
> postgres=#
>
>
> I blocked foo2 in another session.

I'm not sure what's going on with this patch set, but in general a
background process can't just go away when the foreground process goes
away.  We could arrange to kill it, a la pg_terminate_backend(), or we
can let it keep running, and either of those things might be what
somebody wants, depending on the situation.  But it can't just vanish
into thin air.

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

Re: [HACKERS] background sessions

[Pavel Stehule]

2017-03-09 14:52 GMT+01:00 Peter Eisentraut :

> On 3/8/17 14:22, Pavel Stehule wrote:
> > 1. will be background session process closed automatically when parent
> > process is closed?
>
> If the communications queue goes away the process will eventually die.
> This is similar to how a backend process will eventually die if the
> client goes away.  Some more testing would be good here.
>

what means "eventually die"?

I called pg_sleep() in called subprocess.

Cancel, terminating parent process has not any effect. It is maybe
artificial test.

Little bit more realistic - waiting on table lock in background worker was
successful - and when parent was cancelled, then worker process was
destroyed too.

But when parent was terminated, then background worker process continued.

What is worse - the background worker had 100% CPU and I had to restart
notebook.

CREATE OR REPLACE FUNCTION public.foo()
 RETURNS void
 LANGUAGE plpythonu
AS $function$
with plpy.BackgroundSession() as a:
  a.execute('update foo2 set a = 30')
  a.execute('insert into foo2 values(10)')
$function$
postgres=#


I blocked foo2 in another session.

Regards

Pavel


>
> > 2. what timeouts are valid for this process - statement timeout, idle in
> > transaction timeout
>
> Those should work the same way.  It's the same code that runs the
> queries, starts/stops transactions, etc.
>
> > I see significant risk on leaking sessions.
>
> Yeah, that's a valid concern.  But I think it works ok.
>
> > There can be more doc and examples in plpython doc. It will be main
> > interface for this feature. Mainly about session processing.
>
> OK, I'll look into that again.
>
> --
> Peter Eisentraut  http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

Re: [HACKERS] background sessions

[Peter Eisentraut]

On 3/8/17 14:22, Pavel Stehule wrote:
> 1. will be background session process closed automatically when parent
> process is closed?

If the communications queue goes away the process will eventually die.
This is similar to how a backend process will eventually die if the
client goes away.  Some more testing would be good here.

> 2. what timeouts are valid for this process - statement timeout, idle in
> transaction timeout 

Those should work the same way.  It's the same code that runs the
queries, starts/stops transactions, etc.

> I see significant risk on leaking sessions.

Yeah, that's a valid concern.  But I think it works ok.

> There can be more doc and examples in plpython doc. It will be main
> interface for this feature. Mainly about session processing.

OK, I'll look into that again.

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

Re: [HACKERS] background sessions

[Pavel Stehule]

Hi

2017-03-01 3:35 GMT+01:00 Peter Eisentraut :

> > For additional entertainment, I include patches that integrate
> > background sessions into dblink.  So dblink can open a connection to a
> > background session, and then you can use the existing dblink functions
> > to send queries, read results, etc.  People use dblink to make
> > self-connections to get autonomous subsessions, so this would directly
> > address that use case.  The 0001 patch is some prerequisite refactoring
> > to remove an ugly macro mess, which is useful independent of this.  0002
> > is the actual patch.
>
> Updated patch, mainly with improved error handling and some tidying up.
>

I am checking this patch. I have few questions ( I didn't find a reply in
doc)

1. will be background session process closed automatically when parent
process is closed?

2. what timeouts are valid for this process - statement timeout, idle in
transaction timeout

I see significant risk on leaking sessions.

There can be more doc and examples in plpython doc. It will be main
interface for this feature. Mainly about session processing.

Regards

Pavel

p.s. It is great functionality and patch looks very well.





> Related to this is also the patch in
>  693b-cdc6d16b9...@2ndquadrant.com>
> as a resource control mechanism.
>
> --
> 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
>
>

Re: [HACKERS] background sessions

[Peter Eisentraut]

> For additional entertainment, I include patches that integrate
> background sessions into dblink.  So dblink can open a connection to a
> background session, and then you can use the existing dblink functions
> to send queries, read results, etc.  People use dblink to make
> self-connections to get autonomous subsessions, so this would directly
> address that use case.  The 0001 patch is some prerequisite refactoring
> to remove an ugly macro mess, which is useful independent of this.  0002
> is the actual patch.

Updated patch, mainly with improved error handling and some tidying up.

Related to this is also the patch in

as a resource control mechanism.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>From 48058db2817b8955ea3424bc77b55bb5782ffb0d Mon Sep 17 00:00:00 2001
From: Peter Eisentraut 
Date: Wed, 28 Dec 2016 12:00:00 -0500
Subject: [PATCH v3] Add background sessions

This adds a C API to run SQL statements in a background worker,
communicating by FE/BE protocol over a DSM message queue.  This can be
used to execute statements and transactions separate from the main
foreground session.

Also included is a PL/Python interface to this functionality.
---
 doc/src/sgml/bgsession.sgml | 273 +++
 doc/src/sgml/filelist.sgml  |   1 +
 doc/src/sgml/plpython.sgml  | 105 ++-
 doc/src/sgml/postgres.sgml  |   1 +
 doc/src/sgml/stylesheet-common.xsl  |   1 +
 src/backend/commands/variable.c |   5 +
 src/backend/libpq/pqmq.c|  26 +-
 src/backend/storage/ipc/shm_mq.c|  19 +
 src/backend/tcop/Makefile   |   2 +-
 src/backend/tcop/bgsession.c| 929 
 src/backend/tcop/postgres.c |  38 +-
 src/include/commands/variable.h |   1 +
 src/include/libpq/pqmq.h|   1 +
 src/include/storage/shm_mq.h|   3 +
 src/include/tcop/bgsession.h|  32 +
 src/include/tcop/tcopprot.h |  10 +
 src/pl/plpython/Makefile|   2 +
 src/pl/plpython/expected/plpython_bgsession.out | 217 ++
 src/pl/plpython/expected/plpython_test.out  |   7 +-
 src/pl/plpython/plpy_bgsession.c| 509 +
 src/pl/plpython/plpy_bgsession.h|  18 +
 src/pl/plpython/plpy_main.h |   3 +
 src/pl/plpython/plpy_planobject.c   |   1 +
 src/pl/plpython/plpy_planobject.h   |   2 +
 src/pl/plpython/plpy_plpymodule.c   |   5 +
 src/pl/plpython/plpy_spi.c  |  29 +-
 src/pl/plpython/plpy_spi.h  |   3 +
 src/pl/plpython/sql/plpython_bgsession.sql  | 177 +
 28 files changed, 2388 insertions(+), 32 deletions(-)
 create mode 100644 doc/src/sgml/bgsession.sgml
 create mode 100644 src/backend/tcop/bgsession.c
 create mode 100644 src/include/tcop/bgsession.h
 create mode 100644 src/pl/plpython/expected/plpython_bgsession.out
 create mode 100644 src/pl/plpython/plpy_bgsession.c
 create mode 100644 src/pl/plpython/plpy_bgsession.h
 create mode 100644 src/pl/plpython/sql/plpython_bgsession.sql

diff --git a/doc/src/sgml/bgsession.sgml b/doc/src/sgml/bgsession.sgml
new file mode 100644
index 00..14efb1b495
--- /dev/null
+++ b/doc/src/sgml/bgsession.sgml
@@ -0,0 +1,273 @@
+
+
+
+ Background Session API
+
+ 
+  The background session API is a C API for creating additional database
+  sessions in the background and running SQL statements in them.  A background
+  session behaves like a normal (foreground) session in that it has session
+  state, transactions, can run SQL statements, and so on.  Unlike a foreground
+  session, it is not connected directly to a client.  Instead the foreground
+  session can use this API to execute SQL statements and retrieve their
+  results.  Higher-level integrations, such as in procedural languages, can
+  make this functionality available to clients.  Background sessions are
+  independent from their foreground sessions in their session and transaction
+  state.  So a background session cannot see uncommitted data in foreground
+  sessions or vice versa, and there is no preferential treatment about
+  locking.  Like all sessions, background sessions are separate processes.
+  Foreground and background sessions communicate over shared memory messages
+  queues instead of the sockets that a client/server connection uses.
+ 
+
+ 
+  Background sessions can be useful in a variety of scenarios when effects
+  that are independent of the foreground session are to be achieved, for
+  example:
+  
+   
+
+ Commit data independent of whether a foreground 

Re: [HACKERS] background sessions

[Andrew Borodin]

2017-01-12 9:01 GMT+05:00 Peter Eisentraut :
> On 1/10/17 10:58 AM, Robert Haas wrote:
>> On Thu, Dec 29, 2016 at 5:18 PM, Peter Eisentraut
>>  wrote:
>>> For additional entertainment, I include patches that integrate
>>> background sessions into dblink.  So dblink can open a connection to a
>>> background session, and then you can use the existing dblink functions
>>> to send queries, read results, etc.  People use dblink to make
>>> self-connections to get autonomous subsessions, so this would directly
>>> address that use case.  The 0001 patch is some prerequisite refactoring
>>> to remove an ugly macro mess, which is useful independent of this.  0002
>>> is the actual patch.
>>
>> Would that constitute a complete replacement for pg_background?
>
> I think so.
That constitute replacement on the set of existing functionality.
It's not certain whether new features for pg_background would be
coherent with db_link ideology.
E.g. if one day we implement data exchange between two running queries
for pg_background, it would be in conflict with db_link ideology.

I have not opinion on is db_link or pg_background apropriate place for
this functionality. Just mentioning some thoughts.

BTW can we have an automatic FWD for bgsessions? Sounds crazy for me,
but technically make sense.

Best regards, Andrey Borodin.


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

Re: [HACKERS] background sessions

[Peter Eisentraut]

On 1/10/17 10:58 AM, Robert Haas wrote:
> On Thu, Dec 29, 2016 at 5:18 PM, Peter Eisentraut
>  wrote:
>> For additional entertainment, I include patches that integrate
>> background sessions into dblink.  So dblink can open a connection to a
>> background session, and then you can use the existing dblink functions
>> to send queries, read results, etc.  People use dblink to make
>> self-connections to get autonomous subsessions, so this would directly
>> address that use case.  The 0001 patch is some prerequisite refactoring
>> to remove an ugly macro mess, which is useful independent of this.  0002
>> is the actual patch.
> 
> Would that constitute a complete replacement for pg_background?

I think so.

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

Re: [HACKERS] background sessions

[Robert Haas]

On Thu, Dec 29, 2016 at 5:18 PM, Peter Eisentraut
 wrote:
> For additional entertainment, I include patches that integrate
> background sessions into dblink.  So dblink can open a connection to a
> background session, and then you can use the existing dblink functions
> to send queries, read results, etc.  People use dblink to make
> self-connections to get autonomous subsessions, so this would directly
> address that use case.  The 0001 patch is some prerequisite refactoring
> to remove an ugly macro mess, which is useful independent of this.  0002
> is the actual patch.

Would that constitute a complete replacement for pg_background?

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

Re: [HACKERS] background sessions

[amul sul]

On Wed, Jan 4, 2017 at 2:57 PM, Andrew Borodin  wrote:
> 2017-01-04 10:23 GMT+05:00 amul sul :
>> One more query, can we modify
>> BackgroundSessionStart()/BackgroundSession struct to get background
>> worker PID as well?
> I think since session always has a PID it's absoultley reasonable to return 
> PID.
>
>> I can understand this requirement could be sound useless for now,
>> because it only for the benefit of pg_background contrib module only.
> As far as i can unserstand BackgroundSession is not just a feature
> itself, it's the API. So PID would benefit to pg_background and all
> API use cases we didn't implement yet. I do not think that one PID in
> structure will waste huge amount of memory, cycles, dev time,
> readbility of docs, clearness of API etc. AFAIK the only reason may be
> if the PID is not always there.
>

+1, but to make BackgroundSession member accessible outside of
bgsession.c,  we might need to moved BackgroundSession definition to
bgsession.h.

Regards,
Amul Sul


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

Re: [HACKERS] background sessions

[Andrew Borodin]

2017-01-04 10:23 GMT+05:00 amul sul :
> One more query, can we modify
> BackgroundSessionStart()/BackgroundSession struct to get background
> worker PID as well?
I think since session always has a PID it's absoultley reasonable to return PID.

> I can understand this requirement could be sound useless for now,
> because it only for the benefit of pg_background contrib module only.
As far as i can unserstand BackgroundSession is not just a feature
itself, it's the API. So PID would benefit to pg_background and all
API use cases we didn't implement yet. I do not think that one PID in
structure will waste huge amount of memory, cycles, dev time,
readbility of docs, clearness of API etc. AFAIK the only reason may be
if the PID is not always there.

Best regards, Andrey Borodin.


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

Re: [HACKERS] background sessions

[amul sul]

On Tue, Jan 3, 2017 at 11:36 PM, Andrew Borodin  wrote:
> 2017-01-03 19:39 GMT+05:00 Peter Eisentraut
> :
>>
>> On 1/3/17 1:26 AM, amul sul wrote:
>> > One more requirement for pg_background is session, command_qh,
>> > response_qh and worker_handle should be last longer than current
>> > memory context, for that we might need to allocate these in
>> > TopMemoryContext.  Please find attach patch does the same change in
>> > BackgroundSessionStart().
>>
>> I had pondered this issue extensively.  The standard coding convention
>> in postgres is that the caller sets the memory context.  See the dblink
>> and plpython patches that make this happen in their own way.
>>
>> I agree it would make sense that you either pass in a memory context or
>> always use TopMemoryContext.  I'm open to these ideas, but they did not
>> seem to match any existing usage.
>
> +1
> Please excuse me if I'm not getting something obvious, but seems like
> BackgroundSessionNew() caller from pg_background_launch() can pick up
> TopMemoryCtx. I think that context setup should be done by extension, not by
> bg_session API.
>

Agree, will do this changes for pg_background.

One more query, can we modify
BackgroundSessionStart()/BackgroundSession struct to get background
worker PID as well?
I am asking because of BackgroundSessionNew() only returns session pointer,
but pg_background_launch() requires this PID to pass to user, which
will be further
used as session identifier at fetching result and/or executing further
queries as well as
to send query cancelation signal to background worker.

I can understand this requirement could be sound useless for now,
because it only for the benefit of pg_background contrib module only.
Thoughts?

Thanks & Regards,
Amul


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

Re: [HACKERS] background sessions

[Andrew Borodin]

2017-01-03 19:39 GMT+05:00 Peter Eisentraut :

> On 1/3/17 1:26 AM, amul sul wrote:
> > One more requirement for pg_background is session, command_qh,
> > response_qh and worker_handle should be last longer than current
> > memory context, for that we might need to allocate these in
> > TopMemoryContext.  Please find attach patch does the same change in
> > BackgroundSessionStart().
>
> I had pondered this issue extensively.  The standard coding convention
> in postgres is that the caller sets the memory context.  See the dblink
> and plpython patches that make this happen in their own way.
>
> I agree it would make sense that you either pass in a memory context or
> always use TopMemoryContext.  I'm open to these ideas, but they did not
> seem to match any existing usage.
>
+1
Please excuse me if I'm not getting something obvious, but seems like
BackgroundSessionNew() caller from pg_background_launch() can pick up
TopMemoryCtx. I think that context setup should be done by extension, not
by bg_session API.

Best regards, Andrey Borodin.

Re: [HACKERS] background sessions

[Peter Eisentraut]

On 1/3/17 1:26 AM, amul sul wrote:
> One more requirement for pg_background is session, command_qh,
> response_qh and worker_handle should be last longer than current
> memory context, for that we might need to allocate these in
> TopMemoryContext.  Please find attach patch does the same change in
> BackgroundSessionStart().

I had pondered this issue extensively.  The standard coding convention
in postgres is that the caller sets the memory context.  See the dblink
and plpython patches that make this happen in their own way.

I agree it would make sense that you either pass in a memory context or
always use TopMemoryContext.  I'm open to these ideas, but they did not
seem to match any existing usage.

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

Re: [HACKERS] background sessions

[amul sul]

On Fri, Dec 30, 2016 at 3:48 AM, Peter Eisentraut
 wrote:
> On 12/16/16 10:38 AM, Andrew Borodin wrote:
>> 2016-12-16 20:17 GMT+05:00 Peter Eisentraut 
>> :
 And one more thing... Can we have BackgroundSessionExecute() splitted
 into two parts: start query and wait for results?
 It would allow pg_background to reuse bgsession's code.
>>>
>>> Yes, I will look into that.
>>
>> Thank you. I'm marking both patches as "Waiting for author", keeping
>> in mind that pg_background is waiting for bgsessions.
>> After updates I'll review these patches.
>
> New patch, mainly with the function split as you requested above, not
> much else changed.
>

Thanks for your v2 patch, this is really helpful.

One more requirement for pg_background is session, command_qh,
response_qh and worker_handle should be last longer than current
memory context, for that we might need to allocate these in
TopMemoryContext.  Please find attach patch does the same change in
BackgroundSessionStart().

Do let me know if you have any other thoughts/suggestions, thank you.

Regards,
Amul


BackgroundSessionStart.patch
Description: Binary data

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

Re: [HACKERS] background sessions

[Peter Eisentraut]

On 12/16/16 10:38 AM, Andrew Borodin wrote:
> 2016-12-16 20:17 GMT+05:00 Peter Eisentraut 
> :
>>> And one more thing... Can we have BackgroundSessionExecute() splitted
>>> into two parts: start query and wait for results?
>>> It would allow pg_background to reuse bgsession's code.
>>
>> Yes, I will look into that.
> 
> Thank you. I'm marking both patches as "Waiting for author", keeping
> in mind that pg_background is waiting for bgsessions.
> After updates I'll review these patches.

New patch, mainly with the function split as you requested above, not
much else changed.

For additional entertainment, I include patches that integrate
background sessions into dblink.  So dblink can open a connection to a
background session, and then you can use the existing dblink functions
to send queries, read results, etc.  People use dblink to make
self-connections to get autonomous subsessions, so this would directly
address that use case.  The 0001 patch is some prerequisite refactoring
to remove an ugly macro mess, which is useful independent of this.  0002
is the actual patch.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From 95c0499907cfe49ff944dc19900d678e5dd8b5de Mon Sep 17 00:00:00 2001
From: Peter Eisentraut 
Date: Wed, 28 Dec 2016 12:00:00 -0500
Subject: [PATCH v2] Add background sessions

This adds a C API to run SQL statements in a background worker,
communicating by FE/BE protocol over a DSM message queue.  This can be
used to execute statements and transactions separate from the main
foreground session.

Also included is a PL/Python interface to this functionality.
---
 doc/src/sgml/bgsession.sgml | 272 +++
 doc/src/sgml/filelist.sgml  |   1 +
 doc/src/sgml/plpython.sgml  | 102 +++
 doc/src/sgml/postgres.sgml  |   1 +
 src/backend/commands/variable.c |   5 +
 src/backend/libpq/pqmq.c|  26 +-
 src/backend/storage/ipc/shm_mq.c|  19 +
 src/backend/tcop/Makefile   |   2 +-
 src/backend/tcop/bgsession.c| 911 
 src/backend/tcop/postgres.c |  24 +-
 src/include/commands/variable.h |   1 +
 src/include/libpq/pqmq.h|   1 +
 src/include/storage/shm_mq.h|   3 +
 src/include/tcop/bgsession.h|  30 +
 src/include/tcop/tcopprot.h |   9 +
 src/pl/plpython/Makefile|   2 +
 src/pl/plpython/expected/plpython_bgsession.out | 188 +
 src/pl/plpython/expected/plpython_test.out  |   7 +-
 src/pl/plpython/plpy_bgsession.c| 454 
 src/pl/plpython/plpy_bgsession.h|  18 +
 src/pl/plpython/plpy_main.h |   3 +
 src/pl/plpython/plpy_planobject.c   |   1 +
 src/pl/plpython/plpy_planobject.h   |   2 +
 src/pl/plpython/plpy_plpymodule.c   |   5 +
 src/pl/plpython/plpy_spi.c  |   7 +-
 src/pl/plpython/plpy_spi.h  |   3 +
 src/pl/plpython/sql/plpython_bgsession.sql  | 148 
 27 files changed, 2224 insertions(+), 21 deletions(-)
 create mode 100644 doc/src/sgml/bgsession.sgml
 create mode 100644 src/backend/tcop/bgsession.c
 create mode 100644 src/include/tcop/bgsession.h
 create mode 100644 src/pl/plpython/expected/plpython_bgsession.out
 create mode 100644 src/pl/plpython/plpy_bgsession.c
 create mode 100644 src/pl/plpython/plpy_bgsession.h
 create mode 100644 src/pl/plpython/sql/plpython_bgsession.sql

diff --git a/doc/src/sgml/bgsession.sgml b/doc/src/sgml/bgsession.sgml
new file mode 100644
index 00..ba5cb409d6
--- /dev/null
+++ b/doc/src/sgml/bgsession.sgml
@@ -0,0 +1,272 @@
+
+
+
+ Background Session API
+
+ 
+  The background session API is a C API for creating additional database
+  sessions in the background and running SQL statements in them.  A background
+  session behaves like a normal (foreground) session in that it has session
+  state, transactions, can run SQL statements, and so on.  Unlike a foreground
+  session, it is not connected directly to a client.  Instead the foreground
+  session can use this API to execute SQL statements and retrieve their
+  results.  Higher-level integrations, such as in procedural languages, can
+  make this functionality available to clients.  Background sessions are
+  independent from their foreground sessions in their session and transaction
+  state.  So a background session cannot see uncommitted data in foreground
+  sessions or vice versa, and there is no preferential treatment about
+  locking.  Like all sessions, background sessions are separate processes.
+  Foreground and background sessions communicate over shared memory messages
+  queues 

Re: [HACKERS] background sessions

[Andrew Borodin]

2016-12-16 20:17 GMT+05:00 Peter Eisentraut :
>> And one more thing... Can we have BackgroundSessionExecute() splitted
>> into two parts: start query and wait for results?
>> It would allow pg_background to reuse bgsession's code.
>
> Yes, I will look into that.

Thank you. I'm marking both patches as "Waiting for author", keeping
in mind that pg_background is waiting for bgsessions.
After updates I'll review these patches.

Best regards, Andrey Borodin.


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

Re: [HACKERS] background sessions

[Peter Eisentraut]

On 12/15/16 1:54 AM, Andrew Borodin wrote:
> And one more thing... Can we have BackgroundSessionExecute() splitted
> into two parts: start query and wait for results?
> It would allow pg_background to reuse bgsession's code.

Yes, I will look into that.

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

Re: [HACKERS] background sessions

[amul sul]

On Thu, Dec 15, 2016 at 12:24 PM, Andrew Borodin  wrote:
> 2016-12-15 0:30 GMT+05:00 Peter Eisentraut :
> TryBeginSession()?

 What exactly would that do?
>>> Return status (success\failure) and session object, if a function succeeded.
>>>
>>> If there is max_connections exceeded, then (false,null).
>>>
>>> I'm not sure whether this idiom is common for Python.
>>
>> You can catch PostgreSQL exceptions in PL/Python, so this can be handled
>> in user code.
>>
>> Some better connection management or pooling can probably be built on
>> top of the primitives later, I'd say.
>
> Agree, doing this in Python is the better option.
>
> And one more thing... Can we have BackgroundSessionExecute() splitted
> into two parts: start query and wait for results?
> It would allow pg_background to reuse bgsession's code.
>
+1

Regards,
Amul


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

Re: [HACKERS] background sessions

[Andrew Borodin]

2016-12-15 0:30 GMT+05:00 Peter Eisentraut :
 TryBeginSession()?
>>>
>>> What exactly would that do?
>> Return status (success\failure) and session object, if a function succeeded.
>>
>> If there is max_connections exceeded, then (false,null).
>>
>> I'm not sure whether this idiom is common for Python.
>
> You can catch PostgreSQL exceptions in PL/Python, so this can be handled
> in user code.
>
> Some better connection management or pooling can probably be built on
> top of the primitives later, I'd say.

Agree, doing this in Python is the better option.

And one more thing... Can we have BackgroundSessionExecute() splitted
into two parts: start query and wait for results?
It would allow pg_background to reuse bgsession's code.

Best regards, Andrey Borodin.


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

Re: [HACKERS] background sessions

[Peter Eisentraut]

On 12/14/16 11:33 AM, Andrew Borodin wrote:
> 2016-12-14 20:45 GMT+05:00 Peter Eisentraut 
> :
>> On 12/11/16 5:38 AM, Andrew Borodin wrote:
>>> 2. From my point of view the interface of the feature is the strong
>>> point here (compared to pg_background). But it does not help
>>> programmer to follow good practice: bgworker is a valuable and limited
>>> resource, may be we could start session with something like
>>> TryBeginSession()?
>>
>> What exactly would that do?
> Return status (success\failure) and session object, if a function succeeded.
> 
> If there is max_connections exceeded, then (false,null).
> 
> I'm not sure whether this idiom is common for Python.

You can catch PostgreSQL exceptions in PL/Python, so this can be handled
in user code.

Some better connection management or pooling can probably be built on
top of the primitives later, I'd say.

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

Re: [HACKERS] background sessions

[Andrew Borodin]

2016-12-14 20:45 GMT+05:00 Peter Eisentraut :
> On 12/11/16 5:38 AM, Andrew Borodin wrote:
>> 2. From my point of view the interface of the feature is the strong
>> point here (compared to pg_background). But it does not help
>> programmer to follow good practice: bgworker is a valuable and limited
>> resource, may be we could start session with something like
>> TryBeginSession()?
>
> What exactly would that do?
Return status (success\failure) and session object, if a function succeeded.

If there is max_connections exceeded, then (false,null).

I'm not sure whether this idiom is common for Python.

Best regards, Andrey Borodin.


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

Re: [HACKERS] background sessions

[Peter Eisentraut]

On 12/11/16 5:38 AM, Andrew Borodin wrote:
> 2. From my point of view the interface of the feature is the strong
> point here (compared to pg_background). But it does not help
> programmer to follow good practice: bgworker is a valuable and limited
> resource, may be we could start session with something like
> TryBeginSession()?

What exactly would that do?

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

Re: [HACKERS] background sessions

[Petr Jelinek]

On 12/12/16 16:29, Robert Haas wrote:
> On Mon, Dec 12, 2016 at 10:02 AM, Craig Ringer
>  wrote:
>> On 12 Dec. 2016 21:55, "Robert Haas"  wrote:
>> On Sun, Dec 11, 2016 at 5:38 AM, Andrew Borodin 
>> wrote:
>>> 1. As far as I can see, we connot use COPY FROM STDIN in bg session?
>>> Since one of purposes is to orchestrate transactions, may be that
>>> would be valuable.
>>
>> A background worker has no client connection, so what would COPY FROM STDIN
>> do?
>>
>> It doesn't make sense. But a bgworker may well want to supply input to COPY.
>> A COPY FROM CALLBACK of COPY FROM FILEDESC or whatever.
> 
> That's kinda weird, though.  I mean, you don't need to go through all
> of the COPY code just to call heap_multi_insert() or whatever, do you?
>  You can hand-roll whatever you need there.
> 

You do if source of your data is already in COPY (or csv) format. I do
have similar usecase in logical replication followup patch that I plan
to submit to Jan CF, so maybe that will be interesting for this as well.

-- 
  Petr Jelinek  http://www.2ndQuadrant.com/
  PostgreSQL Development, 24x7 Support, 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

Re: [HACKERS] background sessions

[Craig Ringer]

On 12 December 2016 at 23:29, Robert Haas  wrote:
> On Mon, Dec 12, 2016 at 10:02 AM, Craig Ringer
>  wrote:
>> On 12 Dec. 2016 21:55, "Robert Haas"  wrote:
>> On Sun, Dec 11, 2016 at 5:38 AM, Andrew Borodin 
>> wrote:
>>> 1. As far as I can see, we connot use COPY FROM STDIN in bg session?
>>> Since one of purposes is to orchestrate transactions, may be that
>>> would be valuable.
>>
>> A background worker has no client connection, so what would COPY FROM STDIN
>> do?
>>
>> It doesn't make sense. But a bgworker may well want to supply input to COPY.
>> A COPY FROM CALLBACK of COPY FROM FILEDESC or whatever.
>
> That's kinda weird, though.  I mean, you don't need to go through all
> of the COPY code just to call heap_multi_insert() or whatever, do you?
>  You can hand-roll whatever you need there.

And fire triggers and constraint checks if necessary, update indexes,
etc. But yeah.

The original idea with logical rep was to get COPY-format data from
the upstream when initializing a table, and apply it via COPY in a
bgworker. I think that's changed in favour of another approach in
logical rep now, but thought it was worth mentioning as something it
might make sense for someone to want to do.

-- 
 Craig Ringer   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

Re: [HACKERS] background sessions

[Robert Haas]

On Mon, Dec 12, 2016 at 10:02 AM, Craig Ringer
 wrote:
> On 12 Dec. 2016 21:55, "Robert Haas"  wrote:
> On Sun, Dec 11, 2016 at 5:38 AM, Andrew Borodin 
> wrote:
>> 1. As far as I can see, we connot use COPY FROM STDIN in bg session?
>> Since one of purposes is to orchestrate transactions, may be that
>> would be valuable.
>
> A background worker has no client connection, so what would COPY FROM STDIN
> do?
>
> It doesn't make sense. But a bgworker may well want to supply input to COPY.
> A COPY FROM CALLBACK of COPY FROM FILEDESC or whatever.

That's kinda weird, though.  I mean, you don't need to go through all
of the COPY code just to call heap_multi_insert() or whatever, do you?
 You can hand-roll whatever you need there.

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

Re: [HACKERS] background sessions

[Craig Ringer]

On 12 Dec. 2016 21:55, "Robert Haas"  wrote:

On Sun, Dec 11, 2016 at 5:38 AM, Andrew Borodin 
wrote:
> 1. As far as I can see, we connot use COPY FROM STDIN in bg session?
> Since one of purposes is to orchestrate transactions, may be that
> would be valuable.

A background worker has no client connection, so what would COPY FROM STDIN
do?


It doesn't make sense. But a bgworker may well want to supply input to
COPY. A COPY FROM CALLBACK of COPY FROM FILEDESC or whatever.


I have the feeling something like this came up on the logical replication
thread. Logical rep needs to efficiently load data via bgworker.

Re: [HACKERS] background sessions

[Robert Haas]

On Sun, Dec 11, 2016 at 5:38 AM, Andrew Borodin  wrote:
> 1. As far as I can see, we connot use COPY FROM STDIN in bg session?
> Since one of purposes is to orchestrate transactions, may be that
> would be valuable.

A background worker has no client connection, so what would COPY FROM STDIN 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

Re: [HACKERS] background sessions

[Andrew Borodin]

Hi!

I'm planning to review this patch.
I have some questions, maybe answers could help me understand patch better.
1. As far as I can see, we connot use COPY FROM STDIN in bg session?
Since one of purposes is to orchestrate transactions, may be that
would be valuable.
2. From my point of view the interface of the feature is the strong
point here (compared to pg_background). But it does not help
programmer to follow good practice: bgworker is a valuable and limited
resource, may be we could start session with something like
TryBeginSession()? May be this violates some policies or idioms which
I'm not aware of.

Thank you for your work.

Best regards, Andrey Borodin.


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

Re: [HACKERS] background sessions

[Haribabu Kommi]

On Tue, Nov 1, 2016 at 2:25 PM, Peter Eisentraut <
peter.eisentr...@2ndquadrant.com> wrote:

> Here is a patch for the background sessions C API and PL/Python support.
>  This was previously submitted as "autonomous transactions", which
> proved controversial, and there have been several suggestions for a new
> name.
>
> I have renamed everything, removed all the incomplete PL/pgSQL stuff,
> did some refinement on the PL/Python interfaces, added resource owner
> management so that you can preserve session handles across transactions.
>  That allows a pg_background-like behavior implemented in a PL function.
>  I have also added documentation, so reviewers could start there.
> Probably not quite all done yet, but I think it contains a lot of useful
> pieces that we could make into something nice.
>
>
Moved to next CF with "needs review" status.


Regards,
Hari Babu
Fujitsu Australia

[HACKERS] background sessions

[Peter Eisentraut]

Here is a patch for the background sessions C API and PL/Python support.
 This was previously submitted as "autonomous transactions", which
proved controversial, and there have been several suggestions for a new
name.

I have renamed everything, removed all the incomplete PL/pgSQL stuff,
did some refinement on the PL/Python interfaces, added resource owner
management so that you can preserve session handles across transactions.
 That allows a pg_background-like behavior implemented in a PL function.
 I have also added documentation, so reviewers could start there.
Probably not quite all done yet, but I think it contains a lot of useful
pieces that we could make into something nice.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From 661c7fe769982e3f0c71f4ad57a768d7eb55f6e2 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut 
Date: Mon, 31 Oct 2016 12:00:00 -0400
Subject: [PATCH] Add background sessions

This adds a C API to run SQL statements in a background worker,
communicating by FE/BE protocol over a DSM message queue.  This can be
used to execute statements and transactions separate from the main
foreground session.

Also included is a PL/Python interface to this functionality.
---
 doc/src/sgml/bgsession.sgml | 236 +++
 doc/src/sgml/filelist.sgml  |   1 +
 doc/src/sgml/plpython.sgml  | 102 +++
 doc/src/sgml/postgres.sgml  |   1 +
 src/backend/commands/variable.c |   5 +
 src/backend/libpq/pqmq.c|  23 +
 src/backend/tcop/Makefile   |   2 +-
 src/backend/tcop/bgsession.c| 890 
 src/backend/tcop/postgres.c |  24 +-
 src/include/commands/variable.h |   1 +
 src/include/libpq/pqmq.h|   1 +
 src/include/tcop/bgsession.h|  26 +
 src/include/tcop/tcopprot.h |   9 +
 src/pl/plpython/Makefile|   2 +
 src/pl/plpython/expected/plpython_bgsession.out | 188 +
 src/pl/plpython/expected/plpython_test.out  |   7 +-
 src/pl/plpython/plpy_bgsession.c| 454 
 src/pl/plpython/plpy_bgsession.h|  18 +
 src/pl/plpython/plpy_main.h |   3 +
 src/pl/plpython/plpy_planobject.c   |   1 +
 src/pl/plpython/plpy_planobject.h   |   2 +
 src/pl/plpython/plpy_plpymodule.c   |   5 +
 src/pl/plpython/plpy_spi.c  |   7 +-
 src/pl/plpython/plpy_spi.h  |   3 +
 src/pl/plpython/sql/plpython_bgsession.sql  | 148 
 25 files changed, 2139 insertions(+), 20 deletions(-)
 create mode 100644 doc/src/sgml/bgsession.sgml
 create mode 100644 src/backend/tcop/bgsession.c
 create mode 100644 src/include/tcop/bgsession.h
 create mode 100644 src/pl/plpython/expected/plpython_bgsession.out
 create mode 100644 src/pl/plpython/plpy_bgsession.c
 create mode 100644 src/pl/plpython/plpy_bgsession.h
 create mode 100644 src/pl/plpython/sql/plpython_bgsession.sql

diff --git a/doc/src/sgml/bgsession.sgml b/doc/src/sgml/bgsession.sgml
new file mode 100644
index 000..785ee66
--- /dev/null
+++ b/doc/src/sgml/bgsession.sgml
@@ -0,0 +1,236 @@
+
+
+
+ Background Session API
+
+ 
+  The background session API is a C API for creating additional database
+  sessions in the background and running SQL statements in them.  A background
+  session behaves like a normal (foreground) session in that it has session
+  state, transactions, can run SQL statements, and so on.  Unlike a foreground
+  session, it is not connected directly to a client.  Instead the foreground
+  session can use this API to execute SQL statements and retrieve their
+  results.  Higher-level integrations, such as in procedural languages, can
+  make this functionality available to clients.  Background sessions are
+  independent from their foreground sessions in their session and transaction
+  state.  So a background session cannot see uncommitted data in foreground
+  sessions or vice versa, and there is no preferential treatment about
+  locking.  Like all sessions, background sessions are separate processes.
+  Foreground and background sessions communicate over shared memory messages
+  queues instead of the sockets that a client/server connection uses.
+ 
+
+ 
+  Background sessions can be useful in a variety of scenarios when effects
+  that are independent of the foreground session are to be achieved, for
+  example:
+  
+   
+
+ Commit data independent of whether a foreground transaction commits, for
+ example for auditing.  A trigger in the foreground session could effect
+ the necessary writes via a background session.
+
+   
+   
+
+ Large changes can be split up into smaller transactions.  A foreground