[GENERAL] Trigger function problem

[Liglio Cavalcante]

Hi,

I am trying to migrate data from a master table to her partitions. I am
using an update trigger to delete and insert into the master table, and so
an insert trigger on the master table redirects the inserted registers to
the respective parrtition table. The problem is that the update trigger is
not deleting the registers from the master table. I am using postgresql 9.5.

CREATE OR REPLACE FUNCTION tb_master_update_trigger()
RETURNS TRIGGER AS $$
BEGIN
DELETE FROM tb_master WHERE OLD.id_master = id_master ;
INSERT INTO tb_master VALUES (NEW.*); 
RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER update_tb_master_trigger 
BEFORE UPDATE ON tb_master
FOR EACH ROW EXECUTE PROCEDURE tb_master_update_trigger();

UPDATE tb_master SET id_master = id_master ;



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html


-- 
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] Trigger problems/questions

[Jim Fulton]

On Thu, Jun 15, 2017 at 1:56 PM, Tom Lane  wrote:

> Jim Fulton  writes:
> > I have an object database that's mirrored to a table with data in a JSONB
> > column.  Data are organized into "communities".  Community ids aren't
> > stored directly in content but can be found by recursively following
> > __parent__ properties. I want to be able to index content records on
> their
> > community ids.
>
> > (I originally tried to index functions that got ids, but apparently lying
> > about immutability is a bad idea and I suffered the consequences. :-])
>
> > I tried creating a trigger to populate a community_zoid property with a
> > community id when a record is inserted or updated.  The trigger calls a
> > recursive functions to get the community id.
> > ...
> > This scheme succeeds most of the time, but occasionally, it fails.
>
> Since your original idea failed, I suppose that the parent relationships
> are changeable?


Good question.

A few kinds of objects can, rarely, move in the hierarchy, and, they never
move between communities, so their community id never changes.

IDK WTF my indexing attempt.  I could build the index, then add an object
to the tree and it wouldn't be indexed.  This was in a staging database
where there were no other changes.


> What mechanism have you got in place to propagate a
> relationship change back down to the child records?
>

This is a non-issue, at least WRT community ids.  If I were, for example,
to index paths, it would be an issue for some objects, but I'm not at that
point yet.


> Also, this looks to have a race condition: if you search for a record's
> community id at about the same time that someone else is changing the
> parent linkage, you may get the old answer, but by the time you commit the
> record update that answer may be obsolete.  This is a problem because even
> if you had another trigger that was trying (in the someone else's session)
> to propagate new community ids back to affected records, it wouldn't think
> that the record you're working on needs a change, because it would also
> see the old version of that record.
>
> Solutions to the race problem usually involve either SELECT FOR UPDATE
> to lock rows involved in identifying the target record's community ID,
> or use of SERIALIZABLE to cause the whole transaction to fail if its
> results might be inconsistent.  Either one will add some complexity
> to your application code.
>

There's a global lock around all of the updates to the table.  (This isn't
as unreasonable as it sounds :), but anyway, that's outside the scope of
this discussion.)

Even if there was some kind of race, I'd still get a community id set, it
might be wrong, but it would be set.

regards, tom lane
>

Thanks.

Jim

-- 
Jim Fulton
http://jimfulton.info

Re: [GENERAL] Trigger problems/questions

[Tom Lane]

Jim Fulton  writes:
> I have an object database that's mirrored to a table with data in a JSONB
> column.  Data are organized into "communities".  Community ids aren't
> stored directly in content but can be found by recursively following
> __parent__ properties. I want to be able to index content records on their
> community ids.

> (I originally tried to index functions that got ids, but apparently lying
> about immutability is a bad idea and I suffered the consequences. :-])

> I tried creating a trigger to populate a community_zoid property with a
> community id when a record is inserted or updated.  The trigger calls a
> recursive functions to get the community id.
> ...
> This scheme succeeds most of the time, but occasionally, it fails.

Since your original idea failed, I suppose that the parent relationships
are changeable?  What mechanism have you got in place to propagate a
relationship change back down to the child records?

Also, this looks to have a race condition: if you search for a record's
community id at about the same time that someone else is changing the
parent linkage, you may get the old answer, but by the time you commit the
record update that answer may be obsolete.  This is a problem because even
if you had another trigger that was trying (in the someone else's session)
to propagate new community ids back to affected records, it wouldn't think
that the record you're working on needs a change, because it would also
see the old version of that record.

Solutions to the race problem usually involve either SELECT FOR UPDATE
to lock rows involved in identifying the target record's community ID,
or use of SERIALIZABLE to cause the whole transaction to fail if its
results might be inconsistent.  Either one will add some complexity
to your application code.

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] Trigger problems/questions

[Jim Fulton]

On Thu, Jun 15, 2017 at 1:44 PM, Adrian Klaver 
wrote:

> On 06/15/2017 10:23 AM, Jim Fulton wrote:
>

...


> I do not pretend to fully understand what the the triggers/functions are
> really doing,

but I did notice this:
>
> create or replace function populate_community_zoid_triggerf()
>
> ...
>
> new_zoid := NEW.state ->> 'community_zoid';
> zoid := find_community_zoid(
> NEW.zoid, NEW.class_name, NEW.state)::text;
> if zoid is null then
> if new_zoid is not null then
> NEW.state := NEW.state - 'community_zoid';
> end if;
> else
> if new_zoid is null or zoid != new_zoid then
> NEW.state :=
> NEW.state || ('{"community_zoid": ' || zoid || '}')::jsonb;
> end if;
> end if;
>
> ...
>
>
> Not sure what happens if zoid is null and new_zoid is null also?
>

Then then no change is needed and nothing happens, which is correct.


> Or if it matters?


Nope.

Thanks for looking.

Jim

-- 
Jim Fulton
http://jimfulton.info

Re: [GENERAL] Trigger problems/questions

[Adrian Klaver]

On 06/15/2017 10:23 AM, Jim Fulton wrote:
I have an object database that's mirrored to a table with data in a 
JSONB column.  Data are organized into "communities".  Community ids 
aren't stored directly in content but can be found by recursively 
following __parent__ properties. I want to be able to index content 
records on their community ids.


(I originally tried to index functions that got ids, but apparently 
lying about immutability is a bad idea and I suffered the consequences. :-])


I tried creating a trigger to populate a community_zoid property with a 
community id when a record is inserted or updated.  The trigger calls a 
recursive functions to get the community id.


Trigger: 
https://github.com/karlproject/karl/blob/master/karl/scripts/pgevolve.py#L261 



Trigger procedure: 
https://github.com/karlproject/karl/blob/master/karl/scripts/pgevolve.py#L236 



Function to find a community id: 
https://github.com/karlproject/karl/blob/master/karl/scripts/pgevolve.py#L209 



This scheme succeeds most of the time, but occasionally, it fails.

I can find records where it has failed with a query like:

   select zoid
   from newt
   where find_community_zoid(zoid, class_name, state) is not null
 and not state ? 'community_zoid';

If I update the records where it has failed:

   update newt set class_name=class_name
   where find_community_zoid(zoid, class_name, state) is not null
  and not state ? 'community_zoid';

Then retry the query above, I get 0 rows back. This would seem to 
indicate that the trigger is logically correct.


Questions:

  * Am I doing it wrong?  For example, is there some limitation on
trigger procedures that I'm violating?
  * If a trigger procedure errors, is the transaction aborted? (I would
hope so.)
  * Should I expect triggers to be reliable and rely on them for
database integrity?
  * Any suggestions on how to debug this?


I do not pretend to fully understand what the the triggers/functions are 
really doing, but I did notice this:


create or replace function populate_community_zoid_triggerf()

...

new_zoid := NEW.state ->> 'community_zoid';
zoid := find_community_zoid(
NEW.zoid, NEW.class_name, NEW.state)::text;
if zoid is null then
if new_zoid is not null then
NEW.state := NEW.state - 'community_zoid';
end if;
else
if new_zoid is null or zoid != new_zoid then
NEW.state :=
NEW.state || ('{"community_zoid": ' || zoid || '}')::jsonb;
end if;
end if;

...


Not sure what happens if zoid is null and new_zoid is null also?
Or if it matters?




Jim

--
Jim Fulton
http://jimfulton.info



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

[GENERAL] Trigger problems/questions

[Jim Fulton]

I have an object database that's mirrored to a table with data in a JSONB
column.  Data are organized into "communities".  Community ids aren't
stored directly in content but can be found by recursively following
__parent__ properties. I want to be able to index content records on their
community ids.

(I originally tried to index functions that got ids, but apparently lying
about immutability is a bad idea and I suffered the consequences. :-])

I tried creating a trigger to populate a community_zoid property with a
community id when a record is inserted or updated.  The trigger calls a
recursive functions to get the community id.

Trigger: https://github.com/karlproject/karl/blob/master/
karl/scripts/pgevolve.py#L261

Trigger procedure: https://github.com/karlproject/karl/blob/master/
karl/scripts/pgevolve.py#L236

Function to find a community id: https://github.com/
karlproject/karl/blob/master/karl/scripts/pgevolve.py#L209

This scheme succeeds most of the time, but occasionally, it fails.

I can find records where it has failed with a query like:

  select zoid
  from newt
  where find_community_zoid(zoid, class_name, state) is not null
and not state ? 'community_zoid';

If I update the records where it has failed:

  update newt set class_name=class_name
  where find_community_zoid(zoid, class_name, state) is not null
 and not state ? 'community_zoid';

Then retry the query above, I get 0 rows back. This would seem to indicate
that the trigger is logically correct.

Questions:

   - Am I doing it wrong?  For example, is there some limitation on trigger
   procedures that I'm violating?
   - If a trigger procedure errors, is the transaction aborted? (I would
   hope so.)
   - Should I expect triggers to be reliable and rely on them for database
   integrity?
   - Any suggestions on how to debug this?

Jim

-- 
Jim Fulton
http://jimfulton.info

Re: [GENERAL] Trigger based logging alternative to table_log

[Glyn Astill]

> From: Jeff Janes <jeff.ja...@gmail.com>
> To: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
> Sent: Monday, 27 March 2017, 18:08
> Subject: [GENERAL] Trigger based logging alternative to table_log
>
> I have some code which uses table_log 
> (http://pgfoundry.org/projects/tablelog/) to keep a log of changes to 
> selected tables.  I don't use the restore part, just the logging part. 
>
> It creates a new table for each table being logged, with several additional 
> columns, and adds triggers to insert rows in the new table for changes in the 
> original.
> 
> The problem is that table_log hasn't been maintained in nearly 10 years, and 
> pgfoundry itself seems to have one foot in the grave and one on a banana peel.
>
>There are several other systems out there which store the data in hstore or 
>json, which I would probably use if doing this from scratch.  But I'd rather 
>preserve the existing log tables than either throw away that data, or port it 
>over to a new format.
>
>Is there any better-maintained code out there which would be compatible with 
>the existing schema used by table_log?

I was in exactly the same situation a few years ago.  As you say ideally we'd 
move away from table_log - but when the users are used to doing things the 
table_log way and they like it...

I have a slightly more up to date fork (here: 
https://github.com/glynastill/pg_table_audit), which as I recall works fine 
with 9.6.  In general the whole thing would benefit an overhaul, but I think 
the effort of moving to a better format would be less.


I also wrote a pl/pgsql version as mentioned by Felix, but I wasn't ever 
particularly happy it so stuck with the above fork with the intention of 
switching away to a json format eventually.

Glyn


-- 
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] Trigger based logging alternative to table_log

[Thomas Kellerer]

Jeff Janes schrieb am 27.03.2017 um 19:07:

I have some code which uses table_log
(http://pgfoundry.org/projects/tablelog/) to keep a log of changes to
selected tables.  I don't use the restore part, just the logging
part.

It creates a new table for each table being logged, with several
additional columns, and adds triggers to insert rows in the new table
for changes in the original.

The problem is that table_log hasn't been maintained in nearly 10
years, and pgfoundry itself seems to have one foot in the grave and
one on a banana peel.

There are several other systems out there which store the data in
hstore or json, which I would probably use if doing this from
scratch.  But I'd rather preserve the existing log tables than either
throw away that data, or port it over to a new format.

Is there any better-maintained code out there which would be
compatible with the existing schema used by table_log?


Logical replication maybe?

I don't know which programming language you are using, but the JDBC driver has 
a nice example:

https://jdbc.postgresql.org/documentation/head/replication.html

The upside to a trigger based solution is, that it's faster.
The downside is, that the replication "consumer" has to be running to ensure 
the logging





--
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] Trigger based logging alternative to table_log

[Felix Kunde]

> I have some code which uses table_log 
> (http://pgfoundry.org/projects/tablelog/) to keep a log of changes to 
> selected tables.  
> I don't use the restore part, just the logging part.  
> It creates a new table for each table being logged, with several additional 
> columns, and adds triggers to insert rows in the new table for changes in the 
> original.
> The problem is that table_log hasn't been maintained in nearly 10 years, and 
> pgfoundry itself seems to have one foot in the grave and one on a banana peel.
> There are several other systems out there which store the data in hstore or 
> json, which I would probably use if doing this from scratch.  But I'd rather 
> preserve the existing log tables than either throw away that data, or port it 
> over to a new format.
> Is there any better-maintained code out there which would be compatible with 
> the existing schema used by table_log?
 
>Cheers,
>Jeff

Afaik, there is no compatible solution. If tablelog works for you then keep it. 
Do you miss a feature or why do you worry about the unmaintained code base? I 
think, if there would be a problem with any new version that the developer 
would fix it. There is also an existing github repo 
(https://github.com/glynastill/table_log_pl). 

Recently, I have done a comparison of different audit tools to check how good 
my creation (pgMemento) works compared to the others. So I know how most of 
them work. tablelog for example logs both OLD and NEW. So you got all your data 
twice. Other solutions log either OLD of NEW. tablelog uses only one timestamp 
field whereas others using two (or a range).

As tablelog is using history tables with relational layout I would suggest to 
consider other extensions that do a similar thing. If you are interested in 
only logging the data you might check out the temporal_tables extension 
(http://pgxn.org/dist/temporal_tables/). In my test it had the least impact to 
write operations and disk consumption.

Using hstore or json for logging might sound cool in the first place, but it 
only has its benefits if you don't want to adapt the auditing behaviour to 
schema changes (like new columns etc.). With pgMemento I decided to go for 
jsonb but after many hours of programming complex restoring functions I can say 
that my only real argument of using it now, is that I only log values of 
changed fields. I like that but it makes the trigger overhead bigger.

Greetings from Berlin
Felix


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

[GENERAL] Trigger based logging alternative to table_log

[Jeff Janes]

I have some code which uses table_log (
http://pgfoundry.org/projects/tablelog/) to keep a log of changes to
selected tables.  I don't use the restore part, just the logging part.

It creates a new table for each table being logged, with several additional
columns, and adds triggers to insert rows in the new table for changes in
the original.

The problem is that table_log hasn't been maintained in nearly 10 years,
and pgfoundry itself seems to have one foot in the grave and one on a
banana peel.

There are several other systems out there which store the data in hstore or
json, which I would probably use if doing this from scratch.  But I'd
rather preserve the existing log tables than either throw away that data,
or port it over to a new format.

Is there any better-maintained code out there which would be compatible
with the existing schema used by table_log?

Cheers,

Jeff

Re: [GENERAL] Trigger is not working for Inserts from the application

[Kiran]

Hi Jim,

The issue is now resolved thanks to Tom and Adrian.

regards
Kiran

On Sun, Sep 11, 2016 at 4:16 AM, Jim Nasby  wrote:

> On 9/10/16 6:03 AM, Kiran wrote:
>
>> If I insert a record from my application using following code :
>> db.myschema.cf_question.insert({
>> cf_question_type_id:request.payload.type_id,
>> cf_question_category_id:request.payload.cat_id,
>> lang:request.payload.lang,
>> body:request.payload.body
>> }
>> The above app code inserts the record in the DB, but the respective
>> trigger
>> in the database is not triggered hence the "weighted_tsv" columns is empty
>> for this record.
>>
>> But if I insert another record from the postgre's psql, it will insert and
>> the respective trigger is working perfectly.
>>
>> What could be the problem ? Why trigger is not working if I insert from
>> the
>> application ? Am I doing anything wrong ?
>> Any help would be really really appreciated.
>>
>
> Without knowing what that app code is doing it's impossible to know. Try
> turning on logging of all statements (log_statement = ALL) and see what
> queries the app is actually running.
> --
> Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
> Experts in Analytics, Data Architecture and PostgreSQL
> Data in Trouble? Get it in Treble! http://BlueTreble.com
> 855-TREBLE2 (855-873-2532)   mobile: 512-569-9461
>

Re: [GENERAL] Trigger is not working for Inserts from the application

[Jim Nasby]

On 9/10/16 6:03 AM, Kiran wrote:

If I insert a record from my application using following code :
db.myschema.cf_question.insert({
cf_question_type_id:request.payload.type_id,
cf_question_category_id:request.payload.cat_id,
lang:request.payload.lang,
body:request.payload.body
}
The above app code inserts the record in the DB, but the respective trigger
in the database is not triggered hence the "weighted_tsv" columns is empty
for this record.

But if I insert another record from the postgre's psql, it will insert and
the respective trigger is working perfectly.

What could be the problem ? Why trigger is not working if I insert from the
application ? Am I doing anything wrong ?
Any help would be really really appreciated.


Without knowing what that app code is doing it's impossible to know. Try 
turning on logging of all statements (log_statement = ALL) and see what 
queries the app is actually running.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461


--
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] Trigger is not working for Inserts from the application

[Kiran]

Dear Tom, Adrian,

Excellent catch!
Thank you very very much to both. It is resolved now. I can sleep now.
Good night from Sweden.

regards
Kiran


On Sat, Sep 10, 2016 at 11:18 PM, Adrian Klaver 
wrote:

> On 09/10/2016 02:09 PM, Kiran wrote:
>
>> Hi Adrian,
>>
>> The JSON value is inserted into a column in the database which I can see.
>> But the trigger which has to convert this JSON value in not tsvector
>> column is not updating that column.
>>
>
> As Tom explained, in your trigger function you have:
>
> to_tsvector('swedish',coalesce(New.body->>'qtext','')::text);
>
> In the body JSON you are passing in:
>
> $4 = '{"name": "Do you like Pizza ?", "type": "cat", "store":
> [{"labeltext": "Yes", "labelvalue": 1}, {"labeltext": "No", "labelvalue":
> 2}, {"labeltext": "Other", "labelvalue": 3}, {"labeltext": "Don''t know",
> "labelvalue": 4}], "target": {"place": "Sweden"}, "askfreq": "once",
> "whydesc": "Because I like it :)"}'
>
> there is not 'qtext', so New.body->>'qtext' is getting you NULL which the
> COALESCE is turning into '' which is making weighted_tsv look empty.
>
>
>> regards
>>
>> On Sat, Sep 10, 2016 at 10:27 PM, Tom Lane > > wrote:
>>
>> Kiran >
>> writes:
>> > LOG:  execute : INSERT INTO "myschema"."cf_question"
>> > ("cf_question_type_id", "cf_question_category_id", "lang", "body")
>> VALUES
>> > ($1, $2, $3, $4) RETURNING *
>> > DETAIL:  parameters: $1 = '1', $2 = '9', $3 = 'swedish', $4 =
>> '{"name": "Do
>> > you like Pizza ?", "type": "cat", "store": [{"labeltext": "Yes",
>> > "labelvalue": 1}, {"labeltext": "No", "labelvalue": 2},
>> {"labeltext":
>> > "Other", "labelvalue": 3}, {"labeltext": "Don''t know",
>> "labelvalue": 4}],
>> > "target": {"place": "Sweden"}, "askfreq": "once", "whydesc":
>> "Because I
>> > like it :)"}'
>>
>> Well, the obvious comment on that is "that JSON value hasn't got any
>> qtext
>> field".  So the ->> operator is returning null, the coalesce() is
>> replacing that with an empty string, and you end up with an empty
>> tsvector column.
>>
>> regards, tom lane
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Trigger is not working for Inserts from the application

[Adrian Klaver]

On 09/10/2016 02:09 PM, Kiran wrote:

Hi Adrian,

The JSON value is inserted into a column in the database which I can see.
But the trigger which has to convert this JSON value in not tsvector
column is not updating that column.


As Tom explained, in your trigger function you have:

to_tsvector('swedish',coalesce(New.body->>'qtext','')::text);

In the body JSON you are passing in:

$4 = '{"name": "Do you like Pizza ?", "type": "cat", "store": 
[{"labeltext": "Yes", "labelvalue": 1}, {"labeltext": "No", 
"labelvalue": 2}, {"labeltext": "Other", "labelvalue": 3}, {"labeltext": 
"Don''t know", "labelvalue": 4}], "target": {"place": "Sweden"}, 
"askfreq": "once", "whydesc": "Because I like it :)"}'


there is not 'qtext', so New.body->>'qtext' is getting you NULL which 
the COALESCE is turning into '' which is making weighted_tsv look empty.




regards

On Sat, Sep 10, 2016 at 10:27 PM, Tom Lane > wrote:

Kiran >
writes:
> LOG:  execute : INSERT INTO "myschema"."cf_question"
> ("cf_question_type_id", "cf_question_category_id", "lang", "body") VALUES
> ($1, $2, $3, $4) RETURNING *
> DETAIL:  parameters: $1 = '1', $2 = '9', $3 = 'swedish', $4 = '{"name": 
"Do
> you like Pizza ?", "type": "cat", "store": [{"labeltext": "Yes",
> "labelvalue": 1}, {"labeltext": "No", "labelvalue": 2}, {"labeltext":
> "Other", "labelvalue": 3}, {"labeltext": "Don''t know", "labelvalue": 4}],
> "target": {"place": "Sweden"}, "askfreq": "once", "whydesc": "Because I
> like it :)"}'

Well, the obvious comment on that is "that JSON value hasn't got any
qtext
field".  So the ->> operator is returning null, the coalesce() is
replacing that with an empty string, and you end up with an empty
tsvector column.

regards, tom lane





--
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] Trigger is not working for Inserts from the application

[Kiran]

Hi Adrian,

The JSON value is inserted into a column in the database which I can see.
But the trigger which has to convert this JSON value in not tsvector column
is not updating that column.

regards

On Sat, Sep 10, 2016 at 10:27 PM, Tom Lane  wrote:

> Kiran  writes:
> > LOG:  execute : INSERT INTO "myschema"."cf_question"
> > ("cf_question_type_id", "cf_question_category_id", "lang", "body") VALUES
> > ($1, $2, $3, $4) RETURNING *
> > DETAIL:  parameters: $1 = '1', $2 = '9', $3 = 'swedish', $4 = '{"name":
> "Do
> > you like Pizza ?", "type": "cat", "store": [{"labeltext": "Yes",
> > "labelvalue": 1}, {"labeltext": "No", "labelvalue": 2}, {"labeltext":
> > "Other", "labelvalue": 3}, {"labeltext": "Don''t know", "labelvalue":
> 4}],
> > "target": {"place": "Sweden"}, "askfreq": "once", "whydesc": "Because I
> > like it :)"}'
>
> Well, the obvious comment on that is "that JSON value hasn't got any qtext
> field".  So the ->> operator is returning null, the coalesce() is
> replacing that with an empty string, and you end up with an empty
> tsvector column.
>
> regards, tom lane
>

Re: [GENERAL] Trigger is not working for Inserts from the application

[Adrian Klaver]

On 09/10/2016 02:02 PM, Kiran wrote:

Hi Tom,

I have checked and the trigger is not disabled.

and \drds results
Role=blank
database = mydatabase
settings = default_text_search_config=pg_catalog.swedish

Any other tips or suggestions please.


Did you see this post?:

https://www.postgresql.org/message-id/10840.1473539270%40sss.pgh.pa.us



regards
Kiran



On Sat, Sep 10, 2016 at 9:26 PM, Tom Lane > wrote:

Kiran >
writes:
> But I do not know how to check whether the application is subject to
> different ALTER USER  SET parameters.

psql's \drds ("display role/database SETs") would help.

BTW, have you checked that the trigger is not disabled, and that there
isn't another trigger undoing its work?  (psql's \d command on the table
should show these things.)

regards, tom lane





--
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] Trigger is not working for Inserts from the application

[Kiran]

Hi Tom,

I have checked and the trigger is not disabled.

and \drds results
Role=blank
database = mydatabase
settings = default_text_search_config=pg_catalog.swedish

Any other tips or suggestions please.

regards
Kiran



On Sat, Sep 10, 2016 at 9:26 PM, Tom Lane  wrote:

> Kiran  writes:
> > But I do not know how to check whether the application is subject to
> > different ALTER USER  SET parameters.
>
> psql's \drds ("display role/database SETs") would help.
>
> BTW, have you checked that the trigger is not disabled, and that there
> isn't another trigger undoing its work?  (psql's \d command on the table
> should show these things.)
>
> regards, tom lane
>

Re: [GENERAL] Trigger is not working for Inserts from the application

[Tom Lane]

Kiran  writes:
> LOG:  execute : INSERT INTO "myschema"."cf_question"
> ("cf_question_type_id", "cf_question_category_id", "lang", "body") VALUES
> ($1, $2, $3, $4) RETURNING *
> DETAIL:  parameters: $1 = '1', $2 = '9', $3 = 'swedish', $4 = '{"name": "Do
> you like Pizza ?", "type": "cat", "store": [{"labeltext": "Yes",
> "labelvalue": 1}, {"labeltext": "No", "labelvalue": 2}, {"labeltext":
> "Other", "labelvalue": 3}, {"labeltext": "Don''t know", "labelvalue": 4}],
> "target": {"place": "Sweden"}, "askfreq": "once", "whydesc": "Because I
> like it :)"}'

Well, the obvious comment on that is "that JSON value hasn't got any qtext
field".  So the ->> operator is returning null, the coalesce() is
replacing that with an empty string, and you end up with an empty
tsvector column.

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] Trigger is not working for Inserts from the application

[Kiran]

Hi Adrian,

This is the exact log in the file as it appears:

DETAIL:  parameters: $1 = '', $2 = ''
LOG:  connection received: host=localhost port=53284
LOG:  connection authorized: user=deva database=mydatabase
LOG:  connection received: host=localhost port=53285
LOG:  connection authorized: user=deva database=mydatabase
LOG:  execute : INSERT INTO "myschema"."cf_question"
("cf_question_type_id", "cf_question_category_id", "lang", "body") VALUES
($1, $2, $3, $4) RETURNING *
DETAIL:  parameters: $1 = '1', $2 = '9', $3 = 'swedish', $4 = '{"name": "Do
you like Pizza ?", "type": "cat", "store": [{"labeltext": "Yes",
"labelvalue": 1}, {"labeltext": "No", "labelvalue": 2}, {"labeltext":
"Other", "labelvalue": 3}, {"labeltext": "Don''t know", "labelvalue": 4}],
"target": {"place": "Sweden"}, "askfreq": "once", "whydesc": "Because I
like it :)"}'
LOG:  execute : select * from "myschema"."cf_user" where
cf_user_id=$1
DETAIL:  parameters: $1 = '$2a$13$g8VXS3Bt3489I'
*LOG:  LOG for TRIGER called on cf_question*
STATEMENT:  INSERT INTO "monolith"."cf_question" ("cf_question_type_id",
"cf_question_category_id", "lang", "body") VALUES
($1, $2, $3, $4) RETURNING *


As you can see from the above, there is a LOG which says Trigger called.
This is the statement inside the function which means the trigger is
firing, but why the subsequent column is not updated, I can't understand.

Also, I am using log_statement='all' setting. Anything wrong you finding
which I can't recognise  in the log statements ?

regards
Kiran

On Sat, Sep 10, 2016 at 9:08 PM, Adrian Klaver 
wrote:

> On 09/10/2016 11:39 AM, Kiran wrote:
>
>> Hi Adrian,
>>
>> Thanks for your response.
>> I tried with logging. The application is inserting the record that I am
>> passing into the database. But the trigger is not firing.
>>
>
> What is the text of the complete statement as it appears in the logs?
>
> When you do the INSERT the other fields are the same in the database as in
> the row, after the INSERT?
>
> Related to second question, is the the INSERT being done in an explicit
> transaction eg. BEGIN; INSERT something; and if so is there a COMMIT at the
> end?
>
> Have you tried with log_statement = 'all' to see if there are non-mod
> statements running at the same time?
>
>
> I have been looking into this issue since morning with out any positive
>> outcome :(.
>> If you have any other tips, it will be really helpful.
>>
>> regards
>> Kiran
>>
>> On Sat, Sep 10, 2016 at 3:22 PM, Adrian Klaver
>> > wrote:
>>
>> On 09/10/2016 03:59 AM, Kiran wrote:
>>
>> Hi,
>>
>> *Problem background :*
>> I have a *function in the DB* as follows
>> CREATE FUNCTION question_tsv_trigger() RETURNS trigger AS
>> $BODY$
>> begin
>> New.weighted_tsv :=
>> to_tsvector('swedish',coalesce(New.body->>'qtext','')::text);
>> RAISE NOTICE 'TRIGER called on %', TG_TABLE_NAME;
>> return New;
>> end
>> $BODY$
>> LANGUAGE plpgsql VOLATILE
>> COST 100;
>>
>> **Trigger in the DB:**
>> CREATE TRIGGER insert_upd_question_tsvector BEFORE INSERT OR
>> UPDATE
>> ON myschema.cf_question
>> FOR EACH ROW EXECUTE PROCEDURE question_tsv_trigger();
>>
>> If I insert a record from my application using following code :
>> db.myschema.cf_question.insert({
>> cf_question_type_id:request.pa yload.type_id,
>> cf_question_category_id:request.payload.cat_id,
>> lang:request.payload.lang,
>> body:request.payload.body
>> }
>>
>> The above app code inserts the record in the DB, but the
>> respective trigger
>> in the database is not triggered hence the "weighted_tsv"
>> columns is empty
>> for this record.
>>
>> But if I insert a record from the postgres psql, it will insert
>> and
>> the respective trigger is working perfectly.
>>
>> What could be the problem ? Why trigger is not working if I
>> insert from the
>> application ? Am I doing anything wrong ?
>> Any help would be really really appreciated.
>>
>>
>> If you have not, turn on log_statement:
>>
>> https://www.postgresql.org/docs/9.5/static/runtime-config-
>> logging.html#RUNTIME-CONFIG-LOGGING-WHAT
>> > logging.html#RUNTIME-CONFIG-LOGGING-WHAT>
>>
>> Then check your log to see what the application is sending to the
>> database.
>>
>>
>> Thanks
>> Kiran
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Trigger is not working for Inserts from the application

[Tom Lane]

Kiran  writes:
> But I do not know how to check whether the application is subject to
> different ALTER USER  SET parameters.

psql's \drds ("display role/database SETs") would help.

BTW, have you checked that the trigger is not disabled, and that there
isn't another trigger undoing its work?  (psql's \d command on the table
should show these things.)

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] Trigger is not working for Inserts from the application

[Adrian Klaver]

On 09/10/2016 11:39 AM, Kiran wrote:

Hi Adrian,

Thanks for your response.
I tried with logging. The application is inserting the record that I am
passing into the database. But the trigger is not firing.


What is the text of the complete statement as it appears in the logs?

When you do the INSERT the other fields are the same in the database as 
in the row, after the INSERT?


Related to second question, is the the INSERT being done in an explicit 
transaction eg. BEGIN; INSERT something; and if so is there a COMMIT at 
the end?


Have you tried with log_statement = 'all' to see if there are non-mod 
statements running at the same time?




I have been looking into this issue since morning with out any positive
outcome :(.
If you have any other tips, it will be really helpful.

regards
Kiran

On Sat, Sep 10, 2016 at 3:22 PM, Adrian Klaver
> wrote:

On 09/10/2016 03:59 AM, Kiran wrote:

Hi,

*Problem background :*
I have a *function in the DB* as follows
CREATE FUNCTION question_tsv_trigger() RETURNS trigger AS
$BODY$
begin
New.weighted_tsv :=
to_tsvector('swedish',coalesce(New.body->>'qtext','')::text);
RAISE NOTICE 'TRIGER called on %', TG_TABLE_NAME;
return New;
end
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

**Trigger in the DB:**
CREATE TRIGGER insert_upd_question_tsvector BEFORE INSERT OR UPDATE
ON myschema.cf_question
FOR EACH ROW EXECUTE PROCEDURE question_tsv_trigger();

If I insert a record from my application using following code :
db.myschema.cf_question.insert({
cf_question_type_id:request.pa yload.type_id,
cf_question_category_id:request.payload.cat_id,
lang:request.payload.lang,
body:request.payload.body
}

The above app code inserts the record in the DB, but the
respective trigger
in the database is not triggered hence the "weighted_tsv"
columns is empty
for this record.

But if I insert a record from the postgres psql, it will insert and
the respective trigger is working perfectly.

What could be the problem ? Why trigger is not working if I
insert from the
application ? Am I doing anything wrong ?
Any help would be really really appreciated.


If you have not, turn on log_statement:


https://www.postgresql.org/docs/9.5/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT



Then check your log to see what the application is sending to the
database.


Thanks
Kiran



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





--
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] Trigger is not working for Inserts from the application

[Kiran]

Dear Tom,

Thanks for your detailed reply.

(1) Application is connecting to the same database.
(2) Application is inserting to the same table without much luck with the
trigger.
(3) Application is issuing the right insert command.
(4) I am not able to check this, is there any way I can check this?

I have enabled the logging of all the statements in the conf file and  can
see the logs for connection, statements. I have raised the LOG and I can
see the logs in the file without any errors.

But I do not know how to check whether the application is subject to
different ALTER USER  SET parameters. Could you please give me some
reference to which I can refer for this type of error checking or any other
tips to solve this issue. I have been looking into this since morning
without any positive outcome :(

Thanks once again.

regards
Kiran







On Sat, Sep 10, 2016 at 5:21 PM, Tom Lane  wrote:

> Adrian Klaver  writes:
> > On 09/10/2016 03:59 AM, Kiran wrote:
> >> What could be the problem ? Why trigger is not working if I insert from
> the
> >> application ? Am I doing anything wrong ?
>
> > If you have not, turn on log_statement:
> > https://www.postgresql.org/docs/9.5/static/runtime-
> config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT
> > Then check your log to see what the application is sending to the
> database.
>
> At a slightly higher level: everything you've shown us looks fine,
> therefore the problem is in something you didn't show us.  I've seen
> people have problems like this for all sorts of reasons, eg
>
> 1. Application isn't connecting to the same database as your manual
> sessions.  Maybe not even the same server.
>
> 2. Application is using a different search_path setting and therefore
> touching a different table (same name but other schema).
>
> 3. Application isn't issuing the command you think it is, or is failing
> to commit it.
>
> 4. Trigger is firing as expected but something else is overriding its
> change to the data.
>
> Adrian's suggestion of watching log_statement output would help debug
> some of these cases; possibly log_connections would help with others.
> I'd also suggest checking to see if the application is subject to
> different ALTER USER ... SET parameters than your manual session.
> Lastly, RAISE NOTICE is a tried-and-true method of checking whether
> a trigger is firing, but it's not too helpful for debugging queries
> from applications because they invariably drop notices on the floor.
> I'd try RAISE LOG instead, and again watch the server log to see what
> the application is really doing.
>
> regards, tom lane
>

Re: [GENERAL] Trigger is not working for Inserts from the application

[Kiran]

Hi Adrian,

Thanks for your response.
I tried with logging. The application is inserting the record that I am
passing into the database. But the trigger is not firing.
I have been looking into this issue since morning with out any positive
outcome :(.
If you have any other tips, it will be really helpful.

regards
Kiran

On Sat, Sep 10, 2016 at 3:22 PM, Adrian Klaver 
wrote:

> On 09/10/2016 03:59 AM, Kiran wrote:
>
>> Hi,
>>
>> *Problem background :*
>> I have a *function in the DB* as follows
>> CREATE FUNCTION question_tsv_trigger() RETURNS trigger AS
>> $BODY$
>> begin
>> New.weighted_tsv :=
>> to_tsvector('swedish',coalesce(New.body->>'qtext','')::text);
>> RAISE NOTICE 'TRIGER called on %', TG_TABLE_NAME;
>> return New;
>> end
>> $BODY$
>> LANGUAGE plpgsql VOLATILE
>> COST 100;
>>
>> **Trigger in the DB:**
>> CREATE TRIGGER insert_upd_question_tsvector BEFORE INSERT OR UPDATE
>> ON myschema.cf_question
>> FOR EACH ROW EXECUTE PROCEDURE question_tsv_trigger();
>>
>> If I insert a record from my application using following code :
>> db.myschema.cf_question.insert({
>> cf_question_type_id:request.payload.type_id,
>> cf_question_category_id:request.payload.cat_id,
>> lang:request.payload.lang,
>> body:request.payload.body
>> }
>>
>> The above app code inserts the record in the DB, but the respective
>> trigger
>> in the database is not triggered hence the "weighted_tsv" columns is empty
>> for this record.
>>
>> But if I insert a record from the postgres psql, it will insert and
>> the respective trigger is working perfectly.
>>
>> What could be the problem ? Why trigger is not working if I insert from
>> the
>> application ? Am I doing anything wrong ?
>> Any help would be really really appreciated.
>>
>
> If you have not, turn on log_statement:
>
> https://www.postgresql.org/docs/9.5/static/runtime-config-
> logging.html#RUNTIME-CONFIG-LOGGING-WHAT
>
> Then check your log to see what the application is sending to the database.
>
>
>> Thanks
>> Kiran
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Trigger is not working for Inserts from the application

[Tom Lane]

Adrian Klaver  writes:
> On 09/10/2016 03:59 AM, Kiran wrote:
>> What could be the problem ? Why trigger is not working if I insert from the
>> application ? Am I doing anything wrong ?

> If you have not, turn on log_statement:
> https://www.postgresql.org/docs/9.5/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT
> Then check your log to see what the application is sending to the database.

At a slightly higher level: everything you've shown us looks fine,
therefore the problem is in something you didn't show us.  I've seen
people have problems like this for all sorts of reasons, eg

1. Application isn't connecting to the same database as your manual
sessions.  Maybe not even the same server.

2. Application is using a different search_path setting and therefore
touching a different table (same name but other schema).

3. Application isn't issuing the command you think it is, or is failing
to commit it.

4. Trigger is firing as expected but something else is overriding its
change to the data.

Adrian's suggestion of watching log_statement output would help debug
some of these cases; possibly log_connections would help with others.
I'd also suggest checking to see if the application is subject to
different ALTER USER ... SET parameters than your manual session.
Lastly, RAISE NOTICE is a tried-and-true method of checking whether
a trigger is firing, but it's not too helpful for debugging queries
from applications because they invariably drop notices on the floor.
I'd try RAISE LOG instead, and again watch the server log to see what
the application is really doing.

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] Trigger is not working for Inserts from the application

[Adrian Klaver]

On 09/10/2016 03:59 AM, Kiran wrote:

Hi,

*Problem background :*
I have a *function in the DB* as follows
CREATE FUNCTION question_tsv_trigger() RETURNS trigger AS
$BODY$
begin
New.weighted_tsv :=
to_tsvector('swedish',coalesce(New.body->>'qtext','')::text);
RAISE NOTICE 'TRIGER called on %', TG_TABLE_NAME;
return New;
end
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

**Trigger in the DB:**
CREATE TRIGGER insert_upd_question_tsvector BEFORE INSERT OR UPDATE
ON myschema.cf_question
FOR EACH ROW EXECUTE PROCEDURE question_tsv_trigger();

If I insert a record from my application using following code :
db.myschema.cf_question.insert({
cf_question_type_id:request.payload.type_id,
cf_question_category_id:request.payload.cat_id,
lang:request.payload.lang,
body:request.payload.body
}

The above app code inserts the record in the DB, but the respective trigger
in the database is not triggered hence the "weighted_tsv" columns is empty
for this record.

But if I insert a record from the postgres psql, it will insert and
the respective trigger is working perfectly.

What could be the problem ? Why trigger is not working if I insert from the
application ? Am I doing anything wrong ?
Any help would be really really appreciated.


If you have not, turn on log_statement:

https://www.postgresql.org/docs/9.5/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT

Then check your log to see what the application is sending to the database.



Thanks
Kiran



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

[GENERAL] Trigger is not working for Inserts from the application

[Kiran]

Hi,

*Problem background :*
I have a *function in the DB* as follows
CREATE FUNCTION question_tsv_trigger() RETURNS trigger AS
$BODY$
begin
New.weighted_tsv :=
to_tsvector('swedish',coalesce(New.body->>'qtext','')::text);
RAISE NOTICE 'TRIGER called on %', TG_TABLE_NAME;
return New;
end
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

**Trigger in the DB:**
CREATE TRIGGER insert_upd_question_tsvector BEFORE INSERT OR UPDATE
ON myschema.cf_question
FOR EACH ROW EXECUTE PROCEDURE question_tsv_trigger();

If I insert a record from my application using following code :
db.myschema.cf_question.insert({
cf_question_type_id:request.payload.type_id,
cf_question_category_id:request.payload.cat_id,
lang:request.payload.lang,
body:request.payload.body
}
The above app code inserts the record in the DB, but the respective trigger
in the database is not triggered hence the "weighted_tsv" columns is empty
for this record.

But if I insert another record from the postgre's psql, it will insert and
the respective trigger is working perfectly.

What could be the problem ? Why trigger is not working if I insert from the
application ? Am I doing anything wrong ?
Any help would be really really appreciated.

Thanks
Kiran

[GENERAL] Trigger is not working for Inserts from the application

[Kiran]

Hi,

*Problem background :*
I have a *function in the DB* as follows
CREATE FUNCTION question_tsv_trigger() RETURNS trigger AS
$BODY$
begin
New.weighted_tsv :=
to_tsvector('swedish',coalesce(New.body->>'qtext','')::text);
RAISE NOTICE 'TRIGER called on %', TG_TABLE_NAME;
return New;
end
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

**Trigger in the DB:**
CREATE TRIGGER insert_upd_question_tsvector BEFORE INSERT OR UPDATE
ON myschema.cf_question
FOR EACH ROW EXECUTE PROCEDURE question_tsv_trigger();

If I insert a record from my application using following code :
db.myschema.cf_question.insert({
cf_question_type_id:request.payload.type_id,
cf_question_category_id:request.payload.cat_id,
lang:request.payload.lang,
body:request.payload.body
}

The above app code inserts the record in the DB, but the respective trigger
in the database is not triggered hence the "weighted_tsv" columns is empty
for this record.

But if I insert a record from the postgres psql, it will insert and
the respective trigger is working perfectly.

What could be the problem ? Why trigger is not working if I insert from the
application ? Am I doing anything wrong ?
Any help would be really really appreciated.

Thanks
Kiran

Re: [GENERAL] Trigger function interface

[Jim Nasby]

On 1/7/16 6:15 PM, Tatsuo Ishii wrote:

On 1/6/16 7:03 PM, Tatsuo Ishii wrote:

Is it possible to get the parse tree in a C trigger function which is
invoked when DML (INSERT/UPDATE/DELETE against a view) is executed?


Yes, it's in fcinfo->flinfo->fn_expr.


Thanks for the info. But is this the parse tree for the top level
query which involves the trigger?


Hrm, apparently not. fcinfo->context would maybe be helpful, but I'm not 
sure.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.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] Trigger function interface

[Tatsuo Ishii]

> On 1/6/16 7:03 PM, Tatsuo Ishii wrote:
>> Is it possible to get the parse tree in a C trigger function which is
>> invoked when DML (INSERT/UPDATE/DELETE against a view) is executed?
> 
> Yes, it's in fcinfo->flinfo->fn_expr.

Thanks for the info. But is this the parse tree for the top level
query which involves the trigger?

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp


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

[GENERAL] Trigger function interface

[Tatsuo Ishii]

Is it possible to get the parse tree in a C trigger function which is
invoked when DML (INSERT/UPDATE/DELETE against a view) is executed?

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp


-- 
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] Trigger function interface

[Jim Nasby]

On 1/6/16 7:03 PM, Tatsuo Ishii wrote:

Is it possible to get the parse tree in a C trigger function which is
invoked when DML (INSERT/UPDATE/DELETE against a view) is executed?


Yes, it's in fcinfo->flinfo->fn_expr.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


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

[GENERAL] Trigger function, C, lookup of attribute type

[Paul]

I'm writing a trigger procedure in C to catch updates to a
table and write them to a log file.

The function must walk along trigdata->tg_trigtuple
pulling out the attributes, comparing them with those in
trigdata->tg_newtuple and writing the diffs to a flat ASCII
file.

I've got a loop over the attributes, and I can get the values
if I know the type, eg

 Datum d = heap_getattr( trigdata->tg_trigtuple, ...
 int32_t val = DatumGetInt32( d);

but I'm struggling to find how the function can determine the
attribute types.   I'm only using the built-in scalar types.

Do I have to query the system catalogs?  Or maybe there's a
function that works from a TupleDesc?

--
Paul Nicholson
--


--
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] Trigger function, C, lookup of attribute type

[Jim Nasby]

On 12/15/15 4:42 AM, Paul wrote:

I'm writing a trigger procedure in C to catch updates to a
table and write them to a log file.


You might find https://github.com/arkhipov/temporal_tables useful.

Though, if you just want to log things to a file, you should check out 
http://pgaudit.org/.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.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] Trigger function, C, lookup of attribute type

[Paul]

I found the solution: SPI_gettype() does the job fine.

I was led to that by rummaging through the slony source
code to see how they handle the triggers in C.

--
Paul Nicholson
--


--
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] TRIGGER TRUNCATE -- CASCADE or RESTRICT

[Albe Laurenz]

Andreas Ulbrich wrote:
 I'm in a handle for a trigger for TRUNCATE. Is it possible to find out
 whether the TRUNCATE TABLE ist called with CASCADE?

I don't think there is.

But you can find out the table where the trigger is defined and examine
if any foreign key constraints are referring to it.

If yes, then the trigger was called with CASCADE.
If no, it might have been called either way, but the effect would be the same.

Yours,
Laurenz Albe

-- 
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] TRIGGER TRUNCATE -- CASCADE or RESTRICT

[Andreas Ulbrich]

On 02.06.2015 16:20, Melvin Davidson wrote:
You can use the following to list the triggers and see what functions 
they call. Then you can check pg_proc to see how TRUNCATE is used in 
prosrc.



SELECT c.relname,
   t.tgname,
   p.pronameAS function_called,
   t.tgconstraint   AS is_constraint,
   CASE WHEN t.tgconstrrelid  0
THEN (SELECT relname
   FROM pg_class
  WHERE oid = t.tgconstrrelid)
ELSE ''
END AS constr_tbl,
   t.tgenabled
  FROM pg_trigger t
  INNER JOIN pg_proc p  ON ( p.oid = t.tgfoid)
  INNER JOIN pg_class c ON (c.oid = t.tgrelid)
  WHERE tgname NOT LIKE 'pg_%'
AND tgname NOT LIKE 'RI_%'  --  comment out to see constraints
--AND t.tgenabled = FALSE
 ORDER BY 1;


On Tue, Jun 2, 2015 at 5:31 AM, Albe Laurenz laurenz.a...@wien.gv.at 
mailto:laurenz.a...@wien.gv.at wrote:


Andreas Ulbrich wrote:
 I'm in a handle for a trigger for TRUNCATE. Is it possible to
find out
 whether the TRUNCATE TABLE ist called with CASCADE?

I don't think there is.

But you can find out the table where the trigger is defined and
examine
if any foreign key constraints are referring to it.

If yes, then the trigger was called with CASCADE.
If no, it might have been called either way, but the effect would
be the same.

Yours,
Laurenz Albe

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



I think, I must explain the problem deeper:

I have two (or more) tables
CREATE TABLE a (id ... UNIQUE -- maby the PRIMARY KEY,...);
CREATE TABLE b (...) INHERIT (a);

But the id has to be unique over the inheritance. So one solution of the 
problem is:

CREATE key_table (id ... UNIQUE, table REGCLASS);
By trigger every INSERT/UPDATE/DELETE in Table a,b,... changes the 
key_table.

This works.

Now I have a table reference to the id of table a*. This is not 
possible, but reference to key_table(id) works fine.
CREATE TABLE r (..., a_id /* REFERENCES a*(id) */ REFERENCES 
key_tabel(id),..);


And now the problem: Can I support TRUNCATE TABLE?
DELETE is not a problem: for DELETE FROM a the trigger deletes the entry 
in the key_table and if the reference action on delete is CASCADE, the 
entries in r will be deletet.


But TRUNCATE TABLE a! In a TRUNCATE TRIGGER I can delete the entries in 
the key_table WHERE table = a (O.K. the performance) -- it is actual 
not a TRUNCATE TABLE but a TRUNCATE PARTITION.
And if I not specified ONLY, there is also a TRUNCATE TABLE b and the 
trigger ist fired too.


But what is with table r? If I do the delete in the key_table, the 
delete action will be used. But there is not a truncate action, cascaded 
truncation is controlled by execute TRUNCATE. And so, I must delete the 
entries in r if there is a CASCADE in the TRUNCATE or raise an exception 
if the TRUNCATE is RESTRICTED.


Now the Question? How to find out in the trigger function for truncate 
whether is there a CASCADE or not.


regards, Andreas


--
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] TRIGGER TRUNCATE -- CASCADE or RESTRICT

[Melvin Davidson]

Your problem is in your design.

If you do it like this:

CREATE TABLE A
(
p_col serial PRIMARY KEY,
acol  integer
);

CREATE TABLE B() INHERITS (A);

INSERT INTO A(acol) VALUES (1);
INSERT INTO B(acol) VALUES (2);

SELECT * FROM A;
SELECT * FROM B;

Then the sequence (p_col) will be UNIQUE across all tables and can be
referenced.
No need for a key table.

On Tue, Jun 2, 2015 at 3:45 PM, Andreas Ulbrich 
andreas.ulbr...@matheversum.de wrote:

  On 02.06.2015 16:20, Melvin Davidson wrote:

 You can use the following to list the triggers and see what functions they
 call. Then you can check pg_proc to see how TRUNCATE is used in prosrc.


 SELECT c.relname,
t.tgname,
p.pronameAS function_called,
t.tgconstraint   AS is_constraint,
CASE WHEN t.tgconstrrelid  0
 THEN (SELECT relname
FROM pg_class
   WHERE oid = t.tgconstrrelid)
 ELSE ''
 END AS constr_tbl,
t.tgenabled
   FROM pg_trigger t
   INNER JOIN pg_proc p  ON ( p.oid = t.tgfoid)
   INNER JOIN pg_class c ON (c.oid = t.tgrelid)
   WHERE tgname NOT LIKE 'pg_%'
 AND tgname NOT LIKE 'RI_%'  --  comment out to see constraints
 --AND t.tgenabled = FALSE
  ORDER BY 1;


 On Tue, Jun 2, 2015 at 5:31 AM, Albe Laurenz laurenz.a...@wien.gv.at
 wrote:

 Andreas Ulbrich wrote:
  I'm in a handle for a trigger for TRUNCATE. Is it possible to find out
  whether the TRUNCATE TABLE ist called with CASCADE?

 I don't think there is.

 But you can find out the table where the trigger is defined and examine
 if any foreign key constraints are referring to it.

 If yes, then the trigger was called with CASCADE.
 If no, it might have been called either way, but the effect would be the
 same.

 Yours,
 Laurenz Albe

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


  I think, I must explain the problem deeper:

 I have two (or more) tables
 CREATE TABLE a (id ... UNIQUE -- maby the PRIMARY KEY,...);
 CREATE TABLE b (...) INHERIT (a);

 But the id has to be unique over the inheritance. So one solution of the
 problem is:
 CREATE key_table (id ... UNIQUE, table REGCLASS);
 By trigger every INSERT/UPDATE/DELETE in Table a,b,... changes the
 key_table.
 This works.

 Now I have a table reference to the id of table a*. This is not possible,
 but reference to key_table(id) works fine.
 CREATE TABLE r (..., a_id /* REFERENCES a*(id) */ REFERENCES
 key_tabel(id),..);

 And now the problem: Can I support TRUNCATE TABLE?
 DELETE is not a problem: for DELETE FROM a the trigger deletes the entry
 in the key_table and if the reference action on delete is CASCADE, the
 entries in r will be deletet.

 But TRUNCATE TABLE a! In a TRUNCATE TRIGGER I can delete the entries in
 the key_table WHERE table = a (O.K. the performance) -- it is actual not
 a TRUNCATE TABLE but a TRUNCATE PARTITION.
 And if I not specified ONLY, there is also a TRUNCATE TABLE b and the
 trigger ist fired too.

 But what is with table r? If I do the delete in the key_table, the delete
 action will be used. But there is not a truncate action, cascaded
 truncation is controlled by execute TRUNCATE. And so, I must delete the
 entries in r if there is a CASCADE in the TRUNCATE or raise an exception if
 the TRUNCATE is RESTRICTED.

 Now the Question? How to find out in the trigger function for truncate
 whether is there a CASCADE or not.

 regards, Andreas


 --
  *Melvin Davidson*
 I reserve the right to fantasize.  Whether or not you
 wish to share my fantasy is entirely up to you.





-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] TRIGGER TRUNCATE -- CASCADE or RESTRICT

[Andreas Ulbrich]

On 02.06.2015 22:12, Melvin Davidson wrote:

Your problem is in your design.

If you do it like this:

CREATE TABLE A
(
p_col serial PRIMARY KEY,
acol  integer
);

CREATE TABLE B() INHERITS (A);

INSERT INTO A(acol) VALUES (1);
INSERT INTO B(acol) VALUES (2);

SELECT * FROM A;
SELECT * FROM B;

Then the sequence (p_col) will be UNIQUE across all tables and can be 
referenced.

No need for a key table.

No, someone can do:
INSERT INTO A VALUES (2,3);
TABLE A;
shows:
 p_col | acol
---+--
 1 |1
 2 |2
 2 |3
p_col is not unique!



On Tue, Jun 2, 2015 at 3:45 PM, Andreas Ulbrich 
andreas.ulbr...@matheversum.de 
mailto:andreas.ulbr...@matheversum.de wrote:


On 02.06.2015 16:20, Melvin Davidson wrote:

You can use the following to list the triggers and see what
functions they call. Then you can check pg_proc to see how
TRUNCATE is used in prosrc.


SELECT c.relname,
   t.tgname,
   p.pronameAS function_called,
   t.tgconstraint   AS is_constraint,
   CASE WHEN t.tgconstrrelid  0
THEN (SELECT relname
   FROM pg_class
  WHERE oid = t.tgconstrrelid)
ELSE ''
END AS constr_tbl,
   t.tgenabled
  FROM pg_trigger t
  INNER JOIN pg_proc p  ON ( p.oid = t.tgfoid)
  INNER JOIN pg_class c ON (c.oid = t.tgrelid)
  WHERE tgname NOT LIKE 'pg_%'
AND tgname NOT LIKE 'RI_%'  --  comment out to see constraints
--AND t.tgenabled = FALSE
 ORDER BY 1;


On Tue, Jun 2, 2015 at 5:31 AM, Albe Laurenz
laurenz.a...@wien.gv.at mailto:laurenz.a...@wien.gv.at wrote:

Andreas Ulbrich wrote:
 I'm in a handle for a trigger for TRUNCATE. Is it possible
to find out
 whether the TRUNCATE TABLE ist called with CASCADE?

I don't think there is.

But you can find out the table where the trigger is defined
and examine
if any foreign key constraints are referring to it.

If yes, then the trigger was called with CASCADE.
If no, it might have been called either way, but the effect
would be the same.

Yours,
Laurenz Albe

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



I think, I must explain the problem deeper:

I have two (or more) tables
CREATE TABLE a (id ... UNIQUE -- maby the PRIMARY KEY,...);
CREATE TABLE b (...) INHERIT (a);

But the id has to be unique over the inheritance. So one solution
of the problem is:
CREATE key_table (id ... UNIQUE, table REGCLASS);
By trigger every INSERT/UPDATE/DELETE in Table a,b,... changes the
key_table.
This works.

Now I have a table reference to the id of table a*. This is not
possible, but reference to key_table(id) works fine.
CREATE TABLE r (..., a_id /* REFERENCES a*(id) */ REFERENCES
key_tabel(id),..);

And now the problem: Can I support TRUNCATE TABLE?
DELETE is not a problem: for DELETE FROM a the trigger deletes the
entry in the key_table and if the reference action on delete is
CASCADE, the entries in r will be deletet.

But TRUNCATE TABLE a! In a TRUNCATE TRIGGER I can delete the
entries in the key_table WHERE table = a (O.K. the performance)
-- it is actual not a TRUNCATE TABLE but a TRUNCATE PARTITION.
And if I not specified ONLY, there is also a TRUNCATE TABLE b and
the trigger ist fired too.

But what is with table r? If I do the delete in the key_table, the
delete action will be used. But there is not a truncate action,
cascaded truncation is controlled by execute TRUNCATE. And so, I
must delete the entries in r if there is a CASCADE in the TRUNCATE
or raise an exception if the TRUNCATE is RESTRICTED.

Now the Question? How to find out in the trigger function for
truncate whether is there a CASCADE or not.

regards, Andreas


-- 
*Melvin Davidson*

I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.





--
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] TRIGGER TRUNCATE -- CASCADE or RESTRICT

[Gavin Flower]

On 03/06/15 08:40, Andreas Ulbrich wrote:

On 02.06.2015 22:12, Melvin Davidson wrote:

Your problem is in your design.

If you do it like this:

CREATE TABLE A
(
p_col serial PRIMARY KEY,
acol  integer
);

CREATE TABLE B() INHERITS (A);

INSERT INTO A(acol) VALUES (1);
INSERT INTO B(acol) VALUES (2);

SELECT * FROM A;
SELECT * FROM B;

Then the sequence (p_col) will be UNIQUE across all tables and can be 
referenced.

No need for a key table.

No, someone can do:
INSERT INTO A VALUES (2,3);
TABLE A;
shows:
 p_col | acol
---+--
 1 |1
 2 |2
 2 |3
p_col is not unique!
Curious, I tried to investigate, to get a better understanding and ran 
into a problem...


   $ psql
   psql (9.4.1)
   Type help for help.

   gavin= CREATE TABLE A
   gavin- (
   gavin( p_col serial PRIMARY KEY,
   gavin( acol  integer
   gavin( );
   CREATE TABLE
   gavin= CREATE TABLE B() INHERITS (A);
   CREATE TABLE
   gavin= INSERT INTO A(acol) VALUES (1);
   ERROR:  column acol of relation a does not exist
   LINE 1: INSERT INTO A(acol) VALUES (1);
   ^
   gavin= \d+ a
   Table public.a
 Column | Type   |
   Modifiers | Storage | Stats target | Description

   
+-+---+-+--+-
 p_col  | integer | not null default
   nextval('a_p_col_seq'::regclass) | plain   |  |
 acol   | integer
   |   | plain  
   |  |

   Indexes:
a_pkey PRIMARY KEY, btree (p_col)
   Child tables: b

   gavin= \d b
   Table public.b
 Column | Type   | Modifiers
   +-+---
 p_col  | integer | not null default nextval('a_p_col_seq'::regclass)
 acol   | integer |
   Inherits: a

   gavin=




[...]


Cheers,
Gavin


--
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] TRIGGER TRUNCATE -- CASCADE or RESTRICT

[Melvin Davidson]

You can use the following to list the triggers and see what functions they
call. Then you can check pg_proc to see how TRUNCATE is used in prosrc.


SELECT c.relname,
   t.tgname,
   p.pronameAS function_called,
   t.tgconstraint   AS is_constraint,
   CASE WHEN t.tgconstrrelid  0
THEN (SELECT relname
   FROM pg_class
  WHERE oid = t.tgconstrrelid)
ELSE ''
END AS constr_tbl,
   t.tgenabled
  FROM pg_trigger t
  INNER JOIN pg_proc p  ON ( p.oid = t.tgfoid)
  INNER JOIN pg_class c ON (c.oid = t.tgrelid)
  WHERE tgname NOT LIKE 'pg_%'
AND tgname NOT LIKE 'RI_%'  --  comment out to see constraints
--AND t.tgenabled = FALSE
 ORDER BY 1;


On Tue, Jun 2, 2015 at 5:31 AM, Albe Laurenz laurenz.a...@wien.gv.at
wrote:

 Andreas Ulbrich wrote:
  I'm in a handle for a trigger for TRUNCATE. Is it possible to find out
  whether the TRUNCATE TABLE ist called with CASCADE?

 I don't think there is.

 But you can find out the table where the trigger is defined and examine
 if any foreign key constraints are referring to it.

 If yes, then the trigger was called with CASCADE.
 If no, it might have been called either way, but the effect would be the
 same.

 Yours,
 Laurenz Albe

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




-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

[GENERAL] TRIGGER TRUNCATE -- CASCADE or RESTRICT

[Andreas Ulbrich]

Salvete!

I'm in a handle for a trigger for TRUNCATE. Is it possible to find out 
whether the TRUNCATE TABLE ist called with CASCADE?


regads andreas



--
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] trigger Before or After

[Albe Laurenz]

avpro avpro wrote:
 in the pgsql documentation
 (http://www.postgresql.org/docs/9.1/static/sql-createtrigger.html)
 
 
 i haven't seen anything referring to: how is affected the data inserted in 
 the new table by a trigger
 Before Insert compared with a trigger After Insert? and anything related to 
 performance

In your example (the trigger updates a second table) it should make
no difference if the trigger is BEFORE or AFTER INSERT.

The difference is that in a BEFORE trigger you can modify the values that
will be inserted before the INSERT actually happens.

 I read somewhere (I don't find the link anymore) that if the trigger is After 
 Insert, the data
 available in the table LOG might not be available anymore to run the trigger. 
 is that correct? or I
 might understood wrong?

I don't quite understand.
You will have access to the OLD and NEW values in both BEFORE and AFTER 
triggers.
In an AFTER trigger, the table row has already been modified.

 what's the difference related to performance concerning a trigger Before 
 Insert compared with a
 trigger After Insert?

I don't think that there is a big difference, but you can easily test it:
Insert 10 rows with a BEFORE trigger on the table and compare the
time it takes to inserting 10 rows with an AFTER trigger.

Yours,
Laurenz Albe

-- 
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] trigger Before or After

[Adrian Klaver]

On 11/10/2014 10:38 PM, avpro avpro wrote:

hi,

in the pgsql documentation
(http://www.postgresql.org/docs/9.1/static/sql-createtrigger.html)

i haven't seen anything referring to: how is affected the data inserted
in the new table by a trigger Before Insert compared with a trigger
After Insert? and anything related to performance


See bottom of above page and here:

http://www.postgresql.org/docs/9.1/static/trigger-definition.html




thank you
have a sunny day




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

[GENERAL] trigger Before or After

[avpro avpro]

hi,

in the pgsql documentation
(http://www.postgresql.org/docs/9.1/static/sql-createtrigger.html)

i haven't seen anything referring to: how is affected the data inserted in
the new table by a trigger Before Insert compared with a trigger After
Insert? and anything related to performance

for example:

tables: actuals (summarize the total running hours), log (the functional
hours are inserted in LOG as time)
 function: sum
view: timeview (where running hours are calculated as a difference)

-- Function: sum()

-- DROP FUNCTION sum();

CREATE OR REPLACE FUNCTION sum()
 RETURNS trigger AS
$BODY$begin
update actuals
set
hours = hours + (select time from time_view
where idlog = (select max(idlog) from timeview))
where actuals.idmac =
(SELECT idmac FROM selectedmac) ;
return new;
end$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION sum()
  OWNER TO user;




--trigger
CREATE TRIGGER update_actuals_tg01
  AFTER INSERT
  ON log
  FOR EACH ROW
  EXECUTE PROCEDURE sum();


I read somewhere (I don't find the link anymore) that if the trigger is
After Insert, the data available in the table LOG might not be available
anymore to run the trigger. is that correct? or I might understood wrong?

what's the difference related to performance concerning a trigger Before
Insert compared with a trigger After Insert?

thank you
have a sunny day

[GENERAL] Trigger to a queue for sending updates to a cache layer

[Marcus Engene]

Hi,

I'm working with a retail site with tens of millions of products in 
several languages.


For the detail pages, we try to cache in memcached. We also have quite a 
bit of keyword translation lookups (for international queries to solr).


We're thinking of adding a nosql layer that takes the big beating with 
the mundane reads. I don't fancy manually adding code to every possible 
place in the php code that touches the related tables.


What fast trigger solution would you use for this? I'm thinking of 
something that would just push (via fdw?) data to a queue; table name 
and item id. Naturally it should not slow down pg noticable and have 
stellar uptime.


Is the whole concept a stupid idea?

Thanks,
Marcus



--
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] Trigger function cannot reference field name with capital letter

[Patrick Dung]

Thanks all for the help.

BTW, letter casing is just a preference.
Some people liked to use all small caps, some liked to use all big caps.
I sometimes found that mixed case is more meaningful for the filed (eg. 
serialnumber vs serialNumber)

What is your preference or suggestion?



On Thursday, August 14, 2014 12:18 PM, John R Pierce pie...@hogranch.com 
wrote:
 


On 8/13/2014 9:13 PM, John R Pierce wrote:

 SELECT * from tbl1 where new.postTimestamp'  timestamp '2014-01-01 
 00:00:00'

oops.

SELECT * from tbl1 where new.postTimestamp  timestamp '2014-01-01 
00:00:00'

I meant.  I should proof what I write, hah!



-- 
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] Trigger function cannot reference field name with capital letter

[Pavel Stehule]

Hi


2014-08-14 8:10 GMT+02:00 Patrick Dung patrick_...@yahoo.com.hk:

 Thanks all for the help.

 BTW, letter casing is just a preference.
 Some people liked to use all small caps, some liked to use all big caps.
 I sometimes found that mixed case is more meaningful for the filed (eg.
 serialnumber vs serialNumber)

 What is your preference or suggestion?


Camel notation is not practical for SQL identifiers - SQL is not case
sensitive, but there are possible exception when you use double quotes. And
then usually you can hit a situation when one identifier is in one
situation case sensitive and  elsewhere it is translated to lowercase. It
is not bug, it has usually good reasons, but it is terrible issue for
beginners. So SQL identifiers should be in lowercase or uppercase.
Uppercase has bigger sense on Oracle, lowercase on PostgreSQL.

Regards

Pavel




   On Thursday, August 14, 2014 12:18 PM, John R Pierce 
 pie...@hogranch.com wrote:


 On 8/13/2014 9:13 PM, John R Pierce wrote:
 
  SELECT * from tbl1 where new.postTimestamp'  timestamp '2014-01-01
  00:00:00'

 oops.

 SELECT * from tbl1 where new.postTimestamp  timestamp '2014-01-01
 00:00:00'

 I meant.  I should proof what I write, hah!



 --
 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] Trigger function cannot reference field name with capital letter

[Adrian Klaver]

On 08/13/2014 11:10 PM, Patrick Dung wrote:

Thanks all for the help.

BTW, letter casing is just a preference.
Some people liked to use all small caps, some liked to use all big caps.
I sometimes found that mixed case is more meaningful for the filed (eg.
serialnumber vs serialNumber)

What is your preference or suggestion?


My preference is lower case with underscores between words. I stay away 
from CamelCaps for the reasons Pavel mentioned. Probably the most 
important thing is to be consistent in your style.








--
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] Trigger function cannot reference field name with capital letter

[Alban Hertroys]

On 14 August 2014 08:10, Patrick Dung patrick_...@yahoo.com.hk wrote:
 Thanks all for the help.

 BTW, letter casing is just a preference.
 Some people liked to use all small caps, some liked to use all big caps.
 I sometimes found that mixed case is more meaningful for the filed (eg.
 serialnumber vs serialNumber)

 What is your preference or suggestion?

You can use whatever case you like, as long as you never quote your
identifiers so they stay case-insensitive. As long as you stick to
that rule, you can use upper or lower camel caps or all upper or lower
case in your SQL.

The need for quoting identifiers often comes from ORM's that attempt
to preserve case, which is totally unnecessary and causes more trouble
than it has benefits.

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


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

[GENERAL] Trigger function cannot reference field name with capital letter

[Patrick Dung]

Hello PGSQL users,


I have a field called postTimestamp.
The trigger function could not reference it.
When I change my field to post_timestamp. I can reference it from the tigger 
function.
Version is 9.3.5. Any comment?

 2014-08-14 00:23:32.717 HKT ERROR:  post new has no field posttimestamp
 2014-08-14 00:23:32.717 HKT CONTEXT:  SQL statement SELECT * from tbl1
    where NEW.posttimestamp  2014-01-01 00:00:00 )
    PL/pgSQL function test_trigger() line 9 at assignment
 2014-08-14 00:23:32.717 HKT STATEMENT:  INSERT INTO public.tbl1(vendor, 
url, postTimestamp, product, type, itemID) VALUES ('vendor1'::text, 
'http://example.org'::text, '2014-01-01 01:01:01'::timestamp without time zone, 
'product1'::text, 'food'::text, '1'::bigint)
 2014-08-14 00:32:39.708 HKT ERROR:  syntax error at or near SELECT at 
character 314


Thanks and regards,
Patrick

Re: [GENERAL] Trigger function cannot reference field name with capital letter

[Adrian Klaver]

On 08/13/2014 08:52 PM, Patrick Dung wrote:

Hello PGSQL users,

I have a field called postTimestamp.
The trigger function could not reference it.
When I change my field to post_timestamp. I can reference it from the
tigger function.
Version is 9.3.5. Any comment?

 2014-08-14 00:23:32.717 HKT ERROR:  post new has no field
posttimestamp


The clue is above. Postgres folds unquoted mixed case to lower case by 
default, so it is looking for posttimestamp. If you want to preserve the 
mixed case, quote the field name postTimestamp.



 2014-08-14 00:23:32.717 HKT CONTEXT:  SQL statement SELECT * from tbl1
 where NEW.posttimestamp  2014-01-01 00:00:00 )
 PL/pgSQL function test_trigger() line 9 at assignment
 2014-08-14 00:23:32.717 HKT STATEMENT:  INSERT INTO
public.tbl1(vendor, url, postTimestamp, product, type, itemID)
VALUES ('vendor1'::text, 'http://example.org'::text, '2014-01-01
01:01:01'::timestamp without time zone, 'product1'::text, 'food'::text,
'1'::bigint)
 2014-08-14 00:32:39.708 HKT ERROR:  syntax error at or near SELECT
at character 314

Thanks and regards,
Patrick



--
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] Trigger function cannot reference field name with capital letter

[John R Pierce]

On 8/13/2014 8:52 PM, Patrick Dung wrote:


I have a field called postTimestamp.
The trigger function could not reference it.
When I change my field to post_timestamp. I can reference it from the 
tigger function.

Version is 9.3.5. Any comment?

 2014-08-14 00:23:32.717 HKT ERROR: post new has no field 
posttimestamp

 2014-08-14 00:23:32.717 HKT CONTEXT:  SQL statement SELECT * from tbl1
where NEW.posttimestamp  2014-01-01 00:00:00 )
PL/pgSQL function test_trigger() line 9 at assignment



field and talbe names with mixed case have to be Quoted. string 
constants, on the other hand, are are in single 'quotes'.


try...

SELECT * from tbl1 where new.postTimestamp'  timestamp '2014-01-01 
00:00:00'



 2014-08-14 00:23:32.717 HKT STATEMENT:  INSERT INTO 
public.tbl1(vendor, url, postTimestamp, product, type, itemID) 
VALUES ('vendor1'::text, 'http://example.org'::text, '2014-01-01 
01:01:01'::timestamp without time zone, 'product1'::text, 
'food'::text, '1'::bigint)
 2014-08-14 00:32:39.708 HKT ERROR:  syntax error at or near 
SELECT at character 314


those two error logs have different timestamps, I don't believe that 
error is on that statement.


there's a whole lot of unnecessary typecasting in that insert, however.

INSERT INTO public.tbl1(vendor, url, postTimestamp, product, type, 
itemID)
VALUES ('vendor1', 'http://example.org', timestamp '2014-01-01 
01:01:01', 'product1', 'food', 1)


would suffice nicely.




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

Re: [GENERAL] Trigger function cannot reference field name with capital letter

[John R Pierce]

On 8/13/2014 9:13 PM, John R Pierce wrote:


SELECT * from tbl1 where new.postTimestamp'  timestamp '2014-01-01 
00:00:00'


oops.

SELECT * from tbl1 where new.postTimestamp  timestamp '2014-01-01 
00:00:00'


I meant.  I should proof what I write, hah!


--
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] Trigger to convert UNIX time to timestamp without time zone.

[Kevin Grittner]

Andrew Sullivan a...@crankycanuck.ca wrote:

 Are you sure you want this without time zone?  In my experience,
 almost every time people think they want without time zone they
 actually don't.

+1

Basically, if you want to capture a moment in time, such as when
some event happened or some measurement was taken, you want
timestamp WITH time zone.  If you want to know what was (or will
be) showing on a clock in some particular time zone at a moment in
time, you want timestamp WITHOUT time zone.  The names tend to
mislead one on the semantics of these types.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

[GENERAL] Trigger to convert UNIX time to timestamp without time zone.

[Alberto Olivares]

Hello,

I want to insert data into a column timestamp without time zone data
type. The problem is I am receiving the data in UNIX time.

How can I create a trigger to transform the time from UNIX to timestamp
without time zone every time a new record is inserted into my database?

Thank you in advance.

Regards,
Alberto.



*Alberto Olivares Colas*Technical Consultant
Snowflake Software

Tel.: +44 (0)2380 386578

Email: alberto.oliva...@snowflakesoftware.com
ballal.jogle...@snowflakesoftware.com
Website: www.snowflakesoftware.com
Twitter: @sflakesoftware http://www,twitter.com@sflakesoftware/
Follow us on LinkedIn http://www.linkedin.com/company/snowflake-software

Registered in England  Wales. Registered Number: 4294244
-


*Geospatial Technology Company of the Year*
*Read more http://www.snowflakesoftware.com/2014/05/geospatial-awards/*

-- 
*Geospatial Technology Company of the Year*
*Read more http://www.snowflakesoftware.com/2014/05/geospatial-awards/*

Re: [GENERAL] Trigger to convert UNIX time to timestamp without time zone.

[Adrian Klaver]

On 06/06/2014 06:19 AM, Alberto Olivares wrote:

Hello,

I want to insert data into a column timestamp without time zone data
type. The problem is I am receiving the data in UNIX time.

How can I create a trigger to transform the time from UNIX to timestamp
without time zone every time a new record is inserted into my database?


to_timesstamp will take the epoch, which I assume is what you are 
talking about, and turn it into a timestamp with time zone. Don't worry 
about the timezone.


test= select to_timestamp(extract(epoch from now()));
 to_timestamp
---
 2014-06-06 06:27:20.484509-07
(1 row)

test= \d timestamp_test 



  Table public.timestamp_test 



 Column |Type | Modifiers 



+-+--- 



 id | integer | 



 ts | timestamp without time zone | 



 ts_z   | timestamp with time zone|


Inserting a timestamp with time zone into a field that is timestamp 
without timezone will strip the timezone automatically.


test= insert into timestamp_test values (1, to_timestamp(extract(epoch 
from now())), to_timestamp(extract(epoch from now(;

INSERT 0 1

test= select * from timestamp_test ;
 id | ts | ts_z
++---
  1 | 2014-06-06 06:30:58.080158 | 2014-06-06 06:30:58.080158-07
(1 row)

So you just need to wrap the above in a function and call it from your 
trigger.





Thank you in advance.

Regards,
Alberto.


*Alberto Olivares Colas
*Technical Consultant
Snowflake Software




--
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] Trigger to convert UNIX time to timestamp without time zone.

[Andrew Sullivan]

On Fri, Jun 06, 2014 at 02:19:50PM +0100, Alberto Olivares wrote:
 How can I create a trigger to transform the time from UNIX to timestamp
 without time zone every time a new record is inserted into my database?

This is in the manual, section 9.8: to_timestamp(double precision)

It's always a little confusing when you go to look it up, because it's
not with the date and time functions, because it's actually a
formatting issue.  (There's a cross reference, but if you don't know
this is just a formatting issue you won't know to follow the
reference.)  You probably don't need a trigger, just put that in your
query.

Are you sure you want this without time zone?  In my experience,
almost every time people think they want without time zone they
actually don't. 

A


-- 
Andrew Sullivan
a...@crankycanuck.ca


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

[GENERAL] Trigger function permissions

[Keith Fiske]

Just want to make sure I'm understanding the permissions needed for trigger
functions as well as making sure this is what the developers intended
before I go assuming things will always work this way. Also as a sanity
check for myself that I'm not missing something obvious.

I have an extension (https://github.com/omniti-labs/mimeo) that does
logical replication. The setup functions for the trigger-based DML
replication automatically create the queue table, trigger function 
trigger on the source database. I'm working on fixing a bug where the
correct permissions weren't being given and in my testing for a fix found
that just simply giving the trigger function SECURITY DEFINER fixed all the
problems and I don't have to even bother looking up which roles currently
have write permissions on the source table to set them on the queue table
and trigger function.

I understand how SECURITY DEFINER solves the issue of the function writing
to the queue table (function and queue table owner are the same). But I
would've thought that any roles with write privileges to the table would've
needed to be given EXECUTE permissions on the trigger function. I thought
maybe the trigger function was being called as the owner of the table, but
apparently even the owner of the table doesn't need these execute
permissions. Reading through the docs on triggers, I didn't see anything
mentioned about how this is expected to work. Examples are in the gist link
below. You can see the owner has no explicit permissions to the trigger
function and inserts still work even after revoking PUBLIC.

https://gist.github.com/keithf4/83c5c6516e2726609675
--
Keith Fiske
Database Administrator
OmniTI Computer Consulting, Inc.
http://www.keithf4.com

Re: [GENERAL] Trigger Firing Order

[Sameer Kumar]

 But with certain packaged products who create their own triggers, I won't
 have control over this.

 I don't have a lot of sympathy for that argument.  If the product is
 capable of creating Postgres-compatible triggers at all, it should be
 aware that the name is a significant property, and hence provide some
 mechanism for selecting a name.  Even if it somehow forgot about the
 firing-order property, you can't seriously claim that it's our problem
 to cater for combinations of client-side code that each think they can
 choose trigger names in a vacuum.  What if they choose the same name?


Well replication tools which are based on triggers do not let us do that.
And if they let me do that it would become too tedious to use them.
If I have a trigger which add primary key to my inserted row (before
trigger). Now if I plan to create new set of triggers for AUDITING or
replication (where either I have no flexibility of choosing a name or the
trigger name has to follow a standard), then I need to change all my
existing triggers and rename them. Luckily I have ALTER TRIGGER statement
to help me (some database don't have that feature), but it could be a
substantial work depending on number of trigger I have.


Note, too, that you can leave but disable the existing triggers and define
 your own triggers with whatever name you require and simply call the same
 function as the existing trigger.


Isn't that bit of a trouble if I have to do that for every trigger?

I guess such re-naming could introduce problems with applications but that
 is unlikely.


Actually it can cause issues. e.g. when I want to remove a table from
replication set (all the triggers must get dropped). Now if I have modified
a trigger/created another with a different name, that table will still be
part of set.

I was going to propose to work on developing an additional clause *ORDER
n* for CREATE TRIGGER statement. Triggers with lowest order gets called
first.
Any two triggers who have same order will get called based on their sorting
order of their name.
I can always define my triggers with a huge negative number. Now it does
not matter if I add any more triggers in future.

Before proposing this change I wanted to make sure that others in community
as well think that this could add value.

Re: [GENERAL] Trigger Firing Order

[Kevin Grittner]

Sameer Kumar sameer.ku...@ashnik.com wrote:

 If I have a trigger which add primary key to my inserted row
 (before trigger). Now if I plan to create new set of triggers
 for AUDITING or replication (where either I have no flexibility
 of choosing a name or the trigger name has to follow a standard),
 then I need to change all my existing triggers and rename them.

You have auditing or replication triggers that fire as BEFORE
triggers?  What do they do if a subsequent trigger further modifies
the operation before the statement is applied to the database? 
(Obviously, all BEFORE triggers fire before the statement is
applied, and all AFTER triggers fire after the statement is
applied, so naming can never cause an AFTER trigger to fire before
a BEFORE trigger.)

 I was going to propose to work on developing an additional clause
 ORDER n for CREATE TRIGGER statement. Triggers with lowest
 order gets called first.

I just include a 3 digit number as part of my trigger names.  Why
is that harder than adding a new clause to the CREATE TRIGGER
statement?

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
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] Trigger Firing Order

[Sameer Kumar]

That sounds like a nice suggestion. I guess it could get rid of most of the
issues I forsee. I should follow that for my cases too.
I guess we can live without an ORDER clause.
Thanks everyone for helping.

[GENERAL] Trigger Firing Order

[Sameer Kumar]

Hi,

Is it possible for me to define the order in which triggers will be fired?

So far what I have understood from PostgreSQL documentation, in order to
fire 2 triggers in sequence (say trigger1 and trigger2) on a table, I need
to name them in that way.
But with certain packaged products who create their own triggers, I won't
have control over this. Also, this could be an issue for me if I write
business logic in triggers.

Any better way known to work in PostgreSQL?

Best Regards,
*Sameer Kumar | Database Consultant*

*ASHNIK PTE. LTD.*101 Cecil Street, #11-11 Tong Eng Building, Singapore
069533
M : *+65 8110 0350* T: +65 6438 3504 | www.ashnik.com
www.facebook.com/ashnikbiz | www.twitter.com/ashnikbiz

[image: email patch]

This email may contain confidential, privileged or copyright material and
is solely for the use of the intended recipient(s).
image002.jpg

Re: [GENERAL] Trigger Firing Order

[David Johnston]

Sameer Kumar wrote
 Hi,
 
 Is it possible for me to define the order in which triggers will be fired?
 
 So far what I have understood from PostgreSQL documentation, in order to
 fire 2 triggers in sequence (say trigger1 and trigger2) on a table, I need
 to name them in that way.
 But with certain packaged products who create their own triggers, I won't
 have control over this. Also, this could be an issue for me if I write
 business logic in triggers.
 
 Any better way known to work in PostgreSQL?

If you have sufficient enough rights on the database you can modify the
triggers in whatever way you deem fit.  There is currently no alternative
way to specify trigger execution order than alphabetically.

I guess such re-naming could introduce problems with applications but that
is unlikely.

Note, too, that you can leave but disable the existing triggers and define
your own triggers with whatever name you require and simply call the same
function as the existing trigger.

Is this a theoretical question or do you actual have this problem?

David J.





--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Trigger-Firing-Order-tp5782797p5782800.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] Trigger Firing Order

[Tom Lane]

Sameer Kumar sameer.ku...@ashnik.com writes:
 Is it possible for me to define the order in which triggers will be fired?

Sure: choose their names so that their alphabetical ordering is the
firing order you want.  But I see you knew that.

 But with certain packaged products who create their own triggers, I won't
 have control over this.

I don't have a lot of sympathy for that argument.  If the product is
capable of creating Postgres-compatible triggers at all, it should be
aware that the name is a significant property, and hence provide some
mechanism for selecting a name.  Even if it somehow forgot about the
firing-order property, you can't seriously claim that it's our problem
to cater for combinations of client-side code that each think they can
choose trigger names in a vacuum.  What if they choose the same name?

 Also, this could be an issue for me if I write
 business logic in triggers.

This statement lacks content.  What problem do you foresee, and what
other ordering rule would you like that doesn't work about as well
as the name rule?

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

[GENERAL] trigger without trigger call

[Philipp Kraus]

Hello,

I have written a update  delete trigger of a table.
My delete trigger runs an update statement but this create a (semantic) problem.
How can I disable the update trigger for only this update call within the 
delete trigger?
So my delete trigger need not call the update trigger

Thanks

Phil



-- 
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] trigger without trigger call

[Pavel Stehule]

Hello

you can disable trigger by ALTER TABLE tablename DISABLE TRIGGER
triggername;

but it should be executed with owner rights on table.

This statement works perfect, but a fact, so you need it signalize so you
do some wrong. Triggers should to be used primary for checking, logging,
calculating some simply calculated values. Anything else is can carry some
very negative effects with impacts on readability or performance (although
sometimes it is necessary). Try to move some logic from triggers to
functions with explicit call.

Regards

Pavel Stehule


2013/10/14 Philipp Kraus philipp.kr...@tu-clausthal.de

 Hello,

 I have written a update  delete trigger of a table.
 My delete trigger runs an update statement but this create a (semantic)
 problem.
 How can I disable the update trigger for only this update call within the
 delete trigger?
 So my delete trigger need not call the update trigger

 Thanks

 Phil



 --
 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] trigger without trigger call

[Alban Hertroys]

On Oct 14, 2013, at 8:18, Philipp Kraus philipp.kr...@tu-clausthal.de wrote:

 Hello,
 
 I have written a update  delete trigger of a table.
 My delete trigger runs an update statement but this create a (semantic) 
 problem.
 How can I disable the update trigger for only this update call within the 
 delete trigger?
 So my delete trigger need not call the update trigger


You obviously don't want to disable the trigger entirely for all sessions, as 
other sessions may be updating records as well and those should trigger the 
update trigger normally. I think Pavel's suggestion, if executed from your 
delete trigger function, would disable the trigger for that session only, so 
that would work. Don't forget to re-enable it again after the update finishes.

Another solution is to make your update trigger smarter about when it needs to 
do its thing.
For example, you could add a fake boolean column to the table and set that to 
one value when updated from your delete trigger, while you normally leave it at 
the other value (easiest by means of a column DEFAULT value). In your update 
trigger you can then test for the value of that column and escape out of the 
trigger function (RETURN NEW, for example).

Nowadays you can put triggers on views even, in which case you could do the 
above on a view over the table, omitting the need to actually store the fake 
column value.

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



-- 
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] trigger without trigger call

[Philipp Kraus]

Am 14.10.2013 um 11:49 schrieb Alban Hertroys haram...@gmail.com:

 On Oct 14, 2013, at 8:18, Philipp Kraus philipp.kr...@tu-clausthal.de wrote:
 
 Hello,
 
 I have written a update  delete trigger of a table.
 My delete trigger runs an update statement but this create a (semantic) 
 problem.
 How can I disable the update trigger for only this update call within the 
 delete trigger?
 So my delete trigger need not call the update trigger
 
 
 You obviously don't want to disable the trigger entirely for all sessions, as 
 other sessions may be updating records as well and those should trigger the 
 update trigger normally. I think Pavel's suggestion, if executed from your 
 delete trigger function, would disable the trigger for that session only, so 
 that would work. Don't forget to re-enable it again after the update finishes.
 
 Another solution is to make your update trigger smarter about when it needs 
 to do its thing.
 For example, you could add a fake boolean column to the table and set that 
 to one value when updated from your delete trigger, while you normally leave 
 it at the other value (easiest by means of a column DEFAULT value). In your 
 update trigger you can then test for the value of that column and escape out 
 of the trigger function (RETURN NEW, for example).
 
 Nowadays you can put triggers on views even, in which case you could do the 
 above on a view over the table, omitting the need to actually store the fake 
 column value.

I have got a plsql function like that:

begin

 if (TG_OP = 'DELETE') then
   update simulation.source set parent=old.parent where 
parent=old.id; *
   return old;

elseif (TG_OP =  'UPDATE') then

do something

end if;

end


This function is called on the before delete  update call, so the * call 
creates a problem. On this call the 
elseif (TG_OP =  'UPDATE') then need not run at any time. I think the boolean 
field can be helpful, but
is there another idea to disable on the * call the update trigger?

Thanks

Phil

-- 
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] trigger without trigger call

[ChoonSoo Park]

On Mon, Oct 14, 2013 at 6:02 AM, Philipp Kraus 
philipp.kr...@tu-clausthal.de wrote:


 Am 14.10.2013 um 11:49 schrieb Alban Hertroys haram...@gmail.com:

  On Oct 14, 2013, at 8:18, Philipp Kraus philipp.kr...@tu-clausthal.de
 wrote:
 
  Hello,
 
  I have written a update  delete trigger of a table.
  My delete trigger runs an update statement but this create a (semantic)
 problem.
  How can I disable the update trigger for only this update call within
 the delete trigger?
  So my delete trigger need not call the update trigger
 
 
  You obviously don't want to disable the trigger entirely for all
 sessions, as other sessions may be updating records as well and those
 should trigger the update trigger normally. I think Pavel's suggestion, if
 executed from your delete trigger function, would disable the trigger for
 that session only, so that would work. Don't forget to re-enable it again
 after the update finishes.
 
  Another solution is to make your update trigger smarter about when it
 needs to do its thing.
  For example, you could add a fake boolean column to the table and set
 that to one value when updated from your delete trigger, while you normally
 leave it at the other value (easiest by means of a column DEFAULT value).
 In your update trigger you can then test for the value of that column and
 escape out of the trigger function (RETURN NEW, for example).
 
  Nowadays you can put triggers on views even, in which case you could do
 the above on a view over the table, omitting the need to actually store the
 fake column value.

 I have got a plsql function like that:

 begin

  if (TG_OP = 'DELETE') then
update simulation.source set parent=old.parent where parent=
 old.id; *
return old;

 elseif (TG_OP =  'UPDATE') then

 do something

 end if;

 end


 This function is called on the before delete  update call, so the * call
 creates a problem. On this call the
 elseif (TG_OP =  'UPDATE') then need not run at any time. I think the
 boolean field can be helpful, but
 is there another idea to disable on the * call the update trigger?

 Thanks

 Phil



What about using UPDATE OF?

CREATE TRIGGER test_simulation_trigger BEFORE DELETE OR UPDATE OF C1,C2,C3
on simulation FOR EACH ROW EXECUTE PROCEDURE trigger_function();

You can include all columns to be used for update trigger and omit parent
column.

-Luis

[GENERAL] trigger or logging

[Jay Vee]

Before running a script (invoked by .sh that will call stored procs which
may invoke other triggers), I want to capture every change made in the
database which includes the field value before the update and the field
value after the update and also capture all inserts.

With this data, I would be able to write a script that would reverse the
database to all previous values.

This may be possible with a trigger or a set of triggers but I would need
some direction here and to think of all scenarios.

before insert ( capture data that will be inserted ),
if the insert ivokes triggers that insert additional rows or updates rows I
would want to capture this.

on any update: trigger to capture the before value and the after value only
on the field values that change.

Any help or examples would be greatly appreciated.

J.V.

[GENERAL] Trigger to update table info

[Arvind Singh]

Hello friends,

I have a table with the following structure



Create table postablestatus

(

tablename
varchar(30) NOT NULL,

updatetime timestamp,

reccount
int,

size
int,

   CONSTRAINT postablestatus_pkey PRIMARY KEY(tablename)

)
WITH (oids = false);



where
a) tablename - is the name of table where a record is inserted,updated or 
deleted
b) updatetime - the time when table was updated (w/o timezone)
c) reccount - records in table
d) size - bytesize of the table

--
for example, if a table called item_master had 1 record and a
 new record is inserted and it's size is 2000 bytes

 postablestatus must have a entry like

item_master08/12/2013 12:40:00  2 2000
--

I request to know how to write a trigger in PLSQL so that the postablestatus 
is updated whenever a record is inserted,updated or deleted in item_master

PLEASE NOTE : a new record must be created if the item_master is not present in 
postablestatus

thanks in advance
arvind


  

Re: [GENERAL] Trigger to update table info

[raghu ram]

On Mon, Aug 12, 2013 at 12:46 PM, Arvind Singh arvin...@hotmail.com wrote:

 Hello friends,

 I have a table with the following structure


 Create table postablestatus

 (

 tablename varchar(30) NOT NULL,

 updatetime timestamp,

 reccount int,

 size int,

CONSTRAINT postablestatus_pkey PRIMARY KEY(tablename)

 ) WITH (oids = false);


 where
 a) tablename - is the name of table where a record is inserted,updated or
 deleted
 b) updatetime - the time when table was updated (w/o timezone)
 c) reccount - records in table
 d) size - bytesize of the table

 --
 for example, if a table called item_master had 1 record and a
  new record is inserted and it's size is 2000 bytes

  postablestatus must have a entry like

 item_master08/12/2013 12:40:00  2 2000
 --

 I request to know how to write a trigger in PLSQL so that the
 postablestatus
 is updated whenever a record is inserted,updated or deleted in item_master

 PLEASE NOTE : a new record must be created if the item_master is not
 present in postablestatus


 You will get more information from below link for creation of audit
trigger:

http://wiki.postgresql.org/wiki/Audit_trigger

Thanks  Regards
Raghu Ram

Re: [GENERAL] Trigger to update table info

[Jayadevan M]

Hi,
In case you are not keen on getting the latest and really accurate
counts/size, you could just refer the views readily available -
http://stackoverflow.com/questions/2596670/how-do-you-find-the-row-count-for-all-your-tables-in-postgres
You won't get the updatetime, though.
Regards,
Jayadevan


On Mon, Aug 12, 2013 at 12:46 PM, Arvind Singh arvin...@hotmail.com wrote:

 Hello friends,

 I have a table with the following structure


 Create table postablestatus

 (

 tablename varchar(30) NOT NULL,

 updatetime timestamp,

 reccount int,

 size int,

CONSTRAINT postablestatus_pkey PRIMARY KEY(tablename)

 ) WITH (oids = false);


 where
 a) tablename - is the name of table where a record is inserted,updated or
 deleted
 b) updatetime - the time when table was updated (w/o timezone)
 c) reccount - records in table
 d) size - bytesize of the table

 --
 for example, if a table called item_master had 1 record and a
  new record is inserted and it's size is 2000 bytes

  postablestatus must have a entry like

 item_master08/12/2013 12:40:00  2 2000
 --

 I request to know how to write a trigger in PLSQL so that the
 postablestatus
 is updated whenever a record is inserted,updated or deleted in item_master

 PLEASE NOTE : a new record must be created if the item_master is not
 present in postablestatus

 thanks in advance
 arvind

Re: [GENERAL] Trigger and deadlock

[Loïc Rollus]

Hi,

It's ok.
Before the insert, The foreign key constraint locked the row. If
transaction A and B lock the row with FK, before doing UPDATE, they were
stuck.
I found a solution by creating an before insert trigger with a simple
SELECT FROM UPDATE on the row.

Loïc


2013/7/29 Loïc Rollus loicrol...@gmail.com

  Here is pg_lock for relation Y (= 2027300)


  locktype | database | relation | page | tuple | virtualxid |
 transactionid | classid | objid | objsubid | virtualtransaction | pid  |
 mode   | granted

 --+--+--+--+---++---+-+---+--++--+--+-
  tuple|  2026760 |  2027300 |  365 |42 ||
   | |   |  | 6/313  | 9274 | ShareLock
| f
  tuple|  2026760 |  2027300 |  365 |42 ||
   | |   |  | 5/113  | 9273 | ExclusiveLock
| f
  relation |  2026760 |  2027300 |  |   ||
   | |   |  | 3/2532 | 9104 |
 AccessShareLock  | t
  relation |  2026760 |  2027300 |  |   ||
   | |   |  | 3/2532 | 9104 | RowShareLock
   | t
  relation |  2026760 |  2027300 |  |   ||
   | |   |  | 3/2532 | 9104 |
 RowExclusiveLock | t
  relation |  2026760 |  2027300 |  |   ||
   | |   |  | 5/113  | 9273 |
 AccessShareLock  | t
  relation |  2026760 |  2027300 |  |   ||
   | |   |  | 5/113  | 9273 | RowShareLock
   | t
  relation |  2026760 |  2027300 |  |   ||
   | |   |  | 5/113  | 9273 |
 RowExclusiveLock | t
  tuple|  2026760 |  2027300 |  365 |42 ||
   | |   |  | 3/2532 | 9104 | ExclusiveLock
| t
  relation |  2026760 |  2027300 |  |   ||
   | |   |  | 6/313  | 9274 |
 AccessShareLock  | t
  relation |  2026760 |  2027300 |  |   ||
   | |   |  | 6/313  | 9274 | RowShareLock
   | t


 29-07-2013 10:12:29,004 ERROR GrailsExceptionResolver - PSQLException
 occurred when processing request: [POST] /api/annotation.json
 ERROR: deadlock detected
   Détail : Process 9273 waits for ExclusiveLock on tuple (365,42) of
 relation 2027300 of database 2026760; blocked by process 9104.
 Process 9104 waits for ShareLock on transaction 1286966; blocked by
 process 9273.
   Indice : See server log for query details.
   Où : SQL statement UPDATE y
 SET count_x = count_x + 1
 WHERE y.id = NEW.y_id
 PL/pgSQL function incrementy line 6 at SQL statement. Stacktrace follows:
 org.postgresql.util.PSQLException: ERROR: deadlock detected

 I don't understand where the ExclusiveLock come from.
 Postgresql Doc says (
 http://www.postgresql.org/docs/9.1/static/explicit-locking.html):
 EXCLUSIVE: This lock mode is not automatically acquired on tables by any
 PostgreSQL command.
 In log, I just see that Hibernate just read the row y (365,42) (simple
 select). No explicit lock.

 Loïc










 2013/7/29 Loïc Rollus loicrol...@gmail.com

 Hi,

 Thanks for your quick reply!
 I found the table. But the ctid of the row has changed. But during my
 test, I update only 1 row from this table, so I know the row.

 I had already put log_statement to 'all'. It's strange because in the
 log, I only see simple SELECT ... FROM on this table (no UPDATE or SELECT
 FOR UPDATE). But I can't see request from trigger (not show by
 log_statement='all'?).

 Here is a log sample (simplify: insert into X should update the counter
 on the specific row from Y)


 1286781 22142 2013-07-26 13:55:22 CEST LOG:  execute unnamed: insert
 into X...
 1286780 22426 2013-07-26 13:55:22 CEST LOG:  execute unnamed: insert
 into X...
  it should be UPDATE on Y from trigger here
 1286781 22142 2013-07-26 13:55:22 CEST LOG:  execute select this_.id as
 id54_0_, from 
 [...22142 follow its way, commit, and carry another request ]
 1286785 22142 2013-07-26 13:55:23 CEST LOG:  execute unnamed: insert
 into X
 1286785 22142 2013-07-26 13:55:25 CEST ERROR:  deadlock detected
 1286785 22142 2013-07-26 13:55:25 CEST DETAIL:  Process 22142 waits for
 ShareLock on transaction 1286780; blocked by process 22426.
 Process 22426 waits for ExclusiveLock on tuple (365,13) of relation
 2027300 of database 2026760; blocked by process 22142.
 1286785 22142 2013-07-26 13:55:25 CEST HINT:  See server log for query
 details.
 1286785 22142 2013-07-26 13:55:25 CEST CONTEXT:  SQL statement UPDATE Y
 1286785 22142 2013-07-26 13:55:25 CEST STATEMENT:  insert into X
  Process 22426 waits for ShareLock on transaction 1286782; blocked by
 process 22429.
 1286785 22142 2013-07-26 13:55:25 

Re: [GENERAL] Trigger and deadlock

[Albe Laurenz]

Loïc Rollus wrote:
 It's ok.
 Before the insert, The foreign key constraint locked the row. If transaction 
 A and B lock the row with
 FK, before doing UPDATE, they were stuck.
 I found a solution by creating an before insert trigger with a simple 
 SELECT FROM UPDATE on the row.

You mean SELECT FOR UPDATE, richt?

Great that you could figure out where the problem was.

Yours,
Laurenz Albe

-- 
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] Trigger and deadlock

[Loïc Rollus]

Hi,

Thanks for your quick reply!
I found the table. But the ctid of the row has changed. But during my test,
I update only 1 row from this table, so I know the row.

I had already put log_statement to 'all'. It's strange because in the log,
I only see simple SELECT ... FROM on this table (no UPDATE or SELECT FOR
UPDATE). But I can't see request from trigger (not show by
log_statement='all'?).

Here is a log sample (simplify: insert into X should update the counter on
the specific row from Y)


1286781 22142 2013-07-26 13:55:22 CEST LOG:  execute unnamed: insert into
X...
1286780 22426 2013-07-26 13:55:22 CEST LOG:  execute unnamed: insert into
X...
 it should be UPDATE on Y from trigger here
1286781 22142 2013-07-26 13:55:22 CEST LOG:  execute select this_.id as
id54_0_, from 
[...22142 follow its way, commit, and carry another request ]
1286785 22142 2013-07-26 13:55:23 CEST LOG:  execute unnamed: insert into
X
1286785 22142 2013-07-26 13:55:25 CEST ERROR:  deadlock detected
1286785 22142 2013-07-26 13:55:25 CEST DETAIL:  Process 22142 waits for
ShareLock on transaction 1286780; blocked by process 22426.
Process 22426 waits for ExclusiveLock on tuple (365,13) of relation 2027300
of database 2026760; blocked by process 22142.
1286785 22142 2013-07-26 13:55:25 CEST HINT:  See server log for query
details.
1286785 22142 2013-07-26 13:55:25 CEST CONTEXT:  SQL statement UPDATE Y
1286785 22142 2013-07-26 13:55:25 CEST STATEMENT:  insert into X
Process 22426 waits for ShareLock on transaction 1286782; blocked by
process 22429.
1286785 22142 2013-07-26 13:55:25 CEST LOG:  execute S_3: ROLLBACK

I will try to query pg_locks to see more info

Thanks







2013/7/26 Albe Laurenz laurenz.a...@wien.gv.at

 Loïc Rollus wrote:
  I've try to make some concurrency robustness test with an web server app
 that use Hibernate and
  Postgres.
  It seems that my trigger make deadlock when multiple thread use it.
 
  I will try to simplify examples:
  I have a table films(id, title,director) and a table
 directors(id,name,nbreFilms). I want to
  automaticaly count directors's films.
 
  So I have this triggers after each insert on films:
 
  CREATE OR REPLACE FUNCTION incrementDirectors() RETURNS TRIGGER AS
 $incDirectors$
  BEGIN
  UPDATE directors
  SET nbreFilm = nbreFilm + 1
  WHERE directors.id = NEW.director;
  RETURN NEW;
  END;
  $incDirectors$ LANGUAGE plpgsql;
  CREATE TRIGGER triggerIncrDirectors AFTER INSERT ON films FOR EACH ROW
 EXECUTE PROCEDURE
  incrementDirectors();
 
 
  When I do a lot of INSERT films at the same time, I have this error:
 
 
 **
  
  1286785 22142 2013-07-26 13:55:25 CEST ERROR:  deadlock detected
  1286785 22142 2013-07-26 13:55:25 CEST DETAIL:  Process 22142 waits for
 ShareLock on transaction
  1286780; blocked by process 22426.
  Process 22426 waits for ExclusiveLock on tuple (365,13) of relation
 2027300 of database 2026760;
  blocked by process 22142.
  1286785 22142 2013-07-26 13:55:25 CEST HINT:  See server log for query
 details.
 
 **
  
 
  If I look in postgresql log for process, I see this (its a web app):
  1.Process 22142: take a ADD request from http,
  2.Process 22426: take a ADD request from http,
  3.Process 22142: do INSERT of new film
  4.Process 22146: do INSERT of new film
  5.Process 22142: continue request (Process 22146 seems to be blocked)
 and do COMMIT
  6.Process 22142: take a ADD request from http,
  7.Process 22142: do INSERT of new film
  8.DEADLOCK: process 22142 is waiting for 22146 and 22146 is waiting for
 22142
 
  I don't understant why the commit of the process 22142 won't unlock
 process 22426.
 
  Have you an idea?

 It would be interesting to know what relation 2027300 of database 2026760
 is.

 Then you could select the offending tuple with
 SELECT * FROM tablename WHERE ctid='(365,13)';

 What I would do is to set log_statement='all' and see what
 exact SQL statements are issued. Maybe Hibernate does something
 you do not know.

 It may also be interesting to query pg_locks immediately before
 commit to see what locks one transaction holds.

 Yours,
 Laurenz Albe

Re: [GENERAL] Trigger and deadlock

[Loïc Rollus]

 Here is pg_lock for relation Y (= 2027300)


 locktype | database | relation | page | tuple | virtualxid | transactionid
| classid | objid | objsubid | virtualtransaction | pid  |   mode
| granted
--+--+--+--+---++---+-+---+--++--+--+-
 tuple|  2026760 |  2027300 |  365 |42 ||
| |   |  | 6/313  | 9274 | ShareLock
 | f
 tuple|  2026760 |  2027300 |  365 |42 ||
| |   |  | 5/113  | 9273 | ExclusiveLock
 | f
 relation |  2026760 |  2027300 |  |   ||
| |   |  | 3/2532 | 9104 | AccessShareLock
 | t
 relation |  2026760 |  2027300 |  |   ||
| |   |  | 3/2532 | 9104 | RowShareLock
| t
 relation |  2026760 |  2027300 |  |   ||
| |   |  | 3/2532 | 9104 | RowExclusiveLock
| t
 relation |  2026760 |  2027300 |  |   ||
| |   |  | 5/113  | 9273 | AccessShareLock
 | t
 relation |  2026760 |  2027300 |  |   ||
| |   |  | 5/113  | 9273 | RowShareLock
| t
 relation |  2026760 |  2027300 |  |   ||
| |   |  | 5/113  | 9273 | RowExclusiveLock
| t
 tuple|  2026760 |  2027300 |  365 |42 ||
| |   |  | 3/2532 | 9104 | ExclusiveLock
 | t
 relation |  2026760 |  2027300 |  |   ||
| |   |  | 6/313  | 9274 | AccessShareLock
 | t
 relation |  2026760 |  2027300 |  |   ||
| |   |  | 6/313  | 9274 | RowShareLock
| t


29-07-2013 10:12:29,004 ERROR GrailsExceptionResolver - PSQLException
occurred when processing request: [POST] /api/annotation.json
ERROR: deadlock detected
  Détail : Process 9273 waits for ExclusiveLock on tuple (365,42) of
relation 2027300 of database 2026760; blocked by process 9104.
Process 9104 waits for ShareLock on transaction 1286966; blocked by process
9273.
  Indice : See server log for query details.
  Où : SQL statement UPDATE y
SET count_x = count_x + 1
WHERE y.id = NEW.y_id
PL/pgSQL function incrementy line 6 at SQL statement. Stacktrace follows:
org.postgresql.util.PSQLException: ERROR: deadlock detected

I don't understand where the ExclusiveLock come from.
Postgresql Doc says (
http://www.postgresql.org/docs/9.1/static/explicit-locking.html):
EXCLUSIVE: This lock mode is not automatically acquired on tables by any
PostgreSQL command.
In log, I just see that Hibernate just read the row y (365,42) (simple
select). No explicit lock.

Loïc










2013/7/29 Loïc Rollus loicrol...@gmail.com

 Hi,

 Thanks for your quick reply!
 I found the table. But the ctid of the row has changed. But during my
 test, I update only 1 row from this table, so I know the row.

 I had already put log_statement to 'all'. It's strange because in the log,
 I only see simple SELECT ... FROM on this table (no UPDATE or SELECT FOR
 UPDATE). But I can't see request from trigger (not show by
 log_statement='all'?).

 Here is a log sample (simplify: insert into X should update the counter on
 the specific row from Y)


 1286781 22142 2013-07-26 13:55:22 CEST LOG:  execute unnamed: insert
 into X...
 1286780 22426 2013-07-26 13:55:22 CEST LOG:  execute unnamed: insert
 into X...
  it should be UPDATE on Y from trigger here
 1286781 22142 2013-07-26 13:55:22 CEST LOG:  execute select this_.id as
 id54_0_, from 
 [...22142 follow its way, commit, and carry another request ]
 1286785 22142 2013-07-26 13:55:23 CEST LOG:  execute unnamed: insert
 into X
 1286785 22142 2013-07-26 13:55:25 CEST ERROR:  deadlock detected
 1286785 22142 2013-07-26 13:55:25 CEST DETAIL:  Process 22142 waits for
 ShareLock on transaction 1286780; blocked by process 22426.
 Process 22426 waits for ExclusiveLock on tuple (365,13) of relation
 2027300 of database 2026760; blocked by process 22142.
 1286785 22142 2013-07-26 13:55:25 CEST HINT:  See server log for query
 details.
 1286785 22142 2013-07-26 13:55:25 CEST CONTEXT:  SQL statement UPDATE Y
 1286785 22142 2013-07-26 13:55:25 CEST STATEMENT:  insert into X
  Process 22426 waits for ShareLock on transaction 1286782; blocked by
 process 22429.
 1286785 22142 2013-07-26 13:55:25 CEST LOG:  execute S_3: ROLLBACK

 I will try to query pg_locks to see more info

 Thanks







 2013/7/26 Albe Laurenz laurenz.a...@wien.gv.at

 Loïc Rollus wrote:
  I've try to make some concurrency robustness test with an web server
 app that use Hibernate and
  Postgres.
  It seems that my trigger make deadlock when multiple thread use it.
 
  I will try to simplify examples:
  I have a table films(id, 

[GENERAL] Trigger and deadlock

[Loïc Rollus]

Hello,

I've try to make some concurrency robustness test with an web server app
that use Hibernate and Postgres.
It seems that my trigger make deadlock when multiple thread use it.

I will try to simplify examples:
I have a table films(id, title,director) and a table
directors(id,name,nbreFilms). I want to automaticaly count directors's
films.

So I have this triggers after each insert on films:

CREATE OR REPLACE FUNCTION incrementDirectors() RETURNS TRIGGER AS
$incDirectors$
BEGIN
UPDATE directors
SET nbreFilm = nbreFilm + 1
WHERE directors.id = NEW.director;
RETURN NEW;
END;
$incDirectors$ LANGUAGE plpgsql;
CREATE TRIGGER triggerIncrDirectors AFTER INSERT ON films FOR EACH ROW
EXECUTE PROCEDURE incrementDirectors();


When I do a lot of INSERT films at the same time, I have this error:

**
1286785 22142 2013-07-26 13:55:25 CEST ERROR:  deadlock detected
1286785 22142 2013-07-26 13:55:25 CEST DETAIL:  Process 22142 waits for
ShareLock on transaction 1286780; blocked by process 22426.
Process 22426 waits for ExclusiveLock on tuple (365,13) of relation 2027300
of database 2026760; blocked by process 22142.
1286785 22142 2013-07-26 13:55:25 CEST HINT:  See server log for query
details.
**

If I look in postgresql log for process, I see this (its a web app):
1.Process 22142: take a ADD request from http,
2.Process 22426: take a ADD request from http,
3.Process 22142: do INSERT of new film
4.Process 22146: do INSERT of new film
5.Process 22142: continue request (Process 22146 seems to be blocked) and
do COMMIT
6.Process 22142: take a ADD request from http,
7.Process 22142: do INSERT of new film
8.DEADLOCK: process 22142 is waiting for 22146 and 22146 is waiting for
22142

I don't understant why the commit of the process 22142 won't unlock process
22426.

Have you an idea?

Thanks :)

Re: [GENERAL] Trigger and deadlock

[Albe Laurenz]

Loïc Rollus wrote:
 I've try to make some concurrency robustness test with an web server app that 
 use Hibernate and
 Postgres.
 It seems that my trigger make deadlock when multiple thread use it.
 
 I will try to simplify examples:
 I have a table films(id, title,director) and a table 
 directors(id,name,nbreFilms). I want to
 automaticaly count directors's films.
 
 So I have this triggers after each insert on films:
 
 CREATE OR REPLACE FUNCTION incrementDirectors() RETURNS TRIGGER AS 
 $incDirectors$
 BEGIN
 UPDATE directors
 SET nbreFilm = nbreFilm + 1
 WHERE directors.id = NEW.director;
 RETURN NEW;
 END;
 $incDirectors$ LANGUAGE plpgsql;
 CREATE TRIGGER triggerIncrDirectors AFTER INSERT ON films FOR EACH ROW 
 EXECUTE PROCEDURE
 incrementDirectors();
 
 
 When I do a lot of INSERT films at the same time, I have this error:
 
 **
 
 1286785 22142 2013-07-26 13:55:25 CEST ERROR:  deadlock detected
 1286785 22142 2013-07-26 13:55:25 CEST DETAIL:  Process 22142 waits for 
 ShareLock on transaction
 1286780; blocked by process 22426.
 Process 22426 waits for ExclusiveLock on tuple (365,13) of relation 2027300 
 of database 2026760;
 blocked by process 22142.
 1286785 22142 2013-07-26 13:55:25 CEST HINT:  See server log for query 
 details.
 **
 
 
 If I look in postgresql log for process, I see this (its a web app):
 1.Process 22142: take a ADD request from http,
 2.Process 22426: take a ADD request from http,
 3.Process 22142: do INSERT of new film
 4.Process 22146: do INSERT of new film
 5.Process 22142: continue request (Process 22146 seems to be blocked) and do 
 COMMIT
 6.Process 22142: take a ADD request from http,
 7.Process 22142: do INSERT of new film
 8.DEADLOCK: process 22142 is waiting for 22146 and 22146 is waiting for 22142
 
 I don't understant why the commit of the process 22142 won't unlock process 
 22426.
 
 Have you an idea?

It would be interesting to know what relation 2027300 of database 2026760 is.

Then you could select the offending tuple with
SELECT * FROM tablename WHERE ctid='(365,13)';

What I would do is to set log_statement='all' and see what
exact SQL statements are issued. Maybe Hibernate does something
you do not know.

It may also be interesting to query pg_locks immediately before
commit to see what locks one transaction holds.

Yours,
Laurenz Albe

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

[GENERAL] TRIGGER EVENT

[Sajeev Mayandi]

Hi,

I am process of porting from sybase to postgresql. Is there syntax equivalent 
of the TRIGGER EVENT Statement (Please see the sybase help for trigger event 
below) in Postgresql. NOTE the TRIGGER EVENT in sybase could be used to trigger 
an user defined event. I am not seeing any similar syntax or any equivalent 
syntax which behaves similar to it.

Appreciate your help

Thanks,

Sajeev



TRIGGER EVENT statement

Use this statement to trigger a named event. The event may be defined for event 
triggers or be a scheduled event.

Syntax

TRIGGER EVENT event-name [ ( parm = value, ... ) ]


Parameters

 *   parm = value   When a triggering condition causes an event handler to 
execute, the database server can provide context information to the event 
handler using the event_parameter function. The TRIGGER EVENT statement allows 
you to explicitly supply these parameters, to simulate a context for the event 
handler.

Remarks

Actions are tied to particular trigger conditions or schedules by a CREATE 
EVENT statement. You can use the TRIGGER EVENT statement to force the event 
handler to execute, even when the scheduled time or trigger condition has not 
occurred. TRIGGER EVENT does not execute disabled event handlers.

Each value is a string. The maximum length of each value is limited by the 
maximum page size specified by the -gp server option. If the length of value 
exceeds the page size, the string is truncated at the point at which the page 
is full.

Permissions

Must have DBA authority.

Side effects

None.

Re: [GENERAL] TRIGGER EVENT

[Alvaro Herrera]

Sajeev Mayandi wrote:
 Hi,
 
 I am process of porting from sybase to postgresql. Is there syntax equivalent 
 of the TRIGGER EVENT Statement (Please see the sybase help for trigger event 
 below) in Postgresql. NOTE the TRIGGER EVENT in sybase could be used to 
 trigger an user defined event. I am not seeing any similar syntax or any 
 equivalent syntax which behaves similar to it.

This seems similar to LISTEN / NOTIFY.


-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training  Services


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

[GENERAL] Trigger function on Warm Standby

[ning chan]

Hi all,

I have a Primary Standby setup with streaming replication.
Trigger is created on a table, and all it does it to log a message.

The trigger works as expected on Primary, however, I don't see the same on
standby.

I alter the table to have ENABLE ALWAYS TRIGGER, I verified the setting on
both Primary and Standby which is set to A.

Standby:
-bash-4.1$ psql -c select tgrelid, tgname, tgenabled from pg_trigger where
tgname='processnodeafter'
 tgrelid |  tgname  | tgenabled
-+--+---
   19218 | processnodeafter | A

Primary:
postgres=# select tgrelid, tgname, tgenabled from pg_trigger where
tgname='processnodeafter';
 tgrelid |  tgname  | tgenabled
-+--+---
   19218 | processnodeafter | A
(1 row)

Can someone tell me if the trigger will never happen on the Standby server?
Is it expected or am I missing some other settings?

Thanks~
Ning

Re: [GENERAL] Trigger function on Warm Standby

[Jerry Sievers]

ning chan ninchan8...@gmail.com writes:

 Hi all,

 I have a Primary Standby setup with streaming replication.
 Trigger is created on a table, and all it does it to log a message.

 The trigger works as expected on Primary, however, I don't see the same on 
 standby.

 I alter the table to have ENABLE ALWAYS TRIGGER, I verified the setting on 
 both Primary and Standby which is set to A.

 Standby:
 -bash-4.1$ psql -c select tgrelid, tgname, tgenabled from pg_trigger where 
 tgname='processnodeafter'
 ?tgrelid |? tgname? | tgenabled
 -+--+---
 ?? 19218 | processnodeafter | A

 Primary:
 postgres=# select tgrelid, tgname, tgenabled from pg_trigger where 
 tgname='processnodeafter';
 ?tgrelid |? tgname? | tgenabled
 -+--+---
 ?? 19218 | processnodeafter | A
 (1 row)

 Can someone tell me if the trigger will never happen on the Standby server? 
 Is it expected or am I missing some other
 settings?

It will not fire on a standby.

 Thanks~
 Ning


-- 
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] Trigger of Transaction

[Joe Van Dyk]

On Mon, Apr 1, 2013 at 8:41 PM, Juan Pablo Cook juamp...@gmail.com wrote:

 Hi everyone! I need your help with this problem.

 I'm using PostgreSQL *9.2 Server*  the latest jdbc
 driver: postgresql-9.2-1002.jdbc4.jar

 I have a many to one relation. I have this piece of code:

 con.setAutoCommit(false); //transaction block start

 // Make an insert to one table (Vector)
 // After that I insert the childs of the first table with their parent_id
 like the FK.
 con.commit(); //transaction block end

 I have this Trigger:

 CREATE *TRIGGER *trigger_update_index *AFTER INSERT*
 ON Vector FOR EACH ROW
 EXECUTE PROCEDURE update_index();

 CREATE OR REPLACE FUNCTION *update_index*() RETURNS *TRIGGER *AS
 $update_index$
 DECLARE
 BEGIN
  -- Make something
 END;
 $update_index$ LANGUAGE plpgsql;

 What's the problem? that when the trigger fire only the Parent (Vector)
 was inserted an not the childs :S so I need that records to be inserted to
 work in my function.

 I'm trying to make a Trigger, only to *execute after ALL the transaction*.
 So, after all the INSERTs INTO (like 5 or 10) I want to launch my function.
 I found some information in google about this: *Constraint Trigger*,
 that perhaps I can tell some rules before triggering but I don't know if it
 is what I need to and also don't know how to code that.


create constraint trigger my_trigger_name

after insert on products

deferrable

for each row

execute procedure blah();

constraint triggers let you change when the trigger executes.

http://www.postgresql.org/docs/9.2/static/sql-createtrigger.html says When
the CONSTRAINT option is specified, this command creates a *constraint
trigger*. This is the same as a regular trigger except that the timing of
the trigger firing can be adjusted using SET
CONSTRAINTShttp://www.postgresql.org/docs/9.2/static/sql-set-constraints.html.
Constraint triggers must be AFTER ROW triggers. They can be fired either at
the end of the statement causing the triggering event, or at the end of the
containing transaction; in the latter case they are said to be *deferred*.
A pending deferred-trigger firing can also be forced to happen immediately
by using SET CONSTRAINTS. Constraint triggers are expected to raise an
exception when the constraints they implement are violated.




 I appreciate your help a lot.

 Thanks ;)

 JP Cook

[GENERAL] Trigger of Transaction

[Juan Pablo Cook]

Hi everyone! I need your help with this problem.

I'm using PostgreSQL *9.2 Server*  the latest jdbc
driver: postgresql-9.2-1002.jdbc4.jar

I have a many to one relation. I have this piece of code:

con.setAutoCommit(false); //transaction block start

// Make an insert to one table (Vector)
// After that I insert the childs of the first table with their parent_id
like the FK.
con.commit(); //transaction block end

I have this Trigger:

CREATE *TRIGGER *trigger_update_index *AFTER INSERT*
ON Vector FOR EACH ROW
EXECUTE PROCEDURE update_index();

CREATE OR REPLACE FUNCTION *update_index*() RETURNS *TRIGGER *AS
$update_index$
DECLARE
BEGIN
 -- Make something
END;
$update_index$ LANGUAGE plpgsql;

What's the problem? that when the trigger fire only the Parent (Vector) was
inserted an not the childs :S so I need that records to be inserted to work
in my function.

I'm trying to make a Trigger, only to *execute after ALL the transaction*.
So, after all the INSERTs INTO (like 5 or 10) I want to launch my function.
I found some information in google about this: *Constraint Trigger*, that
perhaps I can tell some rules before triggering but I don't know if it is
what I need to and also don't know how to code that.

I appreciate your help a lot.

Thanks ;)

JP Cook

[GENERAL] trigger on adding / deleting / modify user on pg_authid

[Philipp Kraus]

Hello,

can I create on PG 9.1 or newer a trigger or something other, that runs a 
function if a new user is added, changed, deleted to / on the database?
I have got a usertable which stores some additional user information (full 
name, mail address, etc) and I would like to create default data
if a new authid is created. Also I would like to modifiy / delete this rows on 
the table, if the user is removed / modified.
At the moment I add the new user and create also a dataset on my table

Thanks

Phil

-- 
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] Trigger / constraint issue

[Glenn Pierce]

OK I have got it down to a simple test

#connect_string = 'dbname=bmos user=bmos'
connect_string = 'dbname=bmos user=postgres'

if __name__ == __main__:
conn = psycopg2.connect(connect_string)
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)

cur.execute(INSERT INTO sensor_values (timestamp, value, sensor_id)  \
VALUES ('2010-09-30 23:00:00.084000+00:00',
'99.8570022583', '21130'))

conn.commit()

cur.close()
conn.close()
~


When I connect with 'dbname=bmos user=bmos' everything works
but with 'dbname=bmos user=postgres'  it fails


Traceback (most recent call last):
  File ./tests/integrity_error.py, line 42, in module
cur.execute(INSERT INTO sensor_values (timestamp, value, sensor_id)  \
  File /usr/lib/python2.6/dist-packages/psycopg2/extras.py, line 118, in
execute
return _cursor.execute(self, query, vars)
psycopg2.IntegrityError: new row for relation sensor_values_2010q4
violates check constraint sensor_values_2010q4_timestamp_check
CONTEXT:  SQL statement INSERT INTO sensor_values_2010q4 VALUES ( $1 .*)
PL/pgSQL function sensor_values_timestamp_sensor_func_insert_trigger line
25 at SQL statement


Why does the connecting user effect things ?


On 6 December 2012 16:34, Glenn Pierce glennpie...@gmail.com wrote:

 so the issue comes down to this

 CREATE TABLE sensor_values_2010q4 (CHECK ( timestamp = TIMESTAMP WITH
 TIME ZONE '2010-10-01 00:00:00.00+00:00' AND timestamp  TIMESTAMP WITH
 TIME ZONE '2011-01-01 00:00:00.00+00:00' )) INHERITS (sensor_values);

 Trigger:

 IF ( NEW.timestamp = TIMESTAMP WITH TIME ZONE '2010-10-01
 00:00:00.00+00:00' AND NEW.timestamp  TIMESTAMP WITH TIME ZONE
 '2011-01-01 00:00:00.00+00:00' )
 THEN INSERT INTO sensor_values_2010q4 VALUES (NEW.*);


 Is there a way to check NEW.timestamp is correct repect to timezone ?


 On 6 December 2012 16:18, Glenn Pierce glennpie...@gmail.com wrote:

 I'm running 8.4
 timestamps are passed as strings

 I found another timestamp that fails

 2010-09-30 23:00:00.084000+00:00 UTC

 this string  was created from the timestamp  1285887600.084000
 ie  Thu, 30 Sep 2010 23:00:00  with added micro seconds

 In my timezone BST which should not be used it would be
 Fri Oct 01 2010 00:00:00 BST

 'new row for relation sensor_values_2010q4 violates check constraint
 sensor_values_2010q4_timestamp_check\nCONTEXT:  SQL statement INSERT
 INTO sensor_values_2010q4 VALUES ( $1 .*)\nPL/pgSQL function
 sensor_values_timestamp_sensor_func_insert_trigger line 25 at SQL
 statement\n'


 So it must pass the trigger date check but then fail the table constraint.


 Out of curiosity I also removed the milliseconds and that still failed

 GMT ERROR:  new row for relation sensor_values_2010q4 violates check
 constraint sensor_values_2010q4_timestamp_check
 2012-12-06 16:16:11 GMT CONTEXT:  SQL statement INSERT INTO
 sensor_values_2010q4 VALUES ( $1 .*)
 PL/pgSQL function
 sensor_values_timestamp_sensor_func_insert_trigger line 25 at SQL
 statement
 2012-12-06 16:16:11 GMT STATEMENT:  INSERT INTO sensor_values (timestamp,
 value, sensor_id) VALUES ('2010-09-30 23:00:00+00:00', '99.8570022583',
 '2113')






 On 6 December 2012 15:11, Adrian Klaver adrian.kla...@gmail.com wrote:

 On 12/06/2012 01:51 AM, Glenn Pierce wrote:

 The reason you don't see datetime values is the data I am inserting  is
 actually coming from the same table and I am selecting the timestamps
 like so

 to_char(timestamp::**timestamptz, '-MM-DD HH24:MI:SS US TZ') AS
 time

 Which are the strings I use on the insert.




 The log shows

 LOG:  statement: INSERT INTO sensor_values (timestamp, value, sensor_id)
 VALUES ('2011-06-30 23:00:00.001000+00:00', '0', '2103');


 show timezone;  shows
 TimeZone
 --
   UTC


 I set UTC from the script as well as all my values should be stored
 and received in UTC.


 The queries look identical. It's completely bizarre ?


 Well the thing I notice is the time zone is not being set. Given the
 to_char() format you have there should be a timezone abbreviation:

 test= select to_char(now(), '-MM-DD HH24:MI:SS US TZ') AS time
 test- ;
   time
 --**--
  2012-12-06 07:05:17 752641 PST
 (1 row)


 test= set time zone 'UTC';
 SET
 test= select now();
   now
 --**-
  2012-12-06 15:07:05.435609+00
 (1 row)

 test= select to_char(now(), '-MM-DD HH24:MI:SS US TZ') AS time;
   time
 --**--
  2012-12-06 15:07:20 886646 UTC

 (1 row)


 What version of Postgres are you running?
 What do the original timestamps look like?






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

Re: [GENERAL] Trigger / constraint issue

[Adrian Klaver]

On 12/06/2012 10:31 AM, Glenn Pierce wrote:

OK I have got it down to a simple test

#connect_string = 'dbname=bmos user=bmos'
connect_string = 'dbname=bmos user=postgres'

if __name__ == __main__:
 conn = psycopg2.connect(connect_string)
 cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)

 cur.execute(INSERT INTO sensor_values (timestamp, value,
sensor_id)  \
 VALUES ('2010-09-30 23:00:00.084000+00:00',
'99.8570022583', '21130'))

 conn.commit()

 cur.close()
 conn.close()
~


When I connect with 'dbname=bmos user=bmos' everything works
but with 'dbname=bmos user=postgres'  it fails


Traceback (most recent call last):
   File ./tests/integrity_error.py, line 42, in module
 cur.execute(INSERT INTO sensor_values (timestamp, value,
sensor_id)  \
   File /usr/lib/python2.6/dist-packages/psycopg2/extras.py, line 118,
in execute
 return _cursor.execute(self, query, vars)
psycopg2.IntegrityError: new row for relation sensor_values_2010q4
violates check constraint sensor_values_2010q4_timestamp_check
CONTEXT:  SQL statement INSERT INTO sensor_values_2010q4 VALUES ( $1 .*)
PL/pgSQL function sensor_values_timestamp_sensor_func_insert_trigger
line 25 at SQL statement


Why does the connecting user effect things ?


Have you done this:

http://www.postgresql.org/docs/9.2/interactive/sql-alterrole.html

ALTER ROLE name [ IN DATABASE database_name ] SET 
configuration_parameter { TO | = } { value | DEFAULT }



To check:

http://www.postgresql.org/docs/9.2/interactive/app-psql.html

\drds [ role-pattern [ database-pattern ] ]
Lists defined configuration settings. These settings can be 
role-specific, database-specific, or both. role-pattern and 
database-pattern are used to select specific roles and databases to 
list, respectively. If omitted, or if * is specified, all settings are 
listed, including those not role-specific or database-specific, 
respectively.


The ALTER ROLE and ALTER DATABASE commands are used to define per-role 
and per-database configuration settings.




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

[GENERAL] Trigger / constraint issue

[Glenn Pierce]

Hi I wonder if someone can help me I am getting a bit confused about an
error I am getting.

I have a partitioned table called sensor_values which is partitioned on a
timestamp entry.

The parent and some of the child tables are defined like so
(The child tables are yearly quarters and in my actual code they span 15
years)

CREATE TABLE sensor_values (
id SERIAL PRIMARY KEY,
timestamp timestamp with time zone NOT NULL,
value real NOT NULL DEFAULT 'NaN',
sensor_id integer NOT NULL,
FOREIGN KEY (sensor_id) REFERENCES sensors(id)
);

CREATE TABLE sensor_values_2011q2 (CHECK ( timestamp = TIMESTAMP WITH TIME
ZONE '2011-04-01 00:00:00.00+00:00' AND timestamp  TIMESTAMP WITH TIME
ZONE '2011-07-01 00:00:00.00+00:00' )) INHERITS (sensor_values);

CREATE TABLE sensor_values_2011q3 (CHECK ( timestamp = TIMESTAMP WITH TIME
ZONE '2011-07-01 00:00:00.00+00:00' AND timestamp  TIMESTAMP WITH TIME
ZONE '2011-10-01 00:00:00.00+00:00' )) INHERITS (sensor_values);

I have a trigger to determine which table the insert will occur on.
Ie

IF ( NEW.timestamp = TIMESTAMP WITH TIME ZONE '2011-04-01
00:00:00.00+00:00' AND NEW.timestamp  TIMESTAMP WITH TIME ZONE
'2011-07-01 00:00:00.00+00:00' )
THEN INSERT INTO sensor_values_2011q2 VALUES (NEW.*);
ELSIF ( NEW.timestamp = TIMESTAMP WITH TIME ZONE '2011-07-01
00:00:00.00+00:00' AND NEW.timestamp  TIMESTAMP WITH TIME ZONE
'2011-10-01 00:00:00.00+00:00' )
THEN INSERT INTO sensor_values_2011q3 VALUES (NEW.*);


The trouble is I have a python script that inserts some values and I am
getting the following error on one timestamp

The error I get is

new row for relation sensor_values_2011q3 violates check constraint
sensor_values_2011q3_timestamp_checkbr /CONTEXT:  SQL statement INSERT
INTO sensor_values_2011q3 VALUES (NEW.*)br /PL/pgSQL function
sensor_values_timestamp_sensor_func_insert_trigger line 32 at SQL
statementbr /br /

I have printed the query that causes this error and it is

INSERT INTO sensor_values (timestamp, value, sensor_id) VALUES ('2011-06-30
23:00:00.001000+00:00', '0', '2103')

So the trigger has chosen the wrong child table to insert into ?

The funny thing is from psql this insert query works fine. ?

I am not sure why 2011-06-30 23:00:00.001000+00:00 is selected to go into
sensor_values_2011q3 and not sensor_values_2011q2
I suspect its due to UTC / BST as that date time is on the border of the
contraint. Either way I not sure why I get an error and
why does PSQL work ?

Any suggestions / help would be great

Thanks

Re: [GENERAL] Trigger / constraint issue

[Adrian Klaver]

On 12/05/2012 02:24 PM, Glenn Pierce wrote:



The error I get is

new row for relation sensor_values_2011q3 violates check constraint
sensor_values_2011q3_timestamp_checkbr /CONTEXT:  SQL statement
INSERT INTO sensor_values_2011q3 VALUES (NEW.*)br /PL/pgSQL function
sensor_values_timestamp_sensor_func_insert_trigger line 32 at SQL
statementbr /br /

I have printed the query that causes this error and it is

INSERT INTO sensor_values (timestamp, value, sensor_id) VALUES
('2011-06-30 23:00:00.001000+00:00', '0', '2103')

So the trigger has chosen the wrong child table to insert into ?

The funny thing is from psql this insert query works fine. ?

I am not sure why 2011-06-30 23:00:00.001000+00:00 is selected to go
into sensor_values_2011q3 and not sensor_values_2011q2
I suspect its due to UTC / BST as that date time is on the border of the
contraint.  Either way I not sure why I get an error and
why does PSQL work ?


I would suspect  UTC/BST also.
Do you have 'mod' logging enabled?
If so what does the INSERT from the Python script show for a time value?
If not can you log the output from the Python script to get the value?



Any suggestions / help would be great

Thanks



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

[GENERAL] Trigger based replication with ENABLE REPLICA triggers and session_replication_role best practice(s)

[Achilleas Mantzios]

Hello,

we have based all our replication infrastructure on a heavily hacked version of 
DBMirror, which now runs in a 
single master (office DB) - multiple slaves (vessels DBs) mode for 80+ slaves 
and about 300 tables and in 
multiple masters (the same vessels DBs as above) (having partitions of the 
data) - single slave (the same office db as above) mode 
for just two tables. 
Now we are in the process of designing a new solution which requires to have 
some form of multi-master functionality,
without being so much concerned about conflict resolution at this stage.
The issue that we are facing is to prevent replication data originating from a 
vessel DB and consumed into the office DB,
(or replication data originating from the office DB and consumed into a vessel 
DB) to be bounced back to the
originating server because of the invocation of the DBMirror trigger.

We have thought of :
Solution 1)
explicitly disabling the triggers at the start of the transaction and 
re-enabling them,
but that would require knowledge of the name of table in question prior to 
execution of the replication command,
but in our case this not known unless parsing the SQL file which came from the 
originating server.
Since the number of those multi-master tables is not large, we could explicit 
insert the 
ALTER TABLE tblname DISABLE TRIGGER tblname_dbmirror_trig commands prior to 
actual SQL execution
for each table involved and then insert the respective 
ALTER TABLE tblname ENABLE TRIGGER tblname_dbmirror_trig commands after the 
SQL execution.
However this would require hardcoding those commands into the code which runs 
the replication SQL,
and this has many and obvious disadvantages.

Then i looked upon :
Solution 2)
the ENABLE REPLICA TRIGGER in combination with session_replication_role.
Setting default ALTER DATABASE dbname SET session_replication_role TO REPLICA  
(single master + multi-master ones) 
in combination with ALTER TABLE ENABLE REPLICA TRIGGER tblname_dbmirror_trig 
for *all* tables involved in replication
would allow the default behavior of INSERTS/UPDATES/DELETES to result in firing 
the DBmirror trigger, which would work
similar to the default way it has been running for the single direction 
replicated tables.
Then in the code which plays the replication SQLs for the multi-master tables 
we would simply set smth like :
BEGIN ;
SET local session_replication_role TO origin;
execute SQL here
END;
preventing the trigger to be called, and thus eliminating the bounce-back 
effect.

One thing that worries me is setting the database-wide session_replication_role 
to smth different than the default.
In our case, it would be ideal to be able to set session_replication_role to 
some value which would have 
the complimentary effect of REPLICA, smth like e.g. NOREPLICA which would 
result in on-demand temporary
disablement of the triggers when leaving session_replication_role to 
non-REPLICA (such as the default value=origin).

One third solution would be to :
Solution 3)
make the code updating those multi-master tables, replication-aware by putting 
SET local session_replication_role TO REPLICA; inside the affecting 
transactions. But this also has
the obvious disadvantage of making application and system logic blend together, 
and also
making the app programmer prone to errors.

The most elegant solution IMHO is the 2nd but i am concerned that setting the 
database-wide session_replication_role to smth different than the default might 
just 
hide some future risks.

What are your thoughts on that?

Thank you a lot for any input.
-
Achilleas Mantzios
IT DEV
IT DEPT
Dynacom Tankers Mgmt

Re: [GENERAL] trigger and plpgsq help needed

[SUNDAY A. OLUTAYO]

RobR, 

I have tried it 'IF FOUND' but not getting the desired result 


Thanks, 

Sunday Olutayo 


- Original Message -

From: Rob Richardson rdrichard...@rad-con.com 
To: pgsql-general@postgresql.org 
Sent: Wednesday, September 26, 2012 7:14:46 PM 
Subject: Re: [GENERAL] trigger and plpgsq help needed 



Why not use IF FOUND? 

RobR 



From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of SUNDAY A. OLUTAYO 
Sent: Wednesday, September 26, 2012 12:43 PM 
To: pgsql-general@postgresql.org 
Subject: [GENERAL] trigger and plpgsq help needed 


Dear all, 

I have issue with the red portion function below; 

This IF last_id IS NULL THEN suppose to test if last_id is NULL, that is the 
select query did not found it 
then execute the rest of the red sql but it always fail to insert the 
NEW.amount into amount , every other things fine. 

Kindly help out. 

Re: [GENERAL] trigger and plpgsq help needed

[SUNDAY A. OLUTAYO]

I have tried IF NOT FOUND THEN but not getting the desired result

Thanks, 

Sunday Olutayo 
Sadeeb Technologies Ltd 
7 Mayegun Street, Ojo 
Lagos State, Nigeria. 

Tel: +234 1 7404524 
D/L: +234 1 8169922 
Cell: +234 8054600338, 234 8096441688 
Fax: +234 8054895664 
mail: olut...@sadeeb.com 




- Original Message -
From: Chris Ernst cer...@zvelo.com
To: pgsql-general@postgresql.org
Sent: Wednesday, September 26, 2012 7:10:03 PM
Subject: Re: [GENERAL] trigger and plpgsq help needed

On 09/26/2012 10:42 AM, SUNDAY A. OLUTAYO wrote:
 This IF last_id IS NULL THEN suppose to test if last_id is NULL, that is
 the select query did not found it
 then execute the rest of the red sql but it always fail to insert the
 *NEW.amount *into*amount*, every other things fine.

I believe what you're looking for is IF NOT FOUND THEN instead of IF
last_id IS NULL THEN

Cheers!

- Chris



-- 
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] trigger and plpgsq help needed

[SUNDAY A. OLUTAYO]

Thanks you all, 

 IF last_id IS NULL THEN  eventually work 


Thanks, 

Sunday Olutayo 
Sadeeb Technologies Ltd 


- Original Message -

From: SUNDAY A. OLUTAYO olut...@sadeeb.com 
To: Rob Richardson rdrichard...@rad-con.com 
Cc: pgsql-general@postgresql.org 
Sent: Thursday, September 27, 2012 2:04:46 PM 
Subject: Re: [GENERAL] trigger and plpgsq help needed 


RobR, 

I have tried it 'IF FOUND' but not getting the desired result 


Thanks, 

Sunday Olutayo 


- Original Message -

From: Rob Richardson rdrichard...@rad-con.com 
To: pgsql-general@postgresql.org 
Sent: Wednesday, September 26, 2012 7:14:46 PM 
Subject: Re: [GENERAL] trigger and plpgsq help needed 



Why not use IF FOUND? 

RobR 



From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of SUNDAY A. OLUTAYO 
Sent: Wednesday, September 26, 2012 12:43 PM 
To: pgsql-general@postgresql.org 
Subject: [GENERAL] trigger and plpgsq help needed 


Dear all, 

I have issue with the red portion function below; 

This IF last_id IS NULL THEN suppose to test if last_id is NULL, that is the 
select query did not found it 
then execute the rest of the red sql but it always fail to insert the 
NEW.amount into amount , every other things fine. 

Kindly help out. 

[GENERAL] trigger and plpgsq help needed

[SUNDAY A. OLUTAYO]

Dear all, 

I have issue with the red portion function below; 

This IF last_id IS NULL THEN suppose to test if last_id is NULL, that is the 
select query did not found it 
then execute the rest of the red sql but it always fail to insert the 
NEW.amount into amount , every other things fine. 

Kindly help out. 



CREATE OR REPLACE FUNCTION invoice_trigger_func() RETURNS trigger AS 
$$ 

DECLARE 
last_id integer; 
current_balance_id integer; 
oldbalance numeric(10,2); 
newbalance numeric(10,2); 

BEGIN 

SELECT lastbal_id INTO last_id FROM patient 
WHERE patient_id = NEW.patient_id; 
IF last_id IS NULL THEN 
INSERT INTO balance (invoice_id, patient_id, amount) 
VALUES (NEW.invoice_id, NEW.patient_id, NEW.amount); 
UPDATE patient SET lastbal_id = (SELECT currval('balance_balance_id_seq')) 
WHERE patient_id = NEW.patient_id; 
ELSE 
SELECT amount INTO oldbalance FROM balance 
WHERE balance_id = last_id; 
INSERT INTO balance (invoice_id, patient_id, amount) 
VALUES (NEW.invoice_id, NEW.patient_id, oldbalance + NEW.amount); 
UPDATE patient SET lastbal_id = (SELECT currval('balance_balance_id_seq')) 
WHERE patient_id = NEW.patient_id; 
END IF; 
RETURN NEW; 

END; 
$$ LANGUAGE plpgsql; 



Thanks, 

Sunday Olutayo 

Re: [GENERAL] trigger and plpgsq help needed

[Rob Richardson]

Why not use IF FOUND?

RobR

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of SUNDAY A. OLUTAYO
Sent: Wednesday, September 26, 2012 12:43 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] trigger and plpgsq help needed

Dear all,

I have issue with the red portion function below;

This IF last_id IS NULL THEN suppose to test if last_id is NULL, that is the 
select query did not found it
then execute the rest of the red sql but it always fail to insert the 
NEW.amount into amount, every other things fine.

Kindly help out.

Re: [GENERAL] trigger and plpgsq help needed

[Chris Ernst]

On 09/26/2012 10:42 AM, SUNDAY A. OLUTAYO wrote:
 This IF last_id IS NULL THEN suppose to test if last_id is NULL, that is
 the select query did not found it
 then execute the rest of the red sql but it always fail to insert the
 *NEW.amount *into*amount*, every other things fine.

I believe what you're looking for is IF NOT FOUND THEN instead of IF
last_id IS NULL THEN

Cheers!

- Chris



-- 
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] trigger and plpgsq help needed

[Adrian Klaver]

On 09/26/2012 09:42 AM, SUNDAY A. OLUTAYO wrote:

Dear all,

I have issue with the red portion function below;

This IF last_id IS NULL THEN suppose to test if last_id is NULL, that is
the select query did not found it
then execute the rest of the red sql but it always fail to insert the
*NEW.amount *into*amount*, every other things fine.


Is there a NEW.amount?



Kindly help out.




Thanks,

Sunday Olutayo





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

[GENERAL] trigger on view returning created serial

[Philipp Kraus]

Hello,

I have created a view and on this view a trigger, which is called on an insert 
command on the view.
Within the trigger I run an insert on a table and one of the table fields uses 
a serial sequence, that
creates values. If I run in the trigger after the insert a return NEW, the 
field (id) which should
be set by the serial on the table is 0, so how can I get the new created serial 
on the insert command
within the trigger function?

Thanks

Phil
-- 
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] trigger on view returning created serial

[Jeff Davis]

On Tue, 2012-06-12 at 22:35 +0200, Philipp Kraus wrote:
 Hello,
 
 I have created a view and on this view a trigger, which is called on an 
 insert command on the view.
 Within the trigger I run an insert on a table and one of the table fields 
 uses a serial sequence, that
 creates values. If I run in the trigger after the insert a return NEW, the 
 field (id) which should
 be set by the serial on the table is 0, so how can I get the new created 
 serial on the insert command
 within the trigger function?

It's hard for me to tell exactly what problem you're describing, but it
sounds similar to the one solved here:

http://people.planetpostgresql.org/dfetter/index.php?/archives/66-VIEW-triggers-RETURNINGhtml

If I misunderstood, please be more descriptive about what you are trying
to do, what code you wrote, and what went wrong.

Regards,
Jeff Davis


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