Forum: >>> Magnum BBS <<<

[Info-ingres] convtohyb ... is it just me?

From Martin Bowes@21:1/5 to All on Wed May 19 08:12:32 2021

Hi All,

I have a test table:
create table test_conv(
id integer4 not null not default,
a integer4 not null with default,
b float4 not null with default,
tinsert ingresdate not null with default 'now',
tupdate ingresdate not null with default ''
) with nojournaling;

insert into test_conv(id, a, tinsert, tupdate) values
(1, 11, '1-jan-1970', '11-jan-1970 11:12:31'),
(2, 12, '2-dec-1985', '12-jan-1986 12:12:12'),
(3, 13, '3-feb-2020', '5-mar-2021 12:45:56');

modify test_conv to btree unique on id with unique_scope=statement;

create unique index test_conv_a on test_conv(a) with unique_scope=statement;

create index test_conv_b on test_conv(b);

And then run: convtohyb -to_ansi iidbdb test_conv

Now given the data I would expect my tinsert to become ansidate and tupdate to become timestamp(0) without time zone.

And examining the generated conversion script ctohout.sql that is indeed what I get...sort of:
drop index if exists test_conv_a
\p\g
drop index if exists test_conv_b
\p\g
alter table ingres.test_conv alter column tinsert ansidate not null default CURRENT_TIMESTAMP
\p\g
alter table ingres.test_conv alter column tupdate timestamp(0) without time zone not null default NULL
\p\g
modify test_conv to btree unique on
id
with nonleaffill = 80,
leaffill = 70,
fillfactor = 80,
extend = 16,
page_size = 8192,
unique_scope = statement
\p\g

/* INDEXES */
create index (test_conv_a on test_conv (
a) unique
with structure = isam,
page_size = 8192,
nocompression,
key = (a),
unique_scope = statement,
fillfactor = 80,
location = (ii_database)),
(test_conv_b on test_conv (
b)
with structure = isam,
page_size = 8192,
nocompression,
key = (b),
fillfactor = 80,
location = (ii_database))
\p\g

The problem being that the two alter statements don't work and the less said about that attempt to recreate indexes the better!

Martin Bowes

<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=us-ascii">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)"> <style>
</head>
<body lang="EN-GB" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
Hi All,<o:p></o:p>
<o:p> </o:p>
I have a test table:<o:p></o:p>
create table test_conv(<o:p></o:p>
    id integer4 not null not default,<o:p></o:p>
    a  integer4 not null with default,<o:p></o:p>
    b  float4   not null with default,<o:p></o:p>
    tinsert ingresdate not null with default 'now',<o:p></o:p>
    tupdate ingresdate not null with default ''<o:p></o:p>
) with nojournaling;<o:p></o:p>
<o:p> </o:p>
insert into test_conv(id, a, tinsert, tupdate) values<o:p></o:p>
    (1, 11, '1-jan-1970', '11-jan-1970 11:12:31'),<o:p></o:p>
    (2, 12, '2-dec-1985', '12-jan-1986 12:12:12'),<o:p></o:p>
    (3, 13, '3-feb-2020', '5-mar-2021 12:45:56');<o:p></o:p>
<o:p> </o:p>
modify test_conv to btree unique on id with unique_scope=statement;<o:p></o:p>
<o:p> </o:p>
create unique index test_conv_a on test_conv(a) with unique_scope=statement;<o:p></o:p>
<o:p> </o:p>
create index test_conv_b on test_conv(b);<o:p></o:p>
<o:p> </o:p>
And then run: convtohyb –to_ansi iidbdb test_conv<o:p></o:p>
<o:p> </o:p>
Now given the data I would expect my tinsert to become ansidate and tupdate to become timestamp(0) without time zone.<o:p></o:p>
<o:p> </o:p>
And examining the generated conversion script ctohout.sql that is indeed what I get…sort of:<o:p></o:p>
drop index if exists test_conv_a<o:p></o:p>
\p\g<o:p></o:p>
drop index if exists test_conv_b<o:p></o:p>
\p\g<o:p></o:p>
alter table ingres.test_conv alter column tinsert ansidate not null default CURRENT_TIMESTAMP<o:p></o:p>
\p\g<o:p></o:p>
alter table ingres.test_conv alter column tupdate timestamp(0) without time zone not null default NULL<o:p></o:p>
\p\g<o:p></o:p>
modify test_conv to btree unique on<o:p></o:p>
    id<o:p></o:p>
with nonleaffill = 80,<o:p></o:p>
    leaffill = 70,<o:p></o:p>
    fillfactor = 80,<o:p></o:p>
    extend = 16,<o:p></o:p>
    page_size = 8192,<o:p></o:p>
    unique_scope = statement<o:p></o:p>
\p\g<o:p></o:p>
<o:p> </o:p>
    /* INDEXES */<o:p></o:p>
create index (test_conv_a on test_conv (<o:p></o:p>
    a) unique<o:p></o:p>
with structure = isam,<o:p></o:p>
    page_size = 8192,<o:p></o:p>
    nocompression,<o:p></o:p>
    key = (a),<o:p></o:p>
    unique_scope = statement,<o:p></o:p>
    fillfactor = 80,<o:p></o:p>
    location = (ii_database)),<o:p></o:p>
(test_conv_b on test_conv (<o:p></o:p>
    b)<o:p></o:p>
with structure = isam,<o:p></o:p>
    page_size = 8192,<o:p></o:p>
    nocompression,<o:p></o:p>
    key = (b),<o:p></o:p>
    fillfactor = 80,<o:p></o:p>
    location = (ii_database))<o:p></o:p>
\p\g<o:p></o:p>
<o:p> </o:p>
The problem being that the two alter statements don’t work and the less said about that attempt to recreate indexes the better!<o:p></o:p>
<o:p> </o:p>
Martin Bowes<o:p></o:p>
<o:p> </o:p>
</div>
</body>
</html>

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

Who's Online
Recent Visitors
- Brianm
  Sun Sep 8 00:54:31 2024
  from Glasgow via Raw
- Keyop
  Sun Sep 8 00:16:02 2024
  from Huddersfield, West Yorkshire via SSH
- Keyop
  Sun Sep 8 00:15:46 2024
  from Huddersfield, West Yorkshire via SSH
- Bob Worm
  Sat Sep 7 22:43:20 2024
  from Wales, Uk via Telnet
- Bob Worm
  Sat Sep 7 21:40:55 2024
  from Wales, Uk via Telnet
- Bob Worm
  Sat Sep 7 20:53:17 2024
  from Wales, Uk via Telnet
- Zharous
  Sat Sep 7 20:26:45 2024
  from Tempe, Az via Telnet
- Keyop
  Sat Sep 7 03:23:27 2024
  from Huddersfield, West Yorkshire via SSH

System Info

Sysop:	Keyop
Location:	Huddersfield, West Yorkshire, UK
Users:	339
Nodes:	16 (0 / 16)
Uptime:	17:08:45
Calls:	7,467
Calls today:	3
Files:	12,694
Messages:	5,627,323

[Info-ingres] convtohyb ... is it just me?

Who's Online

Recent Visitors

System Info