1,609 reputation
416
bio website wiki.postgresql.org/wiki/…
location Madison, WI
age 59
visits member for 2 years, 3 months
seen Jul 1 at 17:17

I've been making my living working with computers since 1972. In 1980 I founded a consulting company I ran until 2005; during that time I worked with a wide variety of applications, organizations, and technical environments. I'm now with EnterpriseDB as a database architect. I'm a committer for the free, open source version of PostgreSQL, and an active member of that community.

By far the largest patch I've worked on for PostgreSQL has been the Serializable Snapshot Isolation (SSI) implementation which went into PostgreSQL version 9.1. This was a joint project with Dan R.K. Ports of MIT, with helpful input and support from many in the PostgreSQL community. Our paper on that effort can be found here:

http://vldb.org/pvldb/vol5/p1850_danrkports_vldb2012.pdf

For the PostgreSQL docs on the feature, see:

http://www.postgresql.org/docs/current/interactive/transaction-iso.html

For a number of examples see:

http://wiki.postgresql.org/wiki/SSI

More recently I've added support for creating and refreshing materialized views to PostgreSQL, and am in the process of adding support for incremental maintenance based on the view's declaration.


Apr
11
awarded  Yearling
Sep
17
comment How to optimization database for heavy I/O from updates (software and hardware)
In addition to what @dezso said, hard drives are OK for storage dedicated to WAL because access is almost entirely sequential, so seek time is minimal..
Sep
16
answered How to optimization database for heavy I/O from updates (software and hardware)
Apr
11
awarded  Yearling
Feb
18
comment Postgresql - Segmentation fault (core dumped)
To get a core dump in Linux (which is one of your tags), you should run ulimit -c unlimited in the session or script which is starting the executable which is getting the segmentation fault. It looks like this setting is in effect, since it says "Segmentation fault (core dumped)". You can examine the back-trace and values of variables in gdb if you specify the core file when you start it. For that to be useful, you must have built with debug info, and it sometimes helps to reduce the optimization level of code.
Feb
18
comment Is PostgreSQL replication production ready?
@CraigRinger: What I have seen people ask for is not quite what you stated, they sometimes request "Use sync rep but automatically fall back to async rep if the sync is taking too long." So they do not want to pause the master if it falls too far behind the replica -- that's exactly the case where they want it to confirm commits quickly, without any write to another site. To me that sounds like a case of promising more than is being delivered. They want up-front "Yeah, you have sync rep; you're safe." and after a crash "That committed data is gone; it wasn't really written anywhere else."
Feb
10
revised Postgresql - Segmentation fault (core dumped)
Add a paragraph to address the setfault aspect of the question.
Feb
10
answered Postgresql - Segmentation fault (core dumped)
Feb
10
comment Is PostgreSQL replication production ready?
There is one thing about sync rep which surprises some users, but really makes sense if you think about it: the commit of a transaction which is subject to synchronous replication will not return until the transaction has been persisted on at least one cluster besides the master. People coming from some other systems feel that should happen unless the replication attempt takes too long, which would mean "it's synchronous unless it's not," which is not an acceptable guarantee to the PostgreSQL community. Use multiple sync targets to avoid a stall if the replica fails, or use async.
Feb
8
awarded  Custodian
Feb
8
reviewed Satisfactory Decomposition of a relation to 2NF then to 3NF
Jan
18
revised how to query for rows by specifying the range of row numbers in postgresql
Clean up formatting to better represent quotes.
Dec
3
awarded  Enlightened
Dec
3
awarded  Nice Answer
Nov
2
awarded  Nice Answer
Oct
29
comment Locking issue with concurrent DELETE / INSERT in PostgreSQL
As long as you are using READ COMMITTED transactions, you have a race condition: what would happen if another transaction inserted a new row after the first DELETE started and before the second DELETE started? With transactions less strict than SERIALIZABLE the two main ways to close race conditions are through promotion of a conflict (but that doesn't help when the row is being deleted) and materialization of a conflict. You could materialize the conflict by having an "id" table that was updated for every row deleted, or by explicitly locking the table. Or use retries on error.
Oct
28
comment Locking issue with concurrent DELETE / INSERT in PostgreSQL
I'm sure there's a way to get whatever it is that you need working to work consistently with both databases; if nothing else setting the transaction isolation level to SERIALIZABLE and handling serialization failures (SQLSTATE 40001) by retrying the failed transaction from the start would almost certainly do it. For this particular example you want each transaction to leave the database in exactly the same state it started in, which is very easily done in any case, by not doing DML. Perhaps an example closer to what you need would draw useful suggestions for less strict isolation levels.
Oct
27
revised Locking issue with concurrent DELETE / INSERT in PostgreSQL
Add some additional elaborations.
Oct
27
answered Locking issue with concurrent DELETE / INSERT in PostgreSQL
Oct
17
comment Creating High Availability Cluster with PostgreSQL
There are some links here that may be of interest: wiki.postgresql.org/wiki/Category:Replication