Thread: Keeping track of updates/performances

Keeping track of updates/performances

From
Romain Billon-Grand
Date:
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
(I will manage access to the database giving every user a role in his name, those role belonging to group roles such as senior/junior/visitor...) I did not find this in the documentation.


Or, even if it exists, maybe I would better get it back from some user table such as
CREATE TABLE users (username TEXT, role TEXT, id_user..); ??

for timestamps, I guess triggers would be the best way to do the job, but I would like to have the whole table updated automatically without having to include this in all my update quieries...


3/ once such a table is built like this for example
CREATE TABLE track (creation TIMESTAMP, last_modification TIMESTAMP, created_by TEXT, lastmodified_by TEXT)

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

Many thanks for your help!

Re: Keeping track of updates/performances

From
Andreas Kretschmer
Date:
Romain Billon-Grand <billongrand@hotmail.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°


Re: Keeping track of updates/performances

From
Felipe Santos
Date:


2015-03-17 5:50 GMT-03:00 Andreas Kretschmer <akretschmer@spamfence.net>:
Romain Billon-Grand <billongrand@hotmail.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@postgresql.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

Re: Keeping track of updates/performances

From
Luca Ferrari
Date:
Ciao,

On Tue, Mar 17, 2015 at 9:06 AM, Romain Billon-Grand
<billongrand@hotmail.fr> wrote:
> 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...
>

You can use current_user and 'now'::timestamp for instance to get the
actual time and user.
Place this in a trigger for insert/update and you made it.



> Or, even if it exists, maybe I would better get it back from some user table
> such as
> CREATE TABLE users (username TEXT, role TEXT, id_user..); ??
>

What is the aim? There is the catalog for tracking users and roles.

> for timestamps, I guess triggers would be the best way to do the job, but I
> would like to have the whole table updated automatically without having to
> include this in all my update quieries...
>

and triggers will do the magic update as you want, so what is the
problem with triggers?

>
> 3/ once such a table is built like this for example
> CREATE TABLE track (creation TIMESTAMP, last_modification TIMESTAMP,
> created_by TEXT, lastmodified_by TEXT)
>
> What would be the advantages and drawbacks of this :
> CREATE TABLE anytable(...) INHERITS (tracks)
> vs this: CREATE TABLE anytable (...) LIKE tracks?

inheritance means that the new table will share columns with the
parent, while like will copy the DDL for the table and the constraint.
You should have a look at the documentation:
http://www.postgresql.org/docs/current/static/sql-createtable.html

Luca


Re: Keeping track of updates/performances

From
Romain Billon-Grand
Date:
Many thanks for all your helpfull answers. May be I was not accurzte enough in my questions...

So:

´now'::timestamp why not ´simply' current_timestamp?

And..

I have red about inheritance vs Like, but I wanted to know if one of those would have better performance


Date: Tue, 17 Mar 2015 09:18:11 -0300
Subject: Re: [NOVICE] Keeping track of updates/performances
From: felipepts@gmail.com
To: akretschmer@spamfence.net
CC: pgsql-novice@postgresql.org



2015-03-17 5:50 GMT-03:00 Andreas Kretschmer <akretschmer@spamfence.net>:
Romain Billon-Grand <billongrand@hotmail.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@postgresql.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

Re: Keeping track of updates/performances

From
Luca Ferrari
Date:
On Tue, Mar 17, 2015 at 7:21 PM, Romain Billon-Grand
<billongrand@hotmail.fr> wrote:

> ´now'::timestamp why not ´simply' current_timestamp?
>


That's the same (in the final run), the former uses a cast, the second
an internal variable.
Use current_ for anything you need.

> I have red about inheritance vs Like, but I wanted to know if one of those
> would have better performance

That's the wrong question, since it depends on what you are going to achieve.
Let's say that, being inheritance a way of tiying tables together, I
would expect to be able to have better performances in using like
tables, since I can tune them separately to exactly the need I have.
But again, it depends on what you are going to do with such tables and
what performances means to you (usage, creation, deletion, ...).

Luca