Learning about PostgreSQL Temporal Tables

Configuration management (CM) can be hard! That is especially so when working with a dynamic, complex project with multiple moving parts and critical integration requirements. One of the many problems is keeping track of what changed, and when!

This sounds like a problem that “temporal tables” was made to solve. Temporal tables, at their core, add a time dimension to your data. They allow you to store not just the current state of information, but also how it changed over time. This is done by associating each row with a specific time period, effectively creating a history of changes.

Creating and maintaining temporal tables:

While the specifics can get a bit involved, here’s a simplified overview:

1. Table creation: You’ll typically define your table with specific columns to represent the valid time period (for application-time) or use system columns for system-time.  
2. Data insertion and updates: When inserting or updating data, you’ll need to manage the time periods associated with each row.  
3. Temporal queries: Use specialized SQL clauses and functions (e.g., FOR SYSTEM_TIME AS OF, FOR VALID_TIME AS OF) to query data at specific points in time or over time ranges.

This post is a work in progress. The next steps including reviewing existing data; reading (an understanding!) the technical documentation; and some experimentation. Stand by for updates!

Author’s note: This blog post is NOT original work. Extensive exploitation of Google, openAI, and PostgreSQL documentation got me this far. Please do not rely on any information here without rigorously testing it. In addition, I very highly recommend the web site, https://pgdoc.link, by Hugo Lubaczewski (https://gitlab.com/depesz). It is an invaluable resource for direct links to critical information.

Resources

Find the tables that have no timestamp

CREATE OR REPLACE FUNCTION find_tables_without_timestamp()

RETURNS TABLE (x_name TEXT)

LANGUAGE plpgsql

AS $$

BEGIN

    RETURN QUERY

    select i.table_name::text AS x_name

    from information_schema.columns i

    where i.table_schema = ‘public’

    group by i.table_name

        HAVING NOT exists (

            select 1

            from information_schema.columns c2

            where c2.table_name = i.table_name

            and c2.data_type in (‘timestamp without time zone’, ‘timestamp with time zone’)

        );

END;

$$;

Response to PGSQL Phriday #017

I read a challenge from Ryan Booz to discuss the topic, Artificial Intelligence (AI) and PostgreSQL, this morning and decided to crawl out from under my rock and opine in public.

I am still a command-line type of guy. I much prefer vi to any other utility to create scripts. I am a devotee to the documentation. Once upon a time, there was a small, boutique movement called the “Battle Against Any Guess” or BAAG. I admired those guys. I think I even sent money in support of them.

So, what do I think of AI and Postgres?

I think that AI has a place in the daily routine of database users, developers, and administrators. There is a superabundance of knowlege surrounding all database topics. Many of the topics are absolutely essential to successfully maintain and improve database performance. The sad fact is I cannot remember all of them. That is when I turn to AI. Typically my interactions with AI take the form of “Where in postgres documentation is the correct syntax to create a sub-partition of a postgres table?”

Why do I do that? Because it’s faster than thumbing through the docs, searching the correct reference. Does it always work for me? Not always. Maybe eight times out of ten, but not always.

Why do I think that happens? If I understand these “large language models” correctly, AI conducts searches among the myriads of responses to my particular question, and then summarizes the results according to whatever sifts to the top of the heap. The “sifting” is highly dependant on the experience, expertise, and biases of the writers of the algorithms.

In my youth, I worked for a man whose approval I sought. Once, I heard him say, “Expediency is the enemy of integrity.” Those who depend exclusively on AI should remember this phrase.

Regarding Ryan’s bonus, “Who can create the coolest elephant image with AI to match their post?” I cannot (yet). But I will support and admire anyone who can whip up an image based on Ganesha, but cast as a seductive androgynous creature, bearing in his/her hands icons for Perfect Code, five nines performance, excessive salaries, and obsequious program managers.

Beginning with PostgreSQL

A new effort: becoming a PostgreSQL database administrator. The hardest part for me is learning the “new” language. PostgreSQL has a number of command line shortcuts to speed up the DBA’s work. This is a beginning of describing those conventions.

\o FILENAME
— Send the output of the query to FILENAME
— the next occurrence of \o stops sending the output
— Very useful for building dynamic SQL

\i FILENAME
— use the contents of FILENAME as a PostgreSQL command

\h
— help on the individual sql commands

\?
— help on the shortcuts, or backslash commands

— LibPQ is the connection string; a URI with specific structure and members, e.g.
postgresql://username@host:port/database

\x
— for extended display. To turn extended display off, enter \x a second time

\t
— “tuples only”. That is, show only the rows, not the column headings or count of rows. To turn off “tuples only”, enter \t a second time

\r
— “reset”. That is, clear the buffer

\d
— display tables. Most useful when you constrain this with a table name. Examples follow:
\d myschema.employees
\d myschema.*
\d myschema.emp*

This particular post will be updated as I encounter further shortcuts. Standby for more details

Correcting a Faulty Application Installation

Note: This is really a set of notes so I can think things through accurately. It might be instructive to someone else who has similar issues.

Background: Newly assigned to an IT program as a database resource, I have been allocated an AWS EC2 VM (m5.large, 100GiB size, CentOS 7). Now it is time to configure the VM, install and configure the tools I will need to collaborate in the DevOps environment with the new teammates. Git, Jira, Confluence, Jenkins all seemed to install correctly and have been appropriately configured. VS Code 1.56.1 installed, but failed to actually run. The issue was a missing library (gcc 5, including CXXABI_1.3.9). VS Code has a lot of customers. The more recent updates require this module to work with UBUNTU (and I assume other linux flavors). CentOS may be considered “moribund”. The workaround for VS Code is to install the version 1.51.1. The next step is install pgAdmin!

Following local instructions, I install postgreSQL 11. No apparent issues.

Following local instructions, I install pgadmin4-web. yum reports, “Package pgadmin4-web-5.2.1.el7.noarch already installed and latest version. Nothing to do”

Following local instructions, sudo su. This reports an error, “ABRT has detected 1 problem. For more info run: abrt-cli list –since 1621010402”. Running the abrt-cli command, I learn that “xdrp-keygen killed by SIGABRT“. I do not think this is a factor and continue.

The next command in the instructions is sudo python3 /usr/pgadmin4/web/pgAdmin4.py &. That returns a ModuleNotFoundError: No module named ‘cheroot’. This occurred in the pgAdmin4.py operation at line 16 in module from cheroot.wsgi import Server as CherootServer.

Next course of action: If python cannot find cheroot.wsgi, I’ll try to load that myself. The first attempt failed, using pip3 install cheroot-wsgi But error messages indicated that locations in the directory were not writeable, which induced me to try sudo pip3 install cheroot-wsgi and that worked.

Overall, the key to the end of this part of the story is sudo pip3 install cheroot-wsgi.

An unfortunate series of events …

So I have recently started in a new position. The first few weeks after joining a team are hectic. There is a lot to learn about the team, the project, the processes and customs of the new environment, et cetera.

One of the on-boarding tasks is to customize the AWS-provided, Centos 7 virtual server to conform with the recommended development configuration. That customization includes the latest stable edition of google-chrome AND Visual Studio Code (VSC). Here is where it gets complicated.

The latest version of VSC doesn’t work on Centos 7. It was broken in a previous version, when it was upgraded to work with the latest versions of Ubuntu Linux. I assume there was not effort to create a Centos version because Centos has been perceived to be moribund.

The workaround is to use VSC version 1.51.1, the last version to work on Centos 7. So far, so good. But subsequent steps require install of google-chrome. And when yum begins the transaction to install, it uncovers a dependency on VSC 1.56.1. Completing the install of google-chrome seems to trigger the undesired upgrade of VSC 1.51.1 to VSC 1.56.1. And that does not run on Centos 7.

An alternative course of action might be to install the libraries for gcc 5. But as I said, I’m the new guy around here. I am unwilling (and unable) to make configuration changes to this virtual server without the consequences. Should anyone have any idea how to resolve this, I would grateful for your help.

On voting

I am constantly bemused by the ever-increasing number of people who feel that it is their God-given right to tell me what to do and how to do it. Nay, they feel it is their absolute duty to do so. And they insist they get paid for the privilege. There is little I can reasonably do about it.

At least, with a vote, I can try to constrain them to the people who share the same values I do.

But it happens, all too frequently, that the people I vote for fail to win.

Clearly, I must work harder to persuade more of my fellows to my point of view.

Just Trying to Keep Up

There are times when you just don’t know what you don’t know. Every day the universe expands its horizon, usually because I step over the edge. Typically, I stumble across a new feature that I goggle at, and wish that I had the time to dive down that particular rabbit hole.

So, today, I start mapping the rabbit holes, with the fond hope that I will remember to come back to them when some segment of empty time becomes available. Here’s the list:

• Jupyter notebooks for powershell/.NET
• PowerShell can access and manage Streams!
• dbatools.io can remove Orphaned users
• VMWare has a desktop application

I will update this list as I run into more topics to explore.

PowerShell Remoting

So, you’ve got a new laptop? Congratulations!

Do you need to look at or get files from the old laptop? No problem! We’ll just fire up PowerShell and do a little remote computing to salvage what’s necessary from your previous work.

$cred = Get-Credential
$so = New-PSSessionOption -NoMachineProfile 
New-PSSession -SessionOption $so -Credential $cred -ComputerName "Server1"

What? You say you get an error message?

Set-WSManQuickConfig : <f:WSManFault xmlns:f="http://schemas.microsoft.com/wbem/wsman/1/wsmanfault" Code="2150859113"
Machine="localhost"><f:Message><f:ProviderFault provider="Config provider"
path="%systemroot%\system32\WsmSvc.dll"><f:WSManFault xmlns:f="http://schemas.microsoft.com/wbem/wsman/1/wsmanfault"
Code="2150859113" Machine="win81"><f:Message>WinRM firewall exception will not work since one of the network
connection types on this machine is set to Public. Change the network connection type to either Domain or Private and
try again. </f:Message></f:WSManFault></f:ProviderFault></f:Message></f:WSManFault>
At line:1 char:1
+ Set-WSManQuickConfig -Force
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Set-WSManQuickConfig], InvalidOperationException
    + FullyQualifiedErrorId : WsManError,Microsoft.WSMan.Management.SetWSManQuickConfigCommand

You can fix that.

Set-Item WSMan:\localhost\Client\TrustedHosts -Value *

At least that’s what worked for me. Your mileage may vary.

SQLCredentials

The transition from “old guy who’s an Oracle DBA” to “new old guy who’s a SQL Server AND Oracle DBA” continues, slowly … oh so painfully slowly. One of the complications is adopting Microsoft tools with all the complexity and that come with them … and putting aside the familiar Linux/Unix/Solaris tools like bash.

For me, the premier Microsoft tool of choice is PowerShell. That, in itself, is a long slog. There is a lot to learn, and the PowerShell community has techniques to accomplish tasks that I am learning, one by one.