Thread: Is it bug ?
Hi, Is it bug ? Try to create these tables with following order CREATE TABLE m_tvh ( web_teid varchar(40) UNIQUE ); CREATE TABLE m_tvh_web ( teid varchar(40) UNIQUE ); Both wany make index with name 'm_tvh_web_teid_key'. Postgre version = 7.3.1.
Ivar wrote: > Hi, > > Is it bug ? > Try to create these tables with following order > > CREATE TABLE m_tvh ( > web_teid varchar(40) UNIQUE > ); > > CREATE TABLE m_tvh_web ( > teid varchar(40) UNIQUE > ); > > Both wany make index with name 'm_tvh_web_teid_key'. > > Postgre version = 7.3.1. I would say so. I don't think the standard forbids the above, and if that is right, it clearly is a bug. We maybe should relocate auto-created indexes for UNIQUE and PKEY definitions into some sort of special schema and give them funny names? Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
> We maybe should relocate auto-created indexes for UNIQUE and PKEY > definitions into some sort of special schema and give them funny names? MS SQL adds funny names as columnName_236321_215251_156, seems that adds some random at end to avoid similar errors. "Jan Wieck" <JanWieck@Yahoo.com> wrote in message news:3EEE063A.9010805@Yahoo.com... > Ivar wrote: > > Hi, > > > > Is it bug ? > > Try to create these tables with following order > > > > CREATE TABLE m_tvh ( > > web_teid varchar(40) UNIQUE > > ); > > > > CREATE TABLE m_tvh_web ( > > teid varchar(40) UNIQUE > > ); > > > > Both wany make index with name 'm_tvh_web_teid_key'. > > > > Postgre version = 7.3.1. > > I would say so. I don't think the standard forbids the above, and if > that is right, it clearly is a bug. > > We maybe should relocate auto-created indexes for UNIQUE and PKEY > definitions into some sort of special schema and give them funny names? > > > Jan > > -- > #======================================================================# > # It's easier to get forgiveness for being wrong than for being right. # > # Let's break this rule - forgive me. # > #================================================== JanWieck@Yahoo.com # > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend >
"Ivar" <ivar@lumisoft.ee> writes:
> "Jan Wieck" <JanWieck@Yahoo.com> wrote
>> We maybe should relocate auto-created indexes for UNIQUE and PKEY
>> definitions into some sort of special schema and give them funny names?
> MS SQL adds funny names as columnName_236321_215251_156, seems that adds
> some random at end to avoid similar errors.
I think it's good that the index name is predictable. I think we should
stick to the existing behavior as much as we can.
It would probably make sense to check if the generated name is actually
unused, and to stick some digits on the end if not. For example, try
tab_col_key
tab_col_key1
tab_col_key2
etc. until we find an unused name. Of course this is still subject to
race conditions, but I think in practice it will solve the problem.
regards, tom lane
Thats the way my graphical DB dezign tool does it. Tom Lane wrote: > "Ivar" <ivar@lumisoft.ee> writes: > >>"Jan Wieck" <JanWieck@Yahoo.com> wrote >> >>>We maybe should relocate auto-created indexes for UNIQUE and PKEY >>>definitions into some sort of special schema and give them funny names? > > >>MS SQL adds funny names as columnName_236321_215251_156, seems that adds >>some random at end to avoid similar errors. > > > I think it's good that the index name is predictable. I think we should > stick to the existing behavior as much as we can. > > It would probably make sense to check if the generated name is actually > unused, and to stick some digits on the end if not. For example, try > tab_col_key > tab_col_key1 > tab_col_key2 > etc. until we find an unused name. Of course this is still subject to > race conditions, but I think in practice it will solve the problem. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html >
May be its wiser to add additional(may be more than 1) _ between table and column. This solves problem. (For current problem it works, see if I'm missing somethig) CREATE TABLE m_tvh ( web_teid varchar(40) UNIQUE ); CREATE TABLE m_tvh_web ( teid varchar(40) UNIQUE ); Both wany make index with name 'm_tvh_web_teid_key'. new results will be: 1) m_tvh__web_teid 2) m_tvh_web__teid "Tom Lane" <tgl@sss.pgh.pa.us> wrote in message news:170.1055956685@sss.pgh.pa.us... > "Ivar" <ivar@lumisoft.ee> writes: > > "Jan Wieck" <JanWieck@Yahoo.com> wrote > >> We maybe should relocate auto-created indexes for UNIQUE and PKEY > >> definitions into some sort of special schema and give them funny names? > > > MS SQL adds funny names as columnName_236321_215251_156, seems that adds > > some random at end to avoid similar errors. > > I think it's good that the index name is predictable. I think we should > stick to the existing behavior as much as we can. > > It would probably make sense to check if the generated name is actually > unused, and to stick some digits on the end if not. For example, try > tab_col_key > tab_col_key1 > tab_col_key2 > etc. until we find an unused name. Of course this is still subject to > race conditions, but I think in practice it will solve the problem. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html >