Re: Trigger is not working for Inserts from the application

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 <unnamed>: 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 <unnamed>: 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 <adrian(dot)klaver(at)aklaver(dot)com>
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
>> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>> 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 <http://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
>> <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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>