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