Thread: [OT] MySQL is bad, but THIS bad?
Sorry to interrupt, but I have had the "opportinuty" to have to work with MySQL. This nice little gem is packed away in the reference for mysql_use_result(). "On the other hand, you shouldn't use mysql_use_result() if you are doing a lot of processing for each row on the client side, or if the output is sent to a screen on which the user may type a ^S (stop scroll). This ties up the server and prevent other threads from updating any tables from which the data is being fetched." How do busy web sites work like this? What is the best way to go about creating a "plug and play," PostgreSQL replacement for MySQL? I think the biggest problem getting PostgreSQL accepted is that so much code is available for MySQL.
Mark Woodward wrote: > Sorry to interrupt, but I have had the "opportinuty" to have to work with > MySQL. This nice little gem is packed away in the reference for > mysql_use_result(). > > "On the other hand, you shouldn't use mysql_use_result() if you are doing > a lot of processing for each row on the client side, or if the output is > sent to a screen on which the user may type a ^S (stop scroll). This ties > up the server and prevent other threads from updating any tables from > which the data is being fetched." > > How do busy web sites work like this? > > What is the best way to go about creating a "plug and play," PostgreSQL > replacement for MySQL? I think the biggest problem getting PostgreSQL > accepted is that so much code is available for MySQL. That reminds me of the Twilight Zone episode where the guy had a stopwatch that stopped time: http://en.wikipedia.org/wiki/A_Kind_of_a_Stopwatch_(The_Twilight_Zone) -- Bruce Momjian http://candle.pha.pa.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
On May 17, 2006, at 8:08 PM, Mark Woodward wrote: > What is the best way to go about creating a "plug and play," > PostgreSQL > replacement for MySQL? I think the biggest problem getting PostgreSQL > accepted is that so much code is available for MySQL. http://pgfoundry.org/projects/mysqlcompat/ John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL
* Mark Woodward: > "On the other hand, you shouldn't use mysql_use_result() if you are doing > a lot of processing for each row on the client side, or if the output is > sent to a screen on which the user may type a ^S (stop scroll). This ties > up the server and prevent other threads from updating any tables from > which the data is being fetched." > > How do busy web sites work like this? Any system based on locking exhibits this problem. Even with MVCC, you can run into it if you've got multiple writers. As a rule of thumb, I never perform network I/O within transactions which update the database (or "read the database", for systems without MVCC).
On Wed, May 17, 2006 at 09:35:34PM -0400, John DeSoi wrote: > > On May 17, 2006, at 8:08 PM, Mark Woodward wrote: > > >What is the best way to go about creating a "plug and play," > >PostgreSQL > >replacement for MySQL? I think the biggest problem getting PostgreSQL > >accepted is that so much code is available for MySQL. > > > http://pgfoundry.org/projects/mysqlcompat/ Even better would be coming up with a compatability mode, a la what EnterpriseDB has done for Oracle. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
All, > > >What is the best way to go about creating a "plug and play," > > >PostgreSQL > > >replacement for MySQL? I think the biggest problem getting PostgreSQL > > >accepted is that so much code is available for MySQL. > > > > http://pgfoundry.org/projects/mysqlcompat/ > > Even better would be coming up with a compatability mode, a la what > EnterpriseDB has done for Oracle. Um, no offense, but why? Do we really *want* to seek out thousands of users who won't pay anyone for support, don't care about data integrity, and regard the database as an enhanced flat file? Who adore non-standard syntax like `db_object_name` and REPLACE INTO? Who want to just get free downloads and not contribute to a project? Personally, I'd go after MSSQL before I bothered with MySQL. Sure, let's make *migration* easier for those who wake up and smell the BS, but migration can (and probably should) be one-way. If we're talking about other OSS projects, then I think it makes more sense for us to help those projects add PostgreSQL support. And do promote the projects that already *do* support us. -- Josh Berkus Aglio Database Solutions San Francisco
On May 18, 2006, at 12:24 PM, Jim C. Nasby wrote: > Even better would be coming up with a compatability mode, a la what > EnterpriseDB has done for Oracle. Right, you'll definitely need to hack the C source code to force PostgreSQL to accept invalid dates ;) http://sql-info.de/mysql/gotchas.html#1_14 John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL
Jim C. Nasby wrote: > On Wed, May 17, 2006 at 09:35:34PM -0400, John DeSoi wrote: >> On May 17, 2006, at 8:08 PM, Mark Woodward wrote: >> >>> What is the best way to go about creating a "plug and play," >>> PostgreSQL >>> replacement for MySQL? I think the biggest problem getting PostgreSQL >>> accepted is that so much code is available for MySQL. >> >> http://pgfoundry.org/projects/mysqlcompat/ > > Even better would be coming up with a compatability mode, a la what > EnterpriseDB has done for Oracle. Good Lord NO. I don't want a bunch of hacked up code *because* MySQL does it this way, running on top of PostgreSQL. I want to run PostgreSQL. If you want to run MySQL, run MySQL. If you want to run Oracle, run Oracle. Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutionssince 1997 http://www.commandprompt.com/
Josh Berkus wrote: > Personally, I'd go after MSSQL before I bothered with MySQL. Sure, let's > make *migration* easier for those who wake up and smell the BS, but migration > can (and probably should) be one-way. Yeah. Let's write a Transact-SQL PL handler. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Thu, 18 May 2006, Joshua D. Drake wrote: > Jim C. Nasby wrote: >> On Wed, May 17, 2006 at 09:35:34PM -0400, John DeSoi wrote: >>> On May 17, 2006, at 8:08 PM, Mark Woodward wrote: >>> >>>> What is the best way to go about creating a "plug and play," PostgreSQL >>>> replacement for MySQL? I think the biggest problem getting PostgreSQL >>>> accepted is that so much code is available for MySQL. >>> >>> http://pgfoundry.org/projects/mysqlcompat/ >> >> Even better would be coming up with a compatability mode, a la what >> EnterpriseDB has done for Oracle. > > Good Lord NO. I don't want a bunch of hacked up code *because* MySQL does it > this way, running on top of PostgreSQL. 'k, so you want "pure PostgreSQL" ... but, shouldn't it be possible, with all of our CREATE FUNCTION / RULES / etc features to create a 'translation layer' that could be loaded, like anything else in contrib? Hell, even if it gave an initial "in" for MySQL software developers to get their code running on PostgreSQL, and then when they come out that "do this is slower under PostgreSQL", they could optimize their code appropriately? ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
> > 'k, so you want "pure PostgreSQL" ... but, shouldn't it be possible, > with all of our CREATE FUNCTION / RULES / etc features to create a > 'translation layer' that could be loaded, like anything else in contrib? Sure but that isn't what was suggested :) > > Hell, even if it gave an initial "in" for MySQL software developers to > get their code running on PostgreSQL, and then when they come out that > "do this is slower under PostgreSQL", they could optimize their code > appropriately? I understand the idea but I personally don't like it. I am not really interested in cross-database compatible code. 9 times out of 10 it is hacky, slow and lacks a solid supportable model because you are always taking into account *the other* databases the application supports. And to be frank, I don't think we should waste our time on MySQL. It isn't a competitor, it just thinks it is. We should focus on our real competition which is DB2, MSSQL, and Oracle. If we want to create contrib modules that have types etc... that help port from Oracle to PostgreSQL or DB2 to PostgreSQL I am all for it. The first thing that comes to mind is a set of domains that implement Oracle types (names) as PostgreSQL types. Here's a start ;) postgres=# create domain varchar2 AS text; CREATE DOMAIN postgres=# create domain clob as text; CREATE DOMAIN postgres=# create domain blob as bytea; CREATE DOMAIN postgres=# create domain number as integer; CREATE DOMAIN postgres=# Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutionssince 1997 http://www.commandprompt.com/
On Thu, 18 May 2006, Joshua D. Drake wrote: > I understand the idea but I personally don't like it. I am not really > interested in cross-database compatible code. 9 times out of 10 it is > hacky, slow and lacks a solid supportable model because you are always > taking into account *the other* databases the application supports. I don't disagree about the 'hacky, slow and lacks ...' ... but, there are alot of MySQL based apps out there that are just *way too big* to convert wholesale to PostgreSQL that, in all honesty, are better suited to PostgreSQL ... To give someone a running chance at migrating it to PostgreSQL, a 'MySQL compatibility module' would allow them to just plug the existing DB in, and then work at improving sections of the code over time ... Hell, if done well, the module should be able to dump appropriately 'clean' PgSQL schemas ... as in your example elow about the domains ... something like: postgres=# create domain varchar2 AS text; CREATE DOMAIN postgres=# create table test ( mytext varchar2 ); should dump out to: create tabel test ( mytext text ); So, developer would be able to load the MySQL schema and run the application, but would also be able to improve the code in the application and dump out a PgSQL clean schema so that the next person, in theory, wouldn't even need the MySQL module ... the point isn't whether or not MySQL is a competitor ... the point is that there are *alot* of MySQL based applications out there that are a major PITA to convert (or get converted) all at once ... ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
Marc, > To give someone a running chance at migrating it to PostgreSQL, a 'MySQL > compatibility module' would allow them to just plug the existing DB in, > and then work at improving sections of the code over time ... Have you even looked at KL's mysqlcompat? -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco
On Thu, May 18, 2006 at 10:35:48AM -0700, Joshua D. Drake wrote: > I understand the idea but I personally don't like it. I am not > really interested in cross-database compatible code. 9 times out of > 10 it is hacky, slow and lacks a solid supportable model because you > are always taking into account *the other* databases the application > supports. > > And to be frank, I don't think we should waste our time on MySQL. It > isn't a competitor, it just thinks it is. We should focus on our > real competition which is DB2, MSSQL, and Oracle. > > If we want to create contrib modules that have types etc... that > help port from Oracle to PostgreSQL or DB2 to PostgreSQL I am all > for it. > > The first thing that comes to mind is a set of domains that > implement Oracle types (names) as PostgreSQL types. > > Here's a start ;) > > postgres=# create domain varchar2 AS text; > CREATE DOMAIN > postgres=# create domain clob as text; > CREATE DOMAIN > postgres=# create domain blob as bytea; > CREATE DOMAIN > postgres=# create domain number as integer; > CREATE DOMAIN > postgres=# postgres=# CREATE TABLE dual(); CREATE TABLE Cheers, D -- David Fetter <david@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote!
On Thu, May 18, 2006 at 02:56:12PM -0300, Marc G. Fournier wrote: > On Thu, 18 May 2006, Joshua D. Drake wrote: > > >I understand the idea but I personally don't like it. I am not really > >interested in cross-database compatible code. 9 times out of 10 it is > >hacky, slow and lacks a solid supportable model because you are always > >taking into account *the other* databases the application supports. > > I don't disagree about the 'hacky, slow and lacks ...' ... but, > there are alot of MySQL based apps out there that are just *way too > big* to convert wholesale to PostgreSQL that, in all honesty, are > better suited to PostgreSQL ... They're not too big when critical data is in there. Then it's a matter of what cost to make this wrong decision and have to fix it. > To give someone a running chance at migrating it to PostgreSQL, a > 'MySQL compatibility module' would allow them to just plug the > existing DB in, and then work at improving sections of the code over > time ... You cannot cross a chasm in two steps. MySQL apps don't generally lend themselves to incremental improvements because they are so tightly tied to MySQL's misbehaviors. It would be silly and dangerous to give people the misapprehension that this transition can be made painlessly or at low cost. Cheers, D -- David Fetter <david@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote!
On Thu, May 18, 2006 at 11:09:28AM -0700, David Fetter wrote: > postgres=# CREATE TABLE dual(); > CREATE TABLE You forgot to populate it. In reality I think you'd want dual to be a view on SELECT 1; or whatever the appropriate value is. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Thu, May 18, 2006 at 02:56:12PM -0300, Marc G. Fournier wrote: > the point isn't whether or not MySQL is a competitor ... the point is that > there are *alot* of MySQL based applications out there that are a major > PITA to convert (or get converted) all at once ... More importantly, there's a lot of MySQL *users*, and they get to influence which database is chosen in many companies. For many years, FreeBSD was far superior technologically to Linux, but Linux had the popularity to make it into the enterprise. And MySQL is much closer to being a competitor now than they were in 4.1. And feature-wise they'll probably equal PostgreSQL in the next release. Will the features be anywhere near as robust or well thought out? No. But in a heck of a lot of companies that doesn't matter. Maybe a compatability layer isn't worth doing, but I certainly think it's very much worthwhile for the community to do everything possible to encourage migration from MySQL. We should be able to lay claim to most advanced and most popular OSS database. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
> And MySQL is much closer to being a competitor now than they were in > 4.1. And feature-wise they'll probably equal PostgreSQL in the next > release. Will the features be anywhere near as robust or well thought > out? No. But in a heck of a lot of companies that doesn't matter. Your kidding right? Have you seen their "features"? Look at what their stored procedures are actually capable of. The only thing that MySQL *might* pull off is a really good storage backend finally. > Maybe a compatability layer isn't worth doing, but I certainly think > it's very much worthwhile for the community to do everything possible to > encourage migration from MySQL. We should be able to lay claim to most > advanced and most popular OSS database. Oh absolutely, I agree with you here but in order to do so in the most productive manner possible the community would have to be willing to be much more aggressive and much more antagnositic that I believe the community has the stomach for. Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutionssince 1997 http://www.commandprompt.com/
* Joshua D. Drake (jd@commandprompt.com) wrote: > And to be frank, I don't think we should waste our time on MySQL. It > isn't a competitor, it just thinks it is. We should focus on our real > competition which is DB2, MSSQL, and Oracle. One thing which would be kind of nice would be to have a mapping for the common commands. A friend of mine did this with a bit of perl so he could have a single SQL script that could run against both Postgres and Oracle. His thought was actually just an aliasing ability in psql so that you could, for example, alias 'desc' to '\d' and '@' to '\i'. This would have also helped me out with some projects for school- the main issues I ran into were that Oracle required a cascade-drop to include the keyword 'constraints' (which Postgres correctly forbids) and that Oracle uses 'MINUS' instead of 'EXCEPT' (where 'EXCEPT' is the SQL standard, which Oracle doesn't accept for some reason. :/). Just some thoughts, Stephen
On Thu, May 18, 2006 at 01:22:55PM -0500, Jim C. Nasby wrote: > On Thu, May 18, 2006 at 11:09:28AM -0700, David Fetter wrote: > > postgres=# CREATE TABLE dual(); > > CREATE TABLE > > You forgot to populate it. Oh, right. :) postgres=# CREATE TABLE dual AS SELECT 1; SELECT Cheers, D -- David Fetter <david@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote!
Jim C. Nasby wrote: > Maybe a compatability layer isn't worth doing, but I certainly think > it's very much worthwhile for the community to do everything possible to > encourage migration from MySQL. We should be able to lay claim to most > advanced and most popular OSS database. > We'll do that by concentrating on spiffy features, not compatibility layers. I want people to use PostgreSQL because it's the best, not because it's just like something else. cheers andrew
> Jim C. Nasby wrote: >> On Wed, May 17, 2006 at 09:35:34PM -0400, John DeSoi wrote: >>> On May 17, 2006, at 8:08 PM, Mark Woodward wrote: >>> >>>> What is the best way to go about creating a "plug and play," >>>> PostgreSQL >>>> replacement for MySQL? I think the biggest problem getting PostgreSQL >>>> accepted is that so much code is available for MySQL. >>> >>> http://pgfoundry.org/projects/mysqlcompat/ >> >> Even better would be coming up with a compatability mode, a la what >> EnterpriseDB has done for Oracle. > > Good Lord NO. I don't want a bunch of hacked up code *because* MySQL > does it this way, running on top of PostgreSQL. > > I want to run PostgreSQL. If you want to run MySQL, run MySQL. If you > want to run Oracle, run Oracle. It isn't always about what we want to do, unfortunately, sometimes it has to do with external factors outside of our control. The reality is that MySQL is widely supported by some very, shall we say, "interesting" open source projects and using these products with PostgreSQL would be a plus. I ask you, try to find some PHP/Open Source projects that support PostgreSQL just as well as MySQL? Most don't even support PostgreSQL.
> Jim C. Nasby wrote: >> Maybe a compatability layer isn't worth doing, but I certainly think >> it's very much worthwhile for the community to do everything possible to >> encourage migration from MySQL. We should be able to lay claim to most >> advanced and most popular OSS database. >> > > We'll do that by concentrating on spiffy features, not compatibility > layers. I want people to use PostgreSQL because it's the best, not > because it's just like something else. > While I do agree with the ideal, the reality may not be good enough. Even I, a PostgreSQL user for a decade, have to use MySQL right now because that is what the client uses. Again, there is so much code for MySQL, a MySQL emulation layer, MEL for short, could allow plug and play compatibility for open source, and closed source, applications that otherwise would force a PostgreSQL user to hold his or her nose and use MySQL.
John DeSoi wrote: > > Right, you'll definitely need to hack the C source code to force > PostgreSQL to accept invalid dates ;) > > http://sql-info.de/mysql/gotchas.html#1_14 > Couldn't we just install something that replaced invalid dates with a randomly generated but otherwise correct dates? That way they would become completely invisible. No one could even tell that the date was invalid to start with. Regards, Thomas Hallgren
> > While I do agree with the ideal, the reality may not be good enough. Even > I, a PostgreSQL user for a decade, have to use MySQL right now because > that is what the client uses. Then you aren't choosing your clients wisely. :) I am not trying to be rude, but if you don't want to use MySQL, don't. We don't and we are testament to the fact that it is indeed possible to make a living off of JUST PostgreSQL. The only time we touch a different database is to MIGRATE from that database. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutionssince 1997 http://www.commandprompt.com/
Mark Woodward wrote: >> Jim C. Nasby wrote: >> >>> Maybe a compatability layer isn't worth doing, but I certainly think >>> it's very much worthwhile for the community to do everything possible to >>> encourage migration from MySQL. We should be able to lay claim to most >>> advanced and most popular OSS database. >>> >>> >> We'll do that by concentrating on spiffy features, not compatibility >> layers. I want people to use PostgreSQL because it's the best, not >> because it's just like something else. >> >> > > While I do agree with the ideal, the reality may not be good enough. Even > I, a PostgreSQL user for a decade, have to use MySQL right now because > that is what the client uses. > > Again, there is so much code for MySQL, a MySQL emulation layer, MEL for > short, could allow plug and play compatibility for open source, and closed > source, applications that otherwise would force a PostgreSQL user to hold > his or her nose and use MySQL. > > > If we had infinite resources this might make sense. We don't, so it doesn't. There is a real cost to producing a compatibility layer, and the cost will be those spiffy new features. Let's get recursive queries, MERGE, and a couple more things and they will still be chasing our heels. cheers andrew
On Thu, May 18, 2006 at 02:58:11PM -0400, Mark Woodward wrote: > > Jim C. Nasby wrote: > >> On Wed, May 17, 2006 at 09:35:34PM -0400, John DeSoi wrote: > >>> On May 17, 2006, at 8:08 PM, Mark Woodward wrote: > >>> > >>>> What is the best way to go about creating a "plug and play," > >>>> PostgreSQL > >>>> replacement for MySQL? I think the biggest problem getting PostgreSQL > >>>> accepted is that so much code is available for MySQL. > >>> > >>> http://pgfoundry.org/projects/mysqlcompat/ > >> > >> Even better would be coming up with a compatability mode, a la what > >> EnterpriseDB has done for Oracle. > > > > Good Lord NO. I don't want a bunch of hacked up code *because* MySQL > > does it this way, running on top of PostgreSQL. > > > > I want to run PostgreSQL. If you want to run MySQL, run MySQL. If you > > want to run Oracle, run Oracle. > > It isn't always about what we want to do, unfortunately, sometimes it has > to do with external factors outside of our control. > > The reality is that MySQL is widely supported by some very, shall we say, > "interesting" open source projects and using these products with > PostgreSQL would be a plus. > > I ask you, try to find some PHP/Open Source projects that support > PostgreSQL just as well as MySQL? Most don't even support PostgreSQL. Which means most ISPs don't support PostgreSQL. And most OSS users don't get exposed to PostgreSQL. Which leads to more OSS that doesn't support PostgreSQL... -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Thomas Hallgren wrote: > John DeSoi wrote: >> >> Right, you'll definitely need to hack the C source code to force >> PostgreSQL to accept invalid dates ;) >> >> http://sql-info.de/mysql/gotchas.html#1_14 >> > Couldn't we just install something that replaced invalid dates with a > randomly generated but otherwise correct dates? That way they would > become completely invisible. No one could even tell that the date was > invalid to start with. > No we can't, because then we are taking an invalid date, which is potentially valid data (to the user) and modifying it to a valid date that is indeed invalid data. One of the reasons that mysql is just stupid. Joshua D. Drake > Regards, > Thomas Hallgren > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutionssince 1997 http://www.commandprompt.com/
Joshua D. Drake wrote: > Thomas Hallgren wrote: >> John DeSoi wrote: >>> >>> Right, you'll definitely need to hack the C source code to force >>> PostgreSQL to accept invalid dates ;) >>> >>> http://sql-info.de/mysql/gotchas.html#1_14 >>> >> Couldn't we just install something that replaced invalid dates with a >> randomly generated but otherwise correct dates? That way they would >> become completely invisible. No one could even tell that the date was >> invalid to start with. >> > > No we can't, because then we are taking an invalid date, which is > potentially valid data (to the user) and modifying it to a valid date > that is indeed invalid data. > > One of the reasons that mysql is just stupid. I do agree that its probably not worth allocating core resources to this, but spouting outdated FUD is really making you two look foolish. You have to understand that MySQL evolves just like PostgreSQL does. So you better focus on advertising where PostgreSQL shines instead of poking fun at something you apparently do not follow. regards, Lukas
Joshua D. Drake wrote: > Thomas Hallgren wrote:> >> Couldn't we just install something that replaced invalid dates with a >> randomly generated but otherwise correct dates? That way they would >> become completely invisible. No one could even tell that the date was >> invalid to start with. > > No we can't, because then we are taking an invalid date, which is > potentially valid data (to the user) and modifying it to a valid date > that is indeed invalid data. I think you should have read a `;-)' after Thomas' suggestion. ;-) Best Regards, Michael
On Thu, May 18, 2006 at 09:58:21PM +0200, Lukas Smith wrote: > Joshua D. Drake wrote: > > > >No we can't, because then we are taking an invalid date, which is > >potentially valid data (to the user) and modifying it to a valid > >date that is indeed invalid data. > > > >One of the reasons that mysql is just stupid. > > I do agree that its probably not worth allocating core resources to > this, but spouting outdated FUD is really making you two look > foolish. In what way is this outdated? Please provide a specific example. > You have to understand that MySQL evolves just like PostgreSQL does. If it were true, I would have to understand it, but the way MySQL evolves is not even remotely like the way PostgreSQL does. Here are a few concrete differences: * Community input. PostgreSQL's code all comes from the community. MySQL's all comes from employees of MySQL AB becauseMySQL AB requires that all developers sign over copyrights to MySQL AB. Independent developers are not naïf or foolishenough to contribute on those terms. * Standards compliance. PostgreSQL has bailed on quite a few features, and will in the future, in order to comply to SQLstandards. * Marketing departments. PGDG doesn't have one, and if it did, it wouldn't have the power to drive features. > So you better focus on advertising where PostgreSQL shines instead > of poking fun at something you apparently do not follow. You've made some sweeping allegations here and no specifics. Cheers, D -- David Fetter <david@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote!
> I do agree that its probably not worth allocating core resources to > this, but spouting outdated FUD is really making you two look foolish. And which FUD would this be? > > You have to understand that MySQL evolves just like PostgreSQL does. So > you better focus on advertising where PostgreSQL shines instead of > poking fun at something you apparently do not follow. Uhmmm, I am not even going to bother responding to this part. It is obvious that *you* don't follow MySQL versus PostgreSQL. No offense but the development models and thus evolution thereof are completely different. Sincerely, Joshua D. Drake > > regards, > Lukas > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutionssince 1997 http://www.commandprompt.com/
>> You've made some sweeping allegations here and no specifics. > > sweeping allegations? > > http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html > SET [GLOBAL|SESSION] sql_mode='ALLOW_INVALID_DATES' Which can be turned off or on by any mysql user. Not to mention the fact that it is even an option. Joshua D. Drake > follow its development. > > regards, > Lukas > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutionssince 1997 http://www.commandprompt.com/
On Thu, May 18, 2006 at 01:25:34PM -0700, Joshua D. Drake wrote: > > >I do agree that its probably not worth allocating core resources to > >this, but spouting outdated FUD is really making you two look foolish. > > And which FUD would this be? That Feb. 31st is a valid date in MySQL. You can now configure it to reject that (don't know if that's the default or not). > > > >You have to understand that MySQL evolves just like PostgreSQL does. So > >you better focus on advertising where PostgreSQL shines instead of > >poking fun at something you apparently do not follow. > > Uhmmm, I am not even going to bother responding to this part. It is > obvious that *you* don't follow MySQL versus PostgreSQL. > > No offense but the development models and thus evolution thereof are > completely different. You just proved one of my points. It's pretty easy for executives to understand that trying to store Feb. 31st in their database is probably a bad idea, but arguments about development models and their impact on software quality are likely to fall on deaf/befuddled ears. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Andrew Dunstan <andrew@dunslane.net> writes:
> Mark Woodward wrote:
>> Again, there is so much code for MySQL, a MySQL emulation layer, MEL for
>> short, could allow plug and play compatibility for open source, and closed
>> source, applications that otherwise would force a PostgreSQL user to hold
>> his or her nose and use MySQL.
>>
> If we had infinite resources this might make sense. We don't, so it
> doesn't. There is a real cost to producing a compatibility layer, and
> the cost will be those spiffy new features.
The real problem is that there's a whole lot of stuff, such as mysql's
weak error checking, that I don't think a "compatibility layer" could
sanely provide.
regards, tom lane
David Fetter wrote: > On Thu, May 18, 2006 at 09:58:21PM +0200, Lukas Smith wrote: > In what way is this outdated? Please provide a specific example. see below .. >> You have to understand that MySQL evolves just like PostgreSQL does. > > If it were true, I would have to understand it, but the way MySQL > evolves is not even remotely like the way PostgreSQL does. Here are a > few concrete differences: Ok, I was not trying to imply that MySQL evolves in the same way .. only that it also evolves. > You've made some sweeping allegations here and no specifics. sweeping allegations? http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html SET [GLOBAL|SESSION] sql_mode='ALLOW_INVALID_DATES' That being said the solution is still lacking. I even filed a bug report over this: http://bugs.mysql.com/bug.php?id=17998 So I guess you can continue your commentary. Anyways, it was not my intention to educate PostgreSQL developers about MySQL, only that it would be wise not to make general comments about MySQL if you do not follow its development. regards, Lukas
scrappy@postgresql.org ("Marc G. Fournier") writes:
> To give someone a running chance at migrating it to PostgreSQL, a
> 'MySQL compatibility module' would allow them to just plug the
> existing DB in, and then work at improving sections of the code over
> time ...
>
> Hell, if done well, the module should be able to dump appropriately
> 'clean' PgSQL schemas ... as in your example elow about the domains ...
You can't have that because you essentially need to throw out four
aspects of fairly vital "data validation" functionality:
1. Dates cease to be validatable.
2. NULL and 0 and '' are all roughly equivalent, even though they
aren't.
3. Foreign key constraints have to be ignored.
4. You have to fabricate a locale offering a case-insensitive sort
order.
I suppose #4 isn't "vital data validation"...
But after you "gut" the PostgreSQL-based system of those four aspects
of data integrity, I'm not sure there's any remaining benefit to
having PostgreSQL in play...
--
"cbbrowne","@","cbbrowne.com"
http://cbbrowne.com/info/unix.html
CBS News report on Fort Worth tornado damage:
"Eight major downtown buildings were severely damaged and 1,000 homes
were damaged, with 95 uninhabitable. Gov. George W. Bush declared
Tarrant County a disaster area. Federal Emergency Management Agency
workers are expected to arrive sometime next week after required
paperwork is completed."
>> > If we had infinite resources this might make sense. We don't, so it > doesn't. There is a real cost to producing a compatibility layer, > and the cost will be those spiffy new features. > > Let's get recursive queries, MERGE, and a couple more things and > they will still be chasing our heels. As a users of both Postgres and MySQL, I would also say, better add missing features to Postgres than chasing some specialties that are going to vanish anyway in MySQL. I miss : - a core full-text indexing engine. Tsearch2 is nice, but not included. This is a feature often used by PHP/MySQL packages. - per table or column choice of char set/encoding - better configurations or a tool to give the user some clue how to optimize the postgres settings (I know, the pro will know every time, but not every MySQL user - potential Postgres convert is a pro, by large More high end features like these are also very welcomed... - recursive queries - better partitioning - multi-master sync - the compression thread is also interesting (cf IBM DB2 Viper about this) Till now, the postgres project managed to produce amazing stuff. Keeping the project focused will very probably be more difficult as more users are converted... Best regards, Philippe Schmid
Lukas Smith wrote: > .. but spouting outdated FUD is really making you two look foolish. > Wow. On a scale from 1 to 10 measuring seriousness, I'd put my posting on -4. I'd advice you to take a step back and get some distance if you consider it "outdated FUD". Regards, Thomas Hallgren
On 5/18/06, Jim C. Nasby <jnasby@pervasive.com> wrote: > On Wed, May 17, 2006 at 09:35:34PM -0400, John DeSoi wrote: > > > > On May 17, 2006, at 8:08 PM, Mark Woodward wrote: > > > > >What is the best way to go about creating a "plug and play," > > >PostgreSQL > > >replacement for MySQL? I think the biggest problem getting PostgreSQL > > >accepted is that so much code is available for MySQL. > > > > > > http://pgfoundry.org/projects/mysqlcompat/ > > Even better would be coming up with a compatability mode, a la what > EnterpriseDB has done for Oracle. I don't think we actually want such a mode. In theory it would be great, but in practise... Especially from performance standpoint. I mean, application for MySQL would usually be written as: quick connect, do a query, disconnect. No application-level caching, no connection keep-alive. First run on a similar hardware would be a performance disaster. PostgreSQL connection initialisation will be slower (yes, pgpool, but how much do you want to bet, the reaction would be: "you said compatibility mode! no mention of pgpool!"), and no caching would be noticable, to say the least. I don't want to suggest adding caching to postgresql (I know it isn't that great). I want to suggest that MySQL users are used to it and will be unhappy to part from it. So, if you want to help people migrate to Postgres, better tell them how to fix mysqlisms. I mean: "no replace into"? tell them about create trigger on insert to handle duplicates. No caching, tell them about memcached and its advantages. Tell how to use pgpool. Personally, I wish I had better knowledge of PostgreSQL years back, when one project was started... I didn't know PostgreSQL then, and well, MySQL outperformed it. Of course when real load was given, MyISAM were a performance disaster, but then there was InnoDB... And sometimes some developer will select count(*) on InnoDB, and InnoDB will make a temporary table with all the data, holding the lock on the table... And sometimes the replication will just break, and sometimes you need repair table. All the usual fun. ;) While I can talk to the DBAs and tell them about table partitioning, PITR, MVCC, online backups, etc, etc. then add spice with PL/pgSQL and other PL/*s the real problem is people are used to MySQL -- -- they know how to handle it, how to support it, what to expect from it. The bigger the company, the harder it is to convince people that they should start using something completely new. An important application with 1 or 2 people who know what to do, when after a major failure, restart doesn't help. This makes it very hard... Personally my opinion is that there is no point in pushing PostgreSQL everywhere -- if there is no siginifcant performance gain, most managers will refuse it, on the grounds that "if it ain't (too) broke, don't fix it". The real places to "attack at" are the BIG dbs, the dataware housing applications. Places where MySQL is not used, because someones select count(*) should not kill the database. Because the queries take few hours to complete "by design". This should be doable. :) Regards, Dawid
On Thursday 18 May 2006 16:17, Philippe Schmid wrote:
> As a users of both Postgres and MySQL, I would also say, better add
> missing features to Postgres than chasing some specialties that are
> going to vanish anyway in MySQL.
> I miss :
> - a core full-text indexing engine. Tsearch2 is nice, but not
> included. This is a feature often used by PHP/MySQL packages.
We also need better support for non C locales in tsearch. As I was porting
mysql's sakila sample database I was reminded just how painful it is when you
initdb in a non-supported locale (which is probably the default on the
majority of distros out there)
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
On Thursday 18 May 2006 12:38, Josh Berkus wrote:
> Personally, I'd go after MSSQL before I bothered with MySQL. Sure, let's
> make *migration* easier for those who wake up and smell the BS, but
> migration can (and probably should) be one-way.
>
If you want to get users to swtich to your software from your competitors, you
have to eliminate barriers, and a big one for any database is getting locked
into a specific one. People aren't going to take the time to try switching
to postgresql if they can't easily make it back to thier former database.
It's one of the reasons why PostgreSQL's standards compliance is so
important; if you want to swtich to a new database, your best bet is to give
PostgreSQL a shot, because even if you don't like it, we're not going to try
and trap you into our software with bunches of non-standard knobs. Low
barrier to exit == low barrier to entry.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
> And MySQL is much closer to being a competitor now than they were in > 4.1. And feature-wise they'll probably equal PostgreSQL in the next > release. Will the features be anywhere near as robust or well thought > out? No. But in a heck of a lot of companies that doesn't matter. Don't forget that they got nested transactions and PITR both before us. They will also shortly have really nice partitioningbefore us... ...don't underestimate their development speed.
> We also need better support for non C locales in tsearch. As I was porting > mysql's sakila sample database I was reminded just how painful it is when you > initdb in a non-supported locale (which is probably the default on the > majority of distros out there) In 8.2 tsearch2 supports utf8...
> If you want to get users to swtich to your software from your competitors, you > have to eliminate barriers, and a big one for any database is getting locked > into a specific one. People aren't going to take the time to try switching > to postgresql if they can't easily make it back to thier former database. > It's one of the reasons why PostgreSQL's standards compliance is so > important; if you want to swtich to a new database, your best bet is to give > PostgreSQL a shot, because even if you don't like it, we're not going to try > and trap you into our software with bunches of non-standard knobs. Low > barrier to exit == low barrier to entry. Another reason why a tool to export from pgsql to mysql is just as important as the vice versa... Chris
Christopher Kings-Lynne wrote: >> And MySQL is much closer to being a competitor now than they were in >> 4.1. And feature-wise they'll probably equal PostgreSQL in the next >> release. Will the features be anywhere near as robust or well thought >> out? No. But in a heck of a lot of companies that doesn't matter. > > Don't forget that they got nested transactions and PITR both before us. > They will also shortly have really nice partitioning before us... > > ...don't underestimate their development speed. > Second that. In addition they have (early) in-memory multi-node clustering and Jim Starkey is writing them a new transactional storage engine to replace the probably-soon-to-be-license-hampered Innodb... Cheers Mark
Chris Browne wrote:
> scrappy@postgresql.org ("Marc G. Fournier") writes:
>> To give someone a running chance at migrating it to PostgreSQL, a
>> 'MySQL compatibility module' would allow them to just plug the
>> existing DB in, and then work at improving sections of the code over
>> time ...
>>
>> Hell, if done well, the module should be able to dump appropriately
>> 'clean' PgSQL schemas ... as in your example elow about the domains ...
>
> You can't have that because you essentially need to throw out four
> aspects of fairly vital "data validation" functionality:
>
> 1. Dates cease to be validatable.
>
> 2. NULL and 0 and '' are all roughly equivalent, even though they
> aren't.
>
> 3. Foreign key constraints have to be ignored.
>
> 4. You have to fabricate a locale offering a case-insensitive sort
> order.
>
> I suppose #4 isn't "vital data validation"...
>
> But after you "gut" the PostgreSQL-based system of those four aspects
> of data integrity, I'm not sure there's any remaining benefit to
> having PostgreSQL in play...
Assuming the objective with a transition would be to improve on things, an alternative
approach could be to offer a three step migration path:
1. A dump/restore utility that dumps a MySQL database and restores it into a PostgreSQL
database. This utility must have plugin capabilities where logic can be added that deals
with cases #1, #2, and #3 above. It might be as simple as just logging incorrect records to
a file and skip them. A pre-defined set of generic plugins could be supplied that did just
that. A user would have the chance to replace them with customized plugins to cover for
special cases in his own app. Perl or PHP would probably be good candidates for plugin language.
2. Provide an add-on to the PostgreSQL parser that would make it accept MySQL syntax. The
database would still run untainted underneath so from this point on, no more invalid dates
or foreign keys can be entered. Some other add-ons are needed as well to cater for some sane
but non-standard MySQL behavior that PostgreSQL is lacking.
3. A good user guide that helps the user to, over time, move away from the non standard
MySQL specific expressions. The objective being to at some point skip the MySQL syntax layer
altogether.
Regards,
Thomas Hallgren
(dead serious this time)
Christopher Kings-Lynne wrote: >> If you want to get users to swtich to your software from your >> competitors, you have to eliminate barriers, and a big one for any >> database is getting locked into a specific one. People aren't going >> to take the time to try switching to postgresql if they can't easily >> make it back to thier former database. It's one of the reasons why >> PostgreSQL's standards compliance is so important; if you want to >> swtich to a new database, your best bet is to give PostgreSQL a shot, >> because even if you don't like it, we're not going to try and trap you >> into our software with bunches of non-standard knobs. Low barrier to >> exit == low barrier to entry. > > Another reason why a tool to export from pgsql to mysql is just as > important as the vice versa... > If that's really true, then let's create a bidirectional compatibility layer as a joint venture with people from the MySQL camp. Should be a win-win situation. I somehow doubt that is the case. Important yes. But "just as important"? No way. We would loose big time on the export side since the vendor lock-in aspect is seriously out balanced by current levels of standards compliance. On the other hand, we'd win by order of magnitude on the import side. I bet the MySQL people would be utterly uninterested in such a venture. I think that if anything should be done, we should concentrate on import and let the MySQL people worry about going the other way. Once it becomes "just as imporant", they will. Regards, Thomas Hallgren
On Fri, May 19, 2006 13:25, Thomas Hallgren wrote: > If that's really true, then let's create a bidirectional compatibility > layer as a joint > venture with people from the MySQL camp. Should be a win-win situation. I > somehow doubt that > is the case. Important yes. But "just as important"? No way. I'm not too hopeful, for two reasons. First: MySQL is very, very different. I heard they just introduced a "create user" command like everybody else, but that's a drop in an ocean. I'm sure it's unintentional, but publishing a "quaint" SQL dialect amounts to a vendor-lock-in scheme--this time with the barn being locked before the cash cows have walked in. Second: management changes at MySQL seem to have favoured conventional business thinking over following the techs where no man has gone before. A year or two back we discussed porting libpqxx to MySQL so we'd have at least a strong, common C++ layer. Some of the technical people loved it, a provisional team was sketched out, and the idea was pitched to management. The argument: the more stable interfaces we share, the more confident corporate customers will feel adopting free databases. It didn't go anywhere. Reports I heard later amounted to "they don't see why they should spend the money." Jeroen
Am Freitag, 19. Mai 2006 02:35 schrieb Robert Treat: > On Thursday 18 May 2006 12:38, Josh Berkus wrote: > > Personally, I'd go after MSSQL before I bothered with MySQL. Sure, > > let's make *migration* easier for those who wake up and smell the BS, but > > migration can (and probably should) be one-way. > > If you want to get users to swtich to your software from your competitors, > you have to eliminate barriers, and a big one for any database is getting > locked into a specific one. People aren't going to take the time to try > switching to postgresql if they can't easily make it back to thier former > database. It's one of the reasons why PostgreSQL's standards compliance is > so important; if you want to swtich to a new database, your best bet is to > give PostgreSQL a shot, because even if you don't like it, we're not going > to try and trap you into our software with bunches of non-standard knobs. > Low barrier to exit == low barrier to entry. The way to go are standards. If postgresql supports standard-sql (like we all know it know), mysql-users has to justify their apps to use standard-sql. What they gain is not only compatibility with PostgreSQL but compatiblity with all database-servers, which supports this standard. They wont have much trouble to switch back to mysql or downgrade their postgresql to oracle ;-), if they follow standards. Also if PostgreSQL would have a compatibility-layer, it has to follow every quirk of mysql and will be measured by that. Much better is to promote users of mysql to use standards. Tommi
On Fri, May 19, 2006 at 01:26:34AM +0200, Dawid Kuroczko wrote: > Personally my opinion is that there is no point in pushing PostgreSQL > everywhere -- if there is no siginifcant performance gain, most managers > will refuse it, on the grounds that "if it ain't (too) broke, don't fix it". > The real places to "attack at" are the BIG dbs, the dataware housing > applications. Places where MySQL is not used, because someones > select count(*) should not kill the database. Because the queries > take few hours to complete "by design". This should be doable. :) The problem with limiting ourselves to going after only the 'high end' of databases is that MySQL is also pushing in that direction, but they have the advantage of a much larger user base than us. So in the not-to-distant future, a lot of people who are looking to come off of Oracle will look at both MySQL and PostgreSQL (in fact I'm sure there's already some people moving from Oracle to MySQL). When MySQL is at that point, which database do you think executives will be choosing? The one with a very large userbase and lots of marketing and PR that they've heard plenty about, or the one that might theoretically be technically superior but has a small userbase and they've never heard of? And if the technical people in the company are MySQL users, because that's the database they cut their teeth on... -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
When MySQL is at that > point, which database do you think executives will be choosing? The one > with a very large userbase and lots of marketing and PR that they've > heard plenty about, All due respect, Jim -- but don't you work for a publicly traded database company that happens to have its own version of PostgreSQL? This is really a discussion for your marketing (and mine frankly) then the PostgreSQL mailing lists :) Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutionssince 1997 http://www.commandprompt.com/
> Andrew Dunstan <andrew@dunslane.net> writes: >> Mark Woodward wrote: >>> Again, there is so much code for MySQL, a MySQL emulation layer, MEL >>> for >>> short, could allow plug and play compatibility for open source, and >>> closed >>> source, applications that otherwise would force a PostgreSQL user to >>> hold >>> his or her nose and use MySQL. >>> >> If we had infinite resources this might make sense. We don't, so it >> doesn't. There is a real cost to producing a compatibility layer, and >> the cost will be those spiffy new features. > > The real problem is that there's a whole lot of stuff, such as mysql's > weak error checking, that I don't think a "compatibility layer" could > sanely provide. > I kind of agree with this statement, but while I was playing devils's advocate and just grousing a bit about having to use MySQL, there is a sort of reality of "openomics" where mind-share is everything. The more mind-share you have, the more opportunities you have and the more resources become available. Not always, of course, look at OpenSSH, but for the most part. As MySQL adds features, not matter how poorly implemented, and maintain a migration path, we will never reach their users. PostgreSQL is better, true, but it is not ideal in many ways. It can be best said that the difference between PostgreSQL and MySQL is similar to the difference between Linux/BSD and Windows.
On Thu, May 18, 2006 at 02:58:11PM -0400, Mark Woodward wrote: > The reality is that MySQL is widely supported by some very, shall we say, > "interesting" open source projects and using these products with > PostgreSQL would be a plus. The biggest headache I find with using postgres is that various GPL licenced programs have trouble directly shipping postgresql support because of our use of OpenSSL. Each and every one of those program needs to add an exception to their licence for distributors to distribute postgresql support. I'm thinking particularly of FreeRadius but there are others. More than once I thought while waiting for stuff to compile: if I'd chosen mysql I'd be done by now... Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Martijn van Oosterhout wrote: > The biggest headache I find with using postgres is that various GPL > licenced programs have trouble directly shipping postgresql support > because of our use of OpenSSL. Each and every one of those program > needs to add an exception to their licence for distributors to > distribute postgresql support. They could distribute a non-ssl-enabled version, *if* they really need to include libpq in the package, or advise to to replace it with the common version if ssl is required. I bet >99 % of pgsql connections are not encrypted anyway. Regards, Andreas
Martijn van Oosterhout wrote: > On Thu, May 18, 2006 at 02:58:11PM -0400, Mark Woodward wrote: >> The reality is that MySQL is widely supported by some very, shall we say, >> "interesting" open source projects and using these products with >> PostgreSQL would be a plus. > > The biggest headache I find with using postgres is that various GPL > licenced programs have trouble directly shipping postgresql support > because of our use of OpenSSL. Each and every one of those program > needs to add an exception to their licence for distributors to > distribute postgresql support. Why would that be the case... OpenSSL and PostgreSQL both are BSD licensed... Am I missing something? Joshua D. Drake > > I'm thinking particularly of FreeRadius but there are others. More than > once I thought while waiting for stuff to compile: if I'd chosen mysql > I'd be done by now... > > Have a nice day, -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutionssince 1997 http://www.commandprompt.com/
Joshua D. Drake wrote: > Martijn van Oosterhout wrote: > >On Thu, May 18, 2006 at 02:58:11PM -0400, Mark Woodward wrote: > >>The reality is that MySQL is widely supported by some very, shall we say, > >>"interesting" open source projects and using these products with > >>PostgreSQL would be a plus. > > > >The biggest headache I find with using postgres is that various GPL > >licenced programs have trouble directly shipping postgresql support > >because of our use of OpenSSL. Each and every one of those program > >needs to add an exception to their licence for distributors to > >distribute postgresql support. > > Why would that be the case... OpenSSL and PostgreSQL both are BSD > licensed... Am I missing something? Advertising clause. PostgreSQL doesn't have it, OpenSSL does. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Joshua D. Drake wrote: > Martijn van Oosterhout wrote: >> On Thu, May 18, 2006 at 02:58:11PM -0400, Mark Woodward wrote: >>> The reality is that MySQL is widely supported by some very, shall we >>> say, >>> "interesting" open source projects and using these products with >>> PostgreSQL would be a plus. >> >> The biggest headache I find with using postgres is that various GPL >> licenced programs have trouble directly shipping postgresql support >> because of our use of OpenSSL. Each and every one of those program >> needs to add an exception to their licence for distributors to >> distribute postgresql support. > > Why would that be the case... OpenSSL and PostgreSQL both are BSD > licensed... Am I missing something? http://www.openssl.org/support/faq.html#LEGAL2 Of course, on that reasoning, they would need to provide a similar exception for libpq with or without openssl. More and more I love the fact that we don't play these games. cheers andrew
On Fri, 2006-05-19 at 09:11 -0700, Joshua D. Drake wrote: > Martijn van Oosterhout wrote: > > On Thu, May 18, 2006 at 02:58:11PM -0400, Mark Woodward wrote: > >> The reality is that MySQL is widely supported by some very, shall we say, > >> "interesting" open source projects and using these products with > >> PostgreSQL would be a plus. > > > > The biggest headache I find with using postgres is that various GPL > > licenced programs have trouble directly shipping postgresql support > > because of our use of OpenSSL. Each and every one of those program > > needs to add an exception to their licence for distributors to > > distribute postgresql support. > > Why would that be the case... OpenSSL and PostgreSQL both are BSD > licensed... Am I missing something? OpenSSL is not the 3 clause BSD license, it also includes a number of advertising clauses that the GPL has never liked -- GPL must not be modified for derivatives but the advertising clauses are in addition to the GPL, so it must be modified for the combination. Exceptions exist in the GPL for libraries and tools included in the operating system and this is enough in most cases. GPL applications on Windows may have problems. http://www.openssl.org/support/faq.html#LEGAL2 2. Can I use OpenSSL with GPL software? On many systems including the major Linux and BSD distributions, yes (the GPL does not place restrictions on using libraries that are part of the normal operating system distribution). On other systems, the situation is less clear. Some GPL software copyright holders claim that you infringe on their rights if you use OpenSSL with their software on operating systems that don't normally include OpenSSL. If you develop open source software that uses OpenSSL, you may find it useful to choose an other license than the GPL, or state explicitly that "This program is released under the GPL with the additional exemption that compiling, linking, and/or using OpenSSL is allowed." If you are using GPL software developed by others, you may want to ask the copyright holder for permission to use their software with OpenSSL. OpenSSL License* 1. Redistributions of source code must retain the above copyright* notice, this list of conditions andthe following disclaimer. ** 2. Redistributions in binary form must reproduce the above copyright* notice, this listof conditions and the following disclaimer in* the documentation and/or other materials provided with the* distribution.**3. All advertising materials mentioning features or use of this* software must display the following acknowledgment:* "This product includes software developed by the OpenSSL Project* for use in the OpenSSL Toolkit. (http://www.openssl.org/)"**4. The names "OpenSSL Toolkit" and "OpenSSL Project" must not be used to* endorse or promote products derived from this software without* prior written permission. For written permission,please contact* openssl-core@openssl.org.** 5. Products derived from this software may not be called "OpenSSL"* nor may "OpenSSL" appear in their names without prior written* permission of the OpenSSL Project.** 6. Redistributionsof any form whatsoever must retain the following* acknowledgment:* "This product includes software developedby the OpenSSL Project* for use in the OpenSSL Toolkit (http://www.openssl.org/)" > > I'm thinking particularly of FreeRadius but there are others. More than > > once I thought while waiting for stuff to compile: if I'd chosen mysql > > I'd be done by now... > > > > Have a nice day, --
Alvaro Herrera wrote: > Joshua D. Drake wrote: >> Martijn van Oosterhout wrote: >>> On Thu, May 18, 2006 at 02:58:11PM -0400, Mark Woodward wrote: >>>> The reality is that MySQL is widely supported by some very, shall we say, >>>> "interesting" open source projects and using these products with >>>> PostgreSQL would be a plus. >>> The biggest headache I find with using postgres is that various GPL >>> licenced programs have trouble directly shipping postgresql support >>> because of our use of OpenSSL. Each and every one of those program >>> needs to add an exception to their licence for distributors to >>> distribute postgresql support. >> Why would that be the case... OpenSSL and PostgreSQL both are BSD >> licensed... Am I missing something? > > Advertising clause. PostgreSQL doesn't have it, OpenSSL does. > Is that the same clause that caused the XFree86/X.Org fork? J -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutionssince 1997 http://www.commandprompt.com/
On Fri, May 19, 2006 at 11:29:23AM -0400, Mark Woodward wrote: > I kind of agree with this statement, but while I was playing devils's > advocate and just grousing a bit about having to use MySQL, there is a > sort of reality of "openomics" where mind-share is everything. > > The more mind-share you have, the more opportunities you have and the more > resources become available. Not always, of course, look at OpenSSH, but > for the most part. > > As MySQL adds features, not matter how poorly implemented, and maintain a > migration path, we will never reach their users. > > PostgreSQL is better, true, but it is not ideal in many ways. It can be > best said that the difference between PostgreSQL and MySQL is similar to > the difference between Linux/BSD and Windows. Actually, I think it's a lot more accurate to compare PostgreSQL and MySQL as FreeBSD vs Linux from about 5 years ago. Back then FreeBSD was clearly superior from a technology standpoint, and clearly playing second-fiddle when it came to users. And now, Linux is actually technically superior in most ways thanks to all the mindshare that's been poured into it. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
> Actually, I think it's a lot more accurate to compare PostgreSQL and > MySQL as FreeBSD vs Linux from about 5 years ago. Back then FreeBSD was > clearly superior from a technology standpoint, and clearly playing > second-fiddle when it came to users. And now, Linux is actually > technically superior in most ways thanks to all the mindshare that's > been poured into it. And with that, I am going to sit in a lawn chair and watch the bonfire. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutionssince 1997 http://www.commandprompt.com/
Moving to -advocacy, bcc to -hackers. On Fri, May 19, 2006 at 08:11:42AM -0700, Joshua D. Drake wrote: > When MySQL is at that > >point, which database do you think executives will be choosing? The one > >with a very large userbase and lots of marketing and PR that they've > >heard plenty about, > > All due respect, Jim -- but don't you work for a publicly traded > database company that happens to have its own version of PostgreSQL? Actually, we haven't had a distribution of PostgreSQL since 8.0.3, and even then it was only a distribution; the bits were all community. > This is really a discussion for your marketing (and mine frankly) then > the PostgreSQL mailing lists :) Yes and no... should MySQL eventually become popular enough that there's little use of PostgreSQL that hurts the community just as much as it hurts our companies. In fact, I'd say it's already hurting the community more than our companies; look at how many people lament about running software XYZ because it only supports MySQL. Or about trying to find PostgreSQL hosting providers. But yes, the group of PostgreSQL companies should also be working to raise awareness of PostgreSQL as a very viable OSS database. Unfortunately, a lot of the commercial interest is in the higher-end market. And to a large extent, this really needs to be a grass-roots effort. After all, you don't win OSS mindshare by taking out ads or anything like that. So I think this really needs to be a joint venture between companies and the community. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
> >> Actually, I think it's a lot more accurate to compare PostgreSQL and >> MySQL as FreeBSD vs Linux from about 5 years ago. Back then FreeBSD was >> clearly superior from a technology standpoint, and clearly playing >> second-fiddle when it came to users. And now, Linux is actually >> technically superior in most ways thanks to all the mindshare that's >> been poured into it. > > And with that, I am going to sit in a lawn chair and watch the bonfire. > Even I know that is NOT a discussion we want to start.
On Fri, May 19, 2006 at 03:39:23PM -0400, Mark Woodward wrote: > > > >> Actually, I think it's a lot more accurate to compare PostgreSQL and > >> MySQL as FreeBSD vs Linux from about 5 years ago. Back then FreeBSD was > >> clearly superior from a technology standpoint, and clearly playing > >> second-fiddle when it came to users. And now, Linux is actually > >> technically superior in most ways thanks to all the mindshare that's > >> been poured into it. > > > > And with that, I am going to sit in a lawn chair and watch the bonfire. > > Even I know that is NOT a discussion we want to start. Yeah, wasn't trying to start an OS flamewar; my point is that it's now pretty hard to find anything FreeBSD related/specific, and that the sheer popularity of Linux has given it a huge boost in terms of development. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
pgsql@mohawksoft.com ("Mark Woodward") writes:
>> Jim C. Nasby wrote:
>>> Maybe a compatability layer isn't worth doing, but I certainly
>>> think it's very much worthwhile for the community to do everything
>>> possible to encourage migration from MySQL. We should be able to
>>> lay claim to most advanced and most popular OSS database.
>>>
>>
>> We'll do that by concentrating on spiffy features, not
>> compatibility layers. I want people to use PostgreSQL because it's
>> the best, not because it's just like something else.
>>
>
> While I do agree with the ideal, the reality may not be good
> enough. Even I, a PostgreSQL user for a decade, have to use MySQL
> right now because that is what the client uses.
>
> Again, there is so much code for MySQL, a MySQL emulation layer, MEL
> for short, could allow plug and play compatibility for open source,
> and closed source, applications that otherwise would force a
> PostgreSQL user to hold his or her nose and use MySQL.
But this is essentially what killed off OS/2 in the marketplace.
IBM created a "good enough" emulation layer that it ran [early]
Windows(tm) applications sufficiently well that nobody bothered
porting applications to *properly* work with OS/2.
Microsoft then played off that with exceeding success; they made sure
that future versions of Windows(tm) were sufficiently different that
OS/2 was left orphaned.
We *are* in a sufficiently comparable state here; MySQL AB is *NOT*
our friend; they want to successfully 'take over the world,' at least
as far as they can do so with their product line...
--
"cbbrowne","@","cbbrowne.com"
http://cbbrowne.com/info/unix.html
CBS News report on Fort Worth tornado damage:
"Eight major downtown buildings were severely damaged and 1,000 homes
were damaged, with 95 uninhabitable. Gov. George W. Bush declared
Tarrant County a disaster area. Federal Emergency Management Agency
workers are expected to arrive sometime next week after required
paperwork is completed."
Rod Taylor wrote: > Exceptions exist in the GPL for libraries and tools included in the > operating system and this is enough in most cases. GPL applications on > Windows may have problems. What exception, exactly? Does an exception apply to libreadline, because list I looked, it didn't. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian wrote: > Rod Taylor wrote: > >> Exceptions exist in the GPL for libraries and tools included in the >> operating system and this is enough in most cases. GPL applications on >> Windows may have problems. >> > > What exception, exactly? Does an exception apply to libreadline, > because list I looked, it didn't. > > Yes, the exeption applies to libreadline, which is why we can deliver psql with libreadline linked on Linux, for example. But we can't on Windows or Solaris. cheers andrew
Andrew Dunstan wrote: > Bruce Momjian wrote: > > Rod Taylor wrote: > > > >> Exceptions exist in the GPL for libraries and tools included in the > >> operating system and this is enough in most cases. GPL applications on > >> Windows may have problems. > >> > > > > What exception, exactly? Does an exception apply to libreadline, > > because list I looked, it didn't. > > > > > > Yes, the exeption applies to libreadline, which is why we can deliver > psql with libreadline linked on Linux, for example. But we can't on > Windows or Solaris. OK, where do you see this exception? I have not. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
On Fri, May 19, 2006 at 04:41:20PM -0400, Bruce Momjian wrote: > > Yes, the exeption applies to libreadline, which is why we can deliver > > psql with libreadline linked on Linux, for example. But we can't on > > Windows or Solaris. > > OK, where do you see this exception? I have not. The exception is not relevent in this case. The exception is to allow GPL applications to work on non-free operating systems. Obviously a GPL'd application on Windows can never supply the source to the Win32 libraries. libreadline is not a problem because you can distribute postgresql compiled with readline and comply with all licences involved simultaneously. It doesn't work with openssl because the licence requires things that are incompatable with the GPL. The openssl faq suggest that you can take advantage of the exception, which reads: However, as a special exception, the source code distributed need not include anything that is normally distributed (ineither source or binary form) with the major components (compiler, kernel, and so on) of the operating system on whichthe executable runs, unless that component itself accompanies the executable. I don't buy that argument, and I'm not the only one. OpenSSL is an optional part of most Linuxes, so there's no way you can use that exception. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Ühel kenal päeval, R, 2006-05-19 kell 09:40, kirjutas Christopher Kings-Lynne: > > We also need better support for non C locales in tsearch. As I was porting > > mysql's sakila sample database I was reminded just how painful it is when you > > initdb in a non-supported locale (which is probably the default on the > > majority of distros out there) > > > In 8.2 tsearch2 supports utf8... Utf8 is encoding, but I guess that tsearch2 does not care much about locales ? tsearch2 does not do sorts, but it may care about upper()/lower() for languages that support it, so there our locale support should be good for utf8 encoding if we care about language-specific case insensitivity. -- ---------------- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com
On 5/19/06, Joshua D. Drake <jd@commandprompt.com> wrote: > And with that, I am going to sit in a lawn chair and watch the bonfire. This is one of the finest examples of unfocused discussions I've ever seen on -hackers... while surely entertaining, what a huge waste of time. -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation | fax: 732.331.1301 33 Wood Ave S, 2nd Floor | jharris@enterprisedb.com Iselin, New Jersey 08830 | http://www.enterprisedb.com/
Jonah H. Harris wrote: > On 5/19/06, Joshua D. Drake <jd@commandprompt.com> wrote: >> And with that, I am going to sit in a lawn chair and watch the bonfire. > > This is one of the finest examples of unfocused discussions I've ever > seen on -hackers... while surely entertaining, what a huge waste of > time. All discussions on mailing lists are unfocused and a waste of time to some degree, even the best of them because by its very nature, email is a time wasting tool. Have a great weekend! Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutionssince 1997 http://www.commandprompt.com/
Ühel kenal päeval, R, 2006-05-19 kell 22:53, kirjutas Martijn van Oosterhout: > libreadline is not a problem because you can distribute postgresql > compiled with readline and comply with all licences involved > simultaneously. oh? my impression was that we are clear, because libreadline is just one of "readline" implementations we support. > It doesn't work with openssl because the licence > requires things that are incompatable with the GPL. Still clients can compile/use libpq without OpenSSL and be on safe ground. > The openssl faq suggest that you can take advantage of the exception, > which reads: > > However, as a special exception, the source code distributed need not > include anything that is normally distributed (in either source or > binary form) with the major components (compiler, kernel, and so on) > of the operating system on which the executable runs, unless that > component itself accompanies the executable. > > I don't buy that argument, and I'm not the only one. OpenSSL is an > optional part of most Linuxes, so there's no way you can use that > exception. But on most linuxes optional parts ar also "normally distributed" :P Even network drivers may be counted optional for pure linux (kernel) experience, but are still normally distributed. -- ---------------- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com NOTICE: This communication contains privileged or other confidential information. If you have received it in error, please advise the sender by reply email and immediately delete the message and any attachments without copying or disclosing the contents.
Martijn van Oosterhout wrote: -- Start of PGP signed section. > On Fri, May 19, 2006 at 04:41:20PM -0400, Bruce Momjian wrote: > > > Yes, the exeption applies to libreadline, which is why we can deliver > > > psql with libreadline linked on Linux, for example. But we can't on > > > Windows or Solaris. > > > > OK, where do you see this exception? I have not. > > The exception is not relevent in this case. The exception is to allow > GPL applications to work on non-free operating systems. Obviously a > GPL'd application on Windows can never supply the source to the Win32 > libraries. > > libreadline is not a problem because you can distribute postgresql > compiled with readline and comply with all licences involved > simultaneously. It doesn't work with openssl because the licence > requires things that are incompatable with the GPL. My question is whether psql using libreadline.so has to be GPL, meaning the psql source has to be included in a binary distribution. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Joshua D. Drake wrote: > Jonah H. Harris wrote: > > On 5/19/06, Joshua D. Drake <jd@commandprompt.com> wrote: > >> And with that, I am going to sit in a lawn chair and watch the bonfire. > > > > This is one of the finest examples of unfocused discussions I've ever > > seen on -hackers... while surely entertaining, what a huge waste of > > time. > > All discussions on mailing lists are unfocused and a waste of time to > some degree, even the best of them because by its very nature, email is > a time wasting tool. Let's not forget my bad jokes. I am still chuckling at JavaZero. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
On Fri, May 19, 2006 at 07:04:47PM -0400, Bruce Momjian wrote: > > libreadline is not a problem because you can distribute postgresql > > compiled with readline and comply with all licences involved > > simultaneously. It doesn't work with openssl because the licence > > requires things that are incompatable with the GPL. > > My question is whether psql using libreadline.so has to be GPL, meaning > the psql source has to be included in a binary distribution. IANAL, but yes. Or any other of the methods allowed, like providing a written voucher valid for at least three years. People who feel they need to keep the source to psql secret should link against libeditline instead. The way I understand it, the GPL affects programs in two main ways: 1. A program which is GPL'd must, when distributed, be able to provide all source used to build it under terms compatable with the GPL. 2. A program which includes a GPL'd header file while building, must, when distributed, provide its own source and the library under GPL compatable terms, but not necessariliy the source of anything else needed to build it. This is why it's OK that psql links against openssl and readline. These are obviously only relevent when distributing precompiled binaries. If you are only distributing source, none of the above applies to you. There's a third method that some people claim, but I don't buy. This where a program using an interface of a GPL'd library somehow become a derived work of said library. That's just way whacked out. You may ofcourse disagree with any of the above, and hey, if you have a lawyer to back you up, who am I to argue? As for why you don't solve the problem by distributing a libpq not compiled against OpenSSL, well, that's a different question. Back when SSL was considered an arms exports by the US, having both SSL and non-SSL versions was common (and a big PITA). When that disappeared, the main reason for the split went away and people started compiling SSL by default. This solved the problem for 99% of programs. However, one tiny subset remains problematic: - A library implements SSL, but only using OpenSSL - The library doesn't use the GPL, or doesn't have an OpenSSL exception clause. - A GPL'd program uses this library, without an OpenSSL exception clause. In this subset of a subset of a subset of programs, it's a problem. Many libraries that implement SSL provide an alternative to OpenSSL, many programs using such libraries have exception clauses so that there's just a handful of programs and libraries that are problematic. As long as there's a possibility that the situation can change (either every GPL program using postgresql gains an exception clause, or postgresql might someday support some other library) it will probably stay this way. If the the postgresql core decides that OpenSSL will be the only SSL ever supported, no matter what, well, the split distribution may yet happen. In the meantime, we have status quo. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Hi, I really think that PostgreSQL could benefit from a packaged solution that incorporates a lot of the contrib stuff (tsearch2, maybe even some replication setups ..). I really like the approach that PostgreSQL is a clean yet highly extensible base from which other people can build their specific tools. However the fact of the matter is that MySQL provides a good enough, yet very easy to setup and do semi advanced things (like full text, replication etc). My key point here is _good enough_. This means there is obviously still an opportunity to give them something _better_, as long as it does not get in their way of being easy to setup. The improvements to the installer are great, but there simply needs to be a packaged solution that adds more of the things people are very likely to use. From my understanding Bizgres goes in that direction? I just think that whatever highly packaged solution PostgreSQL picks, this should be the download that is pushed at conferences, in articles and books. People with a clue will still know where they can get the clean base. regards, Lukas
On Sat, May 20, 2006 at 10:36:25AM +0200, Lukas Smith wrote: > The improvements to the installer are great, but there simply needs to > be a packaged solution that adds more of the things people are very > likely to use. From my understanding Bizgres goes in that direction? I > just think that whatever highly packaged solution PostgreSQL picks, this > should be the download that is pushed at conferences, in articles and > books. People with a clue will still know where they can get the clean base. There is a fantastic packaged solution already: apt-get install postgresql-8.1 postgresql-contrib-8.1 Voila! Tsearch installed at your fingertips. What else were you expecting? Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Martijn van Oosterhout wrote: > On Sat, May 20, 2006 at 10:36:25AM +0200, Lukas Smith wrote: >> The improvements to the installer are great, but there simply needs to >> be a packaged solution that adds more of the things people are very >> likely to use. From my understanding Bizgres goes in that direction? I >> just think that whatever highly packaged solution PostgreSQL picks, this >> should be the download that is pushed at conferences, in articles and >> books. People with a clue will still know where they can get the clean base. > > There is a fantastic packaged solution already: > > apt-get install postgresql-8.1 postgresql-contrib-8.1 > > Voila! Tsearch installed at your fingertips. What else were you > expecting? I expect this to be one package and I expect this to be what is pushed as the default package on all platforms. If someone just sat in an pgsql talk (or even a talk that mentions pgsql), has read an article, picked up a book .. this is what he should be downloading and installing. I do think that the name PostgreSQL has a fair amount of mindshare, but I do not think that this package needs to be called PostgreSQL necessarily. The problem with calling it PostgreSQL is that this would mean moving things into the core distribution which do not belong there. But expecting the unwashed masses to understand that they need to install contrib ontop of PostgreSQL is not a good idea. If PostgreSQL pushes FooSQL as its "packaged solution" at all opportunities I am sure it would quickly get into the heads of people and if done in a concerted effort along with the corporate sponsors it could provide for a huge marketing opportunity and a slew of articles from the press. But that is a topic for another list. regards, Lukas
Lukas Kahwe Smith wrote: ... >> apt-get install postgresql-8.1 postgresql-contrib-8.1 >> >> Voila! Tsearch installed at your fingertips. What else were you >> expecting? > > I expect this to be one package and I expect this to be what is pushed > as the default package on all platforms. If someone just sat in an pgsql > talk (or even a talk that mentions pgsql), has read an article, picked > up a book .. this is what he should be downloading and installing. ... > If PostgreSQL pushes FooSQL as its "packaged solution" at all > opportunities I am sure it would quickly get into the heads of people > and if done in a concerted effort along with the corporate sponsors it > could provide for a huge marketing opportunity and a slew of articles > from the press. But that is a topic for another list. > maybe the package should read: postgresql-heavy postgresql-complete or even as you seem to suggest: postgresql where the other parts are postgresql-clients, postgresql-server postgresql-contrib and so on. Beware, however, if "complete" means with gui clients, not all people would be happy if you pull X and friends to their unix servers :-) So whatever is in "complete" should depend on the target platform. I think the naming schema of the debian packages go in the right direction - maybe this can be harmonized along the distributions? Regards Tino Wildenhain
On 5/20/06, Lukas Smith <smith@pooteeweet.org> wrote:
> The improvements to the installer are great, but there simply needs to
> be a packaged solution that adds more of the things people are very
> likely to use. From my understanding Bizgres goes in that direction? I
> just think that whatever highly packaged solution PostgreSQL picks, this
> should be the download that is pushed at conferences, in articles and
> books. People with a clue will still know where they can get the clean base.
Hmm, a Comprehensive PostgreSQL Archive Network? ;)
I mean, something like CPAN, CTAN or CRAN? :)
I mean, the -contrib is great, but pushing other things there is a bit
tricky (to say the least) from the maintenance point of view. (Every
bugfix, a new release of -contrib, etc, etc...).
Then again PGfoundry is great to keep development centered, but
finding and building a new package is not really a one-liner, and
if you're unlucky you might get alpha-quality code installed. :)
I think a CPgAN-like solution would be the best. A uniform method
of getting approved Pg extensions. It would simplify installing the
extensions, and would encourage distributions to package such
extensions. Somebody suggested apt-get install postgresql-contrib.
Imagine:
apt-get install postgresql-datatype-fqdn
apt-get install postgresql-gist-ltree
...and so on.
Regards,
Dawid
> > My question is whether psql using libreadline.so has to be GPL, meaning > the psql source has to be included in a binary distribution. If I understand what I have been told by lawyers, here's what using a GPL, and NOT LGPL, library means: According to RMS, the definition of a derivitive work is one which shares the same address space when running. The in-memory process separation also separates works. One may argue this definition, but it is in supporting documents to the GPL and likely to be considered as the intention of the GPL in a court of law. There is no requirement of shipping source with a binary. One must "make available" the source. This can be done by a web site or alternate CD distribution. It need not be free, as in beer, but must be free of any restrictions beyond those of the GPL. There is no requirement that one would need to make the source of the 3rd party GPL library available, as it is available from the original source from whence it was obtained in the first place. Any changes, however, made, by you, to that library must be made available. (If you do not make modifications to libreadline, you don't even need to worry about it.)
On Fri, May 19, 2006 at 06:01:19PM -0400, Jonah H. Harris wrote: > On 5/19/06, Joshua D. Drake <jd@commandprompt.com> wrote: > >And with that, I am going to sit in a lawn chair and watch the bonfire. > > This is one of the finest examples of unfocused discussions I've ever > seen on -hackers... while surely entertaining, what a huge waste of > time. I tried a few times to move it to -advococy... -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Sat, May 20, 2006 at 10:36:25AM +0200, Lukas Smith wrote: > Hi, > > I really think that PostgreSQL could benefit from a packaged solution > that incorporates a lot of the contrib stuff (tsearch2, maybe even some > replication setups ..). I really like the approach that PostgreSQL is a > clean yet highly extensible base from which other people can build their > specific tools. I think we're starting to see some of that, with things like the live CD. What I'd rather see time spent on is a framework that makes it easier to grab things from pgFoundry. To use a bad example, think CPAN for PostgreSQL. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
> > Then again PGfoundry is great to keep development centered, but > finding and building a new package is not really a one-liner, and > if you're unlucky you might get alpha-quality code installed. :) > Mammoth PostgreSQL was designed to fill this role. It is an FOSS project (www.mammothpostgresql.org) that is designed to be a COMPLETE postgresql distribution. Joshua D. Drake >
> On Fri, May 19, 2006 at 07:04:47PM -0400, Bruce Momjian wrote: >> > libreadline is not a problem because you can distribute postgresql >> > compiled with readline and comply with all licences involved >> > simultaneously. It doesn't work with openssl because the licence >> > requires things that are incompatable with the GPL. >> >> My question is whether psql using libreadline.so has to be GPL, meaning >> the psql source has to be included in a binary distribution. > > IANAL, but yes. Or any other of the methods allowed, like providing a > written voucher valid for at least three years. People who feel they > need to keep the source to psql secret should link against libeditline > instead. > > The way I understand it, the GPL affects programs in two main ways: > > 1. A program which is GPL'd must, when distributed, be able to provide > all source used to build it under terms compatable with the GPL. This is not technically true. If you incorporate GPL code that is publically available and unchanged, you needn't provide the 3rd party packages. > > 2. A program which includes a GPL'd header file while building, must, > when distributed, provide its own source and the library under GPL > compatable terms, but not necessariliy the source of anything else > needed to build it. This is why it's OK that psql links against openssl > and readline. This is sort of a disputable position, and RMS himself isn't clear. If the header files are simply definitions and declarations, then no GPL material is actually included in a binary. However, inline functions and macros may constitute code. > > These are obviously only relevent when distributing precompiled > binaries. If you are only distributing source, none of the above > applies to you. > Of course. > There's a third method that some people claim, but I don't buy. This > where a program using an interface of a GPL'd library somehow become a > derived work of said library. That's just way whacked out. There is no supporting argument for that, however, RMS supporting writings indicate that he defines "derived" as being in the same process space. > > You may ofcourse disagree with any of the above, and hey, if you have a > lawyer to back you up, who am I to argue? I have talked to too many lawyers, sigh, aout this stuff. > > As for why you don't solve the problem by distributing a libpq not > compiled against OpenSSL, well, that's a different question. Back when > SSL was considered an arms exports by the US, having both SSL and > non-SSL versions was common (and a big PITA). When that disappeared, > the main reason for the split went away and people started compiling > SSL by default. This solved the problem for 99% of programs. > > However, one tiny subset remains problematic: > - A library implements SSL, but only using OpenSSL > - The library doesn't use the GPL, or doesn't have an OpenSSL exception > clause. > - A GPL'd program uses this library, without an OpenSSL exception > clause. > > In this subset of a subset of a subset of programs, it's a problem. > Many libraries that implement SSL provide an alternative to OpenSSL, > many programs using such libraries have exception clauses so that > there's just a handful of programs and libraries that are problematic. > > As long as there's a possibility that the situation can change (either > every GPL program using postgresql gains an exception clause, or > postgresql might someday support some other library) it will probably > stay this way. > > If the the postgresql core decides that OpenSSL will be the only SSL > ever supported, no matter what, well, the split distribution may yet > happen. In the meantime, we have status quo. > > Have a nice day, > -- > Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ >> From each according to his ability. To each according to his ability to >> litigate. >
> On Thursday 18 May 2006 12:38, Josh Berkus wrote: >> Personally, I'd go after MSSQL before I bothered with MySQL. Sure, let's >> make *migration* easier for those who wake up and smell the BS, but >> migration can (and probably should) be one-way. Somebody earlier was mentioning, why no automatic transformer from Transact-SQL to PLPGSQL (maybe with a bunch of glue routines). The grammar is not a problem, though you have to wonder at all the wired-in keywords (T-SQL always felt like COBOL). The stumbling blocks are not in language, but function. Many of those functions are rarely used, but some big ones are quite common ... T-SQL has statement-level triggers, and they get used a lot (some big apps ONLY put code in triggers). Statement-level triggers are very efficient for maintaining aggregates; the closest PG has are rewrite rules. Other issues: stored procs returning multiple result sets; "print" statements; SELECT TOP n PERCENT; COMPUTE-expressions (subtotals); and some of the @@global variables that are hard to emulate @@IDENTITY being the main problem in older T-SQL code. OpenXML is cool, but such a pig, that its difficulty in emulation is probably not an issue. There are plenty of things that happily go away, or can be implemented with a client wrapper; for example, BULK INSERT and BACKUP. Other things just have no equivalent, and amount to no-ops in a PG world (partition functions) A few things require some custom metadata tables (MSSQL "RULE" != PG "RULE"). If you want to convince MSSQL users to move over to PG, statement-level triggers (with "OLD" and "NEW" rowsets) are a bottom-line requirement. ....... For high-end MSSQL shops, a high value is being able to trace and profile (EXPLAIN) every client SQL command from the server side ... with plenty of options for selective trace.
Mischa, > Somebody earlier was mentioning, why no automatic transformer from > Transact-SQL to PLPGSQL (maybe with a bunch of glue routines). The grammar > is not a problem, though you have to wonder at all the wired-in keywords > (T-SQL always felt like COBOL). Actually, porting TSQL to PL/pgSQL would be very hard. I speak as an expert TSQL developer. For example, most data manipulation in TSQL is done through updatable cursors, something we don't currently support. Also, T-SQL uses un-ordered, callable parameters for SPs, something which we *also* don't support. > Other issues: stored procs returning multiple result sets; "print" > statements; SELECT TOP n PERCENT; COMPUTE-expressions (subtotals); and some > of the @@global variables that are hard to emulate @@IDENTITY being the > main problem in older T-SQL code. Yeah, but @@IDENTITY sucks. Most MSSQL developers are glad to leave it behind. ;-) -- Josh Berkus PostgreSQL @ Sun San Francisco
Ühel kenal päeval, R, 2006-05-19 kell 11:29, kirjutas Mark Woodward: > > Andrew Dunstan <andrew@dunslane.net> writes: > >> Mark Woodward wrote: > >>> Again, there is so much code for MySQL, a MySQL emulation layer, MEL > >>> for > >>> short, could allow plug and play compatibility for open source, and > >>> closed > >>> source, applications that otherwise would force a PostgreSQL user to > >>> hold > >>> his or her nose and use MySQL. > >>> > >> If we had infinite resources this might make sense. We don't, so it > >> doesn't. There is a real cost to producing a compatibility layer, and > >> the cost will be those spiffy new features. > > > > The real problem is that there's a whole lot of stuff, such as mysql's > > weak error checking, that I don't think a "compatibility layer" could > > sanely provide. > > > I kind of agree with this statement, but while I was playing devils's > advocate and just grousing a bit about having to use MySQL, there is a > sort of reality of "openomics" where mind-share is everything. Maybe we could have a Google SoC project to strip Postgres of all its useful features (mainly ACID, but also introduce NULL==""==0 and weak foreign keys) to make it MySQL compatible. And if we start locking a table on each update, we can get rid of MVCC altogether and do fast selects, in-place updates and select from indexes only. Then that branch (PostgreSQLite) could be pimped to MySQL users as a way to "support" postgres. Nah, not really useful, but perhaps we could publicize the list of things that would have to go to be compatible :P ---------------- Hannu
On Fri, May 19, 2006 at 01:03:08PM -0700, Mischa Sandberg wrote: > >On Thursday 18 May 2006 12:38, Josh Berkus wrote: > >>Personally, I'd go after MSSQL before I bothered with MySQL. Sure, let's > >>make *migration* easier for those who wake up and smell the BS, but > >>migration can (and probably should) be one-way. > > Somebody earlier was mentioning, why no automatic transformer from > Transact-SQL > to PLPGSQL (maybe with a bunch of glue routines). The grammar is not a > problem, > though you have to wonder at all the wired-in keywords (T-SQL always felt > like COBOL). > > The stumbling blocks are not in language, but function. Many of those > functions are rarely used, but some big ones are quite common ... > > T-SQL has statement-level triggers, and they get used a lot (some big apps > ONLY put code in triggers). Statement-level triggers are very efficient for > maintaining aggregates; the closest PG has are rewrite rules. Yeah, I wish PostgreSQL had them. I've got clients that could certainly make use of them. > For high-end MSSQL shops, a high value is being able to trace and profile > (EXPLAIN) every client SQL command from the server side ... with plenty of > options for selective trace. This would also be highly valuable to have in PostgreSQL. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Sun, May 21, 2006 at 02:58:17PM -0700, Josh Berkus wrote: > Mischa, > > > Somebody earlier was mentioning, why no automatic transformer from > > Transact-SQL to PLPGSQL (maybe with a bunch of glue routines). The grammar > > is not a problem, though you have to wonder at all the wired-in keywords > > (T-SQL always felt like COBOL). > > Actually, porting TSQL to PL/pgSQL would be very hard. I speak as an expert > TSQL developer. For example, most data manipulation in TSQL is done through > updatable cursors, something we don't currently support. Also, T-SQL uses > un-ordered, callable parameters for SPs, something which we *also* don't > support. And TSQL doesn't fail a transaction on an error, resulting in code like UPDATE ... if @@error = 0 then UPDATE ... end if @@error = 0 then ... Makes for a lot of needless bloat when going to PostgreSQL. Supposedly this is changed in MSSQL05 though. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: Porting MSSQL to PGSQL (Was: [OT] MySQL is bad, but THIS bad?)
From
Martijn van Oosterhout
Date:
On Mon, May 22, 2006 at 10:00:22AM -0500, Jim C. Nasby wrote:
> > T-SQL has statement-level triggers, and they get used a lot (some big apps
> > ONLY put code in triggers). Statement-level triggers are very efficient for
> > maintaining aggregates; the closest PG has are rewrite rules.
>
> Yeah, I wish PostgreSQL had them. I've got clients that could certainly
> make use of them.
What are you referring to that is not supported currently?
CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] } ON table FOR EACH STATEMENT EXECUTE PROCEDURE funcname
(arguments )
> > For high-end MSSQL shops, a high value is being able to trace and profile
> > (EXPLAIN) every client SQL command from the server side ... with plenty of
> > options for selective trace.
>
> This would also be highly valuable to have in PostgreSQL.
Are we talking EXPLAIN (which is cheap) or EXPLAIN ANALYZE (which is
less so)?
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.
On 5/22/06, Martijn van Oosterhout <kleptog@svana.org> wrote:
> On Mon, May 22, 2006 at 10:00:22AM -0500, Jim C. Nasby wrote:
> > > T-SQL has statement-level triggers, and they get used a lot (some big apps
> > > ONLY put code in triggers). Statement-level triggers are very efficient for
> > > maintaining aggregates; the closest PG has are rewrite rules.
> >
> > Yeah, I wish PostgreSQL had them. I've got clients that could certainly
> > make use of them.
>
> What are you referring to that is not supported currently?
>
> CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
> ON table FOR EACH STATEMENT
> EXECUTE PROCEDURE funcname ( arguments )
Each programming language that supports triggers has its own method for making
the trigger input data available to the trigger function. This input
data includes the
type of trigger event (e.g., INSERT or UPDATE) as well as any
arguments that were
listed in CREATE TRIGGER. For a row-level trigger, the input data also
includes the
NEW row for INSERT and UPDATE triggers, and/or the OLD row for UPDATE and
DELETE triggers. Statement-level triggers do not currently have any way to
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
examine the individual row(s) modified by the statement.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
So, if user types:
DELETE FROM foo WHERE doh ='bar' and baf > 5;
(resulting, say with 5000 deleted rows)
...you can either create on delete trigger row level, which will:
UPDATE foo_stat SET count = count -1 WHERE doh='bar';
...which will be fired 5000 times.
The idea is that you could write a statement level trigger
which will count deleted rows and issue
UPDATE foo_stat SET count=count-5000 WHERE doh='bar';
Regards, Dawid
In article <20060522150647.GE24404@svana.org>, Martijn van Oosterhout <kleptog@svana.org> writes: > On Mon, May 22, 2006 at 10:00:22AM -0500, Jim C. Nasby wrote: >> > T-SQL has statement-level triggers, and they get used a lot (some big apps >> > ONLY put code in triggers). Statement-level triggers are very efficient for >> > maintaining aggregates; the closest PG has are rewrite rules. >> >> Yeah, I wish PostgreSQL had them. I've got clients that could certainly >> make use of them. > What are you referring to that is not supported currently? Probably he means that a complete implementation of statement-level triggers should include a way to access the rows affected by the trigger call.
On Mon, May 22, 2006 at 05:06:47PM +0200, Martijn van Oosterhout wrote:
> On Mon, May 22, 2006 at 10:00:22AM -0500, Jim C. Nasby wrote:
> > > T-SQL has statement-level triggers, and they get used a lot (some big apps
> > > ONLY put code in triggers). Statement-level triggers are very efficient for
> > > maintaining aggregates; the closest PG has are rewrite rules.
> >
> > Yeah, I wish PostgreSQL had them. I've got clients that could certainly
> > make use of them.
>
> What are you referring to that is not supported currently?
>
> CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
> ON table FOR EACH STATEMENT
> EXECUTE PROCEDURE funcname ( arguments )
And that doesn't give you any information on the rows that were
modified. Other RDBMSes will provide a NEW rowset and an OLD rowset that
you can select from inside the trigger as if they were real tables.
> > > For high-end MSSQL shops, a high value is being able to trace and profile
> > > (EXPLAIN) every client SQL command from the server side ... with plenty of
> > > options for selective trace.
> >
> > This would also be highly valuable to have in PostgreSQL.
>
> Are we talking EXPLAIN (which is cheap) or EXPLAIN ANALYZE (which is
> less so)?
It's not so much about which case, it's about being able to control the
them from another connection. IE:
Show EXPLAIN for every query run on PID blah
Show EXPLAIN ANALYZE for every query run on PID blah where the command
string matches this regex
etc. Having the ability to do random sampling in there could be very
handy as well. As would firing on queries that hit certain tables
(though the regex functionality could handle that).
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Sat, May 20, 2006 at 02:29:01PM +0200, Dawid Kuroczko wrote: > On 5/20/06, Lukas Smith <smith@pooteeweet.org> wrote: > >The improvements to the installer are great, but there simply needs to > >be a packaged solution that adds more of the things people are very > >likely to use. From my understanding Bizgres goes in that direction? I > >just think that whatever highly packaged solution PostgreSQL picks, this > >should be the download that is pushed at conferences, in articles and > >books. People with a clue will still know where they can get the clean > >base. > > Hmm, a Comprehensive PostgreSQL Archive Network? ;) > > I mean, something like CPAN, CTAN or CRAN? :) > > I mean, the -contrib is great, but pushing other things there is a bit > tricky (to say the least) from the maintenance point of view. (Every > bugfix, a new release of -contrib, etc, etc...). > > Then again PGfoundry is great to keep development centered, but > finding and building a new package is not really a one-liner, and > if you're unlucky you might get alpha-quality code installed. :) I don't see any reason why CPgAN would need to change pgFoundry at all. In fact, my thought was that any such system should use pgFoundry as it's backend/repository. > I think a CPgAN-like solution would be the best. A uniform method > of getting approved Pg extensions. It would simplify installing the > extensions, and would encourage distributions to package such > extensions. Somebody suggested apt-get install postgresql-contrib. > Imagine: > apt-get install postgresql-datatype-fqdn > apt-get install postgresql-gist-ltree > ...and so on. Except that apt doesn't work on all platforms. Though it would certainly make sense to look at lifting the framework for CPgAN from somewhere, rather than coding it ourselves. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
> On Sat, May 20, 2006 at 02:29:01PM +0200, Dawid Kuroczko wrote: >> On 5/20/06, Lukas Smith <smith@pooteeweet.org> wrote: >> >The improvements to the installer are great, but there simply needs to >> >be a packaged solution that adds more of the things people are very >> >likely to use. From my understanding Bizgres goes in that direction? I >> >just think that whatever highly packaged solution PostgreSQL picks, >> this >> >should be the download that is pushed at conferences, in articles and >> >books. People with a clue will still know where they can get the clean >> >base. >> >> Hmm, a Comprehensive PostgreSQL Archive Network? ;) >> >> I mean, something like CPAN, CTAN or CRAN? :) >> >> I mean, the -contrib is great, but pushing other things there is a bit >> tricky (to say the least) from the maintenance point of view. (Every >> bugfix, a new release of -contrib, etc, etc...). >> >> Then again PGfoundry is great to keep development centered, but >> finding and building a new package is not really a one-liner, and >> if you're unlucky you might get alpha-quality code installed. :) > > I don't see any reason why CPgAN would need to change pgFoundry at all. > In fact, my thought was that any such system should use pgFoundry as > it's backend/repository. > >> I think a CPgAN-like solution would be the best. A uniform method >> of getting approved Pg extensions. It would simplify installing the >> extensions, and would encourage distributions to package such >> extensions. Somebody suggested apt-get install postgresql-contrib. >> Imagine: >> apt-get install postgresql-datatype-fqdn >> apt-get install postgresql-gist-ltree >> ...and so on. > > Except that apt doesn't work on all platforms. Though it would certainly > make sense to look at lifting the framework for CPgAN from somewhere, > rather than coding it ourselves. A CPgAN would be a great idea in theory, but I have reservations. As a software developer, I'm fine with pgfoundery, but as a DB admin, and one who deploys data centers from time to time, I'd like to see something closer to the contrib. If I could have any influence at all, I'd like to see "contrib" essentially go away in the main distribution and replaced or renamed "extensions." Then, some advisory group "blesses" extensions, and those extensions get packaged into a PostgreSQL extensions pack. I, again as a DB admin, would have NO problem with PostgreSQL playing favorites and picking best of breed for these extensions.
Re: Porting MSSQL to PGSQL (Was: [OT] MySQL is bad, but THIS bad?)
From
Martijn van Oosterhout
Date:
On Mon, May 22, 2006 at 10:41:59AM -0500, Jim C. Nasby wrote:
> > CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
> > ON table FOR EACH STATEMENT
> > EXECUTE PROCEDURE funcname ( arguments )
>
> And that doesn't give you any information on the rows that were
> modified. Other RDBMSes will provide a NEW rowset and an OLD rowset that
> you can select from inside the trigger as if they were real tables.
Is this on the TODO list? It doesn't seem too difficult to create a
tuplestore and store the NEW and OLD tuples there and pass the whole
set to the trigger.
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.
On 5/22/06, Mark Woodward <pgsql@mohawksoft.com> wrote:
> > Except that apt doesn't work on all platforms. Though it would certainly
> > make sense to look at lifting the framework for CPgAN from somewhere,
> > rather than coding it ourselves.
>
> A CPgAN would be a great idea in theory, but I have reservations.
>
> As a software developer, I'm fine with pgfoundery, but as a DB admin, and
> one who deploys data centers from time to time, I'd like to see something
> closer to the contrib.
>
> If I could have any influence at all, I'd like to see "contrib"
> essentially go away in the main distribution and replaced or renamed
> "extensions." Then, some advisory group "blesses" extensions, and those
> extensions get packaged into a PostgreSQL extensions pack. I, again as a
> DB admin, would have NO problem with PostgreSQL playing favorites and
> picking best of breed for these extensions.
The "problem" with contrib is that no actively developed projects should
be there. It is a feature, not a bug. If it is actively developed, it may be
buggy. If it is proven over time, it can be safely used. Also, for a contrib
it is inefficient to release a whole -contrib whenever a subproject releases
new release. This "forces" -contrib to use stable-and-unchanging packages.
This also makes it extremaly hard to put new or niche projects. New are
risky, because they may need immediate bugfixes. Niche projects used
by a minority of users bloat -contrib and force more frequent releases,
both of which are well, not preferred.
Of course -contrib is great, we all know it. I think a "CPgAN" would be
a good testbed/incubator for new packages, some of which should
eventually get into -contrib.
Also, assuming there is a "pginstall dbanme packagename" interface,
a -contrib package should register all its subpackages within that
system. So, you install postgresql-contrib, and then you can type:
pg_package install mydb index/ltree
and later, provided you change your mind:
pg_package remove mydb index/ltree
(with -f option to "insert CASCADE whenever possible ;)).
This would be somewhat similar to current createlang(1) and friends. :)
Regards,
Dawid
Martijn van Oosterhout wrote:
-- Start of PGP signed section.
> On Mon, May 22, 2006 at 10:41:59AM -0500, Jim C. Nasby wrote:
> > > CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
> > > ON table FOR EACH STATEMENT
> > > EXECUTE PROCEDURE funcname ( arguments )
> >
> > And that doesn't give you any information on the rows that were
> > modified. Other RDBMSes will provide a NEW rowset and an OLD rowset that
> > you can select from inside the trigger as if they were real tables.
>
> Is this on the TODO list? It doesn't seem too difficult to create a
> tuplestore and store the NEW and OLD tuples there and pass the whole
> set to the trigger.
TODO has:
* Allow statement-level triggers to access modified rows
-- Bruce Momjian http://candle.pha.pa.us EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Martijn van Oosterhout wrote:
> On Mon, May 22, 2006 at 10:41:59AM -0500, Jim C. Nasby wrote:
> > > CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
> > > ON table FOR EACH STATEMENT
> > > EXECUTE PROCEDURE funcname ( arguments )
> >
> > And that doesn't give you any information on the rows that were
> > modified. Other RDBMSes will provide a NEW rowset and an OLD rowset that
> > you can select from inside the trigger as if they were real tables.
>
> Is this on the TODO list? It doesn't seem too difficult to create a
> tuplestore and store the NEW and OLD tuples there and pass the whole
> set to the trigger.
OTOH it would be nice to be able to save only the TIDs of the tuples,
not the tuples themselves ... maybe create a "TIDstore"?
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Mon, May 22, 2006 at 08:56:14PM +0200, Dawid Kuroczko wrote: > Also, assuming there is a "pginstall dbanme packagename" interface, > a -contrib package should register all its subpackages within that > system. So, you install postgresql-contrib, and then you can type: > > pg_package install mydb index/ltree Incidently, this reminds me of something I proposed late last year relating to easy installation of modules: http://archives.postgresql.org/pgsql-hackers/2005-09/msg00476.php To idea being that for most modules you only need a script to "install" it (declare functions and types). For these modules you can actually embed the script into the library itself. Thus, the installer only needs to be pointed to the shared object and it's done. If this installer tracked installed modules, you could use pg_depends to track what installed what and thus uninstall stuff afterwards. It might help pg_dump upgrades across versions. Anyway, it was just a thought and it didn't generate any comments at the time. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
On Mon, May 22, 2006 at 08:45:07PM +0200, Martijn van Oosterhout wrote:
> On Mon, May 22, 2006 at 10:41:59AM -0500, Jim C. Nasby wrote:
> > > CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
> > > ON table FOR EACH STATEMENT
> > > EXECUTE PROCEDURE funcname ( arguments )
> >
> > And that doesn't give you any information on the rows that were
> > modified. Other RDBMSes will provide a NEW rowset and an OLD rowset that
> > you can select from inside the trigger as if they were real tables.
>
> Is this on the TODO list? It doesn't seem too difficult to create a
No.
> tuplestore and store the NEW and OLD tuples there and pass the whole
> set to the trigger.
I never thought about it being that easy, but yeah, it probably wouldn't
be terribly difficult.
Can we get this on the TODO? Or does someone want to just come up with a
patch if it's easy enough?
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Mon, May 22, 2006 at 03:11:14PM -0400, Bruce Momjian wrote:
> Martijn van Oosterhout wrote:
> -- Start of PGP signed section.
> > On Mon, May 22, 2006 at 10:41:59AM -0500, Jim C. Nasby wrote:
> > > > CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
> > > > ON table FOR EACH STATEMENT
> > > > EXECUTE PROCEDURE funcname ( arguments )
> > >
> > > And that doesn't give you any information on the rows that were
> > > modified. Other RDBMSes will provide a NEW rowset and an OLD rowset that
> > > you can select from inside the trigger as if they were real tables.
> >
> > Is this on the TODO list? It doesn't seem too difficult to create a
> > tuplestore and store the NEW and OLD tuples there and pass the whole
> > set to the trigger.
>
> TODO has:
>
> * Allow statement-level triggers to access modified rows
No wonder I missed it, it's under the RI section. :)
Actually, it looks like there's a bunch of things in there that are
generic trigger issues and not really RI issues. And some of those are
exact duplicates of the triggers section... wee!
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Mon, May 22, 2006 at 03:14:18PM -0400, Alvaro Herrera wrote:
> Martijn van Oosterhout wrote:
> > On Mon, May 22, 2006 at 10:41:59AM -0500, Jim C. Nasby wrote:
> > > > CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
> > > > ON table FOR EACH STATEMENT
> > > > EXECUTE PROCEDURE funcname ( arguments )
> > >
> > > And that doesn't give you any information on the rows that were
> > > modified. Other RDBMSes will provide a NEW rowset and an OLD rowset that
> > > you can select from inside the trigger as if they were real tables.
> >
> > Is this on the TODO list? It doesn't seem too difficult to create a
> > tuplestore and store the NEW and OLD tuples there and pass the whole
> > set to the trigger.
>
> OTOH it would be nice to be able to save only the TIDs of the tuples,
> not the tuples themselves ... maybe create a "TIDstore"?
That could become a performance issue, though, since you'd need all the
modified pages to still be in cache for this to perform well. A
tuplestore doesn't have that constraint, and I think it was recently
realized that it also shouldn't be saving any MVCC info either (see the
compressed sort thread).
If there was more information than the tuplestore could keep in memory,
then a TIDstore might be faster, but only if it resulted in reading from
the heap sequentially, or very near it.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
OK, I created a "trigger" section and moved "referential integrity"
under "SQL commands".
---------------------------------------------------------------------------
Jim C. Nasby wrote:
> On Mon, May 22, 2006 at 03:11:14PM -0400, Bruce Momjian wrote:
> > Martijn van Oosterhout wrote:
> > -- Start of PGP signed section.
> > > On Mon, May 22, 2006 at 10:41:59AM -0500, Jim C. Nasby wrote:
> > > > > CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
> > > > > ON table FOR EACH STATEMENT
> > > > > EXECUTE PROCEDURE funcname ( arguments )
> > > >
> > > > And that doesn't give you any information on the rows that were
> > > > modified. Other RDBMSes will provide a NEW rowset and an OLD rowset that
> > > > you can select from inside the trigger as if they were real tables.
> > >
> > > Is this on the TODO list? It doesn't seem too difficult to create a
> > > tuplestore and store the NEW and OLD tuples there and pass the whole
> > > set to the trigger.
> >
> > TODO has:
> >
> > * Allow statement-level triggers to access modified rows
>
> No wonder I missed it, it's under the RI section. :)
>
> Actually, it looks like there's a bunch of things in there that are
> generic trigger issues and not really RI issues. And some of those are
> exact duplicates of the triggers section... wee!
> --
> Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
> Pervasive Software http://pervasive.com work: 512-231-6117
> vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
-- Bruce Momjian http://candle.pha.pa.us EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Martijn van Oosterhout wrote: >>> For high-end MSSQL shops, a high value is being able to trace and profile >>> (EXPLAIN) every client SQL command from the server side ... with plenty of >>> options for selective trace. >> This would also be highly valuable to have in PostgreSQL. > > Are we talking EXPLAIN (which is cheap) or EXPLAIN ANALYZE (which is > less so)? No, that's the thing; server-side trace and filtering are EXPLAIN ANALYZE, and require no change to the app, catching (problem) plans in context. For example, (using PG here) we had an ETL program that occasionally went very stupid. It turned out that the problem was the interaction between autovacuum timing, and when the program wiped out and rebuilt a worktable. I had modified the app to EXPLAIN ANALYZE the update commands, but how do you modify the straight SELECTs? Taking the statements out of context and trying them offline with EXPLAIN ANALYZE sent me up the wrong tree a few times :-( With PG, where I end up is setting log_min_duration_statement=10 and grinding the logs for the information I want, hoping to convincingly replay the SQL (with EXPLAIN ANALYZE) under similar circumstances. If there are no applications that PREPARE their statements, I have a chance. BTW was logging of (execution of) prepared statements with context info ever considered? Or have I missed something? ---------- The MSSQL facility make on-the-fly EXPLAIN ANALYZE possible for all statements. Its selective filter functions make feasible in production systems, where you can turn it on for a week, to catch hard-to-reproduce issues. ---------- I did go digging into source to make EXPLAIN ANALYZE work for a server-side trace, but it looked like a major. Any pointers? Willing to go dive into it again. -- Engineers think that equations approximate reality. Physicists think that reality approximates the equations. Mathematicians never make the connection.
Jim C. Nasby wrote: > On Sun, May 21, 2006 at 02:58:17PM -0700, Josh Berkus wrote: >> Actually, porting TSQL to PL/pgSQL would be very hard. I speak as an expert >> TSQL developer. For example, most data manipulation in TSQL is done through >> updatable cursors, something we don't currently support. There are plenty of direct runtime incompatibilities, that have to be implemented not as language-to-language, but language-to-procedure. That also makes them stick out as the first thing to refactor and reimplement, once the basic working system has been ported :-) Hadn't thought about updatable cursors, but solved that problem before in SimbaExpress and SimbaFusion with (effectively) arrays of OID's. That's what I love about PG: so many rich consistent facilities that you can implement things that tie other DB's in knots. >> Also, T-SQL uses >> un-ordered, callable parameters for SPs, something which we *also* don't >> support. Not such a big. Any translation has full access to the catalog (ported over to the target PG system) and the parser/translator can fill in the blanks. > And TSQL doesn't fail a transaction on an error, resulting in code like > > UPDATE ... > > if @@error = 0 then > UPDATE ... > end > if @@error = 0 then > ... Yeah, transaction error behaviour always brings out the nasty incompatibilities (Jim, was that a major problem on Project Lance?). -- Engineers think that equations approximate reality. Physicists think that reality approximates the equations. Mathematicians never make the connection.
"Jim C. Nasby" <jnasby@pervasive.com> writes:
> If there was more information than the tuplestore could keep in memory,
> then a TIDstore might be faster, but only if it resulted in reading from
> the heap sequentially, or very near it.
That's easily arranged, use a bitmap indexing data structure.
I think we could probably even live with the structure becoming lossy
under memory pressure: AFAICS, all rows modified by a single query ought
to have the same XMIN/CMIN (or XMAX/CMAX for deleted rows), so it should
be possible to verify whether a particular row is one of the interesting
ones or not.
I think the hard part of this task is designing the API for access to
the rowsets from triggers.
regards, tom lane
Tom Lane wrote: >"Jim C. Nasby" <jnasby@pervasive.com> writes: > > >>If there was more information than the tuplestore could keep in memory, >>then a TIDstore might be faster, but only if it resulted in reading from >>the heap sequentially, or very near it. >> >> > >That's easily arranged, use a bitmap indexing data structure. > >I think we could probably even live with the structure becoming lossy >under memory pressure: AFAICS, all rows modified by a single query ought >to have the same XMIN/CMIN (or XMAX/CMAX for deleted rows), so it should >be possible to verify whether a particular row is one of the interesting >ones or not. > >I think the hard part of this task is designing the API for access to >the rowsets from triggers. > > > > How expensive is this going to be, especially for huge numbers of rows? Would it be done for all queries, or just those with a per statement trigger, or only when explicitly requested? cheers andrew
Andrew Dunstan <andrew@dunslane.net> writes:
> How expensive is this going to be, especially for huge numbers of rows?
Certainly cheaper than firing a per-row trigger.
> Would it be done for all queries, or just those with a per statement
> trigger, or only when explicitly requested?
Just when there's a per-statement AFTER trigger, I would think. One of
the tricky parts is to minimize overhead if the trigger never actually
asks for access to the rows. However, if all we do during the statement
is build a possibly-lossy bitmap, I don't think the overhead will be
bad.
It might be interesting to think about reimplementing the RI triggers
as per-statement, too ...
regards, tom lane
Tom Lane wrote: > I think the hard part of this task is designing the API for access to > the rowsets from triggers. > My preference would be something similar to two Portal instances (the NEW and OLD). I could then map it in the same way that I map the result of a query. If the API actually used two real Portal instances, the PL/Java implementation would take minutes. Regards, Thomas Hallgren
Tom Lane wrote: > Andrew Dunstan <andrew@dunslane.net> writes: >> How expensive is this going to be, especially for huge numbers of rows? > > Certainly cheaper than firing a per-row trigger. I'm curious: I've never written a MSSQL trigger that did NOT use the INSERTED/DELETED pseudotables (aka NEW/OLD). I know STATEMENT-level triggers have existed in PG for a while ... but what do people use them for??? -- Engineers think that equations approximate reality. Physicists think that reality approximates the equations. Mathematicians never make the connection.