• [Info-ingres] defaults in session tables

    From Martin Bowes@21:1/5 to All on Thu Feb 4 09:00:45 2021
    Hi All,

    Can someone explain this to me...

    declare global temporary table session.test(
    tom integer1 not null not default,
    dick integer4 not null with default -666,
    harry char(10) not null with default 'asdfg'
    ) on commit preserve rows with norecovery;

    insert into session.test(tom) values (1), (2), (3);

    select * from session.test;
    Executing . . .

    E_PS0476 DECLARE GLOBAL TEMPORARY TABLE: User-defined defaults are not
    allowed in temporary tables, CREATE PROCEDURE, or REGISTER TABLE.
    Try WITH DEFAULT instead.
    (Thu Feb 4 08:56:57 2021)

    Okay, that seems a little restrictive to me, but there is a workaround...

    create table test(
    tom integer1 not null not default,
    dick integer4 not null with default -666,
    harry char(10) not null with default 'asdfg'
    );

    declare global temporary table session.test
    as select * from test where 1 = 0
    on commit preserve rows with norecovery;

    insert into session.test(tom) values (1), (2), (3);

    select * from session.test;
    Executing . . .

    (0 rows)
    (3 rows)

    ‚€€€€€€ˆ€€€€€€€€€€€€€ˆ€€€€€€€€€€ƒ
    tom dick harry 
    †€€€€€€Š€€€€€€€€€€€€€Š€€€€€€€€€€‡
     1 -666asdfg 
     2 -666asdfg 
     3 -666asdfg 
    „€€€€€€‰€€€€€€€€€€€€€‰€€€€€€€€€€…
    (3 rows)

    So clearly we have a session temp table which respects my user defaults...we just can't create it directly.

    That seems weird.

    Marty

    <html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=koi8-r">
    <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:"Lucida Console";
    panose-1:2 11 6 9 4 5 4 2 2 4;}
    /* 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;}
    span.EmailStyle17
    {mso-style-type:personal-compose;
    font-family:"Calibri",sans-serif;
    color:windowtext;}
    .MsoChpDefault
    {mso-style-type:export-only;
    font-family:"Calibri",sans-serif;
    mso-fareast-language:EN-US;}
    @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">Hi All,<o:p></o:p></p>
    <p class="MsoNormal"><o:p>&nbsp;</o:p></p>
    <p class="MsoNormal">Can someone explain this to me&#8230;<o:p></o:p></p>
    <p class="MsoNormal"><o:p>&nbsp;</o:p></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">declare global temporary table session.test(<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">&nbsp;&nbsp;&nbsp; tom integer1 not null not default,<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">&nbsp;&nbsp;&nbsp; dick integer4 not null with default -666,<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">&nbsp;&nbsp;&nbsp; harry char(10) not null with default 'asdfg'<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">) on commit preserve rows with norecovery;<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;"><o:p>&nbsp;</o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">insert into session.test(tom) values (1), (2), (3);<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;"><o:p>&nbsp;</o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">select * from session.test;<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">Executing . . .<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;"><o:p>&nbsp;</o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">E_PS0476 DECLARE GLOBAL TEMPORARY TABLE: User-defined defaults are not<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">&nbsp;&nbsp;&nbsp; allowed in temporary tables, CREATE PROCEDURE, or REGISTER TABLE.<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">&nbsp;&nbsp;&nbsp; Try WITH DEFAULT instead.<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">&nbsp;&nbsp;&nbsp; (Thu Feb&nbsp; 4 08:56:57 2021)<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;"><o:p>&nbsp;</o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;;background:yellow;mso-highlight:yellow">Okay, that seems a little restrictive to me, but there is a workaround&#8230;</span><span style="
    font-size:10.0pt;font-family:&quot;Lucida Console&quot;"><o:p></o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;"><o:p>&nbsp;</o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">create table test(<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">&nbsp;&nbsp;&nbsp; tom integer1 not null not default,<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">&nbsp;&nbsp;&nbsp; dick integer4 not null with default -666,<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">&nbsp;&nbsp;&nbsp; harry char(10) not null with default 'asdfg'<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">);<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;"><o:p>&nbsp;</o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">declare global temporary table session.test<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">as select * from test where 1 = 0<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">on commit preserve rows with norecovery;<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;"><o:p>&nbsp;</o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">insert into session.test(tom) values (1), (2), (3);<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;"><o:p>&nbsp;</o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">select * from session.test;<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">Executing . . .<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;"><o:p>&nbsp;</o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">(0 rows)<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">(3 rows)<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;"><o:p>&nbsp;</o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">‚€€€€€€ˆ€€€€€€€€€€€€€ˆ€€€€€€€€€€ƒ<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">tom&nbsp;&nbsp; dick&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; harry&nbsp;&nbsp;&nbsp;&nbsp; <o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">†€€€€€€Š€€€€€€€€€€€€€Š€€€€€€€€€€‡<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -666asdfg&nbsp;&nbsp;&nbsp;&nbsp; <o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -666asdfg&nbsp;&nbsp;&nbsp;&nbsp; <o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -666asdfg&nbsp;&nbsp;&nbsp;&nbsp; <o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">„€€€€€€‰€€€€€€€€€€€€€‰€€€€€€€€€€…<o:p></o:p></span></p>
    <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Lucida Console&quot;">(3 rows)<o:p></o:p></span></p>
    <p class="MsoNormal"><o:p>&nbsp;</o:p></p>
    <p class="MsoNormal"><span style="background:yellow;mso-highlight:yellow">So clearly we have a session temp table which respects my user defaults&#8230;we just can&#8217;t create it directly.</span><o:p></o:p></p>
    <p class="MsoNormal"><o:p>&nbsp;</o:p></p>
    <p class="MsoNormal">That seems weird.<o:p></o:p></p>
    <p class="MsoNormal"><o:p>&nbsp;</o:p></p>
    <p class="MsoNormal">Marty<o:p></o:p></p>
    </div>
    </body>
    </html>

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Martin Bowes@21:1/5 to All on Thu Feb 4 09:49:23 2021
    I am struggling to decide whether it is right that _any_ table created AS SELECT gets the defaults. (Then again, I have never been at ease with the whole idea of default values.)

    I will hate myself when I exploit that trick.


    I love the defaults coming over in create as select. Plus I got comfortable hating myself years ago.

    Marty

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Roy Hann@21:1/5 to Martin Bowes on Thu Feb 4 09:40:32 2021
    Martin Bowes wrote:

    [snip]

    So clearly we have a session temp table which respects my user
    defaults...we just can't create it directly.

    That seems weird.

    I am struggling to decide whether it is right that _any_ table created
    AS SELECT gets the defaults. (Then again, I have never been at
    ease with the whole idea of default values.)

    I will hate myself when I exploit that trick.

    Roy

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Karl Schendel@21:1/5 to All on Thu Feb 4 09:04:53 2021
    On Feb 4, 2021, at 4:00 AM, Martin Bowes <martin.bowes@ndph.ox.ac.uk> wrote:

    So clearly we have a session temp table which respects my user defaults…we just can’t create it directly.

    That seems weird.

    It does seem weird. It might be so that declare global temporary table can be fully locking-free; if defaults were allowed then you need an insert into iidefault.
    The declare as select has the new GTT referring to an existing value in iidefault, so the issue doesn't arise.

    A bit of a lame excuse though.

    Karl

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Martin Bowes@21:1/5 to Karl Schendel on Thu Feb 4 14:17:52 2021
    To: info-ingres@lists.planetingres.org (info-ingres@lists.planetingres.org)

    SGkgS2FybCwNCg0KRG8geW91IG5lZWQgYW4gaW5zZXJ0IGludG8gaWlkZWZhdWx0cz8gSSBwcmVz dW1lIHRoZXJlIG5vIGluc2VydCBpbnRvIGlpcmVsYXRpb24sIGlpYXR0cmlidXRlIGZvciB0aGUg dGVtcHMgd2hlbiBjcmVhdGVkIHNvIEkgYXNzdW1lIHRoYXQgdGhlIHRlbXAgdGFibGUgZGV0YWls cyBhcmUgYSBwdXJlbHkgaW4gbWVtb3J5IHN0cnVjdHVyZS4NCg0KTWFydHkNCg0KLS0tLS1Pcmln aW5hbCBNZXNzYWdlLS0tLS0NCkZyb206IEthcmwgU2NoZW5kZWwgPHNjaGVuZGVsQGtiY29tcHV0 ZXIuY29tPiANClNlbnQ6IDA0IEZlYnJ1YXJ5IDIwMjEgMTQ6MDUNClRvOiBpbmZvLWluZ3Jlc0Bs aXN0cy5wbGFuZXRpbmdyZXMub3JnDQpTdWJqZWN0OiBSZTogW0luZm8taW5ncmVzXSBkZWZhdWx0 cyBpbiBzZXNzaW9uIHRhYmxlcw0KDQoNCj4gT24gRmViIDQsIDIwMjEsIGF0IDQ6MDAgQU0sIE1h cnRpbiBCb3dlcyA8bWFydGluLmJvd2VzQG5kcGgub3guYWMudWs+IHdyb3RlOg0KPiAgDQo+IFNv IGNsZWFybHkgd2UgaGF2ZSBhIHNlc3Npb24gdGVtcCB0YWJsZSB3aGljaCByZXNwZWN0cyBteSB1 c2VyIGRlZmF1bHRz4oCmd2UganVzdCBjYW7igJl0IGNyZWF0ZSBpdCBkaXJlY3RseS4NCj4gIA0K PiBUaGF0IHNlZW1zIHdlaXJkLg0KDQpJdCBkb2VzIHNlZW0gd2VpcmQuICBJdCBtaWdodCBiZSBz byB0aGF0IGRlY2xhcmUgZ2xvYmFsIHRlbXBvcmFyeSB0YWJsZSBjYW4gYmUgZnVsbHkgbG9ja2lu Zy1mcmVlOyBpZiBkZWZhdWx0cyB3ZXJlIGFsbG93ZWQgdGhlbiB5b3UgbmVlZCBhbiBpbnNlcnQg aW50byBpaWRlZmF1bHQuDQpUaGUgZGVjbGFyZSBhcyBzZWxlY3QgaGFzIHRoZSBuZXcgR1RUIHJl ZmVycmluZyB0byBhbiBleGlzdGluZyB2YWx1ZSBpbiBpaWRlZmF1bHQsIHNvIHRoZSBpc3N1ZSBk b2Vzbid0IGFyaXNlLg0KDQpBIGJpdCBvZiBhIGxhbWUgZXhjdXNlIHRob3VnaC4NCg0KS2FybA0K DQpfX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fXw0KSW5mby1p bmdyZXMgbWFpbGluZyBsaXN0DQpJbmZvLWluZ3Jlc0BsaXN0cy5wbGFuZXRpbmdyZXMub3JnDQpo dHRwczovL2xpc3RzLnBsYW5ldGluZ3Jlcy5vcmcvbWFpbG1hbi9saXN0aW5mby9pbmZvLWluZ3Jl cw0K

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Karl Schendel@21:1/5 to All on Thu Feb 4 09:27:14 2021
    iidefault is not table specific; it only contains defid1, defid2, defvalue. Once you
    get an entry into iidefault (via the regular create table), the session table declared as select merely has to reference the existing iidefault.

    If you drop a table, the iidefault rows stay, and in fact I'm not sure if there is
    any mechanism at all to clean up unreferenced iidefault rows. (verifydb, maybe.)
    I haven't verified this, but a series such as create x(with default); declare y as select from x; drop x; insert into y should work and should allow defaulting.

    Karl


    On Feb 4, 2021, at 9:17 AM, Martin Bowes <martin.bowes@ndph.ox.ac.uk> wrote:

    Hi Karl,

    Do you need an insert into iidefaults? I presume there no insert into iirelation, iiattribute for the temps when created so I assume that the temp table details are a purely in memory structure.

    Marty

    -----Original Message-----
    From: Karl Schendel <schendel@kbcomputer.com>
    Sent: 04 February 2021 14:05
    To: info-ingres@lists.planetingres.org
    Subject: Re: [Info-ingres] defaults in session tables


    On Feb 4, 2021, at 4:00 AM, Martin Bowes <martin.bowes@ndph.ox.ac.uk> wrote: >>
    So clearly we have a session temp table which respects my user defaults…we just can’t create it directly.

    That seems weird.

    It does seem weird. It might be so that declare global temporary table can be fully locking-free; if defaults were allowed then you need an insert into iidefault.
    The declare as select has the new GTT referring to an existing value in iidefault, so the issue doesn't arise.

    A bit of a lame excuse though.

    Karl

    _______________________________________________
    Info-ingres mailing list
    Info-ingres@lists.planetingres.org https://lists.planetingres.org/mailman/listinfo/info-ingres

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)