Your PostgreSQL Row Is New. Why Is Its Deadline Already Past?

A newly inserted ticket row appears immediately expired: its expires_at timestamp is already earlier than the current time. The practical question is, which event defined its deadline? In other words, when did we start counting that one-second validity period? Did the deadline originate at transaction start, at the statement execute time, or at a sampled wall-clock instant? The row stores an issued_at timestamp and a one-second duration to expiration, but depending on when issued_at was evaluated, the remaining validity can vary wildly.

For clarity, define:

• stored_interval = expires_at - issued_at (always 1s by our table’s CHECK), and

• remaining_at_observation = expires_at - observed_at, where observed_at is the clock when we measure it.

A correct 1-second stored_interval does not guarantee 1 second remains later. If the origin event happened earlier than expected, the row may already be expired. We will test three ways to compute issued_at: transaction-origin (using CURRENT_TIMESTAMP/now() at BEGIN) vs statement-origin (using statement_timestamp() at the INSERT) vs wall-origin (sampling clock_timestamp() at insertion). Each represents a different “issue event.” We’ll also do a post-commit read to see how much time was left.

Define the event from which the deadline should run

When inserting a new ticket with a 1-second lifetime, the event that “issued” the ticket should match the SQL expression for issued_at. Consider this table schema snippet:

CREATE TABLE tickets (
  case_id text PRIMARY KEY,
  origin_kind text NOT NULL,
  issued_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at timestamptz NOT NULL DEFAULT (CURRENT_TIMESTAMP + INTERVAL '1 second'),
  CHECK (expires_at = issued_at + INTERVAL '1 second')
);

This stores issued_at and enforces a one-second expiry. However, the actual remaining validity (expires_at - now()) depends on when issued_at was taken. The PostgreSQL current-time documentation explains that CURRENT_TIMESTAMP returns the transaction start time, while statement_timestamp() returns the start time of the current statement. If we delay two seconds after BEGIN, these choices produce very different deadlines. We compare three candidate origins for issued_at:

• Transaction start (BEGIN): using CURRENT_TIMESTAMP or now(). Anchors the deadline at the transaction’s start time. If a delay occurs, the deadline may immediately be in the past.

• Statement receipt: using statement_timestamp(). Anchors at the INSERT command time. A prior delay does not eat into the new 1s interval.

• Wall-clock sample: explicitly capturing clock_timestamp() in the INSERT. Anchors exactly at that sampled moment (after our sleep).

Each expression is valid SQL, but only one, if any, will match the business contract. For comparison, Refonte's queue claim and fairness validation covers SKIP LOCKED queues; this article focuses only on timestamp origin in the write path.

Separate stored lifetime from remaining lifetime

We clarify two concepts. The stored_interval (expires_at - issued_at) is fixed at 1s by the table’s CHECK. That just means we asked PostgreSQL to store a one-second span. But once time passes, the remaining_at_observation (expires_at - observed_at) shrinks. For example, if issued_at was 2 seconds ago with a 1s interval, the row is already expired (remaining_at_observation = -1s). We will compute remaining_at_observation at two points: immediately in the INSERT’s RETURNING output, and again after commit with a fresh clock sample. This separates what was stored from what is left when we look at it.

Prepare an isolated database and preserve command boundaries

Use PostgreSQL 18 as the documentation baseline and run the lab in UTC. Record psql --version and the server build from version() for traceability. All timestamp fields use timestamptz, and the session is set to UTC for display, consistent with the PostgreSQL time-zone documentation. Create a fresh disposable database and schema; any existing data is an error because it can mix runs. This is a planned, controlled laboratory, not a measured production incident, so every expected result must be reproduced in the stated environment.

Use explicit BEGIN and COMMIT blocks for the multi-step cases. The PostgreSQL transaction semantics explain both the transaction boundary and why incomplete intermediate states are not visible to concurrent transactions. PostgreSQL otherwise treats each standalone statement as its own transaction, but this lab needs deliberate control of transaction start time.

Crucially, send each SQL statement as a separate client request. The psql command-dispatch rules state that psql normally sends an SQL command as soon as it reaches a command-ending semicolon, including while processing a file with -f. The PostgreSQL simple-query protocol treats all commands in one Query message as one request. In this script, SELECT pg_sleep(2); and the following INSERT therefore arrive as separate messages, so statement_timestamp() is refreshed for the INSERT. By contrast, several commands deliberately combined in one psql -c string share a single request. This controlled dispatch makes the statement timestamp refer to the event we intend.

These preparation steps reflect standard DBA discipline (see Refonte’s discussion of database transaction and performance responsibilities).

Establish the three-clock baseline inside one transaction

Case 1: clock_baseline. As a control, we check how the clocks behave in one transaction. We run:

BEGIN;
SELECT 'before_delay' AS phase,
       transaction_timestamp() AS txn_at,
       statement_timestamp() AS stmt_at,
       clock_timestamp() AS clk_at;
SELECT pg_sleep(2);
SELECT 'after_delay' AS phase,
       transaction_timestamp() AS txn_at,
       statement_timestamp() AS stmt_at,
       clock_timestamp() AS clk_at;
COMMIT;

Label the two phases "before_delay" and "after_delay". The PostgreSQL current-time documentation defines transaction_timestamp() as equivalent to CURRENT_TIMESTAMP and fixed at transaction start, so both txn_at values should equal T0. statement_timestamp() should reflect each SELECT request, so the second stmt_at should be approximately T0 + 2s. clock_timestamp() reports actual wall time and should advance similarly. Verify that transaction time stayed fixed and the other clocks advanced by roughly two seconds, allowing small scheduling jitter. An unexpected wall-clock anomaly puts the run in HOLD.

This baseline is intended to confirm the documented behavior: transaction time is fixed, statement time and wall time advance.

Treat the five-case ledger as the coverage oracle

We plan five “cases”: four INSERTs (cases 2 through 5) plus the two baseline SELECTs. We will verify each inserted row by its case_id. The final SQL includes checks for 4 rows with the exact IDs:

statement_origin_delayed, transaction_origin_delayed,
wall_origin_delayed, wall_origin_delayed_commit

(each must appear exactly once) and that every expires_at - issued_at = 1s. We will not rely solely on row count; the identity of each row matters. The table below outlines each case and what we will check:

For each INSERT, compare issued_at with the intended event time and compute remaining time during RETURNING. Then perform a post-commit observation; the final case also adds a deliberate pre-commit delay.

Reproduce a deadline anchored to the earlier BEGIN

Case 2: transaction_origin_delayed.

BEGIN;
SELECT pg_sleep(2);
WITH written AS (
  INSERT INTO tickets (case_id, origin_kind)
  VALUES ('transaction_origin_delayed', 'transaction')
  RETURNING ,
    transaction_timestamp() AS transaction_at_write,
    statement_timestamp() AS command_at_write,
    clock_timestamp() AS observed_at
)
SELECT 'returning' AS phase,
       ,
       expires_at - observed_at AS remaining
FROM written;
COMMIT;

WITH observed AS MATERIALIZED (SELECT clock_timestamp() AS observed_at)
SELECT 'post_commit' AS phase, t.*, o.observed_at,
       t.expires_at - o.observed_at AS remaining
FROM tickets AS t CROSS JOIN observed AS o
WHERE t.case_id = 'transaction_origin_delayed';

This case relies on the default CURRENT_TIMESTAMP for issued_at. The PostgreSQL current-time documentation defines that value as the transaction start time, which is two seconds before the INSERT in this schedule. In the RETURNING output, transaction_at_write should equal the stored issued_at. The calculated remaining should be roughly negative one second. After COMMIT, the post-commit query takes a fresh wall-clock sample and should still show an expired row. A matching run would demonstrate that a deadline anchored at BEGIN can already be past when the row is inserted. The PostgreSQL RETURNING documentation establishes what values the write returned; it does not promise that any validity remains.

Move the origin to the new command message

Case 3: statement_origin_delayed.

BEGIN;
SELECT pg_sleep(2);
WITH written AS (
  INSERT INTO tickets
    (case_id, origin_kind, issued_at, expires_at)
  VALUES (
    'statement_origin_delayed', 'statement',
    statement_timestamp(),
    statement_timestamp() + INTERVAL '1 second'
  )
  RETURNING ,
    transaction_timestamp() AS transaction_at_write,
    statement_timestamp() AS command_at_write,
    clock_timestamp() AS observed_at
)
SELECT 'returning' AS phase,
       ,
       expires_at - observed_at AS remaining
FROM written;
COMMIT;

WITH observed AS MATERIALIZED (SELECT clock_timestamp() AS observed_at)
SELECT 'post_commit' AS phase, t.*, o.observed_at,
       t.expires_at - o.observed_at AS remaining
FROM tickets AS t CROSS JOIN observed AS o
WHERE t.case_id = 'statement_origin_delayed';

This case explicitly sets issued_at = statement_timestamp(). It anchors the deadline when PostgreSQL receives the INSERT command after the two-second sleep. In the RETURNING output, command_at_write should equal the stored issued_at. The row should initially retain close to one second, minus execution overhead. After COMMIT and a fresh observation, the remaining interval may be smaller and can cross zero if commit or client latency consumes enough time. The PostgreSQL current-time documentation ties statement_timestamp() to the latest command message, so a different batching strategy requires new evidence.

Keep statement_timestamp tied to the submission protocol

The statement_timestamp() value changes with each separately dispatched client command. Under the documented psql semicolon-dispatch behavior, the -f script sends each completed SQL command separately. A different client, protocol mode, escaped semicolon, or batching strategy can change that boundary and must be requalified.

Use one wall-clock sample for both stored fields

Case 4: wall_origin_delayed.

BEGIN;
SELECT pg_sleep(2);
WITH stamp AS MATERIALIZED (
       SELECT clock_timestamp() AS origin_at
     ),
     written AS (
       INSERT INTO tickets (case_id, origin_kind, issued_at, expires_at)
       SELECT 'wall_origin_delayed',
              'wall',
              origin_at,
              origin_at + INTERVAL '1 second'
       FROM stamp
       RETURNING ,
         transaction_timestamp() AS transaction_at_write,
         statement_timestamp() AS command_at_write,
         clock_timestamp() AS observed_at
     )
SELECT 'returning' AS phase,
       s.origin_at,
       w.,
       w.expires_at - w.observed_at AS remaining
FROM written AS w
CROSS JOIN stamp AS s;
COMMIT;

WITH observed AS MATERIALIZED (SELECT clock_timestamp() AS observed_at)
SELECT 'post_commit' AS phase, t.*, o.observed_at,
       t.expires_at - o.observed_at AS remaining
FROM tickets AS t CROSS JOIN observed AS o
WHERE t.case_id = 'wall_origin_delayed';

This case samples clock_timestamp() once as origin_at. Both stored fields derive from that one value. After the two-second delay, origin_at is effectively the current wall time; expires_at remains exactly one second after issued_at. The output includes the sampled origin and the later observation so the equality can be checked directly. The PostgreSQL CTE materialization rules explain how MATERIALIZED forces separate calculation and prevents unwanted repeated evaluation.

Do not turn a newer sample into a commit guarantee

Even though the origin is late, the deadline still counts down through the INSERT and COMMIT. Sampling origin_at later helps, but doesn’t buy extra time after the SQL ends. For instance, if triggers or network latency occur, the deadline can still expire in that gap. The next case will illustrate consuming that remaining time.

Delay COMMIT and measure what remains afterward

Case 5: wall_origin_delayed_commit.

BEGIN;
SELECT pg_sleep(2);
WITH stamp AS MATERIALIZED (
       SELECT clock_timestamp() AS origin_at
     ),
     written AS (
       INSERT INTO tickets (case_id, origin_kind, issued_at, expires_at)
       SELECT 'wall_origin_delayed_commit',
              'wall',
              origin_at,
              origin_at + INTERVAL '1 second'
       FROM stamp
       RETURNING ,
         transaction_timestamp() AS transaction_at_write,
         statement_timestamp() AS command_at_write,
         clock_timestamp() AS observed_at
     )
SELECT 'returning' AS phase,
       s.origin_at,
       w.,
       w.expires_at - w.observed_at AS remaining
FROM written AS w
CROSS JOIN stamp AS s;
SELECT pg_sleep(2);
COMMIT;

WITH observed AS MATERIALIZED (SELECT clock_timestamp() AS observed_at)
SELECT 'post_commit' AS phase, t.*, o.observed_at,
       t.expires_at - o.observed_at AS remaining
FROM tickets AS t CROSS JOIN observed AS o
WHERE t.case_id = 'wall_origin_delayed_commit';

This is the same origin as Case 4, but we now add a 2-second sleep after the INSERT and before COMMIT. In RETURNING (phase "returning"), we see how much remained just after insert. After the extra sleep and COMMIT, the final SELECT (phase "post_commit") shows remaining. On a stable clock, the 1-second deadline will have passed during the extra sleep, so post-commit remaining should be negative. This case is expected to confirm that even a “wall” origin does not protect against delays after insertion.

Read RETURNING and COMMIT as different evidence

The PostgreSQL RETURNING documentation explains that RETURNING reports values from the modified row, which supplies write-time evidence for the stored timestamps. A successful COMMIT persists the row but does not extend its deadline. The post-commit query takes a fresh clock_timestamp() sample and calculates expires_at - observed_at. The lab observes only its own session and does not claim an exact external visibility instant; the transaction visibility rules establish only that incomplete intermediate states are not visible to concurrent transactions.

Reconcile every origin, interval and observation

After execution, the evidence ledger should contain every required value. For each case ID and phase, record issued_at, expires_at, transaction_at_write, command_at_write, observed_at, the sampled origin_at where applicable, and the computed remaining. Also record the run ID, SQL hash, server and client versions, time zone, and submission method.

Check the structural results: 4 rows inserted with the exact case IDs above, and each expires_at - issued_at = 1s. Then verify each origin match:

• transaction_origin_delayed: confirm issued_at = transaction_at_write.

• statement_origin_delayed: confirm issued_at = command_at_write.

• wall_origin_delayed(_commit): confirm issued_at equals the sampled origin_at.

Also inspect remaining. Negative means expired, positive means not yet. If all observed data align with the chosen origin contract, we ACCEPT that origin. If any check fails (e.g. origin mismatch or unexpected expiration), we do not auto-approve. Missing values or errors put the run in HOLD. We do not alter timestamps post-hoc to “fix” mismatches. Each case is evidence: if a case fails the intended comparison, we must investigate or rerun.

Recover without inventing new historical deadlines

If the tested origin expression does not match the contract, stop or isolate that write path and collect evidence for troubleshooting. Do not silently update old rows. In our disposable test, we simply halt. In a live system, you might disable the feature or revert code. Important: do not retroactively change issued_at on rows already written. Deciding how to handle previously expired rows is a business decision (out of scope here). This is different from TTL cleanup at read-time (as in the MongoDB TTL example); here we are dealing with writing deadlines, not how to filter them on read.

Roll out the chosen expression with a reversible decision

Once the correct origin is agreed, update only the specific SQL in your application (keeping the old version comment-out or in version control). Document the change, including rollback criteria (e.g. “if > X% of deadlines still expire unexpectedly, revert”). Monitor live metrics on issuance-to-expiry times to validate the fix. Do not broaden the fix: only adjust the relevant issued_at assignment. Rolling back code only affects future inserts; it cannot recover already-written rows. And remember: passing INSERTs alone are not proof of deadline correctness. Always check the whole pipeline.

Choose an origin from an explicit decision matrix

We summarize the options:

• Transaction-origin: CURRENT_TIMESTAMP or transaction_timestamp(). Anchors at BEGIN. Evidence: issued_at matches transaction_timestamp. Pros: simple default now. Cons: loses any time before the INSERT.

• Statement-origin: statement_timestamp(). Anchors at the INSERT. Evidence: issued_at matches that statement’s timestamp. Pros: gives full time from insert. Cons: still no extension beyond commit.

• Wall-origin: a clock_timestamp() sample. Anchors at an exact clock moment. Evidence: issued_at equals the sampled time. Pros: as late as possible. Cons: still doesn’t guarantee post-commit time.

• Post-commit anchor: (not achievable with built-in functions). If you needed “full second after commit”, that requires external logic.

We ACCEPT only when evidence matches the contract. Otherwise we REPAIR or HOLD. Each failed case must be investigated; do not edit results to make them pass. If an experiment or environment change is needed (e.g. test a clock anomaly or client mode), rerun it. No test should be silently reinterpreted as success.

Require evidence at the event named by the contract

In short, if the contract’s event is X, then the recorded issued_at must demonstrably come from X. For example, if you say “deadline starts on INSERT,” then the INSERT’s RETURNING row must show that. If not, do not accept the row. Gather direct evidence, and only accept an origin when tests confirm it.

Assign ownership of the time contract and its evidence

The application/product owner defines when deadlines start and how long they should last. The database/storage owner verifies that the SQL and transactions implement that intent correctly. QA/audit keeps exact case logs to prove compliance. Operations/SRE watches for clock anomalies and deadline misses in production. All parties should agree on a single source of truth. This kind of precise SQL reasoning is exactly the skill set of a modern DBA. For broader context on these responsibilities, see Refonte’s article on practical database administration skills.

Build the SQL reasoning behind dependable deadline decisions

In summary, the documented clock semantics and the planned lab distinguish transaction-, statement-, and wall-origin deadlines. The evidence determines whether to ACCEPT the existing origin, REPAIR the SQL expression, or HOLD for more investigation. A successful INSERT alone is not enough to satisfy a deadline contract; the timestamps and remaining interval must align with the named event. Keep the SQL change reversible and monitor actual issuance-to-expiry intervals after rollout.

To build deeper SQL and administration skills, explore Refonte Learning's Database Administrator Essentials program. The three-month program requires approximately 12 to 14 hours per week and covers database design and architecture, SQL query optimization, backup and recovery, performance tuning, security practices, cloud database management, data migration, disaster recovery, access control, and monitoring. It includes hands-on projects, mentorship from experts such as Dr. Helena Ferreira, a Training Certificate, and a Certificate of Internship.