pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jason Earl <jdearl@yahoo.com>
To: macky <macky@edsamail.com>
To: pgsql-sql@postgresql.org
To: pgsql-novice@postgresql.org
Subject: Re: Knowing new item in table...
Date: Fri, 3 Aug 2001 10:42:31 -0700 (PDT)
Message-ID: <20010803174231.57921.qmail@web10006.mail.yahoo.com> (raw)
In-Reply-To: <006501c11bf5$f4b20750$5764a8c0@edsamail.com>


--- macky <macky@edsamail.com> wrote:
> im still in the dark when it comes to postgres.....
> is there a way i can
> know if there was a new entry on a table...

That's a pretty common problem.

> scenario...
> 
>   i have a script that runs every 5 minutes... that
> script checks if there's
> a new entry in that table... and if YES there's a
> new entry ill do some
> processing...

Probably the most straightforward thing to do is to
simply insert a timestamp in each row as it is added
to the database.  That way you can do a simple select
to figure out which rows have been added:

SELECT * FROM my_table WHERE insert_time > now() - '5
minutes'::interval

or alternatively

SELECT * FROM my_table WHERE insert_time > '2001-08-03
11:30'

The best part is that creating a column that includes
a timestamp automagically is fairly easy to do. 
Simply define your table like this:

CREATE TABLE foo (
insert_time     timestamp DEFAULT CURRENT_TIMESTAMP,
name            text
);

Once your table is created you simply insert into
table foo ignoring the insert_time column like so:

INSERT INTO foo (name) VALUES ('Jason');

and your timestamp automagically gets inserted:

processdata=> SELECT * FROM foo;
     insert_time        | name  
------------------------+-------
 2001-08-03 11:32:48-06 | Jason
(1 row)

Pretty neat, huh?

> is there an internal utility that i can use in
> postgres that can tell me
> that this rows a new commers... hehehe....

Nope, you have to come up with the logic yourself. 
However, PostgreSQL has all kinds of tools that are
really helpful.

> if someone has other ideas on how to deal with this
> speak out...
> 
> thanks in advance......
> 
> btw..
>    my idea is that that table will have an addtional
> column as reference
> lets say column "READ" 1 for yes 0 for NO
> 

That would work too, but it would be a lot harder. 
For example, you would have to first select all the
rows where READ is 0, do your processing, and then
update all of those rows to 1.  You would almost
certainly want to do all of this in a transaction so
that you could roll READ back to 0 if something went
wrong, and you would probably want to lock the table
to boot as you would have to worry about your
processing step taking more than 5 minutes.  If it
did, the second transaction would see the last 10
minutes of of inserts as being unread even though the
first transaction was still working on them.

I hope this is helpful,
Jason


__________________________________________________
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
http://phonecard.yahoo.com/



view thread (8+ messages)  latest in thread

Message-ID: <20010803174231.57921.qmail@web10006.mail.yahoo.com>
Permalink:  ../20010803174231.57921.qmail@web10006.mail.yahoo.com/
Also on:    postgresql.org/message-id/20010803174231.57921.qmail@web10006.mail.yahoo.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: jdearl@yahoo.com, macky@edsamail.com, pgsql-novice@postgresql.org
  Subject: Re: Knowing new item in table...
  In-Reply-To: <20010803174231.57921.qmail@web10006.mail.yahoo.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox