VACUUM ANALYZE Problem

James Hughes wrote:

I am getting the error...

ERROR: fmgr_info: function 0: cache lookup failed

...after creating a database, creating tables - indexes
and sequences, inserting data (with perl scripts) into 2 tables (570
records in one and 100 records in another), using "vacuum analyze" on
the database then trying "\d <tablename>" or "\dS". Running "vacuum"
alone is OK.

I run the query from psql.c:601 on the psql command line and get the
same result.

I tried the same sequence with a small test database and only a few
records and there were no problems.

I am still looking into this and would appreciate any pointers.

Version ?
gdb output ?

Vadim

On Sun, 1 Feb 1998, Vadim B. Mikheev wrote:

: James Hughes wrote:
: >
: > I am getting the error...
: >
: > ERROR: fmgr_info: function 0: cache lookup failed
: >
: > ...after creating a database, creating tables - indexes
: > and sequences, inserting data (with perl scripts) into 2 tables (570
: > records in one and 100 records in another), using "vacuum analyze" on
: > the database then trying "\d <tablename>" or "\dS". Running "vacuum"
: > alone is OK.
: >
: > I run the query from psql.c:601 on the psql command line and get the
: > same result.
: >
: > I tried the same sequence with a small test database and only a few
: > records and there were no problems.
: >
: > I am still looking into this and would appreciate any pointers.
:
: Version ?

1-31 cvs tree

: gdb output ?

I'll see if I can narrow it down a bit. Might be larger than the sources
at this point ;)

-James

After poking arround some more, I found that the "vacuum analyze" is
causing problems with the "<" and ">" operators. The "> 0" in the SELECT
for "/d <table>" and "/dS" commands in psql cause the error.

I verified that any simple query using the "<" or ">" operators fail
with the same message...

ERROR: fmgr_info: function 0: cache lookup failed

...after using the "vacuum analyse" command.
But, only after vacuuming any relation that was created and populated by
me. Vacumming system catalogs poses no problems.

I did go back to 6.2.0. Found no problems there.

-James

James Hughes wrote:

After poking arround some more, I found that the "vacuum analyze" is
causing problems with the "<" and ">" operators. The "> 0" in the SELECT
for "/d <table>" and "/dS" commands in psql cause the error.

I verified that any simple query using the "<" or ">" operators fail
with the same message...

Analyze uses oper("=",...), oper("<",...) and oper(">",...)...
Are queries with "=" OK ?

ERROR: fmgr_info: function 0: cache lookup failed

...after using the "vacuum analyse" command.
But, only after vacuuming any relation that was created and populated by
me. Vacumming system catalogs poses no problems.

There are comments into vc_updstats:

/*
* invalidating system relations confuses the function cache of
* pg_operator and pg_opclass
*/
if (!IsSystemRelationName(pgcform->relname.data))
RelationInvalidateHeapTuple(rd, rtup);

==> invalidation of user relation causes problems too, Bruce ?

Vadim

On Tue, 3 Feb 1998, Vadim B. Mikheev wrote:

: James Hughes wrote:
: >
: > After poking arround some more, I found that the "vacuum analyze" is
: > causing problems with the "<" and ">" operators. The "> 0" in the SELECT
: > for "/d <table>" and "/dS" commands in psql cause the error.
: >
: > I verified that any simple query using the "<" or ">" operators fail
: > with the same message...
:
: Analyze uses oper("=",...), oper("<",...) and oper(">",...)...
: Are queries with "=" OK ?
:

Yes...

"=" is OK,
"<>" is OK,
"<" is broken,
">" is broken,
"<=" is broken,
">=" is broken

...maybe others, I have no geometrical tables to test
with. I could use some of the code from the regression tests if needed.

-James

James Hughes wrote:

After poking arround some more, I found that the "vacuum analyze" is
causing problems with the "<" and ">" operators. The "> 0" in the SELECT
for "/d <table>" and "/dS" commands in psql cause the error.

I verified that any simple query using the "<" or ">" operators fail
with the same message...

ERROR: fmgr_info: function 0: cache lookup failed

...after using the "vacuum analyse" command.
But, only after vacuuming any relation that was created and populated by
me. Vacumming system catalogs poses no problems.

Well, I found that this problem was caused by selfuncs.c:gethilokey():

static ScanKeyData key[3] = {
{0, Anum_pg_statistic_starelid, F_OIDEQ},
{0, Anum_pg_statistic_staattnum, F_INT2EQ},
{0, Anum_pg_statistic_staop, F_OIDEQ}

: skankeys are hardcoded without call to ScanKeyEntryInitialize() =>
without initialization of sk_func.fn_oid required, I assume, by
new PL support code. Patch for this place follows...
One should check all places where ScanKeyData is used.
Jan, could you do this ?

(Oh, hell! I got this ERROR while testing subselect and spent so many time
to fix this problem...)

Vadim

On Tue, 3 Feb 1998, Vadim B. Mikheev wrote:

: James Hughes wrote:
: >
: > After poking arround some more, I found that the "vacuum analyze" is
: > causing problems with the "<" and ">" operators. The "> 0" in the SELECT
: > for "/d <table>" and "/dS" commands in psql cause the error.
: >
: > I verified that any simple query using the "<" or ">" operators fail
: > with the same message...
: >
: > ERROR: fmgr_info: function 0: cache lookup failed
: >
: > ...after using the "vacuum analyse" command.
: > But, only after vacuuming any relation that was created and populated by
: > me. Vacumming system catalogs poses no problems.
:
: Well, I found that this problem was caused by selfuncs.c:gethilokey():
:
: static ScanKeyData key[3] = {
: {0, Anum_pg_statistic_starelid, F_OIDEQ},
: {0, Anum_pg_statistic_staattnum, F_INT2EQ},
: {0, Anum_pg_statistic_staop, F_OIDEQ}
:
: : skankeys are hardcoded without call to ScanKeyEntryInitialize() =>
: without initialization of sk_func.fn_oid required, I assume, by
: new PL support code. Patch for this place follows...
: One should check all places where ScanKeyData is used.
: Jan, could you do this ?
:

THANKS! I'll patch my code and check the other instances.

: (Oh, hell! I got this ERROR while testing subselect and spent so many time
: to fix this problem...)
:
: Vadim

-James

After poking arround some more, I found that the "vacuum analyze" is
causing problems with the "<" and ">" operators. The "> 0" in the SELECT
for "/d <table>" and "/dS" commands in psql cause the error.

I verified that any simple query using the "<" or ">" operators fail
with the same message...

ERROR: fmgr_info: function 0: cache lookup failed

...after using the "vacuum analyse" command.
But, only after vacuuming any relation that was created and populated by
me. Vacumming system catalogs poses no problems.

I did go back to 6.2.0. Found no problems there.

Glad it wasn't my vacuum code. Lots of bad scan initializations. Who
is working on that? I think someone volunteered.

--
Bruce Momjian
maillist@candle.pha.pa.us

James Hughes wrote:

After poking arround some more, I found that the "vacuum analyze" is
causing problems with the "<" and ">" operators. The "> 0" in the SELECT
for "/d <table>" and "/dS" commands in psql cause the error.

I verified that any simple query using the "<" or ">" operators fail
with the same message...

Analyze uses oper("=",...), oper("<",...) and oper(">",...)...
Are queries with "=" OK ?

ERROR: fmgr_info: function 0: cache lookup failed

...after using the "vacuum analyse" command.
But, only after vacuuming any relation that was created and populated by
me. Vacumming system catalogs poses no problems.

There are comments into vc_updstats:

/*
* invalidating system relations confuses the function cache of
* pg_operator and pg_opclass
*/
if (!IsSystemRelationName(pgcform->relname.data))
RelationInvalidateHeapTuple(rd, rtup);

==> invalidation of user relation causes problems too, Bruce ?

So this is not a problem? right?

--
Bruce Momjian
maillist@candle.pha.pa.us

On Tue, 3 Feb 1998, Vadim B. Mikheev wrote:

: James Hughes wrote:
: >
: > After poking arround some more, I found that the "vacuum analyze" is
: > causing problems with the "<" and ">" operators. The "> 0" in the SELECT
: > for "/d <table>" and "/dS" commands in psql cause the error.
: >
: > I verified that any simple query using the "<" or ">" operators fail
: > with the same message...
: >
: > ERROR: fmgr_info: function 0: cache lookup failed
: >
: > ...after using the "vacuum analyse" command.
: > But, only after vacuuming any relation that was created and populated by
: > me. Vacumming system catalogs poses no problems.
:
: Well, I found that this problem was caused by selfuncs.c:gethilokey():
:
: static ScanKeyData key[3] = {
: {0, Anum_pg_statistic_starelid, F_OIDEQ},
: {0, Anum_pg_statistic_staattnum, F_INT2EQ},
: {0, Anum_pg_statistic_staop, F_OIDEQ}
:
: : skankeys are hardcoded without call to ScanKeyEntryInitialize() =>
: without initialization of sk_func.fn_oid required, I assume, by
: new PL support code. Patch for this place follows...
: One should check all places where ScanKeyData is used.
: Jan, could you do this ?
:

THANKS! I'll patch my code and check the other instances.

James, are you going to submit a patch for all the source code?

--
Bruce Momjian
maillist@candle.pha.pa.us

On Tue, 3 Feb 1998, Bruce Momjian wrote:

: >
: >
: >
: > On Tue, 3 Feb 1998, Vadim B. Mikheev wrote:
: >
: > : James Hughes wrote:
: > : >
: > : > After poking arround some more, I found that the "vacuum analyze" is
: > : > causing problems with the "<" and ">" operators. The "> 0" in the SELECT
: > : > for "/d <table>" and "/dS" commands in psql cause the error.
: > : >
: > : > I verified that any simple query using the "<" or ">" operators fail
: > : > with the same message...
: > : >
: > : > ERROR: fmgr_info: function 0: cache lookup failed
: > : >
: > : > ...after using the "vacuum analyse" command.
: > : > But, only after vacuuming any relation that was created and populated by
: > : > me. Vacumming system catalogs poses no problems.
: > :
: > : Well, I found that this problem was caused by selfuncs.c:gethilokey():
: > :
: > : static ScanKeyData key[3] = {
: > : {0, Anum_pg_statistic_starelid, F_OIDEQ},
: > : {0, Anum_pg_statistic_staattnum, F_INT2EQ},
: > : {0, Anum_pg_statistic_staop, F_OIDEQ}
: > :
: > : : skankeys are hardcoded without call to ScanKeyEntryInitialize() =>
: > : without initialization of sk_func.fn_oid required, I assume, by
: > : new PL support code. Patch for this place follows...
: > : One should check all places where ScanKeyData is used.
: > : Jan, could you do this ?
: > :
: >
: > THANKS! I'll patch my code and check the other instances.
:
: James, are you going to submit a patch for all the source code?
:

Go ahead with just Vadim's patch for now. It fixes the analyze problem.
I am going out of town for a few days and won't have access to my Dev
System until then. I'll work on it this weekend if it still needs doing.

-James

OK, I have looked at this, but can't figure out how to fix the many
initializations of ScanKeyData. Can someone who understands this please
submit a patch for all these initializations so we can stop these vacuum
analyze reports?

Vadim has found the problem, but we need someone to properly fix it.

James Hughes wrote:

After poking arround some more, I found that the "vacuum analyze" is
causing problems with the "<" and ">" operators. The "> 0" in the SELECT
for "/d <table>" and "/dS" commands in psql cause the error.

I verified that any simple query using the "<" or ">" operators fail
with the same message...

ERROR: fmgr_info: function 0: cache lookup failed

...after using the "vacuum analyse" command.
But, only after vacuuming any relation that was created and populated by
me. Vacumming system catalogs poses no problems.

Well, I found that this problem was caused by selfuncs.c:gethilokey():

static ScanKeyData key[3] = {
{0, Anum_pg_statistic_starelid, F_OIDEQ},
{0, Anum_pg_statistic_staattnum, F_INT2EQ},
{0, Anum_pg_statistic_staop, F_OIDEQ}

: skankeys are hardcoded without call to ScanKeyEntryInitialize() =>
without initialization of sk_func.fn_oid required, I assume, by
new PL support code. Patch for this place follows...
One should check all places where ScanKeyData is used.
Jan, could you do this ?

(Oh, hell! I got this ERROR while testing subselect and spent so many time
to fix this problem...)

Vadim
--------------A99EE0A2D8F4D665C5BF3957
Content-Type: application/octet-stream; name="FFF"
Content-Transfer-Encoding: base64
Content-Disposition: attachment; filename="FFF"

KioqIHNlbGZ1bmNzLmN+CU1vbiBGZWIgIDIgMTM6NTU6NDcgMTk5OAotLS0gc2VsZnVuY3Mu
YwlUdWUgRmViICAzIDE3OjM2OjAxIDE5OTgKKioqKioqKioqKioqKioqCioqKiAzMzcsMzQ1
ICoqKioKICAJcmVnaXN0ZXIgUmVsYXRpb24gcmRlc2M7CiAgCXJlZ2lzdGVyIEhlYXBTY2Fu
RGVzYyBzZGVzYzsKICAJc3RhdGljIFNjYW5LZXlEYXRhIGtleVszXSA9IHsKISAJCXswLCBB
bnVtX3BnX3N0YXRpc3RpY19zdGFyZWxpZCwgRl9PSURFUX0sCiEgCQl7MCwgQW51bV9wZ19z
dGF0aXN0aWNfc3RhYXR0bnVtLCBGX0lOVDJFUX0sCiEgCQl7MCwgQW51bV9wZ19zdGF0aXN0
aWNfc3Rhb3AsIEZfT0lERVF9CiAgCX07CiAgCWJvb2wJCWlzbnVsbDsKICAJSGVhcFR1cGxl
CXR1cGxlOwotLS0gMzM3LDM0NSAtLS0tCiAgCXJlZ2lzdGVyIFJlbGF0aW9uIHJkZXNjOwog
IAlyZWdpc3RlciBIZWFwU2NhbkRlc2Mgc2Rlc2M7CiAgCXN0YXRpYyBTY2FuS2V5RGF0YSBr
ZXlbM10gPSB7CiEgCQl7MCwgQW51bV9wZ19zdGF0aXN0aWNfc3RhcmVsaWQsIEZfT0lERVEs
IHswLCAwLCBGX09JREVRfX0sCiEgCQl7MCwgQW51bV9wZ19zdGF0aXN0aWNfc3RhYXR0bnVt
LCBGX0lOVDJFUSwgezAsIDAsIEZfSU5UMkVRfX0sCiEgCQl7MCwgQW51bV9wZ19zdGF0aXN0
aWNfc3Rhb3AsIEZfT0lERVEsIHswLCAwLCBGX09JREVRfX0KICAJfTsKICAJYm9vbAkJaXNu
dWxsOwogIAlIZWFwVHVwbGUJdHVwbGU7Cg==
--------------A99EE0A2D8F4D665C5BF3957--

--
Bruce Momjian
maillist@candle.pha.pa.us

Bruce Momjian wrote:

OK, I have looked at this, but can't figure out how to fix the many
initializations of ScanKeyData. Can someone who understands this please
submit a patch for all these initializations so we can stop these vacuum
analyze reports?

Vadim has found the problem, but we need someone to properly fix it.

Just apply my patch to stop "analyze-problem" reports (sorry, I didn't it).
As for other (possible) places, note that ScanKeyEntryInitialize()
initializes sk_func.fn_oid and so we have to worry about hard-coded
initializations only (when ScanKeyEntryInitialize() is not called).

Vadim

This is a multi-part message in MIME format.
--------------A99EE0A2D8F4D665C5BF3957
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

James Hughes wrote:

After poking arround some more, I found that the "vacuum analyze" is
causing problems with the "<" and ">" operators. The "> 0" in the SELECT
for "/d <table>" and "/dS" commands in psql cause the error.

I verified that any simple query using the "<" or ">" operators fail
with the same message...

ERROR: fmgr_info: function 0: cache lookup failed

...after using the "vacuum analyse" command.
But, only after vacuuming any relation that was created and populated by
me. Vacumming system catalogs poses no problems.

Well, I found that this problem was caused by selfuncs.c:gethilokey():

static ScanKeyData key[3] = {
{0, Anum_pg_statistic_starelid, F_OIDEQ},
{0, Anum_pg_statistic_staattnum, F_INT2EQ},
{0, Anum_pg_statistic_staop, F_OIDEQ}

: skankeys are hardcoded without call to ScanKeyEntryInitialize() =>
without initialization of sk_func.fn_oid required, I assume, by
new PL support code. Patch for this place follows...
One should check all places where ScanKeyData is used.
Jan, could you do this ?

(Oh, hell! I got this ERROR while testing subselect and spent so many time
to fix this problem...)

I assume we can consider this item closed.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

On Sun, 15 Mar 1998, Bruce Momjian wrote:

: >
: > This is a multi-part message in MIME format.
: > --------------A99EE0A2D8F4D665C5BF3957
: > Content-Type: text/plain; charset=us-ascii
: > Content-Transfer-Encoding: 7bit
: >
: > James Hughes wrote:
: > >
: > > After poking arround some more, I found that the "vacuum analyze" is
: > > causing problems with the "<" and ">" operators. The "> 0" in the SELECT
: > > for "/d <table>" and "/dS" commands in psql cause the error.
: > >
: > > I verified that any simple query using the "<" or ">" operators fail
: > > with the same message...
: > >
: > > ERROR: fmgr_info: function 0: cache lookup failed
: > >
: > > ...after using the "vacuum analyse" command.
: > > But, only after vacuuming any relation that was created and populated by
: > > me. Vacumming system catalogs poses no problems.
: >
: > Well, I found that this problem was caused by selfuncs.c:gethilokey():
: >
: > static ScanKeyData key[3] = {
: > {0, Anum_pg_statistic_starelid, F_OIDEQ},
: > {0, Anum_pg_statistic_staattnum, F_INT2EQ},
: > {0, Anum_pg_statistic_staop, F_OIDEQ}
: >
: > : skankeys are hardcoded without call to ScanKeyEntryInitialize() =>
: > without initialization of sk_func.fn_oid required, I assume, by
: > new PL support code. Patch for this place follows...
: > One should check all places where ScanKeyData is used.
: > Jan, could you do this ?
: >
: > (Oh, hell! I got this ERROR while testing subselect and spent so many time
: > to fix this problem...)
:
: I assume we can consider this item closed.
:

The problem on my system was fixed by the patch.

-James