agora inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedDiscarding the resulting rows
11+ messages / 6 participants
[nested] [flat]
* Discarding the resulting rows
@ 2010-04-26 17:43 Murali M. Krishna <murali1729@yahoo.com>
2010-04-26 19:36 ` Re: Discarding the resulting rows Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 11+ messages in thread
From: Murali M. Krishna @ 2010-04-26 17:43 UTC (permalink / raw)
To: pgsql-hackers
Hello Hackers:
Two questions.
1.
I would like to execute queries such as
select * from part and time the query. But I want to ignore how much time is taken for printing the result to a file or the screen.
Basically, I would like to discard the result rows after doing all the work required to execute the query.
I looked at the documentation and I saw something about using the keyword PERFORM rather than SELECT.
I tried PERFORM * from part;
But this gave me a syntax error.
Please let me know how this can be done.
2.
How do I clear the buffer caches between two query runs?
I believe this is not possible in Postgres. Can someone please confirm this or tell me how it may be done.
Thanks,
Murali.
-----------------------------------------------------------------
Please visit NumberFest.com for educational number puzzles & mind exercises for all ages! And please tell your friends about it. Thank You!
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Discarding the resulting rows
2010-04-26 17:43 Discarding the resulting rows Murali M. Krishna <murali1729@yahoo.com>
@ 2010-04-26 19:36 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-04-26 19:49 ` Re: Discarding the resulting rows Jaime Casanova <jcasanov@systemguards.com.ec>
2010-04-26 20:03 ` Re: Discarding the resulting rows Robert Haas <robertmhaas@gmail.com>
0 siblings, 2 replies; 11+ messages in thread
From: Kevin Grittner @ 2010-04-26 19:36 UTC (permalink / raw)
To: pgsql-hackers; Murali M. Krishna <murali1729@yahoo.com>
"Murali M. Krishna" <murali1729@yahoo.com> wrote:
> Basically, I would like to discard the result rows after doing all
> the work required to execute the query.
I would use EXPLAIN ANALYZE SELECT ...
> I looked at the documentation and I saw something about using the
> keyword PERFORM rather than SELECT.
I don't remember ever seeing anything like that. Do you have a URL?
> How do I clear the buffer caches between two query runs?
The easiest way to clear the PostgreSQL cache is to restart the
service. PostgreSQL goes through the OS cache; so you'll need to
clear that, too. How you do that is dependent on your OS. Of
course, in most real use cases, a significant portion of the
database would be cached, so unless you're dealing with a very
unusual situation, it's hard to see what the value would be of such
a benchmark, unless you're trying to create an artificial "worst
case" scenario for bounding purposes.
Oh, and most serious database servers have 256MB or more of battery
backed cache on the RAID controller; don't forget to deal with that
somehow.
-Kevin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Discarding the resulting rows
2010-04-26 17:43 Discarding the resulting rows Murali M. Krishna <murali1729@yahoo.com>
2010-04-26 19:36 ` Re: Discarding the resulting rows Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-04-26 19:49 ` Jaime Casanova <jcasanov@systemguards.com.ec>
1 sibling, 0 replies; 11+ messages in thread
From: Jaime Casanova @ 2010-04-26 19:49 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-hackers; Murali M. Krishna <murali1729@yahoo.com>
On Mon, Apr 26, 2010 at 2:36 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> "Murali M. Krishna" <murali1729@yahoo.com> wrote:
>
>> I looked at the documentation and I saw something about using the
>> keyword PERFORM rather than SELECT.
>
> I don't remember ever seeing anything like that. Do you have a URL?
>
i guess he is refering to the plpgsql's PERFORM statement, which of
course he can't use outside a plpgsql function...
mmm... well, IIRC, in 9.0 he will be able to do DO $$ PERFORM * FROM
tabla; $$ LANGUAGE plpgsql;
but i think DO is not an EXPLAINing statement
--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Discarding the resulting rows
2010-04-26 17:43 Discarding the resulting rows Murali M. Krishna <murali1729@yahoo.com>
2010-04-26 19:36 ` Re: Discarding the resulting rows Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-04-26 20:03 ` Robert Haas <robertmhaas@gmail.com>
2010-04-26 20:16 ` Re: Discarding the resulting rows Jaime Casanova <jcasanov@systemguards.com.ec>
2010-04-26 20:24 ` Re: Discarding the resulting rows Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 2 replies; 11+ messages in thread
From: Robert Haas @ 2010-04-26 20:03 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-hackers; Murali M. Krishna <murali1729@yahoo.com>
On Mon, Apr 26, 2010 at 3:36 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> "Murali M. Krishna" <murali1729@yahoo.com> wrote:
>
>> Basically, I would like to discard the result rows after doing all
>> the work required to execute the query.
>
> I would use EXPLAIN ANALYZE SELECT ...
There's some overhead to that, of course.
....Robert
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Discarding the resulting rows
2010-04-26 17:43 Discarding the resulting rows Murali M. Krishna <murali1729@yahoo.com>
2010-04-26 19:36 ` Re: Discarding the resulting rows Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-04-26 20:03 ` Re: Discarding the resulting rows Robert Haas <robertmhaas@gmail.com>
@ 2010-04-26 20:16 ` Jaime Casanova <jcasanov@systemguards.com.ec>
2010-04-26 20:25 ` Re: Discarding the resulting rows Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 11+ messages in thread
From: Jaime Casanova @ 2010-04-26 20:16 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-hackers; Murali M. Krishna <murali1729@yahoo.com>
On Mon, Apr 26, 2010 at 3:03 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Mon, Apr 26, 2010 at 3:36 PM, Kevin Grittner
> <Kevin.Grittner@wicourts.gov> wrote:
>> "Murali M. Krishna" <murali1729@yahoo.com> wrote:
>>
>>> Basically, I would like to discard the result rows after doing all
>>> the work required to execute the query.
>>
>> I would use EXPLAIN ANALYZE SELECT ...
>
> There's some overhead to that, of course.
>
he could see the "actual time" in the very first row of the EXPLAIN
ANALYZE... isn't that a value that is more close to what the OP is
looking for?
--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Discarding the resulting rows
2010-04-26 17:43 Discarding the resulting rows Murali M. Krishna <murali1729@yahoo.com>
2010-04-26 19:36 ` Re: Discarding the resulting rows Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-04-26 20:03 ` Re: Discarding the resulting rows Robert Haas <robertmhaas@gmail.com>
2010-04-26 20:16 ` Re: Discarding the resulting rows Jaime Casanova <jcasanov@systemguards.com.ec>
@ 2010-04-26 20:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-04-26 23:40 ` Re: Discarding the resulting rows Murali M. Krishna <murali1729@yahoo.com>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2010-04-26 20:25 UTC (permalink / raw)
To: Jaime Casanova <jcasanov@systemguards.com.ec>; +Cc: Robert Haas <robertmhaas@gmail.com>; Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-hackers; Murali M. Krishna <murali1729@yahoo.com>
Jaime Casanova <jcasanov@systemguards.com.ec> writes:
> On Mon, Apr 26, 2010 at 3:03 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>> On Mon, Apr 26, 2010 at 3:36 PM, Kevin Grittner
>> <Kevin.Grittner@wicourts.gov> wrote:
>>> I would use EXPLAIN ANALYZE SELECT ...
>>
>> There's some overhead to that, of course.
> he could see the "actual time" in the very first row of the EXPLAIN
> ANALYZE... isn't that a value that is more close to what the OP is
> looking for?
Well, it will include the instrumentation overhead of EXPLAIN ANALYZE,
which can be nontrivial depending on your hardware and the query plan.
On the other hand, EXPLAIN skips the cost of converting the result data
to text form, not to mention the network overhead of delivering it; so
in another sense it's underestimating the work involved.
I guess the real question is exactly what the OP is hoping to measure
and why.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Discarding the resulting rows
2010-04-26 17:43 Discarding the resulting rows Murali M. Krishna <murali1729@yahoo.com>
2010-04-26 19:36 ` Re: Discarding the resulting rows Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-04-26 20:03 ` Re: Discarding the resulting rows Robert Haas <robertmhaas@gmail.com>
2010-04-26 20:16 ` Re: Discarding the resulting rows Jaime Casanova <jcasanov@systemguards.com.ec>
2010-04-26 20:25 ` Re: Discarding the resulting rows Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-04-26 23:40 ` Murali M. Krishna <murali1729@yahoo.com>
0 siblings, 0 replies; 11+ messages in thread
From: Murali M. Krishna @ 2010-04-26 23:40 UTC (permalink / raw)
To: Jaime Casanova <jcasanov@systemguards.com.ec>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Robert Haas <robertmhaas@gmail.com>; Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-hackers
Hello All:
The optimizer assumes that data is disk resident when computing the cost of a query plan.
I am trying to ascertain what the correlation is between times and costs of some benchmark queries to see how good the cost model is.
Since I have more than 100 queries, it would be painful to stop and start the server each time to force all the buffer pages out. Also, some of these queries have large number of result rows. I don't want the time to be skewed by the output time.
Cheers,
Murali.
-----------------------------------------------------------------
Please visit NumberFest.com for educational number puzzles & mind exercises for all ages! And please tell your friends about it. Thank You!
--- On Mon, 4/26/10, Tom Lane <tgl@sss.pgh.pa.us> wrote:
From: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: [HACKERS] Discarding the resulting rows
To: "Jaime Casanova" <jcasanov@systemguards.com.ec>
Cc: "Robert Haas" <robertmhaas@gmail.com>, "Kevin Grittner" <Kevin.Grittner@wicourts.gov>, pgsql-hackers@postgresql.org, "Murali M. Krishna" <murali1729@yahoo.com>
Date: Monday, April 26, 2010, 1:25 PM
Jaime Casanova <jcasanov@systemguards.com.ec> writes:
> On Mon, Apr 26, 2010 at 3:03 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>> On Mon, Apr 26, 2010 at 3:36 PM, Kevin Grittner
>> <Kevin.Grittner@wicourts.gov> wrote:
>>> I would use EXPLAIN ANALYZE SELECT ...
>>
>> There's some overhead to that, of course.
> he could see the "actual time" in the very first row of the EXPLAIN
> ANALYZE... isn't that a value that is more close to what the OP is
> looking for?
Well, it will include the instrumentation overhead of EXPLAIN ANALYZE,
which can be nontrivial depending on your hardware and the query plan.
On the other hand, EXPLAIN skips the cost of converting the result data
to text form, not to mention the network overhead of delivering it; so
in another sense it's underestimating the work involved.
I guess the real question is exactly what the OP is hoping to measure
and why.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Discarding the resulting rows
2010-04-26 17:43 Discarding the resulting rows Murali M. Krishna <murali1729@yahoo.com>
2010-04-26 19:36 ` Re: Discarding the resulting rows Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-04-26 20:03 ` Re: Discarding the resulting rows Robert Haas <robertmhaas@gmail.com>
@ 2010-04-26 20:24 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 0 replies; 11+ messages in thread
From: Kevin Grittner @ 2010-04-26 20:24 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: pgsql-hackers; Murali M. Krishna <murali1729@yahoo.com>
Robert Haas <robertmhaas@gmail.com> wrote:
> On Mon, Apr 26, 2010 at 3:36 PM, Kevin Grittner
> <Kevin.Grittner@wicourts.gov> wrote:
>> "Murali M. Krishna" <murali1729@yahoo.com> wrote:
>>
>>> Basically, I would like to discard the result rows after doing
>>> all the work required to execute the query.
>>
>> I would use EXPLAIN ANALYZE SELECT ...
>
> There's some overhead to that, of course.
Good point. At the moment I can't think how to do better, though.
Other suggestions, anyone?
-Kevin
^ permalink raw reply [nested|flat] 11+ messages in thread
* [PATCH v1 2/5] Replace literal 0 with InvalidOffsetNumber for OffsetNumber assignments
@ 2026-02-12 09:12 Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Bertrand Drouvot @ 2026-02-12 09:12 UTC (permalink / raw)
Use the proper constant InvalidOffsetNumber instead of literal 0 when
assigning OffsetNumber variables and struct fields.
This improves code clarity by making it explicit that these are
invalid OffsetNumber values rather than ambiguous zero literals.
Author: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
Discussion:
---
src/backend/access/gin/ginfast.c | 4 ++--
src/backend/access/gin/ginget.c | 2 +-
src/backend/access/gin/gininsert.c | 2 +-
src/backend/access/gin/ginxlog.c | 2 +-
src/backend/access/nbtree/nbtsearch.c | 2 +-
src/backend/storage/page/itemptr.c | 2 +-
6 files changed, 7 insertions(+), 7 deletions(-)
71.7% src/backend/access/gin/
19.8% src/backend/access/nbtree/
8.4% src/backend/storage/page/
diff --git a/src/backend/access/gin/ginfast.c b/src/backend/access/gin/ginfast.c
index 7a6b177977b..dde0f534613 100644
--- a/src/backend/access/gin/ginfast.c
+++ b/src/backend/access/gin/ginfast.c
@@ -108,7 +108,7 @@ writeListPage(Relation index, Buffer buffer,
}
else
{
- GinPageGetOpaque(page)->maxoff = 0;
+ GinPageGetOpaque(page)->maxoff = InvalidOffsetNumber;
}
MarkBufferDirty(buffer);
@@ -720,7 +720,7 @@ processPendingPage(BuildAccumulator *accum, KeyArray *ka,
maxoff = PageGetMaxOffsetNumber(page);
Assert(maxoff >= FirstOffsetNumber);
ItemPointerSetInvalid(&heapptr);
- attrnum = 0;
+ attrnum = InvalidOffsetNumber;
for (i = startoff; i <= maxoff; i = OffsetNumberNext(i))
{
diff --git a/src/backend/access/gin/ginget.c b/src/backend/access/gin/ginget.c
index 6b148e69a8e..9f4be6d2be3 100644
--- a/src/backend/access/gin/ginget.c
+++ b/src/backend/access/gin/ginget.c
@@ -859,7 +859,7 @@ entryGetItem(GinState *ginstate, GinScanEntry entry,
* matchResult is lossy. So, on next call we will get next
* result from TIDBitmap.
*/
- entry->offset = 0;
+ entry->offset = InvalidOffsetNumber;
}
if (entry->isFinished)
break;
diff --git a/src/backend/access/gin/gininsert.c b/src/backend/access/gin/gininsert.c
index 0d63fb4ba27..ae9a8736a8f 100644
--- a/src/backend/access/gin/gininsert.c
+++ b/src/backend/access/gin/gininsert.c
@@ -1562,7 +1562,7 @@ GinBufferReset(GinBuffer *buffer)
*/
buffer->key = (Datum) 0;
- buffer->attnum = 0;
+ buffer->attnum = InvalidOffsetNumber;
buffer->category = 0;
buffer->keylen = 0;
buffer->nitems = 0;
diff --git a/src/backend/access/gin/ginxlog.c b/src/backend/access/gin/ginxlog.c
index b1fee3c281f..5d24862405c 100644
--- a/src/backend/access/gin/ginxlog.c
+++ b/src/backend/access/gin/ginxlog.c
@@ -643,7 +643,7 @@ ginRedoInsertListPage(XLogReaderState *record)
}
else
{
- GinPageGetOpaque(page)->maxoff = 0;
+ GinPageGetOpaque(page)->maxoff = InvalidOffsetNumber;
}
payload = XLogRecGetBlockData(record, 0, &totaltupsize);
diff --git a/src/backend/access/nbtree/nbtsearch.c b/src/backend/access/nbtree/nbtsearch.c
index 32ae0bda892..3711efb97e0 100644
--- a/src/backend/access/nbtree/nbtsearch.c
+++ b/src/backend/access/nbtree/nbtsearch.c
@@ -2223,7 +2223,7 @@ _bt_endpoint(IndexScanDesc scan, ScanDirection dir)
else
{
elog(ERROR, "invalid scan direction: %d", (int) dir);
- start = 0; /* keep compiler quiet */
+ start = InvalidOffsetNumber; /* keep compiler quiet */
}
/*
diff --git a/src/backend/storage/page/itemptr.c b/src/backend/storage/page/itemptr.c
index 546874ebc5f..4843ac84f01 100644
--- a/src/backend/storage/page/itemptr.c
+++ b/src/backend/storage/page/itemptr.c
@@ -90,7 +90,7 @@ ItemPointerInc(ItemPointer pointer)
{
if (blk != InvalidBlockNumber)
{
- off = 0;
+ off = InvalidOffsetNumber;
blk++;
}
}
--
2.34.1
--A7qqtM/5Ou92K2X7
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="v1-0003-Replace-literal-0-with-InvalidAttrNumber-for-Attr.patch"
^ permalink raw reply [nested|flat] 11+ messages in thread
* [PATCH v1 2/5] Replace literal 0 with InvalidOffsetNumber for OffsetNumber assignments
@ 2026-02-12 09:12 Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Bertrand Drouvot @ 2026-02-12 09:12 UTC (permalink / raw)
Use the proper constant InvalidOffsetNumber instead of literal 0 when
assigning OffsetNumber variables and struct fields.
This improves code clarity by making it explicit that these are
invalid OffsetNumber values rather than ambiguous zero literals.
Author: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
Discussion:
---
src/backend/access/gin/ginfast.c | 4 ++--
src/backend/access/gin/ginget.c | 2 +-
src/backend/access/gin/gininsert.c | 2 +-
src/backend/access/gin/ginxlog.c | 2 +-
src/backend/access/nbtree/nbtsearch.c | 2 +-
src/backend/storage/page/itemptr.c | 2 +-
6 files changed, 7 insertions(+), 7 deletions(-)
71.7% src/backend/access/gin/
19.8% src/backend/access/nbtree/
8.4% src/backend/storage/page/
diff --git a/src/backend/access/gin/ginfast.c b/src/backend/access/gin/ginfast.c
index 7a6b177977b..dde0f534613 100644
--- a/src/backend/access/gin/ginfast.c
+++ b/src/backend/access/gin/ginfast.c
@@ -108,7 +108,7 @@ writeListPage(Relation index, Buffer buffer,
}
else
{
- GinPageGetOpaque(page)->maxoff = 0;
+ GinPageGetOpaque(page)->maxoff = InvalidOffsetNumber;
}
MarkBufferDirty(buffer);
@@ -720,7 +720,7 @@ processPendingPage(BuildAccumulator *accum, KeyArray *ka,
maxoff = PageGetMaxOffsetNumber(page);
Assert(maxoff >= FirstOffsetNumber);
ItemPointerSetInvalid(&heapptr);
- attrnum = 0;
+ attrnum = InvalidOffsetNumber;
for (i = startoff; i <= maxoff; i = OffsetNumberNext(i))
{
diff --git a/src/backend/access/gin/ginget.c b/src/backend/access/gin/ginget.c
index 6b148e69a8e..9f4be6d2be3 100644
--- a/src/backend/access/gin/ginget.c
+++ b/src/backend/access/gin/ginget.c
@@ -859,7 +859,7 @@ entryGetItem(GinState *ginstate, GinScanEntry entry,
* matchResult is lossy. So, on next call we will get next
* result from TIDBitmap.
*/
- entry->offset = 0;
+ entry->offset = InvalidOffsetNumber;
}
if (entry->isFinished)
break;
diff --git a/src/backend/access/gin/gininsert.c b/src/backend/access/gin/gininsert.c
index 0d63fb4ba27..ae9a8736a8f 100644
--- a/src/backend/access/gin/gininsert.c
+++ b/src/backend/access/gin/gininsert.c
@@ -1562,7 +1562,7 @@ GinBufferReset(GinBuffer *buffer)
*/
buffer->key = (Datum) 0;
- buffer->attnum = 0;
+ buffer->attnum = InvalidOffsetNumber;
buffer->category = 0;
buffer->keylen = 0;
buffer->nitems = 0;
diff --git a/src/backend/access/gin/ginxlog.c b/src/backend/access/gin/ginxlog.c
index b1fee3c281f..5d24862405c 100644
--- a/src/backend/access/gin/ginxlog.c
+++ b/src/backend/access/gin/ginxlog.c
@@ -643,7 +643,7 @@ ginRedoInsertListPage(XLogReaderState *record)
}
else
{
- GinPageGetOpaque(page)->maxoff = 0;
+ GinPageGetOpaque(page)->maxoff = InvalidOffsetNumber;
}
payload = XLogRecGetBlockData(record, 0, &totaltupsize);
diff --git a/src/backend/access/nbtree/nbtsearch.c b/src/backend/access/nbtree/nbtsearch.c
index 32ae0bda892..3711efb97e0 100644
--- a/src/backend/access/nbtree/nbtsearch.c
+++ b/src/backend/access/nbtree/nbtsearch.c
@@ -2223,7 +2223,7 @@ _bt_endpoint(IndexScanDesc scan, ScanDirection dir)
else
{
elog(ERROR, "invalid scan direction: %d", (int) dir);
- start = 0; /* keep compiler quiet */
+ start = InvalidOffsetNumber; /* keep compiler quiet */
}
/*
diff --git a/src/backend/storage/page/itemptr.c b/src/backend/storage/page/itemptr.c
index 546874ebc5f..4843ac84f01 100644
--- a/src/backend/storage/page/itemptr.c
+++ b/src/backend/storage/page/itemptr.c
@@ -90,7 +90,7 @@ ItemPointerInc(ItemPointer pointer)
{
if (blk != InvalidBlockNumber)
{
- off = 0;
+ off = InvalidOffsetNumber;
blk++;
}
}
--
2.34.1
--A7qqtM/5Ou92K2X7
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="v1-0003-Replace-literal-0-with-InvalidAttrNumber-for-Attr.patch"
^ permalink raw reply [nested|flat] 11+ messages in thread
* [PATCH v2 1/2] Re-read subscription state after lock in AlterSubscription
@ 2026-07-03 05:17 Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Bertrand Drouvot @ 2026-07-03 05:17 UTC (permalink / raw)
AlterSubscription() reads the subscription's catalog state via GetSubscription()
before acquiring AccessExclusiveLock on the subscription object. A concurrent
session that commits a DROP or ALTER between the read and the lock acquisition
leaves the other session acting with stale information once it unblocks.
Fix by:
- Re-reading the subscription tuple after LockSharedObject() and refreshing the
Subscription struct.
- Moving the local variable assignments to after the re-read.
- Re-checking the password_required privilege restriction after the re-read.
Remarks:
1/ not re-checking password_required after the re-read would still produce a
"tuple concurrently updated" error, but re-checking it allows us to display a
better error message.
2/ the ownership check is intentionally not re-done after the lock because
AlterSubscriptionOwner() does not take AccessExclusiveLock on the subscription
object: it only takes RowExclusiveLock on the pg_subscription catalog table.
This means ownership can change regardless of our lock, making a re-check after
lock acquisition pointless. The existing "tuple concurrently updated" error from
CatalogTupleUpdate() already provides a protection if ownership changes
concurrently.
Author: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
Reviewed-by: Dilip Kumar <dilipbalaut@gmail.com>
Reviewed-by: Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com>
Discussion: https://postgr.es/m/akZUpiDa1UfmzYxL%40bdtpg
---
src/backend/commands/subscriptioncmds.c | 41 ++++++++++++++++++++++---
1 file changed, 36 insertions(+), 5 deletions(-)
100.0% src/backend/commands/
diff --git a/src/backend/commands/subscriptioncmds.c b/src/backend/commands/subscriptioncmds.c
index 4292e7fb8f4..be03b3eb7e1 100644
--- a/src/backend/commands/subscriptioncmds.c
+++ b/src/backend/commands/subscriptioncmds.c
@@ -1695,11 +1695,6 @@ AlterSubscription(ParseState *pstate, AlterSubscriptionStmt *stmt,
*/
sub = GetSubscription(subid, false, orig_conninfo_needed, false);
- retain_dead_tuples = sub->retaindeadtuples;
- origin = sub->origin;
- max_retention = sub->maxretention;
- retention_active = sub->retentionactive;
-
/*
* Don't allow non-superuser modification of a subscription with
* password_required=false.
@@ -1713,6 +1708,42 @@ AlterSubscription(ParseState *pstate, AlterSubscriptionStmt *stmt,
/* Lock the subscription so nobody else can do anything with it. */
LockSharedObject(SubscriptionRelationId, subid, 0, AccessExclusiveLock);
+ /*
+ * Re-read the subscription tuple after acquiring the lock. A concurrent
+ * DROP or ALTER may have committed before we acquired the lock.
+ */
+ heap_freetuple(tup);
+ tup = SearchSysCacheCopy2(SUBSCRIPTIONNAME, ObjectIdGetDatum(MyDatabaseId),
+ CStringGetDatum(stmt->subname));
+
+ if (!HeapTupleIsValid(tup))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("subscription \"%s\" does not exist",
+ stmt->subname)));
+
+ form = (Form_pg_subscription) GETSTRUCT(tup);
+
+ /* Refresh the subscription. */
+ pfree(sub);
+ sub = GetSubscription(subid, false, orig_conninfo_needed, false);
+
+ /*
+ * Re-check whether a non-superuser is allowed to alter this subscription.
+ * A concurrent ALTER may have set password_required=false while we were
+ * waiting for the lock.
+ */
+ if (!sub->passwordrequired && !superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("password_required=false is superuser-only"),
+ errhint("Subscriptions with the password_required option set to false may only be created or modified by the superuser.")));
+
+ retain_dead_tuples = sub->retaindeadtuples;
+ origin = sub->origin;
+ max_retention = sub->maxretention;
+ retention_active = sub->retentionactive;
+
/* Form a new tuple. */
memset(values, 0, sizeof(values));
memset(nulls, false, sizeof(nulls));
--
2.34.1
--IX1d9TnmxjGKMYcM
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="v2-0002-Re-read-subscription-state-after-lock-in-DropSubs.patch"
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2026-07-03 05:17 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-04-26 17:43 Discarding the resulting rows Murali M. Krishna <murali1729@yahoo.com>
2010-04-26 19:36 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-04-26 19:49 ` Jaime Casanova <jcasanov@systemguards.com.ec>
2010-04-26 20:03 ` Robert Haas <robertmhaas@gmail.com>
2010-04-26 20:16 ` Jaime Casanova <jcasanov@systemguards.com.ec>
2010-04-26 20:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-04-26 23:40 ` Murali M. Krishna <murali1729@yahoo.com>
2010-04-26 20:24 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2026-02-12 09:12 [PATCH v1 2/5] Replace literal 0 with InvalidOffsetNumber for OffsetNumber assignments Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
2026-02-12 09:12 [PATCH v1 2/5] Replace literal 0 with InvalidOffsetNumber for OffsetNumber assignments Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
2026-07-03 05:17 [PATCH v2 1/2] Re-read subscription state after lock in AlterSubscription Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox