[GENERAL] Trigger (Transaction related)

[Harpreet Dhaliwal]

Hi,I've written a trigger after insert on a table (parser) and the trigger calls a function that dynamically loads a shared object written in C.This shared object intends to use the newly inserted row in the table on which trigger is written and insert values in another table, something like this,
sprintf(buffer, "INSERT INTO headers (id, header_content) VALUES ( %d, SELECT contents FROM parser WHERE id = %d ), id, id);SPI_exec(buffer,0).Above line of code is not able to read the values of newly inserted code 
i.e SELECT contents FROM parser WHERE id = %d and throws an error.Is it because its still the same transaction going on and the insert fired on parser talbe won't be reflected in the select query in the shared object which is a part of the same transaction???
Regards,~Harpreet

Re: [GENERAL] Trigger (Transaction related)

[Martijn van Oosterhout]

On Fri, Sep 01, 2006 at 02:51:59AM -0400, Harpreet Dhaliwal wrote:
> Hi,
> I've written a trigger after insert on a table (parser) and the trigger
> calls a function that dynamically loads a shared object written in C.
> 
> This shared object intends to use the newly inserted row in the table on
> which trigger is written and insert values in another table, something like
> this,



> Above line of code is not able to read the values of newly inserted code i.e
> SELECT contents FROM parser WHERE id = %d
> and throws an error.

The new values are in the "NEW" pseudo-tuple. See the trigger
documentation on how to access it. In the pl/sql and pl/pgsql languages
you just say "NEW.". Not sure about C though.

> Is it because its still the same transaction going on and the insert fired
> on parser talbe won't be reflected in the select query in the shared object
> which is a part of the same transaction???

I don't think its visible in the table yet for a BEFORE INSERT trigger.
AFTER INSERT maybe, but in either case, the NEW tuple has the actual
values.

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


signature.asc
Description: Digital signature

Re: [GENERAL] Trigger (Transaction related)

[Harpreet Dhaliwal]

If that is the case then why does it throw error in one on the insert queries in the shared object written in SPI without inserting the row on the table on which record is inserted.Follwing query in the shared object throws an error.
INSERT INTO headers (id, header_content) VALUES (1, SELECT raw_email FROM parser WHERE id = 1)Error is ERROR:  syntax error at or near "SELECT" at character 53Regardless the error in the shared object, why doesn't it insert the row in the table on which AFTER INSERT trigger is written?
Thanks,~HarpreetOn 9/1/06, Martijn van Oosterhout  wrote:
On Fri, Sep 01, 2006 at 02:51:59AM -0400, Harpreet Dhaliwal wrote:> Hi,> I've written a trigger after insert on a table (parser) and the trigger> calls a function that dynamically loads a shared object written in C.
>> This shared object intends to use the newly inserted row in the table on> which trigger is written and insert values in another table, something like> this,> Above line of code is not able to read the values of newly inserted code 
i.e> SELECT contents FROM parser WHERE id = %d> and throws an error.The new values are in the "NEW" pseudo-tuple. See the triggerdocumentation on how to access it. In the pl/sql and pl/pgsql languages
you just say "NEW.". Not sure about C though.> Is it because its still the same transaction going on and the insert fired> on parser talbe won't be reflected in the select query in the shared object
> which is a part of the same transaction???I don't think its visible in the table yet for a BEFORE INSERT trigger.AFTER INSERT maybe, but in either case, the NEW tuple has the actualvalues.
Hope this helps,--Martijn van Oosterhout      http://svana.org/kleptog/> From each according to his ability. To each according to his ability to litigate.
-BEGIN PGP SIGNATURE-Version: GnuPG v1.4.1 (GNU/Linux)iD8DBQFE99qBIB7bNG8LQkwRAq7KAJ4p+rTE1TYgKjpydTlb30YY0yDdFgCghDiPYj3c+7ayJihJ9LKYwrFYoX0==4W51-END PGP SIGNATURE-

Re: [GENERAL] Trigger (Transaction related)

[Roman Neuhauser]

# [EMAIL PROTECTED] / 2006-09-01 03:19:03 -0400:
> If that is the case then why does it throw error in one on the insert
> queries in the shared object written in SPI without inserting the row on the
> table on which record is inserted.
> 
> Follwing query in the shared object throws an error.
> 
> 
> INSERT INTO headers (id, header_content) VALUES (1, SELECT raw_email FROM
> parser WHERE id = 1)
> 
> Error is
> ERROR:  syntax error at or near "SELECT" at character 53

Because there's a syntax error.

test=# INSERT INTO headers (id, header_content) VALUES (1, SELECT raw_email 
FROM parser WHERE id = 1);
ERROR:  syntax error at or near "SELECT" at character 53
LINE 1: ...SERT INTO headers (id, header_content) VALUES (1, SELECT raw...
 ^
test=# INSERT INTO headers (id, header_content) VALUES (1, (SELECT 
raw_email FROM parser WHERE id = 1));
ERROR:  relation "headers" does not exist
test=# 

> Regardless the error in the shared object, why doesn't it insert the
> row in the table on which AFTER INSERT trigger is written?

Perhaps you should verify the syntax of your queries in a less
demanding environment, like psql, first.

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

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

Re: [GENERAL] Trigger (Transaction related)

[Harpreet Dhaliwal]

Thats OK, but my question is why did the original insert not take place. Syntax error was in the select query that is fired as a result of the triggerThanks,~Harpreet
On 9/1/06, Roman Neuhauser <[EMAIL PROTECTED]> wrote:
# [EMAIL PROTECTED] / 2006-09-01 03:19:03 -0400:> If that is the case then why does it throw error in one on the insert> queries in the shared object written in SPI without inserting the row on the
> table on which record is inserted.>> Follwing query in the shared object throws an error.>>> INSERT INTO headers (id, header_content) VALUES (1, SELECT raw_email FROM> parser WHERE id = 1)
>> Error is> ERROR:  syntax error at or near "SELECT" at character 53Because there's a syntax error.test=# INSERT INTO headers (id, header_content) VALUES (1, SELECT raw_email FROM parser WHERE id = 1);
ERROR:  syntax error at or near "SELECT" at character 53LINE 1: ...SERT INTO headers (id, header_content) VALUES (1, SELECT raw... ^
test=# INSERT INTO headers (id, header_content) VALUES (1, (SELECT raw_email FROM parser WHERE id = 1));ERROR:  relation "headers" does not existtest=#> Regardless the error in the shared object, why doesn't it insert the
> row in the table on which AFTER INSERT trigger is written?Perhaps you should verify the syntax of your queries in a lessdemanding environment, like psql, first.--How many Vietnam vets does it take to screw in a light bulb?
You don't know, man.  You don't KNOW.Cause you weren't THERE. http://bash.org/?255991

Re: [GENERAL] Trigger (Transaction related)

[Martijn van Oosterhout]

On Fri, Sep 01, 2006 at 03:33:55AM -0400, Harpreet Dhaliwal wrote:
> Thats OK, but my question is why did the original insert not take place.
> Syntax error was in the select query that is fired as a result of the
> trigger

The trigger is part of the transaction. If the trigger fails, so does
the whole transaction.

It would be kinda silly if a transaction was allowed to succeed even
though not all the triggers were run.

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


signature.asc
Description: Digital signature

Re: [GENERAL] Trigger (Transaction related)

[Tom Lane]

"Harpreet Dhaliwal" <[EMAIL PROTECTED]> writes:
> sprintf(buffer, "INSERT INTO headers (id, header_content) VALUES ( %d,
> SELECT contents FROM parser WHERE id = %d ), id, id);
> SPI_exec(buffer,0).

You're short some parentheses --- try executing a comparable query
manually.

regards, tom lane

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

Re: [GENERAL] Trigger (Transaction related)

[Harpreet Dhaliwal]

Exactly Tom,I was missing a few paranthesis like a nut.Thanks for pointing out.Thanks and regards~HarpreetOn 9/1/06, Tom Lane <
[EMAIL PROTECTED]> wrote:"Harpreet Dhaliwal" <
[EMAIL PROTECTED]> writes:> sprintf(buffer, "INSERT INTO headers (id, header_content) VALUES ( %d,> SELECT contents FROM parser WHERE id = %d ), id, id);
> SPI_exec(buffer,0).You're short some parentheses --- try executing a comparable querymanually.regards, tom lane