Re: [GENERAL] Triggers on foreign Postgres 9.3 tables in Postgres 9.4

[MattF]

Thank you Michael! I will let the admin know then!



--
View this message in context: 
http://postgresql.nabble.com/Triggers-on-foreign-Postgres-9-3-tables-in-Postgres-9-4-tp5839559p5839749.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Triggers on foreign Postgres 9.3 tables in Postgres 9.4

[Michael Paquier]

On Fri, Feb 27, 2015 at 2:13 AM, MattF  wrote:
> I've talked to the admin here and he said that upgrading to Postgres 9.4 is
> possible, however the only risk is for us on the warehouse side is that it
> is entirely possible that while the warehouse servers will be Postgres 9.4,
> the production servers will remain Postgres 9.3. I haven't found a good way
> to phrase this in googling it, but will I still be able to have triggers on
> the foreign 9.3 tables from our 9.4 warehouse?

Yes, it will work. postgres_fdw speaks the Postgres protocol and uses
libpq when requesting remote servers.
-- 
Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Triggers and scalability in high transaction tables.

[Tim Uckun]

I just want to make sure I understood correctly.

All the triggers are firing in a single thread assigned to the connection
and will be run serially no matter how many tables are firing triggers.

If this is correct then yes I guess I have to create a queue of some sort
and process them via an external process.  Thanks.

On Fri, Feb 27, 2015 at 11:12 AM, Jerry Sievers 
wrote:

> Tim Uckun  writes:
>
> > I want to write a trigger which runs semi-complicated code after each
> insert.  I have done some reading and from what I can gather this could
> cause problems because
> > after insert triggers "don't spill to the disk" and can cause queue
> problems.   Many people suggest LISTEN NOTIFY but that's not going to help
> me because my daemons
> > could be offline and I would lose records.
> >
> > I have two questions.
> >
> > There are some hints out there that it could be possible to do
> asynchronous triggers based on dblink but I haven't seen any documentation
> or examples of this.   Is
> > there a writeup someplace about this?
> >
> > Secondly I had the idea of "partitioning" the trigger processing by
> > partitioning the table and then putting a trigger on each child
> > table.  This way theoretically I could be running the triggers
> > in parallel.  Is my presumption correct here?  If I only
> > have one table the trigger calls get queued up one at a time but if I
> > partition my table into N tables I am running N triggers
> > simultaneously?
> >
> False on both counts.
>
> Nothing to prevent concurrent firing of same trigger on same table given
> multi session concurrent insert.
>
> Nothing to prevent contention related single-threading of any triggers
> firing for whatever reason if  the code they are running  will result in
> lock contention with other sessions.
>
> Just like 2 or more sessions trying to update the same row,  you are
> going to single thread around such an operation like it or not.
>
> You need to tell us a lot more about your problem and what the triggers
> do.
>
>
> > Thanks.
> >
>
> --
> Jerry Sievers
> Postgres DBA/Development Consulting
> e: postgres.consult...@comcast.net
> p: 312.241.7800
>

Re: [GENERAL] Triggers and scalability in high transaction tables.

[John R Pierce]

On 2/26/2015 2:03 PM, Merlin Moncure wrote:

I would strongly advise you not to put complex processing in triggers
if at all possible.  Instead have the insert operation write a record
into another table which forms a queue of work to do.  That queue can
then be walked by another process which accumulates the work and takes
appropriate action (and, since you are out of the context of the
operation at hand, can be threaded etc).


I 2nd this emotion...  that's exactly what we do with our complex 
background processing.




--
john r pierce  37N 122W
somewhere on the middle of the left coast



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Triggers and scalability in high transaction tables.

[Jerry Sievers]

Tim Uckun  writes:

> I want to write a trigger which runs semi-complicated code after each 
> insert.  I have done some reading and from what I can gather this could 
> cause problems because
> after insert triggers "don't spill to the disk" and can cause queue 
> problems.   Many people suggest LISTEN NOTIFY but that's not going to help 
> me because my daemons
> could be offline and I would lose records. 
>
> I have two questions.
>
> There are some hints out there that it could be possible to do asynchronous 
> triggers based on dblink but I haven't seen any documentation or examples of 
> this.   Is
> there a writeup someplace about this?
>
> Secondly I had the idea of "partitioning" the trigger processing by
> partitioning the table and then putting a trigger on each child
> table.  This way theoretically I could be running the triggers
> in parallel.  Is my presumption correct here?  If I only
> have one table the trigger calls get queued up one at a time but if I
> partition my table into N tables I am running N triggers
> simultaneously?
>
False on both counts.

Nothing to prevent concurrent firing of same trigger on same table given
multi session concurrent insert.

Nothing to prevent contention related single-threading of any triggers
firing for whatever reason if  the code they are running  will result in
lock contention with other sessions.

Just like 2 or more sessions trying to update the same row,  you are
going to single thread around such an operation like it or not.

You need to tell us a lot more about your problem and what the triggers
do.


> Thanks.
>

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 312.241.7800


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Triggers and scalability in high transaction tables.

[Merlin Moncure]

On Thu, Feb 26, 2015 at 3:54 PM, Tim Uckun  wrote:
> I want to write a trigger which runs semi-complicated code after each
> insert.  I have done some reading and from what I can gather this could
> cause problems because after insert triggers "don't spill to the disk" and
> can cause queue problems.   Many people suggest LISTEN NOTIFY but that's not
> going to help me because my daemons could be offline and I would lose
> records.
>
> I have two questions.
>
> There are some hints out there that it could be possible to do asynchronous
> triggers based on dblink but I haven't seen any documentation or examples of
> this.   Is there a writeup someplace about this?
>
> Secondly I had the idea of "partitioning" the trigger processing by
> partitioning the table and then putting a trigger on each child table.  This
> way theoretically I could be running the triggers in parallel.  Is my
> presumption correct here?  If I only have one table the trigger calls get
> queued up one at a time but if I partition my table into N tables I am
> running N triggers simultaneously?

I would strongly advise you not to put complex processing in triggers
if at all possible.  Instead have the insert operation write a record
into another table which forms a queue of work to do.  That queue can
then be walked by another process which accumulates the work and takes
appropriate action (and, since you are out of the context of the
operation at hand, can be threaded etc).

merlin


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Triggers Operations

[Adrian Klaver]

On 02/25/2015 06:14 PM, Emanuel Araújo wrote:

Hi,

I have an application that replicates data from an Oracle database for
postgresql. The flow goes as follows:

oracle.table1 -> AppSincronizador -> postgresql.table1 -> Trigger (upd,
ins, del) -> postgresql.table2

I'm having situations where the data volume is large that the changes
that should be in the final table are not found, getting the tables in
postgresql nosync. Well, the application makes a single transaction and
makes commits every 1000 records.


How large?



It is as if the triggers disabled, when manually do the operation is
performed. Is there a BUG or situation where the postgresql disable
these triggers?


Hard to say without seeing the trigger definition or the code it is calling.




So Version: CentOS 6.5
PostgreSQL 9.3.5
Oracle: 11G

I found this POST that explain once situation.

AFTER triggers are more expensive than BEFORE triggers because They must
be queued up Until the statement finishes doing its work, Then executed.
They are not spilled to disk if the queue gets big (at least in 9.4 and
below, may change in future) are huge queues AFTER trigger can cause
memory available to overrun, Resulting in the statement aborting.

Link:
http://dba.stackexchange.com/questions/88761/scaling-postgresql-triggers

PS. Right now I'm not interested in the performance, as this take care
later, but the question that there are random operations that do not
complete for the final table.

Thanks!

--
*Atenciosamente,

Emanuel Araújo*
*/Linux Certified, DBA PostgreSQL
/*



--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Triggers

[Thomas Kellerer]

Thomas Kellerer, 05.07.2013 13:46:
> Postgres 9.3 will add "event triggers", but they can only be written in SQL 

That should have been: "only C and procedural languages like PL/pgSQL"



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Triggers

[Thomas Kellerer]

itishree sukla, 05.07.2013 10:29:
> Hello Every one,
> 
> Is Postgresql providing triggers on DB level, schema level ( in same DB)?
> 
You are probably referring to "DDL" triggers and similar things (a trigger when 
a table is created or dropped, a user logs in and so on).

The answer is no as far as I know. 

Postgres 9.3 will add "event triggers", but they can only be written in SQL 






-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Triggers

[itishree sukla]

I didn't get you, you mean  trigger is possible on schema level? if yes can
you give example.

Thank you


On Fri, Jul 5, 2013 at 3:56 PM, Jov  wrote:

> the later ，in same db
>
> jov
> 在 2013-7-5 下午4:32，"itishree sukla" 写道：
>
> Hello Every one,
>>
>> Is Postgresql providing triggers on DB level, schema level ( in same DB)?
>>
>>
>> Regards,
>> Itishree
>>
>

Re: [GENERAL] Triggers

[Jov]

the later ，in same db

jov
在 2013-7-5 下午4:32，"itishree sukla" 写道：

> Hello Every one,
>
> Is Postgresql providing triggers on DB level, schema level ( in same DB)?
>
>
> Regards,
> Itishree
>

Re: [GENERAL] Triggers NOT running as table owner

[Sergey Konoplev]

On Thu, Jun 27, 2013 at 4:58 AM, Sandro Santilli  wrote:
> According to release notes of 8.3.18 (yeah, old docs)
> a trigger runs with the the table owner permission.
>
> This is the only document I found about this matter:
> http://www.postgresql.org/docs/8.3/static/release-8-3-18.html
>
>
>  Require execute permission on the trigger function for CREATE TRIGGER 
> (Robert Haas)
>
>  This missing check could allow another user to execute a trigger
>  function with forged input data, by installing it on a table he
>  owns. This is only of significance for trigger functions marked
>  SECURITY DEFINER, since otherwise trigger functions run as the table
>  owner anyway. (CVE-2012-0866)
>
> But, while I'd need this to be true, I can't confirm this is the case.
>
> Did I misinterpret the note above ?

Looks like you did. It means that the patch adds an execute permission
check on functions that are called by triggers. There was no such
check before the patch, so it was kind of a security hole, because
anyone could call the function by just using it in a trigger on ones
own table. So trigger functions that are marked with SECURITY DEFINER
could be used to access to the objects of their owners illegally.

>
> --strk;
>
>  http://strk.keybit.net
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

Profile: http://www.linkedin.com/in/grayhemp
Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979
Skype: gray-hemp
Jabber: gray...@gmail.com


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Triggers on Foreign Tables

[Adrian Klaver]

On 04/04/2013 04:28 PM, Robert Lefkowitz wrote:

Not sure if this is a feature request or a bug report.

I'm trying to use Foreign Tables for a variety of things and it is useful to 
have a foreign table which appears to be read/write.

Having set one up, I can select data from it.   However, I can't insert, update 
or delete.

No worries, thinks I.  I'll just create an   INSTEAD OF TRIGGER -- and handle 
the updates that way.

However, one can't create a TRIGGER (in PosgreSQL version 9.2) on a foreign 
table -- the error message is:  not a table or view.

My current workaround is to create a view which is defined as   CREATE VIEW 
viewOnForeignTable AS SELECT * FROM foreignTable

I can then create INSTEAD OF TRIGGERs on viewOnForeignTable, as well as SELECT 
from viewOnForeignTable -- so that becomes the canonical interface.

However, it seems like it should be just as acceptable to apply the INSTEAD OF 
TRIGGERs to the FOREIGN TABLE .

Should I consider this a bug (that I can't create aTRIGGER on a foreign table?) 
or a feature request (to allow creating TRIGGERs on foreign tables)?


FYI, 9.3 will have update/insert/delete depending on the capabilities of 
the underlying Foreign Data Wrapper:


http://www.depesz.com/2013/03/17/waiting-for-9-3-support-writable-foreign-tables/

Not sure if that fills your need?

You could file a feature request but new features are not added to minor 
releases, so you will not see it until a later major release. At a guess 
9.4+.




Thanks,

r0ml






--
Adrian Klaver
adrian.kla...@gmail.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Triggers operations and log_statement = all not working?

[Edson Richter]

Em 16/01/2013 13:56, Edson Richter escreveu:

Hi!

I'm debugging few triggers I wrote these days, and I can't see the 
triggers statements being logged.

Probably I'm doing something wrong.

I just enabled

log_statement = 'all'

In postgresql.conf, but I can see all statements issued by my 
application, but the statements in trigger don't show up.
Is there anything else I need to do to see all trigger statements, or 
my triggers are just not being called?


Thanks,


Don't get bored - I just discovered how to enable plsql debugging in 
PgAdmin!


Thanks,

Edson


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] triggers and FK cascades

[Vincent Veyron]

Le vendredi 18 mars 2011 à 08:20 +, Grzegorz Jaśkiewicz a écrit :
> There's a generic trigger that sends a signal to a process whenever
> changes are made (via listen/notify mechanism), but when FK cascade
> fires it will cause a mass amount of notifies to be send out and I
> want to avoid it.
> 

I have no personal experience with this, but citing an answer to a
similar question by Vibhor Kumar from a couple days ago :

use following command:
ALTER TABLE  DISABLE TRIGGER [ trigger_name | ALL | USER ]

-- 
Vincent Veyron
http://marica.fr/
Logiciel de gestion des sinistres pour le service juridique


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] triggers and FK cascades

[Grzegorz Jaśkiewicz]

There's a generic trigger that sends a signal to a process whenever
changes are made (via listen/notify mechanism), but when FK cascade
fires it will cause a mass amount of notifies to be send out and I
want to avoid it.


2011/3/18 David Johnston :
> Don't know if this would work but could you check to see if the corresponding 
> PK exists on A?
>
> It may also help to explain why you would want to do such a thing so that 
> someone may be able to provide an alternative solution as opposed to simply 
> responding to a generic feature question.
>
> David J.
>
> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Grzegorz Jaskiewicz
> Sent: Thursday, March 17, 2011 6:41 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] triggers and FK cascades
>
> Considering the following example.
> Tables A and B.
> Table A contains some data.
> Table B reefers to table A using FK with 'on delete cascade'. Table B has a 
> trigger on it, after delete per row
>
> Now, is there any way I can tell in the trigger on table B that it has been 
> called from a direct delete on that table, as oppose to the indirect (FK) 
> delete on table A?
>
> Trigger is PLpg/SQL or C function.
>
>
> --
> GJ
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make 
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>



-- 
GJ

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] triggers and FK cascades

[David Johnston]

Don't know if this would work but could you check to see if the corresponding 
PK exists on A?  

It may also help to explain why you would want to do such a thing so that 
someone may be able to provide an alternative solution as opposed to simply 
responding to a generic feature question.

David J.

-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Grzegorz Jaskiewicz
Sent: Thursday, March 17, 2011 6:41 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] triggers and FK cascades

Considering the following example.
Tables A and B.
Table A contains some data.
Table B reefers to table A using FK with 'on delete cascade'. Table B has a 
trigger on it, after delete per row

Now, is there any way I can tell in the trigger on table B that it has been 
called from a direct delete on that table, as oppose to the indirect (FK) 
delete on table A?

Trigger is PLpg/SQL or C function.


--
GJ

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make 
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Triggers and locking

[William Temperley]

On 21 September 2010 18:39, Alban Hertroys
 wrote:
> On 21 Sep 2010, at 16:13, William Temperley wrote:
>
>> Dear all,
>>
>> I have a single "source" table that is referenced by six
>> specialization tables, which include:
>> "journal_article"
>> "report"
>> 4 more
>>
>> e.g.:
>> """
>> update source set citation = get_report_citation(
>>    (select source from source where id = NEW.source_ptr_id),
>>    NEW
>> );
>> """
>
> Well, depending on how many rows are in source, updating them all can take a 
> while.
> Eventually those changes will have to go to disk, so it's probably pretty 
> much I/O-bound.
>
> I get the impression you're missing a WHERE clause on that UPDATE statement 
> though, or otherwise I can't understand why you'd want to update all 
> citations every time one source record changes.
>
>
> Alban Hertroys
>

Indeed you're right, I was just missing a WHERE clause. Failed at the
last hurdle there.

All works as intended now, thankyou.

Will Temperley

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Triggers and locking

[Alban Hertroys]

On 21 Sep 2010, at 16:13, William Temperley wrote:

> Dear all,
> 
> I have a single "source" table that is referenced by six
> specialization tables, which include:
> "journal_article"
> "report"
> 4 more
> 
> e.g.:
> """
> update source set citation = get_report_citation(
>(select source from source where id = NEW.source_ptr_id),
>NEW
> );
> """

Well, depending on how many rows are in source, updating them all can take a 
while.
Eventually those changes will have to go to disk, so it's probably pretty much 
I/O-bound.

I get the impression you're missing a WHERE clause on that UPDATE statement 
though, or otherwise I can't understand why you'd want to update all citations 
every time one source record changes.


Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4c98edf010251425489017!



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Triggers made with plpythonu performance issue

[Adrian Klaver]

On Friday 18 December 2009 11:00:33 am sabrina miller wrote:
> Hi everybody,
> My requirements was:
>  + Made a table charge to be partitioned by carrier and month
>  + summarize by charges
>  + summarize by users,
>  + each summarization must be by month and several others columns.
>
> 
>
> Doesn't sound like too much? As I say, im new and I didn't found any
> better. But an insert takes around 135ms in the worst case (create tables
> and insert rows) and about 85 ms in best case (only updates). There are
> something better?

If I am following this it means there is an average of 50ms extra overhead to 
do 
an INSERT on charges.charges then an UPDATE correct? If so you have to consider 
that an INSERT is actually doing quite a lot besides creating a new row in 
charges.charges. There is a time cost to querying the database for existence of 
objects , making decisions based on the result, creating new database objects 
and the populating those objects. The issue then becomes where you want to pay 
it? So the something better question then becomes where is the best place to 
incur that cost. If the 135ms worst case works and does not impede your process 
then it may be the best solution. Unfortunately there is not enough information 
to give a definitive answer.

>
> Thanks in advance, Sabrina



-- 
Adrian Klaver
akla...@comcast.net

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] triggers and execute...

[Alban Hertroys]

On Apr 29, 2009, at 4:20 AM, Scott Marlowe wrote:


Oh man, it just gets worse.  I really need a simple elegant solution
here, because if I try to build the query by hand null inputs make
life a nightmare.  I had built something like this:

q = 'insert into '||schem||'.page_access_'||part||' values (
   '||new.paid||',
   '''||new.timestamp||''',
   '||new.total_time||',
   '''||new.http_host||''',
   '''||new.php_self||''',
   '''||new.query_string||''',
   '''||new.remote_addr||''',
   '''||new.logged_in||''',
   '||new.uid||',
   '''||new.http_user_agent||''',
   '''||new.server_addr||''',
   '''||new.notes||'''
   )';
   execute q;

But if any of the fields referenced are null, the whole query string
is now null.  So the next step is to use coalesce to build a query
string?  That get insane very quickly.  There's got to be some quoting
trick or something to let me use new.*, please someone see this and
know what that trick is.



I think you could do this if you'd be using a PL-language that  
supported reflection (on the NEW objects' type in this case). I can't  
say I know which one does though, I've only been using PL/pgsql so  
far, but I'd guess PL/Python, PL/Perl or PL/Java should be able to do  
the trick. Or plain C.


AFAIK there's no way to dynamically list column names from a table- 
type variable like NEW in PL/pgsql, which is why the above probably  
can't be done any easier using PL/pgsql. It would be nice to be able  
to LOOP over a variable like that or some similar method (I guess a  
more relational approach where the columns would be available as a  
result set would be preferred), especially if it'd be similarly easy  
to inspect the name and type of each column.


Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,49f82a8c129742043099112!



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] triggers and execute...

[Scott Marlowe]

On Tue, Apr 28, 2009 at 10:46 PM, David Fetter  wrote:
> On Tue, Apr 28, 2009 at 08:20:34PM -0600, Scott Marlowe wrote:
>> On Mon, Apr 27, 2009 at 3:24 PM, Richard Broersma
>>  wrote:
>> > On Mon, Apr 27, 2009 at 1:32 PM, Scott Marlowe  
>> > wrote:
>> >> OK, I'm hitting a wall here.  I've written this trigger for partitioning:
>> >>
>> >> create or replace function page_access_insert_trigger ()
>> >> returns trigger as $$
>> >> DECLARE
>> >>        part text;
>> >>        q text;
>> >> BEGIN
>> >>        part = to_char(new."timestamp",'MMDD');
>> >>        q = 'insert into page_access_'||part||' values (new.*)';
>> >> ...
>> >>
>> >> When I create it and try to use it I get this error:
>> >> ERROR:  NEW used in query that is not in a rule
>> >> CONTEXT:  SQL statement "insert into page_access_20090427 values (new.*)"
>> >
>> > At this point I don't think that there is a way for this function to
>> > know the correct table type of new.* since page_access_... is still
>> > only a concatenated string.  There there a way to cast new.* to the
>> > correct table type as part of this insert statement?
>>
>> Oh man, it just gets worse.  I really need a simple elegant solution
>> here, because if I try to build the query by hand null inputs make
>> life a nightmare.  I had built something like this:
>>
>> q = 'insert into '||schem||'.page_access_'||part||' values (
>>                 '||new.paid||',
>>                 '''||new.timestamp||''',
>>                 '||new.total_time||',
>>                 '''||new.http_host||''',
>>                 '''||new.php_self||''',
>>                 '''||new.query_string||''',
>>                 '''||new.remote_addr||''',
>>                 '''||new.logged_in||''',
>>                 '||new.uid||',
>>                 '''||new.http_user_agent||''',
>>                 '''||new.server_addr||''',
>>                 '''||new.notes||'''
>>         )';
>>         execute q;
>>
>> But if any of the fields referenced are null, the whole query string
>> is now null.  So the next step is to use coalesce to build a query
>> string?  That get insane very quickly.  There's got to be some
>> quoting trick or something to let me use new.*, please someone see
>> this and know what that trick is.
>
> Well, you can add in piles of COALESCE, but that way madness lies.
>
> Instead, use dollar quoting, the appropriate quote_*() functions, and
> this:
>
> http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers

Thanks so much!  I'm off to read up on it.  Dollar quoting, quote()
and the wiki.  Thanks again.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] triggers and execute...

[Richard Broersma]

I wonder if it would be easier to perodically replace the entire
trigger function with one that inserts to the correct table using
CRON+SED rather than dynamically building SQL.  This might be a bad
idea however.  I'm just thinking outside the box.


-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] triggers and execute...

[Scott Marlowe]

On Wed, Apr 29, 2009 at 4:23 AM, Alban Hertroys
 wrote:
> On Apr 29, 2009, at 4:20 AM, Scott Marlowe wrote:
>
>> Oh man, it just gets worse.  I really need a simple elegant solution
>> here, because if I try to build the query by hand null inputs make
>> life a nightmare.  I had built something like this:
>>
>> q = 'insert into '||schem||'.page_access_'||part||' values (
>>               '||new.paid||',
>>               '''||new.timestamp||''',
>>               '||new.total_time||',
>>               '''||new.http_host||''',
>>               '''||new.php_self||''',
>>               '''||new.query_string||''',
>>               '''||new.remote_addr||''',
>>               '''||new.logged_in||''',
>>               '||new.uid||',
>>               '''||new.http_user_agent||''',
>>               '''||new.server_addr||''',
>>               '''||new.notes||'''
>>       )';
>>       execute q;
>>
>> But if any of the fields referenced are null, the whole query string
>> is now null.  So the next step is to use coalesce to build a query
>> string?  That get insane very quickly.  There's got to be some quoting
>> trick or something to let me use new.*, please someone see this and
>> know what that trick is.
>
>
> I think you could do this if you'd be using a PL-language that supported
> reflection (on the NEW objects' type in this case). I can't say I know which
> one does though, I've only been using PL/pgsql so far, but I'd guess
> PL/Python, PL/Perl or PL/Java should be able to do the trick. Or plain C.
>
> AFAIK there's no way to dynamically list column names from a table-type
> variable like NEW in PL/pgsql, which is why the above probably can't be done
> any easier using PL/pgsql. It would be nice to be able to LOOP over a
> variable like that or some similar method (I guess a more relational
> approach where the columns would be available as a result set would be
> preferred), especially if it'd be similarly easy to inspect the name and
> type of each column.

I'm really close to using coalesce to make this work, since I can't
get the referenced at
http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers to work.  If
there was some simple quoting trick to get the original (NEW.*) stuff
to work I'd be gold.

Either that or just implement this all in rules with a simple cron job
that creates the new table as needed a week or so in advnace.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] triggers and execute...

[Alvaro Herrera]

Scott Marlowe escribió:

> Oh man, it just gets worse.  I really need a simple elegant solution
> here, because if I try to build the query by hand null inputs make
> life a nightmare.  I had built something like this:
> 
> q = 'insert into '||schem||'.page_access_'||part||' values (
> '||new.paid||',
> '''||new.timestamp||''',
> '||new.total_time||',
> '''||new.http_host||''',
> '''||new.php_self||''',
> '''||new.query_string||''',
> '''||new.remote_addr||''',
> '''||new.logged_in||''',
> '||new.uid||',
> '''||new.http_user_agent||''',
> '''||new.server_addr||''',
> '''||new.notes||'''
> )';
> execute q;
> 
> But if any of the fields referenced are null, the whole query string
> is now null.  So the next step is to use coalesce to build a query
> string?  That get insane very quickly.  There's got to be some quoting
> trick or something to let me use new.*, please someone see this and
> know what that trick is.

Agreed, it is ugly.  I don't think there's a better way to do it though.

One thing you could try is getting the column names and types from the
catalogs to build the insert statement.  That way you don't have to list
each column separately, and you don't need to fiddle with whether each
value needs quotes or not.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] triggers and execute...

[David Fetter]

On Tue, Apr 28, 2009 at 08:20:34PM -0600, Scott Marlowe wrote:
> On Mon, Apr 27, 2009 at 3:24 PM, Richard Broersma
>  wrote:
> > On Mon, Apr 27, 2009 at 1:32 PM, Scott Marlowe  
> > wrote:
> >> OK, I'm hitting a wall here.  I've written this trigger for partitioning:
> >>
> >> create or replace function page_access_insert_trigger ()
> >> returns trigger as $$
> >> DECLARE
> >>        part text;
> >>        q text;
> >> BEGIN
> >>        part = to_char(new."timestamp",'MMDD');
> >>        q = 'insert into page_access_'||part||' values (new.*)';
> >> ...
> >>
> >> When I create it and try to use it I get this error:
> >> ERROR:  NEW used in query that is not in a rule
> >> CONTEXT:  SQL statement "insert into page_access_20090427 values (new.*)"
> >
> > At this point I don't think that there is a way for this function to
> > know the correct table type of new.* since page_access_... is still
> > only a concatenated string.  There there a way to cast new.* to the
> > correct table type as part of this insert statement?
> 
> Oh man, it just gets worse.  I really need a simple elegant solution
> here, because if I try to build the query by hand null inputs make
> life a nightmare.  I had built something like this:
> 
> q = 'insert into '||schem||'.page_access_'||part||' values (
> '||new.paid||',
> '''||new.timestamp||''',
> '||new.total_time||',
> '''||new.http_host||''',
> '''||new.php_self||''',
> '''||new.query_string||''',
> '''||new.remote_addr||''',
> '''||new.logged_in||''',
> '||new.uid||',
> '''||new.http_user_agent||''',
> '''||new.server_addr||''',
> '''||new.notes||'''
> )';
> execute q;
> 
> But if any of the fields referenced are null, the whole query string
> is now null.  So the next step is to use coalesce to build a query
> string?  That get insane very quickly.  There's got to be some
> quoting trick or something to let me use new.*, please someone see
> this and know what that trick is.

Well, you can add in piles of COALESCE, but that way madness lies.

Instead, use dollar quoting, the appropriate quote_*() functions, and
this:

http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] triggers and execute...

[Scott Marlowe]

On Tue, Apr 28, 2009 at 11:24 PM, Scott Marlowe  wrote:
> On Tue, Apr 28, 2009 at 10:46 PM, David Fetter  wrote:
>> On Tue, Apr 28, 2009 at 08:20:34PM -0600, Scott Marlowe wrote:
>>> On Mon, Apr 27, 2009 at 3:24 PM, Richard Broersma
>>>  wrote:
>>> > On Mon, Apr 27, 2009 at 1:32 PM, Scott Marlowe  
>>> > wrote:
>>> >> OK, I'm hitting a wall here.  I've written this trigger for partitioning:
>>> >>
>>> >> create or replace function page_access_insert_trigger ()
>>> >> returns trigger as $$
>>> >> DECLARE
>>> >>        part text;
>>> >>        q text;
>>> >> BEGIN
>>> >>        part = to_char(new."timestamp",'MMDD');
>>> >>        q = 'insert into page_access_'||part||' values (new.*)';
>>> >> ...
>>> >>
>>> >> When I create it and try to use it I get this error:
>>> >> ERROR:  NEW used in query that is not in a rule
>>> >> CONTEXT:  SQL statement "insert into page_access_20090427 values (new.*)"
>>> >
>>> > At this point I don't think that there is a way for this function to
>>> > know the correct table type of new.* since page_access_... is still
>>> > only a concatenated string.  There there a way to cast new.* to the
>>> > correct table type as part of this insert statement?
>>>
>>> Oh man, it just gets worse.  I really need a simple elegant solution
>>> here, because if I try to build the query by hand null inputs make
>>> life a nightmare.  I had built something like this:
>>>
>>> q = 'insert into '||schem||'.page_access_'||part||' values (
>>>                 '||new.paid||',
>>>                 '''||new.timestamp||''',
>>>                 '||new.total_time||',
>>>                 '''||new.http_host||''',
>>>                 '''||new.php_self||''',
>>>                 '''||new.query_string||''',
>>>                 '''||new.remote_addr||''',
>>>                 '''||new.logged_in||''',
>>>                 '||new.uid||',
>>>                 '''||new.http_user_agent||''',
>>>                 '''||new.server_addr||''',
>>>                 '''||new.notes||'''
>>>         )';
>>>         execute q;
>>>
>>> But if any of the fields referenced are null, the whole query string
>>> is now null.  So the next step is to use coalesce to build a query
>>> string?  That get insane very quickly.  There's got to be some
>>> quoting trick or something to let me use new.*, please someone see
>>> this and know what that trick is.
>>
>> Well, you can add in piles of COALESCE, but that way madness lies.
>>
>> Instead, use dollar quoting, the appropriate quote_*() functions, and
>> this:
>>
>> http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers
>
> Thanks so much!  I'm off to read up on it.  Dollar quoting, quote()
> and the wiki.  Thanks again.

OK, I wrote a quick test and it's not working.  I've tried a few
combinations here and there but nothing seems to kick it off.

create or replace function page_access_test ()
returns trigger as $$
DECLARE
var text;
BEGIN
EXECUTE 'SELECT (' ||
 quote_literal(NEW) || '::' || TG_RELID::regclass ||
 ').' || quote_ident(http_host)
 INTO var;
raise notice '%',var;
END;
$$ language plpgsql;

which generates the error:

ERROR:  column "http_host" does not exist

I'm pretty sure that column exists in the table.  Here's the line for
\d on page_access:

 http_host   | text

I've tried new.http_host, which when http_host='xyz' generates an
ERROR:  type "public.xyz" does not exist

It's late, I'll mess with this tomorrow.  This is really frustrating
me and I feel dirty if I resort to a cron job to create the new table.
 I've tested the basic time to do all the work on my laptop and the
code runs pretty fast there.  So checking to see if the table is there
doesn't seem a particularly expensive select.  It's on a small system
table that stays cached.  My laptop can run the main code loop with
inserts (and lying fsync of course) 1500 times per second.  Without
the check it can run 1700 a second.  We do a dozen a minute.  So
unless our application goes insane and starts inserting data a couple
thousand times faster it's a non-issue.

I want a simple, self sustaining solution that requires no cron jobs
to work.  If someone has a simple dynamic trigger example in any
scripting language like plpgsql, plperl or pltcl please post it.  I
don't want to maintain C triggers for this on a production server.  If
I can't get it working I'll implement the cron job.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] triggers and execute...

[Dimitri Fontaine]

On Monday 27 April 2009 22:32:22 Scott Marlowe wrote:
> OK, I'm hitting a wall here.  I've written this trigger for partitioning:
>
> create or replace function page_access_insert_trigger ()
> returns trigger as $$
> DECLARE
>   part text;
>   q text;
> BEGIN
>   part = to_char(new."timestamp",'MMDD');
>   q = 'insert into page_access_'||part||' values (new.*)';

What you want looks like this (thanks RhodiumToad):

 'INSERT INTO page_access_' || part ||
 'SELECT (' || quote_literal(textin(record_out(NEW))) || '::page_access).*;'

That's supposing you have a parent table named page_access, of course. And 
casting this way has drawbacks too (which I can't recall at this moment), but 
I've been using this live for maybe more than a year now without any problem.

> It works.  So, how am I supposed to run it with dynamic table names?

Hack your way around, partitioning is not yet there "for real"...
-- 
dim


signature.asc
Description: This is a digitally signed message part.

Re: [GENERAL] triggers and execute...

[Erik Jones]

On Apr 29, 2009, at 4:14 AM, Jasen Betts wrote:


On 2009-04-29, Scott Marlowe  wrote:

On Mon, Apr 27, 2009 at 3:24 PM, Richard Broersma
 wrote:
On Mon, Apr 27, 2009 at 1:32 PM, Scott Marlowe > wrote:
OK, I'm hitting a wall here.  I've written this trigger for  
partitioning:


create or replace function page_access_insert_trigger ()
returns trigger as $$
DECLARE
   part text;
   q text;
BEGIN
   part = to_char(new."timestamp",'MMDD');
   q = 'insert into page_access_'||part||' values (new.*)';
...

When I create it and try to use it I get this error:
ERROR:  NEW used in query that is not in a rule
CONTEXT:  SQL statement "insert into page_access_20090427 values  
(new.*)"


At this point I don't think that there is a way for this function to
know the correct table type of new.* since page_access_... is still
only a concatenated string.  There there a way to cast new.* to the
correct table type as part of this insert statement?


Oh man, it just gets worse.  I really need a simple elegant solution
here, because if I try to build the query by hand null inputs make
life a nightmare.  I had built something like this:


sounds like you want quote_literal() and/or coalesce()

EXECUTE 'insert into '|| quote_ident($1)  || ' (data) values (' ||
  coalesce(quote_literal( $2 ),'NULL');


I'm fairly certain that quote_literal doesn't work with null values,  
hence the inclusion of quote_nullable() in 8.4.  I ran into this same  
issue when working on a pet project called pg_partitioner (http://github.com/mage2k/pg_partitioner/tree/master 
).  Since 8.4 obviously wasn't available for that yet I ended writing  
my own quote_nullable(), pretty simple.


Scott,

I also couldn't come up with a dynamic way to use new.* so I ended up  
just writing out the attribute names in my partition triggers, as  
well.  In fact, you may want to take a look at pg_partitioner.  It  
needs some polish but most of the basic functionality is there.


Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k






--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] triggers and execute...

[Jasen Betts]

On 2009-04-29, Scott Marlowe  wrote:
> On Mon, Apr 27, 2009 at 3:24 PM, Richard Broersma
> wrote:
>> On Mon, Apr 27, 2009 at 1:32 PM, Scott Marlowe  
>> wrote:
>>> OK, I'm hitting a wall here.  I've written this trigger for partitioning:
>>>
>>> create or replace function page_access_insert_trigger ()
>>> returns trigger as $$
>>> DECLARE
>>>        part text;
>>>        q text;
>>> BEGIN
>>>        part = to_char(new."timestamp",'MMDD');
>>>        q = 'insert into page_access_'||part||' values (new.*)';
>>> ...
>>>
>>> When I create it and try to use it I get this error:
>>> ERROR:  NEW used in query that is not in a rule
>>> CONTEXT:  SQL statement "insert into page_access_20090427 values (new.*)"
>>
>> At this point I don't think that there is a way for this function to
>> know the correct table type of new.* since page_access_... is still
>> only a concatenated string.  There there a way to cast new.* to the
>> correct table type as part of this insert statement?
>
> Oh man, it just gets worse.  I really need a simple elegant solution
> here, because if I try to build the query by hand null inputs make
> life a nightmare.  I had built something like this:

sounds like you want quote_literal() and/or coalesce()

 EXECUTE 'insert into '|| quote_ident($1)  || ' (data) values (' || 
   coalesce(quote_literal( $2 ),'NULL');



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] triggers and execute...

[Scott Marlowe]

On Mon, Apr 27, 2009 at 3:24 PM, Richard Broersma
 wrote:
> On Mon, Apr 27, 2009 at 1:32 PM, Scott Marlowe  
> wrote:
>> OK, I'm hitting a wall here.  I've written this trigger for partitioning:
>>
>> create or replace function page_access_insert_trigger ()
>> returns trigger as $$
>> DECLARE
>>        part text;
>>        q text;
>> BEGIN
>>        part = to_char(new."timestamp",'MMDD');
>>        q = 'insert into page_access_'||part||' values (new.*)';
>> ...
>>
>> When I create it and try to use it I get this error:
>> ERROR:  NEW used in query that is not in a rule
>> CONTEXT:  SQL statement "insert into page_access_20090427 values (new.*)"
>
> At this point I don't think that there is a way for this function to
> know the correct table type of new.* since page_access_... is still
> only a concatenated string.  There there a way to cast new.* to the
> correct table type as part of this insert statement?

Oh man, it just gets worse.  I really need a simple elegant solution
here, because if I try to build the query by hand null inputs make
life a nightmare.  I had built something like this:

q = 'insert into '||schem||'.page_access_'||part||' values (
'||new.paid||',
'''||new.timestamp||''',
'||new.total_time||',
'''||new.http_host||''',
'''||new.php_self||''',
'''||new.query_string||''',
'''||new.remote_addr||''',
'''||new.logged_in||''',
'||new.uid||',
'''||new.http_user_agent||''',
'''||new.server_addr||''',
'''||new.notes||'''
)';
execute q;

But if any of the fields referenced are null, the whole query string
is now null.  So the next step is to use coalesce to build a query
string?  That get insane very quickly.  There's got to be some quoting
trick or something to let me use new.*, please someone see this and
know what that trick is.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] triggers and execute...

[Sam Mason]

On Mon, Apr 27, 2009 at 03:37:22PM -0600, Scott Marlowe wrote:
> On Mon, Apr 27, 2009 at 3:24 PM, Richard Broersma wrote:
> > At this point I don't think that there is a way for this function to
> > know the correct table type of new.* since page_access_... is still
> > only a concatenated string.  There there a way to cast new.* to the
> > correct table type as part of this insert statement?
> 
> I tried casting the new.*::page_access and that didn't work.  For now
> I'll carry on with the complete listing of everything.

In SQL I can do:

  PREPARE _p(parent) AS INSERT INTO subtable SELECT ($1).*;
  EXECUTE _p(new);
  DEALLOCATE _p;

however this seems to interact badly with the EXECUTE in plpgsql, not
sure how to work around that.

-- 
  Sam  http://samason.me.uk/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] triggers and execute...

[Scott Marlowe]

On Mon, Apr 27, 2009 at 3:24 PM, Richard Broersma
 wrote:
> On Mon, Apr 27, 2009 at 1:32 PM, Scott Marlowe  
> wrote:
>> OK, I'm hitting a wall here.  I've written this trigger for partitioning:
>>
>> create or replace function page_access_insert_trigger ()
>> returns trigger as $$
>> DECLARE
>>        part text;
>>        q text;
>> BEGIN
>>        part = to_char(new."timestamp",'MMDD');
>>        q = 'insert into page_access_'||part||' values (new.*)';
>> ...
>>
>> When I create it and try to use it I get this error:
>> ERROR:  NEW used in query that is not in a rule
>> CONTEXT:  SQL statement "insert into page_access_20090427 values (new.*)"
>
> At this point I don't think that there is a way for this function to
> know the correct table type of new.* since page_access_... is still
> only a concatenated string.  There there a way to cast new.* to the
> correct table type as part of this insert statement?

I tried casting the new.*::page_access and that didn't work.  For now
I'll carry on with the complete listing of everything.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] triggers and execute...

[Richard Broersma]

On Mon, Apr 27, 2009 at 1:32 PM, Scott Marlowe  wrote:
> OK, I'm hitting a wall here.  I've written this trigger for partitioning:
>
> create or replace function page_access_insert_trigger ()
> returns trigger as $$
> DECLARE
>        part text;
>        q text;
> BEGIN
>        part = to_char(new."timestamp",'MMDD');
>        q = 'insert into page_access_'||part||' values (new.*)';
> ...
>
> When I create it and try to use it I get this error:
> ERROR:  NEW used in query that is not in a rule
> CONTEXT:  SQL statement "insert into page_access_20090427 values (new.*)"

At this point I don't think that there is a way for this function to
know the correct table type of new.* since page_access_... is still
only a concatenated string.  There there a way to cast new.* to the
correct table type as part of this insert statement?

-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] triggers and execute...

[Scott Marlowe]

On Mon, Apr 27, 2009 at 2:32 PM, Scott Marlowe  wrote:
> OK, I'm hitting a wall here.  I've written this trigger for partitioning:
>
> create or replace function page_access_insert_trigger ()
> returns trigger as $$
> DECLARE
>        part text;
>        q text;
> BEGIN
>        part = to_char(new."timestamp",'MMDD');
>        q = 'insert into page_access_'||part||' values (new.*)';
>        execute q;
>        return null;
> END;
> $$ language plpgsql;
> drop trigger page_access_insert_trigger on page_access cascade;
> create trigger page_access_insert_trigger before insert or update on 
> page_access
>        for each row execute procedure page_access_insert_trigger();
>
>
> When I create it and try to use it I get this error:
> ERROR:  NEW used in query that is not in a rule
> CONTEXT:  SQL statement "insert into page_access_20090427 values (new.*)"
> PL/pgSQL function "page_access_insert_trigger" line 7 at EXECUTE statement

OK, answering my own post here, but not really satisfied with the
answer.  If I create the trigger this way:

create or replace function page_access_insert_trigger ()
returns trigger as $$
DECLARE
part text;
q text;
BEGIN
part = to_char(new."timestamp",'MMDD');
q = 'insert into page_access_'||part||' values (
'||new.paid||',
'''||new.timestamp||''',
'||new.total_time||',
'''||new.http_host||''',
'''||new.php_self||''',
'''||new.query_string||''',
'''||new.remote_addr||''',
'''||new.logged_in||''',
'||new.uid||',
'''||new.http_user_agent||''',
'''||new.server_addr||''',
'''||new.notes||'''
)';
execute q;
--  insert into page_access_20090427 values (new.*);
return null;
END;
$$ language plpgsql;

It now works.  I've tried a variety of constructs of new and || and '
and * and nothing easy like new.* seems to work.

Any suggestions greatly appreciated.  Til then, the explicitly named
fields seems to work well enough.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] triggers

[Yuriy Rusinov]

Hello  Gustavo !

> Gente, buenos dias.
> Como consulto lo triggers desde psql?
>
Unfortunately I don't know Spanish. As far as I understand your
question was about triggers in postgresql. Trigger is the function
calls automatically on operations insert-delete-update.

-- 
Best regards,
Sincerely yours,
Yuriy Rusinov.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] triggers problems whit function

[MOLINA BRAVO FELIPE DE JESUS]

El mié, 22-10-2008 a las 15:01 -0500, Ma. Cristina Peña C. escribió:
> I want to use a function in to a trigger 
> 
>  
> 
> This is my
> 
> CREATE FUNCTION "subradio"(integer) RETURNS integer AS 'select
> cast(count (claveubica) as integer ) from asradios where ubicacion
> =0;' LANGUAGE 'sql';
> 
>  
> 
> And my ttrigger is 
> 
> CREATE TRIGGER validaradios AFTER DELETE ON subestacion FOR EACH ROW
> EXECUTE PROCEDURE subradio(0);
> 
>  
> 
> But I got an error 
> 
> ERROR:  CreateTrigger: function subradio() does not exist
> 
>  
> 
> What can I do??

write your function in plpgsql

http://www.postgresql.org/docs/8.3/interactive/plpgsql-trigger.html




 

> 
>  
> 
>  
> 
> cid:image002.jpg@01C8F24F.E2D90230
> 
> Ing. María Cristina Peña C.
> 
> Analista Programador
> 
>  
> 
> Sensa Control Digital S.A. de C.V.
> 
> Dir. Av. Bravo #93 Ote.
> 
> Col. Centro.
> Torreón Coah.
> 
>  
> 
> Tel. Directo:  (871) 747 01 04
> 
> Conmutador: (871) 747 01 01 
> 
> Tel. Fax:(871) 747 01 90 
> 
> Correo Electrónico: [EMAIL PROTECTED]
> 
>  
> 
> 

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] triggers problems whit function

[Guillaume Lelarge]

Tom Lane a écrit :
> Guillaume Lelarge <[EMAIL PROTECTED]> writes:
>> Fernando Moreno a écrit :
>>> A trigger function must have a specific structure, it takes no arguments
> 
>> It can take arguments.
> 
> No, it can't.  At least not in the declaration.
> 

Oops, you're right. Arguments shouldn't be declared, but can be used in
the body of a PL/pgsql trigger function.

Sorry about this.


-- 
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.com

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] triggers problems whit function

[Tom Lane]

Guillaume Lelarge <[EMAIL PROTECTED]> writes:
> Fernando Moreno a écrit :
>> A trigger function must have a specific structure, it takes no arguments

> It can take arguments.

No, it can't.  At least not in the declaration.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] triggers problems whit function

[Guillaume Lelarge]

Fernando Moreno a écrit :
> 2008/10/22 Ma. Cristina Peña C. <[EMAIL PROTECTED]
> >
> 
> I want to use a function in to a trigger
> 
> This is my
> 
> CREATE FUNCTION "subradio"(integer) RETURNS integer AS 'select
> cast(count (claveubica) as integer ) from asradios where ubicacion
> =0;' LANGUAGE 'sql';
> 
> And my ttrigger is
> 
> CREATE TRIGGER validaradios AFTER DELETE ON subestacion FOR EACH ROW
> EXECUTE PROCEDURE subradio(0);
> 
> But I got an error
> 
> ERROR:  CreateTrigger: function subradio() does not exist
> 
> What can I do??
> 
> A trigger function must have a specific structure, it takes no arguments

It can take arguments.

> and returns "trigger".

And this is why, I think, the message tells "function subradio does not
exist". Because there's no subradio function that returns trigger.

> Besides, trigger functions are supposed to do
> some processing before or after insert, update or delete operations, so
> there's no sense in returning a row count.
> 
> Take a look at the docs, specially chapter 35 and 38.9.
> 

Regards.


-- 
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.com

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] triggers problems whit function

[Fernando Moreno]

2008/10/22 Ma. Cristina Peña C. <[EMAIL PROTECTED]>

>  I want to use a function in to a trigger
>
>
>
> This is my
>
> CREATE FUNCTION "subradio"(integer) RETURNS integer AS 'select cast(count
> (claveubica) as integer ) from asradios where ubicacion =0;' LANGUAGE 'sql';
>
>
>
> And my ttrigger is
>
> CREATE TRIGGER validaradios AFTER DELETE ON subestacion FOR EACH ROW
> EXECUTE PROCEDURE subradio(0);
>
>
>
> But I got an error
>
> ERROR:  CreateTrigger: function subradio() does not exist
>
>
>
> What can I do??
>
A trigger function must have a specific structure, it takes no arguments and
returns "trigger". Besides, trigger functions are supposed to do some
processing before or after insert, update or delete operations, so there's
no sense in returning a row count.

Take a look at the docs, specially chapter 35 and 38.9.

Cheers.

Re: [GENERAL] Triggers not working

[Dale Harris]

I would have called the Entity table the parent table and not the child
table as the Account table inherits from Entity.  Therefore it appears that
the trigger only works on the table where the actual row was added/belongs
to.

It would be great if triggers on the parent table would work for any row
that appears there, even rows added via a child table.  It would mean that
any new table that inherits the parent table, in my case Entity, would
automatically have the global trigger I want enforced. Nice enhancement :)

Regards,

Dale Harris.


-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, 23 September 2008 13:43
To: Dale Harris
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Triggers not working 

"Dale Harris" <[EMAIL PROTECTED]> writes:
> The trigger trAccountUpdate got called, but why didn't the trigger
> trEntityUpdate get called?

Triggers only apply to the exact table they're declared on, not
to child tables.

It does seem like there might be some use-case for applying a trigger to
child tables too, but that's not how it works now.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Triggers not working

[Tom Lane]

"Dale Harris" <[EMAIL PROTECTED]> writes:
> The trigger trAccountUpdate got called, but why didn't the trigger
> trEntityUpdate get called?

Triggers only apply to the exact table they're declared on, not
to child tables.

It does seem like there might be some use-case for applying a trigger to
child tables too, but that's not how it works now.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Triggers not working

[Dale Harris]

Hi Tom,

The trigger trAccountUpdate got called, but why didn't the trigger
trEntityUpdate get called?

Regards,

Dale Harris.


-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED] 
Sent: Monday, 22 September 2008 22:22
To: Dale Harris
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Triggers not working 

"Dale Harris" <[EMAIL PROTECTED]> writes:
> I'm running PostgreSQL 8.3.3 and I'm having trouble with triggers not
always
> working.  I have the following tables and functions as documented below.
My
> problem is that if I perform an update on the Entity table and modify the
> Code field, why doesn't the trigger for the Entity table execute?  (Row
was
> initially added via the Account table.)

Worksforme ...

regression=# insert into "Account" values(1,'code','name');
INSERT 0 1
regression=# select * from "Entity";
 ID | Code | Name | Modified  | ModifiedBy 
+--+--+---+
  1 | code | name | 2008-09-22 08:19:51.70-04 | postgres
(1 row)

regression=# update "Entity" set "Code" = 'foo' where "ID" = 1;
NOTICE:  trAccountUpdate being called for UPDATE of Account.
UPDATE 1
regression=# select * from "Entity";
 ID | Code | Name | Modified  | ModifiedBy 
+--+--+---+
  1 | foo  | name | 2008-09-22 08:20:18.10-04 | postgres
(1 row)


regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Triggers not working

[Tom Lane]

"Dale Harris" <[EMAIL PROTECTED]> writes:
> I'm running PostgreSQL 8.3.3 and I'm having trouble with triggers not always
> working.  I have the following tables and functions as documented below.  My
> problem is that if I perform an update on the Entity table and modify the
> Code field, why doesn't the trigger for the Entity table execute?  (Row was
> initially added via the Account table.)

Worksforme ...

regression=# insert into "Account" values(1,'code','name');
INSERT 0 1
regression=# select * from "Entity";
 ID | Code | Name | Modified  | ModifiedBy 
+--+--+---+
  1 | code | name | 2008-09-22 08:19:51.70-04 | postgres
(1 row)

regression=# update "Entity" set "Code" = 'foo' where "ID" = 1;
NOTICE:  trAccountUpdate being called for UPDATE of Account.
UPDATE 1
regression=# select * from "Entity";
 ID | Code | Name | Modified  | ModifiedBy 
+--+--+---+
  1 | foo  | name | 2008-09-22 08:20:18.10-04 | postgres
(1 row)


regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Triggers et clefs primaires

[Samuel ROZE]

Le mardi 26 août 2008 à 11:01 +0200, Guillaume Lelarge a écrit :
> Samuel ROZE a écrit :
> > [...]
> > J'ai un trigger (AFTER FOR EACH ROW) sur une table qui à chaque fois
> > qu'il y a un enregistrement sur cette même table, exécute une fonction
> > pgplsql qui éxécute elle-même une fonction PL/sh qui exécute un script
> > PHP.
> > 
> > Dans ce script PHP, je créer 10 enregistrements dans une autre tables où
> > il y a un champ qui fait référence à la table sur laquelle il y a le
> > trigger.
> > 
> > Exemple d'application:
> > 
> > INSERT INTO table1 (champ2, champ3) VALUES ('salut', 'samuel');
> > (la table1 as un champ1 (alias id) qui est un serial en clef primaire)
> > 
> > -> La fonction pgplsql (appelée par le trigger) récupère l'ID de
> > l'enregistrement (NEW.id), le donne à la fonction pl/sh qui le donne au
> > script PHP.
> > 
> > J'enregistre des données dans la table2, avec un champ qui fait
> > référence à l'id de la table1. Des données avec comme id de table1, l'ID
> > qui vient juste d'être enregistré...
> > 
> > ERREUR de clef étrangère... :(
> > 
> 
> The row on table1 is inserted at the end of the trigger.
> 

Yes but... my trigger is a AFTER INSERT trigger... So, data might be
insered... no?

> > Note: Les deux tables sont dans deux schémas différents, avec deux
> > utilisateurs différents. Cependant, aucun problème de droit a priori
> > (GRANT USAGE ON SCHEMA... GRANT REFERENCES ON TABLES...)
> > 
> > Avez-vous une idée ?
> > 
> > Merci à vous,
> > Cordialement, Samuel.
> > 
> 
> This is an english channel. If you want a french one, try
> pgsql-fr-generale (http://archives.postgresql.org/pgsql-fr-generale/).
> 

Okay :) 


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Triggers et clefs primaires

[Guillaume Lelarge]

Samuel ROZE a écrit :
> [...]
> J'ai un trigger (AFTER FOR EACH ROW) sur une table qui à chaque fois
> qu'il y a un enregistrement sur cette même table, exécute une fonction
> pgplsql qui éxécute elle-même une fonction PL/sh qui exécute un script
> PHP.
> 
> Dans ce script PHP, je créer 10 enregistrements dans une autre tables où
> il y a un champ qui fait référence à la table sur laquelle il y a le
> trigger.
> 
> Exemple d'application:
> 
> INSERT INTO table1 (champ2, champ3) VALUES ('salut', 'samuel');
> (la table1 as un champ1 (alias id) qui est un serial en clef primaire)
> 
> -> La fonction pgplsql (appelée par le trigger) récupère l'ID de
> l'enregistrement (NEW.id), le donne à la fonction pl/sh qui le donne au
> script PHP.
> 
> J'enregistre des données dans la table2, avec un champ qui fait
> référence à l'id de la table1. Des données avec comme id de table1, l'ID
> qui vient juste d'être enregistré...
> 
> ERREUR de clef étrangère... :(
> 

The row on table1 is inserted at the end of the trigger.

> Note: Les deux tables sont dans deux schémas différents, avec deux
> utilisateurs différents. Cependant, aucun problème de droit a priori
> (GRANT USAGE ON SCHEMA... GRANT REFERENCES ON TABLES...)
> 
> Avez-vous une idée ?
> 
> Merci à vous,
> Cordialement, Samuel.
> 

This is an english channel. If you want a french one, try
pgsql-fr-generale (http://archives.postgresql.org/pgsql-fr-generale/).


-- 
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.com

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] triggers: dynamic references to fields in NEW and OLD?

[Klint Gore]

[see below]

Kerri Reno wrote:

Vance,

I missed your earlier post, so I may be misunderstanding the 
situation, but I think you could do this more easily in plpython, 
because TD['new'] and TD['old'] are dictionaries, and you can traverse 
the dictionaries like this:


for k, v in TD['new'].items():
if tblfld == k:
  plpy.notice('%s' % v)

This probably looks like gibberish if you're not used to python, but 
if you'd like more help, email me back (with your original post) and 
I'll get back to you next week.


Kerri

On 5/15/08, *Vance Maverick* <[EMAIL PROTECTED] 
> wrote:


Thanks!  Your solution clearly works, but it requires the shared
function to
enumerate all possible column names.  In my real case, there are 8-10
distinct names, so that's a bit uglybut it works.

Vance

-Original Message-
If you just need which table triggered the function then
|TG_TABLE_NAME| may
be simpler than passing parameters.

Something like this will probably work for you (replace the raise
notice
with whatever you have to do)

create or replace function atest() returns trigger as $$ declare
   avalue int;
   tblfld text;
begin
   tblfld := tg_argv[0];
   if tblfld = 'aa' then
  avalue := new.aa;
   else
  if tblfld = 'bb' then
  avalue := new.bb ;
  end if;
   end if;
   raise notice '%',avalue;
   return new;
end;
$$ language plpgsql;

klint.


Agree with Kerri - do it in one of the languages other than plpgsql.

Plpgsql can't do the for loop as simply as other languages.  There's no 
way to walk a record structure (new) as a collection/array and pull out 
the item you are interested in.


You could possibly cheat by putting new into a temp table and then 
executing a select on it.  Performance will probably be bad.


 create temp table newblah as select new.*;
 execute 'select new. ' || tg_argv[0] || '::text' ||
  ' from newblah new '   into newval;
 execute 'drop table newblah';

There probably is a function in the plpgsql internals that will pull a 
named field out of a record but I have no idea what it is or if it's 
exposed so that it can be called.  Maybe someone who knows about the 
internals of plpgsql could comment - is there a function like 
getfieldfromrecord(record,text)?


klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789  
Fax: 02 6773 3266

EMail: [EMAIL PROTECTED]


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] triggers: dynamic references to fields in NEW and OLD?

[Kerri Reno]

Vance,

I missed your earlier post, so I may be misunderstanding the situation, but
I think you could do this more easily in plpython, because TD['new'] and
TD['old'] are dictionaries, and you can traverse the dictionaries like this:

for k, v in TD['new'].items():
if tblfld == k:
  plpy.notice('%s' % v)

This probably looks like gibberish if you're not used to python, but if
you'd like more help, email me back (with your original post) and I'll get
back to you next week.

Kerri

On 5/15/08, Vance Maverick <[EMAIL PROTECTED]> wrote:
>
> Thanks!  Your solution clearly works, but it requires the shared function
> to
> enumerate all possible column names.  In my real case, there are 8-10
> distinct names, so that's a bit uglybut it works.
>
> Vance
>
> -Original Message-
> If you just need which table triggered the function then |TG_TABLE_NAME|
> may
> be simpler than passing parameters.
>
> Something like this will probably work for you (replace the raise notice
> with whatever you have to do)
>
> create or replace function atest() returns trigger as $$ declare
>avalue int;
>tblfld text;
> begin
>tblfld := tg_argv[0];
>if tblfld = 'aa' then
>   avalue := new.aa;
>else
>   if tblfld = 'bb' then
>   avalue := new.bb;
>   end if;
>end if;
>raise notice '%',avalue;
>return new;
> end;
> $$ language plpgsql;
>
> klint.
>
> --
> Klint Gore
> Database Manager
> Sheep CRC
> A.G.B.U.
> University of New England
> Armidale NSW 2350
>
> Ph: 02 6773 3789
> Fax: 02 6773 3266
> EMail: [EMAIL PROTECTED]
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
Yuma Educational Computer Consortium
Compass Development Team
Kerri Reno
[EMAIL PROTECTED]  (928) 502-4240
.·:*¨¨*:·.   .·:*¨¨*:·.   .·:*¨¨*:·.

Re: [GENERAL] triggers: dynamic references to fields in NEW and OLD?

[Vance Maverick]

Thanks!  Your solution clearly works, but it requires the shared function to
enumerate all possible column names.  In my real case, there are 8-10
distinct names, so that's a bit uglybut it works.

Vance

-Original Message-
If you just need which table triggered the function then |TG_TABLE_NAME| may
be simpler than passing parameters.

Something like this will probably work for you (replace the raise notice
with whatever you have to do)

create or replace function atest() returns trigger as $$ declare
   avalue int;
   tblfld text;
begin
   tblfld := tg_argv[0];
   if tblfld = 'aa' then
  avalue := new.aa;
   else
  if tblfld = 'bb' then
  avalue := new.bb;
  end if;
   end if;
   raise notice '%',avalue;
   return new;
end;
$$ language plpgsql;

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: [EMAIL PROTECTED]



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] triggers: dynamic references to fields in NEW and OLD?

[Vance Maverick]

Thanks, this does work.  Unfortunately it requires the trigger function
to iterate through all the possible column names explicitly.  (I have
about 10, and the number might grow in the future.)

Vance

-Original Message-
From: Klint Gore [mailto:[EMAIL PROTECTED] 
Sent: Thursday, May 15, 2008 8:06 PM
To: Vance Maverick
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] triggers: dynamic references to fields in NEW and
OLD?

Vance Maverick wrote:
> I have a bunch of tables that are similar in some ways, and I'm about 
> to put triggers on them.  The triggers will all do essentially the 
> same thing -- the only wrinkle is that the name of the column they 
> operate on varies from table to table.  I'd like to have just one 
> trigger function, written 'dynamically' so it can take the name of the

> column as a trigger parameter (in TG_ARGV).  For example, given tables
>  
>   CREATE TABLE a (aa INT);
>   CREATE TABLE b (bb INT);
>  
> I'd like to be able to write a trigger function foo() such that with 
> trigger declarations
>  
>   CREATE TRIGGER a_foo AFTER INSERT OR UPDATE OR DELETE ON a
> FOR EACH ROW EXECUTE PROCEDURE foo('aa');
>   CREATE TRIGGER b_foo AFTER INSERT OR UPDATE OR DELETE ON b
> FOR EACH ROW EXECUTE PROCEDURE foo('bb');
>  
> the logic in foo() reads columns a.aa or b.bb respectively.
>  
> I've tried composing a SQL string including the text 'NEW.aa' or 
> 'NEW.bb' appropriately, and then passing this to EXECUTE.  This fails:
>
>   ERROR: NEW used in query that is not in a rule
>
> Any suggestions?
>   
If you just need which table triggered the function then |TG_TABLE_NAME|
may be simpler than passing parameters.

Something like this will probably work for you (replace the raise notice
with whatever you have to do)

create or replace function atest() returns trigger as $$ declare
   avalue int;
   tblfld text;
begin
   tblfld := tg_argv[0];
   if tblfld = 'aa' then
  avalue := new.aa;
   else
  if tblfld = 'bb' then
  avalue := new.bb;
  end if;
   end if;
   raise notice '%',avalue;
   return new;
end;
$$ language plpgsql;

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: [EMAIL PROTECTED]


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] triggers: dynamic references to fields in NEW and OLD?

[Klint Gore]

Vance Maverick wrote:

I have a bunch of tables that are similar in some ways, and I'm about to
put triggers on them.  The triggers will all do essentially the same
thing -- the only wrinkle is that the name of the column they operate on
varies from table to table.  I'd like to have just one trigger function,
written 'dynamically' so it can take the name of the column as a trigger
parameter (in TG_ARGV).  For example, given tables
 
  CREATE TABLE a (aa INT);

  CREATE TABLE b (bb INT);
 
I'd like to be able to write a trigger function foo() such that with

trigger declarations
 
  CREATE TRIGGER a_foo AFTER INSERT OR UPDATE OR DELETE ON a

FOR EACH ROW EXECUTE PROCEDURE foo('aa');
  CREATE TRIGGER b_foo AFTER INSERT OR UPDATE OR DELETE ON b
FOR EACH ROW EXECUTE PROCEDURE foo('bb');
 
the logic in foo() reads columns a.aa or b.bb respectively.
 
I've tried composing a SQL string including the text 'NEW.aa' or

'NEW.bb' appropriately, and then passing this to EXECUTE.  This fails:

  ERROR: NEW used in query that is not in a rule

Any suggestions?
  
If you just need which table triggered the function then |TG_TABLE_NAME| 
may be simpler than passing parameters.


Something like this will probably work for you (replace the raise notice 
with whatever you have to do)


create or replace function atest() returns trigger as $$
declare
  avalue int;
  tblfld text;
begin
  tblfld := tg_argv[0];
  if tblfld = 'aa' then
 avalue := new.aa;
  else
 if tblfld = 'bb' then
 avalue := new.bb;
 end if;
  end if;
  raise notice '%',avalue;
  return new;
end;
$$ language plpgsql;

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789  
Fax: 02 6773 3266

EMail: [EMAIL PROTECTED]


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Triggers & inheritance

[Tom Lane]

Scott Ribe <[EMAIL PROTECTED]> writes:
> Triggers have never been inherited, right? Not in any version?

AFAIR, no.  If they had been I kinda doubt we would have removed it.

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Triggers: Detecting if a column value is explicitly set in an UPD ATE statement or not... ("IS NULL" not working?)

[Decibel!]

On Wed, Aug 01, 2007 at 09:30:25AM -0500, Weber, Geoffrey M. wrote:
> I want to set a BOOLEAN column value to FALSE by default for all INSERT and
> UPDATE statements performed against a particular table _UNLESS_ it's
> explicitly set to TRUE in the SQL statement.  Here is the trigger I created:
 
The insert case is easy, just set the default for the field to false.

For updates, you'll want a trigger that compares NEW.field to OLD.field,
and if they're the same sets NEW.field to false.
 
> CREATE TABLE table1 ( id INTEGER PRIMARY KEY, data VARCHAR(64), b_flag
> BOOLEAN);
> 
> CREATE OR REPLACE FUNCTION func1() RETURNS trigger AS $func1$
> BEGIN
>  RAISE NOTICE 'NEW.b_flag=%', NEW.b_flag;
>   IF ( NEW.b_flag IS NULL ) THEN
>   NEW.b_flag := FALSE;
>   END IF;
> 
>   RETURN NEW;
> END;
> $func1$ LANGUAGE plpgsql;
> 
> CREATE TRIGGER func1 BEFORE INSERT OR UPDATE ON table1 FOR EACH ROW EXECUTE
> PROCEDURE func1();
> 
> 
> 
> What I get, however, is that for an UPDATE, the "NEW.b_flag" value evaluates
> to TRUE if it has been set to TRUE by a previous UPDATE that explicitly sets
> it.  So...
> 
> tqa=> INSERT INTO table1 VALUES ( '1', 'some data');
> NOTICE:  NEW.b_flag=
> INSERT 0 1
> tqa=> SELECT b_flag FROM table1 WHERE id='1';
>  b_flag
> 
>  f
> (1 row)
> 
> tqa=> UPDATE table1 SET b_flag=TRUE where id='1';
> NOTICE:  NEW.b_flag=t
> UPDATE 1
> tqa=> SELECT b_flag FROM table1 WHERE id='1';
>  b_flag
> 
>  t
> (1 row)
> 
> tqa=> UPDATE table1 SET data='new data' where id='1';
> NOTICE:  NEW.b_flag=t
> UPDATE 1
> tqa=> SELECT b_flag FROM table1 WHERE id='1';
>  b_flag
> 
>  t
> (1 row)
> 
> 
> 
> As you can see, I put a RAISE NOTICE and verified that for some reason, the
> NEW row contains a 'TRUE' value for b_flag, even though I didn't explicitly
> set it in the last UPDATE statement.  Why does it seem to be reading the
> value from the OLD row for that column unless I override it inside the SQL
> statement?  Is there any way to achieve the desired result without having to
> explicitly set 'b_flag' each time I touch a row in the table?
> 
> 
> 
> 
> NOTICE: This electronic mail transmission may contain confidential
> information and is intended only for the person(s) named.  Any use, copying
> or disclosure by any other person is strictly prohibited. If you have
> received this transmission in error, please notify the sender via e-mail.
> 
> 
> 

-- 
Decibel!, aka Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)


pgp9wWogfui3t.pgp
Description: PGP signature

Re: [GENERAL] Triggers to allow user create table?

[John DeSoi]

See the SECURITY DEFINER option for CREATE FUNCTION. This way you  
don't have to give them create table privileges, but they can still  
create a table through your function. You'll need to use EXECUTE to  
create a table in pl/pgsql.





On May 27, 2007, at 4:50 PM, CAJ CAJ wrote:

Had a question on best approach with some security issues around on  
the fly table creation by a user.


I want to users to create dynamic tables from the application. This  
means that the user logged in should have create table privileges  
at the database level. Assuming this is a security risk for  
allowing all users to have table creation privileges. is it  
possible to create some sort of trigger to allow the user to create  
table when ready and once it's done revoke it automatically?


What is the best approach conceptual wise and security wise when  
dealing with these situations? The less the user can do on the  
database the better it is?






John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] Triggers inherited?

[Bertram Scharpf]

Hi,

Am Freitag, 23. Feb 2007, 07:10:06 + schrieb Richard Huxton:
> Bertram Scharpf wrote:
> >it is very inconvenient for me that triggers aren't inherited:
> 
> Foreign keys too (which are a special type of trigger of course).
> 
> >Is this behaviour to be implemented at any point of time in
> >the future? Could it be advisible to write the patch? Or is
> >it just too easy to emulate it?
> 
> If you have the time and skills, I think many people would be interested 
> in a patch to handle this.

I successfully wrote some patches to `plruby' and to
`ruby-postgres'. Seems that I have to give it a try. Don't
expect too much too near.

Anyhow: Thank you very much for the tip. At least I know I'm
not the only one thinking about these things.

Bertram


-- 
Bertram Scharpf
Stuttgart, Deutschland/Germany
http://www.bertram-scharpf.de

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Triggers inherited?

[Marc Evans]

Hi -

I too have encountered this issue. The work around that I created was to 
have every table have a set of 3 cooresponding functions that know how to 
1) create the table; 2) create triggers for the table; 3) create indexes 
for the table. By doing so, I then am able to use a lazy partitioning 
technique, such that an insert trigger determines if the necessary 
partition exists, and if not, calls the functions needed to create it. It 
keeps the SQL needed for a table in a single location (DRY), and is 
flexible enough to be used for creating virgin databases as well as 
updating existing databases.


- Marc

On Thu, 22 Feb 2007, Bertram Scharpf wrote:


Hi,

it is very inconvenient for me that triggers aren't inherited:

 create table watch (
 mod timestamp with time zone default '-infinity' not null
 );

 create function update_mod() returns trigger ...

 create trigger update_mod before insert or update on watch
 for each row execute procedure update_mod();

 create table some  ( ... ) inherits (watch);
 create table other ( ... ) inherits (watch);


Is this behaviour to be implemented at any point of time in
the future? Could it be advisible to write the patch? Or is
it just too easy to emulate it?

Reimplemeting a trigger for each descending table definitely
dosn't satisfy me.

Thanks in advance,

Bertram


--
Bertram Scharpf
Stuttgart, Deutschland/Germany
http://www.bertram-scharpf.de

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Triggers inherited?

[Richard Huxton]

Bertram Scharpf wrote:

Hi,

it is very inconvenient for me that triggers aren't inherited:


Foreign keys too (which are a special type of trigger of course).


Is this behaviour to be implemented at any point of time in
the future? Could it be advisible to write the patch? Or is
it just too easy to emulate it?


If you have the time and skills, I think many people would be interested 
in a patch to handle this.


You will want to check the todo list, developers website and FAQ (and of 
course the internals section of the manuals) then discuss things on the 
hackers mailing list.

  http://www.postgresql.org/docs/faqs.TODO.html
  http://www.postgresql.org/developer/
  http://www.postgresql.org/docs/faqs.FAQ_DEV.html
  http://developer.postgresql.org/
--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] triggers vs b-tree

[Tomas Vondra]

> Hello I have a design question:
> 
>  I have a table representing Families, and a table representing Persons.
> The table Family have a row family_id as primary key.
> The table Person have a row person_id as primary key and contain also a
> row family_id.
> As you can understand, the row family_id in a table ficha_person is not
> unique, I mean is the same for all the family person's.
> 
> So my question is: If most of the time I need to find all the persons
> for one asked family what is the best way to do that?
> I think about two methods:
> 1-  Making a b-tree index in ficha_person with the rows
> family_id and person_id.
> 2 - Adding an array in the table ficha_family containing the
> persons of this family. And creating a Trigger that update this array
> for each  person insert/delete in the table ficha_family.
> 
> So ..., what do you think? There are a better solution or what of the
> above solutions is better ??
> 
>  Thank you in advance,
>   Gustavo.
> 

The trigger/array seems to me as a premature optimization - if you are
not sure the index is 'too slow' (and there's no other way to speed it
up) don't do it.

You should always have a foreign key in Person(family_id) referencing
the Family(family_id) as you need to reinforce data integrity between
these two tables, and the 'rule of a thumb' is to have indexes on
foreign keys in large tables. The reason is pretty simple - the
PostgreSQL does a simple query when checking the foreign key.

So if the Person table is 'small' (less than for example 1000 rows) and
it will not grow too much, there's no need to use an index (as it won't
be used for small tables) and the queries to get all the family members
will be very fast too.

On the other side, if the Person table is 'large' (say more than 10.000
rows), then there should be an index on Person(family_id). Then it
depends on your requirements - the most important things to consider are
these:

1) Will the application be mostly used to select or update?

   The trigger adds (small) overhead to modifications, but if you do
   mostly selects this may not be a problem.

2) Do you need only IDs of the family members, or do you need all the
   data from Person table?

   The trigger/array solution gives you only IDs and you'll have to
   fetch the data in a separate query (most probably). The array simply
   complicates the queries.

Anyway, I would try to stick with the foreign key / index solution as
long as possible. If you are not happy with the speed do some benchmarks
with the trigger / array solution and compare them to the foreign key /
index. Try to do some other optimizations too (for example cluster the
Person table along the family_id column - that usually means a serious
performance boost).

Tomas

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] triggers vs b-tree

[Joris Dobbelsteen]

I believe you should design it in a slightly different way:

>-Original Message-
>From: [EMAIL PROTECTED] 
>[mailto:[EMAIL PROTECTED] On Behalf Of 
>gustavo halperin
>Sent: donderdag 25 januari 2007 21:34
>To: pgsql-general@postgresql.org
>Subject: [GENERAL] triggers vs b-tree
>
>Hello I have a design question:
>
>  I have a table representing Families, and a table 
>representing Persons.
>
> So my question is: If most of the time I need to find all the 
>persons for one asked family what is the best way to do that?
> I think about two methods:
> 1-  Making a b-tree index in ficha_person with the 
>rows family_id and person_id.
> 2 - Adding an array in the table ficha_family 
>containing the persons of this family. And creating a Trigger 
>that update this array for each  person insert/delete in the 
>table ficha_family.

It seems you are quite new (or unfamiliar) to databases.
Are you familiar with "Database Normalization"?

Read this:
http://databases.about.com/od/specificproducts/a/normalization.htm
(Handles all you need)
http://www.phlonx.com/resources/nf3/
(Gives better examples up to 3th normal form)
http://en.wikipedia.org/wiki/Database_normalization
(Wikipedia does not seem to be too good, but it works)

After reading this carefully you should know what option you should
take. The answer is right in there, but there is a lot more useful
information too.

- Joris

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] triggers and TriggerData

[Alan Hodgson]

On Monday 22 January 2007 07:04, Michael Fuhr <[EMAIL PROTECTED]> wrote:
> You can pass literal string arguments to a trigger function.  See
> the CREATE TRIGGER documentation and, for PL/pgSQL, TG_ARGV and
> TG_NARGS.  For C see "Writing Trigger Functions in C"; search for
> tgnargs and tgargs.
>
> http://www.postgresql.org/docs/8.2/interactive/sql-createtrigger.html
> http://www.postgresql.org/docs/8.2/interactive/plpgsql-trigger.html
> http://www.postgresql.org/docs/8.2/interactive/trigger-interface.html
>
> (These links are to the 8.2 documentation but earlier versions also
> support arguments to trigger functions.)

I stand corrected.  And I can imagine some uses for passing string constants 
to triggers, but nothing related to what the OP was looking for ...

-- 
99 percent of lawyers give the rest a bad name


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] triggers and TriggerData

[Michael Fuhr]

On Sun, Jan 21, 2007 at 09:05:30PM -0800, Alan Hodgson wrote:
> On Sunday 21 January 2007 15:56, gustavo halperin <[EMAIL PROTECTED]> wrote:
> > I have another question about triggers, how can I pass arguments ?? I
> > read about some struct TriggerData *CurrentTriggerData, but I didn't
> > found any explanation or example about how to use it in postgres SQL.
> > My problem is that  for any INSERT row in table 'B' I need to pass
> > to the function trigger two values  of this row.  Do you know how to
> > do so or where are examples of how to do it ??
> 
> You cannot pass values to a trigger.

You can pass literal string arguments to a trigger function.  See
the CREATE TRIGGER documentation and, for PL/pgSQL, TG_ARGV and
TG_NARGS.  For C see "Writing Trigger Functions in C"; search for
tgnargs and tgargs.

http://www.postgresql.org/docs/8.2/interactive/sql-createtrigger.html
http://www.postgresql.org/docs/8.2/interactive/plpgsql-trigger.html
http://www.postgresql.org/docs/8.2/interactive/trigger-interface.html

(These links are to the 8.2 documentation but earlier versions also
support arguments to trigger functions.)

-- 
Michael Fuhr

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] triggers and TriggerData

[Alan Hodgson]

On Sunday 21 January 2007 15:56, gustavo halperin 
<[EMAIL PROTECTED]> wrote:
> First, thank you for your answers about my problem with the function
> 'CREATE TRIGGER',
> I have another question about triggers, how can I pass arguments ?? I
> read about some struct TriggerData *CurrentTriggerData, but I didn't
> found any explanation or example about how to use it in postgres SQL.
>  My problem is that  for any INSERT row in table 'B' I need to pass
> to the function trigger two values  of this row.  Do you know how to
> do so or where are examples of how to do it ??

You cannot pass values to a trigger.

However, insert triggers (in PLpgSQL at least) receive the row value in 
the NEW record variable.  There are examples in the documentation.

http://www.postgresql.org/docs/8.2/interactive/plpgsql-trigger.html#PLPGSQL-TRIGGER-EXAMPLE

-- 
"A government that robs Peter to pay Paul can always depend upon the 
support of Paul." - George Bernard Shaw


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] TRIGGERS - access sql query_string that called it?

[Richard Huxton]

Matthew Peter wrote:

Is it possible to access the entire sql query_string that called the trigger?


No. It doesn't necessarily exist in any useful form (think about what 
happens with rules on a view, or triggers making updates which fire 
other triggers - what is the "original" query?).


--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] Triggers

[Tom Lane]

Chris Mair <[EMAIL PROTECTED]> writes:
>> Why you should write a function first and then the trigger, which must
>> call that function?

> ... there's not just PL/PGSQL: you might want
> to define a function in C or Perl and then have a trigger call it.

Right, that's the real reason: this approach doesn't constrain which
PL you can write your triggers in.

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Triggers

[Chris Mair]

> Why you should write a function first and then the trigger, which must
> call that function?
> 
> What are the advantages/disadvantages of that? Where can I find more
> information?

The PG way seems very natural to me:
you can write functions that do something and then have many triggers
call that same function.

Also there's not just PL/PGSQL: you might want
to define a function in C or Perl and then have a trigger call it.

Bye, Chris.


-- 

Chris Mair
http://www.1006.org


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Triggers invoking a stored procedure or a C function

[Roman Neuhauser]

# [EMAIL PROTECTED] / 2006-08-16 11:55:39 -0400:
> Hi,
> Conventionally a trigger would fire a few sql queries on a particular event
> and we have standard code for that.
> 
> My requirement is to start a stored procedure or a C function as a trigger
> action.
> 
> Is this possible?

Besides the fact that PostgreSQL doesn't have stored procedures,
only "SQL-invoked routines", both SQL and external, you can.
After all,
http://www.postgresql.org/docs/8.1/static/sql-createtrigger.html
says

CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
ON table [ FOR [ EACH ] { ROW | STATEMENT } ]
EXECUTE PROCEDURE funcname ( arguments )

The above page also links to "33.4. A Complete Example"
(http://www.postgresql.org/docs/8.1/static/trigger-example.html),
which revolves around a trigger function written in C.

-- 
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man.  You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Triggers in Postgres

[Tom Lane]

Chris Mair <[EMAIL PROTECTED]> writes:
>> http://www.postgresql.org/docs/8.1/interactive/triggers.html
>> " It is not currently possible to write a trigger function in the
>> plain SQL function language. "

> The whole paragraph says.

> "It is also possible to write a trigger function in C, although most
> people find it easier to use one of the procedural languages. It is not
> currently possible to write a trigger function in the plain SQL function
> language."

And that comes *after* a paragraph talking about the different
procedural languages you can write a trigger in.  I can't imagine how
anyone would come away from reading that with the impression that C
is the first recommendation for writing triggers.

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Triggers in Postgres

[Chris Mair]

> http://www.postgresql.org/docs/8.1/interactive/triggers.html
>  
> it says something like this:
>  
> " It is not currently possible to write a trigger function in the
> plain SQL function language. "

The whole paragraph says.

"It is also possible to write a trigger function in C, although most
people find it easier to use one of the procedural languages. It is not
currently possible to write a trigger function in the plain SQL function
language."

That is: you can and you should write your trigger in a procedural
language. In particular - if you want to stay as closed as possible
to SQL you should use procedural SQL, which in PostgreSQL is called
PL/pgSQL:
http://www.postgresql.org/docs/8.1/interactive/plpgsql.html

Bye, Chris.



---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Triggers in Postgres

[Roman Neuhauser]

# [EMAIL PROTECTED] / 2006-08-01 02:35:48 -0400:
> On 8/1/06, Roman Neuhauser <[EMAIL PROTECTED]> wrote:
> >
> ># [EMAIL PROTECTED] / 2006-07-31 11:58:49 -0400:
> >> Actually Postgres manual of triggers says that in postgres, you can't
> >write
> >> a trigger in conventional sql. You have to write it in a procedural
> >language
> >> like C. So wanted some more insight on it.
> >> ~Jas
> >
> >   Where does it say so? Do you have a link?
>
> http://www.postgresql.org/docs/8.1/interactive/triggers.html
> 
> it says something like this:
> 
> " It is not currently possible to write a trigger function in the plain SQL
> function language. "
> 
> though lately I saw triggers written in pure sql in postgres

Notice that the manual doesn't mention C, and I guess those "pure
sql" triggers were written in PL/PgSQL, a "procedural language".

As the following example fails to demonstrate, it's just SQL with a
few control structures, very easy to get running if you have a bit
of SQL and programming background.

CREATE TABLE t (x SERIAL);

CREATE FUNCTION sqlf()
RETURNS SETOF t
STABLE
LANGUAGE SQL
AS
$$
SELECT * FROM t;
$$;

CREATE FUNCTION plpgsqlf()
RETURNS SETOF t
STABLE
LANGUAGE PLPGSQL
AS
$$
DECLARE
r t;
BEGIN
FOR r IN SELECT * FROM t LOOP
RETURN NEXT r;
END LOOP;
END;
$$;

-- 
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man.  You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Triggers in Postgres

[Jasbinder Bali]

http://www.postgresql.org/docs/8.1/interactive/triggers.html
 
it says something like this:
 
" It is not currently possible to write a trigger function in the plain SQL function language. "
 
though lately I saw triggers written in pure sql in postgres
 
~jas 
On 8/1/06, Roman Neuhauser <[EMAIL PROTECTED]> wrote:
# [EMAIL PROTECTED] / 2006-07-31 11:58:49 -0400:> Actually Postgres manual of triggers says that in postgres, you can't write
> a trigger in conventional sql. You have to write it in a procedural language> like C. So wanted some more insight on it.> ~Jas   Where does it say so? Do you have a link?--How many Vietnam vets does it take to screw in a light bulb?
You don't know, man.  You don't KNOW.Cause you weren't THERE. http://bash.org/?255991

Re: [GENERAL] Triggers in Postgres

[Roman Neuhauser]

# [EMAIL PROTECTED] / 2006-07-31 11:58:49 -0400:
> Actually Postgres manual of triggers says that in postgres, you can't write
> a trigger in conventional sql. You have to write it in a procedural language
> like C. So wanted some more insight on it.
> ~Jas

Where does it say so? Do you have a link?

-- 
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man.  You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Triggers in Postgres

[Richard Huxton]

Jasbinder Bali wrote:
Actually Postgres manual of triggers says that in postgres, you can't 
write a trigger in conventional sql. You have to write it in a 
procedural language like C. So wanted some more insight on it.


See chapters 35 - 39 of the manual for details. In particular a 
discussion of plpgsql and an example of writing a trigger using it.


http://www.postgresql.org/docs/8.1/static/plpgsql.html

There are other procedural languages available too: php, ruby, shell, R, 
all of various levels of maturity and with different features.

--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] Triggers in Postgres

[Jasbinder Bali]

Actually Postgres manual of triggers says that in postgres, you can't write a trigger in conventional sql. You have to write it in a procedural language like C. So wanted some more insight on it.
~Jas 
On 7/31/06, Tino Wildenhain <[EMAIL PROTECTED]> wrote:
Jasbinder Bali wrote:> Hi,> Was wondering if one can write triggers with SQL statements as we have
> in other RDBMS like SQL Server and oracle.What would such a trigger "in SQL statements" look like?SQL Server has Triggers in Transact-SQL, which is just somethinglike a pl/language.
> Can these be written in procedural languages only?> Please put some insight on what needs to be known before working with> triggers in postgres.You should probably try it when you read all manuals :-)
pl/sql is very close to pure SQL, so if you manage to writeyour trigger with it - why not? :-)What should your trigger do btw?RegardsTino

Re: [GENERAL] Triggers in Postgres

[Tino Wildenhain]

Jasbinder Bali wrote:
> Hi,
> Was wondering if one can write triggers with SQL statements as we have
> in other RDBMS like SQL Server and oracle.

What would such a trigger "in SQL statements" look like?
SQL Server has Triggers in Transact-SQL, which is just something
like a pl/language.

> Can these be written in procedural languages only?
> Please put some insight on what needs to be known before working with
> triggers in postgres.

You should probably try it when you read all manuals :-)
pl/sql is very close to pure SQL, so if you manage to write
your trigger with it - why not? :-)

What should your trigger do btw?

Regards
Tino

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Triggers in C - Segmentation Fault

[Martijn van Oosterhout]

The core file usually appears in the data directory of the backend.

If you still can't find it, you can try attaching gdb to the running
backend. After the connection has started, find the backend (not the
postmaster) and attach using:

gdb -p 

Once connected, type "cont" and proceed with the actions to trigger the
segfault. When the segfault happens, gdb will catch it and you can type
"bt" to see where you are...

Have a ncie day,

On Mon, May 15, 2006 at 11:47:24AM +0100, Chris Coleman wrote:
> Hi,
> 
> I have done the below command and restarted the server and built my
> triggers with the -g command using gcc, but I cannot seem to find any
> core files nor any reference to where postgres may place them. Where
> would they normally appear?
> 
> Cheers
> Chris
> 
> >Probably the easiest thing to do is make sure your functions are
> >compiled with debugging and enable core dump by running "ulimit -S -c
> >unlimited" before starting the server. You can then use gdb to
> >pin-point where it dies...
> 
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings

-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [GENERAL] Triggers in C - Segmentation Fault

[Chris Coleman]

Hi,

I have done the below command and restarted the server and built my
triggers with the -g command using gcc, but I cannot seem to find any
core files nor any reference to where postgres may place them. Where
would they normally appear?

Cheers
Chris


Probably the easiest thing to do is make sure your functions are
compiled with debugging and enable core dump by running "ulimit -S -c
unlimited" before starting the server. You can then use gdb to
pin-point where it dies...


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Triggers in C - Segmentation Fault

[Martijn van Oosterhout]

On Thu, May 11, 2006 at 10:28:37AM +0100, Chris Coleman wrote:
> Hi,
> 
> I have written a couple of trigger functions in C that utilise the SPI
> interface.  They are both row level triggers, one a before trigger and
> one an after trigger.
> 
> If the triggers are called with an update statement that only affects
> one row then both are excecuted correctly and without error.  But if I
> try to update multiple rows then this fails.

Probably the easiest thing to do is make sure your functions are
compiled with debugging and enable core dump by running "ulimit -S -c
unlimited" before starting the server. You can then use gdb to
pin-point where it dies...

Hope this helps,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [GENERAL] Triggers and Transactions

[Terry Lee Tucker]

On Thursday 20 April 2006 12:25 pm, "Chris Coleman" <[EMAIL PROTECTED]> thus 
communicated:
--> Hi,
-->
--> I have a question about how much of a trigger is in a transaction.
--> I've read the docs and googled around but can't seem to find a
--> concrete answer.
-->
--> I have two triggers that are designed to work together, one is a
--> before trigger and one is an after.  If the before trigger succeeds
--> then it will have made some changes to one of my tables, however if
--> the after one fails some how (elog(ERROR, "")? then I would like to
--> rollback the changes of the before one as well as any made by the
--> after one too.
-->
--> Is this possible?
-->
--> Many thanks
-->
--> Chris Coleman.
-->
--> ---(end of broadcast)---
--> TIP 6: explain analyze is your friend
-->

The whole process is in a transaction and all of it will be rolled back.

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Triggers and Multiple Schemas.

[Paul Newman]

Hi Michael,

Haven't tried it yet .. but THANK YOU !
I will try it later today  assuming it works it will say us a LOT of
maintenance!

Regards

Paul Newman

-Original Message-
From: Michael Fuhr [mailto:[EMAIL PROTECTED] 
Sent: 08 March 2006 23:48
To: Paul Newman
Cc: Louis Gonzales; Scott Marlowe; pgsql general
Subject: Re: [GENERAL] Triggers and Multiple Schemas.

On Wed, Mar 08, 2006 at 11:16:55PM -, Paul Newman wrote:
> So how can I get the schema name of the calling table trigger and use
it
> in the form of set Search_path at the beginning of the function ?

Here's an example:

CREATE FUNCTION trigfunc() RETURNS trigger AS $$
DECLARE
schemaname  text;
oldpath text;
BEGIN
SELECT INTO schemaname n.nspname
  FROM pg_namespace AS n
  JOIN pg_class AS c ON c.relnamespace = n.oid
  WHERE c.oid = TG_RELID;

oldpath := current_setting('search_path');

PERFORM set_config('search_path', schemaname, true);
RAISE INFO 'schema = %  oldpath = %', schemaname, oldpath;
PERFORM set_config('search_path', oldpath, false);

RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE SCHEMA foo;
CREATE SCHEMA bar;

CREATE TABLE foo.tablename (id integer);
CREATE TABLE bar.tablename (id integer);

CREATE TRIGGER footrig BEFORE INSERT OR UPDATE ON foo.tablename
  FOR EACH ROW EXECUTE PROCEDURE trigfunc();

CREATE TRIGGER bartrig BEFORE INSERT OR UPDATE ON bar.tablename
  FOR EACH ROW EXECUTE PROCEDURE trigfunc();


Now let's insert some records:

test=> INSERT INTO foo.tablename VALUES (1);
INFO:  schema = foo  oldpath = public
INSERT 0 1

test=> INSERT INTO bar.tablename VALUES (2);
INFO:  schema = bar  oldpath = public
INSERT 0 1

-- 
Michael Fuhr

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Triggers and Multiple Schemas.

[Michael Fuhr]

On Wed, Mar 08, 2006 at 11:16:55PM -, Paul Newman wrote:
> So how can I get the schema name of the calling table trigger and use it
> in the form of set Search_path at the beginning of the function ?

Here's an example:

CREATE FUNCTION trigfunc() RETURNS trigger AS $$
DECLARE
schemaname  text;
oldpath text;
BEGIN
SELECT INTO schemaname n.nspname
  FROM pg_namespace AS n
  JOIN pg_class AS c ON c.relnamespace = n.oid
  WHERE c.oid = TG_RELID;

oldpath := current_setting('search_path');

PERFORM set_config('search_path', schemaname, true);
RAISE INFO 'schema = %  oldpath = %', schemaname, oldpath;
PERFORM set_config('search_path', oldpath, false);

RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE SCHEMA foo;
CREATE SCHEMA bar;

CREATE TABLE foo.tablename (id integer);
CREATE TABLE bar.tablename (id integer);

CREATE TRIGGER footrig BEFORE INSERT OR UPDATE ON foo.tablename
  FOR EACH ROW EXECUTE PROCEDURE trigfunc();

CREATE TRIGGER bartrig BEFORE INSERT OR UPDATE ON bar.tablename
  FOR EACH ROW EXECUTE PROCEDURE trigfunc();


Now let's insert some records:

test=> INSERT INTO foo.tablename VALUES (1);
INFO:  schema = foo  oldpath = public
INSERT 0 1

test=> INSERT INTO bar.tablename VALUES (2);
INFO:  schema = bar  oldpath = public
INSERT 0 1

-- 
Michael Fuhr

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Triggers and Multiple Schemas.

[Paul Newman]

Hi,
Yes my db is indeed like

dbname.myschema1.sometable
dbname.myschema2.sometable
dbname.myschema2.sometable2
dbname.myschema2.sometable3

Physically all data is in one db .. however each client has there own
schema (or virtual db). Each client schema has identical structure. And
a number of tables have triggers that are identical in each schema. My
problem at the moment is that I also define the trigger functions in
each schema. This is a complete nightmare to maintain in our case since
we will be very rapidly introducing upto about 400 identical schemas
into a single db.

The reason we are doing this is to have resource and connection pooling
(therefore scalability) for many of our clients who run our system.

So how can I get the schema name of the calling table trigger and use it
in the form of set Search_path at the beginning of the function ?

Regards

Paul Newman 

-Original Message-
From: Louis Gonzales [mailto:[EMAIL PROTECTED] 
Sent: 08 March 2006 20:43
To: Scott Marlowe
Cc: Paul Newman; pgsql general
Subject: Re: [GENERAL] Triggers and Multiple Schemas.

Paul,
What is the current schema layout for your db instances?  I don't think 
it's possible to share across db instances like this:

dbname1.myschema.sometable
dbname2.myschema.sometable

But you can share resources of the following type:

dbname.myschema1.sometable
dbname.myschema2.sometable
dbname.myschema2.sometable2
dbname.myschema2.sometable3

I think that it's a mis-statement to call each separate schema a DB, but

the group of:
dbname.myschema2.(collection of objects) is effectively a separate DB, 
in that, the tables are what constitute a functional db.

so you can treat
dbname.myschema1.(...)
and
dbname.myschema2.(...)
as separate databases that share common resources, because they belong 
to the same db instances, namely "dbname"


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Triggers and Multiple Schemas.

[Richard Huxton]

Paul Newman wrote:

Hi,

 We run with multiple identical schemas in our db. Each schema actually
represents a clients db. What we'd like to do is have a common schema
where trigger functions and the like are held whilst each trigger
defined against the tables is in there own particular schema. This would
mean that there is one function per trigger type to maintain.

 


However at the moment we are placing the trigger functions within each
schema along with trigger itself. The reason is that we don't know of a
function or a variable that says "Give me the schema of the trigger that
is calling this function".


You can pass a parameter into the function from the trigger definition. 
That's probably the easiest way. In plpgsql, parameters appear in 
TG_ARGV[]. Or, you could reverse-engineer the schema-name from TG_RELID.


http://www.postgresql.org/docs/8.1/static/plpgsql-trigger.html

HTH
--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [GENERAL] Triggers and Multiple Schemas.

[Louis Gonzales]

Paul,
What is the current schema layout for your db instances?  I don't think 
it's possible to share across db instances like this:


dbname1.myschema.sometable
dbname2.myschema.sometable

But you can share resources of the following type:

dbname.myschema1.sometable
dbname.myschema2.sometable
dbname.myschema2.sometable2
dbname.myschema2.sometable3

I think that it's a mis-statement to call each separate schema a DB, but 
the group of:
dbname.myschema2.(collection of objects) is effectively a separate DB, 
in that, the tables are what constitute a functional db.


so you can treat
dbname.myschema1.(...)
and
dbname.myschema2.(...)
as separate databases that share common resources, because they belong 
to the same db instances, namely "dbname"
begin:vcard
fn:louis
n:gonzales;louis
email;internet:[EMAIL PROTECTED]
tel;home:248.943.0144
tel;cell:248.943.0144
x-mozilla-html:TRUE
version:2.1
end:vcard


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Triggers and Multiple Schemas.

[Michael Fuhr]

On Tue, Mar 07, 2006 at 06:34:33AM -, Paul Newman wrote:
> However at the moment we are placing the trigger functions within each
> schema along with trigger itself. The reason is that we don't know of a
> function or a variable that says "Give me the schema of the trigger that
> is calling this function".

PL/pgSQL triggers receive the table's oid in TG_RELID.  You could
query pg_class and join to pg_namespace to get the table's schema
name.  Is that what you're looking for?

-- 
Michael Fuhr

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Triggers and Multiple Schemas.

[Scott Marlowe]

On Wed, 2006-03-08 at 14:32, Louis Gonzales wrote:
> Scott Marlowe wrote:
> > On Wed, 2006-03-08 at 14:19, Louis Gonzales wrote:
> > 
> >   
> > > Paul,
> > > When you say "multiple identical schemas" are they all separate
> > > explicit schemas?  Or are they all under a general 'public' schema.
> > > From my understanding, when you create a new db instance, it's under
> > > the public level schema by default unless you create an explicit
> > > schema and subsequently a db instance - or several - therein,
> > > effectively establishing sibling db instances belonging to a single
> > > schema, I know at least that data in the form of table access is
> > > allowed across the siblings.  I'd also assume that this would be the
> > > case for triggers and functions that could be identified or defined at
> > > the 'root' level 
> > > 
> > Ummm.  In PostgreSQL schemas are contained within databases, not the
> > other way around.  It's cluster contains databases contains schemas
> > contains objects (tables, sequences, indexes, et. al.)
> > 
> > ---(end of broadcast)---
> > TIP 6: explain analyze is your friend
> >   
> I stand corrected.  That's right.  But under a database you create
> your explicit schemas, to organize tables which constitute your
> separate data, where all of the schemas belonging to a database
> instance, can share resources without conflicting with one another.
> 
> I apologize for giving the inaccurate description of database to
> schema relationship.

Heck, ya just got a couple terms crossed up.  No biggie.

And yes, what the OP wanted to do should work.  You just need to apply
the triggers to each schema's table individually.

I'd suggest scripting the whole thing in bash, perl, or php for easy
maintenance.

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Triggers and Multiple Schemas.

[Louis Gonzales]

Scott Marlowe wrote:

  On Wed, 2006-03-08 at 14:19, Louis Gonzales wrote:

  
  
Paul,
When you say "multiple identical schemas" are they all separate
explicit schemas?  Or are they all under a general 'public' schema.
>From my understanding, when you create a new db instance, it's under
the public level schema by default unless you create an explicit
schema and subsequently a db instance - or several - therein,
effectively establishing sibling db instances belonging to a single
schema, I know at least that data in the form of table access is
allowed across the siblings.  I'd also assume that this would be the
case for triggers and functions that could be identified or defined at
the 'root' level 

  
  
Ummm.  In PostgreSQL schemas are contained within databases, not the
other way around.  It's cluster contains databases contains schemas
contains objects (tables, sequences, indexes, et. al.)

---(end of broadcast)---
TIP 6: explain analyze is your friend
  

I stand corrected.  That's right.  But under a database you create your
explicit schemas, to organize tables which constitute your separate
data, where all of the schemas belonging to a database instance, can
share resources without conflicting with one another.

I apologize for giving the inaccurate description of database to schema
relationship.


begin:vcard
fn:louis
n:gonzales;louis
email;internet:[EMAIL PROTECTED]
tel;home:248.943.0144
tel;cell:248.943.0144
x-mozilla-html:TRUE
version:2.1
end:vcard


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Triggers and Multiple Schemas.

[Scott Marlowe]

On Wed, 2006-03-08 at 14:19, Louis Gonzales wrote:

> > 
> Paul,
> When you say "multiple identical schemas" are they all separate
> explicit schemas?  Or are they all under a general 'public' schema.
> From my understanding, when you create a new db instance, it's under
> the public level schema by default unless you create an explicit
> schema and subsequently a db instance - or several - therein,
> effectively establishing sibling db instances belonging to a single
> schema, I know at least that data in the form of table access is
> allowed across the siblings.  I'd also assume that this would be the
> case for triggers and functions that could be identified or defined at
> the 'root' level 

Ummm.  In PostgreSQL schemas are contained within databases, not the
other way around.  It's cluster contains databases contains schemas
contains objects (tables, sequences, indexes, et. al.)

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Triggers and Multiple Schemas.

[Louis Gonzales]

Paul Newman wrote:

  
  
  
  
  Hi,
   We run with
multiple identical schemas in our db.
Each schema actually represents a clients db. What we’d like to do is
have a common schema where trigger functions and the like are held
whilst each
trigger defined against the tables is in there own particular schema.
This would
mean that there is one function per trigger type to maintain.
   
  However at
the moment we are placing the trigger
functions within each schema along with trigger itself. The reason is
that we don’t
know of a function or a variable that says “Give me the schema of the
trigger that is calling this function”. We are therefore having to
write
the function into every schema and then use set search_path =br1; as
the first
line. This is a real headache to us since we are intending on putting
200 –
300 schemas in one db.
   
  My question
is … is there such a function or
variable ?  …. Or is there a better for us to achieve this ?
   
  Regards
   
  Paul Newman
  

Paul,
When you say "multiple identical schemas" are they all separate
explicit schemas?  Or are they all under a general 'public' schema.
>From my understanding, when you create a new db instance, it's under
the public level schema by default unless you create an explicit schema
and subsequently a db instance - or several - therein, effectively
establishing sibling db instances belonging to a single schema, I know
at least that data in the form of table access is allowed across the
siblings.  I'd also assume that this would be the case for triggers and
functions that could be identified or defined at the 'root' level
schema.

Now I'm sure there is associated jargon with this type of hierarchical
or tiered schema layout, so please don't anybody shoot me because of my
analogy to 'root' level scenario.

I think this is a great opportunity for somebody to add additional
insight with their experience with utilizing explicit schemas, rather
than the default public schema.

We have to remember, that for every database instance, there is at
least one schema to which it belongs, meaning that a schema and is a db
container of sorts, there can be many database instances that exist in
1 schema to - typically public by default.

I know I'm opening up a big can of worms... but hey... let's have it ;)


begin:vcard
fn:louis
n:gonzales;louis
email;internet:[EMAIL PROTECTED]
tel;home:248.943.0144
tel;cell:248.943.0144
x-mozilla-html:TRUE
version:2.1
end:vcard


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Triggers question

[Michael Fuhr]

On Wed, Mar 01, 2006 at 02:22:15PM +0100, [EMAIL PROTECTED] wrote:
> I want to setup a trigger capable to return more than one record.

Your example doesn't show anything related to triggers so I think
you mean "function" instead of "trigger."  If the function can
return more than one row then it's a "set-returning" function.

> Example (table users contains 10 records):
> CREATE FUNCTION get_users() RETURNS
> SOME_TYPE AS '
> BEGIN
>return (SELECT * FROM users);
> ' LANGUAGE 'plpgsql';
> I can't figure out the correct Postgres type for SOME_TYPE (see above
> example).

This example's return type would be "SETOF users".  This particular
function would be simpler in SQL than in PL/pgSQL:

CREATE FUNCTION get_users() RETURNS SETOF users AS '
SELECT * FROM users;
' LANGUAGE sql STABLE;

Here's the PL/pgSQL version:

CREATE FUNCTION get_users() RETURNS SETOF users AS '
DECLARE
row  users%ROWTYPE;
BEGIN
FOR row IN SELECT * FROM users LOOP
RETURN NEXT row;
END LOOP;
RETURN;
END;
' LANGUAGE plpgsql STABLE;

You'd call the function as:

SELECT * FROM get_users();

For more information see "SQL Functions Returning Sets" (for SQL)
and "Control Structures" (for PL/pgSQL) in the documentation.  Here
are links to the documentation for 8.1, but use the documentation
for whatever version you're running:

http://www.postgresql.org/docs/8.1/interactive/xfunc-sql.html#AEN31646
http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html

-- 
Michael Fuhr

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] triggers, rules and alter table

[Richard Huxton]

Sergey Karin wrote:

Hi, List!

I'm using pg 8.1.0

this version allows to create triggers on INSERT, DELETE and UPDATE and also
allows to create rules on SELECT, UPDATE, INSERT and DELETE.
But I want to do some actions on ALTER TABLE ... ADD COLUMN and ALTER TABLE
... DROP COLUMN events. How I can handle that events in 8.1.x version?


You can't. There aren't any hooks to attach triggers to for those actions.

Could you wrap your ALTER TABLE commands in a function?

--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] triggers and SELECT

[Tino Wildenhain]

Sergey Karin schrieb:

Hi, List!

Are there any plans to realize triggers on SELECT in new versions of PG?


What should they do?

You can always use a rule for this...

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Triggers and Audit Trail

[Eric E]

Hi Marcus,

Marcus Couto wrote:

  
  
  
  Hi all. I'm new with PostgreSQL and
this is my first post, so easy on me... :)
   
  I'm thinking of using the native
procedural language and triggers to keep an audit trail. For editing
changes, we only keep a log of the modified fields and we create a
record for each modified value. The audit table record holds information like user, date/time,
table_name, field_name, old_value, new_value, type(delete, new, edit). I have a couple of questions: 
  

I wrote such an audit system and am using it production.  It works
reasonably well.  It was quite a bit of work to develop, and still has
some rough edges.

  Using triggers, is there a way to
loop through the fields of the OLD and NEW records? I haven't found a generic way to get the field
name and value that triggered the update other than hard coding if
statements to compare every field of the OLD and NEW records. 

I had this problem, and as Michael Fuhr mentioned you can't resolve it
in PL/PGSQL.  I ended up using PL/TCL because it was stable under 7.4
and it does the field dereferencing you need.  As of 8.0 and later
PL/PERL is also stable and I believe it does field dereferencing as
well. 


  Another issue is how to keep track
of the audit user since we share the same postgres user and our
application keeps track of the actual current user locally. Is there
some kind of way we can set the current user so that we're able to read
it from the trigger event? Other suggestions?

I looked into that as well, and it's pretty hard.  Most applications
that use only one database user but have multiple application-level
users are three-tier, and the apps tend to do logging themselves, often
using a separate loggin mechanism like log4j and friends.  So for that
part I'd either have your app write the user action into the
appropriate table, or look into retrieving the PK of your audit/history
table row, passing it back to your application and having your
application log the user after writing the row history table. 
Otherwise you're at the mercy of when and how your database connection
is opened (i.e., how long a session lasts).

Some other tips:
I use a PL/TCL trigger function to enumerate the table and fields, and
then call two functions that actually write the log of the action and
the row history table.  
some key lines from that TCL function:

switch $TG_op {
# do different things for different SQL commands
DELETE {}
INSERT {}
UPDATE {}
SELECT {}
default {}

# get the name of the table
spi_exec "select relname as trg_tablename from pg_class where
oid=$TG_relid;"

# loop over all the fields in the relation new getting field names and
values
foreach {fieldname fieldval} [array get NEW] {
# you can use this to assemble your SQL to insert into your row history
table (or pass it to a row-history-writer function as I do)
}

The functions that actually write the log run setuid (i.e. "Security of
definer" checkbox in pgAdmin or SECURITY DEFINER in PGSQL parlance). 
This means that the audit (actions) table and row history tables can be
stored in schemas not readable by users.

Also bear in mind when implementing an audit trail in this way that
you'll have to apply any changes in the tables you are auditing to the
tables that store your audit trail, and this can get complex as the
tables evolve.

There was also some audit code for Postgres written in C, but I
couldn't find much documentation for it, so I abandonded it.  I think a
comprehensive audit package for Postgres would be a great addition, but
sadly I lack the resources to contribute it.

Hope that helps,

Eric

Re: [GENERAL] Triggers and Audit Trail

[Michael Fuhr]

On Thu, Dec 29, 2005 at 11:44:26AM -0600, Jeff Amiel wrote:
> >Using triggers, is there a way to loop through the fields of the OLD 
> >and NEW records? I haven't found a generic way to get the field name 
> >and value that triggered the update other than hard coding if 
> >statements to compare every field of the OLD and NEW records.
> 
> We (my company) never found a way.  We ended up writing java code that 
> analyzed the catalog tables that generated the appropriate 'if' 
> statements in the trigger functions  for us

As far as I know you can't do this yet in PL/pgSQL, but you can in
other languages like PL/Perl and PL/Tcl.

-- 
Michael Fuhr

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Triggers and Audit Trail

[Jeff Amiel]

We (my company) never found a way.  We ended up writing java code that 
analyzed the catalog tables that generated the appropriate 'if' 
statements in the trigger functions  for us


Actuallywe tinkered with hitting the catalog tables inside our 
triggers, but for performance reasons, we generated the 'if' statements 
instead


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Triggers and Audit Trail

[Jeff Amiel]

Using triggers, is there a way to loop through the fields of the OLD 
and NEW records? I haven't found a generic way to get the field name 
and value that triggered the update other than hard coding if 
statements to compare every field of the OLD and NEW records.



We (my company) never found a way.  We ended up writing java code that 
analyzed the catalog tables that generated the appropriate 'if' 
statements in the trigger functions  for us


 
Another issue is how to keep track of the audit user since we share 
the same postgres user and our application keeps track of the actual 
current user locally. Is there some kind of way we can set the current 
user so that we're able to read it from the trigger event? Other 
suggestions?


Inside our application, when we grab a connection from our connection 
pool, the user information is populated into a termporary table that the 
audit triggers can then later read for any transactions on that 
connection. 


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] triggers/constraints?

[Terry Lee Tucker]

I believe you have a design problem. It seems to me that you need two tables; 
one with (id, path) and the other with (parent_id, path). Then you can use an 
UPDATE trigger on item which checks for a change in path. If it has changed, 
then you can update all those records in table "item2" where item2.parent_id 
matches item.id with the new prefix.

You generally do not want to update other records in the same table with a 
trigger. This will send you into a loop unless you take special measures.

HTH

On Friday 07 October 2005 04:26 pm, ako... saith:
> hello,
>
> consider a sample table:
>
> create table item (id serial, parent_id int, path varchar(256));
>
> "id" is a unique identifier of each row,
> "parent_id" is an id of another row in the same table or null
>
> what is the right way in postgresql to ensure that the "path" field of
> a given row is a prefix of the "path" field of the row referenced by
> the "parent_id" field? check constraints? triggers?
>
> please advice, i am new.
>
> thanks in advance
> konstantin
>
>
> ---(end of broadcast)---
> TIP 1: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to [EMAIL PROTECTED] so that your
>message can get through to the mailing list cleanly


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Triggers after a rule

[Jan Wieck]

On 9/28/2005 5:44 AM, Wijnand Wiersma wrote:


Hi list,

I am currently trying to give normal users some read access to some
tables in the database. I also need to give update access to one
column of one table.

I have the table contact, the user should not be able to read or
update anything in it, except for his own record. So I created the
view v_my_account. When the user selects * from it he only sees his
own record. That works great. I also made a rule:
CREATE RULE update_v_my_account AS ON UPDATE TO v_my_account
DO INSTEAD
UPDATE contact set pause=NEW.pause where username=USER;


You probably want that to be

  DO INSTEAD
  UPDATE contact set pause=NEW.pause where username=OLD.username;

This will still not allow the user to update other's records, because 
the internal querytree for the update will have the views where clause 
attached too and that limits the result set already.




This does not work since there are some triggers on the contact table
and the trigger function selects the contact table and I don't want to
give the user access to that.


You want the trigger functions to be declared SECURITY DEFINER.


Jan

--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Triggers after a rule

[Wijnand Wiersma]

2005/9/28, Richard Huxton :
> The solution is to mark your trigger functions with the "SECURITY
> DEFINER" attribute and create them as a user who can access relation
> "contact".
> http://www.postgresql.org/docs/8.0/static/sql-createfunction.html

Damn, I really really missed that one :-(
Thank you very much Richard!

Wijnand

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Triggers after a rule

[Richard Huxton]

Wijnand Wiersma wrote:

2005/9/28, Richard Huxton :


Wijnand Wiersma wrote:


I thought that when a rule is applied the triggers which are triggerd
would also have the same userid as the rule query, but I was wrong.


Can you show the (cut down) contents of your trigger? It's difficult to
see what you mean. I can't think of any way a trigger can provide
results to the user, so any selects within it should be safe enough.



The trigger function is very very long and touches tables the normal
user should not touch. I can't grant select, update and insert to the
users, there is only one user who has the rights to do that. Normal
users should work with the given views and those views are made so
that users only see information that is relevant for them.

database=> update v_my_account set pause='yes';
ERROR:  permission denied for relation contact
CONTEXT:  PL/pgSQL function "activate_contact" line 5 at select into variables

The rule update_v_my_account works and runs as the special user since
that user is owner of v_my_account. It is just strange that the
triggers run as my normal user.


Ah! Now I understand.

The solution is to mark your trigger functions with the "SECURITY 
DEFINER" attribute and create them as a user who can access relation 
"contact".

http://www.postgresql.org/docs/8.0/static/sql-createfunction.html

Of course, you'll need to make sure your trigger function doesn't allow 
your view restrictions to be bypassed.


Now - why does the trigger cause problems? Because it's accessing the 
"raw" table and not the view. The view doesn't add privileges to a user, 
the user is granted access to the view.


Ideally, you could define the trigger on the view, and just let all 
access go through there, but that's not possible (at the moment, anyway).


--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq