Thread: preserving statistics settings

preserving statistics settings

From
nconway@klamath.dyndns.org (Neil Conway)
Date:
Shouldn't the effect of ALTER TABLE ALTER COLUMN SET STATISTICS be
recorded by pg_dump?

For example:

CREATE TABLE foo (col1 int, col2 int);

ALTER TABLE foo ALTER COLUMN col1 SET STATISTICS 100;

$ pg_dumpall

After removing the database and restoring from the dump, the effect of
the ALTER TABLE ALTER COLUMN SET STATISTICS will be lost...

Cheers,

Neil

-- 
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC


Re: preserving statistics settings

From
Tom Lane
Date:
nconway@klamath.dyndns.org (Neil Conway) writes:
> Shouldn't the effect of ALTER TABLE ALTER COLUMN SET STATISTICS be
> recorded by pg_dump?

Probably.  I was dithering about whether that should be true/false/
controllable, and never got 'round to doing anything at all.  One
problem is that I didn't really want to enshrine the current default
value in a bunch of pg_dump scripts, in case we decide it's too small.
So we shouldn't dump SET STATISTICS commands always.

If there were a reliable way to tell whether the attstattarget value had
actually been set by the user, or was merely a default, it'd be easier
to determine what pg_dump should do.
        regards, tom lane


Re: preserving statistics settings

From
nconway@klamath.dyndns.org (Neil Conway)
Date:
On Fri, Jul 19, 2002 at 12:39:16PM -0400, Tom Lane wrote:
> nconway@klamath.dyndns.org (Neil Conway) writes:
> > Shouldn't the effect of ALTER TABLE ALTER COLUMN SET STATISTICS be
> > recorded by pg_dump?

> One problem is that I didn't really want to enshrine the current
> default value in a bunch of pg_dump scripts, in case we decide it's
> too small.

Good point.

> If there were a reliable way to tell whether the attstattarget value had
> actually been set by the user, or was merely a default, it'd be easier
> to determine what pg_dump should do.

Hmmm... we could allow SET STATISTICS to take 'DEFAULT' easily enough
(and that might even be a good idea in any case), but you're right --
without a way for pg_dump to determine the default value and/or whether
the admin have explicitely changed the attstattarget for that column,
there's not much that can be done...

I suppose we could hard-code the current default value into pg_dump,
but that's pretty ugly.

Does anyone have a better suggestion?

Cheers,

Neil

-- 
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC


Re: preserving statistics settings

From
Tom Lane
Date:
nconway@klamath.dyndns.org (Neil Conway) writes:
> Hmmm... we could allow SET STATISTICS to take 'DEFAULT' easily enough
> (and that might even be a good idea in any case), but you're right --
> without a way for pg_dump to determine the default value and/or whether
> the admin have explicitely changed the attstattarget for that column,
> there's not much that can be done...

> I suppose we could hard-code the current default value into pg_dump,
> but that's pretty ugly.

Also, there's no guarantee that pg_dump would know what default the
backend had been compiled with, so it might make the wrong conclusion
anyway.

> Does anyone have a better suggestion?

Not sure why I didn't think of this before, but we could make the stored
value of attstattarget be "-1" to indicate "use the default".  Zero or a
positive value then indicates an explicit selection.  We can't
retroactively fix 7.2, but going forward we'd have a reasonable answer.
        regards, tom lane


Re: preserving statistics settings

From
nconway@klamath.dyndns.org (Neil Conway)
Date:
On Fri, Jul 19, 2002 at 01:28:29PM -0400, Tom Lane wrote:
> nconway@klamath.dyndns.org (Neil Conway) writes:
> > Does anyone have a better suggestion?
> 
> Not sure why I didn't think of this before, but we could make the stored
> value of attstattarget be "-1" to indicate "use the default".  Zero or a
> positive value then indicates an explicit selection.  We can't
> retroactively fix 7.2, but going forward we'd have a reasonable answer.

Sounds good to me. I can implement this, unless you'd rather do it.

Cheers,

Neil

-- 
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC


Re: preserving statistics settings

From
Tom Lane
Date:
nconway@klamath.dyndns.org (Neil Conway) writes:
> On Fri, Jul 19, 2002 at 01:28:29PM -0400, Tom Lane wrote:
>> Not sure why I didn't think of this before, but we could make the stored
>> value of attstattarget be "-1" to indicate "use the default".  Zero or a
>> positive value then indicates an explicit selection.  We can't
>> retroactively fix 7.2, but going forward we'd have a reasonable answer.

> Sounds good to me. I can implement this, unless you'd rather do it.

Go for it.  Note you should be able to remove genbki.sh's knowledge of
DEFAULT_ATTSTATTARGET, as well as all the occurrences of that symbol
in include/catalog/*.h.  Offhand I guess only analyze.c should refer
to that symbol once it's done right.
        regards, tom lane


Re: preserving statistics settings

From
Tom Lane
Date:
BTW, checking my notes about this issue, I notice a related question:
should attstattarget be inherited for the inherited columns when a child
table is created?  Right now it's not, and it might be a bit ugly to
make it so.

In a lot of scenarios you wouldn't necessarily expect the parent and
child tables to have similar contents, so I'm not sure inheriting
attstattarget is appropriate anyway.  Comments anyone?
        regards, tom lane


Re: preserving statistics settings

From
Rod Taylor
Date:
On Fri, 2002-07-19 at 18:52, Tom Lane wrote:
> BTW, checking my notes about this issue, I notice a related question:
> should attstattarget be inherited for the inherited columns when a child
> table is created?  Right now it's not, and it might be a bit ugly to
> make it so.
> 
> In a lot of scenarios you wouldn't necessarily expect the parent and
> child tables to have similar contents, so I'm not sure inheriting
> attstattarget is appropriate anyway.  Comments anyone?

In any of my uses of inheritance the contents have been drastically
different between the various tables.  Not that I've played much with
the statistics stuff, but I don't think blindly applying the value would
be good.