'tail -f' database table

Question

Is it possible to effectively bind a database table so that when a new row is added, the application is immediately notified of a new row? Any database can be used.

Answer 1

Use the ON INSERT trigger.

you will need to check the specifics of calling external applications with the values ​​contained in the inserted record, or you will write your "application" as an SQL procedure and run it in the database.

it looks like you will want to refresh the database altogether before drawing yourself into a corner as the command line approaches.

Answer 2

• Yes, if the database is a flat text file, and the additions end at the end.
• Yes, if the database supports this function in any other way; Check the appropriate manual.
• Otherwise, no. Databases are usually binary files.

Answer 3

I'm not sure, but this may work for databases of primitive / flat files, but as far as I understand (and I might be wrong), modern database files are encrypted. Therefore, reading a newly added line will not work with this command.

Answer 4

I would suggest that most databases allow write triggers, and you can have a script that starts a record that tells you what happened. I do not know what information will be available, as this will depend on a separate database.

Answer 5

There are several options here, some of which are noted:

• Periodically polling for new lines. With MVCC working, it is possible to skip a line if there were two INSERTS in the middle of the transaction at the last request.
• Define a trigger function that will perform some actions for each insert. (In Postgres, you can invoke the NOTIFY command, which can run other processes. LISTEN.) You can combine a trigger with an entry in the unpublished_row_ids table to ensure that your tail process does not miss anything. (The tail process then removes the identifiers from the unpublished_row_ids table when they process them.)
• Interact with database replication functionality if it provides any. This should ensure that no lines are skipped.

I talked in detail about how to make all of these options using Postgres http://btubbs.com/streaming-updates-from-postgres.html .

Answer 6

tail Linux seems to use inotify to indicate when a file changes — it probably uses similar file system frameworks on other operating systems. Therefore, it detects file changes.

However, tail makes a call to fstat() after each change detected and does not output anything if the file size does not increase. Modern database systems use random access to files and reuse of database pages, so it is very possible that the inserted row will not lead to a resizing of the support file.

You are better off using inotify (or the like) directly and even better if you use DB triggers or some kind of mechanism your DBMS offers to view database updates, since not all file updates are necessarily line inserts.

Answer 7

I was in the midst of posting the exact same answer as glowcoder, plus another idea:

A low-tech way to do this is to have a timestamp field, and the program starts the query every n minutes, looking for records where the timestamp is greater than the last run. The same concept can be made by storing the last key if you use a sequence or even add a processed logical field.

Answer 8

With oracle, you can select psuedo-column named "rowid", which gives a unique row identifier in the table, and rowid - ordinal ... new rows get assigned rowids that are larger than any existing rowid.

So first select max (rowid) from table_name

I assume that one reason for the question raised is that there are many, many rows in the table in the table ... so this first step will burden db a bit and take some time.

Then select * from table_name, where rowid> 'whatever_that_rowid_string_was'

you still have to run the request periodically, but now it's just a quick and inexpensive request