[GENERAL] UUID vs int keys....
Hello - Curious if anyone has any experience with the new UUID type in 8.3? We are currently using sequences for all of our keys and ids. However using a UUID instead of a sequence would make some parts of the application easier. However we have a few concerns about performance issues surrounding the new type. Are they any significant impacts with respect to indexing 'random' values over a serial value? The UUID would obviously take up a bit more storage than the int based option. Are there any particular join gotchas etc with using a UUID? Any other gotchas to watch out for? Thanks! -Nate ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Deferred constraints and locks...
Jan Wieck wrote: On 2/12/2008 3:04 PM, Tom Lane wrote: Nathan Wilhelmi [EMAIL PROTECTED] writes: Hello - Trying to track down a lock contention problem, I have a process that does a series of select / insert operations. At some point the process grabs a series of RowExclusiveLock(s) and has the obvious effect of stalling other processes. I logged all the statements and don't see any for update or explicit lock statements. Insert statements would naturally take RowExclusiveLock, but that doesn't block other DML operations. So the question is what *else* are you doing? Those SELECT statements aren't by chance FOR UPDATE, are they? Jan Not that I can see, at least from the PG statement logs. So as the process runs (it a long running parsing process) it does a number of selects/inserts/updates. A pattern I am seeing is that a row is inserted and then later updated as more information is known during the parsing process. I don't see any explicit lock or for updates statements being sent. I can't get a breakpoint low enough in the app yet, but it looks like some of the subsequent update statements are pulling RowExclusiveLock lock(s), maybe inserts as well. This is where I am a bit confused, from Tom's response these are table level locks. From my understanding of MVCC, I didn't think this should happen. I would expect that other transactions wouldn't see updated values which is what I am after, but the problem is that reads are blocking until this long transaction completes. What am I missing here I can see this both on 8.2 and latest 8.3. Thanks! -Nate ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Deferred constraints and locks...
Tom Lane wrote: Nathan Wilhelmi [EMAIL PROTECTED] writes: Hello - Trying to track down a lock contention problem, I have a process that does a series of select / insert operations. At some point the process grabs a series of RowExclusiveLock(s) and has the obvious effect of stalling other processes. I logged all the statements and don't see any for update or explicit lock statements. Insert statements would naturally take RowExclusiveLock, but that doesn't block other DML operations. So the question is what *else* are you doing? regards, tom lane Good question, is there anyway to figure out which rows are locked? -Nate ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Deferred constraints and locks...
Hello - Trying to track down a lock contention problem, I have a process that does a series of select / insert operations. At some point the process grabs a series of RowExclusiveLock(s) and has the obvious effect of stalling other processes. I logged all the statements and don't see any for update or explicit lock statements. So I am guesses this is more of an internal design issue. All the FKEY constraints are deferrable and initially deferred, could this be causing pg to be pulling locks on rows that are part of the fkey? Something else I should be looking for? Thanks! -Nate ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Disk configurations....
Hello - Typically case of a software guy needing to spec hardware for a new DB server. Further typified case of not knowing exact amount of data and I/O patterns. So if you were to spec a disk system for a new general purpose PostgreSQL server any suggestions of what to start with? Details I have: 1) We think 500GB is enough storage for the DB. 2) I would guess 66% read 33% write patterns, maybe closer to 50/50. I would assume that we would want to go SCSI raid. Would you do the whole disk array as Raid 5 or whole you partition it up differently? Would you go with 3x300gb disks or would you use more smaller disks to get there? Any other gotchas or suggestions to look for? Thanks! -Nate ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Specify client encoding for backups...
Hello - We are using PG_ADMIN backup to create the schema DDL scripts for our application. Is there any way to get PGAdmin to not include the encoding value or configure PostgreSQL to set the client encoding to UNICODE? Having problems with the JDBC driver and the UTF8 encoding when we try to execute the scripts as part of our build process. Thanks! -Nate ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Script to reset all sequence values in the a given DB?
Hello - Does anyone happen to have a SQL script or function that can reset all the sequence values found in a given DB? When we rebuild the DB it would be handy to be able to set all the sequence back to a known starting place. Thanks! -Nate ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Importance of CPU floating point performance...
Hello - Trying to find out how much floating point operation performance effects Postgres in general. Looking at some lower power machines that have good integer performance but not great floating point performance, shared FPU across cpus. If we store, but don't use in query criteria, floating point numbers would Postgres perform the same as machine with dedicated FPUs per cpu? Anyone have any suggestions or horror stories to share on this topic? Thanks! -Nate ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Configuration starting point...
Hello - Several people kindly responded directly to me with some specific suggestions for this, however the message was mistakenly deleted and not in the archives. If you remember who you are and wouldn't mind resending that would be fantastic! Thanks! -Nate Hello - Just installed 8.2.4 on a Solaris 9 box. It's an 8-way (15000 MHz sparc) with 32GB of ram. We don't know the exact table structure yet or access patterns, although the first thing that will be looked at is a Sesame triple store DB. I would expect that this DB will be more skewed to reads than writes. Based on this, are the out of the box configs pretty good or are there any recommended changes I should be making to start with? Thanks! -Nate ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Configuration starting point...
Hello - Just installed 8.2.4 on a Solaris 9 box. It's an 8-way (15000 MHz sparc) with 32GB of ram. We don't know the exact table structure yet or access patterns, although the first thing that will be looked at is a Sesame triple store DB. I would expect that this DB will be more skewed to reads than writes. Based on this, are the out of the box configs pretty good or are there any recommended changes I should be making to start with? Thanks! -Nate ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Data corruption problem...
Hello - Ran into an interesting problem last week with data unexpectedly being deleted. Has anyone else experienced this and have any suggestions on how to prevent this in the future? Here is the environment: Server: 8.0.3 on Solaris 9 JDBC Driver: 8.0.311 This is the series of statements that cause the problem, but only under a certain condition listed below. delete from X where id in (select bad_id from Z where name='qwerty'); delete from Y where id in (select bad_id from Z where name='qwerty'); delete from Z where name='qwerty'); The first 2 statements have an error, column bad_id does not exist in table Z. If I run this normally from PGAdmin the first 2 silently fail, and the last succeeds. In this case tables X and Y are untouched. An error for this would be nice but the data is OK, so far so good. Now we have J2EE (Tomcat hosted) app that access this database via connection pool / JDBC driver. The problem scenario is as follows: 1) Start the J2EE app and do some work to access the database, although I don't think this matters. 2) Shut down the app server, and as result the connection pool starts to shut down. 3) Once the connection pool has started to shut down execute the statements listed above from PGAdmin. 4) PGAmin will hang for a second like it's waiting for locks. 5) The statement will return normally, reporting the rows affected from the last statement. 6) Tables X and Y are now empty. This is not good. Now if I fix the first 2 statements the problem doesn't happen, I can execute it during shutdown and the data is OK. Obviously I would really prefer not to have times where executing a bad SQL statement deletes the whole table. Has anyone seen this before, any suggestions how to debug this or where to start looking? Thanks!!! -Nate ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
