Re: [GENERAL] PostgreSQL vs. Microsoft SQL server
On Sat, 2010-05-01 at 20:47 +0200, Thomas Løcke wrote: -->Anybody know of any recent comparisons made between the two? I'm in the process of buying a new telephony related software suite, and I'm getting mixed advice. Some say that MSSQL is _much_ better/faster than PostgreSQL, and others say the opposite. --> -->No shocker there. Their salesmen out for a sale, having no under standing how to compare DB's . --> sales-people all bang on about MSSQL being the superior choice, and PostgreSQL being a "toy compared to the Microsoft RDBMS". -->I've been using MSSQL from 7.0 to MSSQL 2008. Been using Postgresql as of 8.2 and think very highly of it. The administrative overhead of PG IMHO is far less, hardware requirements are lower, easier to develop in and against. MSSQL has lots of MS tools you can use which all cost lots of money. Hardware requirements far steeper, primary because the OS hardware requirements are higher. My first choice is PG --> --> The tech people though are divided into three groups: One group says the two systems are more or less equal, another group who says the Microsoft database is superior and finally a group who speaks highly of PostgreSQL. --> -->To say PG is superior to MSSQL and vice versus is a loaded argument. What is the bases of the comparison. --> -->Comparing the two strictly as DB to DB they are equal. Both support large section of SQL standard, they both have excellent track records not corrupting data, both do WAL, both scale up to thousands of transactions per second. both are ACID --> -->But the equality between the 2 stops there and each product has pluses and Minus. --> -->wiki has some comparison info -->http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems -->http://en.wikipedia.org/wiki/Comparison_of_database_tools --> --> I've not been able to convince them to send me some actual benchmark numbers, which actually should turn on quite a few alarms, come to think about it. :o) --> -->Benchmarking is an important piece but should not be a deciding factor. --> -->Benchmarks on DB's are miss leading, because the way each may execute a given set of queries can and will result in drastically different numbers. --> -->Tweaking a DB and the queries is a time consuming process and normally results in rewriting the queries, add indexes, changing configurations or even changing table layouts. --> -->My experience shows both are very fast if properly configured and the developers understands how a specific DB works to properly write queries. Seen more than once a DB taken to its knees because poorly written SQL statements or design . Performance and General mailing list are packed with such examples. --> Maybe you guys are aware of some recent generic tests/comparisons between the two systems? --> -->General tests and comparisons are worthless. This list is packed with examples as are other DB mailing list where users pick apart benchmarks because the Tester missed some setting, or some other arcane trick that is Database specific. --> --> All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored. CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately. Thank you.
Re: [GENERAL] Am I supposed to be all scared of compound primary keys?
On Sat, 2010-05-01 at 19:25 -0700, Mike Christensen wrote: -->I have a table that stores a user ID and a subscription type, and this is really all it needs to store and any pair of values will always be unique. In fact, I think this pair should be the primary key on the table. However, I'm using Castle ActiveRecord which says at: http://www.castleproject.org/activerecord/documentation/v1rc1/usersguide/pks.html#CompositePK And I quote: Quick Note: Composite keys are highly discouraged. Use only when you have no other alternative. I get the feeling they're discouraged from a SQL point of view, but it doesn't actually say why anywhere. Is there any good reason to avoid using composite keys on a table? Why waste the space of an extra key if you don't have to? Thanks! --> -->I'm not familiar with this project.. That said it seems they have some automated SQL updating/insert/relation building going on in the classes. Nothing more than simplifying the class initialising of .net ADO record sets which are overly complicated. --> -->It seems the class automation can not work with composite keys directly to build relation between classes; create SQL commands to up the records, and make sure within castle framework the composite key has been updated to all the other classes having relations. --> -->This warning has no impact on any database but a limitation and warning about Castle-project framework itself. --> -->The database does not care either way. Given this limitation is within the framework i follow the advice and not use a composite key. --> All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored. CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately. Thank you.
Re: Fwd: [GENERAL] Tablespace Problem
Message from mailto:softlinne...@gmail.com Kalai R softlinne...@gmail.com at 05-03-2010 05:41:24 PM -- Hi, Sir, I have been checked pg_tblspc/86014 directory have no permission to create new folder. I am using Windows XP Os and VB.Net frontend. I created the tablespace folder by programaticaly using http://vb.net/ vb.net. Itried to chang the user permission for that folder by programatically andmanualy using properties of the folder in windows explorer butfolder permission not changed. Is there any option to solve this problem via postgersql? If not , Should I change the folder permissionfor which user? current user or service account for postgresql. ***snip*** the Postgresql account needs to have full control over all its directories No other account needs access to postgresql directories. If security does not read as postgres account having full control and you can't change security, You need to take ownership of the directories first then you can change security. To take ownership you have to be admin on that computer. Then put postgres account as full control. All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored. CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately. Thank you.
Re: [GENERAL] PG & random() strangeness
Message from mailto:m...@sai.msu.ru "Sergey E. Koposov" m...@sai.msu.ru at 05-04-2010 06:36:23 PM -- Hello, I'm getting strange results with PostgreSQL random() function. It would be great if someone could either show where I am wrong or PG is wrong. Here is what I do (PG 8.4.3, x86_64 platform); I basically try to create the table with the column filled with random numbers (either integer or doubles). And I'm getting too many collisions. Here is the SQL code: --- begin; select setseed(0); create temporary table tmpx1 as select ((random())) as id from generate_series(1,10); select id ,count(*) from tmpx1 group by (id) having count(*)1; ***snip** Random() does not guarantee the results will not repeat. its just like rolling dice every time the dice rolls its an independent result so it will repeat at some point, so every time Random() is called its an independent event that has no idea about past results. You are calling random 100,000 times. To create a random list of numbers that don't repeat you have to filter the repeated values out one at a time, with another function. Tip trying to use sql to filter out duplicated values will sort the result set first before filtering duplicates resulting in a not so random list of numbers. All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored. CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately. Thank you.
[GENERAL] files stored in the database
as it keeps coming up on the list off and on, I decided to write a wiki article, comments suggestions http://wiki.postgresql.org/wiki/BinaryFilesInDB I also read over the 9.0 beta release notes, bytea type now allows hex values?? http://developer.postgresql.org/pgdocs/postgres/release-9-0.html#AEN98905 All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored. CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately. Thank you.
Re: [GENERAL] MS Access 2007 update write conflict problem & resolution
The bigger problem is using time stamps to find the record for updating Timestamps will not be unique as more than 1 record can have the same value I suggest changing the updating method to use a unique key. Message from mailto:douglas.lit...@orbitz.com "Little, Douglas" douglas.lit...@orbitz.com at 03-19-2010 10:35:09 AM -- Hi, We’ve been struggling with an MS Access 2007 app that updates a PG table. It was working, and then it wasn’t. It looks like we recreated the table without specifying the precision on the timestamp columns. PG is defaulting to timestamp(6), which doesn’t work with MS Access. The finest precision Access can deal with is timestamp(3). When the record is retrieved from PG it displays 6 microseconds, when Access prepares the update, the value is rounded to 3 microseconds. The update can’t then locate the row (timestamp mismatch), and displays the ‘write conflict’ error. Altering the column to timestamp(0) corrected the issue. 2 questions 1- PGAdmin was reporting the precision as 0, while a select was displaying 6 microseconds. Is this a known bug in pgadmin? 2- Is there a way in PG that I can set the default precision on timestamp? Thanks I’ll cross post to the pgadmin list for the pgadmin question. Doug Little Sr. Data Warehouse Architect | Enterprise Data Management | Orbitz Worldwide 500 W. Madison, Suite 1000 Chicago IL 60661| Office 312.260.2588| Fax 312.894.5164 | Cell 847-997-5741 mailto:douglas.lit...@orbitz.com douglas.lit...@orbitz.com http://www.orbitz.com/ orbitz.com | http://www.ebookers.com/ ebookers.com | http://www.hotelclub.com/ hotelclub.com | http://www.cheaptickets.com/ cheaptickets.com | http://www.ratestogo.com/ ratestogo.com | http://www.asiahotels.com/ asiahotels.com All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored. CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately. Thank you.
Re: [GENERAL] MS Access 2007 update write conflict problem & resolution
When connected to ODBC source Access ignores locking steps. I don't think that's the problem. If memory serves, when Access is building the UPDATE command automatically the WHERE clause includes every single column being updated when the old values, something like this Update set col =145, col2 = 'jjj', col4 = '12/01/10 10:05:5.032' where col =oldvalue1 and col2 = 'jjj' and col4 = '12/01/10 10:05:5.03' this means it will not find the record returning the error you see. to over come this very annoying feature you have to manually specify the update commands and the key to use. Message from mailto:richard.broer...@gmail.com Richard Broersma richard.broer...@gmail.com at 03-19-2010 01:12:35 PM -- On Fri, Mar 19, 2010 at 11:12 AM, jus...@magwerks.com jus...@magwerks.com wrote: The bigger problem is using time stamps to find the record for updating Timestamps will not be unique as more than 1 record can have the same value I suggest changing the updating method to use a unique key. MS-Access achieves optimistic locking by listing all of the columns in a table in the WHERE clause of the UPDATE statement. If ms-access unable to fully represent the data stored in a postgresql table, Access will throw back update conflict errors when postgres doesn't update the record due to the truncated data values. I've also seen this with null valued boolean fields. Part of the solution would be configure the ODBC driver to use PostgreSQL's xmin for optimistic locking. Of-course this doesn't fix MS-Accesses inability to handle the ranges of many of PG data-types. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored. CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately. Thank you.
Re: [GENERAL] ERROR: cache lookup failed for relation X
Because You dropped/deleted the table cache in Session A. The simplest way to look at it is Session B was lock out when the Drop table command was issued from Session A. Now when session B finally got its chance to drop/delete the table it was already gone . What kind error were you expecting from Postgresql to Return when it can't find the table??? In the future please don't cross post to multiple lists. Message from mailto:raagavendra@gmail.com raghavendra t raagavendra@gmail.com at 04-03-2010 10:08:11 PM -- step 6 - Issue the commit in Session A postgres=# commit; Step -7 nowwe can the see the error in the session B ERROR: cache lookup failed for relation X Could plese tell me, why this is generated and what is the cause. Thanks in advance Regards Raghavendra All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored. CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately. Thank you.
