Thread: possible issue with array type created for every heap relationcomposite type
Hello,
In Postgres 8.3, it was decided that an array type would be automatically created for every heap relation's composite type.
Reference thread:
The possible issue I would like to note is related to how the array type is named in makeArrayTypeName() function. The composite type will take the heap relation's relname as its typname and the array type will usually be named with an underscore prepended (after first attempting to use the relname and hitting typname collision with the composite type). If the typname with the underscore prepended is already taken, the logic is to continue prepending underscores until there are no typname collisions (truncating the end of the typname if typname gets past NAMEDATALEN of 64). It is possible that enough heap relations with similar relnames could cause more and more typname collisions until you end up with typnames that primarily consist of underscores or not being able to construct a typname because we have reached a typname consisting of all underscores (which can cause table creation failure).
This is more likely to happen when heap relnames are similar and of string length 63+. I can see this possibly being an issue with table partitioning if a user decides on partition names that reach string length 63+ (assuming user uses exactly 63 characters or does not hit relation already exists from relname truncation). This may also become an issue if we ever decide to do automated partition naming instead of having the user naming the partitions manually.
Is this an issue we should be worrying about? When and how often are array types of heap relation's composite type used (I can only think of doing array_agg on table rows)? Should we consider coding up "CREATE TYPE foo ARRAY OF bar" as suggested in the past in the above referenced hackers thread?
Attached are some SQL examples describing this issue.
Thanks,
Jimmy
Attachment
Re: possible issue with array type created for every heap relation composite type
From
Tom Lane
Date:
Jimmy Yih <jyih@pivotal.io> writes: > The possible issue I would like to note is related to how the array type is > named in makeArrayTypeName() function. The composite type will take the > heap relation's relname as its typname and the array type will usually be > named with an underscore prepended (after first attempting to use the > relname and hitting typname collision with the composite type). If the > typname with the underscore prepended is already taken, the logic is to > continue prepending underscores until there are no typname collisions > (truncating the end of the typname if typname gets past NAMEDATALEN of > 64). It is possible that enough heap relations with similar relnames could > cause more and more typname collisions until you end up with typnames that > primarily consist of underscores or not being able to construct a typname > because we have reached a typname consisting of all underscores (which can > cause table creation failure). We've never heard a field report of this happening, so I'm not terribly concerned about it. regards, tom lane