public inbox for [email protected]
help / color / mirror / Atom feed[PATCH] pg_dump: use a cursor in getBlobs..
31+ messages / 8 participants
[nested] [flat]
* [PATCH] pg_dump: use a cursor in getBlobs..
@ 2021-03-09 20:06 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 31+ messages in thread
From: Justin Pryzby @ 2021-03-09 20:06 UTC (permalink / raw)
..to mitigate huge memory use in the case of millions of large objects
---
src/bin/pg_dump/pg_dump.c | 96 +++++++++++++++++++++------------------
1 file changed, 52 insertions(+), 44 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index aa02ada079..3fd7f48605 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -3333,7 +3333,7 @@ getBlobs(Archive *fout)
BlobInfo *binfo;
DumpableObject *bdata;
PGresult *res;
- int ntups;
+ int ntups, total = 0;
int i;
int i_oid;
int i_lomowner;
@@ -3341,9 +3341,12 @@ getBlobs(Archive *fout)
int i_rlomacl;
int i_initlomacl;
int i_initrlomacl;
+ const char *blobFetchQry = "FETCH 1000 IN blob";
pg_log_info("reading large objects");
+ appendPQExpBuffer(blobQry, "DECLARE blob CURSOR FOR ");
+
/* Fetch BLOB OIDs, and owner/ACL data if >= 9.0 */
if (fout->remoteVersion >= 90600)
{
@@ -3393,58 +3396,66 @@ getBlobs(Archive *fout)
"NULL::oid AS initrlomacl "
" FROM pg_largeobject");
- res = ExecuteSqlQuery(fout, blobQry->data, PGRES_TUPLES_OK);
+ ExecuteSqlStatement(fout, blobQry->data);
+ destroyPQExpBuffer(blobQry);
- i_oid = PQfnumber(res, "oid");
- i_lomowner = PQfnumber(res, "rolname");
- i_lomacl = PQfnumber(res, "lomacl");
- i_rlomacl = PQfnumber(res, "rlomacl");
- i_initlomacl = PQfnumber(res, "initlomacl");
- i_initrlomacl = PQfnumber(res, "initrlomacl");
+ do {
+ res = ExecuteSqlQuery(fout, blobFetchQry, PGRES_TUPLES_OK);
- ntups = PQntuples(res);
+ i_oid = PQfnumber(res, "oid");
+ i_lomowner = PQfnumber(res, "rolname");
+ i_lomacl = PQfnumber(res, "lomacl");
+ i_rlomacl = PQfnumber(res, "rlomacl");
+ i_initlomacl = PQfnumber(res, "initlomacl");
+ i_initrlomacl = PQfnumber(res, "initrlomacl");
- /*
- * Each large object has its own BLOB archive entry.
- */
- binfo = (BlobInfo *) pg_malloc(ntups * sizeof(BlobInfo));
+ ntups = PQntuples(res);
+ total += ntups;
- for (i = 0; i < ntups; i++)
- {
- binfo[i].dobj.objType = DO_BLOB;
- binfo[i].dobj.catId.tableoid = LargeObjectRelationId;
- binfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
- AssignDumpId(&binfo[i].dobj);
+ /*
+ * Each large object has its own BLOB archive entry.
+ */
+ binfo = (BlobInfo *) pg_malloc(ntups * sizeof(BlobInfo));
- binfo[i].dobj.name = pg_strdup(PQgetvalue(res, i, i_oid));
- binfo[i].rolname = pg_strdup(PQgetvalue(res, i, i_lomowner));
- binfo[i].blobacl = pg_strdup(PQgetvalue(res, i, i_lomacl));
- binfo[i].rblobacl = pg_strdup(PQgetvalue(res, i, i_rlomacl));
- binfo[i].initblobacl = pg_strdup(PQgetvalue(res, i, i_initlomacl));
- binfo[i].initrblobacl = pg_strdup(PQgetvalue(res, i, i_initrlomacl));
+ for (i = 0; i < ntups; i++)
+ {
+ binfo[i].dobj.objType = DO_BLOB;
+ binfo[i].dobj.catId.tableoid = LargeObjectRelationId;
+ binfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
+ AssignDumpId(&binfo[i].dobj);
+
+ binfo[i].dobj.name = pg_strdup(PQgetvalue(res, i, i_oid));
+ binfo[i].rolname = pg_strdup(PQgetvalue(res, i, i_lomowner));
+ binfo[i].blobacl = pg_strdup(PQgetvalue(res, i, i_lomacl));
+ binfo[i].rblobacl = pg_strdup(PQgetvalue(res, i, i_rlomacl));
+ binfo[i].initblobacl = pg_strdup(PQgetvalue(res, i, i_initlomacl));
+ binfo[i].initrblobacl = pg_strdup(PQgetvalue(res, i, i_initrlomacl));
+
+ if (PQgetisnull(res, i, i_lomacl) &&
+ PQgetisnull(res, i, i_rlomacl) &&
+ PQgetisnull(res, i, i_initlomacl) &&
+ PQgetisnull(res, i, i_initrlomacl))
+ binfo[i].dobj.dump &= ~DUMP_COMPONENT_ACL;
- if (PQgetisnull(res, i, i_lomacl) &&
- PQgetisnull(res, i, i_rlomacl) &&
- PQgetisnull(res, i, i_initlomacl) &&
- PQgetisnull(res, i, i_initrlomacl))
- binfo[i].dobj.dump &= ~DUMP_COMPONENT_ACL;
+ /*
+ * In binary-upgrade mode for blobs, we do *not* dump out the blob
+ * data, as it will be copied by pg_upgrade, which simply copies the
+ * pg_largeobject table. We *do* however dump out anything but the
+ * data, as pg_upgrade copies just pg_largeobject, but not
+ * pg_largeobject_metadata, after the dump is restored.
+ */
+ if (dopt->binary_upgrade)
+ binfo[i].dobj.dump &= ~DUMP_COMPONENT_DATA;
+ }
- /*
- * In binary-upgrade mode for blobs, we do *not* dump out the blob
- * data, as it will be copied by pg_upgrade, which simply copies the
- * pg_largeobject table. We *do* however dump out anything but the
- * data, as pg_upgrade copies just pg_largeobject, but not
- * pg_largeobject_metadata, after the dump is restored.
- */
- if (dopt->binary_upgrade)
- binfo[i].dobj.dump &= ~DUMP_COMPONENT_DATA;
- }
+ PQclear(res);
+ } while (ntups != 0);
/*
* If we have any large objects, a "BLOBS" archive entry is needed. This
* is just a placeholder for sorting; it carries no data now.
*/
- if (ntups > 0)
+ if (total > 0)
{
bdata = (DumpableObject *) pg_malloc(sizeof(DumpableObject));
bdata->objType = DO_BLOB_DATA;
@@ -3452,9 +3463,6 @@ getBlobs(Archive *fout)
AssignDumpId(bdata);
bdata->name = pg_strdup("BLOBS");
}
-
- PQclear(res);
- destroyPQExpBuffer(blobQry);
}
/*
--
2.17.0
--gatW/ieO32f1wygP--
^ permalink raw reply [nested|flat] 31+ messages in thread
* ReplicationSlotRelease() crashes when the instance is in the single user mode
@ 2025-02-18 05:33 Hayato Kuroda (Fujitsu) <[email protected]>
0 siblings, 1 reply; 31+ messages in thread
From: Hayato Kuroda (Fujitsu) @ 2025-02-18 05:33 UTC (permalink / raw)
To: '[email protected]' <[email protected]>
Dear hackers,
I found $SUBJECT when I'm playing with the single user mode.
How to reproduce
===========
You can reproduce the failure with below steps.
```
# Initialize an instance
$ initdb -D data -U postgres
# Start it as single user mode
$ postgres --single -D data/ postgres
PostgreSQL stand-alone backend 18devel
backend> SELECT pg_create_physical_replication_slot(slot_name := 'physical_slot', immediately_reserve := true);
...
backend> SELECT pg_replication_slot_advance('physical_slot', pg_current_wal_lsn());
1: pg_replication_slot_advance (typeid = 2249, len = -1, typmod = -1, byval = f)
----
TRAP: failed Assert("slot != NULL && (slot->active_pid != 0)"), File: "../postgres/src/backend/replication/slot.c", Line: 674, PID: 430860
postgres(ExceptionalCondition+0xab)[0xb86a2a]
postgres(ReplicationSlotRelease+0x5a)[0x8df10b]
postgres(pg_replication_slot_advance+0x330)[0x8e46ed]
...
```
Analysis
=====
We trapped at below assertion in ReplicationSlotRelease(). IIUC, `slot->active_pid` is set
only when the process is under the postmaster, but ReplicationSlotRelease() always requires it.
```
Assert(slot != NULL && slot->active_pid != 0);
```
Possible fix
=======
Naively considered, there are two approaches to fix this. 1) set active_pid when even in the single
user mode [1], or 2) ease the condition to accept the situation [2]. I'm not familiar with the mode,
but [1] seems better if we want to unify codes.
Thought?
[1]:
```
--- a/src/backend/replication/slot.c
+++ b/src/backend/replication/slot.c
@@ -599,7 +599,7 @@ retry:
SpinLockRelease(&s->mutex);
}
else
- active_pid = MyProcPid;
+ s->active_pid = active_pid = MyProcPid;
LWLockRelease(ReplicationSlotControlLock);
/*
```
[2]:
```
--- a/src/backend/replication/slot.c
+++ b/src/backend/replication/slot.c
@@ -671,7 +671,8 @@ ReplicationSlotRelease(void)
bool is_logical = false; /* keep compiler quiet */
TimestampTz now = 0;
- Assert(slot != NULL && slot->active_pid != 0);
+ Assert(slot != NULL &&
+ (slot->active_pid != 0 || !IsUnderPostmaster));
if (am_walsender)
{
```
Best regards,
Hayato Kuroda
FUJITSU LIMITED
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: ReplicationSlotRelease() crashes when the instance is in the single user mode
@ 2025-02-18 06:15 Michael Paquier <[email protected]>
parent: Hayato Kuroda (Fujitsu) <[email protected]>
0 siblings, 1 reply; 31+ messages in thread
From: Michael Paquier @ 2025-02-18 06:15 UTC (permalink / raw)
To: Hayato Kuroda (Fujitsu) <[email protected]>; +Cc: '[email protected]' <[email protected]>
On Tue, Feb 18, 2025 at 05:33:05AM +0000, Hayato Kuroda (Fujitsu) wrote:
> I found $SUBJECT when I'm playing with the single user mode.
Perhaps a very naive question, but is there any point in authorizing
manipulations of MyReplicationSlot in single-user mode, to begin with?
With this remark, I would mean to apply a rule to
ReplicationSlotAcquire(), so as all its callers would know about that.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, ../../[email protected]/2-signature.asc)
download
^ permalink raw reply [nested|flat] 31+ messages in thread
* RE: ReplicationSlotRelease() crashes when the instance is in the single user mode
@ 2025-02-18 07:29 Hayato Kuroda (Fujitsu) <[email protected]>
parent: Michael Paquier <[email protected]>
0 siblings, 1 reply; 31+ messages in thread
From: Hayato Kuroda (Fujitsu) @ 2025-02-18 07:29 UTC (permalink / raw)
To: 'Michael Paquier' <[email protected]>; +Cc: '[email protected]' <[email protected]>
Dear Michael,
> Perhaps a very naive question, but is there any point in authorizing
> manipulations of MyReplicationSlot in single-user mode, to begin with?
> With this remark, I would mean to apply a rule to
> ReplicationSlotAcquire(), so as all its callers would know about that.
According to the original thread [1], there was a wide consensus replication-related
operations can be rejected, except the slot removal. I feel this is reasonable.
Currently pg_drop_replication_slot() requires the droping slot can be acquired,
so we cannot reject it in single user mode as-is. Maybe we should revive the 0002
patch in [1] then try to do that. Thought?
[1]: https://www.postgresql.org/message-id/3b2f809f-326c-38dd-7a9e-897f957a4eb1%40enterprisedb.com
Best regards,
Hayato Kuroda
FUJITSU LIMITED
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: ReplicationSlotRelease() crashes when the instance is in the single user mode
@ 2025-02-18 07:50 Michael Paquier <[email protected]>
parent: Hayato Kuroda (Fujitsu) <[email protected]>
0 siblings, 1 reply; 31+ messages in thread
From: Michael Paquier @ 2025-02-18 07:50 UTC (permalink / raw)
To: Hayato Kuroda (Fujitsu) <[email protected]>; +Cc: '[email protected]' <[email protected]>
On Tue, Feb 18, 2025 at 07:29:51AM +0000, Hayato Kuroda (Fujitsu) wrote:
> According to the original thread [1], there was a wide consensus replication-related
> operations can be rejected, except the slot removal. I feel this is reasonable.
>
> Currently pg_drop_replication_slot() requires the droping slot can be acquired,
> so we cannot reject it in single user mode as-is. Maybe we should revive the 0002
> patch in [1] then try to do that. Thought?
>
> [1]: https://www.postgresql.org/message-id/3b2f809f-326c-38dd-7a9e-897f957a4eb1%40enterprisedb.com
Ah, good point for the slot drop. So 0ce5cf2ef24f is claiming that
some of these manipulations are OK. I didn't suspect this one.
Slot advancing is a very different beast, unfortunately, that may
depend on many other subsystems. For example with logical slots we
would finish by calling rm_decode, which could be outside of core.
Justifying that this operation is supported in single-user mode is
larger than what you are suggesting here..
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, ../../[email protected]/2-signature.asc)
download
^ permalink raw reply [nested|flat] 31+ messages in thread
* RE: ReplicationSlotRelease() crashes when the instance is in the single user mode
@ 2025-02-18 08:23 Hayato Kuroda (Fujitsu) <[email protected]>
parent: Michael Paquier <[email protected]>
0 siblings, 1 reply; 31+ messages in thread
From: Hayato Kuroda (Fujitsu) @ 2025-02-18 08:23 UTC (permalink / raw)
To: 'Michael Paquier' <[email protected]>; +Cc: '[email protected]' <[email protected]>
Dear Michael,
> Ah, good point for the slot drop. So 0ce5cf2ef24f is claiming that
> some of these manipulations are OK. I didn't suspect this one.
Yeah, I think so.
> Slot advancing is a very different beast, unfortunately, that may
> depend on many other subsystems. For example with logical slots we
> would finish by calling rm_decode, which could be outside of core.
> Justifying that this operation is supported in single-user mode is
> larger than what you are suggesting here..
OK. Actually, I do not have any use-cases in my mind. I played because
I found a special path for single user mode in ReplicationSlotAcquire()
so that I wanted to see the behavior.
Best regards,
Hayato Kuroda
FUJITSU LIMITED
^ permalink raw reply [nested|flat] 31+ messages in thread
* RE: ReplicationSlotRelease() crashes when the instance is in the single user mode
@ 2025-02-19 02:57 Hayato Kuroda (Fujitsu) <[email protected]>
parent: Hayato Kuroda (Fujitsu) <[email protected]>
0 siblings, 1 reply; 31+ messages in thread
From: Hayato Kuroda (Fujitsu) @ 2025-02-19 02:57 UTC (permalink / raw)
To: Hayato Kuroda (Fujitsu) <[email protected]>; +Cc: '[email protected]' <[email protected]>; 'Michael Paquier' <[email protected]>
Dear hackers,
Based on the discussion, I feel it is enough to add quick error out for SQL functions. PSA attached.
Best regards,
Hayato Kuroda
FUJITSU LIMITED
> -----Original Message-----
> From: Hayato Kuroda (Fujitsu) <[email protected]>
> Sent: Tuesday, February 18, 2025 5:24 PM
> To: 'Michael Paquier' <[email protected]>
> Cc: '[email protected]' <[email protected]>
> Subject: RE: ReplicationSlotRelease() crashes when the instance is in the single
> user mode
>
> Dear Michael,
>
> > Ah, good point for the slot drop. So 0ce5cf2ef24f is claiming that
> > some of these manipulations are OK. I didn't suspect this one.
>
> Yeah, I think so.
>
> > Slot advancing is a very different beast, unfortunately, that may
> > depend on many other subsystems. For example with logical slots we
> > would finish by calling rm_decode, which could be outside of core.
> > Justifying that this operation is supported in single-user mode is
> > larger than what you are suggesting here..
>
> OK. Actually, I do not have any use-cases in my mind. I played because
> I found a special path for single user mode in ReplicationSlotAcquire()
> so that I wanted to see the behavior.
>
> Best regards,
> Hayato Kuroda
> FUJITSU LIMITED
>
>
Attachments:
[application/octet-stream] 0001-Prohibit-slot-operations-while-in-the-single-user-mo.patch (2.3K, ../../OSCPR01MB14966F234CD3121F51810D5CCF5C52@OSCPR01MB14966.jpnprd01.prod.outlook.com/2-0001-Prohibit-slot-operations-while-in-the-single-user-mo.patch)
download | inline diff:
From eb72e5e921388fcdacc4d54f89c8d308180acc74 Mon Sep 17 00:00:00 2001
From: Hayato Kuroda <[email protected]>
Date: Wed, 19 Feb 2025 11:37:26 +0900
Subject: [PATCH] Prohibit slot operations while in the single user mode
---
src/backend/replication/slotfuncs.c | 23 +++++++++++++++++++++++
1 file changed, 23 insertions(+)
diff --git a/src/backend/replication/slotfuncs.c b/src/backend/replication/slotfuncs.c
index 8be4b8c65b..cbfadfa428 100644
--- a/src/backend/replication/slotfuncs.c
+++ b/src/backend/replication/slotfuncs.c
@@ -17,6 +17,7 @@
#include "access/xlogrecovery.h"
#include "access/xlogutils.h"
#include "funcapi.h"
+#include "miscadmin.h"
#include "replication/logical.h"
#include "replication/slot.h"
#include "replication/slotsync.h"
@@ -73,6 +74,10 @@ pg_create_physical_replication_slot(PG_FUNCTION_ARGS)
HeapTuple tuple;
Datum result;
+ if (!IsUnderPostmaster)
+ elog(ERROR,
+ "slot operation is prohibited in the single user mode");
+
if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
elog(ERROR, "return type must be a row type");
@@ -179,6 +184,11 @@ pg_create_logical_replication_slot(PG_FUNCTION_ARGS)
Datum values[2];
bool nulls[2];
+ if (!IsUnderPostmaster)
+ elog(ERROR,
+ "slot operation is prohibited in the single user mode");
+
+
if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
elog(ERROR, "return type must be a row type");
@@ -515,6 +525,10 @@ pg_replication_slot_advance(PG_FUNCTION_ARGS)
Assert(!MyReplicationSlot);
+ if (!IsUnderPostmaster)
+ elog(ERROR,
+ "slot operation is prohibited in the single user mode");
+
CheckSlotPermissions();
if (XLogRecPtrIsInvalid(moveto))
@@ -612,6 +626,11 @@ copy_replication_slot(FunctionCallInfo fcinfo, bool logical_slot)
TupleDesc tupdesc;
HeapTuple tuple;
+ if (!IsUnderPostmaster)
+ elog(ERROR,
+ "slot operation is prohibited in the single user mode");
+
+
if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
elog(ERROR, "return type must be a row type");
@@ -866,6 +885,10 @@ pg_sync_replication_slots(PG_FUNCTION_ARGS)
char *err;
StringInfoData app_name;
+ if (!IsUnderPostmaster)
+ elog(ERROR,
+ "slot operation is prohibited in the single user mode");
+
CheckSlotPermissions();
if (!RecoveryInProgress())
--
2.43.5
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: ReplicationSlotRelease() crashes when the instance is in the single user mode
@ 2025-02-20 00:02 Michael Paquier <[email protected]>
parent: Hayato Kuroda (Fujitsu) <[email protected]>
0 siblings, 1 reply; 31+ messages in thread
From: Michael Paquier @ 2025-02-20 00:02 UTC (permalink / raw)
To: Hayato Kuroda (Fujitsu) <[email protected]>; +Cc: '[email protected]' <[email protected]>
On Wed, Feb 19, 2025 at 02:57:34AM +0000, Hayato Kuroda (Fujitsu) wrote:
> Based on the discussion, I feel it is enough to add quick error out
> for SQL functions. PSA attached.
I did not check how these call behave individually, just a few
comments while putting my eyes on the patch.
+ if (!IsUnderPostmaster)
+ elog(ERROR,
+ "slot operation is prohibited in the single user mode");
elog() should not be used for failures that can be user-facing as this
would not provide any translation.
I'd suggest rewording the error message to provide some more context,
as well, say:
"cannot use %s in single-user mode", "function_name"
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, ../../[email protected]/2-signature.asc)
download
^ permalink raw reply [nested|flat] 31+ messages in thread
* RE: ReplicationSlotRelease() crashes when the instance is in the single user mode
@ 2025-02-20 02:22 Hayato Kuroda (Fujitsu) <[email protected]>
parent: Michael Paquier <[email protected]>
0 siblings, 3 replies; 31+ messages in thread
From: Hayato Kuroda (Fujitsu) @ 2025-02-20 02:22 UTC (permalink / raw)
To: 'Michael Paquier' <[email protected]>; +Cc: '[email protected]' <[email protected]>
Dear Michael,
> I did not check how these call behave individually, just a few
> comments while putting my eyes on the patch.
>
> + if (!IsUnderPostmaster)
> + elog(ERROR,
> + "slot operation is prohibited in the single user mode");
>
> elog() should not be used for failures that can be user-facing as this
> would not provide any translation.
I intentionally used elog() because I thought single user mode is not user-facing.
But it is OK for me to use ereport() instead.
> I'd suggest rewording the error message to provide some more context,
> as well, say:
> "cannot use %s in single-user mode", "function_name"
Fixed. PSA new version
Best regards,
Hayato Kuroda
FUJITSU LIMITED
Attachments:
[application/octet-stream] v2-0001-Prohibit-slot-operations-while-in-the-single-user.patch (2.8K, ../../OSCPR01MB14966FCE16ECCF29AE0502AF2F5C42@OSCPR01MB14966.jpnprd01.prod.outlook.com/2-v2-0001-Prohibit-slot-operations-while-in-the-single-user.patch)
download | inline diff:
From 7fe5c8a1854747ce3e14cb9ab0a5a3dc730ae166 Mon Sep 17 00:00:00 2001
From: Hayato Kuroda <[email protected]>
Date: Wed, 19 Feb 2025 11:37:26 +0900
Subject: [PATCH v2] Prohibit slot operations while in the single user mode
---
src/backend/replication/slotfuncs.c | 31 +++++++++++++++++++++++++++++
1 file changed, 31 insertions(+)
diff --git a/src/backend/replication/slotfuncs.c b/src/backend/replication/slotfuncs.c
index f652ec8a73..70c01db891 100644
--- a/src/backend/replication/slotfuncs.c
+++ b/src/backend/replication/slotfuncs.c
@@ -17,6 +17,7 @@
#include "access/xlogrecovery.h"
#include "access/xlogutils.h"
#include "funcapi.h"
+#include "miscadmin.h"
#include "replication/logical.h"
#include "replication/slot.h"
#include "replication/slotsync.h"
@@ -73,6 +74,12 @@ pg_create_physical_replication_slot(PG_FUNCTION_ARGS)
HeapTuple tuple;
Datum result;
+ if (!IsUnderPostmaster)
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("cannot use %s in single-user mode",
+ "pg_create_physical_replication_slot")));
+
if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
elog(ERROR, "return type must be a row type");
@@ -179,6 +186,12 @@ pg_create_logical_replication_slot(PG_FUNCTION_ARGS)
Datum values[2];
bool nulls[2];
+ if (!IsUnderPostmaster)
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("cannot use %s in single-user mode",
+ "pg_create_logical_replication_slot")));
+
if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
elog(ERROR, "return type must be a row type");
@@ -515,6 +528,12 @@ pg_replication_slot_advance(PG_FUNCTION_ARGS)
Assert(!MyReplicationSlot);
+ if (!IsUnderPostmaster)
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("cannot use %s in single-user mode",
+ "pg_replication_slot_advance")));
+
CheckSlotPermissions();
if (XLogRecPtrIsInvalid(moveto))
@@ -612,6 +631,12 @@ copy_replication_slot(FunctionCallInfo fcinfo, bool logical_slot)
TupleDesc tupdesc;
HeapTuple tuple;
+ if (!IsUnderPostmaster)
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("cannot use %s in single-user mode",
+ "pg_copy_replication_slot")));
+
if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
elog(ERROR, "return type must be a row type");
@@ -866,6 +891,12 @@ pg_sync_replication_slots(PG_FUNCTION_ARGS)
char *err;
StringInfoData app_name;
+ if (!IsUnderPostmaster)
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("cannot use %s in single-user mode",
+ "pg_sync_replication_slots")));
+
CheckSlotPermissions();
if (!RecoveryInProgress())
--
2.43.5
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: ReplicationSlotRelease() crashes when the instance is in the single user mode
@ 2025-02-20 02:27 David G. Johnston <[email protected]>
parent: Hayato Kuroda (Fujitsu) <[email protected]>
2 siblings, 0 replies; 31+ messages in thread
From: David G. Johnston @ 2025-02-20 02:27 UTC (permalink / raw)
To: Hayato Kuroda (Fujitsu) <[email protected]>; +Cc: Michael Paquier <[email protected]>; [email protected] <[email protected]>
On Wed, Feb 19, 2025 at 7:23 PM Hayato Kuroda (Fujitsu) <
[email protected]> wrote:
> I intentionally used elog() because I thought single user mode is not
> user-facing.
> But it is OK for me to use ereport() instead.
>
Single-user mode is also known as "Oh crap!" mode, something used when
starting the server in multi-user model fails for non-trivial reasons.
It is also what at least one PostgreSQL Online Service (a.k.a. fiddle) uses
as an implementation choice.
David J.
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: ReplicationSlotRelease() crashes when the instance is in the single user mode
@ 2025-02-20 10:29 Bertrand Drouvot <[email protected]>
parent: Hayato Kuroda (Fujitsu) <[email protected]>
2 siblings, 0 replies; 31+ messages in thread
From: Bertrand Drouvot @ 2025-02-20 10:29 UTC (permalink / raw)
To: Hayato Kuroda (Fujitsu) <[email protected]>; +Cc: 'Michael Paquier' <[email protected]>; '[email protected]' <[email protected]>
Hi,
On Thu, Feb 20, 2025 at 02:22:41AM +0000, Hayato Kuroda (Fujitsu) wrote:
> Dear Michael,
>
Thanks for the report and the patch!
> > I did not check how these call behave individually, just a few
> > comments while putting my eyes on the patch.
> >
> > + if (!IsUnderPostmaster)
> > + elog(ERROR,
> > + "slot operation is prohibited in the single user mode");
> >
> > elog() should not be used for failures that can be user-facing as this
> > would not provide any translation.
>
> I intentionally used elog() because I thought single user mode is not user-facing.
> But it is OK for me to use ereport() instead.
Yeah, I think it's also about using ereport for cases that we think can happen (
and so needs to be translated). In this case, it clearly can happen so ereport()
is better.
> > I'd suggest rewording the error message to provide some more context,
> > as well, say:
> > "cannot use %s in single-user mode", "function_name"
>
> Fixed. PSA new version
=== 1
Nit:
"cannot use function %s in single-user mode", "function_name"" maybe? (that would
somehow match the existing ""user-defined types cannot use subscripting function %s")
=== 2
+ "pg_copy_replication_slot")));
s/pg_copy_replication_slot/copy_replication_slot/ maybe? As it is the function
that would report the error actually (could be called from
pg_copy_logical_replication_slot_[a|b|c] and pg_copy_physical_replication_slot_[a|b]
though).
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 31+ messages in thread
* RE: ReplicationSlotRelease() crashes when the instance is in the single user mode
@ 2025-02-20 12:51 Hayato Kuroda (Fujitsu) <[email protected]>
parent: Hayato Kuroda (Fujitsu) <[email protected]>
2 siblings, 1 reply; 31+ messages in thread
From: Hayato Kuroda (Fujitsu) @ 2025-02-20 12:51 UTC (permalink / raw)
To: '[email protected]' <[email protected]>; +Cc: 'Michael Paquier' <[email protected]>; 'David G. Johnston' <[email protected]>; 'Bertrand Drouvot' <[email protected]>; Zhijie Hou (Fujitsu) <[email protected]>; 'Amit Kapila' <[email protected]>
Dear hackers,
Thanks everyone for giving comments! PSA new version.
What's new:
- Message format was modified to {"cannot use function %s in single-user mode", "function_name"}
- Reporting funcname was adjusted based on the parameters. ternary operator was used.
- Guard was added for functions in logicalfunction.c.
For now, functions for replication origin and replication messages were retained.
I can handle them after the discussion.
Best regards,
Hayato Kuroda
FUJITSU LIMITED
Attachments:
[application/octet-stream] v3-0001-Prohibit-slot-operations-while-in-the-single-user.patch (3.8K, ../../OSCPR01MB149660520A0E53CC53E8B66F1F5C42@OSCPR01MB14966.jpnprd01.prod.outlook.com/2-v3-0001-Prohibit-slot-operations-while-in-the-single-user.patch)
download | inline diff:
From dff4fa6758d4992f0e49879874c317317e122fee Mon Sep 17 00:00:00 2001
From: Hayato Kuroda <[email protected]>
Date: Wed, 19 Feb 2025 11:37:26 +0900
Subject: [PATCH v3] Prohibit slot operations while in the single user mode
---
.../replication/logical/logicalfuncs.c | 9 +++++
src/backend/replication/slotfuncs.c | 33 +++++++++++++++++++
2 files changed, 42 insertions(+)
diff --git a/src/backend/replication/logical/logicalfuncs.c b/src/backend/replication/logical/logicalfuncs.c
index ca53caac2f..a3b52c481e 100644
--- a/src/backend/replication/logical/logicalfuncs.c
+++ b/src/backend/replication/logical/logicalfuncs.c
@@ -113,6 +113,15 @@ pg_logical_slot_get_changes_guts(FunctionCallInfo fcinfo, bool confirm, bool bin
List *options = NIL;
DecodingOutputState *p;
+ if (!IsUnderPostmaster)
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("cannot use function %s in single-user mode",
+ confirm ? (binary ? "pg_logical_slot_get_binary_changes" :
+ "pg_logical_slot_get_changes") :
+ (binary ? "pg_logical_slot_peek_binary_changes" :
+ "pg_logical_slot_peek_changes"))));
+
CheckSlotPermissions();
CheckLogicalDecodingRequirements();
diff --git a/src/backend/replication/slotfuncs.c b/src/backend/replication/slotfuncs.c
index f652ec8a73..d7c4d8b9af 100644
--- a/src/backend/replication/slotfuncs.c
+++ b/src/backend/replication/slotfuncs.c
@@ -17,6 +17,7 @@
#include "access/xlogrecovery.h"
#include "access/xlogutils.h"
#include "funcapi.h"
+#include "miscadmin.h"
#include "replication/logical.h"
#include "replication/slot.h"
#include "replication/slotsync.h"
@@ -73,6 +74,12 @@ pg_create_physical_replication_slot(PG_FUNCTION_ARGS)
HeapTuple tuple;
Datum result;
+ if (!IsUnderPostmaster)
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("cannot use function %s in single-user mode",
+ "pg_create_physical_replication_slot")));
+
if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
elog(ERROR, "return type must be a row type");
@@ -179,6 +186,12 @@ pg_create_logical_replication_slot(PG_FUNCTION_ARGS)
Datum values[2];
bool nulls[2];
+ if (!IsUnderPostmaster)
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("cannot use function %s in single-user mode",
+ "pg_create_logical_replication_slot")));
+
if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
elog(ERROR, "return type must be a row type");
@@ -515,6 +528,12 @@ pg_replication_slot_advance(PG_FUNCTION_ARGS)
Assert(!MyReplicationSlot);
+ if (!IsUnderPostmaster)
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("cannot use function %s in single-user mode",
+ "pg_replication_slot_advance")));
+
CheckSlotPermissions();
if (XLogRecPtrIsInvalid(moveto))
@@ -612,6 +631,14 @@ copy_replication_slot(FunctionCallInfo fcinfo, bool logical_slot)
TupleDesc tupdesc;
HeapTuple tuple;
+ if (!IsUnderPostmaster)
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("cannot use function %s in single-user mode",
+ logical_slot ?
+ "pg_copy_logical_replication_slot" :
+ "pg_copy_physical_replication_slot")));
+
if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
elog(ERROR, "return type must be a row type");
@@ -866,6 +893,12 @@ pg_sync_replication_slots(PG_FUNCTION_ARGS)
char *err;
StringInfoData app_name;
+ if (!IsUnderPostmaster)
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("cannot use function %s in single-user mode",
+ "pg_sync_replication_slots")));
+
CheckSlotPermissions();
if (!RecoveryInProgress())
--
2.43.5
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: ReplicationSlotRelease() crashes when the instance is in the single user mode
@ 2025-02-22 04:58 Amit Kapila <[email protected]>
parent: Hayato Kuroda (Fujitsu) <[email protected]>
0 siblings, 1 reply; 31+ messages in thread
From: Amit Kapila @ 2025-02-22 04:58 UTC (permalink / raw)
To: Hayato Kuroda (Fujitsu) <[email protected]>; +Cc: [email protected] <[email protected]>; Michael Paquier <[email protected]>; David G. Johnston <[email protected]>; Bertrand Drouvot <[email protected]>; Zhijie Hou (Fujitsu) <[email protected]>
On Thu, Feb 20, 2025 at 6:21 PM Hayato Kuroda (Fujitsu)
<[email protected]> wrote:
>
> Dear hackers,
>
> Thanks everyone for giving comments! PSA new version.
> What's new:
>
> - Message format was modified to {"cannot use function %s in single-user mode", "function_name"}
> - Reporting funcname was adjusted based on the parameters. ternary operator was used.
> - Guard was added for functions in logicalfunction.c.
>
Shouldn't such a check be present in the CheckSlotPermissions() kind
of function to perform it in the central place?
> For now, functions for replication origin and replication messages were retained.
> I can handle them after the discussion.
>
Which other functions do we see similar restrictions? I checked
"sequence manipulation functions" (1), and "Transaction ID and
Snapshot Information Functions" (2) but couldn't see similar
restrictions.
(1) - https://www.postgresql.org/docs/current/functions-sequence.html
(2) - https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-INFO-SNAPSHOT
--
With Regards,
Amit Kapila.
^ permalink raw reply [nested|flat] 31+ messages in thread
* RE: ReplicationSlotRelease() crashes when the instance is in the single user mode
@ 2025-02-27 07:58 Hayato Kuroda (Fujitsu) <[email protected]>
parent: Amit Kapila <[email protected]>
0 siblings, 1 reply; 31+ messages in thread
From: Hayato Kuroda (Fujitsu) @ 2025-02-27 07:58 UTC (permalink / raw)
To: 'Amit Kapila' <[email protected]>; +Cc: [email protected] <[email protected]>; Michael Paquier <[email protected]>; David G. Johnston <[email protected]>; Bertrand Drouvot <[email protected]>; Zhijie Hou (Fujitsu) <[email protected]>
Dear Amit,
> Shouldn't such a check be present in the CheckSlotPermissions() kind
> of function to perform it in the central place?
OK. I checked whether we can reuse pre-existing functions, but it seems not appropriate.
CheckSlotPermissions() is called even by pg_drop_replication_slot(), and
CheckSlotRequirements() is not called by pg_sync_replication_slots(). I defined new
function CheckSlotIsInSingleUserMode() and put the check there.
I removed function name from the ereport(), but I feel it is enough - CheckSlotPermissions()
and CheckSlotRequirements() do not have.
> > For now, functions for replication origin and replication messages were
> retained.
> > I can handle them after the discussion.
> >
>
> Which other functions do we see similar restrictions? I checked
> "sequence manipulation functions" (1), and "Transaction ID and
> Snapshot Information Functions" (2) but couldn't see similar
> restrictions.
>
> (1) - https://www.postgresql.org/docs/current/functions-sequence.html
> (2) -
> https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-INF
> O-SNAPSHOT
I grepped sources and could not find explicit limitations neither. So...this might
be overkill.　But I still think the restriction is OK for the slot - no need to do
some efforts for accepting single-user mode, just close the cover.
Best regards,
Hayato Kuroda
FUJITSU LIMITED
Attachments:
[application/octet-stream] v4-0001-Prohibit-slot-operations-while-in-the-single-user.patch (3.9K, ../../OSCPR01MB149669CFD736D48C4450337DDF5CD2@OSCPR01MB14966.jpnprd01.prod.outlook.com/2-v4-0001-Prohibit-slot-operations-while-in-the-single-user.patch)
download | inline diff:
From b19815119909d0529d6c52889ab0959ec44e7e7f Mon Sep 17 00:00:00 2001
From: Hayato Kuroda <[email protected]>
Date: Wed, 19 Feb 2025 11:37:26 +0900
Subject: [PATCH v4] Prohibit slot operations while in the single user mode
---
src/backend/replication/logical/logicalfuncs.c | 2 ++
src/backend/replication/slot.c | 12 ++++++++++++
src/backend/replication/slotfuncs.c | 12 ++++++++++++
src/include/replication/slot.h | 1 +
4 files changed, 27 insertions(+)
diff --git a/src/backend/replication/logical/logicalfuncs.c b/src/backend/replication/logical/logicalfuncs.c
index ca53caac2f..178a07af7d 100644
--- a/src/backend/replication/logical/logicalfuncs.c
+++ b/src/backend/replication/logical/logicalfuncs.c
@@ -113,6 +113,8 @@ pg_logical_slot_get_changes_guts(FunctionCallInfo fcinfo, bool confirm, bool bin
List *options = NIL;
DecodingOutputState *p;
+ CheckSlotIsInSingleUserMode();
+
CheckSlotPermissions();
CheckLogicalDecodingRequirements();
diff --git a/src/backend/replication/slot.c b/src/backend/replication/slot.c
index 719e531eb9..d041adfe48 100644
--- a/src/backend/replication/slot.c
+++ b/src/backend/replication/slot.c
@@ -1442,6 +1442,18 @@ CheckSlotPermissions(void)
"REPLICATION")));
}
+/*
+ * Check whether the instance is in single-user mode.
+ */
+void
+CheckSlotIsInSingleUserMode(void)
+{
+ if (!IsUnderPostmaster)
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("replication slots cannot be used in single-user mode")));
+}
+
/*
* Reserve WAL for the currently active slot.
*
diff --git a/src/backend/replication/slotfuncs.c b/src/backend/replication/slotfuncs.c
index 146eef5871..b669774c87 100644
--- a/src/backend/replication/slotfuncs.c
+++ b/src/backend/replication/slotfuncs.c
@@ -17,6 +17,7 @@
#include "access/xlogrecovery.h"
#include "access/xlogutils.h"
#include "funcapi.h"
+#include "miscadmin.h"
#include "replication/logical.h"
#include "replication/slot.h"
#include "replication/slotsync.h"
@@ -76,6 +77,8 @@ pg_create_physical_replication_slot(PG_FUNCTION_ARGS)
if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
elog(ERROR, "return type must be a row type");
+ CheckSlotIsInSingleUserMode();
+
CheckSlotPermissions();
CheckSlotRequirements();
@@ -182,6 +185,8 @@ pg_create_logical_replication_slot(PG_FUNCTION_ARGS)
if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
elog(ERROR, "return type must be a row type");
+ CheckSlotIsInSingleUserMode();
+
CheckSlotPermissions();
CheckLogicalDecodingRequirements();
@@ -515,6 +520,8 @@ pg_replication_slot_advance(PG_FUNCTION_ARGS)
Assert(!MyReplicationSlot);
+ CheckSlotIsInSingleUserMode();
+
CheckSlotPermissions();
if (XLogRecPtrIsInvalid(moveto))
@@ -612,9 +619,12 @@ copy_replication_slot(FunctionCallInfo fcinfo, bool logical_slot)
TupleDesc tupdesc;
HeapTuple tuple;
+
if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
elog(ERROR, "return type must be a row type");
+ CheckSlotIsInSingleUserMode();
+
CheckSlotPermissions();
if (logical_slot)
@@ -871,6 +881,8 @@ pg_sync_replication_slots(PG_FUNCTION_ARGS)
char *err;
StringInfoData app_name;
+ CheckSlotIsInSingleUserMode();
+
CheckSlotPermissions();
if (!RecoveryInProgress())
diff --git a/src/include/replication/slot.h b/src/include/replication/slot.h
index f5a24ccfbf..24523b1001 100644
--- a/src/include/replication/slot.h
+++ b/src/include/replication/slot.h
@@ -306,6 +306,7 @@ extern void CheckPointReplicationSlots(bool is_shutdown);
extern void CheckSlotRequirements(void);
extern void CheckSlotPermissions(void);
+extern void CheckSlotIsInSingleUserMode(void);
extern ReplicationSlotInvalidationCause
GetSlotInvalidationCause(const char *invalidation_reason);
extern const char *GetSlotInvalidationCauseName(ReplicationSlotInvalidationCause cause);
--
2.43.5
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: ReplicationSlotRelease() crashes when the instance is in the single user mode
@ 2025-02-28 03:13 Amit Kapila <[email protected]>
parent: Hayato Kuroda (Fujitsu) <[email protected]>
0 siblings, 1 reply; 31+ messages in thread
From: Amit Kapila @ 2025-02-28 03:13 UTC (permalink / raw)
To: Hayato Kuroda (Fujitsu) <[email protected]>; +Cc: [email protected] <[email protected]>; Michael Paquier <[email protected]>; David G. Johnston <[email protected]>; Bertrand Drouvot <[email protected]>; Zhijie Hou (Fujitsu) <[email protected]>
On Thu, Feb 27, 2025 at 1:29 PM Hayato Kuroda (Fujitsu)
<[email protected]> wrote:
>
> >
> > Which other functions do we see similar restrictions? I checked
> > "sequence manipulation functions" (1), and "Transaction ID and
> > Snapshot Information Functions" (2) but couldn't see similar
> > restrictions.
> >
> > (1) - https://www.postgresql.org/docs/current/functions-sequence.html
> > (2) -
> > https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-INF
> > O-SNAPSHOT
>
> I grepped sources and could not find explicit limitations neither. So...this might
> be overkill.
>
Yes, this is my worry.
>　But I still think the restriction is OK for the slot - no need to do
> some efforts for accepting single-user mode, just close the cover.
>
I understand that we may not have a clear use case for this to work in
single-user mode. But how will we define the boundary in similar
cases? I mean, we should have some rule for such exposed functions,
and it should be followed uniformly. Now, if one needs a bigger or
complex change to make the function work in single-user mode, then it
is easy to take an exception from what is currently being followed in
the code. However, if the change is as trivial as you proposed in the
first email, why not go with that and make this function work in
single-user mode?
--
With Regards,
Amit Kapila.
^ permalink raw reply [nested|flat] 31+ messages in thread
* RE: ReplicationSlotRelease() crashes when the instance is in the single user mode
@ 2025-02-28 05:41 Hayato Kuroda (Fujitsu) <[email protected]>
parent: Amit Kapila <[email protected]>
0 siblings, 1 reply; 31+ messages in thread
From: Hayato Kuroda (Fujitsu) @ 2025-02-28 05:41 UTC (permalink / raw)
To: 'Amit Kapila' <[email protected]>; Michael Paquier <[email protected]>; David G. Johnston <[email protected]>; Zhijie Hou (Fujitsu) <[email protected]>; Tom Lane <[email protected]>; Bertrand Drouvot <[email protected]>; +Cc: [email protected] <[email protected]>
Dear Amit,
> I understand that we may not have a clear use case for this to work in
> single-user mode. But how will we define the boundary in similar
> cases? I mean, we should have some rule for such exposed functions,
> and it should be followed uniformly. Now, if one needs a bigger or
> complex change to make the function work in single-user mode, then it
> is easy to take an exception from what is currently being followed in
> the code. However, if the change is as trivial as you proposed in the
> first email, why not go with that and make this function work in
> single-user mode?
Hmm, the opinion about this is completely opposite with other reviewers. I want
to ask them again how you feel. I also added Tom who pointed out in the initial
thread.
Question: how you feel to restrict SQL functions for slot during the single-user
mode? Nobody has considered use cases for it; we do not have concrete theory and
similar cases. And needed band-aid to work seems very small.
Best regards,
Hayato Kuroda
FUJITSU LIMITED
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: ReplicationSlotRelease() crashes when the instance is in the single user mode
@ 2025-08-05 16:51 Paul A Jungwirth <[email protected]>
parent: Hayato Kuroda (Fujitsu) <[email protected]>
0 siblings, 2 replies; 31+ messages in thread
From: Paul A Jungwirth @ 2025-08-05 16:51 UTC (permalink / raw)
To: Hayato Kuroda (Fujitsu) <[email protected]>; +Cc: Amit Kapila <[email protected]>; Michael Paquier <[email protected]>; David G. Johnston <[email protected]>; Zhijie Hou (Fujitsu) <[email protected]>; Tom Lane <[email protected]>; Bertrand Drouvot <[email protected]>; [email protected] <[email protected]>; Mutaamba Maasha <[email protected]>
Mutaamba (cc'd) and I reviewed this patch together.
To summarize the patch and thread so far: The patch adds a new
function, CheckSlotIsInSingleUserMode. If true then we raise an error.
Otherwise we would trip an assert in ReplicationSlotRelease requiring
the slot to have an active_pid, which is never set in single-user
mode. We do want to support pg_drop_replication_slot in single-user
mode, but not other replication slot functions. In particular,
advancing the slot may call non-core code, which seems risky in
single-user mode.
The patch does not apply. The attached v5 fixes it with a small update
to the context for the slot.h hunk.
The commit message needs some explanation. Why are we doing this? What
does the patch do? What alternatives did we consider?
The current error message seems reasonable.
The patch has no docs. If we plan to forbid some functions in
single-user mode, we should document which ones (e.g. in func.sgml).
There are no tests. Do we have any tests at all for single-user mode?
The only one I see is in recovery/t/017_shm.pl. What if we had tests
that ran the regress suite in single-user mode? Basically instead of
psql we run postgres --single? Do we expect a lot of it would fail? Is
it small enough the test could maintain a diff that expects those
failures? I'm not saying we should do that as part of this patch, but
is there any interest in that? Since single-user mode is most often
used when things are broken, it's a harsh place to hit a bug.
The patch lacks source comments. Something on
CheckSlotIsInSingleUserMode explaining why would be good.
In ReplicationSlotRelease, why only assert that `slot->active_pid !=
0`? Why not assert that it's MyProcPid (even outside single-user
mode)? Can one backend really release a slot while another is using
it? Can you drop it? Maybe you can copy it?
Are we calling CheckSlotIsInSingleUserMode from everywhere that needs
it? We tried to check all the functions that call
ReplicationSlotCreate, ReplicationSlotRelease, and
update_synced_slots_inactive_since (since they all have these
asserts). To call out a few:
The pg_replication_origin_* functions don't call the Assert-ing functions.
binary_upgrade_logical_slot_has_caught_up - Not available from the command line.
WalSndErrorCleanup - Probably not used in single-user mode?
We also see that PostgresMain calls ReplicationSlotRelease from its
error handler. Presumably single-user mode would be executing
PostgresSingleUserMain instead, but still perhaps we should call
CheckSlotIsInSingleUserMode here just as a sanity-check?
On Thu, Feb 27, 2025 at 9:42 PM Hayato Kuroda (Fujitsu)
<[email protected]> wrote:
> > I understand that we may not have a clear use case for this to work in
> > single-user mode. But how will we define the boundary in similar
> > cases? I mean, we should have some rule for such exposed functions,
> > and it should be followed uniformly. Now, if one needs a bigger or
> > complex change to make the function work in single-user mode, then it
> > is easy to take an exception from what is currently being followed in
> > the code. However, if the change is as trivial as you proposed in the
> > first email, why not go with that and make this function work in
> > single-user mode?
>
> Hmm, the opinion about this is completely opposite with other reviewers. I want
> to ask them again how you feel. I also added Tom who pointed out in the initial
> thread.
>
> Question: how you feel to restrict SQL functions for slot during the single-user
> mode? Nobody has considered use cases for it; we do not have concrete theory and
> similar cases. And needed band-aid to work seems very small.
No one has replied yet, but I vote for forbidding these functions. I
can't articulate a full theory for which functions we restrict in
single-user mode, and I think we should permit as much as possible.
But any theory would weigh usefulness against risk. Here no one has
found a use-case for several years, and executing user-supplied code
in an unusual, higher-risk scenario seems like a good reason to be
cautious. Also without tests for single-user mode, I'm extra wary. If
single-user support is desired by someone, they could submit a patch.
Yours,
--
Paul ~{:-)
[email protected]
Attachments:
[text/x-patch] v5-0001-Prohibit-slot-operations-while-in-the-single-user.patch (3.9K, ../../CA+renyWi+bbD8M8CDAaOMGbUrXptKLCK8zQWEhXy00PJuJqNuQ@mail.gmail.com/2-v5-0001-Prohibit-slot-operations-while-in-the-single-user.patch)
download | inline diff:
From b19815119909d0529d6c52889ab0959ec44e7e7f Mon Sep 17 00:00:00 2001
From: Hayato Kuroda <[email protected]>
Date: Wed, 19 Feb 2025 11:37:26 +0900
Subject: [PATCH v4] Prohibit slot operations while in the single user mode
---
src/backend/replication/logical/logicalfuncs.c | 2 ++
src/backend/replication/slot.c | 12 ++++++++++++
src/backend/replication/slotfuncs.c | 12 ++++++++++++
src/include/replication/slot.h | 1 +
4 files changed, 27 insertions(+)
diff --git a/src/backend/replication/logical/logicalfuncs.c b/src/backend/replication/logical/logicalfuncs.c
index ca53caac2f..178a07af7d 100644
--- a/src/backend/replication/logical/logicalfuncs.c
+++ b/src/backend/replication/logical/logicalfuncs.c
@@ -113,6 +113,8 @@ pg_logical_slot_get_changes_guts(FunctionCallInfo fcinfo, bool confirm, bool bin
List *options = NIL;
DecodingOutputState *p;
+ CheckSlotIsInSingleUserMode();
+
CheckSlotPermissions();
CheckLogicalDecodingRequirements();
diff --git a/src/backend/replication/slot.c b/src/backend/replication/slot.c
index 719e531eb9..d041adfe48 100644
--- a/src/backend/replication/slot.c
+++ b/src/backend/replication/slot.c
@@ -1442,6 +1442,18 @@ CheckSlotPermissions(void)
"REPLICATION")));
}
+/*
+ * Check whether the instance is in single-user mode.
+ */
+void
+CheckSlotIsInSingleUserMode(void)
+{
+ if (!IsUnderPostmaster)
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("replication slots cannot be used in single-user mode")));
+}
+
/*
* Reserve WAL for the currently active slot.
*
diff --git a/src/backend/replication/slotfuncs.c b/src/backend/replication/slotfuncs.c
index 146eef5871..b669774c87 100644
--- a/src/backend/replication/slotfuncs.c
+++ b/src/backend/replication/slotfuncs.c
@@ -17,6 +17,7 @@
#include "access/xlogrecovery.h"
#include "access/xlogutils.h"
#include "funcapi.h"
+#include "miscadmin.h"
#include "replication/logical.h"
#include "replication/slot.h"
#include "replication/slotsync.h"
@@ -76,6 +77,8 @@ pg_create_physical_replication_slot(PG_FUNCTION_ARGS)
if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
elog(ERROR, "return type must be a row type");
+ CheckSlotIsInSingleUserMode();
+
CheckSlotPermissions();
CheckSlotRequirements();
@@ -182,6 +185,8 @@ pg_create_logical_replication_slot(PG_FUNCTION_ARGS)
if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
elog(ERROR, "return type must be a row type");
+ CheckSlotIsInSingleUserMode();
+
CheckSlotPermissions();
CheckLogicalDecodingRequirements();
@@ -515,6 +520,8 @@ pg_replication_slot_advance(PG_FUNCTION_ARGS)
Assert(!MyReplicationSlot);
+ CheckSlotIsInSingleUserMode();
+
CheckSlotPermissions();
if (XLogRecPtrIsInvalid(moveto))
@@ -612,9 +619,12 @@ copy_replication_slot(FunctionCallInfo fcinfo, bool logical_slot)
TupleDesc tupdesc;
HeapTuple tuple;
+
if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
elog(ERROR, "return type must be a row type");
+ CheckSlotIsInSingleUserMode();
+
CheckSlotPermissions();
if (logical_slot)
@@ -871,6 +881,8 @@ pg_sync_replication_slots(PG_FUNCTION_ARGS)
char *err;
StringInfoData app_name;
+ CheckSlotIsInSingleUserMode();
+
CheckSlotPermissions();
if (!RecoveryInProgress())
diff --git a/src/include/replication/slot.h b/src/include/replication/slot.h
index f5a24ccfbf..24523b1001 100644
--- a/src/include/replication/slot.h
+++ b/src/include/replication/slot.h
@@ -306,6 +306,7 @@ extern void CheckPointReplicationSlots(bool is_shutdown);
extern void CheckSlotRequirements(void);
extern void CheckSlotPermissions(void);
+extern void CheckSlotIsInSingleUserMode(void);
extern ReplicationSlotInvalidationCause
GetSlotInvalidationCause(const char *cause_name);
extern const char *GetSlotInvalidationCauseName(ReplicationSlotInvalidationCause cause);
--
2.43.5
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: ReplicationSlotRelease() crashes when the instance is in the single user mode
@ 2025-08-11 18:25 Robert Haas <[email protected]>
parent: Paul A Jungwirth <[email protected]>
1 sibling, 1 reply; 31+ messages in thread
From: Robert Haas @ 2025-08-11 18:25 UTC (permalink / raw)
To: Paul A Jungwirth <[email protected]>; +Cc: Hayato Kuroda (Fujitsu) <[email protected]>; Amit Kapila <[email protected]>; Michael Paquier <[email protected]>; David G. Johnston <[email protected]>; Zhijie Hou (Fujitsu) <[email protected]>; Tom Lane <[email protected]>; Bertrand Drouvot <[email protected]>; [email protected] <[email protected]>; Mutaamba Maasha <[email protected]>
On Tue, Aug 5, 2025 at 12:51 PM Paul A Jungwirth
<[email protected]> wrote:
> No one has replied yet, but I vote for forbidding these functions. I
> can't articulate a full theory for which functions we restrict in
> single-user mode, and I think we should permit as much as possible.
> But any theory would weigh usefulness against risk. Here no one has
> found a use-case for several years, and executing user-supplied code
> in an unusual, higher-risk scenario seems like a good reason to be
> cautious. Also without tests for single-user mode, I'm extra wary. If
> single-user support is desired by someone, they could submit a patch.
I don't feel good about the direction from which this patch is
attacking the problem. The original stack trace looks like this:
postgres(ExceptionalCondition+0xab)[0xb86a2a]
postgres(ReplicationSlotRelease+0x5a)[0x8df10b]
postgres(pg_replication_slot_advance+0x330)[0x8e46ed]
ReplicationSlotRelease() is called near the end of
pg_replication_slot_advance. IIUC, this means that we successfully
acquired the slot and did all the work, and then failed a sanity check
afterward. So, I see two possibilities: either it's not OK to acquire
a replication slot in single-user mode, in which case this should have
failed earlier, or it's also OK to release it, in which case this
should not have failed at all. Interestingly, I see that
ReplicationSlotAcquire() features a special case whose specific
purpose is to allow it to work in single-user mode, which makes me
rather suspect that the latter is correct. It is possible that it was
intentional that acquiring works in single-user mode and dropping
(other than via ReplicationSlotDropAcquired) does not, but so far I
see nothing in the comments suggesting that.
In any event, that inconsistency between how ReplicationSlotAcquire()
works and how ReplicationSlotRelease() works seems to me to be the
core thing that should be fixed here. If the selected fix also
requires error checks in higher-level functions, as added by this
patch, then well and good. But right now, the proposed patch isn't
trying to fix the definitional inconsistency in the underlying layer
at all, and is instead just installing guard rails to try to keep
anyone from bumping into it. That seems like a bad direction to go --
it feels like we're blocking access to potentially-useful
functionality because of an assertion failure that might just be
slightly sloppy coding.
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 31+ messages in thread
* RE: ReplicationSlotRelease() crashes when the instance is in the single user mode
@ 2025-08-13 07:21 Hayato Kuroda (Fujitsu) <[email protected]>
parent: Robert Haas <[email protected]>
0 siblings, 1 reply; 31+ messages in thread
From: Hayato Kuroda (Fujitsu) @ 2025-08-13 07:21 UTC (permalink / raw)
To: 'Robert Haas' <[email protected]>; Paul A Jungwirth <[email protected]>; Mutaamba Maasha <[email protected]>; +Cc: Amit Kapila <[email protected]>; Michael Paquier <[email protected]>; David G. Johnston <[email protected]>; Zhijie Hou (Fujitsu) <[email protected]>; Tom Lane <[email protected]>; Bertrand Drouvot <[email protected]>; [email protected] <[email protected]>
Dear Robert, Paul, Mutaamba,
Sorry for the late reply. I was in the business trip.
> I don't feel good about the direction from which this patch is
> attacking the problem. The original stack trace looks like this:
>
> postgres(ExceptionalCondition+0xab)[0xb86a2a]
> postgres(ReplicationSlotRelease+0x5a)[0x8df10b]
> postgres(pg_replication_slot_advance+0x330)[0x8e46ed]
>
> ReplicationSlotRelease() is called near the end of
> pg_replication_slot_advance. IIUC, this means that we successfully
> acquired the slot and did all the work, and then failed a sanity check
> afterward.
Exactly. The slot is trying to be released after most of tasks are done.
```
#6 0x00000000008fd20b in pg_replication_slot_advance (fcinfo=0x23b4428)
at ../postgres/src/backend/replication/slotfuncs.c:588
588 ReplicationSlotRelease();
(gdb) list
583 * advancing potentially done.
584 */
585 ReplicationSlotsComputeRequiredXmin(false);
586 ReplicationSlotsComputeRequiredLSN();
587
588 ReplicationSlotRelease();
```
> So, I see two possibilities: either it's not OK to acquire
> a replication slot in single-user mode, in which case this should have
> failed earlier, or it's also OK to release it, in which case this
> should not have failed at all. Interestingly, I see that
> ReplicationSlotAcquire() features a special case whose specific
> purpose is to allow it to work in single-user mode, which makes me
> rather suspect that the latter is correct.
Agreed.
> It is possible that it was
> intentional that acquiring works in single-user mode and dropping
> (other than via ReplicationSlotDropAcquired) does not, but so far I
> see nothing in the comments suggesting that.
>
> In any event, that inconsistency between how ReplicationSlotAcquire()
> works and how ReplicationSlotRelease() works seems to me to be the
> core thing that should be fixed here. If the selected fix also
> requires error checks in higher-level functions, as added by this
> patch, then well and good. But right now, the proposed patch isn't
> trying to fix the definitional inconsistency in the underlying layer
> at all, and is instead just installing guard rails to try to keep
> anyone from bumping into it. That seems like a bad direction to go --
To confirm; your point is that we should firstly fix to allow acquiring/releasing
slots in the mode, then consider additional guards, is it right? Valid point.
We've not deeply considered but ideas described in [1] could be used.
> it feels like we're blocking access to potentially-useful
> functionality because of an assertion failure that might just be
> slightly sloppy coding.
I still cannot find enough use-cases to allow manipulating slots, though.
[1]: https://www.postgresql.org/message-id/OSCPR01MB14966ED588A0328DAEBE8CB25F5FA2%40OSCPR01MB14966.jpnpr...
Best regards,
Hayato Kuroda
FUJITSU LIMITED
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: ReplicationSlotRelease() crashes when the instance is in the single user mode
@ 2025-08-13 20:04 Robert Haas <[email protected]>
parent: Hayato Kuroda (Fujitsu) <[email protected]>
0 siblings, 1 reply; 31+ messages in thread
From: Robert Haas @ 2025-08-13 20:04 UTC (permalink / raw)
To: Hayato Kuroda (Fujitsu) <[email protected]>; +Cc: Paul A Jungwirth <[email protected]>; Mutaamba Maasha <[email protected]>; Amit Kapila <[email protected]>; Michael Paquier <[email protected]>; David G. Johnston <[email protected]>; Zhijie Hou (Fujitsu) <[email protected]>; Tom Lane <[email protected]>; Bertrand Drouvot <[email protected]>; [email protected] <[email protected]>
On Wed, Aug 13, 2025 at 3:21 AM Hayato Kuroda (Fujitsu)
<[email protected]> wrote:
> To confirm; your point is that we should firstly fix to allow acquiring/releasing
> slots in the mode, then consider additional guards, is it right? Valid point.
Yes.
> I still cannot find enough use-cases to allow manipulating slots, though.
The use case for single-user mode is quite limited in general, but
most things work in single-user mode unless they are something that
intrinsically can't. For example, parallel query cannot be used in
single-user mode, because there is only one process. Replication
cannot work, for the same reason. But manual slot operations can work,
so I do not think it is good to arbitrarily prohibit them. We do not
need a reason to specifically allow them; it is enough that there is
no good reason for them to be blocked.
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 31+ messages in thread
* RE: ReplicationSlotRelease() crashes when the instance is in the single user mode
@ 2025-08-18 08:47 Hayato Kuroda (Fujitsu) <[email protected]>
parent: Paul A Jungwirth <[email protected]>
1 sibling, 1 reply; 31+ messages in thread
From: Hayato Kuroda (Fujitsu) @ 2025-08-18 08:47 UTC (permalink / raw)
To: 'Paul A Jungwirth' <[email protected]>; Mutaamba Maasha <[email protected]>; +Cc: Amit Kapila <[email protected]>; Michael Paquier <[email protected]>; David G. Johnston <[email protected]>; Zhijie Hou (Fujitsu) <[email protected]>; Tom Lane <[email protected]>; Bertrand Drouvot <[email protected]>; [email protected] <[email protected]>
Dear Paul, Mutaamba,
Here are updated patches. Based on the Robert's suggestion, I separated into two parts.
0001 fixed the original issue and 0002 prohibited the slot manipulation in
single-user mode. I want to focus on 0001 first because on one would argue it.
All comments from you were included in 0002.
> The patch does not apply. The attached v5 fixes it with a small update
> to the context for the slot.h hunk.
Oh, I didn't realize because cfbot said OK. Anyway, new patch could be applied atop HEAD.
> The commit message needs some explanation. Why are we doing this? What
> does the patch do? What alternatives did we consider?
Updated. How do you feel?
> The patch has no docs. If we plan to forbid some functions in
> single-user mode, we should document which ones (e.g. in func.sgml).
A statement was added.
> There are no tests. Do we have any tests at all for single-user mode?
> The only one I see is in recovery/t/017_shm.pl. What if we had tests
> that ran the regress suite in single-user mode? Basically instead of
> psql we run postgres --single? Do we expect a lot of it would fail? Is
> it small enough the test could maintain a diff that expects those
> failures? I'm not saying we should do that as part of this patch, but
> is there any interest in that? Since single-user mode is most often
> used when things are broken, it's a harsh place to hit a bug.
>
> The patch lacks source comments. Something on
> CheckSlotIsInSingleUserMode explaining why would be good.
Comments were added in all caller functions.
> In ReplicationSlotRelease, why only assert that `slot->active_pid !=
> 0`? Why not assert that it's MyProcPid (even outside single-user
> mode)? Can one backend really release a slot while another is using
> it? Can you drop it? Maybe you can copy it?
You meant we should assert `slot->active_pid == MyProcPid`, right?
Naively considered it can be fix, but it is out-of-scope of the thread. It is
existing code, should be discussed verified in another place.
> Are we calling CheckSlotIsInSingleUserMode from everywhere that needs
> it? We tried to check all the functions that call
> ReplicationSlotCreate, ReplicationSlotRelease, and
> update_synced_slots_inactive_since (since they all have these
> asserts). To call out a few:
>
> The pg_replication_origin_* functions don't call the Assert-ing functions.
You asked that whether we should call CheckSlotIsInSingleUserMode in
pg_replication_origin_* APIs, right? It depends on the policy. If we want to
prohibit all replication-related command, it should be. It is still under
discussion, so I did not touch.
> binary_upgrade_logical_slot_has_caught_up - Not available from the command
> line.
I found that we can in both single-user and binary-upgrade mode. At that time
the function could be called:
```
$ postgres --single -D data/ postgres -b
PostgreSQL stand-alone backend 19devel
backend> SELECT binary_upgrade_logical_slot_has_caught_up('test');
1: binary_upgrade_logical_slot_has_caught_up (typeid = 16, len = 1, typmod = -1, byval = t)
----
LOG: starting logical decoding for slot "test"
DETAIL: Streaming transactions committing after 0/0182C640, reading WAL from 0/0182C608.
STATEMENT: SELECT binary_upgrade_logical_slot_has_caught_up('test');
LOG: logical decoding found consistent point at 0/0182C608
DETAIL: There are no running transactions.
STATEMENT: SELECT binary_upgrade_logical_slot_has_caught_up('test');
1: binary_upgrade_logical_slot_has_caught_up = "f" (typeid = 16, len = 1, typmod = -1, byval = t)
----
```
I don't think this is a realistic case, but the check was added.
> WalSndErrorCleanup - Probably not used in single-user mode?
IIUC we won't reach in the single-user mode. Hence it was not called intentionally.
> We also see that PostgresMain calls ReplicationSlotRelease from its
> error handler. Presumably single-user mode would be executing
> PostgresSingleUserMain instead,
ISTM, PostgresMain() would be called from the PostgresSingleUserMain().
> but still perhaps we should call
> CheckSlotIsInSingleUserMode here just as a sanity-check?
I feel this code is to release the acquired slot when ERROR was raised. Since we
have already covered the entrance, it is not needed.
[1]: https://www.postgresql.org/message-id/CA%2BTgmobspWhoMysNHL9b3C9xi%3DOpHdBYhtAgDH4N_A2foyjN-w%40mail...
Best regards,
Hayato Kuroda
FUJITSU LIMITED
Attachments:
[application/octet-stream] v5-0001-Set-ReplicationSlot-active_pid-even-in-single-use.patch (929B, ../../OSCPR01MB14966B39B3ED21229EAEFA10FF531A@OSCPR01MB14966.jpnprd01.prod.outlook.com/2-v5-0001-Set-ReplicationSlot-active_pid-even-in-single-use.patch)
download | inline diff:
From 201b872dbcdfb8a1182edde13b14a24d5aa43226 Mon Sep 17 00:00:00 2001
From: Hayato Kuroda <[email protected]>
Date: Mon, 18 Aug 2025 11:47:43 +0900
Subject: [PATCH v5 1/2] Set ReplicationSlot::active_pid even in single-user
mode
Slot manipulation functions except drop could be failed in single-user mode, because
active_pid was not set. Actually the attribute is no-op in the mode but fix the
inconsisntency anyway.
---
src/backend/replication/slot.c | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/src/backend/replication/slot.c b/src/backend/replication/slot.c
index 8605776ad86..fd0fdb96d42 100644
--- a/src/backend/replication/slot.c
+++ b/src/backend/replication/slot.c
@@ -653,7 +653,7 @@ retry:
}
else
{
- active_pid = MyProcPid;
+ s->active_pid = active_pid = MyProcPid;
ReplicationSlotSetInactiveSince(s, 0, true);
}
LWLockRelease(ReplicationSlotControlLock);
--
2.47.1
[application/octet-stream] v5-0002-Prohibit-slot-manipulation-while-in-single-user-m.patch (5.8K, ../../OSCPR01MB14966B39B3ED21229EAEFA10FF531A@OSCPR01MB14966.jpnprd01.prod.outlook.com/3-v5-0002-Prohibit-slot-manipulation-while-in-single-user-m.patch)
download | inline diff:
From 3ac78c7a7a42afbbc9cb09c03b568c1c39cd035c Mon Sep 17 00:00:00 2001
From: Hayato Kuroda <[email protected]>
Date: Wed, 19 Feb 2025 11:37:26 +0900
Subject: [PATCH v5 2/2] Prohibit slot manipulation while in single-user mode
Replication-related commands are rarely used in single-user mode and have been
broken for years. This commit prohibits calling slot manipulation SQL functions
to avoid additional risk of failures. One exception is pg_drop_replication_slot.
It is still allowed because users may want to clean up their mistakes in the mode.
---
doc/src/sgml/func/func-admin.sgml | 5 +++++
src/backend/replication/logical/logicalfuncs.c | 3 +++
src/backend/replication/slot.c | 12 ++++++++++++
src/backend/replication/slotfuncs.c | 17 +++++++++++++++++
src/backend/utils/adt/pg_upgrade_support.c | 3 +++
src/include/replication/slot.h | 1 +
6 files changed, 41 insertions(+)
diff --git a/doc/src/sgml/func/func-admin.sgml b/doc/src/sgml/func/func-admin.sgml
index 446fdfe56f4..cfa09e4b987 100644
--- a/doc/src/sgml/func/func-admin.sgml
+++ b/doc/src/sgml/func/func-admin.sgml
@@ -1012,6 +1012,11 @@ postgres=# SELECT '0/0'::pg_lsn + pd.segment_number * ps.setting::int + :offset
are also relevant for replication.
</para>
+ <para>
+ Note that slot manipulation functions except <function>pg_drop_replication_slot</function>
+ cannot be used in single-user mode.
+ </para>
+
<table id="functions-replication-table">
<title>Replication Management Functions</title>
<tgroup cols="1">
diff --git a/src/backend/replication/logical/logicalfuncs.c b/src/backend/replication/logical/logicalfuncs.c
index ca53caac2f2..30877fdc3ab 100644
--- a/src/backend/replication/logical/logicalfuncs.c
+++ b/src/backend/replication/logical/logicalfuncs.c
@@ -113,6 +113,9 @@ pg_logical_slot_get_changes_guts(FunctionCallInfo fcinfo, bool confirm, bool bin
List *options = NIL;
DecodingOutputState *p;
+ /* Slot manipulation is not allowed in single-user mode */
+ CheckSlotIsInSingleUserMode();
+
CheckSlotPermissions();
CheckLogicalDecodingRequirements();
diff --git a/src/backend/replication/slot.c b/src/backend/replication/slot.c
index fd0fdb96d42..4ca1227cc94 100644
--- a/src/backend/replication/slot.c
+++ b/src/backend/replication/slot.c
@@ -1529,6 +1529,18 @@ CheckSlotPermissions(void)
"REPLICATION")));
}
+/*
+ * Check whether the instance is in single-user mode.
+ */
+void
+CheckSlotIsInSingleUserMode(void)
+{
+ if (!IsUnderPostmaster)
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("replication slots cannot be used in single-user mode")));
+}
+
/*
* Reserve WAL for the currently active slot.
*
diff --git a/src/backend/replication/slotfuncs.c b/src/backend/replication/slotfuncs.c
index 69f4c6157c5..4de2e52fba9 100644
--- a/src/backend/replication/slotfuncs.c
+++ b/src/backend/replication/slotfuncs.c
@@ -17,6 +17,7 @@
#include "access/xlogrecovery.h"
#include "access/xlogutils.h"
#include "funcapi.h"
+#include "miscadmin.h"
#include "replication/logical.h"
#include "replication/slot.h"
#include "replication/slotsync.h"
@@ -76,6 +77,9 @@ pg_create_physical_replication_slot(PG_FUNCTION_ARGS)
if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
elog(ERROR, "return type must be a row type");
+ /* Slot manipulation is not allowed in single-user mode */
+ CheckSlotIsInSingleUserMode();
+
CheckSlotPermissions();
CheckSlotRequirements();
@@ -182,6 +186,9 @@ pg_create_logical_replication_slot(PG_FUNCTION_ARGS)
if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
elog(ERROR, "return type must be a row type");
+ /* Slot manipulation is not allowed in single-user mode */
+ CheckSlotIsInSingleUserMode();
+
CheckSlotPermissions();
CheckLogicalDecodingRequirements();
@@ -521,6 +528,9 @@ pg_replication_slot_advance(PG_FUNCTION_ARGS)
Assert(!MyReplicationSlot);
+ /* Slot manipulation is not allowed in single-user mode */
+ CheckSlotIsInSingleUserMode();
+
CheckSlotPermissions();
if (XLogRecPtrIsInvalid(moveto))
@@ -618,9 +628,13 @@ copy_replication_slot(FunctionCallInfo fcinfo, bool logical_slot)
TupleDesc tupdesc;
HeapTuple tuple;
+
if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
elog(ERROR, "return type must be a row type");
+ /* Slot manipulation is not allowed in single-user mode */
+ CheckSlotIsInSingleUserMode();
+
CheckSlotPermissions();
if (logical_slot)
@@ -898,6 +912,9 @@ pg_sync_replication_slots(PG_FUNCTION_ARGS)
char *err;
StringInfoData app_name;
+ /* Slot manipulation is not allowed in single-user mode */
+ CheckSlotIsInSingleUserMode();
+
CheckSlotPermissions();
if (!RecoveryInProgress())
diff --git a/src/backend/utils/adt/pg_upgrade_support.c b/src/backend/utils/adt/pg_upgrade_support.c
index a4f8b4faa90..337f705b34b 100644
--- a/src/backend/utils/adt/pg_upgrade_support.c
+++ b/src/backend/utils/adt/pg_upgrade_support.c
@@ -296,6 +296,9 @@ binary_upgrade_logical_slot_has_caught_up(PG_FUNCTION_ARGS)
*/
Assert(has_rolreplication(GetUserId()));
+ /* Slot manipulation is not allowed in single-user mode */
+ CheckSlotIsInSingleUserMode();
+
slot_name = PG_GETARG_NAME(0);
/* Acquire the given slot */
diff --git a/src/include/replication/slot.h b/src/include/replication/slot.h
index e8fc342d1a9..b4d89760f96 100644
--- a/src/include/replication/slot.h
+++ b/src/include/replication/slot.h
@@ -342,6 +342,7 @@ extern void CheckPointReplicationSlots(bool is_shutdown);
extern void CheckSlotRequirements(void);
extern void CheckSlotPermissions(void);
+extern void CheckSlotIsInSingleUserMode(void);
extern ReplicationSlotInvalidationCause
GetSlotInvalidationCause(const char *cause_name);
extern const char *GetSlotInvalidationCauseName(ReplicationSlotInvalidationCause cause);
--
2.47.1
^ permalink raw reply [nested|flat] 31+ messages in thread
* RE: ReplicationSlotRelease() crashes when the instance is in the single user mode
@ 2025-08-18 08:53 Hayato Kuroda (Fujitsu) <[email protected]>
parent: Robert Haas <[email protected]>
0 siblings, 0 replies; 31+ messages in thread
From: Hayato Kuroda (Fujitsu) @ 2025-08-18 08:53 UTC (permalink / raw)
To: 'Robert Haas' <[email protected]>; +Cc: Paul A Jungwirth <[email protected]>; Mutaamba Maasha <[email protected]>; Amit Kapila <[email protected]>; Michael Paquier <[email protected]>; David G. Johnston <[email protected]>; Zhijie Hou (Fujitsu) <[email protected]>; Tom Lane <[email protected]>; Bertrand Drouvot <[email protected]>; [email protected] <[email protected]>
Dear Robert,
> The use case for single-user mode is quite limited in general, but
> most things work in single-user mode unless they are something that
> intrinsically can't. For example, parallel query cannot be used in
> single-user mode, because there is only one process. Replication
> cannot work, for the same reason. But manual slot operations can work,
> so I do not think it is good to arbitrarily prohibit them. We do not
> need a reason to specifically allow them; it is enough that there is
> no good reason for them to be blocked.
Thanks for the confirmation. Till now, it is mostly fifty-fifty in the community
whether we should allow the manipulation in the single-user mode.
I did split a patch to fix the initial issue [1]. Let's focus on the 0001 patch
firstly.
[1]: https://www.postgresql.org/message-id/OSCPR01MB14966B39B3ED21229EAEFA10FF531A%40OSCPR01MB14966.jpnpr...
Best regards,
Hayato Kuroda
FUJITSU LIMITED
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: ReplicationSlotRelease() crashes when the instance is in the single user mode
@ 2025-08-18 09:02 Michael Paquier <[email protected]>
parent: Hayato Kuroda (Fujitsu) <[email protected]>
0 siblings, 1 reply; 31+ messages in thread
From: Michael Paquier @ 2025-08-18 09:02 UTC (permalink / raw)
To: Hayato Kuroda (Fujitsu) <[email protected]>; +Cc: 'Paul A Jungwirth' <[email protected]>; Mutaamba Maasha <[email protected]>; Amit Kapila <[email protected]>; David G. Johnston <[email protected]>; Zhijie Hou (Fujitsu) <[email protected]>; Tom Lane <[email protected]>; Bertrand Drouvot <[email protected]>; [email protected] <[email protected]>
On Mon, Aug 18, 2025 at 08:47:27AM +0000, Hayato Kuroda (Fujitsu) wrote:
> Here are updated patches. Based on the Robert's suggestion, I separated into two parts.
> 0001 fixed the original issue and 0002 prohibited the slot manipulation in
> single-user mode. I want to focus on 0001 first because on one would argue it.
FWIW, I had my doubts at the beginning on the thread about the
use-case, but after re-reading the whole thing I am going to side with
Robert's opinion that if a fix to authorize some of the operations is
simple, then let's just authorize the case(s) and have the fix.
Echoing with Paul, what I find as critically lacking from the proposed
patches are regression tests to check and validate the behaviors you
are looking for in the patches. Please add some in the shape of perl
commands that use direct --single commands of postgres. We have cases
that do so currently in the tree: sepgsql and shm. I don't see a
reason why we could not do that as well here with run_log(). It is
also possible to pass a -c exit_on_error=true to force failures.
Not having a test for the single-user case of a slot drop is of course
something that we are lacking now. As we are playing with this area
of the code, let's add something for this case as well.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, ../../[email protected]/2-signature.asc)
download
^ permalink raw reply [nested|flat] 31+ messages in thread
* RE: ReplicationSlotRelease() crashes when the instance is in the single user mode
@ 2025-08-19 10:26 Hayato Kuroda (Fujitsu) <[email protected]>
parent: Michael Paquier <[email protected]>
0 siblings, 2 replies; 31+ messages in thread
From: Hayato Kuroda (Fujitsu) @ 2025-08-19 10:26 UTC (permalink / raw)
To: 'Michael Paquier' <[email protected]>; +Cc: 'Paul A Jungwirth' <[email protected]>; Mutaamba Maasha <[email protected]>; Amit Kapila <[email protected]>; David G. Johnston <[email protected]>; Zhijie Hou (Fujitsu) <[email protected]>; Tom Lane <[email protected]>; Bertrand Drouvot <[email protected]>; [email protected] <[email protected]>
Dear Michael, Paul, Mutaamba,
> FWIW, I had my doubts at the beginning on the thread about the
> use-case, but after re-reading the whole thing I am going to side with
> Robert's opinion that if a fix to authorize some of the operations is
> simple, then let's just authorize the case(s) and have the fix.
OK, thanks for the clarification. Let's focus on 0001 now.
> Echoing with Paul, what I find as critically lacking from the proposed
> patches are regression tests to check and validate the behaviors you
> are looking for in the patches. Please add some in the shape of perl
> commands that use direct --single commands of postgres. We have cases
> that do so currently in the tree: sepgsql and shm. I don't see a
> reason why we could not do that as well here with run_log(). It is
> also possible to pass a -c exit_on_error=true to force failures.
>
> Not having a test for the single-user case of a slot drop is of course
> something that we are lacking now. As we are playing with this area
> of the code, let's add something for this case as well.
I added a first version of the test. It could work on my env (Linux).
Since I cannot come up the appropriate place, I introduced new test file in
recovery test. creating/dropping/advancing a slot, and doing a logical decoding
is tested. Since standby mode is not supported by the single-user instance,
I did not test the slot synchronization. I have not known that.
0002 was updated accordingly just in case. In the patch some functions were expected
to fail. Also, a check for pg_sync_replication_slots() was removed because we cannot call
the function in the first place.
IIUC, 0001 can be backpatched for all supported branches. I will create for them
after patch would be a good shape.
Best regards,
Hayato Kuroda
FUJITSU LIMITED
Attachments:
[application/octet-stream] v6-0001-Set-ReplicationSlot-active_pid-even-in-single-use.patch (3.6K, ../../OSCPR01MB14966D87D8EB0D9CD32C6A43DF530A@OSCPR01MB14966.jpnprd01.prod.outlook.com/2-v6-0001-Set-ReplicationSlot-active_pid-even-in-single-use.patch)
download | inline diff:
From e7d7de8fc99258ea826fc6269497313fefb417c1 Mon Sep 17 00:00:00 2001
From: Hayato Kuroda <[email protected]>
Date: Mon, 18 Aug 2025 11:47:43 +0900
Subject: [PATCH v6 1/2] Set ReplicationSlot::active_pid even in single-user
mode
Slot manipulation functions except drop could be failed in single-user mode, because
active_pid was not set. Actually the attribute is no-op in the mode but fix the
inconsisntency anyway.
---
src/backend/replication/slot.c | 2 +-
src/test/recovery/meson.build | 3 +-
.../t/049_slots_in_single_user_mode.pl | 67 +++++++++++++++++++
3 files changed, 70 insertions(+), 2 deletions(-)
create mode 100644 src/test/recovery/t/049_slots_in_single_user_mode.pl
diff --git a/src/backend/replication/slot.c b/src/backend/replication/slot.c
index 8605776ad86..fd0fdb96d42 100644
--- a/src/backend/replication/slot.c
+++ b/src/backend/replication/slot.c
@@ -653,7 +653,7 @@ retry:
}
else
{
- active_pid = MyProcPid;
+ s->active_pid = active_pid = MyProcPid;
ReplicationSlotSetInactiveSince(s, 0, true);
}
LWLockRelease(ReplicationSlotControlLock);
diff --git a/src/test/recovery/meson.build b/src/test/recovery/meson.build
index 52993c32dbb..7b1fe30f112 100644
--- a/src/test/recovery/meson.build
+++ b/src/test/recovery/meson.build
@@ -56,7 +56,8 @@ tests += {
't/045_archive_restartpoint.pl',
't/046_checkpoint_logical_slot.pl',
't/047_checkpoint_physical_slot.pl',
- 't/048_vacuum_horizon_floor.pl'
+ 't/048_vacuum_horizon_floor.pl',
+ 't/049_slots_in_single_user_mode.pl',
],
},
}
diff --git a/src/test/recovery/t/049_slots_in_single_user_mode.pl b/src/test/recovery/t/049_slots_in_single_user_mode.pl
new file mode 100644
index 00000000000..de32160be07
--- /dev/null
+++ b/src/test/recovery/t/049_slots_in_single_user_mode.pl
@@ -0,0 +1,67 @@
+use strict;
+use warnings;
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+# Tests the slot manipulation in the single-user mode
+
+# Run passed commands in single-user mode. The return value from the command is
+# passed through.
+sub run_test_in_single_user_mode
+{
+ my ($node, $commands, $testname) = @_;
+
+ my $result = run_log(
+ [
+ 'postgres', '--single', '-F',
+ '-c' => 'exit_on_error=true',
+ '-D' => $node->data_dir,
+ 'postgres'
+ ],
+ \$commands);
+
+ ok($result, $testname);
+}
+
+my $slotname = 'test_slot';
+
+# Initialize a node
+my $node = PostgreSQL::Test::Cluster->new('node');
+$node->init(allows_streaming => "logical");
+$node->start;
+
+# Define initial table
+$node->safe_psql('postgres', "CREATE TABLE foo (id int)");
+
+# Stop the node to run and test in single-user mode
+$node->stop;
+
+run_test_in_single_user_mode(
+ $node,
+ "SELECT pg_create_logical_replication_slot('$slotname', 'test_decoding')",
+ "replication slot can be created in single-user mode");
+
+run_test_in_single_user_mode(
+ $node, qq(
+INSERT INTO foo VALUES (1);
+SELECT count(1) FROM pg_logical_slot_get_changes('$slotname', NULL, NULL);
+),
+ "logical decoding be done in single-user mode");
+
+run_test_in_single_user_mode(
+ $node,
+ "SELECT pg_replication_slot_advance('$slotname', pg_current_wal_lsn())",
+ "replication slot can be advanced in single-user mode");
+
+run_test_in_single_user_mode(
+ $node,
+ "SELECT pg_copy_logical_replication_slot('$slotname', 'dest_slot')",
+ "replication slot can be copied in single-user mode");
+
+run_test_in_single_user_mode(
+ $node,
+ "SELECT pg_drop_replication_slot('$slotname')",
+ "replication slot can be dropped in single-user mode");
+
+done_testing();
--
2.47.1
[application/octet-stream] v6-0002-Prohibit-slot-manipulation-while-in-single-user-m.patch (8.4K, ../../OSCPR01MB14966D87D8EB0D9CD32C6A43DF530A@OSCPR01MB14966.jpnprd01.prod.outlook.com/3-v6-0002-Prohibit-slot-manipulation-while-in-single-user-m.patch)
download | inline diff:
From 4ad717d5549cb6ba26a002b69aba7a148a1cd0fc Mon Sep 17 00:00:00 2001
From: Hayato Kuroda <[email protected]>
Date: Wed, 19 Feb 2025 11:37:26 +0900
Subject: [PATCH v6 2/2] Prohibit slot manipulation while in single-user mode
Replication-related commands are rarely used in single-user mode and have been
broken for years. This commit prohibits calling slot manipulation SQL functions
to avoid additional risk of failures. One exception is pg_drop_replication_slot.
It is still allowed because users may want to clean up their mistakes in the mode.
---
doc/src/sgml/func/func-admin.sgml | 5 ++
.../replication/logical/logicalfuncs.c | 3 ++
src/backend/replication/slot.c | 12 +++++
src/backend/replication/slotfuncs.c | 14 ++++++
src/backend/utils/adt/pg_upgrade_support.c | 3 ++
src/include/replication/slot.h | 1 +
.../t/049_slots_in_single_user_mode.pl | 48 ++++++++++++++-----
7 files changed, 75 insertions(+), 11 deletions(-)
diff --git a/doc/src/sgml/func/func-admin.sgml b/doc/src/sgml/func/func-admin.sgml
index 446fdfe56f4..cfa09e4b987 100644
--- a/doc/src/sgml/func/func-admin.sgml
+++ b/doc/src/sgml/func/func-admin.sgml
@@ -1012,6 +1012,11 @@ postgres=# SELECT '0/0'::pg_lsn + pd.segment_number * ps.setting::int + :offset
are also relevant for replication.
</para>
+ <para>
+ Note that slot manipulation functions except <function>pg_drop_replication_slot</function>
+ cannot be used in single-user mode.
+ </para>
+
<table id="functions-replication-table">
<title>Replication Management Functions</title>
<tgroup cols="1">
diff --git a/src/backend/replication/logical/logicalfuncs.c b/src/backend/replication/logical/logicalfuncs.c
index ca53caac2f2..30877fdc3ab 100644
--- a/src/backend/replication/logical/logicalfuncs.c
+++ b/src/backend/replication/logical/logicalfuncs.c
@@ -113,6 +113,9 @@ pg_logical_slot_get_changes_guts(FunctionCallInfo fcinfo, bool confirm, bool bin
List *options = NIL;
DecodingOutputState *p;
+ /* Slot manipulation is not allowed in single-user mode */
+ CheckSlotIsInSingleUserMode();
+
CheckSlotPermissions();
CheckLogicalDecodingRequirements();
diff --git a/src/backend/replication/slot.c b/src/backend/replication/slot.c
index fd0fdb96d42..4ca1227cc94 100644
--- a/src/backend/replication/slot.c
+++ b/src/backend/replication/slot.c
@@ -1529,6 +1529,18 @@ CheckSlotPermissions(void)
"REPLICATION")));
}
+/*
+ * Check whether the instance is in single-user mode.
+ */
+void
+CheckSlotIsInSingleUserMode(void)
+{
+ if (!IsUnderPostmaster)
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("replication slots cannot be used in single-user mode")));
+}
+
/*
* Reserve WAL for the currently active slot.
*
diff --git a/src/backend/replication/slotfuncs.c b/src/backend/replication/slotfuncs.c
index 69f4c6157c5..90fe63283a1 100644
--- a/src/backend/replication/slotfuncs.c
+++ b/src/backend/replication/slotfuncs.c
@@ -17,6 +17,7 @@
#include "access/xlogrecovery.h"
#include "access/xlogutils.h"
#include "funcapi.h"
+#include "miscadmin.h"
#include "replication/logical.h"
#include "replication/slot.h"
#include "replication/slotsync.h"
@@ -76,6 +77,9 @@ pg_create_physical_replication_slot(PG_FUNCTION_ARGS)
if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
elog(ERROR, "return type must be a row type");
+ /* Slot manipulation is not allowed in single-user mode */
+ CheckSlotIsInSingleUserMode();
+
CheckSlotPermissions();
CheckSlotRequirements();
@@ -182,6 +186,9 @@ pg_create_logical_replication_slot(PG_FUNCTION_ARGS)
if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
elog(ERROR, "return type must be a row type");
+ /* Slot manipulation is not allowed in single-user mode */
+ CheckSlotIsInSingleUserMode();
+
CheckSlotPermissions();
CheckLogicalDecodingRequirements();
@@ -521,6 +528,9 @@ pg_replication_slot_advance(PG_FUNCTION_ARGS)
Assert(!MyReplicationSlot);
+ /* Slot manipulation is not allowed in single-user mode */
+ CheckSlotIsInSingleUserMode();
+
CheckSlotPermissions();
if (XLogRecPtrIsInvalid(moveto))
@@ -618,9 +628,13 @@ copy_replication_slot(FunctionCallInfo fcinfo, bool logical_slot)
TupleDesc tupdesc;
HeapTuple tuple;
+
if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
elog(ERROR, "return type must be a row type");
+ /* Slot manipulation is not allowed in single-user mode */
+ CheckSlotIsInSingleUserMode();
+
CheckSlotPermissions();
if (logical_slot)
diff --git a/src/backend/utils/adt/pg_upgrade_support.c b/src/backend/utils/adt/pg_upgrade_support.c
index a4f8b4faa90..337f705b34b 100644
--- a/src/backend/utils/adt/pg_upgrade_support.c
+++ b/src/backend/utils/adt/pg_upgrade_support.c
@@ -296,6 +296,9 @@ binary_upgrade_logical_slot_has_caught_up(PG_FUNCTION_ARGS)
*/
Assert(has_rolreplication(GetUserId()));
+ /* Slot manipulation is not allowed in single-user mode */
+ CheckSlotIsInSingleUserMode();
+
slot_name = PG_GETARG_NAME(0);
/* Acquire the given slot */
diff --git a/src/include/replication/slot.h b/src/include/replication/slot.h
index e8fc342d1a9..b4d89760f96 100644
--- a/src/include/replication/slot.h
+++ b/src/include/replication/slot.h
@@ -342,6 +342,7 @@ extern void CheckPointReplicationSlots(bool is_shutdown);
extern void CheckSlotRequirements(void);
extern void CheckSlotPermissions(void);
+extern void CheckSlotIsInSingleUserMode(void);
extern ReplicationSlotInvalidationCause
GetSlotInvalidationCause(const char *cause_name);
extern const char *GetSlotInvalidationCauseName(ReplicationSlotInvalidationCause cause);
diff --git a/src/test/recovery/t/049_slots_in_single_user_mode.pl b/src/test/recovery/t/049_slots_in_single_user_mode.pl
index de32160be07..e38a32472b2 100644
--- a/src/test/recovery/t/049_slots_in_single_user_mode.pl
+++ b/src/test/recovery/t/049_slots_in_single_user_mode.pl
@@ -21,9 +21,31 @@ sub run_test_in_single_user_mode
],
\$commands);
+ return $result;
+}
+
+# Wrapper function for run_test_in_single_user_mode. This would be used when
+# the input command succeeds.
+sub run_test_in_single_user_mode_success
+{
+ my ($node, $commands, $testname) = @_;
+
+ my $result = run_test_in_single_user_mode($node, $commands, $testname);
+
ok($result, $testname);
}
+# Wrapper function for run_test_in_single_user_mode. This would be used when
+# the input command fails.
+sub run_test_in_single_user_mode_fail
+{
+ my ($node, $commands, $testname) = @_;
+
+ my $result = run_test_in_single_user_mode($node, $commands, $testname);
+
+ ok(!$result, $testname);
+}
+
my $slotname = 'test_slot';
# Initialize a node
@@ -32,34 +54,38 @@ $node->init(allows_streaming => "logical");
$node->start;
# Define initial table
-$node->safe_psql('postgres', "CREATE TABLE foo (id int)");
+$node->safe_psql(
+ 'postgres', qq(
+CREATE TABLE foo (id int);
+SELECT pg_create_logical_replication_slot('$slotname', 'test_decoding');
+));
# Stop the node to run and test in single-user mode
$node->stop;
-run_test_in_single_user_mode(
+run_test_in_single_user_mode_fail(
$node,
- "SELECT pg_create_logical_replication_slot('$slotname', 'test_decoding')",
- "replication slot can be created in single-user mode");
+ "SELECT pg_create_logical_replication_slot('another_slot', 'test_decoding')",
+ "replication slot cannot be created in single-user mode");
-run_test_in_single_user_mode(
+run_test_in_single_user_mode_fail(
$node, qq(
INSERT INTO foo VALUES (1);
SELECT count(1) FROM pg_logical_slot_get_changes('$slotname', NULL, NULL);
),
- "logical decoding be done in single-user mode");
+ "logical decoding cannot be done in single-user mode");
-run_test_in_single_user_mode(
+run_test_in_single_user_mode_fail(
$node,
"SELECT pg_replication_slot_advance('$slotname', pg_current_wal_lsn())",
- "replication slot can be advanced in single-user mode");
+ "replication slot cannot be advanced in single-user mode");
-run_test_in_single_user_mode(
+run_test_in_single_user_mode_fail(
$node,
"SELECT pg_copy_logical_replication_slot('$slotname', 'dest_slot')",
- "replication slot can be copied in single-user mode");
+ "replication slot cannot be copied in single-user mode");
-run_test_in_single_user_mode(
+run_test_in_single_user_mode_success(
$node,
"SELECT pg_drop_replication_slot('$slotname')",
"replication slot can be dropped in single-user mode");
--
2.47.1
^ permalink raw reply [nested|flat] 31+ messages in thread
* RE: ReplicationSlotRelease() crashes when the instance is in the single user mode
@ 2025-08-19 11:16 Hayato Kuroda (Fujitsu) <[email protected]>
parent: Hayato Kuroda (Fujitsu) <[email protected]>
1 sibling, 0 replies; 31+ messages in thread
From: Hayato Kuroda (Fujitsu) @ 2025-08-19 11:16 UTC (permalink / raw)
To: Hayato Kuroda (Fujitsu) <[email protected]>; 'Michael Paquier' <[email protected]>; +Cc: 'Paul A Jungwirth' <[email protected]>; Mutaamba Maasha <[email protected]>; Amit Kapila <[email protected]>; David G. Johnston <[email protected]>; Zhijie Hou (Fujitsu) <[email protected]>; Tom Lane <[email protected]>; Bertrand Drouvot <[email protected]>; [email protected] <[email protected]>
> I added a first version of the test. It could work on my env (Linux).
Best regards,
Hayato Kuroda
FUJITSU LIMITED
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: ReplicationSlotRelease() crashes when the instance is in the single user mode
@ 2025-08-19 11:17 Michael Paquier <[email protected]>
parent: Hayato Kuroda (Fujitsu) <[email protected]>
1 sibling, 1 reply; 31+ messages in thread
From: Michael Paquier @ 2025-08-19 11:17 UTC (permalink / raw)
To: Hayato Kuroda (Fujitsu) <[email protected]>; +Cc: 'Paul A Jungwirth' <[email protected]>; Mutaamba Maasha <[email protected]>; Amit Kapila <[email protected]>; David G. Johnston <[email protected]>; Zhijie Hou (Fujitsu) <[email protected]>; Tom Lane <[email protected]>; Bertrand Drouvot <[email protected]>; [email protected] <[email protected]>
On Tue, Aug 19, 2025 at 10:26:22AM +0000, Hayato Kuroda (Fujitsu) wrote:
> I added a first version of the test. It could work on my env (Linux).
You could also validate that using the CI, assuming that you have a
cloned repo on github with the CI enabled. See src/tools/ci/README.
I use that a lot with local branches for pre-commit validations where
things could break across the board.
> Since I cannot come up the appropriate place, I introduced new test file in
> recovery test. creating/dropping/advancing a slot, and doing a logical decoding
> is tested. Since standby mode is not supported by the single-user instance,
> I did not test the slot synchronization. I have not known that.
Hmm. src/test/modules/test_misc/ makes more sense to me here. (No
need to send an updated patch just for that.)
> IIUC, 0001 can be backpatched for all supported branches. I will create for them
> after patch would be a good shape.
Thanks, I'll check all that tomorrow.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, ../../[email protected]/2-signature.asc)
download
^ permalink raw reply [nested|flat] 31+ messages in thread
* RE: ReplicationSlotRelease() crashes when the instance is in the single user mode
@ 2025-08-19 12:52 Hayato Kuroda (Fujitsu) <[email protected]>
parent: Michael Paquier <[email protected]>
0 siblings, 1 reply; 31+ messages in thread
From: Hayato Kuroda (Fujitsu) @ 2025-08-19 12:52 UTC (permalink / raw)
To: 'Michael Paquier' <[email protected]>; +Cc: 'Paul A Jungwirth' <[email protected]>; Mutaamba Maasha <[email protected]>; Amit Kapila <[email protected]>; David G. Johnston <[email protected]>; Zhijie Hou (Fujitsu) <[email protected]>; Tom Lane <[email protected]>; Bertrand Drouvot <[email protected]>; [email protected] <[email protected]>
Dear Michael,
> You could also validate that using the CI, assuming that you have a
> cloned repo on github with the CI enabled. See src/tools/ci/README.
> I use that a lot with local branches for pre-commit validations where
> things could break across the board.
I have run the tests on my CI and found that windows cannot accept the test.
Per attached output, starting the postgres with the single-user mode was failed
because the user had admin permissions [1]. However, pg_ctl start command could be
done by the same user.
I'm not familiar with Windows, but according to your blogpost [2], Windows seems
to allows running some server command by generating the restricted context for
running postgres commands. In case of single-user mode, however, we directly run
the given command with the current user. So, there is a possibility that only
instance can boot only by pg_ctl.
Based on that, I want to skip the test on windows platform rather than modifying
the ci environment.
IIUC, other tests which uses --single cannot work on windows as well.
> Hmm. src/test/modules/test_misc/ makes more sense to me here. (No
> need to send an updated patch just for that.)
I did not notice the directory and looked nice. Updated.
[1]:
```
# Running: postgres --single -F -c exit_on_error=true -D C:\cirrus\build/testrun/recovery/049_slots_in_single_user_mode\data/t_049_slots_in_single_user_mode_node_data/pgdata postgres
Execution of PostgreSQL by a user with administrative permissions is not
permitted.
The server must be started under an unprivileged user ID to prevent
possible system security compromises. See the documentation for
more information on how to properly start the server.
```
[2]: https://paquier.xyz/postgresql-2/postgres-utilities-restricted-token/
Best regards,
Hayato Kuroda
FUJITSU LIMITED
# Checking port 16164
# Found port 16164
Name: node
Data directory: C:\cirrus\build/testrun/recovery/049_slots_in_single_user_mode\data/t_049_slots_in_single_user_mode_node_data/pgdata
Backup directory: C:\cirrus\build/testrun/recovery/049_slots_in_single_user_mode\data/t_049_slots_in_single_user_mode_node_data/backup
Archive directory: C:\cirrus\build/testrun/recovery/049_slots_in_single_user_mode\data/t_049_slots_in_single_user_mode_node_data/archives
Connection string: port=16164 host=C:/Windows/TEMP/HeMvBnwWt9
Log file: C:\cirrus\build/testrun/recovery/049_slots_in_single_user_mode\log/049_slots_in_single_user_mode_node.log
[10:27:12.345](0.217s) # initializing database system by copying initdb template
# Running: robocopy /E /NJS /NJH /NFL /NDL /NP C:/cirrus/build/tmp_install/initdb-template C:\cirrus\build/testrun/recovery/049_slots_in_single_user_mode\data/t_049_slots_in_single_user_mode_node_data/pgdata
# Running: C:\cirrus\build\src/test\regress\pg_regress.exe --config-auth C:\cirrus\build/testrun/recovery/049_slots_in_single_user_mode\data/t_049_slots_in_single_user_mode_node_data/pgdata
### Starting node "node"
# Running: pg_ctl --wait --pgdata C:\cirrus\build/testrun/recovery/049_slots_in_single_user_mode\data/t_049_slots_in_single_user_mode_node_data/pgdata --log C:\cirrus\build/testrun/recovery/049_slots_in_single_user_mode\log/049_slots_in_single_user_mode_node.log --options --cluster-name=node start
waiting for server to start.... done
server started
# Postmaster PID for node "node" is 2660
### Stopping node "node" using mode fast
# Running: pg_ctl --pgdata C:\cirrus\build/testrun/recovery/049_slots_in_single_user_mode\data/t_049_slots_in_single_user_mode_node_data/pgdata --mode fast stop
waiting for server to shut down.... done
server stopped
# No postmaster PID for node "node"
# Running: postgres --single -F -c exit_on_error=true -D C:\cirrus\build/testrun/recovery/049_slots_in_single_user_mode\data/t_049_slots_in_single_user_mode_node_data/pgdata postgres
Execution of PostgreSQL by a user with administrative permissions is not
permitted.
The server must be started under an unprivileged user ID to prevent
possible system security compromises. See the documentation for
more information on how to properly start the server.
[10:27:18.865](6.520s) ok 1 - replication slot cannot be created in single-user mode
# Running: postgres --single -F -c exit_on_error=true -D C:\cirrus\build/testrun/recovery/049_slots_in_single_user_mode\data/t_049_slots_in_single_user_mode_node_data/pgdata postgres
Execution of PostgreSQL by a user with administrative permissions is not
permitted.
The server must be started under an unprivileged user ID to prevent
possible system security compromises. See the documentation for
more information on how to properly start the server.
[10:27:18.927](0.062s) ok 2 - logical decoding cannot be done in single-user mode
# Running: postgres --single -F -c exit_on_error=true -D C:\cirrus\build/testrun/recovery/049_slots_in_single_user_mode\data/t_049_slots_in_single_user_mode_node_data/pgdata postgres
Execution of PostgreSQL by a user with administrative permissions is not
permitted.
The server must be started under an unprivileged user ID to prevent
possible system security compromises. See the documentation for
more information on how to properly start the server.
[10:27:19.114](0.187s) ok 3 - replication slot cannot be advanced in single-user mode
# Running: postgres --single -F -c exit_on_error=true -D C:\cirrus\build/testrun/recovery/049_slots_in_single_user_mode\data/t_049_slots_in_single_user_mode_node_data/pgdata postgres
Execution of PostgreSQL by a user with administrative permissions is not
permitted.
The server must be started under an unprivileged user ID to prevent
possible system security compromises. See the documentation for
more information on how to properly start the server.
[10:27:19.285](0.171s) ok 4 - replication slot cannot be copied in single-user mode
# Running: postgres --single -F -c exit_on_error=true -D C:\cirrus\build/testrun/recovery/049_slots_in_single_user_mode\data/t_049_slots_in_single_user_mode_node_data/pgdata postgres
Execution of PostgreSQL by a user with administrative permissions is not
permitted.
The server must be started under an unprivileged user ID to prevent
possible system security compromises. See the documentation for
more information on how to properly start the server.
[10:27:19.413](0.128s) not ok 5 - replication slot can be dropped in single-user mode
[10:27:19.413](0.000s) # Failed test 'replication slot can be dropped in single-user mode'
# at C:/cirrus/src/test/recovery/t/049_slots_in_single_user_mode.pl line 35.
[10:27:19.414](0.000s) 1..5
[10:27:19.429](0.016s) # Looks like you failed 1 test of 5.
Attachments:
[text/plain] regress_log_049_slots_in_single_user_mode.txt (4.7K, ../../OSCPR01MB14966F06D84A7A9C26570A519F530A@OSCPR01MB14966.jpnprd01.prod.outlook.com/2-regress_log_049_slots_in_single_user_mode.txt)
download | inline:
# Checking port 16164
# Found port 16164
Name: node
Data directory: C:\cirrus\build/testrun/recovery/049_slots_in_single_user_mode\data/t_049_slots_in_single_user_mode_node_data/pgdata
Backup directory: C:\cirrus\build/testrun/recovery/049_slots_in_single_user_mode\data/t_049_slots_in_single_user_mode_node_data/backup
Archive directory: C:\cirrus\build/testrun/recovery/049_slots_in_single_user_mode\data/t_049_slots_in_single_user_mode_node_data/archives
Connection string: port=16164 host=C:/Windows/TEMP/HeMvBnwWt9
Log file: C:\cirrus\build/testrun/recovery/049_slots_in_single_user_mode\log/049_slots_in_single_user_mode_node.log
[10:27:12.345](0.217s) # initializing database system by copying initdb template
# Running: robocopy /E /NJS /NJH /NFL /NDL /NP C:/cirrus/build/tmp_install/initdb-template C:\cirrus\build/testrun/recovery/049_slots_in_single_user_mode\data/t_049_slots_in_single_user_mode_node_data/pgdata
# Running: C:\cirrus\build\src/test\regress\pg_regress.exe --config-auth C:\cirrus\build/testrun/recovery/049_slots_in_single_user_mode\data/t_049_slots_in_single_user_mode_node_data/pgdata
### Starting node "node"
# Running: pg_ctl --wait --pgdata C:\cirrus\build/testrun/recovery/049_slots_in_single_user_mode\data/t_049_slots_in_single_user_mode_node_data/pgdata --log C:\cirrus\build/testrun/recovery/049_slots_in_single_user_mode\log/049_slots_in_single_user_mode_node.log --options --cluster-name=node start
waiting for server to start.... done
server started
# Postmaster PID for node "node" is 2660
### Stopping node "node" using mode fast
# Running: pg_ctl --pgdata C:\cirrus\build/testrun/recovery/049_slots_in_single_user_mode\data/t_049_slots_in_single_user_mode_node_data/pgdata --mode fast stop
waiting for server to shut down.... done
server stopped
# No postmaster PID for node "node"
# Running: postgres --single -F -c exit_on_error=true -D C:\cirrus\build/testrun/recovery/049_slots_in_single_user_mode\data/t_049_slots_in_single_user_mode_node_data/pgdata postgres
Execution of PostgreSQL by a user with administrative permissions is not
permitted.
The server must be started under an unprivileged user ID to prevent
possible system security compromises. See the documentation for
more information on how to properly start the server.
[10:27:18.865](6.520s) ok 1 - replication slot cannot be created in single-user mode
# Running: postgres --single -F -c exit_on_error=true -D C:\cirrus\build/testrun/recovery/049_slots_in_single_user_mode\data/t_049_slots_in_single_user_mode_node_data/pgdata postgres
Execution of PostgreSQL by a user with administrative permissions is not
permitted.
The server must be started under an unprivileged user ID to prevent
possible system security compromises. See the documentation for
more information on how to properly start the server.
[10:27:18.927](0.062s) ok 2 - logical decoding cannot be done in single-user mode
# Running: postgres --single -F -c exit_on_error=true -D C:\cirrus\build/testrun/recovery/049_slots_in_single_user_mode\data/t_049_slots_in_single_user_mode_node_data/pgdata postgres
Execution of PostgreSQL by a user with administrative permissions is not
permitted.
The server must be started under an unprivileged user ID to prevent
possible system security compromises. See the documentation for
more information on how to properly start the server.
[10:27:19.114](0.187s) ok 3 - replication slot cannot be advanced in single-user mode
# Running: postgres --single -F -c exit_on_error=true -D C:\cirrus\build/testrun/recovery/049_slots_in_single_user_mode\data/t_049_slots_in_single_user_mode_node_data/pgdata postgres
Execution of PostgreSQL by a user with administrative permissions is not
permitted.
The server must be started under an unprivileged user ID to prevent
possible system security compromises. See the documentation for
more information on how to properly start the server.
[10:27:19.285](0.171s) ok 4 - replication slot cannot be copied in single-user mode
# Running: postgres --single -F -c exit_on_error=true -D C:\cirrus\build/testrun/recovery/049_slots_in_single_user_mode\data/t_049_slots_in_single_user_mode_node_data/pgdata postgres
Execution of PostgreSQL by a user with administrative permissions is not
permitted.
The server must be started under an unprivileged user ID to prevent
possible system security compromises. See the documentation for
more information on how to properly start the server.
[10:27:19.413](0.128s) not ok 5 - replication slot can be dropped in single-user mode
[10:27:19.413](0.000s) # Failed test 'replication slot can be dropped in single-user mode'
# at C:/cirrus/src/test/recovery/t/049_slots_in_single_user_mode.pl line 35.
[10:27:19.414](0.000s) 1..5
[10:27:19.429](0.016s) # Looks like you failed 1 test of 5.
[application/octet-stream] v7-0001-Set-ReplicationSlot-active_pid-even-in-single-use.patch (4.1K, ../../OSCPR01MB14966F06D84A7A9C26570A519F530A@OSCPR01MB14966.jpnprd01.prod.outlook.com/3-v7-0001-Set-ReplicationSlot-active_pid-even-in-single-use.patch)
download | inline diff:
From d30416a303cf78639f71a3d192aa14116da28d4e Mon Sep 17 00:00:00 2001
From: Hayato Kuroda <[email protected]>
Date: Mon, 18 Aug 2025 11:47:43 +0900
Subject: [PATCH v7 1/2] Set ReplicationSlot::active_pid even in single-user
mode
Slot manipulation functions except drop could be failed in single-user mode, because
active_pid was not set. Actually the attribute is no-op in the mode but fix the
inconsisntency anyway.
---
src/backend/replication/slot.c | 2 +-
src/test/modules/test_misc/meson.build | 1 +
.../t/008_slots_in_single_user_mode.pl | 73 +++++++++++++++++++
src/test/recovery/meson.build | 2 +-
4 files changed, 76 insertions(+), 2 deletions(-)
create mode 100644 src/test/modules/test_misc/t/008_slots_in_single_user_mode.pl
diff --git a/src/backend/replication/slot.c b/src/backend/replication/slot.c
index 8605776ad86..fd0fdb96d42 100644
--- a/src/backend/replication/slot.c
+++ b/src/backend/replication/slot.c
@@ -653,7 +653,7 @@ retry:
}
else
{
- active_pid = MyProcPid;
+ s->active_pid = active_pid = MyProcPid;
ReplicationSlotSetInactiveSince(s, 0, true);
}
LWLockRelease(ReplicationSlotControlLock);
diff --git a/src/test/modules/test_misc/meson.build b/src/test/modules/test_misc/meson.build
index 9c50de7efb0..08357d2f2e8 100644
--- a/src/test/modules/test_misc/meson.build
+++ b/src/test/modules/test_misc/meson.build
@@ -16,6 +16,7 @@ tests += {
't/005_timeouts.pl',
't/006_signal_autovacuum.pl',
't/007_catcache_inval.pl',
+ 't/008_slots_in_single_user_mode.pl',
],
},
}
diff --git a/src/test/modules/test_misc/t/008_slots_in_single_user_mode.pl b/src/test/modules/test_misc/t/008_slots_in_single_user_mode.pl
new file mode 100644
index 00000000000..4d15efd71ae
--- /dev/null
+++ b/src/test/modules/test_misc/t/008_slots_in_single_user_mode.pl
@@ -0,0 +1,73 @@
+use strict;
+use warnings;
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+# Tests the slot manipulation in the single-user mode
+
+# Skip the whole thing on the windows platform
+if ($windows_os)
+{
+ plan skip_all => 'this test is not supported by this platform';
+}
+
+# Run passed commands in single-user mode. The return value from the command is
+# passed through.
+sub run_test_in_single_user_mode
+{
+ my ($node, $commands, $testname) = @_;
+
+ my $result = run_log(
+ [
+ 'postgres', '--single', '-F',
+ '-c' => 'exit_on_error=true',
+ '-D' => $node->data_dir,
+ 'postgres'
+ ],
+ \$commands);
+
+ ok($result, $testname);
+}
+
+my $slotname = 'test_slot';
+
+# Initialize a node
+my $node = PostgreSQL::Test::Cluster->new('node');
+$node->init(allows_streaming => "logical");
+$node->start;
+
+# Define initial table
+$node->safe_psql('postgres', "CREATE TABLE foo (id int)");
+
+# Stop the node to run and test in single-user mode
+$node->stop;
+
+run_test_in_single_user_mode(
+ $node,
+ "SELECT pg_create_logical_replication_slot('$slotname', 'test_decoding')",
+ "replication slot can be created in single-user mode");
+
+run_test_in_single_user_mode(
+ $node, qq(
+INSERT INTO foo VALUES (1);
+SELECT count(1) FROM pg_logical_slot_get_changes('$slotname', NULL, NULL);
+),
+ "logical decoding be done in single-user mode");
+
+run_test_in_single_user_mode(
+ $node,
+ "SELECT pg_replication_slot_advance('$slotname', pg_current_wal_lsn())",
+ "replication slot can be advanced in single-user mode");
+
+run_test_in_single_user_mode(
+ $node,
+ "SELECT pg_copy_logical_replication_slot('$slotname', 'dest_slot')",
+ "replication slot can be copied in single-user mode");
+
+run_test_in_single_user_mode(
+ $node,
+ "SELECT pg_drop_replication_slot('$slotname')",
+ "replication slot can be dropped in single-user mode");
+
+done_testing();
diff --git a/src/test/recovery/meson.build b/src/test/recovery/meson.build
index 52993c32dbb..0b79896b61c 100644
--- a/src/test/recovery/meson.build
+++ b/src/test/recovery/meson.build
@@ -56,7 +56,7 @@ tests += {
't/045_archive_restartpoint.pl',
't/046_checkpoint_logical_slot.pl',
't/047_checkpoint_physical_slot.pl',
- 't/048_vacuum_horizon_floor.pl'
+ 't/048_vacuum_horizon_floor.pl',
],
},
}
--
2.47.1
[application/octet-stream] v7-0002-Prohibit-slot-manipulation-while-in-single-user-m.patch (8.4K, ../../OSCPR01MB14966F06D84A7A9C26570A519F530A@OSCPR01MB14966.jpnprd01.prod.outlook.com/4-v7-0002-Prohibit-slot-manipulation-while-in-single-user-m.patch)
download | inline diff:
From 2631ba04a8030fd7878765328b2aec32b81cdde6 Mon Sep 17 00:00:00 2001
From: Hayato Kuroda <[email protected]>
Date: Wed, 19 Feb 2025 11:37:26 +0900
Subject: [PATCH v7 2/2] Prohibit slot manipulation while in single-user mode
Replication-related commands are rarely used in single-user mode and have been
broken for years. This commit prohibits calling slot manipulation SQL functions
to avoid additional risk of failures. One exception is pg_drop_replication_slot.
It is still allowed because users may want to clean up their mistakes in the mode.
---
doc/src/sgml/func/func-admin.sgml | 5 ++
.../replication/logical/logicalfuncs.c | 3 ++
src/backend/replication/slot.c | 12 +++++
src/backend/replication/slotfuncs.c | 14 ++++++
src/backend/utils/adt/pg_upgrade_support.c | 3 ++
src/include/replication/slot.h | 1 +
.../t/008_slots_in_single_user_mode.pl | 48 ++++++++++++++-----
7 files changed, 75 insertions(+), 11 deletions(-)
diff --git a/doc/src/sgml/func/func-admin.sgml b/doc/src/sgml/func/func-admin.sgml
index 446fdfe56f4..cfa09e4b987 100644
--- a/doc/src/sgml/func/func-admin.sgml
+++ b/doc/src/sgml/func/func-admin.sgml
@@ -1012,6 +1012,11 @@ postgres=# SELECT '0/0'::pg_lsn + pd.segment_number * ps.setting::int + :offset
are also relevant for replication.
</para>
+ <para>
+ Note that slot manipulation functions except <function>pg_drop_replication_slot</function>
+ cannot be used in single-user mode.
+ </para>
+
<table id="functions-replication-table">
<title>Replication Management Functions</title>
<tgroup cols="1">
diff --git a/src/backend/replication/logical/logicalfuncs.c b/src/backend/replication/logical/logicalfuncs.c
index ca53caac2f2..30877fdc3ab 100644
--- a/src/backend/replication/logical/logicalfuncs.c
+++ b/src/backend/replication/logical/logicalfuncs.c
@@ -113,6 +113,9 @@ pg_logical_slot_get_changes_guts(FunctionCallInfo fcinfo, bool confirm, bool bin
List *options = NIL;
DecodingOutputState *p;
+ /* Slot manipulation is not allowed in single-user mode */
+ CheckSlotIsInSingleUserMode();
+
CheckSlotPermissions();
CheckLogicalDecodingRequirements();
diff --git a/src/backend/replication/slot.c b/src/backend/replication/slot.c
index fd0fdb96d42..4ca1227cc94 100644
--- a/src/backend/replication/slot.c
+++ b/src/backend/replication/slot.c
@@ -1529,6 +1529,18 @@ CheckSlotPermissions(void)
"REPLICATION")));
}
+/*
+ * Check whether the instance is in single-user mode.
+ */
+void
+CheckSlotIsInSingleUserMode(void)
+{
+ if (!IsUnderPostmaster)
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("replication slots cannot be used in single-user mode")));
+}
+
/*
* Reserve WAL for the currently active slot.
*
diff --git a/src/backend/replication/slotfuncs.c b/src/backend/replication/slotfuncs.c
index 69f4c6157c5..90fe63283a1 100644
--- a/src/backend/replication/slotfuncs.c
+++ b/src/backend/replication/slotfuncs.c
@@ -17,6 +17,7 @@
#include "access/xlogrecovery.h"
#include "access/xlogutils.h"
#include "funcapi.h"
+#include "miscadmin.h"
#include "replication/logical.h"
#include "replication/slot.h"
#include "replication/slotsync.h"
@@ -76,6 +77,9 @@ pg_create_physical_replication_slot(PG_FUNCTION_ARGS)
if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
elog(ERROR, "return type must be a row type");
+ /* Slot manipulation is not allowed in single-user mode */
+ CheckSlotIsInSingleUserMode();
+
CheckSlotPermissions();
CheckSlotRequirements();
@@ -182,6 +186,9 @@ pg_create_logical_replication_slot(PG_FUNCTION_ARGS)
if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
elog(ERROR, "return type must be a row type");
+ /* Slot manipulation is not allowed in single-user mode */
+ CheckSlotIsInSingleUserMode();
+
CheckSlotPermissions();
CheckLogicalDecodingRequirements();
@@ -521,6 +528,9 @@ pg_replication_slot_advance(PG_FUNCTION_ARGS)
Assert(!MyReplicationSlot);
+ /* Slot manipulation is not allowed in single-user mode */
+ CheckSlotIsInSingleUserMode();
+
CheckSlotPermissions();
if (XLogRecPtrIsInvalid(moveto))
@@ -618,9 +628,13 @@ copy_replication_slot(FunctionCallInfo fcinfo, bool logical_slot)
TupleDesc tupdesc;
HeapTuple tuple;
+
if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
elog(ERROR, "return type must be a row type");
+ /* Slot manipulation is not allowed in single-user mode */
+ CheckSlotIsInSingleUserMode();
+
CheckSlotPermissions();
if (logical_slot)
diff --git a/src/backend/utils/adt/pg_upgrade_support.c b/src/backend/utils/adt/pg_upgrade_support.c
index a4f8b4faa90..337f705b34b 100644
--- a/src/backend/utils/adt/pg_upgrade_support.c
+++ b/src/backend/utils/adt/pg_upgrade_support.c
@@ -296,6 +296,9 @@ binary_upgrade_logical_slot_has_caught_up(PG_FUNCTION_ARGS)
*/
Assert(has_rolreplication(GetUserId()));
+ /* Slot manipulation is not allowed in single-user mode */
+ CheckSlotIsInSingleUserMode();
+
slot_name = PG_GETARG_NAME(0);
/* Acquire the given slot */
diff --git a/src/include/replication/slot.h b/src/include/replication/slot.h
index e8fc342d1a9..b4d89760f96 100644
--- a/src/include/replication/slot.h
+++ b/src/include/replication/slot.h
@@ -342,6 +342,7 @@ extern void CheckPointReplicationSlots(bool is_shutdown);
extern void CheckSlotRequirements(void);
extern void CheckSlotPermissions(void);
+extern void CheckSlotIsInSingleUserMode(void);
extern ReplicationSlotInvalidationCause
GetSlotInvalidationCause(const char *cause_name);
extern const char *GetSlotInvalidationCauseName(ReplicationSlotInvalidationCause cause);
diff --git a/src/test/modules/test_misc/t/008_slots_in_single_user_mode.pl b/src/test/modules/test_misc/t/008_slots_in_single_user_mode.pl
index 4d15efd71ae..b3d9fe2fc33 100644
--- a/src/test/modules/test_misc/t/008_slots_in_single_user_mode.pl
+++ b/src/test/modules/test_misc/t/008_slots_in_single_user_mode.pl
@@ -27,9 +27,31 @@ sub run_test_in_single_user_mode
],
\$commands);
+ return $result;
+}
+
+# Wrapper function for run_test_in_single_user_mode. This would be used when
+# the input command succeeds.
+sub run_test_in_single_user_mode_success
+{
+ my ($node, $commands, $testname) = @_;
+
+ my $result = run_test_in_single_user_mode($node, $commands, $testname);
+
ok($result, $testname);
}
+# Wrapper function for run_test_in_single_user_mode. This would be used when
+# the input command fails.
+sub run_test_in_single_user_mode_fail
+{
+ my ($node, $commands, $testname) = @_;
+
+ my $result = run_test_in_single_user_mode($node, $commands, $testname);
+
+ ok(!$result, $testname);
+}
+
my $slotname = 'test_slot';
# Initialize a node
@@ -38,34 +60,38 @@ $node->init(allows_streaming => "logical");
$node->start;
# Define initial table
-$node->safe_psql('postgres', "CREATE TABLE foo (id int)");
+$node->safe_psql(
+ 'postgres', qq(
+CREATE TABLE foo (id int);
+SELECT pg_create_logical_replication_slot('$slotname', 'test_decoding');
+));
# Stop the node to run and test in single-user mode
$node->stop;
-run_test_in_single_user_mode(
+run_test_in_single_user_mode_fail(
$node,
- "SELECT pg_create_logical_replication_slot('$slotname', 'test_decoding')",
- "replication slot can be created in single-user mode");
+ "SELECT pg_create_logical_replication_slot('another_slot', 'test_decoding')",
+ "replication slot cannot be created in single-user mode");
-run_test_in_single_user_mode(
+run_test_in_single_user_mode_fail(
$node, qq(
INSERT INTO foo VALUES (1);
SELECT count(1) FROM pg_logical_slot_get_changes('$slotname', NULL, NULL);
),
- "logical decoding be done in single-user mode");
+ "logical decoding cannot be done in single-user mode");
-run_test_in_single_user_mode(
+run_test_in_single_user_mode_fail(
$node,
"SELECT pg_replication_slot_advance('$slotname', pg_current_wal_lsn())",
- "replication slot can be advanced in single-user mode");
+ "replication slot cannot be advanced in single-user mode");
-run_test_in_single_user_mode(
+run_test_in_single_user_mode_fail(
$node,
"SELECT pg_copy_logical_replication_slot('$slotname', 'dest_slot')",
- "replication slot can be copied in single-user mode");
+ "replication slot cannot be copied in single-user mode");
-run_test_in_single_user_mode(
+run_test_in_single_user_mode_success(
$node,
"SELECT pg_drop_replication_slot('$slotname')",
"replication slot can be dropped in single-user mode");
--
2.47.1
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: ReplicationSlotRelease() crashes when the instance is in the single user mode
@ 2025-08-20 06:05 Michael Paquier <[email protected]>
parent: Hayato Kuroda (Fujitsu) <[email protected]>
0 siblings, 1 reply; 31+ messages in thread
From: Michael Paquier @ 2025-08-20 06:05 UTC (permalink / raw)
To: Hayato Kuroda (Fujitsu) <[email protected]>; +Cc: 'Paul A Jungwirth' <[email protected]>; Mutaamba Maasha <[email protected]>; Amit Kapila <[email protected]>; David G. Johnston <[email protected]>; Zhijie Hou (Fujitsu) <[email protected]>; Tom Lane <[email protected]>; Bertrand Drouvot <[email protected]>; [email protected] <[email protected]>
On Tue, Aug 19, 2025 at 12:52:24PM +0000, Hayato Kuroda (Fujitsu) wrote:
> I have run the tests on my CI and found that windows cannot accept the test.
> Per attached output, starting the postgres with the single-user mode was failed
> because the user had admin permissions [1]. However, pg_ctl start command could be
> done by the same user.
> I'm not familiar with Windows, but according to your blogpost [2], Windows seems
> to allows running some server command by generating the restricted context for
> running postgres commands. In case of single-user mode, however, we directly run
> the given command with the current user. So, there is a possibility that only
> instance can boot only by pg_ctl.
> Based on that, I want to skip the test on windows platform rather than modifying
> the ci environment.
>
> IIUC, other tests which uses --single cannot work on windows as well.
This has reminded me of 1a9d80282811, where I have used a trick with
pg_ctl to allow single-user mode executions to bypass the privileged
account permission failure, but I don't see what we could do here as
we should pass down commands to postgres for execution via stdin for
IPC::Run. We don't really have anything WIN32-specific, so skipping
the tests on Windows sounds fine by me.
One thing that you have forgotten is to update EXTRA_INSTALL to add
test_decoding, or a make check in the module fails. I have expanded a
bit more the tests, as for example we have paths based on active_pid
for temporary slots, which could matter at some point. I have added a
few more things with physical slots.
With all that in mind, I don't really see the point of 0002. There
was also a mention of replication origins upthread, but we don't
really have anything specific to a shared state or a validation in
this area AFAIK, so I don't think that test additions are worth it in
this case.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, ../../[email protected]/2-signature.asc)
download
^ permalink raw reply [nested|flat] 31+ messages in thread
* RE: ReplicationSlotRelease() crashes when the instance is in the single user mode
@ 2025-08-20 07:42 Hayato Kuroda (Fujitsu) <[email protected]>
parent: Michael Paquier <[email protected]>
0 siblings, 1 reply; 31+ messages in thread
From: Hayato Kuroda (Fujitsu) @ 2025-08-20 07:42 UTC (permalink / raw)
To: 'Michael Paquier' <[email protected]>; +Cc: 'Paul A Jungwirth' <[email protected]>; Mutaamba Maasha <[email protected]>; Amit Kapila <[email protected]>; David G. Johnston <[email protected]>; Zhijie Hou (Fujitsu) <[email protected]>; Tom Lane <[email protected]>; Bertrand Drouvot <[email protected]>; [email protected] <[email protected]>
Dear Michael,
I found you've pushed 0001. Thanks!
I've considered to create patches for other branches after you said OK, but
you seemed to push all of them directly.
> This has reminded me of 1a9d80282811, where I have used a trick with
> pg_ctl to allow single-user mode executions to bypass the privileged
> account permission failure, but I don't see what we could do here as
> we should pass down commands to postgres for execution via stdin for
> IPC::Run. We don't really have anything WIN32-specific, so skipping
> the tests on Windows sounds fine by me.
OK, thanks for the confirmation.
> One thing that you have forgotten is to update EXTRA_INSTALL to add
> test_decoding, or a make check in the module fails. I have expanded a
> bit more the tests, as for example we have paths based on active_pid
> for temporary slots, which could matter at some point. I have added a
> few more things with physical slots.
Oh, I didn't recognize because I usually use meson, and my CI said OK.
Good update!
> With all that in mind, I don't really see the point of 0002. There
> was also a mention of replication origins upthread, but we don't
> really have anything specific to a shared state or a validation in
> this area AFAIK, so I don't think that test additions are worth it in
> this case.
Let me clarify your point. For now, there are no decisions to prohibit origin
manipulations. 0002 only restricts to handle slots in the single-user mode. Did
you say that we do not have to do tests to ensure these SQL functions are
prohibited in the mode?
(Since senior members in this thread are against to restrict, I'm planning to
stop working 0002 after creating a final patch for the reference purpose)
Best regards,
Hayato Kuroda
FUJITSU LIMITED
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: ReplicationSlotRelease() crashes when the instance is in the single user mode
@ 2025-08-20 08:02 Michael Paquier <[email protected]>
parent: Hayato Kuroda (Fujitsu) <[email protected]>
0 siblings, 1 reply; 31+ messages in thread
From: Michael Paquier @ 2025-08-20 08:02 UTC (permalink / raw)
To: Hayato Kuroda (Fujitsu) <[email protected]>; +Cc: 'Paul A Jungwirth' <[email protected]>; Mutaamba Maasha <[email protected]>; Amit Kapila <[email protected]>; David G. Johnston <[email protected]>; Zhijie Hou (Fujitsu) <[email protected]>; Tom Lane <[email protected]>; Bertrand Drouvot <[email protected]>; [email protected] <[email protected]>
On Wed, Aug 20, 2025 at 07:42:11AM +0000, Hayato Kuroda (Fujitsu) wrote:
> Let me clarify your point. For now, there are no decisions to prohibit origin
> manipulations. 0002 only restricts to handle slots in the single-user mode. Did
> you say that we do not have to do tests to ensure these SQL functions are
> prohibited in the mode?
I am saying that there is little point in having tests for the origin
functions in single-user mode as these don't do anything really
fancy with global states (there's a acquired_by of course, and no
specific IsUnderPostmaster patch), contrary to the replication slots.
I am not convinced that there is any need to restrict them, either.
If somebody shows a reason to justify such a move, we could argue
about it, of course.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, ../../[email protected]/2-signature.asc)
download
^ permalink raw reply [nested|flat] 31+ messages in thread
* RE: ReplicationSlotRelease() crashes when the instance is in the single user mode
@ 2025-08-21 06:39 Hayato Kuroda (Fujitsu) <[email protected]>
parent: Michael Paquier <[email protected]>
0 siblings, 0 replies; 31+ messages in thread
From: Hayato Kuroda (Fujitsu) @ 2025-08-21 06:39 UTC (permalink / raw)
To: 'Michael Paquier' <[email protected]>; [email protected] <[email protected]>; +Cc: 'Paul A Jungwirth' <[email protected]>; Mutaamba Maasha <[email protected]>; Amit Kapila <[email protected]>; David G. Johnston <[email protected]>; Zhijie Hou (Fujitsu) <[email protected]>; Tom Lane <[email protected]>; Bertrand Drouvot <[email protected]>
Dear Michael and hackers,
This is a wrap-up of this thread. I'm planning to close the thread once.
> I am saying that there is little point in having tests for the origin
> functions in single-user mode as these don't do anything really
> fancy with global states (there's a acquired_by of course, and no
> specific IsUnderPostmaster patch), contrary to the replication slots.
> I am not convinced that there is any need to restrict them, either.
> If somebody shows a reason to justify such a move, we could argue
> about it, of course.
OK, thanks for the clarification. Yes, there are no special path for
single-user mode for origins.
Now, senior members recently appeared here are not so motivated to restrict some
operations in single-user mode unless it cannot be done in principle. Based on
that, I decided to close the thread once until someone raised another point.
For the reference purpose, I attached a patch which prohibits slot manipulation
in single-user mode. Anyone can update and use the patch for any purposes.
Best regards,
Hayato Kuroda
FUJITSU LIMITED
Attachments:
[application/octet-stream] v8-0001-Prohibit-slot-manipulation-while-in-single-user-m.patch (8.6K, ../../OSCPR01MB149668B67979F8F4C73B69E2EF532A@OSCPR01MB14966.jpnprd01.prod.outlook.com/2-v8-0001-Prohibit-slot-manipulation-while-in-single-user-m.patch)
download | inline diff:
From 2e5c5aeab9eadcc386dec00e7f2c67bbaa900667 Mon Sep 17 00:00:00 2001
From: Hayato Kuroda <[email protected]>
Date: Wed, 19 Feb 2025 11:37:26 +0900
Subject: [PATCH v8] Prohibit slot manipulation while in single-user mode
Replication-related commands are rarely used in single-user mode and have been
broken for years. This commit prohibits calling slot manipulation SQL functions
to avoid additional risk of failures. One exception is pg_drop_replication_slot.
It is still allowed because users may want to clean up their mistakes in the mode.
---
doc/src/sgml/func/func-admin.sgml | 5 ++
.../replication/logical/logicalfuncs.c | 3 ++
src/backend/replication/slot.c | 12 +++++
src/backend/replication/slotfuncs.c | 14 +++++
src/backend/utils/adt/pg_upgrade_support.c | 3 ++
src/include/replication/slot.h | 1 +
.../test_misc/t/008_replslot_single_user.pl | 53 ++++++++++++++-----
7 files changed, 78 insertions(+), 13 deletions(-)
diff --git a/doc/src/sgml/func/func-admin.sgml b/doc/src/sgml/func/func-admin.sgml
index 6347fe60b0c..6363084bc94 100644
--- a/doc/src/sgml/func/func-admin.sgml
+++ b/doc/src/sgml/func/func-admin.sgml
@@ -1012,6 +1012,11 @@ postgres=# SELECT '0/0'::pg_lsn + pd.segment_number * ps.setting::int + :offset
are also relevant for replication.
</para>
+ <para>
+ Note that slot manipulation functions except <function>pg_drop_replication_slot</function>
+ cannot be used in single-user mode.
+ </para>
+
<table id="functions-replication-table">
<title>Replication Management Functions</title>
<tgroup cols="1">
diff --git a/src/backend/replication/logical/logicalfuncs.c b/src/backend/replication/logical/logicalfuncs.c
index ca53caac2f2..30877fdc3ab 100644
--- a/src/backend/replication/logical/logicalfuncs.c
+++ b/src/backend/replication/logical/logicalfuncs.c
@@ -113,6 +113,9 @@ pg_logical_slot_get_changes_guts(FunctionCallInfo fcinfo, bool confirm, bool bin
List *options = NIL;
DecodingOutputState *p;
+ /* Slot manipulation is not allowed in single-user mode */
+ CheckSlotIsInSingleUserMode();
+
CheckSlotPermissions();
CheckLogicalDecodingRequirements();
diff --git a/src/backend/replication/slot.c b/src/backend/replication/slot.c
index fd0fdb96d42..4ca1227cc94 100644
--- a/src/backend/replication/slot.c
+++ b/src/backend/replication/slot.c
@@ -1529,6 +1529,18 @@ CheckSlotPermissions(void)
"REPLICATION")));
}
+/*
+ * Check whether the instance is in single-user mode.
+ */
+void
+CheckSlotIsInSingleUserMode(void)
+{
+ if (!IsUnderPostmaster)
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("replication slots cannot be used in single-user mode")));
+}
+
/*
* Reserve WAL for the currently active slot.
*
diff --git a/src/backend/replication/slotfuncs.c b/src/backend/replication/slotfuncs.c
index 69f4c6157c5..90fe63283a1 100644
--- a/src/backend/replication/slotfuncs.c
+++ b/src/backend/replication/slotfuncs.c
@@ -17,6 +17,7 @@
#include "access/xlogrecovery.h"
#include "access/xlogutils.h"
#include "funcapi.h"
+#include "miscadmin.h"
#include "replication/logical.h"
#include "replication/slot.h"
#include "replication/slotsync.h"
@@ -76,6 +77,9 @@ pg_create_physical_replication_slot(PG_FUNCTION_ARGS)
if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
elog(ERROR, "return type must be a row type");
+ /* Slot manipulation is not allowed in single-user mode */
+ CheckSlotIsInSingleUserMode();
+
CheckSlotPermissions();
CheckSlotRequirements();
@@ -182,6 +186,9 @@ pg_create_logical_replication_slot(PG_FUNCTION_ARGS)
if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
elog(ERROR, "return type must be a row type");
+ /* Slot manipulation is not allowed in single-user mode */
+ CheckSlotIsInSingleUserMode();
+
CheckSlotPermissions();
CheckLogicalDecodingRequirements();
@@ -521,6 +528,9 @@ pg_replication_slot_advance(PG_FUNCTION_ARGS)
Assert(!MyReplicationSlot);
+ /* Slot manipulation is not allowed in single-user mode */
+ CheckSlotIsInSingleUserMode();
+
CheckSlotPermissions();
if (XLogRecPtrIsInvalid(moveto))
@@ -618,9 +628,13 @@ copy_replication_slot(FunctionCallInfo fcinfo, bool logical_slot)
TupleDesc tupdesc;
HeapTuple tuple;
+
if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
elog(ERROR, "return type must be a row type");
+ /* Slot manipulation is not allowed in single-user mode */
+ CheckSlotIsInSingleUserMode();
+
CheckSlotPermissions();
if (logical_slot)
diff --git a/src/backend/utils/adt/pg_upgrade_support.c b/src/backend/utils/adt/pg_upgrade_support.c
index a4f8b4faa90..337f705b34b 100644
--- a/src/backend/utils/adt/pg_upgrade_support.c
+++ b/src/backend/utils/adt/pg_upgrade_support.c
@@ -296,6 +296,9 @@ binary_upgrade_logical_slot_has_caught_up(PG_FUNCTION_ARGS)
*/
Assert(has_rolreplication(GetUserId()));
+ /* Slot manipulation is not allowed in single-user mode */
+ CheckSlotIsInSingleUserMode();
+
slot_name = PG_GETARG_NAME(0);
/* Acquire the given slot */
diff --git a/src/include/replication/slot.h b/src/include/replication/slot.h
index e8fc342d1a9..b4d89760f96 100644
--- a/src/include/replication/slot.h
+++ b/src/include/replication/slot.h
@@ -342,6 +342,7 @@ extern void CheckPointReplicationSlots(bool is_shutdown);
extern void CheckSlotRequirements(void);
extern void CheckSlotPermissions(void);
+extern void CheckSlotIsInSingleUserMode(void);
extern ReplicationSlotInvalidationCause
GetSlotInvalidationCause(const char *cause_name);
extern const char *GetSlotInvalidationCauseName(ReplicationSlotInvalidationCause cause);
diff --git a/src/test/modules/test_misc/t/008_replslot_single_user.pl b/src/test/modules/test_misc/t/008_replslot_single_user.pl
index 796700d621f..4a93ed585d3 100644
--- a/src/test/modules/test_misc/t/008_replslot_single_user.pl
+++ b/src/test/modules/test_misc/t/008_replslot_single_user.pl
@@ -29,8 +29,30 @@ sub test_single_mode
],
'<' => \$queries);
+ return $result;
+}
+
+# Wrapper function for test_single_mode. This would be used when the input
+# command succeeds.
+sub test_single_mode_ok
+{
+ my ($node, $queries, $testname) = @_;
+
+ my $result = test_single_mode($node, $queries, $testname);
+
ok($result, $testname);
}
+
+# Wrapper function for test_single_mode. This would be used when the input
+# command fails.
+sub test_single_mode_fail
+{
+ my ($node, $queries, $testname) = @_;
+
+ my $result = test_single_mode($node, $queries, $testname);
+
+ ok(!$result, $testname);
+}
my $slot_logical = 'slot_logical';
my $slot_physical = 'slot_physical';
@@ -41,53 +63,58 @@ $node->init(allows_streaming => "logical");
$node->start;
# Define initial table
-$node->safe_psql('postgres', "CREATE TABLE foo (id int)");
+$node->safe_psql(
+ 'postgres', qq(
+CREATE TABLE foo (id int);
+SELECT pg_create_logical_replication_slot('$slot_logical', 'test_decoding');
+SELECT pg_create_physical_replication_slot('$slot_physical');
+));
$node->stop;
-test_single_mode(
+test_single_mode_fail(
$node,
- "SELECT pg_create_logical_replication_slot('$slot_logical', 'test_decoding')",
+ "SELECT pg_create_logical_replication_slot('another_slot', 'test_decoding')",
"logical slot creation");
-test_single_mode(
+test_single_mode_fail(
$node,
- "SELECT pg_create_physical_replication_slot('$slot_physical', true)",
+ "SELECT pg_create_physical_replication_slot('another_slot', true)",
"physical slot creation");
-test_single_mode(
+test_single_mode_fail(
$node,
"SELECT pg_create_physical_replication_slot('slot_tmp', true, true)",
"temporary physical slot creation");
-test_single_mode(
+test_single_mode_fail(
$node, qq(
INSERT INTO foo VALUES (1);
SELECT pg_logical_slot_get_changes('$slot_logical', NULL, NULL);
),
"logical decoding");
-test_single_mode(
+test_single_mode_fail(
$node,
"SELECT pg_replication_slot_advance('$slot_logical', pg_current_wal_lsn())",
"logical slot advance");
-test_single_mode(
+test_single_mode_fail(
$node,
"SELECT pg_replication_slot_advance('$slot_physical', pg_current_wal_lsn())",
"physical slot advance");
-test_single_mode(
+test_single_mode_fail(
$node,
"SELECT pg_copy_logical_replication_slot('$slot_logical', 'slot_log_copy')",
"logical slot copy");
-test_single_mode(
+test_single_mode_fail(
$node,
"SELECT pg_copy_physical_replication_slot('$slot_physical', 'slot_phy_copy')",
"physical slot copy");
-test_single_mode(
+test_single_mode_ok(
$node,
"SELECT pg_drop_replication_slot('$slot_logical')",
"logical slot drop");
-test_single_mode(
+test_single_mode_ok(
$node,
"SELECT pg_drop_replication_slot('$slot_physical')",
"physical slot drop");
--
2.47.1
^ permalink raw reply [nested|flat] 31+ messages in thread
end of thread, other threads:[~2025-08-21 06:39 UTC | newest]
Thread overview: 31+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-03-09 20:06 [PATCH] pg_dump: use a cursor in getBlobs.. Justin Pryzby <[email protected]>
2025-02-18 05:33 ReplicationSlotRelease() crashes when the instance is in the single user mode Hayato Kuroda (Fujitsu) <[email protected]>
2025-02-18 06:15 ` Re: ReplicationSlotRelease() crashes when the instance is in the single user mode Michael Paquier <[email protected]>
2025-02-18 07:29 ` RE: ReplicationSlotRelease() crashes when the instance is in the single user mode Hayato Kuroda (Fujitsu) <[email protected]>
2025-02-18 07:50 ` Re: ReplicationSlotRelease() crashes when the instance is in the single user mode Michael Paquier <[email protected]>
2025-02-18 08:23 ` RE: ReplicationSlotRelease() crashes when the instance is in the single user mode Hayato Kuroda (Fujitsu) <[email protected]>
2025-02-19 02:57 ` RE: ReplicationSlotRelease() crashes when the instance is in the single user mode Hayato Kuroda (Fujitsu) <[email protected]>
2025-02-20 00:02 ` Re: ReplicationSlotRelease() crashes when the instance is in the single user mode Michael Paquier <[email protected]>
2025-02-20 02:22 ` RE: ReplicationSlotRelease() crashes when the instance is in the single user mode Hayato Kuroda (Fujitsu) <[email protected]>
2025-02-20 02:27 ` Re: ReplicationSlotRelease() crashes when the instance is in the single user mode David G. Johnston <[email protected]>
2025-02-20 10:29 ` Re: ReplicationSlotRelease() crashes when the instance is in the single user mode Bertrand Drouvot <[email protected]>
2025-02-20 12:51 ` RE: ReplicationSlotRelease() crashes when the instance is in the single user mode Hayato Kuroda (Fujitsu) <[email protected]>
2025-02-22 04:58 ` Re: ReplicationSlotRelease() crashes when the instance is in the single user mode Amit Kapila <[email protected]>
2025-02-27 07:58 ` RE: ReplicationSlotRelease() crashes when the instance is in the single user mode Hayato Kuroda (Fujitsu) <[email protected]>
2025-02-28 03:13 ` Re: ReplicationSlotRelease() crashes when the instance is in the single user mode Amit Kapila <[email protected]>
2025-02-28 05:41 ` RE: ReplicationSlotRelease() crashes when the instance is in the single user mode Hayato Kuroda (Fujitsu) <[email protected]>
2025-08-05 16:51 ` Re: ReplicationSlotRelease() crashes when the instance is in the single user mode Paul A Jungwirth <[email protected]>
2025-08-11 18:25 ` Re: ReplicationSlotRelease() crashes when the instance is in the single user mode Robert Haas <[email protected]>
2025-08-13 07:21 ` RE: ReplicationSlotRelease() crashes when the instance is in the single user mode Hayato Kuroda (Fujitsu) <[email protected]>
2025-08-13 20:04 ` Re: ReplicationSlotRelease() crashes when the instance is in the single user mode Robert Haas <[email protected]>
2025-08-18 08:53 ` RE: ReplicationSlotRelease() crashes when the instance is in the single user mode Hayato Kuroda (Fujitsu) <[email protected]>
2025-08-18 08:47 ` RE: ReplicationSlotRelease() crashes when the instance is in the single user mode Hayato Kuroda (Fujitsu) <[email protected]>
2025-08-18 09:02 ` Re: ReplicationSlotRelease() crashes when the instance is in the single user mode Michael Paquier <[email protected]>
2025-08-19 10:26 ` RE: ReplicationSlotRelease() crashes when the instance is in the single user mode Hayato Kuroda (Fujitsu) <[email protected]>
2025-08-19 11:16 ` RE: ReplicationSlotRelease() crashes when the instance is in the single user mode Hayato Kuroda (Fujitsu) <[email protected]>
2025-08-19 11:17 ` Re: ReplicationSlotRelease() crashes when the instance is in the single user mode Michael Paquier <[email protected]>
2025-08-19 12:52 ` RE: ReplicationSlotRelease() crashes when the instance is in the single user mode Hayato Kuroda (Fujitsu) <[email protected]>
2025-08-20 06:05 ` Re: ReplicationSlotRelease() crashes when the instance is in the single user mode Michael Paquier <[email protected]>
2025-08-20 07:42 ` RE: ReplicationSlotRelease() crashes when the instance is in the single user mode Hayato Kuroda (Fujitsu) <[email protected]>
2025-08-20 08:02 ` Re: ReplicationSlotRelease() crashes when the instance is in the single user mode Michael Paquier <[email protected]>
2025-08-21 06:39 ` RE: ReplicationSlotRelease() crashes when the instance is in the single user mode Hayato Kuroda (Fujitsu) <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox