Обсуждение: Error "cannot compare arrays of different element types" when trying to ANALYZE

Hi

Since sometime yesterday, my postgresql (7.4.5) reports
"ERROR:  cannot compare arrays of different element types",
when I analyze a specific table in my database.

Here is the tables definition:
       Column       |          Type          |                     Modifiers
-------------------+------------------------+----------------------------------------------------
  self              | datagraph."GOLink"     | not null default
nextval('datagraph.s_self'::text)
  Bezeichnung       | datagraph."Char"       |
  virtual           | datagraph."Boolean"    | default true
  Mitarbeiter       | datagraph."GOLinkList" |
  Abteilungen       | datagraph."GOLinkList" |
  Memo              | datagraph."Text"       |
  Leiter            | datagraph."GOLink"     |
  PostfachLand      | datagraph."GOLink"     |
  PostfachOrt       | datagraph."Char"       |
  PostfachPLZ       | datagraph."Char"       |
  PostfachPostfach  | datagraph."Char"       |
  PLZ               | datagraph."Char"       |
  Ort               | datagraph."Char"       |
  Land              | datagraph."GOLink"     |
  Strasse           | datagraph."Char"       |
  Telefon           | datagraph."GOLinkList" |
  Telefax           | datagraph."GOLinkList" |
  eMail             | datagraph."GOLinkList" |
  BS Nummer         | datagraph."Char"       |
  Aktiv             | datagraph."Boolean"    |
  Eroeffnung        | datagraph."Date"       |
  Schliessung       | datagraph."Date"       |
  Eintritt          | datagraph."Date"       |
  Austritt          | datagraph."Date"       |
  TUI Agenturnummer | datagraph."Char"       |
  Bankverbindungen  | datagraph."GOLinkList" |
  Marke             | datagraph."GOLink"     |
  Buchhaltungsinfo  | datagraph."GOLink"     |
  Name              | datagraph."Char"       |
  Oeffnungszeiten   | datagraph."Text"       |
  Zusatz            | datagraph."Char"       |
  HR Firma          | datagraph."GOLink"     |
  www               | datagraph."GOLinkList" |
  Vertraege         | datagraph."GOLinkList" |
  Standortinfo      | datagraph."GOLink"     |
  Marketinginfo     | datagraph."GOLink"     |
  OnChange          | datagraph."GOLinkList" |
  Historie          | datagraph."GOLinkList" |

The datatypes are all domains with the basetype being what the name
suggest (e.g. datagraph."Date" is a domain with basetype date).
GOLink has the basetype int8, which GOLinkList has the basetype int8[].

Google just returns one(!) hit when searching for the error-message,
which points to the file generating the error
"/cvsroot/pgsql/src/backend/utils/adt/arrayfuncs.c".

greetings, Florian Pflug

"Florian G. Pflug" <fgp@phlo.org> writes:
> Since sometime yesterday, my postgresql (7.4.5) reports
> "ERROR:  cannot compare arrays of different element types",
> when I analyze a specific table in my database.
> ...
> GOLink has the basetype int8, which GOLinkList has the basetype int8[].

I wonder whether you have somehow managed to store some GOLinkList
entries that internally show their element type as GOLink rather than
int8.  That shouldn't happen (since we don't support arrays of domain
types) but it would explain the error message.  Any idea what you did
to create this situation?

            regards, tom lane

Tom Lane wrote:
> "Florian G. Pflug" <fgp@phlo.org> writes:
>
>>Since sometime yesterday, my postgresql (7.4.5) reports
>>"ERROR:  cannot compare arrays of different element types",
>>when I analyze a specific table in my database.
>>...
>>GOLink has the basetype int8, which GOLinkList has the basetype int8[].
>
> I wonder whether you have somehow managed to store some GOLinkList
> entries that internally show their element type as GOLink rather than
> int8.  That shouldn't happen (since we don't support arrays of domain
> types) but it would explain the error message.  Any idea what you did
> to create this situation?

Hm.. We hade some strange problems a week or so ago, where updating
fields of type GOLinkList would mange the numbers we where trying to
store. E.h, we did 'update datagraph."Filiale" set "Historie" =
"Historie" || array[1] where self = ...', but after the the update, the
last number in the "Historie" field would be some strangly large number
(somewhere in the range of a few millions). I'm not sure that these
problems happened for the "Filiale" table - I'll check with my collegue
who stumbled upton this...

Anyway, this only happened for a few records, and the problem vanished
after deleting and reinserting those records. Could it be that we
somehow managed to insert "GOLink"s instead of int8s into the
GOLinkList, and
those got misinterpretet as int8s when selecting, thus causing the
data-mangling?

greetings, Florian Pflug