[SQL] "too big" transactions
How does PostgreSQL handles a "too big" transaction? By that I mean a transaction which, after a certain point, there will be no way to roll back. On PgSQL, maybe that only happens when the disk fills. Is there a configurable "size" limit for a single transaction? In addition, what happens if the disk fills up? Postgres is able to roll back, right? I'm assuming you can prevent the disk from actually filling up (and crashing the whole server) by turning on quotas for the postgres super user, so that only pgsql would complain. Please correct me if I'm wrong.
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
RE: [SQL] benchmarks
> 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. I forgot to stress that the test was run using *MySQL* benchmark tools, connecting against MySQL and PostgreSQL. I had to optimize the tools to suite PgSQL, because for some reason :) MySQL folks didn't bother to.
RE: [SQL] INSERT [IGNORE] INTO TABLE
> If I try to insert some row which in the table already exist the > postgre don't insert it and tell some error. Everything ok. > I'll insert only if there is not the same (same key) record, > else don't insert and don't tell me errors about it. > In MySQL is a switch [IGNORE]. Not a good feature for me. What do you do if the record already exists? Update it? Check existence and then insert or update. If you want, I guess you could wrap that inside a stored procedure.
RE: [SQL] INSERT [IGNORE] INTO TABLE
> > Check existence and then insert or update. If you want, I guess > you could > > wrap that inside a stored procedure. > > > Well, that's a good idea :-) but I'm not sure about how to create > such procedure. I would appreciate Your help. Quite frankly, I'm not sure either. I'm just starting with PostgreSQL, what I really know is Oracle. I believe it would be something like ... [declare routine, with one argument per record field] ... if exists(select 1 from table_name where [field_key] = [arg_key]) then update table_name set field1 = arg1,...,fieldn = argn where field_key = arg_key; else insert into table_name (field1,...,fieldn) values (arg1,...,argn); end if; ... The syntax is probably off, but that's the idea. I used that several times on oracle.
RE: [SQL] reinitialise serial counter
> after inserting values and doing delete from test if I try to insert > values again, the id starts from where it left off previously. How can I > get the serial counter to restart from 1 ? is it anything to do with > setval(). How to use the setval? The serial value is implemented using a sequence object (see the docs). Once a sequence returns a number it will never return it again (that's what sequences are for!). A serial field is not a good approach if you always want to start numbering at 1. Yours sincerely, Edmar Wiggers BRASMAP Information Systems +55 48 9960 2752
RE: [SQL] how to continue a transaction after an error?
I believe the known mechanism to cope with errors inside transactions are savepoints. That doesn't seem to be the case, though. If you don't care if some inserts fail, then you have multiple transactions instead of just one (use autocommit). If you want a transaction, check existence before each insert. That's the way to do it. > -Original Message- > I would like to insert a bunch of rows in a table in a > transaction. Some of > the insertions will fail due to constraints violation. When this happens, > Postgres automatically ends the transaction and rolls back all > the previous > inserts. I would like to continue the transaction and issue the > commit/rollback command myself.
RE: [SQL] Using Array-Values in subselect
IMHO you should use another table instead of an array. Forget about reasonable speed when using IN sub-queries, you'll get a sequential scan of the sub-query for every row in the master select. I've heard the EXISTS operator provides far better performance. In 7.1, there's a very nice solution: use the sub-query in the FROM clause and make joins to it. Should be MUCH faster. > But this causes an error because the array field just returns a string > instead of seperated values. Is there a way to make arrays return sort > of "real arrays" or something usable in a subselect in reasonable speed?
[SQL] OpenACS
Is there any PostgreSQL developer working on the OpenACS (PG version of the ArsDigita Community Sytem) project? I have installed it and I am very much interested. There are a lot of small bugs though, mostly related to Oracle->Postgres migration, and some which are Postgres specific (queries that should work but don't). I know C programming, and could start hacking in both OpenACS and Postgres to make things work. Some directions from someone who has already dealed with those projects would be VERY appreciated.
Using SELECT as DDL/DML statement is wrong (was RE: [SQL] reinitialize a sequence?)
If and when stored procedures are supported, there should be some way to
prevent functions called in a SELECT statement to modify the database
(create, insert, etc.).
It is confusing (and wrong IMHO) to use statements like
SELECT setval('tablename_serfield_seq',max(serfield)) FROM tablename;
(which is used to reset a sequence)
That should be done with
EXECUTE procedure(tablename_name,sequence_name);
(not sure if execute is the right keyword)
Yours sincerely,
Edmar Wiggers
BRASMAP Information Systems
+55 48 9960 2752
RE: Using SELECT as DDL/DML statement is wrong (was RE: [SQL] reinitialize a sequence?)
> But, altering the database in a procedure > called from a select is a design decision, > and if somebody wants to do it, well, it's > their problem. There may (on very few > occasions, one would hope) actually be some > good reasons to do this. Ok, it's their problem. But, as a DBA, I would like to have stored procedures. Having that, I would also like a way to prevent any application development that uses SELECT statements to alter the database. By definition, that's not what SELECT is for (and so it is bad practice to use it that way). Currently however, that's the only whay to do it (use something similar to stored procs). And, true, that's much better than not doing it at all. Yours sincerely, Edmar Wiggers BRASMAP Information Systems +55 48 9960 2752
[SQL] Unable to convert null timestamp to date. Bug?
Strange, this works: select * from users where last_visit > now() + 7; -- last_visit is nullable, of type timestamp But this doesn't select * from users where last_visit + 7 > now(); ERROR: Unable to convert null timestamp to date -- yes, there are users where last_visit IS NULL BTW, this works too: select null+1; -- obviously returns NULL System is FreeBSD 4.1, PgSQL 7.0.2 Anyone got a clue? Yours sincerely, Edmar Wiggers BRASMAP Information Systems +55 48 9960 2752
