Обсуждение: BUG #3048: pg_dump dumps intarray metadata incorrectly
The following bug has been logged online: Bug reference: 3048 Logged by: Dmitry Koterov Email address: d@koterov.ru PostgreSQL version: 8.2.0 Operating system: Linux Description: pg_dump dumps intarray metadata incorrectly Details: Steps to reproduce: 1. create database ti; 2. <run SQL initialization code for intarray> 3. pg_dump -i -h YourHost -U YourLogin ti > s.sql 4. drop database ti; create database ti; 5. <run SQL produced by pg_dump (s.sql)> You will get a message: ERROR: could not make operator class "gin__int_ops" be default for type pg_catalog.int4[] DETAIL: Operator class "_int4_ops" already is the default. Seems the problem is in the operator CREATE OPERATOR CLASS gin__int_ops DEFAULT FOR TYPE integer[] USING gin AS STORAGE integer , OPERATOR 3 &&(integer[],integer[]) , OPERATOR 6 =(anyarray,anyarray) RECHECK , OPERATOR 7 @>(integer[],integer[]) , OPERATOR 8 <@(integer[],integer[]) RECHECK , OPERATOR 13 @(integer[],integer[]) , OPERATOR 14 ~(integer[],integer[]) RECHECK , OPERATOR 20 @@(integer[],query_int) , FUNCTION 1 btint4cmp(integer,integer) , FUNCTION 2 ginarrayextract(anyarray,internal) , FUNCTION 3 ginint4_queryextract(internal,internal,smallint) , FUNCTION 4 ginint4_consistent(internal,smallint,internal); produced by pg_dump. This is likely because of the operator --mark built-in gin's _int4_ops as non default update pg_opclass set opcdefault = 'f' where pg_opclass.opcamid = (select pg_am.oid from pg_am where amname='gin') and opcname = '_int4_ops'; inside intarray initialization SQL code: pg_dump knows nothing about them in the stage of scheme creation. So, now it is impossible to dump+restore a database containing intarray metadata.
"Dmitry Koterov" <d@koterov.ru> writes: > [ pg_restore fails with ] > ERROR: could not make operator class "gin__int_ops" be default for type > pg_catalog.int4[] > DETAIL: Operator class "_int4_ops" already is the default. Yeah. I'd say that intarray's attempt to override the default status of the built-in gin opclass is simply a bad idea and should be removed. It's not even documented that it does that (in fact I see no mention of GIN at all in README.intarray :-(, so we have a documentation lack here too). Comments? regards, tom lane
Maybe possibly remove DEFAULT definition from the intarray initialization SQL and eliminate in the documentation: "if you want to use GIN with _int4, you have to specify the operator class explicitly and manually"? This at least does not break the standard pg_dump behaviour. We checked, if we remove DEFAULT keyword, a dump is restored correctly. On 2/23/07, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > "Dmitry Koterov" <d@koterov.ru> writes: > > [ pg_restore fails with ] > > ERROR: could not make operator class "gin__int_ops" be default for type > > pg_catalog.int4[] > > DETAIL: Operator class "_int4_ops" already is the default. > > Yeah. I'd say that intarray's attempt to override the default status of > the built-in gin opclass is simply a bad idea and should be removed. > It's not even documented that it does that (in fact I see no mention of > GIN at all in README.intarray :-(, so we have a documentation lack > here too). > > Comments? > > regards, tom lane >
Tom, do you want this fixed for 8.2.X? --------------------------------------------------------------------------- Tom Lane wrote: > "Dmitry Koterov" <d@koterov.ru> writes: > > [ pg_restore fails with ] > > ERROR: could not make operator class "gin__int_ops" be default for type > > pg_catalog.int4[] > > DETAIL: Operator class "_int4_ops" already is the default. > > Yeah. I'd say that intarray's attempt to override the default status of > the built-in gin opclass is simply a bad idea and should be removed. > It's not even documented that it does that (in fact I see no mention of > GIN at all in README.intarray :-(, so we have a documentation lack > here too). > > Comments? > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian <bruce@momjian.us> writes: > Tom, do you want this fixed for 8.2.X? > Tom Lane wrote: >> Yeah. I'd say that intarray's attempt to override the default status of >> the built-in gin opclass is simply a bad idea and should be removed. I don't recall having seen a response from Oleg or Teodor, and would like their input before making a final decision --- but at the moment I think we should take that out. regards, tom lane
Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > Tom, do you want this fixed for 8.2.X? > > > Tom Lane wrote: > >> Yeah. I'd say that intarray's attempt to override the default status of > >> the built-in gin opclass is simply a bad idea and should be removed. > > I don't recall having seen a response from Oleg or Teodor, and would > like their input before making a final decision --- but at the moment > I think we should take that out. Oleg or Teodor, I need a comment on this. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
On Mon, 2 Apr 2007, Bruce Momjian wrote: > Tom Lane wrote: >> Bruce Momjian <bruce@momjian.us> writes: >>> Tom, do you want this fixed for 8.2.X? >> >>> Tom Lane wrote: >>>> Yeah. I'd say that intarray's attempt to override the default status of >>>> the built-in gin opclass is simply a bad idea and should be removed. >> >> I don't recall having seen a response from Oleg or Teodor, and would >> like their input before making a final decision --- but at the moment >> I think we should take that out. > > Oleg or Teodor, I need a comment on this. We agree with Tom in this case and we'll remove update of system catalog. But, I want to rise the problem again - pg_dump doesn't track changes of system catalog. The problem could be more pronounced in case of built-in FTS, if somebody with superuser rights changes fts configurations in system catalog. Regards, Oleg _____________________________________________________________ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83
Oleg Bartunov wrote: > On Mon, 2 Apr 2007, Bruce Momjian wrote: > > >Tom Lane wrote: > >>Bruce Momjian <bruce@momjian.us> writes: > >>>Tom, do you want this fixed for 8.2.X? > >> > >>>Tom Lane wrote: > >>>>Yeah. I'd say that intarray's attempt to override the default status of > >>>>the built-in gin opclass is simply a bad idea and should be removed. > >> > >>I don't recall having seen a response from Oleg or Teodor, and would > >>like their input before making a final decision --- but at the moment > >>I think we should take that out. > > > >Oleg or Teodor, I need a comment on this. > > We agree with Tom in this case and we'll remove update of > system catalog. But, I want to rise the problem again - pg_dump doesn't > track changes of system catalog. The problem could be more pronounced in > case of built-in FTS, if somebody with superuser rights changes > fts configurations in system catalog. But it should dump them as "ALTER whatever" commands, right? -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
> But it should dump them as "ALTER whatever" commands, right? > No :(, see http://archives.postgresql.org/pgsql-hackers/2007-03/msg01112.php about ALTER OPERATOR CLASS -- Teodor Sigaev E-mail: teodor@sigaev.ru WWW: http://www.sigaev.ru/
Oleg, I still haven't seen this patch applied to CVS. --------------------------------------------------------------------------- Oleg Bartunov wrote: > On Mon, 2 Apr 2007, Bruce Momjian wrote: > > > Tom Lane wrote: > >> Bruce Momjian <bruce@momjian.us> writes: > >>> Tom, do you want this fixed for 8.2.X? > >> > >>> Tom Lane wrote: > >>>> Yeah. I'd say that intarray's attempt to override the default status of > >>>> the built-in gin opclass is simply a bad idea and should be removed. > >> > >> I don't recall having seen a response from Oleg or Teodor, and would > >> like their input before making a final decision --- but at the moment > >> I think we should take that out. > > > > Oleg or Teodor, I need a comment on this. > > We agree with Tom in this case and we'll remove update of > system catalog. But, I want to rise the problem again - pg_dump doesn't > track changes of system catalog. The problem could be more pronounced in > case of built-in FTS, if somebody with superuser rights changes > fts configurations in system catalog. > > > > Regards, > Oleg > _____________________________________________________________ > Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), > Sternberg Astronomical Institute, Moscow University, Russia > Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ > phone: +007(495)939-16-83, +007(495)939-23-83 -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Uh, I believe this has not been done. Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --------------------------------------------------------------------------- Oleg Bartunov wrote: > On Mon, 2 Apr 2007, Bruce Momjian wrote: > > > Tom Lane wrote: > >> Bruce Momjian <bruce@momjian.us> writes: > >>> Tom, do you want this fixed for 8.2.X? > >> > >>> Tom Lane wrote: > >>>> Yeah. I'd say that intarray's attempt to override the default status of > >>>> the built-in gin opclass is simply a bad idea and should be removed. > >> > >> I don't recall having seen a response from Oleg or Teodor, and would > >> like their input before making a final decision --- but at the moment > >> I think we should take that out. > > > > Oleg or Teodor, I need a comment on this. > > We agree with Tom in this case and we'll remove update of > system catalog. But, I want to rise the problem again - pg_dump doesn't > track changes of system catalog. The problem could be more pronounced in > case of built-in FTS, if somebody with superuser rights changes > fts configurations in system catalog. > > > > Regards, > Oleg > _____________________________________________________________ > Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), > Sternberg Astronomical Institute, Moscow University, Russia > Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ > phone: +007(495)939-16-83, +007(495)939-23-83 -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +