Add some tests for pg_stat_statements compatibility verification under contrib

Started by Erica Zhangalmost 5 years ago8 messages

ericazhangy@qq.com

almost 5 years ago

1 attachment(s)

Hi All,
On the master branch, it is possible to install multiple versions of pg_stat_statements with CREATE EXTENSION, but all the tests in sql/ on look at the latest version available, without testing past compatibility.

Since we support to install lowest version 1.4 currently, add some tests to verify compatibility, upgrade from lower versions of pg_stat_statements.

Attachments:

add_test_for_pg_stat_statements.patchapplication/octet-stream; charset=ISO-8859-1; name=add_test_for_pg_stat_statements.patchDownload

diff --git a/contrib/pg_stat_statements/expected/pg_stat_statements.out b/contrib/pg_stat_statements/expected/pg_stat_statements.out
index 16158525ca..cde6f29444 100644
--- a/contrib/pg_stat_statements/expected/pg_stat_statements.out
+++ b/contrib/pg_stat_statements/expected/pg_stat_statements.out
@@ -877,3 +877,137 @@ SELECT dealloc FROM pg_stat_statements_info;
 (1 row)

 DROP EXTENSION pg_stat_statements;
+--
+-- Verify compatible with older versions.
+-- Currently the lowest version from which upgrade is supported is 1.4.
+--
+CREATE EXTENSION pg_stat_statements with version '1.4';
+SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements' and installed_version = '1.4';
+        name        | default_version | installed_version |                                comment
+--------------------+-----------------+-------------------+------------------------------------------------------------------------
+ pg_stat_statements | 1.9             | 1.4               | track planning and execution statistics of all SQL statements executed
+(1 row)
+
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset
+--------------------------
+
+(1 row)
+
+SELECT count(query) FROM pg_stat_statements;
+ count
+-------
+     2
+(1 row)
+
+---
+--- Upgrade extension to 1.5
+---
+AlTER EXTENSION pg_stat_statements update to '1.5';
+SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements' and installed_version = '1.5';
+        name        | default_version | installed_version |                                comment
+--------------------+-----------------+-------------------+------------------------------------------------------------------------
+ pg_stat_statements | 1.9             | 1.5               | track planning and execution statistics of all SQL statements executed
+(1 row)
+
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset
+--------------------------
+
+(1 row)
+
+SELECT count(query) FROM pg_stat_statements;
+ count
+-------
+     2
+(1 row)
+
+---
+--- Upgrade extension to 1.6
+---
+AlTER EXTENSION pg_stat_statements update to '1.6';
+SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements' and installed_version = '1.6';
+        name        | default_version | installed_version |                                comment
+--------------------+-----------------+-------------------+------------------------------------------------------------------------
+ pg_stat_statements | 1.9             | 1.6               | track planning and execution statistics of all SQL statements executed
+(1 row)
+
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset
+--------------------------
+
+(1 row)
+
+SELECT count(query) FROM pg_stat_statements;
+ count
+-------
+     2
+(1 row)
+
+---
+--- Upgrade extension to 1.7
+---
+AlTER EXTENSION pg_stat_statements update to '1.7';
+SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements' and installed_version = '1.7';
+        name        | default_version | installed_version |                                comment
+--------------------+-----------------+-------------------+------------------------------------------------------------------------
+ pg_stat_statements | 1.9             | 1.7               | track planning and execution statistics of all SQL statements executed
+(1 row)
+
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset
+--------------------------
+
+(1 row)
+
+SELECT count(query) FROM pg_stat_statements;
+ count
+-------
+     2
+(1 row)
+
+---
+--- Upgrade extension to 1.8
+---
+AlTER EXTENSION pg_stat_statements update to '1.8';
+SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements' and installed_version = '1.8';
+        name        | default_version | installed_version |                                comment
+--------------------+-----------------+-------------------+------------------------------------------------------------------------
+ pg_stat_statements | 1.9             | 1.8               | track planning and execution statistics of all SQL statements executed
+(1 row)
+
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset
+--------------------------
+
+(1 row)
+
+SELECT count(plans) FROM pg_stat_statements;
+ count
+-------
+     2
+(1 row)
+
+---
+--- Upgrade extension to 1.9
+---
+AlTER EXTENSION pg_stat_statements update to '1.9';
+SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements' and installed_version = '1.9';
+        name        | default_version | installed_version |                                comment
+--------------------+-----------------+-------------------+------------------------------------------------------------------------
+ pg_stat_statements | 1.9             | 1.9               | track planning and execution statistics of all SQL statements executed
+(1 row)
+
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset
+--------------------------
+
+(1 row)
+
+SELECT dealloc FROM pg_stat_statements_info;
+ dealloc
+---------
+       0
+(1 row)
+
+DROP EXTENSION pg_stat_statements;
diff --git a/contrib/pg_stat_statements/sql/pg_stat_statements.sql b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
index 6f58d9d0f6..98d8a051b6 100644
--- a/contrib/pg_stat_statements/sql/pg_stat_statements.sql
+++ b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
@@ -365,3 +365,54 @@ SELECT pg_stat_statements_reset();
 SELECT dealloc FROM pg_stat_statements_info;

 DROP EXTENSION pg_stat_statements;
+
+--
+-- Verify compatible with older versions.
+-- Currently the lowest version from which upgrade is supported is 1.4.
+--
+CREATE EXTENSION pg_stat_statements with version '1.4';
+SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements' and installed_version = '1.4';
+SELECT pg_stat_statements_reset();
+SELECT count(query) FROM pg_stat_statements;
+
+---
+--- Upgrade extension to 1.5
+---
+AlTER EXTENSION pg_stat_statements update to '1.5';
+SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements' and installed_version = '1.5';
+SELECT pg_stat_statements_reset();
+SELECT count(query) FROM pg_stat_statements;
+
+---
+--- Upgrade extension to 1.6
+---
+AlTER EXTENSION pg_stat_statements update to '1.6';
+SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements' and installed_version = '1.6';
+SELECT pg_stat_statements_reset();
+SELECT count(query) FROM pg_stat_statements;
+
+---
+--- Upgrade extension to 1.7
+---
+AlTER EXTENSION pg_stat_statements update to '1.7';
+SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements' and installed_version = '1.7';
+SELECT pg_stat_statements_reset();
+SELECT count(query) FROM pg_stat_statements;
+
+---
+--- Upgrade extension to 1.8
+---
+AlTER EXTENSION pg_stat_statements update to '1.8';
+SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements' and installed_version = '1.8';
+SELECT pg_stat_statements_reset();
+SELECT count(plans) FROM pg_stat_statements;
+
+---
+--- Upgrade extension to 1.9
+---
+AlTER EXTENSION pg_stat_statements update to '1.9';
+SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements' and installed_version = '1.9';
+SELECT pg_stat_statements_reset();
+SELECT dealloc FROM pg_stat_statements_info;
+
+DROP EXTENSION pg_stat_statements;

Julien Rouhaud

rjuju123@gmail.com

almost 5 years ago

In reply to: Erica Zhang (#1)

Re: Add some tests for pg_stat_statements compatibility verification under contrib

Hi,

On Tue, Mar 09, 2021 at 11:35:14AM +0800, Erica Zhang wrote:

Hi All,
On the master branch, it is possible to install multiple versions of pg_stat_statements with CREATE EXTENSION, but all the tests in sql/ on look at the latest version available, without testing past compatibility.

Since we support to install lowest version 1.4 currently, add some tests to verify compatibility, upgrade from lower versions of pg_stat_statements.

The upgrade scripts are already tested as postgres will install 1.4 and perform
all upgrades to reach the default version.

But an additional thing being tested here is the ABI compatibility when there's
a mismatch between the library and the SQL definition, which seems like a
reasonable thing to test.

Looking at the patch:

+SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements' and installed_version = '1.4';

Julien Rouhaud

rjuju123@gmail.com

almost 5 years ago

In reply to: Erica Zhang (#1)

Re: Add some tests for pg_stat_statements compatibility verification under contrib

Hi Erica,

On Wed, Mar 10, 2021 at 11:14:52AM +0800, Erica Zhang wrote:

Hi Julien,
Thanks a lot for the quick review. Please see my answer below in blue. Attached is the new patch.

Thanks!

The upgrade scripts are already tested as postgres will install 1.4 and perform
all upgrades to reach the default version.

Thanks for pointing that the upgrades paths are covered by upgrade scripts tests. Since I don't need to test the upgrade, I will test the installation of different versions directly, any concern?

I think you should keep your previous approach. The result will be the same
but it will consume less resources for that which is always good.

+SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements' and installed_version = '1.4';

What is this supposed to test?  All those tests will break every time we change
the default version, which will add maintenance efforts.  It could be good to
have one test breaking when changing the version to remind us to add a test for
the new version, but not more.

Here I just want to verify that "installed" version is the expected version. But we do have the issue as you mentioned which will add maintenance efforts.

So I prefer to keep one test as now which can remind us to add a new version. As for others, just to check the count(*) to make sure installation is success.
Such as SELECT count(*) FROM pg_available_extensions WHERE name = 'pg_stat_statements' and installed_version = '1.4'; What do you think?

How about tweaking your previous query so only the last execution fails when
pg_stat_statements default version is updated? Something like:

SELECT installed_version = default_version, installed_version
FROM pg_available_extensions
WHERE name = 'pg_stat_statements';

This way the same query can be reused for both older versions and current
version.

Also, can you register your patch for the next commitfest at
https://commitfest.postgresql.org/33/, to make sure it won't be forgotten?

Import Notes

Reply to msg id not found: tencent_A7EB5CAC90CA14BA65FA173D0B1A8FBC6F09@qq.com

Erica Zhang

ericazhangy@qq.com

almost 5 years ago

In reply to: Julien Rouhaud (#3)

1 attachment(s)

Re: Add some tests for pg_stat_statements compatibility verification under contrib

Hi Julien,
Thanks a lot for the quick review. Please see my answer below in blue. Attached is the new patch.

------------------ Original ------------------
From: "Julien Rouhaud" <rjuju123@gmail.com>;
Date: Tue, Mar 9, 2021 05:09 PM
To: "Erica Zhang"<ericazhangy@qq.com>;
Cc: "pgsql-hackers"<pgsql-hackers@postgresql.org>;
Subject: Re: Add some tests for pg_stat_statements compatibility verification under contrib

Hi,

On Tue, Mar 09, 2021 at 11:35:14AM +0800, Erica Zhang wrote:
> Hi All,
> On the master branch, it is possible to install multiple versions of pg_stat_statements with CREATE EXTENSION, but all the tests in sql/ on look at the latest version available, without testing past compatibility.
>
> Since we support to install lowest version 1.4 currently, add some tests to verify compatibility, upgrade from lower versions of pg_stat_statements.

The upgrade scripts are already tested as postgres will install 1.4 and perform
all upgrades to reach the default version.
Thanks for pointing that the upgrades paths are covered by upgrade scripts tests. So I don't need to verify the upgrade, I will test the installation of different versions directly, any concern?

But an additional thing being tested here is the ABI compatibility when there's
a mismatch between the library and the SQL definition, which seems like a
reasonable thing to test.

Looking at the patch:

+SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements' and installed_version = '1.4';

What is this supposed to test?  All those tests will break every time we change
the default version, which will add maintenance efforts.  It could be good to
have one test breaking when changing the version to remind us to add a test for
the new version, but not more.
Here I just want to verify that "installed" version is the expected version. But we do have the issue as you mentioned which will add maintenance efforts.

So I prefer to keep one test as now which can remind us to add a new version. As for others, just to check the count(*) to make sure installation is success.
Such as SELECT count(*) FROM pg_available_extensions WHERE name = 'pg_stat_statements' and installed_version = '1.4'; What do you think?

Attachments:

v2_add_test_for_pg_stat_statements.patchapplication/octet-stream; charset=ISO-8859-1; name=v2_add_test_for_pg_stat_statements.patchDownload

commit c1a1216b7c7a7db132c954a29a3d91d00c7e918c
Author: Erica Zhang <ericazhangy@qq.com>
Date:   Tue Mar 9 03:06:36 2021 +0000

    Add some tests for pg_stat_statements compatibility verification

diff --git a/contrib/pg_stat_statements/expected/pg_stat_statements.out b/contrib/pg_stat_statements/expected/pg_stat_statements.out
index 16158525ca..13dc9271f6 100644
--- a/contrib/pg_stat_statements/expected/pg_stat_statements.out
+++ b/contrib/pg_stat_statements/expected/pg_stat_statements.out
@@ -877,3 +877,142 @@ SELECT dealloc FROM pg_stat_statements_info;
 (1 row)
 
 DROP EXTENSION pg_stat_statements;
+--
+-- Verify compatible with older versions.
+-- Currently the lowest version supported is 1.4.
+-- 
+CREATE EXTENSION pg_stat_statements with version '1.4';
+SELECT count(*) FROM pg_available_extensions WHERE name = 'pg_stat_statements' and installed_version = '1.4';
+ count 
+-------
+     1
+(1 row)
+
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+SELECT count(query) FROM pg_stat_statements;
+ count 
+-------
+     2
+(1 row)
+
+DROP EXTENSION pg_stat_statements;
+---
+--- Install pg_stat_statements extension version 1.5
+---
+CREATE EXTENSION pg_stat_statements with version '1.5';
+SELECT count(*) FROM pg_available_extensions WHERE name = 'pg_stat_statements' and installed_version = '1.5';
+ count 
+-------
+     1
+(1 row)
+
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+SELECT count(query) FROM pg_stat_statements;
+ count 
+-------
+     2
+(1 row)
+
+DROP EXTENSION pg_stat_statements;
+---
+--- Install pg_stat_statements extension version 1.6
+---
+CREATE EXTENSION pg_stat_statements with version '1.6';
+SELECT count(*) FROM pg_available_extensions WHERE name = 'pg_stat_statements' and installed_version = '1.6';
+ count 
+-------
+     1
+(1 row)
+
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+SELECT count(query) FROM pg_stat_statements;
+ count 
+-------
+     2
+(1 row)
+
+DROP EXTENSION pg_stat_statements;
+---
+--- Install pg_stat_statements extension version 1.7
+---
+CREATE EXTENSION pg_stat_statements with version '1.7';
+SELECT count(*) FROM pg_available_extensions WHERE name = 'pg_stat_statements' and installed_version = '1.7';
+ count 
+-------
+     1
+(1 row)
+
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+SELECT count(query) FROM pg_stat_statements;
+ count 
+-------
+     2
+(1 row)
+
+DROP EXTENSION pg_stat_statements;
+---
+--- Install pg_stat_statements extension version 1.8
+---
+CREATE EXTENSION pg_stat_statements with version '1.8';
+SELECT count(*) FROM pg_available_extensions WHERE name = 'pg_stat_statements' and installed_version = '1.8';
+ count 
+-------
+     1
+(1 row)
+
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+SELECT count(plans) FROM pg_stat_statements;
+ count 
+-------
+     2
+(1 row)
+
+DROP EXTENSION pg_stat_statements;
+---
+--- Install pg_stat_statements extension to 1.9
+---
+CREATE EXTENSION pg_stat_statements with version '1.9';
+SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements' and installed_version = '1.9';
+        name        | default_version | installed_version |                                comment                                 
+--------------------+-----------------+-------------------+------------------------------------------------------------------------
+ pg_stat_statements | 1.9             | 1.9               | track planning and execution statistics of all SQL statements executed
+(1 row)
+
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+SELECT dealloc FROM pg_stat_statements_info;
+ dealloc 
+---------
+       0
+(1 row)
+
+DROP EXTENSION pg_stat_statements;
diff --git a/contrib/pg_stat_statements/sql/pg_stat_statements.sql b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
index 6f58d9d0f6..1fcd5c00db 100644
--- a/contrib/pg_stat_statements/sql/pg_stat_statements.sql
+++ b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
@@ -365,3 +365,59 @@ SELECT pg_stat_statements_reset();
 SELECT dealloc FROM pg_stat_statements_info;
 
 DROP EXTENSION pg_stat_statements;
+
+--
+-- Verify compatible with older versions.
+-- Currently the lowest version supported is 1.4.
+-- 
+CREATE EXTENSION pg_stat_statements with version '1.4';
+SELECT count(*) FROM pg_available_extensions WHERE name = 'pg_stat_statements' and installed_version = '1.4';
+SELECT pg_stat_statements_reset();
+SELECT count(query) FROM pg_stat_statements;
+DROP EXTENSION pg_stat_statements;
+
+---
+--- Install pg_stat_statements extension version 1.5
+---
+CREATE EXTENSION pg_stat_statements with version '1.5';
+SELECT count(*) FROM pg_available_extensions WHERE name = 'pg_stat_statements' and installed_version = '1.5';
+SELECT pg_stat_statements_reset();
+SELECT count(query) FROM pg_stat_statements;
+DROP EXTENSION pg_stat_statements;
+
+---
+--- Install pg_stat_statements extension version 1.6
+---
+CREATE EXTENSION pg_stat_statements with version '1.6';
+SELECT count(*) FROM pg_available_extensions WHERE name = 'pg_stat_statements' and installed_version = '1.6';
+SELECT pg_stat_statements_reset();
+SELECT count(query) FROM pg_stat_statements;
+DROP EXTENSION pg_stat_statements;
+
+---
+--- Install pg_stat_statements extension version 1.7
+---
+CREATE EXTENSION pg_stat_statements with version '1.7';
+SELECT count(*) FROM pg_available_extensions WHERE name = 'pg_stat_statements' and installed_version = '1.7';
+SELECT pg_stat_statements_reset();
+SELECT count(query) FROM pg_stat_statements;
+DROP EXTENSION pg_stat_statements;
+
+---
+--- Install pg_stat_statements extension version 1.8
+---
+CREATE EXTENSION pg_stat_statements with version '1.8';
+SELECT count(*) FROM pg_available_extensions WHERE name = 'pg_stat_statements' and installed_version = '1.8';
+SELECT pg_stat_statements_reset();
+SELECT count(plans) FROM pg_stat_statements;
+DROP EXTENSION pg_stat_statements;
+
+---
+--- Install pg_stat_statements extension to 1.9
+---
+CREATE EXTENSION pg_stat_statements with version '1.9';
+SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements' and installed_version = '1.9';
+SELECT pg_stat_statements_reset();
+SELECT dealloc FROM pg_stat_statements_info;
+
+DROP EXTENSION pg_stat_statements;

Import Notes

Resolved by subject fallback

Erica Zhang

ericazhangy@qq.com

almost 5 years ago

In reply to: Julien Rouhaud (#3)

1 attachment(s)

Re: Add some tests for pg_stat_statements compatibility verification under contrib

Hi Julien,

------------------ Original ------------------
From: "Julien Rouhaud" <rjuju123@gmail.com>;
Date: Wed, Mar 10, 2021 11:35 AM
To: "Erica Zhang"<ericazhangy@qq.com>;
Cc: "pgsql-hackers"<pgsql-hackers@postgresql.org>;
Subject: Re: Add some tests for pg_stat_statements compatibility verification under contrib

Hi Erica,

On Wed, Mar 10, 2021 at 11:14:52AM +0800, Erica Zhang wrote:
> Hi Julien,
> Thanks a lot for the quick review. Please see my answer below in blue. Attached is the new patch.

Thanks!

>> The upgrade scripts are already tested as postgres will install 1.4 and perform
>> all upgrades to reach the default version.
> Thanks for pointing that the upgrades paths are covered by upgrade scripts tests. Since I don't need to test the upgrade, I will test the installation of different versions directly, any concern?

I think you should keep your previous approach.  The result will be the same
but it will consume less resources for that which is always good.
Agreed!

>> +SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements' and installed_version = '1.4';
>>
>>
>> What is this supposed to test?&nbsp; All those tests will break every time we change
>> the default version, which will add maintenance efforts.&nbsp; It could be good to
>> have one test breaking when changing the version to remind us to add a test for
>> the new version, but not more.
> Here I just want to verify that "installed" version is the expected version. But we do have the issue as you mentioned which will add maintenance efforts.
>
> So I prefer to keep one test as now which can remind us to add a new version. As for others, just to check the count(*) to make sure installation is success.
> Such as SELECT count(*) FROM pg_available_extensions WHERE name = 'pg_stat_statements' and installed_version = '1.4'; What do you think?

How about tweaking your previous query so only the last execution fails when
pg_stat_statements default version is updated?  Something like:

SELECT installed_version = default_version, installed_version
FROM pg_available_extensions
WHERE name = 'pg_stat_statements';

This way the same query can be reused for both older versions and current
version.
Yep, it's neater to use the query as you suggested. Thanks!

Also, can you register your patch for the next commitfest at
https://commitfest.postgresql.org/33/, to make sure it won't be forgotten?

Attachments:

v3_add_test_for_pg_stat_statements.patchapplication/octet-stream; charset=ISO-8859-1; name=v3_add_test_for_pg_stat_statements.patchDownload

commit 0f8ce0be5af0a03c80a6b9e3e89519029e66359b
Author: Erica Zhang <ericazhangy@qq.com>
Date:   Tue Mar 9 03:06:36 2021 +0000

    Add some tests for pg_stat_statements compatibility verification

diff --git a/contrib/pg_stat_statements/expected/pg_stat_statements.out b/contrib/pg_stat_statements/expected/pg_stat_statements.out
index 16158525ca..12ee506015 100644
--- a/contrib/pg_stat_statements/expected/pg_stat_statements.out
+++ b/contrib/pg_stat_statements/expected/pg_stat_statements.out
@@ -877,3 +877,137 @@ SELECT dealloc FROM pg_stat_statements_info;
 (1 row)
 
 DROP EXTENSION pg_stat_statements;
+--
+-- Verify compatible with older versions.
+-- Currently the lowest version supported is 1.4.
+-- 
+CREATE EXTENSION pg_stat_statements with version '1.4';
+SELECT installed_version = default_version, installed_version FROM pg_available_extensions WHERE name = 'pg_stat_statements';
+ ?column? | installed_version 
+----------+-------------------
+ f        | 1.4
+(1 row)
+
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+SELECT count(query) FROM pg_stat_statements;
+ count 
+-------
+     2
+(1 row)
+
+---
+--- Install pg_stat_statements extension version 1.5
+---
+AlTER EXTENSION pg_stat_statements update to '1.5';
+SELECT installed_version = default_version, installed_version FROM pg_available_extensions WHERE name = 'pg_stat_statements';
+ ?column? | installed_version 
+----------+-------------------
+ f        | 1.5
+(1 row)
+
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+SELECT count(query) FROM pg_stat_statements;
+ count 
+-------
+     2
+(1 row)
+
+---
+--- Install pg_stat_statements extension version 1.6
+---
+AlTER EXTENSION pg_stat_statements update to '1.6';
+SELECT installed_version = default_version, installed_version FROM pg_available_extensions WHERE name = 'pg_stat_statements';
+ ?column? | installed_version 
+----------+-------------------
+ f        | 1.6
+(1 row)
+
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+SELECT count(query) FROM pg_stat_statements;
+ count 
+-------
+     2
+(1 row)
+
+---
+--- Install pg_stat_statements extension version 1.7
+---
+AlTER EXTENSION pg_stat_statements update to '1.7';
+SELECT installed_version = default_version, installed_version FROM pg_available_extensions WHERE name = 'pg_stat_statements';
+ ?column? | installed_version 
+----------+-------------------
+ f        | 1.7
+(1 row)
+
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+SELECT count(query) FROM pg_stat_statements;
+ count 
+-------
+     2
+(1 row)
+
+---
+--- Install pg_stat_statements extension version 1.8
+---
+AlTER EXTENSION pg_stat_statements update to '1.8';
+SELECT installed_version = default_version, installed_version FROM pg_available_extensions WHERE name = 'pg_stat_statements';
+ ?column? | installed_version 
+----------+-------------------
+ f        | 1.8
+(1 row)
+
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+SELECT count(plans) FROM pg_stat_statements;
+ count 
+-------
+     2
+(1 row)
+
+---
+--- Install pg_stat_statements extension to 1.9
+---
+AlTER EXTENSION pg_stat_statements update to '1.9';
+SELECT installed_version = default_version, installed_version FROM pg_available_extensions WHERE name = 'pg_stat_statements';
+ ?column? | installed_version 
+----------+-------------------
+ t        | 1.9
+(1 row)
+
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+SELECT dealloc FROM pg_stat_statements_info;
+ dealloc 
+---------
+       0
+(1 row)
+
+DROP EXTENSION pg_stat_statements;
diff --git a/contrib/pg_stat_statements/sql/pg_stat_statements.sql b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
index 6f58d9d0f6..a7ce74a18a 100644
--- a/contrib/pg_stat_statements/sql/pg_stat_statements.sql
+++ b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
@@ -365,3 +365,54 @@ SELECT pg_stat_statements_reset();
 SELECT dealloc FROM pg_stat_statements_info;
 
 DROP EXTENSION pg_stat_statements;
+
+--
+-- Verify compatible with older versions.
+-- Currently the lowest version supported is 1.4.
+-- 
+CREATE EXTENSION pg_stat_statements with version '1.4';
+SELECT installed_version = default_version, installed_version FROM pg_available_extensions WHERE name = 'pg_stat_statements';
+SELECT pg_stat_statements_reset();
+SELECT count(query) FROM pg_stat_statements;
+
+---
+--- Install pg_stat_statements extension version 1.5
+---
+AlTER EXTENSION pg_stat_statements update to '1.5';
+SELECT installed_version = default_version, installed_version FROM pg_available_extensions WHERE name = 'pg_stat_statements';
+SELECT pg_stat_statements_reset();
+SELECT count(query) FROM pg_stat_statements;
+
+---
+--- Install pg_stat_statements extension version 1.6
+---
+AlTER EXTENSION pg_stat_statements update to '1.6';
+SELECT installed_version = default_version, installed_version FROM pg_available_extensions WHERE name = 'pg_stat_statements';
+SELECT pg_stat_statements_reset();
+SELECT count(query) FROM pg_stat_statements;
+
+---
+--- Install pg_stat_statements extension version 1.7
+---
+AlTER EXTENSION pg_stat_statements update to '1.7';
+SELECT installed_version = default_version, installed_version FROM pg_available_extensions WHERE name = 'pg_stat_statements';
+SELECT pg_stat_statements_reset();
+SELECT count(query) FROM pg_stat_statements;
+
+---
+--- Install pg_stat_statements extension version 1.8
+---
+AlTER EXTENSION pg_stat_statements update to '1.8';
+SELECT installed_version = default_version, installed_version FROM pg_available_extensions WHERE name = 'pg_stat_statements';
+SELECT pg_stat_statements_reset();
+SELECT count(plans) FROM pg_stat_statements;
+
+---
+--- Install pg_stat_statements extension to 1.9
+---
+AlTER EXTENSION pg_stat_statements update to '1.9';
+SELECT installed_version = default_version, installed_version FROM pg_available_extensions WHERE name = 'pg_stat_statements';
+SELECT pg_stat_statements_reset();
+SELECT dealloc FROM pg_stat_statements_info;
+
+DROP EXTENSION pg_stat_statements;

Michael Paquier

michael@paquier.xyz

over 4 years ago

In reply to: Erica Zhang (#5)

Re: Add some tests for pg_stat_statements compatibility verification under contrib

On Mon, Mar 15, 2021 at 03:05:24PM +0800, Erica Zhang wrote:

This way the same query can be reused for both older versions and current
version.
Yep, it's neater to use the query as you suggested. Thanks!

Also, can you register your patch for the next commitfest at
https://commitfest.postgresql.org/33/, to make sure it won't be forgotten?

I was just looking at your patch, and I think that you should move all
the past compatibility tests into a separate test file, in a way
consistent to what we do in contrib/pageinspect/ for
oldextversions.sql. I would suggest to use the same file names, while
on it.
--
Michael

Michael Paquier

michael@paquier.xyz

over 4 years ago

In reply to: Michael Paquier (#6)

1 attachment(s)

Re: Add some tests for pg_stat_statements compatibility verification under contrib

On Wed, Aug 25, 2021 at 04:16:08PM +0900, Michael Paquier wrote:

I was just looking at your patch, and I think that you should move all
the past compatibility tests into a separate test file, in a way
consistent to what we do in contrib/pageinspect/ for
oldextversions.sql. I would suggest to use the same file names, while
on it.

The current commit fest is ending, and it would be a waste to do
nothing here, so I have looked at what you proposed and reworked it.
The patch was blindly testing pg_stat_statements_reset() in all the
versions bumped with the same query on pg_stat_statements done each
time, which does not help in checking the actual parts of the code
that have changed, and there are two of them:
- pg_stat_statements_reset() execution got authorized for
pg_read_all_stats once in 1.6.
- pg_stat_statements() has been extended in 1.8, so we could just have
one query stressing this function in the tests for <= 1.7.

There is also no need for tests on 1.9, which is the latest version.
Tests for this one should be added once we bump the code to the next
version. At the end I finish with the attached, counting for the
back-and-forth game with pg_read_all_stats.
--
Michael

Attachments:

v4-0001-Add-some-tests-for-past-versions-of-pg_stat_state.patchtext/x-diff; charset=us-asciiDownload

From 9742f2a8b717310ff75ac061f274cbc0502ac8b8 Mon Sep 17 00:00:00 2001
From: Michael Paquier <michael@paquier.xyz>
Date: Thu, 30 Sep 2021 11:10:09 +0900
Subject: [PATCH v4] Add some tests for past versions of pg_stat_statements

---
 contrib/pg_stat_statements/Makefile           |   2 +-
 .../expected/oldextversions.out               | 139 ++++++++++++++++++
 .../pg_stat_statements/sql/oldextversions.sql |  39 +++++
 3 files changed, 179 insertions(+), 1 deletion(-)
 create mode 100644 contrib/pg_stat_statements/expected/oldextversions.out
 create mode 100644 contrib/pg_stat_statements/sql/oldextversions.sql

diff --git a/contrib/pg_stat_statements/Makefile b/contrib/pg_stat_statements/Makefile
index 3ec627b956..7fabd96f38 100644
--- a/contrib/pg_stat_statements/Makefile
+++ b/contrib/pg_stat_statements/Makefile
@@ -16,7 +16,7 @@ PGFILEDESC = "pg_stat_statements - execution statistics of SQL statements"
 LDFLAGS_SL += $(filter -lm, $(LIBS))
 
 REGRESS_OPTS = --temp-config $(top_srcdir)/contrib/pg_stat_statements/pg_stat_statements.conf
-REGRESS = pg_stat_statements
+REGRESS = pg_stat_statements oldextversions
 # Disabled because these tests require "shared_preload_libraries=pg_stat_statements",
 # which typical installcheck users do not have (e.g. buildfarm clients).
 NO_INSTALLCHECK = 1
diff --git a/contrib/pg_stat_statements/expected/oldextversions.out b/contrib/pg_stat_statements/expected/oldextversions.out
new file mode 100644
index 0000000000..515d8fd736
--- /dev/null
+++ b/contrib/pg_stat_statements/expected/oldextversions.out
@@ -0,0 +1,139 @@
+-- test old extension version entry points
+CREATE EXTENSION pg_stat_statements WITH VERSION '1.4';
+-- Execution of pg_stat_statements_reset() is granted to pg_read_all_stats
+-- since 1.5, so this fails.
+SET SESSION AUTHORIZATION pg_read_all_stats;
+SELECT pg_stat_statements_reset();
+ERROR:  permission denied for function pg_stat_statements_reset
+RESET SESSION AUTHORIZATION;
+AlTER EXTENSION pg_stat_statements UPDATE TO '1.5';
+-- Execution of pg_stat_statements_reset() should be granted to
+-- pg_read_all_stats now, so this works.
+SET SESSION AUTHORIZATION pg_read_all_stats;
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+RESET SESSION AUTHORIZATION;
+-- And in 1.6 it got restricted back to superusers.
+AlTER EXTENSION pg_stat_statements UPDATE TO '1.6';
+SET SESSION AUTHORIZATION pg_read_all_stats;
+SELECT pg_stat_statements_reset();
+ERROR:  permission denied for function pg_stat_statements_reset
+RESET SESSION AUTHORIZATION;
+SELECT pg_get_functiondef('pg_stat_statements_reset'::regproc);
+                              pg_get_functiondef                               
+-------------------------------------------------------------------------------
+ CREATE OR REPLACE FUNCTION public.pg_stat_statements_reset()                 +
+  RETURNS void                                                                +
+  LANGUAGE c                                                                  +
+  PARALLEL SAFE                                                               +
+ AS '$libdir/pg_stat_statements', $function$pg_stat_statements_reset$function$+
+ 
+(1 row)
+
+-- New function for pg_stat_statements_reset introduced, still
+-- restricted for non-superusers.
+AlTER EXTENSION pg_stat_statements UPDATE TO '1.7';
+SET SESSION AUTHORIZATION pg_read_all_stats;
+SELECT pg_stat_statements_reset();
+ERROR:  permission denied for function pg_stat_statements_reset
+RESET SESSION AUTHORIZATION;
+SELECT pg_get_functiondef('pg_stat_statements_reset'::regproc);
+                                                       pg_get_functiondef                                                       
+--------------------------------------------------------------------------------------------------------------------------------
+ CREATE OR REPLACE FUNCTION public.pg_stat_statements_reset(userid oid DEFAULT 0, dbid oid DEFAULT 0, queryid bigint DEFAULT 0)+
+  RETURNS void                                                                                                                 +
+  LANGUAGE c                                                                                                                   +
+  PARALLEL SAFE STRICT                                                                                                         +
+ AS '$libdir/pg_stat_statements', $function$pg_stat_statements_reset_1_7$function$                                             +
+ 
+(1 row)
+
+\d pg_stat_statements
+                    View "public.pg_stat_statements"
+       Column        |       Type       | Collation | Nullable | Default 
+---------------------+------------------+-----------+----------+---------
+ userid              | oid              |           |          | 
+ dbid                | oid              |           |          | 
+ queryid             | bigint           |           |          | 
+ query               | text             |           |          | 
+ calls               | bigint           |           |          | 
+ total_time          | double precision |           |          | 
+ min_time            | double precision |           |          | 
+ max_time            | double precision |           |          | 
+ mean_time           | double precision |           |          | 
+ stddev_time         | double precision |           |          | 
+ rows                | bigint           |           |          | 
+ shared_blks_hit     | bigint           |           |          | 
+ shared_blks_read    | bigint           |           |          | 
+ shared_blks_dirtied | bigint           |           |          | 
+ shared_blks_written | bigint           |           |          | 
+ local_blks_hit      | bigint           |           |          | 
+ local_blks_read     | bigint           |           |          | 
+ local_blks_dirtied  | bigint           |           |          | 
+ local_blks_written  | bigint           |           |          | 
+ temp_blks_read      | bigint           |           |          | 
+ temp_blks_written   | bigint           |           |          | 
+ blk_read_time       | double precision |           |          | 
+ blk_write_time      | double precision |           |          | 
+
+SELECT count(*) > 0 AS has_data FROM pg_stat_statements;
+ has_data 
+----------
+ t
+(1 row)
+
+-- New functions and views for pg_stat_statements in 1.8
+AlTER EXTENSION pg_stat_statements UPDATE TO '1.8';
+\d pg_stat_statements
+                    View "public.pg_stat_statements"
+       Column        |       Type       | Collation | Nullable | Default 
+---------------------+------------------+-----------+----------+---------
+ userid              | oid              |           |          | 
+ dbid                | oid              |           |          | 
+ queryid             | bigint           |           |          | 
+ query               | text             |           |          | 
+ plans               | bigint           |           |          | 
+ total_plan_time     | double precision |           |          | 
+ min_plan_time       | double precision |           |          | 
+ max_plan_time       | double precision |           |          | 
+ mean_plan_time      | double precision |           |          | 
+ stddev_plan_time    | double precision |           |          | 
+ calls               | bigint           |           |          | 
+ total_exec_time     | double precision |           |          | 
+ min_exec_time       | double precision |           |          | 
+ max_exec_time       | double precision |           |          | 
+ mean_exec_time      | double precision |           |          | 
+ stddev_exec_time    | double precision |           |          | 
+ rows                | bigint           |           |          | 
+ shared_blks_hit     | bigint           |           |          | 
+ shared_blks_read    | bigint           |           |          | 
+ shared_blks_dirtied | bigint           |           |          | 
+ shared_blks_written | bigint           |           |          | 
+ local_blks_hit      | bigint           |           |          | 
+ local_blks_read     | bigint           |           |          | 
+ local_blks_dirtied  | bigint           |           |          | 
+ local_blks_written  | bigint           |           |          | 
+ temp_blks_read      | bigint           |           |          | 
+ temp_blks_written   | bigint           |           |          | 
+ blk_read_time       | double precision |           |          | 
+ blk_write_time      | double precision |           |          | 
+ wal_records         | bigint           |           |          | 
+ wal_fpi             | bigint           |           |          | 
+ wal_bytes           | numeric          |           |          | 
+
+SELECT pg_get_functiondef('pg_stat_statements_reset'::regproc);
+                                                       pg_get_functiondef                                                       
+--------------------------------------------------------------------------------------------------------------------------------
+ CREATE OR REPLACE FUNCTION public.pg_stat_statements_reset(userid oid DEFAULT 0, dbid oid DEFAULT 0, queryid bigint DEFAULT 0)+
+  RETURNS void                                                                                                                 +
+  LANGUAGE c                                                                                                                   +
+  PARALLEL SAFE STRICT                                                                                                         +
+ AS '$libdir/pg_stat_statements', $function$pg_stat_statements_reset_1_7$function$                                             +
+ 
+(1 row)
+
+DROP EXTENSION pg_stat_statements;
diff --git a/contrib/pg_stat_statements/sql/oldextversions.sql b/contrib/pg_stat_statements/sql/oldextversions.sql
new file mode 100644
index 0000000000..de2ee1a71f
--- /dev/null
+++ b/contrib/pg_stat_statements/sql/oldextversions.sql
@@ -0,0 +1,39 @@
+-- test old extension version entry points
+
+CREATE EXTENSION pg_stat_statements WITH VERSION '1.4';
+-- Execution of pg_stat_statements_reset() is granted to pg_read_all_stats
+-- since 1.5, so this fails.
+SET SESSION AUTHORIZATION pg_read_all_stats;
+SELECT pg_stat_statements_reset();
+RESET SESSION AUTHORIZATION;
+
+AlTER EXTENSION pg_stat_statements UPDATE TO '1.5';
+-- Execution of pg_stat_statements_reset() should be granted to
+-- pg_read_all_stats now, so this works.
+SET SESSION AUTHORIZATION pg_read_all_stats;
+SELECT pg_stat_statements_reset();
+RESET SESSION AUTHORIZATION;
+
+-- And in 1.6 it got restricted back to superusers.
+AlTER EXTENSION pg_stat_statements UPDATE TO '1.6';
+SET SESSION AUTHORIZATION pg_read_all_stats;
+SELECT pg_stat_statements_reset();
+RESET SESSION AUTHORIZATION;
+SELECT pg_get_functiondef('pg_stat_statements_reset'::regproc);
+
+-- New function for pg_stat_statements_reset introduced, still
+-- restricted for non-superusers.
+AlTER EXTENSION pg_stat_statements UPDATE TO '1.7';
+SET SESSION AUTHORIZATION pg_read_all_stats;
+SELECT pg_stat_statements_reset();
+RESET SESSION AUTHORIZATION;
+SELECT pg_get_functiondef('pg_stat_statements_reset'::regproc);
+\d pg_stat_statements
+SELECT count(*) > 0 AS has_data FROM pg_stat_statements;
+
+-- New functions and views for pg_stat_statements in 1.8
+AlTER EXTENSION pg_stat_statements UPDATE TO '1.8';
+\d pg_stat_statements
+SELECT pg_get_functiondef('pg_stat_statements_reset'::regproc);
+
+DROP EXTENSION pg_stat_statements;
-- 
2.33.0

Michael Paquier

michael@paquier.xyz

over 4 years ago

In reply to: Michael Paquier (#7)

Re: Add some tests for pg_stat_statements compatibility verification under contrib

On Thu, Sep 30, 2021 at 11:12:21AM +0900, Michael Paquier wrote:

There is also no need for tests on 1.9, which is the latest version.
Tests for this one should be added once we bump the code to the next
version. At the end I finish with the attached, counting for the
back-and-forth game with pg_read_all_stats.

Done as of 2b0da03.
--
Michael