Thread: Function Hangs
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
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
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
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
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
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
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
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
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
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
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
>