Resolving the Fatal Python Error when using PyGreSQL

Szymon palucha

Follow

Dec 1, 2020 · 5 min read

This article is dedicated to explaining, reproducing and resolving the “Fatal Python Error: deallocating None” which can happen while using the PyGreSQL package to query PostgreSQL data. This error only occurs with very specific queries and when either querying large amounts of data or querying many times without restarting the script or server.

I’ve reported this bug on github and it should be resolved in PyGreSQL version 5.2.2 but in the meantime I will present two workarounds without the need to upgrade.

Understanding the error

In order to understand and resolve this error we first need to know a little bit about Python’s memory management.

Reference Counting Background

In a nutshell every variable in Python is a reference (a pointer) to an object and not the actual value itself. These references are stored in CPython (the original Python implementation using C) and can be accessed inside Python using the sys module. For example,

import sysa = [1, 2, 3]
print(sys.getrefcount(a))  # gives 2
b = a
print(sys.getrefcount(a))  # gives 3
c = [1, 2, 3]
print(sys.getrefcount(c))  # gives 2
del b
print(sys.getrefcount(a))  # gives 2

In the first line we create a list object and assign it to the variable a, creating a reference to this specific list. We can access this list’s reference count with sys.getrefcount(a). As explained in the documentation the reason we actually see a reference count of two is:

The count returned is generally one higher than you might expect, because it includes the (temporary) reference as an argument to getrefcount().

(Compare this with e.g.sys.getrefcount([1, 2, 3]) which would give 1)

In the next line, we assign a to a new variable b. This increases the reference count of the original list object to three. But if we instead make a new list object and assign it to a variable c, the reference count of c is two. This is because we know have two different list objects (even though they are identical in “value”).

If we now deleted the variable b we would delete one of the references and the reference count of a decreases to two. Deleting a would now deallocate this list object, releasing it from memory.

The same reference counting procedure applies with constants such as True, False and None. For instance

print(sys.getrefcount(None))  # gives some high number like 30757
a = None
print(sys.getrefcount(None))  # gives 30758

The reference count of None needs to always be greater than zero. If it ever reached zero it would be deallocated causing the fatal error.

If you want to find out more about memory allocation and garbage collection then the this post explains it quite well.

Reproducing the error

So the fatal error happens because there must be a reference counting bug in PyGreSQL’s implementation which causes None to be deallocated. The question is: under what conditions does this happen? After trying to reproduce this I found that it occurs when querying for columns that are of type citext array (citext is a case insensitive character string type).

Steps to reproduce

First we need to create some PostgreSQL data. Let’s create a relation called teams with an id column representing a team id, and a players column representing the list of players in the team. We will make the players column type a citext array. Inserting just one row will be sufficient to reproduce the bug. To create the data execute the following commands in PostgreSQL:

CREATE EXTENSION citext;  # If you don't already have citext
CREATE TABLE teams (id INT, players CITEXT[]);
INSERT INTO teams VALUES (1, '{"Alice", "Bob"}');

The first command is only needed if the citext extension doesn’t already exist. (Just to illustrate why using citext can be useful, you can execute the following command

SELECT * FROM teams WHERE players='{"Alice", "bob"}';

and you can see that this brings back the row even though we searched for “bob” instead of “Bob”.)

Now execute the following Python script

import pgdb
import sys


def execute_query(query):
    cursor = CONNECTION.cursor()
    cursor.execute(query)
    rows = cursor.fetchall()
    cursor.close()
    return rows


def reproduce_bug():
    print(sys.getrefcount(None))
    for i in range(10000):
        execute_query('SELECT players FROM teams')
        print(sys.getrefcount(None))


if __name__ == '__main__':
    info = {'user': 'paluchasz', 'host': 'localhost', 'database': 'paluchasz', 'port': 5432, 'password': 'abcd'}
    CONNECTION = pgdb.connect(user=info['user'], host=info['host'], database=info['database'], port=info['port'],
                              password=info['password'])
    reproduce_bug()

Note you can get your own connection details with the \conninfo PostgreSQL command and you can change your password with \password. In the script execute_query is just a helper function which executes a query in a transaction. To reproduce we need to query the players column from teams many times without restarting the script. This happens in the reproduce_bug function. Running the script you should see the reference count of None steadily decreasing and eventually causing the fatal error. The last few output lines could look like

8
7
6
5
4
3
2
Fatal Python error: deallocating None

Now that we reproduced the bug, let’s look at how to fix it!

Resolving the issue

One way to resolve the issue is to manually increase the reference count whenever it goes down below some number e.g a thousand (see this post). For example,

import ctypes

_incref = ctypes.pythonapi.Py_IncRef
_incref.argtypes = [ctypes.py_object]
_incref_restype = None
def increase_ref():
    for _ in range(5000):
        _incref(None)
def fix_bug():
    print(sys.getrefcount(None))
    for i in range(10000):
        execute_query('SELECT players FROM teams')
        if sys.getrefcount(None) < 1000:
            increase_ref()
        print(sys.getrefcount(None))

However, this is a bit messy and probably not recommended. As it turns out there is a much simpler solution and it only involves changing our query by casting the players column to a text array instead:

def fix_bug_v2():
    print(sys.getrefcount(None))
    for i in range(10000):
        execute_query('SELECT players::text[] FROM teams')
        print(sys.getrefcount(None))

If you try running this instead you will see that the reference count stays constant!

Conclusions

Hopefully by now if you encountered this problem while using the PyGreSQL package this article has helped you resolve it. You also either learnt or refreshed your memory about Python’s reference counting. If you had the same problem but with different Postgres data then leave a comment and I will add it into the article! Thanks for reading.