postgresql-meet-your-queueの英文抜き出し

AMQP for PostgreSQLの紹介の資料の抜き出し。
http://www.slideshare.net/postwait/postgresql-meet-your-queue

PostgreSQL meet your Queue

- - - - -

・Fast.
・Extensible.
・Tablespaces.
・Robust data types.
・Partitioning (albeit fake).
・Partial and functional indexes.
・Extremely supportive community.
・Extremely compliant with database standards

- - - - -

■PostgreSQL is not the “world”

・Inevitably, we must interact with the rest of the world.
・“non-SQL” components:
- nosql systems
- caching systems
- search systems (solr/lucene)
- management processes

- - - - -

■Appropiare Typicalis

・Enforce in the application:
- the application code that updates the price or description of a product in the products table;
- the application submits the updates to the search index system.
・The flaw:
- psql# UPDATE products SET description = REPLACE(description, ‘behaviour’, ‘behavior’);
- Administrative fixes like that require out-of-band dependency handling.

- - - - -

■A Solution

・Ideally, the database would notify all of these systems.
・The most common case I see: memcached.
- app: pull from memcached user::jesus@omniti.com
if not found:
select * from users where email=‘jesus@omniti.com‘
put row in memcached at user::jesus@omniti.com
- app: update users set mood=‘happy‘ where email=‘jesus@omniti.com‘
(a) purge memcached record
(b) get full row and replace in memcached
- hence: pgmemcache
・Problem:
- need a Postgres module for each remote component

- - - - -

■Enter Queueing

・Queueing?
- A generic message bus that allows PostgreSQL to communicate
with other components in the architecture.
- Enter AMQP: “Advanced Message Queueing Protocol”
- Why not STOMP?
- Why not Starling?
- AMQP has been around the block, and the specification is quite complete.
- Almost every “real” message broker implementation supports AMQP

- - - - -

■Setups: Installing

・svn export \
https://labs.omniti.com/pgtreats/trunk/contrib/pg_amqp
・cd pg_amqp
・make USE_PGXS=1
・make install
・add to postgresql.conf:
shared_preload_libraries = 'pg_amqp.so'
・(re)start postgres
・load the pg_amqp.sql file into your database.

- - - - -

■Setup: configuring your broker

INSERT INTO amqp.broker (host,port,vhost,username,password)
VALUES (‘localhost’,5672,NULL,‘guest’,‘guest’)
RETURNING broker_id

- - - - -

■Setup: declaring an exchange

・This can often be done outside of the AMQP client
- using an AMQP management process
(that is just, in fact, an AMQP client)
・Often, another component has already created the exchange.
・If you really need to do it within PostgreSQL:
SELECT amqp.declare_exchange(broker_id, exchange_name, exchange_type, passive, durable, auto_delete)

- - - - -

■Usage: sending messages

・How do I connect?
- It’s implicit... you don’t need to.
・How do I disconnect?
- Broker connections are cached and live across transactions.
- If you want to explicitly disconnect:
SELECT amqp.disconnect(broker_id);

- - - - -

■Usage: sending messages for real

・Sending messages as a part of my transaction:
SELECT amqp.publish(broker_id, exchange, routing_key, message);
- This will publish the “message” over the specified “exchange” using
the specified “routing_key,” but only on transaction commit within Postgres.

・Sending messages NOW:
SELECT amqp.autonomous_publish(broker_id, exchange,routing_key, message);
- Publish the same message, but do it immediately.

- - - - -

■A dark side: unsafe? WTF?
・Currently, pg_amqp uses the AMQP 0.8 specification.
・The AMQP 1.0 specification introduces formal 2PC.
・It is possible in the current implementation to have AMQP transaction
fail when we commit it (in postgres’s commit txn hook) in a fashion
that we cannot act on. Ouch.
・This only happens when the AMQP broker crashes between the last
in-transaction publish call and the COMMIT on the database side.
・Once RabbitMQ supports AMQP 1.0, I'll update the driver to use 2PC.

- - - - -