YugaByte DB Engineering Update, June 27, 2019 — PostgreSQL Stored Procedure Support and More!

YugaByte DB Release Updates

We are pleased to announce that YugaByte DB 1.2.11 is live! You can read the release notes of this and previous versions here. This release is shipping with stored procedures support, plus 30 new features, enhancements and bug fixes. Here’s a few highlights from the 1.2.11 release.

[#1155] Support for FUNCTION and PROCEDURE

Stored procedures allow developers to “bundle up” operations that would otherwise take several queries and round trips into a single function. Stored procedures also help minimize duplicate code, as developers can reuse existing stored procedures to perform the same actions. What’s the difference between a FUNCTION and a PROCEDURE? A function cannot execute transactions. In other words, inside a function you cannot open a new transaction, even commit or rollback the current transaction. In PostgreSQL 11, stored procedures with transactional support were introduced to remedy this problem.

Note: You’ll need to enable ysql_beta_features flag on your cluster to try out this feature.

[#870] Allow WITH clauses in CREATE TABLE

In PostgreSQL, the WITH clause is used in CREATE TABLE statements to specify storage parameters or primary keys in system tables, also known as object identifiers or OIDs. Although, PostgreSQL storage parameters aren't relevant to tables stored in YugaByte because it handles data distribution differently, some standard utilities such as submit DDL statements with this clause.

Getting Started with Stored Procedures

Enable the FUNCTIONS Flag

If you haven’t already installed YugaByte DB, do so by following the first two steps in our quickstart.

Restart your cluster to enable the stored procedure feature:

./bin/yb-ctl restart --tserver_flags="ysql_beta_features=true"

Open up the YSQL Shell

What’s YSQL? It’s YugaByte DB’s PostgreSQL-compatible, distributed SQL API.

Before executing any SQL, we’ll need to be in the YSQL shell. You can access it by executing:

./bin/ysqlsh — echo-queries
ysqlsh (11.2)
Type “help” for help.
postgres=# 

Create an Accounts Table

CREATE TABLE accounts (
id INT GENERATED BY DEFAULT AS IDENTITY,
name VARCHAR(100) NOT NULL,
balance DEC(15,2) NOT NULL,
PRIMARY KEY(id)
);

Insert Data Into the Accounts Table

INSERT INTO accounts(name,balance) VALUES(‘Bob’,10000);
INSERT INTO accounts(name,balance) VALUES(‘Alice’,10000);

Verify the Data in the Accounts Table

SELECT * FROM accounts;
 
 id | name | balance
 — — + — — — -+ — — — — — 
 1 | Bob | 10000.00
 2 | Alice | 10000.00
 
 (2 rows)

Create a Stored Procedure

The following example creates a stored procedure named transfer that transfers a specified amount of money from one account to another.

CREATE OR REPLACE PROCEDURE transfer(INT, INT, DEC)
LANGUAGE plpgsql
AS $$
BEGIN
 — subtracting the amount from the sender’s account
UPDATE accounts
SET balance = balance — $3
WHERE id = $1;
— adding the amount to the receiver’s account
UPDATE accounts
SET balance = balance + $3
WHERE id = $2;
COMMIT;
END;
$$;

Calling the Stored Procedure

Next, let’s call the transfer stored procedure and transfer $1000 from an account with id 1 (Bob) to the account with id 2 (Alice).

CALL transfer(1,2,1000);

Verify the Transfer of Money

select * from accounts;
 
id | name | balance
 — — + — — — -+ — — — — — 
1 | Bob | 9000.00
2 | Alice | 11000.00
 
(2 rows)

New Documentation, Blogs and Videos

New Blogs

• GO-JEK’s Performance Benchmarking of CockroachDB, TiDB & YugaByte DB on Kubernetes
• Best Practices for Deploying Confluent Kafka, Spring Boot & Distributed SQL Based Streaming Apps on Kubernetes
• How Data Sharding Works in a Distributed SQL Database

New Docs

• Build a Java App with Spring Data JPA with Hibernate ORM Example
• Build a NodeJS App with a Sequelize ORM Example
• Build a Go App with a GORM ORM Example
• Build a Python App with SQL Alchemy ORM Example
• Build a Ruby App with Rails ActiveRecord ORM Example
• Want to contribute code to the YugaByte DB project? Contributor guides are now live!
• A new Benchmarking Guide is also available.

Upcoming Meetups and Conferences

We will be at a number of conferences and meetups over the next few months, below are some select few with the highlights. Do stop by, say hello and ask us any questions you have.

PostgreSQL Meetups

• July 10: Chicago PostgreSQL Meetup Group
• July 25: Boston PostgreSQL Meetup Group

SpringOne Tour

• July 9–10: Chicago
• July 15–16: Philadelphia
• July 25–26: Burlington

SpringOne Platform

• Oct 7–10: Austin

AWS re:Invent

• Dec 2–6: Las Vegas

We are Hiring!

YugaByte is growing fast and we’d like you to help us keep the momentum going! Check out our currently open positions:

• Software Engineer — Cloud Infrastructure — Sunnyvale, CA
• Software Engineer — Core Database — Sunnyvale, CA
• Software Engineer — Full Stack — Sunnyvale, CA
• Developer Advocate — Sunnyvale, CA

Our team consists of domain experts from leading software companies such as Facebook, Oracle, Nutanix, Google and LinkedIn. We have come a long way in a short time but we cannot rest on our past accomplishments. We need your ideas and skills to make us better at every function that is necessary to create the next great software company. All while having tons of fun and blazing new trails!

Get Started

Ready to start exploring YugaByte DB features? Getting up and running locally with a three node cluster on your laptop is fast. Just download, extract, create the cluster, check its status and your ready to go!

What’s Next?

• Using YugaByte at your company? Tell us about it and we’ll send you a hoodie!
• Compare YugaByte DB in depth to databases like CockroachDB, Google Cloud Spanner and MongoDB.
• Get started with YugaByte DB on macOS, Linux, Docker, and Kubernetes.
• Contact us to learn more about licensing, pricing or to schedule a technical overview.

Originally published at https://blog.yugabyte.com on June 27, 2019.