pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Knowing new item in table...
8+ messages / 8 participants
[nested] [flat]

* Knowing new item in table...
@ 2001-08-03 08:26 macky <macky@edsamail.com>
  2001-08-03 08:56 ` Re: Knowing new item in table... Jason Wong <postgresql@gremlins.com.hk>
  2001-08-03 09:54 ` Re: Knowing new item in table... Bryan Buchanan <bryanb@webbtide.com.au>
  2001-08-03 10:03 ` RE: Knowing new item in table... tamsin <tg_mail@bryncadfan.co.uk>
  2001-08-03 10:05 ` Re: Knowing new item in table... Christopher Sawtell <csawtell@paradise.net.nz>
  2001-08-03 10:27 ` Re: Knowing new item in table... Ken Corey <ken.corey@atomic-interactive.com>
  2001-08-03 17:42 ` Re: Knowing new item in table... Jason Earl <jdearl@yahoo.com>
  0 siblings, 6 replies; 8+ messages in thread

From: macky @ 2001-08-03 08:26 UTC (permalink / raw)
  To: pgsql-sql; pgsql-novice@postgresql.org

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...

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...


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


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







^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Knowing new item in table...
  2001-08-03 08:26 Knowing new item in table... macky <macky@edsamail.com>
@ 2001-08-03 08:56 ` Jason Wong <postgresql@gremlins.com.hk>
  5 siblings, 0 replies; 8+ messages in thread

From: Jason Wong @ 2001-08-03 08:56 UTC (permalink / raw)
  To: macky <macky@edsamail.com>; pgsql-sql; pgsql-novice@postgresql.org

The best way to do this is probably have an auto-increment field in your
table. When your script runs it checks and stores the highest value in that
auto-increment field and compares with the value it had the previous time it
was run. It then returns all records greater than the previous value of the
auto-increment field.

regards
--
Jason Wong
Gremlins Associates

----- Original Message -----
From: macky <macky@edsamail.com>
To: <pgsql-sql@postgresql.org>; <pgsql-novice@postgresql.org>
Sent: Friday, August 03, 2001 4:26 PM
Subject: [NOVICE] Knowing new item in table...


> 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...
>
> 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...
>
>
> is there an internal utility that i can use in postgres that can tell me
> that this rows a new commers... hehehe....
>
>
> 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
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Knowing new item in table...
  2001-08-03 08:26 Knowing new item in table... macky <macky@edsamail.com>
@ 2001-08-03 09:54 ` Bryan Buchanan <bryanb@webbtide.com.au>
  5 siblings, 0 replies; 8+ messages in thread

From: Bryan Buchanan @ 2001-08-03 09:54 UTC (permalink / raw)
  To: macky <macky@edsamail.com>; +Cc: pgsql-sql; pgsql-novice@postgresql.org

macky 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...
>
> 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...
>
> is there an internal utility that i can use in postgres that can tell me
> that this rows a new commers... hehehe....
>
> 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

You could have a timestamp field with a default value of 'now' defined,
and have your script select all records from the table with a time after
5 minutes ago.

Bryan




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* RE: Knowing new item in table...
  2001-08-03 08:26 Knowing new item in table... macky <macky@edsamail.com>
@ 2001-08-03 10:03 ` tamsin <tg_mail@bryncadfan.co.uk>
  5 siblings, 0 replies; 8+ messages in thread

From: tamsin @ 2001-08-03 10:03 UTC (permalink / raw)
  To: pgsql-sql; pgsql-novice@postgresql.org

what kind of processing are you doing in the script?
is there a reason why you are not using a trigger or rule on the table that
is executed straight away when a new row is added?
tamsin



> -----Original Message-----
> From: pgsql-novice-owner@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of macky
> Sent: 03 August 2001 09:26
> To: pgsql-sql@postgresql.org; pgsql-novice@postgresql.org
> Subject: [NOVICE] Knowing new item in table...
>
>
> 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...
>
> 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...
>
>
> is there an internal utility that i can use in postgres that can tell me
> that this rows a new commers... hehehe....
>
>
> 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
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Knowing new item in table...
  2001-08-03 08:26 Knowing new item in table... macky <macky@edsamail.com>
@ 2001-08-03 10:05 ` Christopher Sawtell <csawtell@paradise.net.nz>
  5 siblings, 0 replies; 8+ messages in thread

From: Christopher Sawtell @ 2001-08-03 10:05 UTC (permalink / raw)
  To: macky <macky@edsamail.com>; pgsql-sql; pgsql-novice@postgresql.org

On Fri, 03 Aug 2001 20:26, macky 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...
>
> 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...
>
>
> is there an internal utility that i can use in postgres that can tell me
> that this rows a new commers... hehehe....
>
>
> if someone has other ideas on how to deal with this speak out...

Here's mine:

Make sure the tables you wish to check have an id field of type SERIAL.
Then check the last entry number in the associated sequence when you wish to 
check for new entries.

That will tell you if there are any new entries.


-- 
Sincerely etc.,

 NAME       Christopher Sawtell
 CELL PHONE 021 257 4451
 ICQ UIN    45863470
 EMAIL      csawtell @ paradise . net . nz
 CNOTES     ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz

 -> Please refrain from using HTML or WORD attachments in e-mails to me <-




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Knowing new item in table...
  2001-08-03 08:26 Knowing new item in table... macky <macky@edsamail.com>
@ 2001-08-03 10:27 ` Ken Corey <ken.corey@atomic-interactive.com>
  5 siblings, 0 replies; 8+ messages in thread

From: Ken Corey @ 2001-08-03 10:27 UTC (permalink / raw)
  To: macky <macky@edsamail.com>; pgsql-sql; pgsql-novice@postgresql.org

On Friday 03 August 2001 09:26, macky 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...
>
> 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...

Isn't this a perfect example of when a notification and listen setup would be 
helpful (assuming that a trigger isn't acceptable)?

-Ken



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Knowing new item in table...
  2001-08-03 08:26 Knowing new item in table... macky <macky@edsamail.com>
@ 2001-08-03 17:42 ` Jason Earl <jdearl@yahoo.com>
  2001-08-04 00:25   ` Re: Knowing new item in table... Danny Aldham <danny@lennon.postino.com>
  5 siblings, 1 reply; 8+ messages in thread

From: Jason Earl @ 2001-08-03 17:42 UTC (permalink / raw)
  To: macky <macky@edsamail.com>; pgsql-sql; pgsql-novice@postgresql.org


--- 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/



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Knowing new item in table...
  2001-08-03 08:26 Knowing new item in table... macky <macky@edsamail.com>
  2001-08-03 17:42 ` Re: Knowing new item in table... Jason Earl <jdearl@yahoo.com>
@ 2001-08-04 00:25   ` Danny Aldham <danny@lennon.postino.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Danny Aldham @ 2001-08-04 00:25 UTC (permalink / raw)
  To: jdearl@yahoo.com; +Cc: pgsql-novice@postgresql.org

> 
> 
> --- 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.
> 

create trigger upd_table before insert table for each row execute 

-- 
Danny Aldham     Providing Certified Internetworking Solutions to Business
www.postino.com  E-Mail, Web Servers, Web Databases, SQL PHP & Perl



^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2001-08-04 00:25 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-08-03 08:26 Knowing new item in table... macky <macky@edsamail.com>
2001-08-03 08:56 ` Jason Wong <postgresql@gremlins.com.hk>
2001-08-03 09:54 ` Bryan Buchanan <bryanb@webbtide.com.au>
2001-08-03 10:03 ` tamsin <tg_mail@bryncadfan.co.uk>
2001-08-03 10:05 ` Christopher Sawtell <csawtell@paradise.net.nz>
2001-08-03 10:27 ` Ken Corey <ken.corey@atomic-interactive.com>
2001-08-03 17:42 ` Jason Earl <jdearl@yahoo.com>
2001-08-04 00:25   ` Danny Aldham <danny@lennon.postino.com>

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