Thread: using worker_spi as pattern
Hello - I have compiled and installed the extension worker_spi. I also launched the process via SELECT worker_spi_launch(1);
I see this in pg_stat_activity:
WITH deleted AS (DELETE FROM schema1.counted WHERE type = 'delta' RETURNING value), total AS (SELECT coalesce(sum(value), 0) as sum FROM deleted) UPDATE schema1.counted SET value = counted.value + total.sum FROM total WHERE type = 'total' RETURNING counted.value
However, I'm not sure what I am supposed to do next? The docs at the top of the module say:
To see it working, insert an initial value
* with "total" type and some initial value; then insert some other rows with
* "delta" type. Delta rows will be deleted by this worker and their values
* aggregated into the total.
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
I am trying to use this extension as a pattern for my own background worker, but just trying to understand it.
Thanks!
Jeremy
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
Attachment
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 could not find the table schema1.counted. What confused me is that I ran SELECT worker_spi_launch(1); but it created the schema in the database postgres instead of the current database I am in! Doesn't that seem a bit counter-intuitive? Anyway, I found it now, so I am good to go! Thank you!
> 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
Since you mention, can anyone elaborate further on the memory leak danger here?
Line 193 in src/test/modules/worker_spi/worker_spi.c read:
# Note some memory might be leaked here.
Is this any reason not to use this pattern in production?
Thanks,
Jeremy
On Thu, Mar 08, 2018 at 11:04:20PM -0600, Jeremy Finzel wrote: > Since you mention, can anyone elaborate further on the memory leak danger > here? > > Line 193 in src/test/modules/worker_spi/worker_spi.c read: > # Note some memory might be leaked here. > > Is this any reason *not *to use this pattern in production? quote_identifier may palloc the result, so the first pstrdup on the top to save "schema" and "table" refer to a pointer which may perhaps get lost. Those are just a couple of bytes, so the code complication is not worth the cleanup IMO. -- Michael
Attachment
On Fri, Mar 9, 2018 at 12:34 AM, Michael Paquier <michael@paquier.xyz> wrote:
On Thu, Mar 08, 2018 at 11:04:20PM -0600, Jeremy Finzel wrote:
> Since you mention, can anyone elaborate further on the memory leak danger
> here?
>
> Line 193 in src/test/modules/worker_spi/worker_spi.c read:
> # Note some memory might be leaked here.
>
> Is this any reason *not *to use this pattern in production?
quote_identifier may palloc the result, so the first pstrdup on the top
to save "schema" and "table" refer to a pointer which may perhaps get
lost. Those are just a couple of bytes, so the code complication is not
worth the cleanup IMO.
--
Michael
Makes sense, thank you.