pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Extract created and last modified data 2+ messages / 2 participants [nested] [flat]
* Extract created and last modified data @ 2010-08-19 11:48 Gordon McVey <grj.mcvey@googlemail.com> 0 siblings, 1 reply; 2+ messages in thread From: Gordon McVey @ 2010-08-19 11:48 UTC (permalink / raw) To: pgsql-sql I have a CMS with a table of items, and another table serving as a log of operations performed on the items. The revelent table structure is as follows: items itm_id | usr_id_create | itm_date_create | usr_id_modify | itm_date_modify | ..... itm_id is a serial primary key. usr_id_* are the keys of the users who created and last modified the item. item_date_* are timestamps for creation and last modification times. changelog itm_id | usr_id | log_timestamp | log_op itm_id and usr_id are foreign keys into the respective item and user tables. log_timestamp is when the last change was made and log_op was the operation performed. I realised that the creation and last modified data was being stored in two places, and this is causing some issues with desynching. I'm thinking of removing the creation and last modified fields from the items table and using a view to fetch the create data and ID, and the last modified date and ID from the changelog table instead. However I'm not sure of how to do this, and how it would impact performance. Can anybody help with writing the query to get a view that replicates the items table except with the create and last modified user IDs and timestamps loaded from the changelog table? If there is anybody who has actually implemented something similar, what kind of performance impact would it have? ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Extract created and last modified data @ 2010-08-19 13:24 Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt> parent: Gordon McVey <grj.mcvey@googlemail.com> 0 siblings, 0 replies; 2+ messages in thread From: Oliveiros d'Azevedo Cristina @ 2010-08-19 13:24 UTC (permalink / raw) To: Gordon McVey <grj.mcvey@googlemail.com>; pgsql-sql Howdy, Gordon. This query is an attempt to replicate your items table with data just from changelog table. Could you please try it out? Best, Oliveiros SELECT itm_id,usr_id_create, itm_date_create, usr_id_modify, itm_date_modify FROM ( SELECT a.itm_id, a.usr_id as usr_id_create, create as itm_date_create FROM changelog a JOIN (SELECT MIN(log_timestamp) as create, itm_id FROM changelog GROUP BY itm_id) b ON (a.itm_id = b.itm_id AND log_timestamp = create) ) subquery1 NATURAL JOIN ( SELECT c.itm_id, usr_id as usr_id_modify, last_modified as itm_date_modify FROM changelog c JOIN (SELECT MAX(log_timestamp) as last_modified, itm_id FROM changelog GROUP BY itm_id) d ON (d.itm_id = c.itm_id AND c.log_timestamp = last_modified) ) subquery2 I have a CMS with a table of items, and another table serving as a log of operations performed on the items. The revelent table structure is as follows: items itm_id | usr_id_create | itm_date_create | usr_id_modify | itm_date_modify | ..... itm_id is a serial primary key. usr_id_* are the keys of the users who created and last modified the item. item_date_* are timestamps for creation and last modification times. changelog itm_id | usr_id | log_timestamp | log_op itm_id and usr_id are foreign keys into the respective item and user tables. log_timestamp is when the last change was made and log_op was the operation performed. . Can anybody help with writing the query to get a view that replicates the items table except with the create and last modified user IDs and timestamps loaded from the changelog table? If there is anybody who has actually implemented something similar, what kind of performance impact would it have? ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2010-08-19 13:24 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2010-08-19 11:48 Extract created and last modified data Gordon McVey <grj.mcvey@googlemail.com> 2010-08-19 13:24 ` Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox