public inbox for pgsql-www@postgresql.org
help / color / mirror / Atom feedFrom: Célestin Matte <celestin.matte@cmatte.me>
To: Magnus Hagander <magnus@hagander.net>
Cc: PostgreSQL WWW <pgsql-www@lists.postgresql.org>
Subject: Re: pglister: pgarchives does not synchronize subscribers with email delivery disabled (proposed patch)
Date: Thu, 18 Dec 2025 12:22:21 +0100
Message-ID: <3de25c28-38a9-414a-aa69-219164bd6afb@cmatte.me> (raw)
In-Reply-To: <CABUevEyGraEg-z_602j8oukikOYy05j-Raxgt8Ndi3WGsi069Q@mail.gmail.com>
References: <b39abaaf-00f0-47b9-b819-3b22d83a67d8@cmatte.me>
<CABUevEyGraEg-z_602j8oukikOYy05j-Raxgt8Ndi3WGsi069Q@mail.gmail.com>
> mailinglist_subscribers is used for actual mail delivery as well, isn't it? If you just change the view, it will change all deliveries and basically the nomail switch to not work at all?
>
> While you're on the right track to include them, don't we also need to include the nomail column as a column in the view and then filter on that when sending email?
Correct. I added nomail in the mailinglist_subscribers view, then made just it was filtered upon in mailhandler.py. Series of patches attached
Thanks for the review!
--
Célestin Matte
Attachments:
[text/x-patch] 0001-Include-nomail-users-in-API-call-results.patch (1.6K, 2-0001-Include-nomail-users-in-API-call-results.patch)
download | inline diff:
From 37ad3abc2887f28f118d1ae478e704698b731c3b Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?C=C3=A9lestin=20Matte?= <dev@cmatte.me>
Date: Mon, 22 Sep 2025 19:57:21 +0200
Subject: [PATCH 1/3] Include nomail users in API call results
Otherwise, pgarchives's pglister_sync won't give access to these users
---
...60_fix_mailinglist_subscribers_sql_view.py | 28 +++++++++++++++++++
1 file changed, 28 insertions(+)
create mode 100644 web/pglister/lists/migrations/0060_fix_mailinglist_subscribers_sql_view.py
diff --git a/web/pglister/lists/migrations/0060_fix_mailinglist_subscribers_sql_view.py b/web/pglister/lists/migrations/0060_fix_mailinglist_subscribers_sql_view.py
new file mode 100644
index 0000000..5f49e48
--- /dev/null
+++ b/web/pglister/lists/migrations/0060_fix_mailinglist_subscribers_sql_view.py
@@ -0,0 +1,28 @@
+# -*- coding: utf-8 -*-
+from __future__ import unicode_literals
+
+from django.db import migrations, models
+
+
+class Migration(migrations.Migration):
+
+ dependencies = [
+ ('lists', '0059_rfc8058'),
+ ]
+
+ operations = [
+ migrations.RunSQL("""
+CREATE OR REPLACE VIEW mailinglist_subscribers AS
+ SELECT list_id AS listid,
+ ls.subscriber_id AS subscriberaddress_id,
+ email,
+ eliminatecc,
+ token,
+ s.user_id AS userid,
+ (SELECT array_agg(listtag_id) FROM lists_listsubscription_tags lst WHERE lst.listsubscription_id=ls.id) AS tags
+ FROM lists_listsubscription ls
+ INNER JOIN lists_subscriberaddress sa ON sa.id=ls.subscriber_id
+ LEFT JOIN lists_subscriber s ON s.user_id=sa.subscriber_id
+ WHERE confirmed AND subscription_confirmed
+"""),
+ ]
--
2.52.0
[text/x-patch] 0002-Add-nomail-in-mailinglist_subscribers-view.patch (1.3K, 3-0002-Add-nomail-in-mailinglist_subscribers-view.patch)
download | inline diff:
From cc778a4360fa7e3788eeff260b6cdaf313a62fc7 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?C=C3=A9lestin=20Matte?= <dev@cmatte.me>
Date: Thu, 18 Dec 2025 11:21:04 +0100
Subject: [PATCH 2/3] Add nomail in mailinglist_subscribers view
So we can filter based on it during email delivery
---
.../migrations/0060_fix_mailinglist_subscribers_sql_view.py | 3 ++-
1 file changed, 2 insertions(+), 1 deletion(-)
diff --git a/web/pglister/lists/migrations/0060_fix_mailinglist_subscribers_sql_view.py b/web/pglister/lists/migrations/0060_fix_mailinglist_subscribers_sql_view.py
index 5f49e48..a8e2971 100644
--- a/web/pglister/lists/migrations/0060_fix_mailinglist_subscribers_sql_view.py
+++ b/web/pglister/lists/migrations/0060_fix_mailinglist_subscribers_sql_view.py
@@ -19,7 +19,8 @@ CREATE OR REPLACE VIEW mailinglist_subscribers AS
eliminatecc,
token,
s.user_id AS userid,
- (SELECT array_agg(listtag_id) FROM lists_listsubscription_tags lst WHERE lst.listsubscription_id=ls.id) AS tags
+ (SELECT array_agg(listtag_id) FROM lists_listsubscription_tags lst WHERE lst.listsubscription_id=ls.id) AS tags,
+ nomail
FROM lists_listsubscription ls
INNER JOIN lists_subscriberaddress sa ON sa.id=ls.subscriber_id
LEFT JOIN lists_subscriber s ON s.user_id=sa.subscriber_id
--
2.52.0
[text/x-patch] 0003-mail-handler-don-t-send-email-to-users-with-nomail-s.patch (1.8K, 4-0003-mail-handler-don-t-send-email-to-users-with-nomail-s.patch)
download | inline diff:
From 351155d94417bdade7e6cbaff0b04f430566067a Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?C=C3=A9lestin=20Matte?= <dev@cmatte.me>
Date: Thu, 18 Dec 2025 12:04:08 +0100
Subject: [PATCH 3/3] mail handler: don't send email to users with nomail set
---
lib/handlers/mailhandler.py | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/lib/handlers/mailhandler.py b/lib/handlers/mailhandler.py
index 71a7d4e..c248871 100644
--- a/lib/handlers/mailhandler.py
+++ b/lib/handlers/mailhandler.py
@@ -866,7 +866,7 @@ ORDER BY 1""",
'Mail to list {0} delivered to {1} tags ({2})'.format(self.mlist.name, len(self.tags), ",".join(self.tagnames)),
self.messageid)
- curs.execute("INSERT INTO outgoing_recipients (outgoing_id, subscriberaddress_id, recipient_headers) SELECT %(outid)s, subscriberaddress_id, CASE WHEN l.disable_list_headers THEN '' ELSE recipient_list_headers(%(webroot)s, %(listid)s, token) END FROM mailinglist_subscribers s INNER JOIN lists_list l ON l.id=s.listid WHERE listid=%(listid)s {0} AND NOT (COALESCE(eliminatecc, false) AND email=ANY(%(cclist)s)) ORDER BY split_part(email, '@', 2)".format(qextra), params)
+ curs.execute("INSERT INTO outgoing_recipients (outgoing_id, subscriberaddress_id, recipient_headers) SELECT %(outid)s, subscriberaddress_id, CASE WHEN l.disable_list_headers THEN '' ELSE recipient_list_headers(%(webroot)s, %(listid)s, token) END FROM mailinglist_subscribers s INNER JOIN lists_list l ON l.id=s.listid WHERE listid=%(listid)s {0} AND NOT (COALESCE(eliminatecc, false) AND email=ANY(%(cclist)s)) AND not nomail ORDER BY split_part(email, '@', 2)".format(qextra), params)
recipientcount = curs.rowcount
curs.execute("NOTIFY outgoing")
--
2.52.0
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-www@postgresql.org
Cc: celestin.matte@cmatte.me, magnus@hagander.net, pgsql-www@lists.postgresql.org
Subject: Re: pglister: pgarchives does not synchronize subscribers with email delivery disabled (proposed patch)
In-Reply-To: <3de25c28-38a9-414a-aa69-219164bd6afb@cmatte.me>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox