select * from pg_database order by datname failed : missing support function 1 for attribute 1 of index "pg_opclass_oid_index

Any operation (like backups or "disable Pgsql feature") will fail with the following message:

".. PostgreSQL database failed! : SQL select * from pg_database order by datname failed : missing support function 1 for attribute 1 of index "pg_opclass_oid_index" at ../web-lib-funcs.pl line 1397, line 1."

Any ideas what may cause this?

Status: 
Active

Comments

Which Linux distribution and version are you running there? Also, do you have the version of PostgreSQL that came with your OS, or did you do a custom install?

Hi Jamie,

This is CentOS 6 64bit, stock Postgresql 8.4.20-1.el6_5. I have not customised anything as far as I can tell.

That's very odd - I don't see this on any of my test systems.

If you connect to PostgreSQL from the command line and run a query like select * from pg_database , does it work OK?

Yep, that works fine, outputs a bunch of DBs I have around (all unused). All I'm seeing in the webmin logs is this in miniserv.error:

Error: SQL select * from pg_database order by datname failed : missing support function 1 for attribute 1 of index "pg_opclass_oid_index"

The above happens when I try to go in the "Edit databases" of an account which has a pgsql db.

How about if you try the full command Webmin uses select * from pg_database order by datname

Yes, I tried that as well in my previous attempt, also as user postgres, it works just fine, displays some stuff, no errors.

Also found a work around, stopping postgresql altogether no longer produces that error, virtualmin will simply show me the mysql dbs. Still, it's not kosher..

How many PostgreSQL database do you have that are being managed by Virtualmin?

They're 7 all in all (except postgres, template0 and template1), all managed by Virtualmin. None are in use, they were just created because the Pgsql feature was enabled by default.

It's odd that Virtualmin was able to create these DBs, but cannot manage them. If you create a new test domain, does it also get a PostgreSQL database without any error?

Jamie,

I think the domains were created some time ago when stuff was still working, I tried to add a new domain with pgsql enabled and I got the error and the creation process has failed:

Failed to create virtual server : SQL select * from pg_database order by datname failed : missing support function 1 for attribute 1 of index "pg_opclass_oid_index"

So what changed on your system in the period between when it worked, and when it stopped working? Any upgrade or re-configuration of PostgreSQL?

FYI, on a test system running CentOS 6 with the latest Webmin, Virtualmin and PostgreSQL packages, I don't see this error.

Jamie,

Would it be possible to post your working pgsql cfg files somewhere for comparison?

Sure .. but which specific config file are you interested in? PostgreSQL has several..

Say pg_hba.conf, pg_ident.conf and postgresql.conf?

I did a little digging, and it doesn't actually appear that any of those config files are setup on a typical/default CentOS install.

They are all there on Ubuntu/Debian installs, but I don't see any of them on any of the CentOS installations I checked.

If you're seeing those files are installed into /etc/ on your server, you might consider temporarily moving/renaming them to see if that causes Postgres to then run with the defaults.

Alternatively, it looks like the Postgres package ships with samples of all those files in "/usr/share/pgsql/".

If not a config file or setting issue, it sounds a bit like you may be seeing some sort of data corruption issue.

We've unfortunately never seen anything like what you're describing before.

Jamie,

The files I was mentioning are in /var/lib/pgsql/data/

I do not really use pgsql for anything. How could I manually force this feature off in Virtualmin? Trying to uncheck the feature complains that several servers have the option on.. and I can't set the option off in those accounts because of the said error.

I had done a system-wide "find" for those files, and none of them exist on any of my CentOS servers, except for the samples in "/usr/share/pgsql/".

Postgres is working properly on those particular servers.

To disable the Postgres feature, you may need to 'cd' into '/etc/webmin/virtual-server/domains', and there, grep all the files in that directory for "postgres=".

In each of those files, if you see "postgres=1", you'd want to set that to "postgres=0".

And if "db_postgres" shows any databases associated with that domain -- that parameter should be blank. It should simply read "db_postgres=".

After making changes to those files (each one represents a domain on your server), you would want to restart Webmin, which you can do with:

/etc/init.d/webmin restart

Thanks, your instructions worked!

Re conf files, when you do "service postgresql initdb" (otherwise you can't start the service), then these config files get generated.

Hello, I got this error after an application update (which who knows what did to one of the db it uses). Got any idea how to solve this?