Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [queue]

35 questions
116
votes
1 answer

Postgres UPDATE ... LIMIT 1

I have a Postgres database which contains details on clusters of servers, such as server status ('active', 'standby' etc). Active servers at any time may need to fail over to a standby, and I don't care which standby is used in particular. I want a…
vastlysuperiorman
  • 1,315
  • 2
  • 9
  • 8
21
votes
2 answers

Postgres Listen/Notify As Message Queue

Is there any way to use Postgres Listen/Notify feature to deliver a message to a channel and have only one listener consume this message? The purpose for this is that I have multiple 'worker' apps all listening to the same Postgres channel. But I…
moesef
  • 311
  • 1
  • 2
  • 4
20
votes
1 answer

Automatic aging-out (deletion) of old records in Postgres

Does Postgres have any features to support aging-out old records? I want to use Postgres for logging, as a sort of queue, where records (log events) older than two weeks are automatically deleted.
Basil Bourque
  • 8,776
  • 14
  • 46
  • 78
18
votes
2 answers

FIFO queue table for multiple workers in SQL Server

I was attempting to answer the following stackoverflow question: What SQL Server 2005/2008 locking approach should I use to process individual table rows in multiple server application instances? After posting a somewhat naive answer, I figured…
ErikE
  • 4,045
  • 4
  • 25
  • 39
13
votes
3 answers

Best way to implement concurrent table based queue

I have a table in MySQL that represents a queue of links to be processed. The links are processed by an external app, one by one, and deleted in the end. This is a high volume queue and I have multiple instances of the processing app, spread across…
Miguel E
  • 255
  • 1
  • 2
  • 7
7
votes
1 answer

Recommendations on how to organize Queues in Service Broker

I have a service broker application that currently has 5 or 6 queues on two servers. The general workflow is: Server A User populates some tables User fires stored proc which puts a message into a header queue indicating there is work to be…
JNK
  • 17,718
  • 5
  • 56
  • 97
7
votes
1 answer

No service broker active on database

I am trying to set up query notification however when I try to create a queue and service I get the error 'There is no Service Broker active in the database. Change to a database context that contains a Service Broker.' When I run the query select…
Johnathon64
  • 257
  • 3
  • 5
6
votes
1 answer

Using a Table as a Queue without sp_getapplock/sp_releaseapplock

I have a list of commands I need to execute, all of which are contained within a table I've named myQueue. This table is a little unique in that some commands should be grouped together such that their execution is performed sequentially, rather…
John Eisbrener
  • 8,970
  • 5
  • 23
  • 55
4
votes
3 answers

Why is our query suddenly returning rows it should not (using READPAST and UPDLOCK options)?

We have a job table that looks like this CREATE TABLE [dbo].[Clearing]( [Skey] [decimal](19, 0) IDENTITY(1,1) NOT NULL, [BsAcctId] [int] NULL, [Status] [varchar](20) NULL, CONSTRAINT [csPk_Clearing] PRIMARY KEY CLUSTERED ( [Skey]…
brian beuning
  • 151
  • 4
4
votes
1 answer

How can I limit SQL Server Agent's simultaneous jobs number?

I'm looking for a setting (other than schedules shifting) which could limit the number of jobs running simultaneously. I have 844 jobs in my SQL Agent (SQL Server 2017 Enterprise). Most of them needs few minutes to complete, there are about 190 jobs…
bigder
  • 43
  • 2
3
votes
1 answer

Work queue with complex select and long processing: Ensuring concurrency

I have a queue table from which worker processes claim records, one at a time. It is crucial that no two workers claim same item to process. Normally this kind of thing is done with select top (1) ... with (updlock, rowlock, readpast). This works as…
GSerg
  • 1,341
  • 1
  • 14
  • 27
2
votes
1 answer

Retrieve next queue item

I have a simple table in SQL Server 2012 which implements a processing queue. As data has been inserted the query to retrieve the next item has gone from <100ms to a constant 5-6secs. I would be hugely grateful if someone could point me at the cause…
Chris L
  • 285
  • 1
  • 9
2
votes
1 answer

Schema for multiple identical queue tables

I am migrating a message processing application that was built on MSMQ to SQL Server 2012 (preferably Standard Edition). Messages are received and distributed via TCP/IP endpoints and the typical installation has between 100 and 500 such endpoints.…
Dan
  • 495
  • 2
  • 5
  • 14
2
votes
0 answers

Exactly-once FIFO queue in Synapse

Creating a queue table in SQL Server is a much-studied problem. However, I would like to implement one in Azure Synapse where many of the building blocks do not exists. Specifically no table hints (READPAST etc.) no OUTPUT clause sp_getapplock is…
Michael Green
  • 22,481
  • 12
  • 46
  • 87
2
votes
1 answer

Duplicate key errors in Postgres queue-like table when rows should be locked by SELECT FOR UPDATE SKIP LOCKED

I have a table called queue with items that need to be processed: CREATE TABLE public.queue ( id serial NOT NULL CONSTRAINT queue_pkey PRIMARY KEY ); Another table process represents processed items from the queue (e.g. a…
illagrenan
  • 123
  • 5
1
2 3