Table partition existing table where partition key is not part of the primary key Main objective: Add partitioning to table to make deletion of old orders non-blocking/quicker (and also understand partitioning)

I have an existing table Order, like this:

CREATE TABLE Order (
    OrderId INT,
    OrderDate Datetime,
    Quantity INT,
    CONSTRAINT [PK_OrderId] PRIMARY KEY CLUSTERED
(
    [OrderId] ASC
)
ON [PRIMARY];

This table contains 50 million rows from the last 10 years.
I only need the last 5 years data.

I have a partition function like this:

CREATE PARTITION FUNCTION OrderPF (datetime)
AS RANGE RIGHT FOR VALUES ('2014-01-01')

I have a partition scheme like this:

CREATE PARTITION SCHEME OrderPS
AS PARTITION OrderPF ALL TO ([PRIMARY])

My question is how to proceed?
I still want a primary key on the table.

Does the [OrderDate] column have to be a part of the clustered index? (Main question)

CREATE UNIQUE CLUSTERED INDEX IX_Order ON Order(OrderDate,OrderId) ON OrderPS(OrderDate) ;

If so, do I then have to create an extra non-clustered Primary Key purely on [OrderId]?

ALTER TABLE Order ADD CONSTRAINT PK_OrderId PRIMARY KEY NONCLUSTERED (Id) ON [PRIMARY];

Is this the correct approach?

If OrderId is monotonically increasing, you can partition on that.  Then you can truncate old partitions having no data you need to retain.  Something like:

create partition function pf_OrderId(int)
as range right for values (0,1000000,2000000,3000000,4000000,5000000,6000000,7000000,8000000,9000000)

create partition scheme ps_OrderId
as partition pf_OrderId all to ([Primary])

go

CREATE TABLE [Order] (
    OrderId INT,
    OrderDate Datetime,
    Quantity INT,
    constraint [PK_OrderId] primary key clustered (OrderId)
)
ON ps_OrderId(OrderId)

go

--then you can examine the max OrderDate in each partition when trimming old data
select p.partition_number,
       (select max(OrderDate) MaxOrderDate
        from [Order]
        where $PARTITION.pf_OrderId(OrderId) = p.partition_number) MaxOrderDate
from sys.partitions p
where p.object_id = object_id('Order')
and p.index_id = 1

And of course you can adjust the granularity of your partitions to roughly align to your data retention requirements.  And if you have a hard requirement to purge old data then you would truncate N partitions and run a DELETE on at most one partition.  And you can always split the partition function to insert a partition boundary at important times, like overnight at the beginning of a year or quarter.

To move an existing table to a partition scheme, you drop all the indexes and the clustered primary key constraint, and recreate them on the new partition scheme.  Once you create the clustered index on the partition scheme, subsequently-created indexes will go there by default. If you don't drop the non-clustered indexes first, they will be rebuilt when you drop the clustered PK, and rebuilt again when you recreate it, and they still won't be partitioned. EG

CREATE TABLE [Order] (
    OrderId INT,
    OrderDate Datetime,
    Quantity INT,
    constraint [PK_OrderId] primary key clustered (OrderId),
    index ix_Order_Orderdate (OrderDate)
)

go

create partition function pf_OrderId(int)
as range right for values (0,1000000,2000000,3000000,4000000,5000000,6000000,7000000,8000000,9000000)

create partition scheme ps_OrderId
as partition pf_OrderId all to ([Primary])

go

drop index ix_Order_Orderdate on [Order]

alter table [Order]
drop constraint [PK_OrderId]

alter table [Order]
add constraint [PK_OrderId] primary key clustered (OrderId)
on ps_OrderId(OrderId)

create index ix_Order_Orderdate on [Order](OrderDate)

Then verify that both the clustered and non-clustered indexes are partitioned:

select i.name index_name, p.partition_number
from sys.partitions p
join sys.indexes i
 on p.object_id = i.object_id
 and p.index_id = i.index_id
where p.object_id = object_id('Order')


SQL - use variable as column in SELECT statement and WHERE clause Can any one please support me with below problem

I want to use Variable column name in Select Statement.
But when I give variable - its giving out put of Column Name itself

Note : My data in column is INT but Column Heading is Varchar.

Please refer attached Image for Code & Results ( Expected & Current )

Code :

Alter Procedure dbo.dayplanv00
(
@dt as varchar(255) = '[15-04-2017]',
@fg as nvarchar(255)= 'XC 1 IEC'
)
as
begin
Select [15-04-2017] as dt from RollingPlanWK15 where FNGD = 'XC 1 IEC'
END

Execute dbo.dayplanv00

You need to use dynamic sql to use variables as column names, try:

ALTER PROCEDURE dbo.dayplanv00 (
    @dt nvarchar(255) = '[15-04-2017]'
    ,@fg nvarchar(255)= 'XC 1 IEC'
)
AS
BEGIN
    DECLARE @sql nvarchar(200)= 'Select ' + @dt + ' as dt from RollingPlanWK15 where FNGD = ''' + @fg + ''''
    EXEC sp_executesql @sql
END

Note, are you wanting those default values in there? would make more sense (in my opinion) to have it as a required field on the execution of the proc so you don't accidentally run it with no values and presume its right


Where does InnoDB store transaction data before committing it? I've done some tests using READ_COMMITTED and READ_UNCOMMITTED at home, using the JDBC technology.
I see that READ_UNCOMMITTED can actually read uncommitted data, e.g. data from some transaction not yet committed (could perform an UPDATE-query).
Questions

Where is uncommitted data stored, such that a READ_UNCOMMITTED transaction can read uncommitted data from another transaction?
Why isn't it possible for a READ_COMMITTED transaction to read uncommitted data, i.e. performing a "dirty read"? What mechanism enforces this restriction?

"Where is uncommitted data stored, such that a READ_UNCOMMITTED transaction can read uncommitted data from another transaction?"

The new uncommitted record (clustered PK) versions are treated as the "current" version of the record on page. So they can be stored in the buffer pool and/or in the tablespace (e.g. tablename.ibd). Transactions that then need to build a snapshot/view in anything other than READ-UNCOMMITTED, need to construct a previous version of the row (following the history list) using the UNDO records (stored in the system tablespace). When reading the uncommitted record, InnoDB may also need to read some uncommitted secondary index records from the Change Buffer and apply them before presenting the record back to the user.

It's this behavior that can make rollbacks in InnoDB relatively expensive. It's the big factor that can also lead to potential performance issues from long running idle transactions that are holding updated records, as those transactions will block purge operations and the history list of old record versions grows, and the UNDO records needed to rebuild those old versions on-demand, will continue to grow. It slows down new transactions that need to read an older/committed version of the record, as they need to traverse a longer and longer history list--which is a singly linked list of UNDO records--and do more work in order to reconstruct the old version of the record. So you end up using a lot of CPU cycles (not to mention internal locking primitives: mutexes, rw_locks, semaphores, etc.) on non-user visible work that slows down query processing.

Hopefully that makes sense? :)

As an FYI, in MySQL 5.7 you can move the UNDO tablespace and logs out of the system tablespace, and have them automatically truncated. They can grow quite large if you have a long running transaction that prevents purge operations, resulting in a very long and ever growing history list length. Having them stored in the system tablespace was the single most common cause of a huge/growing ibdata1 file, which in turn cannot be truncated/shrunk/vacuumed in order to later reclaim that space.


How do I prove that the act of removing foreign keys doesn't corrupt existing data? TL;DR: Prove that in practice, the execution of the alter table table_name drop foreign key constraint_name statement does not corrupt existing data. The important consideration is the execution of the statement itself; consider data changes after the fact irrelevant. (By analogy: opening the stable door is the harmful action, not the horse bolting.)

My boss and I are having a difference of opinion about whether or not to use foreign keys in a MySQL/InnoDB database. I'm for using them for enforcing RI and taking advantage of ON DELETE CASCADE and ON UPDATE RESTRICT/SET NULL, while he's against (confident that he can enforce RI at the application level).

His argument is chiefly:

Drupal doesn't use them and gets along fine without them, so why should we?
They're too inflexible when you need to change data and/or structure.
He's removed them from existing tables to change things and it's caused data corruption that was only noticeable weeks or months later, on high-traffic/ high activity sites, so he'd rather not use them.

My arguments are chiefly:

Not all of the databases/ DB engines supported by Drupal 5-7 (MyISAM, SQLite 3) enforce FKs by default, so Drupal leaves them as documented only, whereas this might be different in D8.
Yes, they can be a pain to deal with, but perhaps the fault lies with poor planning/designing on the part of the developer, not the FKs.
Surely not enforcing RI with FKs at the DBMS level is more likely to cause data corruption than otherwise. (Case in point is D6's user reference module not restricting changing user status when existing content references a user that must have a certain status).
It's a waste of time and resources to make code do/attempt what the DBMS already does. How can he guarantee that his code will work as well as (or better than) the DBMS?

Essentially, he'll come over to my way of thinking if I can prove that the act of removing a foreign key itself (regardless of subsequent data inserts/updates) won't corrupt existing data. I can't see a way clear to prove their usefulness/advantage because I'm not sure how I would satisfactorily observe/document the effect a drop foreign key would have immediately after execution other than comparing data from before execution to data after execution.

So, how do I persuade my boss that we should use foreign keys by proving that the act of later removal/alteration of them won't corrupt existing data, whereas using them will not cause any issues? How would I best set up practical usage tests?

Note: I don't so much want to prove that I am right (from an egotistical point of view) to use FKs, but that it benefits the data and the application code more to use them at the DBMS level than at the application code level.

Implementing this stuff at an app level is a nightmare. You and your team will have to test, double check and retest code which does EXACTLY the same thing that's been done by MySQL (for InnoDB) for MILLIONS of users over a period of YEARS.

Follow the discussion (one of the best threads I've seen on stackoverflow) here. With all due respect to you and your team, does your boss REALLY think that you can write bug-free RI (Referential Integrity) code that has significant functionality in less than 5 years? I certainly don't!

I can't tell you the number of times I've read on other forums (primarily Oracle) where some poor schmuck is crying his eyes out over an app he's inherited where RI was enforced at the app layer. Orphaned records, childless parents, inconsistent data...  the list goes on and on... data might as well be Swiss cheese, it's got that many holes! Tell your boss to have a read of this book by database professionals (Oracle experts, some of the best, most readable technical writing I've ever seen). From a review here - an outline of what Jonathan Lewis (a man who wrote a 530 page book on only (get this) the FUNDAMENTALS of the Oracle optimiser.

  Chapter 10: Design Disasters, by Jonathan Lewis

  More war stories, for fans of Chapter 8! "Now prepare yourself to read
  all about 'The World's Worst Oracle Project.'" - Jonathan Lewis.

  This chapter describes some of the most common mistakes in development
  Oracle database applications. You'll certainly recognise some of them,
  because so many people stubbornly cling to certain beliefs. I know I
  like to bring up several of his points when I get into common
  arguments like these:
  1. We want our application to be "Database Independent."
  2. We will check data integrity at the application level instead of taking advantage of Oracle's constraint checking abilities.
  3. We want to use sequences for our primary keys.

Take CAREFUL note of point 2! If your boss persists with this madness, then my advice to you is to run fast and run far! You will spend your days in a miserable hellhole of constant firefighting, and never be able to fulfill your potential as a developer or a DBA and you'll learn very little! Just a few thoughts!


Migrate a big database from Oracle 11g to 19c I have a big and customized db on Oracle 11g with many store procedures, jobs, triggers, materialised views and data.
We need to migrate all data, structures, etc on Oracle 19c.
Do you know if there are incompatibilities or Oracle 19c is completely compatible with 11g syntax and structures?
Thanks

please look into this Oracle documentation about the upgrades, if you are using 11.2.0.4 then you  should be fine.
https://www.oracle.com/a/tech/docs/twp-upgrade-oracle-database-19c.pdf


Select data from a table and update a second table using most recent value I have two table: Stampings and Registries.

Stampings is populate from a program that read a CSV file and do an insert into the table. Some field into the table are: name, surname, card-code and employee-freshman.

Registries contains name, surname, card-code and employee-freshman.

Now, I need a control that from Stampings select name, surname, card-code and employee-freshman and comparate with data present into Registries using card-code like unique field.

If value from Stampings and Registries not match I want to update the record in Registries with the new value using date like where cause. The update must do taking the last inserted record into Stampings.

This because a card can be from an user, but at any time it could be transferred to another person.

Can someone help me?

This is my schema:

Stampings
(
ID INT
Date DATE
Hour VARCHAR
Card-Code VARCHAR
Name VARCHAR
Surname VARCHAR
employee-freshman VARCHAR
);

Registries
(
ID INT,
Card-code VARCHAR
Name VARCHAR
Surname VARCHAR
employee-freshman VARCHAR
);

I used C# tag because here below I report the code I'm using.

Tell me if you think that logic is correct to approach.

using (SqlCommand selezionaCodiciTessera = new SqlCommand("SELECT Stampings.Card-code, Stampings.Surname, Stampings.Name, Stampings.employee-freshman FROM Stampings INNER JOIN Registries ON Stampings.Card-code = Registries.Card-code WHERE Stampings.Date = (SELECT MAX(Date) FROM Stampings WHERE Stampings.Card-code = Registries.Card-code) AND Stampings.Hour = (SELECT MAX(Hour) FROM Stampings WHERE Stampings.Card-code = Registries.Card-code) GROUP BY Stampings.Card-code, Stampings.Surname, Stampings.Name, Stampings.employee-freshman ORDER BY Stampings.Card-code;", connessione))
{
    SqlDataReader reader = selezionaCodiciTessera.ExecuteReader();

    while (reader.Read())
    {
        using (SqlConnection connessione2 = new SqlConnection(connString))
        {
            connessione2.Open();

            string query = "";

            if (reader[1].ToString().Contains("'"))
            {
                string modifica = reader[1].ToString().Replace("'", "''");

                query = "UPDATE Registries SET Surname = '" + modifica + "', Name = '" + reader[2].ToString() + "', employee-freshman = '" + reader[3].ToString() + "' WHERE Card-code = '" + reader[0].ToString() + "';";

                using (SqlCommand aggiornaOinsersci = new SqlCommand(query, connessione2))
                {
                    aggiornaOinsersci.CommandTimeout = 180;

                    try
                    {
                        aggiornaOinsersci.ExecuteNonQuery();
                    }
                    catch (SqlException e)
                    {
                        LOG("Timeout nell'esecuzione della query. " + e.ToString());
                    }
                }
            }
        }
    }
}

If I understand correctly, you are trying to update Registries table with the newest row of Stampings table, replacing single quotes ' (if exists) by a pair of single quotes ''.

As I pointed out in my comments you can get it by using a single SQL command, that you can execute on SSMS or by using a C# program.

I've set up a fiddle with your table schema (or similar because you have not provided columns size):

CREATE TABLE Stampings
(
    ID INT,
    Date DATE,
    Hour VARCHAR(5),
    CardCode VARCHAR(3),
    Name VARCHAR(10),
    Surname VARCHAR(20),
    EmployeeFreshman VARCHAR(10)
);

CREATE TABLE Registries
(
    ID INT,
    CardCode VARCHAR(3),
    Name VARCHAR(10),
    Surname VARCHAR(20),
    EmployeeFreshman VARCHAR(10)
);

INSERT INTO Registries VALUES
(1, 'AAA', 'Name 1', 'Surname 1', 'A'),
(2, 'BBB', 'Name 2', 'Surname 2', 'B'),
(3, 'CCC', 'Name 3', 'Surname 3', 'C'),
(4, 'DDD', 'Name 4', 'Surname 4', 'D');

INSERT INTO Stampings VALUES
(1, '20180101', '02:05', 'AAA', 'AAA Name', 'AA1 ''Surname', 'E'),
(2, '20180101', '05:05', 'AAA', 'AAA Name', 'AA2 ''Surname', 'E'),
(3, '20180101', '12:05', 'AAA', 'AAA Name', 'AA3 ''Surname', 'E'),
(4, '20180102', '12:05', 'BBB', 'BBB Name', 'BB4 Surname', 'F'),
(5, '20180102', '15:05', 'BBB', 'BBB Name', 'BB5 Surname', 'F'),
(6, '20180103', '11:05', 'CCC', 'CCC Name', 'CC6 ''Surname', 'G'),
(7, '20180103', '12:05', 'CCC', 'CCC Name', 'CC7 ''Surname', 'G'),
(8, '20180103', '13:05', 'CCC', 'CCC Name', 'CC8 ''Surname', 'G');

Now I'm using a single command to update your data in this way:

UPDATE r
SET    Surname = REPLACE(s.Surname, '''', ''''''),
       Name = s.Name,
       EmployeeFreshman = s.EmployeeFreshman
FROM   Registries r
CROSS APPLY (SELECT TOP 1
                    CardCode,
                    Surname,
                    Name,
                    EmployeeFreshman, Date, Hour
             FROM   Stampings
             WHERE  CardCode = r.CardCode
             AND    Surname LIKE '%''%'
             ORDER BY Date DESC, Hour DESC) s;

CROSS APPLY join returns the newest Stampings inserted row of each Registry,  and REPLACE(s.Surname, '''', '''''') do the trick with the Surname

This is the final result:

SELECT * FROM Registries;
GO

ID | CardCode | Name     | Surname       | EmployeeFreshman
-: | :------- | :------- | :------------ | :---------------
 1 | AAA      | AAA Name | AA3 ''Surname | E
 2 | BBB      | Name 2   | Surname 2     | B
 3 | CCC      | CCC Name | CC8 ''Surname | G
 4 | DDD      | Name 4   | Surname 4     | D

db<>fiddle here


How many rows were copied from table I want to write a script to backup a table using COPY.

psql "connection parameters" -c "COPY (SELECT * FROM tbl WHERE insertion_date > 'date') TO STDOUT WITH CSV HEADER;" | bzip2 -c > backup.csv.bz2

Now I want to log how many lines are copied to zip file? I want to calculate it while copying, not with another command.

Update: Since Postgres 9.3 plpgsql can access the number of rows processed by COPY directly:

Get the count of rows from a COPY command

I recently ran into the same problem. I tried with a plpgsql function, but ROW_COUNT is not set by COPY - in Postgres 9.2 or older.

You could just run two queries. First count() and then COPY. With simple queries that's probably the way to go. But with huge / complex queries this is a pain and can double the execution time.

I came up with a solution that uses a temporary table and counts the rows before executing COPY.

I adapted what I have for you so you can COPY TO STDOUT and pipe to bzip2, which is not possible from within a plpgsql function:

1. Create a function that takes an SQL string and creates a temporary table with it:

CREATE OR REPLACE FUNCTION f_copy_prep(_query text)
  RETURNS void AS
$func$
DECLARE
   ct integer;
BEGIN
   -- If you deal with huge results, set more RAM for temp tables locally:
   -- SET temp_buffers = 512MB';  -- example value

   EXECUTE 'CREATE TEMP TABLE cp_tmp ON COMMIT DROP AS (' || _query || ')';

   GET DIAGNOSTICS ct = ROW_COUNT;
   RAISE LOG 'My text here. rows: %', ct;

END
$func$  LANGUAGE plpgsql;
ALTER FUNCTION f_copy_prep(text) SET search_path=public,pg_temp;
REVOKE ALL ON FUNCTION f_copy_prep(text) FROM public;
GRANT EXECUTE ON FUNCTION f_copy_prep(text) TO ???;

Function executes dynamic SQL, so you can use it for any query.
This is inherently unsafe, so run it with the minimum rights necessary, revoke all rights from public and grant EXECUTE exclusively to a trusted user. Follow the instructions in the manual!
Create temporary table with ON COMMIT DROP, so it gets dropped automatically at the end of the transaction.
Get the row count with GET DIAGNOSTICS - ROW_COUNT is set by the SELECT statement in EXECUTE. Write it to the log - your requirement. No need for a separate count(*).

2. Call from shell-script to pipe output through bzip2

psql "connection parameters" \
-c "SELECT f_copy_prep('SELECT * FROM tbl WHERE insertion_date > ''date'''); \
    COPY cp_tmp TO STDOUT WITH CSV HEADER;" \
| bzip2 -c > backup.csv.bz2

Put two SQL commands into your -c argument or put complex queries in a file and use the -f parameter. All is executed in one transaction. Only the output of the last command is returned - fits our need. Careful with the syntax - multiple layers of interpretation (first shell, then Postgres).
First command is the above function with your query-string as parameter. Second is the COPY TO STDOUT.

I tested this with PostgreSQL 9.1 on Linux and it worked for me: data in the file, message with row-count in the log.


SQL Server 2008 R2 Transaction Counts Good Afternoon,
We have a pretty big SQL Box  32proc 128gb.

We had a change in our environment, not sure what exactly change,  db/code.  We're investigating that aspect as well.   One of the things we noticed from a baseline stats were the number of transactions.   It went from 10-20K to 60-80K.

SELECT name, transaction_type, COUNT(1)
FROM sys.dm_tran_active_transactions
WHERE name <> 'worktable'
GROUP BY name, transaction_type

Results:
name
implicit_transaction
INSERT
sort_init
UPDATE STATISTICS
UpdateQPStats
user_transaction
LobStorageProviderSession
SELECT
sort_fake_worktable
topn_fake_worktable
workfile
WorkFileGroup_fake_worktable

Some of the names are self explanatory, others not so much.  I have not been able to find any of these on the web.  Does anyone have an explanation or a link where I could find the answers?

Thanks,

Check the transaction log file.  Has it increased significantly.  Also check the full transaction by query.  It will give you more detail about what was actually running.

/* Query to check transaction count per query and session_id */

select er.session_id, er.request_id,
er.open_transaction_count, er.transaction_id,
at.name, at.transaction_begin_time,
REPLACE (REPLACE([text], char(13), ' '), CHAR(10), ' ') AS sql_text
from     sys.dm_exec_requests er
join     sys.dm_tran_active_transactions at
  on     er.transaction_id = at.transaction_id
join     sys.dm_exec_query_stats stat
  on     er.plan_handle = stat.plan_handle
CROSS
APPLY    sys.dm_exec_sql_text (stat.sql_handle)
GO

/* Check task allocation by query */

SELECT t1.session_id, t1.request_id, t1.task_alloc,
t1.task_dealloc, t2.statement_start_offset,
t2.statement_end_offset, REPLACE (REPLACE([text], char(13), ' '), CHAR(10), ' ') AS sql_text
FROM (Select session_id, request_id,
SUM(internal_objects_alloc_page_count) AS task_alloc,
SUM (internal_objects_dealloc_page_count) AS task_dealloc
FROM sys.dm_db_task_space_usage
GROUP BY session_id, request_id) AS t1,
        sys.dm_exec_requests AS t2
INNER JOIN sys.dm_exec_query_stats stat ON t2.plan_handle = stat.plan_handle
CROSS
APPLY  sys.dm_exec_sql_text (stat.sql_handle)
WHERE  t1.session_id = t2.session_id
  AND  (t1.request_id = t2.request_id)
GO


Does my associated ASM Instance have any play in Re-naming my SID? Greetings DBA Experts,

I have changed my Oracle SID in the past by updating the init.ora file, and updating
my environment (vars, tns, lintener, etc..), yet not since we moved to 11g with ASM.
I come from an app development team, and when I ask our system-level DBA's the best way to do this, they just reply with "Why would you want to do that?".
Is there anything extra or different I need to do in renaming a SID with respect to our ASM instance?
Thanks very much!

This blog post shows you the required steps. I'd think long & hard before doing so, though as recreating control files is not something to betaken lightly. You don't need to do anything with the +ASM instance.


Log Shipping - Secondary Database - Can CDC be enabled? I was reading this article about SQL Server log shipping and the statement was made that log shipping supports limited read-only access to secondary databases (during the interval between restore jobs).

The article doesn't go on to mention any other limitations, if any, which apply to the secondary database. Specifically, I would like to know if 1) CDC could be enabled on the secondary database; 2) Could triggers be applied to some tables on the secondary database.

Essentially I'm trying to determine, via T-SQL, for any given table, what rows were inserted, updated and/or deleted during the last restore job.

SQL Server 2014 is being used. Also note that CDC is not enabled on the primary and the company is adamant that it not be enabled for the primary. However, they would have no issues enabling it for the secondary, if it's possible.

For log shipping, yes you can put it in read only between restores with a standby file.

However you won’t be able alter the database regardless of whether you are thinking of CDC or triggers. Also if you think about how CDC works it was created based off of replication technology. It has a log reader that reads out of the transaction log, that’s different to simply restoring a log backup which is what log shipping is doing.

You could potentially use transactional replication instead then have CDC fire on the subscriber - I have seen this a couple of times. The caveat with this approach is that if you have to re-sync your subscriber from the publisher you will either end up with duplicate data in the CDC tables or missing data - depending if you initialise from a snapshot or backup. So worth exploring your requirements and setting up some tests to go through the different scenarios.


How to fix postgres stuck in recovery mode? I have Jira and Confluence installed in a server. We didn't have any free disk space one day, and these services didn't started correctly, so after deleting some files we now have some free space available. The problem now, is that postgres seems not to start correctly. It's in this recovery mode, but it can't get out of it.

valter@web:~$ ps -ef | grep post
postgres 14580     1  0 Jan03 ?        00:01:16 /usr/lib/postgresql/9.3/bin/postgres -D /var/lib/postgresql/9.3/main -c config_file=/etc/postgresql/9.3/main/postgresql.conf
postgres 14581 14580  0 Jan03 ?        00:00:00 postgres: startup process   recovering 000000010000000A0000000D

Any idea what is going on here ?

valter@web:~$ sudo ls -ltha /var/lib/postgresql/9.3/main/pg_clog/
total 3,9M

drwx------ 15 postgres postgres 4,0K Jan  3 12:41 ..
drwx------  2 postgres postgres 4,0K Jan  3 12:34 .
-rw-------  1 postgres postgres  48K Jan  3 12:41 000E
-rw-------  1 postgres postgres 256K Jan  3 12:30 000D
-rw-------  1 postgres postgres 256K Nov 15 09:39 000C
-rw-------  1 postgres postgres 256K Okt 13 23:07 000B
-rw-------  1 postgres postgres 256K Sep 11 23:11 000A
-rw-------  1 postgres postgres 256K Aug  8 10:45 0009
-rw-------  1 postgres postgres 256K Jul  3  2015 0008
-rw-------  1 postgres postgres 256K Mai 26  2015 0007
-rw-------  1 postgres postgres 256K Apr 17  2015 0006
-rw-------  1 postgres postgres 256K Mär  9  2015 0005
-rw-------  1 postgres postgres 256K Jan 30  2015 0004
-rw-------  1 postgres postgres 256K Dez 19  2014 0003
-rw-------  1 postgres postgres 256K Nov  9  2014 0002
-rw-------  1 postgres postgres 256K Sep 29  2014 0001
-rw-------  1 postgres postgres 256K Aug 22  2014 0000

EDIT 1:

valter@web:~/9.3/main/pg_xlog$ ls -ltha
total 97M
drwx------ 15 postgres postgres 4.0K Jan  3 12:41 ..
-rw-------  1 postgres postgres  16M Jan  1 12:16 000000010000000A0000000D
-rw-------  1 postgres postgres  16M Dec 23 00:00 000000010000000A0000000C
drwx------  3 postgres postgres 4.0K Dec 21 23:45 .
-rw-------  1 postgres postgres  16M Dec 21 23:36 000000010000000A00000011
-rw-------  1 postgres postgres  16M Dec 21 19:00 000000010000000A00000010
-rw-------  1 postgres postgres  16M Dec 21 14:25 000000010000000A0000000F
-rw-------  1 postgres postgres  16M Dec 21 09:55 000000010000000A0000000E
drwx------  2 postgres postgres 4.0K Jul 10  2014 archive_status

Server log file:

    valter@web:~/9.3/main/pg_xlog$ tail -f /var/log/postgresql/postgresql-9.3-main.log
    ...
    2016-01-05 17:23:08 CET FATAL:  the database system is starting up
    2016-01-05 17:23:09 CET FATAL:  the database system is starting up
    2016-01-05 17:23:10 CET FATAL:  the database system is starting up
    2016-01-05 17:23:11 CET FATAL:  the database system is starting up
    ...

If you can re import your data, you can uninstall postgresql and reinstall it. And then re import your data. I had a similar issue and this worked for me.

for ubuntu : sudo apt-get --purge remove postgresql-YOUR VERSION


Connection pools being reset with Error: 18056, Severity: 20, State: 46. & Perfmon Counters not showing We are using SQL Authentication (to reduce the number of connection pools) & .NET 4.0 connection strings to connect to SQL Server Enterprise Edition 2012 SP1 on a Windows 2008 R2 Enterprise Server:

  Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
  Oct 19 2012 13:38:57
  Copyright (c) Microsoft Corporation
  Enterprise Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)

We use about 50 servers split into 8 different groups different parts of a website.

Our website is using this SQL Server to log visit tracking data. Over the last few days it has spat out the following messages about the resetting connection pools:

  The client was unable to reuse a session with SPID 1327, which had been reset for connection pooling. The failure ID is 46. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.

Error log reads:

  Error: 18056, Severity: 20, State: 46.
  The client was unable to reuse a session with SPID 959, which had been reset for connection pooling. The failure ID is 46. This error may have been caused by an earlier  operation failing. Check the error logs for failed operations immediately before this error message.
  Login failed for user 'xxxx'. Reason: Failed to open the database 'xxxxxxxx' configured in the login object while revalidating the login on the connection. [CLIENT: 10.xx.xx.xxx]

After some digging, I found this document on the CSS blog: How It Works: Error 18056 – The client was unable to reuse a session with SPID ##, which had been reset for connection pooling and this one by Aaron Bertrand: Troubleshooting Error 18456. I know the error number is different but the failure ID is the same with a number of the messages are identical).

Failure ID 46 suggests that the login did not have permissions. Our logins default to the master database and the db name is specificied in the connection string.

I wanted to check the number of connection strings pools, etc. and checked all the counters in Perfmon for .Net Data Provider for SqlServer. It only gave me the option of defaultdomain9675 for the instance, so I selected that assuming that is a system generated ID name for our Datacentre network. Unfortunately all of the counters are reading zero. On one of our other main servers the connection pools are hovering around 10 which is what I expected to see on a healthy server with that kind of load.

My question is 3 fold

Can anyone suggest why the Windows 2008 R2 Server is not showing .Net Data Provider for SqlServer?
Has anyone experienced this as I obviously believe that the login not having permissions is a red herring ?
If different groups of web servers have the same connection string syntax but with slightly different whitespace, would this cause the server to use another connection pool?

Min & max memory settings are 20GB & 58GB respectively. The server is a dedicated database server with 64GB of RAM. I don't think memory is the issue as the box seems to have a decent page lfe expectancy. Auto close is not enabled. The server is always up: this is a 24x7 website with heavy usage.

1 - can't say for sure, I'd have to go find a server to dig into myself.

2 - yes, I see this periodically in my environment though we're not on sql 2012 yet on the systems we see this from. You may also want to check this post though State 46 seems to be related to having a specific Database=xxx in the connection string, does that db still exist?

The way my network is set up I suspect it's the network's automatic closing of tcp sessions after being 5 minutes idle that's the issue - neither the db nor the client is closing the session so the connection pool still thinks the connection is open and tries to use it only to find it's not really open anymore. You don't mention how the network between your web servers and db is configured, maybe your case is similar.

Another possibility may be the (old, not sure if ever really resolved, see http://support.microsoft.com/kb/942861) issue about TCP Chimney Offload settings.

3 - My understanding is pooling requires exact string matches, so whitespace and different order of parameters would cause different pools. (If I'm wrong on that, please let me know.)


How to manage SQL Log File .ldf My problem is having issue with SQL Server 2014 Database and its size growing and consuming Hard disk is almost full, Only 50GB space is left in 1TB Hard drive, and SQL database log (.ldf) file is consuming 800GB space and continue increasing, We can Shrink the database-log but there are several blogs which says it effects the server performance and data loss, or we can increase the space of the hard disk, Please advise.

Why Does the Transaction Log Keep Growing or Run Out of Space? does not mention the main performance problem of Virtual Log Files.

Any Help or suggestions much appreciated.

Although the question  Why Does the Transaction Log Keep Growing or Run Out of Space? provides lots of information on transaction log management, it does not mention the main performance problem of Virtual Log Files, which I will address below.

Since your question is about managing size and performance of the transaction log, I'll write about each subject individually.

The transaction log records all transactions and database modifications made by each transaction, and several maintenance actions have an affect on the space requirements of the transaction log. When managing the size of the transaction log, you will need to schedule all maintenance activities with an understanding of how they will affect growth and how they won't. Maintenance activities include transaction log backups, updating statistics, rebuilding indexes and other regularly scheduled activities. Your transaction log contains active virtual log files and inactive virtual log files in on single physical file. Active log files contain transactions that have not been backed up and inactive log files are available for reuse and are mainly the free space in the log file. Generally, when pages in the database change, those changes are recorded in the transaction log. During an operation like rebuilding all indexes, pages will change at a rapid pace and could prompt the database engine to autogrow the log file. Autogrowths actions are recorded in the default trace. I've experienced rapid growth due to index rebuilding, but I had the space to accommodate so I didn't make any changes. You could increase the frequency of the transaction log backups during index rebuilds, assuming you use full recovery model, and distribute the index rebuilds over time so that the growth is mitigated.

I've experienced poor performance in log files to the point where some of the applications developed ineternally were slowing noticibly and timing out. It was due to excessive number of small virtual log files. The size and number of virtual log files contined in one physical file is directly related to the autogrowth settings. To check if you have excessive VLFs, run DBCC Loginfo. If the resulting number of records is in the hundreds or thousands, then perform the following procedure:

Change the autogrowth settings to something reasonable. It's difficult to make a recommendation in your case because an 800GB log file is highly unusual for a 96GB database. It needs to be a value that is large enough so that the database engine only creates a minimal amount of VLFs. I would try 500MB to 1GB.
Shrink the file as small as possible
Reinitialize the file. Usually this step is to reinitialize the file back to it's original size. If you have no idea what size to reinitialize it, try 50GB.

Allowing it to grow without reinitializing it will allow the VLF issue to occur again and contributes to higher IO because each autogrowth of the log file needs to be completely written out with zeros before it can be used. This is why shrinking the log file causes poor performance.


How to add a column with computed columns with values of row on Sybase IQ How to add a computed column to a table referring a field from current row at Sybase IQ 16

Here is want I want to achieve:
When inserting a new row (or updating a row) I want to have a column updated based on a function referencing another field. To make an example

I've got this table:

CREATE TABLE "myTable" (
    id integer,
    sales integer,
    bonus integer
)

Where bonus should be calculated as sales * 10 -- remember, just an example.

I was thinking about adding some trigger functions doing this, but then I found computed columns -- unfortunately wasn't able to get this running.

Page Considerations When Using the CREATE TABLE Statement from the Sybase IQ 16 online manual:

Computed Columns
Computed column support differs as follows:

SAP SQL Anywhere supports computed columns that can be indexed.

Adaptive Server Enterprise and SAP Sybase IQ do not.

It seems to support views though, so you could define a view that contains the columns of the table plus the function, as another column (the column values will not be materialized this way, only computed when needed.)


Mysql consumes a lot of memory Here is my situation.

I have an ubuntu server monitored by nagios.
recently nagios sends alarms regarding the memory consumption on the server. I logged into the server and executed the top command then I found that mysql is the process that consumes a lot of the memory (about 64%).

This server contains a forum, and DB replica for another server so I logged into mysql and show processlist I found only 7 records.

How could I dig more in this situation to decide what is the application which consumes all that memory?

MySQL consumes 64% is not a very big deal if the load is very high and the memory is low on the machine. Typically I would say its better that it should not consume more than 20 to 25% of the total memory available.

Anyways for your question, just try to find out the bottlenecks.
1 You should be able to find out if there are any long running queries - Say for example you are fetching some 1 million rows or so in a InnoDB engine (where transaction is supported) and your query has lot of joins.
2 Then you can tweak the actual config file of MySQL to increase the number of connections that is allowed and the max memory that is assigned to MySQL.
3 Also make sure you close all the sql connections in your application if you have not done it.
4 If long running queries are the bottleneck, then you can change some of the tables to MyIsam if it doesn't matter with transactions.

I hope it helps !


Receiving duplicate results in one condition of JOINED query I always end up here when Googling for solutions, so I thought I'd cut out the middleman since I'm unable to fix this via searching/forums. It's my first time posting, so please forgive any faux pas in posting etiquette. I have read through the related posts, but either I'm not savvy enough to see the connection to my problem(very likely) or there doesn't seem to be an issue quite like mine.

My environment is MySQLi

My issue is that I'm using a JOINED query that isn't working as intended.  The query is supposed to give me a result if it finds a category match, either on primary_cat, which is stored in the link's table, or via a secondary cat, which is stored in a linkcat table.  The issue that I'm running into is that my query is showing duplicate(always 2) results if it is matched on the primary_cat, but properly shows a single result when matched on the secondary

    $q_urls = "
    SELECT shortenified_urls.*
    FROM shortenified_urls
        LEFT JOIN linkcats
            ON linkcats.link_id = shortenified_urls.id
        WHERE
            (shortenified_urls.primary_cat = $vu_cat_view
            OR linkcats.cat_id = $vu_cat_view)
        AND shortenified_urls.is_social = '1'
        AND shortenified_urls.active = '1'
        ORDER BY date_added DESC
    ";

I have a screenshot of my problem, which can be found here: http://en.zimagez.com/zimage/screenshot-10162014-111452am.php

I have tried playing with a DISTINCT nested query(nested because I need all the data from the result, but can't seem to figure out how to get DISTINCT to do that without one), but I'm truly terribad at this and can't figure out the solution on my own.

Any help in getting this fixed would be greatly appreciated.  Thanks for your time!

Since I could not comment, I had a similar issue earlier, and I found the solution in this answer. When joining, you should group the results in order to have only one row per result.
Besides that, do you need all the columns of the table? It is always a good practice to select columns by their name, instead of select *


Single database schema or multiple schemas? I'm designing a website for an annual event. The events are similar but mostly independent year-to-year. I see 2 possible designs:

A single schema with most tables having an EventID column to tag data to specific year. Possibly have views for each year.
Multiple schema one per event. The events are independent, so it would be plausible to simply copy a schema and purge old data for each year.

I'd have multiple schemas for faster performance, ease of change, and programming simplicity, but I also feel that a single schema has its advantages such as database simplicity, uniformity, and easier access to old data (not common).

Which approach is preferable?
Any real world experiences on this?

Thanks.

the 'cleaner' solution would definitely using a single schema, and relate your data to a specific event, either with a EventID column, or yet better, using an Events table, relating your data to a row in this table (but keep it simple, you don't need to relate every row in every table to your event, just the 'top-most' objects)

but on the other hand, maybe 'You Aint Gonna Need It', a lot can change in a year, and you'll probably have to make a lot of changes and improvements for next years event. anyway you'll find yourself carrying over and migrating data which nobody needs or wants, so you may end up purging your database after all.

i learned to stick to the YAGNI rule the hard way several times, and keeping things simple always proofed to be the better solution. as long as you produce clean and maintainable code, it's often much simpler to add functionality, than to trim unneeded (and often untested) fat


Disable a bad SQL Server Query Plan vs. Force a good plan I like to leave query planning up to the database as much as possible, and only force when absolutely necessary. I'll usually do this in SSMS with the 'Force plan' option.

So below, my purple query went rogue today and timed out 10 times in a row, but the black one seems to be working great. So I can Force the black query.

But what about the turquoise plan vs. the black? They're about the same.

I've been wondering for a while why there doesn't seem to be any option to 'Disable' a query plan from running. I know I can delete a plan from the cache, but there's nothing stopping it recreating it right?

Especially times when I don't have the resources for a full investigation I'd much rather say 'skip this plan' - but not have to commit to forcing a specific plan. I can find no such feature.

I can't even find a discussion of why this doesn't seem to exist. I was expecting 'Yes a lot of people ask this, and generally it's better to...' - but nothing.

I don't understand also why it picked a timeout plan 10 times in a row (the last purple square) and didn't even attempt the other plans once. But that's probably a different question. Also if you're curious - the above query is to get order history for a specific SKU - so the corresponding number of source rows may vary greatly for each SKU.

The current ways (I'm oversimplifying) to affect plan shape are hinting, which usually removes options from the optimizer, and forcing a particular plan, whether with USE PLAN, plan guide, or Query Store.

Adding hints can prevent the plan you don't want, but will likely prevent other options as well. Forcing a plan has the same issue of course - it prevents ALL other options.

Your best practical option here is probably to force a plan that works well, then continue to monitor it.

If you really want to keep both options available, then you may want to apply hints with a plan guide.


SQLLite query datatime divided into week of the year, and month of the year In my database I have two tables:

type_of_activity

CREATE TABLE IF NOT EXISTS type_of_activity(id_type INTEGER PRIMARY KEY, name VARCHAR(20))

activity

CREATE TABLE IF NOT EXISTS activity(
 id_activity INTEGER PRIMARY KEY AUTOINCREMENT,
 id_type INTEGER,
 start_time TEXT,
 distance REAL,
 duration INTEGER,
 elevation INTEGER,
 FOREIGN KEY(id_type) REFERENCES type_of_activity(id_type))

Some details: start_time format: "YYYY-MM-DD HH:MM",
duration in seconds.
I want to make query that summary:

Every week mileage, duration, maybe from this data calculate pace, and elevation.
The same as above, but for months

I think that is good to convert the start_time to:
in 1)strftime('%Y-%W',start_time); 2)strftime('%Y-%m',start_time)
But I don't know how to aggregate results.

Can someone help me?

I made it

SELECT
  strftime('%Y-%m',star_time) AS date
  , Sum(distance)
FROM activity
WHERE
Group By
  strftime('%Y-%m',start_time)


Hash Match inner join in simple query with in statement I am running the execution plan for the following query:

select m_uid from EmpTaxAudit
where clientid = 91682
and  empuid = 42100176452603
and newvalue in('Deleted','DB-Deleted','Added')

Here is the execution plan:

I have a non clustered index on EmpTaxAudit Table on ClientId and NewValue columns which shows above as 14.9% of the execution:

CREATE NONCLUSTERED INDEX [idx_EmpTaxAudit_clientid_newvalue] ON [dbo].

[EmpTaxAudit]
(
    [ClientID] ASC,
    [NewValue] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

I also have a non clustered unique index PK as follows:

ALTER TABLE [dbo].[EmpTaxAudit] ADD  CONSTRAINT [PK_EmpTaxAudit] PRIMARY KEY NONCLUSTERED
(
    [ClientID] ASC,
    [EmpUID] ASC,
    [m_uid] ASC,
    [m_eff_start_date] ASC,
    [ReplacedOn] ASC,
    [ColumnName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

Trigger code in source table EmpTax:

CREATE                trigger [dbo].[trins_EmpTax]
on [dbo].[emptax]
for insert
as
begin
  declare
    @intRowCount  int,
      @user          varchar(30)

  select @intRowCount = @@RowCount
  IF @intRowCount > 0
  begin
     select @user = suser_sname()

     insert EmpTaxAudit (Clientid, empuid,m_uid,m_eff_start_date, ColumnName, ReplacedOn, ReplacedBy, OldValue,dblogin,newvalue)
     select Clientid, empuid,m_uid,m_eff_start_date,'taxcode', getdate(),IsNull(userid,@user), '', Left(@user,15),'Added'
        from inserted i
         where m_uid not in (select m_uid from EmpTaxAudit
                    where clientid = i.clientid  and (newvalue = 'Deleted'
            or newvalue = 'DB-Deleted'
                        or newvalue = 'Added') and  empuid = i.empuid)
       and i.m_eff_end_date is null

     insert EmpTaxAudit (Clientid, empuid,m_uid,m_eff_start_date, ColumnName, ReplacedOn, ReplacedBy, OldValue,dblogin,newvalue)
     select Clientid, empuid,m_uid,m_eff_start_date,'taxcode', getdate(),IsNull(userid,@user), '', Left(@user,15),'Deleted'
        from inserted i
         where m_uid not in (select m_uid from EmpTaxAudit
                    where clientid = i.clientid  and (newvalue = 'Deleted'
            or newvalue = 'DB-Deleted'
                        or newvalue = 'Added') and  empuid = i.empuid)
       and i.m_eff_end_date is not null

  end
end

What can I do to avoid the high cost of Hash Match (Inner Join)?

Thanks!

For the 1st query, an index that uses all three columns from the WHERE clause and includes the column from the SELECT list would be much more useful:

-- index suggestion A
(clientid, empuid, newvalue) INCLUDE (m_uid)

or an index targeted specifically for this query:

-- index suggestion B
(clientid, empuid, m_uid)
WHERE newvalue in ('Deleted', 'DB-Deleted', 'Added')

Regarding the trigger, some comments:

The first query you show does not appear in the trigger. What appears is a join from that table to the inserted rows to another table (which has the trigger).
My suggestion B above seems better suited to be used by the trigger.
The trigger has 2 almost identical insert statements. Why? I think they could be combined in one - and simpler - insert and using NOT EXISTS instead of NOT IN:

insert EmpTaxAudit
  ( Clientid, empuid, m_uid, m_eff_start_date, ColumnName,
    ReplacedOn, ReplacedBy, OldValue, dblogin,
    newvalue
  )
select
    Clientid, empuid, m_uid, m_eff_start_date, 'taxcode',
    getdate(), IsNull(userid,@user), '', Left(@user,15),
    case when m_eff_end_date is null
        then 'Added' else 'Deleted'
    end
from inserted i
where not exists
      ( select 1
        from EmpTaxAudit
        where m_uid = i.m_uid
          and clientid = i.clientid
          and empuid = i.empuid
          and newvalue in ('Deleted', 'DB-Deleted', 'Added')
      ) ;


Backup databases based on wildcard on database name We have a MS SQL 2016 databse server with a number of databases that need backing up.  We are using a maintenance plan to backup all databases at the moment but running low on space on one of our drives.

We have the following databases:

foo1
foo2
..
..
foo10
fred01
fred02

How would we go about swapping out the T-SQL in the maintenance plan task to only backup the databases foo* and not the fred databases?

I could select the databases manually but over time new foo databases are added and I want to ensure that these are automatically backed up without needing to update the maintenance plan.

Thanks

Use T-sql to query sys.databases and backup your databases. one free solution is to use Ola's backup solution

A. Back up all user databases, using checksums and compression; verify the backup; and delete old backup files

EXECUTE dbo.DatabaseBackup
@Databases = '%foo%', -- only databases that will have foo in name
@Directory = '\\server\Backup',
@BackupType = 'FULL',
@Verify = 'Y',
@Compress = 'Y',
@CheckSum = 'Y',
@CleanupTime = 24

Change parameters as per your needs.


MongoDB / WiredTiger: reduce storage size after deleting properties from documents I use MongoDB 3.4 with WiredTiger storage engine on a replica set of 4 nodes.

I shrinked a lot a documents by removing properties that takes most of the space, but the documentation says that this operation doesn't reduce the storage size (only the data size), and I can confirm it's true.

So I tried using the compact command: some space was freed but the storage size is still way bigger than data size. Is it because it only moves documents but does not reduce already allocated space per documents?

Do I need to delete and recreate all documents to really reduce the storage size?

Just to clarify, please be careful about using repairDatabase on a replica set node. repairDatabase is meant to be used to salvage readable data i.e. after a disk corruption, so it can remove unreadable data and let MongoDB start in the face of disk corruption.

If this node has an undetected disk corruption and you run repairDatabase on it, this could lead into that particular node having a different data content vs. the other node as a result of repairDatabase. Since MongoDB assumes all nodes in a replica set contains identical data, this could lead to crashes and hard to diagnose problems. Due to its nature, this issue could stay dormant for a long time, and suddenly manifest itself with a vengeance, seemingly without any apparent reason.

WiredTiger will eventually reuse the empty spaces with new data, and the periodic checkpointing that WiredTiger does could potentially release space to the OS without any intervention on your part.

If you really need to give space back to the OS, then an initial sync is the safest choice if you have a replica set. On a standalone, dump/restore will achieve the same result. Otherwise, compact is the safer choice vs. repairDatabase. Please backup your data before doing any of these, since in my opinion this would qualify as a major maintenance.


MySQL - Restore 1TB database using myloader Im trying to restore a database which is 1.3TB and its in AWS Aurora. I want to restore a copy of this database to an EC2 instance(32 vCPU, 244GB MEMORY).

I used mydumper to take backup from AWS RDS and it took 6 Hrs.

But Im trying to load this backup using myloader. Its running more than 12hrs, but still 30% data only restored.

Myloader Settings:

Threads: 200
Queries Per thread: 10000

MySQL Settings:

max_connections = 3000
wait_timeout = 86400
interactive_timeout = 86400
innodb_buffer_pool_size =235G
bulk_insert_buffer_size = 5G
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_read_io_threads = 8
innodb_write_io_threads = 22
innodb_adaptive_hash_index = 0
innodb_old_blocks_pct = 45
transaction_isolation = READ-UNCOMMITTED
innodb_purge_threads = 2
innodb_change_buffering = inserts
innodb_change_buffer_max_size = 30
query_cache_size = 0
innodb_buffer_pool_instances=20
innodb_log_buffer_size = 1G
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 1G
max_allowed_packet=1G

Is there any additional settings that I should change for faster restore?

I think it is choking on 200 threads.  Recommend not using more than twice the number of CPU cores you have.  And even that may be too high...

In particular, there is no use adding more threads after saturating the I/O.  (I am not familiar with your configuration, so I cannot be more specific.)

innodb_buffer_pool_size =235G is dangerously high for 244GB of RAM.  If there is swapping, that could be killing performance.

If you have "indexed every column", that is another performance killer for loading.  And "indexing every column" is usually useless.  Instead, discover what indexes you need, and have only them.  This often includes 'composite' indexes.

Also, multiple UNIQUE (including PRIMARY) indexes on a table is costly.


CROSS APPLY versus INNER JOIN I was reading a lot about CROSS APPLY versus INNER JOIN recently. I tried writing some queries and they both work in a similar fashion, the execution plan seems to be same as well.

Maybe someone with a deeper knowledge could explain to me what the difference is between these two?

OUTER APPLY (SELECT UserID
                , ActionPerformedDate = MAX(ActionDate)
            FROM dbo.AdminUsage
            WHERE SubscriberID = S.SubscriberID
                AND ActionPerformed = 'Some Action'
                AND Description = 'True'
            GROUP BY UserID) AS AU

LEFT JOIN (SELECT SubscriberID
                , UserID
                , MAX(ActionDate) OVER(PARTITION BY SubscriberID) AS ActionPerformedDate
            FROM dbo.AdminUsage
            WHERE ActionPerformed = 'Some Action'
                AND Description = 'True') AS AU
    ON AU.SubscriberID = S.SubscriberID

Is there a preferred approach in this case?

They're two slightly different animals that can be used for the same purposes, as in your example. CROSS APPLY is your only option for "joining" table value functions and "expanding" xml documents, though.

Some queries, particularly parallel queries, can exhibit vastly improved performance using CROSS APPLY, provided you have the requisite processor threads and indexing strategy.

Microsoft MVP Itzik Ben-Gan elaborates a couple of great examples in this talk


MySQL: How to set the default string using another field by string-interpolation? I have the following table and I wondering whether string interpolation using another is possible:
CREATE TABLE Account (
    AccountID varchar(25),
    About varchar(300) NOT NULL DEFAULT
        'My id is ${AccountID}',
    PageLink varchar(255) NOT NULL UNIQUE,

That is: I want different default values for each row using the primary key.
id: 1
About: My id is 1

id: 2
About: My id is 2

This is possible in MySQL 8.0 because they support expressions in DEFAULT clauses now. But it won't work with interpolation like you show. It works like any other MySQL string expression:
CREATE TABLE Account (
 AccountID varchar(25),
 About varchar(300) not null default (CONCAT('My id is ', AccountID)),
 Pagelink varchar(255) not null unique
);

Example:
mysql> INSERT INTO Account SET AccountID = '8675309', Pagelink = '<link>';
Query OK, 1 row affected (0.00 sec)

mysql> select * from Account;
+-----------+------------------+----------+
| AccountID | About            | Pagelink |
+-----------+------------------+----------+
| 8675309   | My id is 8675309 | <link>   |
+-----------+------------------+----------+

In earlier versions of MySQL, defaults can only be a literal value, or NULL, or CURRENT_TIMESTAMP, but not an expression.


Postgresql, escape character in string_agg function Question, can I add escape character \ just for fields param_name and value for this?

string_agg(distinct '{name:"' || param_name || '",value:"' || value || '"}',',') as "params",

Result is.

{name:"připojení",value:"1/2""},

I need this (use \ before " only for fields/vars inside "param_name" and "value", in case, when value has " character.

{name:"připojení",value:"1/2\""},

Thanks.

Update, no json, I need export to csv. Some code here.

copy(

with recursive
cte as(
select
category_id,
category_parent,
category_name::text,
category_id::text category_ids
from s_category as c
where category_parent = 0
union all
select
c.category_id,
c.category_parent,
concat(cte.category_name, ' > ', c.category_name),
concat(cte.category_ids, ':', c.category_id::text)
from s_category as c,cte
where cte.category_id = c.category_parent
)

select distinct
s_product.product_id as "itemID",

...

string_agg(distinct '{name:"' || param_name || '",value:"' || value || '"}',',') as "params",
case when price_tax = price_rec then '' else 'Akce' end as "label"

from
s_product
left join s_cf_j_product_value on s_product.product_id = s_cf_j_product_value.product_id
left join s_product_image on s_product.product_id = s_product_image.product_id
left join s_pricelist_generated_lists on s_product.product_id = s_pricelist_generated_lists.product_id
left join s_producer on s_product.producer_id = s_producer.producer_id
left join s_category on s_product.category_id = s_category.category_id
left join cte on s_product.category_id = cte.category_id
left join s_cf_j_product_value as a on s_product.product_id = a.product_id
left join s_cf_value as v on a.value_id = v.value_id
left join s_cf_param as p on v.param_id = p.param_id

where

...

group by
s_product.product_id,
s_product_image.filename,
s_category.category_id,
s_pricelist_generated_lists.price_tax,
s_pricelist_generated_lists.price_rec,
s_producer.producer_name,
cte.category_name,
cte.category_ids

) TO stdout DELIMITER ',' CSV;

It returns this.

... {name:""připojení"",value:""1/2""""},{name:""připojovací rozteč"",value:""558 mm""},{name:""rozměry"",value:""600 x 960 mm""}",""

But I need this.

... {name:"připojení",value:"1/2\""},{name:"připojovací rozteč",value:"558 mm"},{name:"rozměry",value:"600 x 960 mm"}","

The doubled double-quotes ("") are the necessary consequence of " being the default quote character for COPY with CSV format. The manual:

  QUOTE

  Specifies the quoting character to be used when a data value is quoted. The default is double-quote. This must be a single one-byte character. This option is allowed only when using CSV format.

  ESCAPE

  Specifies the character that should appear before a data character that matches the QUOTE value. The default is the same as the QUOTE
  value (so that the quoting character is doubled if it appears in the
  data). This must be a single one-byte character. This option is
  allowed only when using CSV format.

You could use something like (assuming Postgres 9.1 or later):

COPY (...) TO stdout (FORMAT CSV, QUOTE '\');

Then double-quotes are not escaped.

To ...

  add escape character \ just for fields param_name and value

.. you could simply replace():

string_agg(distinct '{name:"' || replace(param_name, '"', '\"') || '",value:"' || replace(value, '"', '\"') || '"}',',') as "params"

Or:

string_agg(distinct format('{name:"%s",value:"%s"}', replace(param_name, '"', '\"'), replace(value, '"', '\"'), ',') as "params"

But string_agg() has nothing to do with either. And I am not sure what you are trying to achieve.


java.sql.SQLRecoverableException: No more data to read from socket When I use java code to insert data to oracle,the following information appeared:

java.sql.SQLRecoverableException: No more data to read from socket
    at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1200)
    at oracle.jdbc.driver.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:1155)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:279)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1008)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1307)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449)
    at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3530)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1350)

The oracle release is 11g.

For errors like this you should involve oracle support. Unfortunately you do not mention what oracle release you are using. The error can be related to optimizer bind peeking. Depending on the oracle version different workarounds apply.

You have two ways to address this:

upgrade to 11.2
set oracle parameter _optim_peek_user_binds = false

Of course underscore parameters should only be set if advised by oracle support


Initialize, Start Server and Create Database in Single Script I am writing a provision script for a dev environment with the following:

sudo su - postgres <<EOF
cd ~/data
pg_ctl -D . initdb
pg_ctl -D . -l run.log start
createdb mydb
pg_ctl -D . stop
EOF

It fails with the following error:

createdb: could not connect to database template1: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/run/postgresql/.s.PGSQL.5432"?

Manually executing the commands works. I have noticed that when createdb succeeds, there are several postgres processes, often 5 or 6, so I added the following:

...
pg_ctl -D . -l run.log start
until [ `pgrep -c postgres` -gt 4 ]; do true; done
createdb mydb
...

But it hangs, meaning pgrep never gets over 5 counts inside the script runtime. I have also tried using a subshell:

...
(pg_ctl -D . -l run.log start)
wait
createdb mydb
...

With no success.

Try adding the "-w" flag to your pg_ctl -D . -l run.log start command, so that pg_ctl should only return once the server is up. If that worked, the problem was that your createdb command was running too quickly after pg_ctl, before Postgres was actually up and accepting connections, when those commands were executed in a script as opposed to manually.

(Re-posting comment as an answer, as requested.)


Mongos not returning existing data in shards So we have a configuration of 2 shard (each is a replica set) and one sharded collection.

There are around 10 million records in each shard. We are using mongo 2.6.4 version. We have sharded with hash_id shard key which we generated specific for sharding.

We are fetching data by id from mongos and most of the queries work but for some of the queries (around 10k) mongos returns null although they exists in one of the shards and we can fetch it from shard directly.

Does anyone knows why mongos or config server don't have information for some ids and is there any way of fixing it? Is there any kind of operation that would refresh data in config servers to have informations about all records in database?

Thanks,
Ivan

issue flushRouterConfig command, and give a try. It enforces mongos to flush cached metadata which is out of date.

Let me know if it doesn't work.


Issue with getting data from XML file by using SSIS I'm getting the data from XML file on the internet like below:

<?xml version="1.0" encoding="ISO-8859-1"?>
-<exchangerates type="Exhange rates" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" refamt="1" refcur="DKK" author="Bank">
-<dailyrates id="2016-07-22">
<currency rate="505.47" desc="Australian dollars" code="AUD"/>
<currency rate="380.36" desc="Bulgarian lev" code="BGN"/>
<currency rate="205.85" desc="Brazilian real" code="BRL"/>
<currency rate="515.31" desc="Canadian dollars" code="CAD"/>
<currency rate="684.99" desc="Swiss francs" code="CHF"/>
<currency rate="101.21" desc="Chinese yuan renminbi" code="CNY"/>
</dailyrates>
</exchangerates>

XSD file looks like:

<?xml version="1.0"?>
<xs:schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="exchangerates">
    <xs:complexType>
      <xs:sequence>
        <xs:element minOccurs="0" name="dailyrates">
          <xs:complexType>
            <xs:sequence>
              <xs:element minOccurs="0" maxOccurs="unbounded" name="currency">
                <xs:complexType>
                  <xs:attribute name="code" type="xs:string" use="optional" />
                  <xs:attribute name="desc" type="xs:string" use="optional" />
                  <xs:attribute name="rate" type="xs:string" use="optional" />
                </xs:complexType>
              </xs:element>
            </xs:sequence>
            <xs:attribute name="id" type="xs:date" use="optional" />
          </xs:complexType>
        </xs:element>
      </xs:sequence>
      <xs:attribute name="type" type="xs:string" use="optional" />
      <xs:attribute name="author" type="xs:string" use="optional" />
      <xs:attribute name="refcur" type="xs:string" use="optional" />
      <xs:attribute name="refamt" type="xs:unsignedByte" use="optional" />
    </xs:complexType>
  </xs:element>
</xs:schema>

But in SSIS (from XML Source Task) I get the data like below. Last column shows me "1" instead of "date" (2016-07-22 in this case). I need this date to be in every row  of the "dailyrates_Id" column. Any ideas how to solve this ?

code    desc    rate    dailyrates_Id
AUD Australian dollars  505.47  1
BGN Bulgarian lev   380.36  1
BRL Brazilian real  205.85  1
CAD Canadian dollars    515.31  1

Metadata of XML Source Task:

Those attributes are on different levels so this is kind of the way the XML Source works.  You have a couple of options:

Use Sort options with a Merge Join to bring the two sections back together, something like this:

Capture the XML in a variable, pass it to a stored proc as a parameter and shred it in the proc, using something like this:

DECLARE @xml XML = '<?xml version="1.0" encoding="iso-8859-1"?>
<exchangerates type="Exhange rates" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" refamt="1" refcur="DKK" author="Bank">
    <dailyrates id="2016-07-22">
        <currency rate="505.47" desc="Australian dollars" code="AUD"/>
        <currency rate="380.36" desc="Bulgarian lev" code="BGN"/>
        <currency rate="205.85" desc="Brazilian real" code="BRL"/>
        <currency rate="515.31" desc="Canadian dollars" code="CAD"/>
        <currency rate="684.99" desc="Swiss francs" code="CHF"/>
        <currency rate="101.21" desc="Chinese yuan renminbi" code="CNY"/>
    </dailyrates>
</exchangerates>'

SELECT
    dr.c.value('@id', 'DATE') id,
    c.c.value('@rate', 'MONEY') rate,
    c.c.value('@desc', 'VARCHAR(50)') [desc],
    c.c.value('@code', 'VARCHAR(50)') code
FROM @xml.nodes('exchangerates/dailyrates') dr(c)
    CROSS APPLY dr.c.nodes('currency')  c(c)

Good luck!


Database structure user service connection I am working on a SQL database structure for an application that connects users to different API's of services. So one user can connect to for example 2 API's and the data of one service will be used inside the other service via the user.

I have drawn a simple database structure with a user, service and connection table. My idea is to make the connection by storing the user_id and service_id in the connection table together with the credentials.

The problem is that some services use OAuth (so based on access tokens) and some use a username and password. I don't know how to arrange my connections table to provide both options.

In agreement with the comments made by @Laughing Vergil, this is the structure I would use:

CREATE TABLE users
(
    user_id BIGINT PRIMARY KEY,
    username CHARACTER VARYING(255) NOT NULL,
    password CHARACTER VARYING(255) NOT NULL
) ;

CREATE TABLE services
(
    service_id BIGINT PRIMARY KEY,
    name CHARACTER VARYING(45) NOT NULL
) ;

CREATE TABLE connections
(
    connection_id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(user_id),
    service_id BIGINT NOT NULL REFERENCES services(service_id),

    authorization_code CHARACTER VARYING(255),
    refreh_token CHARACTER VARYING(255),
    access_token CHARACTER VARYING(255),

    service_username CHARACTER VARYING(255),
    service_password CHARACTER VARYING(255),

    /* Either one of authorization code or username must be null,
       but not both */
    CHECK ((authorization_code IS NULL) <> (service_username IS NULL)),

    /* If the authorization_code is NULL;
           refresh_token and access_token must also be null */
    CHECK (
        CASE WHEN (authorization_code IS NULL) THEN
            (refreh_token IS NULL) AND
            (authorization_code IS NULL)
        ELSE
            true
        END),

    /* If the service_username is NULL; service_password must also be null */
    CHECK ((service_username IS NULL) = (service_password IS NULL))
) ;

You can do a few tests:

INSERT INTO users VALUES(1, 'user', 'pwd');
INSERT INTO services VALUES (1, 'service');

/* Good */
INSERT INTO connections (connection_id, user_id, service_id, service_username, service_password)
VALUES (10, 1, 1, 'user', 'pwd');

/* Also good */
INSERT INTO connections (connection_id, user_id, service_id, authorization_code, refreh_token, access_token)
VALUES (20, 1, 1, 'auth_code', NULL /* Don't know yet */, NULL /* Don't know yet */) ;

/* Bad */
INSERT INTO connections (connection_id, user_id, service_id, service_username, authorization_code)
VALUES (21, 1, 1, 'user', 'auth') ;

/* Bad */
INSERT INTO connections (connection_id, user_id, service_id, service_username, service_password)
VALUES (30, 1, 1, 'user', NULL);


SQL Server SMO Method "Discover()" I think I just found the coolest thing every with SQL Server SMO and PowerShell. I was asked to get a script together to find the owner of every object in an instance of SQL Server. I found a script on Technet/MSDN SQL Server library but thought it would be a lot easier to get it with PowerShell and be able to hit all the instances on the network at once.

Well in playing around I came across this method within the Database class: Discover()

So if you use this code:

#Load SMO
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | Out-Null
$srv = New-Object 'Microsoft.SqlServer.Management.Smo.Server' "your instance name"

$db = $srv.Databases

$db.Discover()

When you run that last line you are going to see your console start scrolling text like it is on drugs, if you connect to an instance with a lot of databases on it.

Well I went to do  $db | gm  and it pretty much shows you every TypeName available within SMO objects.

Now my question. I can execute $db.Discover() | Select Name, Owner and it will start spitting out everything with a name and an owner value.

How could I also get the object type of the object being returned (like if it is a trigger, schema, table, etc)? I can't seem to figure that out so far.

EDIT

So far I find that it seems to only "discovery" things on the database level. Apparently somewhere in my console cache I had set  $db to something like $db = ($srv.Databases).Item("aDatabase"), cause this is the only way you can call the method. Still looking for a way to pull out Object Name, type, and Owner though.

Try this

$db.Discover() | select @{Name="Type";Expression={$_.GetType().Name}};


ORA-00933: SQL command not properly ended I have created a database link to a remote database, MIGRERDOSSIER.US.ORACLE.COM, then I use it to insert records in a table in a local database. Here is the request I am trying to execute:

insert into
    Dossier(num_dossier,indice,code_service,num_secteur,date_depot,type_dossier,SUP_DECL_ARE,SUP_DECL_CE,SUP_DECL_HE,SUP_DEF_ARE,SUP_DEF_CE,SUP_DEF_HE)
    select s.num_dossier,s.indice,s.code_service,s.num_secteur,s.date_depot,s.cod_sous_typ,s.SUP_DECL_ARE,s.SUP_DECL_CA,s.SUP_DECL_HE,s.SUP_DEF_ARE,s.SUP_DEF_CA,s.SUP_DEF_HA
    from MIGRERDOSSIER.US.ORACLE.COM@Dossier s

but Oracle is displaying the error ORA  00933: SQL command not properly ended.

I can't figure the problem with my request, any help would be greatly appreciated.

Your syntax for using the database link is incorrect, you've got the link and table identifiers in the wrong order.

It should be:

select ... from table_name@db.link.name

Having too many . in a table name results in an ORA-00933 error, like you're getting.


Unused Memory Grant on Delete I have this delete statement

delete from dbo.HighSchoolGradeLevel where Id=@P0

This is the execution plan

https://www.brentozar.com/pastetheplan/?id=H1qzvl3nW

when the application is busy and there are many deletes coming, the performance become so bad, My questions are.

How to make this delete perform better?

why the granted memory is so high, it steals the memory from the buffer pool ?

Note: there is on delete cascade on the 2 tables Highschoolcourse and highschoolgrade.

CREATE TABLE [dbo].[HighSchoolCourseGrade](
    [Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [TermSubTypeLkupId] [int] NULL,
    [Grade] [varchar](50) NULL,
    [GradePoint] [float] NULL,
    [CourseId] [int] NOT NULL,
    [CreatedDate] [datetime] NOT NULL,
    [UpdatedDate] [datetime] NOT NULL,
    [CourseUnits] [float] NULL,
 CONSTRAINT [PK_HighSchoolCourseGrade] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING ON

GO

/****** Object:  Index [ix_HighSchoolCourseGrade_CourseId_includes]    Script Date: 10/11/2017 4:33:38 PM ******/
CREATE NONCLUSTERED INDEX [ix_HighSchoolCourseGrade_CourseId_includes] ON [dbo].[HighSchoolCourseGrade]
(
    [CourseId] ASC
)
INCLUDE (   [Id],
    [TermSubTypeLkupId],
    [Grade],
    [GradePoint],
    [CreatedDate],
    [UpdatedDate],
    [CourseUnits]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

ALTER TABLE [dbo].[HighSchoolCourseGrade]  WITH CHECK ADD  CONSTRAINT [FK_HighSchoolCourseGrade_CourseId] FOREIGN KEY([CourseId])
REFERENCES [dbo].[HighSchoolCourse] ([Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[HighSchoolCourseGrade] CHECK CONSTRAINT [FK_HighSchoolCourseGrade_CourseId]
GO

CREATE TABLE [dbo].[HighSchoolCourse](
    [Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [CourseName] [varchar](200) NULL,
    [CourseTypeLkupId] [int] NULL,
    [EntryTypeLkupId] [int] NULL,
    [SubjectKey] [int] NULL,
    [GradeLevelId] [int] NOT NULL,
    [CreatedDate] [datetime] NOT NULL,
    [UpdatedDate] [datetime] NOT NULL,
    [TotalCourseUnit] [float] NULL,
    [TotalGradePoint] [float] NULL,
 CONSTRAINT [PK_HighSchoolCourse] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING ON

GO

/****** Object:  Index [ix_HighSchoolCourse_GradeLevelId_includes]    Script Date: 10/11/2017 4:32:58 PM ******/
CREATE NONCLUSTERED INDEX [ix_HighSchoolCourse_GradeLevelId_includes] ON [dbo].[HighSchoolCourse]
(
    [GradeLevelId] ASC,
    [SubjectKey] ASC
)
INCLUDE (   [Id],
    [CourseName],
    [CourseTypeLkupId],
    [EntryTypeLkupId],
    [CreatedDate],
    [UpdatedDate],
    [TotalCourseUnit],
    [TotalGradePoint]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

ALTER TABLE [dbo].[HighSchoolCourse]  WITH CHECK ADD  CONSTRAINT [FK_HighSchoolCourse_GradeLevelId] FOREIGN KEY([GradeLevelId])
REFERENCES [dbo].[HighSchoolGradeLevel] ([Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[HighSchoolCourse] CHECK CONSTRAINT [FK_HighSchoolCourse_GradeLevelId]
GO

This is from sp_blitzcahce

Update

I find this Delete in the Query Store , there were 3 plans and I forced SQL to use different plan. I am wondering Why SQL choose to use the most expensive execution plan?

Try adding Id as one of the key columns of ix_HighSchoolCourseGrade_CourseId_includes. That may help with the additional sort needs as well as back the foreign key.
If you can change the foreign key, remove the cascading actions and do your deletes from the referencing tables first. Cascading foreign keys use serializable locks behind the scenes, and when deleting large amounts of data can cause bad blocking scenarios.
If you can change code, do your deletes via stored proc. That's easier than creating plan guides for app code. It gives you many tuning options to help tune down the memory grants as well. You can use MAX_GRANT_PERCENT among other things (recompile, etc.) to get a more appropriate plan.

Making the index change should be your first effort, though.


Reckless behavior of psql \e psql 13.2 executes the query buffer no matter what when exiting the editor, even after quitting it - in which case the buffer may hold the most recently executed SQL query now.
To reproduce:

Start editor (vim in my case) with \e or \e filename from the psql console.
Type something or not, doesn't make a difference.
Cancel edit with :q (or :q! to force it)

Expected behavior: go back to the state before starting the editor. (I cancelled the edit!)
Observed behavior: re-execute the last executed SQL query.
What seems to happen with \e:

it executes (the equivalent of) \p, so the current query buffer contains the previous command when it was empty. (Which is not visible to the user when editing a file!)
The manual  on \p :

\p or \print
Print the current query buffer to the standard output. If the current query buffer is empty, the most recently executed query is printed instead.

Start up the editor. If a filename was provided, load the file, else, load the query buffer.

If the editor ends with a write, copy the result to the query buffer.
If the edit is cancelled (:q in vim), don't overwrite the query buffer. (It holds the previous command now!)

Execute the query buffer. Or, to be precise, do this:

How to prevent query execution after exiting the \e editor in psql?

The behavior seems ok for \p (just copy the query buffer, no harm done), but not for \e, which can execute a command I didn't even get to see.
Is it a bug?
Either way, I would at least like to disable auto-execute when leaving the editor. But that doesn't seem to be possible. Or am I missing something?

This has been fixed in v14 as a reaction to this question. The rest of the answer refers to the pre-v14 behavior:
This is (sort of) documented:

If filename is specified, the file is edited; after the editor exits, the file's content is copied into the current query buffer. If no filename is given, the current query buffer is copied to a temporary file which is then edited in the same fashion. Or, if the current query buffer is empty, the most recently executed query is copied to a temporary file and edited in the same fashion.
The new contents of the query buffer are then re-parsed according to the normal rules of psql, treating the whole buffer as a single line.

If you quit the editor without saving, it is the current query buffer or the most recently executed query that are executed.
I agree with you that this is annoying, and you are not the first person I heard complain.
When editing a script, the behavior clearly violates the “principle of least astonishment”:
test=> \! cat q.sql
SELECT 99;

test=> SELECT 42;
 ?column?
----------
       42
(1 row)

test=> \e q.sql
 ?column?
----------
       42
(1 row)


How do I store phone numbers in PostgreSQL? Let's assume I want to store phone numbers in a database. I may accept phone numbers from outside of the United States. How would I go about storing these phone numbers?

libphonenumber

When possible always use the canonical form. The more normalized the form the better. If there is a standard, use it. For this problem, let's use Google's libphonenumber, by proxy of pg-libphonenumber.

CREATE EXTENSION pg_libphonenumber;

This currently installs the phone_number type which has comparison operators and functions. It stores the number in an international canonical form. This is the best compromise in my opinion.

parse_phone_number('textnumber', 'CountryCode');

Because we can tell when phone numbers equal each other and we provide an internal normal form, we can do this..

SELECT parse_phone_number('03 7010 1234', 'AU') = parse_phone_number('(03) 7010 1234', 'AU');

(returns true). This also means that DISTINCT works so we can do this to get the effect you seem to want above.

CREATE TABLE foo
AS
  SELECT DISTINCT parse_phone_number(ph, 'AU')
  FROM ( VALUES
    ('0370101234'),
    ('03 7010 1234'),
    ('(03) 7010 1234')
  ) AS t(ph);
SELECT 1

That puts in..

 parse_phone_number
--------------------
 +61 3 7010 1234
(1 row)


postgresql configuring wal_archive directory I had a problem with PostgreSQL server.

I took over PostgreSQL server from my former colleague, and today my server crashed due to filled up disk space with wal_archives.

Usually wal segments were put in root directory /wal_archive/, I checked it - its empty. Some time ago I checked it and it had some segments...

I dug deeper and find out that all wal segments were stored in pg_data/pg_xlog directory.

How could this happen? While postgresql.conf is set up like this (showing only enabled segment of 'write ahead log' rows)

wal_level = hot_standby                 # minimal, archive, hot_standby, or
archive_mode = on               # allows archiving to be done
archive_command = 'test ! -f /wal_archive/%f && cp %p /wal_archive/%f'

Why my wal_archive directory now is empty wwhile config settings showing different information?

The wal_archive directory was owned by root user
pg_hba.conf file does not have any replication user - I assume that there is no replication on the server also. There are no files like recovery.conf anywhere.

  cp: cannot stat 'pg_xlog/000000010000033C00000081': No such file or directory
      2018-12-05 14:37:54 EET [7540-1860] LOG:  archive command failed with exit code 1
      2018-12-05 14:37:54 EET [7540-1861] DETAIL:  The failed archive command was: test ! -f /wal_archive/000000010000033C00000081 && cp pg_xlog/000000010000033C00000081 /wal_archive/000000010000033C00000081
      2018-12-05 14:37:54 EET [7540-1862] WARNING:  archiving transaction log file "000000010000033C00000081" failed too many times, will try again later

Also due to panic, I accidentally deleted big chunk of wal segments in xlog directory, to fire up server. Everything is working ok now, but I want to ensure that there will be no problems in the future.

"pg_xlog" is the working directory the WAL files are stored in while PostgreSQL itself still might need the WAL files, i.e. to recovery from a power outage or other "soft" crash.  They are removed from there only once PostgreSQL is sure they are no longer needed for that purpose, and once "archive_command" has successfully completed.

We can't tell what happened to your "/wal_archive/", but here is a plausible story:

You had some kind of network filesystem (NFS, CIFS, etc.) mounted using "/wal_archive" as a mount point.  That mount failed and never got re-established.  That means the original filesystem containing your WAL files is still floating out there somewhere, but the computer no longer know how to access it. You see the former /wal_archive mount point as an empty directory because that is how mounts work on Linux.  The user running your postgres server probably can't write to this residual directory, because when the mount was lost so were the permissions.  So the archive_command has been failing with permissions errors, but you never noticed that because you weren't looking in the server log.  It repeatedly tried to archive the WAL file out of pg_xlog, but repeated failed.  Since it failed, it could never remove the file from pg_xlog, until eventually it filled up.  In a panic, you removed some of the files from pg_xlog.  Now it is still trying to archive that one file, but it is now failing for a different reason, the file is no longer in pg_xlog, as demonstrated by the error message "cp: cannot stat 'pg_xlog/000000010000033C00000081'".

Now that you have it working again, either files should be showing up in the /wal_archive/, or there should be errors in the server log file telling you that the archive_command failed.

Note that the archive_command you are using is not perfectly safe.  It does not fsync the file contents just copied into /wal_archive/ before it reports success, so a poorly-timed crash could mean that the just-archived file doesn't actually exist anywhere.  You might want to switch to a canned solutions like "pgbackrest" or "barman", or using streaming replication, to avoid that kind of pitfall.

Also note that any backups you think you have are likely to be invalid, unless you are also taking logical backups (like with pg_dump).  You better take new ones and test them.


need to use PLSQL to insert distinct rows I need to take some data from Table A, use some logic, and then insert one or more rows into Table B.  I have a PLSQL block that brings in data from Table A with a cursor, performs all the logic required, and inserts the required rows.

The problem is there are duplicate rows in Table B - it's the nature of the beast.  But I need the final result to be a Table B with no duplicates.

It's Oracle so temporary tables are bad form - what's the best way to accomplish this?

If the duplicates are being introduced by the inserts from Table A, then it seems a MERGE would be in order.  A MERGE can INSERT data when the record does not exist in Table B and UPDATE data if the record already exists thus preventing the introduction of duplicates.

FrustratedWithFormsDesign is correct that you probably should have a constraint preventing the introduction of duplicates.  He is also correct in pointing out that this code would probably benefit from Bulk processing.

Phil is correct that if you only need to INSERT data, then a subquery pruning rows from the insert would be preferable.


Postgres SQL on AWS RDS how to grant permission for schema, select on tables to user I have logged in as superuser and created a readonly user.

When I run grant command, it says that schema does not exists

I am logging in as root:

sql -h myhost_name -U root -d postgres -W

grant usage on schema autorsid to readonly ;
ERROR:  schema "autorsid" does not exist

while schema does exists  but schema owner is not root.

Isn't when you are superuser, we should be able to grant permissions for all database/schema/tables etc.

Try grant with double quotes around schema name:

grant usage on schema "autorsid" to readonly ;


T-SQL tool that requires a semicolon to end statements I have what is probably a rather unusual question. Is there a T-SQL tool that requires using a semicolon to terminate statements? I know that it's not required to use semicolons with SQL Server 2008 R1, which is what I'm connecting to, but I actually would like to be forced to use it for running update queries. I'm sure we're all aware of the dangers of writing a query like the one below in the middle of a sql pad, and then highlighting it and running it (which is unfortunately something I tend to do a lot):

UPDATE dbo.Customers
SET ZipCode = '40223'
WHERE Name = 'Bob Loblaw'

... because you only have to miss-select your text once to nuke all your customer's zipcodes!

So, ideally, I'd like to continue doing most of my sql work in SQL Server Management Studio, but have a separate pad open (in a tool that requires semicolons) and only allow myself to write data manipulation queries over there. This would give me great peace of mind, because I'd know I could never miss-select my commonly used update queries. If I did, it wouldn't run, because it wouldn't have a semicolon.

Rather than relying on semicolons (a misplaced semicolon could also cause the problem you are describing), better to rely on transactions.

Set up your query tool to require a commit, and you will have the option rollback if necessary.

In SSMS, you can do this by checking the option SET IMPLICIT TRANSACTIONS under Tools/Options/Query Execution/SQL Server/ANSI. Or, you can turn it on with this command:

SET IMPLICIT_TRANSACTIONS ON

SET IMPLICIT_TRANSACTIONS (Transact-SQL)

  When ON, SET IMPLICIT_TRANSACTIONS sets the connection into implicit
  transaction mode. When OFF, it returns the connection to autocommit
  transaction mode.

  When a connection is in implicit transaction mode and the connection
  is not currently in a transaction, executing any of the following
  statements starts a transaction:

  ALTER TABLE FETCH REVOKE BEGIN TRANSACTION GRANT SELECT CREATE INSERT
  TRUNCATE TABLE DELETE OPEN UPDATE DROP

  If the connection is already in
  an open transaction, the statements do not start a new transaction.

  Transactions that are automatically opened as the result of this
  setting being ON must be explicitly committed or rolled back by the
  user at the end of the transaction. Otherwise, the transaction and all
  of the data changes it contains are rolled back when the user
  disconnects. After a transaction is committed, executing one of the
  statements above starts a new transaction.


Optimize MySQL Table for Charting Our application is collecting data points at different rates (between 500ms - 10,000ms) depending on user settings and that data is queried to plot points on a chart.

I started with the following table structure:

CREATE TABLE `monitored_parameter_data_378` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `command_id` int(11) DEFAULT NULL,
  `data_value` varchar(255) DEFAULT NULL,
  `system_unit` varchar(255) DEFAULT NULL,
  `unit` varchar(255) DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Once we collected over 20,000 points (which will obviously keep growing) performance slowed down quite a bit.  It is taking between 950-1,100ms to get results back so I changed the schema to the following in hopes of benefitting from having the timestamp column indexed:

CREATE TABLE `monitored_parameter_data_378_test` (
  `command_id` int(11) DEFAULT NULL,
  `data_value` varchar(255) DEFAULT NULL,
  `system_unit` varchar(255) DEFAULT NULL,
  `unit` varchar(255) DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `CREATED_IDX` (`created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Unfortunately I am not seeing any improvement in response time after making the change.

For testing and establishing a baseline I am using the following query:

SELECT * FROM <table_name> WHERE created BETWEEN '2017-01-01' AND '2017-03-13';

The indexed version of the table takes between 850ms-1,100ms to return so I am unsure of how best to proceed.  When I run EXPLAIN on the queries I see that the index is being used and that 50% of the data is filtered which is much better than the 11.11% in the non-indexed version.

Does anyone have any insight on how I might take performance to the next level given the fairly simple structure and approach to pulling the data?

Note: the machine in question is Raspberry Pi

Update: I marked @a_vlad's answer as accepted yesterday because based on the original question it is correct.  That said, I rewrote the logic to be done in 3 steps, first I get back the first/last point within the date range along with count of total points, second I calculate the interval between those points to based on the number of points the user wants to plot, last I query for the data by doing the following:

'SELECT * FROM monitored_parameter_data_378 WHERE id >= <variable containing first point in range> AND id <= <variable containing last point in range> AND id MOD <variable containing interval> = 0';

This gets the data back onto a chart in ~4 seconds in a table with 1,000,000 records.

First of all, better to combine both of your structures:

and have PK
and have index for "created"
as a variant, think about a natural PK - created + something which uniquely identifies a row
added - also think about units (system_unit) - if it is identification of your point(?), it may be a good idea to change it to INT format, it reduces memory and disk space usage for this column.

PK could be good for some other queries, and InnoDB still creates it even if you do not define it, but you can not use it in this case.

More importantly, response time depends not only on indexes (of course it MUST HAVE), but also from number of records. You can change the query to:

SELECT count(*) FROM <table_name> WHERE created BETWEEN '2017-01-01' AND '2017-03-13';

and it will show you - how many records the server must return.

The only few ways for feature improvements:

reduce timeframe
reduce number of columns - but this is not your case
faster server - SSD (if still not), increase memory for InnoDB buffer at least (if it is less than necessary)