How do you directly modify the system catalogs in SQL Server 2017? NOTE: I understand the risks involved, and the possibility of destroying a production system by doing this. I'm interested in doing it anyway.

Whenever I try to play with system catalogs, I gets these weird errors,

UPDATE sys.sql_logins
SET password_hash = PWDENCRYPT('pass')
WHERE name = 'sa';

Error produced,

  Msg 259 [...] Ad hoc updates to system catalogs are not allowed.

I've tried numerious ways to take the training wheels off,

sp_configure 'allow updates',`
go
reconfigure
go

But, I can't figure out the right option...

It even says in this answer:

  this is no longer possible (at least not without jumping through a ton of additional hoops beyond just an sp_configure option - it's not something you ever want to do on a production system), and all of the system catalog is now exposed through read-only views like sys.objects.

Well, what if I want to jump through those hoops. How can I do it?

!!   BE SURE TO READ THE WARNING, IN BOLD, AT THE BOTTOM   !!

The server configuration option of allow updates was rendered non-functional starting in SQL Server 2005. The documentation states that it does not error, but won't actually allow for any direct updates to system catalog tables.

The only way to achieve this now, BEING FULLY AWARE THAT THIS IS DANGEROUS AND IS NOT RECOMMENDED, is to use a Dedicated Admin Console (DAC) connection.

First you will need the actual Table name that you want to update. The name that you SELECT from is just a system catalog View, not the real Table. I usually use the following:

EXEC sp_helptext N'sys.{some name here}';

Then, do the following:

Restart the instance in Single-User mode with

sudo systemctl stop mssql-server
sudo -u mssql /opt/mssql/bin/sqlservr -m

Just to be clear: this step is to allow for system catalog updates. Connecting to the DAC itself for doing other operations does not require Single-User mode.
Connect via Dedicated Admin Console connection with sa or some other sysadmin Login. You can do this in a SQLCMD interactive session by running the following in a Command Prompt window:

sqlcmd -S admin:localhost -U sa

If the Linux SQLCMD doesn't support this for some reason, you can enable remote DAC connections and then later use a Windows machine to dug that DAC is working. You can enable DAC on Linux with,:

EXEC sp_configure 'remote admin connections', 1;
RECONFIGURE;

In that DB, try something like the following:

UPDATE sys.{whatever_name_you_found} {enter}
SET [some_column] = {some_value} {enter}
WHERE [some_other_column] = {the_better_be_correct_or_else_ID}; {enter}
GO {enter}

It won't execute the statement until you put in the GO {enter}.

DANGER WILL ROBINSON!!

Please be cautious when directly editing system catalog tables, and do not get too comfortable with the idea of doing it. This is something that should be done only if there is absolutely no other way of fixing a problem (such as the case here). There are probably several reasons to avoid making direct edits, but the two that initially come to mind are:

Much like the data-models that we create, there are likely rules and workflows for how things work that we are unaware of (e.g. denormalization, "business" rules governing the state of data across various tables, etc)
Quite likely making direct edits voids Microsoft's responsibility to assist you if you run into problems and have a support contract (I have not seen the terms of the Support agreement(s), but I have a hard time believing that such language would not be in there).

@Paul Randal confirmed in a comment: "manually editing a system table irrevocably sets a flag in the boot page of the database that marks your database as having been edited in this way, and CSS might decide to not help you if you subsequently have problems with that database."


Does SQL Server's is_nullable mean anything at all? SQL Server has a type flag called is_nullable you can see it on sys.types. Currently, (SQL Server 2019) it's only set to FALSE two types, sys.timestamp and sys.sysname. On sys.timestamp this type seems to accept null. On sys.sysname it does not. Is there any explanation of this behavior?
You can see these types with
SELECT * FROM sys.types WHERE is_nullable = 0;
SELECT TYPEPROPERTY('timestamp', 'AllowsNull');  -- returns 0

And you can use it like this,
CREATE TABLE foo ( a sys.timestamp );
INSERT INTO foo (a) VALUES ( null );

Note this won't work if you use sys.sysname

Msg 515 Level 16 State 2 Line 1
Cannot insert the value NULL into column 'a', table 'dbo.foo'; column does not allow nulls. INSERT fails.
Msg 3621 Level 0 State 0 Line 1
The statement has been terminated.

It also will not work if you create a user-defined data type which is NOT NULL,
CREATE TYPE bar FROM int NOT NULL;

Is this just a hint for the user?

The answer to your question is yes.
That column specifies the default nullability of a data type, which can be important if the user doesn't express a preference e.g. in a CREATE TABLE statement with an explicit NULL or NOT NULL specification. Sadly, that information is only present in the documentation for the old compatibility view sys.systypes:

allownulls (bit)
Indicates the default nullability for this data type. This default value is overridden by if nullability is specified by using CREATE TABLE or ALTER TABLE.|

For example, one can override the default NOT NULL behaviour of the sysname type:
DECLARE @T table (s sysname NULL);
INSERT @T (s) VALUES (NULL);
SELECT s FROM @T AS T;

s

NULL

The timestamp type (preferred name rowversion) has additional special behaviours due to its intended use to detect row changes. See this other answer for some more details. You can still override its nullability in the same way, but it is not possible to actually store a NULL for a timestamp/rowversion attribute.
There are several complex layers to determining attribute nullability. Rather than repeat some of that detail here, I refer you to Why you Should Always Specify Whether a Column Accepts Nulls by Phil Factor for background information and additional reading.


What's wrong with this MySQL query? I am getting a syntax error for my MySQL (5.5.24) query.

The error is:

  #1064 - You have an error in your SQL syntax; check the manual that
  corresponds to your MySQL server version for the right syntax to use
  near ''topics'( 'topicid' int unsigned not null auto_increment primary
  key, 'creator' at line 1

The query is:

create table 'topics'(
  'topicid' int unsigned not null auto_increment primary key,
  'creator' varchar(255) not null,
  'createtime' datetime not null,
  'content' text not null
);

What's wrong?

You are using the wrong quotes.  Use ` instead of '

CREATE TABLE `topics` (
  `topicid` int(11) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `creator` varchar(255) NOT NULL,
  `createtime` datetime NOT NULL,
  `content` text NOT NULL
) ENGINE=InnoDB;

I also would recommend specifying the engine.


How to safely add column to replicated mysql database We have 2 databases, one is a replication used for read only, with around a million records, we want to add a new column to the master db, to one of the tables.

Do I just go on MySql workbench and add the column, or do I need to shutdown the database first?

SELECT COUNT(1) FROM mytable;

===>489170

    CREATE TABLE `mytable` (
  `column1` int(11) NOT NULL auto_increment,
  `column2` char(1) NOT NULL default '',
  `column3` varchar(32) NOT NULL default '',
  `column4` varchar(32) NOT NULL default '',
  `column5` varchar(32) NOT NULL default '',
  `column6` datetime NOT NULL default '0000-00-00 00:00:00',
  `column7` varchar(96) NOT NULL default '',
  `column8` int(11) NOT NULL default '0',
  `column9` varchar(32) NOT NULL default '',
  `column10` tinyint(1) NOT NULL default '1',
  `column11` varchar(32) default NULL,
  `column12` tinyint(1) NOT NULL default '1',
  `column13` varchar(32) default NULL,
  `column14` tinyint(1) NOT NULL default '1',
  `column15` tinyint(1) NOT NULL default '1',
  `column16` varchar(32) default NULL,
  `column17` varchar(32) default NULL,
  `column18` tinyint(1) NOT NULL default '1',
  `column19` varchar(40) NOT NULL default '',
  `column20` char(1) default '1',
  `column21` varchar(11) default NULL,
  `column22` tinyint(3) unsigned NOT NULL default '0',
  `column23` int(11) NOT NULL default '1',
  `column24` int(10) default '0',
  `column25` text,
  `column26` varchar(64) default NULL,
  `column27` varchar(64) default NULL,
  `column28` varchar(64) NOT NULL default '',
  `column29` tinyint(2) default NULL,
  `column30` varchar(12) NOT NULL default '',
  `column31` varchar(12) NOT NULL default '',
  `column32` varchar(32) NOT NULL default '',
  `column33` varchar(32) default NULL,
  `column34` int(10) default NULL,
  `column35` int(10) default NULL,
  `column36` tinyint(1) default '0',
  `column37` int(1) NOT NULL default '0',
  `column38` int(11) NOT NULL,
  `column39` varchar(64) default NULL,
  PRIMARY KEY  (`column1`),
  KEY `key1` (`column7`),
  KEY `key2` (`column21`),
  KEY `key3` (`column4`,`column5`),
  KEY `key4` (`column8`),
  KEY `key5` (`column24`),
  KEY `key6` (`column38`)
) ENGINE=MyISAM AUTO_INCREMENT=489814 DEFAULT CHARSET=latin1

"Safely" can have several meanings:

If you mean transactionally-safe, that is not possible, as you are using MyISAM. Operations on MyISAM are not guaranteed to be atomic, and so recoverable. Fully crash-safe tables require InnoDB (or Aria). The good news is that most MyISAM ALTER TABLE operations are implemented by recreating the table as a separate set of files, so in the event of a crash, nothing should be lost
If you mean safe, as in no operational disruption, a table like that may take a couple of minutes to be ALTERed, during which the original table could be read without problems for most of the process. Writes to that table, however, are blocked "Waiting for table metadata lock" and queued during the whole process. CPU and IOPS will increase -as you are doing a copy of the whole table-, but nothing alarming unless you are on full capacity already.
Regarding replication, ALTER TABLE will be transmitted through the replication process in a fully consistent manner. However, no writes to that table are sent to the slave (as they are not applied to the master) until the alter finishes. The table is not ALTERed and locked on the slave until then, when the alter will be applied. Replication will pause for that period (as replication is applied serially). Everything will be done consistently (as you shouldn't be doing out of band writes on the slave), but a lag equal to the time the alter takes to execute on the slave will occur.

If lag is a no-go for you, I recommend you to use an online alter table tool like pt-online-schema-change. The tools is not without problem (as rows are copied through SQL, it will generate a lot of traffic through the binary log), but a) it will allow writes to the table while the copy is done -although with reduced throughput, due to using MyISAM- b) it will minimize lag between the nodes, as the tables on the master and on the slave require almost no locking at all c) it will throttle automatically its own operations, stopping them temporarily if it detects that a slave more than 1 seconds (by default) behind the master due to the increased replication traffic.


Postgres inet array contains a single ip Requirement: user can add single ips or ip address ranges to a database table(for example permissions table having ipAddresses of inet[] type).  I need to query that a single ip is contained in the array of ipAddresses field which can be array of ip addresses or ip address ranges or both. I have tried to add where query like this:

WHERE "ipAddresses" @> '192.168.1.5'::inet

and also like this

WHERE "ipAddresses" >> '192.168.1.5'::inet

but no luck.
Thanks in advance.

id          uuid      NOT NULL  gen_random_uuid()
permissions uuid[]
ipAddresses inet[]
createdAt   timestamp with time zone        now()
updatedAt   timestamp with time zone        now()

source: pg array functions and
pg network address functions

While McNets's solutions would also work, it's possibly the easiest to use ANY with an operator, in this case equality:

SELECT '10.10.10.10' = ANY ('{10.10.10.10, 10.10.10.20}'::inet[]);

The background is that an inet array is no different from any other array type in this regard - ANY will work with any boolean-returning operator that is defined on the base type.

The above applies to equality of single IP addresses.  If you need to match a single IP against a range, you'll need the <<= operator instead of =:

SELECT '10.10.10.10'::inet <<= ANY ('{10.10.10.10, 10.10.10.20}'::inet[]);

 ?column?
──────────
 t

SELECT '10.10.10.10'::inet <<= ANY ('{10.10.10.0/28, 10.10.10.20}'::inet[]);

 ?column?
──────────
 t

In your case, the solution would look like

WHERE '192.168.1.5' <<= ANY ("ipAddresses")

The performance of this can be poor for the lack of really efficient indexing.  To address this, see the suggestion in McNets's answer.


Does SQL Server cache calculated values in a query? Every time I run into this type of queries I always wonder how would SQL Server work it out. If I run any type of query that requires a calculation and then use that value in multiple places, for example in the select and the order by, will SQL Server calculate it twice for every row or will it be cached? Furthermore, how does this work with User Defined Functions?

Examples:

SELECT CompanyId, Count(*)
FROM Sales
ORDER BY Count(*) desc

SELECT Geom.BufferWithTolerance(@radius, 0.01, 0).STEnvelope().STPointN(1).STX, Geom.BufferWithTolerance(@radius, 0.01, 0).STEnvelope().STPointN(1).STY
FROM Table

SELECT Id, udf.MyFunction(Id)
FROM Table
ORDER BY udf.MyFunction(Id)

Is there a way to make it more efficient or is SQL Server smart enough to handle it for me?

The SQL Server query optimizer can combine repeated calculated values into a single Compute Scalar operator. Whether or not it will do this depends on query plan costing and the properties of the calculated value. As expected, it will not do this for calculated values which are nondeterministic, which a few exceptions such as RAND(). It also will not do this for user defined functions.

I will start with a user defined function example. Here is an excellent example of a user defined function:

CREATE OR ALTER FUNCTION dbo.NULL_FUNCTION (@N BIGINT) RETURNS BIGINT
WITH SCHEMABINDING
AS
BEGIN
RETURN NULL;
END;

I also want to create a table and to put 100 rows into it:

CREATE TABLE X_100 (N BIGINT NOT NULL);

WITH
L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
INSERT INTO X_100 WITH (TABLOCK)
SELECT n
FROM Nums WHERE n <= 100;

The dbo.NULL_FUNCTION function is determistic. How many times will it be executed for the following query?

SELECT n, dbo.NULL_FUNCTION(n)
FROM X_100;

Based on the query plan this will be executed once for each row, or 100 times:

SQL Server 2016 introduced the sys.dm_exec_function_stats DMV. We can take snapshots of that DMV to see how many times a UDF is executed by a query.

SELECT execution_count
FROM sys.dm_exec_function_stats
WHERE object_id = OBJECT_ID('NULL_FUNCTION');

The result of that is 100, so the function was executed 100 times.

Let's try another simple query:

SELECT n, dbo.NULL_FUNCTION(n), dbo.NULL_FUNCTION(n)
FROM X_100;

The query plan suggests that the function will be executed 200 times:

The results of sys.dm_exec_function_stats suggest that the function was executed 200 times.

Note that you cannot always use the query plan to figure out how many times a compute scalar is executed. The following quote is from "Compute Scalars, Expressions and Execution Plan Performance":

  This leads people to think that Compute Scalar behaves like the majority of other operators: as rows flow through it,  the results of whatever computations the Compute Scalar contains are added to the stream.  This is not generally true.  Despite the name, Compute Scalar does not always compute anything, and does not always contain a single scalar value (it can be a vector, an alias, or even a Boolean predicate, for example).  More often than not, a Compute Scalar simply defines an expression; the actual computation is deferred until something later in the execution plan needs the result.

Let's try another example. For the following query I would hope that the UDF is calculated one time:

WITH NULL_FUNCTION_CTE (NULL_VALUE) AS
(
SELECT DISTINCT dbo.NULL_FUNCTION(0)
)
SELECT n , cte.NULL_VALUE
FROM X_100
CROSS JOIN NULL_FUNCTION_CTE cte;

The query plan suggests that it will be calculated one time:

However, the DMV reveals the truth. The compute scalar is deferred until it is needed, which is in the join operator. It is evaluated 100 times.

You also asked what you can do to encourage the optimizer to avoid recalculating the same expression multiple times. The best thing that you can do is to avoid using scalar UDFs in your code. Those have a number of performance issues outside of this question, including inflating memory grants, forcing the entire query to run with MAXDOP 1, bad cardinality estimates, and leading to additional CPU utilization. If you do need to use a UDF and the value of that UDF is a constant you can calculate it outside of the query and to put it in a local variable.

For queries without UDFs, you can try to avoid writing expressions which return the same result but aren't typed in exactly the same way. For this next example I'm using the publicly available AdventureworksDW2016CTP3 database, but really any database will do. How many times will COUNT(*) be calculated for this query?

SELECT OrderDateKey, COUNT(*)
FROM dbo.FactResellerSales
GROUP BY OrderDateKey
ORDER BY COUNT(*) DESC;

For this query we can figure this out by looking at the Hash Match (aggregate) operator.

The COUNT(*) is computed once for each unique value of OrderDateKey. Including the ORDER BY clause does not cause it to be calculated twice. You can see the execution plan here.

Now, consider a query that will return the exact same results but is written in a different way:

SELECT OrderDateKey, SUM(1)
FROM dbo.FactResellerSales
GROUP BY OrderDateKey
ORDER BY COUNT(*) DESC;

The query optimizer is not smart enough to combine them, so additional work will be done:


How to write case when in where statement in Oracle I wrote below statement. In MySQL it executes ok, but in Oracle it fails with error:

  meg:ORA-00905: missing keyword

How should I write this SQL?

SELECT count(*)
FROM  userTable
WHERE ( CASE WHEN mac IS NULL THEN mac IS NULL ELSE mac = '000fe95erd32' END )

You're mixing up two ways of doing case. You either need:

CASE <expression>
WHEN <comparison expression> THEN <return expression>
…

or

CASE
WHEN <condition> THEN <return expression>
...

your query is malformed

SELECT count(*)
FROM  userTable
WHERE ( CASE WHEN mac IS NULL THEN mac IS NULL ELSE mac = '000fe95erd32' END )

your clause is not well good. you can do it differently
 like this :

SELECT count(*)
FROM  userTable
WHERE mac is null or mac = '000fe95erd32';

You can read here for further information.


Hierarchical references abstraction with strict model I want to abstract a real world hierarchy in a strict DB model (as strict as possible).

My problem is to to create a structure with a this-or-this-but-not-both rules set.

I have two entity types,

Device, should be connectable to one other "device" as child and as parent.
Combiner, it can be connected with multiple "devices" as parents and one as child.

BUT(!) a device can only be connected to one of them at the same time. So like "device" as only one child and one parent.

I try to visualize it somehow:

All my ideas are not strict but I searching if there is a pattern to solve this without rules on application level. On a class model I would solve this with inheritance. Some ideas?

Here is a possible solution.  Navigating the structure from top to bottom may be a little gross but maybe the app side objects can handle it.

I think you can build this out using 2 tables dbo.Device and dbo.Combiner.  Each table has a column that references who its parent object is, regardless of whether it is a device or a combiner.  We can then use a couple different types of constraints and a couple of triggers to ensure that the data follows the path you are looking to use.

Table Structures (now your objects probably have more data points than just a name but for the sake of simplicity I am only going to keep track of a name)

dbo.Device

CREATE TABLE dbo.Device
(
    ID INT IDENTITY(1,1) NOT NULL,
    Name VarChar(100) NOT NULL,
    ParentDeviceID INT NULL,
    ParentCombinerID INT NULL,
    CONSTRAINT [PK_Device] PRIMARY KEY CLUSTERED
    (
        [ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

CREATE UNIQUE NONCLUSTERED INDEX IX_UniqueParentDeviceIDDevice
ON dbo.Device(ParentDeviceID)
WHERE ParentDeviceID IS NOT NULL

ALTER TABLE [dbo].[Device]  WITH CHECK ADD  CONSTRAINT [FK_Device_Device] FOREIGN KEY([ParentDeviceID])
REFERENCES [dbo].[Device] ([ID])
GO

ALTER TABLE [dbo].[Device]  WITH CHECK ADD  CONSTRAINT [FK_Combiner_Device] FOREIGN KEY([ParentCombinerID])
REFERENCES [dbo].[Combiner] ([ID])
GO

ALTER TABLE [dbo].[Device]
ADD CONSTRAINT CHK_Device CHECK (ParentDeviceID IS NULL OR ParentCombinerID IS NULL)
GO

dbo.Combiner

CREATE TABLE dbo.Combiner
(
    ID INT IDENTITY(1,1) NOT NULL,
    Name VarChar(100) NOT NULL,
    ParentDeviceID INT NULL,
    ParentCombinerID INT NULL,
    CONSTRAINT [PK_Combiner] PRIMARY KEY CLUSTERED
    (
        [ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

CREATE UNIQUE NONCLUSTERED INDEX IX_UniqueParentDeviceIDCombiner
ON dbo.Combiner(ParentDeviceID)
WHERE ParentDeviceID IS NOT NULL

ALTER TABLE [dbo].[Combiner]  WITH CHECK ADD  CONSTRAINT [FK_Combiner_Device] FOREIGN KEY([ParentDeviceID])
REFERENCES [dbo].[Device] ([ID])
GO

ALTER TABLE [dbo].[Combiner]  WITH CHECK ADD  CONSTRAINT [FK_Combiner_Combiner] FOREIGN KEY([ParentCombinerID])
REFERENCES [dbo].[Combiner] ([ID])
GO

ALTER TABLE [dbo].[Combiner]
ADD CONSTRAINT CHK_Combiner CHECK (ParentDeviceID IS NULL OR ParentCombinerID IS NULL)
GO

Each table holds who its parent object is via ParentDeviceID or ParentCombinerID. The Unique Filtered Index on ParentDeviceID helps ensure that a given Device can only be the parent of one object (A Unique Constraint would stop multiple rows with a NULL value).  It isn't guaranteed so that is why we are going to use some triggers down the road.  The two CHECK Constraints help ensure that any given object can only have 1 parent object instead of having both a Device and Combiner as a parent. The Foreign Keys ensure that the values stored in ParentDeviceID and ParentCombinerID are valid values for those two tables.

Triggers

We can insert a Trigger on each table.  These Triggers need to ensure:

On the insert/update of a dbo.Device record a value stored on ParentDeviceID is not already on dbo.Combiner.ParentDeviceID
On the Insert/Update of a dbo.Combiner record a value stored on ParentDeviceID is not already on dbo.Device.ParentDeviceID

-- On the Insert/Update of a dbo.Combiner record a value stored on ParentDeviceID is not already on dbo.Device.ParentDeviceID
CREATE TRIGGER dbo.CombinerCheck ON dbo.Combiner
AFTER INSERT, UPDATE
AS
BEGIN
    IF EXISTS
    (
        SELECT *
        FROM INSERTED I
            INNER JOIN Device D
                ON I.ParentDeviceID = D.ParentDeviceID
    )
    BEGIN
        RAISERROR ('This Combiner cannot be added since it''s parent Device is already a parent to a different Device.', 16, 1);
        ROLLBACK TRANSACTION; --stops the Insert/Update
        RETURN
    END
END

--On the insert/update of a dbo.Device record a value stored on ParentDeviceID is not already on dbo.Combiner.ParentDeviceID
CREATE TRIGGER dbo.DeviceCheck ON dbo.Device
AFTER INSERT, UPDATE
AS
BEGIN
    IF EXISTS
    (
        SELECT *
        FROM INSERTED I
            INNER JOIN Combiner C
                ON I.ParentDeviceID = C.ParentDeviceID
    )
    BEGIN
        RAISERROR ('This Device cannot be added since it''s parent Device is already a parent to a Combiner.', 16, 1);
        ROLLBACK TRANSACTION; --stops the Insert/Update
        RETURN
    END
END

As an alternative this StackOverflow question apperently give the same or similar effect with a Unique Index on a View.  If you want to avoid Triggers than this may be a solution for you.  https://stackoverflow.com/questions/16314372/ms-sql-server-cross-table-constraint. I am not familiar with this approach but maybe it is a better solution for you.

Examples

Implementing the 1st Example provided (just 3 devices in a chain) would look something like this.  Just 3 records in dbo.Device:

+----+------+----------------+------------------+
| ID | Name | ParentDeviceID | ParentCombinerID |
+----+------+----------------+------------------+
|  1 | D1   | NULL           | NULL             |
|  2 | D2   | 1              | NULL             |
|  3 | D3   | 2              | NULL             |
+----+------+----------------+------------------+

Implementing the 2nd Example provided (1 Device --> 1 Combiner --> 3 Devices) would look something like this.  4 Records in dbo.Device and 1 Record in dbo.Combiner.

Device
+----+------+----------------+------------------+
| ID | Name | ParentDeviceID | ParentCombinerID |
+----+------+----------------+------------------+
|  1 | D1   | NULL           | NULL             |
|  2 | D2   | NULL           | 1                |
|  3 | D3   | NULL           | 1                |
|  4 | D4   | NULL           | 1                |
+----+------+----------------+------------------+

Combiner
+----+------+----------------+------------------+
| ID | Name | ParentDeviceID | ParentCombinerID |
+----+------+----------------+------------------+
|  1 | C1   |              1 | NULL             |
+----+------+----------------+------------------+

One more, slightly more complicated example to display all the relationship options:

dbo.Device
+----+------+----------------+------------------+
| ID | Name | ParentDeviceID | ParentCombinerID |
+----+------+----------------+------------------+
|  1 | D1   | NULL           | NULL             |
|  2 | D2   | 1              | NULL             |
|  3 | D3   | NULL           | 1                |
|  4 | D4   | NULL           | 1                |
|  5 | D5   | NULL           | 1                |
|  6 | D6   | NULL           | 2                |
|  7 | D7   | NULL           | 2                |
|  8 | D8   | NULL           | 2                |
+----+------+----------------+------------------+

dbo.Combiner
+----+------+----------------+------------------+
| ID | Name | ParentDeviceID | ParentCombinerID |
+----+------+----------------+------------------+
|  1 | C1   | 2              | NULL             |
|  2 | C2   | NULL           | 1                |
+----+------+----------------+------------------+

Hopefully this give you all you need from the database side.  Again I think this will be complicated on the App side navigating the table structures.  But the database should keep the relationships the way you want to.


Oracle ASM Backup Strategy When the Full Backup (hot backup)  happens and let us say it takes about 5-6 hours to complete.  Then what about the Transactions that happen during the backup.  Will it be recorded in the Backup file when the Full Backup is complete ?

If you include PLUS ARCHIVELOG in backup command, transactions that happened during the backup will be recorded in a separate file.


How to use case and filter with a group by statement and having an aggregate calculation? I would like to do the following

SELECT
  PO.col1,
  PO.col2,
  CASE
       WHEN PO.col3 <> 8 THEN PO.col4
       WHEN PO.col3 = 8
            THEN CASE
                      WHEN (ROUND(CAST(PO.col4 AS double) - SUM(CAST(PO.col5 AS double)), 2)) > 0
                            AND SUM(CAST(PO.col5 AS double)) > 0
                            THEN ROUND(CAST(PO.col4 AS double) - SUM(CAST(PO.col5 AS double)), 2)
                      END
       END AS Quantity
FROM
  my_table AS PO
GROUP BY
  PO.col1, PO.col2
HAVING
  Quantity > 0

I apologize for not being able to provide sample data/columns. I am not allowed to share my work data.

The logic which I have been told to implement is as follows.

For each unique pair combination of col1, col2, get a constructed attribute called Quantity. Quantity = col4 wherever col3 = 8. If col3 != 8, then check SUM(col5) > 0. If yes then check col4 - SUM(col5) > 0. If yes, then keep the record, else skip.

Out of my own observation I checked that COUNT(DISTINCT col4) for each unique pair of col1 and col2 is 1. I hope that's helpful answering my question.

We are working with Spark SQL so I don't know what tag to use for this in the question.

SAMPLE DATA

FWIW, I think you will be way better of separating the 2 cases for PO.col3, and combine the results with a UNION (which I assume is what you had before). Meanwhile, I tried to fix your query so that it compiles in your fiddle:

SELECT * FROM (
    SELECT
        PO.col1,
        PO.col2,
        CASE WHEN PO.col3 <> 8 THEN PO.col4::float
             WHEN PO.col3 = 8
             THEN CASE WHEN (CAST(PO.col4 AS float) - SUM(CAST(PO.col5 AS float))) > 0
                        AND SUM(CAST(PO.col5 AS float)) > 0
                       THEN (CAST(PO.col4 AS float) - SUM(CAST(PO.col5 AS float)))
                       END
             END AS Quantity
    FROM mytable AS PO
    GROUP BY PO.col1, PO.col2, PO.col3, PO.col4
) AS T
WHERE Quantity > 0

I have no idea whether it is correct or not, for that I would need a small sample and an expected result.


Convert the IP Address range to two BIGINT for faster lookup I am working on a project in which we need to do IP Address lookups. So for this we have got the datasets which will look like below and now the next steps is to populate this datasets in to the MySql table. Below is the dataset example containing columns-

ip_address      country region  city           metro-code   latitude    longitude   postal-code   country-code   region-code    city-code   continent-code  country-code

24.32.116.116   usa       tx    clarksville    623           33.6103    -95.0498    75426            840           44             34918            6            us
65.29.108.232   usa       mi    novi           505           42.4637    -83.4604    48375            840           23             4339             6            us
66.169.99.69    usa       tx    ft worth       623           32.7016    -97.3823    76109            840           44             771              6            us
72.70.100.111   usa       ma    peabody        506           42.5332    -70.9726    1960             840           22             1392             6            us
174.252.116.137 usa       va    oakton         511           38.8892    -77.3222    22124            840           47             3860             6            us
68.55.144.242   usa       md    pasadena       512           39.1276    -76.5125    21122            840           21             4358             6            us
174.252.83.27   usa       pa    lancaster      566           40.0459    -76.3542    17603            840           39             333              6            us
174.78.192.31   usa       ga    warner robins  503           32.5958    -83.6384    31088            840           11             5052             6            us
98.192.146.235  usa       fl    panama city    656           30.1804    -85.5598    32404            840           10             671              6            us
71.192.181.20   usa       ma    springfield    543           42.1187    -72.5483    1109             840           22             967              6            us
76.183.54.227   usa       tx    dallas         623           32.7202    -96.6769    75217            840           44             77               6            us
69.62.143.140   usa       ca    granite bay    862           38.7442    -121.191    95746            840           5              49451            6            us
69.142.76.32    usa       nj    penns grove    504           39.707     -75.4467    8069             840           31             2335             6            us
70.173.54.93    usa       nv    las vegas      839           36.2059    -115.225    89108            840           29             173              6            us
98.242.8.222    usa       ca    fresno         866           36.7968    -119.883    93722            840           5              19               6            us

Problem Statement:-

I am planning to store the START_IP_NUM and END_IP_NUM as BIGINT in the MySql database instead of storing IP Address in the table as doing a lookup against a BIGINT is far faster than searching a string (dotted ip notation).

So my question is we will be having separate Java Program that will populate the above datasets in the MySql table. So I need to device some sort of logic which can convert the above datasets into like below-

start_ip_num
end_ip_num
country
region
city
metro-code
latitude
longitude
postal-code
country-code
region-code
city-code
continent-code
country-code

I am confuse given a IP Address how should I device start_ip_num and end_ip_num here and store it into the MySql table

If I am reading your requirements correctly.

From the dataset provided the start_ip_num and end_ip_num will be the same.

Why you may ask? you are not storing a range of ip addresses; take for example a /24 range: 192.168.1.0/24 this provides a range 192.168.1.0 - 192.168.1.255, whilst really only ip's 192.168.1.1 though 192.168.1.254 are usable (.255 being the broadcast).

If you are storing a 'range' the start and end values will differ

mysql> select INET_ATON('192.168.1.1') as start_ip_num, INET_ATON('192.168.1.254') as end_ip_num;
+--------------+------------+
| start_ip_num | end_ip_num |
+--------------+------------+
|   3232235777 | 3232236030 |
+--------------+------------+
1 row in set (0.00 sec)

If not they will be the same, if you want the ranges for each a whois 24.32.116.116 will reveal this information.

e.g.

24.32.116.116 has
24.32.0.0/16

and
24.32.116.0/22  "Comment:        Clarksville, Tx Customers"

The later providing the required information.

As such your 'range' is strictly speaking: 24.32.116.0 - 24.32.119.255

mysql> select INET_ATON('24.32.116.0') as start_ip_num, INET_ATON('24.32.116.255') as end_ip_num;
+--------------+------------+
| start_ip_num | end_ip_num |
+--------------+------------+
|    404780032 |  404780287 |
+--------------+------------+
1 row in set (0.00 sec)


Clarification on mysql deadlock situation Following deadlock is encountered while load testing a application. After going through MySQL documentation and other online resources what I figured out was that

1) Transaction (2) holds exclusive lock for PERMISSION_ID index.

2) Transaction (1) comes and try to acquire insert intention exclusive lock for PERMISSION_ID. But put on to wait since exclusive lock is already with (2)

3) Then transaction (2) also wants to acquire insert intention exclusive lock for PERMISSION_ID which place it behind (1) in waiting queue. Thus deadlock.

What I am not sure about my explanation is assumption in step (2) where request to insert intention exclusive lock for PERMISSION_ID waits for exclusive lock to be released. Is it correct or am I missing something here. Please explain.

*** (1) TRANSACTION:
TRANSACTION 22466201, ACTIVE 0 sec inserting
mysql tables in use 2, locked 2
LOCK WAIT 10 lock struct(s), heap size 1184, 16 row lock(s), undo log entries 2
MySQL thread id 12287, OS thread handle 0x2b4ea1f61700, query id 130397444 10.0.1.222 admin Sending data

INSERT INTO ROLE (PERMISSION_ID, ROLE_NAME, DOMAIN_ID) VALUES (4188, 'everyone', (SELECT DOMAIN_ID FROM DOMAIN WHERE TENANT_ID=1 AND DOMAIN_NAME='INTERNAL'))

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10787 page no 139 n bits 176 index PERMISSION_ID of table IDENTITY.ROLE trx id 22466201 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 22466203, ACTIVE 0 sec inserting
mysql tables in use 2, locked 2
8 lock struct(s), heap size 1184, 14 row lock(s), undo log entries 1
MySQL thread id 12279, OS thread handle 0x2b4ea1700700, query id 130397446 10.0.1.222 admin Sending data

INSERT INTO ROLE (PERMISSION_ID, ROLE_NAME, DOMAIN_ID) VALUES (4187, 'anonymous', (SELECT DOMAIN_ID FROM DOMAIN WHERE TENANT_ID=1 AND DOMAIN_NAME='SYSTEM'))

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 10787 page no 139 n bits 176 index PERMISSION_ID of table IDENTITY.ROLE trx id 22466203 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10787 page no 139 n bits 176 index PERMISSION_ID of table IDENTITY.ROLE trx id 22466203 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)

Your assumption is correct. Nevertheless, you need to pay attention to what is being locked.
Both transactions are locking

space id 10787
page no 139
n bits 176
index PERMISSION_ID of table IDENTITY.ROLE

The DB Connections 12287 and 12279 ran one INSERT each
Here is what I notice: In each INSERT, you are running SELECT DOMAIN_ID FROM DOMAIN WHERE TENANT_ID=1 AND DOMAIN_NAME='...' to fill in DOMAIN_ID. This would cause additional locks because a query's result is needed to fill DOMAIN_ID while other indexes are made to wait for the materialization of ROLE's primary key. This deadlock lasts a little longer that normal because of this intermittent need to run a SELECT.
SUGGESTION
Instead of doing
INSERT INTO ROLE (PERMISSION_ID, ROLE_NAME, DOMAIN_ID) VALUES (4188, 'everyone',
(SELECT DOMAIN_ID FROM DOMAIN WHERE TENANT_ID=1 AND DOMAIN_NAME='INTERNAL'));

you should retrieve the DOMAIN_ID separately into a variable and then use it in the INSERT
SELECT DOMAIN_ID INTO @domain_id FROM DOMAIN
WHERE TENANT_ID=1 AND DOMAIN_NAME='INTERNAL';
INSERT INTO ROLE (PERMISSION_ID, ROLE_NAME, DOMAIN_ID) VALUES (4188,'everyone',@domain_id);

This might reduce the need to pile up locks


Msg 39011, SQL Server was unable to communicate with the LaunchPad service We have just installed SQL Server Enterprise 2016 CTP 3.3 on a server that already had SQL 2012 Standard installed.  We installed SQL2016 as a Named Instance and have been trying to get the R integration working, but we are having problems running the external scripts from management studio.

We walked through the Install the R Packages described here: https://msdn.microsoft.com/en-us/library/mt590809.aspx,
And we have manually run the Post-Installation R Configuration steps listed here: https://msdn.microsoft.com/en-us/library/mt590536.aspx

At this point, we are able to connect to SQL from RStudio and manipulate data locally or on the server.  However, we are not able to specify R script in Management Studio.  When we try to run an R script in TSQL, we get the error message:

  Msg 39011, Level 16, State 1, Line 1
  SQL Server was unable to communicate with the LaunchPad service. Please verify the configuration of the service.

We have tried several of the sample R scripts including the following:

execute sp_execute_external_script
@language = N'R'
, @script = N' mytextvariable <- c("hello", " ", "world");
                 OutputDataSet <- as.data.frame(mytextvariable);'
, @input_data_1 = N' SELECT 1 as Temp1'
WITH RESULT SETS (([col] char(20) NOT NULL));

execute sp_execute_external_script
@language = N'R'
, @script = N' OutputDataSet <- InputDataSet;'
, @input_data_1 = N' SELECT *  FROM MyData;'
WITH RESULT SETS (([NewColName] int NOT NULL));

execute sp_execute_external_script
@language = N'R'
, @script = N' SQLOut <- SQLIn;'
, @input_data_1 = N' SELECT 12 as Col;'
, @input_data_1_name  = N'SQLIn'
, @output_data_1_name =  N'SQLOut'
WITH RESULT SETS (([NewColName] int NOT NULL));

All of them result in the same error messages about not being able to communicate with the LaunchPad service.

Here are the configuration options we have verified seem to be configured correctly:

We can see that “external scripts enabled” has a value of 1 when running “exec sp_configure”
We can see the Windows User Group “SQLRUserGroupSQL2016” has been created in “Local Users and Groups”
We can see the 20 Windows Users created for R processing in “Local Users and Groups”
We can see that the Database Role “db_rrerole“ was created correctly on master db.
We can see the Extended Stored Procedures “xp_ScaleR_ …” have been correctly created on master db.
We have verified that the SQL Server Service for our Instance is running along with the SQL Agent Service and SQL LaunchPad Service.

They are all logged in as the same Windows Domain User Account, and that user has been added to the Administrators Group for the server.

We have tried restarting the above services many times as well as rebooting the server.
We have tried connecting to Management Studio both using Windows Auth with a Domain Account that is in the Database Role “sysadmins”, and as a local SQL User Account that is in the Database Role “sysadmins”
We manually added those users to the “db_owner” and “db_rrerole” roles on the master db (just to be sure)

Don’t know what else to check…

UPDATE: It looks like R Services setup is significantly simpler from SQL 2016 RC2 onwards.  See here.

This post applies to early CTPs of SQL Server 2016 where additional setup was required for R.

I had this error when I upgraded from CTP3.2 to 3.3.  The first thing to check is, if you have a named instance, did you use that name when you ran the post-installation script:

"...rxLibs\x64\registerRext.exe" /install /instance:sql2016

I assume so because you've got the db_rrerole but if not, uninstall using the uninstall switch and try again using your instance name:

"...rxLibs\x64\registerRext.exe" /uninstall

Monitor the output for errors during the install or consider re-directing the output to a file so you can review it afterwards, eg:

"...rxLibs\x64\registerRext.exe" /install /instance:yourNamedInstance > mylog.txt

Check through the log and report any errors you find.  If the script succeeds, the Launchpad service should be running and you can check this by running services.msc from the command-line.  Here's my service running for the default instance:

Yours should say SQL2016 where mine says MSSQLSERVER.  If that's all ok you can open up the the config file, eg from the command-line:

notepad "%programfiles%\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\rlauncher.config"

You will have to change the path above, but the file should be in the Binn directory of your SQL Server install.  Inspect the config file and make sure the paths are correct / look sensible.  I found initially that there were version numbers on the paths from multiple installs eg ( MSSQLSERVER1000 ) and basically this was interfering with the R install.  You can check registry keys HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLLaunchpad$SQL2016 and HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server to see if they are consistent but I strongly advise against making changes to your registry, unless you can afford to lose the machine / VM and have to do a full re-install of Windows etc.

I basically did a full uninstall of SQL Server (including a somewhat brutal registry clean-up) and then re-installed a default instance and it worked.  I've since resolved not to mix CTPs and set up clean VMs for each one.  Also remember CTPs are basically beta products so you should not install them on any servers you value - hopefully this is a test box or non-production VM?

My best advice to you is honestly just use a VM for the CTPs because they are not designed to play well with other versions or even CTP upgrades.  I use VirtualBox on my laptop which is fine most of the time.  If you have an MSDN subscription (or even a trial one) you can set up a free VM with the latest CTP and throw it away when you're done.

Good luck!


MySQL - Where can I find metrics on the performance of Blob vs file system? I know and understand that there are performance hits in storing blob data in the database, but the blob portion of the data is going to be rarely retrieved/viewed, it is for smaller data (the vast majority under 256k with a max of 10mb), it is not going to be used by most customers, and the total rows is expected to be relatively low, very likely under half a million, if not less. Also some of the data is dynamic and can change for some users, as in it's not static images. In other words we're at the edge of whether or not it's worth it.

I keep reading that it's better to store in the file system but I can't find actual metrics that show the performance difference, just people repeating each other without any concrete proof or metrics. For us it may be worth the performance cost in exchange for being fully ACID as well as guaranteeing that all our backups are completely synched.

That being said does anyone know or have any real world metrics to show the performance difference between storing items as blobs vs in the file system. I'm trying to understand if the performance penalty is worth it or not rather than blindly following the general rule of thumb and after spending at least 2-3 hours and I've yet to be able to see anyone show any actual numbers. It's all just words with nothing concrete.

By the way this is a MySQL InnoDB table. The actual data table has a link to the blob data table, so the blob is not in the main able and is only retrieved when need be to avoid any I/O issues. In other words instead of the path to the data on the filesystem, it's an ID to another table with only blobs. How does that compare in terms of performance? Is it 25% worse? Is it 100%? Is it 200-500%? Is it 1000%?

If the cost is only 100%-200% it is probably worth it for us because again the data is rarely retrieved. So even if we had say 10,000 concurrent users, maybe only 50 users would be retrieving their blob data concurrently at best. Yes the data is specific to each user, it isn't images.

The main cost of handling the data is the I/O.  You are doing approximately the same amount of I/O, whether it is in 4KB chunks in the OS (plus directory traversal) or 16KB chunks in InnoDB (plus indirect block lookup).

The filesystem and InnoDB are cached in radically different ways; this may factor into a difference -- depending on how cacheable the blogs are.

You say "rarely retrieved". So why does speed matter?

So, I doubt if it will be more than 25% difference.  And I can't predict which will be faster.

As for space, again there are several differences, so it is hard to predict which would be tighter.  In any case, the diff can't be more than about 2% for the size blobs you mentioned.

How compressible are the blobs?  (Most image formats are already compressed; text is typically compressible 3:1.)  If compressible, then do so in the client.  (InnoDB's builtin optional compression is easier, but not as good.)

And, yes, having it in a "parallel table" (as you mentioned) is often better.

Another point -- If the blob is an image destined for a web page, it is more efficient to simply have it in a file and say <img srg=file-path>.  If it is in a table BLOB, you have to do extra work to hand it off to the web page.  Since I/O is the main difference, I might expect the img tag to be 2x faster.


Cassandra query performs differently at different times I am using apache-cassandra-2.0.12 in production, with network topology strategy and ReplicationFactor : 3 in a cluster with  2 DC’s each contains 4 nodes

While analyzing the response time for the read requests, we found out that some queries are performing slower than it actually does.
Eg : Consider the following table

Create ColumnFamily "Employee"
(
  empID bigint,
  uniqueID text,
  col1 text,
  col2 text,
  col3 text,
  primary key (empID,uniqueID)
)

This CF contains data for more that 5K row entires and and each employee contains minimum of 100K columns and the maximum of 1000K columns

So in this CF the response time for the following queries differs drastically from time to time

SELECT * form "Employee" where empID = xxx and uniqueID = 'value';

Some times the response time for the above query is more than 3 sec, whereas it should actually take within 50 milliseconds

I have monitored the load (compaction time, disk utilizations etc ), CPU and the memory of the nodes at that time . All these params were normal.

Is there anything that I have missed or is this the normal behavior of cassandra ?

Note: I don't have any tombstone columns in this CF

primary key (empID,uniqueID)

  This CF contains data for more that 5K row entires and and each employee contains minimum of 100K columns and the maximum of 1000K columns

That's WAY too many rows per partition.  My guess is that the query slowdowns happen when large partitions are queried.  It all depends on data cell value size and data width, but as a general rule, I would not model more than 10k-30k rows per partition.

To test this out, you could run nodetool tablehistorgrams on your table to gauge things like max cell count and partition size.  Then run your query against both small and large partitions with TRACING ON, and I'm sure you'll see the difference.

Basically, try reworking your model for smaller partitions.


Why does pg_dumpall use the wrong encoding in Ubuntu 16.04 / Postgres 9.5? I cannot run a "pg_upgradecluster" because there are problems with pg_dumpall. I have set the encoding to UTF8 everywhere on the server but pg_dumpall always uses

"SET client_encoding = 'LATIN1';"

. Where does this "LATIN1" come from?
postgres=# show server_encoding; show client_encoding;

server_encoding
-----------------
 UTF8
(1 row)

 client_encoding
-----------------
 UTF8
(1 row)

postgres@ubunt:~$ locale
LANG=en_US.UTF-8
LANGUAGE=
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=

postgres@ubuntu:~$ env
SHELL=/bin/bash
TERM=xterm
USER=postgres
MAIL=/var/mail/postgres
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/snap/bin
PWD=/var/lib/postgresql
LANG=en_US.UTF-8
SHLVL=1
HOME=/var/lib/postgresql
LOGNAME=postgres
XDG_DATA_DIRS=/usr/local/share:/usr/share:/var/lib/snapd/desktop
_=/usr/bin/env

postgres@ubuntu:~$ cat /etc/default/locale
#  File generated by update-locale
LANG=en_US.UTF-8
#LANGUAGE="en_US:en"

Because of the wrong encoding I get errors:

pg_dump: Dumping the contents of table "TBLDATEN" failed:
PQgetResult() failed. pg_dump: Error message from server: ERROR:
character with byte sequence 0xe2 0x80 0xa6 in encoding "UTF8" has no
equivalent in encoding "LATIN1"

I have the same problem with pg_dump but then I can use

pg_dump -E UTF8

to set the correct encoding.

postgres@recworus157:~$ psql -l | grep LATIN
postgres@recworus157:~$ psql -l
                                         List of databases
         Name         |    Owner    | Encoding |   Collate   |    Ctype    |   Access privileges
----------------------+-------------+----------+-------------+-------------+------------------------
 Liste1               | cpl_user    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 Liste2               | postgres    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 Liste3               | postgres    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 Liste4               | postgres    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 Liste5               | postgres    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 Liste6               | postgres    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
...

Set the environment variable PGCLIENTENCODING to UTF8, then the dump will be created in UTF-8.


Order by x DESC - but only continuous rows I have a table with an ID (integer) and a text field (the name). My current query looks like this:

SELECT id, name FROM table WHERE id <= X ORDER BY id DESC

X is an ID I get from another query. What this query currently does is it outputs all the entries lower than the ID I put into, ordered by ID.

What I'd like to achieve is that this is only done until the first "gap" in the IDs - for example, with a table like this:

id      name
1       Test 1
2       Test 2
3       Test 3
6       Test 6
7       Test 7
8       Test 8
9       Test 9

I'd like to have a query which only returns the following set for X = 8:

id      name
8       Test 8
7       Test 7
6       Test 6

Because then, there is a gap in the IDs (ID 5 is missing), and then I don't need the other lower IDs.

How could that be done?

This should work:

SELECT id, name
FROM data
WHERE id <= 8 AND id >= (
    SELECT MAX(d1.id)
    FROM data d1
    LEFT JOIN data d2 ON d2.id = d1.id - 1
    WHERE d2.id IS NULL
)
ORDER BY id DESC
;

The subquery look for the first gap (i.e. 5 to 6). See SQL Fiddle.

Output:

id | name
 8 | Test 8
 7 | Test 7
 6 | Test 6


How to list objects that are using another object? I have to move certain objects from a schema to another. The database is rather large and
I want to make sure we test properly all affected objects.

On TOAD there is a Tab called "Used By" that show all objects that are referencing the selected object.

Is there a way to achieve a similar output with an SQL Command?
I tried checking the different DBA Views but I haven't been able to find any with the information I need.

This seems to be the query from TOAD when you click the used_by tab - you just need to replace the 'OBJECT_NAME' with whatever the name of the object you are interested in is, and change the object_type to whatever is appropriate for your object.

WITH Objs
     AS (    SELECT object_id
               FROM (SELECT object_id, referenced_object_id
                       FROM public_dependency
                       WHERE referenced_object_id <> object_id) pd
         CONNECT BY PRIOR object_id = referenced_object_id
         START WITH referenced_object_id = (select object_id from dba_objects where object_name = 'OBJECT_NAME' and object_type = 'TABLE'))
SELECT o.owner,
       o.object_type,
       o.object_name,
       o.object_id,
       o.status
  FROM sys.DBA_OBJECTS o, Objs
 WHERE o.Object_id = Objs.Object_id

You can view the SQL that TOAD generates by starting a second session, go into the session browser and then view the current statement to see the query your first TOAD session is running.


Execution plan not understanding correctly I am running a query:

SELECT * FROM message where fr_user = 1 ORDER BY created DESC LIMIT 10;

Without the LIMIT there would be 170 rows returned where fr_user = 1;

I am now trying to understand how efficient this query (not trying to improve the query, just to see how efficient it CURRENTLY is) is.

I have ran the query and looked at the execution plan:

In the execution plan there is a query cost of 43.00... what does this mean/how is it calculated?
What do the four fields (Read, Eval, Prefix, DataRead) mean?
Why does the ratio of rows examined to the ratio of rows produced = 100% when there are 170 rows examined and 10 rows produced? I have an index (fr_user, created) so why are all the rows (170) being examined? If I have to examine all the rows it would seem pointless to have an index then?

(To answer @Michael, then segue into the OP's question.)  ORDER BY fr_user DESC, created DESC can use an index backwards if all are DESC.  Or, as in this orginal case, it can do WHERE fr_user = constant ORDER BY created DESC when you have INDEX(fr_user, created).  It simply goes to the end of the fr_user=1 entries in the index and walks backward.

But your question is "why are all 170 rows examined".  Well, they aren't.  The "170" is bogus because EXPLAIN almost always ignores LIMIT.  This is a known failing of EXPLAIN.  The Optimizer, in some situations, does take LIMIT into consideration.


First log backup after FULL taking lots of space In the backups I'm doing in my database, the first LOG backup I do after the FULL backup is taking too much space, even more than the full backup.

For example:

4:50 AM:  FULL BACKUP  - .bak 7.2GB
6:30 AM:  LOG BACKUP   - .trn 9.3GB
7:00 AM:  LOG BACKUP   - .trn 2mb
7:30 AM:  LOG BACKUP   - .trn 150mb
8:00 AM:  LOG BACKUP   - .trn 1mb
8:30 AM:  LOG BACKUP   - .trn 50mb

Note that the first LOG backup is in GB, while the others are in MB.

What I'm doing is the following:

4:50 am:  (using multiple steps in the same job, not running all of them in parallel)

Index optimize
DB integrity check
FULL backup

6:30am and every 30 minutes after that:

LOG backup

Also, another job called PurgeOldImportData is executed every hour and it deletes old information, but it's currently deleting very little information (if any).  In fact, it takes only 2 seconds to execute.

Can anyone tell me why is this happening? Besides the space it takes, it takes longer to do the RESTORE.  Thank you!

UPDATE:

I'm using Ola Hallengren's scripts for backup.  This is what each job/step does:

JOB: DatabaseBackup - FULL

IndexOptimize:   [dbo].[IndexOptimize] @Databases = 'USER_DATABASES'
Database Integrity Check:  [dbo].[DatabaseIntegrityCheck] @Databases = 'USER_DATABASES'
Full Backup:  [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'D:\SQLServer\Backups', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 24, @CheckSum = 'Y'
BackupToS3:  (executes a .bat which moves the file, does not touch the SQL Server)

JOB: DatabaseBackup - LOG

[dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'D:\SQLServer\Backups', @BackupType = 'LOG', @Verify = 'Y', @CleanupTime = 24, @CheckSum = 'Y'
BackupToS3:  (executes a .bat which moves the file, does not touch the SQL Server)

At night users run some import data files, but they are not running nothing that produces 9GB of changes AFAIK. Even more, the biggest data imports are not run from 4:50am to 6:30am.

Taking a FULL backup won't clear out the transaction log entries (unless the database is in SIMPLE Recovery Model). So whatever changes take place between your last LOG backup before the FULL backup (@ 4:30 AM), and the first LOG backup once that process is over (@ 6:30 AM) would be included in that 6:30 AM LOG backup. That is a 2 hour span (you typically do a LOG backup every 30 minutes otherwise) in which you, at the very least, run index maintenance via the IndexOptimize stored procedure.

If you want to test this out, just temporarily disable both the "IndexOptimize" and "Database Integrity Check" steps (meaning, if you are using the CmdExec step type so it can call SQLCMD, just add REM to the beginning of the line in both steps). Let that run for one night and if I am right, it will be very obvious. Then, remove the REM from one of the two steps so that only one of them runs again (maybe start with the "Database Integrity Check" step) and let that run for one night and then you should see how much impact each of those first two steps has on that "first" LOG backup.

If the "Database Integrity Check" step is taking up more than a small portion of that LOG backup, then you might be able to split that initial backup into two (even if not an even 50/50) by doing a LOG backup after the "IndexOptimize" step. If all (or most) of the LOG backup size is from the "IndexOptimize" step, then maybe try to schedule a LOG backup for 4:36 AM to attempt to cut the resulting LOG backup in approximately half (given that you show that first step running for just under 14 minutes).


Changing all character varying to text across all tables and columns I'm trying to find a way to iterate over all of my tables and changing each charvar[n] to text and charvar[][n] to text[].
However, only found ways to change it for a specific table and column or specific column name. Using postgres 10.
Any advice?

Thanks

For one-off changes, create a query to generate the SQL commands so that you can review them:

SELECT format('ALTER TABLE %I.%I ALTER COLUMN %I SET DATA TYPE text;',
              table_schema, table_name, column_name)
FROM information_schema.columns
WHERE udt_name = 'varchar'           -- '_varchar' for arrays
  AND table_schema = 'my_schema';


MySQL using a index referencing a different column I have a large table which uses a versioning system in the table. When a record is updated a new record is inserted with the old data and the original record is updated ( so to maintain the same primary key for the original record ).

In the new record there is a reference to the original record.

So to query the last record for a specific customer/user we need to do the following.

select * from  where business_key = 1234 and parent = id

I want add an index on business key does it make sense to make the index a multi-column index?

ALTER TABLE MyTable ADD INDEX NewIndx ( business_key ASC, parent ASC, id ASC )

Will the parent part in the index be used, because it is referencing the id and a constant?

Sample records

id | parent | data
1  | 1      | Last record
2  | 1      | previous record of 1
3  | 3      | Other record

According to your question, you use select * from MyTable where business_key = 1234 and parent = id;  to get the last record.
There is caveat you need to be aware of with regard to the index. If a particular business_key has 1000 records, the query will do a index range scan through all 1000 key entries. Since you know that parent=id indicates the last record, it would plausible for you to change the query to take advantage of that. Why? The index you would automatically have the ids sorted for any given business_key,parent combination. With that in mind, please change two things
CHANGE #1 : Refactor the Query to the following:
select B.* from
(
    select MIN(id) id FROM MyTable
    where business_key = 1234
) A INNER JOIN MyTable B using (id);

Keep in mind that MAX(id) would be the original (the oldest) record.
CHANGE #2 : Add the following index
ALTER TABLE MyTable ADD INDEX NewerIndx (business_key,id);

If you have queries that have this WHERE clause (WHERE business_key=1234 AND parent=999), then do not drop NewIndx. You may want to drop your old index on a staging or dev server and test all your queries, if any of the queries get worse on the dev server, keep your old index.
Give it a Try !!!


Restrict Front Ended User access to Form entry only I have a front end database that I want to restrict user access to only enter data into a form. I do not want them to be able to scroll through other entries via the form. How can I restrict this in Access 2007-2010?

There's a property on the form called DataEntry
DataEntry property documentation

If you set it to True, it will only allow new additions, it will not show you other records.


Compare two columns and replace duplicates with nulls I have a table like this:

id  | t1 | t2
---------------
 1  |  a |  b
 2  |  c |  a
 3  |  a |  e
 4  |  f |  g
 5  |  c |  c

I want to compare columns t1 and t2 with each other to remove matching values and get unique values in each columns, like this:

   t1 | t2
  -----------
    a |  b
    c |  null
 null |  e
    f |  g
  null| null

It doesn't matter much which row to choose to show the value and which other ones would show nulls. For instance, as value a is also found in row 3 of the original dataset, the following output would also be valid:

   t1 | t2
  -----------
 null |  b
    c |  null
    a |  e
    f |  g
  null| null

The end goal is to show all distinct values and no identical values across the two columns.

Without window functions this is pretty hard in MySQL. Since the two columns - t1 and t2 - store similar content and you only want disctinct values from both, it would be much easier to get the values in a single column:

select t1 as tx from t
union distinct
select t2 from t ;

I don't see any reason to have the original convoluted result, except if you want to keep info from other columns in the table and just remove duplicates from these 2 columns. And in that case, an UPDATE would make more sense.

Here is a method to get this result, anyway. It assumes that (id) has a UNIQUE constraint. Tested at dbfiddle.uk:

select t.id, gt1.tx as t1, gt2.tx as t2
from t
  left join
  ( select ut.tx, min(ut.id) as id
    from
      ( select id, t1 as tx from t
        union all
        select id, t2 from t
      ) ut
    group by ut.tx
  ) as gt1
  on t.t1 = gt1.tx and t.id = gt1.id
  left join
  ( select ut.tx, min(ut.id) as id
    from
      ( select id, t1 as tx from t
        union all
        select id, t2 from t
      ) ut
    group by ut.tx
  ) as gt2
  on t.t2 = gt2.tx and t.id = gt2.id and gt1.tx <> gt2.tx
 ;

Notice that in MariaDB (MySQL's first cousin), that has CTEs, the same query can be rewritten more compactly and more clearly:

with gt as
  ( select ut.tx, min(ut.id) as id
    from
      ( select id, t1 as tx from t
        union all
        select id, t2 from t
      ) ut
    group by ut.tx
  )
select t.id, gt1.tx as t1, gt2.tx as t2
from t
  left join gt as gt1
    on t.t1 = gt1.tx and t.id = gt1.id
  left join gt as gt2
    on t.t2 = gt2.tx and t.id = gt2.id and gt1.tx <> gt2.tx
 ;

Logically, both variations work the same way, though. This subselect:

  ( select ut.tx, min(ut.id) as id
    from
      ( select id, t1 as tx from t
        union all
        select id, t2 from t
      ) ut
    group by ut.tx
  )

returns all distinct t1 and t2 values along with the ID of the first1 row where each is encountered. For your example, it produces this output:

tx   id
---  ---
a    1
b    1
c    2
e    3
f    4
g    4

The above set is joined against the original table twice, on t1 and on t2. In each case, where the original row's ID matches the subselect row's ID, the value is returned intact, because the match indicates that the row is the value's first occurrence; otherwise the value is replaced with a null.

1“First" in the order of ID.


Proactively Determining Bad Plans/Poor Performance at a Query Level tldr; I'm looking for ways to monitor server performance to alert when individual, small, fast queries get a bad plan that will in aggregate hurt the performance of the business.

Over the past week we've suddenly had a few stored procedures grab bad query plans, and drag the server to slow downs.  It hasn't been the same query, but it has involved the same tables for one group (which I imagined was a statistics problem), and when we FREEPROCCACHE'd the server another query got a bad plan, and has picked it up at random again.

Being the guy who is held responsible for the server, I'm being asked to do things like ensure no query will ever get a bad plan again, or that I react to it before the users notice the performance degradation.

I have monitoring tools, I'm monitoring long running queries - but this is a case of many queries that run inside 10ms changing to taking 5s, and that's not immediately noticeable, it barely even blips the performance monitoring CPU and memory-wise (apart from more frequent buffer cache dumps perhaps).

For any individual known query I can set up a baseline and an agent job that alerts me, or FREEPROCCACHEs its own plan when it gets a bad plan - but what is my best way to determine this for a busy server?

Should I be baselining run stats for all queries and checking the query stats for all?  Can BlitzCache help me out?  Will this sort of checking be too intrusive on the performance of the server?

Instead of developing a solution yourself you could consider upgrading to SQL Server 2017 and trying out the automatic tuning feature. Reproducing part of the documentation:

  Database Engine automatically detects any potential plan choice
  regression including the plan that should be used instead of the wrong
  plan. When the Database Engine applies the last known good plan, it
  automatically monitors the performance of the forced plan. If the
  forced plan is not better than the regressed plan, the new plan will
  be unforced and the Database Engine will compile a new plan. If
  Database Engine verifies that the forced plan is better than regressed
  one, the forced plan will be retained until a recompile (for example,
  on next statistics or schema change) if it is better than the
  regressed plan.

  Note: Any plans auto forced do not persist on a restart of the SQL
  Server instance.

This could make it easier to meet the "react to it before the users notice the performance degradation" option.


Using an enum index to limit records A common issue, just not sure on the keywords.

I have a table of assessments (14k+ records).

Each assessment can be in many different states ('draft', 'proofing', 'amends', etc... and 'done').

Most records (13k) are 'done'... so doing a:

WHERE state != 'done'

It should ignore most records, however MySQL ignores the index (presumably due to the low cardinality).

I could USE/FORCE INDEX, which kind of helps, but was wondering if this is the best approach.

One option is to add a datetime for when it is done, but not really used at the moment... but would have high cardinality (with NULL for those 1k records).

While similar queries with = or < or <= will consider (and depending on cardinality estimates will use) an index on (state), a query with a condition of this type will not use an index.:

WHERE state <> 'done'

Things you can do instead:

write the condition as:

WHERE (state < 'done' OR state > 'done')

use UNION:

SELECT a.*
FROM assessments AS a
WHERE state < 'done'

UNION ALL

SELECT a.*
FROM assessments AS a
WHERE state > 'done' ;

write the condition with IN / OR:

WHERE state IN  ('draft', 'proofing', 'amends', ...)    -- everything except 'done'

WHERE (state = 'draft' OR state = 'proofing' OR state = 'amends' OR ...)


Do I need to execute "create extension pgcrypto" everytime? I have used crypt() in one of my functions to hash user password. But what I observed the next day I started my pc that the same thing failed as cryptographic functions became unavailable upon restart. It showed message like functions gen_salt() / crypt() is unknown or not defined. I executed create extension pgcrypto; once and it started working.

How to handle this in prod environment? Is there a way to make this module permanently available in db even if db is restarted?

Or do I have to instruct server manager to run this command in case they required to restart db server?

Or can I write this statement in functions itself so that it will create the extension every time before using cryptographic functions?

Moreover, we are using a read-replica of db, where it did not allowed to execute this "create extension" statement as there is read permission only. So how to make this cryptographic functions available in such read-only environment?

pgcrypto is a regular Postgres extension. You have to install it once per database (not per DB cluster like you may have been assuming) to use it. Installation is permanent, surviving DB restarts.

If you want every new database in the same DB cluster to have it pre-installed, you can install it to the default template database template1 by connecting to it and installing it normally. Or install it in any other database and use that as template with:

CREATE DATABASE new_db TEMPLATE my_template;

See:

Creating UUID extension as the non super privileged user

One other thing that can go wrong: the search_path. See:

Is it recommended to install extensions into pg_catalog schema?
How does the search_path influence identifier resolution and the “current schema”

But if that was your problem you'd get warnings when trying to install the same extension a second time.

The only other exotic explanation I can think of: your DB might have been restored to a previous state. But you would certainly know of that, wouldn't you?

Or did you just connect to a different database by mistake? The "read replica" you mentioned?

  how to make this cryptographic functions available in such read-only environment?

If it's not replicated by your replication solution, you have to install it manually once.


If an account has REQUIRE SUBJECT, does it still need a password? I'm in the process of setting up SSL-secured replication between two servers.  Each server has its own public/private keypair, and the CA cert is just the concatenation of the two public certs, like this answer.

Now I'm updating the replication account with REQUIRE SUBJECT "exact subject of the client"

Is there any practical value to also having a password on the replication account (IDENTIFIED BY "secret")?

When a user account has REQUIRE SUBJECT, it simply places the restriction on connection attempts that the client must present a valid X509 certificate containing the subject subject. If the client presents a certificate that is valid but has a different subject, the server rejects the connection.

Note that, in the MySQL Documentation, a sample user was created with both:

GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
  IDENTIFIED BY 'goodsecret'
  REQUIRE SUBJECT '/C=EE/ST=Some-State/L=Tallinn/
    O=MySQL demo client certificate/
    CN=Tonu Samuel/emailAddress=tonu@example.com';

PCI and HIPAA would shriek "INSECURE" if a root user had no password, SSL or no SSL. This would be the case even more so with a Replication User.

Therefore, what is the practical value? : Having a password would provide another level security.


Why parameter for one query returning the same # of results as a different parameter, is extremely disproportionately slower I am an application developer that has inherited a SQL Database from a previous developer. There is one view that is causing an issue. This view selects a bunch of different columns from different tables and contains 6 different inner joins. I am running a query on this view like so...

SELECT * FROM dbo.View where UserID = " + employeeId + "

When I run this query on all of the different employees it returns fine within a few seconds. For one employee however, It takes up to a minute or two even when the result set is nearly the same in terms of quantity and times out in the API. I have narrowed the cause to one of the joined tables which makes sense because this joined table has the most rows and makes a lot of connections. I don't know how to diagnose the problem any further.

What I have tried:

creating an identical table as the table that is causing the issue, copying over the same data, but with a new clustered index. The clustered index column I changed to is the column that is being used in the joins by the view. This didn't help. Is there something I need to do "run the cluster and sort" or something like that?

How would one of you move forward with further diagnosing?

Look at the execution plans.

SQL Server will build different execution plans based on the different number of rows that it expects to return. (Even though the ACTUAL number of rows that may be returned could be the same, the ACTUALS could be different.)

To get the actual execution plans in SSMS, click Query, Include Actual Plan. Then you can compare the different plans to see if they have different shapes.

If you're using SQL Server Management Studio 2016, you can use the Compare Showplan feature to make that easier. (It works even for querying 2008 boxes.)

If you can share the execution plans publicly, use PasteThePlan.com to upload the two different execution plans, and then folks can point out what's different between them. (Be aware that this tool is completely public - everyone will see the full text of your query, and your parameters.)


Slow DELETEs of LOB data in SQL Server I have a tables for logging, and a stored procedure to purge old data that has very slow DELETE performance, beyond what makes sense to me. I am looking for how to modify the tables or DELETE statements to perform reasonably well on LOB data. Alternatively, if there is an acknowledgement from Microsoft of the problem -- something like "we have fixed this with SQL server version x", or even "we see this performs poorly, but it is not a priority" -- that would work also.

This is running on Microsoft SQL Server 2012 (SP3). The below is substantially my actual table and code, just slightly simplified:

CREATE TABLE [LOG_VALUE](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [VALUE] [varchar](max) NOT NULL,
 [CHECKSUM] [int] NOT NULL,
 [VALUE_LEN] [int] NOT NULL,
 CONSTRAINT [PK_LOG_REQUEST] PRIMARY KEY CLUSTERED ([ID] ASC)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

The deletes in question are done with this:

WHILE (@@ROWCOUNT > 0)
  DELETE TOP (100) [LOG_VALUE]
  OUTPUT DELETED.[VALUE_LEN] INTO @DELETED_ROWS
  WHERE [ID] IN (SELECT [ID] FROM @DELETE_IDS);

The basic stored procedure process is:

remove the values that reference the rows to be purged
select the [ID] values from the LOB table where NOT EXISTS in the above log table, into table variable @DELETE_IDS
delete top 100 rows at a time (to reduce/prevent contention/locking)

It definitely appears that the LOB data must be loaded into memory on the server in order to be deleted. Support: 1) when I insert 12 thousand rows into the table, it can be nearly instantaneous 2) retrieving 12 thousand rows is nearly instantaneous 3) once they are no longer in cache, both selecting and deleting the same 12 thousand rows takes ~40 seconds.

The most related question I could find is at:
https://serverfault.com/questions/241893/delete-performance-for-lob-data-in-sql-server
from 2011, but sadly has no satisfactory answer. I'm just hoping that asking this on dba.stackexchange.com will find a more knowledgeable audience :). That question's author definitely knew more about this than me, but as I read it the central points are:

when deleting rows, SQL server normally just marks them as deleted, and they are actually cleaned up by a "Ghost Cleanup Task" later on
when deleting LOB data, eXclusive locks are placed on all of the LOB data pages during the delete, and the pages are deallocated
when the pages are not in the buffer cache, these eXclusive locks wait for the pages to be loaded into memory
these pages being deallocated happens "up-front", not in a cleanup task. This means that the delete operation always waits on the data to be loaded before the statement finishes, unlocking the relevant rows

It seems like this deallocation should happen in a clean-up task, and not up-front. And since the LOB data is not referenced in the predicate (or OUTPUT), there should be no need to load it into the cache at all.

More information which may be relevant, but I don't think it is:

the values are often over 100KB, but vary significantly in size
the other table that points to the [LOG_VALUE].[ID] is not enforced with  Referential Integrity
the @DELETED_ROWS table is accumulated to note, at the end of the stored procedure, how many rows and bytes were deleted
there are currently 1.1 million rows, and by the value of the IDENTITY, there were only ever 1.6 million
There are no triggers
Currently, Ghost Record Count = 0
READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION are both OFF.

The whole database (and others on the same server) perform as I would expect in all cases except deleting/updating LOB data.

...once they are no longer in cache, both selecting and deleting the same 12 thousand rows takes ~40 seconds.

This seems to indicate that the storage subsystem is inadequate. If this is the cause, SQL Server will probably be waiting with one of the PAGEIOLATCH_XX wait types.

  It definitely appears that the LOB data must be loaded into memory on the server in order to be deleted.

As Paul Randal is reported to have said (update 2, in the question you linked to), SQL Server must traverse the LOB tree in order to locate the pages that must be removed. To do this, SQL Server needs to bring these LOB tree pages into memory. There really is no way around this; the information is just not available in any other way.

  It seems like this deallocation should happen in a clean-up task, and not up-front.

The way this works is not very well documented.

In my tests, SQL Server deletes the LOB data immediately if there is no special reason to keep the row around. This seems to be an optimization: after all, if we are already touching the LOB data, we might as well delete it while we are there. Deferring it to ghost cleanup (GC) would only add overhead, since GC would also need to traverse the chain. In addition, logging is reduced, since SQL Server only has to record the deallocation, not the complete LOB content.

The LOB delete operation is deferred to GC if (for example) there is a trigger on the table, or if a row-versioning isolation level is enabled. Both these features use row versions, which must be maintained on the LOB rows. In these cases, the LOB delete is fully logged. When GC runs later on, it performs the deallocation of the LOB pages. GC can be temporarily disabled with (documented) global trace flag 661, to see these ghosted LOB records if you want to test it yourself.

Given all that, it is hard to see how (fully) logging the delete and deferring a repeat of much of the same work already done to the GC would help you. It is likely faster to log less and remove the LOB data during the delete.

There's not enough in the question to speculate much about the precise cause of your poor delete performance, beyond the possibility of an overwhelmed/underspecified storage system, but I can say that in general it is best to:

Avoid accessing LOB data under read uncommitted isolation
Avoid having triggers on tables with LOB data when performing deletes
Be aware of the side-effects of row versioning
Ensure the storage subsystem is adequate for logging and general I/O
Run the latest build of SQL Server (currently 11.00.6523 for SQL Server 2012)

For more information about point 1, see Performance bug: NOLOCK scans involving off-row LOB data by Paul Randal. For more on points 2 and 3, see Deletes that Split Pages and Forwarded Ghosts (by me).

It could equally be that the simplified example misses a crucial detail, or the table variables produce an inefficient execution plan, or your table has billions of ghost LOB records already. A detailed analysis would probably require access to the system itself.


What is the difference between Oracle clusterware and Real Application Clusters? What is the difference between Oracle clusterware and Real Application Clusters? If we have 2 servers and want to join them into a cluster why there are two separate software products?

Oracle Clusterware is the clustering software that is a component of an Oracle RAC setup. Rather than being seen as a separate software product, Clusterware is part of the software stack (which includes the OS, ASM, Oracle RDBMS) that makes up an Oracle RAC cluster/deployment. Oracle support other clustering products in a RAC environment on some operating systems.


I don't claim to understand what you are doing, but your logic for p_i is wrong. To get your wanted result:

SELECT
    name, date_hr, AVG(nor), AVG(p_i)
FROM
    (SELECT
        name,
            CASE
                WHEN TIME(date_hour) > '12:00:00' THEN DATE(date_hour) + INTERVAL 1 DAY
                ELSE DATE(date_hour)
            END AS date_hr,
            nor,
            validation,
            CASE
                WHEN validation = 1 AND p_i > 0 THEN NULL
                ELSE p_i
            END AS p_i
    FROM
        report) z1
GROUP BY name , date_hr;

name | date_hr    | AVG(nor) | AVG(p_i)
:--- | :--------- | -------: | -------:
SYN  | 2018-02-03 |     18.5 |     null
SYN  | 2018-02-04 |       10 |        3
SYN  | 2020-04-07 |     14.5 |      3.5
YRT  | 2020-04-06 |      136 |     null

db<>fiddle here


repmgr 3.0.4 and postgres 9.4 on ubuntu (ERROR: replication slot "repmgr_slot_2" does not exist) I'm not a DBA but I have been tasked with setting up a database and streaming replication and repmgr is the tool I have decided to use.

The master goes on fine and I can see it registered in the cluster.

The error on the slave is as follows

[2016-07-22 15:39:35] [NOTICE] using configuration file "/etc/repmgr/repmgr.conf"
[2016-07-22 15:39:35] [NOTICE] destination directory '/var/lib/postgresql/9.4/main' provided
[2016-07-22 15:39:36] [ERROR] aborting, remote host xx.xxx.xxx.xxx is not reachable.
[2016-07-22 15:39:36] [ERROR] unable to drop replication slot "repmgr_slot_2":
ERROR:  replication slot "repmgr_slot_2" does not exist

[2016-07-22 15:39:36] [ERROR] unable to take a base backup of the master server
[2016-07-22 15:39:36] [WARNING] destination directory (/var/lib/postgresql/9.4/main) may need to be cleaned up manually

The command casuiging the abover error is

repmgr -f /etc/repmgr/repmgr.conf --force --rsync-only -h dbhost1 -d repmgr -U repmgr --verbose standby clone

The config file is as follows

cluster=test
node=2
node_name=db-002
use_replication_slots=1
conninfo='host=dbhost2 user=repmgr dbname=repmgr'
pg_bindir=/usr/lib/postgresql/9.4/bin

I have tried loging into the dbhost1/masrer from dbhost2/slave using

psql -h xx.xxx.xxx.xxx -p xxxx -d repmgr -U repmgr

and it works just fine so the pg_hba file in dbhost1/master is configured correctly using trust for authentication.

OK, I found the issue. I hadn't properly configured passwordless ssh login between my master and slave. So despite being able to login into my master from my slave the issue was it couldn't copy files because ssh wold require a password.

My only issue is error reporting is horrible for repmgr.

Following this guide I missed a very small step. Always make sure you test the login of ssh between both master and slave for passwordless login to ensure it was setup correctly.


Postgresql Union: Do not repeat based on 1 column only UNION filters out duplicate entries while UNION ALL keeps duplicate. Similar to that scenario, Consider this anological case, I want to set union to check only single column duplicacy.
SELECT id, 1 AS category FROM users UNION SELECT id, 2 FROM users_2;

Here I want to reject all UNION entries from id, 2 and check duplicacy based only on id.
OUTCOME:
id   |   category
------------------
100  |    1
101  |    1
...  |    ...
100  |    2   # Skip this as 100 is already present
201  |    2
....

EXPECTED:
id   | category
----------------
100  |   1
101  |   1
...  |  ...
201  |   2

full outer join is very handy for this as follows:
select id, case when users.id is null then 2 else 1 end as category
from users
full outer join users_2 using (id);

How this works is that the id column will be null if the row comes from users while it won't be null when it's coming from users_2.
Alternatively you can do it with union (and use union all because we're already using not exists to discard duplicates):
select id, 1 as category
from users
union all
select id, 2
from users_2 where not exists (select id from users where id = users_2.id);

but as you can see the query becomes much longer.
A third way that is even longer and probably even less efficient is to use distinct on:
select distinct on (id) id, category
from (
  select id, 1 as category
  from users
  union all
  select id, 2
  from users_2
) x
order by id, category;

NB This last query does guarantee the order in the output; the first two queries don't!
See https://dbfiddle.uk/?rdbms=postgres_13&fiddle=dd8bdbc82edc26cce9e8ba9fdde84517


SQL Server 2012 Exclude a value I'm teaching myself SQL, and I ran into a scenario that I cannot seem to solve. Please any help.

I have a table called tblRecord with a column called BuyerID, in this case there are two BuyerIDS, and I have another table called tblOffer that has a column that is called InsertedID.

What I'm trying to do is if the two declared BuyerID from tblRecord are not the same as tbloffer in insertedID, then exclude that value.

I'm using SQL Server 2012 Management Studio.

SELECT
    COUNT(DISTINCT r.RecordID) AS Lines,
    COUNT(r.RecordID) AS Offers,
    MONTH(r1.Submitdate) AS M,
    DAY(r1.submitdate) AS D
FROM
    tblReq r1
INNER JOIN
    tblRecord r ON r1.ReqID = r.ReqID
INNER JOIN
    tblOffer o ON r.RecordID = o.RecordID
WHERE
    r.BuyerID IN (36, 160499)
    AND o.OfferTypeID IN (1, 3)
    AND r1.Submitted = 1
    AND r1.SubmitDate >= '2015-07-01'
GROUP BY
    MONTH(r1.Submitdate), DAY(r1.submitdate);

If my understanding of your question is correct and you are only wanting to return records from tbloffer if there are also records in tblRecord matching those buyer ids, then you should be able to add a clause within your join so that it will only return those records.

I think this will cover it for you.

SELECT  COUNT(DISTINCT r.RecordID) AS Lines
      , COUNT(r.RecordID) AS Offers
      , MONTH(r1.Submitdate) AS M
      , DAY(r1.submitdate) AS D
FROM    tblReq r1
        INNER JOIN tblRecord r
            ON r1.ReqID = r.ReqID
        INNER JOIN tblOffer o
            ON r.RecordID = o.RecordID
            AND r.BuyerID = o.InsertedID
WHERE   r.BuyerID IN (36, 160499)
        AND o.OfferTypeID IN (1, 3)
        AND r1.Submitted = 1
        AND r1.SubmitDate >= '2015-07-01'
GROUP BY MONTH(r1.Submitdate)
      , DAY(r1.submitdate);

Hopefully I am understanding your question correctly and this is able to help you.


Postgres Function/Trigger Avg I am trying to create a function that calculates the moving average for an item. What I have is a table of "item" and then each user can "vote" which produces one vote, with a score, user_id and item_id.

I want to calculate an AVG, which is stored in item_id after every insert and is calculated based on number of vote's (selected by vote.item_id) and averages the score using vote.score.

this is the idea of the query.. this works, but stores the moving average int he last column of vote and is not usable, but I am close!!

select vote.score as single_vote_score, vote.user_id, vote.item_id, items.score as item_score,
    AVG(vote.score)
        OVER(ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM vote
    LEFT JOIN items on vote.item_id = items.id
WHERE vote.item_id = 10;

This is the function

CREATE FUNCTION calculate_item_score() RETURNS TRIGGER AS $calculates_items_score_by_average$
    BEGIN UPDATE "item"
        SET "score" = AVG("vote"."score")
        FROM "vote"
        WHERE "item"."id"="vote"."item_id";
        RETURN NEW;
    END;
$calculates_items_score_by_average$
LANGUAGE plpgsql;

This trigger should run every time a vote is inserted!

CREATE TRIGGER "update_item_score"
AFTER INSERT ON "vote"
FOR EACH ROW EXECUTE
PROCEDURE calculate_item_score();

-- this errors out
INSERT INTO vote (user_id, photo_id, score) VALUES ('129','10','50.0');

This is my error

ERROR:  aggregate functions are not allowed in UPDATE
LINE 1: UPDATE "photo" SET "score" = AVG("vote"."score") FROM "vote"...
                                 ^
QUERY:  UPDATE "item" SET "score" = AVG("vote"."score") FROM "vote" WHERE
"item"."id"="vote"."item_id"
CONTEXT:  PL/pgSQL function calculate_item_score() line 1 at SQL statement

BEGIN UPDATE "item"
    SET "score" = (select AVG("vote"."score") FROM "vote")

Use it as follows:

create or replace function test (id int) returns void as
$$
begin
update employees
set commission_pct=(select min(commission_pct) from employees)
where employee_id= $1;
end
$$ LANGUAGE plpgsql VOLATILE;

select test(198); --call function

The process of aggregate functions is calculated after getting data set from data in table, we cannot use any calculating without selecting data from table first. In the above function is update column score* to AVG(score) but there is not data-set to calculate the AVG(score).


SQL Database Server Recovery and connections We have a server 2008 R2 with SQL 2008 instance.  The underlying hardware RAID is failing and we need to migrate the databases to a stable piece of hardware with the same hostname on the network quickly.  If we remove the existing server off the network to allow the new hardware to be joined to the domain with the same hostname will that allow us to reattach/restore the databases from the former server without having to change client based connection strings/ODBC?  If this fails, will restarting the old server and rejoing it to the domain mean that the databases/aplications still connect ok?

I ask this because I do not fully understand how the clients connect to the instance...does it just use DNS and connect with the server hostname and instance name or does it use the underlying SID to identify what to connect to?

Apologies for my ignorance.

Yes, your plan will work.  There are no hidden dependencies that will cause connections to fail.  The client doesn't know anything about the SIDs of the server or server logins.  Whatever the client might learn about the server is not retained from one session to the next.

If you want a bit of a smoother transition, you can run a script on the old server to create scripts that will create the logins on the new server with the same SIDs as the old server.  If you do this, you won't have to remap the database users to the server logins after restoring the databases.  See Sync users and logins.