pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
trigger output to a file
3+ messages / 3 participants
[nested] [flat]

* trigger output to a file
@ 2001-03-23 07:15  pgsql-sql <pgsql-sql@fc.emc.com.ph>
  0 siblings, 1 reply; 3+ messages in thread

From: pgsql-sql @ 2001-03-23 07:15 UTC (permalink / raw)
  To: pgsql-sql

Hello Everyone,

Here's my simple question.

I just want to know/get the recent changes made to a table.
Deeper? I wanted the Postgresql server to *publish* every
changes made to a table (similar to replication, incremental transfer,
etc.).
What is the best way to go about it?

My idea is to create a trigger for every insert/update that will
select the new/updated record and output it to a file. Or better
yet, I would like to output the actual sql query to file.
Is it possible to output the result of an sql query from a trigger
to a file? How?

I would appreciate any answer. Thank you very much.
Sherwin




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

* Re: trigger output to a file
@ 2001-03-23 18:02  Jan Wieck <JanWieck@Yahoo.com>
  parent: pgsql-sql <pgsql-sql@fc.emc.com.ph>
  0 siblings, 1 reply; 3+ messages in thread

From: Jan Wieck @ 2001-03-23 18:02 UTC (permalink / raw)
  To: pgsql-sql <pgsql-sql@fc.emc.com.ph>; +Cc: pgsql-sql

pgsql-sql wrote:
> Hello Everyone,
>
> Here's my simple question.
>
> I just want to know/get the recent changes made to a table.
> Deeper? I wanted the Postgresql server to *publish* every
> changes made to a table (similar to replication, incremental transfer,
> etc.).
> What is the best way to go about it?
>
> My idea is to create a trigger for every insert/update that will
> select the new/updated record and output it to a file. Or better
> yet, I would like to output the actual sql query to file.
> Is it possible to output the result of an sql query from a trigger
> to a file? How?

    Putting the SQL query to a file would be my approach too.

    The  trigger approach lacks the capability to discard changes
    already logged in case of a  transaction  rollback.  Thus,  I
    wouldn't buy it.

    For the query string logging, alot more is required. Not only
    the queries themself are  needed,  you'd  need  to  serialize
    snapshot creation, log sequence allocations and the like. And
    the program rolling forward this kind  of  log  into  another
    database  needs  control mechanisms to tell the database that
    it's in this recovery mode and has to ask for those values in
    case it needs them.

    You might guess it already, I've been thinking about it for a
    year or so now. And I'm still not past  the  point  to  start
    implementing it.

>
> I would appreciate any answer. Thank you very much.
> Sherwin
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl
>


--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com




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

* Re: trigger output to a file
@ 2001-03-23 19:53  Andrew Perrin <aperrin@socrates.berkeley.edu>
  parent: Jan Wieck <JanWieck@Yahoo.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Andrew Perrin @ 2001-03-23 19:53 UTC (permalink / raw)
  To: Jan Wieck <JanWieck@Yahoo.com>; +Cc: pgsql-sql <pgsql-sql@fc.emc.com.ph>; pgsql-sql

I haven't given this a lot of thought, so take it with a grain of
salt. But my feeling is that publishing such a detailed log isn't the most
effective way to do this sort of thing. How about, instead, changing the
structure of your database to keep "old" information?  Consider, for
example, a simple phone book. You might have the following fields:

id  firstname   lastname   phone   fax

just to keep things simple.  How about, instead, having two tables:
1.) Records, which ONLY has the id column; and
2.) Data, which has:

id   rev   firstname   lastname   phone   fax

you can get what you're looking for by simply JOINing Records and
Data. Then, when you want to "change" a record - say, for example, Andrew
Perrin moves from Berkeley to Chapel Hill, thereby changing phones from
510-xxx-xxxx to 919-xxx-xxxx - you actually *add* a new record, with a
higher rev, to Data.  So, before:

id: 0
rev: 1
firstname: Andrew
lastname: Perrin
phone: 510-xxx-xxxx
fax:

And after:

id: 0
rev: 1
firstname: Andrew
lastname: Perrin
phone: 510-xxx-xxxx
fax:

id: 0
rev: 2
firstname: Andrew
lastname: Perrin
phone: 919-xxx-xxxx
fax:


SELECTing the highest rev will give you current data; selecting everything
for id 0 sorted by rev will give you the change history.

Just a thought.

Andy Perrin

----------------------------------------------------------------------
Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology  
(Soon: Asst Professor of Sociology, U of North Carolina, Chapel Hill)
andrew_perrin@unc.edu - http://www.unc.edu/~aperrin

On Fri, 23 Mar 2001, Jan Wieck wrote:

> pgsql-sql wrote:
> > Hello Everyone,
> >
> > Here's my simple question.
> >
> > I just want to know/get the recent changes made to a table.
> > Deeper? I wanted the Postgresql server to *publish* every
> > changes made to a table (similar to replication, incremental transfer,
> > etc.).
> > What is the best way to go about it?
> >
> > My idea is to create a trigger for every insert/update that will
> > select the new/updated record and output it to a file. Or better
> > yet, I would like to output the actual sql query to file.
> > Is it possible to output the result of an sql query from a trigger
> > to a file? How?
> 
>     Putting the SQL query to a file would be my approach too.
> 
>     The  trigger approach lacks the capability to discard changes
>     already logged in case of a  transaction  rollback.  Thus,  I
>     wouldn't buy it.
> 
>     For the query string logging, alot more is required. Not only
>     the queries themself are  needed,  you'd  need  to  serialize
>     snapshot creation, log sequence allocations and the like. And
>     the program rolling forward this kind  of  log  into  another
>     database  needs  control mechanisms to tell the database that
>     it's in this recovery mode and has to ask for those values in
>     case it needs them.
> 
>     You might guess it already, I've been thinking about it for a
>     year or so now. And I'm still not past  the  point  to  start
>     implementing it.
> 
> >
> > I would appreciate any answer. Thank you very much.
> > Sherwin
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://www.postgresql.org/search.mpl
> >
> 
> 
> --
> 
> #======================================================================#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me.                                  #
> #================================================== JanWieck@Yahoo.com #
> 
> 
> 
> _________________________________________________________
> Do You Yahoo!?
> Get your free @yahoo.com address at http://mail.yahoo.com
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html
> 




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


end of thread, other threads:[~2001-03-23 19:53 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-23 07:15 trigger output to a file pgsql-sql <pgsql-sql@fc.emc.com.ph>
2001-03-23 18:02 ` Jan Wieck <JanWieck@Yahoo.com>
2001-03-23 19:53   ` Andrew Perrin <aperrin@socrates.berkeley.edu>

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