Column having Auto_Increment() is getting updated!!!
Moderator: NorbertKrupa
-
- Intermediate
- Posts: 87
- Joined: Wed Apr 25, 2012 8:10 am
Column having Auto_Increment() is getting updated!!!
Hi,
I was surprised to see that the Column in a table containing Auto_Increment() property is able to get updated.
But as per my knowledge , it should not get updated even if we try to do so right??.
Please help me, why it is happening so..
Thank you,
Raj
I was surprised to see that the Column in a table containing Auto_Increment() property is able to get updated.
But as per my knowledge , it should not get updated even if we try to do so right??.
Please help me, why it is happening so..
Thank you,
Raj
Rajasekhar.T|HP ATP Vertica Big Data Solutions V1
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Column having Auto_Increment() is getting updated!!!
Hmm... This may or may not be a bug considering what the Vertica SQL Reference Guide (http://my.vertica.com/docs/5.1.5/PDF/SQ ... Manual.pdf) says on page 709 about the AUTO_INCREMENT column-constraint:
However, now that I read the document section again, maybe by "You cannot change the value of an AUTO_INCREMENT column" they mean that we cannot modify the increment value because it is always 1, or more than likely, that we cannot change the value that will be inserted next. It will always be the last inserted value + 1. In my example above I couldn't control that the next value was going to be 2 even though there was already a 2 in the table.
What do you guys think?
Although we can clearly update an AUTO_INCREMENT column:Creates a table column whose values are automatically generated by the database. The initial value of an AUTO_INCREMENT column is always 1. You cannot specify a different initial value.
Each time you add a row to the table, Vertica increments the column value by 1. You cannot change the value of an AUTO_INCREMENT column.
Code: Select all
dbadmin=> create table test (col1 auto_increment, col2 int);
CREATE TABLE
dbadmin=> insert into test (col2) values (1);
OUTPUT
--------
1
(1 row)
dbadmin=> select * from test;
col1 | col2
------+------
1 | 1
(1 row)
dbadmin=> update test set col1 = 2;
OUTPUT
--------
1
(1 row)
dbadmin=> select * from test;
col1 | col2
------+------
2 | 1
(1 row)
dbadmin=> insert into test (col2) values (2);
OUTPUT
--------
1
(1 row)
dbadmin=> select * from test;
col1 | col2
------+------
2 | 1
2 | 2
(2 rows)
What do you guys think?
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Column having Auto_Increment() is getting updated!!!
Hmm... it depends on session and cache size (default 250K).It will always be the last inserted value + 1
Let's show it:
Code: Select all
CREATE TABLE public.EnumExample
(
id IDENTITY ,
col char(1)
);
Code: Select all
test_db=> copy EnumExample from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> a
>> s
>> d
>> f
>> g
>> \.
Code: Select all
test_db=> select * from EnumExample;
id | col
----+-----
1 | a
2 | s
3 | d
4 | f
5 | g
(5 rows)
Code: Select all
test_db=> select close_session(current_session());
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
Code: Select all
test_db=> copy EnumExample from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> q
>> w
>> e
>> r
>> \.
Code: Select all
test_db=> select * from EnumExample;
id | col
--------+-----
1 | a
2 | s
3 | d
4 | f
5 | g
250001 | q
250002 | w
250003 | e
250004 | r
(9 rows)
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Column having Auto_Increment() is getting updated!!!
Good point about the separate sessions! It's pretty cool the way Vertica handles this...
In one session I did this:
In a separate session I did this:
Then going back to session 1, I inserted more than 250,000 rows to see how Vertica would handle the potential overlap at 250001:
It handled it!
In one session I did this:
Code: Select all
dbadmin=> create table test (col1 auto_increment, col2 varchar);
CREATE TABLE
dbadmin=> insert into test (col2) values ('Created by Session 1');
OUTPUT
--------
1
(1 row)
dbadmin=> commit;
COMMIT
dbadmin=> select * from test;
col1 | col2
------+----------------------
1 | Created by Session 1
(1 row)
Code: Select all
dbadmin=> insert into test (col2) values ('Created by Session 2');
OUTPUT
--------
1
(1 row)
dbadmin=> commit;
COMMIT
dbadmin=> select * from test;
col1 | col2
--------+----------------------
1 | Created by Session 1
250001 | Created by Session 2
(2 rows)
Code: Select all
dbadmin=> insert into test (col2) select 'Created by Session 1' from tables cross join columns;
OUTPUT
--------
793520
(1 row)
dbadmin=> commit;
COMMIT
dbadmin=> select * from test where col1 between 249995 and 500005 order by 1;
col1 | col2
--------+----------------------
249995 | Created by Session 1
249996 | Created by Session 1
249997 | Created by Session 1
249998 | Created by Session 1
249999 | Created by Session 1
250000 | Created by Session 1
250001 | Created by Session 2
500001 | Created by Session 1
500002 | Created by Session 1
500003 | Created by Session 1
500004 | Created by Session 1
500005 | Created by Session 1
(12 rows)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Column having Auto_Increment() is getting updated!!!
Hi,
This is an Interesting thread. I can find in the docs how to change the default cache for a sequence, but how do we change it for an auto_increment column?
This is an Interesting thread. I can find in the docs how to change the default cache for a sequence, but how do we change it for an auto_increment column?
Thanks,
Juliette
Juliette
Re: Column having Auto_Increment() is getting updated!!!
it's AUTO increment , otherwise use in sequences or IDENTITY(cache, start, increment ).but how do we change it for an auto_increment column?
Example for increment "last id +1" only (i.e. we need no caching):
Code: Select all
CREATE TABLE Enum
(
id IDENTITY ( 1 ) , --- identical to IDENTITY(1,1,1)
c char(1)
);
Code: Select all
test_db=> copy Enum from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> a
>> s
>> d
>> \.
Code: Select all
test_db=> select close_session(current_session());
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
Code: Select all
test_db=> copy Enum from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> q
>> w
>> e
>> \.
Code: Select all
test_db=> select close_session(current_session());
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
Code: Select all
test_db=> select * from enum;
id | c
----+---
1 | a
2 | s
3 | d
4 | q
5 | w
6 | e
7 | p
8 | o
9 | i
(9 rows)
-
- Intermediate
- Posts: 87
- Joined: Wed Apr 25, 2012 8:10 am
Re: Column having Auto_Increment() is getting updated!!!
Wow..! Good Observation man..
Thanks
Raj
Thanks
Raj
Rajasekhar.T|HP ATP Vertica Big Data Solutions V1