Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
2013/1/3 Stephen Frost sfr...@snowman.net: * Robert Haas (robertmh...@gmail.com) wrote: Well, IMHO, there is no need for any syntax change at all. CREATE TABLE and CREATE DATABASE should just record the creation time somewhere, and that's all. If you dump-and-reload, the creation time changes. Deal with it, or hack your catalogs if you really care that much. I'd be alright with this also, tbh. Not preserving such information across pg_dump's wouldn't really be all *that* much of a loss. As for hacking at the catalogs, I do find that a rather terrible recommendation, ever. I'm currently trying to convince people at $work that hacking at pg_database to modify datallowconns is really not a good or ideal solution (and requires a lot more people to have superuser rights than really should, which is practically no one, imo). Annoyingly, we don't seem to have a way to ALTER DATABASE to set that value, although I *think* 'connection limit = 0' might be good enough. I find the suggestion of using event triggers for this to miss the point almost completely. At least in my case, the time when you really wish you had some timestamps is when you get dropped into a customer environment and need to do forensics. The customer will not have installed the convenient package of event triggers at database bootstrap time. Their environment will likely be poorly configured and completely undocumented; that's why you're doing forensics, isn't it? Exactly, that's what I was trying to get at upstream. I know this has been discussed and rejected before, but I find that rejection to be wrong-headed. I have repeatedly been asked, with levels of exasperation ranging from mild to homicidal, why we don't have this feature, and I have no good answer. If it were somehow difficult to record this or likely to produce a lot of overhead, that would be one thing. But it isn't. It's probably a hundred-line patch, and AFAICS the overhead would be miniscule. +1 +1 yes, this task can be simply solved by EVENT TRIGGERS, but native implementation can carry some unification - and time of creation is basic attribute that I would to see everywhere. And I am not alone regards Pavel Stehule Thanks, Stephen -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
On 12/28/2012 03:14 AM, Stephen Frost wrote: ... I agree that what I was suggesting would be possible to implement with event triggers, but I see that as a rather advanced feature that most users aren't going to understand or implement. At the same time, those more novice users are likely to be looking for this kind of information- being told oh, well, you *could* have been collecting it all along if you knew about event triggers isn't a particularly satisfying answer. That's my 2c on it. I agree that having the example in the docs would be nice- examples are always good things to include. If what you want is something close to current unix file time semantics (ctime, mtime, atime) then why not just create a function to look up these attributes on database directory and/or database files ? Hannu -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
2013/1/3 Hannu Krosing ha...@krosing.net: On 12/28/2012 03:14 AM, Stephen Frost wrote: ... I agree that what I was suggesting would be possible to implement with event triggers, but I see that as a rather advanced feature that most users aren't going to understand or implement. At the same time, those more novice users are likely to be looking for this kind of information- being told oh, well, you *could* have been collecting it all along if you knew about event triggers isn't a particularly satisfying answer. That's my 2c on it. I agree that having the example in the docs would be nice- examples are always good things to include. If what you want is something close to current unix file time semantics (ctime, mtime, atime) then why not just create a function to look up these attributes on database directory and/or database files ? Implementation of ctime, mtime, atime will have little bit higher impact than just creation time - and these values should be moved to statistics instead bloated pg_class. You cannot use a filesystem data, because some requests are solved by cache not by filesystem. I had to emulate MySQL fields - and this was a first implementation, but totally useles - now we have a solution based on enhancing pg_stat and it works as expected Regards Pavel Hannu -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
--On 2. Januar 2013 23:04:43 -0500 Robert Haas robertmh...@gmail.com wrote: TBH, I don't think anyone has any business changing the creation timestamp. Ever. For me, the fact that pg_dump wouldn't preserve this information would be a feature, not a bug. I mostly meant to point out that someone could bypass it if they cared enough, not to recommend it. Honestly, I'd probably *rather* store this information someplace where it couldn't be changed via SQL *at all*. But I don't think we have such a place, so I'm happy enough to store it in the catalogs, with the associated risks of catalog hackery that entails. This is exactly what Informix does, it stores creation or modification dates of a table in its system catalog (systables.created, to be specific). Any export/import of tables doesn't preserve the dates, if you restore a database (or table), the creation date is adjusted. I'm not aware of any SQL interface to influence this. -- Thanks Bernd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
On 01/03/2013 05:04 AM, Robert Haas wrote:
O
Yeah, I don't think this is really a problem. I would expect the psql
support for this feature to be not a whole lot more complicated than
that. Sure, it might be more than 5 lines of raw code if it requires
an additional version of some query for which we're currently using
the same version for both PG93 and PG92, but it's hardly fair to cite
that as an argument for not doing this. Such changes are almost
entirely boilerplate.
Here is a pl/python function which gives you the real database
creation time.
CREATE OR REPLACE FUNCTION database_create_ts(INOUT dbname text, OUT
ctime timestamp)
RETURNS SETOF RECORD
LANGUAGE plpythonu AS
$$
import os, time
res = plpy.execute(select datname,
current_setting('data_directory') ddir,
oid as dboid
from pg_database where datname like '%s'; %
dbname)
for row in res:
dbpath = '%(ddir)s/base/%(dboid)s' % row
stat = os.stat(dbpath)
yield row['datname'], '%04d-%02d-%02d %02d:%02d:%02d+00' %
time.gmtime(stat.st_ctime)[:6]
$$;
SELECT * FROM database_create_ts('template%');
--
Hannu
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
* Fabrízio de Royes Mello (fabriziome...@gmail.com) wrote: The attached patch add a new column into 'pg_database' called 'datcreated' to store the timestamp of database creation. Please use hard-tabs (not spaces) and the column should come before the variable length records (see the comment in pg_database.h). Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
* Hannu Krosing (ha...@krosing.net) wrote: If what you want is something close to current unix file time semantics (ctime, mtime, atime) then why not just create a function to look up these attributes on database directory and/or database files ? Because, as noted before, those aren't always going to be correct. Database files can be rewritten and recreated based on certain commands (eg: CLUSTER). Perhaps there's a fork that isn't, but that almost seems like it's more painful to try and figure out than just hooking in with the CREATE command. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
On Thu, Jan 3, 2013 at 11:13 AM, Stephen Frost sfr...@snowman.net wrote: * Fabrízio de Royes Mello (fabriziome...@gmail.com) wrote: The attached patch add a new column into 'pg_database' called 'datcreated' to store the timestamp of database creation. Please use hard-tabs (not spaces) and the column should come before the variable length records (see the comment in pg_database.h). You all right... I fixed it in attached patch. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello pg_database_add_datcreated_column_v2.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
On Thu, Jan 3, 2013 at 11:33 AM, Fabrízio de Royes Mello fabriziome...@gmail.com wrote: On Thu, Jan 3, 2013 at 11:13 AM, Stephen Frost sfr...@snowman.net wrote: * Fabrízio de Royes Mello (fabriziome...@gmail.com) wrote: The attached patch add a new column into 'pg_database' called 'datcreated' to store the timestamp of database creation. Please use hard-tabs (not spaces) and the column should come before the variable length records (see the comment in pg_database.h). You all right... I fixed it in attached patch. Please... discard this patch... I make a mistake... soon I send the new one. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
* Fabrízio de Royes Mello (fabriziome...@gmail.com) wrote: On Thu, Jan 3, 2013 at 11:13 AM, Stephen Frost sfr...@snowman.net wrote: Please use hard-tabs (not spaces) and the column should come before the variable length records (see the comment in pg_database.h). You all right... I fixed it in attached patch. You also need to fix the Anum_* values to match what's in the struct definition now.. I'd recommend that you look over the code more closely and ensure that you're ordering everything correctly throughout and that it all makes sense.. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
On 1/2/13 11:08 PM, Fabrízio de Royes Mello wrote: The attached patch add a new column into 'pg_database' called 'datcreated' to store the timestamp of database creation. If this feature is approved I could extend it to add a column into 'pg_class' to store creation timestamp too. While I'm entirely in favor of this feature in general, I think this is the wrong way to approach it. It will end up like the CREATE OR REPLACE support: We add it for a few commands in one release, for a few more commands in the next release, for almost all commands in the following release, and now we're still not done. If we're going to store object creation time, I think we should do it for all objects, stored in a separate catalog, like pg_depend or pg_description, keyed off classid, objectid. And have a simple C function to call to update the information stored there. That would also make storing the modification time, which I'd ask for next, easier. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
2013/1/3 Peter Eisentraut pete...@gmx.net: On 1/2/13 11:08 PM, Fabrízio de Royes Mello wrote: The attached patch add a new column into 'pg_database' called 'datcreated' to store the timestamp of database creation. If this feature is approved I could extend it to add a column into 'pg_class' to store creation timestamp too. While I'm entirely in favor of this feature in general, I think this is the wrong way to approach it. It will end up like the CREATE OR REPLACE support: We add it for a few commands in one release, for a few more commands in the next release, for almost all commands in the following release, and now we're still not done. If we're going to store object creation time, I think we should do it for all objects, stored in a separate catalog, like pg_depend or pg_description, keyed off classid, objectid. And have a simple C function to call to update the information stored there. That would also make storing the modification time, which I'd ask for next, easier. +1 Pavel -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
On Thu, Jan 3, 2013 at 9:18 AM, Pavel Stehule pavel.steh...@gmail.com wrote: 2013/1/3 Peter Eisentraut pete...@gmx.net: On 1/2/13 11:08 PM, Fabrízio de Royes Mello wrote: The attached patch add a new column into 'pg_database' called 'datcreated' to store the timestamp of database creation. If this feature is approved I could extend it to add a column into 'pg_class' to store creation timestamp too. While I'm entirely in favor of this feature in general, I think this is the wrong way to approach it. It will end up like the CREATE OR REPLACE support: We add it for a few commands in one release, for a few more commands in the next release, for almost all commands in the following release, and now we're still not done. If we're going to store object creation time, I think we should do it for all objects, stored in a separate catalog, like pg_depend or pg_description, keyed off classid, objectid. And have a simple C function to call to update the information stored there. That would also make storing the modification time, which I'd ask for next, easier. +1 +1 -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
Peter Eisentraut escribió: If we're going to store object creation time, I think we should do it for all objects, stored in a separate catalog, like pg_depend or pg_description, keyed off classid, objectid. And have a simple C function to call to update the information stored there. +1 We require two catalogs though, one shared, one database-local. Would we track ctime of subsidiary objects such as constraints etc? -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
On Thu, Jan 3, 2013 at 11:41 AM, Stephen Frost sfr...@snowman.net wrote: * Fabrízio de Royes Mello (fabriziome...@gmail.com) wrote: On Thu, Jan 3, 2013 at 11:13 AM, Stephen Frost sfr...@snowman.net wrote: Please use hard-tabs (not spaces) and the column should come before the variable length records (see the comment in pg_database.h). You all right... I fixed it in attached patch. You also need to fix the Anum_* values to match what's in the struct definition now.. I'd recommend that you look over the code more closely and ensure that you're ordering everything correctly throughout and that it all makes sense.. Now I fixed it. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello pg_database_add_datcreated_column_v3.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
On Thu, Jan 3, 2013 at 12:30 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Peter Eisentraut escribió: If we're going to store object creation time, I think we should do it for all objects, stored in a separate catalog, like pg_depend or pg_description, keyed off classid, objectid. And have a simple C function to call to update the information stored there. +1 +1 We require two catalogs though, one shared, one database-local. Have you a suggestion for the names of this new two catalogs? Would we track ctime of subsidiary objects such as constraints etc? If we're going to this way I think yes... As Peter said we can start add it for a few commands in one release (maybe first for shared objects) and then for a few more commands in a next release, and next... until we cover all commands... Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
Fabrízio de Royes Mello escribió: As Peter said we can start add it for a few commands in one release (maybe first for shared objects) and then for a few more commands in a next release, and next... until we cover all commands... No, he was describing a pessimistic scenario that he doesn't want us to be on. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
On Thu, Jan 3, 2013 at 2:06 AM, Stephen Frost sfr...@snowman.net wrote: I'd be alright with this also, tbh. Not preserving such information across pg_dump's wouldn't really be all *that* much of a loss. I think it would be mandatory for pg_dump not to restore this info actually. A fair amount of work has gone into pg_dump -s to ensure that the output is identical for identical databases. OIDs were removed and the sort order was changed to be deterministic for example. Any alter table set creation time 'xxx' will defeat that entirely. When last I managed a production Postgres database I would use pg_dump -s to regenerate a schema file that was checked into revision control. And when I migrated changes live I would rerun pg_dump -s and diff that against the checked in schema. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
On Wed, Dec 26, 2012 at 11:13 PM, Tom Lane t...@sss.pgh.pa.us wrote: This has been debated, and rejected, before. To mention just one problem, are we going to add nonstandard, non-backwards-compatible syntax to every single kind of CREATE to allow pg_dump to preserve the creation dates? Another interesting question is whether we should likewise track the last ALTER time, or perhaps whether a sufficiently major ALTER redefinition should update the creation time. Well, IMHO, there is no need for any syntax change at all. CREATE TABLE and CREATE DATABASE should just record the creation time somewhere, and that's all. If you dump-and-reload, the creation time changes. Deal with it, or hack your catalogs if you really care that much. I find the suggestion of using event triggers for this to miss the point almost completely. At least in my case, the time when you really wish you had some timestamps is when you get dropped into a customer environment and need to do forensics. The customer will not have installed the convenient package of event triggers at database bootstrap time. Their environment will likely be poorly configured and completely undocumented; that's why you're doing forensics, isn't it? I know this has been discussed and rejected before, but I find that rejection to be wrong-headed. I have repeatedly been asked, with levels of exasperation ranging from mild to homicidal, why we don't have this feature, and I have no good answer. If it were somehow difficult to record this or likely to produce a lot of overhead, that would be one thing. But it isn't. It's probably a hundred-line patch, and AFAICS the overhead would be miniscule. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
Robert Haas robertmh...@gmail.com writes: [ on creation timestamps ] I know this has been discussed and rejected before, but I find that rejection to be wrong-headed. I have repeatedly been asked, with levels of exasperation ranging from mild to homicidal, why we don't have this feature, and I have no good answer. If it were somehow difficult to record this or likely to produce a lot of overhead, that would be one thing. But it isn't. It's probably a hundred-line patch, and AFAICS the overhead would be miniscule. If I believed that it would be a hundred-line patch, and would *stay* a hundred-line patch, I'd be fine with it. But it won't. I will bet a very fine dinner that the feature wouldn't get out the door before there would be demands for pg_dump support. And arguments about whether ALTER should or should not change the timestamp. And I doubt you counted psql \d support in that hundred lines. So this is just a can of worms that I'd rather not open. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
* Robert Haas (robertmh...@gmail.com) wrote: Well, IMHO, there is no need for any syntax change at all. CREATE TABLE and CREATE DATABASE should just record the creation time somewhere, and that's all. If you dump-and-reload, the creation time changes. Deal with it, or hack your catalogs if you really care that much. I'd be alright with this also, tbh. Not preserving such information across pg_dump's wouldn't really be all *that* much of a loss. As for hacking at the catalogs, I do find that a rather terrible recommendation, ever. I'm currently trying to convince people at $work that hacking at pg_database to modify datallowconns is really not a good or ideal solution (and requires a lot more people to have superuser rights than really should, which is practically no one, imo). Annoyingly, we don't seem to have a way to ALTER DATABASE to set that value, although I *think* 'connection limit = 0' might be good enough. I find the suggestion of using event triggers for this to miss the point almost completely. At least in my case, the time when you really wish you had some timestamps is when you get dropped into a customer environment and need to do forensics. The customer will not have installed the convenient package of event triggers at database bootstrap time. Their environment will likely be poorly configured and completely undocumented; that's why you're doing forensics, isn't it? Exactly, that's what I was trying to get at upstream. I know this has been discussed and rejected before, but I find that rejection to be wrong-headed. I have repeatedly been asked, with levels of exasperation ranging from mild to homicidal, why we don't have this feature, and I have no good answer. If it were somehow difficult to record this or likely to produce a lot of overhead, that would be one thing. But it isn't. It's probably a hundred-line patch, and AFAICS the overhead would be miniscule. +1 Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
* Tom Lane (t...@sss.pgh.pa.us) wrote: If I believed that it would be a hundred-line patch, and would *stay* a hundred-line patch, I'd be fine with it. But it won't. I will bet a very fine dinner that the feature wouldn't get out the door before there would be demands for pg_dump support. Fine, how about a function that can be called by pg_dump (and anyone else who has the rights and feels the need) to set that value? That avoids all need for any new syntax and still gives us the pg_dump and friends support that will apparently be asked for. And arguments about whether ALTER should or should not change the timestamp. There is no case where ALTER should change the *creation* time, imo. And I doubt you counted psql \d support in that hundred lines. So this is just a can of worms that I'd rather not open. The last psql \d support change that I looked at (thanks Jon) had a diffstat (excluding documentation and whitespace changes) of: sfrost@beorn:/home/sfrost/Downloads cat qq | diffstat describe.c |5 + 1 file changed, 5 insertions(+) Just saying. ;) Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
On Wed, Jan 2, 2013 at 9:12 PM, Stephen Frost sfr...@snowman.net wrote: * Tom Lane (t...@sss.pgh.pa.us) wrote: If I believed that it would be a hundred-line patch, and would *stay* a hundred-line patch, I'd be fine with it. But it won't. I will bet a very fine dinner that the feature wouldn't get out the door before there would be demands for pg_dump support. Fine, how about a function that can be called by pg_dump (and anyone else who has the rights and feels the need) to set that value? That avoids all need for any new syntax and still gives us the pg_dump and friends support that will apparently be asked for. TBH, I don't think anyone has any business changing the creation timestamp. Ever. For me, the fact that pg_dump wouldn't preserve this information would be a feature, not a bug. I mostly meant to point out that someone could bypass it if they cared enough, not to recommend it. Honestly, I'd probably *rather* store this information someplace where it couldn't be changed via SQL *at all*. But I don't think we have such a place, so I'm happy enough to store it in the catalogs, with the associated risks of catalog hackery that entails. And arguments about whether ALTER should or should not change the timestamp. There is no case where ALTER should change the *creation* time, imo. Duh. And I doubt you counted psql \d support in that hundred lines. So this is just a can of worms that I'd rather not open. The last psql \d support change that I looked at (thanks Jon) had a diffstat (excluding documentation and whitespace changes) of: sfrost@beorn:/home/sfrost/Downloads cat qq | diffstat describe.c |5 + 1 file changed, 5 insertions(+) Just saying. ;) Yeah, I don't think this is really a problem. I would expect the psql support for this feature to be not a whole lot more complicated than that. Sure, it might be more than 5 lines of raw code if it requires an additional version of some query for which we're currently using the same version for both PG93 and PG92, but it's hardly fair to cite that as an argument for not doing this. Such changes are almost entirely boilerplate. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
* Robert Haas robertmh...@gmail.com wrote: I know this has been discussed and rejected before, but I find that rejection to be wrong-headed. I have repeatedly been asked, with levels of exasperation ranging from mild to homicidal, why we don't have this feature, and I have no good answer. If it were somehow difficult to record this or likely to produce a lot of overhead, that would be one thing. But it isn't. It's probably a hundred-line patch, and AFAICS the overhead would be miniscule. Hi all, The attached patch add a new column into 'pg_database' called 'datcreated' to store the timestamp of database creation. If this feature is approved I could extend it to add a column into 'pg_class' to store creation timestamp too. I think we can discuss about psql support to show this new info about databases... Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello pg_database_add_datcreated_column_v1.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
Fabrízio de Royes Mello fabriziome...@gmail.com writes: Event triggers don't cover CREATE DATABASE statement. The reason why is because you create Event Triggers in a specific database and they only get run when you happen to be connected to that specific database. So for example say you install your Event Trigger in the postgres database but then do a CREATE DATABASE while connected to mydb, the Event Trigger is not installed and will not fire. It's the same analysis about tablespaces and roles, for all global objects in fact. I don't think there's much of a technical implementation reason why not supporting Event Triggers on those, it's all about POLA violation. I personnaly think that given a good documentation coverage having Event Trigger on global objects could be useful enough, even if you would have to install them in every database when you want them to fire no matter what. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
Dimitri, * Dimitri Fontaine (dimi...@2ndquadrant.fr) wrote: I personnaly think that given a good documentation coverage having Event Trigger on global objects could be useful enough, even if you would have to install them in every database when you want them to fire no matter what. I disagree. If we're going to have what are essentially 'global' event triggers, then they should go into a shared catalog- the user shouldn't be required to install them everywhere to get coverage. In addition, they should always fire in the same database (eg: postgres), so you could reasonably have a single log of 'CREATE DATABASE' commands being run. Of course, then we get into the technical issues which prevent that, such as having one backend connected to database xyz but needing to run commands in the postgres database. So, for my 2c, I do think there's a technical challenge which would have to be overcome to have global event triggers. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
Stephen Frost sfr...@snowman.net writes: I disagree. If we're going to have what are essentially 'global' event triggers, then they should go into a shared catalog- the user shouldn't be required to install them everywhere to get coverage. In addition, I understand your view point, and if we think we will be able to get that in the future, then I think we should be careful not to implement something else in the mean time. they should always fire in the same database (eg: postgres), so you could reasonably have a single log of 'CREATE DATABASE' commands being run. Of course, then we get into the technical issues which prevent that, such as having one backend connected to database xyz but needing to run commands in the postgres database. So, for my 2c, I do think there's a technical challenge which would have to be overcome to have global event triggers. It sounds to me like either autonomous transaction with the capability to run the independant transaction in another database, or some dblink creative use case. Another approach would be to get plproxy into core as a Foreign Data Wrapper for FOREIGN FUNCTION that would target PostgreSQL. Given that, we could maybe have an internal setup that allows us to run foreign functions in the postgres database from any other one, providing what we need for Global Event Triggers. Oh, I don't see that happening in 9.3. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
* Dimitri Fontaine (dimi...@2ndquadrant.fr) wrote: It sounds to me like either autonomous transaction with the capability to run the independant transaction in another database, or some dblink creative use case. Another approach would be to get plproxy into core as a Foreign Data Wrapper for FOREIGN FUNCTION that would target PostgreSQL. Given that, we could maybe have an internal setup that allows us to run foreign functions in the postgres database from any other one, providing what we need for Global Event Triggers. Of those, I'd think autonomous transactions is by far the most likely and also useful for other sitatuions. I don't see dblink or plproxy being used for this. Having some internal setup which allows us to run foreign functions in other databases seems more-or-less akin to autonomous transactions also. Oh, I don't see that happening in 9.3. I agree, didn't mean to imply otherwise. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
Stephen Frost sfr...@snowman.net writes:
Apparently I've managed to miss the tricky case..?
That shouldn't be tricky as a user, but has been a tricky subject every
time we've been talking about implement Event Triggers in the past two
years, so I though I would include it:
create schema test
create table foo(id serial primary key, f1 text);
create event trigger track_table
on ddl_command_trace
when tag in ('create table', 'alter table', 'drop table')
and context in ('toplevel', 'generated', 'subcommand')
execute procedure public.track_table_activity();
The trick is that you then want to fire the event trigger for a command
in a 'subcommand' context, as seen in the logs provided by the snitch
example:
NOTICE: snitch event: ddl_command_end, context: SUBCOMMAND
NOTICE: tag: CREATE TABLE, operation: CREATE, type: TABLE
NOTICE: oid: 25139, schema: test, name: foo
Sure, dropping tables, schemas, etc, would have an impact on the values.
we don't have, as of yet, support for a 'cascade' context. We will need
some heavy refactoring to get there, basically forcing the cascade drops
to happen via ProcessUtility(), but having a single DropStmt to handle
that I guess it shouldn't be very hard to do.
being told oh, well, you *could* have been collecting it all along if
you knew about event triggers isn't a particularly satisfying answer.
True that.
Now, having at least a way to do that without resorting to hacking the
backend or writing a C coded extension sure feels nice enough an answer
to me here.
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
Hi all, And about proposal that originated this thread... I proposed only to add a column on shared catalog pg_database with timestamp of its creation. Event triggers don't cover CREATE DATABASE statement. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
On 12/28/12 4:05 AM, Fabrízio de Royes Mello wrote: Hi all, And about proposal that originated this thread... I proposed only to add a column on shared catalog pg_database with timestamp of its creation. Event triggers don't cover CREATE DATABASE statement. Works for me, in that case. You'd need something a lot more mature than checking the file timestamp on PG_VERSION, though. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
* Josh Berkus (j...@agliodbs.com) wrote: This information could be extremely useful for forensics, debugging, ETL processes (many of which create tables as part of their processes), etc. I'd say moderately useful at best. Quite a number of things could make the creation dates misleading or not distinctive (think partition replacement, restore from pg_dump, replicas, etc.). ALTER dates would be more useful, but as Tom points out, would need the user-configurability which can only be delivered by something like event triggers. To be honest, I really just don't find this to be *that* difficult and an intuitive set of rules which are well documented feels like it'd cover 99% of the cases. pg_dump would preserve the times (though it could be optional), replicas should as well, etc. We haven't even started talking about the 'hard' part, which would be a 'modification' type of field.. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
Hi,
Tom Lane t...@sss.pgh.pa.us writes:
This proposal is about add a column datcreated on pg_database to store
the timestamp of the database creation.
I'm inclined to think that anyone who really needs this should be
pointed at event triggers. That feature (if it gets in) will allow
people to track creation/DDL-change times with exactly the behavior
they want.
Agreed.
Stephen Frost sfr...@snowman.net writes:
To be honest, I really just don't find this to be *that* difficult and
an intuitive set of rules which are well documented feels like it'd
cover 99% of the cases. pg_dump would preserve the times (though it
could be optional), replicas should as well, etc. We haven't even
started talking about the 'hard' part, which would be a 'modification'
type of field..
Here's a complete test case that works with my current branch, with a
tricky test while at it, of course:
create table public.tracking
(
relation regclass primary key,
relname name not null, -- in case it changes later
relnamespace name not null, -- same reason
created timestamptz default now(),
altered timestamptz,
dropped timestamptz
);
create or replace function public.track_table_activity() returns
event_trigger
language plpgsql
as $$
begin
raise notice 'track table activity: % %', tg_tag, tg_objectid::regclass;
if tg_operation = 'CREATE'
then
insert into public.tracking(relation, relname, relnamespace)
select tg_objectid, tg_objectname, tg_schemaname;
elsif tg_operation = 'ALTER'
then
update public.tracking set altered = now() where relation = tg_objectid;
elsif tg_operation = 'DROP'
then
update public.tracking set dropped = now() where relation = tg_objectid;
else
raise notice 'unknown operation';
end if;
end;
$$;
drop event trigger if exists track_table;
create event trigger track_table
on ddl_command_trace
when tag in ('create table', 'alter table', 'drop table')
and context in ('toplevel', 'generated', 'subcommand')
execute procedure public.track_table_activity();
drop schema if exists test cascade;
create schema test
create table foo(id serial primary key, f1 text);
alter table test.foo add column f2 text;
select relation::regclass, * from public.tracking;
drop table test.foo;
select * from public.tracking;
select * from public.tracking;
-[ RECORD 1 ]+--
relation | tracking
relname | tracking
relnamespace | public
created | 2012-12-27 17:02:13.567979+01
altered |
dropped |
-[ RECORD 2 ]+--
relation | 25139
relname | foo
relnamespace | test
created | 2012-12-27 17:02:26.696039+01
altered | 2012-12-27 17:02:29.105241+01
dropped | 2012-12-27 17:02:37.834997+01
Maybe the best way to reconciliate both your views would be to provide
the previous example in the event trigger docs?
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
On Thu, Dec 27, 2012 at 2:13 AM, Tom Lane t...@sss.pgh.pa.us wrote: This has been debated, and rejected, before. I know this discussion... To mention just one problem, are we going to add nonstandard, non-backwards-compatible syntax to every single kind of CREATE to allow pg_dump to preserve the creation dates? Another interesting question is whether we should likewise track the last ALTER time, or perhaps whether a sufficiently major ALTER redefinition should update the creation time. I agree with you because now we have Event Triggers... I'm inclined to think that anyone who really needs this should be pointed at event triggers. That feature (if it gets in) will allow people to track creation/DDL-change times with exactly the behavior they want. Exactly, but Event Triggers [1] don't cover CREATE DATABASE statement, and for this reason I propose the patch to add a single column datcreated on shared catalog pg_database. [1] http://www.postgresql.org/docs/devel/static/event-trigger-matrix.html Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
On Thu, Dec 27, 2012 at 2:04 PM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: Tom Lane t...@sss.pgh.pa.us writes: This proposal is about add a column datcreated on pg_database to store the timestamp of the database creation. I'm inclined to think that anyone who really needs this should be pointed at event triggers. That feature (if it gets in) will allow people to track creation/DDL-change times with exactly the behavior they want. Agreed. +1 Maybe the best way to reconciliate both your views would be to provide the previous example in the event trigger docs? +1 If all of you agree I can improve the event trigger docs... Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
Dimitri, * Dimitri Fontaine (dimi...@2ndquadrant.fr) wrote: Here's a complete test case that works with my current branch, with a tricky test while at it, of course: Apparently I've managed to miss the tricky case..? Sure, dropping tables, schemas, etc, would have an impact on the values. Dropping a table and then recreating it would be akin to deleteing a row and then inserting a new one- the create value would be set to the time of the new table being created and information about the dropped table would be lost. I'm not thinking of this as audit tracking where every action is logged. I agree that what I was suggesting would be possible to implement with event triggers, but I see that as a rather advanced feature that most users aren't going to understand or implement. At the same time, those more novice users are likely to be looking for this kind of information- being told oh, well, you *could* have been collecting it all along if you knew about event triggers isn't a particularly satisfying answer. That's my 2c on it. I agree that having the example in the docs would be nice- examples are always good things to include. Thanks, Stephen signature.asc Description: Digital signature
[HACKERS] Proposal: Store timestamptz of database creation on pg_database
Hi all,
This proposal is about add a column datcreated on pg_database to store
the timestamp of the database creation.
A couple weeks ago I had a trouble with a PostgreSQL instance, actually our
ERP had some strange behaviors with some data loss, but I searched for
ERRORs in log files (OS and PG) and I found nothing.
Looking at the files and directories in the cluster noticed something
strange, the date / time of the file base//PG_VERSION (database of
our ERP) was different compared to when we create it. So I used the
following SQL to check the date / time of creation of the databases in the
cluster:
fabrizio=# SELECT datname,
(pg_stat_file('base/'||oid||'/PG_VERSION')).modification AS datcreated
fabrizio-# FROM pg_database;
datname | datcreated
---+
template1 | 2012-12-26 12:11:53-02
template0 | 2012-12-26 12:11:54-02
postgres | 2012-12-26 12:11:54-02
fabrizio | 2012-12-26 12:12:02-02
(4 rows)
This isn't an elegant solution to do that, but worked fine. However, why
not we have a column to store this information?
Somebody have another idea?
Regards,
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
Blog sobre TI: http://fabriziomello.blogspot.com
Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
On 12/26/12 4:48 PM, Fabrízio de Royes Mello wrote: Hi all, This proposal is about add a column datcreated on pg_database to store the timestamp of the database creation. I agree that it would be useful. However, if we're going to get into created dates, we should at least consider adding them to the other catalogs, particularly pg_class. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
* Josh Berkus (j...@agliodbs.com) wrote: On 12/26/12 4:48 PM, Fabrízio de Royes Mello wrote: This proposal is about add a column datcreated on pg_database to store the timestamp of the database creation. I agree that it would be useful. However, if we're going to get into created dates, we should at least consider adding them to the other catalogs, particularly pg_class. I was thinking more-or-less the same thing. Along those lines, however, perhaps we should put them into a separate catalog to avoid the increased size of pg_class and friends..? Also, we'd probably have 2 of those, one for global and one for per-database objects, ala pg_depend and pg_shdepend, and then a view that brings it all together, resolves the OIDs to names, etc. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
Stephen Frost sfr...@snowman.net writes: * Josh Berkus (j...@agliodbs.com) wrote: On 12/26/12 4:48 PM, Fabrízio de Royes Mello wrote: This proposal is about add a column datcreated on pg_database to store the timestamp of the database creation. I agree that it would be useful. However, if we're going to get into created dates, we should at least consider adding them to the other catalogs, particularly pg_class. I was thinking more-or-less the same thing. This has been debated, and rejected, before. To mention just one problem, are we going to add nonstandard, non-backwards-compatible syntax to every single kind of CREATE to allow pg_dump to preserve the creation dates? Another interesting question is whether we should likewise track the last ALTER time, or perhaps whether a sufficiently major ALTER redefinition should update the creation time. I'm inclined to think that anyone who really needs this should be pointed at event triggers. That feature (if it gets in) will allow people to track creation/DDL-change times with exactly the behavior they want. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
* Tom Lane (t...@sss.pgh.pa.us) wrote: To mention just one problem, are we going to add nonstandard, non-backwards-compatible syntax to every single kind of CREATE to allow pg_dump to preserve the creation dates? Perhaps 'ALTER' would be a better place to put it, but concerns around how to make pg_dump work with it hardly strikes me as a serious argument against this. I agree that we may be overloading ourselves with syntax but that's a compromise we made long ago in order to have pg_dump be able to act like a regular 'user'. Another interesting question is whether we should likewise track the last ALTER time, or perhaps whether a sufficiently major ALTER redefinition should update the creation time. Yes, tracking the last 'ALTER' time would be useful as well, as it's own field. 'ALTER' wouldn't change the 'CREATE' time, except perhaps if it has an explicit 'make the CREATE time X' option. I'm inclined to think that anyone who really needs this should be pointed at event triggers. That feature (if it gets in) will allow people to track creation/DDL-change times with exactly the behavior they want. I considered that and rejected it. Event triggers will be great to allow people to customize and/or specialize exactly what is tracked and how, but I dislike that they would be the only way to get this information. I'm on the fence about if, assuming event triggers go in, we provide this kind of information through a 'default' set of event triggers. I wouldn't want users to be able to modify those event triggers and I'd expect the results to go into a system table that we wouldn't want users messing with either. This information could be extremely useful for forensics, debugging, ETL processes (many of which create tables as part of their processes), etc. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
This information could be extremely useful for forensics, debugging, ETL processes (many of which create tables as part of their processes), etc. I'd say moderately useful at best. Quite a number of things could make the creation dates misleading or not distinctive (think partition replacement, restore from pg_dump, replicas, etc.). ALTER dates would be more useful, but as Tom points out, would need the user-configurability which can only be delivered by something like event triggers. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
