Thread: Cannot turn track_counts on
Hello, all. I have a Postgres sever with the 'track_counts' setting stuck in 'off'. I cannot seem to enable it with either of a. ALTER SYSTEM, b. ALTER DATABASE, c. ALTER USER, d. or plain SET. pg_settings shows: name | track_counts setting | on source | override What does 'override' mean in the 'source' column? How can I find where in the system this setting is overridden? -- () ascii ribbon campaign -- against html e-mail /\ www.asciiribbon.org -- against proprietary attachments
Anton Shepelev <anton.txt@gmail.com> writes:
> pg_settings shows:
> name | track_counts
> setting | on
> source | override
> What does 'override' mean in the 'source' column? How can I
> find where in the system this setting is overridden?
I am fairly certain that there is nothing in core Postgres that
would do that. PGC_S_OVERRIDE is used to lock down the values
of certain variables that shouldn't be allowed to change, but
track_counts surely isn't one of those. And a quick grep
through the code finds nothing applying PGC_S_OVERRIDE to it.
What extensions do you have installed?
regards, tom lane
> On 16 Apr 2025, at 16:53, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Anton Shepelev <anton.txt@gmail.com> writes: >> pg_settings shows: > >> name | track_counts >> setting | on >> source | override > >> What does 'override' mean in the 'source' column? How can I >> find where in the system this setting is overridden? > > I am fairly certain that there is nothing in core Postgres that > would do that. PGC_S_OVERRIDE is used to lock down the values > of certain variables that shouldn't be allowed to change, but > track_counts surely isn't one of those. And a quick grep > through the code finds nothing applying PGC_S_OVERRIDE to it. > > What extensions do you have installed? Also, is this by any chance a managed instance like Amazon RDS or Azure, or is it a local database under your control? -- Daniel Gustafsson
On 4/16/25 02:27, Anton Shepelev wrote: > Hello, all. > > I have a Postgres sever with the 'track_counts' setting > stuck in 'off'. I cannot seem to enable it with either of > a. ALTER SYSTEM, > b. ALTER DATABASE, > c. ALTER USER, > d. or plain SET. > > pg_settings shows: > > name | track_counts > setting | on > source | override This shows a setting of 'on' not the 'off' you mention in the first paragraph. > > What does 'override' mean in the 'source' column? How can I > find where in the system this setting is overridden? > -- Adrian Klaver adrian.klaver@aklaver.com
Adrian Klaver: > Anton Shepelev: > > > have a Postgres sever with the 'track_counts' > > setting stuck in 'off'. > > [...] > > name | track_counts > > setting | on > > source | override > > This shows a setting of 'on' not the 'off' you mention in > the first paragraph. I beg pardon. Having no immediate access to the system in question, I confess to having mocked up those results from an analogous query on our reference system. Here is the actual result from the affected server (db name changed): db=# select * from pg_settings where name = 'track_counts'; -[ RECORD 1 ]---+-------------------------------------------------- name | track_counts setting | off unit | category | Statistics / Query and Index Statistics Collector short_desc | Collects statistics on database activity. extra_desc | context | superuser vartype | bool source | override min_val | max_val | enumvals | boot_val | on reset_val | off sourcefile | sourceline | pending_restart | f It was very wrong of me so to misinform you. -- () ascii ribbon campaign -- against html e-mail /\ www.asciiribbon.org -- against proprietary attachments
Daniel Gustafsson: > Also, is this by any chance a managed instance like Amazon > RDS or Azure, or is it a local database under your > control? It is a normal installation on a Linux machine, and my company has full root access to it over SSH. Because of strict security measures, however, only a certain employee can connect, and only form a certain client machine. I will answer the other questions as soon as I am able to arrange a session at his PC to perform the recommended diagnostic queries. -- () ascii ribbon campaign -- against html e-mail /\ www.asciiribbon.org -- against proprietary attachments
On 4/16/25 09:25, Anton Shepelev wrote: > Adrian Klaver: >> Anton Shepelev: >> >> This shows a setting of 'on' not the 'off' you mention in >> the first paragraph. > > I beg pardon. Having no immediate access to the system in > question, I confess to having mocked up those results from > an analogous query on our reference system. Here is the > actual result from the affected server (db name changed): > > db=# select * from pg_settings where name = 'track_counts'; > -[ RECORD 1 ]---+-------------------------------------------------- > name | track_counts > setting | off > unit | > category | Statistics / Query and Index Statistics Collector > short_desc | Collects statistics on database activity. > extra_desc | > context | superuser > vartype | bool > source | override > min_val | > max_val | > enumvals | > boot_val | on > reset_val | off > sourcefile | > sourceline | > pending_restart | f > > It was very wrong of me so to misinform you. > Per post from Daniel Gustafsson: "Also, is this by any chance a managed instance like Amazon RDS or Azure, or is it a local database under your control?" And from Tom Lane: "What extensions do you have installed?" -- Adrian Klaver adrian.klaver@aklaver.com
On Thu, Apr 17, 2025 at 5:13 AM Anton Shepelev <anton.txt@gmail.com> wrote:
Daniel Gustafsson:
> Also, is this by any chance a managed instance like Amazon
> RDS or Azure, or is it a local database under your
> control?
It is a normal installation on a Linux machine, and my
company has full root access to it over SSH. Because of
strict security measures, however, only a certain employee
can connect,
Better hope he doesn't get hit by a bus, decide to quit, etc.
and only form a certain client machine.
And that specific client machine doesn't break, become corrupted during a Windows Update, get malware, etc.
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
Tom Lane to Anton Shepelev:
> > I have a Postgres sever with the 'track_counts' setting
> > stuck in 'off'. [...]
>
> I am fairly certain that there is nothing in core Postgres
> that would do that. PGC_S_OVERRIDE is used to lock down
> the values of certain variables that shouldn't be allowed
> to change, but track_counts surely isn't one of those.
> And a quick grep through the code finds nothing applying
> PGC_S_OVERRIDE to it.
Thanks for checking it, Tom.
> What extensions do you have installed?
Nothing much:
db=# show shared_preload_libraries;
shared_preload_libraries
---------------------------
online_analyze, plantuner
db=# \dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
--
() ascii ribbon campaign -- against html e-mail
/\ www.asciiribbon.org -- against proprietary attachments
Anton Shepelev <anton.txt@gmail.com> writes:
>> What extensions do you have installed?
> Nothing much:
> db=# show shared_preload_libraries;
> shared_preload_libraries
> ---------------------------
> online_analyze, plantuner
Never heard of either of those, but just from the names, they sound
like they might be things that would take it on themselves to fool
with your stats-collection settings. I'd check their code for
something close to
SetConfigOption("track_counts", ..., PGC_S_OVERRIDE);
regards, tom lane
On 4/17/25 07:23, Tom Lane wrote:
> Anton Shepelev <anton.txt@gmail.com> writes:
>>> What extensions do you have installed?
>
>> Nothing much:
>
>> db=# show shared_preload_libraries;
>> shared_preload_libraries
>> ---------------------------
>> online_analyze, plantuner
>
> Never heard of either of those, but just from the names, they sound
> like they might be things that would take it on themselves to fool
> with your stats-collection settings. I'd check their code for
> something close to
>
> SetConfigOption("track_counts", ..., PGC_S_OVERRIDE);
I found this:
https://github.com/postgrespro/plantuner/blob/master/plantuner.c
Could not see PGC_S_OVERRIDE in it.
For online_analyze the only code I could find is old:
https://github.com/postgrespro/pgwininstall/blob/master/patches/postgresql/9.6/online_analyze.patch
Again no PGC_S_OVERRIDE.
>
> regards, tom lane
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
On 4/17/25 07:05, Anton Shepelev wrote: > Tom Lane to Anton Shepelev: > >>> I have a Postgres sever with the 'track_counts' setting >>> stuck in 'off'. [...] >> >> I am fairly certain that there is nothing in core Postgres >> that would do that. PGC_S_OVERRIDE is used to lock down >> the values of certain variables that shouldn't be allowed >> to change, but track_counts surely isn't one of those. >> And a quick grep through the code finds nothing applying >> PGC_S_OVERRIDE to it. > > Thanks for checking it, Tom. > >> What extensions do you have installed? > > Nothing much: > > db=# show shared_preload_libraries; > shared_preload_libraries > --------------------------- > online_analyze, plantuner Are you running PostgresPro? Both those modules are associated with it: https://postgrespro.com/docs/postgrespro/17/contrib.html If you are you might want to talk to their tech support. > > db=# \dx > List of installed extensions > Name | Version | Schema | Description > ---------+---------+------------+------------------------------ > plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language > -- Adrian Klaver adrian.klaver@aklaver.com
Adrian Klaver to Anton Shepelev: > > db=# show shared_preload_libraries; > > shared_preload_libraries > > --------------------------- > > online_analyze, plantuner > > Are you running PostgresPro? > > Both those modules are associated with it: > > https://postgrespro.com/docs/postgrespro/17/contrib.html Not at all. Whereas `pg_config --version' answers with an irrelevant quip: You need to install postgresql-server-dev-NN for building a server-side extension or libpq-dev for building a client-side application. The version() SQL function returns: PostgreSQL 11.21 (Debian 1:11.21-astra.se6+ci1) on x86_64-pc-linux-gnu, compiled by gcc (AstraLinuxSE 8.3.0-6) 8.3.0, 64-bit I will test if clearing shared_preload_libraries and restarting Postgres has any effect on track_counts, just in case. -- () ascii ribbon campaign -- against html e-mail /\ www.asciiribbon.org -- against proprietary attachments
On 4/17/25 14:23, Anton Shepelev wrote: > Adrian Klaver to Anton Shepelev: > >>> db=# show shared_preload_libraries; >>> shared_preload_libraries >>> --------------------------- >>> online_analyze, plantuner >> >> Are you running PostgresPro? >> >> Both those modules are associated with it: >> >> https://postgrespro.com/docs/postgrespro/17/contrib.html > > Not at all. Whereas `pg_config --version' answers with an > irrelevant quip: The below does not look like a Postgres message to me. > > You need to install postgresql-server-dev-NN for building > a server-side extension or libpq-dev for building a > client-side application. > How was this Postgres instance installed or built? > The version() SQL function returns: > > PostgreSQL 11.21 (Debian 1:11.21-astra.se6+ci1) > on x86_64-pc-linux-gnu, > compiled by gcc (AstraLinuxSE 8.3.0-6) 8.3.0, 64-bit Not seeing how the above is matching up with: https://en.wikipedia.org/wiki/Astra_Linux > > I will test if clearing shared_preload_libraries and > restarting Postgres has any effect on track_counts, just in > case. > -- Adrian Klaver adrian.klaver@aklaver.com
Adrian Klaver to Anton Shepelev: > The below does not look like a Postgres message to me. > > > You need to install postgresql-server-dev-NN for > > building a server-side extension or libpq-dev for > > building a client-side application. Yet that is what we get for `pg_config --version' on the affected system. A nearly identical error message seems to come up while installing psycopg2 -- a Python library for Postgres, e.g.: <https://stackoverflow.com/q/28253681/2862241> <https://www.matheusmello.io/posts/python-you-need-to-install-postgresql-server-dev-x-y-for-building-a-server-side-extensi> I have not the slightest idea why pg_config should print this message, unless it is not a genuine pg_config. I will be checking its binary against the one that works as expected on our reference system. > How was this Postgres instance installed or built? Installed from Astra Linux's native repository: ant@xx:~$ apt list postgresql-11 Listing... Done postgresql-11/stable,stable,now 1:11.21-astra.se6+ci1 amd64 [installed] > > The version() SQL function returns: > > > > PostgreSQL 11.21 (Debian 1:11.21-astra.se6+ci1) > > on x86_64-pc-linux-gnu, > > compiled by gcc (AstraLinuxSE 8.3.0-6) 8.3.0, 64-bit > > Not seeing how the above is matching up with: > > https://en.wikipedia.org/wiki/Astra_Linux Do you mean 8.3.0-6? It looks like the GCC version. Our Astra is 1.7.5: ant@xx:~$ cat /etc/issue Astra Linux 1.7.5 \n \l -- () ascii ribbon campaign -- against html e-mail /\ www.asciiribbon.org -- against proprietary attachments
On 4/17/25 15:25, Anton Shepelev wrote: > Adrian Klaver to Anton Shepelev: > Yet that is what we get for `pg_config --version' on the > affected system. A nearly identical error message seems to > come up while installing psycopg2 -- a Python library for > Postgres, e.g.: > > <https://stackoverflow.com/q/28253681/2862241> > <https://www.matheusmello.io/posts/python-you-need-to-install-postgresql-server-dev-x-y-for-building-a-server-side-extensi> > > I have not the slightest idea why pg_config should print > this message, unless it is not a genuine pg_config. I will > be checking its binary against the one that works as expected > on our reference system. Found it. It is coming from the Debian postgresql-common packaging. /usr/bin/pg_config is wrapper that contains: #!/bin/sh # If postgresql-server-dev-* is installed, call pg_config from the latest # available one. Otherwise fall back to libpq-dev's version. # # (C) 2011 Martin Pitt <mpitt@debian.org> # (C) 2014-2018 Christoph Berg <myon@debian.org> # # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by # the Free Software Foundation; either version 2 of the License, or # (at your option) any later version. # # This program is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # GNU General Public License for more details. set -e PGBINROOT="/usr/lib/postgresql/" #redhat# PGBINROOT="/usr/pgsql-" LATEST_SERVER_DEV=`ls -v $PGBINROOT*/bin/pg_config 2>/dev/null|tail -n1` if [ -n "$LATEST_SERVER_DEV" ]; then exec "$LATEST_SERVER_DEV" "$@" else if [ -x /usr/bin/pg_config.libpq-dev ]; then exec /usr/bin/pg_config.libpq-dev "$@" else echo "You need to install postgresql-server-dev-NN for building a server-side extension or libpq-dev for building a client-side application." >&2 exit 1 fi fi The message is telling you that on the machine you ran pg_config you have not installed either: postgresql-server-dev-X where X is a major version or libpq-dev If you want to get information from pg_config you will need to install one or the other. I would suggest libpq-dev. -- Adrian Klaver adrian.klaver@aklaver.com
Adrian Klaver: > Found it. It is coming from the Debian postgresql-common > packaging. > > /usr/bin/pg_config is wrapper that contains: > > #!/bin/sh > > # If postgresql-server-dev-* is installed, call pg_config from the latest > # available one. Otherwise fall back to libpq-dev's version. Ah, that makes sense. The error message, however, is misleading: You need to install postgresql-server-dev-NN for building a server-side extension or libpq-dev for building a client-side application. because the user calling `pg_config' may not be intent upon buidling either kind of application. Stating that pg_config requires one of these pachages would be more understandable. Thank you very much. -- () ascii ribbon campaign -- against html e-mail /\ www.asciiribbon.org -- against proprietary attachments
I wrote: > I will test if clearing shared_preload_libraries and > restarting Postgres has any effect on track_counts, just > in case. Nope, it didn't unstick track_counts: db=# show shared_preload_libraries ; -[ RECORD 1 ]------------+- shared_preload_libraries | db=# select * from pg_settings where name like '%k_cou%'; -[ RECORD 1 ]---+-------------------------------------------------- name | track_counts setting | off unit | category | Statistics / Query and Index Statistics Collector short_desc | Collects statistics on database activity. extra_desc | context | superuser vartype | bool source | override min_val | max_val | enumvals | boot_val | on reset_val | off sourcefile | sourceline | pending_restart | Have you any further ideas how to turn track_counts on? -- () ascii ribbon campaign -- against html e-mail /\ www.asciiribbon.org -- against proprietary attachments
On 4/21/25 09:26, Anton Shepelev wrote: > Adrian Klaver: > >> Found it. It is coming from the Debian postgresql-common >> packaging. >> >> /usr/bin/pg_config is wrapper that contains: >> >> #!/bin/sh >> >> # If postgresql-server-dev-* is installed, call pg_config from the latest >> # available one. Otherwise fall back to libpq-dev's version. > > Ah, that makes sense. The error message, however, is > misleading: > > You need to install postgresql-server-dev-NN > for building a server-side extension > or libpq-dev > for building a client-side application. > > because the user calling `pg_config' may not be intent upon > buidling either kind of application. Stating that pg_config > requires one of these pachages would be more understandable. > Thank you very much. > That is a request for the Debian packaging team. They can be reached at: https://www.postgresql.org/list/pgsql-pkg-debian/ Or through alternate means as detailed here: https://wiki.postgresql.org/wiki/Apt#Bugs -- Adrian Klaver adrian.klaver@aklaver.com
On 4/21/25 09:46, Anton Shepelev wrote: > I wrote: > >> I will test if clearing shared_preload_libraries and >> restarting Postgres has any effect on track_counts, just >> in case. > > Nope, it didn't unstick track_counts: > > db=# show shared_preload_libraries ; > -[ RECORD 1 ]------------+- > shared_preload_libraries | > > db=# select * from pg_settings where name like '%k_cou%'; > -[ RECORD 1 ]---+-------------------------------------------------- > name | track_counts > setting | off > unit | > category | Statistics / Query and Index Statistics Collector > short_desc | Collects statistics on database activity. > extra_desc | > context | superuser > vartype | bool > source | override > min_val | > max_val | > enumvals | > boot_val | on > reset_val | off > sourcefile | > sourceline | > pending_restart | > > Have you any further ideas how to turn track_counts on? It is matter of finding out what is setting?: source | override There is something different about your setup, as here on Ubuntu(which uses the Debian packaging) I see: select * from pg_settings where name like '%k_cou%'; -[ RECORD 1 ]---+--------------------------------------------------- name | track_counts setting | on unit | NULL category | Statistics / Cumulative Query and Index Statistics short_desc | Collects statistics on database activity. extra_desc | NULL context | superuser vartype | bool source | default min_val | NULL max_val | NULL enumvals | NULL boot_val | on reset_val | on sourcefile | NULL sourceline | NULL pending_restart | f -- Adrian Klaver adrian.klaver@aklaver.com
Adrian Klaver: > Anton Shepelev: > > > Have you any further ideas how to turn track_counts on? > > It is matter of finding out what is setting?: > > source | override Indeed, I having begun with this crucial question. > There is something different about your setup, as here on > Ubuntu(which uses the Debian packaging) I see: > [...] Yes. It is on on your side, and pgsql shows NULL values as NULL. Can the latter be due to a differnce in Postgres versions, for mine is 11.21 (as I have reported before)? We have several other servers with identical versions of the OS and PostgreSQL: user@DC:/opt/sva$ cat /etc/issue Astra Linux 1.7.5 user@xx:/opt/sva$ apt show postgresql Package: postgresql Version: 11+225astra3 Priority: optional Section: database Source: postgresql-common (225astra3) Maintainer: Debian PostgreSQL Maintainers <team+postgresql@tracker.debian.org> user@xx:/opt/sva$ psql -V psql (PostgreSQL) 11.21 (Debian 1:11.21-astra.se6+ci1) but `track_counts' is stuck off only on one. A complete reinstall with purging of all configuration data comes to mind, but it is a last-resort measure, as the system is a production one, and actively used. -- () ascii ribbon campaign -- against html e-mail /\ www.asciiribbon.org -- against proprietary attachments
On 4/22/25 01:31, Anton Shepelev wrote: > Adrian Klaver: > >> There is something different about your setup, as here on >> Ubuntu(which uses the Debian packaging) I see: >> [...] > > Yes. It is on on your side, and pgsql shows NULL values as > NULL. Can the latter be due to a differnce in Postgres > versions, for mine is 11.21 (as I have reported before)? No that is because I have my ~/.psqlrc set up with: \pset null 'NULL' From here: https://www.postgresql.org/docs/current/app-psql.html See the: Meta-Commands \pset section as well as Files psqlrc and ~/.psqlrc > user@xx:/opt/sva$ psql -V > psql (PostgreSQL) 11.21 (Debian 1:11.21-astra.se6+ci1) > > but `track_counts' is stuck off only on one. > > A complete reinstall with purging of all configuration data > comes to mind, but it is a last-resort measure, as the > system is a production one, and actively used. > Given that the Debian Postgres packaging allows you to install multiple clusters of a given version I would create a new 11.21 cluster as a test and see what track_counts is set to. That would help determine whether the setting is coming from the initial install. You could then add the other components to the cluster one by one and see if and where the track_counts setting changes. -- Adrian Klaver adrian.klaver@aklaver.com