Thread: 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. +