Building a notification system.

On Sunday, July 15, 2018, Anto Aravinth <anto.aravinth.cse@gmail.com> wrote:

I'm not sure, how to get started with this. Read about NOTIFY:
https://www.postgresql.org/docs/current/static/sql-notify.html

Not sure that fits my use case, thanks for your help in this.

It doesn't. You need to record time stamps for the relevant events and use
them to decide what is new and what is old.

David J.

On Sunday, July 15, 2018, David G. Johnston <david.g.johnston@gmail.com>
wrote:

On Sunday, July 15, 2018, Anto Aravinth <anto.aravinth.cse@gmail.com>
wrote:

I'm not sure, how to get started with this. Read about NOTIFY:
https://www.postgresql.org/docs/current/static/sql-notify.html

Not sure that fits my use case, thanks for your help in this.

It doesn't. You need to record time stamps for the relevant events and
use them to decide what is new and what is old.

Or, to avoid time skew issues, an equivalent protocol using serial
(big)integers (see create sequence)

David J.

So just maintaining a notification table along with user id would do I
believe. Just notifies the user if he/she has the userid in notification
table and its not in read state.

On Sun, Jul 15, 2018 at 10:23 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Sun, Jul 15, 2018, 5:30 AM Anto Aravinth, <anto.aravinth.cse@gmail.com>
wrote:

Hello Everyone,

I'm playing around with postgresql with SO datasets. In the process, I
have dumped 60M questions data onto the postgresql. I'm trying to build a
notification system on top of this, so that, when a user edits a question,
I need to show a notification to the user when he/she logs in next time. So
literally, trying to create the clone of SO (with very minimal feature)

I'm not sure, how to get started with this. Read about NOTIFY:
https://www.postgresql.org/docs/current/static/sql-notify.html

Not sure that fits my use case, thanks for your help in this.

I do not think that the NOTIFY command implemented in postgreSQL is
terribly likely to be useful for your application.

That command is useful for distribution of notifications to applications
that are continuously connected to the database, which is not likely true
for web app connections, particularly in view of your comment about
notifying users "when they log in next time."

Instead, you need a table that captures a log of undelivered notifications
of changes to questions. It should capture useful attributes such as..
- Who made the change
- Who is to be notified
- The time of the change
- Perhaps the nature of the change, which could be pretty open ended
- A reference to the question, e.g. its ID
- Some lifecycle attribute such as "viewed-on" or "acknowledged-on"

When a user logs in, it should be easy to query that table, providing the
list of unexamined updates.

Perhaps entries may be removed as soon as they are viewed, or you may need
a more sophisticated lifecycle so they are only removed after some express
indication that the change has been fully acknowledged.

On Mon, Jul 16, 2018 at 8:02 AM, Christopher Browne <cbbrowne@gmail.com>
wrote:

On Sun, Jul 15, 2018, 5:30 AM Anto Aravinth, <anto.aravinth.cse@gmail.com>
wrote:

Hello Everyone,

I'm playing around with postgresql with SO datasets. In the process, I
have dumped 60M questions data onto the postgresql. I'm trying to build a
notification system on top of this, so that, when a user edits a question,
I need to show a notification to the user when he/she logs in next time. So
literally, trying to create the clone of SO (with very minimal feature)

I'm not sure, how to get started with this. Read about NOTIFY:
https://www.postgresql.org/docs/current/static/sql-notify.html

Not sure that fits my use case, thanks for your help in this.

I do not think that the NOTIFY command implemented in postgreSQL is
terribly likely to be useful for your application.

That command is useful for distribution of notifications to applications
that are continuously connected to the database, which is not likely true
for web app connections, particularly in view of your comment about
notifying users "when they log in next time."

Instead, you need a table that captures a log of undelivered notifications
of changes to questions. It should capture useful attributes such as..
- Who made the change
- Who is to be notified
- The time of the change
- Perhaps the nature of the change, which could be pretty open ended
- A reference to the question, e.g. its ID
- Some lifecycle attribute such as "viewed-on" or "acknowledged-on"

When a user logs in, it should be easy to query that table, providing the
list of unexamined updates.

Perhaps entries may be removed as soon as they are viewed, or you may need
a more sophisticated lifecycle so they are only removed after some express
indication that the change has been fully acknowledged.

Thanks that helps a lot.

On 15 July 2018 at 23:25, Anto Aravinth <anto.aravinth.cse@gmail.com> wrote:

On Mon, Jul 16, 2018 at 8:02 AM, Christopher Browne <cbbrowne@gmail.com>
wrote:

On Sun, Jul 15, 2018, 5:30 AM Anto Aravinth, <anto.aravinth.cse@gmail.com>
wrote:

Hello Everyone,

I'm playing around with postgresql with SO datasets. In the process, I
have dumped 60M questions data onto the postgresql. I'm trying to build a
notification system on top of this, so that, when a user edits a question,
I need to show a notification to the user when he/she logs in next time. So
literally, trying to create the clone of SO (with very minimal feature)

I'm not sure, how to get started with this. Read about NOTIFY:
https://www.postgresql.org/docs/current/static/sql-notify.html

Not sure that fits my use case, thanks for your help in this.

I do not think that the NOTIFY command implemented in postgreSQL is
terribly likely to be useful for your application.

That command is useful for distribution of notifications to applications
that are continuously connected to the database, which is not likely true
for web app connections, particularly in view of your comment about
notifying users "when they log in next time."

Instead, you need a table that captures a log of undelivered
notifications of changes to questions. It should capture useful attributes
such as..
- Who made the change
- Who is to be notified
- The time of the change
- Perhaps the nature of the change, which could be pretty open ended
- A reference to the question, e.g. its ID
- Some lifecycle attribute such as "viewed-on" or "acknowledged-on"

When a user logs in, it should be easy to query that table, providing the
list of unexamined updates.

Also look at Logical Decoding for implementing Change Data Capture

Dave Cramer

davec@postgresintl.com
www.postgresintl.com