Postgres queue table

[Terence Young]

You can take a look at pgAgent; this is equivalent to SQL servers' 'SQL Server Agent'.

pgAgent  is a scheduling agent for PostgreSQL databases. It can run shell scripts or SQL commands according to complicated schedules. The agent runs as a daemon on Unix systems, and a service on Windows.

Although, if on unix or MacOSX, yu could probably use a cron that executes a psql with an sql script attached.

terry


Terence J. Young, DC

System Administrator

IT

(608) 280-2679

________________________________
From: omnisdev-en <omnisdev-en-bounces at lists.omnis-dev.com> on behalf of Jim Pistrang <jim at jpcr.com>
Sent: Thursday, October 22, 2020 5:43 AM
To: Omnis List Mail <omnisdev-en at lists.omnis-dev.com>
Subject: Postgres queue table

CAUTION: This email originated from outside Journey. Please verify the sender before opening attachments or clicking links.


Hi all,

Apologies for putting a Postgres question here, but I'm having a difficult time getting a clear answer from stackoverflow and the other usual sources.

I have a trigger in a Postgres table that executes a function after a value in a specific column in the table is increased.  It works, but the function is rather complex, and in Postgres the transaction that updates the table is not complete until the function is executed.  I want to have the trigger simply insert a row into a queue table, and have another process in Postgres poll the queue table, grab a row that has not been processed, and execute the function.

I will not be dealing with a huge volume of transactions - a process that polls the queue table every 5 or 10 seconds will be more than adequate.  I see a number of 3rd party solutions out there, but I would like to do this strictly in the Postgres environment.  Is there a way to do this?

Jim

--
Jim Pistrang
JP Computer Resources
413-237-5710
<https://jpcr.com>


_____________________________________________________________
Manage your list subscriptions at http://lists.omnis-dev.com
Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com
If you received this e-mail in error, please notify the sender, delete the e-mail, and do not use, disclose or store the information it contains. This e-mail and any attachments may contain confidential information, including records protected by WI Mental Health 51.30 and/or AODA federal confidentiality rules (42 CFR Part 2) - both prohibit you from making any further disclosure of this information unless further disclosure is expressly permitted by the written consent of the person to whom it pertains or as otherwise permitted by 51.30 or 42 CFR Part 2. A general authorization for the release of medical or other information is NOT sufficient for this purpose. Federal rules restrict any use of this information to criminally investigate or prosecute any alcohol or drug abuse client.