### Establish Primary Database Connection in C++

Source: https://context7.com/pgadmin-org/pgagent/llms.txt

This C++ code snippet demonstrates establishing the primary database connection for the pgAgent main loop using DBconn::InitConnection. It also includes a check to confirm the pgagent schema is installed and retrieves the backend PID.

```cpp
// pgAgent.cpp — MainLoop() calls InitConnection once per retry cycle
DBconn *serviceConn = DBconn::InitConnection(connectString);
// connectString is populated by setOptions() from the CLI argument, e.g.:
//   "host=localhost dbname=mydb user=postgres"

if (serviceConn)
{
    // Confirm pgagent schema is present
    DBresultPtr res = serviceConn->Execute(
        "SELECT count(*) AS count, pg_backend_pid() AS pid "
        "  FROM pg_class cl "
        "  JOIN pg_namespace ns ON ns.oid = relnamespace "
        " WHERE relname='pga_job' AND nspname='pgagent'"
    );
    std::string count = res->GetString("count"); // "1" if schema installed
    std::string pid   = res->GetString("pid");   // backend PID for agent registration
}
else
{
    // Connection failed — MainLoop retries up to MAXATTEMPTS (10) times
}
```

--------------------------------

### Build pgAgent with CMake

Source: https://context7.com/pgadmin-org/pgagent/llms.txt

Build pgAgent using CMake. Ensure you have the necessary dependencies like Boost and PostgreSQL installed. The CMAKE_INSTALL_PREFIX and PostgreSQL_CONFIG_EXECUTABLE should be adjusted based on your system setup.

```bash
mkdir build && cd build

cmake /path/to/pgagent \
  -DCMAKE_BUILD_TYPE=Release \
  -DCMAKE_INSTALL_PREFIX=/usr/local \
  -DPostgreSQL_CONFIG_EXECUTABLE=/usr/local/pgsql/bin/pg_config \
  -DPG_EXTENSION=1          # build as a PostgreSQL extension

make
sudo make install

make USE_PGXS=1 -f test/Makefile installcheck
```

--------------------------------

### Windows Build Configuration for pgaevent

Source: https://github.com/pgadmin-org/pgagent/blob/master/pgaevent/CMakeLists.txt

This snippet configures the build for the pgaevent module specifically for Windows. It defines the source files and creates a MODULE library, then installs it.

```cmake
IF(WIN32)
    SET(_srcs pgaevent.c pgaevent.def pgamsgevent.rc)

    ADD_LIBRARY(pgaevent MODULE ${_srcs})

    INSTALL(TARGETS pgaevent DESTINATION .)
ENDIF(WIN32)
```

--------------------------------

### Install pgAgent Database Schema

Source: https://context7.com/pgadmin-org/pgagent/llms.txt

Install the pgAgent schema into your PostgreSQL database. The extension method is recommended for PostgreSQL 9.1 and later. Verify the installation or upgrade an existing schema.

```sql
CREATE EXTENSION pgagent;

\i /usr/local/share/pgagent.sql

SELECT pgagent.pgagent_schema_version();
-- Returns: 4

ALTER EXTENSION "pgagent" UPDATE;
```

--------------------------------

### Compute Next Job Run Time with pgagent.pga_next_schedule

Source: https://context7.com/pgadmin-org/pgagent/llms.txt

Use this function to calculate the next scheduled execution time for a job, considering various scheduling constraints like minutes, hours, weekdays, and specific dates. The function advances through boolean arrays representing these constraints to find the next valid timestamp at or after a given start time.

```sql
SELECT pgagent.pga_next_schedule(
    5,                          -- jscid
    '2024-01-01 00:00:00+00',  -- jscstart
    '2025-12-31 23:59:59+00',  -- jscend
    -- jscminutes: only minute 30
    ARRAY[f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,
          t,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f]::bool[],
    -- jschours: only hour 2 (index 3 = hour 2, 1-based)
    ARRAY[f,f,t,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f]::bool[],
    -- jscweekdays: all days (wildcard — all false = any)
    ARRAY[f,f,f,f,f,f,f]::bool[],
    -- jscmonthdays: all days (wildcard)
    ARRAY[f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f]::bool[],
    -- jscmonths: all months (wildcard)
    ARRAY[f,f,f,f,f,f,f,f,f,f,f,f]::bool[]
);
```

--------------------------------

### Create a pgAgent Job with Steps and Schedule via SQL

Source: https://context7.com/pgadmin-org/pgagent/llms.txt

This SQL script demonstrates the complete process of creating a pgAgent job, including defining its steps (SQL and batch) and establishing a recurring schedule. All job management is performed using standard SQL DML statements. The `jobnextrun` is automatically updated by triggers upon completion of these DML operations.

```sql
BEGIN;

-- 1. Create a job
INSERT INTO pgagent.pga_job (jobjclid, jobname, jobdesc, jobenabled)
VALUES (
    1,                              -- jclid for 'Routine Maintenance'
    'Nightly VACUUM',
    'Runs VACUUM ANALYZE on key tables every night at 01:00',
    TRUE
)
RETURNING jobid;                    -- e.g. returns 3

-- 2. Add a SQL step
INSERT INTO pgagent.pga_jobstep (jstjobid, jstname, jstenabled, jstkind,
                                  jstdbname, jstcode, jstonerror)
VALUES (
    3,                              -- jobid from above
    'Vacuum sales table',
    TRUE,
    's',                            -- 's' = SQL step
    'mydb',                         -- target database name
    'VACUUM ANALYZE sales;',        -- SQL to execute
    'f'                             -- 'f' = fail job on error
);

-- 3. Add a batch step (Unix shell script)
INSERT INTO pgagent.pga_jobstep (jstjobid, jstname, jstenabled, jstkind,
                                  jstdbname, jstconnstr, jstcode, jstonerror)
VALUES (
    3,
    'Archive old logs',
    TRUE,
    'b',                            -- 'b' = batch/shell step
    '',                             -- not used for batch steps
    '',
    '#!/bin/bash
find /var/log/myapp -name "*.log" -mtime +30 -exec gzip {} \;`,
    'i'                             -- 'i' = ignore error and continue
);

-- 4. Create a schedule: run at 01:00 every day
INSERT INTO pgagent.pga_schedule (jscjobid, jscname, jscenabled,
                                   jscstart, jscminutes, jschours,
                                   jscweekdays, jscmonthdays, jscmonths)
VALUES (
    3,
    'Daily at 01:00',
    TRUE,
    now(),
    -- minute 0 only (index 1 = minute 0, 1-based array)
    ARRAY[t,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,
          f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f]::bool[],
    -- hour 1 only (index 2 = hour 1)
    ARRAY[f,t,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f]::bool[],
    ARRAY[f,f,f,f,f,f,f]::bool[],  -- all weekdays (wildcard)
    ARRAY[f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,
          f,f,f,f,f,f,f,f]::bool[], -- all month days (wildcard)
    ARRAY[f,f,f,f,f,f,f,f,f,f,f,f]::bool[] -- all months (wildcard)
);

COMMIT;
-- jobnextrun is now automatically set on pga_job by pga_schedule_trigger + pga_job_trigger
```

--------------------------------

### Step Type Dispatch Logic

Source: https://context7.com/pgadmin-org/pgagent/llms.txt

Illustrates the internal logic for handling different step types (SQL, Batch/Shell) within Job::Execute.

```cpp
// SQL step (jstkind == 's'):
//   Opens a DBconn to jstdbname or jstconnstr, runs jstcode via ExecuteVoid.
//   On error, honours jstonerror: 'f'=fail job, 's'=mark success+continue, 'i'=ignore+continue

// Batch/shell step (jstkind == 'b'):
//   Writes jstcode to a uniquely-named temp file under a 0700 temp directory.
//   On Unix: executes via popen(), captures stdout, reads stderr from a temp file.
//   On Windows: uses win32_popen_r() (ReadFile on an anonymous pipe).
//   Cleans up the temp directory when done.
//   Exit code 0 → succeeded; non-zero → failed.
```

--------------------------------

### Run pgAgent on Unix/Linux/macOS

Source: https://context7.com/pgadmin-org/pgagent/llms.txt

Launch pgAgent from the command line using a libpq connection string. Options can be used to control foreground execution, logging, and polling intervals. The -v flag prints the version and exits.

```bash
# Syntax
pgagent [options] <connect-string>

# Minimal example — connect as user "postgres" to database "mydb" on localhost
pgagent host=localhost dbname=mydb user=postgres

# Run in foreground with verbose debug logging and custom log file
pgagent -f -l 2 -s /var/log/pgagent.log \
  "host=localhost port=5432 dbname=mydb user=postgres password=secret"

# Tune polling and reconnect intervals
pgagent -t 10 -r 30 host=localhost dbname=mydb user=postgres
# -t 10  : poll for new jobs every 10 seconds (default 5)
# -r 30  : retry primary connection every 30 seconds after failure (default 30, min 10)

# Print version and exit
pgagent -v
# Output: PostgreSQL Scheduling Agent
#         Version: 4.2.3
```

--------------------------------

### Execute Job Steps - Job::Execute

Source: https://context7.com/pgadmin-org/pgagent/llms.txt

Runs all enabled steps for a job, updating logs and setting job status. Handles SQL, batch, and shell steps.

```cpp
// job.cpp — JobThread::operator()() creates a Job and calls Execute()
DBconn *threadConn = DBconn::Get();
Job job(threadConn, "17");          // "17" is the jobid as a string

if (job.Runnable())                  // true when constructor claimed the job (status == 'r')
{
    int rc = job.Execute();
    // rc == 0  : all steps succeeded, job status set to 's'
    // rc == -1 : a step failed or internal error; job status set to 'f' or 'i'
}
// ~Job() fires automatically, writing final status and duration to pga_joblog
// and calling threadConn->Return() to release the pooled connection
```

--------------------------------

### Acquire Database Connection - DBconn::Get

Source: https://context7.com/pgadmin-org/pgagent/llms.txt

Reuses an idle connection from the pool or creates a new one. Thread-safe. Can specify database name or connection string.

```cpp
// job.cpp — JobThread::operator()() acquires a pooled connection for the job thread
DBconn *threadConn = DBconn::Get();         // reuse a connection to the primary DB

// For a job step targeting a specific database by name:
DBconn *stepConn = DBconn::Get("", "reporting_db");

// For a job step with its own explicit connection string:
DBconn *stepConn = DBconn::Get(
    "host=replica.example.com dbname=analytics user=reader",
    ""
);

if (stepConn)
{
    int rc = stepConn->ExecuteVoid("VACUUM ANALYZE sales");
    bool ok = stepConn->LastCommandOk(); // true on PGRES_COMMAND_OK / PGRES_TUPLES_OK
    stepConn->Return();                  // resets state and returns connection to pool
}
```

--------------------------------

### Structured Logging with LogMessage

Source: https://context7.com/pgadmin-org/pgagent/llms.txt

Thread-safe logging function that writes timestamped messages. LOG_ERROR calls exit(1), and LOG_STARTUP always logs. Use different log levels for different message severities.

```cpp
// Log levels: LOG_ERROR=0, LOG_WARNING=1, LOG_DEBUG=2, LOG_STARTUP=15

// Simple informational startup message (always printed)
LogMessage("pgAgent starting on host: " + host_name, LOG_STARTUP);

// Debug-only message (only emitted when -l 2)
LogMessage("Checking for jobs to run", LOG_DEBUG);

// Warning — emitted when -l 1 or -l 2
LogMessage(
    "Failed to create connection to 'analytics': " + conn->GetLastError(),
    LOG_WARNING
);

// Fatal error — logs the message then calls exit(1)
LogMessage(
    "Unsupported schema version: 3. Version 4 is required - "
    "please run ALTER EXTENSION \"pgagent\" UPDATE;",
    LOG_ERROR
);
// Sample output (to stdout or -s logfile):
// Wed Jun 12 01:30:05 2024 ERROR: Unsupported schema version: 3. ...

```

--------------------------------

### RAII Mutex Guard with MutexLocker

Source: https://context7.com/pgadmin-org/pgagent/llms.txt

RAII wrapper for boost::mutex to protect shared resources like the connection pool and log file. Supports explicit release before exit() to avoid assertion failures.

```cpp
static boost::mutex s_poolLock;

{
    MutexLocker locker(&s_poolLock);
    // critical section: safe to read/write the connection pool
    DBconn *conn = ms_primaryConn;
    // ...
}   // locker destructs here, releasing s_poolLock

// Explicit early release before exit() (see LogMessage LOG_ERROR handling):
MutexLocker locker(&s_poolLock);
// ... do work ...
locker = (boost::mutex *)NULL;   // unlocks immediately
exit(1);

```

--------------------------------

### Execute SQL Query - DBconn::Execute

Source: https://context7.com/pgadmin-org/pgagent/llms.txt

Executes a SQL query and returns a DBresult for row iteration. Use ExecuteScalar for a single value or ExecuteVoid for DML/DDL.

```cpp
// Execute a query and iterate over result rows
DBresultPtr res = conn->Execute(
    "SELECT jobid FROM pgagent.pga_job "
    " WHERE jobenabled AND jobagentid IS NULL AND jobnextrun <= now() "
    " ORDER BY jobnextrun"
);
if (res)
{
    while (res->HasData())
    {
        std::string jobid = res->GetString("jobid");  // by column name
        // or: res->GetString(0);                      // by column index
        // ... spawn job thread ...
        res->MoveNext();
    }
}
```

```cpp
// ExecuteScalar — fetch a single value
std::string schemaVer = conn->ExecuteScalar(
    "SELECT pgagent.pgagent_schema_version()"
);
// schemaVer == "4"
```

```cpp
// ExecuteVoid — DML / DDL where rows affected matter
int affected = conn->ExecuteVoid(
    "UPDATE pgagent.pga_job SET jobagentid=NULL, jobnextrun=NULL WHERE jobid=42"
);
// affected == 1 on success, -1 on error
```

--------------------------------

### Query pgAgent Job Status and History

Source: https://context7.com/pgadmin-org/pgagent/llms.txt

SQL queries to check job status, next run times, and view recent job run history with status and duration. Also includes queries for step-level output and active agents.

```sql
-- Check all enabled jobs and their next scheduled run
SELECT jobid, jobname, jobenabled, jobnextrun, joblastrun
  FROM pgagent.pga_job
 ORDER BY jobnextrun NULLS LAST;

```

```sql
-- View recent job run history with duration and outcome
SELECT j.jobname,
       l.jlgstart,
       l.jlgduration,
       CASE l.jlgstatus
           WHEN 'r' THEN 'Running'
           WHEN 's' THEN 'Success'
           WHEN 'f' THEN 'Failed'
           WHEN 'i' THEN 'No steps / internal error'
           WHEN 'd' THEN 'Aborted (zombie cleanup)'
       END AS status
  FROM pgagent.pga_joblog l
  JOIN pgagent.pga_job j ON j.jobid = l.jlgjobid
 ORDER BY l.jlgstart DESC
LIMIT 50;

```

```sql
-- View step-level output for a specific job run (jlgid=17)
SELECT s.jstname,
       sl.jslstart,
       sl.jslduration,
       sl.jslresult   AS exit_code,
       sl.jslstatus,
       sl.jsloutput
  FROM pgagent.pga_jobsteplog sl
  JOIN pgagent.pga_jobstep s ON s.jstid = sl.jsljstid
 WHERE sl.jsljlgid = 17
 ORDER BY sl.jslstart;

```

```sql
-- List currently active agents
SELECT jagpid, jagstation, jaglogintime
  FROM pgagent.pga_jobagent;

```

--------------------------------

### Auto-Update jobnextrun with pgagent Triggers

Source: https://context7.com/pgadmin-org/pgagent/llms.txt

These trigger functions automatically recalculate the `jobnextrun` field in `pga_job` when job, schedule, or exception data changes. This ensures the `jobnextrun` is always accurate without manual intervention. The `pga_job_trigger` relies on `pga_next_schedule` for recalculation.

```sql
-- The trigger fires automatically; this shows the underlying pattern it uses:
UPDATE pgagent.pga_job
   SET jobnextrun = NULL         -- triggers pga_job_trigger BEFORE UPDATE
 WHERE jobenabled AND jobid = 42;

-- pga_job_trigger then populates jobnextrun:
-- NEW.jobnextrun := MIN(pga_next_schedule(...)) FROM pga_schedule WHERE jscenabled AND jscjobid=42

-- Add a schedule exception to skip a specific run:
INSERT INTO pgagent.pga_exception (jexscid, jexdate, jextime)
VALUES (
    7,                     -- schedule ID
    '2024-12-25',          -- skip on Christmas
    '02:30:00'             -- at this specific time (NULL = skip all day)
);
-- pga_exception_trigger fires, setting jobnextrun=NULL on the parent job,
-- and pga_job_trigger recalculates, skipping 2024-12-25 02:30.
```

--------------------------------

### Drain Connection Pool - DBconn::ClearConnections

Source: https://context7.com/pgadmin-org/pgagent/llms.txt

Closes idle connections in the pool. When called with `true`, also closes the primary connection.

```cpp
// Release only idle (unused) connections — called at end of each poll cycle
// when no runnable jobs were found
DBconn::ClearConnections();          // all=false (default)

// Release ALL connections including primary — called before a full reconnect
DBconn::ClearConnections(true);
// Log output at LOG_DEBUG:
//   "Connection stats: total - 5, free - 4, deleted - 4"
```

=== COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.