Re: Keeping track of updates/performances

2015-03-17 5:50 GMT-03:00 Andreas Kretschmer <akretschmer(at)spamfence(dot)net>:

> Romain Billon-Grand <billongrand(at)hotmail(dot)fr> wrote:
>
> > Hello!
> >
> > Please give me your advices about the best way to keep in each table
> those
> > informations:
> > name of the user who created a row
> > name of the last user who modified it
> > timestamp for creation
> > timestamp for last modification
> >
> > 1/ I have been looking for built in options in postgres but I guess I
> have to
> > build it myself...?
> > Of course if Postgres already does it I am very happy, and all of the
> following
> > quetsions are useless but I did not find this in the documentation...
> >
> > 2/ Is there some SQL query to return the "usermane" from pg_catalog or
> > something
>
> You can use TRIGGER for Insert/Update, the current user can you detect
> with current_user.
>
>
> Andreas
> --
> Really, I'm not out to destroy Microsoft. That will just be a completely
> unintentional side effect. (Linus Torvalds)
> "If I was god, I would recompile penguin with --enable-fly." (unknown)
> Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
>
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>

"What would be the advantages and drawbacks of this :
CREATE TABLE anytable(...) INHERITS (tracks)
vs this: CREATE TABLE anytable (...) LIKE tracks?"

I see them differently.

INHERITS creates a child-table that is still connected with the
parent-table. I use this feature to emulate the partition-feature that
exists in other RDBMSs. If you add a column to the father table, it will
automatically appear on the child-table, and that is exactly what I want.
And then I use triggers to do the "partition function/scheme" (in SQL
Server words). You can partition by date, by user, by country or any other
thing that makes sense to you. Also, if you query the child table you get
only the child-table's rows. But if you query the parent-table you get
parent-table's rows PLUS all child-tables' rows.

CREATE TABLE LIKE is like making a photocopy of a table to create another
one, but just that. The tables are not "connected" and dont have any kind
of relation with one another.

Hope it helps