Long story short, using PostgreSQL 11 and RUM index you can do both TOP-N query and COUNT(*) query for non-selective FTS queries without fetching all the matching results from heap (and that is certainly much faster). If you’re interested in details, then please read the detailed description below.

At November 1st 2017, Tome Lane committed a patch enabling bitmap scans to behave like index-only scan when possible. In particular, since PostgreSQL 11 COUNT(*) queries can be evaluated using bitmap scans without accessing heap, when corresponding bit in visibility map is set.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

commit 7c70996ebf0949b142a99c9445061c3c83ce62b3
Author: Tom Lane <a href="&#109;&#097;&#105;&#108;&#116;&#111;:&#116;&#103;&#108;&#064;&#115;&#115;&#115;&#046;&#112;&#103;&#104;&#046;&#112;&#097;&#046;&#117;&#115;">&#116;&#103;&#108;&#064;&#115;&#115;&#115;&#046;&#112;&#103;&#104;&#046;&#112;&#097;&#046;&#117;&#115;</a>
Date:   Wed Nov 1 17:38:12 2017 -0400</p>

<pre><code>Allow bitmap scans to operate as index-only scans when possible.

If we don't have to return any columns from heap tuples, and there's
no need to recheck qual conditions, and the heap page is all-visible,
then we can skip fetching the heap page altogether.

Skip prefetching pages too, when possible, on the assumption that the
recheck flag will remain the same from one page to the next.  While that
assumption is hardly bulletproof, it seems like a good bet most of the
time, and better than prefetching pages we don't need.

This commit installs the executor infrastructure, but doesn't change
any planner cost estimates, thus possibly causing bitmap scans to
not be chosen in cases where this change renders them the best choice.
I (tgl) am not entirely convinced that we need to account for this
behavior in the planner, because I think typically the bitmap scan would
get chosen anyway if it's the best bet.  In any case the submitted patch
took way too many shortcuts, resulting in too many clearly-bad 

For those of you out there working with PostgreSQL in a professional way, migrating from Oracle to PostgreSQL might be one of the most beloved tasks available. One of the first things most people will notice, however, is that those data types available in Oracle might not be quite the same in PostgreSQL. This blog will try to shed some light and show, how things work.

Data types in Oracle and PostgreSQL

While there are many similarities between Oracle and PostgreSQL there are a couple of differences, which are quite noteworthy. The first thing many people might notice is: PostgreSQL has many more data types than Oracle. As of version 10.0 an empty PostgreSQL database will expose the staggering number of 92 data types. Of course not all of them are useful and many of them are purely internal used for internal purposes.

Still: At the end of the day there are just more data types, which are can be used by applications, which of course

Let us take a look and see, which types can be matched. The following table contains a lof of potential options:

1. Parse: parse SQL statement
2.  Bind: bind prameters to the parsed SQL statement
3.  Execute: run the query
4.  Sync: ask PostgreSQL to return the query results

1. Parse: SELECT pg_sleep(2) (Query A)
2.  Bind: bind prameters to the parsed SQL statement A
3.  Execute: run the query A
4. Parse: SELECT pg_sleep(3) (Query B)
5. Bind B
6.  Execute B
7.  Sync

I have completed the draft version of the Postgres 11 release notes. Consisting of 167 items, this release makes big advances in partitioning, parallelism, and server-side transaction control via procedures. One of the more unexpected yet useful features is "Allow 'quit' and 'exit' to exit psql when used in an empty buffer".

The release notes will be continually updated until the final release, which is expected in September or October of this year.

void main()
{
   long int s = 0;
   int i;

   for (i in 1; i < 10000000; i++)
     s := i;

   printf("%ld\n", s);
}

In a recent wrestling match with the Linux “out-of-memory killer” for a Cybertec customer I got acquainted with Linux control groups (“cgroups”), and I want to give you a short introduction how they can be used with PostgreSQL and discuss their usefulness.

Warning: This was done on my RedHat Fedora 27 system running Linux 4.16.5 with cgroups v1 managed by systemd version 234. Both cgroups and systemd‘s handling of them seem to be undergoing changes, so your mileage may vary considerably. Still, it should be a useful starting point if you want to explore cgroups.

What are Linux cgroups?

From the cgroups manual page:

Control cgroups, usually referred to as cgroups, are a Linux kernel feature which allow processes to be organized into hierarchical groups whose usage of various types of resources can then be limited and monitored.

cgroups are managed with special commands that start with “cg”, but can also be managed through a special cgroups file system and systemd.

Now a running PostgreSQL cluster is a group of processes, so that’s a perfect fit.

There are several subsystems defined (also called “controllers” in cgroups terminology). Of these, the following are interesting for PostgreSQL:

• memory: useful for limiting the total memory usage
• blkio: useful for limiting the I/O throughput
• cpu: useful to define upper and lower limits to the CPU time available for the processes
• cpuset: useful for binding the processes to a subset of the available CPU cores

Configuring cgroups

During system startup, cgroups are created as defined in the /etc/cgconfig.conf configuration file.

Let’s create a cgroup to build a cage for a PostgreSQL cluster:

group db_cage {
    # user and group "postgres" can manage these cgroups
    perm {
        task {
            uid = postgres;
            gid = postgres;
            fperm = 774;
        }
        admin {
            uid = postgres;
            gid = postgres;
            dperm = 775;
            fperm = 774;
        }
    }

    # limit memory to 1 GB and di

Common Table Expressions (CTEs) are a powerful construct within SQL. In day to day conversation, you may hear CTEs referred to as WITH clauses. You can think of CTEs as similar to a view that is materialized only while that query is running and does not exist outside of that query. CTEs can be very useful building blocks for allowing your large SQL queries to be more readable. But, they can also be used recursively allowing you to create some very complex queries without having to drop down to a procedural language like plpgsql or plv8.

Recursive CTEs allow themselves to be called until some condition is met. Let’s jump right in and explore a recursive CTE—a basic one, and using PostgreSQL of course—and then let’s dissect the recursive CTE a bit further to see what we can use it for:

WITH RECURSIVE tens (n) AS (
    SELECT 10
  UNION ALL
    SELECT n+10 FROM tens WHERE n+10<= 100
)
SELECT n FROM tens;

When the above is run we’ll get the following result:

  n
-----
  10
  20
  30
  40
  50
  60
  70
  80
  90
 100
(10 rows)

With the above we could also easily do this with a generate_series. But stick with us and you’ll see the more complex things we can do that aren’t possible with generate_series. First let’s take a closer look at how it works.

The first part you’ll notice is WITH RECURSIVE. This tells Postgres the CTE can recursively call itself. The next portion you’ll notice is it takes some parameters into it. In this case (n), it can also take more than one should you need.

Moving further into the CTE, we have the first query that is executed, SELECT 10, which generates the first value. The second portion is where all the fun begins. The UNION ALL specifies that we’re going to return all the records that are produced from the loop. Then SELECT n+10 FROM tens WHERE n+10<= 100 will keep calling the tens CTE that is created until the condition is met.

So those are the basics, but the interesting question is: when would you use a recursive CTE? When you have a tree or hierarchical structure to your data, re

The May meeting will be held at 18:00 EST on Tues, the 22^nd. Once again, we will be holding the meeting in the community space at CoverMyMeds. Please RSVP on MeetUp so we have an idea on the amount of food needed.

BRING YOUR LAPTOP

What

Due to technical issues last month, CoverMyMeds’ very own Andy will be re-presenting on PostgreSQL data restores this month. We’ll continue to use our VMs and pgBackRest to dive into the topic as well as the concepts and commands underneath. Please bring your laptop as this will be hands-on.

Where

CoverMyMeds has graciously agreed to validate your parking if you use their garage so please park there:

You can safely ignore any sign saying to not park in the garage as long as it’s after 17:30 when you arrive.

Park in any space that is not marked ‘24 hour reserved’.

Once parked, take the elevator/stairs to the 3^rd floor to reach the Miranova lobby. Once in the lobby, the elevator bank is in the back (West side) of the building. Take a left and walk down the hall until you see the elevator bank on your right. Grab an elevator up to the 11^th floor. (If the elevator won’t let you pick the 11^th floor, contact Doug or CJ (info below)). Once you exit the elevator, look to your left and right; one side will have visible cubicles, the other won’t. Head to the side without cubicles. You’re now in the community space:

The kitchen is to your right (grab yourself a drink) and the meeting will be held to your left. Walk down the room towards the stage.

If you have any issues or questions with parking or the elevators, feel free to text/call Doug at +1.614.316.5079 or CJ at +1.740.407.7043

I presented my “PostGIS for Managers” talk for the last time (at least in this form) today at FOSS4G North America. The reason it’s for the last time is that the central conceipt it’s built around, that a core decision is between a proprietary and an open source database, isn’t really operative anymore. The real decisions are now being driven by other considerations, like cloud platforms, and the services available there. So, it’s not really PostgreSQL vs Oracle anymore.

• PDF Version

I also presented my “SQL Festival” talk, for the first time! New material is always a little challenging: will it work, is it the right level for the audience? It seemed to be well received, a mix of generic SQL tidbits, and some specific interesting queries you can do with PostGIS.

• Google Slides

The Incident

While cleaning up some obsolete user accounts, one of my DROP USER commands failed with:

ERROR:  2BP01: role "joe" cannot be dropped because some objects depend on it

Querying the usual catalog views, I couldn’t find any objects owned by this user. That’s when I noticed the line just after the ERROR:

DETAIL:  privileges for schema public

The \dn+ command confirmed that user joe had explicit USAGE privileges on the public schema:

                           List of schemas
   Name   |  Owner   |  Access privileges   |      Description
----------+----------+----------------------+------------------------
 public   | postgres | postgres=UC/postgres+| standard public schema
          |          | =UC/postgres        +|
          |          | joe=U/postgres       |

I revoked this and was then able to drop that user.

> REVOKE USAGE ON SCHEMA public FROM joe;
REVOKE
> DROP USER joe;
DROP ROLE

Replicating The Problem

It’s simple enough to replicate the problem, and it isn’t limited to the special public schema (although that does bring up another question for later).

Setup

We already have the public schema, let’s create another one for testing and then create our three lab rat users:

-- Create a test schema, we'll use this in addition to public
> CREATE SCHEMA appstuff;
CREATE SCHEMA

-- Create our three users
> CREATE USER moe;
CREATE ROLE
> CREATE USER larry;
CREATE ROLE
> CREATE USER curly;
CREATE ROLE

Now let’s grant USAGE on the two schemas to two of the users:

> GRANT USAGE ON SCHEMA public TO moe;
GRANT
> GRANT USAGE ON SCHEMA appstuff TO larry;
GRANT

-- Show the access privileges on the schemas
> \dn+
                           List of schemas
   Name   |  Owner   |  Access privileges   |      Description
----------+----------+----------------------+------------------------
 appstuff | postgres | postgres=UC/postgres+|
          |          | larry=U/postgres     |
 public   | postgres | postgres=UC/postgres+| standard public schema
          |          | =UC/postgres       

The slides from my talk on PostgreSQL Replication at PerconaLive 2018 are available.

When: 6-8pm Thursday May 17, 2018
Where: iovation
Who: Dylan Hornstein
What: Learning SQL

During this PDXPUG meetup, we will talk about one person’s journey to learn SQL. From joining iovation’s Reporting Team without any experience with SQL or relational databases, to using SQL every day for adhoc data analysis and bulk data dumps, Dylan Hornstein will talk about his experience getting familiar with SQL and learning to navigate a relational database, as well as some challenges and tips he’s found along the way. The presentation is geared towards those starting out in data roles and we will likely expand into a wider conversation around using SQL and understanding data.

Dylan has been a Data Analyst at iovation for three years now. Having spent six months as a Client Manager before moving to his Data Analyst role, Dylan has experience working directly with iovation’s clients as well as working behind the scenes with the data. In his current role, he is responsible for providing reports, adhoc analysis and bulk data dumps for clients and internal teams. While Dylan had prior work experience as a Data Analyst, his move to iovation’s Reporting Team came with a steep learning curve, as he was new to working with SQL and relational databases.

—
If you have a job posting or event you would like me to announce at the meeting, please send it along. The deadline for inclusion is 5pm the day before the meeting.
—

Our meeting will be held at iovation, on the 3rd floor of the US Bancorp Tower at 111 SW 5th (5th & Oak). It’s right on the Green & Yellow Max lines. Underground bike parking is available in the parking garage; outdoors all around the block in the usual spots. No bikes in the office, sorry! For access to the 3rd floor of the plaza, please either take the lobby stairs to the third floor or take the plaza elevator (near Subway and Rabbit’s Cafe) to the third floor. There will be signs directing you to the meeting room. All attendess must check in at the iovation front desk.

See you there!

Preface

There are several ways of building PostgreSQL under Windows. Official manual stands using Visual Studio is the simplest one, wiki describes how to use mingw and mingw-w64.

As for me, using new MSYS2 building platform for Windows is a bless. With its help not only PostgreSQL sources are built smoothly, but even extensions are not a problem anymore. And if you were playing with extensions under Windows, you know what I’m talking about.

At its core MSYS2 is an independent rewrite of MSYS, based on modern Cygwin (POSIX compatibility layer) and MinGW-w64 with the aim of better interoperability with native Windows software. It provides a bash shell, Autotools, revision control systems and the like for building native Windows applications using MinGW-w64 toolchains.

Installation

It is pretty straightforward. On the official page you have the step-by-step guide, but in case of troubles, you may check detailed install guide.

After installation and upgrading run in cmd console

C:\msys64\msys2_shell.cmd -mingw64

or simply just click on the Start menu “MSYS2 MinGW 64-bit” shortcut:

Then update packages:

pacman -Syu

Install only needed packages:

pacman --needed -S git mingw-w64-x86_64-gcc base-devel

Let’s build it!

Get the PostgreSQL sources. Here you have two options:
1. Download them from the official site.
2. Use git repository:

git clone git://git.postgresql.org/git/postgresql.git

Enter to the source folder:

cd postgresql

And run configure followed by make and make install:

./configure --host=x86_64-w64-mingw32 --prefix=/c/pgsql/ && make && make install

Here we tell our target machine will be 64-bit and we want our binaries to be copied to C:\pgsql\.

Conclusion

As you may see building PostgreSQL with MSYS2 for Windows is simple enough. But the main profit of such toolchain is building extensions for PostgreSQL. And there it shines in all its glory. Stay tuned.

The post Building PostgreSQL with MSYS2 and MinGW under Windows appeared first on Cybertec.

The following commit adds a new feature which is part of Postgres 11, and matters a lot for a couple of tools:

commit: e79350fef2917522571add750e3e21af293b50fe
author: Stephen Frost <sfrost@snowman.net>
date: Fri, 6 Apr 2018 14:47:10 -0400
Remove explicit superuser checks in favor of ACLs

This removes the explicit superuser checks in the various file-access
functions in the backend, specifically pg_ls_dir(), pg_read_file(),
pg_read_binary_file(), and pg_stat_file().  Instead, EXECUTE is REVOKE'd
from public for these, meaning that only a superuser is able to run them
by default, but access to them can be GRANT'd to other roles.

Reviewed-By: Michael Paquier
Discussion: https://postgr.es/m/20171231191939.GR2416%40tamriel.snowman.net

This is rather a simple thing: a set of in-core functions like using a hardcoded superuser check to make sure that they do not run with unprivileged user rights. For the last couple of releases, an effort has been made to remove those hardcoded checks so as one can GRANT execution access to a couple or more functions so as actions which would need a full superuser (a user who theoritically can do anything on the cluster and administers it), are delegated to extra users with rights limited to those actions.

This commit, while making lookups to the data directory easier, is actually very useful for pg_rewind as it removes the need of having a database superuser in order to perform the rewind operation when the source server is up and running.

In order to get to this state, one can create a dedicated user and then grant execution to a subset of functions, which can be done as follows:

CREATE USER rewind_user LOGIN;
GRANT EXECUTE ON function pg_catalog.pg_ls_dir(text, boolean, boolean) TO rewind_user;
GRANT EXECUTE ON function pg_catalog.pg_stat_file(text, boolean) TO rewind_user;
GRANT EXECUTE ON function pg_catalog.pg_read_binary_file(text) TO rewind_user;
GRANT EXECUTE ON function pg_catalog.pg_read_binary_file(text, bigint, bigint, boolean) TO rewind_user;

Once run, then this

In the previous post we introduced the PostgreSQL’s dependency system.

At first sight the implementation can look like a maze where the succession of relationships are not clear.

This post will try to give a practical example to show how pg_depend can act like an Ariadne’s thread in order to resolve the dependencies.

The scenario presented is very simple but can be used as a starting point for more complex requirements.

Hi. I work as a data architect in San Francisco and I’m auditing Dr. Jones class to stay up to date on the latest technologies and she mentioned you might be able to help me before I get too deep into the design of a new system.

I would be happy to help. Can you give me an overview of where you’re at?

Well my default was just to use Postgres. I had a few questions on what schema designs might make most sense.

Well I’m working with more interesting data architectures. Really getting excited about what’s possible with neomodern data architectures, they make it so my app devs can build any feature their hearts desire.

I thought your expertise used to be relational databases?

It was, but neomodern data architectures are better. Neomodern data architectures allow it so app devs can build any feature they like without having to think about data models. Really, it’s the future of databases.

Hmmm, my app is a pretty straightforward web app that allows salespeople to send campaigns and track metrics on the campaigns. My app should be fine in a Postgres database right?

That might work for you, but really, if you have to define a data model up front, then you’re limiting yourself.

How do you mean limiting? Can’t I just add new tables as I need them? Or add columns to existing tables?

Well you could, but meta document stores can take care of that for you

Meta document stores?

Yeah, you have a custom document for each data model, but then have a document store that auto parses each document and can tell you the structure without having to read the document itself

Oh nice, so I can have an index of all my data.

Exactly, and because you have a bunch of smaller document stores you can distribute them around the world and read the data from the nearest one

So reading from the nearest copy of data would be faster?

Well it’s supposed to be, but to make sure the data is correct you have to read from at least 3 nodes to get consensus

Consensus?

Yeah concensus. Since the underlying storage is all in blockchain, there has to be an agre

• Philadelphia 
• Ohio (in combination with Ohio Linux Fest) 
• South Africa 
• Seattle 
• Austin 
• Jersey City (PostgresConf US 2018) 
• Nepal 

PostgresConf US 2018

Logistics

• Days: 5, 2 for training, 3 for Breakout sessions and summits
• Official Attendance #: 601
• Content: Over 207 sessions submitted
• Sessions: Over 108 sessions provided 

Partner Support (Sponsors): 28

Diversity

Professional Growth

We held a Career and Talent Fair. A dozen companies were present to connect with potential employees.

We also held a surprisingly well attended speed mentoring

Continuing our series of PostgreSQL Data Types today we’re going to introduce the PostgreSQL Point type.

In order to put the Point datatype in a context where it makes sense, we’re going to download a complete geolocation data set and normalize it, thus making good use of both the normalization good practice and those other PostgreSQL data types we’ve been learning about in the previous articles of this series.

Buckle-up, this is a long article with a lot of SQL inside.

PostgreSQL query tuning is our daily bread at Cybertec, and once you have done some of that, you’ll start bristling whenever you see an OR in a query, because they are usually the cause for bad query performance.

Of course there is a reason why there is an OR in SQL, and if you cannot avoid it, you have to use it. But you should be aware of the performance implications.

In this article I’ll explore “good” and “bad” ORs and what you can do to avoid the latter.

A little sample schema

We’ll use this simple setup for demonstration:

CREATE TABLE a(id integer NOT NULL, a_val text NOT NULL);

INSERT INTO a
   SELECT i, md5(i::text)
   FROM generate_series(1, 100000) i;

CREATE TABLE b(id integer NOT NULL, b_val text NOT NULL);

INSERT INTO b
   SELECT i, md5(i::text)
   FROM generate_series(1, 100000) i;

ALTER TABLE a ADD PRIMARY KEY (id);
ALTER TABLE b ADD PRIMARY KEY (id);
ALTER TABLE b ADD FOREIGN KEY (id) REFERENCES a;

VACUUM (ANALYZE) a;
VACUUM (ANALYZE) b;

Suppose that we want to run queries with equality and LIKE conditions on the text columns, so we need some indexes:

CREATE INDEX a_val_idx ON a(a_val text_pattern_ops);
CREATE INDEX b_val_idx ON b(b_val text_pattern_ops);

Have a look at the documentation if you don’t understand text_pattern_ops.

The “good” OR

An OR is fine in most parts of an SQL query: if it is not used to filter out rows from your query result, it will have no negative effect on query performance.

So if your OR appears in a CASE expression in the SELECT list, don’t worry.

Unfortunately you usually find the OR where it hurts: in the WHERE clause.

The “bad” OR

Now for an example of an OR in a WHERE clause that is still pretty nice:

EXPLAIN (COSTS off)
SELECT id FROM a
WHERE id = 42
   OR a_val = 'value 42';

                        QUERY PLAN                         
-----------------------------------------------------------
 Bitmap Heap Scan on a
   Recheck Cond: ((id = 42) OR (a_val = 'value 42'::text))
   ->  BitmapOr
         ->  Bitmap Index Scan on a_pkey

With the addition of logical replication in Postgres 10, it’s natural to ask "what’s next"? Though not directly supported yet, would it be possible to subscribe two Postgres 10 nodes to each other? What kind of future would that be, and what kind of scenarios would be ideal for such an arrangement?

As it turns out, we already have a kind of answer thanks to the latency inherent to the speed of light: locality. If we can provide a local database node for every physical app location, we also reduce latency by multiple orders of magnitude.

Let’s explore the niche BDR was designed to fill.

What is Postgres-BDR?

Postgres-BDR is simply short for Postgres Bi-Directional Replication. Believe it or not, that’s all it needs to be. The implications of the name itself are numerous once fully explored, and we’ll be doing plenty of that.

So what does it do?

• Logical replication
• Multi-Master
• Basic conflict resolution (last update wins)
• Distributed locking
• Global sequences
• High latency replay (imagine a node leaves and comes back later)

The key to having Multi-Master and associated functionality is logical replication. Once we can attach to the logical replication stream, we just need a piece of software to communicate between all participating nodes to consume those streams. This is necessary to prevent nodes from re-transmitting rows received from another system and resolve basic conflicts.

Why Geographical Distribution?

Specifically:

• Local database instances
• Inter-node communication is high latency
• Eventual consistency is the only consistency (for now)
• That’s a Good Thing!(tm)

We don’t want latency between our application and the database, so it’s better to operate locally. This moves the latency into the back-end between the database nodes, and that means the dreaded "eventual consistency" model. It’s a natural consequence, but we can use it to our advantage.

Consider This

We have an application stack that operates in four locations: Sydney, Dubai, Dallas, and Tokyo.

We tried to get a data center in Antarctica, but t

plperl does not allow direct sub invocation, so the only way is to execute a query.

plperl: invoking other subroutines

The official plperl documentation shows you a way to use a subref to invoke code shared across different plperl functions via the special global hash %_SHARED. While this is a good approach, it only works for code attached to the hash, that is a kind of closure (e.g., a dispatch table), and requires each time an initialization of the %_SHARED hash since plperl interpreters does not share nothing across sections.

The other way, always working, is to execute a query to perform the SELECT that will invoke the function. As an example:

CREATE OR REPLACE FUNCTION plperl_trampoline( fun_name text ) RETURNS TEXT AS $PERL$ my ( $fun_name ) = @_; return undef if ( ! $fun_name ); elog( DEBUG, "Calling [$fun_name]" ); my $result_set = spi_exec_query( "SELECT $fun_name() AS result;" ); return $result_set->{ rows }[ 0 ]->{ result }; $PERL$ LANGUAGE plperl; 

so that you can simply do:

> select plperl_trampoline( 'now' ); plperl_trampoline ------------------------------ 2018-05-04 13:09:17.11772+02 

The problem of this solution should be clear: it can work only for a...

Recently I did some PostgreSQL consulting in the Berlin area (Germany) when I stumbled over an interesting request: How can data be shared across function calls in PostgreSQL? I recalled some one of the other features of PostgreSQL (15+ years old or so) to solve the issue. Here is how it works.

Stored procedures in PostgreSQL

As many of you might know PostgreSQL allows you to write stored procedures in many different languages. Two of the more popular ones are Perl and Python, which have been around for quite some time. The cool thing is: Both languages offer a way to share variables across function calls. In Perl you can make use of the $_SHARED variable, which is always there.

Here is an example:

CREATE OR REPLACE FUNCTION set_var(int)
RETURNS int AS $$
   $_SHARED{'some_name'} = $_[0];
   return $_[0];
$$ LANGUAGE plperl;

What the code does is to assign a value to some_name and returns the assigned value. Some other function can then make use of this data, which is stored inside your database connection. Here is an example:

CREATE OR REPLACE FUNCTION increment_var()
RETURNS int AS $$
   $_SHARED{'some_name'} += 1;
   return $_SHARED{'some_name'};
$$ LANGUAGE plperl;

This function will simply increment the value and return it. As you can see the code is pretty simple and easy to write.

Assigning shared variables

The following listing shows, how the code can be used. The first call will assign a value to the function while the second one will simply increment that value:

test=# SELECT set_var(5);
 set_var
---------
 5
(1 row)

test=# SELECT increment_var(), increment_var();
 increment_var | increment_var
---------------+---------------
             6 | 7
(1 row)

It is especially noteworthy here that the second column will already see the changes made by the first column, which is exactly what we want here.

Shared variables and transactions

When working with shared variables in PL/Perl or PL/Python you have to keep in mind that those changes will not be transactional as all the rest in PostgreSQL is. Even if you

Hey everyone! Just a quick note to let you all know that both CJ and myself are manning the PostgreSQL community booth at this year’s PyCon next week in Cleveland, OH.

The Python and PostgreSQL communities have a long history together, and we’re super excited to be able to represent PostgreSQL at the largest annual gathering of Pythonistas. If you can make the drive, come on up to Cleveland, say hi, and learn some Python!

https://us.pycon.org/2018/#!