How do I query the local PostgreSQL JobQueue tables directly?

In Spirion Sensitive Data Platform (SDP) v13.6+, the agent-side queuing architecture stores all scan tasks and results locally in PostgreSQL.

Because each Queue is a SQL table, each Item is a row, and Progress is a status transition, you can query the database directly to inspect live scan progress, diagnose stuck workers, or troubleshoot shipper backlogs.

1. Connection Parameters & Credentials

The local PostgreSQL instance is isolated and runs directly on the Agent host:

• Host: 127.0.0.1 / localhost
• Default Ports:
• • PostgreSQL (Direct): 5433 (used to avoid conflict with standard 5432)
  • pgBouncer (Pooled): 6433 (used to avoid conflict with standard 6432)
• Connection Strings & Port Verification:
• • Windows Registry: HKLM\SOFTWARE\WOW6432Node\Identity Finder\Postgres (contains Port, PgbouncerPort, and username configs).
  • Linux / macOS: /etc/pgbouncer/pgbouncer.ini or /opt/identityfinder/config/agent.conf.
• CLI Connection via psql:

  psql -h 127.0.0.1 -p 5433 -U <PostgresUser> -d <DatabaseName>

2. Core Queue Tables & Status Codes

Status Column Values (status smallint):

• 0 = Pending (Waiting to be claimed)
• 1 = Processing (Claimed and actively being scanned)
• 2 = Processed (Completed successfully)
• 3 = Failed (Encountered an error; eligible for retry)
• 4 = PermFailed (Exhausted retry budget; permanently failed)

3. Essential Troubleshooting SQL Queries

A. Check Overall Job Distribution for an Active Scan

To see how many items are waiting, actively scanning, completed, or failed run the following commands:

SELECT 
    status,
    CASE status
        WHEN 0 THEN '0 - Pending (Waiting)'
        WHEN 1 THEN '1 - Processing (Scanning)'
        WHEN 2 THEN '2 - Processed (Complete)'
        WHEN 3 THEN '3 - Failed (Retrying)'
        WHEN 4 THEN '4 - PermFailed (Exhausted)'
    END AS status_name,
    COUNT(*) AS total_items
FROM "search_queue_<scanId>"
GROUP BY status
ORDER BY status;

B. Identify Stuck/Orphaned Jobs in Processing

If a scan appears frozen, check which workers own the in-flight tasks and when they were last updated using the following commands:

SELECT 
    id, 
    endpointid AS owning_agent_id, 
    retry_count, 
    created, 
    updated, 
    payload
FROM "search_queue_<scanId>"
WHERE status = 1
ORDER BY updated ASC
LIMIT 25;

(If rows remain in Processing with stale updated timestamps $> 1\text{ minute}$, the EPS background maintenance worker will automatically return them to Pending for another worker to claim).

C. Check Agent Liveness & Heartbeats

Verify that search workers are actively communicating with the queue host using the following commands:

SELECT 
    id AS endpoint_id, 
    updated AS last_heartbeat, 
    NOW() - updated AS time_since_heartbeat
FROM heartbeats
ORDER BY updated DESC;

D. Check the Shipper Queue Backlog (Outbound Results)

Confirm whether match results are staged locally or backing up due to network/Ingress issues using the following commands:

SELECT 
    status,
    CASE status
        WHEN 0 THEN '0 - Pending (Waiting to Ship)'
        WHEN 1 THEN '1 - Processing (Shipping to Ingress)'
        WHEN 2 THEN '2 - Processed (Shipped OK)'
        WHEN 3 THEN '3 - Failed (Ingress Unreachable)'
        WHEN 4 THEN '4 - PermFailed'
    END AS shipper_status,
    COUNT(*) AS result_batches
FROM shipper_queue
GROUP BY status
ORDER BY status;

E. Inspect Failed Items & Error Reasons

Review jobs that failed inspection to identify problematic file paths or permission locks using the following commands:

SELECT 
    id, 
    endpointid, 
    retry_count, 
    updated, 
    payload
FROM "search_queue_<scanId>"
WHERE status IN (3, 4)
ORDER BY updated DESC;