Re: [GENERAL] Trigger is not working for Inserts from the application
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
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
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 mailto:bangalore.ki...@gmail.com>>
>> 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
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 mailto:t...@sss.pgh.pa.us>> wrote:
Kiran mailto:bangalore.ki...@gmail.com>>
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
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
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 mailto:t...@sss.pgh.pa.us>> wrote:
Kiran mailto:bangalore.ki...@gmail.com>>
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
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
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
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
>> mailto:adrian.kla...@aklaver.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 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
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
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
mailto:adrian.kla...@aklaver.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 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
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
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
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
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
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
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
