Re: [GENERAL] Easy way to convert a database from WIN1252 to UTF8?
On 7/1/2010 11:08 AM, Mike Christensen wrote: I'd like to convert a small database to UTF8 before it becomes too large. I'm running on 8.3.x on Windows. It doesn't seem that pgAdmin has any native way of doing this, what's the easiest way to go about doing this? Thanks! Mike Dump/Backup the database , then create a new database using utf-8 then restore the database. 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. attachment: justin.vcf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Where has ms2pg gone?
On 6/10/2010 4:12 AM, Thom Brown wrote: Does anyone know if ms2pg is available from somewhere other than http://edoceo.com/creo/ms2pg ? Attempts to download it result in not found. Unless someone knows of an alternative attempt to automate migration of MSSQL to PostgreSQL? Thanks Thom MSSQL has many tools that can automate moving the data and creating the tables using ODBC connection. http://en.wikipedia.org/wiki/SQL_Server_Integration_Services http://en.wikipedia.org/wiki/Data_Transformation_Services There is also export functions along with table and column mapping tools built into SQL Management Studio that makes moving data simple and straight forward. moving the stored procedures, triggers, views, and indexes are bit of a problem, Transact and pl/pgSQL are far to different. 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. attachment: justin.vcf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cognitive dissonance
On 6/8/2010 9:23 AM, Peter Hunsberger wrote: On Tue, Jun 8, 2010 at 4:04 AM, John Gagejsmg...@numericable.fr wrote: Unix is a text-based operating system with unbelievably helpful text manipulation tools. Postgres is a creature of Unix which happens to have unbelievable text searching and manipulation tools. Yet, the only one file edition of the Postgres documentation is in...pdf format. Huh? I suppose the next thing you'll be suggesting is that, because Postgres is a database, the documentation should be stored as some form of searchable table within the database itself? runs and hides/ Its also available in chm windows help file format. Which i find allot more useful http://www.postgresql.org/docs/manuals/ you could print chm to a text file. also it not hard to dump a PDF document into a text file. 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. attachment: justin.vcf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cognitive dissonance
***SNIP*** 2) Its also available in chm windows help file format. Which i find allot more useful http://www.postgresql.org/docs/manuals/ you could print chm to a text file. --I'll have to boot over to XP, ugh. Will do. There are linux chm readers http://www.linux.com/news/software/applications/8209-chm-viewers-for-linux and one for firefox https://addons.mozilla.org/en-US/firefox/addon/3235/ 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. attachment: justin.vcf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to debug efficiently
On 6/3/2010 5:43 AM, Jamie Lawrence-Jenner wrote: Hi All In SQL Server I could copy sql code out of an application and paste it into SSMS, declare assign vars that exist in the sql and run.. yay great debugging scenario. e.g. (please note I am rusty and syntax may be incorrect) declare @x as varchar(10) set @x = 'abc' select * from sometable where somefield = @x I want to do something simular with postgres in pgadmin3 (or another postgres tool, anyy reccomendations?) I realise you can create pgscript, but it doesn't appear to be very good, for example, if I do the equlivent of above, it doesn't put the single quotes around the value in @x, nor does it let me by doubling them up and you don't get a table out after - only text... Currently I have a peice of sql someone has written that has 3 unique varibles in it which are used around 6 times each... So the question is how do other people debug sql this sql EFFICIENTLY, preferably in a simular fashion to my sql server days. by pgscript I take you meaning pl/pgsql which unlike Transact-SQL is actually useful. to debug in PG with pgadmin we have http://pgfoundry.org/projects/edb-debugger/ that makes debugging pl/pgsql very easy and it works with pgadmin and http://www.sqlmaestro.com/products/postgresql/maestro/tour/pgsql_debugger/ Your little example would like so in pl/pgsql - Create or Replace function MyTest() returns integer AS $BODYOFFUNCTION$ declare x text = 'abc'; Begin perform (select * from sometable where somefield = x); end; return 1 ; $BODYOFFUNCTION$ LANGUAGE 'plpgsql' VOLATILE COST 100; 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. attachment: justin.vcf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] child/parent creation
On 5/29/2010 1:05 PM, Dennis Gearon wrote: Is it possible to create a complex schema object in one transaction, I'm not sure i understand what you mean by schema object using prepared statements to protect(somewaht) against SQL injection? In short no Prepared statements do not protect from SQL injection. Prepared statements are used to skip the planning stage of the query. There are really only two ways to block SQL injection parameterized queries or check/remove command characters from the query sent to the DB Example: A 'family tree object' (having obvious relationships) consisting of: Table grandparent Table parent table childA table childB If I have all the information for each 'sub-object' in the 'family tree object', but of course, the primary, integer, sequence keys. So, using a script language, the procedure I'm doing now is creating one node, getting the id with another query (because of a (reported and actual) bug in the Doctrine ORM), and then creating the next level down. My application has nothing to do with family trees, actually, just an example. each of the (relevant) primary keys is a BIGSERIL, sequence backed, BIGINT. I do not understand what you are trying to do here, please clarify 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. attachment: justin.vcf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installing version 8.4
On 5/29/2010 6:26 PM, Bob Pawley wrote: Found it in XP it doesn't seem to exist in Windows 7. I can't even find Doc and Settings in 7. It's a large file. I'm not sure what is needed but here is the latter part of the file. Bob ***Snip*** Windows 7 and vista move lots of things around Documents and Settings is in the root directory but normally hidden and secured turn on show hidden files/folders and show system files/folders. form the start menu, type the document name out and the OS will find it for you. Windows Vista was/is the first real attempt by MS to setup a OS in a secure manner. it kinda like having to run sudo in the linux world. Still allot of things need to be improved namely the click through interface, should be pass worded 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. attachment: justin.vcf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Please help me write a query
On 5/27/2010 9:04 AM, Nikolas Everett wrote: Say I have a table that stores state transitions over time like so: id, transitionable_id, state1, state2, timestamp I'm trying to write a query that coalesces changes in state2 away to produce just a list of transitions of state1. I guess it would look something like SELECT state1, FIRST(timestamp) FROM table but I have no idea how to aggregate just the repeated state1 rows. if i understand what your after Select distinct transitinable_id, state1, min(timestamp) from table group by transitinable_id, state1 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Please help me write a query
On 5/27/2010 9:45 AM, Nikolas Everett wrote: Sorry. Here is the setup: CREATE TABLE test (id BIGSERIAL PRIMARY KEY, state1 INT NOT NULL, state2 INT NOT NULL, timestamp TIMESTAMP); INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() - interval '12 hours'); INSERT INTO test (state1, state2, timestamp) VALUES (1, 2, now() - interval '11 hours'); INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() - interval '10 hours'); INSERT INTO test (state1, state2, timestamp) VALUES (2, 1, now() - interval '9 hours'); INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() - interval '8 hours'); I want to write a query that spits out: state1 | timestamp + 1 | now() - interval '12 hours' 2 | now() - interval '9 hours' 1 | now() - interval '8 hours' Have a question what makes these values different other than the timestamp??? 1, 1, now() - interval '12 hours' *1, 1, now() - interval '10 hours'* The reason i ask, is because you show *1, 1, now() - interval '8 hours'* in the desired output. What logic keeps the 8 hour and 12 hour but not the 10hour interval??? Its kinda hard to understand why the 10hour interval is being skipped??? 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. attachment: justin.vcf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Hiding data in postgresql
On 5/24/2010 3:18 PM, Hector Beyers wrote: Yes, I mean hide. I am approaching the problem out of the perspective of a malicious user / hacker. **snip*** First hiding data is not a solution to secure or block access to information. This only slows people down it does not stop them, never underestimate users with access to the data It would be helpful to explain the type of data that needs to be hidden/secured Example of failed attempts to hide data is to look at the numerous mistakes in securing credit card data at many Companies. In almost every case that i have read the programmers just tried to hide the data or limit access instead of doing Public Key Private Key encryption methodology .I know of several big name apps that still store credit card data where the end users can reverse the encryption meaning if the key becomes unsecured any the data is visible that is encrypted. I have seen where the data is only encrypted inside the database so the information is transmitted in the clear to the client as the database decrypted the data on the fly . What is the point?? Trying to hide information is waste of time and energy look into encryption. 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. attachment: justin.vcf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Hiding data in postgresql
On 5/25/2010 2:58 AM, Hector Beyers wrote: No, I have not considered encrypting or decrypting data. The reason for this is that I am trying to /secure a database/ by thinking like a /malicious user / criminal/. I want to hide (for example) fraudulent data on a database where it is not easily seen by others and then build a tool to detect this hidden data. On your questions: *) What data is to remain secret? *) Who is allowed to see the secret data? *) When do they see it? *) What sacrifices are you willing to make to keep the data secret? *) Where are you going to store the key? the answers: * fraudulent data / or data that needs to be hidden. * only the malicious user - and hopefully later a detection mechanism that I aim to build. * I don't really have a preference on when they can see the data, but maybe when you export a dump. * The main purpose of hiding the data is that the normal users of the database will not easily find the hidden data. If this criteria is met, then any other sacrifices can be made. * Still need to figure that one out. Any good brainstorming ideas will help! Missed this bit prior to first responds. I think some of the assumptions here are flawed. If hacker actually got into a database why would they do this??? what is being accomplished??? why would anyone want to do this??? Again it would make allot more sense if a hacker stored data in plain site. Create tables that look like real tables following the same naming schema or use already existing tables like logs, Modify the tables adding columns to store data. Then create/update records encrypting the contents, this would protect the contents from ever being read by anyone except by the creator. Think this line through how long would a Hacker go unnoticed if they used the already existing tables adding in columns or take over stale records like old customers that are no longer active. Then use the text fields to store data. The hacker could create normal user account to access those records throwing up no red flags. How many people review table structures or update to already existing records. The current crop of hackers are not hexeditor high-school wannabe's. Hackers want to go unnoticed for as long as they can so that means doing nothing out of ordinary that throws up red flags. Just read up on the investigations on stolen credit cards. Or fake ATMS that's been installed at malls. The hackers/thieves figured out how to go unnoticed for long periods of time by appearing normal. Second assumption is the hacker actual got a admin/root level access to be able to do these kind of things. This means security upfront was lacks which point there are far bigger problems than hidden data. Far better way to secure is not trying think what they can do once they get access, but stop them getting in to start with.If anyone gets this high level of access protecting from or figuring out if they have hidden data is immaterial to the problems someone has. 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. attachment: justin.vcf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why Performance of SQL Query is *much* Slower in GUI PgAdmin
On 5/17/2010 12:52 AM, Yan Cheng CHEOK wrote: The pgadmin result seems different with my machine. My friend and I are using Windows machine. Are you using Linux machine? Thanks and Regards Yan Cheng CHEOK **snip** I use both windows and Linux using pgadmin, and on occasion use psql I take my work home so I backup then drop then recreate the databasse on my computers at home. So I create the database at least 2 or 3 times a week. I see no meaningful difference between machines or client used to create a DB using either Windows or Linux. I have never timed it. Why would anyone time creating a DB??? But i would say its less than second. So something weird is going with your friends computer. 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. attachment: justin.vcf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Reliability of Windows versions 8.3 or 8.4
On 5/12/2010 11:45 AM, Richard Broersma wrote: Can anyone advise me if either PostgreSQL 8.3 or 8.4 is ready for special case of production use? I'm considering using the windows version PostgreSQL in the following conditions: at least 10 years of up time (with periodic power failures= 1 a year) single table with less-than 50 record inserts a day reporting at most once a month by a single connection I question any database on the market that will guarantee such a thing. Power in industrial plants is some of the dirtiest. brown outs, spikes, surges, harmonics, and the list keeps going. That is not the best environment for computers, even industrial ones. Given how few records are being inserted a day a full database like Postgresql is over kill. I would do a plain text file something like XML. Given this is for industrial use 10 years is a good number for warranty and support, but this stuff will hang around years later, think 20 to 30 years. How many people understand FLAT ISAM tables from the 1980's today, let alone tools to read/modify the records. I suggest storing the records in manner that is human readable 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Reliability of Windows versions 8.3 or 8.4
On 5/12/2010 12:33 PM, Richard Broersma wrote: On Wed, May 12, 2010 at 9:18 AM, Justin Grafjus...@magwerks.com wrote: I would do a plain text file something like XML. Given this is for industrial use 10 years is a good number for warranty and support, but this stuff will hang around years later, think 20 to 30 years. How many people understand FLAT ISAM tables from the 1980's today, let alone tools to read/modify the records. I suggest storing the records in manner that is human readable These are all good points. There is one concern that I do have, this information will be used to audit the billing system. Is there any concern for loss of data if a file rewrite is interrupted by a power failure? When using postgres there are some protections provided to reduce this kind of data loss. However, I do agree that tabular/xml data would stand the test of time. Text files are a little hard to corrupt to the point nobody can read them. Obviously if the system is in the middle of writing the XML node and looses power the XML layout is toasted. To limit data lose create new XML file each day or month or what ever time period makes sense. If you end up with a screwed XML file kiss it off or note it so a person can manually fix the entries and get most of the data back. 50 records * 365 * 10 = 182500 records. Not allot of data for 10 years of collecting, but very big for a single XML file 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Storing many big files in database- should I do it?
On 4/29/2010 12:07 PM, David Wall wrote: Big downside for the DB is that all large objects appear to be stored together in pg_catalog.pg_largeobject, which seems axiomatically troubling that you know you have lots of big data, so you then store them together, and then worry about running out of 'loids'. Huh ??? isn't that point of using bytea or text datatypes. I could have sworn bytea does not use large object interface it uses TOAST or have i gone insane Many people encode the binary data in Base64 and store as text data type?? Then never have to deal with escaping bytea data type. Which i have found can be a pain 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Storing many big files in database- should I do it?
On 4/29/2010 1:51 PM, David Wall wrote: Put it another way: bytea values are not stored in the pg_largeobject catalog. I missed the part that BYTEA was being used since it's generally not a good way for starting large binary data because you are right that BYTEA requires escaping across the wire (client to backend) both directions, which for true binary data (like compressed/encrypted data, images or other non-text files) makes for a lot of expansion in size and related memory. BYTEA and TEXT both can store up to 1GB of data (max field length), which means even less file size supported if you use TEXT with base64 coding. LO supports 2GB of data. In JDBC, typically BYTEA is used with byte[] or binary stream while LOs with BLOB. I think LOs allow for streaming with the backend, too, but not sure about that, whereas I'm pretty sure BYTEA/TEXT move all the data together you it will be in memory all or nothing. Of course, to support larger file storage than 1GB or 2GB, you'll have to create your own toast like capability to split them into multiple rows. David Outside of videos/media streams what other kind of data is going to be 1gig in size. Thats allot of data still even still today. We all talk about 1 gig and 2 gig limits on this, but really who has bumped into that on regular bases??? Every time i hear about that not being big enough the person is trying to shoe horn in media files into the database, which is insane 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Storing many big files in database- should I do it?
On 4/29/2010 3:18 PM, Tom Lane wrote: Alvaro Herreraalvhe...@commandprompt.com writes: However, that toast limit is per-table, whereas the pg_largeobject limit is per-database. So for example if you have a partitioned table then the toast limit only applies per partition. With large objects you'd fall over at 4G objects (probably quite a bit less in practice) no matter what. regards, tom lane has there been any thought of doing something similar to MS filestream http://msdn.microsoft.com/en-us/library/cc949109.aspx it seems to overcome all the draw backs of storing files in the DB. 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query is stuck
On 4/14/2010 9:20 AM, Satish Burnwal (sburnwal) wrote: Index Scan using repcopy_index on repcopy a (cost=0.00..87824607.17 *rows=28* width=142) (actual time=11773.105..689111.440*rows=1* loops=1) Index Cond: ((dm_user)::text = 'u3'::text) Filter: ((report_status = 0) AND (report_time = (subplan))) SubPlan - Aggregate (cost=3531.30..3531.31 rows=1 width=8) (actual time=58.447..58.448 rows=1 loops=11788) - Index Scan using repcopy_index on repcopy b (cost=0.00..3526.30*rows=2000* width=8) (actual time=0.017..36.779 *rows=25842* loops=11788) Index Cond: ((($0)::text = (dm_user)::text) AND (($1)::text = (dm_ip)::text)) Filter: ((ss_key)::text ''::text) Total runtime: 689111.511 ms (9 rows) The estimated cost and actual are way off. Have you run Analyze on the table you may want to change the statistics collected for this table http://www.postgresql.org/docs/8.1/static/planner-stats.html ALTER [ COLUMN ]/column/ SET STATISTICS/integer/ http://www.postgresql.org/docs/8.1/static/sql-altertable.html 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] Query is stuck
On 4/14/2010 9:42 AM, Bill Moran wrote: Man, it's hard to read your emails. I've reformatted, I suggest you improve the formatting on future emails, as I was about to say to hell with this question because it was just too difficult to read, and I expect there are others on the list who did just that. I did for the most part. select report_id, dm_ip, dm_mac, dm_user, dm_os, report_time, sys_name, sys_user, sys_user_domain, ss_key, login_time, role_id, new_vlan_id from repcopy as a where report_time = ( select max(report_time) from repcopy as b where a.dm_user=b.dm_user and a.dm_ip = b.dm_ip and b.ss_key != '' ) and report_status = 0 and dm_user = 'u3'; I suggest writting something like this. select report_id, dm_ip, dm_mac, dm_user, dm_os, report_time, sys_name, sys_user, sys_user_domain, ss_key, login_time, role_id, new_vlan_id from repcopy as a (select max(report_time) as rtime, dm_user, dm_ip from repcopy group by dm_user, dm_ip where ss_key != '') as materialized where report_time = materialized.rtime and materialized.dm_user = a.dm_user and materialized.dm__ip = a_ip and report_status = 0 and dm_user = 'u3'; 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [offtopic] How do you name a table...
On 4/8/2010 9:30 AM, Bill Moran wrote: In response to Ognjen Blagojevicogn...@etf.bg.ac.rs: Is this: a. Lookup table b. Classifier c. Cypher(er)? I'm looking for the appropriate term in English. I try to make it an ENUM when it's very unlikely to change, i.e. day of the week is a good candidate for an enum ... when's the last time that changed? For lookup tables that aren't static enough to be an enum, it usually ends up appended with _list (i.e. gender_list, county_list, etc) As others have said enum for things that are static like day, week, month etc.. For things that are not static and change like system/application settings i do something like this CREATE TABLE syssettings ( sys_id serial primary key, sys_group text, sys_value text, sys_displayvalue text, sys_datatype text DEFAULT 'text' ) This allows for easy grouping values together and assigning the data type for casting, along with how the information is to be displayed in the user interface. example what the data looks like: the below data is used to create option pull down list on a website. So if the user of the application wants to add more options all they have is make an entry. 15;ExamLevel;1;Level 1;integer 16;ExamLevel;2;Level 2;integer 17;ExamLevel;3;Level 3;integer 18;QuestionOrder;StandardList;Standard not random;text 19;QuestionOrder;RandomList;Random from List;text 20;QuestionOrder;Random;Random From All Questions;text 21;ExamType;MPI;MPI;char(10) 22;ExamType;RT;RT;char(10) 23;ExamType;UT;UT;char(10) 24;ExamType;ECT;ECT;char(10) 25;ExamType;LPI;LPI;char(10) 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] round(x) function
On 3/26/2010 12:12 PM, Tom Lane wrote: Gaietti, Mauro \(SELEX GALILEO Guest, Italy\)mauro.gaie...@guests.selexgalileo.com writes: This query: select round(0.5), round(0.5::integer), round(0.5::bigint), round( 0.5::float ), round( 0.5::double precision ),round(cast(0.5 as double precision )),round(cast(0.5::double precision as numeric )); has strange result: 1 1 1 0 0 0 1 Is this correct? On most machines the float4/float8 operations follow the IEEE-754 spec's round to nearest even rule for such cases. I think all the other ones are add 0.5 and truncate implementations. It's unlikely we'll mess with either; and *highly* unlikely that we would change the float behavior since that's not even under our direct control (the hardware or libm is what's doing that). There's some case to be made for making numeric and integer math do it the IEEE way, but I think that would displease at least as many people as it pleased ... regards, tom lane This topic keeps coming up every few months about rounding which way is correct. I would be in favor of adding a option to round() function to specify method. Leave the defaults as they are for backwards capability, meaning if no option is passed to Round() it follows current methods. I agree changing how it works now would annoy many. The option would be very simple something like this Round( 0.5, RoundToEven) = 0 Round( -0.5, RoundToEven) = 0 Round(0.5, RoundUp) = 1 Round(-0.5, RoundUp) = 0 Round(0.5, RoundDown) = 0 Round(-0.5, RoundDown) = -1 Round(0.5, RoundToZero) = 0 Round(-0.5, RoundToZero) = 0 There are so many methods of rounding to choose from http://en.wikipedia.org/wiki/Rounding 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL]
On 3/18/2010 12:52 PM, Scott Mead wrote: xtuple ERP does and the latest version of GNUCash can use postgres as a backend too. --Scott M On Thu, Mar 18, 2010 at 1:11 PM, Garry Saddington ga...@schoolteachers.co.uk wrote: Does anyone know of a web based accounting(finance) package that uses Postgresql as a backend? Thanks Garry -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general i don't know about the current version of xtuple but the past ones had numerous accounting begs, that management deemed not urgent to fix. Trail Balance does not work Rounding Errors, Unit of measure errors No audit trail in places Zero traceability in WIP module No audit trail for past Inventory Count Cycles Inventory Costing was completely worthless allows users to mix Weighted with Standard Cost in the same warehouse (breaks accounting standards) No WIP elevation. You have a number in the GL but try to figure out what makes that number up based on whats in WIP tables to make sure the GL agrees with WIP transactions. Oh wait they don't record the wip transactions in fine enough detail. Thats what i can remember from my previous job. On the very bright side it introduced me to all the fine folks at PG
Re: [GENERAL] app table names
On 3/16/2010 3:35 PM, Vick Khera wrote: On Tue, Mar 16, 2010 at 3:03 PM, Jamie Kahgeejamie.kah...@gmail.com wrote: I'm curious what people consider best practice (or how do you do it) to help ensure these name collisions don't happen. Do not mix data from multiple applications in one database. Use multiple databases to isolate them entirely. That's not always a practical solution to the problem, the Apps may need to share numerous tables, duplicating the data and keeping it sync can be a pain. Having to open numerous database connections to different databases is a resource hog . what i have been doing of late is defining PG_SCHEMA variable to tell the app where the data is located . Common tables to many apps go into the public schema or a schema that's in the search path.Selects look something like this Select * from + PG_SCHEMA + foo 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Daylight savings time confusion
On 3/15/2010 2:40 PM, Rob Richardson wrote: Greetings! Our database monitors the progression of steel coils through the annealing process. The times for each step are recorded in wallclock time (US eastern time zone for this customer) and in UTC time. During standard time, the difference will be 5 hours, and during daylight savings time the difference will be 4 hours. I just looked at the record for a charge for which heating started just after 9:00 Saturday night, less than 3 hours before the change to daylight savings time. The UTC time stored for this event is six hours later! The function that writes these times first stores the UTC time in a variable named UTCTimestamp: select into UTCTimestamp current_timestamp at time zone 'UTC'; Then, later in the function, the two times get written into the record (along with some other stuff): update charge set status=ChargeStatus,fire_date=current_timestamp, fire_date_utc=UTCTimestamp, fire_user=FurnaceTender, updated_by=UserId,updated_date=current_timestamp where charge=ChargeNum; Can someone explain why fire_date is 2010-03-13 21:39:51.744 and fire_date_utc is 2010-03-14 03:39:51.744 for this record? There is another charge that began firing five and a half hours before the DST switch. The difference between its fire_date and fire_date_utc times is five hours, as expected. RobR My first thought is the server is using libraries that don't know the DST was brought forward 3 weeks earlier than last year, its clock is all confused. i would check the time on Postgresql Server making sure it read out correctly. below was run on pg 8.4 windows 2008 server Select current_timestamp, current_timestamp at time zone 'UTC'; 2010-03-15 16:43:11.382-04;2010-03-15 20:43:11.382 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Naming conventions for lots of stored procedures
On 3/10/2010 11:52 PM, Chris Travers wrote: There are two major limitations here of schemas: 1) They can't be nested leading again to possible namespace ambiguity. 2) there are a number of requests to try to get the application to install into an arbitrary, nonpublic schema. If schemas could be nested this would solve both of these problems. However, if the above is anywhere near a complete list of schemas for 1200 procedures, you must also have some strong naming conventions to prevent collisions. I would be interested in what they are. Best wishes, Chris Travers This is an app i took over and there was no strong name convention plus an godly amount of overloaded procedures. the procedures use very very long names example createardebitmemo(int, text, text date, numeric, text, int, int mint date, int int, numeric ) createarcreditmemo(integer, text, text, date, numeric, text, integer, integer, integer, date, integer, integer, numeric, integer, integer) this means Create Accounts Receiver Debit Memo deleteaccount(integer) deleteaccountingperiod(integer) deleteaccountingyearperiod(integer) deletecustomer(integer) after the moving the functions into schemas this is how one would/could call them. gl.deleteaccount(integer) gl.deleteaccountingperiod(integer) gl.deleteaccountingyearperiod(integer) ar.deletecustomer(integer) ar.createardebitmemo(int, text, text date, numeric, text, int, int mint date, int int, numeric ) ar.createardreditmemo(integer, text, text, date, numeric, text, integer, integer, integer, date, integer, integer, numeric, integer, integer) Now one problem is if 2 functions have the same name, same number and type of inputs then Postgresql will throw ambiguous error, if the search path includes the 2 schemas where the functions are stored . I wonder if any database out there allows for nesting schemas. Which i'm at a loss why nesting would help solve any problem what so ever. I imagine the search path on some connections would be all inclusive so ambiguous names is not solved. Also would not be a big fan typing something like AR.Customer.Editing.Delete(ID) what has been gained??? think if the search path was all inclusive AR.Contact.Editing.Delete WIP.WorkOrder.Delete and this was called Select Delete(5784); Postgresql will through ambiguous error which delete, the one in AR.Customer, AR.Contact or WIP.Workorder schema. 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Naming conventions for lots of stored procedures
On 3/10/2010 8:16 PM, Chris Travers wrote: Hi all; One of my applications currently has over 60 stored procedures and future versions will likely have several hundred. I am wondering what folks find to be helpful naming conventions for managing a large number of stored procedures. We tried using double underscores to separate module vs procedure names and that just became a mess. I have found a few possible separators that might possibly work but they are aesthetically revolting (_$ for example, like select test_$echo(1);). I can't imagine I am the first person to run up against this problem and would rather ask advice of more experienced folks then to wander from one maintenance headache into a possibly far worse one. So, what are approaches each of you have taken in the past? Best Wishes, Chris Traverl look into schemas. this allow group table and procedure logically and can limit access based on schemas. what i did is group procedures, views, and tables into schemas to keep them logically grouped. in one project there is 300 tables, and 1200 procedures wip (work in process) sales AR AP GL public 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] managing tablespaces like files?
On 3/9/2010 12:07 AM, Sam Carleton wrote: I would like to thank both John and Scott for the help. It is very clear to me that PostgreSQL isn't the ideal solution for my current model. The conversation has gotten me thinking of ways the model could be modified to work with PostgrSQL (and other client/server RDBM). Thus I will return to the drawing board just to double check to see if there might be a better model. Thank you all, this has been truly valuable! Sam Hello Sam: I've had similar conversation with Application developers who are used to working with Access databases aka file based databases. When it comes time to backup or move the databases to other computer or share the database over a file-share they look at databases as just files to be copied around from machine to machine. No database server like oracle, postgresql, mssql, mysql, or db2 allow simple copying of the database to another location/server having it start right up. None are going to work that way. These databases expressly deny direct access to the files and make the assumption their the only process accessing the files. Another problem you may run into and need to think about is Anti-virus apps have a tendency to create all kinds of problems with database servers. The application can work and make the user life even easier. The approach has to be different on how the backup restore and moving the databases around is going to work . Take a look at a couple of examples how others have solved this problem One company using postgresql, directly aimed at small companies (where there is no IT help let money to pay for IT) wrote a very nice and easy to use front end for there application to backup, restore and move the application to another server. go to www.3cx.com and download there app they have a free version check out the instruction for back and restore. Its very easy clean interface how they backup and restore the database. Another company that has very nice and easy to use backup and restore of a MySQL database is Gordano Messaging Server. www.gordano.com their backup, recovery and moving to another server is very simply. A simple little command line tool backups the database, user profiles, and other system settings into a nice little file called settings.txt. They also have gui tool that will automate the backing up of the database. 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Scratching my head why results are different between machines.
On 3/4/2010 3:51 AM, Richard Huxton wrote: On 04/03/10 01:35, Craig Ringer wrote: http://support.microsoft.com/kb/942976/en-us Classy. Even better - according to the linked page, the 64 bit version is in the System32 folder - yippee! * The 32-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\SysWoW64 folder. * The 64-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\System32 folder. Some of the naming conventions for Windows 64 suck. SysWoW64 = System Windows on Windows 64 where does anyone get the idea this is where the 32bit apps are stored.Maintaining the System32 and using it to store 64 bit apps is Insane. To pretty much anyone outside MS, a sane human would think 64 bit apps in SysWoW64 and 32Bit apps in System32. :'( 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Scratching my head why results are different between machines.
On 3/4/2010 10:00 AM, Greg Stark wrote: On Thu, Mar 4, 2010 at 2:14 PM, Justin Grafjus...@magwerks.com wrote: To pretty much anyone outside MS, a sane human would think 64 bit apps in SysWoW64 and 32Bit apps in System32. :'( Ah, but you all are forgetting that the 32 here is to distinguish it from the default odbc interface which as i recall was a *16* bit interface. I'm not sure what exactly that means but as a result the 32-bit odbc configuration is entirely separate from the regular odbc configuration. It's not the instruction set that the dll uses it's which set of shared data structures it uses and which api it provides ON the Way back machine we had System and System32. System32 came about in Windows 3.1 if memory serves to separate where 16 and 32 bit are placed. This time MS around is not doing that System32 is being used for 64bit apps and SysWoW64 is where all the legacy 32 bits at getting stuck. 32 bit apps in a directory with 64 in its name, and 64 bit apps in directory thats intention was/is 32bit apps =-O 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Scratching my head why results are different between machines.
On 3/3/2010 3:40 PM, Michael Gould wrote: On my machine the UUID that is returned is 16 bytes and I cannot make out any relevant numbers from the UUID key in the citystateinfo table. I've tried this in a Windows XP machine and a Windows 7 64 bit. Now here is the weird thing. I did a complete backup of my database and had the author restore the data on his. It works every time there. He then did a pg_dumpall and I reloaded my db with his script and while his runs all of the queries fine, mine is still not returning the proper length UUID column. I'm using the UTF ODBC driver 8.4 and Postgres 8.4.2. You state the PgAdmin returns the correct result on your machine connecting to the database that returns the wrong result using Data Conversion App, RIGHT??? If so I'm betting it has to do with ODBC driver doing something odd or the DATA Conversion App is doing something ODD. What is the name of app being used to convert the data? Which ODBC are you using ? http://psqlodbc.projects.postgresql.org/ or http://projects.commandprompt.com/public/odbcng/ Note: PgAdmin does not use ODBC interface . 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Scratching my head why results are different between machines.
On 3/3/2010 5:16 PM, Michael Gould wrote: One thing I've noticed is that on my machines, when I install the odbc driver I get no error messages but when I look in the ODBC administrator I do not see any entry for PostGres in the drivers list. I do know that it somehow is working because the DMC conversion tool only uses ODBC and builds it's data structures based on what it gets back from querying the database. Programs like WinSQL also work and I believe it uses ODBC. In the past I believe I remember seeing the Postgres driver listed in the ODBC admin tool under the drivers tab. I'm running on a Windows 7 64 bit machine and I'm logged into our domain as the domain administrator. Is there something else I need to do to install the odbc driver under windows? I've even turned UAC off and it didn't seem to help Can't help on the Windows 7 ODBC not appearing in the drivers list If you can manage to configure a Data Source Name turn on the logging then connect using this DSN in your DMC app just do a simple select The log will get big quick so keep the selects small and use limit. This will allow to see what ODBC driver is up to open the C:\mylog_XXX and look at and you will find all the commands sent the Postgresql and the results then you will look for something like this. This will tell us what the Select looks like and what being sent DMC app. *[1444-387.642]Exec_with_parameters_resolved: copying statement params: trans_status=1, len=51, stmt='Select user_password from mediawiki.mwuser limit 10'* [1444-387.645] stmt_with_params = 'Select user_password from mediawiki.mwuser limit 10' [1444-387.646]about to begin SC_execute [1444-387.647] Sending SELECT statement on stmt=02C5D8C0, cursor_name='SQL_CUR02C5D8C0' qflag=0,1 [1444-387.648]CC_send_query: conn=04313E00, query='Select user_password from mediawiki.mwuser limit 10' --snip-- *[1444-387.665]qresult: len=44, buffer=':B:e2e28556:8ff13b68ebc64eccad0921d1571bf08c'* [1444-387.666]qresult: len=0, buffer='' *[1444-387.667]qresult: len=44, buffer=':B:60a118ba:592cb633245cc330d48cc2e52c922f90'* [1444-387.667]qresult: len=0, buffer='' [1444-387.668]qresult: len=0, buffer='' [1444-387.668]qresult: len=0, buffer='' [1444-387.669]qresult: len=0, buffer='' [1444-387.669]qresult: len=0, buffer='' [1444-387.670]qresult: len=0, buffer='' 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] windows7 login- user account
On 2/10/2010 7:15 PM, paul e wrote: Before Installed postgresql Windows7 went straight to my user account. Now when it boots I have to go to a selection page where I choose between my user account and a postgresql user account. Is there any way to bypass this so it boots directly to my user account? Your E-mail and More On-the-Go. Get Windows Live Hotmail Free. Sign up now. http://clk.atdmt.com/GBL/go/201469229/direct/01/ go here for instructions http://www.howtogeek.com/howto/windows-vista/make-windows-vista-log-on-automatically/ 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] Best way to handle multi-billion row read-only table?
On 2/9/2010 12:47 PM, Asher wrote: Hello. I'm putting together a database to store the readings from various measurement devices for later processing. Since these things (water pressure monitors attached to very large water pipes) take readings at 200Hz and are typically deployed over multiple sites for several months at a time I've got many billions of rows of data, each (at the moment) with the following simple format: value REAL NOT NULL, sample_time TIMESTAMP WITH TIME ZONE NOT NULL, channel INTEGER REFERENCES channel(id) NOT NULL (Where the channel table contains metadata to identify the particular sensor, data logger, etc. used to obtain the data and the combination of channel and sample_time is unique.) Well first is that 200hz meaning 200 samples per channel per second. That is very fast sampling for pressure sensor, I would be surprised if the meters are actually giving real results at that rate. I would look at reducing that down to what the meter is actual capable of sending What kind of AD card is being used as this effects what makes sense to record. I would look into table partitioning http://www.postgresql.org/docs/current/static/ddl-partitioning.html http://wiki.postgresql.org/wiki/Table_partitioning A one big index for such a small record will not be a big win because the index are going to be the same size as table. Look into limiting the number of records each index covers. http://www.postgresql.org/docs/8.4/static/sql-createindex.html 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Best way to handle multi-billion row read-only table?
On 2/9/2010 4:41 PM, Asher Hoskins wrote: Thanks for that, it looks like partitioning is the way to go. I'm assuming that I should try and keep my total_relation_sizes less than the memory size of the machine? This depends on what the quires look like. As other have stated when partitioning you have to consider how the data is quired. If I partition so that each partition holds data for a single channel (and set a CHECK constraint for this) then I can presumably remove the channel from the index since constraint exclusion will mean that only partitions holding the channel I'm interested in will be searched in a query. Given that within a partition all of my sample_time's will be different do you know if there's a more efficient way to index these? Given the timestamp will most likely be the where clause, NO on the plus side its only 8 bytes 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Order by and strings
On 2/8/2010 7:09 PM, Fredric Fredricson wrote:
Hi!
New to the list with a question that I cannot find the answer to in
the manual or on the internet but I suspect is trivial. If somebody
could point me in the correct direction I would be greatful.
This is what I do (condensed, of course):
# create table tmp ( x text ) ;
CREATE TABLE
# insert into tmp(x) values ('a'),('c'),('-b') ;
INSERT 0 3
# select * from tmp order by x ;
x
a
-b
c
(3 rows)
It has to do with the collation you are using
see
http://www.postgresql.org/docs/8.1/interactive/charset.html
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] Documentation - PgAdmin
over the last 3 years i can't recall it being included in the msi installer Now the MSI installers from Enterprise DB is a One Click installer i'm not sure whats all included. I have it installed on one or 2 machines but never really dug into what's all included to tell you what all in the new installer. but the the pgAdmin installer from here http://www.postgresql.org/ftp/pgadmin3/release/v1.10.0/win32/ As far I can think back this installer never included CHM. I found it annoying but given this supports multiple versions of PG which CHM file does one include. Message from mailto:db.subscripti...@shepherdhill.biz at 07-05-2009 07:14:49 AM -- Quoting justin jus...@emproshunts.com: CHM has not been packaged for as long as i know of with PgAdmin.. Justin, Are you sure? Windows help file CHM has been the default help file that pgAdmin opens when you click on the help icon on the toolbar of pgAdmin (uptill Postgresql's version 8.3.7) using MSI installer. Or are you saying the MSI package maintainers included the CHM files and not pgAdmin packagers? If that is the case, I should direct my question to EnterpriseDB who is the new windows binary maintainer. Regards, Chris
Re: [GENERAL] 64 Bit ODBC Drivers for windows
use the .net provider http://npgsql.projects.postgresql.org/ I'm not sure of a 64bit build. although a 32bit version should run on 64 bit windows without any problems. Message from mailto:drewtimm...@gmail.com Andrew Timmins drewtimm...@gmail.com at 06-29-2009 05:36:58 PM -- Is there anyway to connect to postgre using a 64 bit Windows OS? I am having problems connecting to a local DB since i have upgraded my computer. I would like to connect using C#.NET Any help would be appreciated. Drew
Re: [GENERAL] Very weird problem of order by in postgresql
Thisis the same problempostgresql has whendoing sorting when runonwindows vs. linux. Postgresql relies on the OS tohandlecollating aka sort orders. to Quote PostgreSQL uses the standard ISO C and POSIX locale facilities provided by the server operating system http://www.postgresql.org/docs/8.3/interactive/locale.html Message from mailto:[EMAIL PROTECTED] Peter Cai [EMAIL PROTECTED] at 09-08-2008 11:51:32 AM -- Hi all, I have 2 postgresql running on linux on 2 different physical machines. Then I create 2 identical database on them, both using utf8 as server encoding and GBK as client encoding. But when I try to order by some query result with a column containing Chinese characters, the result is different One db return result in correct Chinese alphabet order but the other doesn't! Is the problem of the configure of the operating system or the postgresql??? Thanks a lot! PS : I read some solutions on this problem. It seems that many people met the same order by problem and have to convert the strings to GBK in there sql to get correct sorted result. I don't think this is the correct solution.
