Re: How can a Postgres SQL script be automatically run when a new table turns up?

On Thu, Jan 13, 2022 at 4:32 PM David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Thu, Jan 13, 2022 at 8:55 AM Shaozhong SHI <shishaozhong(at)gmail(dot)com>
> wrote:
>
>>
>> How to create an event trigger in Postgres? When a user finished loading
>> a new table on to it, the trigger can start off an script 10 minutes after
>> the event?
>>
>>
> You could have a script execute every minute (say via cron)
>

Vendor-specific, but on AWS, you can invoke a lambda from RDS or Aurora.

1. An event trigger function runs after CREATE TABLE and invokes a lambda.
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL-Lambda.html

2. The lambda calls StartExecution on a step function.
https://docs.aws.amazon.com/step-functions/latest/apireference/API_StartExecution.html

3. In the first step of the step functions, call wait for 10 minutes.
https://docs.aws.amazon.com/step-functions/latest/dg/amazon-states-language-wait-state.html

4. In the second step, have the lambda perform whatever operation(s) you
need to do.

No polling required, but you'd have to be running in a managed service in
the Amazon Cloud.

Similarly, if you are self-hosting and willing/able to write some C code or
run some pl/pythonu, you could create an extension/function that performs
this logic.

Or again if you are self-managed and go the cron route as suggested by
David Johnson, there's the extension pg_cron.

– Miles Elam