pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedObscure problem due to high system OID or privileges?
3+ messages / 3 participants
[nested] [flat]
* Obscure problem due to high system OID or privileges?
@ 2011-01-03 14:16 Jan-Peter Seifert <Jan-Peter.Seifert@gmx.de>
2011-01-03 16:05 ` Re: Obscure problem due to high system OID or privileges? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Jan-Peter Seifert @ 2011-01-03 14:16 UTC (permalink / raw)
To: pgsql-admin
Hello,
we have a strange problem with a server instance:
PostgreSQL 8.2.11 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.3 20070929 (prerelease) (Ubuntu 4.1.2-16ubuntu2)
It's one of several clones (server/system) where one of our applications cannot work with a specific database restored from a dump of a database on a 8.2.18 server and created with the server's binaries. Recreating the database with template0 again and doing a new restore didn't help. There are no error messages during backup/restore.
The same database/app works fine on the other clones.
The same app works fine after we put the database on a different instance. So it seems to be a specific problem with this server instance. Other databases on this instance seem to work fine though.
During 'start up' the application does a couple of SELECTs on pg_catalog:
.
.
.
SELECT COUNT(*) FROM pg_attribute JOIN pg_type ON pg_attribute.atttypid=pg_type.oid LEFT OUTER JOIN pg_attrdef ON ( pg_attribute.attnum=pg_attrdef.adnum AND pg_attribute.attrelid=pg_attrdef.adrelid ) WHERE pg_attribute.attrelid = 2147483647 AND pg_attribute.attnum >= 0 AND pg_attribute.attisdropped = 'false'
.
.
.
It seems that the application doesn't find some rows. There are no error messages in the server's log though.
The only two obvious differences we found are:
1. The superuser 'nutzer' used for the session had been given two roles ('nutzerrolle' and 'nutzeradmin') in the problematic server instance:
CREATE ROLE nutzerrolle
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
CREATE ROLE nutzeradmin
SUPERUSER INHERIT CREATEDB CREATEROLE;
UPDATE pg_authid SET rolcatupdate=true WHERE OID=9413195::oid;
CREATE ROLE nutzer LOGIN
ENCRYPTED PASSWORD 'xxxx'
SUPERUSER INHERIT CREATEDB CREATEROLE;
UPDATE pg_authid SET rolcatupdate=true WHERE OID=2147483647::oid;
Removing these group memberships didn't help though. Recreating the user didn't help neither.
2. Very high system OIDs - e.g. pg_attribute.attrelid max 3318384368 where another server had max 94363332. The application connects via psqlODBC (8.4.200).
Is this maybe a known problem?
Could you tell me, please?
Thank you very much,
Peter
--
NEU: FreePhone - kostenlos mobil telefonieren und surfen!
Jetzt informieren: http://www.gmx.net/de/go/freephone
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Obscure problem due to high system OID or privileges?
2011-01-03 14:16 Obscure problem due to high system OID or privileges? Jan-Peter Seifert <Jan-Peter.Seifert@gmx.de>
@ 2011-01-03 16:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-01-03 16:29 ` Re: Obscure problem due to high system OID or privileges? Jan-Peter.Seifert@gmx.de
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2011-01-03 16:05 UTC (permalink / raw)
To: Jan-Peter Seifert <Jan-Peter.Seifert@gmx.de>; +Cc: pgsql-admin
"Jan-Peter Seifert" <Jan-Peter.Seifert@gmx.de> writes:
> During 'start up' the application does a couple of SELECTs on pg_catalog:
> SELECT COUNT(*) FROM pg_attribute JOIN pg_type ON pg_attribute.atttypid=pg_type.oid LEFT OUTER JOIN pg_attrdef ON ( pg_attribute.attnum=pg_attrdef.adnum AND pg_attribute.attrelid=pg_attrdef.adrelid ) WHERE pg_attribute.attrelid = 2147483647 AND pg_attribute.attnum >= 0 AND pg_attribute.attisdropped = 'false'
Is the table it's looking for really exactly OID 2147483647 (ie 2^31-1)?
That seems a bit improbable. I suspect something in your app is choking
on an OID that is in fact larger than that, and it's somehow getting
clamped to INT_MAX.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Obscure problem due to high system OID or privileges?
2011-01-03 14:16 Obscure problem due to high system OID or privileges? Jan-Peter Seifert <Jan-Peter.Seifert@gmx.de>
2011-01-03 16:05 ` Re: Obscure problem due to high system OID or privileges? Tom Lane <tgl@sss.pgh.pa.us>
@ 2011-01-03 16:29 ` Jan-Peter.Seifert@gmx.de
0 siblings, 0 replies; 3+ messages in thread
From: Jan-Peter.Seifert@gmx.de @ 2011-01-03 16:29 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
Hello,
-------- Original-Nachricht --------
> Datum: Mon, 03 Jan 2011 11:05:30 -0500
> Von: Tom Lane <tgl@sss.pgh.pa.us>
> An: "Jan-Peter Seifert" <Jan-Peter.Seifert@gmx.de>
> CC: pgsql-admin@postgresql.org
> Betreff: Re: [ADMIN] Obscure problem due to high system OID or privileges?
> "Jan-Peter Seifert" <Jan-Peter.Seifert@gmx.de> writes:
> > During 'start up' the application does a couple of SELECTs on
> pg_catalog:
>
> > SELECT COUNT(*) FROM pg_attribute JOIN pg_type ON
> pg_attribute.atttypid=pg_type.oid LEFT OUTER JOIN pg_attrdef ON (
> pg_attribute.attnum=pg_attrdef.adnum AND pg_attribute.attrelid=pg_attrdef.adrelid ) WHERE
> pg_attribute.attrelid = 2147483647 AND pg_attribute.attnum >= 0 AND
> pg_attribute.attisdropped = 'false'
>
> Is the table it's looking for really exactly OID 2147483647 (ie 2^31-1)?
> That seems a bit improbable. I suspect something in your app is choking
> on an OID that is in fact larger than that, and it's somehow getting
> clamped to INT_MAX.
Err - that's really very suspicious as it's a 32-Bit application. We'll test it again with a clone of the server instance as I'm not sure whether it was a log from a try on the current database restore. Could take some time though.
Thank you very much!
Peter
--
GMX DSL Doppel-Flat ab 19,99 Euro/mtl.! Jetzt mit
gratis Handy-Flat! http://portal.gmx.net/de/go/dsl
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-01-03 16:29 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-01-03 14:16 Obscure problem due to high system OID or privileges? Jan-Peter Seifert <Jan-Peter.Seifert@gmx.de>
2011-01-03 16:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-01-03 16:29 ` Jan-Peter.Seifert@gmx.de
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox