Re: [HACKERS] Re: Proposal: Store timestamptz of database creation on pg_database
One thing i'd really like to be in this common object info catalog is DDL which created or altered the referenced object. If we additionally could make it possible to have ordinary triggers on this catalog it would solve most logical DDL replication problems Hannu Sent from Samsung Galaxy NotePeter Eisentraut pete...@gmx.net wrote:On Tue, 2013-01-08 at 17:17 -0500, Stephen Frost wrote: Seriously tho, the argument for not putting these things into the various individual catalogs is that they'd create bloat and these items don't need to be performant. I would think that the kind of timestamps that we're talking about fall into the same data category as comments on tables. If there isn't a good reason for comments on objects to be off in a generic this is for any kind of object table, then perhaps we should move them into the appropriate catalog tables? I think basic refactoring logic would support taking common things out of the individual catalogs and keeping them in a common structure, especially when they are for amusement only and not needed in any critical paths. All the ALTER command refactoring and so on that's been going on is also moving into the direction that for data definition management, there should be mainly one kind of object with a few variants here and there.
Re: [HACKERS] Re: Proposal: Store timestamptz of database creation on pg_database
On Fri, Jan 4, 2013 at 1:07 PM, Peter Eisentraut pete...@gmx.net wrote: On 1/3/13 3:26 PM, Robert Haas wrote: It's true, as we've often said here, that leveraging the OS facilities means that we get the benefit of improving OS facilities for free - but it also means that we never exceed what the OS facilities are able to provide. And that should be the deciding factor, shouldn't it? Clearly, the OS timestamps do not satisfy the requirements of tracking database object creation times. Yes, I think so. But I am not entirely sold on tracking the creation time of every SQL object. It might be all right, but what about catalog bloat? I am on board for databases, and for tables, at any rate. -- 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] Re: Proposal: Store timestamptz of database creation on pg_database
On Sat, Jan 5, 2013 at 11:04 AM, Stephen Frost sfr...@snowman.net wrote: * Fabrízio de Royes Mello (fabriziome...@gmail.com) wrote: * also we discuss about create two new catalogs, one local and another shared (like pg_description and pg_shdescription) to track creation times of all database objects. Creating a separate catalog (or two) every time we want to track XYZ for all objects is rather overkill... Thinking about this a bit more, and noting that pg_description/shdescription more-or-less already exist as a framework for tracking 'something' for 'all catalog entries'- why don't we just add these columns to those tables..? This would also address Peter's concern about making sure we do this 'wholesale' and in one release rather than spread across multiple releases- just make sure it covers the same set of things which 'comment' does. I suspect that trying to shoehorn this into pg_description/pg_shdescription will contort both features unnecessarily, but I'm willing to be proven wrong. Also, I don't think we really need a GUC for this. Indeed, a GUC would seem to me to defeat the entire point of the feature. -- 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] Re: Proposal: Store timestamptz of database creation on pg_database
On 1/5/13 11:04 AM, Stephen Frost wrote: Creating a separate catalog (or two) every time we want to track XYZ for all objects is rather overkill... Thinking about this a bit more, and noting that pg_description/shdescription more-or-less already exist as a framework for tracking 'something' for 'all catalog entries'- why don't we just add these columns to those tables..? This would also address Peter's concern about making sure we do this 'wholesale' and in one release rather than spread across multiple releases- just make sure it covers the same set of things which 'comment' does. Yeah, actually, the other day I was thinking we should get rid of all the system catalogs and use a big EAV-like schema instead. We're not getting any relational-database value out of the current way, and it's just a lot of duplicate code. If we had a full EAV system, we could even do in-place upgrade. Obviously, this isn't going to happen any time soon or ever, but I think I agree with your concern above as a partial step. -- 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] Re: Proposal: Store timestamptz of database creation on pg_database
2013/1/8 Peter Eisentraut pete...@gmx.net: On 1/5/13 11:04 AM, Stephen Frost wrote: Creating a separate catalog (or two) every time we want to track XYZ for all objects is rather overkill... Thinking about this a bit more, and noting that pg_description/shdescription more-or-less already exist as a framework for tracking 'something' for 'all catalog entries'- why don't we just add these columns to those tables..? This would also address Peter's concern about making sure we do this 'wholesale' and in one release rather than spread across multiple releases- just make sure it covers the same set of things which 'comment' does. Yeah, actually, the other day I was thinking we should get rid of all the system catalogs and use a big EAV-like schema instead. We're not getting any relational-database value out of the current way, and it's just a lot of duplicate code. If we had a full EAV system, we could even do in-place upgrade. -1 now we have a thousands tables, I am not sure so EAV can get good performance Pavel Obviously, this isn't going to happen any time soon or ever, but I think I agree with your concern above as a partial step. -- 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] Re: Proposal: Store timestamptz of database creation on pg_database
* Pavel Stehule (pavel.steh...@gmail.com) wrote: 2013/1/8 Peter Eisentraut pete...@gmx.net: On 1/5/13 11:04 AM, Stephen Frost wrote: Yeah, actually, the other day I was thinking we should get rid of all the system catalogs and use a big EAV-like schema instead. We're not getting any relational-database value out of the current way, and it's just a lot of duplicate code. If we had a full EAV system, we could even do in-place upgrade. -1 now we have a thousands tables, I am not sure so EAV can get good performance To be honest, my first reaction to this was an assumption that it was pure sarcasm.. Seriously tho, the argument for not putting these things into the various individual catalogs is that they'd create bloat and these items don't need to be performant. I would think that the kind of timestamps that we're talking about fall into the same data category as comments on tables. If there isn't a good reason for comments on objects to be off in a generic this is for any kind of object table, then perhaps we should move them into the appropriate catalog tables? Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Re: Proposal: Store timestamptz of database creation on pg_database
On Tue, 2013-01-08 at 17:17 -0500, Stephen Frost wrote: Seriously tho, the argument for not putting these things into the various individual catalogs is that they'd create bloat and these items don't need to be performant. I would think that the kind of timestamps that we're talking about fall into the same data category as comments on tables. If there isn't a good reason for comments on objects to be off in a generic this is for any kind of object table, then perhaps we should move them into the appropriate catalog tables? I think basic refactoring logic would support taking common things out of the individual catalogs and keeping them in a common structure, especially when they are for amusement only and not needed in any critical paths. All the ALTER command refactoring and so on that's been going on is also moving into the direction that for data definition management, there should be mainly one kind of object with a few variants here and there. -- 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] Re: Proposal: Store timestamptz of database creation on pg_database
* Fabrízio de Royes Mello (fabriziome...@gmail.com) wrote: Understood... a COMMENT is a database object, then if we add a creation time column to pg_description/shdescription tables how we track his creation time? When it's NULL it doesn't exist, in this case, when it transistions from NULL, it becomes created. A transistion from non-NULL to non-NULL would be an alter, and a transistion from non-NULL to NULL would be a drop/remove. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Re: Proposal: Store timestamptz of database creation on pg_database
* Stephen Frost sfr...@snowman.net wrote: Yes, and have the actual 'description' field (as it's variable) at the end of the catalog. Regarding the semantics of it- I was thinking about how directories and unix files work. Basically, adding or removing a sub-object would update the alter time on the object itself, changing an already existing object or sub-object would update only the object/sub-object's alter time. Creating an object or sub/object would set its create time and alter time to the same value. I would distinguish 'create' from 'ctime', however, and have our 'create' time be only the actual *creation* time of the object. ALTER table OWNER TO user; would update tables alter time. Understood... a COMMENT is a database object, then if we add a creation time column to pg_description/shdescription tables how we track his creation time? Open to other thoughts on this and perhaps we should create a wiki page to start documentating the semantics. Once we get agreement there, it's just a bit of code. :) +1 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] Re: Proposal: Store timestamptz of database creation on pg_database
On Fri, Jan 4, 2013 at 4:07 PM, Peter Eisentraut pete...@gmx.net wrote: On 1/3/13 3:26 PM, Robert Haas wrote: It's true, as we've often said here, that leveraging the OS facilities means that we get the benefit of improving OS facilities for free - but it also means that we never exceed what the OS facilities are able to provide. And that should be the deciding factor, shouldn't it? Clearly, the OS timestamps do not satisfy the requirements of tracking database object creation times. +1 And IMHO we must decide what we do or if we'll don't anything. In this thread was discussed many ways to how to implement and how not implement, so I compile some important points discussed before (sorry if I forgot something): * the original proposal was just to add a column in shared catalog 'pg_database' to track creation time (I already sent a patch [1]), but the discussion going to implement a way to track creation time off all database objects * some people said if we implement that then we must have some way to alter creation times by SQL (ALTER cmds) and also have a way to dump and restore this info by pg_dump/pg_restore. Some agreed and others disagree. * we talk about implement it with EventTriggers, but they not cover shared objects (like databases, roles, tablespaces,...), and someone talked to extend EventTriggers to cover this kind of objects or maybe we have a way to create *shared tables* (this is what I understood). This way force to every people implement your own track time system or maybe someone share a extension to do that. * also we discuss about create two new catalogs, one local and another shared (like pg_description and pg_shdescription) to track creation times of all database objects. Please fix if I forgot something. Anyway, we must decide what to do. I don't know enough, but I have another idea. What you guys think about we have tables like stats tables to track creation times, with a GUC to enable or disable this behavior. Regards, [1] http://archives.postgresql.org/pgsql-hackers/2013-01/msg00111.php -- 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] Re: Proposal: Store timestamptz of database creation on pg_database
* Fabrízio de Royes Mello (fabriziome...@gmail.com) wrote: * also we discuss about create two new catalogs, one local and another shared (like pg_description and pg_shdescription) to track creation times of all database objects. Creating a separate catalog (or two) every time we want to track XYZ for all objects is rather overkill... Thinking about this a bit more, and noting that pg_description/shdescription more-or-less already exist as a framework for tracking 'something' for 'all catalog entries'- why don't we just add these columns to those tables..? This would also address Peter's concern about making sure we do this 'wholesale' and in one release rather than spread across multiple releases- just make sure it covers the same set of things which 'comment' does. Also, I don't think we really need a GUC for this. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Re: Proposal: Store timestamptz of database creation on pg_database
* Stephen Frost sfr...@snowman.net wrote: * Fabrízio de Royes Mello (fabriziome...@gmail.com) wrote: * also we discuss about create two new catalogs, one local and another shared (like pg_description and pg_shdescription) to track creation times of all database objects. Creating a separate catalog (or two) every time we want to track XYZ for all objects is rather overkill... Thinking about this a bit more, and noting that pg_description/shdescription more-or-less already exist as a framework for tracking 'something' for 'all catalog entries'- why don't we just add these columns to those tables..? But those tables are filled only when we execute COMMENT ON statement... then your idea is create a 'null' comment every time we create a single object... is 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
Re: [HACKERS] Re: Proposal: Store timestamptz of database creation on pg_database
* Fabrízio de Royes Mello (fabriziome...@gmail.com) wrote: But those tables are filled only when we execute COMMENT ON statement... then your idea is create a 'null' comment every time we create a single object... is it? Yes, and have the actual 'description' field (as it's variable) at the end of the catalog. Regarding the semantics of it- I was thinking about how directories and unix files work. Basically, adding or removing a sub-object would update the alter time on the object itself, changing an already existing object or sub-object would update only the object/sub-object's alter time. Creating an object or sub/object would set its create time and alter time to the same value. I would distinguish 'create' from 'ctime', however, and have our 'create' time be only the actual *creation* time of the object. ALTER table OWNER TO user; would update tables alter time. Open to other thoughts on this and perhaps we should create a wiki page to start documentating the semantics. Once we get agreement there, it's just a bit of code. :) Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Re: Proposal: Store timestamptz of database creation on pg_database
On 1/3/13 3:26 PM, Robert Haas wrote: It's true, as we've often said here, that leveraging the OS facilities means that we get the benefit of improving OS facilities for free - but it also means that we never exceed what the OS facilities are able to provide. And that should be the deciding factor, shouldn't it? Clearly, the OS timestamps do not satisfy the requirements of tracking database object creation times. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: Proposal: Store timestamptz of database creation on pg_database
On 2013-01-03 11:03:17 +0100, Hannu Krosing wrote: 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 ? Because too many things change those. Moving to a different tablespace, a rewriting ALTER TABLE, etc. Greetings, Andres Freund -- Andres Freund http://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] Re: Proposal: Store timestamptz of database creation on pg_database
On 01/03/2013 11:18 AM, Andres Freund wrote: On 2013-01-03 11:03:17 +0100, Hannu Krosing wrote: 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 ? Because too many things change those. Moving to a different tablespace, a rewriting ALTER TABLE, etc. Can't we actually fix these to preserve file creation date like tar does and still keep unix file semantics ? So it is as about agreeing on what we actually want this create time mean opening a can of worms as tom predicted ? For example, how would this work in replication context ? Greetings, Andres Freund -- Andres Freundhttp://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] Re: Proposal: Store timestamptz of database creation on pg_database
* Hannu Krosing (ha...@krosing.net) wrote: Can't we actually fix these to preserve file creation date like tar does and still keep unix file semantics ? I'm not sure that I really see the advantage to trying to use the filesystem to keep this information for us..? So it is as about agreeing on what we actually want this create time mean opening a can of worms as tom predicted ? I agree that we need to hash out what, exactly, the values mean, but I don't think that's a terribly difficult thing to do. For example, how would this work in replication context ? If it's stored in the database catalogs, this is clear- it's replicated just like the catalog, and then you don't have to worry about trying to ensure that the file creation timestamp in the filesystem is right... Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Re: Proposal: Store timestamptz of database creation on pg_database
On 01/03/2013 02:17 PM, Stephen Frost wrote: * Hannu Krosing (ha...@krosing.net) wrote: Can't we actually fix these to preserve file creation date like tar does and still keep unix file semantics ? I'm not sure that I really see the advantage to trying to use the filesystem to keep this information for us..? If we would treat database as a file in this case then it would give us pre-defined meaning :) So it is as about agreeing on what we actually want this create time mean opening a can of worms as tom predicted ? I agree that we need to hash out what, exactly, the values mean, but I don't think that's a terribly difficult thing to do. For example, how would this work in replication context ? If it's stored in the database catalogs, this is clear- it's replicated just like the catalog, and then you don't have to worry about trying to ensure that the file creation timestamp in the filesystem is right... But then some customer comes and wants it to mean when was this replica database created ? 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] Re: Proposal: Store timestamptz of database creation on pg_database
* Hannu Krosing (ha...@krosing.net) wrote: But then some customer comes and wants it to mean when was this replica database created ? That's an entirely different question, imv, than what we're talking about. I'm not saying that it won't be asked, but as it's a different question, we can look to answer it in a different way. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Re: Proposal: Store timestamptz of database creation on pg_database
On 01/03/2013 02:42 PM, Stephen Frost wrote: * Hannu Krosing (ha...@krosing.net) wrote: But then some customer comes and wants it to mean when was this replica database created ? That's an entirely different question, imv, than what we're talking about. I'm not saying that it won't be asked, but as it's a different question, we can look to answer it in a different way. How is what does database creation date mean? a different question ? It is same question as : what is the creation date of db when I create a replica of my database from backup? does it depend on how I restore my replica ? can I restore it from pg_dump and still have same creation date ? etc. etc. 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] Re: Proposal: Store timestamptz of database creation on pg_database
On Thu, Jan 3, 2013 at 8:46 AM, Hannu Krosing ha...@2ndquadrant.com wrote: How is what does database creation date mean? a different question ? It is same question as : what is the creation date of db when I create a replica of my database from backup? does it depend on how I restore my replica ? can I restore it from pg_dump and still have same creation date ? etc. etc. I think you (and Tom) are doing an excellent job of making a simple problem seem complicated. Suppose a man comes walking out of the desert looking exhausted and collapses on our front doorstep, muttering, in a semi-conscious state, the single word water. Now this is a somewhat incoherent utterance, so there are several objections that might be raised: - It is not clear what the man wants done with the water. - The amount of water to be provided is unspecified. - Does he want tap water, bottled water, or club soda? - Furthermore, if we do give him water, he might go on to ask for a few crackers and a phone call; we could end up spending the whole morning on this. - In a situation of extreme thirst, a solution involving a proper electrolyte balance would likely be superior to plain water. Of course, these objections miss the point. Even an imperfect solution will be better than no solution at all. And it is very likely that if we simply provide whatever hydrating agent lies closest to hand, we'll get full marks. Similarly, in the present situation, I believe that there is little reason to suppose that the simplest possible implementation of this feature won't resolve the overwhelming majority of the needs that people have. We have many features about which users might raise the same kinds of questions that you are raising about this one, and they do, and those questions are perfectly valid. But they are not reasons to remove those features, and the questions you raise are not reasons to avoid having this one. They are simply things that must be documented and explained, just as we need to do with every other feature we ship. And if someone is not perfectly happy with the design, it won't be the first time for that, either. It does not mean that it's worse than not having anything. -- 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] Re: Proposal: Store timestamptz of database creation on pg_database
On 1/3/13 6:34 AM, Hannu Krosing 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 too many things change those. Moving to a different tablespace, a rewriting ALTER TABLE, etc. Can't we actually fix these to preserve file creation date like tar does and still keep unix file semantics ? I don't think that would be a good idea, because various file system tools might actually want to look at, say, the mtime to know what to back up. Also, none of those file attributes are the *creation* time, so we wouldn't actually solve the original problem. -- 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] Re: Proposal: Store timestamptz of database creation on pg_database
On 01/03/2013 03:09 PM, Robert Haas wrote: On Thu, Jan 3, 2013 at 8:46 AM, Hannu Krosing ha...@2ndquadrant.com wrote: How is what does database creation date mean? a different question ? It is same question as : what is the creation date of db when I create a replica of my database from backup? does it depend on how I restore my replica ? can I restore it from pg_dump and still have same creation date ? etc. etc. ... Of course, these objections miss the point. Even an imperfect solution will be better than no solution at all. And it is very likely that if we simply provide whatever hydrating agent lies closest to hand, we'll get full marks. This is what I did with my sample pl/python function ;) Similarly, in the present situation, I believe that there is little reason to suppose that the simplest possible implementation of this feature won't resolve the overwhelming majority of the needs that people have. We have many features about which users might raise the same kinds of questions that you are raising about this one, and they do, and those questions are perfectly valid. But they are not reasons to remove those features, and the questions you raise are not reasons to avoid having this one. They are simply things that must be documented and explained, just as we need to do with every other feature we ship. And if someone is not perfectly happy with the design, it won't be the first time for that, either. It does not mean that it's worse than not having anything. If we made sure that things like CLUSTER or moving to another tablespace would keep file ctime, then this would answer 98% of requests . Even without keeping them, this would be giving the chap water ... So my proposal is to just have a pg_database_createtime(dbname) function and solve the simple part of the problem. - 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] Re: Proposal: Store timestamptz of database creation on pg_database
On Thu, Jan 3, 2013 at 11:15 AM, Hannu Krosing ha...@2ndquadrant.com wrote: This is what I did with my sample pl/python function ;) Yeah, except that the c in ctime does not stand for create, and therefore the function isn't necessarily reliable. The problem is even worse for tables, where a rewrite may remove the old file and create a new one. I mean, I'm not stupid about this: when I need to figure this kind of stuff out, I do in fact look at the file times - mtime, ctime, atime, whatever there is. Sometimes that turns out to be helpful, and sometimes it doesn't. An obvious example of the latter is when you're looking at a bunch of files that have just been untarred from a backup device. -- 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] Re: Proposal: Store timestamptz of database creation on pg_database
On Thu, Jan 3, 2013 at 12:27 PM, Robert Haas robertmh...@gmail.com wrote: On Thu, Jan 3, 2013 at 11:15 AM, Hannu Krosing ha...@2ndquadrant.com wrote: This is what I did with my sample pl/python function ;) Yeah, except that the c in ctime does not stand for create, and therefore the function isn't necessarily reliable. The problem is even worse for tables, where a rewrite may remove the old file and create a new one. I mean, I'm not stupid about this: when I need to figure this kind of stuff out, I do in fact look at the file times - mtime, ctime, atime, whatever there is. Sometimes that turns out to be helpful, and sometimes it doesn't. An obvious example of the latter is when you're looking at a bunch of files that have just been untarred from a backup device. Yep, and I think that the behaviour of tar pretty nicely characterizes what's troublesome here. It is quite likely that a tar run will *capture* the creation time of a file, but if you pull data from a tar archive, it is by no means obvious that the filesystem can or will accept that date and apply it to the extracted copy. I'd contrast pg_dump with tar in that the former is intended as more of a logical dump than the latter, so that, in keeping with Greg Stark's comments, these timestamps Should Not be captured or carried forward by pg_dump. The interaction with streaming replication is pretty analogous to the interaction one might expect to get out of filesystem snapshot technologies like DRBD, zfs, btrfs, LVM. If we put a creation time into pg_database or pg_class, then streaming replication will, as a physical replication mechanism, carry the timestamp forward into replicas, in pretty much exactly the same fashion that timestamps would be carried onto clones/snapshots by the filesystem snapshotting systems. And in contrast, I'd expect Andres Freund's logical replication infrastructure *NOT* to carry these dates over, but rather to establish fresh new creation dates on a replica. (And from a forensic perspective, that's a perfectly fine thing.) I imagine that we should be careful to put these forensic timestamps onto things with some care. - Putting them on pg_database seems like a fine idea. - Putting them on pg_attribute seems mighty dodgy; I don't expect I'd often care, and this change increases the size of an extremely heavily accessed system table - I am equivocal about putting them on pg_class. That increases the size of a pretty big, heavily accessed system table. - Perhaps there are other relevant tables (pg_event_trigger, pg_extension, FDW tables, pg_language, pg_proc, pg_tablespace); I don't feel so strongly about them, but if you're puzzling over what went wrong with an extension, event trigger, or FDW, time of creation seems like it might be useful. -- When confronted by a difficult problem, solve it by reducing it to the question, How would the Lone Ranger handle this? -- 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] Re: Proposal: Store timestamptz of database creation on pg_database
On Thu, Jan 3, 2013 at 12:54 PM, Christopher Browne cbbro...@gmail.com wrote: Yep, and I think that the behaviour of tar pretty nicely characterizes what's troublesome here. It is quite likely that a tar run will *capture* the creation time of a file, but if you pull data from a tar archive, it is by no means obvious that the filesystem can or will accept that date and apply it to the extracted copy. I'd contrast pg_dump with tar in that the former is intended as more of a logical dump than the latter, so that, in keeping with Greg Stark's comments, these timestamps Should Not be captured or carried forward by pg_dump. The interaction with streaming replication is pretty analogous to the interaction one might expect to get out of filesystem snapshot technologies like DRBD, zfs, btrfs, LVM. If we put a creation time into pg_database or pg_class, then streaming replication will, as a physical replication mechanism, carry the timestamp forward into replicas, in pretty much exactly the same fashion that timestamps would be carried onto clones/snapshots by the filesystem snapshotting systems. And in contrast, I'd expect Andres Freund's logical replication infrastructure *NOT* to carry these dates over, but rather to establish fresh new creation dates on a replica. (And from a forensic perspective, that's a perfectly fine thing.) I agree all around. And to take a step back and speak a bit more broadly about this, I believe that, more and more, we can't rely on the operating system to do things for us any more. Five or ten years ago, maybe people were running Linux, and PostgreSQL was a part of that. Now, more and more, people are running PostgreSQL, and Linux (or Windows, or some other OS) is a way to make that happen. At least when I talk to customers, places where the OS behavior bleeds into what the database server does are not viewed as features. Telling people that we use the OS collation facilities, or that we use the OS buffer cache, or that we don't provide a scheduler because Linux has cron and Windows has scheduled tasks, or that people should examine file timestamps to try to work out when a relation was created results in bemusement, or sometimes incredulity. Many people are understanding of the idea that we don't have the manpower to implement everything ourselves, but very few customers I've spoken with think that planning to rely on the OS facilities is a sound design principle. It's true, as we've often said here, that leveraging the OS facilities means that we get the benefit of improving OS facilities for free - but it also means that we never exceed what the OS facilities are able to provide. And frankly, as in this case, the OS facilities are often poorly suited to what users actually want. We obviously do not want to go bonkers and take over everything from the OS, but I don't think we should be afraid to rotate the knob a little bit in that direction. The fact that people are pushing us to go there is a sign of our success. We are the ecosystem. I do have a concern about catalog bloat. I think it would be easy to add so many knobs that we end up slowing the system down and bloating the size of an otherwise-empty database, or one with lots of SQL objects. Let's not do that. But let's not do nothing, either. -- 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] Re: Proposal: Store timestamptz of database creation on pg_database
Robert Haas wrote: Christopher Browne cbbro...@gmail.com wrote: these timestamps Should Not be captured or carried forward by pg_dump. If we put a creation time into pg_database or pg_class, then streaming replication will, as a physical replication mechanism, carry the timestamp forward into replicas And in contrast, I'd expect Andres Freund's logical replication infrastructure *NOT* to carry these dates over, but rather to establish fresh new creation dates on a replica. (And from a forensic perspective, that's a perfectly fine thing.) I agree all around. +1 My analogy would be to xmin in tuples. Anything that preserves that should preserve table creation timestamp. If the tuples' xmin values in the table receiving the data differ, the creation timestamp should, too. In my experience, this would have been valuable forensic information many times. Preserving xmin rather than aggressively freezing never has been or would have been useful to me. -Kevin -- 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] Re: Proposal: Store timestamptz of database creation on pg_database
On 01/03/2013 04:51 PM, Kevin Grittner wrote: Robert Haas wrote: Christopher Browne cbbro...@gmail.com wrote: these timestamps Should Not be captured or carried forward by pg_dump. If we put a creation time into pg_database or pg_class, then streaming replication will, as a physical replication mechanism, carry the timestamp forward into replicas And in contrast, I'd expect Andres Freund's logical replication infrastructure *NOT* to carry these dates over, but rather to establish fresh new creation dates on a replica. (And from a forensic perspective, that's a perfectly fine thing.) I agree all around. +1 My analogy would be to xmin in tuples. Anything that preserves that should preserve table creation timestamp. If the tuples' xmin values in the table receiving the data differ, the creation timestamp should, too. In my experience, this would have been valuable forensic information many times. Preserving xmin rather than aggressively freezing never has been or would have been useful to me. I don't especially have a horse in the race, but ISTM that if you want the information you want it to be able to persist across dump/restore, at least optionally. If you can happily lose it when you're forced to recover using a logical dump then it's not that important to you. cheers andrew -- 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] Re: Proposal: Store timestamptz of database creation on pg_database
Andrew Dunstan wrote: I don't especially have a horse in the race, but ISTM that if you want the information you want it to be able to persist across dump/restore, at least optionally. If you can happily lose it when you're forced to recover using a logical dump then it's not that important to you. On that point I guess we will just disagree. In my experience, if you are OK with a periodic pg_dump for your primary backup technique, then the data is just not that important to you. And if you drop and re-create a table from pg_dump output, that event is worth noting -- I would rather see the timestamp of applying the pg_dump output. When it comes to forensics, why don't we feel that it is worth preserving next available xid and every tuple's xmin and xmax through pg_dump? I don't think we should, but the arguments against trying to do it seem similar to me. They are newly created tables when you run the SQL generated by pg_dump, with fresh rows and indexes. To pretend otherwise seems to me to reduce the value of the feature. On the other hand, having one central way to deal with it for all object types seems to increase the value of the feature. -Kevin -- 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] Re: Proposal: Store timestamptz of database creation on pg_database
On 01/03/2013 02:30 PM, Kevin Grittner wrote: Andrew Dunstan wrote: I don't especially have a horse in the race, but ISTM that if you want the information you want it to be able to persist across dump/restore, at least optionally. If you can happily lose it when you're forced to recover using a logical dump then it's not that important to you. On that point I guess we will just disagree. In my experience, if you are OK with a periodic pg_dump for your primary backup technique, then the data is just not that important to you. Or the data doesn't change that much but in principle I agree with you. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC @cmdpromptinc - 509-416-6579 -- 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] Re: Proposal: Store timestamptz of database creation on pg_database
On Sat, Dec 29, 2012 at 10:26 AM, Andres Freund and...@2ndquadrant.com wrote: A shared table for event triggers sounds like it would be the far easier solution (9.4+ that is). The problem is that the event trigger table is a just a pointer to a function, and there's no procedure OID to store in that shared catalog unless you also have a proposal for making pg_proc into a shared catalog ... which would also require making pg_language into a shared catalog, and maybe a few others. -- 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] Re: Proposal: Store timestamptz of database creation on pg_database
* Robert Haas (robertmh...@gmail.com) wrote: On Sat, Dec 29, 2012 at 10:26 AM, Andres Freund and...@2ndquadrant.com wrote: A shared table for event triggers sounds like it would be the far easier solution (9.4+ that is). The problem is that the event trigger table is a just a pointer to a function, and there's no procedure OID to store in that shared catalog unless you also have a proposal for making pg_proc into a shared catalog ... which would also require making pg_language into a shared catalog, and maybe a few others. This was why I was suggesting that there be a single database in which the events would actually fire and that's where the function itself would also be stored. The information to pass to the function would have to be collected and represented logically from the calling database, of course, and it wouldn't be possible to make changes in the database where the modification happened without using something like dblink, but I could still see there being a lot of good use cases for such a thing. All pie-in-the-sky currently though, of course, but that's along the lines of what I was thinking. Thanks, Stephen signature.asc Description: Digital signature
[HACKERS] Re: Proposal: Store timestamptz of database creation on pg_database
On 2012-12-29 09:59:49 -0500, Stephen Frost wrote: * 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. I don't think autonomous transactions are the biggest worry here. Transactions essentially already span multiple databases, so thats not really a problem in this context. Making it possible to change catalogs while still being active in another database seems *far* harder. To the point where I would say its not really feasible. A shared table for event triggers sounds like it would be the far easier solution (9.4+ that is). Greetings, Andres Freund -- Andres Freund http://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
[HACKERS] Re: Proposal: Store timestamptz of database creation on pg_database
* Andres Freund (and...@2ndquadrant.com) wrote: I don't think autonomous transactions are the biggest worry here. Transactions essentially already span multiple databases, so thats not really a problem in this context. Making it possible to change catalogs while still being active in another database seems *far* harder. To the point where I would say its not really feasible. There's two pieces- one is changing catalogs and the other is being able to have multiple top-level transactions running in a single backend. I agree that transactions already span multiple databases but I was expecting the global event trigger to need to run in its own transaction in the other database, similar to autonomous transactions (though those could be running in the same database, thus omitting the catalog switch issue). Spawning a new backend which connects to any database and gets a new transaction would handle both, which is what I was thinking about. A shared table for event triggers sounds like it would be the far easier solution (9.4+ that is). But what happens when it fires and tries to insert a record into a table..? Does that table have to exist in every database or the event fails? If it exists in every database, the admin/user/whomever has to go hunting through all the databases to get a complete picture.. Neither is very good, imv. Thanks, Stephen signature.asc Description: Digital signature
