Re: using worker_spi as pattern - Mailing list pgsql-hackers

On Thu, Mar 08, 2018 at 03:29:52PM -0600, Jeremy Finzel wrote:
> However, this raises many questions for me:
>
>    - Insert a value into what table?  I see the process referring to an
>    object that doesn't exist in my database - schema1.counted
>    - What is "total" type?  I don't see any type with this name in the
>    database
>    - Same question for "delta" type

If you look at the code of worker_spi.c closely the answer shows up by
itself:

appendStringInfo(&buf,
         "CREATE SCHEMA \"%s\" "
         "CREATE TABLE \"%s\" ("
         "        type text CHECK (type IN ('total', 'delta')), "
         "        value    integer)"
         "CREATE UNIQUE INDEX \"%s_unique_total\" ON \"%s\" (type) "
         "WHERE type = 'total'",

In this case "total" is not a type, it is one of the authorized value in
the value.  So just insert an initial tuple like that:
INSERT INTO schema1.counted VALUES ('total', 1);
And then insert periodically for example the following:
INSERT INTO schema1.counted VALUES ('delta', 3);
And then the background worker will sum up the values inserted in
"delta" tuples to the actual "total".

> I am trying to use this extension as a pattern for my own background
> worker, but just trying to understand it.

You are right to do so, this is a good learning step.
--
Michael