Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-948

Columnstore engine(1.0.11): Transaction is NOT integrated with TRIGGER in an INSERT statement !

    XMLWordPrintable

Details

    Description

      Current culumnstore can't support constraints FOREIGN KEY,UNIQUE,PRIMARY KEY, but these constraints is very important for avoid logic error from applications, I try use TRIGGER to avoid the limitations, but I found NOT an integrated transaction in an INSERT statement:
      CREATE TABLE head
      (id int PRIMARY KEY,
      sVal varchar(50)
      ) ENGINE=INNODB;
      INSERT INTO head(id,sVal) values(1,'Val1'),(2,'Val3'),(3,'Val3');

      CREATE TABLE detail_inno
      (
      id int AUTO_INCREMENT PRIMARY KEY,
      fk int NOT NULL,
      sData varchar(30)
      )ENGINE=INNODB;
      CREATE TABLE detail_columnstore
      (
      id int,
      fk int NOT NULL,
      sData varchar(30)
      )ENGINE=COLUMNSTORE COMMENT = 'autoincrement=id';;

      DELIMITER //
      CREATE TRIGGER fktest_inno BEFORE INSERT ON detail_inno FOR EACH ROW
      BEGIN
      IF NOT EXISTS(SELECT * FROM head WHERE id=NEW.fk) THEN
      SIGNAL sqlstate '45001' set message_text = "Force Key fk NOT exists in table head ! ";
      END IF;
      END//
      CREATE TRIGGER fktest_columnstore BEFORE INSERT ON detail_columnstore FOR EACH ROW
      BEGIN
      IF NOT EXISTS(SELECT * FROM head WHERE id=NEW.fk) THEN
      SIGNAL sqlstate '45001' set message_text = "Force Key fk NOT exists in table head ! ";
      END IF;
      END//
      DELIMITER ;

      INSERT INTO detail_inno(fk,sData) VALUES(1,'data1'),(2,'data2'),(4,'data4');--GOOD: NOT any records inserted at here.
      INSERT INTO detail_columnstore(fk,sData) VALUES(1,'data1'),(2,'data2'),(4,'data4');-- BAD: id 1,2 is inserted at here, id 4 not inserted.

      Attachments

        Activity

          People

            toddstoffel Todd Stoffel (Inactive)
            gavin3180 Gavin Chen
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.