pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedintagg.sgml: example wrongly named and does not compile
6+ messages / 4 participants
[nested] [flat]
* intagg.sgml: example wrongly named and does not compile
@ 2017-04-21 15:12 Christophe Courtois <christophe.courtois@dalibo.com>
0 siblings, 1 reply; 6+ messages in thread
From: Christophe Courtois @ 2017-04-21 15:12 UTC (permalink / raw)
To: pgsql-docs
Hi,
I've found out that the example in intagg.sgml is wrongly named: the
one-to-many table is a many-to-many.
And my colleague Thibaut Madeleine has seen that the "CREATE TABLE
right" and "CREATE TABLE left" examples cannot compile due to the
reserved words.
I propose the attached patch to fix that.
Yours,
--
Christophe Courtois
Consultant Dalibo
http://dalibo.com/ - http://dalibo.org/
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
Attachments:
[text/x-patch] intagg.sgml.patch (0B, ../../aa7cfd73-0d8d-596a-b684-39faa479afa5@dalibo.com/2-intagg.sgml.patch)
download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: intagg.sgml: example wrongly named and does not compile
@ 2017-04-21 15:45 Tom Lane <tgl@sss.pgh.pa.us>
parent: Christophe Courtois <christophe.courtois@dalibo.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2017-04-21 15:45 UTC (permalink / raw)
To: Christophe Courtois <christophe.courtois@dalibo.com>; +Cc: pgsql-docs
Christophe Courtois <christophe.courtois@dalibo.com> writes:
> I've found out that the example in intagg.sgml is wrongly named: the
> one-to-many table is a many-to-many.
Well, it'd depend on how it was used. The example clearly intends that
it be one-to-many, and I'm not sure it still makes sense without that
restriction. Maybe better to add a unique constraint on
one_to_many(left)?
> And my colleague Thibaut Madeleine has seen that the "CREATE TABLE
> right" and "CREATE TABLE left" examples cannot compile due to the
> reserved words.
Ouch. Shows you how old this module is :-(
> I propose the attached patch to fix that.
Um, the attached file seems empty from here.
regards, tom lane
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: intagg.sgml: example wrongly named and does not compile
@ 2017-04-21 16:07 Christophe Courtois <christophe.courtois@dalibo.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Christophe Courtois @ 2017-04-21 16:07 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-docs
Le 21/04/2017 à 17:45, Tom Lane a écrit :
> Christophe Courtois <christophe.courtois@dalibo.com> writes:
>> I've found out that the example in intagg.sgml is wrongly named: the
>> one-to-many table is a many-to-many.
> Well, it'd depend on how it was used. The example clearly intends that
> it be one-to-many, and I'm not sure it still makes sense without that
> restriction. Maybe better to add a unique constraint on
> one_to_many(left)?
Perhaps the whole example can be simplified to get rid of the "left"
table, but I didn't intend to rewrite it.
>> And my colleague Thibaut Madeleine has seen that the "CREATE TABLE
>> right" and "CREATE TABLE left" examples cannot compile due to the
>> reserved words.
> Ouch. Shows you how old this module is :-(
Indeed.
>> I propose the attached patch to fix that.
> Um, the attached file seems empty from here.
Ooops, sorry. It is attached.
--
Christophe Courtois
Consultant Dalibo
http://dalibo.com/ - http://dalibo.org/
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
Attachments:
[text/x-patch] intagg.sgml.patch (3.7K, ../../ba54d8fa-603f-c789-2bc4-e6e86b7b01b2@dalibo.com/2-intagg.sgml.patch)
download | inline diff:
--- intagg.sgml.HEAD 2016-11-17 09:41:53.173988630 +0100
+++ intagg.sgml 2017-04-21 16:29:10.421996579 +0200
@@ -54,20 +54,23 @@
<title>Sample Uses</title>
<para>
- Many database systems have the notion of a one to many table. Such a table
+ Many database systems have the notion of a many to many table. Such a table
usually sits between two indexed tables, for example:
<programlisting>
-CREATE TABLE left (id INT PRIMARY KEY, ...);
-CREATE TABLE right (id INT PRIMARY KEY, ...);
-CREATE TABLE one_to_many(left INT REFERENCES left, right INT REFERENCES right);
+CREATE TABLE left_table (id INT PRIMARY KEY, ...);
+CREATE TABLE right_table (id INT PRIMARY KEY, ...);
+CREATE TABLE many_to_many(id_left INT REFERENCES left_table,
+ id_right INT REFERENCES right_table);
</programlisting>
It is typically used like this:
<programlisting>
-SELECT right.* from right JOIN one_to_many ON (right.id = one_to_many.right)
- WHERE one_to_many.left = <replaceable>item</>;
+SELECT right_table.*
+FROM right_table
+JOIN many_to_many ON (right_table.id = many_to_many.id_right)
+WHERE many_to_many.id_left = <replaceable>item</>;
</programlisting>
This will return all the items in the right hand table for an entry
@@ -76,7 +79,7 @@
<para>
Now, this methodology can be cumbersome with a very large number of
- entries in the <structname>one_to_many</> table. Often,
+ entries in the <structname>many_to_many</> table. Often,
a join like this would result in an index scan
and a fetch for each right hand entry in the table for a particular
left hand entry. If you have a very dynamic system, there is not much you
@@ -85,9 +88,9 @@
<programlisting>
CREATE TABLE summary AS
- SELECT left, int_array_aggregate(right) AS right
- FROM one_to_many
- GROUP BY left;
+ SELECT id_left, int_array_aggregate(id_right) AS rights
+ FROM many_to_many
+ GROUP BY id_left;
</programlisting>
This will create a table with one row per left item, and an array
@@ -95,33 +98,35 @@
the array; that's why there is an array enumerator. You can do
<programlisting>
-SELECT left, int_array_enum(right) FROM summary WHERE left = <replaceable>item</>;
+SELECT id_left, int_array_enum(rights) FROM summary WHERE id_left = <replaceable>item</>;
</programlisting>
The above query using <function>int_array_enum</> produces the same results
as
<programlisting>
-SELECT left, right FROM one_to_many WHERE left = <replaceable>item</>;
+SELECT id_left, id_right FROM many_to_many WHERE id_left = <replaceable>item</>;
</programlisting>
The difference is that the query against the summary table has to get
only one row from the table, whereas the direct query against
- <structname>one_to_many</> must index scan and fetch a row for each entry.
+ <structname>many_to_many</> must index scan and fetch a row for each entry.
</para>
<para>
On one system, an <command>EXPLAIN</> showed a query with a cost of 8488 was
reduced to a cost of 329. The original query was a join involving the
- <structname>one_to_many</> table, which was replaced by:
+ <structname>many_to_many</> table, which was replaced by:
<programlisting>
-SELECT right, count(right) FROM
- ( SELECT left, int_array_enum(right) AS right
- FROM summary JOIN (SELECT left FROM left_table WHERE left = <replaceable>item</>) AS lefts
- ON (summary.left = lefts.left)
+SELECT id_right, count(id_right) FROM
+ ( SELECT id_left, int_array_enum(rights) AS id_right
+ FROM summary
+ JOIN (SELECT id FROM left_table
+ WHERE id = <replaceable>item</>) AS lefts
+ ON (summary.id_left = lefts.id)
) AS list
- GROUP BY right
+ GROUP BY id_right
ORDER BY count DESC;
</programlisting>
</para>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [DOCS] intagg.sgml: example wrongly named and does not compile
@ 2023-11-22 03:27 Bruce Momjian <bruce@momjian.us>
parent: Christophe Courtois <christophe.courtois@dalibo.com>
0 siblings, 1 reply; 6+ messages in thread
From: Bruce Momjian @ 2023-11-22 03:27 UTC (permalink / raw)
To: Christophe Courtois <christophe.courtois@dalibo.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-docs
On Fri, Apr 21, 2017 at 06:07:13PM +0200, Christophe Courtois wrote:
> Le 21/04/2017 à 17:45, Tom Lane a écrit :
> > Christophe Courtois <christophe.courtois@dalibo.com> writes:
> >> I've found out that the example in intagg.sgml is wrongly named: the
> >> one-to-many table is a many-to-many.
> > Well, it'd depend on how it was used. The example clearly intends that
> > it be one-to-many, and I'm not sure it still makes sense without that
> > restriction. Maybe better to add a unique constraint on
> > one_to_many(left)?
>
> Perhaps the whole example can be simplified to get rid of the "left"
> table, but I didn't intend to rewrite it.
>
> >> And my colleague Thibaut Madeleine has seen that the "CREATE TABLE
> >> right" and "CREATE TABLE left" examples cannot compile due to the
> >> reserved words.
> > Ouch. Shows you how old this module is :-(
>
> Indeed.
>
> >> I propose the attached patch to fix that.
> > Um, the attached file seems empty from here.
>
> Ooops, sorry. It is attached.
I like this six year old patch so would like to apply it to master,
attached.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
Attachments:
[text/x-diff] agg.diff (4.1K, ../../ZV11Gh7fB2G39jR9@momjian.us/2-agg.diff)
download | inline diff:
diff --git a/doc/src/sgml/intagg.sgml b/doc/src/sgml/intagg.sgml
index 44a766eb4b..29e74ce146 100644
--- a/doc/src/sgml/intagg.sgml
+++ b/doc/src/sgml/intagg.sgml
@@ -54,20 +54,22 @@
<title>Sample Uses</title>
<para>
- Many database systems have the notion of a one to many table. Such a table
+ Many database systems have the notion of a many to many table. Such a table
usually sits between two indexed tables, for example:
<programlisting>
-CREATE TABLE left (id INT PRIMARY KEY, ...);
-CREATE TABLE right (id INT PRIMARY KEY, ...);
-CREATE TABLE one_to_many(left INT REFERENCES left, right INT REFERENCES right);
+CREATE TABLE left_table (id INT PRIMARY KEY, ...);
+CREATE TABLE right_table (id INT PRIMARY KEY, ...);
+CREATE TABLE many_to_many(id_left INT REFERENCES left_table,
+ id_right INT REFERENCES right_table);
</programlisting>
It is typically used like this:
<programlisting>
-SELECT right.* from right JOIN one_to_many ON (right.id = one_to_many.right)
- WHERE one_to_many.left = <replaceable>item</replaceable>;
+SELECT right_table.*
+FROM right_table JOIN many_to_many ON (right_table.id = many_to_many.id_right)
+WHERE many_to_many.id_left = <replaceable>item</replaceable>;
</programlisting>
This will return all the items in the right hand table for an entry
@@ -76,7 +78,7 @@ SELECT right.* from right JOIN one_to_many ON (right.id = one_to_many.right)
<para>
Now, this methodology can be cumbersome with a very large number of
- entries in the <structname>one_to_many</structname> table. Often,
+ entries in the <structname>many_to_many</structname> table. Often,
a join like this would result in an index scan
and a fetch for each right hand entry in the table for a particular
left hand entry. If you have a very dynamic system, there is not much you
@@ -85,9 +87,9 @@ SELECT right.* from right JOIN one_to_many ON (right.id = one_to_many.right)
<programlisting>
CREATE TABLE summary AS
- SELECT left, int_array_aggregate(right) AS right
- FROM one_to_many
- GROUP BY left;
+ SELECT id_left, int_array_aggregate(id_right) AS rights
+ FROM many_to_many
+ GROUP BY id_left;
</programlisting>
This will create a table with one row per left item, and an array
@@ -95,33 +97,35 @@ CREATE TABLE summary AS
the array; that's why there is an array enumerator. You can do
<programlisting>
-SELECT left, int_array_enum(right) FROM summary WHERE left = <replaceable>item</replaceable>;
+SELECT id_left, int_array_enum(rights) FROM summary WHERE id_left = <replaceable>item</replaceable>;
</programlisting>
The above query using <function>int_array_enum</function> produces the same results
as
<programlisting>
-SELECT left, right FROM one_to_many WHERE left = <replaceable>item</replaceable>;
+SELECT id_left, id_right FROM many_to_many WHERE id_left = <replaceable>item</replaceable>;
</programlisting>
The difference is that the query against the summary table has to get
only one row from the table, whereas the direct query against
- <structname>one_to_many</structname> must index scan and fetch a row for each entry.
+ <structname>many_to_many</structname> must index scan and fetch a row for each entry.
</para>
<para>
On one system, an <command>EXPLAIN</command> showed a query with a cost of 8488 was
reduced to a cost of 329. The original query was a join involving the
- <structname>one_to_many</structname> table, which was replaced by:
+ <structname>many_to_many</structname> table, which was replaced by:
<programlisting>
-SELECT right, count(right) FROM
- ( SELECT left, int_array_enum(right) AS right
- FROM summary JOIN (SELECT left FROM left_table WHERE left = <replaceable>item</replaceable>) AS lefts
- ON (summary.left = lefts.left)
+SELECT id_right, count(id_right) FROM
+ ( SELECT id_left, int_array_enum(rights) AS id_right
+ FROM summary
+ JOIN (SELECT id FROM left_table
+ WHERE id = <replaceable>item</replaceable>) AS lefts
+ ON (summary.id_left = lefts.id)
) AS list
- GROUP BY right
+ GROUP BY id_right
ORDER BY count DESC;
</programlisting>
</para>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [DOCS] intagg.sgml: example wrongly named and does not compile
@ 2023-11-22 09:19 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 6+ messages in thread
From: Laurenz Albe @ 2023-11-22 09:19 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; Christophe Courtois <christophe.courtois@dalibo.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-docs
On Tue, 2023-11-21 at 22:27 -0500, Bruce Momjian wrote:
> I like this six year old patch so would like to apply it to master,
> attached.
+1, since it is arguably a bug fix.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [DOCS] intagg.sgml: example wrongly named and does not compile
@ 2023-12-08 00:37 Bruce Momjian <bruce@momjian.us>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 6+ messages in thread
From: Bruce Momjian @ 2023-12-08 00:37 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Christophe Courtois <christophe.courtois@dalibo.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-docs
On Wed, Nov 22, 2023 at 10:19:57AM +0100, Laurenz Albe wrote:
> On Tue, 2023-11-21 at 22:27 -0500, Bruce Momjian wrote:
> > I like this six year old patch so would like to apply it to master,
> > attached.
>
> +1, since it is arguably a bug fix.
Patch applied to master.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2023-12-08 00:37 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-04-21 15:12 intagg.sgml: example wrongly named and does not compile Christophe Courtois <christophe.courtois@dalibo.com>
2017-04-21 15:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-04-21 16:07 ` Christophe Courtois <christophe.courtois@dalibo.com>
2023-11-22 03:27 ` Bruce Momjian <bruce@momjian.us>
2023-11-22 09:19 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-12-08 00:37 ` Bruce Momjian <bruce@momjian.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