BUG #3048: pg_dump dumps intarray metadata incorrectly

От:

"Dmitry Koterov" <d@koterov.ru>

Дата:

22 февраля 2007 г. в 18:50:36

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. 
3. pg_dump -i -h YourHost -U YourLogin ti > s.sql
4. drop database ti; create database ti;
5. 

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.

Re: BUG #3048: pg_dump dumps intarray metadata incorrectly

От:

Bruce Momjian <bruce@momjian.us>

Дата:

26 марта 2007 г. в 22:54:16

Tom, do you want this fixed for 8.2.X?

---------------------------------------------------------------------------

Tom Lane wrote:
> "Dmitry Koterov"  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            http://momjian.us
  EnterpriseDB                               http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Re: BUG #3048: pg_dump dumps intarray metadata incorrectly

От:

Bruce Momjian <bruce@momjian.us>

Дата:

2 апреля 2007 г. в 19:24:20

Tom Lane wrote:
> Bruce Momjian  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            http://momjian.us
  EnterpriseDB                               http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Re: BUG #3048: pg_dump dumps intarray metadata incorrectly

От:

Alvaro Herrera <alvherre@commandprompt.com>

Дата:

9 апреля 2007 г. в 16:23:08

Oleg Bartunov wrote:
> On Mon, 2 Apr 2007, Bruce Momjian wrote:
> 
> >Tom Lane wrote:
> >>Bruce Momjian  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.

Re: BUG #3048: pg_dump dumps intarray metadata incorrectly

От:

Bruce Momjian <bruce@momjian.us>

Дата:

26 апреля 2007 г. в 19:46:02

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  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            http://momjian.us
  EnterpriseDB                               http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Re: BUG #3048: pg_dump dumps intarray metadata incorrectly

От:

Bruce Momjian <bruce@momjian.us>

Дата:

15 мая 2007 г. в 21:53:34

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  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            http://momjian.us
  EnterpriseDB                               http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Re: BUG #3048: pg_dump dumps intarray metadata incorrectly

От:

Tom Lane <tgl@sss.pgh.pa.us>

Дата:

23 февраля 2007 г. в 12:17:06

"Dmitry Koterov"  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

Re: BUG #3048: pg_dump dumps intarray metadata incorrectly

От:

Teodor Sigaev <teodor@sigaev.ru>

Дата:

10 апреля 2007 г. в 11:09:46

> 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/

Re: BUG #3048: pg_dump dumps intarray metadata incorrectly

От:

Tom Lane <tgl@sss.pgh.pa.us>

Дата:

26 марта 2007 г. в 23:11:55

Bruce Momjian  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

Re: BUG #3048: pg_dump dumps intarray metadata incorrectly

От:

"Dmitry Koterov" <dmitry@koterov.ru>

Дата:

25 февраля 2007 г. в 13:19:10

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  wrote:
>
> "Dmitry Koterov"  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
>

Re: BUG #3048: pg_dump dumps intarray metadata incorrectly

От:

Oleg Bartunov <oleg@sai.msu.su>

Дата:

9 апреля 2007 г. в 15:51:40

On Mon, 2 Apr 2007, Bruce Momjian wrote:

> Tom Lane wrote:
>> Bruce Momjian  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