PostgreSQL: the envelope tables' timestamp default depends on the session's time zone, so InboxStaleTime gives back envelopes that are still running (6.41.0) #4663

What happens

With InboxStaleTime (or OutboxStaleTime) set, the PostgreSQL store adds a timestamp column declared timestamptz with the default (now() at time zone 'utc') (IncomingEnvelopeTable.cs:36, OutgoingEnvelopeTable.cs:25). now() at time zone 'utc' is a timestamp without time zone holding the UTC wall clock, and PostgreSQL reads it back into the timestamptz column in the zone of the session that inserts:

BumpStaleIncomingEnvelopesOperation compares the column with a true UTC cutoff. Through Wolverine, with InboxStaleTime at one hour, a handler still running and the database's sessions in Pacific/Kiritimati (set with ALTER DATABASE … SET timezone, as a server installed in that zone would have it), the incoming row's timestamp was 14.00 hours behind the server's now(), and the handler ran a second time 21.8 s after the first started, while the first was still running. With the sessions in UTC, the row was 0.00 hours off and the handler ran once in 30 s.

So a session ahead of UTC gives back envelopes that are still running, and one behind UTC makes a stranded envelope wait that many hours longer. The official images run in UTC, so tests do not see it; a server installed in the zone of its machine does, because a new session's zone is the server's timezone unless someone sets it.

It is the shape the Oracle store had until #2634, which replaced its SYS_EXTRACT_UTC(SYSTIMESTAMP) defaults - a timestamp without a time zone going into a TIMESTAMP WITH TIME ZONE column - with SYSTIMESTAMP AT TIME ZONE 'UTC'. The PostgreSQL claim-check store already works around the same default (PostgresqlClaimCheckStore.cs:90-95, whose comment says "on any non-UTC session the stored value is skewed by the offset", for the expiration sweep of GH-3509), and the comment in MessageDatabase.Admin.cs:48 says the column is written in UTC, which holds only for a session in UTC. The same expression is the default of the transport's queue and scheduled tables (QueueTable.cs:17, ScheduledMessageTable.cs:18), of the table in PostgresqlMessageStore.cs:284, and the cutoff in PostgresqlQueueListener.cs:163; we did not measure those.

Measured with 6.41.0 on .NET 10.0.12 and postgres:18-alpine.

What we expected

now() (or CURRENT_TIMESTAMP) as the default: a timestamptz is the instant whatever the session's zone.

In the meantime our migration step sets ALTER TABLE … ALTER COLUMN "timestamp" SET DEFAULT now() after the library's migration, on the main store and on every tenant's. The library's next migration keeps a default it did not write, which we measured, and a column it has to add again comes back with its own.

Reproducer

Scenario D of the project in #4662, which runs the same scenario with the sessions in Pacific/Kiritimati and, as the control, in UTC. Its output on 6.41.0:

D. InboxStaleTime = 1 hour, a handler still running, and the database's sessions in Pacific/Kiritimati (UTC+14)
  the incoming row's timestamp:     -14.00 h from the server's now()
  runs of the handler while the first was still running: 2, the second 21.8 s after the first started

D, control. The same with the sessions in UTC
  the incoming row's timestamp:     0.00 h from the server's now()
  runs of the handler while the first was still running: 1, watched for 30 s

And the column on its own, with the library's declaration as it is, in psql:

-- The column as Wolverine.Postgresql declares it when InboxStaleTime is set.
DROP TABLE IF EXISTS tz_probe;
CREATE TABLE tz_probe (id int, "timestamp" timestamp with time zone DEFAULT (now() at time zone 'utc'));

SET timezone TO 'UTC';
INSERT INTO tz_probe (id) VALUES (1);

SET timezone TO 'Pacific/Kiritimati';
INSERT INTO tz_probe (id) VALUES (2);

SET timezone TO 'America/Santiago';
INSERT INTO tz_probe (id) VALUES (3);

SET timezone TO 'UTC';
SELECT id, "timestamp", now() AS server_now,
       round(extract(epoch FROM ("timestamp" - now())) / 3600.0, 2) AS hours_off
FROM tz_probe ORDER BY id;

 id |           timestamp           |          server_now           | hours_off
----+-------------------------------+-------------------------------+-----------
  1 | 2026-09-28 11:42:08.985805+00 | 2026-09-28 11:42:08.989942+00 |      0.00
  2 | 2026-09-27 21:42:08.987648+00 | 2026-09-28 11:42:08.989942+00 |    -14.00
  3 | 2026-09-28 14:42:08.988962+00 | 2026-09-28 11:42:08.989942+00 |      3.00