Column having Auto_Increment() is getting updated!!!

[rajasekhart]

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

[JimKnicely]

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:

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.

Although we can clearly update 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)

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?

[id10t]

It will always be the last inserted value + 1

Hmm... it depends on session and cache size (default 250K).
Let's show it:

Code: Select all

CREATE TABLE public.EnumExample
(
    id  IDENTITY ,
    col char(1)
);

Insert data:

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)

Close current session and get a new one:

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.

Add data :

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
>> \.

Show data:

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)

PS Vertica is not OLTP, its a DWH so no data deleted (epochs updated and on UPDATE record Vertica does {mart row as deleted, insert} ) that why identity field can be "updated"

[JimKnicely]

Good point about the separate sessions! It's pretty cool the way Vertica handles this...

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)

In a separate session I did this:

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)

Then going back to session 1, I inserted more than 250,000 rows to see how Vertica would handle the potential overlap at 250001:

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)

It handled it!

[Julie]

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?

[id10t]

but how do we change it for an auto_increment column?

it's AUTO increment , otherwise use in sequences or IDENTITY(cache, start, increment ).

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)
);

Data:

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
>> \.

Close session:

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.

Insert more data:

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
>> \.

Close session one more time:

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.

And... moment of truth:

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)

Voua la --- no GAPS!!!

[rajasekhart]

Wow..! Good Observation man..

Thanks
Raj