Обсуждение: pg_stat_statements_info

I don't have this view:

ERROR:  relation "pg_stat_statements_info" does not exist
LINE 1: select * from pg_stat_statements_info;
                      ^

But I definitely have the pg_stat_statements extension installed and query pg_stat_statements quite frequently... What gives?

It's just not in a schema contained in you search_path

Am 11. September 2023 23:26:22 MESZ schrieb Wells Oliver <wells.oliver@gmail.com>:

I don't have this view:

ERROR:  relation "pg_stat_statements_info" does not exist
LINE 1: select * from pg_stat_statements_info;
                      ^

But I definitely have the pg_stat_statements extension installed and query pg_stat_statements quite frequently... What gives?

On Mon, 2023-09-11 at 14:26 -0700, Wells Oliver wrote:
> I don't have this view:
>
> ERROR:  relation "pg_stat_statements_info" does not exist
> LINE 1: select * from pg_stat_statements_info;
>                       ^
> But I definitely have the pg_stat_statements extension installed and query pg_stat_statements quite frequently... What gives?

This query will show you the version installed and the schema:

  SELECT extversion, extnamespace::regnamespace
  FROM pg_extension
  WHERE extname = 'pg_stat_statements';

"pg_stat_statements_info" is not a view, but a function, so if the extension
schema is "public", try

  SELECT * FROM public.pg_stat_statements_info();

Yours,
Laurenz Albe

Hi,

Le mar. 12 sept. 2023, 06:22, Wells Oliver <wells.oliver@gmail.com> a écrit :

Does this make sense?

SELECT extversion, extnamespace::regnamespace
FROM pg_extension
WHERE extname = 'pg_stat_statements';

Shows 1.8 and public, but..

SELECT * FROM public.pg_stat_statements_info();

ERROR:  function public.pg_stat_statements_info() does not exist
LINE 1: SELECT * FROM public.pg_stat_statements_info();
                      ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

This is on RDS, I am not sure if the extension should somehow behave differently installed there.

1.8 seems to be PostgreSQL 13. You need at least PostgreSQL 14 to have pg_stat_statements_info.

On Mon, Sep 11, 2023 at 7:24 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:

On Mon, 2023-09-11 at 14:26 -0700, Wells Oliver wrote:
> I don't have this view:
>
> ERROR:  relation "pg_stat_statements_info" does not exist
> LINE 1: select * from pg_stat_statements_info;
>                       ^
> But I definitely have the pg_stat_statements extension installed and query pg_stat_statements quite frequently... What gives?

This query will show you the version installed and the schema:

  SELECT extversion, extnamespace::regnamespace
  FROM pg_extension
  WHERE extname = 'pg_stat_statements';

"pg_stat_statements_info" is not a view, but a function, so if the extension
schema is "public", try

  SELECT * FROM public.pg_stat_statements_info();

Yours,
Laurenz Albe

--

Wells Oliver
wells.oliver@gmail.com

-- 

Guillaume

Le mar. 12 sept. 2023, 06:51, Wells Oliver <wells.oliver@gmail.com> a écrit :

Odd... definitely running Postgres 14.8.

Then you probably forgot to update your extension. What does "select * from pg_available_extensions" show for the pg_stat_statements line?

On Mon, Sep 11, 2023 at 9:44 PM Guillaume Lelarge <guillaume@lelarge.info> wrote:

Hi,

Le mar. 12 sept. 2023, 06:22, Wells Oliver <wells.oliver@gmail.com> a écrit :

Does this make sense?

SELECT extversion, extnamespace::regnamespace
FROM pg_extension
WHERE extname = 'pg_stat_statements';

Shows 1.8 and public, but..

SELECT * FROM public.pg_stat_statements_info();

ERROR:  function public.pg_stat_statements_info() does not exist
LINE 1: SELECT * FROM public.pg_stat_statements_info();
                      ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

This is on RDS, I am not sure if the extension should somehow behave differently installed there.

1.8 seems to be PostgreSQL 13. You need at least PostgreSQL 14 to have pg_stat_statements_info.

On Mon, Sep 11, 2023 at 7:24 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:

On Mon, 2023-09-11 at 14:26 -0700, Wells Oliver wrote:
> I don't have this view:
>
> ERROR:  relation "pg_stat_statements_info" does not exist
> LINE 1: select * from pg_stat_statements_info;
>                       ^
> But I definitely have the pg_stat_statements extension installed and query pg_stat_statements quite frequently... What gives?

This query will show you the version installed and the schema:

  SELECT extversion, extnamespace::regnamespace
  FROM pg_extension
  WHERE extname = 'pg_stat_statements';

"pg_stat_statements_info" is not a view, but a function, so if the extension
schema is "public", try

  SELECT * FROM public.pg_stat_statements_info();

Yours,
Laurenz Albe

--

Wells Oliver
wells.oliver@gmail.com

-- 

Guillaume

--

Wells Oliver
wells.oliver@gmail.com