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

Submitted by Lucian on Thu, 10/16/2014 - 18:27

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

Submitted by JamieCameron on Thu, 10/16/2014 - 21:57

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?

Submitted by Lucian on Fri, 10/17/2014 - 04:33

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.

Submitted by JamieCameron on Fri, 10/17/2014 - 11:16

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?

Submitted by Lucian on Fri, 10/17/2014 - 12:58

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.

Submitted by JamieCameron on Fri, 10/17/2014 - 17:39

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

Submitted by Lucian on Fri, 10/17/2014 - 20:52

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

Submitted by Lucian on Fri, 10/17/2014 - 20:54

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..

Submitted by JamieCameron on Sat, 10/18/2014 - 00:00

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

Submitted by Lucian on Sat, 10/18/2014 - 07:19

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.

Submitted by JamieCameron on Sat, 10/18/2014 - 17:40

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?

Submitted by Lucian on Sun, 10/19/2014 - 10:25

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"

Submitted by JamieCameron on Sun, 10/19/2014 - 10:40

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.

Submitted by Lucian on Mon, 10/20/2014 - 05:41

Jamie,

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

Submitted by JamieCameron on Mon, 10/20/2014 - 08:53

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

Submitted by Lucian on Mon, 10/20/2014 - 09:54

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

Submitted by andreychek on Mon, 10/20/2014 - 19:17

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.

Submitted by Lucian on Tue, 10/21/2014 - 05:17

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.

Submitted by andreychek on Tue, 10/21/2014 - 09:55

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

Submitted by Lucian on Tue, 10/21/2014 - 11:00

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.

Submitted by peloton on Mon, 05/20/2019 - 06:51

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?