spi extension

The spi module provides several workable examples of using the Server Programming Interface (SPI) and triggers.

YugabyteDB supports the following four (of five — timetravel is not currently supported) extensions provided in the spi module:

  • autoinc functions auto-increment fields.
  • insert_username functions track who changed a table.
  • moddatetime functions track last modification times.
  • refint functions implement referential integrity.

Example

  1. Connect using ysqlsh and run the following commands:

    CREATE EXTENSION insert_username;
CREATE EXTENSION moddatetime;

  2. Set up a table with triggers for tracking modification time and user (role):

    CREATE TABLE spi_test (
  id int primary key,
  content text,
  username text not null,
  moddate timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TRIGGER insert_usernames
  BEFORE INSERT OR UPDATE ON spi_test
  FOR EACH ROW
  EXECUTE PROCEDURE insert_username (username);

CREATE TRIGGER update_moddatetime
  BEFORE UPDATE ON spi_test
  FOR EACH ROW
  EXECUTE PROCEDURE moddatetime (moddate);

  3. Insert some rows. Each insert should add the current role as username and the current timestamp as moddate.

    SET ROLE yugabyte;
INSERT INTO spi_test VALUES(1, 'desc1');

SET ROLE postgres;
INSERT INTO spi_test VALUES(2, 'desc2');
INSERT INTO spi_test VALUES(3, 'desc3');

SET ROLE yugabyte;
INSERT INTO spi_test VALUES(4, 'desc4');

SELECT * FROM spi_test ORDER BY id;

     id | content | username |          moddate
----+---------+----------+----------------------------
  1 | desc1   | yugabyte | 2019-09-13 16:55:53.969907
  2 | desc2   | postgres | 2019-09-13 16:55:53.983306
  3 | desc3   | postgres | 2019-09-13 16:55:53.98658
  4 | desc4   | yugabyte | 2019-09-13 16:55:53.991315
(4 rows)

    The yugabyte and (for compatibility) postgres YSQL users are created by default.

  4. Update some rows. This should update both username and moddate accordingly.

    UPDATE spi_test SET content = 'desc1_updated' WHERE id = 1;
UPDATE spi_test SET content = 'desc3_updated' WHERE id = 3;

SELECT * FROM spi_test ORDER BY id;

    id |    content    | username |          moddate
----+---------------+----------+----------------------------
  1 | desc1_updated | yugabyte | 2019-09-13 16:56:27.623513
  2 | desc2         | postgres | 2019-09-13 16:55:53.983306
  3 | desc3_updated | yugabyte | 2019-09-13 16:56:27.634099
  4 | desc4         | yugabyte | 2019-09-13 16:55:53.991315
(4 rows)