Message-ID: <c2e39c70c229cf59eb430431b08e9abfffe43b08.camel@j-davis.com>
Subject: Re: Statistics Import and Export
From: Jeff Davis <pgsql@j-davis.com>
To: Corey Huinker <corey.huinker@gmail.com>
Cc: Matthias van de Meent <boekewurm+postgres@gmail.com>, Bruce Momjian
 <bruce@momjian.us>, Tom Lane <tgl@sss.pgh.pa.us>, Nathan Bossart
 <nathandbossart@gmail.com>, Magnus Hagander <magnus@hagander.net>, Stephen
 Frost <sfrost@snowman.net>, Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>,
 Peter Smith <smithpb2250@gmail.com>, PostgreSQL Hackers
 <pgsql-hackers@lists.postgresql.org>,  Tomas Vondra
 <tomas.vondra@enterprisedb.com>, alvherre@alvh.no-ip.org
Date: Thu, 01 Aug 2024 23:44:54 -0700
In-Reply-To: 
 <CADkLM=chb8YT+OD40aDDj1MGz52FMw6-SW3atgEpsw6Rp-3mhg@mail.gmail.com>
References: <e8135244d99e6bc388853598cb0ec4bf59085494.camel@j-davis.com>
	 <1798867.1712376328@sss.pgh.pa.us>
	 <CADkLM=dA0tOc_fGDoiRsF41XFFH2_S3D5kibcbw9H948KdsS-A@mail.gmail.com>
	 <CADkLM=eE2BD-40uGMOpJs19inYJ2PSY+21srXT0K49NseBe30w@mail.gmail.com>
	 <20240417165053.GA3253364@nathanxps13>
	 <0943da4616837682c65e4620050b8b1e86fe3503.camel@j-davis.com>
	 <3156140.1713817153@sss.pgh.pa.us>
	 <1d70aabacd379ed8f9a7db1bf4ca22d1f5cd943f.camel@j-davis.com>
	 <3228677.1713844341@sss.pgh.pa.us>
	 <CAEze2WiX_YWEhe4iLSLHEkSJimE0=wU1cGhmPikurZGOTrh1tg@mail.gmail.com>
	 <ZileJQ2wl-Y63VWt@momjian.us>
	 <CAEze2WjM8QDZUJa7G4_Om2B76nFMw5RYz+1D+J=VMpr6aN3BJQ@mail.gmail.com>
	 <CADkLM=dHYkDa-raQrahd5-PkO_=aw1RuffUxVAfEnEyX5ZOB5A@mail.gmail.com>
	 <CADkLM=cQgghMJOS8EcAVBwRO4s1dUVtxGZv5gLPfZkQ1nL1gzA@mail.gmail.com>
	 <10f251857a13cfc799079ba89d50b135c974a18e.camel@j-davis.com>
	 <CADkLM=dse9b6VYkgt8MMe3htt=tD1bZOdk3KvH=3Gt17LqSVXA@mail.gmail.com>
	 <1a8ec76a863539ec2f849f247ee0e99dff8303e2.camel@j-davis.com>
	 <CADkLM=dNQzmdsWfXVVDBqv7gHsa-7dk3v13AqrAzBoJWTMrWEA@mail.gmail.com>
	 <CADkLM=ccvjLJeGnWzbH8N=2aBNpnL5HnwC4Ny2LqFVJyGH5ZKw@mail.gmail.com>
	 <e2f8ad0da0096d4efaf68a6a441cc1931fa5cd80.camel@j-davis.com>
	 <CADkLM=c9gnYMkQCyh1+R56VrCkVWbu8-SCpmHepkbAJHMcVuuA@mail.gmail.com>
	 <CADkLM=f_SNcxfPC-XbwcHgCdS6FPSNo-=UZq08AWFYoBCDcxOg@mail.gmail.com>
	 <85eaf260a4be2e08376145a9d6a69d9d74e89daa.camel@j-davis.com>
	 <CADkLM=evyx0L96XxrLSUDVS+iXgHszLbWMrGwVbBnNVO1ejfJA@mail.gmail.com>
	 <CADkLM=cJMEYrP8o4dbAqm_mW=ks63sXi1VXy94ybev0BNLm7_g@mail.gmail.com>
	 <e0c1284347ab88860bdaf75a8188df075f592d98.camel@j-davis.com>
	 <CADkLM=chb8YT+OD40aDDj1MGz52FMw6-SW3atgEpsw6Rp-3mhg@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.44.4-0ubuntu2 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/c2e39c70c229cf59eb430431b08e9abfffe43b08.camel%40j-davis.com>
Precedence: bulk

On Sat, 2024-07-27 at 21:08 -0400, Corey Huinker wrote:
>=20
> > I don't like the idea of mixing statistics and control parameters
> > in
> > the same list.
> >=20
>=20
>=20
> There's no way around it, at least now we need never worry about a
> confusing order for the parameters in the _restore_ functions because
> they can now be in any order you like.

Perhaps I was not precise enough when I said "control" parameters.
Mainly what I was worried about is trying to take parameters that
control things like transaction behavior (in-place vs mvcc), and
pg_dump should not be specifying that kind of thing. A parameter like
"version" is specified by pg_dump anyway, so it's probably fine the way
you've done it.

> SELECT pg_catalog.pg_set_attribute_stats(
> =C2=A0 =C2=A0 relation =3D> 'stats_import.test'::regclass::oid,
> =C2=A0 =C2=A0 attname =3D> 'arange'::name,
> =C2=A0 =C2=A0 inherited =3D> false::boolean,
> =C2=A0 =C2=A0 null_frac =3D> 0.5::real,
> =C2=A0 =C2=A0 avg_width =3D> 2::integer,
> =C2=A0 =C2=A0 n_distinct =3D> -0.1::real,
> =C2=A0 =C2=A0 range_empty_frac =3D> 0.5::real,
> =C2=A0 =C2=A0 range_length_histogram =3D> '{399,499,Infinity}'::text
> =C2=A0 =C2=A0 );
> =C2=A0pg_set_attribute_stats=20
> ------------------------
> =C2=A0
> (1 row)

I like it.

> and here is a restore function
>=20
> -- warning: mcv cast failure
> SELECT *
> FROM pg_catalog.pg_restore_attribute_stats(
> =C2=A0 =C2=A0 'relation', 'stats_import.test'::regclass::oid,
> =C2=A0 =C2=A0 'attname', 'id'::name,
> =C2=A0 =C2=A0 'inherited', false::boolean,
> =C2=A0 =C2=A0 'version', 150000::integer,
> =C2=A0 =C2=A0 'null_frac', 0.5::real,
> =C2=A0 =C2=A0 'avg_width', 2::integer,
> =C2=A0 =C2=A0 'n_distinct', -0.4::real,
> =C2=A0 =C2=A0 'most_common_vals', '{2,four,3}'::text,
> =C2=A0 =C2=A0 'most_common_freqs', '{0.3,0.25,0.05}'::real[]
> =C2=A0 =C2=A0 );
> WARNING: =C2=A0invalid input syntax for type integer: "four"
> =C2=A0row_written | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0stats_applied =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
> =C2=A0stats_rejected =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| params_re=
jected=20
> -------------+----------------------------------+--------------------
> ------------------+-----------------
> =C2=A0t =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | {null_frac,avg_width,n_disti=
nct} |
> {most_common_vals,most_common_freqs} |=20
> (1 row)

I think I like this, as well, except for the return value, which seems
like too much information and a bit over-engineered. Can we simplify it
to what's actually going to be used by pg_upgrade and other tools?

> Attached is v25.

I believe 0001 and 0002 are in good shape API-wise, and I can start
getting those committed. I will try to clean up the code in the
process.

Regards,
	Jeff Davis