Adding log10() to PostgreSQL

December 12, 2018
Hacking PostgreSQL

I’m pretty excited because I’m writing a patch that will add functions to PostgreSQL, so I’m hacking into Postgres.

The first function that I’d like to add is the log10 one. It’s needed for SQL:2016 compliance. PostgreSQL already implemented logarithm in base 10 (it’s called log), so I just need to add an alias to point to the function that was implemented in Postgres.

What to change

I first decided to take a tour of every file I’ll need to change and I came up with:

I decided not to change the regression tests as it’s only an alias. If log is good, log10 will be.

Let’s go


First, I changed the documentation. That way, I’ll be sure of what I wanted to do. It helped me understand I’ll have 2 functions : one that will take a number and another one that will take a double precision.

I decided to add that new log10 function in a new row just below the log() function.

        <literal><function>log10(<type>dp</type> or <type>numeric</type>)</function></literal>
       <entry>(same as input)</entry>
       <entry>base 10 logarithm</entry>

And that was done for documentation.


Now, I could really add the function to Postgres. The file src/include/catalog/pg_proc.dat just stores data for the future catalog of postgres functions.

I needed unused oids to be able to add my function to postgres catalog. Call me naive, I thought items where added by ascending order of oid so I scaned the file looking for the next free oid after my two log functions and added my 2 functions.

That’s it! Patch done! Time to compile!

Duplicate OIDs detected:
found 2 duplicate OID(s) in catalog data

What ? Ok, functions are not stored in ascending order of oid in that file. And sure enough, those oids where already taken. So how could I find which ones are free? I searched for a readme somewhere. Nope, there was nothing.

At the last moment, when I was becoming desperate, I did a simple ls on the directory. And then a bright green file (green means executable in my shell) ringed a bell in my mind. That beautiful file was called `unused_oid.

I executed that file and found out the next oids I was looking for were 1364 and 2023.

I could write my patch!

{ oid => '1364', descr => 'base 10 logarithm',
  proname => 'log10', prorettype => 'float8', proargtypes => 'float8',
  prosrc => 'dlog10' },
{ oid => '2023', descr => 'base 10 logarithm',
  proname => 'log10', prolang => '14', prorettype => 'numeric',
  proargtypes => 'numeric', prosrc => 'select pg_catalog.log(10, $1)' },

Can't connect to Postgres

March 30, 2020
Administration PostgreSQL Troubleshooting

Restoring Through timeline change

March 24, 2020
Installation PostgreSQL

What's wrong with Postgres?

March 22, 2020
Administration PostgreSQL Troubleshooting