Subject: Re: Adding a readme to the pgarchives repo

From: Magnus Hagander <[email-masked]>
Date: Mon, 16 Jun 2025 13:26:20 +0200

On Sat, May 31, 2025 at 1:52 PM Jelte Fennema-Nio <[email-masked]>
wrote:

> Ping again.
>
> On Sun, 13 Apr 2025 at 22:05, Jelte Fennema-Nio <[email-masked]>
> wrote:
> >
> > On Thu, 31 Oct 2024 at 21:15, Jelte Fennema-Nio <[email-masked]>
> wrote:
> >>
> >> On Tue, 24 Sept 2024 at 22:56, Magnus Hagander <[email-masked]>
> wrote:
> >> > Well, that's just plain wrong :) The README is in the archives repo,
> it has nothing to do with commitfests...
> >>
> >> Fixed
> >
> >
> >  Ping ;)
>

Sorry about the ridiculous slowness in this one :/

I've applied patch 2, it'll be included in my next push (it's sitting
behind another thing that I'm currently testing, but soon).

For patch 1, there are a few things still.

The change in schema.sql to add subscriber_access should surely be a
separate commit, it's not part of adding a README file. (in fact, many
thing are not adding a README file). So I'll separate thato ne out right
away. That said, this one is also caught in the limbo between some things
being as migrations and some things not, that reall needs to get cleaned up
:/ Celestin worked on that at some point, I think, and I need to get back
to properly reviewing that.

I'm not sure how much sense it makes to load data from schema.sql. Wouldn't
it be better with a dev_data entr?

Also, I don't think removing text search configuration completely from a
dev install is the right way to go. All of this is definitely part of the
system and if nothing else one would want to make not to accidentally
*break* it in dev, thus they are needed.

The example settings file I think would also need the CSRF_COOKIE_SECURE
and SESSION_COOKIE_SECURE flags, no?

The comment for PUBLIC_ARCHIVES is incorrect, as it's not just about the
API, it's about access to the archives at all.

Why is uwsgi in requirements.txt and not in the apt-get install
prerequisite? That would make it much easier :) (same thing would go for
the other requirements added of course, but those do make more sense to
keep around)

And if one follows the instructions, the whole run_dev script shouldn't be
needed at all since you now the virtual env is installed in env?

And finally why do we need to comment out things in the sample file? it's
just a sample file after all?

//Magnus

---

From: Jelte Fennema-Nio <[email-masked]>
Date: Mon, 16 Jun 2025 22:32:40 +0200

On Mon, 16 Jun 2025 at 13:26, Magnus Hagander <[email-masked]> wrote:
> The change in schema.sql to add subscriber_access should surely be a separate commit, it's not part of adding a README file. (in fact, many thing are not adding a README file). So I'll separate thato ne out right away.

Makes sense. To be clear, the README + other changes I proposed is
mainly born out of trying to get this system running without any info
on how to do so. So reverse engineered some steps to get a
semi-functioning setup. I spent quite some effort trying to get
something usable working locally. So it seemed good to have that at
least as an example for other people to start from. Even if there's
probably some missing/weird/suboptimal stuff in there.

>That said, this one is also caught in the limbo between some things being as migrations and some things not, that reall needs to get cleaned up :/ Celestin worked on that at some point, I think, and I need to get back to properly reviewing that.

That sounds nice. I was unable to figure out why it was split like
this, so I chose to change the schema.sql file, because it seemed much
more complete.

> I'm not sure how much sense it makes to load data from schema.sql. Wouldn't it be better with a dev_data entr?

Done

> Also, I don't think removing text search configuration completely from a dev install is the right way to go. All of this is definitely part of the system and if nothing else one would want to make not to accidentally *break* it in dev, thus they are needed.

Added setup instructions now for those. I didn't before because it was
quite hard to reverse engineer how to set this up and it wasn't
necessary to get the API working. They work for me i.e. I can search.
But feel free to change them if you think they are incorrect.

> The example settings file I think would also need the CSRF_COOKIE_SECURE and SESSION_COOKIE_SECURE flags, no?

I didn't hit problems with this during my testing, but seems reasonable yes.

> The comment for PUBLIC_ARCHIVES is incorrect, as it's not just about the API, it's about access to the archives at all.

Changed

> Why is uwsgi in requirements.txt and not in the apt-get install prerequisite? That would make it much easier :) (same thing would go for the other requirements added of course, but those do make more sense to keep around)

Seemed nice to not pollute people their globally installed tools
unnecessarily. Also, less cross-platform stuff to worry about, since
not every-one is developing on a debian based system.

> And if one follows the instructions, the whole run_dev script shouldn't be needed at all since you now the virtual env is installed in env?

Sadly the run_dev script is needed, because we need to provide the
path to the django admin static files. That path includes the python
version number even in the venv... And the python version obviously
differs across systems.

> And finally why do we need to comment out things in the sample file? it's just a sample file after all?

The main reason is so that this file can simply be cp-ed to
`archives.ini` and the system will work (although without the
commented out features). I ran into this because the varnish purgeurl
made load_message.py get stuck, I assume because the default URL is
firewalled.
From e0469a0ecdcdd99d57601d9eb3d4b62347dd4997 Mon Sep 17 00:00:00 2001
From: Jelte Fennema-Nio <[email-masked]>
Date: Mon, 16 Jun 2025 17:46:45 +0200
Subject: [PATCH v4 1/2] Add LICENSE file

---
 LICENSE | 18 ++++++++++++++++++
 1 file changed, 18 insertions(+)
 create mode 100644 LICENSE

diff --git a/LICENSE b/LICENSE
new file mode 100644
index 0000000..7a6d41f
--- /dev/null
+++ b/LICENSE
@@ -0,0 +1,18 @@
+Copyright (c) 2013-2024, PostgreSQL Global Development Group
+
+Permission to use, copy, modify, and distribute this software and its
+documentation for any purpose, without fee, and without a written agreement
+is hereby granted, provided that the above copyright notice and this
+paragraph and the following two paragraphs appear in all copies.
+
+IN NO EVENT SHALL THE POSTGRESQL GLOBAL DEVELOPMENT GROUP BE LIABLE TO ANY PARTY FOR
+DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
+LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
+DOCUMENTATION, EVEN IF THE POSTGESQL GLOBAL DEVELOPMENT GROUP HAS BEEN ADVISED OF THE
+POSSIBILITY OF SUCH DAMAGE.
+
+THE POSTGESQL GLOBAL DEVELOPMENT GROUP SPECIFICALLY DISCLAIMS ANY WARRANTIES,
+INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
+AND FITNESS FOR A PARTICULAR PURPOSE.  THE SOFTWARE PROVIDED HEREUNDER IS
+ON AN "AS IS" BASIS, AND THE POSTGESQL GLOBAL DEVELOPMENT GROUP HAS NO OBLIGATIONS TO
+PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.

base-commit: 2326da76c3e94621eb9c0839ea7a8b8b0b456ebe
-- 
2.43.0

From a665178ee7b565d74219ab5fff4ef94899e672b8 Mon Sep 17 00:00:00 2001
From: Jelte Fennema-Nio <[email-masked]>
Date: Tue, 20 Aug 2024 09:21:49 +0200
Subject: [PATCH v4 2/2] Change to make local development easier, including
 README

This makes a bunch of changes, that are useful to get a local
development environment working. The primary change being a README
explaining how to do so.
---
 .gitignore                                |  1 +
 README.md                                 | 99 +++++++++++++++++++++++
 django/archives/example_settings_local.py | 23 ++++++
 django/run_dev.py                         | 22 +++++
 django/uwsgi_dev.ini                      | 10 +++
 loader/archives.ini.sample                | 16 ++--
 loader/sql/dev_data.sql                   |  3 +
 loader/sql/schema.sql                     |  3 +-
 pyproject.toml                            | 21 +++++
 9 files changed, 189 insertions(+), 9 deletions(-)
 create mode 100644 .gitignore
 create mode 100644 README.md
 create mode 100644 django/archives/example_settings_local.py
 create mode 100755 django/run_dev.py
 create mode 100644 django/uwsgi_dev.ini
 create mode 100644 loader/sql/dev_data.sql
 create mode 100644 pyproject.toml

diff --git a/.gitignore b/.gitignore
new file mode 100644
index 0000000..11041c7
--- /dev/null
+++ b/.gitignore
@@ -0,0 +1 @@
+*.egg-info
diff --git a/README.md b/README.md
new file mode 100644
index 0000000..2d6cc6d
--- /dev/null
+++ b/README.md
@@ -0,0 +1,99 @@
+# PG archives
+
+This application manages PostgreSQL mailing list archives.
+
+## The Application
+
+This is a Django 4.2 application backed by PostgreSQL and running on Python 3.x.
+
+## Getting Started
+
+### Ubuntu instructions
+
+First, prepare your development environment by installing python3, postgresql-server-dev-X.Y, formail and libtidy (use `--no-install-recommends` to avoid installing postfix):
+
+```bash
+sudo apt install python3 postgresql-server-dev-14 procmail libtidy5deb1 --no-install-recommends
+```
+
+Next, configure your local environment with virtualenv and install local dependencies.
+
+```bash
+python3 -m venv env
+source env/bin/activate
+pip install -e '.[dev]'
+```
+
+Install the `pg_tsparser` extension:
+```bash
+git clone https://github.com/postgrespro/pg_tsparser 
+cd pg_tsparser
+make USE_PGXS=1
+make USE_PGXS=1 install # might need sudo depending on your Postgres installation
+cd ..
+rm -rf pg_tsparser/
+```
+
+Install the `en_us` dictionary for the `pg_tsparser` extension:
+
+```bash
+git clone https://github.com/postgrespro/hunspell_dicts
+cd hunspell_dicts/hunspell_en_us
+make USE_PGXS=1
+make USE_PGXS=1 install # might need sudo depending on your Postgres installation
+cd ../..
+rm -rf hunspell_dicts/
+```
+
+Create dummy synonym and stop files:
+```bash
+touch "$(pg_config --sharedir)/tsearch_data/pg_dict.syn"
+touch "$(pg_config --sharedir)/tsearch_data/pg_dict.stop"
+```
+
+Create a database for the application:
+
+```bash
+createdb archives
+psql -d archives -c 'CREATE EXTENSION pg_tsparser' \
+    -c 'CREATE EXTENSION hunspell_en_us'  \
+    -c '\i loader/sql/schema.sql' -c 'COMMIT' \
+    -c '\i loader/sql/dev_data.sql'
+```
+
+Create config for the loader scripts:
+
+```bash
+cp loader/archives.ini.sample loader/archives.ini
+```
+
+Load some emails from the actual PostgreSQL archives by downloading an mbox
+file from <https://www.postgresql.org/list/pgsql-hackers/> (e.g. the one from
+the current month) and running the following command. 
+
+NOTE: it's totally fine if some of the emails will fail to load due to a missing Message-ID field.
+
+```bash
+loader/load_message.py --list pgsql-hackers --mbox /path/to/downloaded/mbox/file
+```
+
+Then go to the `django` directory, that's where the actual web application is.
+
+```bash
+cd django
+```
+
+Create a local settings file (feel free to edit it):
+
+```bash
+cp archives/example_settings_local.py archives/settings_local.py
+```
+
+Finally, you're ready to start the web application:
+
+```bash
+./run_dev.py
+```
+
+Then open <http://localhost:8001/list/pgsql-hackers> to view your local mailing
+list archives.
diff --git a/django/archives/example_settings_local.py b/django/archives/example_settings_local.py
new file mode 100644
index 0000000..230c56b
--- /dev/null
+++ b/django/archives/example_settings_local.py
@@ -0,0 +1,23 @@
+# Enable more debugging information
+DEBUG = True
+# Prevent logging to try to send emails to postgresql.org admins.
+# Use the default Django logging settings instead.
+LOGGING = None
+
+DATABASES = {
+    "default": {
+        "ENGINE": "django.db.backends.postgresql_psycopg2",
+        "NAME": "archives",
+        "USER": "postgres",
+        "PASSWORD": "postgres",
+        "HOST": "0.0.0.0",
+    }
+}
+
+# We don't use HTTPS during development
+SESSION_COOKIE_SECURE = False
+CSRF_COOKIE_SECURE = False
+
+# Allow access to all clients
+PUBLIC_ARCHIVES = True
+ALLOWED_HOSTS = ["*"]
diff --git a/django/run_dev.py b/django/run_dev.py
new file mode 100755
index 0000000..268cd83
--- /dev/null
+++ b/django/run_dev.py
@@ -0,0 +1,22 @@
+#!/usr/bin/env python3
+from importlib.machinery import PathFinder
+import subprocess
+import sys
+
+django_path = PathFinder().find_spec("django").submodule_search_locations[0]
+
+django_admin_path = django_path + "/contrib/admin/static/admin"
+
+if len(sys.argv) > 1:
+    ini_file = sys.argv[1]
+else:
+    ini_file = "uwsgi_dev.ini"
+
+subprocess.run(
+    [
+        "uwsgi",
+        "--static-map",
+        f"/static/admin={django_path}/contrib/admin/static/admin",
+        ini_file,
+    ]
+)
diff --git a/django/uwsgi_dev.ini b/django/uwsgi_dev.ini
new file mode 100644
index 0000000..9ab26b2
--- /dev/null
+++ b/django/uwsgi_dev.ini
@@ -0,0 +1,10 @@
+[uwsgi]
+threads=1
+env=DJANGO_SETTINGS_MODULE=archives.settings
+module=archives.wsgi:application
+py-autoreload=1
+touch-reload = archives/settings.py
+touch-reload = archives/settings_local.py
+touch-reload = uwsgi_dev.ini
+http=127.0.0.1:8001
+static-map=/media-archives=media
diff --git a/loader/archives.ini.sample b/loader/archives.ini.sample
index a146f69..087cc43 100644
--- a/loader/archives.ini.sample
+++ b/loader/archives.ini.sample
@@ -2,16 +2,16 @@
 connstr=dbname=archives
 
 [varnish]
-purgeurl=https://wrigleys.postgresql.org/api/varnish/purge/
+# purgeurl=https://wrigleys.postgresql.org/api/varnish/purge/
 
 [smtp]
-server=localhost:9911
-heloname=localhost
-resender=[email-masked]
+# server=localhost:9911
+# heloname=localhost
+# resender=[email-masked]
 
 [pglister]
 # synchronize subscribers between pgarchives and pglister
-subscribers=0
-root=/path/to/pglister
-myname=pgarchives
-apikey=CHANGEME
+# subscribers=0
+# root=/path/to/pglister
+# myname=pgarchives
+# apikey=CHANGEME
diff --git a/loader/sql/dev_data.sql b/loader/sql/dev_data.sql
new file mode 100644
index 0000000..c937f7d
--- /dev/null
+++ b/loader/sql/dev_data.sql
@@ -0,0 +1,3 @@
+INSERT INTO listgroups (groupid, groupname, sortkey) VALUES (1, 'Developer lists', 1);
+INSERT INTO lists (listid, listname, shortdesc, description, active, groupid, subscriber_access) VALUES (1, 'pgsql-hackers', 'pgsql-hackers', 'The PostgreSQL developers team lives here. Discussion of current development issues, problems and bugs, and proposed new features. If your question cannot be answered by people in the other lists, and it is likely that only a developer will know the answer, you may re-post your question in this list. You must try elsewhere first!', True, 1, True);
+
diff --git a/loader/sql/schema.sql b/loader/sql/schema.sql
index 4e9508e..7180d34 100644
--- a/loader/sql/schema.sql
+++ b/loader/sql/schema.sql
@@ -91,6 +91,7 @@ CREATE TABLE loaderrors(
    err text NOT NULL
 );
 
+
 /* textsearch configs */
 CREATE TEXT SEARCH CONFIGURATION pg (PARSER=tsparser);
 
@@ -113,7 +114,7 @@ ALTER TEXT SEARCH CONFIGURATION pg
                      word, hword, hword_part
     WITH pg_stop, pg_dict, english_ispell, english_stem;
 ALTER TEXT SEARCH CONFIGURATION pg
-   DROP MAPPING FOR email, url, url_path, sfloat, float;
+   DROP MAPPING IF EXISTS FOR email, url, url_path, sfloat, float;
 
 CREATE FUNCTION messages_fti_trigger_func() RETURNS trigger AS $$
 BEGIN
diff --git a/pyproject.toml b/pyproject.toml
new file mode 100644
index 0000000..dc1712f
--- /dev/null
+++ b/pyproject.toml
@@ -0,0 +1,21 @@
+[project]
+name = "pgarchives"
+description = "Email archives webapp for the PostgreSQL community"
+dynamic = ["version"]
+readme = "README.md"
+license = "PostgreSQL"
+dependencies = [
+    "django>=4.2,<5.0",
+    "psycopg2",
+    "simplejson",
+    "requests",
+    "pycryptodomex",
+    "python-dateutil",
+    "pytidylib",
+]
+
+[project.optional-dependencies]
+dev = ["uwsgi"]
+
+[tool.setuptools.packages.find]
+include = ["django/archives*"]
-- 
2.43.0

---

From: Célestin Matte <[email-masked]>
Date: Fri, 20 Jun 2025 11:59:56 +0200

Hello,

(Unsure what the beginning of the conversation and the patches are, I don't seem to see them on the list)

> The change in schema.sql to add subscriber_access should surely be a separate commit, it's not part of adding a README file. (in fact, many thing are not adding a README file). So I'll separate thato ne out right away. That said, this one is also caught in the limbo between some things being as migrations and some things not, that reall needs to get cleaned up :/ Celestin worked on that at some point, I think, and I need to get back to properly reviewing that.

Yes, I actually already fixed subsciber_access and it's still waiting for integration, see https://www.postgresql.org/message-id/a99938be-c9ad-8d94-0a16-8305e6391f1e%40cmatte.me (beginning of thread here: https://www.postgresql.org/message-id/[email-masked] )

> I'm not sure how much sense it makes to load data from schema.sql. Wouldn't it be better with a dev_data entr?

I fixed the schema.sql mess here: https://www.postgresql.org/message-id/a31a7071-7338-4423-a8e5-16d70f19c2c8%40cmatte.me (beginning of thread here: https://www.postgresql.org/message-id/12eb75f0-3fc2-14f3-0931-4f29e145f182%40cmatte.me )

> Also, I don't think removing text search configuration completely from a dev install is the right way to go. All of this is definitely part of the system and if nothing else one would want to make…

Not sure if related, but I fixed search web front-end here: https://www.postgresql.org/message-id/[email-masked]

@Jelte: I maintain a version that's integrating my patches that fix the various issues encountered around a new deployment here: https://gitlab.com/cmatte/pgarchives/

I wrote an install procedure (which may not be fully up-to-date anymore) here: https://gitlab.com/cmatte/pglister/-/blob/master/INSTALL.md
and made an ansible script to install the various components here: https://gitlab.com/cmatte/ansible-pglister

Hopefully my patches all get reviewed and integrated at some point. As a reminder, I made a list to simplify that here: https://www.postgresql.org/message-id/[email-masked]

-- 
Célestin Matte

---

From: Célestin Matte <[email-masked]>
Date: Fri, 20 Jun 2025 12:15:56 +0200

> I wrote an install procedure (which may not be fully up-to-date anymore) here: https://gitlab.com/cmatte/pglister/-/blob/master/INSTALL.md

Update: I updated it to take latest changes into account. It should be up-to-date now, although I may still have forgotten something.

-- 
Célestin Matte

---

From: Jelte Fennema-Nio <[email-masked]>
Date: Tue, 2 Dec 2025 00:28:01 +0100

> Done

I had to try something with the archives API again today, and I
realized I still had to apply these patches to get it working locally.
Could these (or something similar) be merged please. Or at least get
some feedback if they are still not good enough.