Remote Desktop Multi-Monitor

With everybody working from home, you may wish have your multi monitor setup at work on your multi monitor setup at home.

The old way to do this is to set the height and width of your home setup to be the size of both your screens combined. Lets say you have two monitors that are 1920W x 1080H. You would then set your width to 1920*2=3840 and your height to 1080. The problem with this setup happens when you maximize an application, then it maximizes it across both screens.

psqlODBC finally fixed for 32/64bit

At work the finance department likes Excel and uses the ODBC drivers to get data from PostgreSQL. This worked just fine when we had 32-bit Office, but when we switched to 64-bit Office, the documents would no longer work due to the fact that the 32-bit and 64-bit drivers were named differently. Historically if you used psqlODBC on a 32bit machine (PostgreSQL Unicode) and then used the same file on a 64bit machine (PostgreSQL Unicode(x64)).

Minimal wal_level optimization can cause slowdowns

We found that using minimal WAL files together with the CREATE TABLE and COPY commands inside a transaction causes a 10x to 300x slowdown, whereas the PostgreSQL code and docs say this will make the commands faster due to optimizations that writes the table directly bypassing the WAL files. The slowdown is amplified by the amount of shared buffers due to the fact that the FlushRelationBuffers function sequentially searches the entire shared buffers.

Slow Queries and the AutoVacuum/Analyze System

I was recently asked by another PostgreSQL DBA about what could be causing queries to run slow while their application is running but then run fast when the application is stopped. While I have given several presentations on this subject, each one has been on a specific cause and not talking about all of them together. I decided I would compile them into a list on this page.

How to read new records

What you thought you knew about copying new records from a table with a serial id is all wrong once you have multiple writes happening to your database inside transactions. We ran across into a situation with a MS SQL where we missed getting some records from a table that is appended only. There is a serial primary key and we would grab any new serials since the last grab of data. We found that we missed some records due to the inserts being performed by more than one thread. So developers asked how could we prevent this problem with PostgreSQL.

PostgresConf Silicon Valley

I will be presenting on Viewing data at the intersection between roles at the PostgresConf Silicon Valley which is being held October 15 - 16, 2018 at the Hilton San Jose. The full schedule of the conference is available online.

How to restore a database with check constraints

I have found an edge case where pg_dump and pg_restore fails to restore databases with check constraints that points to data in another table that is restored after the table containing the check constraint. This has been tested and found to be a problem on PostgreSQL 9.5.2, 10.3 and 10.4.

In this how to, we will go over the following information:

Viewing data at the intersection between roles

We ran across a use case where we needed to restrict people's access to the data by requiring them to belong to 2 or more groups. PostgreSQL by default does not support this. I have worked up several way to be able to perform this action, but as always, there are pro's and con's to each method. In our use case, we wanted the user to SELECT on a table and return an error if the user did not have the correct rights instead of an empty table. Ideally, a user without the correct rights would see an error even if there was no data in the table.

Canceling authentication due to timeout aka Denial of Service Attack

UPDATED 2/6/2020

This has been fully fixed with PostgreSQL 12. It was such a big fix that the full fix was split between Postgres 11 and Postgres 12.

The Postgres Security team has decided not to call this a security bug, because if they did so, it would have to be back patched to all currently suported Postgres versions. I personally think it would be better to back patch this fix, but since they have not, this is a big reason to upgrade to Postgres 12.

UPDATED 07/30/2018

Subscribe to Blog