Search system catalog for mystery type

On Wed, Nov 04, 2009 at 11:31:55AM -0500, Carlo Stonebanks wrote:

When I try the following command:
ALTER TABLE mdx_core.audit_impt RENAME TO _audit_impt;

ERROR: type "_audit_impt" already exists

How do I search the system catalogs to find this particular "type"?

select typname, typinput, typoutput from pg_type;

may help. I've not realized before, but most types/relations seem to
appear again with an underscore (i.e. your case) for their array type.
See:

http://www.postgresql.org/docs/current/static/sql-createtype.html#SQL-CREATETYPE-NOTES

--
Sam http://samason.me.uk/

Sam Mason <sam@samason.me.uk> writes:

On Wed, Nov 04, 2009 at 11:31:55AM -0500, Carlo Stonebanks wrote:

When I try the following command:
ALTER TABLE mdx_core.audit_impt RENAME TO _audit_impt;

ERROR: type "_audit_impt" already exists

You aren't going to be able to do that, because of the conflict with the
array type for it.

It might work to rename the array type out of the way --- I think that
in all PG releases where we have array of composite, the underscore
prefix is only a convention and not the hard-wired way of finding
the array type for a given element type.

There is some code in there to rename array types out of the way
automatically, but I think it only works during CREATE not RENAME.

regards, tom lane

Carlo Stonebanks wrote:

When I try the following command:
ALTER TABLE mdx_core.audit_impt RENAME TO _audit_impt;

I get the error message:

ERROR: type "_audit_impt" already exists
SQL state: 42710

Probably the easiest way around this is to use two underscores instead
of one:

ALTER TABLE mdx_core.audit_impt RENAME TO __audit_impt;

Or any other char for that matter -- you picked the only one that would
cause a problem. Even a space should be fine:

ALTER TABLE mdx_core.audit_impt RENAME TO " audit_impt";

(Not sure I can recommend this though)

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Ok, it appears that relations and types automatically get a copy made of
some object with the same name, prefixed with a leading underscore. Now,
that was a dangerous choice! By convention, we all know that the safest
characters to use across DB platforms are lowercase chars and underscores.

In the past, I had been able to prefix tables, indexes, etc with
underscores. It is a tactic we use when I am "soft deleting" objects, or
installing new versions over old. The number of underscrores indicates how
many versions old it is. This convention (rather than the classic
"my_table_old") makes the tables float to the top of schema listings,
attracting attention during Db maintenance.

This was really common with us with PG for years, and now it errors out -
what happened, and when?

Carlo

"Alvaro Herrera" <alvherre@commandprompt.com> wrote in message
news:20091104205734.GJ3531@alvh.no-ip.org...

On Fri, Nov 06, 2009 at 11:09:23AM -0500, Carlo Stonebanks wrote:

This was really common with us with PG for years, and now it errors out -
what happened, and when?

There are references in the docs at least back to 7.1:

http://www.postgresql.org/docs/7.1/static/sql-createtype.html#R2-SQL-CREATETYPE-3

are you sure you haven't started doing something new?

--
Sam http://samason.me.uk/

Sam Mason <sam@samason.me.uk> writes:

On Fri, Nov 06, 2009 at 11:09:23AM -0500, Carlo Stonebanks wrote:

This was really common with us with PG for years, and now it errors out -
what happened, and when?

There are references in the docs at least back to 7.1:
http://www.postgresql.org/docs/7.1/static/sql-createtype.html#R2-SQL-CREATETYPE-3
are you sure you haven't started doing something new?

The array-types-use-leading-underscore convention has been there since
Berkeley days. What did change recently is that arrays of composite
types didn't exist until I-forget-which release. So now, if you have
a table foo, you also have a rowtype foo and an array type _foo; you
didn't use to have the latter.

In simple cases the system will attempt to rename a conflicting array
type out of your way, but I don't think ALTER RENAME does that.

regards, tom lane

There are renamed tables with one leading underscore sitting on the system
right now. I don't know how many mechanisms there are for renaming, as this
renaming (until now) was done from a "rename" function within pgAdmin or EMS
PG-SQL Manager - this is the first time I can think of having explicitly
used ALTER RENAME, though.

"Sam Mason" <sam@samason.me.uk> wrote in message
news:20091106170833.GX5407@samason.me.uk...