Search Postgresql Archives

Re: PostgreSQL intenal scheduler?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



If I follow your use case, we have written something that just may fit your scenario and plan to open source it rather soon.

It has several layers but let me boil it down. First we use an open sourced auditing system to log changes to the source tables. This becomes your queue. A postgres background worker will asynchronously process these changes based on your configuration, which is highly configurable. It also handles the concurrency you are questioning.

This allows you to build history tables without requiring you for example to do it directly via a trigger. It also removes redundancy if you have the same key updated multiple times. It assumes we are fine with the data built not being 100% up to date data because these updates obviously don’t all happen in the same transaction as the source data change.

Let me know if this interests you and I can share more.

Thanks,
Jeremy 

On Wed, Sep 5, 2018 at 10:07 AM Thiemo Kellner <thiemo@xxxxxxxxxxxxxxxxxxxx> wrote:

Hi all

I am designing a framework for historisation implementation (SCD). One 
feature I would like to provide is a table in that the actual state of 
an entity is put and if this is complete, this history table is 
"updated":

       ------------         -------------
==>  | ENTITY_ACT |  ==>  | ENTITY_HIST |
       ------------         -------------

I plan to use instead-of-triggers on the hist table that read the 
actual table and perfoms all necessary inserts und updates on the 
history table. If I want the termination of a record version (actually 
the record of a specific business key with a specific payload) to get 
propagated up and/or down referential integrities (no overlapping 
validities) I have to make sure that only one of those processes is 
modifying a table. I was thinking of a scheduler queue where the 
trigger would put a process request and PostgreSQL would work through. 
Is there a scheduler within PostgreSQL? I read the documentation and 
searched the web but could not find a hint. But before going another 
road or implementing something myself, I ask. Maybe this design is no 
good at all.

Kind regards

Thiemo



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux