[Info-ingres] The column with no datatype

[Martin Bowes]

Hi All,

Well 600 lines of ESQLC later … nope still can’t make a test case.

Here is an outline of the situation. We have three databases: an External one, an Internal one and a distributed database that links them.

In the Internal and external databases we have a table which has 288 columns, mainly integer2, integer4, a few Booleans and ingresdates, but with one long varchar as column 18 of the 288.

We have a process that transfers recent data in the external version of this table, via the ddb into the internal version of the table.

So in External:
Drop table if exists ztrans;
Create table ztrans as select from table where it’s recent with no journaling;

Then in Internal:
Drop table if exists ztrans;
Create table ztrans as select * from table where 1 = 0 with nojournaling.

Then in DDB:
Remove existing ztrans registrations;
Register external ztrans;
Register internal ztrans
Insert into internal_ztrans as select * from external_zrans where sanity condition;

Then in Internal:
Modify ztrans to structure;
Insert into table select * from ztrans where not exists (..we already have this data …)

Simple enough.

But either the External or Internal ztrans table will not have a datatype listed in iicolumns for what should be the long varchar column.

select varchar(r.relid, 13), attid, varchar(attname, 13),
a.attfrmt, a.attfrml,
varchar(uppercase(iitypename(ii_ext_type(a.attfrmt, a.attfrml))), 12) as datatype
from iiattribute a
    join iirelation r on a.attrelid = r.reltid and a.attrelidx = r.reltidx
where a.attname = 'responses'

┌─────────────┬──────┬─────────────┬───────┬─────────────┬────────────┐
│col1         │attid │col3         │attfrmt│attfrml      │datatype    │
├─────────────┼──────┼─────────────┼───────┼─────────────┼────────────┤
│symdig       │    18│responses    │     22│           32│LONG VARCHAR│
│ztrans_symdig│    18│responses    │     36│           16│            │
└─────────────┴──────┴─────────────┴───────┴─────────────┴────────────┘
(2 rows)

I’ve run a verifydb -odbms_catalogs on the internal database and it runs without any report of a problem.

The internal and external tables both have correct linkage to iietabs holding the long varchar data and this is  selectable without any error.

Anyone got any ideas?

Marty
From: Martin Bowes
Sent: 18 June 2021 13:25
To: 'paul.white at shift7solutions.com.au' <paul.white at shift7solutions.com.au>; info-ingres at lists.planetingres.org
Subject: RE: [Info-ingres] The column with no datatype

Hi Paul,

Worse than Colonel Mustard with the machete, something has just managed to rebuild the table without a problem. I’m not sure yet if this is some automated task, but judging by the errlog it looks like it’s been throwing wobblies since May.

Marty

From: Paul White <paul.white at shift7solutions.com.au<mailto:paul.white at shift7solutions.com.au>>
Sent: 18 June 2021 13:08
To: info-ingres at lists.planetingres.org<mailto:info-ingres at lists.planetingres.org>
Subject: Re: [Info-ingres] The column with no datatype


This is a bit like Colonel Mustard in the Drawing room with the ice pick



Need some clues Marty.

help table?   create time?  what is in errlog.log, iidbms...log  around the time?

Is the database and table journaled?  Maybe run auditdb -aborted_transactions near the create time.

What is the state of the the extended table?
select r1.relid as base_table, c.attname as column_name, r2.relid as extend_table
from iirelation r1,iirelation r2,iiattribute c,iiextended_relation e
where r1.reltid=e.etab_base and r2.reltid=e.etab_extension
  and r1.reltid=c.attrelid
  and c.attid=e.etab_attid
  and r1.reltidx=0
  and c.attrelidx=0
where column_name = 'responses'
order by base_table,column_name

help table (r2.relid from above)

select * from iirelation where table_name = (r2.relid from above)

Paul



On 18/06/2021 9:18 pm, Martin Bowes wrote:
Hi All,

Knowing the perverted nature of the user in question I wondered if he had created the table from a distributed database via a statement like:

direct execute immediate ‘create table ztrans_symdig as select * from symdig’
with node = ‘…’, database =’…’

So I set up a test case and … nope, worked perfectly.

The user’s log file had better be phenomenally good.

Marty

From: Martin Bowes <martin.bowes at ndph.ox.ac.uk><mailto:martin.bowes at ndph.ox.ac.uk>
Sent: 18 June 2021 11:25
To: info-ingres at lists.planetingres.org<mailto:info-ingres at lists.planetingres.org>
Subject: [Info-ingres] The column with no datatype

Hi All,

So one day I did…
copydb -c -uoqs oqs_result_live ztrans_symdig
INGRES COPYDB Copyright 2016 Actian Corporation
Unload directory is '/user/ingres'.
Reload directory is '/user/ingres'.
There are 0 sequences owned by user 'oqs'.
There is one table owned by user 'oqs'.
E_AD2003 ADF routine was passed an unknown datatype name.

‘WTF!’ I thought to myself and after some investigation….

select column_sequence, column_datatype
from iicolumns
where table_name='ztrans_symdig' and column_name = 'responses';

┌───────────────┬────────────────────────────────┐
│column_sequence│column_datatype                 │
├───────────────┼────────────────────────────────┤
│             18│                                │
└───────────────┴────────────────────────────────┘
(1 row)
How is that possible?

Now iicolumns is a view and so I went back to the iirelation and iiattribute tables:

select varchar(r.relid, 13), attid, varchar(attname, 13),
a.attfrmt, a.attfrml,
varchar(uppercase(iitypename(ii_ext_type(a.attfrmt, a.attfrml))), 12) as datatype
from iiattribute a
    join iirelation r on a.attrelid = r.reltid and a.attrelidx = r.reltidx
where a.attname = 'responses'

┌─────────────┬──────┬─────────────┬───────┬─────────────┬────────────┐
│col1         │attid │col3         │attfrmt│attfrml      │datatype    │
├─────────────┼──────┼─────────────┼───────┼─────────────┼────────────┤
│symdig       │    18│responses    │     22│           32│LONG VARCHAR│
│ztrans_symdig│    18│responses    │     36│           16│            │
└─────────────┴──────┴─────────────┴───────┴─────────────┴────────────┘
(2 rows)

I’m trying to track down how the user has created this table and if they have a log of the activity.
I think the user is doing a create table ztrans_symdig as select * from symdig where 1 = 0;
However,  when I try this the responses column correctly becomes a long varchar without any problem.

Any suggestions?

Marty


_______________________________________________

Info-ingres mailing list

Info-ingres at lists.planetingres.org<mailto:Info-ingres at lists.planetingres.org>

https://lists.planetingres.org/mailman/listinfo/info-ingres
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20210623/a4e382f3/attachment-0001.html>