[Info-ingres] Ingres 3.0.2, alter table... alter column..., strange results

DATABASE

    Sponsored Links

    Next

  • 1. Error handling in DBPs
    We all know what the manual says about errors in database procedures: "If the procedure was invoked by a rule, an error has the following effects: - The procedure is terminated. - Those statements in the procedure which have been executed are rolled back. - The statement that fired the rule is rolled back. "If the procedure was executed directly, an error has the following effects: - All statements in the procedure up to the point of the error are rolled back. - The procedure continues execution with the statement following the statement that caused the error." In reality it seems that *directly executed* procedures don't always work that way. Some errors (such as divide by zero) terminate the procedure and control returns immediately to the calling program. Others (such as selecting a null into a non-nullable variable) work as the manual describes. Being too lazy to read the code myself, does anyone know what (if any) guiding principle decides when a directly executed DBP keeps control after an error? Roy
  • 2. [Info-ingres] SPAM: PEACE TO YOU
    This is a multi-part message in MIME format Dear friend I am joel Titoh, working with Diplomatic securities and finance company in europe,i have a deal which i want to talk with you. Our company have assisted some of the african leaders and politicians to move some fund and valuableS through our special arrangments as a consignment.Some of these consignments where not claimed as a result of deaths and some government restriction, therefore sent to suspense whare house. Presently we have some unclaimed ones ready for suspense whare house,but i have perfected arrangment to claim one the the consigment containing a lot of funds if i have a reiable person to assist me. if you are interested in the matter, you let know through this email adress for more datails and how to proceed. Sincerely Joel Titoh
  • 3. Ingres tools
    With the release of Ingres R3, is there any more happening on the 3rd party tools front for Ingres ? Is there any demand for such tools ? Several years ago I wrote a pretty-printer/formatter for Ingres SQL, which incorporated basic support for reporting on code coverage on Ingres database procedures, at the level of statement, branch and loop coverage. As I've recently moved jobs, and have more free time, I've picked up the development of this utility again, and am moving it in the direction of Ingres R3. Is there any demand out there for such a tool/utility ? I'm interested in discussing this on a commercial basis or non-commercial basis with any individuals or organisations who think they may have a use for such a utility. I'm based in the UK, Regards, Mark.

Re: [Info-ingres] Ingres 3.0.2, alter table... alter column..., strange results

Postby Betty & Karl Schendel » Wed, 07 Sep 2005 23:10:19 GMT



I don't think you can alter the default of a column with ALTER COLUMN.
(What do you do with the column values for all the rows that
existed before you added the column?  Those rows all have defaults
for the new column.  Ingres doesn't want to go back and rewrite all
the rows with the new default value, and it doesn't know which rows
to rewrite anyway.)

The parser probably should have rejected the statement with an error.

Karl

RE: [Info-ingres] Ingres 3.0.2, alter table... alter column..., strange results

Postby Paul White » Wed, 07 Sep 2005 23:39:21 GMT

> (What do you do with the column values for all the rows that

I'd like to think I can set a default value to apply only to records inserted from this point on.  If I wanted to modify old records which are already in the table I would do an explicit update. I'd be pretty cranky if the dbms changed my null values to some other value.

Paul
ps. MSSQL doesnt support it either. 


Re: [Info-ingres] Ingres 3.0.2, alter table... alter column..., strange results

Postby Gerhard Hofmann » Thu, 08 Sep 2005 01:11:17 GMT



My assumption was the same: a default value should only affect new rows 
but not existing ones.

Regards
Gerhard

Similar Threads:

1.[Info-ingres] Ingres 3.0.2, alter table... alter column..., strange results

At 6:11 PM +0200 9/6/05, Gerhard Hofmann wrote:
>Paul White wrote:
>>>(What do you do with the column values for all the rows that
>>>existed before you added the column?  Those rows all have defaults
>>>for the new column.  Ingres doesn't want to go back and rewrite all
>>>the rows with the new default value, and it doesn't know which rows
>>>to rewrite anyway.)
>>
>>
>>I'd like to think I can set a default value to apply only to 
>>records inserted from this point on.  If I wanted to modify old 
>>records which are already in the table I would do an explicit 
>>update. I'd be pretty cranky if the dbms changed my null values to 
>>some other value.
>>
>>Paul
>>ps. MSSQL doesnt support it either.
>
>My assumption was the same: a default value should only affect new 
>rows but not existing ones.

While I do have a certain amount of sympathy for this position, I don't
think it's wise to rush into it.  The meaning of a relation shouldn't be
time sensitive.  To say that some rows have an x column default of V1,
while others have a default for the same column of V2 just because they
happened to be inserted later, rubs me the wrong way.  And it still leaves
relatively unanswered the question of just what values does column X
have in pre-existing rows, anyway, if X was added or altered after table
creation?  The DBMS is not (currently) in a position to instantiate
anything other than zero-blank-or-null for a column when retrieving a
row that doesn't currently contain that column (because it's an old row).

I wouldn't be fanatically against allowing value defaults for add column
or alter column, but I'd want to see some sort of coherent statement of
semantics that preserves the meaning of pre-existing rows.

Karl

2.[Info-Ingres] alter table alter column weirdness

Hi Everyone,

I'm running II 9.0.4 (a64.lnx/105)NPTL + p12125

I have two databases, one being a test version of the other. These are 
copies of each other and both exist on the same Ingres installation.

In one table within each database I wish to change a column from not 
null to nullable. The column is not part of any key on the table, there are 
no secondary indexes on the table, there are no constraints on the 
table.

So I did:
alter table odin_postal_reply alter column dob_str varchar(20) with 
null not default;

This worked without a problem on the test version of the database. But 
fails on the production version with error:
E_US0F13 ALTER TABLE: invalid change of attributes on an ALTER 
COLUMN

I have confirmed the table has 4k page size in both, it is a journaled 
table in both databases.

Anyone got any ideas what I've done wrong?

Martin Bowes
--
Random Farscape Quote #32:
John   - I don't believe I've left a Nuclear bomb on an elevator.
Chiana - You've done worse.



<?xml  version="1.0" ?><html>
<head>
<title></title>
</head>
<body>
<div align="left"><font face="Arial"><span style="font-size:10pt">Hi Everyone,</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">I'm running II 9.0.4 (a64.lnx/105)NPTL + p12125</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">I have two databases, one being a test version of the other. These are 
copies of each other and both exist on the same Ingres installation.</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">In one table within each database I wish to change a column from not 
null to nullable. The column is not part of any key on the table, there are 
no secondary indexes on the table, there are no constraints on the 
table.</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">So I did:</span></font></div>
<div align="left"><font face="Arial" color="#7f0000"><span style="font-size:10pt"><b>alter table odin_postal_reply alter column dob_str varchar(20) with 
null not default;</b></span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">This worked without a problem on the test version of the database. But 
fails on the production version with error:</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">E_US0F13 ALTER TABLE: invalid change of attributes on an ALTER 
COLUMN</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">I have confirmed the table has 4k page size in both, it is a journaled 
table in both databases.</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">Anyone got any ideas what I've done wrong?</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">Martin Bowes</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">--</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">Random Farscape Quote #32:</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">John   - I don't believe I've left a Nuclear bomb on an elevator.</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">Chiana - You've done worse.</span></font></div>
<div align="left"><br/>
</div>
<div align="left"></div>
</body>
</html>

3.[Info-Ingres] Error while adding column using ALTER TABLE

4.Ingres 3.0.2, alter table... alter column..., strange results

Hi all,

I installed Ingres 3.0.2 on Win XP Pro.

I created a table like this:
create table mytab
(col1 varchar(10) not null default 'WERT',
col2 integer not null default 3);
commit;

Added a new column col3:
alter table mytab add column
col3 varchar(10) not null with default;
commit;

Changed default setting of col3:
alter table mytab alter column
col3 varchar(10) default 'WERT2';
commit;


Ran "help table" to see the result:
Column Information:
                                                                   Key
Column Name                      Type       Length Nulls Defaults Seq
col1                             varchar        10   no   value
col2                             integer         4   no   value
col3                             varchar        10  yes    null


I would have expected something different for col3 because I had 
assigned default value "WERT2".


Tried it slightly different (not nullable + explicit default setting):
alter table mytab alter column
col3 varchar(10) not null default 'WERT2';
commit;


Ran "help table" again:
Column Information:
                                                                   Key
Column Name                      Type       Length Nulls Defaults Seq
col1                             varchar        10   no   value
col2                             integer         4   no   value
col3                             varchar        10   no      no


Nullability setting seems correct, but where is the default setting for 
col3?

TIA
Gerhard





5.alter table alter column weirdness

On Jul 20, 12:16 am,  XXXX@XXXXX.COM  wrote:
> create table x (a varchar(20) not null not default, b integer not null not
> default) with page_size=4096\g
> alter table x alter column a varchar(20) with null not default\g

Isn't that an erroneous statement?  Not sure if not default and with
null have ever been able to co-exist.  If R3 accepted it, that would
be a bug in R3.

6. alter table alter columns vs. domains

7. [Info-ingres] altering temp tables

8. [Info-Ingres] Alter table question



Return to DATABASE

 

Who is online

Users browsing this forum: No registered users and 84 guest