Timestamp inconsistencies when using PostgreSQL with pgpool

Using Django Using the ORM
1

Hi all,

I want to discuss an issue I found of which I’m not sure if this is intendend (might be very unlikely), a misconfiguration of Django or PostgreSQL/pgpool or something in between.

TL;DR:
In a specific environment, reading a DateTimeField of a model, saving the same time back and reading it again can lead to a different value.

Longer version:
I found this issue when running Django 4.2.7 with psycopg2 2.9.9 against PostgreSQL 16.3 in HA mode with pgpool, but tested it against Django 5.1.1 and psycopg 3.2.1, too.

Short pseudo-code:

c = MyModel.objects.create(timestamp=timezone.now())
old_timestamp = c.timestamp
while True:
   c.refresh_from_db()
   if c.timestamp != old_timestamp:
      raise Exception() # This will raise very likely within the first few iterations.
   c.timestamp = old_timestamp
   c.save(update_fields=["timestamp"])

Django’s configuration defaults to use UTC as the timezone and to be TZ-aware.
The big issue comes from PostgreSQL to be configured to run in GMT+2 (Europe/Berlin).

In this case, Django executes SELECT set_config('TimeZone', 'UTC', false) at the beginning of each session to switch to UTC.
However, as PostgreSQL is configured to run behind pgpool, this setting seems not to be always in effect, e.g. when switching from a reader to a writer node in-between to be able to run the UPDATE query (it’s only my guess).

To work around this problem, I found two solutions:

  • Configure to run PostgreSQL with timezone=UTC
  • Set PGTZ=UTC as a environment variable so that psql/psycopg can use this as the default TZ.

The first option might not be possible for bigger clusters/production environments in which the database serves other projects, too, which might not be TZ-aware.

The second option would be the best way to solve this issue, but I would expect Django to work out of the box even without such a configuration. I did not found any docs/comments on special requirements when using Django with pgpool.

I’ve created a small example project at GitHub - Flauschbaellchen/django-timestamp-inconsistency to reproduce this issue with the minimal needed configuration.
It includes a management command to be able to reproduce it within Django, and a bash script to compare it against a vanilla psql execution.
See the README for further details.

My final question is:
Can it even be solved within the Django framework? Or is this something PostgreSQL needs to tackle in regards to the set_config query?
What would be the best way to solve this issue so that newcomers won’t run into it, too?

With kind regards :slight_smile:

2

Theoretically speaking, you could manage timezone conversions by specifying the timezone for each query instead of depending on session-level configurations, just using Django.