PG Functions

Writing Advanced PostgreSQL Functions

lloydalbin's picture
Submitted by lloydalbin on Tue, 06/06/2017 - 01:00

What we are going to cover

  • Function Basics
    • Procedural Languages
    • Function Behavior
    • NULL INPUT
    • Security
    • Syntax
    • Inline (Function) Syntax
  • Basic Example Functions
    • Functional Indexes
    • Real World Example of Functional Indexes
    • Case Insensitive Indexes
  • Advanced Functions
    • Working with View Definitions
  • Debugging Functions
    • Debugging your Function
Documents: 
PDF icon 2017-06-26 Writing PostgreSQL Functions.pdf
File 2017-06-26 Writing PostgreSQL Functions.pptx

Using R inside your Data Warehouse

lloydalbin's picture
Submitted by lloydalbin on Tue, 12/01/2015 - 00:00

We want to monitor index bloat every hour, but the data set is getting to large to handle in Excel. This is because the table is adding 100,000+ rows of data an hour. We have created a view on this data that reduces the data to one row per day, but that means that after 10 days worth of data we have hit Excel’s row limit of 1,048,576 rows and can no longer use the pivot table and graphing in Excel.

This left me with a problem of how to look at this data when I did not have an application that could deal with this much data.

In reading some presentations on Data Warehousing and Postgres, I saw people adding R into the Data Warehouse.

Documents: 
File 2015-12-01 Using R inside your Data Warehouse.pptx

Using pgTap to unit test your functions and queries

lloydalbin's picture
Submitted by lloydalbin on Tue, 01/03/2017 - 00:00

pgTAP: Unit Testing for PostgreSQL

pgTAP is a suite of database functions that make it easy to write TAP-emitting unit tests in psql scripts or xUnit-style test functions. The TAP output is suitable for harvesting, analysis, and reporting by a TAP harness, such as those used in Perl applications.

    Good for:
    • Application Development
    • Schema Validation
    • xUnit-Style Testing
    • Module Development
Documents: 
File 2017-01-03 pgTAP.pptx

Shadow Tables vers PGAudit

lloydalbin's picture
Submitted by lloydalbin on Wed, 01/17/2018 - 16:07

A shadow table is an table that is read and written by a trigger function and contains data similar to (in the same format as) its primary table, which is the table it's "shadowing" along with a few extra fields so that you can time travel the shadow table and know who did what and when. In this presentation we will cover traditional Shadow Tables which works with all current version of Postgresql and all cloud providers vers PGAudit which required Postgres 9.5 or newer. PGAudit provide PostgreSQL users with capability to produce audit logs often required to comply with government, financial, or ISO certifications. While both provide similar things, they are drastically different in the goals and the outputs and what you can easily do with those outputs.

  • Shadow Tables:
    • Writing a Shadow Table Function
    • Attaching the Function via a Trigger to a Normal Table
    • Time Traveling your Shadow Table
    • Capturing DDL Changes
  • PGAudit:
    • Compiling PG Audit
    • Auditing Tables
    • Auditing DDL
    • Setting who can be audited
    • Setting what can be audited
  • Pros & Cons
    • Shadow Tables
    • PGAudit
  • Cloud Providers
    • Amazon RDS Postgresql
    • Amazon Aurora Postgresql
    • Microsoft Azure Postgresql
    • Google Postgresql
  • Other Shadow Table Projects

The 3/22/2019 Presentation and Zip (sql) files are now presented online here that I gave in at the PostgresConf in New York.

Documents: 
File 2018-02-22 Shadow Tables vers pgAudit.pptx
File 2019-03-22 Shadow Tables vers pgAudit.pptx
Package icon 2019-03-22 Shadow Tables vers pgAudit.zip
File 2019-10-08 Shadow Tables vers pgAudit.pptx

Shadow Tables

lloydalbin's picture
Submitted by lloydalbin on Tue, 02/02/2016 - 00:00

Sometimes we want to have a copy of a table and know when everything happened to the original table, insert, update, delete, and truncate. This is possible to have happen automatically with a trigger function.

In these first slides, I will give you the basic function that I have given you before.

Then in the next set of slides, I will show you how to write a generic shadow function that can write multiple shadow tables.

Documents: 
File 2016-02-02 Shadow Tables.pptx

Functional Indexes

lloydalbin's picture
Submitted by lloydalbin on Tue, 05/05/2015 - 00:00

This presentation will go over how to write functions that can be used to index fields. We receive faxes that are multi-page TIFF’s. The TIFF file name are called the raster id. We have data where we have the full path of the file name, the raster id and the raster id with page number.

Examples:
0000/000000
0000/0000001111
/studydata/studyname/0000/000000

We want to join across these fields quickly and this can be done with functional indexes, where the indexed value is not the data inside the field. In this case we will be indexing the value 0000/000000 from the above example.

Documents: 
File 2015-05-05 Functional Indexes.pptx