5 ms·
while (true) { Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("SELECT 1"); rs.close(); stmt.close();
by sehrope 13y ago
while (true) {
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT 1");
rs.close();
stmt.close();
org.postgresql.PGNotification notifications[] = pgconn.getNotifications();
handleNotifications(notifications);
Thread.sleep(100);
}
The problem with this approach[1] is there's a 100ms of latency between polling attempts (the sleep call).
LISTEN/NOTIFY is very useful, particularly the transactional nature and not requiring any additional tech stack (e.g. no need for a separate MQ server), but for frequent/high-value/lower latency signalling you're better off using something that doesn't require sleeping/polling.
[1]: That and the lack of exception handling or resource cleanup.
- yummyfajitas 13y agoUnfortunately it isn't correct that you don't need a separate MQ. Postgres DB connections are not cheap - the best way to use this pattern is to have a single server listening to PG notifications and publish them to a real MQ. Also the 100ms of latency is a limitation of JDBC, not of using Postgres. Note the complete lack of sleeping in the python sample code.
- masklinn 13y ago> Also the 100ms of latency is a limitation of JDBC Yeah, JDBC has no provision for asynchronous notification. The alternative is to create/use libpq bindings directly.
- sehrope 13y ago> Unfortunately it isn't correct that you don't need a separate MQ. Postgres DB connections are not cheap That depends on your app size. If you're building something relatively small then having a few additional DB connections vs a dedicated MQ server can be worth it (it's really just extra shared memory for the connection). I do agree though that most folks are better off just using a real MQ server. For anything larger (both app size and app scale) it ends up being much better. > ... the best way to use this pattern is to have a single server listening to PG notifications and publish them to a real MQ. Another approach I've been looking at is creating a writable FDW[1] that bridges to an MQ system. That combined with a PG background worker[2] to listen for notifications gives you a transactional system that starts/stop with your database. [1]: http://wiki.postgresql.org/wiki/Foreign_data_wrappers http://wiki.postgresql.org/wiki/Foreign_data_wrappers [2]: http://www.postgresql.org/docs/9.3/static/bgworker.html http://www.postgresql.org/docs/9.3/static/bgworker.html
- ibotty 13y agothat sounds great. be sure to write about it when you (or someone else) implements it!