Re: [GENERAL] plperl function
>
> ERROR: operator does not exist: integer = integer[]
> HINT: No operator matches the given name and argument type(s).
> You might need to add explicit type casts.
>
Sounds like you are trying to return directly the query.
You must do a loop with that query inside (cursor) and
use next clause (to return one by one the values)
OR
return the query directly using return query (i don't remember
right now the plperl function to do that)
CREATE OR REPLACE FUNCTION perl_func()
RETURNS SETOF INTEGER AS $$
my $rv = spi_exec_query('select id from ctable where cmid in (
select i from mlist( 168.4, 55.2, 0.1 ) );');
my $status = $rv->{status};
my $nrows = $rv->{processed};
foreach my $rn (0..$nrows -1) {
return_next($row->{i});
}
return undef;
$$ LANGUAGE plperl;
SELECT * FROM perl_func();
I didn't test it, if you have problems, i'll try to help again :)
The error is telling you that could not return an array into
integer.
--
Emanuel Calvo Franco
Database consultant at:
www.siu.edu.ar
www.emanuelcalvofranco.com.ar
--
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] plperl function called > once in cascading triggers
Martijn van Oosterhout wrote: On Wed, Mar 14, 2007 at 08:09:24PM -0400, Kenneth Downs wrote: What I have noticed is that once the innermost instance exits, none of the outer instances execute any further, suggesting that the plperl routine is not "re-entrant" (if I am using that term correctly). Doesn't sound right, do you have a test case? Have a nice day, Yes, but it is all tied up in my framework. I'll put together a hardcoded example.
Re: [GENERAL] plperl function called > once in cascading triggers
On Wed, Mar 14, 2007 at 08:09:24PM -0400, Kenneth Downs wrote: > What I have noticed is that once the innermost instance exits, none of > the outer instances execute any further, suggesting that the plperl > routine is not "re-entrant" (if I am using that term correctly). Doesn't sound right, do you have a test case? 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] PLPERL Function very Slow
1 -- Drop your indexes on the table to be inserted into. 2 -- Execute a BEGIN transaction 3 -- Execute your inserts. 4 -- Execute a commit or rollback and END transaction 5 -- Rebuild / recreate your indexes "Alex" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hi, > i am having a problem with a plperl function i am trying to write. > (using 8.1.4) > > the function does a select (ca 30,000 rows) using > > spi_query($query); while (my $row = spi_fetchrow($handle)) > > and within the while loop inserts the record into a table using > spi_exec_query($query); > > The initial select is pretty fast and first inserts very fast, but after > a few thousand inserts the inserts start to slow down until it crawls. > Writing the same in a normal perl script takes less than 90 seconds > while the function is taking 10 minutes. > > My guess is that it is all memory related and was wondering if there is > a memory leak (as I read in some mails) or if there is a better way to > do what I want. > > Also is there a way to commit transactions within a stored procedure / > function? I noticed that if I do a delete and insert within the same > function that the deletes are not committed until the function returns. > > Thanks for any advice. > > Alex > > > > > > ---(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 > ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PLPERL Function very Slow
Alex <[EMAIL PROTECTED]> writes: > The initial select is pretty fast and first inserts very fast, but after > a few thousand inserts the inserts start to slow down until it crawls. > Writing the same in a normal perl script takes less than 90 seconds > while the function is taking 10 minutes. Can you provide a self-contained test case that shows this behavior? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] plperl function to return nulls
On Mon, Sep 19, 2005 at 10:52:23AM +1200, Brent Wood wrote: > I have a plperl function returning an int. The int is returned as the > result of a system call. > > It is set to return a null if one of the inputs is null, but I can't see > how to return a null if the result is indeterminate. The function > currently returns a 0 instead. > > How do I stick an if in the function to return a null where appropriate? >From the PL/Perl documentation: "As shown above, to return an SQL null value from a PL/Perl function, return an undefined value." http://www.postgresql.org/docs/8.0/interactive/plperl.html Is that what you're looking for? -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] plperl function fails to "fire" Slony trigger
On 4/22/2005 2:08 PM, Tom Lane wrote: Sven Willenberger <[EMAIL PROTECTED]> writes: We have a replication set up between 2 servers using Slony; both are runnind PostgreSQL 8.0.1. The issue is that when updates/inserts are made to a replicated table, the replication does not occur; apparently this is due to spi_exec somehow not allowing/causing the slony trigger function to fire. Yuck :-(. The only idea that comes to mind is that 8.0 changed the timing of trigger firing --- the triggers are probably firing while your function still has control, whereas in earlier releases they'd only fire after it returns. Could this be breaking some assumption Slony makes about the order of operations? regards, tom lane Slony triggers are AFTER ROW triggers. All they do is one SPI_execp() to insert the log row. The only way that could possibly be suppressed is by bypassing the executor and doing direct heap_ access. So how does plperl manage that? Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] plperl function fails to "fire" Slony trigger
On Fri, Apr 22, 2005 at 03:09:13PM -0400, Sven Willenberger wrote:
> On Fri, 2005-04-22 at 14:43 -0400, Alvaro Herrera wrote:
> > On Fri, Apr 22, 2005 at 02:24:57PM -0400, Jan Wieck wrote:
> > > On 4/22/2005 2:08 PM, Tom Lane wrote:
> > >
> > > >Sven Willenberger <[EMAIL PROTECTED]> writes:
> > > >>We have a replication set up between 2 servers using Slony; both are
> > > >>runnind PostgreSQL 8.0.1. The issue is that when updates/inserts are
> > > >>made to a replicated table, the replication does not occur; apparently
> > > >>this is due to spi_exec somehow not allowing/causing the slony trigger
> > > >>function to fire.
> > > >
> > > >Yuck :-(. The only idea that comes to mind is that 8.0 changed the
> > > >timing of trigger firing --- the triggers are probably firing while your
> > > >function still has control, whereas in earlier releases they'd only fire
> > > >after it returns. Could this be breaking some assumption Slony makes
> > > >about the order of operations?
> > >
> > > Slony triggers are AFTER ROW triggers. All they do is one SPI_execp() to
> > > insert the log row. The only way that could possibly be suppressed is by
> > > bypassing the executor and doing direct heap_ access.
> > >
> > > So how does plperl manage that?
> >
> > It doesn't; it only uses SPI. I guess we would need the original
> > function to see what is really happening.
> >
>
> If by the "original function" you mean the plperl function, it was in
> the thread parent; in essense it runs an "insert into tablename (cols)
> select vals from " query. The Slony trigger is:
> "_T2_logtrigger_10" AFTER INSERT OR DELETE OR UPDATE ON inter_rootdecks
> FOR EACH ROW EXECUTE PROCEDURE "_T2".logtrigger('_T2', '10', 'kkkv')
>
> If by "orginal function" you mean the logtrigger (slony) function it
> would appear from a cursory glance at the source that logtrigger
> accesses the heap (HeapTuple). It would appear the same manipulation is
> utilized by plperl's SPI calls. How this all interacts with the
> "executor" is, admittedly, beyond the scope of my comprehension at this
> point ...
Hmm. There are no direct heap manipulations that I can see in plperl.
The HeapTuple functions that it uses are merely to check the catalog in
lookup of the function, AFAICS.
I don't have Slony installed here, so I can't run a simple test
directly, but I think the problem lies elsewhere.
The only idea that comes to mind is that the new tuples somehow fail the
snapshot test of the Slony trigger ... not sure if that makes any sense,
because the trigger should affect all new tuples, I imagine.
--
Alvaro Herrera (<[EMAIL PROTECTED]>)
"El número de instalaciones de UNIX se ha elevado a 10,
y se espera que este número aumente" (UPM, 1972)
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] plperl function fails to "fire" Slony trigger
Sven Willenberger <[EMAIL PROTECTED]> writes: > We have a replication set up between 2 servers using Slony; both are > runnind PostgreSQL 8.0.1. The issue is that when updates/inserts are > made to a replicated table, the replication does not occur; apparently > this is due to spi_exec somehow not allowing/causing the slony trigger > function to fire. Yuck :-(. The only idea that comes to mind is that 8.0 changed the timing of trigger firing --- the triggers are probably firing while your function still has control, whereas in earlier releases they'd only fire after it returns. Could this be breaking some assumption Slony makes about the order of operations? regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] plperl function fails to "fire" Slony trigger
On Fri, 2005-04-22 at 14:43 -0400, Alvaro Herrera wrote:
> On Fri, Apr 22, 2005 at 02:24:57PM -0400, Jan Wieck wrote:
> > On 4/22/2005 2:08 PM, Tom Lane wrote:
> >
> > >Sven Willenberger <[EMAIL PROTECTED]> writes:
> > >>We have a replication set up between 2 servers using Slony; both are
> > >>runnind PostgreSQL 8.0.1. The issue is that when updates/inserts are
> > >>made to a replicated table, the replication does not occur; apparently
> > >>this is due to spi_exec somehow not allowing/causing the slony trigger
> > >>function to fire.
> > >
> > >Yuck :-(. The only idea that comes to mind is that 8.0 changed the
> > >timing of trigger firing --- the triggers are probably firing while your
> > >function still has control, whereas in earlier releases they'd only fire
> > >after it returns. Could this be breaking some assumption Slony makes
> > >about the order of operations?
> >
> > Slony triggers are AFTER ROW triggers. All they do is one SPI_execp() to
> > insert the log row. The only way that could possibly be suppressed is by
> > bypassing the executor and doing direct heap_ access.
> >
> > So how does plperl manage that?
>
> It doesn't; it only uses SPI. I guess we would need the original
> function to see what is really happening.
>
If by the "original function" you mean the plperl function, it was in
the thread parent; in essense it runs an "insert into tablename (cols)
select vals from " query. The Slony trigger is:
"_T2_logtrigger_10" AFTER INSERT OR DELETE OR UPDATE ON inter_rootdecks
FOR EACH ROW EXECUTE PROCEDURE "_T2".logtrigger('_T2', '10', 'kkkv')
If by "orginal function" you mean the logtrigger (slony) function it
would appear from a cursory glance at the source that logtrigger
accesses the heap (HeapTuple). It would appear the same manipulation is
utilized by plperl's SPI calls. How this all interacts with the
"executor" is, admittedly, beyond the scope of my comprehension at this
point ...
Sven
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] plperl function fails to "fire" Slony trigger
On Fri, Apr 22, 2005 at 02:24:57PM -0400, Jan Wieck wrote: > On 4/22/2005 2:08 PM, Tom Lane wrote: > > >Sven Willenberger <[EMAIL PROTECTED]> writes: > >>We have a replication set up between 2 servers using Slony; both are > >>runnind PostgreSQL 8.0.1. The issue is that when updates/inserts are > >>made to a replicated table, the replication does not occur; apparently > >>this is due to spi_exec somehow not allowing/causing the slony trigger > >>function to fire. > > > >Yuck :-(. The only idea that comes to mind is that 8.0 changed the > >timing of trigger firing --- the triggers are probably firing while your > >function still has control, whereas in earlier releases they'd only fire > >after it returns. Could this be breaking some assumption Slony makes > >about the order of operations? > > Slony triggers are AFTER ROW triggers. All they do is one SPI_execp() to > insert the log row. The only way that could possibly be suppressed is by > bypassing the executor and doing direct heap_ access. > > So how does plperl manage that? It doesn't; it only uses SPI. I guess we would need the original function to see what is really happening. -- Alvaro Herrera (<[EMAIL PROTECTED]>) "Limítate a mirar... y algun día veras" ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] PLPERL function error - utf-8 to iso8859-1
Ah there in lies the problem. I have my database encoding set to LATIN1
and I have this value stored in my table and can even write SQL to search
against it:
select *
from mdc_products
where description ~* '?'
Patrick Hatcher
Macys.Com
Legacy Integration Developer
415-422-1610 office
HatcherPT - AIM
Tom Lane
<[EMAIL PROTECTED] To: "Patrick Hatcher" <[EMAIL
PROTECTED]>
a.us>cc: [EMAIL PROTECTED]
Subject: Re: [GENERAL] PLPERL function
error - utf-8 to iso8859-1
10/14/2003
09:07 PM
"Patrick Hatcher" <[EMAIL PROTECTED]> writes:
> Trying to create a plperl function to strip non-friendly mainframe
> characters from a string. However, when I try to add the Trademark
symbol
> (â"¢) as a replace criteria, PG spits back an error:
> ERROR: Could not convert UTF-8 to ISO8859-1
AFAICT this means that the trademark symbol is not in the character set
that you've specified to be used in the database; accordingly there's no
need to try to prevent it from being stored...
Perhaps you should have selected the database encoding to be the same as
what you're using on the client side.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] PLPERL function error - utf-8 to iso8859-1
"Patrick Hatcher" <[EMAIL PROTECTED]> writes: > Trying to create a plperl function to strip non-friendly mainframe > characters from a string. However, when I try to add the Trademark symbol > (™) as a replace criteria, PG spits back an error: > ERROR: Could not convert UTF-8 to ISO8859-1 AFAICT this means that the trademark symbol is not in the character set that you've specified to be used in the database; accordingly there's no need to try to prevent it from being stored... Perhaps you should have selected the database encoding to be the same as what you're using on the client side. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
