SQL Profiler - Scripting a trace with HostName filter I have a stock of scripted trace definitions that I use for different levels of diagnostics, none of which filter by HostName. I needed to filter traffic by host today so:

Fired up Profiler
Created an empty trace
Added SP:Start/Complete
Set a filter on hostname
Scripted to test.sql file

Open file in SSMS, no sign of the filter. Rinse, repeat, same again. Assuming I was doing something daft I looked up the value to use (harder to find than it should be!) and added to my trace definition manually, which worked.

EXEC sp_trace_setfilter @TraceId, 8, 0, 6, N'MyHostName'

Came back to check on why I couldn't get this to work from Profiler and same result. I found mention of this being a problem when scripting traces for 2000 from SSMS but no mention of this situation with SSMS2005/2008 to 2005/2008 servers. Is this a bug in Profiler?

When the script is generated in Profiler 2005, the default value saved for the logical  operator is 1='OR', so the trace will capture more information than we initially intended.

exec sp_trace_setfilter @TraceID, 8, 1, 0, N'HOSTNAME1'    -- OR
exec sp_trace_setfilter @TraceID, 8, 1, 0, N'HOSTNAME2'
exec sp_trace_setfilter @TraceID, 8, 1, 0, N'HOSTNAME3'
set @bigintfilter = 10 -- reads >= 10
exec sp_trace_setfilter @TraceID, 16, 0, 4, @bigintfilter  -- AND
exec sp_trace_setfilter @TraceID, 35, 1, 6, N'DB'          -- OR

In Profiler >=2008, the script has 0(AND) for the first occurrence of the column being filtered.

exec sp_trace_setfilter @TraceID, 8, 0, 0, N'HOSTNAME1'    -- AND
exec sp_trace_setfilter @TraceID, 8, 1, 0, N'HOSTNAME2'
exec sp_trace_setfilter @TraceID, 8, 1, 0, N'HOSTNAME3'
exec sp_trace_setfilter @TraceID, 35,0, 6, N'DB'           -- AND
set @bigintfilter = 10 -- reads
exec sp_trace_setfilter @TraceID, 16, 0, 4, @bigintfilter  -- AND

This looks like a bug in Profiler 2005.


Find a row with value zero in any column I’ve 10,000 rows with 64 columns.

If any column in any row has the value “0”, that row needs to be selected. Does anyone have a ready-made query? It would be a benefit to my project.

The values can be null. The value cannot be negative. It is an int value. The version is SQL Server 2016.

Too many variants:

WHERE column1=0 OR column2=0 OR ... OR column64=0
WHERE column1*column2* ... *column64=0
WHERE 0 IN (column1, column2, ... column64)

and so on...


Predicting the primary keys exhaustion in MySQL based on rate of insertion We are running out of primary keys for our table in production. We do not want that to occur again. So we need a mechanism to alert us when we are running out of primary keys based on the rate of insertion so that we can mitigate that.

I have written a Jenkins job that executes a query on information_schema and extracts the index_length, table_rows and auto_increment from the MySQL database. But we need to keep track of the previous reading since we want to take diff of it with the current in order to predict when the keys are going to be exhausted.

SELECT
  INDEX_LENGTH, TABLE_NAME, AUTO_INCREMENT, TABLE_ROWS
FROM
  information_schema.TABLES
ORDER BY
  index_length DESC;

I cannot find anything related to the rate of change in MySQL tables that should predict the rate at which the primary keys are being used up. I am also open to use any good alternate approach if anyone thinks of but keep in mind I have to get a query run by a cron job or Jenkins job that should tell that which tables will be exhausted at the end of this year.

Find tables that have an AUTO_INCREMENT column.
Discover the datatype of that column, including SIGNED vs UNSIGNED.
Use that to establish the drop-dead limit.
SELECT MAX(id) FROM tbl; to see where you are now.

At that point, you can go one of two directions.

Plan A:  Compute what percent of the limit the id already is, and sort on that.

Plan B:  Check MAX(id) a week later, then compute how many weeks before the limit will be hit.

When you do find a table that is threatening to hit the limit, find out if the ids are being "burned".  Do this by comparing COUNT(*) versus MAX(id).  Either the table is really big (compared to the datatype used), or there are a lot of gaps.

If there are a lot of gaps, then dig into what the code does.  There are many flavors of INSERT that "burn" ids.  REPLACE burns a id because it is effectively DELETE + INSERT.  INSERT IGNORE, if the row is present, burns the id that it otherwise would use.  IODKU burns when it updates.  Etc.

If you are burning ids due to Normalization, see this.


Inherit default privileges: every table created by *any member of* role A is readable by role B If I do this ...

ALTER DEFAULT PRIVILEGES
    FOR ROLE engineering
    IN SCHEMA reference
    GRANT SELECT ON TABLES to analytics;

I get ...

This syntax runs and has a valid meaning. The way it is implemented, any time the engineering role creates a table (in the reference schema), then any member of the reporting role will automatically have read access to it. But for that to work, you must be logged in or impersonating (set session authorization, set role etc) as the engineering role when you create the table.

I understand the rationale behind the way postgres implemented this. They want each role/user to set their own defaults. The default is a convenient shorthand so the user doesn't have to set permissions on every table they create. Postgres even allows someone to set the defaults for any role they can impersonate (and hence super users can set defaults for everyone). This is pretty powerful, but postgres didn't want to make it too powerful. They still wanted people to have to issue a separate command for each user's defaults. They wanted people to think hard about every user to whom they are giving this convenience.

But I want ...

But, I've already thought hard about it and I do want to set up those conveniences for a whole team. I want a single command where every user in the engineering team inherits these default privileges. Then, every time one of them creates a table, it would automatically be readable by every member of reporting. In fact, that's how I interpreted the syntax when I first found out I could include the FOR ROLE engineering clause.  But defaults are not inherited.

Alternatively, I would also be interested in a solution where no matter who (no matter what team they are in) creates a table in the reference schema, it will be readable by all the members of the reporting role. So in this variation, I can still open up the schema to reporting even if I can't (directly) control who gives those permissions (and why would I want to?). If I have this solution, I can still effectively limit it to the engineering team members if they are the only ones with create privileges in the reference schema.

Question

Am I missing something (such as an alternate syntax)? Or is there some other work-around?

  I am open to any solution (even building a custom extension) so long as it does not fall under one of the two categories below:

The solution doesn't really set a "default"

i.e. every time a member of the engineering team signs in or creates a table, they have to do some additional command to handle the permissions (such as change to the engineering role when they start the session)

The solution does not really inherit to the whole team

i.e. you have to set the default privileges for every team member in separate commands (and remember to do it whenever a new member is added

I've done my homework

None of the following questions (which Stack Exchange suggested based on the text of my question) has an answer for my nuanced need.

These questions are basic (didn't know about default permissions or how to use them as intended):

How to GRANT SELECT by default to B, on tables created by A?
How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?

These questions are similar to mine, but haven't been answered:

Default privileges granted only if table created by one superuser
How can I grant permissions on tables created in the future to members of a particular role?

These have some discussion of what I'm trying to do, but no solutions are offered:

Alter default privileges for role
PostgreSQL Role Configuration

You have researched well, and there is no way to get what you want.

So maybe you should change your requirements.

My suggestion is that all these users are members of a role (let's call it tableowner), and only that role has the CREATE privilege on the schemas.

So in order to create, alter or drop a table, the users first have to use SET ROLE tableowner.

Then all tables are owned by that role, and a single ALTER DEFAULT PRIVILEGES FOR ROLE tableowner ... will do the trick.


SQL Stored Procedure with one parameter and several variables that will change over time I am attempting to build a stored procedure with variables that will change over time. on SP execution (Right clicking SP in programmability under the database and execute) results in

Error output:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'file20160303FROMx:'.

(1 row(s) affected)

I am really open to any feedback, be it a complete re-design if need be. Still a junior in this field.

Script:

USE [dbname]
GO
/****** Object:  StoredProcedure [dbo].[procname]    Script Date: 2016-03-03 09:45:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

 --=============================================
 --Author:      <Author,,Name>
 --Create date: <Create Date,,>
 --Description: <Description,,>
 --=============================================
 ALTER PROC [dbo].[procname]
@p1 nvarchar(8)
AS
BEGIN

SET NOCOUNT ON;
Declare @insertvariable NVARCHAR(MAX)
Declare @var1 NVARCHAR (32) = 'x:\xxxx\xxxx\file\err';
Declare @var2 NVARCHAR (15) = convert(varchar, getdate(),112) + '.' + replace(convert(varchar, getdate(),108),':','')
Declare @var3 NVARCHAR (MAX) = @var1 + @var2 + '.log';
Declare @var4  NVARCHAR(MAX)= 'x:\xxxx\xxxx\file';
Declare @var5 NVARCHAR(MAX) = @var2 + @p1 + '.tab.txt';
Declare @formatFile NVARCHAR (MAX) = 'x:\xxxx\xxxx\formatFile';

set @insertvariable =  N'
BULK INSERT dbname.dbo.tablename' + @p1 + 'FROM' + @var5 + 'WITH
    (
   FIRSTROW = 1,
   ORDER (Id),
   CODEPAGE = Finnish_Swedish_CI_AS,
   FIELDTERMINATOR = \t,
   ROWTERMINATOR = 0x0a,
   TABLOCK,
   ERRORFILE = @var3,
   FORMATFILE = @formatFile,
   KEEPNULLS
   );'
   exec sp_executesql @insertvariable, N'@p1 nvarchar, @var5 nvarchar, @var3 nvarchar, @formatFile nvarchar', @p1, @var5, @var3, @formatFile
   end

You are missing spaces in your concatenated string and you also need to quote your filename.
I also don't see the point of mixing concatenation and calling sp_executesql with parameters.

Try changing this part:

set @insertvariable =  N'
BULK INSERT dbname.dbo.tablename' + @p1 + 'FROM' + @var5 + 'WITH
    (
   FIRSTROW = 1,
   ORDER (Id),
   CODEPAGE = Finnish_Swedish_CI_AS,
   FIELDTERMINATOR = \t,
   ROWTERMINATOR = 0x0a,
   TABLOCK,
   ERRORFILE = @var3,
   FORMATFILE = @formatFile,
   KEEPNULLS
   );'
   exec sp_executesql @insertvariable, N'@p1 nvarchar, @var5 nvarchar, @var3 nvarchar, @formatFile nvarchar', @p1, @var5, @var3, @formatFile
   end

Into this, mind the spaces where you concatenate @p1 and @var5 and single quotes:

set @insertvariable =  N'
BULK INSERT dbname.dbo.tablename ' + @p1 + ' FROM ''' + @var5 + ''' WITH
    (
   FIRSTROW = 1,
   ORDER (Id),
   CODEPAGE = ''Finnish_Swedish_CI_AS'',
   FIELDTERMINATOR = \t,
   ROWTERMINATOR = 0x0a,
   TABLOCK,
   ERRORFILE = ''' + @var3 + ''',
   FORMATFILE = ''' + @formatFile + ''',
   KEEPNULLS
   );'
   exec sp_executesql @insertvariable
   end


Is Windows 10 compatible with SQL Server 2014 Developer MDS? I have recently installed SQL Server 2014 Developer edition onto my PN, which has Windows 10 Professional installed. I upgraded from Windows 7 Pro to Windows 10 Pro last year as it was a free update from Microsoft.

I am trying to install Master Data Services and I receive the following warning:

  Before creating an MDS web application, you must correct the following errors:
  Internet Information Services (IIS) is not configured on this server.
  These required Web Server role services are not installed:
  Windows Authentication
  For web application requirements, see http://go.microsoft.com/fwlink/?LinkId=309491.

I am now going to install Windows authentication to correct the problem.  However, I have looked at the link and Windows 10 Pro is not listed as being compatible with SQL Server 2014 Developer edition. Is that correct?

I think it is fine; the documentation was just written long before Windows 10 was released and hasn't been updated (I've sent in a note that it should be corrected).

In fact it is listed as fully supported here, with no special caveats for MDS. I have never heard of any components of SQL Server 2014 having any compatibility issues with Windows 10.


PostgreSQL Operator Class for index support of custom operator I'm trying to create an index that will support queries that use my custom operator. This is on PostgreSQL 10.4.
The custom operator
I followed the tips in this SO answer to create an operator that performs "LIKE" style matching on elements in an text ARRAY.
CREATE FUNCTION reverse_like (text, text) returns boolean language sql
as $$ select $2 like $1 $$;

CREATE OPERATOR <~~ ( function =reverse_like, leftarg = text, rightarg=text );

The above operator allows me to do things like
SELECT 'ab%' <~~ ANY('{"abc","def"}');

The schema, index and query
I have a table with web traffic visits called sessions which includes an array column.
CREATE TABLE sessions
(
   session_id    varchar(24) NOT NULL,
   first_seen    timestamp,
   domains       varchar[]
);

To query the domains column to see if a given domain (or partial/ wildcarded domain name) was visited I can do the following:
SELECT count(*)
FROM session_4070ba14_f081_41cb_9ef7_9dd385934da7
WHERE 'www.foo%' <~~ ANY(domains);

I want to speed up the above queries with GIN index. So I created the index as follows:
CREATE INDEX idx_domains ON session USING GIN(domains);

The Question
After running  analyze on the table and a set enable_seqscan = false; I have no luck getting Postgres to employ this index. It's always doing a seqscan. It uses the above index of array operators like @> but not for my custom <~~ operator.
I think its because the GIN index doesn't know how to handle my custom operator - so do I need to create an operator class and then create my index using that? Or do I create a functional index?

For trigram support, you can try the parray_gin extension

WHERE domains @@> ARRAY['www.foo%'];

If you just want to do prefix matching (more efficiently than that provided by trigram), I don't think there is any way you can do that without writing some C code to glue the pieces together.  I think you would then work on the array type directly, so wouldn't need the ANY, and so wouldn't benefit from the reverse_like operator at all.


Concatenate Columns in Stored Procedure I have a stored procedure which returns a weekly result from input parameters start date and end date. I am getting multiple rows in the result.

Requirement: I want to add the results of columns in a single column. Below is my final query. From this I want to concatenate the result:

SET @sqlText =
    N'SELECT ['+REPLACE(@columns,',','],[') + ']' +
    '
    FROM dbo.timesheet
    WHERE
        month ='''+ @strMonth+'''
        AND [Task ID] IN
        (
            SELECT TaskID
            FROM ManageTasks
            WHERE
                TeamMemberUserID ='+  Cast(@UserID AS VARCHAR(max)) +
        ')'

--Here I want to add the columns result and need to show as single column

PRINT @sqlText
EXEC (@sqlText)

Use FOR XML PATH

Sample from TSQL – Concatenate Rows using FOR XML PATH() - sqlandme.com

SELECT      CAT.Name AS [Category],
            STUFF((    SELECT ',' + SUB.Name AS [text()]
                        — Add a comma (,) before each value
                        FROM Production.ProductSubcategory SUB
                        WHERE
                        SUB.ProductCategoryID = CAT.ProductCategoryID
                        FOR XML PATH('') — Select it as XML
                        ), 1, 1, '' )
                        — This is done to remove the first character (,)
                        — from the result
            AS [Sub Categories]
FROM  Production.ProductCategory CAT

Without FOR XML PATH

With FOR XML PATH


SQL CHANGE DATE FORMAT IN BULK I have a database with several hundred fields but my data structure is wrong. It is currently in uk format as follows:

d/m/y 01/01/85 01/01/96 23/12/87

What would be the most efficient way to change the dates in bulk to sql standard of year/month/day

eg. 02/01/85 --> 1985/01/02

Plan A:  Clean up the dates as you load the data.  How are you loading it?  INSERT?  LOAD DATA INFILE?  It is possible to do the STR_TO_DATE() in either case.

Plan B:  (This requires "Plan A" for future inserts.)  Devise a script, possibly mostly inside a Stored Routine that would do the following for any given column known to be a VARCHAR that should be a DATE.  (Do something similar for a DATETIME.)  Here is a sketch of what is needed for one $column in one $table:

ALTER TABLE $table ADD COLUMN tmp DATE NOT NULL;
UPDATE $table SET tmp = STR_TO_DATE($column, '%d/%m/%Y');
ALTER TABLE $table DROP COLUMN $column, RENAME COLUMN tmp TO $column;


export and import all the permissions on schema objects within a database SQL Server 2008 Is it possible to export all the users roles and their permissions on schema objects within a database in a .sql file and import them later in an other database ?

There are a couple of answers to a similar question that might be of use to you.

How to export all the security-related information from a SQL Server database

The answer I believe is yes. You can use SSMS to generate scripts and have it filtered to just users.

Here is a webpage detailing how you would Create Script to Copy Database Schema and All The Objects.


How to insert values into a table from two different tables? I have three tables

students table
------------------------------------
id(PK, A_I)  |  student_name | nationality

teachers table
------------------------------------
id(PK, A_I)  |  teacher_name |  email

classroom table
----------------------
id(PK, A_I)   | date   | teacher_id(FK to teachers.id)  |  student_id(FK to students.id)

If I was given teacher's name (david for example) and student_id (7 for example) and asked to insert the teacher_id into the classroom table based on the id in the teachers table, I would do :

insert into classroom (date, teacher_id, student_id)
select '2014-07-08', id, 7
from teachers
where teacher_name = 'david';

Now, what if I was not given the student's id directly and given only the name of the student? Suppose I was given teacher's name 'david' and student's name 'sam'. How do I get the teacher_id from teachers table and also student_id from the students table and insert both into the classroom table based on their respective names?

You would write the query like this

insert into classroom (date, teacher_id, student_id)
select '2014-07-08', t.id, s.id
from teachers t,students s
where t.teacher_name = 'david'
and s.student_name = 'sam';

Be careful. This is a Cartesian product. Another way to approach this is

select teacher_id into @tid from teachers where teacher_name = 'david';
select student_id into @sid from students where student_name = 'sam';
insert into classroom (date, teacher_id, student_id) values ('2014-07-08',@tid,@sid);


Remote access to SQL Server 2008 R2 on Windows Server 2012 R2? I have SQL Server 2008 R2 (named instance) on a Windows Server 2012 R2:

Enabled remote access to SQL Server
Enabled shared memory & TCP/IP protocols & named pipes for the SQL Server instance.
Created a firewall rule for TCP Port 1433
Created a firewall rule for UDP Port 1434
Created a firewall rule for SQLBrowser.exe
Created a firewall rule for SQLservr.exe
Ensured SQL Browser & SQL Server are running

I'm able to remotely access SQL Server (ip\instance name,port number) but can't access with (ip\instance name).

I tried with turning off firewall, but the problem was not resolved.

How can I fix it?

It is a known issue with Sql Server 2008. Happens also in SSAS.
Search for Microsoft fix/ or ask support for it (fix applies on the OS site and not the DB site).


Is there a way to determine locks that a given query might apply on SQL objects? Consider a simple query, as simple as a select statement:

select * from Teachers where Name like N'%John%'

As soon as I start this query, it finishes. Thus, I get no chance to see sp_who2 to find out what type of lock this query has applied on database objects.

I can inflate this query with transaction statements:

begin transaction

select * from Teachers where Name like N'%John%'

-- Here, I won't commit transaction, thus holding the lock

But frankly, that doesn't seem the proper way for natural locking detection, because I've manipulated the default behavior using transaction statements.

Do we have a tool, like Display Estimated Execution Plan to show us some information about locking of a given query? If not, how can we find out what type of lock a given query can apply on a database, because they get executed TOO fast to be detected.

No there's no way of knowing without running it.

The problem with opening a transaction is that you still won't see all the locks taken. At read committed the shared locks are released as soon as the row is read not end of the transaction. And even for locks that are held till end of transaction if a lock is escalated you miss the higher granularity locks taken out before that.

If this is a dev machine you can use trace flag 1200 (undocumented)

DBCC TRACEON(1200,3604,-1);
SELECT ....
DBCC TRACEOFF(1200,3604,-1);

This will output the lock events to the messages tab, it is often best to execute the batch twice as the first run will contain lots of stuff related to compilation as well as the execution.

It is also possible to use SQL Server Profiler/Trace and extended events to see locks acquired and released.

Again this is best done on a dev server as these are potentially extremely frequent events and even with filtering would likely add a great deal of overhead on a busy server.


What's the best practice to avoid collisions in throwaway temp tables? In SQL Server, I'm used to creating a #temp table for temporary storage in a complex stored procedure. Not wanting to clutter up the system, I DROP the table when done.

However, if I do the same in Oracle:

CREATE GLOBAL TEMPORARY TABLE Temp.RecordsToBeDeleted
    (PK INT NOT NULL PRIMARY KEY, Value VARCHAR2(10) NOT NULL)
-- Populate Temp.RecordsToBeDeleted
-- Use Temp.RecordsToBeDeleted
DROP TABLE Temp.RecordsToBeDeleted

...in one stored procedure, and another stored procedure does something similar and also uses the name Temp.RecordsToBeDeleted, they could collide.

The actual data in the table will not be visible across stored procedures, but the schemas could be different, or I could DROP one while the other is running.

Is there an established best practice for this? I could prefix each table name with the name of the stored procedure, but that's clumsy. I could treat GTTs as first-level objects, created with the rest of the schema rather than being created and dropped as needed, but that would increase maintenance; stored procedures would no longer be wholly self-contained.

The standard approach in Oracle is that global temporary tables are first-level objects.  They should be created at install time when you create all your permanent tables.  Doing DDL at runtime is very much frowned upon.  I'm not sure how this increases maintenance or how it makes stored procedures "no longer wholly self-contained"-- you don't need to do any maintenance on a temporary table and stored procedures presumably already have references to tables so I'm not sure what problem adding a reference to a temporary table creates.

It is unusual, though, in Oracle to use a temporary table in the first place.  I've been developing in Oracle for years and I can count the number of temporary tables I've created there on one hand.  Perhaps you would be better served by a collection of some type.  Perhaps you just need a view that implements whatever logic you're using in SQL Server to populate the temporary table.  Perhaps you want a pipelined table function.  Perhaps you even want a materialized view.  It will depend on why you're creating the temporary table to begin with.  Here is another thread that talks about alternatives to temporary tables in Oracle in more detail.


Large number of row deletes fails with 'out of log space' I know this is a common question, but I'm at a loss how to fix it.

Table has an indexed date column so I'm using that to delete one day at a time, oldest first. Within the loop that does each day there's another loop that deletes the TOP 1000 rows at a time, the inner, 1000 row, loop is wrapped in BEGIN/COMMIT TRAN block and the outer, daily, loop has a CHECKPOINT statement at the end. As far as I can tell this should mean that each 1000 rows is deleted in a transaction and after each day a CHECKPOINT is issued.

The statement fails with:  The transaction log for database 'database' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
Checking sys.databases I can see the reason is 'CHECKPOINT' (which is why I added the CHECKPOINT statement to the query). The database is using the SIMPLE recovery model, so as far as I can see this shouldn't be happening. It's also rolling back everything when it fails, because I can see the oldest record's date isn't changing.

I've checked there are no long running transactions locking the log, but I'm wondering if it might be because the db used to be log-shipped (it was moved here from a different server and is no longer). Maybe the transactions are marked for replication, but I don't know how to tell.

Does anyone have any suggestions?

My statement logic looks like this:

@NoOfDays = -10 (for example, with 10 days data to delete)
while @NoOfDays < 0
Begin
    While (1=1)
    Begin
        Begin Tran
        Delete 1000 rows that are older than @NoOfDays
        End Tran
        If @@Rowcount < 1000 Break
    End
    Checkpoint
    @NoOfDays += 1
End

If there is a FULL recovery model - you need to schedule the regular transaction log backups
If not (your case) - try to run your statements in smaller bulks, instead of one huge transaction
and finally the most like the your problem - place your begin tran/commit inside the loop


MariaDB Multi-source Replication SQL_SLAVE_SKIP_COUNTER When an error occurs on a slave while executing a statement from the master, it is sometimes useful to use SQL_SLAVE_SKIP_COUNTER variable to skip the execution of the query, like this:

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

While this works for main replication thread, I wonder how this is possible on a MariaDB Multi-source replication thread?

Change the default_master_connection variable for the session to make it work for a specific multi-source connection:

STOP SLAVE 'src1';
SET @@default_master_connection = 'src1';
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE 'src1';
SET @@default_master_connection = '';


Performance impact of setting PostgreSQL to log all statements PostgreSQL 9.2.2 on Windows 7 64-bit.

The setting log_statement in the postgresql.conf file specifies what statements to log (none, DDL, data-modifying, or all).  To what extent does setting log_statement = 'all' degrade performance?  I've read that this significantly reduces performance, but these claims come from older articles, so just how much of a performance loss should I expect relative to log_statement = 'none'?

Like almost everything else, it depends on your hardware, configuration and workload.

If you're running few expensive statements it'll make little difference; if you're running lots of tiny simple statements it'll make more difference.

If your logs are on the same file system or even just the same disk as the database, the logging will make more difference than if the logs are stored on a separate disk. Even though the logs aren't explicitly fsync()'d and are written sequentially, they're still competing with the main DB for I/O.

More importantly, some file systems (ext3 in particular) will flush all data when any file is fsync()'d, so the log writes become closer to random I/O than sequential I/O and slow down every commit. IIRC this is not an issue on ext4 or xfs.

It'll also depend on whether you have statement timing enabled, the logging method you're using, and more.

The best option is to benchmark it with a simulation of your real workload and see.


Optimizing a DB Query I am using MySQL database with PHP's Yii Framework, It is an old version of MySQL, It can't be updated to latest one due to some reasons.
I have created this query to get Current month records of current year. This query was working fine on my local system, I have around 10,000 records, But when i made it to production server, it was also working fine but after 1 month, Records reach upto 1 million in that table, So query is taking more than 10 seconds, when there is huge traffic on the app, its makes the server slow, mysql is taking high CPU. But when records are less, The query is working fine, Otherwise query is taking so much time to return records, sometimes it takes like 25-26 seconds.
Here is my query
SELECT SUM(`coins`) as coins, subscription_id
FROM `user_rewards` `t`
WHERE YEAR(date_awarded) = YEAR(CURRENT_DATE)
  AND MONTH(date_awarded) = MONTH(CURRENT_DATE)
GROUP BY subscription_id
ORDER BY coins DESC LIMIT 10

Is it possible to optimize this query more in a better way?
This is my user_rewards table
id, subscription_id, coins, date_awarded

A single subscription_id can have multiple records thats why I sum and do group by in my group...
Below is table schema.
CREATE TABLE `user_rewards` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `date_awarded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `subscription_id` bigint(20) NOT NULL,
 `coins` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `fk_sub_id` (`subscription_id`),
 CONSTRAINT `fk_sub_id` FOREIGN KEY (`subscription_id`) REFERENCES `subscription_info` (`subscription_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12907 DEFAULT CHARSET=latin1

First visible improvement (assuming that there is no date_awarded values in future) is
WHERE date_awarded >= DATE_FORMAT(CURRENT_DATE, '%Y-%m-01')

Second - the index by (subscription_id, date_awarded, coins) (or maybe (date_awarded, subscription_id, coins), depends on table statistic, must be tested).


Postgres logging_collector probably blocks auto_explain output Postgres 10.4

Postgres 9.6

Problem on both versions

I want to collect postgresql slow queries plan, so I added to my postgresql.conf setting from documentation

session_preload_libraries = 'auto_explain'

auto_explain.log_min_duration = '5s'

in the end of config

But also we use

logging_collector = true

and if logging_collector is on, no explain in logs

If logging_collector commented, I can see plan of queries
How can I use both options?

UPDATE:
We had about 10 queries runs more than 5 seconds, but finally with grep we found only one output in log with explain instead of all plans
How it could be only one record was logged? neither all plans, no no plans only one explain

Works for me.

Could it be that the other long queries were run over existing connections (perhaps with a connection pooler) for which the session_preload_libraries has not taken effect?


When is it beneficial to move duplicate data to a reference table? If there is column with values from some finite set S, for example S = { alive, dead, unknown } for a life_state column in a persons table, when is it beneficial to add a reference table?

Options:

Storing duplicated static data in a life_state column

simple strings: code on the application layer level would need to exist to get possible data values since SELECT DISTINCT could be missing some values.
ENUM: if you want to get all possible data values for life_state then you would need to run SHOW COLUMN.. from INFORMATION_SCHEMA to get all possible data values and parse that data on the application layer level or have duplicate code on the application layer level. If a new life_state value needed to be added we would need to ALTER the table and lock the whole table up in the process while it is being altered. No additional data could be stored for each life_state value should it be necessary.

Normalized Create a reference table life_states and a linking table back to persons table. The possible values could be derived from SELECT name from life_states; which would return 3 rows. Additional joins are now required to get the life_state for any persons instance. This is not only slower but requires longer queries/more code on the application layer level. Any additional values could easily be added if necessary, though it isn't anticipated. Furthermore, if some other data needed to be added to the life_state type it could be easily added.

Other considerations:

INT/ENUM indexing might be quicker/take up less space strings.

Related discussions:

Is it wasteful to create a new database table instead of using enum data type?

When is it beneficial to move duplicate data to a reference table?

When the referenced data exists independently from the referencing data. In this example, is it useful in the problem domain to know what the possible life states are even when there are no rows in persons.
When the values can only be drawn from a known, finite set. This is often referred to as "reference data". A foreign key constraint can enforce this.
When referential integrity must be maintained between two (or more) normalized entities. This will be for transactional data where the values will not be known in advance. For example where an application allows the addition of  new users at run-time, and has last_updated_userid on each table. Again a foreign key will enforce this.
When additional domain values are discovered that depend sole on the life_state. This is the usual key dependency normalization.
When meta data is required e.g. is_active for "soft deletes" instead of removing the value from the system entirely.

Although it is customary to create a surrogate key for the reference table it is not required. For the example given, the reference table could have a single seven-character column with three rows. The human-readable values would then appear in the referencing tables. This would obviate the need to join to the referenced table.


Pagination in Oracle 12c I have 20000 records in a table. I want to fetch 200 records at a time and do either update or delete operation. The next time I fetch 200 records, these previously processed/non-deleted records shouldn't come into picture.I though of using pagination, but could get to the logic of implementing it.Is there any way to achieve it?

You can create a column for update and/or delete status for the records. Do update instead of delete and assign a value that specifies this process. Assign another value for update process so that you can track the record's status.

When fetching you should have a where condition that returns records which have neither of these assigned values. After fetching these 200 records, you can do your delete or update process. I wouldn't use paging for this requirement, but if you want, just add your condition to the below paging

Sample paging:

SELECT * FROM (
    SELECT COL1,COL2,COL3,ROWNUM RNUM
    FROM SAMPLE_TABLE
    WHERE UPD_OR_DELETE NOT IN (1,2) AND ROWNUM < ( (p_page_number * p_page_size) + 1)) a
WHERE rnum >= ( ( (p_page_number - 1) * p_page_size) + 1);


How to drop an in-memory temporal columnstore Consider the following:

CREATE DATABASE [Foo]
ALTER DATABASE [Foo] ADD FILEGROUP XTP CONTAINS MEMORY_OPTIMIZED_DATA
ALTER DATABASE [Foo] ADD FILE (NAME=XTP,FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\Bar_XTP') TO FILEGROUP XTP
GO
USE [Foo]
CREATE TABLE dbo.A(
    ID INT NOT NULL CONSTRAINT PK_A_ID PRIMARY KEY NONCLUSTERED,
    [Start] DATETIME2 GENERATED ALWAYS AS ROW START,
    [End] DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME([Start], [End]),
    INDEX IX_A_CCS CLUSTERED COLUMNSTORE
) WITH (
    MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA,
    SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.A_History)
)

This wonderful contraption combines all the new features of SQL Server: an in-memory columnstore that's also a temporal table. Admittedly, the use cases for this should be limited: an in-memory columnstore typically supports real-time analytics and would not be expected to require versioning. Even so, if it's possible, someone will no doubt find some use for it.

There's just one slight thing I've got a problem with: I don't know how to get rid of it. A simple DROP TABLE produces:

  Msg 13552, Level 16, State 1, Line 27
  Drop table operation failed on table 'Foo.dbo.A' because it is not a supported operation on
  system-versioned temporal tables.

This is to be expected; you get the same error with disk-based tables. You're supposed to turn off system versioning first. Unfortunately ALTER TABLE A SET (SYSTEM_VERSIONING = OFF) produces:

  Msg 10794, Level 16, State 13, Line 1
  The operation 'ALTER TABLE' is not supported with memory optimized tables that have a column store index.

And that's also to be expected, but it seems to leave me out of options. Deleting the table from Management Studio produces the same sequence of commands under the covers, and so that also fails.

I've searched around but found neither a solution, nor someone who's attempted the same thing and confirmed it as a problem yet to be fixed. Workarounds readily suggest themselves (like renaming the table to something obscure) but is there a way to properly drop it?

(This was tested with the most recent non-Azure version of SQL Server as of writing, which is 14.0.3037.1, 2017 RTM CU10.)

It seems you have to drop the columnstore index first.  This works for me:

ALTER TABLE A DROP INDEX IX_A_CCS;
ALTER TABLE A SET (SYSTEM_VERSIONING = OFF);
DROP TABLE A;

Note that this leaves behind the temporal history table, so you might want to run DROP TABLE A_History; for good measure (which is the default naming when a history table name is not specified).


Oracle SQL for left outer join to rownum = 1 of another query? Background

I have a query joining many tables together, the chief of which is a workorder table.

I also have a table called WOStatusHistory, which stores every status for every workorder.

A diagram of the left outer join between the two would be something like:

 +------------+           +----------------+
 |Workorder   |           |WOStatusHistory |
 |------------| LO JOIN   |----------------|
 |WONum       |+--------->|WONum           |
 |Location    |           |Status          |
 |Description |           |ChangeDate      |
 |...         |           +----------------+
 +------------+

Goal

I need to return the most recent date that the status was complete for a specific workorder (in human terms, "tell me the last time that this workorder was completed")

Process So Far

The SQL for this without returning the top record is easy enough:

SELECT *
FROM   WORKORDER
       LEFT OUTER JOIN (SELECT WONUM        AS STATUSWONUM
                               , STATUS     AS STATUS
                               , CHANGEDATE AS STATUSCHANGEDATE
                        FROM   WOSTATUSHISTORY
                        WHERE  STATUS = 'COMP'
                        ORDER  BY CHANGEDATE DESC)LASTCOMPLETE
         ON ( WORKORDER.WONUM = LASTCOMPLETE.STATUSWONUM )

;

The Issue

However, I'm having trouble figuring out how to take rownum = 1 at the appropriate time in order to return only the most recent date.

It seems my only options are (though I hope I'm wrong):

Place rownum = 1 inside the wostatushistory query. This seems like it would limit the results before they're even linked.
Place rownum = 1 outside of the join. This wouldn't serve the purpose, as it would give me only one record from my total returned set.

Thanks in advance for any help you can give! Happy to clarify anything.

Basically, I think you should just get the max date, if that is all you are looking for here, using your same filter, etc. You already have STATUS (=COMP) and WONUM (JOIN). If you needed the whole record from this table, and it was more complicated than this, I would recommend the oracle inline analytic functions with over/partition by logic to filter by the max date.

SELECT *
FROM   WORKORDER
       LEFT OUTER JOIN (SELECT WONUM AS STATUSWONUM
                               , STATUS AS STATUS
                               , MAX(CHANGEDATE) AS STATUSCHANGEDATE
                        FROM   WOSTATUSHISTORY
                        WHERE  STATUS = 'COMP'
                        GROUP  BY WONUM, STATUS)LASTCOMPLETE
         ON ( WORKORDER.WONUM = LASTCOMPLETE.STATUSWONUM )

;


Controlling number of idle sessions in postgresql 11.5 We recently moved from PostgreSQL 9.0 to PostgreSQL 11.5.
We are now having problems with hundreds of idle sessions consuming all the database connections.
We have tried defining idle_in_transaction_session_timeout=30000.  That only managed to abruptly terminate legitimate processes while they were awaiting results from queries
We have tried defining tcp_keepalives_idle=300, tcp_keepalives_interval=30, and tcp_keepalives_count=3  Still, the number of idle sessions are in the hundreds.
When we kill idle sessions, or restart the database, the idle sessions reconnect, and stay idle.

You have to make a distinction between idle and idle in transaction. The database parameter idle_in_transaction_session_timeout limits the duration of the latter state, but there is nothing in PostgreSQL that will terminate idle connections. The reason is that they are not a problem, and in the case of connection pooling they are even something desirable.
What is odd about your question is that idle_in_transaction_session_timeout will never terminate a query that is active – being executed while the client waits for the result. The parameter for that is statement_timeout.
If you are running foul of max_connections because your application leaves connections open in idle state, you have a connection leak on the application side that you have to fix there.
If the connections are idle in transaction for a long time, that is also an application problem and should be fixed there. idle_in_transaction_session_timeout is just a brutal way to ascertain the database's health in the face of a buggy application that cannot be fixed.


SQL Server in a state of confusion: does the database exist or doesn't it? Got a really weird, annoying problem.. Somehow the instance of SQL Server 2008 R2 running on our server has gotten somewhat corrupted.

First, we noticed that the database we created yesterday was missing. So, we looked around and found that it was still there, but detached. So, we tried to attach the mdf but got a message which was something like The file is currently in use.

I thought that was odd, so restarted SQL Server. Same thing... okay, time for drastic measures... so, I stopped the service, zipped up the mdf, started the service, unzipped it and then tried to restore it. The above message was gone, but then I got:

  Cannot attach a database with the same name as an existing database

Ouch. Of course it's not showing in the database explorer, so no idea what's going on... last resort:

DROP DATABASE [DatabaseName]

Of course that didn't work.. that tells me the database does not exist. So, I'm stuck... at one point SQL Server thinks the database does exist and at another point it thinks the db does not exist.. obviously it's in a state of confusion.

Has anyone seen this before? Got any ideas on how to fix it?

Right.. I figured it out.. some clown here (who won't own up to it) renamed the db so that its name in SSMS is different to the mdf and ldf file names.. and not just a bit different... completely different. LOL. Thanks all for your helpful suggestions anyway.


Too much memory is being used during backup I have mongodb in ubuntu server. DB version : 3.2.8

CPU : 4 Core

Memory : 16 GB

Disk : 50GB

Normally server memory status:

Running backup script:

DIR=`date +%m%d%y`
DEST=DBBackup/MongoDB/$DIR
mongodump -d onedb -o $DEST
mongodump -d twodb -o $DEST
mongodump -d threedb -o $DEST
mongodump -d fourdb -o $DEST
mongodump -d fivedb -o $DEST

Script finished and server memory status:

The cache remains the same. If I repeatedly a backup for five days, all memory will be used. The memory is not flush automatically. What should I do?

MongoDB will eventually use all free memory, always! Of course in the situation where data and it indexes are smaller than free memory, mongod cannot fill whole memory.

Now, when you do backups, mongod reads all needed data (for those backups) to the memory and let it (the data) be there as long as it has free space. When there is no free memory anymore and mongod need to read something that is not already in the memory, oldest data is thrown away.


I got the following to work...

SELECT
    r0.category,
    r0.name,
    r1.s1time AS "s1",
    r2.s2time AS "s2",
    r3.s3time AS "s3",
    r4.s4time AS "s4",
    r5.s5time AS "s5"
FROM
    (SELECT
        name, riderid, category
     FROM
         raceresults
     ) r0
    LEFT JOIN raceresults r1
        ON r1.riderid = r0.riderid AND r1.s1time=(SELECT min(s1time) FROM raceresults WHERE s1time>0 AND category=r1.category)
    LEFT JOIN raceresults r2
        ON r2.riderid = r0.riderid AND r2.s2time=(SELECT min(s2time) FROM raceresults WHERE s2time>0 AND category=r2.category)
    LEFT JOIN raceresults r3
        ON r3.riderid = r0.riderid AND r3.s3time=(SELECT min(s3time) FROM raceresults WHERE s3time>0 AND category=r3.category)
    LEFT JOIN raceresults r4
        ON r4.riderid = r0.riderid AND r4.s4time=(SELECT min(s4time) FROM raceresults WHERE s4time>0 AND category=r4.category)
    LEFT JOIN raceresults r5
        ON r5.riderid = r0.riderid AND r5.s5time=(SELECT min(s5time) FROM raceresults WHERE s5time>0 AND category=r5.category)

Is there a better way?


How to resolve TNS error in Oracle when using an ODBC DSN I have come across this error when I was trying to insert data from front end (using JSP) to back end (Oracle 10g):

java.sql.SQLException: [Oracle][ODBC][Ora]ORA-12154:
                       TNS:could not resolve the connect identifier specified

Steps followed:

went to Microsoft ODBC
User DSN
Enter DNS ,TNS SERVICE NAME, USERID
When I click on "Test connection", the result was "Connection Successfull"

When I run my frontend JSP which has a text box and a button, I get the error mentioned above when I click on the button.

Make sure that your tns files (tnsnames.ora, sqlnet.ora) are in a certain directory that is readable by all users.  for example "/oracle/network/admin/"
Make sure that all your users and processes that need to work with the database[s] have the environt variable TNS_ADMIN defined with the value "/oracle/network/admin/".
Doing so gives a good chance that all processes look to the same configuration.


MySQL optimization - year column grouping - using temporary table, filesort I have a transactions table which is having 600,000 records, I need to list the count for the dashboard on financial year basis. The table used is MyISAM. I tried adding index for the transaction date (tran_date). Even though it is using the index it creates temporary table which is taking more time because of the temporary table and the filesort. Is there any way to optimize the query to improve the query time?

SELECT COUNT( * ) AS cnt, CASE WHEN MONTH( tran_date ) >=3
THEN concat( YEAR( tran_date ) , '-', YEAR( tran_date ) +1 )
ELSE concat( YEAR( tran_date ) -1, '-', YEAR( tran_date ) )
END AS financial_year
FROM `transactions1`
WHERE tran_date >= '2010-06-01'
GROUP BY financial_year

Showing rows 0 - 4 (5 total, Query took 1.2095 sec)

id select_type  table       type  possible_keys     key key_len    ref  rows    Extra
1   SIMPLE    transactions1 range PRIMARY,tran_date tran_date 8  NULL   346485  Using where; Using index; Using temporary; Using filesort

Keyname     Type    Unique  Packed  Field       Cardinality   Collation
PRIMARY     BTREE   Yes       No    tran_date      205720         A
                                    tran_ID        617162         A
coupon_No   BTREE   No        No    coupon_No      617162         A
account_typeBTREE   No        No    account_type   3              A
prodCode    BTREE   No        No    prodCode       430            A
                                    tran_date      308581         A
tran_date   BTREE   No        No    tran_date      205720         A
cust_ID     BTREE   No        No    cust_ID        3265           A
                                    tran_date      308581         A
                                    account_type   308581         A
                                    points_earned  617162         A

Update :

Tried adding partition which is not that much helpful in comparison with non partitioned one. Does replication help in this case for reading this table?. There will be more grouping based on the dates (using the date functions) when reading the data.

Edit:

I altered the query and reduced the query execution time. The query I used is,

SELECT SUM( count )
FROM (
SELECT COUNT( * ) AS count,
CASE WHEN MONTH( tran_date ) >=3
THEN concat( YEAR( tran_date ) , '-', YEAR( tran_date ) +1 )
ELSE concat( YEAR( tran_date ) -1, '-', YEAR( tran_date ) )
END AS format_date
FROM transactions1
GROUP BY tran_date
) AS s
GROUP BY format_date

Showing rows 0 - 4 (5 total, Query took 0.5636 sec)

id  select_type     table     type  possible_keys   key     key_len     ref     rows    Extra
1   PRIMARY     &ltderived2&gt    ALL       NULL        NULL      NULL      NULL    229676  Using temporary; Using filesort
2   DERIVED     transactions1 index     NULL        tran_date   8       NULL    617162  Using index

But when using

SELECT COUNT( * ) AS count,
CASE WHEN MONTH( tran_date ) >=3
THEN concat( YEAR( tran_date ) , '-', YEAR( tran_date ) +1 )
ELSE concat( YEAR( tran_date ) -1, '-', YEAR( tran_date ) )
END AS format_date
FROM transactions1
GROUP BY tran_date

Showing rows 0 - 29 (229,676 total, Query took 0.0006 sec)

gives less time without using the SUM(count) in the derived table. Is there any other way to get the sum without using the subquery in MySQL or can the subquery be optimized to get the index.

I don't see a lot of opportunity for improvement.

The index you added was probably a big help, because it's being used for the range matching on the WHERE clause (type => range, key => tran_date), and it's being used as a covering index (extra => using index), avoiding the need to seek into the table to fetch the row data.

But since you're using functions to construct the financial_year value for the group by, both the "using filesort" and "using temporary" can't be avoided.  But, those aren't the real problem.  The real problem is that you're evaluating MONTH(tran_date) 346,485 times and YEAR(tran_date) at least that many times... ~700,000 function calls in one second doesn't seem too bad.

Plan B: I am definitely not a fan of storing redundant data, and I'm dead-set against making the application responsible for maintaining it... but one option I might be tempted to try would be to create a dashboard_stats_by_financial_year table, and use after-insert/update/delete triggers on the transactions1 table to manage keeping those stats current.

That option has a cost, of course -- adding to the amount of time it takes to update/insert/delete a transaction... but, waiting > 1200 milliseconds for stats for your dashboard is a cost, too.  So it may come down to whether you want to pay for it now or pay for it later.


SQL Server 2008 R2, error creating file for FileStream I'm trying to create a file to add to a filestream file group and I get the error:

  Msg 5120, Level 16, State 106, Line 1
  Unable to open the physical file "d:\data\filestreamstore.mdf". Operating System Error -1073741790: "0xc0000022(failed to retrieve text for this error. Reason: 15105)".

I am running the query connection as administrator and I have full privileges on the drive and folder in question.

In the ERROR log I see:

  Error: 17207, Severity 16, State:1.
  STREAMFCB::Startup: Operating system error 0xc0000022(failed to retrieve text for this error. Reason: 15105) occurred while createing or opening file 'd:\data\filestreamstore.mdf'. Diagnose and correct the operating system error, and retry the operation.
  Error: 17204, Severity 16, State 1.
  FCB::Startup () failed: Could not open file d:\data\filestreamstore.mdf for file number 65537.  OS errof: 0xc0000022(failed to retrieve text for this error. Reason: 15105)

These identical errors are found in the Application log, with no more details.

Has anyone seen this before?

Turned out to be an HBSS issue.  Typically, I would have had this turned off during install but the environment I'm working with is particularly secured where you can't have HBSS set to "learning mode."

it turns out that filestream creates a registry key for each directory created and these exceptions have to be in place before creating the filestream enabled "file".

HBSS stands for Host Based Security System. Ours is a McAfee product. It's an incredibly powerful tool that is unrelated to SQL server, but on systems that require it, you will encounter strange problems like this with little to no explanation.


Pglogical - multiple database tables to single table I have 4 databases, each database is having 2 tables. The table structure on all the databases are same. So i want to replicate these two tables from all 4 databases to a single database.

So my centralized db will have 2 tables. Those tables having all 4 databases data.

FDW would be a better solution. But  i have logical replication as the required solution. So want to know the approach, possibilities.

Never mind, Its possible, I have tested with this. But we have to make sure that if you have any PK, Unique key it should unique across your source databases(all 4).Or on the subscription database create the table without the constraints.


What is the significance of sync_binlog? What is the significance of sync_binlog?

What exactly happens If I set sync_binlog=1?

How does it affect the replication between master and slave?

Turning it on flushes binlog events to disk.  This costs some extra I/O.

Turning it off lets the events eventually go to disk.  If the one of the servers crashes, the Slave can be in an "impossible" position in the replication stream.  The 'fix' is to move its pointer to the beginning of the next binlog on the Master.

Recommend you turn it on.  If you are overloaded on I/O, then look around for other remedies, such as improving the worst query in the slowlog.


Stolen Server Memory and Database Cache Memory I have the same behavior in 2 SQL Servers where Stolen Server Memory and Database Cache Memory counters are decreasing (Db Cache) and increasing (Stolen Memory) in a linear way. The amount of memory decreased from Db Cache Memory is almost the same amount of memory increased in Stolen Memory.
Has anyone seeing this behavior before? Is it an expected behavior?

Both server's configuration:

SQL Server 2012 ENTERPRISE with SP3
32GB memory

Thanks for the attention

"stolen" memory?  I like that.  Seriously though, I think the question may be clearer with OS vs SQL memory.  SQL will use the startup minimum set up in the config.  If you didn't set a maximum, then it will continue to steal memory as more work is demanded.

Thus the available total RAM for the OS decreases over time.  Simply set a maximum in the SQL properties, in your case 28GB, so it doesn't steal everything and starve the OS.

This is how SQL behaves normally so it's not that much of a thief.  Hope that helps.


oracle 12c grid standalone Installation failed with crsconfig_params file not found error in windows Oracle 12c grid installation failed (39% progressed) with "crsconfig_params file not found" error in Windows operating system:

Can we abort the session or need to fix and retry the session?

Please share your advice.

Make sure of unzip the grid installer in just one directory. For example, if the files you need to unzip are the following: winx64_1202_grid1of2.zip and winx64_1202_grid2of2.zip, then unzip both of them on the Grid directory and install from there.


Add cumulative sum to time-series query PostgreSQL 9.5 I wrote the query that gives me time-series over some date range and interval that shows revenue for each time interval:

SELECT
    interval_date,
    coalesce(campaign_revenue,0) AS campaign_revenue,
FROM
    -- generate_series helps fill the empty gaps in the following JOIN
    generate_series(
        $2::timestamp,
        $3::timestamp,
        $4) AS interval_date -- could be '1 day', '1 hour' or '1 minute'.
LEFT OUTER JOIN
    -- This SELECT gets all timeseries rows that have data
    (SELECT
        date_trunc($4, s.created) AS interval,
        SUM(s.revenue) campaign_revenue
    FROM
        sale_event AS s
    WHERE
        s.campaignid = $1 AND s.created BETWEEN $2 AND $3 AND s.event_type = 'session_closed'
    GROUP BY
        interval) results
ON
    (results.interval = interval_date);

The query takes every row of sale_event table, truncates the created date to some interval (aligns the created timestamp with the time-series wanted granularity), groups by this time interval and sums up the revenue columns on the rows where event_type is session_closed.

This works very well and gives me the revenue in the specified interval. The result may look like:

interval_date   |   campaign_revenue
------------------------------------
 2018-08-05     |   0.0
 2018-08-06     |   1.5
 2018-08-07     |   0.0
 2018-08-08     |   0.5
 2018-08-09     |   1.0

When the provided range is 2018-08-05 - 2018-08-09 and interval = '1 day'.

I want to add to the result the sum of revenue up to that date. So if before 2018-08-05 there a total revenue of 10.0, the result would be:

interval_date   |   campaign_revenue   |   total_campaign_revenue
-----------------------------------------------------------------
 2018-08-05     |   0.0                |   10.0
 2018-08-06     |   1.5                |   11.5
 2018-08-07     |   0.0                |   11.5
 2018-08-08     |   0.5                |   12.0
 2018-08-09     |   1.0                |   13.0

If I get it right you can just add a window function outside of your query like:

SELECT interval_date, campaign_revenue
     , SUM(campaign_revenue) OVER (ORDER BY interval_date)
      + (SELECT SUM(revenue)
         FROM sale_event
         WHERE s.campaignid = $1
           AND s.created < $2
           AND s.event_type = 'session_closed') as total_campaign_revenue
FROM (
    SELECT interval_date
         , coalesce(campaign_revenue,0) AS campaign_revenue
    FROM
        -- generate_series helps fill the empty gaps in the following JOIN
        ...
        interval) results
    ON (results.interval = interval_date)
);

Another option is to apply the window function directly, and use a FILTER clause for campaign_revenue


How to return a boolean response for a unique and new entry I am looking for an SQL query to identify if there are any new classs in my table:

CREATE TABLE `Features` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `account_username` varchar(64) NOT NULL DEFAULT '',
  `class` int(11) DEFAULT NULL,
  `new` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Edge cases

Here class 3 has no previously new=0 entries. So the SQL should return 1.

| class | new |
|-------|-----|
|     1 |   0 |
|     2 |   0 |
|     3 |   1 |

Here class 2 has already a new=0. So the SQL statement should return 0.

| class | new |
|-------|-----|
|     1 |   0 |
|     2 |   0 |
|     2 |   1 |

Here there are no new classes. So the SQL statement should return 0.

| class | new |
|-------|-----|
|     1 |   0 |
|     2 |   0 |
|     3 |   0 |

Here all classes are new. So the SQL statement should return 1.

| class | new |
|-------|-----|
|     1 |   1 |
|     2 |   1 |
|     3 |   1 |

This query should run on a single account_username.

If I understand your question correctly, you want to select a boolean value based on whether another query (that determines if there are any new entries in the table based on some new flag) selects any rows.

Someone could probably do this in a more optimised way, but it could be achieved by doing this:

SELECT CASE
           WHEN COUNT(y.class) > 0 THEN
               1
           ELSE
               0
       END
FROM (SELECT class
    FROM Features
    WHERE account_username = 'someusername'
    GROUP BY class) y
    LEFT JOIN
    (
        SELECT class
        FROM Features
        WHERE new = 0 AND account_username = 'someusername'
        GROUP BY class
    ) x
        ON x.class = y.class
WHERE x.class IS NULL

This selects 1 if the inner query selects any rows, otherwise 0. The inner query selects the rows that have a new flag associated with them by performing a left join on all the possible classes and all the classes where the flag is 0 where the x.class is null.

To run it on a single username, create a stored procedure and parameterise the username column.

Hope this helps!


Optimize a UNIQUE CONSTRAINT to make SELECT faster I have a database on SQL Server 2012 SP3 with this table:

CREATE TABLE [dbo].[Code] (
    [CodeId]            INT            IDENTITY (1, 1) NOT NULL,
    [Serial]            NVARCHAR (20)  NOT NULL,
    [AggregationLevelId]  TINYINT      NOT NULL,
    [...],
    CONSTRAINT [PK_CODE] PRIMARY KEY CLUSTERED ([CodeId] ASC),
    CONSTRAINT [UC_CODE_SERIAL] UNIQUE NONCLUSTERED ([Serial] ASC)
)

Using Sql Server Management Studio Activity Monitor I have seen that the following select has a 32ms average duration.

set @maxCode = (select max(Serial) from Code where AggregationLevelId = @codeLevel);

Is there any way to improve it? I asked this because I don't know if I can add a new index to a column that has a Unique Constraint.

UPDATE:

Code table has also this index:

CREATE NONCLUSTERED INDEX [ix255] ON [dbo].[Code]
(
    [AggregationLevelId] ASC
)
INCLUDE (   [Serial],
    [CodeId])
WHERE ([CommissioningFlag]=(255))

For the best performance of your query the table should have an index on (AggregationLevelId, Serial). One index on two columns. The order of columns in the index is important. Since Serial is unique, this two-column index can and should be declared unique as well.

CREATE UNIQUE NONCLUSTERED INDEX [IX_AggregationLevelId_Serial] ON [dbo].[Code]
(
    [AggregationLevelId] ASC,
    [Serial] ASC
)

You can create such index in addition to existing indexes and constraints. There is no limitation. It will affect the performance of inserts and updates as any other extra index.

Most likely the optimiser would use a single seek in such index for your query, but occasionally I came across a situation when the query with MAX didn't perform well even with the proper supporting index. (I vaguely remember that it happened when the table had no rows for the given AggregationLevelId).

So, I personally use the equivalent variant of the query:

SET @maxCode =
(
    SELECT TOP(1) Serial
    FROM Code
    WHERE AggregationLevelId = @codeLevel
    ORDER BY Serial DESC
);

Optimiser should be smart enough to use the index with Serial ASC, even though the query orders by Serial DESC.


MySQLTuner request - high CPU problems I've been running a pretty high pop website for a few months new and as its growing we've been having some problems lately.

Its a 2 core Xeon, 8GB, SSD server.
We're pulling +- 150k people a day.

Its a lightning fast wordpress stack and up to about 450 concurrent (200 pageviews a minute) it remains lightning fast.

If it goes beyond that, the load goes INSANE, from 1.5 to 20 or 30!
I've been trying to fine tune my.cfg but I'm about to give up, there is LOADS of information which is very varying, so I'm starting to get confused.

Here is my MySQLTuner from the last 4 days.

As you will see, it tells me to increase query cache, doing so however absolutely destroys my CPU even more and a lot earlier than when it remains at 8MB...

We also converted all tables to InnoDB and saw a big performance gain that way in the past.

I hope I can get some insight from professionals. I would even pay for some one on one time if needed.

Thank you!

1st suggestion to consider for your my.cnf [mysqld] section:

query_cache_type=0  # from 1 (ON) to OFF for NO qc to conserve CPU cycles
query_cache_size=0  # from 8M to conserve RAM for more useful purposes
innodb_io_capacity=1800  # from default (200) to increase SSD IOPS limit
innodb_flushing_avg_loops=5  # from 30 to reduce innodb_buffer_pool_pages_dirty count

# added 20181221 8 PM CT USA

innodb_purge_threads=4  # from 1 to expedite purge activity

# this will require stop/start after my.cnf change

You may monitor innodb_buffer_pool_pages_dirty count with the MySQL command:

SHOW GLOBAL STATUS LIKE '%dirty%';

If you check it every 10 minutes, you should see reduction and CPU busy will reduce as well until you get near 0 pages dirty.

I still need additional information requested to allow for additional performance improvements.

Please view my profile, Network profile and get in touch when you have a moment.


Why is my threads_connected ALWAYS = 1? I run a website with fairly high traffic. I've been having a lot of issues lately with "too many connections" or "could not connect" to MySQL and I think it's because of bots but I'm not 100% positive. I connect to the database using one username. So anyone who visits the site connects with the same script (and MySQL user).

If I have 1,000 people on the site at one time and I show processlist, the threads_connected is always 1. Is that normal? Could this be the cause of the too many connections issue?

The strange thing is, I can have 20,000 people on the site and it runs fine. But MySQL has been crashing a lot lately with an average of 200 people on the site - which my Amazon instance can EASILY handle. And there are always bots in the access_log when it goes down. I have tried so many things to stop MySQL from crashing and nothing is working.

There are two different issues -- threads connected and threads running.

In a typical, well oiled, production server, you will see this in SHOW PROCESSLIST:

Lots of "Sleep" connections,
Fewer than max_connections lines in the processlist,
One, or a small number, of non-Sleep connections.
Most queries are so fast that they go back to Sleep before you notice it.  I often see "thousands of Queries per second", yet "nothing running".  This is good.

"Sleep" means that the connection is not running a query at the moment.  Instead it might be doing application processing, or it might be part of a "connection pool".

The reason you always see "1" is that you have to run a query (SHOW PROCESSLIST or something else) to get the number.  So the "1" is itself.

You said Threads_connected is always 1.  Did you mean Threads_running?  Note also the capitalization.

What is the value of thread_cache_size?  8 is typical, but the exact value does not matter a lot.

What about Max_used_connections and max_connections?

Is there any sort of "connection pooling"?  Are there multiple clients?

Consider providing me with the info mentioned in tuning here .


Always ON - What is the difference between the virtual cluster name and Listener In clustering you specify DNS name/IP address of cluster that is then used by all applications. In case of failover, the same DNS name/IP address will point to the appropriate database server.

In Always on Availability Groups, there is concept of Listener which to my understanding does the same as above, so do we really need a Listener when the Failover clustering already handles this concept (IP address) of failover?

do we really need a Listener when the Failover clustering already handles this concept (IP address) of failover?

A SQL Server Failover Cluster Instance (FCI) will always have a "Network name resource" for clients to connect to the active node.

But with AlwaysOn Availability Groups it is optional, and created through SQL Server.  If you don't create an AG Listener, the Windows cluster will not have any IP address or hostname that moves along with the AG.

In any case an AG Listener is a Windows Server Failover Clustering "Network name resource", and so is confined to a single Cluster.  If you have a Distributed Availability Group which spans multiple Clusters, or a Read-Scale Availability Group which has no cluster, then you can't have an AG Listener.  (Although with a Distributed AG each participating AG can have its own listener).


How to get last level of a hierarchical list, group by some field Given the schema:

create table containers (
    id int primary key,
    name text,
    product_id int,
    lot int,
    qty int,
    parent_id int);

create table orders_items (
    id int,
    position int,
    container_id int);

Where containers is a hierarchical table with an unknown number of levels.

containers.parent_id = containers.id OR null

A container can hold other containers, this is a containers tree structure:

  23 Box 40 Parent  [ID: NULL, Lot: NULL, Qty: SUM(20+20)=40]
   |
   |--22 Bag 20     [Parent ID: 23, Lot: NULL, Qty: SUM(10+10)=20]
   |   |--21 Bag 10 [Parent ID: 22, Lot: 701, Qty: 10]
   |   |--22 Bag 10 [Parent ID: 22, Lot: 703, Qty: 10]
   |
   |--19 Bag 20     [Parent ID: 23, Lot: NULL, Qty: SUM(10+10)=20]
       |--17 Bag 10 [Parent ID: 19, Lot: 700, Qty: 10]
       |--18 Bag 10 [Parent ID: 19, Lot: 701, Qty: 10]

For us a container is considered as one expeditionary unit, depending on customer requirements a container can be a simple plastic bag of 10 pieces, or a full pallet with 10 boxes of 5000 pieces each one. Only containers of last level has a lot assigned, parent containers can hold different lots, but always belonging to same product.

We need to list all products included in a single order, group by lot.

Different products can't have the same lot.
One container can only hold containers of the same product.

Sample data:

insert into containers values
  (23, 'Box 40',  2, null, 40,  null)
, (16, 'Pallet',  1, null, 120, null)
, (12, 'Bag 20',  1, null, 20,  14)
, (13, 'Bag 20',  1, null, 20,  14)
, (14, 'Box 40',  1, null, 40,  16)
, (19, 'Bag 20',  2, null, 20,  23)
, (22, 'Bag 20',  2, null, 20,  23)
, (5,  'Bag 20',  1, null, 20,  7)
, (6,  'Bag 20',  1, null, 20,  7)
, (7,  'Box 40',  1, null, 40,  16)
, (1,  'Bag 10',  1, 500,  10,  5)
, (2,  'Bag 10',  1, 501,  10,  5)
, (3,  'Bag 10',  1, 502,  10,  6)
, (4,  'Bag 10',  1, 500,  10,  6)
, (8,  'Bag 10',  1, 600,  10,  12)
, (9,  'Bag 10',  1, 601,  10,  12)
, (10, 'Bag 10',  1, 502,  10,  13)
, (11, 'Bag 10',  1, 501,  10,  13)
, (15, 'Box 40',  1, 600,  40,  16)
, (17, 'Bag 10',  2, 700,  10,  19)
, (18, 'Bag 10',  2, 701,  10,  19)
, (20, 'Bag 10',  2, 703,  10,  22)
, (21, 'Bag 10',  2, 701,  10,  22);

insert into orders_items values
(1, 1, 16),
(1, 2, 23);

The order number 1 has two details lines, containers 16& 23, I need to get all lots included in these containers.

In this example the result should show this rows:

+----+------------+-------------+------+------+------------+
| id |    name    |  product_id |  lot |  qty |  parent_id |
+----+------------+-------------+------+------+------------+
|  1 |  'Bag 10'  |           1 |  500 |   10 |          5 |
|  2 |  'Bag 10'  |           1 |  501 |   10 |          5 |
|  3 |  'Bag 10'  |           1 |  502 |   10 |          6 |
|  4 |  'Bag 10'  |           1 |  500 |   10 |          6 |
|  8 |  'Bag 10'  |           1 |  600 |   10 |         12 |
|  9 |  'Bag 10'  |           1 |  601 |   10 |         12 |
| 10 |  'Bag 10'  |           1 |  502 |   10 |         13 |
| 11 |  'Bag 10'  |           1 |  501 |   10 |         13 |
| 15 |  'Box 40'  |           1 |  600 |   40 |         16 |
| 17 |  'Bag 10'  |           2 |  700 |   10 |         19 |
| 18 |  'Bag 10'  |           2 |  701 |   10 |         19 |
| 20 |  'Bag 10'  |           2 |  703 |   10 |         22 |
| 21 |  'Bag 10'  |           2 |  701 |   10 |         22 |
+----+------------+-------------+------+------+------------+

Group by lot:

|----------|---------|-----|----------|
| Order ID | Product | Lot | Quantity |
|----------|---------|-----|----------|
|    1     |    1    | 500 |    20    |
|    1     |    1    | 501 |    20    |
|    1     |    1    | 502 |    20    |
|    1     |    1    | 600 |    50    |
|    1     |    1    | 601 |    10    |
|    1     |    2    | 700 |    10    |
|    1     |    2    | 701 |    20    |
|    1     |    2    | 703 |    10    |
|----------|---------|-----|----------|

I've created a rextester example with this values.

This code shows how to use a recursive CTE, to return the results you're looking for.

IF OBJECT_ID('dbo.OrdersItems') IS NOT NULL DROP TABLE dbo.OrdersItems;
IF OBJECT_ID('dbo.Containers') IS NOT NULL DROP TABLE dbo.Containers;

CREATE TABLE dbo.Containers
(
    ContainerID int NOT NULL
        CONSTRAINT PK_containers
        PRIMARY KEY CLUSTERED
    , ContainerName text NOT NULL
    , ProductID int NOT NULL
    , Lot int NULL
    , Quantity int NOT NULL
    , ParentContainerID int NULL
        CONSTRAINT FK_Containers_ContainerID
        FOREIGN KEY
        REFERENCES dbo.Containers (ContainerID)
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
);

CREATE TABLE dbo.OrdersItems
(
      OrderID INT NOT NULL
    , Position int NOT NULL
    , ContainerID int NULL
        CONSTRAINT FK_OrdersItems_ContainerID
        FOREIGN KEY
        REFERENCES dbo.Containers (ContainerID)
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
    , CONSTRAINT PK_OrdersItems
        PRIMARY KEY CLUSTERED (OrderID, Position)
);

Insert the sample data:

INSERT INTO dbo.Containers (ContainerID, ContainerName, ProductID
                             , Lot, Quantity, ParentContainerID)
VALUES
      (23, 'Box 40',  2, null, 40,  null)
    , (16, 'Pallet',  1, null, 120, null)
    , (12, 'Bag 20',  1, null, 20,  14)
    , (13, 'Bag 20',  1, null, 20,  14)
    , (14, 'Box 40',  1, null, 40,  16)
    , (19, 'Bag 20',  2, null, 20,  23)
    , (22, 'Bag 20',  2, null, 20,  23)
    , (5,  'Bag 20',  1, null, 20,  7)
    , (6,  'Bag 20',  1, null, 20,  7)
    , (7,  'Box 40',  1, null, 40,  16)
    , (1,  'Bag 10',  1, 500,  10,  5)
    , (2,  'Bag 10',  1, 501,  10,  5)
    , (3,  'Bag 10',  1, 502,  10,  6)
    , (4,  'Bag 10',  1, 500,  10,  6)
    , (8,  'Bag 10',  1, 600,  10,  12)
    , (9,  'Bag 10',  1, 601,  10,  12)
    , (10, 'Bag 10',  1, 502,  10,  13)
    , (11, 'Bag 10',  1, 501,  10,  13)
    , (15, 'Box 40',  1, 600,  40,  16)
    , (17, 'Bag 10',  2, 700,  10,  19)
    , (18, 'Bag 10',  2, 701,  10,  19)
    , (20, 'Bag 10',  2, 703,  10,  22)
    , (21, 'Bag 10',  2, 701,  10,  22);

INSERT INTO dbo.OrdersItems (OrderID, Position, ContainerID)
VALUES (1, 1, 16)
    , (1, 2, 23);

Here's the recursive CTE:

;WITH RecursiveCTE AS
(
    SELECT c1.ContainerID
        , c1.ContainerName
        , c1.Lot
        , c1.ParentContainerID
        , c1.ProductID
        , c1.Quantity
        , Level = 1
    FROM dbo.Containers c1
    WHERE c1.ParentContainerID IS NULL
    UNION ALL
    SELECT c2.ContainerID
        , c2.ContainerName
        , c2.Lot
        , c2.ParentContainerID
        , c2.ProductID
        , c2.Quantity
        , Level = RecursiveCTE.Level + 1
    FROM dbo.Containers c2
        INNER JOIN RecursiveCTE ON RecursiveCTE.ContainerID = c2.ParentContainerID
)
SELECT r.ProductID
    , r.Lot
    , QuantityTotal = SUM(r.Quantity)
FROM RecursiveCTE r
WHERE r.Lot IS NOT NULL
    AND r.Level > 1
GROUP BY r.ProductID
    , r.Lot;

The results:

ProductID | Lot | QuantityTotal
--------: | --: | ------------:
        1 | 500 |            20
        1 | 501 |            20
        1 | 502 |            20
        1 | 600 |            50
        1 | 601 |            10
        2 | 700 |            10
        2 | 701 |            20
        2 | 703 |            10

As you can tell from the above code, I've renamed some of your columns to more clearly reflect their contents, and have added some minor formatting, along with foreign-key references.

db<>fiddle here


MySQL: SELECT and JOIN with not exists values? How can i SELECT data from two tables using 'JOIN' operator, if some values with cat_idx and tar_id from categories (tar_id = 87) does not exists in time_discounts table. (I need to print '0' in this case)

See My SQL Schema:

CREATE TABLE `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `tar_id` int(11) NOT NULL,
  `cat_idx` int(11) NOT NULL);

CREATE TABLE `time_discounts` (
  `dis_id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `tar_id` int(11) NOT NULL,
  `cat_idx` int(11) NOT NULL,
  `discount`double NOT NULL
);

insert into categories values(null, 87, 122);
insert into categories values(null, 82, 120);
insert into categories values(null, 87, 128);
insert into categories values(null, 81, 14);
insert into categories values(null, 87, 135);
insert into categories values(null, 87, 140);

insert into time_discounts values(null, 87, 122, 2.34);
insert into time_discounts values(null, 82, 120, 9.22);
insert into time_discounts values(null, 81, 14, 2.11);
insert into time_discounts values(null, 87, 135, 9.35);
insert into time_discounts values(null, 80, 11, 83.22);

For tar_id = 87 and cat_idx = 122 from categories table we can find values from time_discounts table, but for cat_idx = 140 and 128 values does not exists in time_discounts table. In this case output should be something like this:

| tar_id | cat_idx | discount |
|--------|---------|----------|
|     87 |     122 |     2.34 |
|     87 |     128 |     0    |
|     87 |     135 |     9.35 |
|     87 |     140 |     0    |

My query:

SELECT `c`.`tar_id`, `c`.`cat_idx`, IFNULL(`td`.`discount`, 0)
FROM `categories` AS `c`
LEFT JOIN `time_discounts` AS `td`
    ON `td`.`tar_id` = `c`.`tar_id`
    AND `td`.`cat_idx` = `c`.`cat_idx`;

But i need to filter data by tar_id = 87 and if i add AND c.tar_id = 87 i also gets all values from time_discounts, not only tar_id = 87.

Please help and sorry for my english

Change AND c.tar_id = 87 to WHERE c.tar_id = 87.

Your soultion would work only if it was an INNER JOIN instead.
With LEFT JOIN it works different. The filter is applied for the join predicate and the result is different.


How can i return the date of max id? I want to select the max id_product and date that corresponds to this max id.
my problem is that it returns the max id_ product with a different date.

I can't use the max(date) because it will return always the max what i need is the date of this max id.

This my query :

 SELECT
   MAX(id_product),
   `date`
 FROM `products`
 WHERE `fk_stat` = '1' and `id_order` = '77185' ;

The result:

But the value that's must return:

09/07/2015 for id = 152752

Thanks for your help.

Do it with a subquery,assuming unique values on id_product

SELECT id_product,date  FROM products
WHERE id_product IN
(SELECT MAX(id_product) as maxid FROM products WHERE
fk_stat = '1' and id_order = '77185')


Install galera cluster without remove existing mariadb I have to setup Galera cluster in one of my production servers.

OS: Ubuntu 14.04
Existing mysql: maridb 10.1

While installing this cluster, its asking to remove the existing mariadb-server-10.1

How can I install this without remove anything?

If you installed MariaDB 10.1, you don't need to remove anything because MariaDB Server and MariaDB Galera Server packages have been combined and Galera packages and their dependencies get installed automatically when installing MariaDB 10.1.

So you must only configure galera without remove anything.

Reference


Question about postgres commits to table in UNLOGGED mode I have three tables: data, processed, and processing_status. The first one has rows coming in quite quickly. Every few minutes, I check the highest ID in data and compare it to processing_status. If enough new datapoints have come in, the new ones are processed and aggregated into processed table, and processing_status is updated to the highest ID. Obviously, both of those happen in one transaction, so that if there's a crash, everything stays sane, and neither table is updated without the other.
I'd like to improve the performance of this, regarding the bloat to the WAL logs. If I set processed and processing_status to UNLOGGED in Postgres, will I retain the property that when I commit the transaction, either both tables are updated, or neither? Even in the event of a crash?
I believe that if I set UNLOGGED, the difference is that in a crash, it will always revert both, whereas in LOGGED mode, the WAL could be used to include a few extra in-progress transactions right during the crash. Is this understanding accurate?

Your understanding is correct. UNLOGGED tables participate in transactions just like regular, logged tables.
The only difference is the crash-safety, so you may lose data if your server crashes (unexpected power-outage, operating system crash, Postgres bug, ...)


Select all children down a hierarchy I have a table of hierarchical data like this:

BookGroupID  GroupName  ParentGroupID
-----------  ---------  -------------
0            Primary    0
1            abc        0
2            abd        0
3            abe        0
4            cbc        1
5            ceg        2
6            cjd        3
7            hjd        5
8            bjy        5
9            mfs        4
10           ikl        7
11           hjy        8
12           mnf        5
13           aws        10
14           qws        11
15           aqe        13

I want to select a specific BookGroupID and all its direct and indirect children down the hierarchy. For instance, for the BookGroupID of 2 the output should look like this:

BookGroupID
-----------
2
5
7
8
10
11
12
13
14
15

To explain, row 5 is in the output because it is a direct child of row 2, rows 7, 8 and 12 are direct children of 5, 15 is a child of 13 which is a child of 10 which is a child of 7, 14 is a child of 11 which is a child of 8.

So, how can I do that in SQL?

The technique to use is called a recursive common table expression, or recursive CTE. The Microsoft SQL documentation is comprehensive and there are a lot of third-party blog posts to help explain.

Here's an implementation for your specific question.

This part just creates the sample data from your question. It uses a temporary table (one that uses the # symbol)

    create table #t (BookGroupID int, GroupName varchar(100), ParentGroupID int)
    insert into #t
    values
    (0      ,'Primary',    0    ),
    (1      ,'abc',        0    ),
    (2      ,'abd',        0    ),
    (3      ,'abe',        0    ),
    (4      ,'cbc',        1    ),
    (5      ,'ceg',        2    ),
    (6      ,'cjd',        3    ),
    (7      ,'hjd',        5    ),
    (8      ,'bjy',        5    ),
    (9      ,'mfs',        4    ),
    (10     ,'ikl',        7    ),
    (11     ,'hjy',        8    ),
    (12     ,'mnf',        5    ),
    (13     ,'aws',        10),
    (14     ,'qws',        11),
    (15     ,'aqe',        13);

This is the value from which to start looking:

    declare @parent int = 2;

This is the recursive CTE. You do not have to call it "cte" as in this example. The initial semicolon is redundant. It ensures the previous statement is properly ended if the programmer has been lazy. It does no harm to leave it.

    ;with cte
    as (select BookGroupId
        from #t as t
        where BookGroupId = @parent
        UNION ALL
        select t.BookGroupId
        from #t as t
        join cte
        on t.ParentGroupId = cte.BookGroupId)
    select *
    from cte
    order by BookGroupId;


Is support for Parallel Scalar UDF a reasonable feature request? It is fairly well documented that scalar UDF's force an overall serial plan.

Running functions in parallel

Given a large number of rows coming into a point in the pipeline where a UDF must be calculated, why can't the engine just distribute them among the processors?  If there is no state within a UDF then the order shouldn't matter.

There are claims about UDF's being a black box must use cursor.  I can see that a user cursor cannot be parallelized within an SP for the cases where some state is maintained between iterations but seems like it should be parallelizable otherwise.

Extra points for explaining why the engine forces the whole plan to be serial instead of just the UDF calculation stage.

Is support for parallel UDF a reasonable feature to request?

It is fairly well documented that UDFs force an overall serial plan.

I'm not certain it is all that well documented.

A scalar T-SQL function prevents parallelism anywhere in the plan.
A scalar CLR function can be executed in parallel, so long as it does not access the database.
A multi-statement table-valued T-SQL function forces a serial zone in a plan that may use parallelism elsewhere.
An inline table-valued T-SQL function is expanded like a view, so has no direct effect.

See Forcing a Parallel Execution Plan and/or Craig Freedman's Parallel Execution presentation.

  There are claims about UDFs being a black box must use cursor.

These claims are not correct.

  Extra points for explaining why the engine forces the whole plan to be serial instead of just the UDF calculation stage.

My understanding is that the current restrictions are a purely the result of certain implementation details. There is no fundamental reason why functions could not be executed using parallelism.

Specifically, T-SQL scalar functions execute inside a separate T-SQL context, which complicates correct operation, coordination and shutdown (especially in the case of an error) significantly.

Equally, table variables do support parallel reads (but not writes) in general, but the table variable exposed by a table-valued function is not able to support parallel reads for implementation-specific reasons. You would need someone with source code access (and the freedom to share details) to provide an authoritative answer, I'm afraid.

  Is support for parallel UDF a reasonable feature to request?

Of course, if you can make a strong-enough case. My own feeling is that the work involved would be extensive, so your proposal would have to meet an extremely high bar. For example, a related (and much simpler) request to provide inline scalar functions has great support, but has languished unimplemented for years now.

You might like to read the Microsoft paper:

Froid: Optimization of Imperative Programs in a Relational Database (pdf)

...which outlines the approach Microsoft look to be taking to address T-SQL scalar function performance issues in the release after SQL Server 2017.

  The goal of Froid is to enable developers to use the abstractions of UDFs
  and procedures without compromising on performance. Froid achieves this goal using a novel technique to automatically convert imperative programs into equivalent relational algebraic forms whenever possible. Froid models blocks of imperative code as relational expressions, and systematically combines them into a single expression using the Apply operator, thereby enabling the query optimizer to choose efficient set-oriented, parallel query plans.

(emphasis mine)

Inline scalar T-SQL functions are now implemented in SQL Server 2019.


One of the table of MySQL database is missing One of the tables of my database is missing. I didn't drop it but it just went missing.
If I make a query on that table of database. It says

Query 1 ERROR: Tablespace is missing for table courtData.Data.

If I use the command SHOW TABLES it shows me all three tables. When I go to the DB folder, It has only 2 files.
The disk size used on the drive is 15GB. I don't have much going on the server other than DB so I am guessing that the db is still there somewhere on the disk.

Not table but tablespace is missing. This means that the file in which table's data was stored on disk was removed (deleted, renamed, moved, made inacessible/nonscannable). Look in the filesystem for it. Try to undelete...


Stop PostgreSQL from logging statements on error My postgresql 9.3 logs are sprinkled with entries like these (redacted to protect the guilty):

STATEMENT:  INSERT INTO table (key, value) VALUES ($1, $2)
ERROR:  duplicate key value violates unique constraint "table_pkey"
DETAIL:  Key (key)=(xyz) already exists.
STATEMENT:  INSERT INTO table (key, value) VALUES ('xyz', '{...json...}')

I know the source of the problem (a race condition where two app servers do the same work) and that code gracefully handles the insertion failure, but for the life of me I can't seem to get PostgreSQL to stop logging the statement which generated the error to begin with.

I've set log_statements for this db to 'none' (which persists across connections):

show log_statement;
 log_statement
---------------
none
(1 row)

But to no avail. These entries still keep hitting the logs. I wouldn't mind except the "values" from the logged statement are  JSON blobs which aren't tiny. How can I keep PostgreSQL from logging whenever an insert violates the unique constraint?

The amount of details logged is controlled by log_error_verbosity. Setting it to TERSE will exclude DETAIL, HINT, QUERY, and CONTEXT error information:

ALTER DATABASE db_name SET log_error_verbosity to 'TERSE';


Backup and truncate LDF with xp_sqlmaint A job is backing up an LDF with the following command:

EXECUTE master.dbo.xp_sqlmaint N'-PlanID <the id> -WriteHistory  -VrfyBackup -BkUpMedia DISK -BkUpLog "\\server\sqlbak$" -DelBkUps 6HOURS -BkExt "TRN"'

I would like the job to truncate the LDF after backing it up.

I have looked at the xp_sqlmaint documentation and it seems that the flag -RmUnusedSpace may do the trick, but I'm not sure as it seems to only remove unused space rather than truncating.

I read that using dbcc shrinkfile('logfile' , destination size) can break the backup chain, as you cannot then backup to a specific point in time.

How can I go about truncating the LDF after the backup?

How can I go about truncating the LDF after the backup?

Don't. A maintenance plan has no business truncating files, LDF nor MDF. There is no point to shrink something only to have it grown back overnite, at (huge) runtime cost. If the file is large, it means it has to be large. Truncating files is just another way of asking for downtime because it gives a false impressions of free disk, where the disk is reclaimed back during normal operations, but potentially can fail (grinding everything to a halt) because the needed space was meanwhile taken by other files.

So just don't.

On special occasions after the file has grown due to a one-time incident, a manual one-time truncation of LDF is OK. But not as part of regular maintenance, that is absolutely not OK.


Is there appreciable overhead from having a large proportion of empty table partitions in SQL Server? I recently inherited a project using partitioning on date where a daily scheduled task implements a sliding window scheme with 30 days in the past and 60 future dates.

In reality the data that is inserted uses SYSUTCDATETIME() for the partitioning column so the 60 future partitions are always empty.

Is this an issue that needs addressing or should I just let sleeping dogs lie?


Free space from image column after update I'm doing some cleanup on one of our database for development purposes on a test server. There are a couple of tables with one IMAGE column. Both tables take roughly 50Gb of space, being the image column the culprit as the the other files are just int, datetime and small size varchar. I ran an update on the tables, using recommendations I found here in SO and other sites, putting the db on simple recovery mode, disabling indexes, setting transaction isolation levet to read uncommited and updating in chunks of 1K rows. Updates takes some times to run, about an hour. Here is my code:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--mytable
UPDATE TOP(1000) mytable
SET    myImgCol = NULL
WHERE  DATALENGTH(myImgCol) > 0

WHILE @@rowcount > 0
BEGIN
    UPDATE TOP(1000) mytable
    SET    myImgCol = NULL
    WHERE  DATALENGTH(myImgCol) > 0;
END
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Setting to NULL I guess is the best way to free that space, correct?

Then, issue is that after runnning the updates the size of the database remains the same size. I guess the space is not freed up inmediately. Found several references to a ghost process that is supposed to be running behind the curtains freeing space but it takes quite long time to regain all the space that was used by the IMAGE column. Then found recommendations on running DBCC CLEANTABLE statement to force to free space and afterwards. Also some recommend running DBCC UPDATEUSAGE at the end of the process to update the reports related to used pages, reserved pages, etc. Yes, I read about the transaction log growing. My plan is to run DBCC CLEANTABLE by chunks, as recomended on BOL pages and as I've done with the updates.

Running these commands will help me reclaim the space occupied by the image columns?

EDIT

Just ran the commands. Database size remains the same. Any advice?

DBCC CLEANTABLE (0, 'myBigTableWithNullImages', 1000);
GO
DBCC UPDATEUSAGE (0);
GO

Running EXEC sp_spaceused I get following data.

database_size    unallocated space
159115.02 MB        81323.46 MB

Removing data will not release the space used by to the filesystem unless you then perform a database (or per file) shrink. The newly unallocated space will get used by new data as it comes in before any more is claimed from the OS by growing the relevant files.

If you need to fully reclaim the space then you need to look at DBCC SHRINKDATABASE and DBCC SHRINKFILE, though don't do this unless you have to as it can cause internal fragmentation that if allowed to build up can impact performance.

When you take a backup only used pages are included, so you will see backup sizes drop even though the actual database size has not reduced.


How to get a SQL value literal for arbitrary type in PostgreSQL? I want to be able to get a valid SQL literal for any type and value.

If the type is text, varchar, etc... and the value is abc I want to get 'abc'.

If the type is timestamp I want quoted and formatted timestamp... Is there a builtin function for this in PostgreSQL or do I have to build one myself?

I'm doing the case thing right now, but I'm afraid that I'll miss some kind of edge case. I can't find an exhaustive list of all possible value literals in PostgreSQL.

I'm trying to generate a CREATE AGGREGATE statement and the problem is that I can't properly format the INITCOND value for every type. I have the type and the value... here is my query:

SELECT format('CREATE AGGREGATE %s (SFUNC = %s, STYPE = %s%s%s%s%s)',
         a.aggfnoid::regprocedure,
         a.aggtransfn,
         a.aggtranstype::regtype,
         ', SORTOP = '    || NULLIF(a.aggsortop, 0)::regoper,
         ', INITCOND = '  || (case a.aggtranstype::regtype::text
                               when 'text' then '''' || a.agginitval || ''''
                               when 'varchar' then '''' || a.agginitval || ''''
                               when 'char' then '''' || a.agginitval || ''''
                               when 'timestamp' then '''' || a.agginitval || ''''
                               when 'date' then '''' || a.agginitval || ''''
                               when 'time' then '''' || a.agginitval || ''''
                               else a.agginitval end),
         ', FINALFUNC = ' || NULLIF(aggfinalfn, 0),
         CASE WHEN aggfinalextra THEN ', FINALFUNC_EXTRA' END
) AS ddl_agg
FROM pg_aggregate a
join pg_proc p  on a.aggfnoid = p.oid
join pg_namespace n ON p.pronamespace = n.oid
where n.nspname = 'public'

With quote_literal you can get a text representation of any type. Or if your input can be NULL quote_nullable might be more appropriate depending on what you wanna do with it.

select quote_literal(now());
          quote_literal
---------------------------------
'2018-08-01 16:27:42.696751+02'
(1 row)


Is there a way to connect a Postgres 9 database client to a Postgres 8 system? I a now work mainly with Postgres 9.5, but I alsoo need to connect to a number Postgres 8 databases connect them probably as different schemas, if that is possible.
Are foreign database wrappers the kind of thing which makes this possible, or is there some built in support in Postbres 9.5?

Via PostgreSQL FDW you can connect to any other PostgreSQL (as well as other databases), regardless of the version of the location (remote, local, or same) or version (older, same, or newer) of that other PostgreSQL.