Waiting for 8.4 – pg_conf_load_time, time-related generate_series and enum values in \dT+

3 new functionalities from 3 people:

First, patch from George Gensure, committed by Tom Lane.

New function – pg_conf_load_time() returns timestamp when PostgreSQL reread its configuration last time. In trivial situation it will be the same as pg_postmaster_start_time(), but if someone issued

pg_ctl reload

Then it will differ:

=> psql -c "select pg_postmaster_start_time(), pg_conf_load_time()"
   pg_postmaster_start_time    |       pg_conf_load_time
-------------------------------+-------------------------------
 2008-05-05 09:03:23.651523+02 | 2008-05-05 09:03:23.306127+02
(1 ROW)
=> pg_ctl reload
server signaled
=> psql -c "select pg_postmaster_start_time(), pg_conf_load_time()"
   pg_postmaster_start_time    |       pg_conf_load_time
-------------------------------+-------------------------------
 2008-05-05 09:03:23.651523+02 | 2008-05-05 09:19:27.009284+02
(1 ROW)

Second patch, also committed by Tom, was written by Hitoshi Harada.

It adds time-related generate_series() functions.

How it works? Well, it's pretty simple:

# SELECT * FROM generate_series(now(), now() + '2 days'::INTERVAL, '6 hour'::INTERVAL);
       generate_series
------------------------------
 2008-05-05 07:22:34.53166+00
 2008-05-05 13:22:34.53166+00
 2008-05-05 19:22:34.53166+00
 2008-05-06 01:22:34.53166+00
 2008-05-06 07:22:34.53166+00
 2008-05-06 13:22:34.53166+00
 2008-05-06 19:22:34.53166+00
 2008-05-07 01:22:34.53166+00
 2008-05-07 07:22:34.53166+00
(9 ROWS)

As You can see it takes 3 arguments – when to start, when to end, and what is the stepping interval.

Of course You could do the same thing without time-related generate_series, but new syntax is so much nicer.

Last patch was written by David Fetter, and committed by Andrew Dunstan.

Now, the functionality it adds is pretty basic, but apparently there was no simple way to get it before it. Simple – means without touching system catalog.

What the patch does? It lists all possible ENUM values in \dT+ output:

# CREATE TYPE test AS enum ('lorem', 'ipsum', 'dolor', 'sit',
    'amet', 'consectetuer', 'adipiscing', 'elit', 'pellentesque',
    'vel', 'hendrerit', 'wisi', 'nulla', 'cursus');
CREATE TYPE
# \dT+ test
                        List OF DATA types
 Schema | Name | Internal name | SIZE |   Elements   | Description
--------+------+---------------+------+--------------+-------------
 public | test | test          | 4    | lorem        |
                                      : ipsum
                                      : dolor
                                      : sit
                                      : amet
                                      : consectetuer
                                      : adipiscing
                                      : elit
                                      : pellentesque
                                      : vel
                                      : hendrerit
                                      : wisi
                                      : nulla
                                      : cursus
(1 ROW)

It's pretty good to have this information, but I personally think that until there will be sane way to add and remove elements from ENUM – functionality of this data type is very limited. So limited that I honestly can't imagine myself any situation in which I would use it.

5 thoughts on “Waiting for 8.4 – pg_conf_load_time, time-related generate_series and enum values in \dT+”

  1. The second patch is very nice to have. Since 8.0 I’ve been declaring such functions manually when I’ve needed them, and it always seemed to be something that should have been included in Pg as standard.

  2. @depesz: “until there will be sane way to add and remove elements from ENUM – functionality of this data type is very limited. So limited that I honestly can’t imagine myself any situation in which I would use it.”

    You can create a new enum type and cast your columns to it (and recreate any functions using this type, which might get ugly).

    create type e1 as enum (‘a’, ‘b’, ‘foo’);
    create type e2 as enum (‘a’, ‘b’, ‘c’, ‘bar’);
    create table x ( col e1 );
    alter table x alter column col type e2 using col::text::e2;

    …and will correctly blow up if the data doesn’t fit the second enum (ie, if you have any ‘foo’ values there).

    Maybe not the prettiest solution, but works.

  3. @Radek Zielinski:
    It’s great to have Your comment here, but trust me – doing “alter table” to fix enum is not acceptable.
    It gets exclusive lock on whole table for the time of processing it. Just imagine multi-hour downtime of production site, and You’ll understand why it’s not cool.
    There are other ways that “work” as well – like modifying pg_catalog, but it’s also not acceptable in production environment.

  4. @depesz: Just out of curiosity, why do you consider an insert to pg_enum (yes, I know it doesn’t solve the sorting problem) unacceptable?

  5. @Radek Zielinski:
    Because pg_enum is in system catalog. I strongly believe that user shouldn’t ever need to touch it.

Comments are closed.