2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
  • Products
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / OmniDB3 / OmniDB debugger for PostgreSQL 11
William Ivanski

OmniDB debugger for PostgreSQL 11

November 6, 2018/1 Comment/in OmniDB, William's PlanetPostgreSQL /by William Ivanski

PostgreSQL 11 was released recently, with exciting new features. One of them is the ability to write SQL procedures that can perform full transaction management, enabling developers to create more advanced server-side applications. SQL procedures can be created using the CREATE PROCEDURE command and executed using the CALL command. Since OmniDB 2.3.0 it is possible to debug PostgreSQL PL/pgSQL functions. Support to PostgreSQL 11 functions and procedures was added in OmniDB 2.11.0.

Last week we released OmniDB 2.12.0 with nice new features and a new revamped visual, so I’m going to show you how OmniDB 2.12.0 can debug PostgreSQL 11 procedures.

First of all, if you have not done that already, download and install a binary PostgreSQL library called omnidb_plugin and enable it in PostgreSQL’s config file. The debugger also uses a special schema with special tables to control the whole debugging process. This can be manually created or with an extension. For more details on the installation, please refer to the instructions. You can also refer to the documentation about the debugger.

Creating some tables in OmniDB

For our tests, let’s create 2 simple tables, foo and bar. Let’s do that using the OmniDB Console Tab:

CREATE TABLE public.foo (
    a INTEGER PRIMARY KEY
);
 
CREATE TABLE public.bar (
    a INTEGER,
    b INTEGER
);

Creating a procedure with transaction management

Note that OmniDB has a Procedures node in the tree view. Right-click on it, then click on Create Procedure. It will open a Query Tab with a SQL template showing basic SQL syntax to create a procedure.

If you want to know more about procedures, you can read online documentation without leaving OmniDB. Simple click on Procedures -> Doc: Procedures and a browser tab will be open for you already pointing to the documentation page:

Now let’s go back to the Create Procedure tab and change the code to actually create a procedure, like this:

CREATE OR REPLACE PROCEDURE public.prc_test
(
    p INTEGER
)
LANGUAGE plpgsql
AS
$procedure$
BEGIN
    FOR i IN 1 .. p LOOP
        INSERT INTO public.bar (a, b) VALUES (p, i);
    END LOOP;
 
    IF p NOT IN (SELECT a FROM public.foo) THEN
        INSERT INTO public.foo (a) VALUES (p);
        COMMIT;
        RAISE NOTICE 'Inserted %', p;
    ELSE
        ROLLBACK;
        RAISE NOTICE '% was already inserted', p;
    END IF;
END;
$procedure$

Once you click on Run or hit Alt-Q, the procedure will be compiled and after refreshing the Procedures node, you will see the procedure we just created:

Debugging a procedure with transaction management

To open the OmniDB debugger interface, right-click on the procedure and then click on Debug Procedure.

Now fill the value of the parameter p, for example 3. In this case, when PostgreSQL executes the COMMIT, we will be able to see data in both tables foo and bar even before the procedure finishes its execution.

Click on the lightning button to start debugging. The debugger will point its cursor on the first command and you will be able to see all variables.

By clicking on the right arrow button (Step Over), you can watch PostgreSQL executing statement by statement. You will see it performs an INSERT 3 times on the table bar. But if you run a SELECT on this table, you will see it is still empty.

Now advance the debugger until it performs the COMMIT statement, then try fetching data from the bar table again.

 

The procedure did not finish its execution yet, but thanks to the ability of the user to commit everything that was done so far, you are able to see data from a different backend. Now advance the procedure until the end.

The procedure logic grants that data is not inserted in the bar table if p already exists in the foo table. So let’s start the debugger again with p=3 to see the ROLLBACK statement being executed:

Omnidb debugger Postgres 11

Omnidb debugger Postgresql 11

Tags: OmniDB, postgres, PostgreSQL, PostgreSQL 11, PostgreSQL 11 New Features, PostgreSQL11
Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
1 reply
  1. eman
    eman says:
    November 16, 2018 at 5:54 pm

    there is no features to handled unstructured data??

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Get in touch with us!

Recent Posts

  • Random Data December 3, 2020
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up] November 13, 2020
  • Full-text search since PostgreSQL 8.3 November 5, 2020
  • Random numbers November 3, 2020
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up] November 2, 2020

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
Webinar: PostgreSQL is NOT your traditional SQL database [Follow Up] PostgreSQL Buildfarm Client Release 9
Scroll to top
×