Bruce Momjian wrote:
> I have an idea for this TODO item:
>
> * Allow administrators to safely terminate individual sessions either
> via an SQL function or SIGTERM
>
> Lock table corruption following SIGTERM of an individual backend
> has been reported in 8.0. A possible cause was fixed in 8.1, but
> it is unknown whether other problems exist. This item mostly
> requires additional testing rather than of writing any new code.
>
> http://archives.postgresql.org/pgsql-hackers/2006-08/msg00174.php
>
> When we get the termination signal, why can't we just set a global
> boolean, do a query cancel, and in the setjmp() code block check the
> global and exit --- at that stage we know we have released all locks and
> can exit cleanly.
I have implemented this idea with the attached patch.
--
Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/func.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.429
diff -c -c -r1.429 func.sgml
*** doc/src/sgml/func.sgml 10 Apr 2008 13:34:33 -0000 1.429
--- doc/src/sgml/func.sgml 12 Apr 2008 13:48:33 -0000
***************
*** 11849,11854 ****
--- 11849,11857 ----
<primary>pg_cancel_backend</primary>
</indexterm>
<indexterm>
+ <primary>pg_terminate_backend</primary>
+ </indexterm>
+ <indexterm>
<primary>pg_reload_conf</primary>
</indexterm>
<indexterm>
***************
*** 11885,11890 ****
--- 11888,11900 ----
</row>
<row>
<entry>
+ <literal><function>pg_terminate_backend</function>(<parameter>pid</parameter> <type>int</>)</literal>
+ </entry>
+ <entry><type>boolean</type></entry>
+ <entry>Terminate a backend</entry>
+ </row>
+ <row>
+ <entry>
<literal><function>pg_reload_conf</function>()</literal>
</entry>
<entry><type>boolean</type></entry>
***************
*** 11909,11915 ****
<para>
<function>pg_cancel_backend</> sends a query cancel
(<systemitem>SIGINT</>) signal to a backend process identified by
! process ID. The process ID of an active backend can be found from
the <structfield>procpid</structfield> column in the
<structname>pg_stat_activity</structname> view, or by listing the
<command>postgres</command> processes on the server with
--- 11919,11927 ----
<para>
<function>pg_cancel_backend</> sends a query cancel
(<systemitem>SIGINT</>) signal to a backend process identified by
! process ID. <function>pg_terminate_backend</> sends a terminate
! (<systemitem>SIGTERM</>) signal to the specified process ID. The
! process ID of an active backend can be found from
the <structfield>procpid</structfield> column in the
<structname>pg_stat_activity</structname> view, or by listing the
<command>postgres</command> processes on the server with
Index: src/backend/port/ipc_test.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/port/ipc_test.c,v
retrieving revision 1.24
diff -c -c -r1.24 ipc_test.c
*** src/backend/port/ipc_test.c 24 Mar 2008 18:08:47 -0000 1.24
--- src/backend/port/ipc_test.c 12 Apr 2008 13:48:33 -0000
***************
*** 40,45 ****
--- 40,46 ----
volatile bool InterruptPending = false;
volatile bool QueryCancelPending = false;
+ volatile bool BackendTerminatePending = false;
volatile bool ProcDiePending = false;
volatile bool ImmediateInterruptOK = false;
volatile uint32 InterruptHoldoffCount = 0;
Index: src/backend/postmaster/autovacuum.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/postmaster/autovacuum.c,v
retrieving revision 1.76
diff -c -c -r1.76 autovacuum.c
*** src/backend/postmaster/autovacuum.c 26 Mar 2008 21:10:38 -0000 1.76
--- src/backend/postmaster/autovacuum.c 12 Apr 2008 13:48:33 -0000
***************
*** 1475,1481 ****
* means abort and exit cleanly, and SIGQUIT means abandon ship.
*/
pqsignal(SIGINT, StatementCancelHandler);
! pqsignal(SIGTERM, die);
pqsignal(SIGQUIT, quickdie);
pqsignal(SIGALRM, handle_sig_alarm);
--- 1475,1481 ----
* means abort and exit cleanly, and SIGQUIT means abandon ship.
*/
pqsignal(SIGINT, StatementCancelHandler);
! pqsignal(SIGTERM, BackendTerminateHandler);
pqsignal(SIGQUIT, quickdie);
pqsignal(SIGALRM, handle_sig_alarm);
Index: src/backend/tcop/postgres.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/tcop/postgres.c,v
retrieving revision 1.548
diff -c -c -r1.548 postgres.c
*** src/backend/tcop/postgres.c 2 Apr 2008 18:31:50 -0000 1.548
--- src/backend/tcop/postgres.c 12 Apr 2008 13:48:33 -0000
***************
*** 2541,2547 ****
* waiting for input, however.
*/
if (ImmediateInterruptOK && InterruptHoldoffCount == 0 &&
! CritSectionCount == 0 && !DoingCommandRead)
{
/* bump holdoff count to make ProcessInterrupts() a no-op */
/* until we are done getting ready for it */
--- 2541,2548 ----
* waiting for input, however.
*/
if (ImmediateInterruptOK && InterruptHoldoffCount == 0 &&
! CritSectionCount == 0 &&
! (!DoingCommandRead || BackendTerminatePending))
{
/* bump holdoff count to make ProcessInterrupts() a no-op */
/* until we are done getting ready for it */
***************
*** 2557,2562 ****
--- 2558,2575 ----
errno = save_errno;
}
+ /*
+ * Backend terminate happens by canceling the current query, then
+ * exiting after the longjump() returns to the main query loop.
+ * This is done so that all resources are properly reset before exit.
+ */
+ void
+ BackendTerminateHandler(SIGNAL_ARGS)
+ {
+ BackendTerminatePending = true;
+ StatementCancelHandler(postgres_signal_arg);
+ }
+
/* signal handler for floating point exception */
void
FloatExceptionHandler(SIGNAL_ARGS)
***************
*** 2621,2626 ****
--- 2634,2643 ----
ereport(ERROR,
(errcode(ERRCODE_QUERY_CANCELED),
errmsg("canceling autovacuum task")));
+ else if (BackendTerminatePending)
+ ereport(ERROR,
+ (errcode(ERRCODE_QUERY_CANCELED),
+ errmsg("terminating backend due to user request")));
else
ereport(ERROR,
(errcode(ERRCODE_QUERY_CANCELED),
***************
*** 3154,3160 ****
*/
pqsignal(SIGHUP, SigHupHandler); /* set flag to read config file */
pqsignal(SIGINT, StatementCancelHandler); /* cancel current query */
! pqsignal(SIGTERM, die); /* cancel current query and exit */
/*
* In a standalone backend, SIGQUIT can be generated from the keyboard
--- 3171,3177 ----
*/
pqsignal(SIGHUP, SigHupHandler); /* set flag to read config file */
pqsignal(SIGINT, StatementCancelHandler); /* cancel current query */
! pqsignal(SIGTERM, BackendTerminateHandler);
/*
* In a standalone backend, SIGQUIT can be generated from the keyboard
***************
*** 3459,3464 ****
--- 3476,3484 ----
/* We don't have a transaction command open anymore */
xact_started = false;
+ if (BackendTerminatePending)
+ die(SIGTERM);
+
/* Now we can allow interrupts again */
RESUME_INTERRUPTS();
}
Index: src/backend/utils/adt/misc.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/misc.c,v
retrieving revision 1.59
diff -c -c -r1.59 misc.c
*** src/backend/utils/adt/misc.c 4 Apr 2008 16:57:21 -0000 1.59
--- src/backend/utils/adt/misc.c 12 Apr 2008 13:48:33 -0000
***************
*** 129,134 ****
--- 129,140 ----
}
Datum
+ pg_terminate_backend(PG_FUNCTION_ARGS)
+ {
+ PG_RETURN_BOOL(pg_signal_backend(PG_GETARG_INT32(0), SIGTERM));
+ }
+
+ Datum
pg_reload_conf(PG_FUNCTION_ARGS)
{
if (!superuser())
Index: src/backend/utils/init/globals.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/init/globals.c,v
retrieving revision 1.105
diff -c -c -r1.105 globals.c
*** src/backend/utils/init/globals.c 17 Feb 2008 02:09:29 -0000 1.105
--- src/backend/utils/init/globals.c 12 Apr 2008 13:48:33 -0000
***************
*** 27,32 ****
--- 27,33 ----
volatile bool InterruptPending = false;
volatile bool QueryCancelPending = false;
+ volatile bool BackendTerminatePending = false;
volatile bool ProcDiePending = false;
volatile bool ImmediateInterruptOK = false;
volatile uint32 InterruptHoldoffCount = 0;
Index: src/include/miscadmin.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/miscadmin.h,v
retrieving revision 1.201
diff -c -c -r1.201 miscadmin.h
*** src/include/miscadmin.h 20 Feb 2008 22:46:24 -0000 1.201
--- src/include/miscadmin.h 12 Apr 2008 13:48:33 -0000
***************
*** 68,73 ****
--- 68,74 ----
/* these are marked volatile because they are set by signal handlers: */
extern PGDLLIMPORT volatile bool InterruptPending;
extern volatile bool QueryCancelPending;
+ extern volatile bool BackendTerminatePending;
extern volatile bool ProcDiePending;
/* these are marked volatile because they are examined by signal handlers: */
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.488
diff -c -c -r1.488 pg_proc.h
*** src/include/catalog/pg_proc.h 10 Apr 2008 22:25:25 -0000 1.488
--- src/include/catalog/pg_proc.h 12 Apr 2008 13:48:33 -0000
***************
*** 3157,3162 ****
--- 3157,3164 ----
DATA(insert OID = 2171 ( pg_cancel_backend PGNSP PGUID 12 1 0 f f t f v 1 16 "23" _null_ _null_ _null_ pg_cancel_backend - _null_ _null_ ));
DESCR("cancel a server process' current query");
+ DATA(insert OID = 2096 ( pg_terminate_backend PGNSP PGUID 12 1 0 f f t f v 1 16 "23" _null_ _null_ _null_ pg_terminate_backend - _null_ _null_ ));
+ DESCR("terminate a server process");
DATA(insert OID = 2172 ( pg_start_backup PGNSP PGUID 12 1 0 f f t f v 1 25 "25" _null_ _null_ _null_ pg_start_backup - _null_ _null_ ));
DESCR("prepare for taking an online backup");
DATA(insert OID = 2173 ( pg_stop_backup PGNSP PGUID 12 1 0 f f t f v 0 25 "" _null_ _null_ _null_ pg_stop_backup - _null_ _null_ ));
Index: src/include/tcop/tcopprot.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/tcop/tcopprot.h,v
retrieving revision 1.93
diff -c -c -r1.93 tcopprot.h
*** src/include/tcop/tcopprot.h 10 Mar 2008 12:55:13 -0000 1.93
--- src/include/tcop/tcopprot.h 12 Apr 2008 13:48:33 -0000
***************
*** 60,65 ****
--- 60,66 ----
extern void quickdie(SIGNAL_ARGS);
extern void authdie(SIGNAL_ARGS);
extern void StatementCancelHandler(SIGNAL_ARGS);
+ extern void BackendTerminateHandler(SIGNAL_ARGS);
extern void FloatExceptionHandler(SIGNAL_ARGS);
extern void prepare_for_client_read(void);
extern void client_read_ended(void);
Index: src/include/utils/builtins.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/utils/builtins.h,v
retrieving revision 1.312
diff -c -c -r1.312 builtins.h
*** src/include/utils/builtins.h 4 Apr 2008 18:45:36 -0000 1.312
--- src/include/utils/builtins.h 12 Apr 2008 13:48:33 -0000
***************
*** 416,421 ****
--- 416,422 ----
extern Datum current_database(PG_FUNCTION_ARGS);
extern Datum current_query(PG_FUNCTION_ARGS);
extern Datum pg_cancel_backend(PG_FUNCTION_ARGS);
+ extern Datum pg_terminate_backend(PG_FUNCTION_ARGS);
extern Datum pg_reload_conf(PG_FUNCTION_ARGS);
extern Datum pg_tablespace_databases(PG_FUNCTION_ARGS);
extern Datum pg_rotate_logfile(PG_FUNCTION_ARGS);
--
Sent via pgsql-patches mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches
