Main navigation

pgjdbc-ng for Postgresql LISTEN and NOTIFY

Using pgjdbc-ng Improves Overhead.

pgjdbc-ng is the perfect solution when using Postgresql LISTEN and NOTIFY. I had a need to get notified when a table has a row inserted into it. Once notified I can then go read additional data to complete my processing. The initial idea was to poll the database every 10 seconds to see if a new row was inserted. This polling would work but too much overhead. pgjdbc-ng is what I needed.

Just What I Needed

Looking around I found that Postgresql has LISTEN and NOTIFY statements that support the event handling I am looking for. One issue, the default Postgresql JDBC driver doesn’t block waiting for the event to happen. Instead you need to continue to poll the database for the events. Better on the overhead since no query is actually executed but not optimal. I found that pgjdbc_ng is a re-written JDBC driver that does block and wait for an event to happen. Perfect solution! Also, as a side note the Postgresql C libraries work a bit different. Here is an example of the C implementation.

Whats needed to make this work using pgjdbc-ng?

Table to monitor

PSQL Function to send the notify event with a payload

PSQL Trigger to call the above function on a row insert

Java code to listen for events

Table to monitor

PgSQL

1

2

3

4

5

6

CREATETABLEdm_queue

(

idinteger,

domainidinteger,

commandcharacter varying(1024)

);

PSQL Function to send the notify event with a payload

PgSQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

CREATEORREPLACEFUNCTIONqueue_event()RETURNSTRIGGERAS$$

DECLARE

datajson;

notificationjson;

BEGIN

-- Convert the old or new row to JSON, based on the kind of action.

-- Action = DELETE? -&gt; OLD row

-- Action = INSERT or UPDATE? -&gt; NEW row

IF(TG_OP='DELETE')THEN

data=row_to_json(OLD);

ELSE

data=row_to_json(NEW);

END IF;

-- Contruct the notification as a JSON string.

notification=json_build_object(

'table',TG_TABLE_NAME,

'action',TG_OP,

'data',data);

-- Execute pg_notify(channel, notification)

PERFORMpg_notify('q_event',notification::text);

-- Result is ignored since this is an AFTER trigger

RETURNNULL;

END;

$$LANGUAGEplpgsql;

PSQL Trigger to call the above function on a row insert

PgSQL

1

2

3

CREATETRIGGERqueue_notify_event

AFTERINSERTONdm_queue

FOREACHROWEXECUTEPROCEDUREqueue_event();

Java code to listen for events

Java

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

importjava.sql.SQLException;

importjava.sql.Statement;

importjava.util.concurrent.ArrayBlockingQueue;

importjava.util.concurrent.BlockingQueue;

importcom.impossibl.postgres.api.jdbc.PGConnection;

importcom.impossibl.postgres.api.jdbc.PGNotificationListener;

importcom.impossibl.postgres.jdbc.PGDataSource;

/**

* This program uses the pgjdbc_ng driver which has an asynchronous

* implementation for blocking on the Postgres NOTIFY/LISTEN events.

*

* No polling is done using this driver. You will see a forever loop

* "while(true)" in the main(). This is done to keep the program running

* and listening to multiple events happening in Postgres. Normally you

Testing it out

Start the Java program running. Make sure to have the environment variables set so the program can connect to the database.

Next, go into pgAdmin or psql and run:

PgSQL

1

insertintodm_queuevalues(6,6,'here');

You should see the following output:
/channels/q_event {“table” : “dm_queue”, “action” : “INSERT”, “data” : {“domainid”:6,”id”:6,”command”:”here”}}

The payload is formatted into JSON by the Function and contains the full row that was inserted. Events can by issued on INSERT, UPDATE, and DELETE by just modifying the Trigger’s AFTER clause.
The Java program is in an infinite loop in order to test the notifies over and over.