Cannot turn track_counts on

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&gt;
<https://www.matheusmello.io/posts/python-you-need-to-install-postgresql-server-dev-x-y-for-building-a-server-side-extensi&gt;

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&gt;
<https://www.matheusmello.io/posts/python-you-need-to-install-postgresql-server-dev-x-y-for-building-a-server-side-extensi&gt;

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