Re: Pulling data from Postgres DB table for every 5 seconds.

On Wed, Jan 9, 2019 at 12:26 PM Rob Sargent <robjsargent(at)gmail(dot)com> wrote:

>
>
> On Jan 9, 2019, at 11:11 AM, github kran <githubkran(at)gmail(dot)com> wrote:
>
> Rob - It's a Java based application. We dont have triggers yet on the
> table and is trigger a only option in 9.6 version ?.
>
> On Wed, Jan 9, 2019 at 12:01 PM Rob Sargent <robjsargent(at)gmail(dot)com> wrote:
>
>>
>> On 1/9/19 10:21 AM, github kran wrote:
>>
>> Thanks for your reply Rob. Reading the below documentation link says the
>> EVENT trigger is only supported for DDL commands. Is it not correct ?.
>>
>> *1) https://www.postgresql.org/docs/9.6/event-trigger-definition.html
>> <https://www.postgresql.org/docs/9.6/event-trigger-definition.html> *
>> (An event trigger fires whenever the event with which it is associated
>> occurs in the database in which it is defined. Currently, the only
>> supported events are ddl_command_start, ddl_command_end, table_rewrite
>> and sql_drop. Support for additional events may be added in future
>> releases.).
>> 2) Doesnt the trigger slow down inserts/update we are doing to the table
>> ?. Does it slow down if we are reading the data using the API when we have
>> a trigger in place ?.
>>
>>
>> Ah, right you are. Are triggers off the table? You would want to write
>> the trigger function in some (trusted?) language with access to the outside
>>
>
> (Custom here is to “bottom post”)
>
> Have you tried triggers and found them to have too much impact on total
> system? I can’t see them being more expensive than looking for changes
> every 5 seconds. If your hardware can scan 1T that quickly then I suspect
> your trigger will not be noticed. I would have the trigger write to queue
> and have something else using the queue to talk to IOT piece.
>
> Failing that, perhaps your java (server-side?) app making the changes can
> be taught to emit the necessary details to IOT-thingy?
>

*Sure will try with a trigger and send data to a Queue and gather data for
every 5 seconds reading off from the queue and send to IOT topic. Already
we have a queue where every message inserted or updated on the table is
sent to a queue but **that is lot of data we are gathering. We want to
rather minimize collecting data from DB.*