Thread: Re: [BUGS] postgresql-7.4RC1 - unrecognized privilege type when selecting from information_schema.tables

<bvctravel@yahoo.com> writes:
>    select * from information_schema.tables;
>    ERROR:  unrecognized privilege type: "RERERENCES"

> Replacing the word "RERERENCES" with "REFERENCES" in
> the predicate "has_table_privilege(c.oid,
> 'RERERENCES'::text)" near the end of the view SQL
> seems to correct the problem.

Good catch.  There are two other places with the same typo :-( (all
copied and pasted no doubt).

I've applied the patch but am loathe to force an initdb this late in
the beta cycle.  Any opinions out there?
        regards, tom lane

Tom Lane wrote:
> <bvctravel@yahoo.com> writes:
> >    select * from information_schema.tables;
> >    ERROR:  unrecognized privilege type: "RERERENCES"
> 
> > Replacing the word "RERERENCES" with "REFERENCES" in
> > the predicate "has_table_privilege(c.oid,
> > 'RERERENCES'::text)" near the end of the view SQL
> > seems to correct the problem.
> 
> Good catch.  There are two other places with the same typo :-( (all
> copied and pasted no doubt).
> 
> I've applied the patch but am loathe to force an initdb this late in
> the beta cycle.  Any opinions out there?

Seems if the queries are failing in current, then fixing it can't hurt ---
it changes failed queries for those that do initdb.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

On Sat, 8 Nov 2003, Tom Lane wrote:

> <bvctravel@yahoo.com> writes:
> >    select * from information_schema.tables;
> >    ERROR:  unrecognized privilege type: "RERERENCES"
>
> > Replacing the word "RERERENCES" with "REFERENCES" in
> > the predicate "has_table_privilege(c.oid,
> > 'RERERENCES'::text)" near the end of the view SQL
> > seems to correct the problem.
>
> Good catch.  There are two other places with the same typo :-( (all
> copied and pasted no doubt).
>
> I've applied the patch but am loathe to force an initdb this late in
> the beta cycle.  Any opinions out there?

Annoying as a spelling mistake is (and, from my read of the above, that is
all it is?), I don't thnk it warrants forcing an initdb ... unless I'm
missing a larger scope?

"Marc G. Fournier" <scrappy@postgresql.org> writes:
> On Sat, 8 Nov 2003, Tom Lane wrote:
>> I've applied the patch but am loathe to force an initdb this late in
>> the beta cycle.  Any opinions out there?

> Annoying as a spelling mistake is (and, from my read of the above, that is
> all it is?), I don't thnk it warrants forcing an initdb ... unless I'm
> missing a larger scope?

The problem is that the spelling mistake causes the view to fail in the
right circumstances.  I believe you need to be a non-superuser and to do
"select * from information_schema.tables" when there is at least one
table that you don't own and have no privileges for.  That's not a very
unusual set of circumstances, so it verges on saying that that view is
unusable without the fix.  information_schema.views and
information_schema.columns have the identical problem.

We do not *have* to force initdb --- any adopters of beta5 or RC1 who
run into this problem can be told to recreate those views with the
corrected definitions.  A quick "CREATE OR REPLACE VIEW" as superuser
ought to do it.  So it's just an issue of cleanliness of the release.
        regards, tom lane

Marc G. Fournier wrote:
> 
> 
> On Sat, 8 Nov 2003, Tom Lane wrote:
> 
> > <bvctravel@yahoo.com> writes:
> > >    select * from information_schema.tables;
> > >    ERROR:  unrecognized privilege type: "RERERENCES"
> >
> > > Replacing the word "RERERENCES" with "REFERENCES" in
> > > the predicate "has_table_privilege(c.oid,
> > > 'RERERENCES'::text)" near the end of the view SQL
> > > seems to correct the problem.
> >
> > Good catch.  There are two other places with the same typo :-( (all
> > copied and pasted no doubt).
> >
> > I've applied the patch but am loathe to force an initdb this late in
> > the beta cycle.  Any opinions out there?
> 
> Annoying as a spelling mistake is (and, from my read of the above, that is
> all it is?), I don't thnk it warrants forcing an initdb ... unless I'm
> missing a larger scope?

It must have a larger affect than just a spelling effect if he is seeing
an error:

> >    ERROR:  unrecognized privilege type: "RERERENCES"

I now see the question Tom had was wether we force an initdb.  Seems
this query "select * from information_schema.tables;" is broken without
it.  I think we should fix it but not force an initdb ---
information_schema is new and I am not sure how many people are using
it.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I think we should fix it but not force an initdb ---
> information_schema is new and I am not sure how many people are using
> it.

Yeah, I'm leaning that way too.  We could publicize a script to fix the
problem in any beta5 or RC1 databases that people don't want to
re-initdb.
        regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> 
>>I think we should fix it but not force an initdb ---
>>information_schema is new and I am not sure how many people are using
>>it.
> 
> Yeah, I'm leaning that way too.  We could publicize a script to fix the
> problem in any beta5 or RC1 databases that people don't want to
> re-initdb.
> 

I agree, and this brings up a question that I've pondered before. Why do 
we ever *require* and initdb when only metadata has changed (i.e. the 
contents of the system catalogs, not catalog or page structure)?

ISTM that we ought to distinguish between catalog version, meaning 
something structural has changed, and catalog-data version which is 
correctible by running a script.

Joe

Joe Conway <mail@joeconway.com> writes:
> I agree, and this brings up a question that I've pondered before. Why do 
> we ever *require* and initdb when only metadata has changed (i.e. the 
> contents of the system catalogs, not catalog or page structure)?

In some cases we have to do it because there is a backend code change
that's dependent on the metadata change; that is, the backend will not
function correctly if you haven't fixed the catalog contents.  The
reverse direction (old backend, new catalogs) is also dangerous.  The
point of having a catalog version number is to ensure that the backend
and catalogs are in sync.

It's possible that we could devise some upgrade procedure that gets from
old backend/old catalogs to new backend/new catalogs without an initdb,
but I tend to think that this is basically the problem pg_upgrade is
supposed to solve.  I'm not eager to spend time on a "pg_simple_upgrade"
procedure.
        regards, tom lane