Transaction control in PL procedures
A couple of months back, I wrote about how we now have the ability to write Stored Procedures in PostgreSQL. This post follows up on that and talks about the next step that was implemented: transaction control in PL procedures. The feature was committed on 22-Jan-2018.
With this addition, you now have the ability to call COMMIT and ROLLBACK commands in PL/pgSQL from within a procedure. To illustrate:
CREATE TABLE test1 (a int);
CREATE PROCEDURE transaction_test1()
FOR i IN 0..9 LOOP
INSERT INTO test1 (a) VALUES (i);
IF i % 2 = 0 THEN
RAISE NOTICE 'i=%, txid=% will be committed', i, txid_current();
Last week marked a rather big step in the PostgreSQL world that went largely unnoticed. Thanks to the work done by 2ndQuadrant contributors, we now have the ability to write Stored Procedures in PostgreSQL!
(Ok, well not exactly now but we will have the ability once PostgreSQL 11 comes out)
A procedure is essentially a set of commands to be executed in a particular order. As opposed to functions, procedures are not required to return a value. With this addition, you can now invoke a procedure by simply using the new CALL statement rather than using SELECT. The implementation is fully compliant with the SQL standard and will allow users to write procedures that are somewhat compatible with DB2, MySQL, and to a lesser extent, Oracle.
The commit from last week adds new commands CALL (more…)
The short answer … Hell Yeah!
The long answer lies in extensive improvements and the impressive new feature list that makes up this major release - which, by the way, changes the version scheme of PostgreSQL as well (more details on that here). This wiki page lists out, in detail, all the new features in PostgreSQL 10, but for the purpose of this blog, I will focus on some of the exciting features contributed by 2ndQuadrant. (more…)
jOOQ is an ORM-alternative that is relational model centric rather than domain model centric like most ORMs. For example, while Hibernate lets you write Java code and then automatically translates it to SQL, jOOQ let’s you write relational objects in your database using SQL and then automatically generates Java code to map to those relational objects.
The writers of jOOQ believe in the power of SQL and assume that you want low level control of the SQL running for your application. This means that you can write your database and your schema without having to worry about how (and if!) it can be handled in Java.
Why Use jOOQ?
While JPA provides a huge framework with a great deal of flexibility and power, it can very quickly become quite complex. jOOQ provides a simpler interface for (more…)
This is part of a series of blogs on Java & PostgreSQL. For links to other blogs in this series, please scroll to the end of this post.
Starting v9.2, PostgreSQL is providing native data type support for JSON objects. Subsequent releases introduced JSONB (binary formatted JSON objects) and many data manipulation functions for JSONs, making it a very powerful tool for NoSQL operations.
String manipulation and parsing are very expensive operations in a database, so although you could have potentially stored JSON objects in strings in PostgreSQL before, introduction of the native data type has taken away overheads and made throughput a lot faster for JSON manipulation.
JSON & JSONB
JSONB was introduced as a native data type in v9.4 of PostgreSQL and it stores JSON objects (more…)
When I talk to various people about PostgreSQL, based on where they are in the process of analyzing it, I get asked many similar questions. One of the most frequently asked questions is: What is the PostgreSQL licensing model? Here is my attempt to address this question from the perspective of what the PostgreSQL license means for your business.
Go Ahead, Read It - It’s Really Not That Scary
Let’s be honest with ourselves, we are really not in the habit of reading license agreements - they are best left to the legal department to decipher. And seriously, licenses have become so complex and so detailed that even if I try really really hard to read just a few clauses, my head starts to hurt.
The PostgreSQL License is different though.
I have reproduced the license in full at (more…)
PGDG announced the General Availability of PostgreSQL 9.6 on the 29th of September. Like each year, this new release brings with it a lot of exciting new features. Using these features, of course, requires installing PostgreSQL ... a task that can be a touch daunting for novice users. 2UDA, a graphical installer for PostgreSQL, is designed to make the installation as easy as 1-2-3.
Admittedly, the primary purpose of 2UDA is to provide a unified package for data analytics using PostgreSQL. But it can just as easily be used to install PostgreSQL on the most popular platforms - Windows, OSX, & Linux. This is especially helpful for Windows & OSX, where users are more accustomed to graphical point-and-click installers.
PostgreSQL 9.6 is now available in 2UDA installers and (more…)
The latest version of PostgreSQL 9.6 is planned to be released later today, bringing with it some much anticipated features and updates. As the most advanced open source database, PostgreSQL strives to release a major version roughly once every year. With an active and collaborative community, this PostgreSQL release boasts impressive features and updates thanks to contributions from many of the highly knowledgeable community members.
The expanding team at 2ndQuadrant has continued to show dedication to the PostgreSQL database project by contributing heavily to the PostgreSQL 9.6 release. Parallel execution of large queries has been a known shortcoming of PostgreSQL for some time, but this is no longer an issue with the 9.6 release. David Rowley and Simon Riggs contributed to (more…)
I was in Jakarta a couple of weeks ago and there happened to be a meetup of the Indonesia PUG in Bandung while I was there. Because it is just a 2 hour, rather picturesque drive, from Jakarta, I thought it was too good of an opportunity to miss. So I showed up.
The meetup was hosted by Julyanto Sutandang of Equnix Business Solutions and the conversation was mostly centered around convincing the local technology industry about PostgreSQL in comparison to Oracle. We got into fairly detailed discussions on the typical challenges of moving an Oracle production database to PostgreSQL. I especially love talking about hierarchical queries - Oracle's CONNECT BY PRIOR and PostgreSQL's WITH RECURSIVE.
It was very interesting to find out how popular BDR - the Multi Master (more…)
Querydsl is a domain model centric ORM that was created out of a need to add typesafety to HQL. As demonstrated in my blog on HQL, to write queries using HQL, at times long string concatenations need to be used. Such strings are not only hard to read, they are prone to runtime errors, avoiding which is the main driver for using ORMs.
Although it was initially primarily targeted at HQL, Querydsl is now also available for JPA, JDO, JDBC, Lucene, Hibernate Search, MongoDB, Collections and RDFBean.
Why Use Querydsl?
The biggest advantage Querydsl offers is typesafety. Rather than relying on strings, it provides you the ability of querying your database using functions. This takes care of syntactic correctness and ensures that you don’t get runtime errors.
With typesafety, comes the (more…)