Thread: ERROR: Unable to locate type oid 0 in catalog...

ERROR: Unable to locate type oid 0 in catalog...

From

frbn

Date:

18 November 2004, 12:32:52

hi all,
we currently experience an original problem
on a production server with a postgresql 7.1.3
( a bit old, I know :\ )

We just want to know if somebody experienced this problem:

the error message :

   "ERROR: Unable to locate type oid 0 in catalog"

I know that such a type can't exists as the types are
generated at the install with the first oid "16".

We found that drop the table en re-creating it with the same data
fixed the problem...

We think about a hardware problem (RAM default or disk maybe)
occurred and killed parts of our data.

These queries worked last week, we got many other problems too
during the last week on
    "operators = from typeX to typeY"
disapeared
(fixed by creating these operators in the emergency)

Next to this, we can now see pg_temp.<num>.0 tables appearing in the
\d output !?

this looks like temporary tables "getting to public" ?

    oid       | relname
    1048844570 | pg_temp.20806.1
    1048844234 | pg_temp.20806.0


We stongly think about upgrading, but these problems
drops pg_dump to a fatal error... (Murphy's my best friend :] )
so do backups for a few days :(

I don't ask for a quick fix,
but just something like: "drop  all that and upgrade"
or "do that at last..."


best regards,

   franck

Re: ERROR: Unable to locate type oid 0 in catalog...

From

Tom Lane

Date:

18 November 2004, 18:13:40

frbn <frbn@efbs-seafrigo.fr> writes:
> we currently experience an original problem
> on a production server with a postgresql 7.1.3
> ( a bit old, I know :\ )

More than a bit.  I wouldn't worry about trying to fix the installation
--- concentrate on getting a dump so you can upgrade.

> We stongly think about upgrading, but these problems
> drops pg_dump to a fatal error... (Murphy's my best friend :] )

What error exactly?  I'd suggest trying to use a newer version of
pg_dump (ie, the one from the PG version you want to update to)
to see if it avoids the problem.  If not, turn on query logging
so you can see the queries issued by pg_dump, and let us know the
exact failure message and the last query issued before it happens.

            regards, tom lane

Re: ERROR: Unable to locate type oid 0 in catalog...

From

frbn

Date:

18 November 2004, 18:31:59

hi,
first: thank you very much for your support !

I now get the last postgresql stable version...

and here is the level 2 debug output :
(I'm sorry for this, it's huge)
___________________________
8936752 /usr/bin/postmaster: ServerLoop:        handling reading 5
8936753 /usr/bin/postmaster: ServerLoop:        handling reading 5
8936754 /usr/bin/postmaster: ServerLoop:        handling reading 5
8936755 /usr/bin/postmaster: ServerLoop:        handling reading 5
8936756 /usr/bin/postmaster: ServerLoop:        handling writing 5
8936757 /usr/bin/postmaster: BackendStartup: pid 31602 user postgres db
statistiques socket 5
8936758 /usr/bin/postmaster child[31602]: starting with (postgres -d2
-v131072 -p statistiques )
8936759 FindExec: found "/usr/bin/postgres" using argv[0]
8936760 2004-11-18 16:17:34 [31602]  DEBUG:  connection: host=[local]
user=postgres database=statistiques
8936761 2004-11-18 16:17:34 [31602]  DEBUG:  InitPostgres
8936762 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936763 2004-11-18 16:17:34 [31602]  DEBUG:  query: select
getdatabaseencoding()
8936764 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936765 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936766 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936767 2004-11-18 16:17:34 [31602]  DEBUG:  query: SET datestyle =
'european'
8936768 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessUtility: SET
datestyle = 'european'
8936769 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936770 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936771 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT version()
8936772 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936773 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936774 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936775 2004-11-18 16:17:34 [31602]  DEBUG:  query: begin
8936776 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessUtility: begin
8936777 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936778 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936779 2004-11-18 16:17:34 [31602]  DEBUG:  query: set transaction
isolation level serializable
8936780 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessUtility: set
transaction isolation level serializable
8936781 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936782 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936783 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT datlastsysoid
from pg_database where datname = 'statistiques'
8936784 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936785 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936786 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936787 2004-11-18 16:17:34 [31602]  DEBUG:  query: select (select
usename from pg_user where datdba = usesysid) as dba from pg_database
where datname = 'statistiques'
8936788 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936789 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936790 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936791 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT pg_type.oid,
typowner, typname, typlen, typprtlen, typinput, typoutput, typreceive,
typsend, typelem, typdelim, typdefault, typrelid, typalign, typstorage,
typbyval, typisdefined, (select usename from pg_user where typowner =
usesysid) as usename, format_type(pg_type.oid, NULL) as typedefn from
pg_type
8936792 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936793 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936794 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936795 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT pg_proc.oid,
proname, prolang, pronargs, prorettype, proretset, proargtypes, prosrc,
probin, (select usename from pg_user where proowner = usesysid) as
usename, proiscachable, proisstrict from pg_proc where pg_proc.oid >
'18539'::oid
8936796 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936797 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936798 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936799 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT
pg_aggregate.oid, aggname, aggtransfn, aggfinalfn, aggtranstype,
aggbasetype, agginitval, 't'::boolean as convertok, (select usename from
pg_user where aggowner = usesysid) as usename from pg_aggregate
8936800 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936801 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936802 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936803 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT
pg_operator.oid, oprname, oprkind, oprcode, oprleft, oprright, oprcom,
oprnegate, oprrest, oprjoin, oprcanhash, oprlsortop, oprrsortop, (select
usename from pg_user where oprowner = usesysid) as usename from pg_operator
8936804 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936805 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936806 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936807 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT pg_class.oid,
relname, relacl, relkind, (select usename from pg_user where relowner =
usesysid) as usename, relchecks, reltriggers, relhasindex from pg_class
where relname !~ '^pg_' and relkind in ('r', 'S', 'v') order by oid
8936808 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936809 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936810 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936811 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT Oid FROM
pg_index i WHERE i.indisprimary AND i.indrelid = 126626536
8936812 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936813 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936814 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936815 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT Oid FROM
pg_index i WHERE i.indisprimary AND i.indrelid = 126630187
8936816 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936817 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936818 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936819 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT Oid FROM
pg_index i WHERE i.indisprimary AND i.indrelid = 126640073
8936820 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936821 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936822 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936823 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT Oid FROM
pg_index i WHERE i.indisprimary AND i.indrelid = 225114846
8936824 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936825 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936826 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936827 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT tgname,
tgfoid, tgtype, tgnargs, tgargs, tgisconstraint, tgconstrname,
tgdeferrable, tgconstrrelid, tginitdeferred, oid, (select relname from
pg_class where oid = tgconstrrelid)        as tgconstrrelname from
pg_trigger where tgrelid = '272231954'::oid
8936828 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936829 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936830 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936831 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT Oid FROM
pg_index i WHERE i.indisprimary AND i.indrelid = 314698456
8936832 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936833 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936834 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936835 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT Oid FROM
pg_index i WHERE i.indisprimary AND i.indrelid = 696158295
8936836 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936837 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936838 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936839 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT Oid FROM
pg_index i WHERE i.indisprimary AND i.indrelid = 873407485
8936840 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936841 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936842 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936843 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT c.relname
FROM pg_index i LEFT OUTER JOIN pg_class c ON c.oid = i.indexrelid WHERE
i.indrelid = 873407485AND   i.indisprimary
8936844 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936845 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936846 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936847 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT tgname,
tgfoid, tgtype, tgnargs, tgargs, tgisconstraint, tgconstrname,
tgdeferrable, tgconstrrelid, tginitdeferred, oid, (select relname from
pg_class where oid = tgconstrrelid)        as tgconstrrelname from
pg_trigger where tgrelid = '873407485'::oid
8936848 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936849 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936850 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936851 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT Oid FROM
pg_index i WHERE i.indisprimary AND i.indrelid = 873407585
8936852 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936853 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936854 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936855 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT c.relname
FROM pg_index i LEFT OUTER JOIN pg_class c ON c.oid = i.indexrelid WHERE
i.indrelid = 873407585AND   i.indisprimary
8936856 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936857 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936858 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936859 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT Oid FROM
pg_index i WHERE i.indisprimary AND i.indrelid = 873407619
8936860 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936861 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936862 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936863 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT c.relname
FROM pg_index i LEFT OUTER JOIN pg_class c ON c.oid = i.indexrelid WHERE
i.indrelid = 873407619AND   i.indisprimary
8936864 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936865 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936866 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936867 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT Oid FROM
pg_index i WHERE i.indisprimary AND i.indrelid = 873407653
8936868 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936869 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936870 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936871 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT c.relname
FROM pg_index i LEFT OUTER JOIN pg_class c ON c.oid = i.indexrelid WHERE
i.indrelid = 873407653AND   i.indisprimary
8936872 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936873 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936874 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936875 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT Oid FROM
pg_index i WHERE i.indisprimary AND i.indrelid = 873407687
8936876 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936877 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936878 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936879 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT c.relname
FROM pg_index i LEFT OUTER JOIN pg_class c ON c.oid = i.indexrelid WHERE
i.indrelid = 873407687AND   i.indisprimary
8936880 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936881 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936882 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936883 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT Oid FROM
pg_index i WHERE i.indisprimary AND i.indrelid = 873407744
8936884 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936885 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936886 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936887 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT Oid FROM
pg_index i WHERE i.indisprimary AND i.indrelid = 873407778
8936888 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936889 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936890 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936891 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT tgname,
tgfoid, tgtype, tgnargs, tgargs, tgisconstraint, tgconstrname,
tgdeferrable, tgconstrrelid, tginitdeferred, oid, (select relname from
pg_class where oid = tgconstrrelid)        as tgconstrrelname from
pg_trigger where tgrelid = '889715365'::oid
8936892 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936893 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936894 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936895 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT Oid FROM
pg_index i WHERE i.indisprimary AND i.indrelid = 925014305
8936896 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936897 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936898 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936899 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT Oid FROM
pg_index i WHERE i.indisprimary AND i.indrelid = 931983025
8936900 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936901 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936902 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936903 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT c.relname
FROM pg_index i LEFT OUTER JOIN pg_class c ON c.oid = i.indexrelid WHERE
i.indrelid = 931983025AND   i.indisprimary
8936904 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936905 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936906 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936907 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT tgname,
tgfoid, tgtype, tgnargs, tgargs, tgisconstraint, tgconstrname,
tgdeferrable, tgconstrrelid, tginitdeferred, oid, (select relname from
pg_class where oid = tgconstrrelid)        as tgconstrrelname from
pg_trigger where tgrelid = '931983025'::oid
8936908 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936909 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936910 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936911 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT tgname,
tgfoid, tgtype, tgnargs, tgargs, tgisconstraint, tgconstrname,
tgdeferrable, tgconstrrelid, tginitdeferred, oid, (select relname from
pg_class where oid = tgconstrrelid)        as tgconstrrelname from
pg_trigger where tgrelid = '939024007'::oid
8936912 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936913 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936915 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT definition as
viewdef, (select oid from pg_rewrite where  rulename=('_RET' ||
viewname)::name) as view_oid from pg_views where viewname = 'sternon_es';
8936916 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936917 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT * FROM
pg_rewrite WHERE rulename = $1
8936918 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936919 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936920 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT Oid FROM
pg_index i WHERE i.indisprimary AND i.indrelid = 1124348089
8936921 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936922 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936923 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936924 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT Oid FROM
pg_index i WHERE i.indisprimary AND i.indrelid = 1153481046
8936925 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936926 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936927 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936928 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT c.relname
FROM pg_index i LEFT OUTER JOIN pg_class c ON c.oid = i.indexrelid WHERE
i.indrelid = 1153481046AND   i.indisprimary
8936929 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936930 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936931 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936932 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT tgname,
tgfoid, tgtype, tgnargs, tgargs, tgisconstraint, tgconstrname,
tgdeferrable, tgconstrrelid, tginitdeferred, oid, (select relname from
pg_class where oid = tgconstrrelid)        as tgconstrrelname from
pg_trigger where tgrelid = '1153481046'::oid
8936933 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936934 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936935 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936936 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT tgname,
tgfoid, tgtype, tgnargs, tgargs, tgisconstraint, tgconstrname,
tgdeferrable, tgconstrrelid, tginitdeferred, oid, (select relname from
pg_class where oid = tgconstrrelid)        as tgconstrrelname from
pg_trigger where tgrelid = '1153481084'::oid
8936937 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936938 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936939 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936940 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT Oid FROM
pg_index i WHERE i.indisprimary AND i.indrelid = 1206644764
8936941 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936942 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936943 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936944 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT Oid FROM
pg_index i WHERE i.indisprimary AND i.indrelid = 1424610154
8936945 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936946 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936947 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936948 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT c.relname
FROM pg_index i LEFT OUTER JOIN pg_class c ON c.oid = i.indexrelid WHERE
i.indrelid = 1424610154AND   i.indisprimary
8936949 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936950 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936951 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936952 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT Oid FROM
pg_index i WHERE i.indisprimary AND i.indrelid = 1517634425
8936953 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936954 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936955 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936956 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT Oid FROM
pg_index i WHERE i.indisprimary AND i.indrelid = 1927721821
8936957 2004-11-18 16:17:34 [31602]  DEBUG:  ProcessQuery
8936958 2004-11-18 16:17:34 [31602]  DEBUG:  CommitTransactionCommand
8936959 2004-11-18 16:17:34 [31602]  DEBUG:  StartTransactionCommand
8936960 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT Oid FROM
pg_index i WHERE i.indisprimary AND i.indrelid = 2178456572
8936961 2004-11-18 16:17:34 [31602]  ERROR:  dtoi4: integer out of range
8936962 2004-11-18 16:17:34 [31602]  DEBUG:  AbortCurrentTransaction
8936963 2004-11-18 16:17:34 [31602]  DEBUG:  proc_exit(0)
8936964 2004-11-18 16:17:34 [31602]  DEBUG:  shmem_exit(0)
8936965 2004-11-18 16:17:34 [31602]  DEBUG:  exit(0)
8936966 /usr/bin/postmaster: reaping dead processes...
8936967 /usr/bin/postmaster: CleanupProc: pid 31602 exited with status 0
___________________________

I see what fails:

    SELECT Oid FROM pg_index i WHERE i.indisprimary AND i.indrelid =
2178456572

I tried this query with quotes and it works!
( SELECT Oid FROM pg_index i WHERE i.indisprimary AND i.indrelid =
'2178456572')

It looks the same as all the problems we have with types
when doing <int4> = <float8> for example...

I can create an operator for such comparaison (as I did yesterday)
but for "dtoi4" I'm a bit... lost.


best regards
    franck




Tom Lane wrote:

>frbn <frbn@efbs-seafrigo.fr> writes:
>
>
>>we currently experience an original problem
>>on a production server with a postgresql 7.1.3
>>( a bit old, I know :\ )
>>
>>
>
>More than a bit.  I wouldn't worry about trying to fix the installation
>--- concentrate on getting a dump so you can upgrade.
>
>
>
>>We stongly think about upgrading, but these problems
>>drops pg_dump to a fatal error... (Murphy's my best friend :] )
>>
>>
>
>What error exactly?  I'd suggest trying to use a newer version of
>pg_dump (ie, the one from the PG version you want to update to)
>to see if it avoids the problem.  If not, turn on query logging
>so you can see the queries issued by pg_dump, and let us know the
>exact failure message and the last query issued before it happens.
>
>            regards, tom lane
>
>
>

Re: ERROR: Unable to locate type oid 0 in catalog...

From

Tom Lane

Date:

18 November 2004, 18:51:48

frbn <frbn@efbs-seafrigo.fr> writes:
> 8936960 2004-11-18 16:17:34 [31602]  DEBUG:  query: SELECT Oid FROM
> pg_index i WHERE i.indisprimary AND i.indrelid = 2178456572
> 8936961 2004-11-18 16:17:34 [31602]  ERROR:  dtoi4: integer out of range

This is a 7.1-vintage pg_dump bug --- it's not putting quotes around the
OID value.  You really want to use pg_dump from a later release to dump
the data out of the old server.

            regards, tom lane