How "apply" the fix for pg_track_settings issue described in #120
mzpqnxow opened this issue · 4 comments
Hello there
For @rjuju regarding pg_track_settings and issue #120
I'm seeing the same issue as was reported in #120 but I'm not sure how to fix it and didn't see any documentation in the pg_track_settings repo that seemed relevant
What I did was:
DROP EXTENSION pg_track_settings- Clone master branch of pg_track_settings
- Copy the *.psql files from the cloned repo over top of those in
/usr/share/postgresql/10/extension/ - Used
CREATE EXTENSION pg_track_settings
This may be rather naive and I admit I didn't look much at all into how pg_track_settings is actually implemented. I should probably read the Debian control data, but thought I might ask the authority first :)
Can you point me in the right direction?
Thanks
I see now there's also a .control file, so I copied that as well, repeated the DROP/CREATE though I'm still seeing the error
This is Ubuntu 18.04 w/PostgreSQL 10, by the way
Do I just need to fiddle with the default version in the control file? Or use a specific tagged branch from the repo?
Sorry, just a bit under-informed about how extensions work (but learning ...)
Hi,
I'm not sure that I get the root problem. Are you saying that for some reason you're using some outdated packaged version of pg_track_settings that doesn't contain the mentioned fix? That would be version 1.0.1 max, from July 2018.
If not, what is the version you have installed, both from the OS package side and postgres side (\dx on all related databases / servers). Also, are you using local or remote mode?
About extension, there are quite a lot of things to consider. Only copying the SQL files and .control file only works for pure SQL extension, like pg_track_settings. But if you overwrite existing file, you have to think about what put those files in the first place. If it's a packaged installation, your modification will either get overwritten again at the next package update or will break the packaging system, so it's usually not recommended to do that. Instead remove the packaged version and install you own. But the real recommended way is to setup a repository that does contain up to date packages. The PGDG repository for Debian/Ubuntu (https://apt.postgresql.org) should contain that, although maybe not for ubuntu 18.04 / pg 10 as your OS has been EOL for about 2 years and your postgres version for about 6 months. Ideally you should upgrade to supported versions for both.
Hi,
I'm not sure that I get the root problem
Yes, I apologize I did not do a great job of explaining it. Bear with me ...
Are you saying that for some reason you're using some outdated packaged version of pg_track_settings that doesn't contain the mentioned fix? That would be version 1.0.1 max, from July 2018.
If not, what is the version you have installed, both from the OS package side and postgres side (\dx on all related databases / servers). Also, are you using local or remote mode?
lsb_release -a / dpkg --list
19:06:26 › dpkg --list | grep -E '(powa|pg-track)'
ii postgresql-10-pg-track-settings 2.0.1-1.pgdg16.04+1 all PostgreSQL extension tracking of configuration settings
ii postgresql-10-powa 4.1.2-1.pgdg16.04+1 ppc64el PostgreSQL Workload Analyzer -- PostgreSQL 10 extension
19:06:24 › lsb_release -a
LSB Version: security-9.20170808ubuntu1-noarch
Distributor ID: Ubuntu
Description: Ubuntu **18.04.6 LTS**
Release: 18.04
Codename: bionic
\dx
[local]:5432 dbadmin@dbsrv=# \dx
List of installed extensions
+--------------------+---------+------------+-------------------------------------------------------------------+
| Name | Version | Schema | Description |
+--------------------+---------+------------+-------------------------------------------------------------------+
| btree_gin | 1.2 | public | support for indexing common datatypes in GIN |
| btree_gist | 1.5 | public | support for indexing common datatypes in GiST |
| citext | 1.4 | public | data type for case-insensitive character strings |
| hypopg | 1.1.3 | public | Hypothetical indexes for PostgreSQL |
| ip4r | 2.4 | public | |
| ltree | 1.1 | public | data type for hierarchical tree-like structures |
| pg_partman | 3.1.2 | public | Extension to manage partitioned tables by time or ID |
| pg_qualstats | 2.0.2 | public | An extension collecting statistics about quals |
| pg_repack | 1.4.2 | public | Reorganize tables in PostgreSQL databases with minimal locks |
| pg_similarity | 1.0 | public | support similarity queries |
| pg_stat_kcache | 2.1.3 | public | Kernel statistics gathering |
| pg_stat_statements | 1.6 | public | track execution statistics of all SQL statements executed |
| pg_track_settings | 2.1.0 | public | Track settings changes |
| pg_trgm | 1.3 | public | text similarity measurement and index searching based on trigrams |
| pg_wait_sampling | 1.1 | public | sampling based statistics of wait events |
| pgstattuple | 1.5 | public | show tuple-level statistics |
| plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language |
| plpython3u | 1.0 | pg_catalog | PL/Python3U untrusted procedural language |
| powa | 4.1.2 | public | PostgreSQL Workload Analyser-core |
+--------------------+---------+------------+-------------------------------------------------------------------+
First, you'll notice that \dx shows pg_track_settings @ version 2.1.0, while the package manager shows 2.0.1-1; this is expected and a result of me modifying /usr/share/postgresql/10/extension/pg_track_settings.control while trying to see if any of the versions addressed the issue (including the one from the Debian-specific tag referenced in the issue). When I gave up yesterday I left it at 2.1.0 for no good reason
About extension, there are quite a lot of things to consider. Only copying the SQL files and .control file only works for pure SQL extension, like pg_track_settings.
This is actually what had me so confused at the start- I had never seen a pure SQL extension and couldn't figure out where the shared library (or C src) was! :)
It's a bit more intuitive to me now (those .control files were previously unknown to me) and I feel foolish for not having looked into it a little further before entering the issue
But if you overwrite existing file, you have to think about what put those files in the first place. If it's a packaged installation, your modification will either get overwritten again at the next package update or will break the packaging system, so it's usually not recommended to do that. Instead remove the packaged version and install you own.
Understood- I decided to modify it in place because there were no newer packages available for it, but you're right- it's a potential for creating a confusing mess when done carelessly and regularly- not something I would typically do, I promise ;)
Along this same line of thinking (but on the more responsible side), before I entered this issue, the first thing I did was sudo apt-get install --reinstall postgresql-10-pg-track-settings. No luck there
But the real recommended way is to setup a repository that does contain up to date packages. The PGDG repository for Debian/Ubuntu (https://apt.postgresql.org) should contain that, although maybe not for ubuntu 18.04 / pg 10 as your OS has been EOL for about 2 years and your postgres version for about 6 months. Ideally you should upgrade to supported versions for both.
After all of this fiddling and taking up some of your time as well, I now realize that when I did the upgrade from 16.04 -> 18.04 some time ago, I temporarily commented out the PGDG repository in the /etc/apt/sources.list.d/ rather than just updating it to pull from "xenial" -> "bionic" - I don't typically do major updates of distributions and I don't trust the Debian/Ubuntu package marking/holding stuff for those sorts of operations- so I removed it because I was afraid it would somehow decide to put PostgreSQL 11 in place and destroy the PostgreSQL installation (which under no circumstances should have been possible, but that's beside the point ...)
I think we can close this for now, if an apt-get install --upgrade postgresql-10-pg-track-settings (which should push it to 2.1.1-3.pgdg18.04+1) doesn't take care of it, I at least now understand well enough how the extension works to further review it myself, and, if necessary, patch the SQL in place
Thank you for your time in reviewing this, I'm sure you have plenty of other things to do on a weekend :>
First, you'll notice that \dx shows pg_track_settings @ version 2.1.0, while the package manager shows 2.0.1-1; this is expected and a result of me modifying /usr/share/postgresql/10/extension/pg_track_settings.control while trying to see if any of the versions addressed the issue (including the one from the Debian-specific tag referenced in the issue). When I gave up yesterday I left it at 2.1.0 for no good reason
Well, in any case you had a version higher than 2.0.0, so the mentioned bugfixes should have already been included. More importantly, there isn't any bugfix on NULL timestamp (or any other bugfix) since 2.0.1, so maybe you actually hit a new bug?
Understood- I decided to modify it in place because there were no newer packages available for it, but you're right- it's a potential for creating a confusing mess when done carelessly and regularly- not something I would typically do, I promise ;)
No worries :) Also I think Debian / Ubuntu is much more forgiving about that than RHEL / Rocky / Fedora.
I think we can close this for now, if an apt-get install --upgrade postgresql-10-pg-track-settings (which should push it to 2.1.1-3.pgdg18.04+1) doesn't take care of it, I at least now understand well enough how the extension works to further review it myself, and, if necessary, patch the SQL in place
Ok! Well if you do find an issue feel free to report it, with or without a patch :)