当前位置: 动力学知识库 > 问答 > 编程问答 >

postgresql - How do I use Returning and Commit in Postgres so I can get a committed recordset?

问题描述:

I am building a FIFO queue using postgres and the answer based on apinstein answer to Job queue as SQL table with multiple consumers (PostgreSQL)

The issue is, if I use a transaction so the statement looks like:

 begin;

lock table queued_message in exclusive mode;

update

queued_message

set

status='IN_PROGRESS'

where

id in (

select

id

from

queued_message

where

status='SUBMITTED' and queue='BACKFILL_REQUEST'

order by

id asc

limit 1

)

returning *;

commit;

Then my returning values get discarded. If I run the same statement without the begin/commit the recordset returns fine.

Obviously I would prefer the transaction; the statement may not even be safe without it. So how do I return my committed recordeset?

EDIT

I'm marking the answer since it got me on the right track, but here is the function I ended up with:

CREATE TYPE returned_message as (id bigint, body json, status character varying(50) , queue character varying(150), last_modified timestamp without time zone)

CREATE OR REPLACE FUNCTION get_next_message(desiredQueue character varying(150))

RETURNS returned_message AS $$

DECLARE result returned_message;

BEGIN

lock table queued_message in exclusive mode;

update queued_message

set

status='IN_PROGRESS'

where

id in (

select

id

from

queued_message

where

status='SUBMITTED' and queue=desiredQueue

order by

id asc

limit 1

)

returning * into result;

RETURN result;

END;$$LANGUAGE plpgsql;

select * from get_next_message('BACKFILL_REQUEST')

网友答案:

You can create a function returning needed values. Each function executes as transaction. Do not place "begin;" and "commit;" in the function body. Function below should work I believe.

create or replace function set_in_progress()
returns setof queued_message 
language sql as $$
    lock table queued_message in exclusive mode;
    update 
        queued_message
    set 
        status='IN_PROGRESS'
    where
        id in (
            select
                id
            from
                queued_message
            where
                status='SUBMITTED' and queue='BACKFILL_REQUEST'
            order by 
                id asc
            limit 1
        )
    returning *;
$$;

select * from set_in_progress();
分享给朋友:
您可能感兴趣的文章:
随机阅读: