Request to replace the `not null` constraint on column `created` in `net._http_response` with a check constraint and make it not inheritable

[diraneyya]

This is not a bug but a code-design proposal

I am building a scraper extension on top of pg_net and identified that you can do some really nifty things when you inherit from net._http_response and install triggers to re-route the response to other dedicated scraper buffer tables.

This is made possible with your very flexible design where the net._http_response has on primary key and is intentionally kept quite raw. I like this a lot.

To support my work (and others building on top of pg_net), I am proposing either to remove the not null constraint on the created column altogether or replace it with a check constraint that is not inheritable.

I would like to know how you feel about this.

[diraneyya]

Immediately after posting this. I am not quite sure about it anymore (namely, that it would make a difference for me). So I would like to put it on hold and come back to it at a later point in time if needed.

It would make sense for me to finish the functional scraper and then come back with more solid points.

[steve-chavez]

Interesting use case!

you can do some really nifty things when you inherit from net._http_response and install triggers to re-route the response to other dedicated scraper buffer tables.

So in your trigger you prevent insertions from reaching the net._http_response table? It's always kept empty? Do you clean your other response table manually?

Inheritance is a nice native interface, it synergies with our background worker as its internal queries can keep working with inherited tables (without running into errors like missing columns).

The only drawback I see is that more triggers would impact throughput (#160).

(might not be necessary, but an idea to avoid triggers would be to allow configuring pg_net to use an inherited table instead of net._http_response, we can check this by querying pg_inherits)

to remove the not null constraint on the created column altogether or replace it with a check constraint that is not inheritable.

The problem with removing the NOT NULL on the created column is that we no longer would be capable of automatically cleaning the response table, the pg_net.ttl would not work.

[diraneyya]

Hello @steve-chavez thank you for the reply and for the engagement.

The fetch_cycle_buffer table inherits both from net._http_response and from a continuation abstract type:

-- This table is used as a return data type for the continuation functions
create table if not exists _fetch_cycle_buffer (
  pgnet_id bigint,
  "state" jsonb not null default '{}'::jsonb
  -- abstract parent table 
  constraint abtract_no_data check(false) no inherit
);

comment on table _fetch_cycle_buffer is
    'This abstract table does not accept data, it is used as a return type for the cycle-continuation logic';

create table if not exists fetch_cycle_buffer (
    fetch_id smallserial,
    cycle_type citext null references fetch_cycle_types (id),
    step_counter integer null default 0 check (step_counter is null or step_counter < 100)
) inherits (_fetch_cycle_buffer, net._http_response); -- INHERITS FROM BOTH

The trigger on net._http_response is minimal:

create or replace function before_insert_on_pgnet_reponse()
  returns trigger language plpgsql as $$
declare 
  previous_step fetch_cycle_buffer := null;
begin 
  if tg_op = 'INSERT' 
    and tg_when = 'BEFORE' 
    and tg_level = 'ROW'
    and tg_table_schema = 'net' 
    and tg_table_name = '_http_response' then

    select * from fetch_cycle_buffer where pgnet_id = new.id into previous_step;
    if found then 
      raise notice '>> FETCH CYCLE DETECTED, RE-ROUTING PG_NET#% TO THE FETCH CYCLE BUFFER <<', new.id;
      insert into fetch_cycle_buffer values (new.*);

      -- Cancels the insertion
      return null;
    else 
      return new;
    end if;
  else 
    raise warning '>> !! THE FETCH-CYCLE PGNET TRIGGER IS MISCONFIGURED !! <<';
  end if;
end $$;

[diraneyya]

How it works for a simple repetitive fetch cycle:

call start_fetch_cycle('timeout', jsonb_build_object(
  'delay_ms', 1000,
  'repeat_count', 10,
  'callable_proc_name', 'foo',
  'callable_proc_args', '[]'::jsonb
));

timeout is a simple fetch-cycle type that calls an arbitrary procedure after a certain duration in milliseconds, which can be repeated for any number of times (10 times in the case of the call above).

The fetch in this case is to a sleep endpoint.

For more useful cycle types you can think of fetch cycles that scrape a paginated APIs, collect data in a custom buffer, and flushes them after they are merged into a target table.

Using inheritance, I allow cycle developers to inherit from fetch_cycle_buffer. There are two types of cycle types:

1. Simple cycle types: use the default buffer, which is immutable (no updates allowed, deletions allowed). The steps are deleted immediately when they are continued. This is done in an atomic swap using a before insert trigger on the default buffer table. The insertion of the last step in a cycle is inhibited, so the buffer cleans up itself without any help from the cycle developer.
2. Advanced cycle types: use a custom buffer table that inherits from the default buffer. Using inheritance in postgres, the entries in custom buffers are visible when querying the default buffer table. Steps in custom buffers are only archived by setting their pgnet_id field to NULL but while retaining the rows until they are flushed by the cycle developer. This is necessary for scraping cycles where data must be checked and merged.

I am still actively developing this.

[diraneyya]

This is a diagram of the current design of my fetch-cycle library/concept:

classDiagram
    fetch_cycle_buffer_table_or_derived <|..|> before_insert_on_http_response : queries for matching pgnet_id (sees what is in the default and custom buffer tables, thanks to inheritance)
    fetch_cycle_buffer_table_or_derived <|-- insert_on_fetch_cycle_buffer_or_derived : insert into the right buffer (default or custom) based on the cycle type
    insert_on_fetch_cycle_buffer_or_derived --|> continue_fetch_cycle_specific : chooses the right continuation function based on cycle type
    insert_on_fetch_cycle_buffer_or_derived <|-- before_insert_on_http_response : cancels pg_net insertion, inserts response into the right buffer (can rely on the before-insert trigger on the default buffer for routing support and avoiding code duplication)
    insert_on_fetch_cycle_buffer_or_derived <|-- continue_fetch_cycle_specific : returns continuation data (pgnet_id, state)
    insert_on_fetch_cycle_buffer_or_derived <|-- start_fetch_cycle : inserts into the right buffer based on the cycle type (can rely on the before-insert trigger on the default buffer for routing support and avoiding code duplication)
    fetch_cycle_types <|..|> insert_on_fetch_cycle_buffer_or_derived : looks up cycle type
    fetch_cycle_types <|..|> start_fetch_cycle : looks up cycle type
    fetch_cycle_types <|..|> insert_on_fetch_cycle_buffer_or_derived : checks if cycle uses a custom buffer
    
    class start_fetch_cycle {
      Initiates a new fetch cycle
      initial_state: jsonb ()
      cycle_type: citext ()
    }
    class continue_fetch_cycle_specific {
      Implements continuation logic for different cycle types
      last_step_buffer_row: fetch_cycle_buffer* ()
    }
    class before_insert_on_http_response{
      A response to a previous pgnet request is ready
      NEW: row type of net._http_response ()
    }
    class insert_on_fetch_cycle_buffer_or_derived{
      Does most of the heavy lifting in this system, validations, 
      invocation of continuation logic, routing, and cleanup for 
      simple cycles. Supports installation before inserts or after
      inserts depending on the cycle type.
      NEW: row type of fetch_cycle_buffer* ()
    }
    class fetch_cycle_buffer_table_or_derived {
      The default fetch cycle buffer table or a custom 
      buffer that inherits from it
      pgnet_id: bigint ()
      state: jsonb ()
      cycle_type: citext (references fetch_cycle_types)
      step_counter: integer()
      ... : inherits from net._http_response ()
      ... : more fields can be added in custom buffers ()
    }
    class fetch_cycle_types {
      Registry table for the different cycle type and whether
      they use a custom buffer
      cycle_type: citext (PK)
      use_custom_buffer: boolean ()
    }

Loading

[diraneyya]

This is a good summary of the system by Claude.ai

Fetch Cycle System Summary

The fetch cycle system is a PostgreSQL-based workflow engine built on top of pg_net that orchestrates multi-step HTTP request sequences. It enables complex patterns like paginated API scraping, request chaining, and stateful HTTP workflows.

Core Architecture

The system uses PostgreSQL table inheritance and triggers to intercept HTTP responses from pg_net and route them through a continuation-based workflow engine. When pg_net completes an HTTP request, a trigger on net._http_response checks if the response belongs to an active fetch cycle. If it does, the response is redirected to a buffer table where continuation logic determines the next step.

Key Components

Buffer Tables: The base fetch_cycle_buffer table inherits from net._http_response and stores cycle state. Custom cycle types can create derived buffer tables (e.g., fetch_cycle_buffer_jobsuche) that inherit from the base, adding type-specific fields while remaining visible to parent table queries.

Unified Handler: A single trigger function (fetch_cycle_buffer_insert_handler) handles all cycle types, supporting both BEFORE and AFTER insert triggers. It performs routing between default and custom buffers, manages step counting, invokes continuation functions, and handles cleanup based on retention policies.

Continuation Functions: Each cycle type implements a continue_fetch_cycle_[type] function that receives the complete buffer row (including computed columns for custom buffers) and returns the next step's configuration or signals cycle completion.

Type Registry: The fetch_cycle_types table tracks available cycle types and their configuration, including whether they use custom buffers and their retention policies.

Workflow Execution

1. start_fetch_cycle initiates a cycle by inserting into the buffer with initial state
2. When HTTP responses arrive, the pg_net trigger redirects them to the appropriate buffer
3. The buffer's insert trigger invokes the cycle-specific continuation function
4. The continuation either queues the next request or completes the cycle
5. Previous steps are deleted or archived based on the cycle's retention policy

This architecture achieves extensibility through naming conventions and inheritance rather than complex configuration, allowing new cycle types to be added without modifying core components.

[diraneyya]

https://forgejo.orwa.tech/open-source/pg_fetch_cycle

[diraneyya]

It may seem obvious, but after a lot of head scratching over the weekend over missing data from my fetch-cycle buffers, I realised that because I inherited from net._http_response, the pg_net extension has been cleaning up data from my buffers according to its pg_net.ttl setting.

If extending pg_net through inheriting from net._http_response becomes a useful pattern, then we could consider using the only keyword when deleting from net._http_response, to ensure that extensions remain free to implement their custom retention policy over their own fetch buffers.