Thread: Search system catalog for mystery type
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 I have looked through tables, types, sequences etc. I even did a PLAIN schema (no data) backup on the DB and did a text search on the resulting file for this name, nothing. How do I search the system catalogs to find this particular "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... > 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 > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
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... > 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/ > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >