Re: Documentation of FOR ROLE clause of ALTER DEFAULT PRIVILEGES missing
On Thu, Feb 22, 2018 at 07:50:19PM +, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: > https://www.postgresql.org/docs/10/static/sql-alterdefaultprivileges.html > Description: > > The documentation of ALTER DEFAULT PRIVILEGES statements seems to lack an > elaboration of the FOR ROLE clause. > > It specifies what restrictions exist (namely "The name of an existing role > of which the current role is a member."), but what the specified role is > actually used for remains a mystery. > > The documentation should read something like: "The default permissions are > only granted on objects created by this role." I know this is five years late, but we fixed this issue and the new docs will appear in Postgres 17: https://www.postgresql.org/docs/devel/sql-alterdefaultprivileges.html We felt the doc rewrite was too extensive to backpatch. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: [DOCS] The reference to 'atacontrol' on FreeBSD is outdated.
On Sat, Aug 12, 2017 at 10:47:00PM +0100, John Ekins wrote: > Hi, > > Sure. Camcontrol does indeed cover IDE, SCSI and SAS drives. > > Replace this line: > On FreeBSD, IDE drives can be queried using atacontrol... > > With this: > On FreeBSD, IDE drives can be queried using camcontrol identify... > > In other words, the query is the same as for SCSI drives. For verification > this > is what the output looks like: > > root@bsd11desk:/home/jre # camcontrol identify 0:0:0 | egrep "Feature|write > cache" > Feature Support Enabled Value Vendor > write cacheyes yes > > I appreciate the Postgresql docs are not meant to be FreeBSD tutorials. I know this email is six years old, but I have applied this fix to all supported Postgres versions. A huge apology for this delay. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: [DOCS] Confusing Trigger Docs.
On Thu, Aug 31, 2017 at 09:22:22AM -0700, Peter Geoghegan wrote: > On Thu, Aug 31, 2017 at 6:25 AM, Bruce Momjian wrote: > > On Mon, Jul 3, 2017 at 08:07:10PM +, n...@fairwindsoft.com wrote: > >> The following documentation comment has been logged on the website: > >> > >> Page: https://www.postgresql.org/docs/9.6/static/trigger-definition.html > >> Description: > >> > >> https://www.postgresql.org/docs/devel/static/trigger-definition.html > >> > >> This sentence: > >> > >> "If an INSERT contains an ON CONFLICT DO UPDATE clause, it is > >> possible that > >> the effects of all row-level BEFORE INSERT triggers and all row-level > >> BEFORE > >> UPDATE triggers can both be applied in a way that is apparent from the > >> final > >> state of the updated row, if an EXCLUDED column is referenced." > >> > >> is very hard to digest. > > EXCLUDED.* is exactly what the name suggests -- the tuple that was not > inserted because of a conflict. So, naturally it has the effects of > any before insert trigger, and carries them forward. But you still > have before triggers on the update side. > > Typically, this won't matter at all, because before triggers tend to > be written in an idempotent fashion -- something gets filled in. But I > can imagine cases where it is not idempotent, and apply a before > update trigger modifies the row in a way that is surprising. Just > because ON CONFLICT DO UPDATE was used rather than UPDATE. That's what > the documentation warns about. I know this thread is six years old, but I still found it confusing, so the attached patch tries to simplify it. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml new file mode 100644 index 6e1f370..0615f8a *** a/doc/src/sgml/trigger.sgml --- b/doc/src/sgml/trigger.sgml *** *** 140,160 ! If an INSERT contains an ON CONFLICT ! DO UPDATE clause, it is possible that the effects of ! row-level BEFORE INSERT triggers and ! row-level BEFORE UPDATE triggers can ! both be applied in a way that is apparent from the final state of ! the updated row, if an EXCLUDED column is referenced. ! There need not be an EXCLUDED column reference for ! both sets of row-level BEFORE triggers to execute, ! though. The ! possibility of surprising outcomes should be considered when there ! are both BEFORE INSERT and ! BEFORE UPDATE row-level triggers ! that change a row being inserted/updated (this can be ! problematic even if the modifications are more or less equivalent, if ! they're not also idempotent). Note that statement-level UPDATE triggers are executed when ON CONFLICT DO UPDATE is specified, regardless of whether or not any rows were affected by the UPDATE (and --- 140,158 ! If an INSERT contains an ON ! CONFLICT DO UPDATE clause, it is possible for both ! row-level BEFORE INSERT and ! BEFORE UPDATE triggers to be ! executed on the same row. The possibility of surprising outcomes ! should be considered when they change rows being inserted/updated ! (this can be problematic even if the modifications are more or less ! equivalent, if they're not also idempotent). The modification of ! EXCLUDED columns has similar interactions. ! ! ! ! Note that statement-level UPDATE triggers are executed when ON CONFLICT DO UPDATE is specified, regardless of whether or not any rows were affected by the UPDATE (and
Re: [DOCS] The reference to 'atacontrol' on FreeBSD is outdated.
On Wed, Nov 22, 2023 at 2:12 PM Bruce Momjian wrote: > On Sat, Aug 12, 2017 at 10:47:00PM +0100, John Ekins wrote: > > Sure. Camcontrol does indeed cover IDE, SCSI and SAS drives. > > > > Replace this line: > > On FreeBSD, IDE drives can be queried using atacontrol... > > > > With this: > > On FreeBSD, IDE drives can be queried using camcontrol identify... > > > > In other words, the query is the same as for SCSI drives. For verification > > this > > is what the output looks like: > > > > root@bsd11desk:/home/jre # camcontrol identify 0:0:0 | egrep "Feature|write > > cache" > > Feature Support Enabled Value Vendor > > write cacheyes yes > > > > I appreciate the Postgresql docs are not meant to be FreeBSD tutorials. > > I know this email is six years old, but I have applied this fix to all > supported Postgres versions. A huge apology for this delay. It's funny that we discuss IDE drives at all. They stopped being manufactured a decade ago and are not supported by modern chipsets or boards etc[1]. I've been meaning to do something about that, but I didn't have enough round tuits to research what to write about current storage technologies... but also in the case of FreeBSD, it doesn't let you turn the write cache on and off with nvmecontrol yet, but someone has a relevant patch out (D32700) so I was also waiting for that to go somewhere before writing in... [1] https://en.wikipedia.org/wiki/Parallel_ATA#Primacy_and_obsolescence
Re: [DOCS] The reference to 'atacontrol' on FreeBSD is outdated.
On Wed, Nov 22, 2023 at 03:25:07PM +1300, Thomas Munro wrote: > On Wed, Nov 22, 2023 at 2:12 PM Bruce Momjian wrote: > > On Sat, Aug 12, 2017 at 10:47:00PM +0100, John Ekins wrote: > > > Sure. Camcontrol does indeed cover IDE, SCSI and SAS drives. > > > > > > Replace this line: > > > On FreeBSD, IDE drives can be queried using atacontrol... > > > > > > With this: > > > On FreeBSD, IDE drives can be queried using camcontrol identify... > > > > > > In other words, the query is the same as for SCSI drives. For > > > verification this > > > is what the output looks like: > > > > > > root@bsd11desk:/home/jre # camcontrol identify 0:0:0 | egrep > > > "Feature|write > > > cache" > > > Feature Support Enabled Value Vendor > > > write cacheyes yes > > > > > > I appreciate the Postgresql docs are not meant to be FreeBSD tutorials. > > > > I know this email is six years old, but I have applied this fix to all > > supported Postgres versions. A huge apology for this delay. > > It's funny that we discuss IDE drives at all. They stopped being > manufactured a decade ago and are not supported by modern chipsets or > boards etc[1]. I've been meaning to do something about that, but I > didn't have enough round tuits to research what to write about current > storage technologies... but also in the case of FreeBSD, it doesn't > let you turn the write cache on and off with nvmecontrol yet, but > someone has a relevant patch out (D32700) so I was also waiting for > that to go somewhere before writing in... I don't think it is safe to disable the nvme cache since it is a cache and a staging area. If you turn it off, it will burn out the chips over time. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: [DOCS] intagg.sgml: example wrongly named and does not compile
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 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 https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. 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 @@ Sample Uses - 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: -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); It is typically used like this: -SELECT right.* from right JOIN one_to_many ON (right.id = one_to_many.right) - WHERE one_to_many.left = 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 = item; 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) Now, this methodology can be cumbersome with a very large number of - entries in the one_to_many table. Often, + entries in the 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 +87,9 @@ SELECT right.* from right JOIN one_to_many ON (right.id = one_to_many.right) 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; 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 -SELECT left, int_array_enum(right) FROM summary WHERE left = item; +SELECT id_left, int_array_enum(rights) FROM summary WHERE id_left = item; The above query using int_array_enum produces the same results as -SELECT left, right FROM one_to_many WHERE left = item; +SELECT id_left, id_right FROM many_to_many WHERE id_left = item; The difference is that the query against the summary table has to get only one row from the table, whereas the direct query against - one_to_many must index scan and fetch a row for each entry. + many_to_many must index scan and fetch a row for each entry. On one system, an EXPLAIN showed a query with a cost of 8488 was reduced to a cost of 329. The original query was a join involving the - one_to_many table, which was replaced by: + many_to_many table, which was replaced by: -SELECT right, count(right) FROM - ( SELECT left, int_array_enum(right) AS right -FROM summary JOIN (SELECT left FROM left_table WHERE left = 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 = item) AS lefts +ON (summary.id_left = lefts.id) ) AS list - GROUP BY right + GROUP BY id_right ORDER BY count DESC;
Re: [DOCS] Add example about date ISO format
On Fri, Feb 17, 2017 at 04:01:54PM +, juha.musto...@iki.fi wrote:
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/9.6/static/functions-formatting.html
> Description:
>
> The documentation should include an example how to format datetime entry
> into most commonly known ISO format. This is a bit tricky as literal
> character needs to included with quotes:
>
> to_char(NOW(), '-MM-DD"T"HH24:MI:SSZ')
I know this is a six-year-old idea, but it is still a good one. I have
developed the attached patch I would like to apply to master.
--
Bruce Momjian https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 93f068edcf..297cafb341 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -8489,6 +8489,14 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
+
+
+to_char(current_timestamp AT TIME ZONE 'UTC',
+'-MM-DD"T"HH24:MI:SSZ') outputs the current UTC
+date/time in ISO 8601 date/time format.
+
+
+
