Column RENAME not present in Virtuoso SQL?

Virtuoso Universal Server
1

Hi, I was looking in Openlink Virtuoso documentation but I can’t find the RENAME COLUMN statement, so I can’t do the rename of a column, is correct ?

Regards.

2

Direct rename of columns is not supported.

Although it can be done indirectly by adding a new column of the required name and datatype, update the new column with the values of the original column name with update query, then drop the original column name ie

create table t1 (id int)
insert into t1 values (1);
insert into t1 values (2);
alter table t1 add id_new int;
log_enable(3); — if table is large
update t1 set id_new = id;
alter table t1 drop id;
3

Hi @hwilliams,

sorry but for big table I have problems:

SQLState: 40005
Message: SR325: Transaction aborted because it’s log after image size went above the 50000000 bytes limit

Also I have strange behaviors, you can find the example below:
drop table t1;
create table t1 (id int);
insert into t1 values (1);
insert into t1 values (2);
select * from t1;
alter table t1 add id_new int;
log_enable(3);
update t1 set id_new = id;
alter table t1 drop id;
select * from t1;

OUTPUT:

image
image693×627 18.5 KB

4

You should run these commands with the Virtuoso isql command line tool, which runs in auto-commit mode by default, rather than via the conductor interactive SQL UI.

For the Transaction aborted because it’s log after image size went above the 50000000 bytes limit error you should increase the TransactionAfterImageLimit INI file param as detailed in the documentation.

5

Yes, I try to but table is very large, there is a max TransactionAfterImageLimit (I try 88888888) to use that don’t breaks Virtuoso… ?

6

Not sure what is the problem or issue, as you seem to be implying the max TransactionAfterImageLimit is 88888888 which is not the case as this param is only limited by the available memory and I have seen it set as high as 1500000000 ?

Are you encountering a specific error when increasing the TransactionAfterImageLimit size ?

You can also try setting log_enable(2) when updating the table, which turns off transaction logging, although ensure a backup of the database or table is in place first.

7

Hi @hwilliams,

I think this process is quite expensive for big tables, I think you should implement the rename function on the Virtuoso tables.

Regards.