[GENERAL] Fw: UUID vs Serial or BigSerial
All, From a performance standpoint what is the downside to using a UUID column? Our current database with 25 locations after 5 years is only about 2.5 gig. We do about 200,000 orders per year so we are not getting hit with a huge amount of data. Much of our data is static or once it has been processed it is static from that point forward. The only reason that I was thinking about using a UUID data type is that this is a commerical product and we are also looking to move into the ASP space. It is possible that we might have 2 companies merge and I thought that using the UUID would cause less hassles if we had to merge 2 databases. That being said the changes of that happening are pretty slim. I've got one DBA telling me that I should be using a UUID datatype for all of our tables. Best Regards, Michael Gould, Manager Information Technology All Coast Intermodal Services, Inc. First Coast Intermodal Services, Inc. First Coast Logistics Services, LLC. 904-226-0978 904-592-5250 fax
[GENERAL] Postgres as the embedded database
I am looking to hear from people that are using PostGres as their backend in a commerically available product and how much work would be needed to ensure that our configurations are optimized for each platform along with normal database routines? What has the stability factor been once installed if the user has no administrative / superuser access to the database? In our application, we never allow for deletes to happen, the records get marked as deleted, but until a archival process is run (normally every 2-3 years) the records cannot be removed. We have used SQL Anywhere in the past and while I know that the PostGreSQL installation will be more complex than what we currently have, there are many aspects of the actual database that have made us look at it as a replacement to SQL Anywhere in our new product line. I can say that with SQL Anywhere it is pretty much install and forget it. I'd like to have it pretty much the same with PostGreSQL. With SQL Anywhere we use their internal event processor to manage the database when it is needed. We fully expect that we will need to write our own external event processor to manage some of the database maintenance processes so that isn't really a issue. We will have several hundred installations and what I don't want to do is significantly increase our maintenance costs with PostGreSQL to gain some of the functionality that it has (partitioned tables being one). Best Regards, Michael Gould, Manager Information Technology All Coast Intermodal Services, Inc. First Coast Intermodal Services, Inc. First Coast Logistics Services, LLC. 904-226-0978 904-592-5250 fax
[GENERAL] Partitioned tables and views
I have several tables that we have partitioned by physical location. This seems to give us the best overall performance when doing location specific queries. I have a few questions. 1. Is the planner/optimizer intelligent enough to know when we are not doing a query based on location? For example we might have a trailer that is used by multiple locations and we need to know all of the locations where that trailer has been used. Other queries might look for a specific work order that could only be in one of the partitions. 2. How are views handled with partitioned tables? I don't find anything in the documentation that tells me how views are handled. Depending on the view will it only use the partitioned table or will it use the master table? Best Regards Michael Gould
[GENERAL] Using PostGres general distribution
We currently use SQL Anywhere 9.0.2 as our database in our current product. The main reason is the low maintenance that is required and the installation is a breeze. All we need to do is to ship 3 dll's and a db and log file. I understand that with PostGres that the installation will end up being much more complex, however that doesn't really worry me as much as how much administration of the database is needed. SQL Anywhere has an event processor built in to make doing database backups while the system is online very easy. We also are able to do certain types of maintenance such as selective reorganize of tables, automatically adding additional free space at night so that it doesn't affect performance during processing hours and many other functions. If we had 1500 customers running our system with PostGres and we have little control over the server hardware, the OS the customer would be running the db on, is Postgres the appropriate choice or is this going to be a maintenance nightmare? How self sufficient is Postgres? Best Regards, Michael Gould, Manager Information Technology All Coast Intermodal Services, Inc. First Coast Intermodal Services, Inc. First Coast Logistical Services, LLC. 904-226-0978
[GENERAL] Data Types
We are converting our system from using Sybase's SQL Anywhere 10 to PostGres 8.3. In SQL Anywhere there technically isn't any difference in how a char and varchar is stored. They are all an array of char[1]. So we always just defined everything as a char since right truncation is the default. In PostGres though if we are using a character type column to search should we define that as a varchar or a char or does it make any difference? Best Regards Michael Gould Intermodal Software Solutions, LLC.
Re: [GENERAL] Data Types
Thanks for all of the replies. Best Regards, Michael Gould, Manager Information Technology All Coast Intermodal Services, Inc. 904-226-0978 _ From: Tom Lane [mailto:[EMAIL PROTECTED] To: Roberts, Jon [mailto:[EMAIL PROTECTED] Cc: [EMAIL PROTECTED], pgsql-general General [mailto:[EMAIL PROTECTED] Sent: Mon, 23 Jun 2008 15:00:05 -0400 Subject: Re: [GENERAL] Data Types Roberts, Jon [EMAIL PROTECTED] writes: Character will use more disk space than varchar so it does make a difference. char also has very peculiar comparison semantics. Unless your strings are really truly fixed-length, you should just about always use varchar. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] SERIAL and Primary Key
CREATE TABLE fav5.batchnumber ( batchnumberid SERIAL NOT NULL, processtype SMALLINT NOT NULL, termloc CHAR(3) NOT NULL, batchno INTEGER NOT NULL ) WITHOUT OIDS; With the above table definition, is batchnumberid by default also defined as the primary key or do I still need to define a separate Primary Key constraint? Best Regards, Michael Gould All Coast Intermodal Services, Inc. 904-226-0978
[GENERAL] feature requests
I would like to see a CREATE Variable dataname datatype added. The scope of these variables would be global. Along this same line I would like to see a way to have a trigger or rule fired upon connection initialization. This would allow for these type of variables to be SET along with other defaults that need to be setup on a per connection basis from the server side instead of the client application. Best Regards, Michael Gould All Coast Intermodal Services, Inc. 904-376-7030 _ From: Tom Lane [mailto:[EMAIL PROTECTED] To: Naz Gassiep [mailto:[EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Sent: Sat, 07 Jul 2007 19:50:03 -0400 Subject: Re: [GENERAL] Changing DB Encodings Naz Gassiep [EMAIL PROTECTED] writes: Tom Lane wrote: No, that implies a lack of error checking. Surely, then, that's a bug? Shouldn't postmaster check if a DB is in an encoding that differs from the selected locale? Yeah, it should. Whether it can is a different question. Part of the problem here is the lack of a reliable way to tell *which* encoding is implied by a locale. On some systems you can get a poorly-standardized string name for the locale's encoding; on others you can't get anything. There's been some experimental code in initdb for awhile now that tries to guess encoding from locale. I have not heard reports of it failing lately, so maybe we could promote it into a hard error check, or at least a backend-side warning at CREATE DATABASE time. It still won't help on old systems without nl_langinfo(CODESET), though. (But how many of those are left? That call is specified by the Single Unix Spec. Anybody know if it works on Windows?) regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Trigger Priority
Is there a way to determine the order that triggers are executed? We are converting from SQL Anywhere 9 and one of the features that they have is the ability to add a order statement to triggers. This allows from a programmatic way to control how triggers are fired if there is more than one trigger for a particular type (before, after) the insert, update, delete commands. Best Regards, Michael Gould All Coast Intermodal Services, Inc. 904-376-7030
[GENERAL] Another conversion from ASA to PostGres how to
All, As the subject says we are converting from Sybase's SQL Anywhere to PostGres 8.2.4. One of the features in ASA is a SET OPTION PUBLIC.login_procedure='DBA.login_check'. After a user is authenticated on a connection, if this setting is set, then the database automatically triggers this procedure to be run. We use this to set up a bunch of global variables and other settings which need to be updated when a connection is made. How would I handle this in PostGres? Best Regards, Michael Gould All Coast Intermodal Services, Inc. 904-376-7030
[GENERAL] Distributing PostGres database to various customers
All, I am new to PostGres 8 (using 8.2.4 windows version). We have for several years been using iAnywhere's SQL Anywhere product with our commercial transportation software. With ASA there are 2 files that must be distributed for the database, a filename.db and a filename.log. When we do a new installation we normally try and preload the database with data used for lookups, some registration data and if a customer is moving from another software where we've been contracted to convert their old data to our system we preload that. Once that is done we can distribute the database as part of the setup process. How can we do this with PostGres? Other than backup and restore or creating SQL scripts I haven't been able to find another method. Some of these tables may have over a million rows in them initially if we convert old data. Best Regards, Michael Gould All Coast Intermodal Services, Inc. 904-376-7030
