I'm at Windows 11 x64 machine, created the below table:
begin;
create schema if not exists tweet;
create table tweet.activity
(
messageid bigint not null primary key,
rts bigint,
favs bigint
);
commit;
Then created the below trigger function:
begin;
create or replace function tweet.tg_notify_counters ()
returns trigger
language plpgsql
as $$
declare
channel text := TG_ARGV[0];
begin
PERFORM pg_notify(channel, row_to_json(NEW)::text);
RAISE NOTICE 'New record posted';
RETURN NEW;
end;
With the following trigger:
CREATE TRIGGER notify_counters
AFTER INSERT
ON tweet.activity
FOR EACH ROW
EXECUTE PROCEDURE tweet.tg_notify_counters('tweet.activity');
commit;
And trying to listen to the notification in my go code as:
package dataBase
import (
"fmt"
"log"
"time"
"github.com/lib/pq"
)
type Config struct {
Channel string
minReconn time.Duration
maxReconn time.Duration
}
func waitForNotification(l *pq.Listener) {
err := l.Ping()
if err != nil {
fmt.Println("Error pining the database:", err)
}
fmt.Println("Connection is active")
// The application exits here and do not listen to any channel ?!
select {
case <-l.Notify:
fmt.Println("received notification, new work available")
case <-time.After(90 * time.Second):
go l.Ping()
fmt.Println("received no work for 90 seconds, checking for new work")
}
}
func Listener() {
conf := Config{
Channel: "notify_counters",
minReconn: time.Minute,
maxReconn: 5 * time.Minute,
}
reportProblem := func(ev pq.ListenerEventType, err error) {
if err != nil {
fmt.Println(err.Error())
}
}
listener := pq.NewListener(conninfo, conf.minReconn, conf.maxReconn, reportProblem)
err := listener.Listen(conf.Channel) // <<-- listen here to the trigger
if err != nil {
log.Printf(
"Failed to LISTEN to channel '%s': %s",
conf.Channel, err)
panic(err)
}
log.Printf(
"Listening to notifications on channel \"%s\"",
conf.Channel)
for {
waitForNotification(listener)
}
}
But the app is exit as:
2022/07/10 15:10:42 Listening to notifications on channel "notify_counters"
Connection is active2022/07/10 15:10:42 error:exit status 1
exit status 1
PS D:\Deployment\sql>
And at the sql terminal I got:
2022-07-10 15:10:42.496 +03 [26764] LOG: could not receive data from client: An existing connection was forcibly closed by the remote host.
I checked the trigger at PGAdmin, and looks it is working fine (I think):
I'm at Windows 11 x64 machine, created the below table:
Then created the below trigger function:
With the following trigger:
And trying to listen to the notification in my go code as:
But the app is exit as:
And at the sql terminal I got:
I checked the trigger at PGAdmin, and looks it is working fine (I think):