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.
Show quoted text
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. +