Streaming Replication Disconnection Behavior under recovery_min_apply_delay Configuration

[Riku Kashiwaki (Fujitsu)]

I would like to know the behavior when disconnecting streaming replication.
An attempt was made to disconnect streaming replication from a standby server 
during streaming replication by using the ALTER SET command and setting primary 
_conninfo to an empty string.
However, we have observed that if recovery_min_apply_delay is set and 
primary_conninfo is set to an empty string + reloaded, with some WAL remaining 
that has been sent to the standby but not yet applied, replication will not 
break.

Specifically, the following procedures are used.
environment: Postgres16
 recovery_min_apply_delay= set to 5min
 One Primary, One Standby Streaming Replication Configuration
① primary_conninfo set to an empty string
 #ALTER SET primary_conninfo=''"
② Reload and reload the configuration file
 #pg_ctl reload
③ Ensure replication is not terminated
 #psql -d postgres -p 27500 -c "select * from pg_stat_replication"
   pid | usesysid | usename | application_name | client_addr | client_hostname 
| client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | 
flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | 
sync_state | reply_time
-+--+-+--+-+-+-+---+--+---+++++-+-+-+---++---
 3707259 | 10 |  | walreceiver | | | -1 | 2025-**-** **:**:**.*** | | 
streaming | 0/190047E0 | 0/190047E0 | 0/190047E0 | 0/19002840 | 00:00:00.73 
| 00:00:00.000261 | 00:01:16.999715 | 0 | async | 2025-**-** **:**:**.**
(1 line)

④ Verify replication is finished after 5 minutes
 #psql -d postgres -p 27500 -c "select * from pg_stat_replication"
   pid | usesysid | usename | application_name | client_addr | client_hostname 
| client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | 
flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | 
sync_state | reply_time
-+--+-+--+-+-+-+---+--+---+++++-+-+-+---++---
(0 lines)
As shown above, walreceiver does not terminate until the time specified by 
recovery_min_apply_delay has elapsed, even after receiving WAL from the primary.
When you stop the strep configuration, you don't need to make the data 
consistent with the primary (make the DB state equivalent to the primary), and 
it seems natural that the strep configuration should be disconnected 
immediately.
Why doesn't the walreceiver process exit until WAL has been applied?

Also, in the source code of walreceiver (Lines 452 to 491 of walreceiver.c), 
there is a process to wait for completion of WAL reception (* 1), but there is 
no description of the action to wait for walreceiver to terminate if unapplied 
WAL remains.
I would appreciate it if you could tell me where you are waiting for 
walreceiver to exit.


*1 Details
https://github.com/postgres/postgres/blob/master/src/backend/replication/walreceiver.c

(walreceiver.c)
[Processing to loop until walreceiver ends]
 Defines the endofwal flag used to terminate the walreceiver.
/* Loop until end-of-streaming or error */
for (;;)
{
char *buf;
int len;
boolendofwal = false;
pgsocketwait_fd = PGINVALID_SOCKET;
int rc;
TimestampTz nextWakeup;
longnap;


[Continuing/terminating walreceiver with reference to the length of unreceived 
WAL (len)]
 len is assigned the return value of the walrcv_receive function (★).
 walrcv_receive returns the length of WAL that has not yet been received from 
the primary (or -1 if it has).


/* See if we can read data immediately */
len = walrcv_receive(wrconn, &buf, &wait_fd); ★
if (len != 0)
{
/*
 * Process the received data, and any 
subsequent data we
 * can read without blocking.
 */
for (;;)
{
if (len > 0)
{

Re: issue/bug management, project management, people management, product management all in one, preferably open source software ?

[Achilleas Mantzios]

On 5/22/25 19:50, Sam Gendler wrote:
I think the closest you would get to a single product for all that is 
a single suite of products which are reasonably well-integrated. 
issue, project, and product management are almost always combined 
unless you are just using github issue tracking or the like. Jira 
certainly covers all of those.  And it will integrate with vanilla 
git, though you will lose functionality compared to the integrations 
with github and bitbucket simply because vanilla git lacks features 
compared to those.  The rest of the atlassian product suite adds 
plenty of functionality around the rest of the stuff you are asking 
for. But it's hard to imagine you haven't already considered 
atlassian, so I assume you are looking for a recommendation for 
something other than that, but I can't think of anything I've used 
that is actually better (and I say that as someone who doesn't really 
like Jira, either). It's a pretty low bar, admittedly.


Jira is one of those products, like SalesForce, which is enormously 
powerful, but only after you've customized the heck out of it because 
that is how it is intended to be used. Massively flexible, 
configurable, and automatable, the setup out of the box is far from 
optimal for most teams.  You really have to know what you want and 
tell the software how to set itself up to support that. And it helps 
if you have an agile-esque project management methodology as it is 
certainly developed with that in mind. It isn't going to dictate to 
you how to manage issues, projects, and products. It will very 
flexibly allow you to set it up to work with it in almost any way you 
like - but you have to have someone become something of an expert in 
the platform AND you have to have a very concrete understanding of 
exactly how you want to use it or you end up with the wishy washy, not 
very effective project and issue management workflows dictated by the 
default configuration, which is very lowest common denominator.


Thank you! I can see the eternal trade-off : features (we take) VS 
complexity of usage (we give). Thing is , I do a lot of things , lots of 
roles, but project mgmt is a must, so someone , somewhere must bite the 
bullet sooner or later.


I will definitely consider this JIRA !



On Thu, May 22, 2025 at 10:52 AM Achilleas Mantzios 
 wrote:


Hi people

I'd like to know if people here know of or use any integrated
solution
for all or some of the above. It would be nice if it supported LDAP /
OAuth 2.0 , integrate with plain vanilla git (not github / gitlab)
and
be open, and active as a project.

We are at a phase our business is expanding, the projects also are
increasing in number and size, several of those are interconnected,
either depending or prerequisite or even inter-meshed .

I'd like to have a tool to manage all this, but also a tool to
show to
the stakeholders the actual picture of our system.

I'd be grateful for any hints !

Changing a varchar(7) domain into text directly in pg_type

[Richard Zetterberg]

Hello,

I have a read-only table that contains a set of never changing categories.
Each category has a unique alpha numerical ID and a description. The
purpose of this table is so that other tables can reference the ID of this
table, to make sure that they don't contain invalid/unknown categories and
so that users can lookup the description of each category.

This category table has the following type on the ID column: "varchar(7)"
(yes, I should have used text). In order to avoid having to type
"varchar(7)" in all the tables that references the category table, I
created this domain that I used as type for all referencing columns:
"CREATE DOMAIN cat.id AS varchar(7);".

During some data archeology, I found a bunch of new categories that haven't
been imported into the database yet, and they have IDs longer than 7.

I've seen claims that varchar and text have the same representation on disk
and that they are treated the same way "under the hood", except for the
extra constraint checks on varchar. So, I thought that maybe I could just
change the type of my domain to text, directly in pg_type and that should
solve my problems.

After some thinkering, and looking at how "CREATE DOMAIN cat.id AS
varchar(7);" and "CREATE DOMAIN cat.id AS text;" appeared in pg_type and
information_schema.domains, I came up with this query:

```
WITH text_oid AS (
  SELECT typ.oid AS text_oid
FROM pg_type AS typ
 INNER JOIN pg_namespace AS ns
 ON typ.typnamespace = ns.oid
AND typ.typname  = 'text'
), target_oid AS (
  SELECT typ.oid AS target_oid
FROM pg_type AS typ
 INNER JOIN pg_namespace AS ns
 ON typ.typnamespace = ns.oid
AND ns.nspname   = 'cat
AND typ.typname  = 'id'
), oids AS (
  SELECT *
FROM text_oid
 CROSS JOIN target_oid
) UPDATE pg_type AS styp
   SET typoutput   = 'textout'
 , typsend = 'textsend'
 , typbasetype = o.text_oid
 , typtypmod   = -1
  FROM oids AS o
 WHERE oid = o.target_oid;
```

After running that query, my domain had the type text instead of varchar(7)
and all the
fkeys to my category table seems to be working.

Could this be a viable option to solve my problem? Or will I face serious
problems later down the line that I haven't discovered/considered yet?

Thanks for any insight,
Richard Zetterberg

Re: PostgreSQL 15.10 update corrective action for ATTACH PARTITION/DETACH PARTITION

[Alvaro Herrera]

Hello,

Belatedly, I came back to this issue in the release notes.  Here's a
query for correctly reporting the problem and not reporting the cases
where there isn't a problem:

SELECT conrelid::pg_catalog.regclass AS "constrained table",
   conname AS constraint,
   confrelid::pg_catalog.regclass AS "references",
   pg_catalog.format('ALTER TABLE %s DROP CONSTRAINT %I;',
 conrelid::pg_catalog.regclass, conname) AS "drop",
   pg_catalog.format('ALTER TABLE %s ADD CONSTRAINT %I %s;',
 conrelid::pg_catalog.regclass, conname,
 pg_catalog.pg_get_constraintdef(oid)) AS "add"
FROM pg_catalog.pg_constraint c
WHERE contype = 'f' AND conparentid = 0 AND
   (SELECT count(*) FROM pg_catalog.pg_constraint c2
WHERE c2.conparentid = c.oid) <>
   ((SELECT count(*) FROM pg_catalog.pg_inherits i
WHERE (i.inhparent = c.conrelid OR i.inhparent = c.confrelid) AND
  EXISTS (SELECT 1 FROM pg_catalog.pg_partitioned_table
  WHERE partrelid = i.inhparent)) +
CASE WHEN pg_partition_root(conrelid) = confrelid THEN
  (SELECT count(*) FROM pg_catalog.pg_partition_tree(confrelid) 
WHERE level = 1)
 ELSE 0 END);

The difference from the query that's currently in the release notes is
that here we count the number of direct partitions of the referenced
table and expect that there be exactly that number of additional
constraint entries in a self-referential FK, compared to the situation
where the FK references a different table.  (The query I suggested
previously in this thread had a "+1" instead of adding the number of
partitions, which obviously works correctly only in one particular
case.)

I tested this using Paul's scenario, and a few more, and as far as I can
tell, it is correct.

I'm going to fix the query in the release notes for all past branches
now, to avoid confusing people upgrading in the future ... hopefully not
many, but I don't think it's going to be zero people.

Regards

-- 
Álvaro HerreraBreisgau, Deutschland  —  https://www.EnterpriseDB.com/
"Para tener más hay que desear menos"

Re: PostgreSQL 15.10 update corrective action for ATTACH PARTITION/DETACH PARTITION

[Tom Lane]

Alvaro Herrera  writes:
> I'm going to fix the query in the release notes for all past branches
> now, to avoid confusing people upgrading in the future ... hopefully not
> many, but I don't think it's going to be zero people.

OK, thanks.

regards, tom lane

Re: Changing a varchar(7) domain into text directly in pg_type

[Adrian Klaver]

On 5/27/25 7:27 AM, Richard Zetterberg wrote:

Hello,

I have a read-only table that contains a set of never changing 
categories. Each category has a unique alpha numerical ID and a 
description. The purpose of this table is so that other tables can 
reference the ID of this table, to make sure that they don't contain 
invalid/unknown categories and so that users can lookup the description 
of each category.
Define 'read-only'. In other words can you temporarily make it not 
read-only and change the type to text(or just varchar (no length specifier)?

This would be the easiest fix.



This category table has the following type on the ID column: 
"varchar(7)" (yes, I should have used text). In order to avoid having to 
type "varchar(7)" in all the tables that references the category table, 
I created this domain that I used as type for all referencing columns: 
"CREATE DOMAIN cat.id  AS varchar(7);".


During some data archeology, I found a bunch of new categories that 
haven't been imported into the database yet, and they have IDs longer 
than 7.


If the read-only table field has a maximum length of 7 and you have 
incoming data that is coming in longer then 7 characters, how are they 
going to reference the read-only table?




I've seen claims that varchar and text have the same representation on 
disk and that they are treated the same way "under the hood", except for 
the extra constraint checks on varchar. So, I thought that maybe I could 
just change the type of my domain to text, directly in pg_type and that 
should solve my problems


Per my comment above, how?



Thanks for any insight,
Richard Zetterberg


--
Adrian Klaver
adrian.kla...@aklaver.com