Posted
about 16 hours
ago
I added a couple of fixes to the code and released it as 1.0. We have been using it here at hi5 for some time w/o problems. Thanks everyone who has helped with feedback. Also thanks to Devrim GUNDUZ for his help. The latest version can be downloaded on pgfoundry.
Posted
about 20 hours
ago
I write a lot of stored procedures in Postgres, most of it with PL/pgSQL. This is why Pavel Stehule is my hero. For the last 3 years this amazing Czech has been adding more and more stored procedure goodness to PostgreSQL
Posted
2 days
ago
PGDay.eu was held this week-end in Paris, and it really was a great
moment. Lots of attendees, lots of quality talks (slides are online), good
food, great party: all the ingredients were there!
It also was for me the occasion to
... [More]
first talk about this tool I've been
working on for months, called pg_staging, which aims to empower those boring
production backups to help maintaining staging environments (for your
developers and testers).
All in all such events keep reminding me what it means exactly when we way
that one of the greatest things about PostgreSQL is its community. If you
don't know what I'm talking about, consider joining! [Less]
Posted
2 days
ago
I’ve got a busy couple of weeks in November:
November 11, 2009 – I’m presenting Bucardo (a sweet replication system for Postgres) at the Portland Perl Mongers group, 7pm at Free Geek.
November 13-14, 2009
... [More]
– I’ll be helping run OpenSQL Camp with Eric Day here in Portland, OR. We’re having it at Souk, and kicking things off on Friday night at Old Town Pizza, starting around 6pm. Eric asked about having an n-master (multi-multi-multi…etc master) replication session, so I might talk with him about that there.
November 19, 2009 – PostgreSQL Clustering Summit in Tokyo. I’ll be giving a 5-minute presentation on the state of Bucardo development, and meeting (or seeing again!) the major contributors to replication and clustering technology for Postgres.
November 20-21, 2009 – Japanese PostgreSQL User Group 10th Anniversary Summit. I’ll be presenting a talk on User Groups with Magnus Hagander, President of PostgreSQL Europe.
I’m happy to say that I’ve got my slide decks done well in advance this time, and am mostly working on example configurations. I started a repo on github to hold my bucardo examples. Enjoy! [Less]
Posted
2 days
ago
In the last article, I argued that:
A schedule conflict is a typical business problem.
The later you try to resolve a schedule conflict, the more costly it is to resolve.
In particular, there is a big jump in the cost the moment
... [More]
after conflicting data is recorded.
Therefore, it’s best for the DBMS itself to enforce the constraint, because only the DBMS can avoid the conflict effectively before the conflict is recorded.
Then, I opened up a discussion to see how people are dealing with these schedule conflicts. In the comments I received at the end of the article, as well as other anecdotes from conferences, user groups, mailing lists, and my own experience, the solutions fall into a few categories:
The rate of conflicts is so low that the costs are not important. For instance, you may make 0.1% of your customers unhappy, and need to refund them, but perhaps that’s a cost you’re willing to pay.
The application receives so few requests that performance is not an object, and serialization of all requests is a viable option. The serialization is done using big locks and a read-check-write cycle. Even if performance is not an object, these applications sometimes run into maintenance problems or unexpected outages because of the big locks required.
You can break the time slices into manageable chunks, e.g. one day chunks aligned at midnight. This kind of solution is highly specific to the business, reduces the flexibility of the business, and often requires a substantial amount of custom, error-prone procedural code.
Complex procedural code: usually a mix of application code, functions in the DBMS, row-level locking, static data in tables that only exists for the purposes of row-level locks, etc. This kind of solution is generally very specific to the application and the business, requires lots of very error-prone custom procedural code, is difficult to adequately test, and it’s hard to understand what’s going on in the system at any given time. Hunting down sporadic performance problems would be a nightmare.
Those solutions just aren’t good enough. We use relational database systems because they are smart, declarative, generally useful for many problems, and maintainable (Note: these principles contrast with NoSQL, which is moving in the opposite direction — more on that in another article).
The project that I’ve been working on for PostgreSQL 8.5 is called “Operator Exclusion Constraints“. These are a new type of constraint that most closely resembles the UNIQUE constraint, because one tuple can preclude the existence of other tuples. With a UNIQUE constraint on attribute A of a table with attributes (A, B, C), the existence of the tuple (5, 6, 7) precludes the existence of any tuple (5, _, _) in that table at the same time. This is different from a foreign key, which requires the existence of a tuple in another table; and different from a CHECK constraint which rejects tuples independently from any other tuple in any table (and the same goes for NOT NULL).
The same semantics as a UNIQUE constraint can be easily specified as an Operator Exclusion Constraint, with a minor performance penalty at insert time (one additional index search, usually only touching pages that are already in cache). Exclusion constraints are more general than UNIQUE, however. For instance, with a complex type such as CIRCLE, you can specify that no two circles in a table overlap — which is a constraint that is impossible to specify otherwise (without resorting to the poor solutions mentioned above).
This applies to temporal keys very nicely. First, get the PERIOD data type, which allows you a better way to work with periods of time (sets of time, really), rather than points in time. Second, you need to install the btree_gist contrib module. Then, use an exclusion constraint like so:
CREATE TABLE room_reservation
(
name TEXT,
room TEXT,
during PERIOD,
EXCLUDE USING gist (room CHECK WITH =, during CHECK WITH &&)
);
That will prevent two reservations on the same room from overlapping. There are a few pieces to this that require explanation:
&& is the “overlaps” operator for the PERIOD data type.
USING gist tells PostgreSQL what kind of index to create to enforce this constraint. The operators must map to search strategies for this index method, and searching for overlapping periods requires a GiST index.
Because we are using GiST, we need GiST support for equality searches for the TEXT data type, which is the reason we need the btree_gist contrib module.
Conflicts will only occur if two tuples have equal room numbers, and overlapping periods of time for the reservation.
This solution:
Performs well under light and heavy contention. Not quite as well as a UNIQUE constraint, but much better than the alternatives, and without the surprises you might get from using big locks. Note that the constraint will be enforced at some point, so ignoring the problem is not a high-performance alternative (interpersonal communication has higher latency than a computer).
Is declarative. The implementation shows through a little bit — the user will know that an index is being used, for instance — but it’s a relatively simple declaration. As a consequence, it’s not very error-prone from the schema designer’s standpoint.
Is not specific to the business. You don’t have to decide on an appropriate time slice (e.g. one hour, one day, etc.); you don’t have to try to partition locks in creative ways; you don’t have to write procedural code (in the database system or application); and you don’t have to come up with interesting ways to detect a conflict or notify the user.
Temporal keys are just one part of the support required for effective temporal data management inside the DBMS. However, it’s one of the most important pieces that requires support from the core engine, and cannot be implemented as a module. [Less]
Posted
2 days
ago
by
nos...@example.com (Robert Treat)
While a good portion of the Postgres community was making their way to France for PGDay Europe, fellow BWPUG member Greg Smith and I were manning the home-front in Baltimore at the 2009 Large Installation and Systems Administration (aka LISA)
... [More]
conference, held this year in Baltimore, MD. The two of us took to the exhibition floor to man a booth for the PostgreSQL project, a two-day stint that gave us plenty of face time with the LISA attendees. For me it had been three years since my last LISA conference (at my other local city, Washington, D.C.) so I was curious to see how things had changed since then. Some thoughts/notes I took while working the show floor:
1) There were a lot of Postgres users at the show. A lot of happy Postgres users. Compared to 3 years ago when we ran into just a few, more than half the people who stopped at the booth were already using Postgres.
2) No one asked me "so why should I use Postgres instead of MySQL". Which is not to say the topic of MySQL didn't come up, but the above question is by far the #1 question I normally hear working community booths (even got it at OSCon this summer), so to not get anyone asking was quite a surprise in retrospect. I think this is probably due to two factors; first that Postgres advocacy has been working hard to make the case for Postgres and clarify the differences between the two projects, and second that we've gotten a lot of converts over the past three years so there's much more knowledge about Postgres these days. A couple people showed there was still work to do; some glossed over differences between the projects, and one person even thought Postgres was the commercial version of MySQL; so the job of Postgres advocacy goes on.
3) So where did they come from? Many of the people who told us they were happy Postgres users also mentioned previous database systems they had worked on. These aren't formal numbers, but I'd say the breakdown was close to 55% MySQL, 35% Oracle, and 5% Sybase and 5% MSSQL. Again rough numbers, but that seems about right. As the LISA crowd is heavy on system administrators, the complaints were mostly that MySQL was a pain to keep running (regular corruption issues and similar problems), and Oracle just couldn't justify its cost.
4) One person I spoke told me about a problem they had with setting up authentication. They run a university where they initially setup authentication for students via LDAP which they thought was pretty nice. They then ran into a problem because the students had to write scripts for classes, which required them to hard-code in their ldap passwords in the scripts, which were easily read by other students. They ended up solving the problem by configuring the apache server to run files as the script owner rather than the more standard "nobody" user, which allowed them to prevent others from seeing individual scripts. This isn't the first person I've run into with this type of problem; I'd love to see more people blogging on topics like this.
5) Several people asked about the business model behind Postgres. Many people get stuck in the idea that every piece of open source project has a single corporate backer/owner. I've been a big proponent of highlighting both the strength of the Postgres community and the nature of being a true Open Source project; so for me these are great questions to get to talk about, but it's something we should make sure other folks volunteering for booth duty are prepared to answer.
Finally, I want to say a big thanks to the folks running LISA and to the crowd at large. In a conference thin on DBA's, we still managed to get a number of donations which will help with further advocacy efforts. I guess system admins are into solid database software too. [Less]
Posted
3 days
ago
by
nos...@example.com (Magnus Hagander)
I'm currently sitting on my flight home from Paris CDG, after a couple of very hectic days. It's going to be a couple of days (which in reality is going to drag out into a couple of weeks due to other work engagements and then travel for the JPUG
... [More]
conference) before it'll be possible to completely evaluate the conference and things around it, but here's what I have so far.
I'm going to leave the evaluation of the talks themselves to somebody else. There were many others of the "regular PostgreSQL bloggers" present at the conference and we've already seen some posts around it. Hopefully there will be more, both in French and English. If you are blogging about this and your blog isn't already up on Planet PostgreSQL, please consider adding it so that the community at large gets notification of your posts.
Continue reading "PGDay.EU 2009 - it's a wrap" [Less]
Posted
3 days
ago
by
nos...@example.com (Andreas 'ads' Scherbaum)
Andreas 'ads' ScherbaumPGDay.EU 2009 in Paris is over. It was a very thrilling conference with many impressions and interesting talks. But in sequence.
We (my wife, my son plus me) had met Andreas Kretschmer and his family on the Düsseldorf
... [More]
airport and headed together to Paris. In the evening Andreas and i reached the Dalibo office (on the way we could gaze in wonder about the impressive theft protection for the cars) just to find out that everyone is already in the restaurant ;-)
On thursday we helped preparing the conference, David already blogged about his experience with nerds and logistics. Oh, and if a french is telling you "in 200 meters" - never ever trust him. Multiply the number by 5 or 6, that's probably the correct distance. It's like "10 italian minutes" last year in Prato ;-) By the way, the shortest path to the restaurant (near the hotel) is not a short walk. No, it's a round trip using the Metro, followed by a longer walk including several "200 meters" units. That's the experience we gained on the way to the restaurant where everyone was meeting this evening.
Friday started with hecticness: every visitor was to register, name tags was to cut out and assemble (yes, David, i will start even more in advance with the name tags next year ;-) ), find the fitting shirt for every visitor, and the rooms was to be prepared too. The conference itself started with an opening session by Magnus and Jean-Paul, followed by Simon's very promising talk, where he showed a short overview over the last 20 years and gave a view forward on his ideas for the next 20 years. I spent the forenoon on booth duty on the PGEU stand, until we got (about noon) the two long-missing parcels with elephants. I sent two boxes with elephants (using DHL) to Paris two weeks ago. On thursday, the online tracking told us: "delivery failed", which gleu solved by calling DHL and the France postal service several times on thursday afternoon. "Just in time" one might think, but actually i prefer two or three days in advance. The 20 elephants found a new home in less than one hour.
The day ended (more exactly: the evening started) with a party, sponsored by EnterpriseDB, in the Les funables restaurant, near the venue, operated by Ivanne et Sidonie. That's the same company which did the catering for the conference and i have to admit i never attended a conference which such a good food. I should not forget to tell about the cute kids shirts, weared by Andreas' daugther and our son (well, actually, someone have a good picture with the kids+shirt on it for me?) For anyone who asked: the shirts are made in a good copy-shop in my hometown (same shop which prints my training documents). And yes, we try to have some of the shirts for FOSDEM.
On saturday we also had the developer room, which hosted interesting technical talks, in addition to the french and english track. The closing session in the amphie theathre started with the group picture, followed by an auction for the two big blue plush elephants. Sean from EnterpriseDB gave both elephants a new home, thanks a lot for this.
The lightning talks were very exhilarative. I remember (among others) Davids talk about Lolcats and how to do a lightning talk, as well as Gaviks talk - who wasn't there. The talk was - in general amusement - given by Gregory (powerpoint karaoke anyone?). Last but not least there was the Thank You to everyone who helped making the PGDay such a great success, followed by the invitation to the netherlands next year (probably in Amsterdam, probably in october).
I would like to take the chance and thank everyone (in unsorted order) who helped and made this conference possible:
the members of PostgreSQL.fr, especially Damien and Guillaume
the members of PostgreSQL.eu and all associated countries
Ivanne et Sidonie
all sponsors
ParisTech Telecom
all speakers
anyone who helped and whom i missed here
The slides will be available on the PostgreSQL wiki. We would like to ask you giving us feedback (what was good, what could be done better). The feedback site is online. [Less]
Posted
3 days
ago
by
nos...@example.com (Leo Hsu and Regina Obe)
Debugmode Wink is a freeware piece of software for both business as well as personal use for doing screencasts and incorporating sound into your screen captures. The main useful format it outputs to is macromedia flash though you can output to PDF
... [More]
for handouts and so forth. While its not a PostgreSQL related item per se,
it can be useful for making all sorts of tutorials including PostgreSQL tutorials that involve showing people how to do things on screen. We've started to experiment with tutorials of this
format and hope to get into doing
more hands on like tutorials. We'll probably be doing a using PostgreSQL in OpenOffice tutorial in this issue since it lends itself well to screen casting.
Continue reading "Wink: Making screencast tutorials" [Less]
Posted
3 days
ago
PGDay.EU is over, with plans already rolling for next year's in the
Netherlands.
