Re: [SQL] renaming columns... danger?
Subject:[SQL] renaming columns... danger? > I just discovered that doing an alter table ... alter > column (to rename a column) does not do a complete > rename throughout the database. > shouldn't rename update any index and key definitions? > I'm very frightened right now, because I'm rather > dependent upon my database right now. I don't like > the thought that my database is corrupt at the schema > level. > Yes, I believe the same is true about trigger definitions and suchlike. In short - to do a rename on column I do a pg_dumpall and change all references of the name by hand :*((( Btw, is there a way to see what triggers are defined for particular field? Or how to drop triggers, which (by default) are unnamed?
Re: [SQL] HELP! ... pg_locale ???
Sandis Jerics <[EMAIL PROTECTED]> writes: > As result, now all queries, written inside the php code on multiply lines, > returns the following: > ERROR: parser: parse error at or near " " At a guess, you're having trouble with newline representations (Unix convention is \n only, DOS/Windows convention is \r\n, and then there's Macintosh which likes \r only). We've been working to change Postgres to accept all of these choices, but depending on which version of which tool you are using, you may need to toe the Unix line faithfully. The above message looks a lot like something spitting up on a stray \r. Dunno what your admin did to make the problem appear where you hadn't had it before... regards, tom lane
Re: [SQL] benchmarks
Frankyl, Clayton: Comparing PostgreSQL to MySQL is like comparing an 18-wheel Kenworth to a Porsche. The two are not equivalent ... if you want a simple, very very fast READ-ONLY database, use MySQL. If you want a full-featured transaction-environment database for a huge, complex set of data, use PostgreSQL. Any benchmarks you find are likely comapring the two for hosting web sites, which is unfair to PostgreSQL. If everything your database needs to do is serve up page content, go for MySQL. If you've got to build a 60-user inventory management system, MySQL won't even bring you close - heck, it's not even close to SQL92-compliant. Oracle is another game altogether, though I can imagine Postgres catching up in a few years. Still, the price tag for Oracle weeds out all but the very serious and deep-pocketed. And stay away from MS SQL Server ... I run two of the damn machines, and they're nothing but grief. -Josh -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] except on nulls?
Daniel Kalchev <[EMAIL PROTECTED]> writes: > [ EXCEPT behaves oddly in the presence of NULLs ] Yup, it does, because it's implemented like NOT IN, and NOT IN on a set containing nulls can never return 'true', only 'false' or 'unknown'. For example, 1 NOT IN (1,2,NULL) is clearly FALSE. But 3 NOT IN (1,2,NULL) is not clearly either true or false --- the null is effectively "I don't know what this value is", and so it's unknown whether 3 is equal to it or not. The SQL92 spec mandates that this NOT IN result be 'unknown' (NULL), which is then treated like 'false' by EXCEPT. Net result: nulls in EXCEPT's right-hand set cause its output set to be empty. While this behavior is all according to spec for IN/NOT IN, it's *not* according to spec for EXCEPT, because the spec defines UNION/INTERSECT/ EXCEPT in terms of a different concept, of rows being "distinct" or "not distinct". NULLs are distinct from non-NULLs and so a null row behaves the way you'd expect. UNION/INTERSECT/EXCEPT are reimplemented for 7.1 in a way that behaves according to spec. There's no simple patch for 7.0.* unfortunately. > (but I sort of think this worked before...) Could be. Before 7.0, IN/NOT IN were not up to spec on NULL handling either, so EXCEPT probably worked differently in this case then. > ERROR: Unable to identify an operator '<>' for types '_text' and '_text' > You will have to retype this query using an explicit cast There are no comparison operators for array types ... regards, tom lane
Re: [SQL] benchmarks
> Comparing PostgreSQL to MySQL is like comparing an 18-wheel Kenworth to > a Porsche. The two are not equivalent That's it. Major differences in features makes any benchmarking `apples to oranges.' > ... if you want a simple, very > very fast READ-ONLY database, use MySQL. BTW, can it be used on a r/o partition, or it still writes something to the files (locks, stats)? > If you want a full-featured transaction-environment database for a huge, > complex set of data, use PostgreSQL. Even if the system is relatively small and simple Everything will go down in fires, this is the law. So, the first thing to think about is what will your options be when you're trying to keep the parts. Stress and torture tests would be more usefull than benchmarking. > Any benchmarks you find are likely comapring the two for hosting web > sites, which is unfair to PostgreSQL. IIRC, there was one in favor PGSQL (wasn't it run by the Bridge?). However, the very fact of benchmarking these two along with non-disclosed commercial products, where PGSQL wins with flying colours, IMHO does more harm than good. > If everything your database needs > to do is serve up page content, go for MySQL. How 'bout flat files? They will be even faster. Main advantage of DBMS, as I see it, is a possibility to build searchable, logically connected structures, with consistency and concurrency supported on the server side. With MySQL one has neither referential integrity, nor server side logic. Moreof, HTML pages are bad as textual attributes even when tuple/page size limitation can be easily worked around. If you want dynamic content, SSI and CGI will do, and there's nothing about DBMS. Storing page templates as BLOBs gives nothing but slowdown. > If you've got to build a 60-user inventory management system, > MySQL won't even bring you close - heck, it's not even close to > SQL92-compliant. It's still possible, one can even get it for $ 200, but TCO will sky rocket when it starts to phreak, or when business rules will be changed. -- contaminated fish and microchips huge supertankers on Arabian trips oily propaganda from the leaders' lips all about the future there's people over here, people over there everybody's looking for a little more air crossing all the borders just to take their share planning for the future Rainbow, Difficult to Cure
RE: [SQL] benchmarks
As a former Oracle developer, I decided to start working with cheaper DBMS's. After a quick look on the market, PostgreSQL was the only one really worth looking into. But people liked MySQL, and I had to look for benchmarks. I found only one (attached). Not satisfied, I got PostgreSQL and MySQL, compiled and installed both. MySQL comes with bechmarking tools, so I decided to use them. Very impressive results for MySQL, obviously. For PgSQL, the problem was that the benchmarks were not optimized. Not even bulk loading was used. So I optimized it, and ran the test with PgSQL "NO FSYNC" option. The results were that PgSQL was slower than MySQL only by a factor of 2 or 3 (say, 3 seconds for MySQL against 6 or 8 seconds for PgSQL). Pretty good in my opinion. Note that for READ-ONLY access, PgSQL is practically as fast as MySQL. And, according to the attached document (not written by me), PgSQL gets faster when the SELECT involves several joined tables. > -Original Message- > hi all, > > lately at work there has been a debate over > mysql versus postgres > > im just looking for independent benchmarks > > i personally love postgres > at work they like mysql > > currently we are investigating other possible db solutions > > and they are looking at oracle, i think we could save a lot of dollarsz > if we decided to go to postgres > > > i was wondering if anyone can share links to any current independent > benchmarks > > as i would like some real data on these > > or at the very least give me a how to so i can do my own testing! A Comparison Of 4 Databases --- Intro - This paper shows the results of an evaluation of 4 databases. I am posting it to this mail group as I think Postgresql emerged quite favourably. The evaluated databases were Oracle, Informix, Mysql and Postgresql. Features and performance were examined. Hardware And Operating System - 2 x HP Vertra VE 7 each with 160M RAM + 1x3.2G + 1x13G Quantum IDE Drives were used. Redhat 6.0 was used as the operating system. No kernel changes were made. Initial Experiences --- Mysql was obtained in rpm format and was amazingly easy to install. The installation process created and started a database. The version was 3.22.27 Documentation was supplied and was good. Postgresql was similarly elementary to install, and again a database was created and started. The product comes with the flavour of Linux used and was in rpm format. The version was 6.5.2 Documentation was supplied and was very good. Informix was more cryptic to install. It was obtained in rpm format and installed. However this merely installed an archive and the real installation process had to be run thereafter. After this it had to be divined as to what was required next - the install does not create a database. Using some of the (not supplied) documentation it was discovered how to create and configure a database. The version was 7.30 ( This is old, but all they are supplying on this platform - 9.x is current) Documentation was not supplied, it was available on the Informix web site. It is ok. Oracle was difficult to judge as the author frequently installs it. However pretending to be coming to it new, it would be very difficult to install. It does not come in rpm format. It is downloadable from the Oracle web site. The small amount of included documentation is sufficient to enable someone to work out how to start the installer program. This program is a rudimentary wizard that asks questions and presents a list of components to choose a newcomer would suffer confusion here. The installer can create a database as part of the install. The version was 8.0.5 (this is slightly old - 8.1.5 is Current but buggy, 8.0.5 is the latest stable release on this platform). Documentation is not supplied, it is available from the Oracle web site. It is ok. Tests And results - Database Feature Comparison Database CostTrans Row Const Program Sec Fail Hot actions Lock raints mableure Safe back Mysql 0 /3000 No NoNo Partial Yes NoNo Postgresql 0 Yes Yes Partial Yes Yes Yes No Oracle 3200Yes Yes Yes Yes Yes Yes Yes Informix 2000Yes NoYes Yes Yes NoNo Cost NZ$ for 10 user license. Two prices mean that the product is charged for if resold as part of an application ( Mysql ) Support is not included Transactions Commit, rollback, isolation levels of at least read commited Row Locking select for update that locks only the rows selected and does not block reads Constraints primary and foreign key, with ability to enable/ disable or drop / add existence will give ""Partial"" and enable etc will give "Yes" Programmable create trigger, procedural language extensions to SQL
