<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
@font-face
{font-family:"Lucida Console";
panose-1:2 11 6 9 4 5 4 2 2 4;}
@font-face
{font-family:"Lucida Console \,serif";}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
p
{mso-style-priority:99;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
pre
{mso-style-priority:99;
mso-style-link:"HTML Preformatted Char";
margin:0cm;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";}
span.HTMLPreformattedChar
{mso-style-name:"HTML Preformatted Char";
mso-style-priority:99;
mso-style-link:"HTML Preformatted";
font-family:Consolas;
mso-fareast-language:EN-US;}
p.msonormal0, li.msonormal0, div.msonormal0
{mso-style-name:msonormal;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
span.EmailStyle20
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.EmailStyle22
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-GB" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
<p class="MsoNormal"><span style="color:#1F497D">Hi Paul,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Ztrans_symdig is the actual table.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">I have not been able to reproduce this at all, which is damn frustrating. You’d think this was some problem in the base symdig table which is replicated over to the ztrans when we do a create table as select
where 1 = 0. But I can run countless versions of that command with no problem whatsoever.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">The really, really strange thing is the way it swaps from the internal to the external database. On one run the problem could surface in the internal database, the very next run the problem evidences in the external
database where the ztrans table on the internal database is now fine. I suspect some locking weirdness is occurring, but both internal and external database are not heavily used. Nonetheless, both internal and external ztrans are created in sessions with autocommit
on and readlock = nolock.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">I’ll persevere and raise an issue when I give up or finally crack a reliable test case.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Marty<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span lang="EN-US" style="mso-fareast-language:EN-GB">From:</span></b><span lang="EN-US" style="mso-fareast-language:EN-GB"> Paul Mason <Paul.Mason@actian.com>
<br>
<b>Sent:</b> 23 June 2021 15:52<br>
<b>To:</b> info-ingres@lists.planetingres.org<br>
<b>Subject:</b> Re: [Info-ingres] The column with no datatype<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Is ztrans_symdig the actual table or is it the registration in the star DB?<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Anyway datatype 36 is something called a “long varchar locator”. I think it’s supposed to be an internal datatype, not one you can define as a table column type. That would be why iitypename is not naming it for you.
<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">A quick look at iitypename’s implementation and yes, it returns an empty string if the datatype is one that’s “not allowed in database”. So I think the fact you’ve got it in table is a bug.
<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Definitely raise an issue. Would be helpful if you can reproduce it.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span lang="EN-US" style="mso-fareast-language:EN-GB">From:</span></b><span lang="EN-US" style="mso-fareast-language:EN-GB">
<a href="mailto:info-ingres-bounces@lists.planetingres.org">info-ingres-bounces@lists.planetingres.org</a> <<a href="mailto:info-ingres-bounces@lists.planetingres.org">info-ingres-bounces@lists.planetingres.org</a>>
<b>On Behalf Of </b>Martin Bowes<br>
<b>Sent:</b> 23 June 2021 15:27<br>
<b>To:</b> <a href="mailto:info-ingres@lists.planetingres.org">info-ingres@lists.planetingres.org</a><br>
<b>Subject:</b> Re: [Info-ingres] The column with no datatype<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span style="color:#1F497D">Hi All,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Well 600 lines of ESQLC later … nope still can’t make a test case.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Here is an outline of the situation. We have three databases: an External one, an Internal one and a distributed database that links them.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">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.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">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.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">So in External:<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Drop table if exists ztrans;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Create table ztrans as select from table where it’s recent with no journaling;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Then in Internal:<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Drop table if exists ztrans;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Create table ztrans as select * from table where 1 = 0 with nojournaling.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Then in DDB:<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Remove existing ztrans registrations;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Register external ztrans;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Register internal ztrans<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Insert into internal_ztrans as select * from external_zrans where sanity condition;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Then in Internal:<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Modify ztrans to structure;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Insert into table select * from ztrans where not exists (..we already have this data …)<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Simple enough.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">But either the External or Internal ztrans table will not have a datatype listed in iicolumns for what should be the long varchar column.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">select varchar(r.relid, 13), attid, varchar(attname, 13),</span><span style="font-size:10.0pt;font-family:"Lucida Console";mso-fareast-language:EN-GB"><o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">a.attfrmt, a.attfrml,<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">varchar(uppercase(iitypename(ii_ext_type(a.attfrmt, a.attfrml))), 12) as datatype<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">from iiattribute a<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""> join iirelation r on a.attrelid = r.reltid and a.attrelidx = r.reltidx<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">where a.attname = 'responses'<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""><o:p> </o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">┌─────────────┬──────┬─────────────┬───────┬─────────────┬────────────┐<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">│col1 │attid │col3 │attfrmt│attfrml │datatype │<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">├─────────────┼──────┼─────────────┼───────┼─────────────┼────────────┤<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">│symdig │ 18│responses │ 22│ 32│LONG VARCHAR│<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">│ztrans_symdig│ 18│responses │
<span style="background:yellow;mso-highlight:yellow">36│ 16│</span> │<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">└─────────────┴──────┴─────────────┴───────┴─────────────┴────────────┘<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">(2 rows)<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">I’ve run a verifydb -odbms_catalogs on the internal database and it runs without any report of a problem.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">The internal and external tables both have correct linkage to iietabs holding the long varchar data and this is selectable without any error.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Anyone got any ideas?<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Marty<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span lang="EN-US" style="mso-fareast-language:EN-GB">From:</span></b><span lang="EN-US" style="mso-fareast-language:EN-GB"> Martin Bowes
<br>
<b>Sent:</b> 18 June 2021 13:25<br>
<b>To:</b> 'paul.white@shift7solutions.com.au' <<a href="mailto:paul.white@shift7solutions.com.au">paul.white@shift7solutions.com.au</a>>;
<a href="mailto:info-ingres@lists.planetingres.org">info-ingres@lists.planetingres.org</a><br>
<b>Subject:</b> RE: [Info-ingres] The column with no datatype<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span style="color:#1F497D">Hi Paul,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">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. <o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Marty<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span lang="EN-US" style="mso-fareast-language:EN-GB">From:</span></b><span lang="EN-US" style="mso-fareast-language:EN-GB"> Paul White <<a href="mailto:paul.white@shift7solutions.com.au">paul.white@shift7solutions.com.au</a>>
<br>
<b>Sent:</b> 18 June 2021 13:08<br>
<b>To:</b> <a href="mailto:info-ingres@lists.planetingres.org">info-ingres@lists.planetingres.org</a><br>
<b>Subject:</b> Re: [Info-ingres] The column with no datatype<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p>This is a bit like Colonel Mustard in the Drawing room with the ice pick<o:p></o:p></p>
<p><o:p> </o:p></p>
<p>Need some clues Marty. <o:p></o:p></p>
<p>help table? create time? what is in errlog.log, iidbms...log around the time?<o:p></o:p></p>
<p>Is the database and table journaled? Maybe run auditdb -aborted_transactions near the create time.<o:p></o:p></p>
<p>What is the state of the the extended table?<o:p></o:p></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">select r1.relid as base_table, c.attname as column_name, r2.relid as extend_table<br>
from iirelation r1,iirelation r2,iiattribute c,iiextended_relation e<br>
where r1.reltid=e.etab_base and r2.reltid=e.etab_extension<br>
and r1.reltid=c.attrelid<br>
and c.attid=e.etab_attid<br>
and r1.reltidx=0<br>
and c.attrelidx=0</span><br>
<span style="font-family:"Courier New"">where column_name = 'responses'</span><br>
<span style="font-family:"Courier New"">order by base_table,column_name</span> <o:p>
</o:p></p>
<p><span style="font-family:"Courier New"">help table (r2.relid from above)</span><o:p></o:p></p>
<p><span style="font-family:"Courier New"">select * from iirelation where table_name = (r2.relid from above)</span><o:p></o:p></p>
<p><span style="font-family:"Courier New"">Paul</span><o:p></o:p></p>
<p><o:p> </o:p></p>
<p>On 18/06/2021 9:18 pm, Martin Bowes wrote:<o:p></o:p></p>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<p class="MsoNormal"><span style="color:#1F497D">Hi All,</span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D">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:</span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D">direct execute immediate ‘create table ztrans_symdig as select * from symdig’</span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D">with node = ‘…’, database =’…’</span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D">So I set up a test case and … nope, worked perfectly.</span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D">The user’s log file had better be phenomenally good.</span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D">Marty</span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D"> </span><o:p></o:p></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span lang="EN-US" style="mso-fareast-language:JA">From:</span></b><span lang="EN-US" style="mso-fareast-language:JA"> Martin Bowes
<a href="mailto:martin.bowes@ndph.ox.ac.uk"><martin.bowes@ndph.ox.ac.uk></a> <br>
<b>Sent:</b> 18 June 2021 11:25<br>
<b>To:</b> <a href="mailto:info-ingres@lists.planetingres.org">info-ingres@lists.planetingres.org</a><br>
<b>Subject:</b> [Info-ingres] The column with no datatype</span><o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">Hi All,<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">So one day I did…<o:p></o:p></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console \,serif"">copydb -c -uoqs oqs_result_live ztrans_symdig</span><o:p></o:p></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console \,serif"">INGRES COPYDB Copyright 2016 Actian Corporation</span><o:p></o:p></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console \,serif"">Unload directory is '/user/ingres'.</span><o:p></o:p></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console \,serif"">Reload directory is '/user/ingres'.</span><o:p></o:p></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console \,serif"">There are 0 sequences owned by user 'oqs'.</span><o:p></o:p></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console \,serif"">There is one table owned by user 'oqs'.</span><o:p></o:p></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt">E_AD2003 ADF routine was passed an unknown datatype name.</span><o:p></o:p></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console \,serif""> </span><o:p></o:p></p>
<p class="MsoNormal">‘WTF!’ I thought to myself and after some investigation….<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console \,serif"">select column_sequence, column_datatype</span><o:p></o:p></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console \,serif"">from iicolumns</span><o:p></o:p></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console \,serif"">where table_name='ztrans_symdig' and column_name = 'responses';</span><o:p></o:p></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console \,serif""> </span><o:p></o:p></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console \,serif"">┌───────────────┬────────────────────────────────┐</span><o:p></o:p></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console \,serif"">│column_sequence│column_datatype │</span><o:p></o:p></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console \,serif"">├───────────────┼────────────────────────────────┤</span><o:p></o:p></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console \,serif"">│ 18│ │</span><o:p></o:p></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console \,serif"">└───────────────┴────────────────────────────────┘</span><o:p></o:p></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console \,serif"">(1 row)</span><o:p></o:p></p>
<p class="MsoNormal">How is that possible?<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">Now iicolumns is a view and so I went back to the iirelation and iiattribute tables:<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console \,serif"">select varchar(r.relid, 13), attid, varchar(attname, 13),</span><o:p></o:p></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console \,serif"">a.attfrmt, a.attfrml,</span><o:p></o:p></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console \,serif"">varchar(uppercase(iitypename(ii_ext_type(a.attfrmt, a.attfrml))), 12) as datatype</span><o:p></o:p></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console \,serif"">from iiattribute a</span><o:p></o:p></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console \,serif""> join iirelation r on a.attrelid = r.reltid and a.attrelidx = r.reltidx</span><o:p></o:p></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console \,serif"">where a.attname = 'responses'</span><o:p></o:p></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console \,serif""> </span><o:p></o:p></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console \,serif"">┌─────────────┬──────┬─────────────┬───────┬─────────────┬────────────┐</span><o:p></o:p></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console \,serif"">│col1 │attid │col3 │attfrmt│attfrml │datatype │</span><o:p></o:p></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console \,serif"">├─────────────┼──────┼─────────────┼───────┼─────────────┼────────────┤</span><o:p></o:p></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console \,serif"">│symdig │ 18│responses │ 22│ 32│LONG VARCHAR│</span><o:p></o:p></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console \,serif"">│ztrans_symdig│ 18│responses │ 36│ 16│ │</span><o:p></o:p></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console \,serif"">└─────────────┴──────┴─────────────┴───────┴─────────────┴────────────┘</span><o:p></o:p></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console \,serif"">(2 rows)</span><o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">I’m trying to track down how the user has created this table and if they have a log of the activity.<o:p></o:p></p>
<p class="MsoNormal">I think the user is doing a create table ztrans_symdig as select * from symdig where 1 = 0;<o:p></o:p></p>
<p class="MsoNormal">However, when I try this the responses column correctly becomes a long varchar without any problem.<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">Any suggestions?<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">Marty<o:p></o:p></p>
<p class="MsoNormal" style="margin-bottom:12.0pt"><span style="font-size:12.0pt;font-family:"Times New Roman",serif;mso-fareast-language:EN-GB"><o:p> </o:p></span></p>
<pre>_______________________________________________<o:p></o:p></pre>
<pre>Info-ingres mailing list<o:p></o:p></pre>
<pre><a href="mailto:Info-ingres@lists.planetingres.org">Info-ingres@lists.planetingres.org</a><o:p></o:p></pre>
<pre><a href="https://lists.planetingres.org/mailman/listinfo/info-ingres">https://lists.planetingres.org/mailman/listinfo/info-ingres</a><o:p></o:p></pre>
</blockquote>
</div>
</body>
</html>