Thread: Function Hangs

Function Hangs

From
"Patrick Hatcher"
Date:
I created the following function below.  It hangs when I need to do the
table update.  Originally I wrote this function in MS SQL using cursors.
However reading the documentation, I couldn't figure out how to loop
through a cursor so I tried the FOR...LOOP instead.

Any help would be appreciated.
TIA

CREATE FUNCTION "removeduppchange"() RETURNS "int4" AS '

DECLARE
     v_prevProd int4;
     v_count int4;
     myRec  RECORD;

BEGIN
     v_prevProd := 0;
     v_count :=0;
     FOR myRec IN
     -- Find all duplicate records that are still valid according to date
                            Select o.keyf_products ,o.keyp_priceschedule
                           from ozpricing o,
                          (
                           Select keyf_products from ozpricing
                          group by keyf_products
                          having count(*) >1
                          ) a
                         where o.keyf_products =a.keyf_products
                         and o.keyf_products =76
                        and (date_end >= ''now'' and date_start <= ''now'')
                       order by  keyf_products,date_start desc  LOOP

 --If we find that the previous keyF matches the current keyF then lets
mark it.
             if myRec.keyf_products = v_prevProd then
   begin
/*
PROBLEM HAPPENS HERE.  If I remove update statement, I get the correct
record
count of how many records should be marked

*/
              update ozpricing set useascurrprice = 0 where
keyp_priceschedule = myRec.keyp_priceschedule;
           v_count =  v_count + 1;
   end;
             end if;
           v_prevProd := myRec.keyf_products;
      END LOOP;
     RETURN  v_count;
END; ' LANGUAGE 'plpgsql';

Patrick Hatcher
Macys.Com
Legacy Integration Developer
415-932-0610 office




Re: Function Hangs

From
"Josh Berkus"
Date:
Patrick,

Oh, and take out this BEGIN and END marker, it's confusing the parser:

>    begin
<snip>
>               update ozpricing set useascurrprice = 0 where
> keyp_priceschedule = myRec.keyp_priceschedule;
>            v_count =  v_count + 1;
>    end;

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

Re: Function Hangs

From
"Josh Berkus"
Date:
Patrick,

> Any help would be appreciated.
> TIA

Take a look at your postgres log and see if you're getting a deadlock
 warning.  The last time I had a looping function lock, it was because
 I was (unintentionally) trying to update the same records multiple
 times ...

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

Re: Function Hangs

From
"Patrick Hatcher"
Date:
Thanks.  Stupid question:  Where would I find the database log?

Patrick Hatcher
Macys.Com
Legacy Integration Developer
415-932-0610 office





    
                    "Josh Berkus"
    
                    <josh@agliodbs.com>           To:     "Patrick Hatcher" <PHatcher@macys.com>,
    
                    Sent by:                       pgsql-novice@postgresql.org
    
                    pgsql-novice-owner@post       cc:
    
                    gresql.org                    Subject:     Re: [NOVICE] Function Hangs
    

    

    
                    02/12/2002 09:43 PM
    

    

    




Patrick,

> Any help would be appreciated.
> TIA

Take a look at your postgres log and see if you're getting a deadlock
 warning.  The last time I had a looping function lock, it was because
 I was (unintentionally) trying to update the same records multiple
 times ...

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html





Re: Function Hangs

From
"Patrick Hatcher"
Date:
It was a Mandrake install, so it auto-starts during system start up.

Patrick Hatcher
Macys.Com
Legacy Integration Developer
415-932-0610 office





                    "Josh Berkus"
                    <josh@agliodbs       To:     "Patrick Hatcher" <PHatcher@macys.com>
                    .com>                cc:     pgsql-novice@postgresql.org
                                         Subject:     Re: [NOVICE] Function Hangs
                    02/13/2002
                    11:30 AM






Patrick,

> Thanks.  Stupid question:  Where would I find the database log?

Wherever you put it in your startup options for Postmaster.  I usually
 put mine in /var/log/postgresql.  How do you start postmaster?

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco





Re: Function Hangs

From
"Josh Berkus"
Date:
Patrick,

> Thanks.  Stupid question:  Where would I find the database log?

Wherever you put it in your startup options for Postmaster.  I usually
 put mine in /var/log/postgresql.  How do you start postmaster?

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

Re: Function Hangs

From
"Patrick Hatcher"
Date:
I found the Postmaster startup file.  Here is the start params:

su -l postgres -c "/usr/bin/pg_ctl -D $PGDATA -p /usr/bin/postmaster -o "
-i" start >/dev/null 2>&1"

would the name of the log file go where the /dev/null is?

Thanks again for the help

Patrick Hatcher
Macys.Com
Legacy Integration Developer
415-932-0610 office





                    Patrick
                    Hatcher              To:     "Josh Berkus" <josh@agliodbs.com>@FDS-NOTES
                                         cc:     pgsql-novice@postgresql.org
                    02/13/2002           Subject:     Re: [NOVICE] Function Hangs(Document link: Patrick Hatcher)
                    11:33 AM





It was a Mandrake install, so it auto-starts during system start up.

Patrick Hatcher
Macys.Com
Legacy Integration Developer
415-932-0610 office





                    "Josh Berkus"
                    <josh@agliodbs       To:     "Patrick Hatcher" <PHatcher@macys.com>
                    .com>                cc:     pgsql-novice@postgresql.org
                                         Subject:     Re: [NOVICE] Function Hangs
                    02/13/2002
                    11:30 AM






Patrick,

> Thanks.  Stupid question:  Where would I find the database log?

Wherever you put it in your startup options for Postmaster.  I usually
 put mine in /var/log/postgresql.  How do you start postmaster?

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco







Re: Function Hangs

From
Stephen Ingram
Date:
On Wednesday 13 February 2002 02:48 pm, Patrick Hatcher wrote:
> I found the Postmaster startup file.  Here is the start params:
>
> su -l postgres -c "/usr/bin/pg_ctl -D $PGDATA -p /usr/bin/postmaster -o "
> -i" start >/dev/null 2>&1"
>
> would the name of the log file go where the /dev/null is?
>

Hi,

Josh will answer anyway ( :) ), but this tripped me up too when I started.

I know the idea is for it to be configured for ones own site,
but it seems this is one of the first questions people who start
with PG ask.

It would be kind of nice if the startup scripts came as

POSTMASTER_LOG=${POSTMASTER_LOG:-"/tmp/postmaster.log"}
export POSTMASTER_LOG

su -l postgres -c "/usr/bin/pg_ctl -D $PGDATA -p /usr/bin/postmaster -o "-i"
    start >${POSTMASTER_LOG} 2>&1"

or some such.

Just waffling on...

Steve


> Thanks again for the help
>
> Patrick Hatcher
> Macys.Com
> Legacy Integration Developer
> 415-932-0610 office
>
>
>
>
>
>                     Patrick
>                     Hatcher              To:     "Josh Berkus"
> <josh@agliodbs.com>@FDS-NOTES cc:     pgsql-novice@postgresql.org
> 02/13/2002           Subject:     Re: [NOVICE] Function Hangs(Document
> link: Patrick Hatcher) 11:33 AM
>
>
>
>
>
> It was a Mandrake install, so it auto-starts during system start up.
>
> Patrick Hatcher
> Macys.Com
> Legacy Integration Developer
> 415-932-0610 office
>
>
>
>
>
>                     "Josh Berkus"
>                     <josh@agliodbs       To:     "Patrick Hatcher"
> <PHatcher@macys.com> .com>                cc:
> pgsql-novice@postgresql.org Subject:     Re: [NOVICE] Function Hangs
> 02/13/2002
>                     11:30 AM
>
>
>
>
>
>
> Patrick,
>
> > Thanks.  Stupid question:  Where would I find the database log?
>
> Wherever you put it in your startup options for Postmaster.  I usually
>  put mine in /var/log/postgresql.  How do you start postmaster?
>
> -Josh
>
> ______AGLIO DATABASE SOLUTIONS___________________________
>                                        Josh Berkus
>   Complete information technology      josh@agliodbs.com
>    and data management solutions       (415) 565-7293
>   for law firms, small businesses        fax 621-2533
>     and non-profit organizations.      San Francisco
>
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

Re: Function Hangs

From
"Josh Berkus"
Date:
Patrick,

> > su -l postgres -c "/usr/bin/pg_ctl -D $PGDATA -p
>  /usr/bin/postmaster -o "
> > -i" start >/dev/null 2>&1"
> >
> > would the name of the log file go where the /dev/null is?

Nope.  Instead, right after the word "postmaster", insert:
 -l /var/log/postgresql
.. or wherever you want to store the log.  You should also make
 provisions in CRON to clear the log periodically, or it will continue
 to grow forever.

> Josh will answer anyway ( :) ), but this tripped me up too when I
>  started.

Postgres Novice Help Desk, at your service!  ;-)

> I know the idea is for it to be configured for ones own site,
> but it seems this is one of the first questions people who start
> with PG ask.

Yeah.  I don't know why most Postgres startup scripts ignore the log
 file.  Every time I do a Postgres install, I have to alter the startup
 script by hand.

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

Re: Function Hangs

From
"Patrick Hatcher"
Date:
Ah.  Thank you ALL!!!

Patrick Hatcher
Macys.Com
Legacy Integration Developer
415-932-0610 office





                    "Josh Berkus"
                    <josh@agliodbs       To:     ingram@samsix.com, "Patrick Hatcher" <PHatcher@macys.com>
                    .com>                cc:     pgsql-novice@postgresql.org
                                         Subject:     Re: [NOVICE] Function Hangs
                    02/13/2002
                    01:17 PM






Patrick,

> > su -l postgres -c "/usr/bin/pg_ctl -D $PGDATA -p
>  /usr/bin/postmaster -o "
> > -i" start >/dev/null 2>&1"
> >
> > would the name of the log file go where the /dev/null is?

Nope.  Instead, right after the word "postmaster", insert:
 -l /var/log/postgresql
.. or wherever you want to store the log.  You should also make
 provisions in CRON to clear the log periodically, or it will continue
 to grow forever.

> Josh will answer anyway ( :) ), but this tripped me up too when I
>  started.

Postgres Novice Help Desk, at your service!  ;-)

> I know the idea is for it to be configured for ones own site,
> but it seems this is one of the first questions people who start
> with PG ask.

Yeah.  I don't know why most Postgres startup scripts ignore the log
 file.  Every time I do a Postgres install, I have to alter the startup
 script by hand.

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco





Re: Function Hangs

From
"Rob Arnold"
Date:
Josh,

Yes, it goes where /dev/null is.  The only thing is that the log can't/wont
rotate that way.  It just keeps growing until postgres is restarted.  (It
can't because there is no way to SIG HUP the postmaster into releasing the
log file, it just stays "in use" until a restart) That may or may not be an
issue.  The other way to handle logging is to enable syslog logging in the
startup config file.  You will have to dig in the manual for the specifics
on that.

--rob

----- Original Message -----
From: "Stephen Ingram" <ingram@samsix.com>
To: "Patrick Hatcher" <PHatcher@macys.com>
Cc: "Josh Berkus" <josh@agliodbs.com>; <pgsql-novice@postgresql.org>
Sent: Wednesday, February 13, 2002 3:11 PM
Subject: Re: Function Hangs


> On Wednesday 13 February 2002 02:48 pm, Patrick Hatcher wrote:
> > I found the Postmaster startup file.  Here is the start params:
> >
> > su -l postgres -c "/usr/bin/pg_ctl -D $PGDATA -p /usr/bin/postmaster -o
"
> > -i" start >/dev/null 2>&1"
> >
> > would the name of the log file go where the /dev/null is?
> >
>
> Hi,
>
> Josh will answer anyway ( :) ), but this tripped me up too when I started.
>
> I know the idea is for it to be configured for ones own site,
> but it seems this is one of the first questions people who start
> with PG ask.
>
> It would be kind of nice if the startup scripts came as
>
> POSTMASTER_LOG=${POSTMASTER_LOG:-"/tmp/postmaster.log"}
> export POSTMASTER_LOG
>
> su -l postgres -c "/usr/bin/pg_ctl -D $PGDATA -p /usr/bin/postmaster -o
"-i"
>     start >${POSTMASTER_LOG} 2>&1"
>
> or some such.
>
> Just waffling on...
>
> Steve
>
>
> > Thanks again for the help
> >
> > Patrick Hatcher
> > Macys.Com
> > Legacy Integration Developer
> > 415-932-0610 office
> >
> >
> >
> >
> >
> >                     Patrick
> >                     Hatcher              To:     "Josh Berkus"
> > <josh@agliodbs.com>@FDS-NOTES cc:     pgsql-novice@postgresql.org
> > 02/13/2002           Subject:     Re: [NOVICE] Function Hangs(Document
> > link: Patrick Hatcher) 11:33 AM
> >
> >
> >
> >
> >
> > It was a Mandrake install, so it auto-starts during system start up.
> >
> > Patrick Hatcher
> > Macys.Com
> > Legacy Integration Developer
> > 415-932-0610 office
> >
> >
> >
> >
> >
> >                     "Josh Berkus"
> >                     <josh@agliodbs       To:     "Patrick Hatcher"
> > <PHatcher@macys.com> .com>                cc:
> > pgsql-novice@postgresql.org Subject:     Re: [NOVICE] Function Hangs
> > 02/13/2002
> >                     11:30 AM
> >
> >
> >
> >
> >
> >
> > Patrick,
> >
> > > Thanks.  Stupid question:  Where would I find the database log?
> >
> > Wherever you put it in your startup options for Postmaster.  I usually
> >  put mine in /var/log/postgresql.  How do you start postmaster?
> >
> > -Josh
> >
> > ______AGLIO DATABASE SOLUTIONS___________________________
> >                                        Josh Berkus
> >   Complete information technology      josh@agliodbs.com
> >    and data management solutions       (415) 565-7293
> >   for law firms, small businesses        fax 621-2533
> >     and non-profit organizations.      San Francisco
> >
> >
> >
> >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
>