CODEWITHSUNDEEP
cassandracassandra-2.0cql3cqlshnosql
greenhost87
greenhost87

Reputation: 67

Failed to update static column in cassandra

I have strange problem with Cassandra (version 2.2.3) database and using static columns when write some proof of concept for simple application with send money functionality.

My table is:

CREATE TABLE transactions (
profile text,
timestamp timestamp,
amount text,
balance text,
lock int static,
PRIMARY KEY (profile, timestamp)) WITH CLUSTERING ORDER BY (timestamp ASC);

First step I add new record

INSERT INTO transactions (profile, timestamp, amount) VALUES ( 'test_profile', '2015-11-05 15:20:01+0000', '10USD');

Then I want to 'lock' current user transaction to do some action with his balance. I try to execute this request:

UPDATE transactions SET lock = 1 WHERE profile = 'test_profile' IF lock = null;

But as result in cqlsh I see

     [applied]
-----------
     False

I don't understand why 'False', because current data for profile is:

 profile      | timestamp                | lock | amount | balance
--------------+--------------------------+------+--------+---------
 test_profile | 2015-11-05 15:20:01+0000 | null |  10USD |    null

Any idea what I do wrong?

UPDATE

After read Nenad Bozic answer I modify my example to clarify why I need condition in update. Full code sample 

CREATE TABLE transactions (
    profile text,
    timestamp timestamp,
    amount text,
    balance text,
    lock int static,
    balances map<text,text> static,
    PRIMARY KEY (profile, timestamp)
) WITH CLUSTERING ORDER BY (timestamp ASC);
INSERT INTO transactions (profile, timestamp, amount) VALUES ( 'test_profile', '2015-11-05 15:20:01+0000', '1USD');
INSERT INTO transactions (profile, lock) VALUES ('test_profile', 1) IF NOT EXISTS;
BEGIN BATCH
        UPDATE transactions SET balances={'USD':'1USD'} WHERE profile='test_profile';
        UPDATE transactions SET balance='1USD' WHERE profile='test_profile' AND timestamp='2015-11-05 15:20:01+0000';
        DELETE lock FROM transactions WHERE profile='test_profile';
APPLY BATCH;

And if I try get lock again I get

INSERT INTO transactions (profile, lock) VALUES ('test_profile', 1) IF NOT EXISTS;

 [applied] | profile      | timestamp | balances        | lock | amount | balance
-----------+--------------+-----------+-----------------+------+--------+---------
     False | test_profile |      null | {'USD': '1USD'} | null |   null |    null

Upvotes: 3

Views: 1505

Answers (1)

Nenad Bozic
Nenad Bozic

Reputation: 3784

When you INSERT you do not insert lock field which means this field does not exist. Null representation in CQLSH or DevCenter is only synthetic sugar to make results looks like tabular data but in reality it has dynamic key values and lock is not present in that map of key values. It is useful to look thrift representation of data even though it is not used anymore to get sense how it is stored to disk.

So when UPDATE is fired it is expecting column to be present to updated it. In your case lock column is not even present so it cannot update it. This thread on difference between INSERT and UPDATE is also good read.

You have two solutions to make this work:

Insert null explicitly

You can add lock to your insert statement and set it to null (which is different in Cassandra than excluding it from insert because this way it will get null value and when you exclude it this column would not exist in 

INSERT INTO transactions (profile, timestamp, amount, lock) 
VALUES ( 'test_profile', '2015-11-05 15:20:01+0000', '10USD', null);

Use insert on second statement

Since you are inserting on second statement lock for first time instead of updating existing value and since it is static column for that partition you can use INSERT IF NOT EXISTS instead of UPDATE IF LWT way of doing it (lock would not exist so this will pass first time and fail all other times since lock will have value):

INSERT INTO transactions (profile, lock) 
VALUES ('test_profile', 1) IF NOT EXISTS;

Upvotes: 2

Related Questions