Re: [HACKERS] 9.4 Proposal: Initdb creates a single table
On 24 April 2014 05:32, David Fetter da...@fetter.org wrote: On Wed, Apr 23, 2014 at 08:27:52PM -0400, Tom Lane wrote: David Fetter da...@fetter.org writes: Is there any good reason not to roll native UUID generation into our standard distribution? It's already there (as of 9.4) in pg_crypto. Sorry I wasn't clear enough. Since contrib/pgcrypto is a module that might well not be installed, people can't just build software for PostgreSQL and have UUIDs available, certainly not in the sense that, for example, BIGSERIAL is. +1 to include in core - strange to have a UUID datatype in core but no means to generate -- Simon Riggs 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] 9.4 Proposal: Initdb creates a single table
On Thu, Apr 24, 2014 at 3:59 PM, Simon Riggs si...@2ndquadrant.com wrote: On 24 April 2014 05:32, David Fetter da...@fetter.org wrote: Since contrib/pgcrypto is a module that might well not be installed, people can't just build software for PostgreSQL and have UUIDs available, certainly not in the sense that, for example, BIGSERIAL is. +1 to include in core - strange to have a UUID datatype in core but no means to generate +1. -- Michael -- 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] 9.4 Proposal: Initdb creates a single table
On Wed, Apr 23, 2014 at 11:26 AM, David Rowley dgrowle...@gmail.com wrote: but for a long time I've thought that it would be nice if PostgreSQL came with an example database that had a number of tables, perhaps that mock up some easy to relate to real-world application. These would be very useful to use as examples in the documents instead of inventing them in the ad-hoc way that we currently do. Like here: http://www.postgresql.org/docs/9.3/static/tutorial-window.html I think that's a great idea. I'm not convinced it should be created by default in initdb, but a CREATE EXTENSION sample_data seems easy enough for newbies to use and has a good chance of getting merged into contrib. Regards, Marti -- 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] 9.4 Proposal: Initdb creates a single table
2014-04-24 15:40 GMT+02:00 Marti Raudsepp ma...@juffo.org: On Wed, Apr 23, 2014 at 11:26 AM, David Rowley dgrowle...@gmail.com wrote: but for a long time I've thought that it would be nice if PostgreSQL came with an example database that had a number of tables, perhaps that mock up some easy to relate to real-world application. These would be very useful to use as examples in the documents instead of inventing them in the ad-hoc way that we currently do. Like here: http://www.postgresql.org/docs/9.3/static/tutorial-window.html I think that's a great idea. I'm not convinced it should be created by default in initdb, but a CREATE EXTENSION sample_data seems easy enough for newbies to use and has a good chance of getting merged into contrib. Good idea Pavel Regards, Marti -- 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] 9.4 Proposal: Initdb creates a single table
Simon Riggs si...@2ndquadrant.com writes: On 24 April 2014 05:32, David Fetter da...@fetter.org wrote: Since contrib/pgcrypto is a module that might well not be installed, people can't just build software for PostgreSQL and have UUIDs available, certainly not in the sense that, for example, BIGSERIAL is. +1 to include in core - strange to have a UUID datatype in core but no means to generate The reason why there's no generation function in core is that there is no standardized, guaranteed-to-produce-a-universally-unique-value generation algorithm. That was the reason for not putting something in core when the type was first created, and I do not see that the technology has advanced. 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] 9.4 Proposal: Initdb creates a single table
On Thu, Apr 24, 2014 at 11:21 AM, Pavel Stehule pavel.steh...@gmail.comwrote: 2014-04-24 15:40 GMT+02:00 Marti Raudsepp ma...@juffo.org: On Wed, Apr 23, 2014 at 11:26 AM, David Rowley dgrowle...@gmail.com wrote: but for a long time I've thought that it would be nice if PostgreSQL came with an example database that had a number of tables, perhaps that mock up some easy to relate to real-world application. These would be very useful to use as examples in the documents instead of inventing them in the ad-hoc way that we currently do. Like here: http://www.postgresql.org/docs/9.3/static/tutorial-window.html I think that's a great idea. I'm not convinced it should be created by default in initdb, but a CREATE EXTENSION sample_data seems easy enough for newbies to use and has a good chance of getting merged into contrib. Good idea +1 This sample data can be very useful to many things: * trainning * automated tests * benchmark ??? -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] 9.4 Proposal: Initdb creates a single table
On 04/24/2014 04:57 PM, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: On 24 April 2014 05:32, David Fetter da...@fetter.org wrote: Since contrib/pgcrypto is a module that might well not be installed, people can't just build software for PostgreSQL and have UUIDs available, certainly not in the sense that, for example, BIGSERIAL is. +1 to include in core - strange to have a UUID datatype in core but no means to generate The reason why there's no generation function in core is that there is no standardized, guaranteed-to-produce-a-universally-unique-value generation algorithm. That was the reason for not putting something in core when the type was first created, and I do not see that the technology has advanced. Why can't we implement all 5 variants from http://en.wikipedia.org/wiki/Universally_unique_identifier and just warn about the dangers in documentation ? we could expose it something like next_uuid(version nr); As the article points out Since the identifiers have a finite size, it is possible for two differing items to share the same identifier. so it is a known limitation of UUID and not something PostgreSQL specific. Cheers -- Hannu Krosing PostgreSQL Consultant Performance, Scalability and High Availability 2ndQuadrant Nordic OÜ -- 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] 9.4 Proposal: Initdb creates a single table
Hannu Krosing ha...@2ndquadrant.com writes: On 04/24/2014 04:57 PM, Tom Lane wrote: The reason why there's no generation function in core is that there is no standardized, guaranteed-to-produce-a-universally-unique-value generation algorithm. That was the reason for not putting something in core when the type was first created, and I do not see that the technology has advanced. Why can't we implement all 5 variants from http://en.wikipedia.org/wiki/Universally_unique_identifier and just warn about the dangers in documentation ? Essentially, that would mean carrying around our own implementation of libuuid --- which includes a bunch of not-terribly-portable stuff, such as discovering the machine's MAC address(es). That's not really something I want to see us putting project manpower into. I wonder what it would take to adapt contrib/uuid-ossp to work on top of some other popular implementation of that code. We pretty much bet on the wrong horse when we picked the OSSP library to depend on, but otherwise I think the principle of using an external library was good. 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] 9.4 Proposal: Initdb creates a single table
On Thu, Apr 24, 2014 at 11:30:15AM -0400, Tom Lane wrote: Hannu Krosing ha...@2ndquadrant.com writes: On 04/24/2014 04:57 PM, Tom Lane wrote: The reason why there's no generation function in core is that there is no standardized, guaranteed-to-produce-a-universally-unique-value generation algorithm. That was the reason for not putting something in core when the type was first created, and I do not see that the technology has advanced. Why can't we implement all 5 variants from http://en.wikipedia.org/wiki/Universally_unique_identifier and just warn about the dangers in documentation ? Essentially, that would mean carrying around our own implementation of libuuid --- which includes a bunch of not-terribly-portable stuff, such as discovering the machine's MAC address(es). That's not really something I want to see us putting project manpower into. We don't need to do the not-terribly-portable stuff in the first round. For that, there could still be a bundled extension. The point is that UUIDs are nowhere near as usable as users have the right to expect, and we should fix that. I wonder what it would take to adapt contrib/uuid-ossp to work on top of some other popular implementation of that code. We pretty much bet on the wrong horse when we picked the OSSP library to depend on, but otherwise I think the principle of using an external library was good. So long as we can pick another horse later, sure. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] 9.4 Proposal: Initdb creates a single table
David Fetter da...@fetter.org writes: On Thu, Apr 24, 2014 at 11:30:15AM -0400, Tom Lane wrote: Essentially, that would mean carrying around our own implementation of libuuid --- which includes a bunch of not-terribly-portable stuff, such as discovering the machine's MAC address(es). That's not really something I want to see us putting project manpower into. We don't need to do the not-terribly-portable stuff in the first round. For that, there could still be a bundled extension. The point is that UUIDs are nowhere near as usable as users have the right to expect, and we should fix that. The reason that UUIDs aren't as usable as users have a right to expect is that the underlying technology doesn't meet their (your) expectations. Just because it's easy to imagine that there are universally unique identifiers doesn't mean that there actually *are* universally unique identifiers. There are only approximations with varying failure modes. This is not our fault, and I don't want us to get caught up in trying to fix a fundamentally broken concept --- which is what a generic uuidserial API would be. If you try to paper over the difficulties here, they'll just bite you on the rear someday. 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] 9.4 Proposal: Initdb creates a single table
Tom Lane wrote: This is not our fault, and I don't want us to get caught up in trying to fix a fundamentally broken concept --- which is what a generic uuidserial API would be. If you try to paper over the difficulties here, they'll just bite you on the rear someday. But we have non-colliding generation technology for OIDs in system catalogs. We could try to reuse the idea in a UUID generator: grab one value, try to insert; if it fails generate a new one, lather, rinse, repeat. This would make uuidserial not quite as simple as SERIAL pseudotype, of course. -- Á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] 9.4 Proposal: Initdb creates a single table
On 04/24/2014 08:00 PM, Alvaro Herrera wrote: Tom Lane wrote: This is not our fault, and I don't want us to get caught up in trying to fix a fundamentally broken concept --- which is what a generic uuidserial API would be. If you try to paper over the difficulties here, they'll just bite you on the rear someday. But we have non-colliding generation technology for OIDs in system catalogs. We could try to reuse the idea in a UUID generator: grab one value, try to insert; if it fails generate a new one, lather, rinse, repeat. Umm, UUID stands for Universally Unique IDentifier. That would hardly be *universally* unique. - Heikki -- 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] 9.4 Proposal: Initdb creates a single table
On 04/24/2014 10:00 AM, Alvaro Herrera wrote: Tom Lane wrote: This is not our fault, and I don't want us to get caught up in trying to fix a fundamentally broken concept --- which is what a generic uuidserial API would be. If you try to paper over the difficulties here, they'll just bite you on the rear someday. But we have non-colliding generation technology for OIDs in system catalogs. We could try to reuse the idea in a UUID generator: grab one value, try to insert; if it fails generate a new one, lather, rinse, repeat. This would make uuidserial not quite as simple as SERIAL pseudotype, of course. Tangentally related to this, I believe that PostgreSQL may be the only current user of the uuid-ossp code at this point. So we're going to end up maintaining it for the UUID contrib module, or dropping that module. Regardless, this all sounds like speculative 9.5ish conversation, and only vaguely related to Simon's original proposal. -- 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] 9.4 Proposal: Initdb creates a single table
Heikki Linnakangas wrote: On 04/24/2014 08:00 PM, Alvaro Herrera wrote: Tom Lane wrote: This is not our fault, and I don't want us to get caught up in trying to fix a fundamentally broken concept --- which is what a generic uuidserial API would be. If you try to paper over the difficulties here, they'll just bite you on the rear someday. But we have non-colliding generation technology for OIDs in system catalogs. We could try to reuse the idea in a UUID generator: grab one value, try to insert; if it fails generate a new one, lather, rinse, repeat. Umm, UUID stands for Universally Unique IDentifier. That would hardly be *universally* unique. I don't understand your point. I'm only replying to Tom's assertion that UUID generation might not be all that unique after all (or, in other words, AIUI, that the universally unique part of the name is wishful thinking and not an actual property of the real thing.) -- Á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] 9.4 Proposal: Initdb creates a single table
Alvaro Herrera wrote: Heikki Linnakangas wrote: On 04/24/2014 08:00 PM, Alvaro Herrera wrote: Tom Lane wrote: This is not our fault, and I don't want us to get caught up in trying to fix a fundamentally broken concept --- which is what a generic uuidserial API would be. If you try to paper over the difficulties here, they'll just bite you on the rear someday. But we have non-colliding generation technology for OIDs in system catalogs. We could try to reuse the idea in a UUID generator: grab one value, try to insert; if it fails generate a new one, lather, rinse, repeat. Umm, UUID stands for Universally Unique IDentifier. That would hardly be *universally* unique. I don't understand your point. I'm only replying to Tom's assertion that UUID generation might not be all that unique after all (or, in other words, AIUI, that the universally unique part of the name is wishful thinking and not an actual property of the real thing.) Oh, I think I see your point: it's that no matter what we do here, there would be no way to guarantee that a value we generate does not collide with any other value elsewhere (either on other uuidserial columns, or on other servers). Is that it? Because if it is, then I think the problem is that the UUID concept might be flawed yet users still want to use it, and we do no service by refusing to provide it on those grounds. Now I certainly don't want my rear bitten more than it already is, anyway. -- Á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] 9.4 Proposal: Initdb creates a single table
On 04/24/2014 08:23 PM, Alvaro Herrera wrote: Alvaro Herrera wrote: Heikki Linnakangas wrote: On 04/24/2014 08:00 PM, Alvaro Herrera wrote: Tom Lane wrote: This is not our fault, and I don't want us to get caught up in trying to fix a fundamentally broken concept --- which is what a generic uuidserial API would be. If you try to paper over the difficulties here, they'll just bite you on the rear someday. But we have non-colliding generation technology for OIDs in system catalogs. We could try to reuse the idea in a UUID generator: grab one value, try to insert; if it fails generate a new one, lather, rinse, repeat. Umm, UUID stands for Universally Unique IDentifier. That would hardly be *universally* unique. I don't understand your point. I'm only replying to Tom's assertion that UUID generation might not be all that unique after all (or, in other words, AIUI, that the universally unique part of the name is wishful thinking and not an actual property of the real thing.) Oh, I think I see your point: it's that no matter what we do here, there would be no way to guarantee that a value we generate does not collide with any other value elsewhere (either on other uuidserial columns, or on other servers). Is that it? Yep. Because if it is, then I think the problem is that the UUID concept might be flawed yet users still want to use it, and we do no service by refusing to provide it on those grounds. Well, we should make a reasonable effort to make them unique. If there is a reliable-enough way to generate UUIDs that doesn't depend on external libraries, by all means lets have it in core. I believe the reason we put gen_random_uuid() in pgcrypto is that it needs a good random number generator, and we don't trust plain old random() to be good enough for that. - Heikki -- 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] 9.4 Proposal: Initdb creates a single table
Alvaro Herrera alvhe...@2ndquadrant.com writes: Oh, I think I see your point: it's that no matter what we do here, there would be no way to guarantee that a value we generate does not collide with any other value elsewhere (either on other uuidserial columns, or on other servers). Not that way, anyway. Because if it is, then I think the problem is that the UUID concept might be flawed yet users still want to use it, and we do no service by refusing to provide it on those grounds. My point is a bit more subtle than that. It's perfectly reasonable to want a universally-unique identifier in some applications. The problem is that there is no perfect implementation, and so people really need to stop and think about which generator algorithm they're going to use and whether its particular failure modes are acceptable in their context. So I'm not eager to provide some easy to use API that encourages users to believe that no thought is required on their part. Having said that, though, the argument around whether such facilities belong in core seems to devolve to whether you want to buy into maintaining libuuid for ourselves (in fact worse than that: it's not clear that libuuid ever has worked on all our platforms, so we might be buying into some up-front porting work too). As Josh notes, we may be forced into that anyway if we don't want to walk away from contrib/uuid-ossp. But first I'd like to see a bit more research into what the alternatives might be... 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] 9.4 Proposal: Initdb creates a single table
On Thu, 2014-04-24 at 13:45 -0400, Tom Lane wrote: Having said that, though, the argument around whether such facilities belong in core seems to devolve to whether you want to buy into maintaining libuuid for ourselves (in fact worse than that: it's not clear that libuuid ever has worked on all our platforms, so we might be buying into some up-front porting work too). As Josh notes, we may be forced into that anyway if we don't want to walk away from contrib/uuid-ossp. I think we arrived at the conclusion a short while go that we wanted to stick a deprecation notice on contrib/uuid-ossp for the 9.4 release. I wrote an extension that wraps libuuid: https://github.com/petere/pglibuuid Between pgcrypto and that, is there anything missing that users of uuid-ossp would have had? -- 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] 9.4 Proposal: Initdb creates a single table
Simon Riggs si...@2ndquadrant.com writes: By now, some of you will be doubled over laughing as if this is an April fool joke. Indeed. 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] 9.4 Proposal: Initdb creates a single table
On 04/23/2014 02:11 PM, Simon Riggs wrote: I propose we add a single table called Postgres when we Initdb CREATE TABLE Postgres (Id Integer, Data Jsonb); Without particular comment on the need for the table, I'd be concerned about calling it postgres. My personal impression from Stack Overflow etc has been that users are readily confused by the fact that we have: - Database engine/system postgres - backend binary postgres (they see it in ps) - unix user postgres - Pg superuser postgres - database postgres Sure, there's an argument for running with the theme here, but I suspect using the name postgres for a default table will just muddy the waters a bit more. Even postgres_table would help. It *absolutely must* be lower case, whatever it is, IMO. If you're going for newest-of-the-newbies, the last thing you want to do is having them dealing with it being just Postgres in some places, and having to be Postgres in others. Personally, don't know if I'm convinced it's overly worth doing - but I think it's silly to dismiss without actually corralling up some users who're unfamiliar with Pg and watching them get started. I'd love to see some properly conducted usability studies of Pg, and something like this would fit in well. -- Craig Ringer 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] 9.4 Proposal: Initdb creates a single table
Hello if you are thinking about this direction, then store there some demo project. I am don't think so isolated table has significant price. Regards Pavel 2014-04-23 8:45 GMT+02:00 Craig Ringer cr...@2ndquadrant.com: On 04/23/2014 02:11 PM, Simon Riggs wrote: I propose we add a single table called Postgres when we Initdb CREATE TABLE Postgres (Id Integer, Data Jsonb); Without particular comment on the need for the table, I'd be concerned about calling it postgres. My personal impression from Stack Overflow etc has been that users are readily confused by the fact that we have: - Database engine/system postgres - backend binary postgres (they see it in ps) - unix user postgres - Pg superuser postgres - database postgres Sure, there's an argument for running with the theme here, but I suspect using the name postgres for a default table will just muddy the waters a bit more. Even postgres_table would help. It *absolutely must* be lower case, whatever it is, IMO. If you're going for newest-of-the-newbies, the last thing you want to do is having them dealing with it being just Postgres in some places, and having to be Postgres in others. Personally, don't know if I'm convinced it's overly worth doing - but I think it's silly to dismiss without actually corralling up some users who're unfamiliar with Pg and watching them get started. I'd love to see some properly conducted usability studies of Pg, and something like this would fit in well. -- Craig Ringer 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] 9.4 Proposal: Initdb creates a single table
Simon Riggs wrote: I propose we add a single table called Postgres when we Initdb CREATE TABLE Postgres (Id Integer, Data Jsonb); COMMENT ON TABLE Postgres IS 'Single table for quick start usage - design your database'; The purpose of this is to make the database immediately usable. By including this table in the default initdb it will mean that programs can rely on the existence of this table and begin working quicker. By now, some of you will be doubled over laughing as if this is an April fool joke. I don't mean it to be at all. The idea is to have a stupidly obvious and easy table that will potentially be usable by just about everyone, in any language. I am a PostgreSQL newbie. How is this table useful for me? I want to develop a database application. I want to store personal data like name and birth date! Actually, I feel confused. What should I do with this table? Is it part of the database system? Will the database be broken if I drop it? Do I have to ship it with my application? If you don't like it, don't use it. If you really dislike it, drop it. No, I'm not the kind of person who reads a manual to figure out what to do with this table. I want to start coding *right now*. But for new people coming to Postgres, they will have a data object to access and begin using the database immediately. Their code will work, their examples will work. OK, so they need to go back and think about the design, but at least they got it to work and will be encouraged to do more. I have found a sample application for personal data on the internet. How can I make it work with this table? Remember when we didn't have a database called Postgres? Remember how much simpler life is now? Remember that now. Good that you mention that! I have wondered what to do with it. When I first connected to PostgreSQL, I created a sample table, but the senior developer from the other office told me that this is the postgres database and that I shouldn't create any objects there. What is it good for? Can I delete it? Yours, Laurenz Albe -- 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] 9.4 Proposal: Initdb creates a single table
On 04/23/2014 03:20 PM, Albe Laurenz wrote: Good that you mention that! I have wondered what to do with it. When I first connected to PostgreSQL, I created a sample table, but the senior developer from the other office told me that this is the postgres database and that I shouldn't create any objects there. What is it good for? Can I delete it? A key difference between the postgres DB and a default table is that the postgres DB is very convenient with PostgreSQL's default of connecting to a DB of the same name as the user. We don't have a corresponding INSERT VALUES (42); SELECT fred; where there is some invisible implicit table name. Personally I wish Pg was able to start w/o connecting to any specific DB, but that's just not how the architecture works, and with that limitation the postgres DB seemed like a good compromise. (That said, it's really weird that the username of the superuser defaults to the current unix user when initdb'ing, but the db created by default is still always postgres). -- Craig Ringer 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] 9.4 Proposal: Initdb creates a single table
On 23 April 2014 07:14, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: By now, some of you will be doubled over laughing as if this is an April fool joke. Indeed. I do like to share the odd joke now and then, it has to be said. So I'm glad I enriched your day. I was taught that ideas are accepted in this order: first we think them a joke, then we perceive them as a threat, then they become obvious. I can't find a Wikipedia article to give that idea more weight (OK, that was a joke). Just trying to think about how to improve the out of the box experience in ways that others already consider obvious. -- Simon Riggs 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] 9.4 Proposal: Initdb creates a single table
Craig Ringer wrote: Good that you mention that! I have wondered what to do with it. When I first connected to PostgreSQL, I created a sample table, but the senior developer from the other office told me that this is the postgres database and that I shouldn't create any objects there. What is it good for? Can I delete it? A key difference between the postgres DB and a default table is that the postgres DB is very convenient with PostgreSQL's default of connecting to a DB of the same name as the user. I did not seriously want to dispute the value of the postgres DB, I just think that making things easier to understand for the newbie is *not* its greatest merit. It is mostly for the convenience of the administrator, right? Yours, Laurenz Albe -- 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] 9.4 Proposal: Initdb creates a single table
On Wed, Apr 23, 2014 at 6:11 PM, Simon Riggs si...@2ndquadrant.com wrote: I propose we add a single table called Postgres when we Initdb CREATE TABLE Postgres (Id Integer, Data Jsonb); COMMENT ON TABLE Postgres IS 'Single table for quick start usage - design your database'; The purpose of this is to make the database immediately usable. By including this table in the default initdb it will mean that programs can rely on the existence of this table and begin working quicker. I'm not quite sure it would serve the same purpose as to what you're proposing here, but for a long time I've thought that it would be nice if PostgreSQL came with an example database that had a number of tables, perhaps that mock up some easy to relate to real-world application. These would be very useful to use as examples in the documents instead of inventing them in the ad-hoc way that we currently do. Like here: http://www.postgresql.org/docs/9.3/static/tutorial-window.html In the above link we have some table called empsalary, but the new user can't go an execute that query to test it without first working out how to first create a table and insert some data into that table. It would be really nice if new users could create this example database somehow and then they could play around with the example queries we put in the manual. Regards David Rowley
Re: [HACKERS] 9.4 Proposal: Initdb creates a single table
On 04/23/2014 08:11 AM, Simon Riggs wrote: We start with a database called Postgres and a schema called Public. Yet we don't start up with any usable tables. I propose we add a single table called Postgres when we Initdb CREATE TABLE Postgres (Id Integer, Data Jsonb); COMMENT ON TABLE Postgres IS 'Single table for quick start usage - design your database'; The purpose of this is to make the database immediately usable. With default access controls it still aint :( And most of complaints I have heard are about the need of fidgeting with pg_hba.conf to get *anything* started. So maybe we could start with something like this at the end of initdb: createdb example psql example sample# create table stuff(id serial primary key, data jsonb); sample# grant all on stuff to public; And also have the following lines in pg_hba.conf for it local all all trust hostall all 127.0.0.1/32trust hostall all ::1/128 trust This would solve most of the frustration with starting pg development for newcomers Cheers Hannu By including this table in the default initdb it will mean that programs can rely on the existence of this table and begin working quicker. By now, some of you will be doubled over laughing as if this is an April fool joke. I don't mean it to be at all. The idea is to have a stupidly obvious and easy table that will potentially be usable by just about everyone, in any language. If you don't like it, don't use it. If you really dislike it, drop it. But for new people coming to Postgres, they will have a data object to access and begin using the database immediately. Their code will work, their examples will work. OK, so they need to go back and think about the design, but at least they got it to work and will be encouraged to do more. Remember when we didn't have a database called Postgres? Remember how much simpler life is now? Remember that now. We can also insert a single row, Id = 0 with Postgres sample data in it, but that seems a step too far. -- Hannu Krosing PostgreSQL Consultant Performance, Scalability and High Availability 2ndQuadrant Nordic OÜ -- 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] 9.4 Proposal: Initdb creates a single table
On 23 April 2014 09:26, David Rowley dgrowle...@gmail.com wrote: On Wed, Apr 23, 2014 at 6:11 PM, Simon Riggs si...@2ndquadrant.com wrote: I propose we add a single table called Postgres when we Initdb CREATE TABLE Postgres (Id Integer, Data Jsonb); COMMENT ON TABLE Postgres IS 'Single table for quick start usage - design your database'; The purpose of this is to make the database immediately usable. By including this table in the default initdb it will mean that programs can rely on the existence of this table and begin working quicker. I'm not quite sure it would serve the same purpose as to what you're proposing here, but for a long time I've thought that it would be nice if PostgreSQL came with an example database that had a number of tables, perhaps that mock up some easy to relate to real-world application. These would be very useful to use as examples in the documents instead of inventing them in the ad-hoc way that we currently do. Like here: http://www.postgresql.org/docs/9.3/static/tutorial-window.html +1 to the idea of an example database, used throughout the docs Sounds like a summer of code project. Since we don't have that now, it won't work for 9.4. I still like the idea of a database installed by default on initdb, by default. Packagers can of course do what they like. -- Simon Riggs 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] 9.4 Proposal: Initdb creates a single table
This seems like a much better idea - whereas a single table, related to nothing - on the other hand, is at best not very helpful (and it could be argued, might contribute to teaching poor data data design). Regards Mark On 23/04/14 19:13, Pavel Stehule wrote: Hello if you are thinking about this direction, then store there some demo project. I am don't think so isolated table has significant price. -- 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] 9.4 Proposal: Initdb creates a single table
* Simon Riggs (si...@2ndquadrant.com) wrote: +1 to the idea of an example database, used throughout the docs Sounds like a summer of code project. Agreed. I'll add it to the GSoC ideas page. Since we don't have that now, it won't work for 9.4. None of this is on the table for 9.4 as far as I'm concerned.. I still like the idea of a database installed by default on initdb, by default. Packagers can of course do what they like. I fail to see the point of adding something that's targetted at novice / end-users which 90% (yes, it's a random # that I pulled, but it's surely the majority, at least) of installs won't have. For my 2c, it'd also be a disservice to our users and to ourselves to encourage a design that minimizes the database's understanding of the data and greatly reduces the set of PG's capabilities that can be used. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] 9.4 Proposal: Initdb creates a single table
On 04/23/2014 03:28 PM, Stephen Frost wrote: * Simon Riggs (si...@2ndquadrant.com) wrote: +1 to the idea of an example database, used throughout the docs Sounds like a summer of code project. Agreed. I'll add it to the GSoC ideas page. I don't think this is a good GSoC project. Documentation-only projects are not eligible for GSoC (https://www.google-melange.com/gsoc/document/show/gsoc_program/google/gsoc2014/help_page#12._Are_proposals_for_documentation_work). Perhaps you can argue that a sample database is not documentation, but it's certainly not in the spirit of the program. - Heikki -- 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] 9.4 Proposal: Initdb creates a single table
On 23 April 2014 13:46, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 04/23/2014 03:28 PM, Stephen Frost wrote: * Simon Riggs (si...@2ndquadrant.com) wrote: +1 to the idea of an example database, used throughout the docs Sounds like a summer of code project. Agreed. I'll add it to the GSoC ideas page. I don't think this is a good GSoC project. Documentation-only projects are not eligible for GSoC (https://www.google-melange.com/gsoc/document/show/gsoc_program/google/gsoc2014/help_page#12._Are_proposals_for_documentation_work). Perhaps you can argue that a sample database is not documentation, but it's certainly not in the spirit of the program. Out of curiosity, had anyone seen this which some of us were briefly working on last year at PgCon's unconference? https://github.com/pvh/postgresql-sample-database -- Thom -- 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] 9.4 Proposal: Initdb creates a single table
Heikki, * Heikki Linnakangas (hlinnakan...@vmware.com) wrote: On 04/23/2014 03:28 PM, Stephen Frost wrote: Agreed. I'll add it to the GSoC ideas page. I don't think this is a good GSoC project. Documentation-only projects are not eligible for GSoC (https://www.google-melange.com/gsoc/document/show/gsoc_program/google/gsoc2014/help_page#12._Are_proposals_for_documentation_work). Ah, right, of course. Sorry, I've gotten to thinking of the GSoC page as being a TODO but for small projects. :) Perhaps you can argue that a sample database is not documentation, but it's certainly not in the spirit of the program. Nah, that wasn't my intent at all. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] 9.4 Proposal: Initdb creates a single table
On Wed, Apr 23, 2014 at 10:11 AM, Simon Riggs si...@2ndquadrant.com wrote: We start with a database called Postgres and a schema called Public. Yet we don't start up with any usable tables. I propose we add a single table called Postgres when we Initdb CREATE TABLE Postgres (Id Integer, Data Jsonb); COMMENT ON TABLE Postgres IS 'Single table for quick start usage - design your database'; The purpose of this is to make the database immediately usable. By including this table in the default initdb it will mean that programs can rely on the existence of this table and begin working quicker. By now, some of you will be doubled over laughing as if this is an April fool joke. I don't mean it to be at all. I can propose contrib PostgreNoSQL providing following: 1) Table postgres as you proposed. 2) Functions: get_postgres(id intgeger) returns jsonb, set_postgres(id integer, data jsonb) returns void, search_postgres(query jsonb) returns setof postgres. search_postgres will have semantics of @ jsonb operator 3) Background workers which provides HTTP wrapper over those functions. -- With best regards, Alexander Korotkov.
Re: [HACKERS] 9.4 Proposal: Initdb creates a single table
On Wed, Apr 23, 2014 at 4:50 AM, Simon Riggs si...@2ndquadrant.com wrote: On 23 April 2014 07:14, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: By now, some of you will be doubled over laughing as if this is an April fool joke. Indeed. I do like to share the odd joke now and then, it has to be said. So I'm glad I enriched your day. I was taught that ideas are accepted in this order: first we think them a joke, then we perceive them as a threat, then they become obvious. I can't find a Wikipedia article to give that idea more weight (OK, that was a joke). Just trying to think about how to improve the out of the box experience in ways that others already consider obvious. Mahatma Gandhi said: First they ignore you, then they laugh at you, then they fight you, then you win. ;-) More info in [1] [2]. Regards, [1] http://en.wikiquote.org/wiki/Mahatma_Gandhi [2] http://www.brainyquote.com/quotes/quotes/m/mahatmagan103630.html -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] 9.4 Proposal: Initdb creates a single table
On 04/23/2014 07:43 AM, Alexander Korotkov wrote: I can propose contrib PostgreNoSQL providing following: 1) Table postgres as you proposed. 2) Functions: get_postgres(id intgeger) returns jsonb, set_postgres(id integer, data jsonb) returns void, search_postgres(query jsonb) returns setof postgres. search_postgres will have semantics of @ jsonb operator 3) Background workers which provides HTTP wrapper over those functions. You're forgetting ... sharding/replication over multiple masters. Also, the id should be a text value so that folks can use hash keys, or whatever. -- 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] 9.4 Proposal: Initdb creates a single table
Josh Berkus wrote: On 04/23/2014 07:43 AM, Alexander Korotkov wrote: I can propose contrib PostgreNoSQL providing following: 1) Table postgres as you proposed. 2) Functions: get_postgres(id intgeger) returns jsonb, set_postgres(id integer, data jsonb) returns void, search_postgres(query jsonb) returns setof postgres. search_postgres will have semantics of @ jsonb operator 3) Background workers which provides HTTP wrapper over those functions. You're forgetting ... sharding/replication over multiple masters. Also, the id should be a text value so that folks can use hash keys, or whatever. We should totally have a type uuidserial. -- Á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] 9.4 Proposal: Initdb creates a single table
On Wed, Apr 23, 2014 at 02:26:50PM -0300, Alvaro Herrera wrote: Josh Berkus wrote: On 04/23/2014 07:43 AM, Alexander Korotkov wrote: I can propose contrib PostgreNoSQL providing following: 1) Table postgres as you proposed. 2) Functions: get_postgres(id intgeger) returns jsonb, set_postgres(id integer, data jsonb) returns void, search_postgres(query jsonb) returns setof postgres. search_postgres will have semantics of @ jsonb operator 3) Background workers which provides HTTP wrapper over those functions. You're forgetting ... sharding/replication over multiple masters. Also, the id should be a text value so that folks can use hash keys, or whatever. We should totally have a type uuidserial. Something like it, certainly. One thing SQL Server does right is to have an opaque identity column, for which UUID would do an admirable job. We would need to build UUID functionality in, and I don't see this as a hard task. Should I draft it up as a self-contained extension? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] 9.4 Proposal: Initdb creates a single table
On Wed, Apr 23, 2014 at 12:24:21PM -0700, David Fetter wrote: On Wed, Apr 23, 2014 at 02:26:50PM -0300, Alvaro Herrera wrote: Josh Berkus wrote: On 04/23/2014 07:43 AM, Alexander Korotkov wrote: I can propose contrib PostgreNoSQL providing following: 1) Table postgres as you proposed. 2) Functions: get_postgres(id intgeger) returns jsonb, set_postgres(id integer, data jsonb) returns void, search_postgres(query jsonb) returns setof postgres. search_postgres will have semantics of @ jsonb operator 3) Background workers which provides HTTP wrapper over those functions. You're forgetting ... sharding/replication over multiple masters. Also, the id should be a text value so that folks can use hash keys, or whatever. We should totally have a type uuidserial. Something like it, certainly. One thing SQL Server does right is to have an opaque identity column, for which UUID would do an admirable job. We would need to build UUID functionality in, and I don't see this as a hard task. Should I draft it up as a self-contained extension? So I did a little research, and it appears that there's a part of util-linux that does UUIDs and is available under the 3-clause BSDL. It only does time- and urandom-based UUIDs, but that's probably a better start than nothing. Is there any good reason not to roll native UUID generation into our standard distribution? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] 9.4 Proposal: Initdb creates a single table
David Fetter da...@fetter.org writes: Is there any good reason not to roll native UUID generation into our standard distribution? It's already there (as of 9.4) in pg_crypto. 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] 9.4 Proposal: Initdb creates a single table
On Wed, Apr 23, 2014 at 08:27:52PM -0400, Tom Lane wrote: David Fetter da...@fetter.org writes: Is there any good reason not to roll native UUID generation into our standard distribution? It's already there (as of 9.4) in pg_crypto. Sorry I wasn't clear enough. Since contrib/pgcrypto is a module that might well not be installed, people can't just build software for PostgreSQL and have UUIDs available, certainly not in the sense that, for example, BIGSERIAL is. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
