ERROR: could not find tuple for statistics object - is there a way to clean this up?

Started by Morris de Oryxover 5 years ago5 messagesgeneral
Jump to latest
#1Morris de Oryx
morrisdeoryx@gmail.com

I've been experimenting with CREATE STATISTICS to declare some functionally
dependent columns. Right now, I'm working with a local copy of Postgres
running on this version:

PostgreSQL 12.5 on x86_64-apple-darwin16.7.0, compiled by Apple LLVM
version 8.1.0 (clang-802.0.42), 64-bit

We deploy on RDS, also in the 12.x line.

Here's an example of the DROP:
DROP TABLE data.samples CASCADE;And

And here's the error that I get back:

ERROR: could not find tuple for statistics object 147574.

I'm out of my depth here. pg_statistic_ext does not have any row with an
oid or stxrelid of 147574. I've hunted around some in pg_class and
pg_statitic, but can't find any column with a reference to this value.

I tried upgrading (I was on 12.3), shutting down and restarting the server
a few times, and running ANALYZE to see if anything would change. It hasn't.

Any idea how this problem can be created, avoided, or resolved? Many thanks.

For those of you familiar with the source, I've Googled for this error, and
have not seen it discussed. It comes up on pg-hackers about 12 years ago,
and it's located in the source at:

https://doxygen.postgresql.org/objectaddress_8h.html

Here's a snippet where the error is thrown, when HeapTupleIsValid returns
false.

case OCLASS_STATISTIC_EXT:

{
HeapTuple stxTup;
Form_pg_statistic_ext stxForm;
char *nspname;

stxTup = SearchSysCache1(STATEXTOID,
ObjectIdGetDatum(object->objectId));
if (!HeapTupleIsValid(stxTup))
{
if (!missing_ok)
elog(ERROR, "could not find tuple for statistics object %u",
object->objectId);
break;
}

stxForm = (Form_pg_statistic_ext) GETSTRUCT(stxTup);

/* Qualify the name if not visible in search path */
if (StatisticsObjIsVisible(object->objectId))
nspname = NULL;
else
nspname = get_namespace_name(stxForm->stxnamespace);

appendStringInfo(&buffer, _("statistics object %s"),
quote_qualified_identifier(nspname,
NameStr(stxForm->stxname)));

ReleaseSysCache(stxTup);
break;
}

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Morris de Oryx (#1)
Re: ERROR: could not find tuple for statistics object - is there a way to clean this up?

Morris de Oryx <morrisdeoryx@gmail.com> writes:

And here's the error that I get back:
ERROR: could not find tuple for statistics object 147574.

Can you give a self-contained recipe for triggering this?

regards, tom lane

#3Morris de Oryx
morrisdeoryx@gmail.com
In reply to: Tom Lane (#2)
Re: ERROR: could not find tuple for statistics object - is there a way to clean this up?

After posting, I realized that this is likely a Stupid User Error. I was
mucking around, and did something along the lines of

delete from pg_statistic_ext;

or
delete from pg_stats_ext;

...instead of DROP STATISTICS calls. Would this likely explain what I'm
seeing? If so, bug is in front of keyboard...my understanding is that you
shouldn't mess with the catalog data directly.

On Sun, Nov 15, 2020 at 6:08 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Show quoted text

Morris de Oryx <morrisdeoryx@gmail.com> writes:

And here's the error that I get back:
ERROR: could not find tuple for statistics object 147574.

Can you give a self-contained recipe for triggering this?

regards, tom lane

#4Tom Lane
tgl@sss.pgh.pa.us
In reply to: Morris de Oryx (#3)
Re: ERROR: could not find tuple for statistics object - is there a way to clean this up?

Morris de Oryx <morrisdeoryx@gmail.com> writes:

After posting, I realized that this is likely a Stupid User Error. I was
mucking around, and did something along the lines of
delete from pg_statistic_ext;
or
delete from pg_stats_ext;

...instead of DROP STATISTICS calls. Would this likely explain what I'm
seeing?

Ah, yeah, it likely would.

If this isn't a throwaway database, what you'd have to do to clear the
errors is to find and remove the now-dangling links to the deleted objects
in pg_depend.

regards, tom lane

#5Morris de Oryx
morrisdeoryx@gmail.com
In reply to: Tom Lane (#4)
Re: ERROR: could not find tuple for statistics object - is there a way to clean this up?

Thanks, it's my local copy, which I drop and rebuild constantly as part of
my testing cycle. On our deployed servers, my patch and test procedures are
very strict. Locally, I like to experiment and blow things up. Well,
mission accomplished there ;-)

Thanks for taking the time to answer.

On Mon, Nov 16, 2020 at 3:34 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Show quoted text

Morris de Oryx <morrisdeoryx@gmail.com> writes:

After posting, I realized that this is likely a Stupid User Error. I was
mucking around, and did something along the lines of
delete from pg_statistic_ext;
or
delete from pg_stats_ext;

...instead of DROP STATISTICS calls. Would this likely explain what I'm
seeing?

Ah, yeah, it likely would.

If this isn't a throwaway database, what you'd have to do to clear the
errors is to find and remove the now-dangling links to the deleted objects
in pg_depend.

regards, tom lane