Skip site navigation (1) Skip section navigation (2)

Re: How can I speed up this function?

From: David Mitchell <david(dot)mitchell(at)telogis(dot)com>
To: Gnanavel Shanmugam <s(dot)gnanavel(at)inbox(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: How can I speed up this function?
Date: 2005-06-28 04:29:32
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
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.



Gnanavel Shanmugam wrote:
> Merge the two select statements like this and try,
> SELECT t.trans_id as ID,, s.transaction_id, s.table_name, s.op,
>    FROM pending_trans AS t join dbmirror.pending_statement AS s
>    on (
> WHERE t.fetched = false order by t.trans_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
>>Here is our function:
>>CREATE OR REPLACE FUNCTION dbmirror.get_pending()
>>   RETURNS SETOF dbmirror.pending_statement AS
>>         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 =
>>	    FOR statement IN SELECT, s.transaction_id, s.table_name, s.op,
>>                 FROM dbmirror.pending_statement AS s
>>                 WHERE s.transaction_id =
>>                 ORDER BY 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)
>>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)
>>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.
>>David Mitchell
>>Software Engineer
>>---------------------------(end of broadcast)---------------------------
>>TIP 8: explain analyze is your friend

David Mitchell
Software Engineer

In response to


pgsql-performance by date

Next:From: Gnanavel ShanmugamDate: 2005-06-28 04:42:28
Subject: Re: How can I speed up this function?
Previous:From: Gnanavel ShanmugamDate: 2005-06-28 04:06:11
Subject: Re: How can I speed up this function?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group