Skip to content
ParticleDB
Release candidateParticleDB 1.1.19-rc.17 is not yet GA or ready for production data.Release status →

Write Python Particles

Start with the requirements, the complete function and the loading instructions in your first Particle. Every example on this page loads with --python-functions on the standard free-edition server.

Annotate each argument and the return value. str, int, float and bool map to SQL text, integer, double precision and boolean. Save this as kinds.py:

from particledb import scalar, aggregate, window
@scalar
def shout(value: str) -> str:
return value.upper() + "!"
@scalar
def add_one(n: int) -> int:
return n + 1
@scalar
def is_even(n: int) -> bool:
return n % 2 == 0
@scalar(called_on_null=True)
def label_or_missing(value: str) -> str:
return "missing" if value is None else value
@aggregate
def spread(values: list[float]) -> float:
return max(values) - min(values)
@window
def running_total(values: list[float]) -> list[float]:
totals, total = [], 0.0
for value in values:
total += value
totals.append(total)
return totals
@scalar
def checked_ratio(n: int) -> int:
raise ValueError("ratio input rejected")

Load it beside tools.py by repeating the flag: --python-functions ./tools.py --python-functions ./kinds.py.

SELECT shout('hi') AS shouted, add_one(41) AS next, is_even(4) AS even;

The row is HI!, 42 and true. Keep each function name distinct from SQL built-ins; see names and errors.

By default a scalar function is strict: it returns SQL NULL without calling Python when an input is NULL.

SELECT priority(NULL, 0.8) AS score;

With called_on_null=True, Python receives None instead:

SELECT label_or_missing(NULL) AS label;

The result is missing.

An @aggregate function receives the values of one group as a list and returns one value. A @window function receives the partition’s values in the window’s ORDER BY order and returns a list of the same length.

CREATE TABLE particle_readings (sensor int, reading float8, seq int);
INSERT INTO particle_readings VALUES (1, 1.0, 1), (1, 4.0, 2), (2, 2.0, 1), (2, 7.5, 2), (2, 3.0, 3);
SELECT sensor, spread(reading) AS spread FROM particle_readings GROUP BY sensor ORDER BY sensor;
SELECT sensor, seq, running_total(reading) OVER (PARTITION BY sensor ORDER BY seq) AS total
FROM particle_readings ORDER BY sensor, seq;

The spreads are 3 and 5.5; the running totals are 1, 5 for sensor 1 and 2, 9.5, 12.5 for sensor 2.

An exception raised by the function fails the statement with SQLSTATE 38000, and the message names the function and the exception:

SELECT checked_ratio(1);

Inside a transaction, the failure aborts the transaction; roll back before reusing the connection.

Each call has a time budget: 10,000 ms by default, set server-wide with --particles-call-timeout-ms or per function with @scalar(timeout_ms=500). A call that exceeds it fails with SQLSTATE 57014 and its worker process is stopped. --particles-memory-limit-mb (default 1024) caps each worker; a call past it fails with 53200. --particles-workers sets the maximum number of worker processes.

Install the packages your module imports into the same interpreter the server runs. A module whose import fails stops the server at startup with the Python error. Test with the same interpreter and dependency versions used in deployment. Keep secrets out of the module and review runtime and trust.

Loading · Testing

Documentation home · Troubleshooting