Thread: pg_restore fails - postgres 7.3.4

Trying to dump and restore a production database with no success.

pg_dump database using the following:
  pg_dump -v -b -C -Fc -U postgres -f servdb.tar.gz servdb

pg_restore with with following:
  pg_restore -v -C -D template1 -Fc -U postgres servdb.tar.gz

restore fails with:

pg_restore: [archiver (db)] could not execute query: ERROR: data type
integer has no default operator class for access method "gist"
You must specify an operator class for the index or define a default
operator class for the data type
pg_restore: ***aborted because of error

From the documentation the following query illustrates that I have four

gist acc_methods available:

  select am.amname as acc_method, opc.opcname as ops_name
  from pg_am am, pg_opclass opc
  where opc.opcamid = am.oid
  order by acc_method, ops_name;

acc_method | ops_name
-----------------------------
gist | gist_box_ops
gist | gist_int4_ops
gist | gist_poly_ops
gist | gist_timestamp_ops

The create index statement that is failing the restore:
  CREATE INDEX idx_disc_loc ON order_items USING gist (disc, loc);

Has anyone battled this one before?  Is there a problem with
dumping/restoring with blobs?

Thanks,
Rob

"Rob Long" <rlong@micropat.com> writes:
> Trying to dump and restore a production database with no success.

> pg_restore: [archiver (db)] could not execute query: ERROR: data type
> integer has no default operator class for access method "gist"

Hm, this is probably a matter of incorrect object restoration order ...
could we see the output of "pg_restore -l" for this dump file?

            regards, tom lane

Attached.

The previously posted index failure stated:
CREATE INDEX idx_disc_loc ON order_items USING gist (disc, loc);

However in this list it is:
CREATE INDEX idx_item_loc ON content_items USING gist (disclosuer, loc);

Rob

Tom Lane wrote:

> "Rob Long" <rlong@micropat.com> writes:
> > Trying to dump and restore a production database with no success.
>
> > pg_restore: [archiver (db)] could not execute query: ERROR: data type
> > integer has no default operator class for access method "gist"
>
> Hm, this is probably a matter of incorrect object restoration order ...
> could we see the output of "pg_restore -l" for this dump file?
>
>                         regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
;
; Archive created at Wed Oct  1 15:14:39 2003
;     dbname: servdb
;     TOC Entries: 270
;     Compression: -1
;     Dump Version: 1.7-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 4 bytes
;
;
; Selected TOC Entries:
;
2; 95388961 SCHEMA map_1 postgres
3; 95523966 SCHEMA map_2 postgres
4; 95524214 SCHEMA map_3 postgres
135; 78537297 FUNCTION plpgsql_call_handler () postgres
134; 78537298 PROCEDURAL LANGUAGE plpgsql
136; 78537299 FUNCTION database_size (name) postgres
137; 78537300 FUNCTION relation_size (text) postgres
138; 78537302 FUNCTION int4key_in (cstring) postgres
139; 78537303 FUNCTION int4key_out (int4key) postgres
5; 78537301 TYPE int4key postgres
140; 78537305 FUNCTION gint4_consistent (internal, integer, smallint) postgres
141; 78537306 FUNCTION gint4_compress (internal) postgres
142; 78537307 FUNCTION btree_decompress (internal) postgres
143; 78537308 FUNCTION gint4_penalty (internal, internal, internal) postgres
144; 78537309 FUNCTION gint4_picksplit (internal, internal) postgres
145; 78537310 FUNCTION gint4_union (bytea, internal) postgres
146; 78537311 FUNCTION gint4_same (internal, internal, internal) postgres
170; 78537312 OPERATOR CLASS gist_int4_ops postgres
147; 78537314 FUNCTION tskey_in (cstring) postgres
148; 78537315 FUNCTION tskey_out (tskey) postgres
6; 78537313 TYPE tskey postgres
149; 78537317 FUNCTION gts_consistent (internal, timestamp without time zone, smallint) postgres
150; 78537318 FUNCTION gts_compress (internal) postgres
151; 78537319 FUNCTION gts_penalty (internal, internal, internal) postgres
152; 78537320 FUNCTION gts_picksplit (internal, internal) postgres
153; 78537321 FUNCTION gts_union (bytea, internal) postgres
154; 78537322 FUNCTION gts_same (internal, internal, internal) postgres
171; 78537323 OPERATOR CLASS gist_timestamp_ops postgres
155; 78537324 FUNCTION gbox_consistent (internal, box, integer) postgres
156; 78537325 FUNCTION gbox_compress (internal) postgres
157; 78537326 FUNCTION rtree_decompress (internal) postgres
158; 78537327 FUNCTION gbox_penalty (internal, internal, internal) postgres
159; 78537328 FUNCTION gbox_picksplit (internal, internal) postgres
160; 78537329 FUNCTION gbox_union (bytea, internal) postgres
161; 78537330 FUNCTION gbox_same (box, box, internal) postgres
172; 78537331 OPERATOR CLASS gist_box_ops postgres
162; 78537332 FUNCTION gpoly_consistent (internal, polygon, integer) postgres
163; 78537333 FUNCTION gpoly_compress (internal) postgres
173; 78537334 OPERATOR CLASS gist_poly_ops postgres
7; 78537335 SEQUENCE seq_mapid postgres
9; 78537337 TABLE maps postgres
10; 78537346 TABLE content postgres
164; 78537362 FUNCTION delete_map_trigger () postgres
11; 78537364 TABLE attr_types postgres
12; 78537368 TABLE label_types postgres
165; 78537381 FUNCTION new_map_trigger () postgres
166; 78537383 FUNCTION build_map_schema (integer, boolean, boolean) postgres
167; 78537385 FUNCTION setmap (integer) postgres
168; 78537386 FUNCTION after_update_content () postgres
169; 78537389 FUNCTION copymap (character varying, character varying, boolean) postgres
13; 95388963 TABLE content_items postgres
14; 95388968 TABLE attributes postgres
15; 95388971 TABLE item_text_attribs postgres
16; 95388976 TABLE item_date_attribs postgres
17; 95388978 TABLE item_dbl_attribs postgres
18; 95388980 TABLE item_int_attribs postgres
19; 95388984 VIEW item_attribs postgres
20; 95388985 TABLE documents postgres
21; 95388990 TABLE contours postgres
22; 95388995 TABLE queries postgres
23; 95389000 TABLE groups postgres
24; 95389005 TABLE timeslices postgres
25; 95389010 TABLE syn_units postgres
26; 95389015 TABLE labels postgres
27; 95389020 TABLE sem_units postgres
28; 95389025 TABLE params postgres
29; 95523968 TABLE content_items postgres
30; 95523973 TABLE attributes postgres
31; 95523976 TABLE item_text_attribs postgres
32; 95523981 TABLE item_date_attribs postgres
33; 95523983 TABLE item_dbl_attribs postgres
34; 95523985 TABLE item_int_attribs postgres
35; 95523989 VIEW item_attribs postgres
36; 95523990 TABLE documents postgres
37; 95523995 TABLE contours postgres
38; 95524000 TABLE queries postgres
39; 95524005 TABLE groups postgres
40; 95524010 TABLE timeslices postgres
41; 95524015 TABLE syn_units postgres
42; 95524020 TABLE labels postgres
43; 95524025 TABLE sem_units postgres
44; 95524030 TABLE params postgres
45; 95524216 TABLE content_items postgres
46; 95524221 TABLE attributes postgres
47; 95524224 TABLE item_text_attribs postgres
48; 95524229 TABLE item_date_attribs postgres
49; 95524231 TABLE item_dbl_attribs postgres
50; 95524233 TABLE item_int_attribs postgres
51; 95524237 VIEW item_attribs postgres
52; 95524238 TABLE documents postgres
53; 95524243 TABLE contours postgres
54; 95524248 TABLE queries postgres
55; 95524253 TABLE groups postgres
56; 95524258 TABLE timeslices postgres
57; 95524263 TABLE syn_units postgres
58; 95524268 TABLE labels postgres
59; 95524273 TABLE sem_units postgres
60; 95524278 TABLE params postgres
174; 78537337 TABLE DATA maps postgres
175; 78537346 TABLE DATA content postgres
176; 78537364 TABLE DATA attr_types postgres
177; 78537368 TABLE DATA label_types postgres
178; 95388963 TABLE DATA content_items postgres
179; 95388968 TABLE DATA attributes postgres
180; 95388971 TABLE DATA item_text_attribs postgres
181; 95388976 TABLE DATA item_date_attribs postgres
182; 95388978 TABLE DATA item_dbl_attribs postgres
183; 95388980 TABLE DATA item_int_attribs postgres
184; 95388985 TABLE DATA documents postgres
185; 95388990 TABLE DATA contours postgres
186; 95388995 TABLE DATA queries postgres
187; 95389000 TABLE DATA groups postgres
188; 95389005 TABLE DATA timeslices postgres
189; 95389010 TABLE DATA syn_units postgres
190; 95389015 TABLE DATA labels postgres
191; 95389020 TABLE DATA sem_units postgres
192; 95389025 TABLE DATA params postgres
193; 95523968 TABLE DATA content_items postgres
194; 95523973 TABLE DATA attributes postgres
195; 95523976 TABLE DATA item_text_attribs postgres
196; 95523981 TABLE DATA item_date_attribs postgres
197; 95523983 TABLE DATA item_dbl_attribs postgres
198; 95523985 TABLE DATA item_int_attribs postgres
199; 95523990 TABLE DATA documents postgres
200; 95523995 TABLE DATA contours postgres
201; 95524000 TABLE DATA queries postgres
202; 95524005 TABLE DATA groups postgres
203; 95524010 TABLE DATA timeslices postgres
204; 95524015 TABLE DATA syn_units postgres
205; 95524020 TABLE DATA labels postgres
206; 95524025 TABLE DATA sem_units postgres
207; 95524030 TABLE DATA params postgres
208; 95524216 TABLE DATA content_items postgres
209; 95524221 TABLE DATA attributes postgres
210; 95524224 TABLE DATA item_text_attribs postgres
211; 95524229 TABLE DATA item_date_attribs postgres
212; 95524231 TABLE DATA item_dbl_attribs postgres
213; 95524233 TABLE DATA item_int_attribs postgres
214; 95524238 TABLE DATA documents postgres
215; 95524243 TABLE DATA contours postgres
216; 95524248 TABLE DATA queries postgres
217; 95524253 TABLE DATA groups postgres
218; 95524258 TABLE DATA timeslices postgres
219; 95524263 TABLE DATA syn_units postgres
220; 95524268 TABLE DATA labels postgres
221; 95524273 TABLE DATA sem_units postgres
222; 95524278 TABLE DATA params postgres
223; 0 BLOBS BLOBS
61; 78537345 INDEX idx_path postgres
65; 78537357 INDEX idx_doc_vectors_loid postgres
64; 78537358 INDEX idx_assoc_matrix_loid postgres
67; 78537359 INDEX idx_terrain_grid_loid postgres
66; 78537360 INDEX idx_lighting_grid_loid postgres
68; 78537361 INDEX idx_training_cell_grid_loid postgres
72; 95523874 INDEX idx_item_discl postgres
73; 95523875 INDEX idx_item_loc postgres
75; 95523882 INDEX idx_attr_name postgres
82; 95523931 INDEX idx_cntr_coords postgres
87; 95523950 INDEX idx_label_discl postgres
88; 95523951 INDEX idx_label_loc postgres
93; 95524123 INDEX idx_item_discl postgres
94; 95524124 INDEX idx_item_loc postgres
96; 95524131 INDEX idx_attr_name postgres
103; 95524180 INDEX idx_cntr_coords postgres
108; 95524199 INDEX idx_label_discl postgres
109; 95524200 INDEX idx_label_loc postgres
114; 95526336 INDEX idx_item_discl postgres
115; 95526337 INDEX idx_item_loc postgres
117; 95526344 INDEX idx_attr_name postgres
124; 95526393 INDEX idx_cntr_coords postgres
129; 95526412 INDEX idx_label_discl postgres
130; 95526413 INDEX idx_label_loc postgres
62; 78537343 CONSTRAINT maps_pkey postgres
63; 78537351 CONSTRAINT content_pkey postgres
224; 78537353 CONSTRAINT $1 postgres
69; 78537366 CONSTRAINT attr_types_pkey postgres
70; 78537370 CONSTRAINT label_types_pkey postgres
71; 95523872 CONSTRAINT content_items_pkey postgres
74; 95523876 CONSTRAINT attributes_pkey postgres
225; 95523878 CONSTRAINT $01 postgres
76; 95523883 CONSTRAINT item_text_attribs_pkey postgres
226; 95523885 CONSTRAINT $01 postgres
227; 95523889 CONSTRAINT $02 postgres
77; 95523893 CONSTRAINT item_date_attribs_pkey postgres
228; 95523895 CONSTRAINT $01 postgres
229; 95523899 CONSTRAINT $02 postgres
78; 95523903 CONSTRAINT item_dbl_attribs_pkey postgres
230; 95523905 CONSTRAINT $01 postgres
231; 95523909 CONSTRAINT $02 postgres
79; 95523913 CONSTRAINT item_int_attribs_pkey postgres
232; 95523915 CONSTRAINT $01 postgres
233; 95523919 CONSTRAINT $02 postgres
80; 95523923 CONSTRAINT docs_pkey postgres
234; 95523925 CONSTRAINT $01 postgres
81; 95523929 CONSTRAINT contours_pkey postgres
83; 95523932 CONSTRAINT query_pkey postgres
84; 95523934 CONSTRAINT groups_pkey postgres
235; 95523936 CONSTRAINT $01 postgres
85; 95523940 CONSTRAINT timeslices_pkey postgres
86; 95523942 CONSTRAINT syn_units_pkey postgres
89; 95523944 CONSTRAINT labels_pkey postgres
236; 95523946 CONSTRAINT $01 postgres
90; 95523952 CONSTRAINT sem_units_pkey postgres
237; 95523954 CONSTRAINT $01 postgres
238; 95523958 CONSTRAINT $02 postgres
91; 95523962 CONSTRAINT params_pkey postgres
92; 95524121 CONSTRAINT content_items_pkey postgres
95; 95524125 CONSTRAINT attributes_pkey postgres
239; 95524127 CONSTRAINT $01 postgres
97; 95524132 CONSTRAINT item_text_attribs_pkey postgres
240; 95524134 CONSTRAINT $01 postgres
241; 95524138 CONSTRAINT $02 postgres
98; 95524142 CONSTRAINT item_date_attribs_pkey postgres
242; 95524144 CONSTRAINT $01 postgres
243; 95524148 CONSTRAINT $02 postgres
99; 95524152 CONSTRAINT item_dbl_attribs_pkey postgres
244; 95524154 CONSTRAINT $01 postgres
245; 95524158 CONSTRAINT $02 postgres
100; 95524162 CONSTRAINT item_int_attribs_pkey postgres
246; 95524164 CONSTRAINT $01 postgres
247; 95524168 CONSTRAINT $02 postgres
101; 95524172 CONSTRAINT docs_pkey postgres
248; 95524174 CONSTRAINT $01 postgres
102; 95524178 CONSTRAINT contours_pkey postgres
104; 95524181 CONSTRAINT query_pkey postgres
105; 95524183 CONSTRAINT groups_pkey postgres
249; 95524185 CONSTRAINT $01 postgres
106; 95524189 CONSTRAINT timeslices_pkey postgres
107; 95524191 CONSTRAINT syn_units_pkey postgres
110; 95524193 CONSTRAINT labels_pkey postgres
250; 95524195 CONSTRAINT $01 postgres
111; 95524201 CONSTRAINT sem_units_pkey postgres
251; 95524203 CONSTRAINT $01 postgres
252; 95524207 CONSTRAINT $02 postgres
112; 95524211 CONSTRAINT params_pkey postgres
113; 95526334 CONSTRAINT content_items_pkey postgres
116; 95526338 CONSTRAINT attributes_pkey postgres
253; 95526340 CONSTRAINT $01 postgres
118; 95526345 CONSTRAINT item_text_attribs_pkey postgres
254; 95526347 CONSTRAINT $01 postgres
255; 95526351 CONSTRAINT $02 postgres
119; 95526355 CONSTRAINT item_date_attribs_pkey postgres
256; 95526357 CONSTRAINT $01 postgres
257; 95526361 CONSTRAINT $02 postgres
120; 95526365 CONSTRAINT item_dbl_attribs_pkey postgres
258; 95526367 CONSTRAINT $01 postgres
259; 95526371 CONSTRAINT $02 postgres
121; 95526375 CONSTRAINT item_int_attribs_pkey postgres
260; 95526377 CONSTRAINT $01 postgres
261; 95526381 CONSTRAINT $02 postgres
122; 95526385 CONSTRAINT docs_pkey postgres
262; 95526387 CONSTRAINT $01 postgres
123; 95526391 CONSTRAINT contours_pkey postgres
125; 95526394 CONSTRAINT query_pkey postgres
126; 95526396 CONSTRAINT groups_pkey postgres
263; 95526398 CONSTRAINT $01 postgres
127; 95526402 CONSTRAINT timeslices_pkey postgres
128; 95526404 CONSTRAINT syn_units_pkey postgres
131; 95526406 CONSTRAINT labels_pkey postgres
264; 95526408 CONSTRAINT $01 postgres
132; 95526414 CONSTRAINT sem_units_pkey postgres
265; 95526416 CONSTRAINT $01 postgres
266; 95526420 CONSTRAINT $02 postgres
133; 95526424 CONSTRAINT params_pkey postgres
267; 78537363 TRIGGER trg_after_map_del postgres
268; 78537382 TRIGGER trg_after_map_insert postgres
270; 78537387 TRIGGER trg_after_content_update postgres
269; 78537388 TRIGGER trg_after_content_delete postgres
8; 78537335 SEQUENCE SET seq_mapid postgres

"Rob Long" <rlong@micropat.com> writes:
>>> Trying to dump and restore a production database with no success.
>>>
>>> pg_restore: [archiver (db)] could not execute query: ERROR: data type
>>> integer has no default operator class for access method "gist"
>>
>> Hm, this is probably a matter of incorrect object restoration order ...
>> could we see the output of "pg_restore -l" for this dump file?

After playing around with this I realize that it's a bug associated with
schemas --- if you have an operator class that's not in pg_catalog, the
opclass name has to be explicitly qualified in pg_dump's output, or it
won't restore correctly.  I'm surprised no one reported this before,
since it affects all the contrib operator classes.

Attached is a patch against 7.3.4, in case it helps.

            regards, tom lane

Index: src/backend/utils/adt/ruleutils.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/utils/adt/ruleutils.c,v
retrieving revision 1.124.2.1
diff -c -r1.124.2.1 ruleutils.c
*** src/backend/utils/adt/ruleutils.c    8 Jan 2003 22:54:36 -0000    1.124.2.1
--- src/backend/utils/adt/ruleutils.c    2 Oct 2003 22:21:03 -0000
***************
*** 2951,2956 ****
--- 2951,2957 ----
      Form_pg_opclass opcrec;
      char       *opcname;
      char       *nspname;
+     bool        isvisible;

      /* Domains use their base type's default opclass */
      if (OidIsValid(actual_datatype))
***************
*** 2962,2972 ****
      if (!HeapTupleIsValid(ht_opc))
          elog(ERROR, "cache lookup failed for opclass %u", opclass);
      opcrec = (Form_pg_opclass) GETSTRUCT(ht_opc);
!     if (actual_datatype != opcrec->opcintype || !opcrec->opcdefault)
      {
          /* Okay, we need the opclass name.    Do we need to qualify it? */
          opcname = NameStr(opcrec->opcname);
!         if (OpclassIsVisible(opclass))
              appendStringInfo(buf, " %s", quote_identifier(opcname));
          else
          {
--- 2963,2978 ----
      if (!HeapTupleIsValid(ht_opc))
          elog(ERROR, "cache lookup failed for opclass %u", opclass);
      opcrec = (Form_pg_opclass) GETSTRUCT(ht_opc);
!
!     /* Must force use of opclass name if not in search path */
!     isvisible = OpclassIsVisible(opclass);
!
!     if (actual_datatype != opcrec->opcintype || !opcrec->opcdefault ||
!         !isvisible)
      {
          /* Okay, we need the opclass name.    Do we need to qualify it? */
          opcname = NameStr(opcrec->opcname);
!         if (isvisible)
              appendStringInfo(buf, " %s", quote_identifier(opcname));
          else
          {

Tom,

Will this be fixed in 7.4?

Thanks for the support.

Rob

----- Original Message -----
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Thursday, October 2, 2003 6:28 pm
Subject: Re: [ADMIN] pg_restore fails - postgres 7.3.4

> "Rob Long" <rlong@micropat.com> writes:
> >>> Trying to dump and restore a production database with no success.
> >>>
> >>> pg_restore: [archiver (db)] could not execute query: ERROR:
> data type
> >>> integer has no default operator class for access method "gist"
> >>
> >> Hm, this is probably a matter of incorrect object restoration
> order ...
> >> could we see the output of "pg_restore -l" for this dump file?
>
> After playing around with this I realize that it's a bug
> associated with
> schemas --- if you have an operator class that's not in
> pg_catalog, the
> opclass name has to be explicitly qualified in pg_dump's output,
> or it
> won't restore correctly.  I'm surprised no one reported this before,
> since it affects all the contrib operator classes.
>
> Attached is a patch against 7.3.4, in case it helps.
>
>            regards, tom lane
>
>
Index: src/backend/utils/adt/ruleutils.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/utils/adt/ruleutils.c,v
retrieving revision 1.124.2.1
diff -c -r1.124.2.1 ruleutils.c
*** src/backend/utils/adt/ruleutils.c    8 Jan 2003 22:54:36 -0000    1.124.2.1
--- src/backend/utils/adt/ruleutils.c    2 Oct 2003 22:21:03 -0000
***************
*** 2951,2956 ****
--- 2951,2957 ----
      Form_pg_opclass opcrec;
      char       *opcname;
      char       *nspname;
+     bool        isvisible;

      /* Domains use their base type's default opclass */
      if (OidIsValid(actual_datatype))
***************
*** 2962,2972 ****
      if (!HeapTupleIsValid(ht_opc))
          elog(ERROR, "cache lookup failed for opclass %u", opclass);
      opcrec = (Form_pg_opclass) GETSTRUCT(ht_opc);
!     if (actual_datatype != opcrec->opcintype || !opcrec->opcdefault)
      {
          /* Okay, we need the opclass name.    Do we need to qualify it? */
          opcname = NameStr(opcrec->opcname);
!         if (OpclassIsVisible(opclass))
              appendStringInfo(buf, " %s", quote_identifier(opcname));
          else
          {
--- 2963,2978 ----
      if (!HeapTupleIsValid(ht_opc))
          elog(ERROR, "cache lookup failed for opclass %u", opclass);
      opcrec = (Form_pg_opclass) GETSTRUCT(ht_opc);
!
!     /* Must force use of opclass name if not in search path */
!     isvisible = OpclassIsVisible(opclass);
!
!     if (actual_datatype != opcrec->opcintype || !opcrec->opcdefault ||
!         !isvisible)
      {
          /* Okay, we need the opclass name.    Do we need to qualify it? */
          opcname = NameStr(opcrec->opcname);
!         if (isvisible)
              appendStringInfo(buf, " %s", quote_identifier(opcname));
          else
          {

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

"Rob Long" <rlong@micropat.com> writes:
> Will this be fixed in 7.4?

Yes, and also in 7.3.5 if there is such a release.

            regards, tom lane