I have multiple users in Postgres. I would like to set up different statement timeouts for different users. Eg: Guest 5 minutes and Admin 10 minutes. Is it possible in Postgres 11.11?

I understand that I can set the value of the global MAX_EXECUTION_TIME by running (from mysql client): ``` SET GLOBAL MAX_EXECUTION_TIME ``` But how do I just inquire its value? GET instead of SET does not seem to accomplish that.

Is there anyway to execute stored procedure with `SET LOCK_TIMEOUT` explicitly? E.g. I have sproc called `dbo.LoadData`. It just takes data from table `dbo.Abc` and insert them in table `dbo.Xyz`. Now I want to execute the sproc `dbo.LoadData`, but if the table `dbo.Abc` is `locked` for e.g. more than 10 seconds, stop the execution of the stored procedure. I know the option `SET LOCK_TIMEOUT` can be implemented in the sproc `dbo.LoadData` itself, but I am wondering if there is any way how to call it explicitly (= not implementing the option in the stored procedure itself).

I use SQL Server 2008 R2 and ASP.NET 4.5. Sometimes, I get this error when I executed a complex procedure: System.Data.SqlClient.SqlException Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding Any suggestions for troubleshooting or checklist (steps for improvements SQL)? My .NET code: ``` return Translate(SqlDbHelper.ExecuteProcedure("SP_CalendarQuery", pfechaAlerta, pfechaAlertaFin, pAsunto, pidArea, pidTipoAlerta, pTomador, pidUsuarioConsulta, pCartera)); public static DataTable ExecuteProcedure(string sql, params SqlParameter[] listParams) { var dt = new DataTable(); using (var conn = new SqlConnection(ConnectionString)) { using (var command = new SqlCommand(sql, conn)) { command.CommandType = CommandType.StoredProcedure; command.Parameters.AddRange(listParams); using (var dataAdapter = new SqlDataAdapter(command)) { dataAdapter.Fill(dt); } } } return dt; } ``` SQL Server Procedure: ``` ALTER PROCEDURE [dbo].[SP_CalendarQuery] @DateStart datetime = NULL, @DateEnd datetime = NULL, @Subject varchar(1000) = NULL, @Area int = NULL, @TypeAlert int = NULL, @Tomador varchar(50) = NULL, @UserQuery int = NULL, @CarteraUserQueQuery int = null AS BEGIN DECLARE @intTypeRol INT DECLARE @intTypeMediador INT DECLARE @intTypeAsociacion INT IF @UserQuery IS NOT NULL begin SELECT @intTypeRol = FK_ID_ROL_PORTAL, @intTypeMediador = FK_ID_Type_MEDIADOR, @intTypeAsociacion = ASOCIACION FROM [AccessRoles.Users] WHERE FK_ID_DATOS_PERSONALES = @UserQuery END ELSE BEGIN SET @intTypeRol = NULL SET @intTypeMediador = NULL SET @intTypeAsociacion = NULL END SELECT C.[ID_Alert] ,C.[Empresa] ,C.[Subject] ,C.[Date_Star

I am using ASP.NET with MVC3 with a SQL server database. From time to time, one of our stored procedures become incredibly slow when called by the web platform, but stay as fast when called directly in SSMS. The solution to this problem is to write "SET ARITHABORT ON" at the beginning of the stored procedure, which magically works. However, I want this to stop since I don't what is causing procedures to randomly start being incredibly slow on the web and causing timeouts. Is there a way to resolve the problem definitely? Thanks in advance, Eric

My understanding is that PostgreSQL functions are executed similar to a transaction. However, when I tried to "SET LOCAL statement_timeout" within a function, it did not work. Here's how it works within a transaction: ``` BEGIN; SET LOCAL statement_timeout = 100; SELECT pg_sleep(10); COMMIT; ``` where the results are (as expected): ``` BEGIN SET ERROR: canceling statement due to statement timeout ROLLBACK ``` However, if I put the same commands within a function body: ``` CREATE OR REPLACE FUNCTION test() RETURNS void AS ' SET LOCAL statement_timeout = 100; SELECT pg_sleep(10); ' LANGUAGE sql; SELECT test(); ``` the timeout does not occur, and the function `test()` takes 10 seconds to execute. Please advise on why the two cases differ, and how I can correct it to set statement timeouts within a function.

SQL Server 2008 SP3 How do I track down these timeout errors ? The errors are displayed on an intranet dashboard used specifically for error reporting in IIS. My suspicion is that there is a default timeout of 30 seconds in the web application and if a query takes more than thirty seconds, an exception is thrown. As there are many queries that take longer than 30 seconds on these SQL servers, I can't just filter in profiler based on duration. Serving up the website being monitored by this dashboard are two IIS servers retrieving data from seven SQL Server instances. Could I use the "User Error Message Event" and the "OLEDB Errors Event" to track these errors in SQL Server Profiler?

I am trying to debug a web app that uses a database and I need a little help. The problem is that part of the code throws a timeout error. I started using `SQL Server Profiler` to check the communication between the app and the database. (I am not really a database expert and this is a new tool for me). The app is supposed to call an `UPDATE` on a specific record. Shortly after, it calls a `SELECT` on the same record (because it refreshes the screen). I can see both the `UPDATE` and the `SELECT` event classes in the Profiler trace. (There are a few other event classes between them). The `SELECT` is showing with a duration of 30499 and an Error of `2 - Abort`. I am guessing this is the source of my timeout error. This is where it gets interesting. By selecting the `EventClass` in the Profiler trace, I can see the SQL `SELECT` statement that is being called. I copied and pasted this into a new query in Management Studio and tried it out. The `SELECT` statement worked fine and returned the record before my finger left the f5 key. However, the data in the record was the old data. It seems that the previous `UPDATE` call did not change the record. Looking at the Profiler trace again, the `UPDATE` event class shows an Error of `0 - OK`. When I select the `UPDATE` event class and copy the SQL code into Management Studio it runs fine and does update the record. Is anyone able to help me work out what is going on please. - If the `UPDATE` statement is shown in Profiler with no error, then does this mean that it should have updated the database? - What could be causing the `SELECT` statement to abort (considering that it runs fine when I try it in SQL Server Management Studio)? - Could problems with the `UPDATE` be causing the record to lock up which stop the `SELECT` from running? Any advice to help me debug and fix this would be appreciated.

I am seeing a timeout to the Pricincipel SQL server (2008 R2) The exact error I am seeing is this: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. This failure occured while attempting to connect to the Principle server. System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. This failure occured while attempting to connect to the Principle server. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out at XXXX What I am trying to understand is is this a standard timeout, ie. the DB was unable to return the required results within the allowed time? Or does the "Principle Server" wording signify another issue? I have not been able to find much about this specific error and need to know if its a performance issue that tuning the indexes and procs will help resolve or is there another issue I need to look at instead.

When working with SQL Server, there can be multiple application host accessing it, and each application can have one or multiple connections. Each connection can potentially have multiple transactions (please correct me if I am wrong). Each transaction can perform query or non-query SQL. In my experience, I easily run into timeout if I query a table that is exclusively locked. I also saw SQL Server detect and throw a deadlock exception instead of timeout if two different applications lock on the same resource. I also show rebuilding index timeout, which possible due to someone still have connection to table. However, I also encounter a kind of deadlock where SQL Server doesn't detect it or timeout out. In this application, it opened two connections, two separate transactions, where 1st transaction locked a resource, and the 2nd transaction try to access the same resource, but it hasn't close the first transaction. Would someone provide a list of types of timeouts and/or deadlocks, it would help me to avoid these kind of cases when working on the application.