MC and VSQL shows different query results

kfruchtman

Hi Experts!
Weird thing...I have a table with 3 rows.when I tried to insert into the table the rows through MC I got :Cannot insert into or update IDENTITY/AUTO_INCREMENT column "id". So I followed this ;
https://forum.vertica.com/discussion/221757/insert-statement-for-auto-increment-column
and I went to vsql and typed: SELECT set_optimizer_directives('AllowIdentityInsert=True');
after that I was able to insert to my table the 3 rows.
when I query vsql with select * from mytable I get all 3 rows but when I am in the MC and querying the same table I get 0 rows.I tried to refresh the MC but I have no idea why it shows me different query result.
any ideas?

marcothesane

Did you commit your data?
Data is only visible in other sessions if the session inserting the new data COMMIT-s the operation.
JDBC and ODBC connections usually have auto-commit enabled. vsql, by default, has not, and requires you to COMMIT your changes explicitly.
Here' a scenario with two sessions I just played for you; Session 1 inserts some data into table foo ; Session 2 won't see that data before Session 1 COMMIT-s , while they are immediately visible from within Session 1.
VSQL session 1:

sbx=> select * from foo;
 id | first_name | last_name | hire_dt
----+------------+-----------+---------
(0 rows)
sbx=> INSERT INTO foo(id,first_name,last_name,hire_dt)
sbx->           SELECT 1,'Arthur','Dent',DATE '2017-02-05'
sbx-> UNION ALL SELECT 2,'Ford','Prefect',DATE '2017-02-05'
sbx-> UNION ALL SELECT 3,'Zaphod','Beeblebrox',DATE '2017-02-05'
sbx-> UNION ALL SELECT 4,'Tricia','McMillan',DATE '2017-02-05'
sbx-> UNION ALL SELECT 5,'Gag','Halfrunt',DATE '2017-02-05'
sbx-> UNION ALL SELECT 6,'Prostetnic Vogon','Jeltz',DATE '2017-02-05'
sbx-> UNION ALL SELECT 7,'Lionel','Prosser',DATE '2017-02-05'
sbx-> UNION ALL SELECT 8,'Benji','Mouse',DATE '2017-02-05'
sbx-> UNION ALL SELECT 9,'Frankie','Mouse',DATE '2017-02-05'
sbx-> UNION ALL SELECT 10,'Gengis Temjin','Khan',DATE '2016-03-04'
sbx-> ;
 OUTPUT
--------
     10
(1 row)

sbx=> SELECT * FROM foo;
 id |    first_name    | last_name  |  hire_dt
----+------------------+------------+------------
  1 | Arthur           | Dent       | 2017-02-05
  2 | Ford             | Prefect    | 2017-02-05
  3 | Zaphod           | Beeblebrox | 2017-02-05
  4 | Tricia           | McMillan   | 2017-02-05
  5 | Gag              | Halfrunt   | 2017-02-05
  6 | Prostetnic Vogon | Jeltz      | 2017-02-05
  7 | Lionel           | Prosser    | 2017-02-05
  8 | Benji            | Mouse      | 2017-02-05
  9 | Frankie          | Mouse      | 2017-02-05
 10 | Gengis Temjin    | Khan       | 2016-03-04
(10 rows)

Session 2:

sbx=> SELECT * FROM foo;
 id | first_name | last_name | hire_dt
----+------------+-----------+---------
(0 rows)

Session 1:

sbx=> COMMIT;
COMMIT

Session 2:

sbx=> SELECT * FROM foo;
 id |    first_name    | last_name  |  hire_dt
----+------------------+------------+------------
  1 | Arthur           | Dent       | 2017-02-05
  2 | Ford             | Prefect    | 2017-02-05
  3 | Zaphod           | Beeblebrox | 2017-02-05
  4 | Tricia           | McMillan   | 2017-02-05
  5 | Gag              | Halfrunt   | 2017-02-05
  6 | Prostetnic Vogon | Jeltz      | 2017-02-05
  7 | Lionel           | Prosser    | 2017-02-05
  8 | Benji            | Mouse      | 2017-02-05
  9 | Frankie          | Mouse      | 2017-02-05
 10 | Gengis Temjin    | Khan       | 2016-03-04
(10 rows)

kfruchtman

And I just want to add it is for sure not a privs issue since my MC user is a dbadmin and I do all vsql commands as dbadmin..

kfruchtman

No I didnt and that was actually the problem! I didn't do any INSERT commands until now and all other commands like truncate,drop and others didn't require commit so I assumed INSERT was the same.
Thanks so much! that solved my problem:-)

marcothesane

TRUNCATE, is a DDL command, like CREATE , ALTER, DROP. DDL commands implicitly commit the transaction. DML, like INSERT, UPDATE, DELETE, and MERGE, are commands that can be rolled back. So, like in any other DBMS, they are only permanent, and thus visible to the rest of the users and sessions, when they are COMMIT-ted.

kfruchtman

Thanks so much for the detailed explanation.helped me a lot!
all the best!
Keren