Transaction wraparound and read committed isolation level

[PG Doc comments form]

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/15/routine-vacuuming.html
Description:

hey guys

thanks for the work you do we really appreciate it. 

In the transaction wraparound section this statement is misleading and got
me really confused. 

“a row version with an insertion XID greater than the current transaction's
XID is “in the future” and should not be visible to the current
transaction“

If the current transaction isolation level is read committed it absolutely
can see rows committed by future transactions with higher XIDs. 

Would be cool to add a note. 

this also bear the question that the wraparound isn’t really a problem with
default isolation level but more for higher levels such as repeatable read
and such. 

please correct me if my understanding is incorrect.

Add missing meson arguments in docs

[Jelte Fennema]

The -Dcassert and -Db_coverage of the meson build didn't show in the
docs that they needed to be passed true or false. All other options
specified the arguments they expected. This patch fixes that.


0001-Add-missing-meson-arguments-in-docs.patch
Description: Binary data

Re: Transaction wraparound and read committed isolation level

[Laurenz Albe]

On Sun, 2023-02-19 at 03:10 +, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
> 
> Page: https://www.postgresql.org/docs/15/routine-vacuuming.html
> Description:
> 
> hey guys
> 
> thanks for the work you do we really appreciate it. 
> 
> In the transaction wraparound section this statement is misleading and got
> me really confused. 
> 
> “a row version with an insertion XID greater than the current transaction's
> XID is “in the future” and should not be visible to the current
> transaction“
> 
> If the current transaction isolation level is read committed it absolutely
> can see rows committed by future transactions with higher XIDs.

You are right, in combination with PlanQualEval you can.

> Would be cool to add a note. 
> 
> this also bear the question that the wraparound isn’t really a problem with
> default isolation level but more for higher levels such as repeatable read
> and such. 
> 
> please correct me if my understanding is incorrect.

Wraparound can be a problem on all isolation levels.  It has to do with
transaction IDs and visibility.

Yours,
Laurenz Albe

Re: February 9th, 2023 Release links to a missing page

[Jonathan S. Katz]

On 2/20/23 8:57 PM, Kirk Wolak wrote:
On Sun, Feb 19, 2023 at 4:55 PM Jonathan S. Katz 


Well, that gives me an idea. In the release announcement, we could
put a
line that says "You can send any corrections or suggestions to the
public pgsql-...@lists.postgresql.org
 mailing list" or something like
that. That way, it would remove the guessing on what to do.

Perfect...  I love this community!


Thanks. As I'd probably forget before the next update announcement, I 
sketched out the draft of it to ensure it included a line[1] around how 
to report corrections/suggestions.


Thanks again!

Jonathan

[1] 
https://git.postgresql.org/gitweb/?p=press.git;a=commitdiff;h=c5d98a4a9caece103205d1a213b11fa96086bd13;hp=9bffe5a98298e034976708a29c88a98a4cec9831


OpenPGP_signature
Description: OpenPGP digital signature

MERGE examples not clear

[PG Doc comments form]

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?

Re: MERGE examples not clear

[David G. Johnston]

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).

David J.

Re: Transaction wraparound and read committed isolation level

[Peter Geoghegan]

On Tue, Feb 21, 2023 at 1:51 AM Laurenz Albe  wrote:
> > If the current transaction isolation level is read committed it absolutely
> > can see rows committed by future transactions with higher XIDs.
>
> You are right, in combination with PlanQualEval you can.

You don't need to bring EvalPlanQual (RC update conflict handling) into it.

All that you need is two statements within the same READ COMMITTED
transaction, combined with a second concurrently executing
transaction. The second transaction need only start after the first
one (giving it a later XID), and then commit before the first
transaction's second statement begins. Each RC statement gets its own
snapshot, so the second statement in the first transaction can see any
effects from the first transaction.

> > this also bear the question that the wraparound isn’t really a problem with
> > default isolation level but more for higher levels such as repeatable read
> > and such.
> >
> > please correct me if my understanding is incorrect.
>
> Wraparound can be a problem on all isolation levels.  It has to do with
> transaction IDs and visibility.

The way that the docs explain wraparound is seriously misleading.

-- 
Peter Geoghegan

Re: Transaction wraparound and read committed isolation level

[Peter Geoghegan]

On Tue, Feb 21, 2023 at 9:46 AM Peter Geoghegan  wrote:
> All that you need is two statements within the same READ COMMITTED
> transaction, combined with a second concurrently executing
> transaction. The second transaction need only start after the first
> one (giving it a later XID), and then commit before the first
> transaction's second statement begins. Each RC statement gets its own
> snapshot, so the second statement in the first transaction can see any
> effects from the first transaction.

Correction: Each RC statement gets its own snapshot, so the second
statement in the first transaction can see any effects from the
*second* (now committed) transaction.

-- 
Peter Geoghegan

Row Level Security Execution within the SQL Evaluation Pipeline

[PG Doc comments form]

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/15/sql-createpolicy.html
Description:

RLS documentation seems to say the user's predicate is evaluated AFTER the
policy is evaluated. This presents to me an issue that I can't wrap my head
around, and seems to confuse others as well.

Setup:
1. We have a policy-enabled table that has millions of rows, with ID as a
primary key. 
2. There exists a row in this table whose ID column is 10.
3. User submits a query: SELECT * FROM MYTABLE WHERE id = 10. 

According to the documentation, the WHERE clause is not evaluated until
AFTER the policy is evaluated, thus there is initially a full table scan of
MYTABLE that returns only the set of rows that the user has access to (via
the policy), THEN the WHERE clause is activated that reduces the row count
to 1 or 0.

This sounds non-performant, and if this is or is not the case, I think it
should be more clearly explained. In addition, a link to a "best practices
using the policy effectively" would be useful, as from reviewing stack
overflow, there is lots of concern over performance of RLS.

Re: Row Level Security Execution within the SQL Evaluation Pipeline

[Stephen Frost]

Greetings,

* PG Doc comments form (nore...@postgresql.org) wrote:
> RLS documentation seems to say the user's predicate is evaluated AFTER the
> policy is evaluated. This presents to me an issue that I can't wrap my head
> around, and seems to confuse others as well.
> 
> Setup:
> 1. We have a policy-enabled table that has millions of rows, with ID as a
> primary key. 
> 2. There exists a row in this table whose ID column is 10.
> 3. User submits a query: SELECT * FROM MYTABLE WHERE id = 10. 
> 
> According to the documentation, the WHERE clause is not evaluated until
> AFTER the policy is evaluated, thus there is initially a full table scan of
> MYTABLE that returns only the set of rows that the user has access to (via
> the policy), THEN the WHERE clause is activated that reduces the row count
> to 1 or 0.
> 
> This sounds non-performant, and if this is or is not the case, I think it
> should be more clearly explained. In addition, a link to a "best practices
> using the policy effectively" would be useful, as from reviewing stack
> overflow, there is lots of concern over performance of RLS.

Functions which are leakproof can be pushed down below the policy
because those functions won't ever leak information about the values
that they might see in the data that the user shouldn't be allowed to
see.

Logically, the WHERE clause still comes after the policy, but with the
leakproof function that backs the '=' operator, we're able to optimize
the query and use the 'id' index that exists.

Of course, RLS isn't going to be free and you can certainly have cases
where you're using a function or operator that isn't leakproof and then
you'll have the issues you describe, or just in general adding on the
conditions of the policy could have performance impacts, but this
specific case isn't going to be an issue.  Note that we do explicitly
perform query optimization *after* adding in the RLS policies into the
query.

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: Add missing meson arguments in docs

[Michael Paquier]

On Tue, Feb 21, 2023 at 10:40:12AM +0100, Jelte Fennema wrote:
> The -Dcassert and -Db_coverage of the meson build didn't show in the
> docs that they needed to be passed true or false. All other options
> specified the arguments they expected. This patch fixes that.

Indeed, values have to be specified after the equal sign of each
option, so applied what you are suggesting here.

-Db_coverage is part of the core options of meson because it is not
listed in meson_options.txt, no?  Documenting the switch is fine, but
it seems like we lack contents here.  The paragraph describing
-Db_coverage links to regress-coverage, still this only has
instructions for ./configure.  Shouldn't we have some docs to show one
how to achieve the same with meson?  I'd guess that a separate section
for meson would make the most sense, for as long as we support both
methods in parallel.

https://wiki.postgresql.org/wiki/Meson tells nothing about that, for
one.
--
Michael


signature.asc
Description: PGP signature