PostgreSQL – Handling the Unix Epoch Problem

dbmspostgresql

I was wondering if Postgres will be handling the unix epoch problem coming in 2038? I have read about this and am wondering.

It's about a productivity thing obviously because it is so far away, but I am curious.

Best Answer

If you look at https://www.postgresql.org/docs/11/functions-datetime.html in the documentation it displays data about the to_timestamp function that returns a timestamp with time zone value from an epoch value. Complete description is: "Convert Unix epoch (seconds since 1970-01-01 00:00:00+00) to timestamp"

The input type of the function is double precision.

From https://www.postgresql.org/docs/11/datatype-numeric.html you can see that double precision is defined as 8 bytes with 15 decimal digits precision. A little down below you can read:

The double precision type typically has a range of around 1E-307 to 1E+308 with a precision of at least 15 digits.

So 1E+308 seconds after 1970-01-01 should carry you far after 2038...

In fact if you look at https://wiki.postgresql.org/wiki/TodoDone84, which lists all features that have been done in PostgreSQL 8.4 you can see:

Completed item: Extend timezone code to allow 64-bit values so we can represent years beyond 2038

Related Solutions

PostgreSQL – How to Restore Superuser Account

Superuser rights aren't in any config file, they're part of the pg_catalog.pg_authid database table, which is shared between all databases in a PostgreSQL install.

You need to stop the PostgreSQL server, then restart it in single user mode, where it's always running as superuser.

There, you can ALTER USER myuser SUPERUSER to grant superuser rights. Exit single user mode, and start the server back up.

The exact details are a little distro and version dependent, but something like:

service postgresql stop
sudo -u postgres postgres --single /var/lib/pgsql/9.1/main/data

ALTER USER postgres SUPERUSER;

(control-D to exit)

service postgresql start

On some systems you might need to use an explicit path to the postgres executable, like /usr/pgsql-9.1/bin/postgres. Again, paths may vary based on version and distro.

Postgresql – A (simple?) solution to the “logical decoding / sequence” problem in Postgres

Your proposed solution is fine so long as you only need to track inserts and you don't need to be scalable. You can use an id-generator table you query with UPDATE ... RETURNING to implement it very simply; search for "gapless sequence".

Since it destroys write concurrency, it'll only be able to grow to a limited point.

It also won't help you track updates or deletes. Unless you start recording them with triggers that insert into a queue table. At which point you might as well use logical decoding. Or Londiste.

Logical decoding is indeed channel based. You can't step in at some arbitrary point. It's designed to ensure that you receive every change exactly once. You can skip over changes, though, or filter them at the output plugin level. The only restrictions really are that:

you cannot "rewind" once you confirm receipt of changes (but you can intentionally replay the same changes multiple times before you confirm; see the peek functions)
the initial slot position is always the current server's insert position or slightly after it. You can't create a slot that starts in the past.

Personally I think you should just be using one of the json based logical decoding plugins for this, but I don't know the full picture of what you're trying to achieve.

Best Answer

Related Solutions

PostgreSQL – How to Restore Superuser Account

Postgresql – A (simple?) solution to the “logical decoding / sequence” problem in Postgres

Related Question