If mtime is supposed to track modified time, then use triggers (with example code)

[mw44118]

I see tables with a ctime timestamp and an mtime timestamp column. I'm guessing ctime is meant to track "created time" for that row, and mtime is meant to track the time that row was modified.

I prefer to use "inserted" and "updated" for these concepts, but that's not really important.

But right now, mtime is kind of useless unless app code is setting it on every update.

Anyhow, here's an example of how to use a trigger on a table to set a column like mtime (except I'm calling it "updated" but you can use your imagination):

create table fibityfoo
(                                                 
    title text primary key,    
    description text,
    inserted timestamp not null default now(),
    updated timestamp
);                               

create or replace function set_updated_column ()
returns trigger                                                       
as  
$$                      

begin                        

    NEW.updated = now();
    return NEW;

end;
$$                                            
language plpgsql;    

create trigger fibityfoo_set_updated_column
before update                            
on fibityfoo
for each row
execute procedure set_updated_column();

Now, if you add another table, you don't need to redefine the function set_updated_column, but you MUST add the trigger to run set_updated_column on every update.

Here's an example:

create table geronimo
(
    title text primary key,
    description text,
    inserted timestamp not null default now(),
    updated timestamp
);

create trigger geronimo_set_updated_column
before update                            
on geronimo
for each row
execute procedure set_updated_column();

I hope that helps!

[chadwhitacre]

Thanks @mw44118! I've added this to the current Infrastructure milestone.

For tables that have a ctime and an mtime, we actually only ever insert into that table. An example would be the tips table:

d5ar5ri3pht6ti=> \d tips
                                   Table "public.tips"
┌────────┬──────────────────────────┬───────────────────────────────────────────────────┐
│ Column │           Type           │                     Modifiers                     │
├────────┼──────────────────────────┼───────────────────────────────────────────────────┤
│ id     │ integer                  │ not null default nextval('tips_id_seq'::regclass) │
│ ctime  │ timestamp with time zone │ not null                                          │
│ mtime  │ timestamp with time zone │ not null default now()                            │
│ tipper │ text                     │ not null                                          │
│ tippee │ text                     │ not null                                          │
│ amount │ numeric(35,2)            │ not null                                          │
└────────┴──────────────────────────┴───────────────────────────────────────────────────┘

Every time a user sets or resets a tip, we INSERT into that table. Basically that table is unique on (tipper, tippee, mtime) (though we don't actually have that constraint on the table; we probably should), and ctime is denormalized for easy reads; it is equivalent to the oldest mtime for a given (tipper, tippee). We use COALESCE to carry forward the ctime. Here's the SQL:

            INSERT INTO tips
                        (ctime, tipper, tippee, amount)
                 VALUES ( COALESCE (( SELECT ctime
                                        FROM tips
                                       WHERE (tipper=%s AND tippee=%s)
                                       LIMIT 1
                                      ), CURRENT_TIMESTAMP)
                        , %s, %s, %s
                         )
              RETURNING ( SELECT count(*) = 0 FROM tips WHERE tipper=%s )
                     AS first_time_tipper

We're using this pattern in a couple places in the app. Now that postgres.py is out the door (#1320) I want to write docs on our schema (#1272) and clean it up in the process. I want to abstract what we can of this pattern and others.

[mw44118]

So will mtime always match ctime?

On Tue, Sep 24, 2013 at 10:15 AM, Chad Whitacre [email protected]:

Thanks @mw44118 https://github.com/mw44118! I've added this to the
current Infrastructure milestone.

For tables that have a ctime and an mtime, we actually only ever insert
into that table. An example would be the tips table:

d5ar5ri3pht6ti=> \d tips
Table "public.tips"
┌────────┬──────────────────────────┬───────────────────────────────────────────────────┐
│ Column │ Type │ Modifiers │
├────────┼──────────────────────────┼───────────────────────────────────────────────────┤
│ id │ integer │ not null default nextval('tips_id_seq'::regclass) │
│ ctime │ timestamp with time zone │ not null │
│ mtime │ timestamp with time zone │ not null default now() │
│ tipper │ text │ not null │
│ tippee │ text │ not null │
│ amount │ numeric(35,2) │ not null │
└────────┴──────────────────────────┴───────────────────────────────────────────────────┘

Every time a user sets or resets a tip, we INSERT into that table.
Basically that table is unique on (tipper, tippee, mtime) (though we
don't actually have that constraint on the table; we probably should), and
ctime is denormalized for easy reads; it is equivalent to the oldest mtimefor a given (
tipper, tippee). We use COALESCE to carry forward the ctime. Here's the
SQLhttps://github.com/gittip/www.gittip.com/blob/master/gittip/models/participant.py#L330-340
:

        INSERT INTO tips
                    (ctime, tipper, tippee, amount)
             VALUES ( COALESCE (( SELECT ctime
                                    FROM tips
                                   WHERE (tipper=%s AND tippee=%s)
                                   LIMIT 1
                                  ), CURRENT_TIMESTAMP)
                    , %s, %s, %s
                     )
          RETURNING ( SELECT count(*) = 0 FROM tips WHERE tipper=%s )
                 AS first_time_tipper

We're using this pattern in a couple places in the app. Now that
postgres.py is out the door I want to write docs on our schema and clean it
up. I want to abstract what we can of this pattern and others.

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/1498#issuecomment-25007441
.

W. Matthew Wilson
[email protected]
http://tplus1.com

[chadwhitacre]

No, it looks like this:

┌───────────────────────────────┬───────────────────────────────┬────────┬────────┬────────┐
│             ctime             │             mtime             │ tipper │ tippee │ amount │
├───────────────────────────────┼───────────────────────────────┼────────┼────────┼────────┤
│ 2012-07-02 15:32:27.730099+00 │ 2013-09-24 14:30:54.216455+00 │ foo    │ bar    │   0.00 │
│ 2013-04-16 15:15:24.078803+00 │ 2013-09-24 14:30:54.216455+00 │ bar    │ baz    │   1.00 │
│ 2013-09-24 14:25:47.796424+00 │ 2013-09-24 14:25:59.945739+00 │ bar    │ rok    │   2.00 │
│ 2013-09-24 14:25:47.796424+00 │ 2013-09-24 14:25:47.796424+00 │ bar    │ rok    │   1.00 │
└───────────────────────────────┴───────────────────────────────┴────────┴────────┴────────┘

That's ordered by mtime desc.

[mw44118]

I'm baffled. What does the "c" in ctime stand for?

[zbynekwinkler]

I must say I am lost as well. Actually I am finding some of the queries really difficult to understand. For example I have no idea why don't we do just plain insert into the tips table and/or why we have two timestamps there.

[seanlinsley]

If I had to guess, ctime is the time created, and mtime is the most recent time it's been modified.

[pjz]

Without an ORDER BY in the first subselect, isn't the result of a LIMIT 1 undefined? I mean, the row returned will match the criteria, but there's no telling which matching row will be returned.

[zbynekwinkler]

It is. I guess we need to get more eyes on the code. For that i'd like to think about simplifying things so that it is more obvious from the first sight what the author wanted to say/do. The data model is definitely first and most important target. That might help even more than documentation in bringing more people to it IMO.

[chadwhitacre]

I'm baffled. What does the "c" in ctime stand for?

As @daxter intimates, the ctime, mtime convention is borrowed from Unix, where they refer to the creation time and modification time of a file (in Unix there's also atime for access time, which we're not using).

[chadwhitacre]

For example I have no idea why don't we do just plain insert into the tips table and/or why we have two timestamps there.

We have two timestamps there so that we can know when a user first created a tip, and when the user modified the tip.

┌───────────────────────────────┬───────────────────────────────┬────────┬────────┬────────┐
│             ctime             │             mtime             │ tipper │ tippee │ amount │
├───────────────────────────────┼───────────────────────────────┼────────┼────────┼────────┤
│ 2012-07-02 15:32:27.730099+00 │ 2013-09-24 14:30:54.216455+00 │ foo    │ bar    │   0.00 │

Here, for example, we see that foo started tipping bar way back in June, 2012, and just yesterday dropped their gift to bar down to zero. We could of course compute the date of first tip (ctime) with a query:

SELECT * FROM tips WHERE tipper = 'foo' and tippee = 'bar' ORDER BY mtime ASC LIMIT 1;

┌───────────────────────────────┬───────────────────────────────┬────────┬────────┬────────┐
│             ctime             │             mtime             │ tipper │ tippee │ amount │
├───────────────────────────────┼───────────────────────────────┼────────┼────────┼────────┤
│ 2012-07-02 15:32:27.730099+00 │ 2012-07-02 15:32:27.730099+00 │ foo    │ bar    │   0.64 │

I made the decision when designing the schema that it would be useful and interesting to carry this ctime information forward in each tip record.

[chadwhitacre]

Without an ORDER BY in the first subselect, isn't the result of a LIMIT 1 undefined? I mean, the row returned will match the criteria, but there's no telling which matching row will be returned.

Correct! But since we're only selecting ctime, and ctime is the same for all (tipper, tippee) combinations (it's denormalized, as mentioned above), then we don't care which matching row is returned, and I therefore decided not to order them, as a slight optimization.

[chadwhitacre]

I guess we need to get more eyes on the code.

Always a good thing. :-)

The data model is definitely first and most important target. That might help even more than documentation in bringing more people to it IMO.

How about documentation about the data model (#1272)? :-)

[chadwhitacre]

@pjz in IRC:

I thought that might be it, but the issue is that your original example table had only foo, bar as tippee, tipper, when it's really multiple pairs and not all from the same pair

True. I did a hack job of anonymizing the example, sorry.

[chadwhitacre]

I've modified the example at #1498 (comment). It should be correct now.

[zbynekwinkler]

How about documentation about the data model (#1272)? :-)

Care to guess who is the one who has got to write it because no one else understands it (#1502)? :-)

[chadwhitacre]

@zwn :-)

[zbynekwinkler]

There is one thing missing for me to let this issue lay closed. What is the difference between trigger and rule? I have found a description in postgres documentation.

For the things that can be implemented by both, which is best depends on the usage of the database. A trigger is fired once for each affected row. A rule modifies the query or generates an additional query. So if many rows are affected in one statement, a rule issuing one extra command is likely to be faster than a trigger that is called for every single row and must re-determine what to do many times. However, the trigger approach is conceptually far simpler than the rule approach, and is easier for novices to get right.

@mw44118 From the #1502 we know that we are using rules to create the mtimes. I believe that's conceptually the same thing (while the difference being implementation detail).

[chadwhitacre]

@zwn We could do what we're doing with rules using triggers, yes. I implemented them with rules because it fit this example in the RULE docs and seemed to work well for what we want to do here. I'm open to the possibility that a trigger would work better for this. For example, perhaps we could factor out the ctime coalescing into a PL/pgSQL function and use that in multiple triggers. But perhaps we could factor that out and use it multiple rules as well?

[zbynekwinkler]

@whit537 Let's have a wider discussion about which way to go (#1549).