Snehil_Shah@pg_dispatch

v0.1.3•Created 3 days ago•By Snehil_Shah

pg_dispatch

A TLE compliant alternative to pg_later.

An asynchronous task dispatcher for PostgreSQL that helps unblock your main transaction by offloading heavy SQL as deferrable jobs, allowing you to run SQL queries asynchronously.

This is meant to be a TLE compliant alternative to pg_later built on top of pg_cron, which means you can easily use it in sandboxed environments like Supabase and AWS RDS.

Use cases

This extension is particularly useful when writing database-native server-side logic (in something like PL/pgSQL) and wanting to dispatch side-effects asynchronously in a separate transaction.

Say you have an AFTER INSERT trigger on a user profiles table that is called every time a new user hops in by calling an RPC (remote procedure call). You can offload the bulky and asynchronous side-effects (written as PostgreSQL functions), such as sending notifications to other users or updating large tables storing analytics, thereby unblocking your main RPC, for which btw, the client is still waiting for a response from.

Prerequisites

  • PostgreSQL >= v13
  • pg_cron >= v1.5
  • pgcrypto

Installation

Install via database.dev:

SELECT dbdev.install(Snehil_Shah@pg_dispatch);

To learn how to install dbdev and published TLE extensions, read here.

[!WARNING] This extension is installed in the pgdispatch schema and can potentially cause namespace collisions if you already had one before.

You can also manually install the distribution from PGXN here.

Usage

CREATE EXTENSION "Snehil_Shah@pg_dispatch";
<!-- <docs> -->

pgdispatch.fire( command TEXT )

Dispatches an SQL command for asynchronous execution.

SELECT pgdispatch.fire('SELECT pg_sleep(40);');

Parameters

  • command (TEXT) - The SQL statement to dispatch

Returns:

  • VOID

pgdispatch.snooze( command TEXT, delay INTERVAL )

Dispatches a delayed SQL command for asynchronous execution.

SELECT pgdispatch.snooze('SELECT pg_sleep(20);', '20 seconds');

Note: The delay is scheduled asynchronously and will not block your main transaction.

Parameters:

  • command (TEXT) - The SQL statement to dispatch
  • delay (INTERVAL) - How long to delay the execution (truncates to seconds precision)

Returns:

  • VOID
<!-- /<docs> -->

Install

  1. Install the dbdev package manager
  2. Install the package:
select dbdev.install('Snehil_Shah@pg_dispatch');
create extension "Snehil_Shah@pg_dispatch"
    version '0.1.3';

Downloads

  • 13 all time downloads
  • 13 downloads in the last 30 days
  • 13 downloads in the last 90 days
  • 13 downloads in the last 180 days