Thread: Schema bug
Hi, I found a weird bug today. If you rename the public schema, it becomes unavailable. Here is a patch to fix it. It modifies the query to use the oid instead of the schema's name. Works great on Linux, should'nt be a problem on win32. Regards. -- Guillaume. <!-- http://abs.traduc.org/ http://lfs.traduc.org/ http://traduc.postgresqlfr.org/ -->
Attachment
Guillaume LELARGE wrote: > Hi, > > I found a weird bug today. If you rename the public schema, it becomes > unavailable. Here is a patch to fix it. It modifies the query to use the oid > instead of the schema's name. Works great on Linux, should'nt be a problem on > win32. Actually, to me renaming the public schema appears as the primary bug... There are many ways to corrupt pgAdmin's behaviour, and you found one of 'em. Renaming public is so irregular, I doubt it's worth changing the behaviour. Regards, Andreas
2005/12/7, Andreas Pflug <pgadmin@pse-consulting.de>: > Guillaume LELARGE wrote: > > Hi, > > > > I found a weird bug today. If you rename the public schema, it becomes > > unavailable. Here is a patch to fix it. It modifies the query to use the oid > > instead of the schema's name. Works great on Linux, should'nt be a problem on > > win32. > > Actually, to me renaming the public schema appears as the primary bug... > There are many ways to corrupt pgAdmin's behaviour, and you found one of > 'em. Renaming public is so irregular, I doubt it's worth changing the > behaviour. > If you're right, pgAdmin should not allow to change the public schema's name. It's really dangerous if renamming/dropping the public shema's name can corrupt pgAdmin's behavior. PostgreSQL's documentation says : There is nothing special about the public schema except that it exists by default. It can be dropped, too. (see http://www.postgresql.org/docs/8.1/interactive/ddl-schemas.html#DDL-SCHEMAS-PATH) It even says that : Also, there is no concept of a public schema in the SQL standard. For maximum conformance to the standard, you should not use (perhaps even remove) the public schema. (see http://www.postgresql.org/docs/8.1/interactive/ddl-schemas.html#DDL-SCHEMAS-PORTABILITY) Regards. -- Guillaume.
Andreas Pflug wrote: > Guillaume LELARGE wrote: > >> Hi, >> >> I found a weird bug today. If you rename the public schema, it becomes >> unavailable. Here is a patch to fix it. It modifies the query to use >> the oid instead of the schema's name. Works great on Linux, should'nt >> be a problem on win32. > > Actually, to me renaming the public schema appears as the primary bug... > There are many ways to corrupt pgAdmin's behaviour, and you found one of > 'em. Renaming public is so irregular, I doubt it's worth changing the > behaviour. This argument scares me... I believe a GUI-Tool shouldn't impose any additional restrictions to what you can do with your database - otherwise GUI-Users become second-class citzicens when compared to those who use the commandline/psql. Why exactly does pgadmin depend on the existance of the public schema? greetings, Florian Pflug
Attachment
> -----Original Message----- > From: pgadmin-hackers-owner@postgresql.org > [mailto:pgadmin-hackers-owner@postgresql.org] On Behalf Of > Florian G. Pflug > Sent: 08 December 2005 15:33 > To: Andreas Pflug; pgadmin-hackers > Subject: Re: [pgadmin-hackers] Schema bug > > Andreas Pflug wrote: > > Guillaume LELARGE wrote: > > > >> Hi, > >> > >> I found a weird bug today. If you rename the public > schema, it becomes > >> unavailable. Here is a patch to fix it. It modifies the > query to use > >> the oid instead of the schema's name. Works great on > Linux, should'nt > >> be a problem on win32. > > > > Actually, to me renaming the public schema appears as the > primary bug... > > There are many ways to corrupt pgAdmin's behaviour, and you > found one of > > 'em. Renaming public is so irregular, I doubt it's worth > changing the > > behaviour. > > This argument scares me... I believe a GUI-Tool shouldn't impose any > additional restrictions to what you can do with your database - > otherwise GUI-Users become second-class citzicens when > compared to those > who use the commandline/psql. Why exactly does pgadmin depend on > the existance of the public schema? The first thing that springs to mind is that we can only tell that it is *not* a system schema from it's name. The normal test (oid < LAST_SYSTEM_OID) doesn't work because it's created during (in template1/template0 at least). /D
Florian G. Pflug wrote: > Andreas Pflug wrote: > >> Guillaume LELARGE wrote: >> >>> Hi, >>> >>> I found a weird bug today. If you rename the public schema, it >>> becomes unavailable. Here is a patch to fix it. It modifies the query >>> to use the oid instead of the schema's name. Works great on Linux, >>> should'nt be a problem on win32. >> >> >> Actually, to me renaming the public schema appears as the primary bug... >> There are many ways to corrupt pgAdmin's behaviour, and you found one >> of 'em. Renaming public is so irregular, I doubt it's worth changing >> the behaviour. > > > This argument scares me... I believe a GUI-Tool shouldn't impose any > additional restrictions to what you can do with your database - > otherwise GUI-Users become second-class citzicens when compared to those > who use the commandline/psql. Why exactly does pgadmin depend on > the existance of the public schema? It does *not* depend on its existence. Regards, Andreas
Dave Page wrote: >>Andreas Pflug wrote: >>>Guillaume LELARGE wrote: >>>>Hi, >>>>I found a weird bug today. If you rename the public >>>>schema, it becomes >>>>unavailable. Here is a patch to fix it. It modifies the >>>>query to use >>>>the oid instead of the schema's name. Works great on >>>>Linux, should'nt >>>>be a problem on win32. >>> >>>Actually, to me renaming the public schema appears as the >>>primary bug... >>>There are many ways to corrupt pgAdmin's behaviour, and you >>>found one of >>>'em. Renaming public is so irregular, I doubt it's worth >>>changing the >>>behaviour. >> >>This argument scares me... I believe a GUI-Tool shouldn't impose any >>additional restrictions to what you can do with your database - >>otherwise GUI-Users become second-class citzicens when >>compared to those >>who use the commandline/psql. Why exactly does pgadmin depend on >>the existance of the public schema? > > The first thing that springs to mind is that we can only tell that it is > *not* a system schema from it's name. The normal test (oid < > LAST_SYSTEM_OID) doesn't work because it's created during (in > template1/template0 at least). Hm.. couldn't it check the system-schemas by name instead - so, saying "it's a non-system schema if it isn't called information_schema or pg_catalog", instead of "it's a system-schema if oid < LAST_SYSTEM_OID and name is not public"? Or are there other system schemas I don't know of (quite possible ;-) )? greetings, Florian Pflug
> -----Original Message----- > From: Florian G. Pflug [mailto:fgp@phlo.org] > Sent: 09 December 2005 01:15 > To: Dave Page > Cc: Andreas Pflug; pgadmin-hackers > Subject: Re: [pgadmin-hackers] Schema bug > > Hm.. couldn't it check the system-schemas by name instead - so, saying > "it's a non-system schema if it isn't called information_schema or > pg_catalog", instead of "it's a system-schema if oid < > LAST_SYSTEM_OID > and name is not public"? Or are there other system schemas I > don't know > of (quite possible ;-) )? Probably - but it's more of a future-proofing kinda thing. Pretty weak argument though... /D
Le Vendredi 09 Décembre 2005 09:21, Dave Page a écrit : > > -----Original Message----- > > From: Florian G. Pflug [mailto:fgp@phlo.org] > > Sent: 09 December 2005 01:15 > > To: Dave Page > > Cc: Andreas Pflug; pgadmin-hackers > > Subject: Re: [pgadmin-hackers] Schema bug > > > > Hm.. couldn't it check the system-schemas by name instead - so, saying > > "it's a non-system schema if it isn't called information_schema or > > pg_catalog", instead of "it's a system-schema if oid < > > LAST_SYSTEM_OID > > and name is not public"? Or are there other system schemas I > > don't know > > of (quite possible ;-) )? > > Probably - but it's more of a future-proofing kinda thing. Pretty weak > argument though... > I had two ways to fix this bug : detect system schemas on their names, or detect public schema by its OID. I think it's better to only keep the public schema with its OID, it's much less risky to have a wrong one. That's what the patch is doing : AND nspname != 'public' is replaced by AND nsp.oid != 2200 I still think this patch should be applied. -- Guillaume. <!-- http://abs.traduc.org/ http://lfs.traduc.org/ http://traduc.postgresqlfr.org/ -->
> -----Original Message----- > From: pgadmin-hackers-owner@postgresql.org > [mailto:pgadmin-hackers-owner@postgresql.org] On Behalf Of > Guillaume LELARGE > Sent: 09 December 2005 22:43 > To: pgadmin-hackers@postgresql.org > Subject: Re: [pgadmin-hackers] Schema bug > > Le Vendredi 09 Décembre 2005 09:21, Dave Page a écrit : > > > -----Original Message----- > > > From: Florian G. Pflug [mailto:fgp@phlo.org] > > > Sent: 09 December 2005 01:15 > > > To: Dave Page > > > Cc: Andreas Pflug; pgadmin-hackers > > > Subject: Re: [pgadmin-hackers] Schema bug > > > > > > Hm.. couldn't it check the system-schemas by name instead > - so, saying > > > "it's a non-system schema if it isn't called information_schema or > > > pg_catalog", instead of "it's a system-schema if oid < > > > LAST_SYSTEM_OID > > > and name is not public"? Or are there other system schemas I > > > don't know > > > of (quite possible ;-) )? > > > > Probably - but it's more of a future-proofing kinda thing. > Pretty weak > > argument though... > > > > I had two ways to fix this bug : detect system schemas on > their names, or > detect public schema by its OID. I think it's better to only > keep the public > schema with its OID, it's much less risky to have a wrong > one. That's what > the patch is doing : > AND nspname != 'public' > is replaced by > AND nsp.oid != 2200 > > I still think this patch should be applied. Does anyone see a reason /not/ to do this? Regards, Dave.
Dave Page wrote: >> >>I still think this patch should be applied. > > > Does anyone see a reason /not/ to do this? Hm, Guillaume started a thread on pgsql-hackers about renaming system schemas in general, maybe we should wait for the result of that discussion. Regards, Andreas
Dave Page wrote: >>I had two ways to fix this bug : detect system schemas on >>their names, or >>detect public schema by its OID. I think it's better to only >>keep the public >>schema with its OID, it's much less risky to have a wrong >>one. That's what >>the patch is doing : >> AND nspname != 'public' >>is replaced by >> AND nsp.oid != 2200 >> >>I still think this patch should be applied. > > Does anyone see a reason /not/ to do this? I'd prefer system-catalogs being excluded by name - preferably the exclusion-list would be editable, and part of the pgadmin preferences. Seems more transparent to me - and future-proof, in the sense that even if a future postgres version chooses to rename some catalog, the user will be able to just add the new names to the exclusion list. greetings, Florian Pflug
Florian G. Pflug wrote: > > I'd prefer system-catalogs being excluded by name - preferably the > exclusion-list would be editable, and part of the pgadmin preferences. > Seems more transparent to me - and future-proof, in the sense that > even if a future postgres version chooses to rename some catalog, the > user will be able to just add the new names to the exclusion list. There's already a database restriction and a schema restriction, just use it. Regards, Andreas
Andreas Pflug wrote: > Florian G. Pflug wrote: >> I'd prefer system-catalogs being excluded by name - preferably the >> exclusion-list would be editable, and part of the pgadmin preferences. >> Seems more transparent to me - and future-proof, in the sense that >> even if a future postgres version chooses to rename some catalog, the >> user will be able to just add the new names to the exclusion list. > > There's already a database restriction and a schema restriction, just > use it. Ups... Should have checked before posting... Doesn't invalidate my point, though ;-) BTW, I just checked the postgresql CVS, and found the in the beginnings of schema-support in pgsql, the public schema had oid 2071. It got changed to 2200 before 7.3 was released, though. greetings, Florian Pflug
FWIW, here's what we ended up going with in newsysviews:
create or replace function _pg_sv_system_schema(name) returns boolean
as 'select $1 in (name ''pg_catalog'', name ''pg_toast'',
name ''pg_sysviews'', name ''information_schema'')'
language sql immutable strict;
This was after someone dug into pg_dump to see what it considers a
system schema (AndrewSN I believe).
On Tue, Dec 13, 2005 at 12:29:01AM +0100, Florian G. Pflug wrote:
> Andreas Pflug wrote:
> > Florian G. Pflug wrote:
> >> I'd prefer system-catalogs being excluded by name - preferably the
> >> exclusion-list would be editable, and part of the pgadmin preferences.
> >> Seems more transparent to me - and future-proof, in the sense that
> >> even if a future postgres version chooses to rename some catalog, the
> >> user will be able to just add the new names to the exclusion list.
> >
> > There's already a database restriction and a schema restriction, just
> > use it.
> Ups... Should have checked before posting... Doesn't invalidate my
> point, though ;-)
>
> BTW, I just checked the postgresql CVS, and found the in the beginnings
> of schema-support in pgsql, the public schema had oid 2071. It got
> changed to 2200 before 7.3 was released, though.
>
> greetings, Florian Pflug
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
--
Jim C. Nasby, Database Architect decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
Le Samedi 10 Décembre 2005 16:55, Andreas Pflug a écrit : > Dave Page wrote: > >>I still think this patch should be applied. > > > > Does anyone see a reason /not/ to do this? > > Hm, Guillaume started a thread on pgsql-hackers about renaming system > schemas in general, maybe we should wait for the result of that discussion. > Well, it seems that pg_* schemas are system schemas and that public and information_schema schemas are public one. I think we should exclude system schemas depending on their names and show public and information_schema, whatever their actual name are. I can bring a new patch if you agree with this and if you find this useful. -- Guillaume. <!-- http://abs.traduc.org/ http://lfs.traduc.org/ http://traduc.postgresqlfr.org/ -->
On Tue, Dec 13, 2005 at 12:48:55AM +0100, Guillaume LELARGE wrote: > Well, it seems that pg_* schemas are system schemas and that public and > information_schema schemas are public one. I think we should exclude system > schemas depending on their names and show public and information_schema, > whatever their actual name are. BTW, it'd probably be useful to have an easy way to show everything. It's not that uncommon to want to go into the catalog tables... -- Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
Jim C. Nasby wrote: > On Tue, Dec 13, 2005 at 12:48:55AM +0100, Guillaume LELARGE wrote: > >>Well, it seems that pg_* schemas are system schemas and that public and >>information_schema schemas are public one. I think we should exclude system >>schemas depending on their names and show public and information_schema, >>whatever their actual name are. > > > BTW, it'd probably be useful to have an easy way to show everything. > It's not that uncommon to want to go into the catalog tables... This option is called "system objects" and is present in pgadmin since the very beginning... Regards, Andreas
Le Mardi 13 Décembre 2005 05:58, Andreas Pflug a écrit : > Jim C. Nasby wrote: > > On Tue, Dec 13, 2005 at 12:48:55AM +0100, Guillaume LELARGE wrote: > >>Well, it seems that pg_* schemas are system schemas and that public and > >>information_schema schemas are public one. I think we should exclude > >> system schemas depending on their names and show public and > >> information_schema, whatever their actual name are. > > > > BTW, it'd probably be useful to have an easy way to show everything. > > It's not that uncommon to want to go into the catalog tables... > > This option is called "system objects" and is present in pgadmin since > the very beginning... > And this is the only way to see a renammed public schema. -- Guillaume. <!-- http://abs.traduc.org/ http://lfs.traduc.org/ http://traduc.postgresqlfr.org/ -->
Le Mardi 13 Décembre 2005 00:48, Guillaume LELARGE a écrit : > Le Samedi 10 Décembre 2005 16:55, Andreas Pflug a écrit : > > Dave Page wrote: > > >>I still think this patch should be applied. > > > > > > Does anyone see a reason /not/ to do this? > > > > Hm, Guillaume started a thread on pgsql-hackers about renaming system > > schemas in general, maybe we should wait for the result of that > > discussion. > > Well, it seems that pg_* schemas are system schemas and that public and > information_schema schemas are public one. I think we should exclude system > schemas depending on their names and show public and information_schema, > whatever their actual name are. > > I can bring a new patch if you agree with this and if you find this useful. What about this one ? -- Guillaume. <!-- http://abs.traduc.org/ http://lfs.traduc.org/ http://traduc.postgresqlfr.org/ -->
Attachment
> -----Original Message----- > From: pgadmin-hackers-owner@postgresql.org > [mailto:pgadmin-hackers-owner@postgresql.org] On Behalf Of > Guillaume LELARGE > Sent: 13 December 2005 23:44 > To: pgadmin-hackers@postgresql.org > Subject: Re: [pgadmin-hackers] Schema bug > > Le Mardi 13 Décembre 2005 00:48, Guillaume LELARGE a écrit : > > Le Samedi 10 Décembre 2005 16:55, Andreas Pflug a écrit : > > > Dave Page wrote: > > > >>I still think this patch should be applied. > > > > > > > > Does anyone see a reason /not/ to do this? > > > > > > Hm, Guillaume started a thread on pgsql-hackers about > renaming system > > > schemas in general, maybe we should wait for the result of that > > > discussion. > > > > Well, it seems that pg_* schemas are system schemas and > that public and > > information_schema schemas are public one. I think we > should exclude system > > schemas depending on their names and show public and > information_schema, > > whatever their actual name are. > > > > I can bring a new patch if you agree with this and if you > find this useful. > > What about this one ? I don't have a problem with that. Anyone else? /D
Dave Page wrote: > > > >>-----Original Message----- >>From: pgadmin-hackers-owner@postgresql.org >>[mailto:pgadmin-hackers-owner@postgresql.org] On Behalf Of >>Guillaume LELARGE >>Sent: 13 December 2005 23:44 >>To: pgadmin-hackers@postgresql.org >>Subject: Re: [pgadmin-hackers] Schema bug >> >>Le Mardi 13 Décembre 2005 00:48, Guillaume LELARGE a écrit : >> >>>Le Samedi 10 Décembre 2005 16:55, Andreas Pflug a écrit : >>> >>>>Dave Page wrote: >>>> >>>>>>I still think this patch should be applied. >>>>> >>>>>Does anyone see a reason /not/ to do this? >>>> >>>>Hm, Guillaume started a thread on pgsql-hackers about >> >>renaming system >> >>>>schemas in general, maybe we should wait for the result of that >>>>discussion. >>> >>>Well, it seems that pg_* schemas are system schemas and >> >>that public and >> >>>information_schema schemas are public one. I think we >> >>should exclude system >> >>>schemas depending on their names and show public and >> >>information_schema, >> >>>whatever their actual name are. >>> >>>I can bring a new patch if you agree with this and if you >> >>find this useful. >> >>What about this one ? > > > I don't have a problem with that. Anyone else? I *do* have a problem if information_schema becomes non-system. For pgsql-core this is non-system, but a user would consider this system and like to have its display suppressed for day-to-day work. I'm still not convinced we need to do anything. Renaming public is highly irregular, and finally showing system objects will make it reappear. The schema restriction allows individual filters who likes it. Regards, Andreas
> -----Original Message----- > From: Andreas Pflug [mailto:pgadmin@pse-consulting.de] > Sent: 14 December 2005 10:27 > To: Dave Page > Cc: Guillaume LELARGE; pgadmin-hackers@postgresql.org > Subject: Re: [pgadmin-hackers] Schema bug > > > I don't have a problem with that. Anyone else? > > I *do* have a problem if information_schema becomes non-system. For > pgsql-core this is non-system, but a user would consider this > system and > like to have its display suppressed for day-to-day work. Yeah, well, apart from that bit. > I'm still not convinced we need to do anything. Renaming public is > highly irregular, and finally showing system objects will make it > reappear. The schema restriction allows individual filters > who likes it. Renaming public is irregular, but if we can allow it without breaking anything else, then I see no reason why we shouldn't do it. /D
Dave Page wrote: > >>I'm still not convinced we need to do anything. Renaming public is >>highly irregular, and finally showing system objects will make it >>reappear. The schema restriction allows individual filters >>who likes it. > > > Renaming public is irregular, but if we can allow it without breaking > anything else, then I see no reason why we shouldn't do it. So for god's sake do implement it, but in general I'm less than inclined to implement workarounds for people doing weird things to the db. I'm waiting for the guy who claims that his "was-public" schema suddenly reapperars in pgadmin, while he just renamed it to have it hidden from the users.... There *are* admins that deliberately rename pg objects to hide them from pgadmin's sight. Regards, Andreas
Le Mercredi 14 Décembre 2005 18:14, Andreas Pflug a écrit : > Dave Page wrote: > >>I'm still not convinced we need to do anything. Renaming public is > >>highly irregular, and finally showing system objects will make it > >>reappear. The schema restriction allows individual filters > >>who likes it. > > > > Renaming public is irregular, but if we can allow it without breaking > > anything else, then I see no reason why we shouldn't do it. > > So for god's sake do implement it, but in general I'm less than inclined > to implement workarounds for people doing weird things to the db. I'm > waiting for the guy who claims that his "was-public" schema suddenly > reapperars in pgadmin, while he just renamed it to have it hidden from > the users.... There *are* admins that deliberately rename pg objects to > hide them from pgadmin's sight. > I can understand some of your feelings but not this one. If an admin don't want the public schema, they just have to drop it. Juste take a look at the PostgreSQL manual : « There is nothing special about the public schema except that it exists by default. It can be dropped, too. » « Also, there is no concept of a public schema in the SQL standard. For maximum conformance to the standard, you should not use (perhaps even remove) the public schema. » From http://www.postgresql.org/docs/8.1/static/ddl-schemas.html -- Guillaume. <!-- http://abs.traduc.org/ http://lfs.traduc.org/ http://traduc.postgresqlfr.org/ -->
2005/12/15, Dave Page <dpage@vale-housing.co.uk>: > > Le Mardi 13 Décembre 2005 00:48, Guillaume LELARGE a écrit: > > > Le Samedi 10 Décembre 2005 16:55, Andreas Pflug a écrit: > > > > Dave Page wrote: > > > > >>I still think this patch should be applied. > > > > > > > > > > Does anyone see a reason /not/ to do this? > > > > > > > > Hm, Guillaume started a thread on pgsql-hackers about > > renaming system > > > > schemas in general, maybe we should wait for the result of that > > > > discussion. > > > > > > Well, it seems that pg_* schemas are system schemas and > > that public and > > > information_schema schemas are public one. I think we > > should exclude system > > > schemas depending on their names and show public and > > information_schema, > > > whatever their actual name are. > > > > > > I can bring a new patch if you agree with this and if you > > find this useful. > > > > What about this one ? > > I've applied a modified version of this patch - thanks. > I'm OK with your changes. Thanks for applying. -- Guillaume.