Misleading description for IPC wait events in PostgreSQL documentation

[SAMEER KUMAR]

Hi,

While preparing for my presentation on PostgreSQL Wait Events at PgConf
India, I was trying to understand *IPC:XactGroupUpdate* in more detail.
PostgreSQL documentation [1] mentions:

> A process is waiting for the group leader to update the transaction
status at the end of a _parallel operation_.

I was looking at `TransactionGroupUpdateXidStatus` in PostgreSQL code
(`clog.c`)
Line `481` [2] sets this wait event.

And after reading the code - my understanding is it does not necessarily
need to be a "_parallel operation_". Or maybe I am just misinterpreting
"parallel operation" in this context. But it is possible for other users to
confuse it with the parallel query feature.

**My understanding is**
In order to avoid `XactSLRULock` being passed between backends, backends
waiting for it will add themselves to the queue [3]. The first backend in
the queue (also the leader) will be the only one to
acquire `XactSLRULock` and update the XID status for all those pids which
are in the queue. This wait event `IPC:XactGroupUpdate` is observed in
other backened processes in queue who are waiting for the group leader to
update the XID status.

I recommend making a change to PostgreSQL docs to clearly indicate what
this wait event means:

> When concurrent processes are trying to update transaction status in the
same page of commit logs, a group leader is assigned to perform the update
and other processes wanting to perform the update wait on this IPC wait
event.

OR

> When multiple backend processes wait on a group leader to update their
transaction status, so that PostgreSQL can avoid passing around the
LwLock `XactSLRULock` between multiple backend processes.


A similar change should be done for `ProcArrayGroupUpdate` to indicate that
the wait event is a result of concurrent backend processes trying to clear
the transaction id (instead of saying "parallel operation").

Thoughts?



[1]
https://www.postgresql.org/docs/current/monitoring-stats.html#WAIT-EVENT-IPC-TABLE

[2]
https://github.com/postgres/postgres/blob/master/src/backend/access/transam/clog.c#L481

[3]
https://github.com/postgres/postgres/blob/master/src/backend/access/transam/clog.c#L399


Thanks,
Sameer
DB Specialist,
Amazon Web Services

Re: Make SSPI documentation clearer

[Tomas Pospisek]

On 13.03.23 01:36, Stephen Frost wrote:

> * PG Doc comments form (nore...@postgresql.org) wrote:
> > Page: https://www.postgresql.org/docs/15/sspi-auth.html
> > Description:
> >
> > The [current SSPI
> > documentation](https://www.postgresql.org/docs/current/sspi-auth.html)
> > reads:
> >
> > "SSPI authentication only works when both server and client are
> > running Windows, or, on non-Windows platforms, when GSSAPI is
> > available."
> >
> > I interpret that phrase like this:
> >
> > * there's a case where both server and client are running Windows
> > * there's a case where both are running non-Windows
>
> Yeah, that phrasing isn't great.
>
> > What about mixed cases? When the client is non-Windows, then can it
> > use SSPI? No, AFAIK not. So I'd suggest to make that phrase above
> > clearer and completely explicit:
>
> SSPI is Windows-specific, yeah.
>
> > "SSPI authentication works when both server and client are running
> > Windows.
> >
> > When the server is on a non-Windows platform then the server must
> > use GSSAPI if it wants to authenticate the client either via
> > Kerberos or via Active Directory. A client on a Windows platform
> > that connects to a non-Windows Postgresql server can either use SSPI
> > (strongly encouraged) or GSS (much more difficult to set up) if it
> > wants to authenticate via Kerberos or Active Directory. A client
> > from a non-Windows platform must use GSS if it wants to authenticate
> > via Kerberos or Active Directory."
>
> Rather than work in negative, I feel like it might make more sense to
> work in positives?  That is, perhaps this instead:
>
> On Windows platforms, SSPI is the default and most commonly used
> mechanism.  Note that an SSPI client can authenticate to a server
> which is using either SSPI or GSSAPI, and a GSSAPI client can
> authenticate to a server which is using either SSPI or GSSAPI.
> Generally speaking, clients and servers on Windows are recommended to
> use SSPI while clients and servers on Unix (non-Windows) platforms use
> GSSAPI.
>
> Stricltly speaking, this is all independent of if AD is being used as
> the KDC or not.

I agree, that's a better formulation. I'd suggest to improve your 
version in three ways:


1. replace "mechanism" with "authentication mechanism"
2. be explicit about Active Directory so there's no doubt wrt to setting
   up authentication
3. be explicit that GSSAPI should be used on non-Windows platform
   servers when one wants clients in an AD domain to seamlessly
   authenticate with the non-Windows server. I'd mention that because if
   the windows clients are *not* in an AD domain then they will *not* be
   able to authenticate to the non-Windows server with GSSAPI.

So finally the whole start of the SSPI paragraph in the docu would look 
like this:



--

21.7. SSPI Authentication

On Windows platforms, SSPI is the default and most commonly used
authentication mechanism.  Note that an SSPI client can authenticate to
a server which is using either SSPI or GSSAPI, and a GSSAPI client can
authenticate to a server which is using either SSPI or GSSAPI.
Generally speaking, clients and servers on Windows are recommended to
use SSPI while clients and servers on Unix (non-Windows) platforms are
recommended to use GSSAPI if they want to interoperate seamlessly with 
Active Directory or Kerberos authentication.


When using Kerberos authentication, SSPI works the same way GSSAPI does; 
see Section 21.6 for details.


--

If the docu is changed in this way, then the phrase "PostgreSQL will use 
SSPI in negotiate mode" is dropped wrt to the previous documentation. I 
have not been able to find out what "SSPI in negotion mode" is and 
therefore if it's in any way relevant to mention that in the docs.


Thanks,
*t

Please improve "Limitations" section for GIN indexes

[Marcin Barczynski]

We've learned the hard way that keys are never removed in GIN indexes,
leading to infinite index size growth in certain scenarios. The PostgreSQL
documentation doesn't mention it even in the "Limitations" section for GIN
indexes (https://www.postgresql.org/docs/15/gin-limit.html).

The only place I found info about the unusual behavior is README file in
the source code:
https://github.com/postgres/postgres/blob/master/src/backend/access/gin/README#L391
.


We keep a tree-like structure in the database and use GIN indexes to find
descendants of a node quickly. The simplified table looks as follows:

node_id | ancestor_ids
|-
 1  | []
 2  | [1]
 15 | [1, 2]

node_id is a sequence, and nodes are often added and removed from the
table: today node_ids are in the range from 1 to 100K but next month it
will be 500K-600K. Because GIN index never removes entry keys, it will
contain keys from 1 to 600K. In our case the GIN index became 2.5 times
larger than the table - that's the hard-way part.

How about sparing others the hard-way part and explicitly mentioning the
unusual behavior in the "Limitations" section for GIN indexes?

Best regards,
Marcin Barczyński