pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedExample 43.6. A PL/pgSQL Trigger Function for Maintaining a Summary Table
6+ messages / 3 participants
[nested] [flat]
* Example 43.6. A PL/pgSQL Trigger Function for Maintaining a Summary Table
@ 2023-10-26 07:33 Maxim Yablokov <m.yablokov@postgrespro.ru>
2023-10-26 09:29 ` Re: Example 43.6. A PL/pgSQL Trigger Function for Maintaining a Summary Table Daniel Gustafsson <daniel@yesql.se>
0 siblings, 1 reply; 6+ messages in thread
From: Maxim Yablokov @ 2023-10-26 07:33 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org
Hello!
Example 43.6 on the page
https://www.postgresql.org/docs/16/plpgsql-trigger.html has two tables
called main. But it seems that the table *time_dimension* is not used
any further, it's a bit strange for one of main tables of the example.
Am I missing something, or should this example be extended (or reduced)?
--
Best regards,
Maxim Yablokov
Technical writer-translator
Postgres Professional <https://postgrespro.com/;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Example 43.6. A PL/pgSQL Trigger Function for Maintaining a Summary Table
2023-10-26 07:33 Example 43.6. A PL/pgSQL Trigger Function for Maintaining a Summary Table Maxim Yablokov <m.yablokov@postgrespro.ru>
@ 2023-10-26 09:29 ` Daniel Gustafsson <daniel@yesql.se>
2023-10-26 09:36 ` Re: Example 43.6. A PL/pgSQL Trigger Function for Maintaining a Summary Table Maxim Yablokov <m.yablokov@postgrespro.ru>
2023-10-26 10:31 ` Re: Example 43.6. A PL/pgSQL Trigger Function for Maintaining a Summary Table Maxim Yablokov <m.yablokov@postgrespro.ru>
0 siblings, 2 replies; 6+ messages in thread
From: Daniel Gustafsson @ 2023-10-26 09:29 UTC (permalink / raw)
To: Maxim Yablokov <m.yablokov@postgrespro.ru>; +Cc: pgsql-docs@lists.postgresql.org
> On 26 Oct 2023, at 09:33, Maxim Yablokov <m.yablokov@postgrespro.ru> wrote:
> Example 43.6 on the page https://www.postgresql.org/docs/16/plpgsql-trigger.html has two tables called main. But it seems that the table time_dimension is not used any further, it's a bit strange for one of main tables of the example. Am I missing something, or should this example be extended (or reduced)?
Interesting catch, time_dimension has been unused since introduced with commit
a294726bc1d (in 2005), and the archives doesn't provide any further insights.
While I don't have access to the book referred to for the example, we don't
expect our readers to either so it seems to me that we could remove it from the
example to avoid risk of confusion. Would you like to propose a patch for this?
--
Daniel Gustafsson
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Example 43.6. A PL/pgSQL Trigger Function for Maintaining a Summary Table
2023-10-26 07:33 Example 43.6. A PL/pgSQL Trigger Function for Maintaining a Summary Table Maxim Yablokov <m.yablokov@postgrespro.ru>
2023-10-26 09:29 ` Re: Example 43.6. A PL/pgSQL Trigger Function for Maintaining a Summary Table Daniel Gustafsson <daniel@yesql.se>
@ 2023-10-26 09:36 ` Maxim Yablokov <m.yablokov@postgrespro.ru>
1 sibling, 0 replies; 6+ messages in thread
From: Maxim Yablokov @ 2023-10-26 09:36 UTC (permalink / raw)
To: Daniel Gustafsson <daniel@yesql.se>; +Cc: pgsql-docs@lists.postgresql.org
Yeah, sure thing! Thanks! I'll send a patch soon.
26.10.2023 12:29, Daniel Gustafsson пишет:
>> On 26 Oct 2023, at 09:33, Maxim Yablokov<m.yablokov@postgrespro.ru> wrote:
>> Example 43.6 on the pagehttps://www.postgresql.org/docs/16/plpgsql-trigger.html has two tables called main. But it seems that the table time_dimension is not used any further, it's a bit strange for one of main tables of the example. Am I missing something, or should this example be extended (or reduced)?
> Interesting catch, time_dimension has been unused since introduced with commit
> a294726bc1d (in 2005), and the archives doesn't provide any further insights.
> While I don't have access to the book referred to for the example, we don't
> expect our readers to either so it seems to me that we could remove it from the
> example to avoid risk of confusion. Would you like to propose a patch for this?
>
> --
> Daniel Gustafsson
>
--
Best regards,
Maxim Yablokov
Technical writer-translator
Postgres Professional <https://postgrespro.com/;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Example 43.6. A PL/pgSQL Trigger Function for Maintaining a Summary Table
2023-10-26 07:33 Example 43.6. A PL/pgSQL Trigger Function for Maintaining a Summary Table Maxim Yablokov <m.yablokov@postgrespro.ru>
2023-10-26 09:29 ` Re: Example 43.6. A PL/pgSQL Trigger Function for Maintaining a Summary Table Daniel Gustafsson <daniel@yesql.se>
@ 2023-10-26 10:31 ` Maxim Yablokov <m.yablokov@postgrespro.ru>
2023-10-27 13:15 ` Re: Example 43.6. A PL/pgSQL Trigger Function for Maintaining a Summary Table Daniel Gustafsson <daniel@yesql.se>
1 sibling, 1 reply; 6+ messages in thread
From: Maxim Yablokov @ 2023-10-26 10:31 UTC (permalink / raw)
To: Daniel Gustafsson <daniel@yesql.se>; +Cc: pgsql-docs@lists.postgresql.org
I believe that the attached patch should fix this problem. Please have a
look.
26.10.2023 12:29, Daniel Gustafsson пишет:
>> On 26 Oct 2023, at 09:33, Maxim Yablokov<m.yablokov@postgrespro.ru> wrote:
>> Example 43.6 on the pagehttps://www.postgresql.org/docs/16/plpgsql-trigger.html has two tables called main. But it seems that the table time_dimension is not used any further, it's a bit strange for one of main tables of the example. Am I missing something, or should this example be extended (or reduced)?
> Interesting catch, time_dimension has been unused since introduced with commit
> a294726bc1d (in 2005), and the archives doesn't provide any further insights.
> While I don't have access to the book referred to for the example, we don't
> expect our readers to either so it seems to me that we could remove it from the
> example to avoid risk of confusion. Would you like to propose a patch for this?
>
> --
> Daniel Gustafsson
>
--
Best regards,
Maxim Yablokov
Technical writer-translator
Postgres Professional <https://postgrespro.com/;
Attachments:
[text/x-patch] plpgsql-trigger.patch (888B, ../../fe405123-9cae-4f16-81c0-4e73bec93b81@postgrespro.ru/3-plpgsql-trigger.patch)
download | inline diff:
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 83f867f91d2..7103ab03b74 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -4449,18 +4449,8 @@ INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_view
<programlisting>
--
--- Main tables - time dimension and sales fact.
+-- Main table - sales fact.
--
-CREATE TABLE time_dimension (
- time_key integer NOT NULL,
- day_of_week integer NOT NULL,
- day_of_month integer NOT NULL,
- month integer NOT NULL,
- quarter integer NOT NULL,
- year integer NOT NULL
-);
-CREATE UNIQUE INDEX time_dimension_key ON time_dimension(time_key);
-
CREATE TABLE sales_fact (
time_key integer NOT NULL,
product_key integer NOT NULL,
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Example 43.6. A PL/pgSQL Trigger Function for Maintaining a Summary Table
2023-10-26 07:33 Example 43.6. A PL/pgSQL Trigger Function for Maintaining a Summary Table Maxim Yablokov <m.yablokov@postgrespro.ru>
2023-10-26 09:29 ` Re: Example 43.6. A PL/pgSQL Trigger Function for Maintaining a Summary Table Daniel Gustafsson <daniel@yesql.se>
2023-10-26 10:31 ` Re: Example 43.6. A PL/pgSQL Trigger Function for Maintaining a Summary Table Maxim Yablokov <m.yablokov@postgrespro.ru>
@ 2023-10-27 13:15 ` Daniel Gustafsson <daniel@yesql.se>
2023-10-28 02:16 ` Re: Example 43.6. A PL/pgSQL Trigger Function for Maintaining a Summary Table Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Daniel Gustafsson @ 2023-10-27 13:15 UTC (permalink / raw)
To: Maxim Yablokov <m.yablokov@postgrespro.ru>; +Cc: pgsql-docs@lists.postgresql.org
> On 26 Oct 2023, at 12:31, Maxim Yablokov <m.yablokov@postgrespro.ru> wrote:
>
> I believe that the attached patch should fix this problem. Please have a look.
Thanks, I'll await pushing and backpatching if Tom who committed it has
insights into whether it was missed or if it indeed serves a purpose. If not I
think it should be backpatched all the way since it exists for all supported
versions.
--
Daniel Gustafsson
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Example 43.6. A PL/pgSQL Trigger Function for Maintaining a Summary Table
2023-10-26 07:33 Example 43.6. A PL/pgSQL Trigger Function for Maintaining a Summary Table Maxim Yablokov <m.yablokov@postgrespro.ru>
2023-10-26 09:29 ` Re: Example 43.6. A PL/pgSQL Trigger Function for Maintaining a Summary Table Daniel Gustafsson <daniel@yesql.se>
2023-10-26 10:31 ` Re: Example 43.6. A PL/pgSQL Trigger Function for Maintaining a Summary Table Maxim Yablokov <m.yablokov@postgrespro.ru>
2023-10-27 13:15 ` Re: Example 43.6. A PL/pgSQL Trigger Function for Maintaining a Summary Table Daniel Gustafsson <daniel@yesql.se>
@ 2023-10-28 02:16 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2023-10-28 02:16 UTC (permalink / raw)
To: Daniel Gustafsson <daniel@yesql.se>; +Cc: Maxim Yablokov <m.yablokov@postgrespro.ru>; pgsql-docs@lists.postgresql.org
Daniel Gustafsson <daniel@yesql.se> writes:
> Thanks, I'll await pushing and backpatching if Tom who committed it has
> insights into whether it was missed or if it indeed serves a purpose.
Hey, I just pushed that for somebody else, I don't claim authorship ;-)
It seems clear that the example intends to show a star-schema database
where the fact table refers to various dimension tables. But it's
incomplete --- there's no foreign-key constraint on time_key, and
even less infrastructure for product_key or store_key. I don't
have the cited book either, so I don't know how complete the original
example was. Perhaps the bit in the trigger function about forbidding
updates to time_key has something to do with that model.
Anyway, I don't see any reason to object to this patch. The extra
table isn't adding much. My only thought is would it make sense to
change time_key to be a timestamp or timestamptz value?
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2023-10-28 02:16 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-26 07:33 Example 43.6. A PL/pgSQL Trigger Function for Maintaining a Summary Table Maxim Yablokov <m.yablokov@postgrespro.ru>
2023-10-26 09:29 ` Daniel Gustafsson <daniel@yesql.se>
2023-10-26 09:36 ` Maxim Yablokov <m.yablokov@postgrespro.ru>
2023-10-26 10:31 ` Maxim Yablokov <m.yablokov@postgrespro.ru>
2023-10-27 13:15 ` Daniel Gustafsson <daniel@yesql.se>
2023-10-28 02:16 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox