Postgres Pro
Downloads
Home   >   mailing lists

Thread: Databas has no Object Identifier Types & Functions

Databas has no Object Identifier Types & Functions

From
Rebecca Clarke
Date:
Hi there,

I'm running postgresql 8.4 on a debian system. I have a database that has no object identifier types and functions in the pg_catalog, 
e.g.  regclass, regclassout.


What's the best way to get these in to this database? I don't want to upgrade postgresql.
There are other databases on the server that do have them.


Thanks

R Clarke


Re: Databas has no Object Identifier Types & Functions

From
Pavan Deolasee
Date:


On Tue, Sep 4, 2012 at 3:58 PM, Rebecca Clarke <r.clarke83@gmail.com> wrote:
Hi there,

I'm running postgresql 8.4 on a debian system. I have a database that has no object identifier types and functions in the pg_catalog, 
e.g.  regclass, regclassout.


Are you sure you don't have them ? I thought regclass is quite old, but I may be wrong. What does the following query returns ?

SELECT * FROM pg_type WHERE typname = 'regclass'; 

Thanks,
Pavan

Re: Databas has no Object Identifier Types & Functions

From
Rebecca Clarke
Date:
Returns 0 rows.


On Tue, Sep 4, 2012 at 11:41 AM, Pavan Deolasee <pavan.deolasee@gmail.com> wrote:


On Tue, Sep 4, 2012 at 3:58 PM, Rebecca Clarke <r.clarke83@gmail.com> wrote:
Hi there,

I'm running postgresql 8.4 on a debian system. I have a database that has no object identifier types and functions in the pg_catalog, 
e.g.  regclass, regclassout.


Are you sure you don't have them ? I thought regclass is quite old, but I may be wrong. What does the following query returns ?

SELECT * FROM pg_type WHERE typname = 'regclass'; 

Thanks,
Pavan

Re: Databas has no Object Identifier Types & Functions

From
Chris Travers
Date:


On Tue, Sep 4, 2012 at 3:28 AM, Rebecca Clarke <r.clarke83@gmail.com> wrote:
Hi there,

I'm running postgresql 8.4 on a debian system. I have a database that has no object identifier types and functions in the pg_catalog, 
e.g.  regclass, regclassout.


What's the best way to get these in to this database? I don't want to upgrade postgresql.
There are other databases on the server that do have them.


 Ouch.

First thign I would do is check and make sure they are in template1.

Then I would probably rename the db (just in case), create a new db, and use psql and pg_dump to move the old data into the freshly created one.

There may be ways to restore just these types but I would worry about what else is missing and this seems to me to be the safest course of action if/when you can afford the downtime.

If they are not in template1, create the database from template0.  You may want to recreate template1 in that case also.

Best Wishes,
Chris Travers

Re: Databas has no Object Identifier Types & Functions

From
Merlin Moncure
Date:
On Tue, Sep 4, 2012 at 6:57 AM, Rebecca Clarke <r.clarke83@gmail.com> wrote:
> Returns 0 rows.

how in the world did you get yourself in that situation?

merlin