Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger

2010-11-08 Thread Tom Lane
=?UTF-8?B?SmFuIFVyYmHFhHNraQ==?= wulc...@wulczer.org writes: PS: I'm wondering if there's any noticable slowdown from always starting a subxact before doing SPI. Plperl users seemed not to notice, so I guess I shouldn't worry. It's not cheap :-( ... but it's *necessary*. There's no other way

Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger

2010-11-08 Thread Greg Stark
On Fri, Nov 5, 2010 at 2:56 PM, Tom Lane t...@sss.pgh.pa.us wrote: It's not cheap :-( ... but it's *necessary*.  There's no other way to get sane behavior. If the cost annoys you, you should put some effort into making subxact start/stop cheaper overall, rather than trying to avoid having one

Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger

2010-11-05 Thread Jan Urbański
On 04/11/10 20:43, Hannu Krosing wrote: On Thu, 2010-11-04 at 11:07 -0600, Alex Hunsaker wrote: On Thu, Nov 4, 2010 at 03:54, Hannu Krosing ha...@2ndquadrant.com wrote: try: plpy.execute(insert into foo values(1)) except plpy.UniqueViolation, e: plpy.notice(Ooops, you got yourself a

Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger

2010-11-05 Thread Alvaro Herrera
Excerpts from Jan Urbański's message of vie nov 05 04:19:07 -0300 2010: PS: I'm wondering if there's any noticable slowdown from always starting a subxact before doing SPI. Plperl users seemed not to notice, so I guess I shouldn't worry. I think it's more plperl users have to put up with it

Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger

2010-11-04 Thread Hannu Krosing
On Wed, 2010-11-03 at 21:43 +0100, Jan Urbański wrote: The validator is ready, once I'm done with the hash tables I'll try to fix up the error checking (get rid of the global error state) and finally do what started it all, that is make plpythonu use subtransactions for SPI and be able to do:

Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger

2010-11-04 Thread Hannu Krosing
On Thu, 2010-11-04 at 11:46 +0200, Hannu Krosing wrote: On Wed, 2010-11-03 at 21:43 +0100, Jan Urbański wrote: The validator is ready, once I'm done with the hash tables I'll try to fix up the error checking (get rid of the global error state) and finally do what started it all, that is

Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger

2010-11-04 Thread Peter Eisentraut
On ons, 2010-11-03 at 14:15 -0700, David E. Wheeler wrote: /me wants a global $dbh that mimics the DBI interface but just uses SPI under the hood. Not volunteering, either… Already exists: DBD::PgSPI. Probably needs lots of updating through. -- Sent via pgsql-hackers mailing list

Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger

2010-11-04 Thread Tom Lane
Hannu Krosing ha...@2ndquadrant.com writes: Are you sure that having each try/except use a subtransaction is the right way to do it ? Actually it is not: what you have to do is use a subtransaction in the plpy.execute() operation, so that if the called SQL operation fails, you can clean it up

Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger

2010-11-04 Thread Alex Hunsaker
On Thu, Nov 4, 2010 at 03:54, Hannu Krosing ha...@2ndquadrant.com wrote: try:     plpy.execute(insert into foo values(1)) except plpy.UniqueViolation, e:     plpy.notice(Ooops, you got yourself a SQLSTATE %d, e.sqlstate) Are you sure that having each try/except use a subtransaction is

Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger

2010-11-04 Thread Hannu Krosing
On Thu, 2010-11-04 at 11:07 -0600, Alex Hunsaker wrote: On Thu, Nov 4, 2010 at 03:54, Hannu Krosing ha...@2ndquadrant.com wrote: try: plpy.execute(insert into foo values(1)) except plpy.UniqueViolation, e: plpy.notice(Ooops, you got yourself a SQLSTATE %d, e.sqlstate) Are

Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger

2010-11-04 Thread David E. Wheeler
On Nov 4, 2010, at 4:20 AM, Peter Eisentraut wrote: On ons, 2010-11-03 at 14:15 -0700, David E. Wheeler wrote: /me wants a global $dbh that mimics the DBI interface but just uses SPI under the hood. Not volunteering, either… Already exists: DBD::PgSPI. Probably needs lots of updating

Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger

2010-11-04 Thread Alex Hunsaker
On Thu, Nov 4, 2010 at 13:43, Hannu Krosing ha...@2ndquadrant.com wrote: So your plan was to have some savepoint before each execute ? How would one rollback the latest transaction ? It is always rolled back. Its how plperl works today: create or replace function foo() returns int as $$ eval

Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger

2010-11-04 Thread Alex Hunsaker
On Thu, Nov 4, 2010 at 14:29, Alex Hunsaker bada...@gmail.com wrote: On Thu, Nov 4, 2010 at 13:43, Hannu Krosing ha...@2ndquadrant.com wrote: So your plan was to have some savepoint before each execute ? How would one rollback the latest transaction ? It is always rolled back.  Its how

Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger

2010-11-03 Thread Tom Lane
Alex Hunsaker bada...@gmail.com writes: On Mon, Nov 1, 2010 at 16:59, Tom Lane t...@sss.pgh.pa.us wrote: Surely, removing the internal name's dependency on the istrigger flag is wrong.  If you're going to maintain separate hash entries at the pltcl level, why would you want to risk collisions

Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger

2010-11-03 Thread Alex Hunsaker
On Wed, Nov 3, 2010 at 10:28, Tom Lane t...@sss.pgh.pa.us wrote: OK, applied. Thanks! I notice that plpython is also using the trigger relation's OID, but I don't know that language well enough to tell whether it really needs to. This thread was started by someone working a plpython a

Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger

2010-11-03 Thread Jan Urbański
On 03/11/10 20:57, Alex Hunsaker wrote: On Wed, Nov 3, 2010 at 10:28, Tom Lane t...@sss.pgh.pa.us wrote: OK, applied. Thanks! I notice that plpython is also using the trigger relation's OID, but I don't know that language well enough to tell whether it really needs to. This thread was

Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger

2010-11-03 Thread Alex Hunsaker
On Wed, Nov 3, 2010 at 14:43, Jan Urbański wulc...@wulczer.org wrote: By the way, I'm leaning in the direction of not using a Python dictionary for the cache, but a standard Postgres HTAB instead. It's more like other pls this way, and you can get rid of PyCObjects (which are deprecated BTW)

Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger

2010-11-03 Thread David E. Wheeler
On Nov 3, 2010, at 2:06 PM, Alex Hunsaker wrote: try: plpy.execute(insert into foo values(1)) except plpy.UniqueViolation, e: plpy.notice(Ooops, you got yourself a SQLSTATE %d, e.sqlstate) Ouuu googly eyes. [ now that eval { }, thanks to Tim Bunce, works with plperl it should be

Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger

2010-11-01 Thread Tom Lane
Andrew Dunstan and...@dunslane.net writes: On 10/31/2010 04:40 PM, Alex Hunsaker wrote: which happens because prodesc-result_in_func.fn_addr (flinfo) is NULL. That happens because when we are a trigger we don't setup input/output conversion. And with the change we get the same proc_desc for

Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger

2010-11-01 Thread Andrew Dunstan
On 11/01/2010 11:28 AM, Tom Lane wrote: The fundamental issue here is that the contents of plperl_proc_desc structs are different between the trigger and non-trigger cases. Unless you're prepared to make them the same, and guarantee that they always will be the same in future, I think that

Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger

2010-11-01 Thread Alex Hunsaker
On Mon, Nov 1, 2010 at 09:28, Tom Lane t...@sss.pgh.pa.us wrote: I think the crash is dependent on the fact that the function is created and called in the same session.  That means the validator gets called on it first, and the validator not unreasonably assumes istrigger = true, and then it

Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger

2010-11-01 Thread Alex Hunsaker
On Mon, Nov 1, 2010 at 15:24, Alex Hunsaker bada...@gmail.com wrote: houldn't cache any of the setup but just redo it all every time. Huh?  I might try and argue that if the new test was more complex than 2 compares :P.  In-fact the way it stands now we uselessly grab the functions pg_proc

Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger

2010-11-01 Thread Tom Lane
Alex Hunsaker bada...@gmail.com writes: Speaking of which, pltcl stores the trigger reloid instead of a flag (it also uses tg_reloid in the internal proname). It seems a tad excessive to have one function *per* trigger table. I looked through the history to see if there was some reason, it

Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger

2010-11-01 Thread Alex Hunsaker
On Mon, Nov 1, 2010 at 16:59, Tom Lane t...@sss.pgh.pa.us wrote: Alex Hunsaker bada...@gmail.com writes: Speaking of which, pltcl stores the trigger reloid instead of a flag (it also uses tg_reloid in the internal proname).  It seems a tad excessive to have one function *per* trigger table.

Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger

2010-10-31 Thread Jan Urbański
On 25/10/10 03:59, Andrew Dunstan wrote: On 10/24/2010 09:34 PM, Tom Lane wrote: For both trigger and non-trigger functions, we compile this ahead of the user-set function code: our $_TD; local $_TD=shift; Non-trigger functions get passed undef to correspond to this invisible

Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger

2010-10-31 Thread Tom Lane
=?UTF-8?B?SmFuIFVyYmHFhHNraQ==?= wulc...@wulczer.org writes: Seems that this circumverts some output conversion error checking, since adding the attached to the regression suite results in a segfault during the plperl installcheck. Reverting 2d01ec0708d571eef926f3f5795aa73759df5d9a fixes it.

Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger

2010-10-31 Thread Andrew Dunstan
On 10/31/2010 11:44 AM, Tom Lane wrote: =?UTF-8?B?SmFuIFVyYmHFhHNraQ==?=wulc...@wulczer.org writes: Seems that this circumverts some output conversion error checking, since adding the attached to the regression suite results in a segfault during the plperl installcheck. Reverting

Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger

2010-10-31 Thread Alex Hunsaker
On Sun, Oct 31, 2010 at 12:00, Andrew Dunstan and...@dunslane.net wrote: On 10/31/2010 11:44 AM, Tom Lane wrote: Good catch, patch reverted (and regression test added). Well, I guess that answers the question of why we needed it, which nobody could answer before. I'm not sure I exactly

Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger

2010-10-31 Thread Andrew Dunstan
On 10/31/2010 04:40 PM, Alex Hunsaker wrote: On Sun, Oct 31, 2010 at 12:00, Andrew Dunstanand...@dunslane.net wrote: On 10/31/2010 11:44 AM, Tom Lane wrote: Good catch, patch reverted (and regression test added). Well, I guess that answers the question of why we needed it, which nobody

Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger

2010-10-31 Thread Alex Hunsaker
On Sun, Oct 31, 2010 at 15:17, Andrew Dunstan and...@dunslane.net wrote: On 10/31/2010 04:40 PM, Alex Hunsaker wrote: And with the change we get the same proc_desc for triggers and non triggers, so if the trigger function gets called first, any call to the direct function will use the same

[HACKERS] why does plperl cache functions using just a bool for is_trigger

2010-10-24 Thread Jan Urbański
I see that plperl uses a triple of (function oid, is_trigger flag, user id) as a hash key for caching compiled functions. OTOH pltcl and plpgsql both use (oid, trigger relation oid, user id). Is there any reason why just using a bool as plperl does would be wrong? I'm trying to write a validator

Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger

2010-10-24 Thread Tom Lane
=?UTF-8?B?SmFuIFVyYmHFhHNraQ==?= wulc...@wulczer.org writes: I see that plperl uses a triple of (function oid, is_trigger flag, user id) as a hash key for caching compiled functions. OTOH pltcl and plpgsql both use (oid, trigger relation oid, user id). Is there any reason why just using a bool

Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger

2010-10-24 Thread Andrew Dunstan
On 10/24/2010 06:44 PM, Tom Lane wrote: =?UTF-8?B?SmFuIFVyYmHFhHNraQ==?=wulc...@wulczer.org writes: I see that plperl uses a triple of (function oid, is_trigger flag, user id) as a hash key for caching compiled functions. OTOH pltcl and plpgsql both use (oid, trigger relation oid, user id).

Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger

2010-10-24 Thread Tom Lane
Andrew Dunstan and...@dunslane.net writes: On 10/24/2010 06:44 PM, Tom Lane wrote: I'm not certain that plperl is actually correct to do it that way, but that's the basic idea. Why do we need the is_trigger flag at all for the plperl hash key? At first glance it strikes me as unnecessary.

Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger

2010-10-24 Thread Andrew Dunstan
On 10/24/2010 07:50 PM, Tom Lane wrote: Andrew Dunstanand...@dunslane.net writes: On 10/24/2010 06:44 PM, Tom Lane wrote: I'm not certain that plperl is actually correct to do it that way, but that's the basic idea. Why do we need the is_trigger flag at all for the plperl hash key? At

Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger

2010-10-24 Thread Tom Lane
Andrew Dunstan and...@dunslane.net writes: On 10/24/2010 07:50 PM, Tom Lane wrote: Andrew Dunstanand...@dunslane.net writes: Why do we need the is_trigger flag at all for the plperl hash key? At first glance it strikes me as unnecessary. We might not. Does the presence or absence of the

Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger

2010-10-24 Thread Andrew Dunstan
On 10/24/2010 09:34 PM, Tom Lane wrote: For both trigger and non-trigger functions, we compile this ahead of the user-set function code: our $_TD; local $_TD=shift; Non-trigger functions get passed undef to correspond to this invisible argument, while trigger functions get passed the