[Info-Ingres] E_OP0930 ... and confusion reigns

[Martin Bowes]

Hi All,

 

II 10.0.0 (a64.lnx/132)NPTL + patch14122

 

I have a database in which the attempt to optimize or statdump a specific table will fail with the message:

E_OP0930        optimizedb: database 't_ldb15', table 'cryovial_store' is not a base table and will be ignored.

 

Typically this message is associated with attempting to optimize/statdump something that is not a table.

 

In this case the table in question is defined as btree, unique, nocompression…it is a table.

 

The table name is unique in the entire database…select count(*) from iitables for the table_name shows only 1 row and its table_type=’T’.

 

The only thing unusual about this table is that the table_owner has defined a view upon the table and that one user (not the table owner) has registered a synonym for the view and called that synonym the same name as the table in question.

 

Sadly when I attempt to set this situation up with a dummy database the error will not reproduce.

 

Has anyone got any idea what else might be wrong?

 

Martin Bowes

[Martin Bowes]

Hi All,

 

Playing with II_EMED_SET has shown that the optimizer will issue something like:

select t.table_owner, t.num_rows, t.table_type,

  t.table_reltid, t.table_reltidx, t.number_pages, t.table_stats, t.overflow_pages, t.row_width

from(iitables t left join iisynonyms s on t.table_name=s.table_name)

where(t.table_owner='lust' )and((t.table_name='cryovial_store' )or(s.synonym_name= 'cryovial_store' ))

Executing . . .

 

 

┌────────────────────────────────┬─────────────┬──────┬─────────────┬─────────────┬─────────────┬──────┬─────────────┬─────────────┐

│table_owner                     │num_rows     │table_│table_reltid │table_reltidx│number_pages │table_│overflow_page│row_width    │

├────────────────────────────────┼─────────────┼──────┼─────────────┼─────────────┼─────────────┼──────┼─────────────┼─────────────┤

│lust                            │            0│V     │          629│            0│            3│N     │            0│           58│

│lust                            │        99147│T     │          278│            0│         1706│Y     │            0│           58│

│lust                            │            0│V     │          630│            0│            3│N     │            0│           58│

└────────────────────────────────┴─────────────┴──────┴─────────────┴─────────────┴─────────────┴──────┴─────────────┴─────────────┘

 

I think the system is actually expecting a single row back and is only processing the last row. It sees it as a view and bitchs.

 

I believe the query used is wrong and should put table owner details into the join condition and use s.table_name not s.synonym_name in the where clause.

 

Marty

[Ingres Forums]

I think we've got this figured out.
In the community edition source, back/opf/opq/opqutils.sc has a query
which looks like this:

exec sql repeated select t.table_owner, t.num_rows,
t.table_type,
t.table_reltid, t.table_reltidx,
t.number_pages,
t.table_stats, t.overflow_pages, t.row_width,
t.storage_structure
into :es_ownname, :es_nrows, :es_type,
:es_reltid, :es_xreltid, :es_pages,
:es_tstat, :es_ovflow, :es_relwid,
:es_storage_str
from (iitables t left join iisynonyms s on
t.table_name = s.table_name)
where (t.table_owner = :es_owner)
and ((t.table_name = :es_relname) or
(s.synonym_name = :es_relname));

In the case Marty describes, 2 rows are retrieved. One is a table, the
other is a view. If the view is returned after the table in the result
set, the problem occurs.

Will knock together a test case and send it over to the support guys for
perusal.

GJ


--
geraintjones
------------------------------------------------------------------------
geraintjones's Profile: http://community.actian.com/forum/member.php?userid=57763
View this thread: http://community.actian.com/forum/showthread.php?t=15860

[Martin Bowes]

Ø  I believe the query used is wrong and should put table owner details into the join condition and use s.table_name not s.synonym_name in the where clause.

 

Actually on reflection I’ll change that to say: I believe the query used is wrong and should put table owner details into the join condition.

[Paul A.]

On 15/04/2014 15:23, Martin Bowes wrote:
> one user (not the table owner) has registered a synonym for the view
> and called that synonym the same name as the table in question.

Isn't this the real problem, that the system allows views to be created
with the same name as an existing table, making a query ambiguous?

[Alex Hanshaw]

The optimized query should probably restrict result rows to type T.

Alex

--
Alex Hanshaw
Director, Engineering
Actian | Engineering
Accelerating Big Data 2.0
O +44 1753 559515
M +44 7793 757929
www.actian.com

_______________________________________________
Info-Ingres mailing list
Info-...@kettleriverconsulting.com
http://ext-cando.kettleriverconsulting.com/mailman/listinfo/info-ingres

[Martin Bowes]

Hi Paul,

No I don't think so.

A single user must have unique names for their tables/views/synonyms. But any user may create and should be able to create a table/view/synonym with the same names as those used by any other user. There are some code benefits in this approach.

Marty

[Alex Hanshaw]

Fix is currently being put through HOQA testing and will be available in a 10.1.0 patch soon.
Great work from the Oxford Uni guys in building out a test case and figuring out the root cause.

Thanks

Alex

--
Alex Hanshaw
Director, Engineering
Actian | Engineering
Accelerating Big Data 2.0
O +44 1753 559515
M +44 7793 757929
www.actian.com


-----Original Message-----

From: info-ingr...@kettleriverconsulting.com [mailto:info-ingr...@kettleriverconsulting.com] On Behalf Of Ingres Forums
Sent: 15 April 2014 15:34
To: info-...@kettleriverconsulting.com
Subject: Re: [Info-Ingres] E_OP0930 ... and confusion reigns