Re: MERGE examples not clear
On Wed, Sep 27, 2023 at 07:42:42PM -0400, Bruce Momjian wrote:
> On Tue, Feb 21, 2023 at 08:56:50AM -0700, David G. Johnston wrote:
> > On Tue, Feb 21, 2023 at 8:35 AM PG Doc comments form
> >
> > wrote:
> >
> > The following documentation comment has been logged on the website:
> >
> > Page: https://www.postgresql.org/docs/15/sql-merge.html
> > Description:
> >
> > On this page: https://www.postgresql.org/docs/15/sql-merge.html
> > the first and second examples seems to be contrasted (by "this would be
> > exactly equivalent to the following statement"), however the difference
> > does
> > not seem to related to the stated reason ("the MATCHED result does not
> > change"). It seems like the difference should involve the order of WHEN
> > clauses?
> > Of course, it might be that I don't understand the point, in which case
> > maybe the point could be stated more clearly?
> >
> >
> > Yeah, that is a pretty poor pair of examples. Given that a given customer
> > can
> > reasonably be assumed to have more than one recent transaction the MERGE
> > has a
> > good chance of failing.
> >
> > The only difference between the two is the second one uses an explicit
> > subquery
> > as the source while the first simply names a table. If the subquery had a
> > GROUP BY customer_id that would be a good change explaining that the second
> > query is different because it is resilient in the face of duplicate customer
> > recent transactions.
> >
> > While here...source_alias (...completely hides...the fact that a query was
> > issued). What? Probably it should read (not verified) that it is actually
> > required when the source is a query (maybe tweaking the syntax to match).
>
> The attached patch removes the second example, which doesn't seem to add
> much.
Patch from September 2023 applied.
--
Bruce Momjian https://momjian.us
EDB https://enterprisedb.com
When a patient asks the doctor, "Am I going to die?", he means
"Am I going to die soon?"
Re: incorrect (incomplete) description for "alter domain"
On Wed, Oct 16, 2024 at 05:11:54PM -0400, Bruce Momjian wrote:
> > Actually, there *is* a bug in the description, because experimentation
> > shows that CREATE DOMAIN accepts NULL in this syntax (as advertised)
> > but ALTER DOMAIN does not. We could alternatively decide that that's
> > a code bug and make ALTER DOMAIN take it, but I don't think it's worth
> > any effort (and this behavior may actually have been intentional, too).
> > I think we should just add
> >
> > where domain_constraint is:
> >
> > [ CONSTRAINT constraint_name ]
> > { NOT NULL | CHECK (expression) }
> >
> > to the ALTER DOMAIN page, and then remove the claim that it's
> > identical to CREATE DOMAIN.
>
> I have written the attached patch to document this. I assume this
> should be backpatched to PG 12.
Patch applied back to PG 12.
--
Bruce Momjian https://momjian.us
EDB https://enterprisedb.com
When a patient asks the doctor, "Am I going to die?", he means
"Am I going to die soon?"
Re: Documentation of .pgpass for Unix is incomplete
Thanks, Bruce! > Am 01.11.2024 um 18:32 schrieb Bruce Momjian : > > On Tue, Aug 20, 2024 at 09:24:43AM +0200, Marc Balmer wrote: >> >>> Well, it is more complicated than checking just HOME because it calls >>> getpwuid_r() if HOME is not set: >>> >>> >>> https://doxygen.postgresql.org/fe-connect_8c.html#a3f49cbb20595c1765bd0db5ff434c9c3 >>> >>> Is it worth going into that detail in the docs? >> >> Yes definitely. This not being properly documented caused me hours of work… > > Patch applied, thanks. > > -- > Bruce Momjian https://momjian.us > EDB https://enterprisedb.com > > When a patient asks the doctor, "Am I going to die?", he means > "Am I going to die soon?"
Re: Connection Info
On Wed, Oct 16, 2024 at 09:36:00PM -0400, Bruce Momjian wrote: > On Mon, May 6, 2024 at 04:15:24PM +0200, Laurenz Albe wrote: > > On Sun, 2024-05-05 at 20:25 +, PG Doc comments form wrote: > > > On this page: > > > https://www.postgresql.org/docs/14/server-start.html#CLIENT-CONNECTION-PROBLEMS > > > > > > It notes: " A common mistake is to forget to configure the server to > > > allow TCP/IP connections." > > > > > > I would expect a hyperlink to a section explaining exactly HOW to > > > "configure > > > the server to allow TCP/IP connections" - from that note text. > > > > I agree. > > > > Perhaps it should say: > > > > A common mistake is to forget to configure "listen_addresses" so that the > > server accepts remote TCP connections. > > > > Then "listen_addresses" could hyperlink to the parameter's documentation. > > Agreed, patch attached. Patch applied. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com When a patient asks the doctor, "Am I going to die?", he means "Am I going to die soon?"
Re: Documentation of .pgpass for Unix is incomplete
On Tue, Aug 20, 2024 at 09:24:43AM +0200, Marc Balmer wrote: > > > Well, it is more complicated than checking just HOME because it calls > > getpwuid_r() if HOME is not set: > > > > > > https://doxygen.postgresql.org/fe-connect_8c.html#a3f49cbb20595c1765bd0db5ff434c9c3 > > > > Is it worth going into that detail in the docs? > > Yes definitely. This not being properly documented caused me hours of work… Patch applied, thanks. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com When a patient asks the doctor, "Am I going to die?", he means "Am I going to die soon?"
Re: PostgreSQL limits
On Fri, Nov 1, 2024 at 09:14:20AM +, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/17/limits.html > Description: > > I think it can be useful to add the maximum SQL statement length that > PostgreSQL can handle > in the Appendix K. PostgreSQL Limits. > If I'm not wrong it is 2147483648 that is a quite large number, larger than > many other RDBMS. Uh, we can add it, but we try to only list limits that are likely to be exceeded. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com When a patient asks the doctor, "Am I going to die?", he means "Am I going to die soon?"
Re: fir for row level security
On Fri, Nov 1, 2024 at 11:04:53AM +, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/16/ddl-rowsecurity.html > Description: > > Cite > When multiple policies apply to a given query, they are combined using > either OR (for permissive policies, which are the default) or using AND (for > restrictive policies). This is similar to the rule that a given role has the > privileges of all roles that they are a member of. > end cite > > Not clear for what is "this is". May be better "The default behaviour is > similar..." I see your point. Attached is a patch which clarifies this. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com When a patient asks the doctor, "Am I going to die?", he means "Am I going to die soon?" diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index f6344b3b79a..b4554253fbe 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -2592,7 +2592,8 @@ GRANT SELECT (col1), UPDATE (col1) ON mytable TO miriam_rw; When multiple policies apply to a given query, they are combined using either OR (for permissive policies, which are the default) or using AND (for restrictive policies). - This is similar to the rule that a given role has the privileges + The OR behavior is similar to the rule that a given + role has the privileges of all roles that they are a member of. Permissive vs. restrictive policies are discussed further below.
Re: Add mention to related system catalog functions on Tablespaces documentation pages.
On Mon, Oct 14, 2024 at 07:21:32PM -0400, Bruce Momjian wrote: > On Mon, Oct 14, 2024 at 07:15:50PM -0400, Bruce Momjian wrote: > > On Wed, Sep 11, 2024 at 11:26:50PM +, PG Doc comments form wrote: > > Yes, very good points. I have developed the attached patch which I > > think achieves the goals you mentioned. I plan to apply the patch for > > master/PG 18. > > > > I also removed the unnecessary mention of PG 9.1. > > > > Seems the reply email address is g***@gmail.com, which seems like some > > kind of mistake. > > Sorry, patch attached. Invalid email address removed. Patch applied to master. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com When a patient asks the doctor, "Am I going to die?", he means "Am I going to die soon?"
Re: Mistake in statement example
On Wed, Sep 27, 2023 at 07:23:09PM -0400, Bruce Momjian wrote: > On Wed, Mar 1, 2023 at 09:45:00AM -0700, David G. Johnston wrote: > > That may be, but the descriptive text and point of the example (which isn't > > atomicity, but concurrency) doesn't even require the second update command > > to > > be present. What the example could use is a more traditional two-session > > depiction of the commands instead of having a single transaction and letting > > the user envision the correct concurrency. > > > > Something like: > > > > S1: SELECT balance FROM accounts WHERE acctnum = 12345; //100 > > S1: BEGIN; > > S2: BEGIN; > > S1: UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345; > > //200 > > S2: UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345; // > > WAITING ON S1 > > S1: COMMIT; > > S2: UPDATED; balance = 300 > > S2: COMMIT; > > > > Though maybe "balance" isn't a good example domain, the incrementing example > > used just after this one seems more appropriate along with the added > > benefit of > > consistency. > > I developed the attached patch. I explained the example, I mentioned a > "second" transaciton, I changed the account number so I can talk about > the second statement, because read committed changes the row visibility > of the non-first statements, and I changed "transaction" to "statement". Patch from September 2023 applied. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com When a patient asks the doctor, "Am I going to die?", he means "Am I going to die soon?"
RE: Tip box on Adding a Column
On 2024-11-01 09:25:24, David Rowley wrote: > We have the ability to store immutable defaults in the catalogue tables and > "fill in the blanks" from there for any tuples that don't have the new > column. Since we only can store 1 value per column, it must be a constant, > i.e. the same for all rows. Thanks for clarifying that David. Profiling can be deceiving if you don't have experience in the code base. > If timing isn't confirmation enough, try doing the above with: SET > client_min_messages=debug1; > > Only adding column "d" gives you: DEBUG: rewriting table "t1" Oh wow, that's a very useful setting to have, thanks for pointing that out too. Best, Marcelo
Re: Tip box on Adding a Column
On Fri, 1 Nov 2024 at 22:06, Marcelo Fernandes wrote: > - > https://www.postgresql.org/docs/current/ddl-alter.html#DDL-ALTER-ADDING-A-COLUMN > > That says: > > > From PostgreSQL 11, adding a column with a constant default value no longer > > means that each row of the table needs to be updated when the ALTER TABLE > > statement is executed. Instead, the default value will be returned the next > > time the row is accessed, and applied when the table is rewritten, making > > the > > ALTER TABLE very fast even on large tables. > > I'm just seeking clarification if this advice is true **even for** new columns > declared with NOT NULL? Yes, providing the default expression is immutable. > Historically, I've had to add new fields on existing big tables with a NULL to > avoid downtime, but it may be different when a DEFAULT is provided? We have the ability to store immutable defaults in the catalogue tables and "fill in the blanks" from there for any tuples that don't have the new column. Since we only can store 1 value per column, it must be a constant, i.e. the same for all rows. > I have used perf to profile the call-chain for adding a NOT NULL column with > a default versus just an ordinary NULL with a default, and they are fairly > similar. > > However, I see these functions being called in both cases: > > - ATRewriteTables > find_composite_type_dependencies > systable_beginscan > index_rescan > btrescan > > And the names raised my eyebrow... I don't have a deep understanding of the > internals here, so it would be great if someone could clarify this for me. I think you're better off staying in userland here and just doing some experiments and using the performance numbers to help give you an idea of what's going on. Using psql: \timing on create table t1 as select a from generate_Series(1,100)a; alter table t1 add column b int null; -- Time: 1.721 ms alter table t1 add column c int not null default 1234; -- Time: 5.450 ms alter table t1 add column d int not null default random(1,10); -- Time: 436.735 ms!! only adding column d required a rewrite, that's because the default expression isn't immutable. If timing isn't confirmation enough, try doing the above with: SET client_min_messages=debug1; Only adding column "d" gives you: DEBUG: rewriting table "t1" David
Tip box on Adding a Column
Hi folks, We have this Tip box under the "Adding a Column" header here: - https://www.postgresql.org/docs/current/ddl-alter.html#DDL-ALTER-ADDING-A-COLUMN That says: > From PostgreSQL 11, adding a column with a constant default value no longer > means that each row of the table needs to be updated when the ALTER TABLE > statement is executed. Instead, the default value will be returned the next > time the row is accessed, and applied when the table is rewritten, making the > ALTER TABLE very fast even on large tables. I'm just seeking clarification if this advice is true **even for** new columns declared with NOT NULL? Historically, I've had to add new fields on existing big tables with a NULL to avoid downtime, but it may be different when a DEFAULT is provided? I have used perf to profile the call-chain for adding a NOT NULL column with a default versus just an ordinary NULL with a default, and they are fairly similar. However, I see these functions being called in both cases: - ATRewriteTables find_composite_type_dependencies systable_beginscan index_rescan btrescan And the names raised my eyebrow... I don't have a deep understanding of the internals here, so it would be great if someone could clarify this for me. Thanks, Marcelo.
fir for row level security
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/ddl-rowsecurity.html Description: Cite When multiple policies apply to a given query, they are combined using either OR (for permissive policies, which are the default) or using AND (for restrictive policies). This is similar to the rule that a given role has the privileges of all roles that they are a member of. end cite Not clear for what is "this is". May be better "The default behaviour is similar..."
PostgreSQL limits
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/17/limits.html Description: I think it can be useful to add the maximum SQL statement length that PostgreSQL can handle in the Appendix K. PostgreSQL Limits. If I'm not wrong it is 2147483648 that is a quite large number, larger than many other RDBMS.
