Re: How can I speed up this function?

I think the following logic will do want you expect
FOR statement IN <previous_query> LOOP
-- update statement goes here --
if count > 100 and temp <> transaction_id then
// reaches here only if the transaction is complete
return;
else
count:= count+1;
temp:=transaction_id;
end if;
end loop;

with regards,
S.Gnanavel

> -----Original Message-----
> From: david(dot)mitchell(at)telogis(dot)com
> Sent: Tue, 28 Jun 2005 16:55:00 +1200
> To: s(dot)gnanavel(at)inbox(dot)com
> Subject: Re: [PERFORM] How can I speed up this function?
>
> The function I have exits the loop when the count hits 100 yes, but the
> inner loop can push the count up as high as necessary to select all the
> statements for a transaction, so by the time it exits, the count could
> be much higher. I do want to limit the statements, but I want to get
> enough for complete transactions.
>
> David
>
> Gnanavel Shanmugam wrote:
> > But in the function you are exiting the loop when the count hits 100.
> If you
> > do not want to limit the statements then remove the limit clause from
> the
> > query I've written.
> >
> > with regards,
> > S.Gnanavel
> >
> >
> >
> >>-----Original Message-----
> >>From: david(dot)mitchell(at)telogis(dot)com
> >>Sent: Tue, 28 Jun 2005 16:29:32 +1200
> >>To: s(dot)gnanavel(at)inbox(dot)com
> >>Subject: Re: [PERFORM] How can I speed up this function?
> >>
> >>Hi Gnanavel,
> >>
> >>Thanks, but that will only return at most 100 statements. If there is a
> >>transaction with 110 statements then this will not return all the
> >>statements for that transaction. We need to make sure that the function
> >>returns all the statements for a transaction.
> >>
> >>Cheers
> >>
> >>David
> >>
> >>Gnanavel Shanmugam wrote:
> >>
> >>>Merge the two select statements like this and try,
> >>>
> >>>SELECT t.trans_id as ID,s.id, s.transaction_id, s.table_name, s.op,
> >>
> >>s.data
> >>
> >>> FROM pending_trans AS t join dbmirror.pending_statement AS s
> >>> on (s.transaction_id=t.id)
> >>>WHERE t.fetched = false order by t.trans_id,s.id limit 100;
> >>>
> >>> If the above query works in the way you want, then you can also do
> the
> >>>update
> >>>using the same.
> >>>
> >>>with regards,
> >>>S.Gnanavel
> >>>
> >>>
> >>>
> >>>
> >>>>-----Original Message-----
> >>>>From: david(dot)mitchell(at)telogis(dot)com
> >>>>Sent: Tue, 28 Jun 2005 14:37:34 +1200
> >>>>To: pgsql-performance(at)postgresql(dot)org
> >>>>Subject: [PERFORM] How can I speed up this function?
> >>>>
> >>>>We have the following function in our home grown mirroring package,
> but
> >>>>it isn't running as fast as we would like. We need to select
> statements
> >>>
> >>>>from the pending_statement table, and we want to select all the
> >>>
> >>>>statements for a single transaction (pending_trans) in one go (that
> is,
> >>>>we either select all the statements for a transaction, or none of
> >>
> >>them).
> >>
> >>>>We select as many blocks of statements as it takes to top the 100
> >>>>statement limit (so if the last transaction we pull has enough
> >>>>statements to put our count at 110, we'll still take it, but then
> we're
> >>>>done).
> >>>>
> >>>>Here is our function:
> >>>>
> >>>>CREATE OR REPLACE FUNCTION dbmirror.get_pending()
> >>>> RETURNS SETOF dbmirror.pending_statement AS
> >>>>$BODY$
> >>>>
> >>>>DECLARE
> >>>> count INT4;
> >>>> transaction RECORD;
> >>>> statement dbmirror.pending_statement;
> >>>> BEGIN
> >>>> count := 0;
> >>>>
> >>>> FOR transaction IN SELECT t.trans_id as ID
> >>>> FROM pending_trans AS t WHERE fetched = false
> >>>> ORDER BY trans_id LIMIT 50
> >>>> LOOP
> >>>> update pending_trans set fetched = true where trans_id =
> >>>>transaction.id;
> >>>>
> >>>> FOR statement IN SELECT s.id, s.transaction_id, s.table_name,
> >>
> >>s.op,
> >>
> >>>>s.data
> >>>> FROM dbmirror.pending_statement AS s
> >>>> WHERE s.transaction_id = transaction.id
> >>>> ORDER BY s.id ASC
> >>>> LOOP
> >>>> count := count + 1;
> >>>>
> >>>> RETURN NEXT statement;
> >>>> END LOOP;
> >>>>
> >>>> IF count > 100 THEN
> >>>> EXIT;
> >>>> END IF;
> >>>> END LOOP;
> >>>>
> >>>> RETURN;
> >>>> END;$BODY$
> >>>> LANGUAGE 'plpgsql' VOLATILE;
> >>>>
> >>>>Table Schemas:
> >>>>
> >>>>CREATE TABLE dbmirror.pending_trans
> >>>>(
> >>>> trans_id oid NOT NULL,
> >>>> fetched bool DEFAULT false,
> >>>> CONSTRAINT pending_trans_pkey PRIMARY KEY (trans_id)
> >>>>)
> >>>>WITHOUT OIDS;
> >>>>
> >>>>CREATE TABLE dbmirror.pending_statement
> >>>>(
> >>>> id oid NOT NULL DEFAULT nextval('dbmirror.statement_id_seq'::text),
> >>>> transaction_id oid NOT NULL,
> >>>> table_name text NOT NULL,
> >>>> op char NOT NULL,
> >>>> data text NOT NULL,
> >>>> CONSTRAINT pending_statement_pkey PRIMARY KEY (id)
> >>>>)
> >>>>WITHOUT OIDS;
> >>>>
> >>>>CREATE UNIQUE INDEX idx_stmt_tran_id_id
> >>>> ON dbmirror.pending_statement
> >>>> USING btree
> >>>> (transaction_id, id);
> >>>>
> >>>>Postgres 8.0.1 on Linux.
> >>>>
> >>>>Any Help would be greatly appreciated.
> >>>>
> >>>>Regards
> >>>>
> >>>>--
> >>>>David Mitchell
> >>>>Software Engineer
> >>>>Telogis
> >>>>
> >>>>---------------------------(end of
> >>
> >>broadcast)---------------------------
> >>
> >>>>TIP 8: explain analyze is your friend
> >>
> >>
> >>--
> >>David Mitchell
> >>Software Engineer
> >>Telogis
>
>
> --
> David Mitchell
> Software Engineer
> Telogis