waiting for 8.4 – select * from depesz;

another new, cool feature commited by tom lane: “Support statement-level ON TRUNCATE triggers."

original patch was submitted by simon riggs, and tom commited it today/yesterday (depending on time zone).

commit log can be found in pgsql-committers archives.

patch is quite large, it modified 23 files.

functionality seems to be pretty obvious: you can create triggers called when “truncate" will happen.

two benefits that i can think of right now are:

slony will be able to work despite doing truncate on database (slony is just an example, it is equally important to any other replication system based on triggers)
auditing software (think table_log will be able to log data about truncation

as for example usage, let's write a trigger that will backup truncated tables (just in case we'd need to get the data back).

sample data structure:

CREATE TABLE users (
    id       SERIAL PRIMARY KEY,
    username TEXT NOT NULL DEFAULT '' UNIQUE,
    password TEXT
);

now trigger:

CREATE OR REPLACE FUNCTION trg_truncate() RETURNS TRIGGER AS
$BODY$
DECLARE
    new_table_name TEXT;
BEGIN
    new_table_name := 'backup_' || TG_TABLE_NAME || '_' || EXTRACT(epoch FROM timeofday()::TIMESTAMP);
    EXECUTE 'CREATE TABLE ' || quote_ident(new_table_name) || ' AS SELECT * FROM '|| quote_ident(TG_TABLE_NAME);
    RETURN NULL;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER trg_truncate BEFORE TRUNCATE ON users FOR EACH STATEMENT EXECUTE PROCEDURE trg_truncate();

now, let's test the thing:

# INSERT INTO users (username) VALUES ('test'),('depesz'),('xxx');
INSERT 0 3

data inserted, which can be checked:

# SELECT * FROM users;
 id | username | password
----+----------+----------
  1 | test     | [NULL]
  2 | depesz   | [NULL]
  3 | xxx      | [NULL]
(3 ROWS)

quick glance at list of tables:

# \dt
        List of relations
 Schema | Name  | Type  | Owner
--------+-------+-------+--------
 public | users | table | depesz
(1 row)

nothing surprising. only 1 table, our testbed 🙂

now, for the real work:

# TRUNCATE users;
TRUNCATE TABLE

data should be lost. is it?

# SELECT * FROM users;
 id | username | password
----+----------+----------
(0 ROWS)

what about table list?

# \dt
                    List of relations
 Schema |             Name              | Type  | Owner
--------+-------------------------------+-------+--------
 public | backup_users_1206737561.90902 | table | depesz
 public | users                         | table | depesz
(2 rows)

ok, we have new table. and it's content?

# SELECT * FROM "backup_users_1206737561.90902";
 id | username | password
----+----------+----------
  1 | test     | [NULL]
  2 | depesz   | [NULL]
  3 | xxx      | [NULL]
(3 ROWS)

everything worked as planned. another great feature in upcoming release.

4 thoughts on “waiting for 8.4”

Hey, you can’t await 8.4, right?

Btw.: Thank you for the nice articles.

@Andreas ‘akretschmer’ Kretschmer:
i’ve been using cvs-head builds since 7.something (on my development machine, not on production servers), so it’s not very strange for me to do cvs up; make every 2-3 days.

i just decided, that since i have pg-related blog, i will write about new features in 8.4, so others will get some idea about what to expect from new version.

usually when new version reaches beta and/or prerelease stage, a lot of people write about what’s new. but they usually cover only the “big” changes. i’ll try to write about every new feature, so when it will be finally released, and somebody will be asked “whether we should upgrade to 8.4”, he will be able to simply point asker to https://www.depesz.com/tag/84/ to get a quick overview of what’s new 🙂

I can’t wait to take Postgresql 8.3.x for a spin. There’s a lot of buzz around the Net about this version that promises something for everyone.

@MaxTheITpro:
so why dont you? i mean – 8.3 is released. and there is even 8.3.1 already there.

besides – take a note that i’m commenting what’s *not* in 8.3 – these posts are for 8.4 features.

Comments are closed.