Tuesday, March 20

Using EclipseLink with PostgreSQL

2ndQuadrant, Umair's PlanetPostgreSQL
1. Introduction EclipseLink was announced in 2008 as the JPA 2.0 implementation from the Eclipse Foundation. It is based on the TopLink project from which then Oracle contributed code to the EclipseLink project. The project delivers an open source runtime framework supporting the Java Persistence API standards. The EclipseLink project provides a proven, commercial quality persistence solution that can be used in both Java SE and Java EE applications. EclipseLink is open source and is distributed under the Eclipse Public License. 2. Implementation Details Like Hibernate, EclipseLink is also fully JPA 2.0 compliant. This makes the implementation details quite similar to those already described in a preceding Hibernate blog. For the illustration below, we will continue using the (more…)

PostgreSQL – The most loved RDBMS

2ndQuadrant, Simon's PlanetPostgreSQL
The 2018 StackOverflow survey has just been published, with good news for PostgreSQL. https://insights.stackoverflow.com/survey/2018/#technology-most-loved-dreaded-and-wanted-databases StackOverflow got more than 100,000 responses from people in a comprehensive 30 minute survey. PostgreSQL is the third most commonly used database, with 33% of respondents, slightly behind MySQL and SQLServer, yet well ahead of other options. Early in January, the DBEngines results showed PostgreSQL in 4th place behind Oracle, yet here we see that actually Oracle heads up the Most Dreaded list along with DB2, leaving PostgreSQL to power through to 3rd place. PostgreSQL at 62% is the second most loved database, so close behind Redis (on 64%) that they're almost even. But then Redis is only used by (more…)

Using Java ORMs with PostgreSQL – MyBatis

2ndQuadrant, Umair's PlanetPostgreSQL
In my previous blogs, I wrote about Hibernate Query Language (HQL) and Querydsl in detail, now I'm going to talk about MyBatis. While ORMs typically map Java objects to database tables (or vice versa), MyBatis takes a different approach by mapping Java methods to SQL statements. This gives you complete control over writing SQL and its subsequent execution. With the help of a mapper, MyBatis also allows automatic mapping of database objects to Java objects. Like all other Java persistence frameworks, the main aim of MyBatis is to reduce the time and coding requirements of talking to a database using raw JDBC. It is licensed as Apache License 2.0 and is free to use. Why Use MyBatis? MyBatis design has a database-centric approach, so if your application is driven by relational (more…)
PostgreSQL Meltdown Benchmarks

PostgreSQL Meltdown Benchmarks

2ndQuadrant, PostgreSQL, Tomas' PlanetPostgreSQL
Two serious security vulnerabilities (code named Meltdown and Spectre) were revealed a couple of weeks ago. Initial tests suggested the performance impact of mitigations (added in the kernel) might be up to ~30% for some workloads, depending on the syscall rate. Those early estimates had to be done quickly, and so were based on limited amounts of testing. Furthermore, the in-kernel fixes evolved and improved over time, and we now also got retpoline which should address Spectre v2. This post presents data from more thorough tests, hopefully providing more reliable estimates for typical PostgreSQL workloads. (more…)
Dev Corner: error context stack corruption

Dev Corner: error context stack corruption

Craig's PlanetPostgreSQL
PostgreSQL uses error context callbacks to allow code paths to annotate errors with additional information. For example, pl/pgsql uses them to add a CONTEXT message reporting the procedure that was executing at the time of the error. But if you get it wrong when you use one in an extension or a patch to core, it can be quite hard to debug. I'd like to share some hints here for people involved in PostgreSQL's C core and extensions. (more…)

PostgreSQL Maximum Table Size

Simon's PlanetPostgreSQL
Various limits on the PostgreSQL database are listed here: https://www.postgresql.org/about/ One of those limits is the Maximum Table Size, listed as 32TB. It's been that way for many years now. Only problem is that it has always been wrong, slightly. And now its got much, much bigger. The table size is limited by the maximum number of blocks in a table, which is 2^32 blocks. The default block size is 8192 bytes, hence the default limit was 32TB as listed. That was wrong in a two ways, because PostgreSQL has always had a configurable block size which allows up to 32768 bytes per block, which would give a maximum size of 128TB. Only problem is that requires an unload/reload to change the block size, so the effective limit per table was 32TB as advertized. PostgreSQL has always (more…)

PostgreSQL 11 – Server-side Procedures (Part 2)

2ndQuadrant, Umair's PlanetPostgreSQL
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() AS $$ BEGIN    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();            COMMIT;        ELSE (more…)

Webinar: Data Integration With PostgreSQL [Follow Up]

Liaqat's PlanetPostgreSQL, Webinars
Having a unified view when combining data from different sources is essential for every business. Whether you want to connect to a cloud application, import/export data to/from a text file, or sync data between your datasource and a 3rd party platform - having a database platform that provides a seamless and effective data integration process is critical. This week on Tuesday, 13 February, 2ndQuadrant hosted the second webinar in their PostgreSQL webinar series. Principal Consultant, Shaun Thomas, conducted the webinar, covering various topics around integration of PostgreSQL with other data sources and database technologies. The recording of the Webinar is now available. Those who couldn’t make it to the live session can now view Data Integration with PostgreSQL here. Below, (more…)

Oracle with OmniDB

OmniDB, William's PlanetPostgreSQL
OmniDB was designed to allow users to manage multiple databases from different RDMBS within an unified workspace. The philosophy is to use the same familiar web interface (same structures, icons, context menu and handy features such as SQL Autocomplete, Create/Edit Table Form and Edit Data) so the user feels comfortable and confident to manage different systems as if they are just different connections from the same system. OmniDB 2.5.0 introduces basic support to Oracle databases. Users can connect and interact with Oracle databases the same way the tool already does for PostgreSQL databases. Oracle versions 11g and 12c are supported. Let's connect to Oracle databases As you can see below, I already have some OmniDB connections. Now let's connect to Oracle right away, click on New (more…)

OmniDB: Monitoring Dashboard

OmniDB, William's PlanetPostgreSQL
OmniDB 2.4.0 introduces a new cool feature called Monitoring Dashboard. We know a picture is worth a thousand words, so please take a look: As you can see, this is a new kind of inner tab showing some charts and grids. This Monitoring inner tab is automatically opened once you expand the tree root node (the PostgreSQL node). You can keep it open or close it at any time. To open it again, right-click the root node and click on Dashboard. The dashboard is composed of handy information rectangles called Monitoring Units. Currently there are 3 types of Monitoring Units: Grid: The most simple kind, just executes a query from time to time and shows the results in a data grid. Chart: Every time it refreshes, it renders a new complete chart. The old set of values is (more…)