After truncating a single partition its Primary key's index becomes unusable and all inserts/updates into that partition fail

That's expected, most DDL operations on partitions will invalidate the indexes affected by the DDL. The ALTER TABLE docs state that on all relevant operations.

Specifically for truncate partition:

  For each partition or subpartition truncated, Oracle Database also truncates corresponding local index partitions and subpartitions. If those index partitions or subpartitions are marked UNUSABLE, then the database truncates them and resets the UNUSABLE marker to VALID.

So local indexes are truncated and marked valid. Global indexes are invalidated unless you specifically request that they be maintained. (See further down.)
In your case, it looks like your primary key is not based on a local index – it appears in fact not to be partitioned, since you can't do an alter index ... rebuild on a partitioned index, you need to rebuild each partition. So the invalidation is expected.

To do that, you can add an UPDATE INDEXES / UPDATE GLOBAL INDEXES clause to your ALTER TABLE statement to have Oracle automatically maintain the indexes for you during the ALTER - See Updating Indexes Automatically. There are some limitations though, read the Considerations when Updating Indexes Automatically section carefully.

But that's not always desirable. Sometimes it's more efficient to rebuild the indexes after the load. In that case, your load process can often use the SKIP_UNUSABLE_INDEXES parameter (can be set at session level). (This parameter defaults to TRUE in 11gR2.)


Optimal SQL Server 2012 Disk Configuration

Best practises state that the optimal storage configuration is to have at least three volumes: One for the OS, another for the Database files and the third for transaction logs and to that you should add a fourth one for tempdb.

Sometimes you just don't live in an optional world though. If you cant possibly get a third pair of disks, I would recommend that you benchmark different configuration using SQLIO. In your case you might be able to squeeze more I/O from the raid controller, without sacrificing redundancy by setting all four disks up as a single volume using RAID 1+0 and create four partitions on that for the os, data, log and tempdb. Only a benchmark will tell.


PosgreSQL query slow when sorting on indexed timestamp colum

The index on (cube_id, period_type) cannot be used for the ORDER BY. But it can implement the AND period_type = '1min' very efficiently.

The index on (cube_id, end_time, period_type) can be used for the ORDER BY, but cannot be used efficiently for the AND period_type = '1min'.  It can be used, just not efficiently.  It can filter the rows in the index without visiting the table, but it can't jump to a specific part of the index where it knows all of the '1min' values are located.  (The reason is that all the '1min' values within a given cube_id are not located together)

So, it has to make a choice, efficient ORDER BY or efficient period_type = '1min'.  Perhaps it is making the wrong choice, although you haven't shown evidence of that.

You should create the index (cube_id, period_type, end_time).  Then it does not have to make a choice, it gets the best of both.  It can jump to the part of the index where cube_id = 5 AND period_type = '1min', and once there then it can read the rows in order by end_time.


For a relatively small database, the mysqldump utility can be an effective way to backup the database.

We use a shell script, execution scheduled thru cron

We run separate mysqldump for each database. We omit the "create database" and "drop table" statements. We also include options to get a "consistent" backup of the InnoDB tables. There's some housekeeping work the script does (such as getting the list of databases to backup, etc.) We run the output from mysqldump straight into a compression routine.

For each database, this is what gets run:

  $MYSQLDUMP -h localhost -u root -pTOPSECRET \
  --insert-ignore --single-transaction --quick --tz-utc \
  --routines --no-create-db --skip-add-drop-table $DB \
  | gzip -c >$BACKUPDIR/backup_$BDT.$DBF.sql.gz

Obviously, some variables are set when this gets executed:

$MYSQLDUMP is the full qualified location of the mysqldump executable
$DB is the name of the database
$BACKUPDIR is the directory, and $BDT and $DBF are parts of the file name

The generated .sql file is suitable for running into an empty database; we test restore by moving the file to a different machine, and do restore a clone of the database (into a new database name.)


pg_database_size() is small,but pg_dump is very big

One of them is the binary size on disk. PostgreSQL compresses on disk all TOASTable fields over 2k (like text). Think about a binary value: they usually represent less space on disk than the same value CASTed to text anyway.

SELECT b::text, pg_column_size(b) AS on_disk, length(b::text) AS text
FROM ( VALUES (now()) ) AS t(b)
UNION ALL
  SELECT b::text, pg_column_size(b), length(b::text)
  FROM ( VALUES (49839489::int) ) AS t(b)
UNION ALL
  SELECT b::text, pg_column_size(b), length(b::text)
  FROM ( VALUES ('192.168.43.58'::inet) ) AS t(b);
               b               | on_disk | text
-------------------------------+---------+------
 2017-06-05 20:56:45.978472-05 |       8 |   29
 49839489                      |       4 |    8
 192.168.43.58/32              |      10 |   16
(3 rows)

In addition, you're going to have overhead merely because the SQL, schema, and input format (being CSV or whatever).

You may want to look into the -z option. (Or, xy if you need the best compression).

-Z 0..9
--compress=0..9
  Specify the compression level to use. Zero means no compression. For the custom archive format, this specifies compression of individual table-data
  segments, and the default is to compress at a moderate level. For plain text output, setting a nonzero compression level causes the entire output file
  to be compressed, as though it had been fed through gzip; but the default is not to compress. The tar archive format currently does not support
  compression at all.


Insert trigger function from select query, plus static values pulled from the new.

Your problem is that your query is using new.columnname to mean different things.

In the SELECT list of the subquery, the first new.columnname is intended to use the actual value stored new.columnname. The second new.columnname, the one in the COUNT() and the one in the GROUP BY are all intended not to be the literal value stored in new.columnname, but a reference to the actual OrderDetails column named in new.columnname. In other words, the first is the string literal "format", while the second is the database column OrderDetails.format.

Unfortunately, the engine will view each of new.columnname in the query as a string value "format", and will never interpret it as OrderDetails.format.

There are at least two potential ways to get around this problem:

Explicit INSERTs for each column

For each of the columns in OrderDetails, write a separate INSERT statement in your trigger:

if old.isdropdown <> new.isdropdown then --if there was a change
  if new.isdropdown = true then --and the change was from false to true
    if new.columnname = 'first_column' then
      insert into dropdownstats
      (orderid,columnname,columnvalues,countofcolumnvalues)
        select
        orderid
        , 'first_column'
        , first_column
        , count(*)
        from orderdetails
        group by orderid, first_column;
    elsif new.columnname = 'format' then
      insert into dropdownstats
      (orderid,columnname,columnvalues,countofcolumnvalues)
        select
        orderid
        , 'format'
        , format
        , count(*)
        from orderdetails
        group by orderid, format;
    elsif ...
    end;
  else --the transition was from true to false
    delete from dropdownstats as dd where dd.columnname = new.columnname;
  end if;
end if;

(If there's special handling you want to do if new.columnname doesn't match any of the values, you can use CASE ... WHEN ... ELSE ... END; instead of IF ... THEN ... ELSIF ... THEN ... END;, if you like.)

Dynamic SQL

Unless PostgreSQL has some restriction against it, you could use dynamic SQL to build the SQL statement you need each time through:

if old.isdropdown <> new.isdropdown then --if there was a change
  if new.isdropdown = true then --and the change was from false to true
    EXECUTE format( 'insert into dropdownstats '
                    '(orderid,columnname,columnvalues,countofcolumnvalues) '
                      'select '
                      'orderid '
                      ', %L '
                      ', %I '
                      ', count(*) '
                      'from orderdetails '
                      'group by orderid, %I '
                   ,new.columnname, new.columnname, new.columnname);
  else --the transition was from true to false
    delete from dropdownstats as dd where dd.columnname = new.columnname;
  end if;
end if;

CAVEATS: Code is untested, and PostgreSQL is not my primary SQL language, so I may have included typos or misunderstood some subtleties.


I just answered this recent question : Full text search results in a large amount of time spent in 'FULLTEXT initialization'

Since you are using MyISAM, I would recommend refactoring your query

Here is my proposed query

SELECT id,
title,
company,
state,
city
FROM `jobs`
WHERE MATCH (title, company, state, city) AGAINST
('senior software engineer in san fransisco california')
LIMIT 0,10

SELECT B.id,
B.title,
B.company,
B.state,
B.city
FROM
(SELECT id FROM`jobs`
WHERE MATCH (title, company, state, city) AGAINST
('senior software engineer in san fransisco california')
LIMIT 0,10) A
LEFT JOIN `jobs` USING (id);

GIVE IT A TRY !!!


WHILE Loop for data migration/merge with multiple fields for matching

So I am assuming the tables look something like this (for my easy I am using table variables instead of physical/temp tables):

DECLARE @North TABLE
(
    ID INT NOT NULL,
    FirstName VarChar(20) NULL,
    LastName VarChar(50) NULL,
    Email VarChar(100) NULL,
    [Login] VarChar(20) NOT NULL
)

DECLARE @South TABLE
(
    ID INT NOT NULL,
    FirstName VarChar(20) NULL,
    LastName VarChar(50) NULL,
    Email VarChar(100) NULL,
    [Login] VarChar(20) NOT NULL
)

DECLARE @MigrationPersonLog TABLE
(
    [SourceWfoPersonID] INT NULL
    ,[SourceFirstName] VarChar(20) NULL
    ,[SourceLastName] VarChar(50) NULL
    ,[SourceEmail] VarChar(100) NULL
    ,[SourceLogin] VarChar(20) NULL

    ,[TargetWfoPersonID] INT NULL
    ,[TargetFirstName] VarChar(20) NULL
    ,[TargetLastName] VarChar(50) NULL
    ,[TargetEmail] VarChar(100) NULL
    ,[TargetLogin] VarChar(20) NULL
    ,[Matched] BIT --Matched (= 1) or not (=0)
    ,[MatchArtifact] VarCHar(50) --What was matched or not matched
)

To scsimon's point, SQL really prefers to work in sets instead of iterations.  So anything you can do to avoid loops is preferred in almost all situations.   I think this should solve basically everything you need.  I may not have understood everything you wanted to do with the logging table when it came to inserting new rows into the North table.  But I definitely think this should be a framework you can work with.  I tried to make the code self-documenting but I can always explain something if that is needed.

--Check for all instances where the Login's Match
INSERT INTO @MigrationPersonLog
(
    [SourceWfoPersonID]
    ,[SourceFirstName]
    ,[SourceLastName]
    ,[SourceEmail]
    ,[SourceLogin]
    ,[TargetWfoPersonID]
    ,[TargetFirstName]
    ,[TargetLastName]
    ,[TargetEmail]
    ,[TargetLogin]
    ,[Matched]
    ,[MatchArtifact]
)
SELECT
S.ID,   --[SourceWfoPersonID]
S.FirstName,    --[SourceFirstName]
S.LastName, --[SourceLastName]
S.Email,    --[SourceEmail]
S.[Login],  --[SourceLogin]
N.ID,   --[TargetWfoPersonID]
N.FirstName,    --[TargetFirstName]
N.LastName, --[TargetLastName]
N.Email,    --[TargetEmail]
N.[Login],  --[TargetLogin]
1,  --[Matched]
'Login' --[MatchArtifact]
FROM @South S
    INNER JOIN @North N
        ON S.[Login] = N.[Login]

--Check for All Instances where the Email's Match
INSERT INTO @MigrationPersonLog
(
    [SourceWfoPersonID]
    ,[SourceFirstName]
    ,[SourceLastName]
    ,[SourceEmail]
    ,[SourceLogin]
    ,[TargetWfoPersonID]
    ,[TargetFirstName]
    ,[TargetLastName]
    ,[TargetEmail]
    ,[TargetLogin]
    ,[Matched]
    ,[MatchArtifact]
)
SELECT
S.ID,   --[SourceWfoPersonID]
S.FirstName,    --[SourceFirstName]
S.LastName, --[SourceLastName]
S.Email,    --[SourceEmail]
S.[Login],  --[SourceLogin]
N.ID,   --[TargetWfoPersonID]
N.FirstName,    --[TargetFirstName]
N.LastName, --[TargetLastName]
N.Email,    --[TargetEmail]
N.[Login],  --[TargetLogin]
1,  --[Matched]
'Email' --[MatchArtifact]
FROM @South S
    INNER JOIN @North N
        ON S.Email = N.Email
    LEFT OUTER JOIN @MigrationPersonLog L --This LEFT OUTER JOIN makes sure we don't double log people who match on LoginID and Email
        ON L.[SourceWfoPersonID] = S.ID
WHERE L.[SourceWfoPersonID] IS NULL

--Check for All Instances where the First Name + Last Name Matches
INSERT INTO @MigrationPersonLog
(
    [SourceWfoPersonID]
    ,[SourceFirstName]
    ,[SourceLastName]
    ,[SourceEmail]
    ,[SourceLogin]
    ,[TargetWfoPersonID]
    ,[TargetFirstName]
    ,[TargetLastName]
    ,[TargetEmail]
    ,[TargetLogin]
    ,[Matched]
    ,[MatchArtifact]
)
SELECT
S.ID,   --[SourceWfoPersonID]
S.FirstName,    --[SourceFirstName]
S.LastName, --[SourceLastName]
S.Email,    --[SourceEmail]
S.[Login],  --[SourceLogin]
N.ID,   --[TargetWfoPersonID]
N.FirstName,    --[TargetFirstName]
N.LastName, --[TargetLastName]
N.Email,    --[TargetEmail]
N.[Login],  --[TargetLogin]
1,  --[Matched]
'FirstName+LastName'    --[MatchArtifact]
FROM @South S
    INNER JOIN @North N
        ON (S.FirstName + S.LastName) = (N.FirstName + N.LastName)
    LEFT OUTER JOIN @MigrationPersonLog L --This LEFT OUTER JOIN makes sure we don't double log people who match on First Name + Last Name AND (LoginID and/or Email)
        ON L.[SourceWfoPersonID] = S.ID
WHERE L.[SourceWfoPersonID] IS NULL

--Log all instances which failed previous 3 checks that they failed the checks
INSERT INTO @MigrationPersonLog
(
    [SourceWfoPersonID]
    ,[SourceFirstName]
    ,[SourceLastName]
    ,[SourceEmail]
    ,[SourceLogin]
    ,[TargetWfoPersonID]
    ,[TargetFirstName]
    ,[TargetLastName]
    ,[TargetEmail]
    ,[TargetLogin]
    ,[Matched]
    ,[MatchArtifact]
)
SELECT
S.ID,   --[SourceWfoPersonID]
S.FirstName,    --[SourceFirstName]
S.LastName, --[SourceLastName]
S.Email,    --[SourceEmail]
S.[Login],  --[SourceLogin]
NULL,   --[TargetWfoPersonID]
NULL,   --[TargetFirstName]
NULL,   --[TargetLastName]
NULL,   --[TargetEmail]
NULL,   --[TargetLogin]
0,  --[Matched]
NULL    --[MatchArtifact]
FROM @South S
    LEFT OUTER JOIN @MigrationPersonLog L --This is used to quickly catch anyone who didn't get logged previously.
        ON L.[SourceWfoPersonID] = S.ID
WHERE L.[SourceWfoPersonID] IS NULL

INSERT INTO @North
(
    ID, --Unless this is and Identity and you can ignore it
    FirstName,
    LastName,
    Email,
    [Login]
)
S.ID,   --ID,
S.FirstName,    --FirstName,
S.LastName, --LastName,
S.Email,    --Email,
S.[Login]   --[Login]
FROM @South S
    LEFT OUTER JOIN @MigrationPersonLog L --This is used to quickly catch anyone who didn't get logged previously.
        ON L.[SourceWfoPersonID] = S.ID
WHERE L.[SourceWfoPersonID] IS NULL

Hope this give you what you are looking for.


Organizing a comments table: How to justify the choice?

This is not really an answer, but more of a set of reflections. However it is to long for a comment so I'll add it as an answer anyhow. These are the base tables if I get it right:

CREATE TABLE USER
( USER_ID ... NOT NULL PRIMARY KEY
, ... );

CREATE TABLE MONEY
( MONEY_ID ... NOT NULL PRIMARY KEY
, ... );

CREATE TABLE PROGRESS
( PROGRESS_ID ... NOT NULL PRIMARY KEY
, ... );

Since I don't know your business I just invented some identifier as a primary key. It may very well be a composite key, but for simplicity I do it like this.

Alternative 1:

CREATE TABLE comment
( user_id ... not null references USER (user_id)
, text ...
, created_at ...
, subject_type ... -- 'money' or 'progress'
, subject_id ... -- identifier from 'money' or 'progress'
);

In all essential this is a union type for comments on either MONEY or PROGRESS. It is a construction that does not really fit well in a relational world. One major drawback is that it will not be possible to implement referential integrity (foreign key). I'm not sure what the primary key would be here.

Alternative 2:

I'm not sure I understand what you are saying here. Is MONEY dependent of the COMMENT? I.e.

CREATE TABLE comment (
    id…, -- not unique
    created_at ... not null
    user_id… not null references USER (user_id)
    text ... not null
    CONSTRAINT comment_pk PRIMARY KEY (id, created_at)
);

ALTER TABLE MONEY ADD CONSTRAINT ...
    FOREIGN KEY (MONEY_ID, created_at) -- add created_at in MONEY
    REFERENCES COMMENT (id, created_at);

and similar for PROGRESS. If this is what you mean then the COMMENT will have to exist before MONEY or PROGRESS and this seems counter intuitive to me.

Alternative 3

I would probably change the name to:

CREATE TABLE MONEY_COMMENT
( MONEY_ID ... REFERENCES MONEY (MONEY_ID)
, USER_ID ... REFERENCES USER (USER_ID)
, CREATED_AT ...
, ...
,    PRIMARY KEY (MONEY_ID, USER_ID, CREATED_AT) );

and a similar table PROGRESS_COMMENT. I share your thoughts that this is the most elegant solution. If you are interested in all COMMENTS by a USER you can create a view like:

CREATE VIEW COMMENTS AS
    SELECT 'money' as COMMENT_TYPE
         , MONEY_ID AS COMMENT_ID
         , USER_ID
         , CREATED_AT
         , ...
    FROM MONEY_COMMENT
    UNION ALL
    SELECT 'progress' as COMMENT_TYPE
         , PROGRESS_ID AS COMMENT_ID
         , USER_ID
         , CREATED_AT
         , ...
    FROM PROGRESS_COMMENT


Is there benefit from having 2 tables for 1 entity?

Generally it is best to hold data in a normalised structure.

If testing at scale, on production-grade hardware, proves there is a problem, and that the problem is because of an over-large table, and index tuning and SQL re-factoring do not bring the problem withing acceptable bounds, then splitting the table is a recognised technique.

Moving some rows to a different table is called horizontal partitioning or sharding. Moving some columns to a different table is call vertical partitioning. Both entail additional design and maintenance work. There will be run-time implications if values from several partitions are required by a single query.

There is some further discussion at this answer.


pt-table-checksum to compare two tables?

If you have a Windows workstation and have odbc driver to MySQL, then you might try my utility - there is a free edition. Maybe it will help you.
https://selectcompare.com


There are a couple of problems with this design.

Your link between users and expenses is around the wrong way. As it is designed, a single expense record can have multiple users. This should be a single user can have multiple expense records. Remove expense_id from users and add user_id to expenses (with related foreign keys).
You're going to end up with many NULL values in your expenses table and it will be a pain to query for analysis because of this, you will end up having to coalesce fields for any analysis of total expenses rather than individual expense types. Instead of having separate columns for the gas expense, toll expense and misc expense, have a single expense_value field and create an expense_type field linking to an expense_type lookup table that contains gas, toll and miscellaneous as records
Your personal_salary field should not be an attribute of an expense record, it should be an attribute of the user record. Again, you'll be storing this data repeatedly for no reason. Imagine if you were asked to provide a report on personal salaries only - do you want to query thousands of expense records or a few dozen employee records?
As above but with company_income, this should be an attribute of a company record, not an expense record.

NOTE: With points 3 & 4 above, if personal salary and company income are not annual/on-going static values but are instead values for a specific project or contract then these should still not be stored with expenses. Create a new table for Project/Contract, add a foreign key referencing a project/contract to the expense table and do it that way.

Also, consider your application design. Doing aggregations and calculations across small data sets in Java is fine, but if you're talking about producing annual reports and your data set grows large, this processing would probably be best handled by the database engine which can handle this work in a set-based method rather than row-by-row processing as happens in most applications.


Why is a query with a join and limit clause running so slow, even using a covering index?

Even if both tables have the appropriate indexes on A.id and A_id you still may need an index on B.value to get decent performance.  I've reformatted your query for readability.

SELECT  B.A_id
FROM    B
JOIN    A
ON      B.A_id = A.id
WHERE   B.value IS NULL
LIMIT   1000;

It appears that A_id is a foreign key for a many to one relationship from B to A.  If that is the case we don't need to reference table A as A_id will be NOT NULL if the tables would join.

SELECT  B.A_id
FROM    B
WHERE   B.value IS NULL
AND     A_id IS NOT NULL
LIMIT   1000;

If B.value is rarely NULL you may not 1000 records in B that where B.value is NULL.  You can count the records, but not get a distribution of the records with the query.

SELECT  COUNT(1)
FROM    B
WHERE   B.value IS NULL;

An index on B.value would likely speed up the query.  A limit of 1000 is significantly larger than I have used on a production system.   Try the query with smaller limits like 1, 2, 5, 10, 20, 50 or 100.

Problems with query performance are best investigated with an EXPLAIN PLAN of the query.  This will tell you how the database is executing the query.  I've seen the optimizer choose rather strange plans, usually providing better performance than the plan I expected.  Rarely, the optimizer will choose a really bad plan.  Without the right indexes and statistics, the optimizer may not be able to generate a plan the performs well.


Strategy for Repair Database on SQL Server 2008 R2 Standard

Seems like you have a serious database corruption as CHECKDB points it out

  repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB

The only feasible options are :

Restore from backup or
Repair with data loss => Might leave the database in a transactionally inconsistent state.

In both cases, you will have data loss (provided you have good full and T-log backups before that guy pushed green button instead of blue).

I would go for option 1 => Restore from a know latest good backup !

Refer to IAM pages, IAM chains, and allocation units and IAM page corruption examples

  UAT looks like as with success, no data missing

If you regularly have UAT in sync with PROD, best is to use that dataset to restore PROD - unless you are masking PROD data in UAT.


Get profiles values where message.sender_user_id = profile.user_id

First, what is "wrong" with the data you are returning?

My guess is that you intend to be using an [INNER] JOIN which will only return rows where there is a matching record, not a LEFT JOIN which will return all the rows in the left table regardless of whether there are matches in the right table.

For example:

mysql> select distinct p.display_name, p.first_name, p.last_name, m.sender_user_id
    -> FROM default_profiles p
    -> JOIN default_messages m ON m.sender_user_id = p.user_id;
+--------------------+------------+------------+----------------+
| display_name       | first_name | last_name  | sender_user_id |
+--------------------+------------+------------+----------------+
| Reynier Perez Mira | Reynier    | Perez Mira |              1 |
| User1              | User1      | Lastname1  |              2 |
+--------------------+------------+------------+----------------+
2 rows in set (0.00 sec)

vs

mysql> select distinct p.display_name, p.first_name, p.last_name, m.sender_user_id
    -> FROM default_profiles p
    -> LEFT JOIN default_messages m ON m.sender_user_id = p.user_id;
+--------------------+------------+---------------+----------------+
| display_name       | first_name | last_name     | sender_user_id |
+--------------------+------------+---------------+----------------+
| Reynier Perez Mira | Reynier    | Perez Mira    |              1 |
| User1              | User1      | Lastname1     |              2 |
| Tomas Losis        | Tomas      | Losis         |           NULL |
| Roberto            | Roberto    | Scharffenorth |           NULL |
| webmaster 3wd      | webmaster  | 3wd           |           NULL |
| polar              | polar      | pilsen        |           NULL |
| rubi               | rubi       | acosta        |           NULL |
+--------------------+------------+---------------+----------------+
7 rows in set (0.00 sec)

I would rewrite your provided query like so:

SELECT DISTINCT p.display_name, p.first_name, p.last_name, rcp.*, msg.*
FROM default_profiles p
JOIN default_messages msg ON p.user_id = msg.sender_user_id
JOIN default_recipient rcp ON msg.id = rcp.message_id
WHERE rcp.user_id = 1
  AND rcp.deleted = 0
ORDER BY msg.date DESC;

That being said, in your sample data, both our queries return the same results.  Perhaps there is different data in the full set.


How is it possible to convert a 3-node replica set to a 1-node replica set during low-usage hours

MongoDB replication is to provide automatic fail over during disaster recovery, minimize downtime during maintenance, to use the secondary for analytical use cases and so on. In your scenario, you don't want to avail these benefits during off-peak hours and as far as I know there is no perfect workaround to do these tasks without downtime.

  Simply, shutting down the secondary + arbiter makes Mongo unable to
  elect a new primary and the app fails

Yes, election process will not happen in a replica set with single node. It needs at least two mongod nodes which might not be present in your case if you remove secondary and an arbiter.

  If the best/only solution is to remove the secondary and arbiter from
  the replica set

You can remove the secondary and arbiter from the replica set by following the steps in this link mongo-replica-set-to-single-server.

  what are the consequences of doing when adding them
  back on the next peak hours cycle?

While adding back the replica sets, you will have to restart mongod instances, issue rs.reconfig() method and it can force the current primary to step down which causes an election. During this window time, your app will not read/write anything from/into database which can last up to 10-30 seconds (for re-configuring replicas), another 20-30 seconds (for restarting mongod instances) based on network bandwidth and replica set configuration. Hence, it is not recommended during peak hours.


SQL Server: Clustered index, sorting and pagination

First I need to reiterate what RDFozz said in his answer about including an explicit ORDER BY. If SQL Server does an allocation order scan you may get the wrong results. Including ORDER BY in the query doesn't cause a performance hit. Why not do it?

From a query performance perspective, the index that you want depends on how many rows you return at a time and how many columns are actually needed from the table.

First I'll throw around 6.5 million rows into a table with six tenants:

CREATE TABLE dbo.Users2 (
    IsDeleted Bit NOT NULL,
    [AccountId] UNIQUEIDENTIFIER NOT NULL,
    [LastName] NVARCHAR(50) NOT NULL,
    [UsefulColumn] NVARCHAR(20) NOT NULL,
    [OtherColumns] NVARCHAR(100) NOT NULL
);

CREATE CLUSTERED INDEX [idx] ON [Users2]
(
     IsDeleted ASC,
     [AccountId] ASC,
     [LastName] ASC
);

CREATE TABLE #ids (id INT NOT NULL IDENTITY (0, 1), [AccountId] UNIQUEIDENTIFIER NOT NULL);
INSERT INTO #ids
SELECT TOP 6 NEWID()
FROM master..spt_values;

INSERT INTO [Users2] WITH (TABLOCK)
SELECT
CASE WHEN t1.number % 10 = 1 THEN 1 ELSE 0 END
, #ids.[AccountId]
, LEFT(REPLACE(CONVERT(NVARCHAR(50), NEWID()), '-', ''), 12)
, REPLICATE(N'Z', 20)
, REPLICATE(N'Z', 100)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
LEFT OUTER JOIN #ids ON ABS(t1.number % 6) = #ids.id;

DROP TABLE #ids;

-- get an ID: FFA7D6D8-63E8-422B-B5E7-F7020871CDB4
SELECT TOP 1 [AccountId] FROM Users2
WHERE IsDeleted = 0
ORDER BY [AccountId] DESC;

When running a query similar to yours:

SELECT TOP (20) *
FROM Users2
WHERE IsDeleted = 0
AND AccountId = 'FFA7D6D8-63E8-422B-B5E7-F7020871CDB4'
ORDER BY [LastName];

I get a clustered index seek as expected:

Is using the clustered index the best option? It depends. If you don't need to select every column from the table then you can define a smaller covering index which returns the data you need without an explicit sort. Having a smaller covering index is good for performance as you page out further into the data. Suppose that you only need UsefulColumn and not the OtherColumns column. You could define the following index:

CREATE NONCLUSTERED INDEX [idx_1] ON [Users2]
(
     [AccountId] ASC,
     [LastName] ASC
)
INCLUDE ([UsefulColumn])
WHERE IsDeleted = 0;

It's pretty large. For my test case it's around 28% of the size of the data. For this index, it's important to note that changing the clustered key of the table will not have a big impact on its size. SQL Server stores the clustered key columns in the leaf node of the index unless they are already included in the index. This can be demonstrated with a simple test:

CREATE TABLE dbo.IX_TEST (
    COL1 BIGINT NOT NULL,
    COL2 BIGINT NOT NULL,
    FILLER VARCHAR(6) NOT NULL,
    PRIMARY KEY (COL1)
);

INSERT INTO dbo.IX_TEST WITH (TABLOCK)
SELECT TOP (1000000)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, REPLICATE('Z', 6)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

EXEC sp_spaceused 'IX_TEST'; -- 96 KB

CREATE INDEX COL1 ON dbo.IX_TEST (COL1)
EXEC sp_spaceused 'IX_TEST'; -- 14032 KB

CREATE INDEX COL2 ON dbo.IX_TEST (COL2)
EXEC sp_spaceused 'IX_TEST'; --- 35920 KB

Going back to our table, if I created an index on just the UsefulColumn column there would be an overhead (with no compression) of approximately 1 byte for the IsDeleted column, 16 bytes for the AccountId, 2 * the average length of the LastName column, and 0 or 4 bytes for the internal uniquifier (only happens for duplicate last names). For my test data that's quite a bit of overhead:

  1 + 16 + 2 * 12 + 0 = 41 bytes

However for the idx_1 index I defined above it's around only 1 bytes (1 for IsDeleted and 0 for the uniquifier, assuming not very many duplicate last names). The index is mainly large because I'm using wide columns as key columns. Keep in mind that the indexes will be the same size with your current clustering key, but changing the clustering key of the table to a thinner set of columns will greatly decrease the size of the index only defined on UsefulColumn.

For any TOP value I should get a nice index seek on the covering index. This query:

SELECT TOP (200000) [LastName], [UsefulColumn]
FROM Users2
WHERE IsDeleted = 0 AND AccountId = 'FFA7D6D8-63E8-422B-B5E7-F7020871CDB4'
ORDER BY [LastName];

Has the following plan:

Even if you need every single column from the table the index defined above can still give good enough performance. You'll get a key lookup for every row that you return. For a single end user, doing key lookup on 20 rows it shouldn't be noticeable. In your test you saw execution times of 3 ms and 18 ms. However, if you have a highly concurrent workload it could make a difference. Only you can evaluate that properly.

Even without the caveats above, when selecting many rows you may notice a large performance difference:

The first query has an index hint to force the use of the index. The query optimizer thinks that plan will be much more expensive than the plan that uses the clustered index.

If I had to guess, I would say that you probably don't need to use a clustered index to perform your paging. If you have a lot of indexes on the table you might benefit from creating a new covering index for your pagination queries and by defining the clustered index on a smaller, unique set of columns.


SQL Server Log Shipping: Where is the hottest IO during RESTORE LOG: To db or to log?

The data is in:

Improving speed of the LDF storage makes a dramatic improvement in log restore time.

Thank you all for the input!

What I did:

I am using Rackspace Cloud servers. Not their "SQL Server" instances, just plain jane Win 2008 images, that I load SQL 2008 onto. For storage I use Rackspace block storage. I also evaluated Amazon EC2 with EBS (the Amazon block storage).

The core questions I was attacking:

1) Is it viable to run SQL Server on a cloud server for a 300+ GB database?

2) Is there benefit for the extra spend to get the Rackspace SSD based block storage? (It is rather pricey).

The methodology I used:

I did two kinds of testing:

i) SQL IO test tools, to measure disk performance of the block storage, and to compare to dedicated hardware (both "on the metal" and in virtual machines)

ii) An actual restore of a big database, and automated log shipping from the production database.

Answers:

a) Rackspace regular block storage is much faster than Amazon AWS (EBS). Much much.

b) Based on my measurements and experience, I would not put a production SQL Server DB onto Amazon EBS. There are just too many "pauses" and other bad performance issues. Note that SQL Server will work on EC2 w EBS -- many folks do it -- just that based on my measurements there will be IO delays (a lot of them). This will particularly impact RESTORE LOG operations, and all write-intensive operations (reads can be cached... but LDF writes are not cached...).

c) Rackspace cloud with regular block storage can absolutely be used for SQL Server. It is not mind blowing fast, but it is great for reads, and OK for writes.

d) Rackspace SSD block storage makes a dramatic difference for log restore operations, and (I extrapolate here), any write-intensive use of SQL server will get a big boost.

So the bad news is: When you are pushing around a 500gb database, you can't get the performance of a $150K SAN for $300/month.

The good news is: You can get really decent performance, that matches or beats RAID5 from a virtual machine on dedicated hardware, for about $480/month

The Data

Here is the data of the log restore operation, showing first the duration of log restore operations on the slower system, then on the faster system, and then the difference. This log restore is happening every 10 mins on both systems.


Reduce number of rows with same id repeated based on status

The only trick here is to create something you can group by that involves the year, and the quarter. This isn't the only way to do it, but you can do this...

make_timestamp(posted_year,1,1,0,0,0)::date
+ posted_quarter*3*'1 month'::interval

Remember, three months in a quarter-year. You can also do posted_year + posted_quater*0.25 but all the same.

From there, it's pretty basic. I'll use a CTE to separate formatting from calculation (feel free to ditch it for speed).

DISTINCT ON()

WITH t AS (
  SELECT (make_timestamp(posted_year,1,1,0,0,0)::date + posted_quarter*3*'1 month'::interval)::date AS qtr, *
  FROM foo
)
SELECT DISTINCT ON ( location_id, qtr ) location_id, qtr, status
FROM t
ORDER BY location_id, qtr, status='active' DESC;

GROUP BY ... bool_or()

Or, alternatively (and maybe faster),

WITH t AS (
  SELECT (make_timestamp(posted_year,1,1,0,0,0)::date + posted_quarter*3*'1 month'::interval)::date AS qtr, *
  FROM foo
)
SELECT location_id,
  qtr,
  CASE WHEN bool_or(status='active') THEN 'active' ELSE 'inactive' END
FROM t
GROUP BY location_id, qtr
ORDER BY location_id, qtr;


SQL Server considerations when backing up / restoring a database that uses replication

The answer depends on your type of replication. For example, merge replication allows you to update in more than one source DB, and then distribute those updates. With this topology, you would of course need backups everywhere data originates.

As for automatic syncing, I've seen that most of the time if there is any issue with repl on either end of publisher / subscriber, you will usually have to reinitialize the repl. An exception to this might be when you have a subscriber problem such that it is offline to the distributor. In that case the distributor would "catch up" the subscriber when it came back up IF there was no corruption (bad sequence) at the subscriber.

As a rule, back everything up, but don't count on being able to "jump-start" repl back to a functional state. Re-initializing it is common, and painful with large DBs.


Bulk insert into SQL Server from VMWare guest using distributed switch

I would suggest using Wireshark or Microsoft NetMon and sniffing the traffic to see what is happening here.  Could also be related to the TCP Chimney Offload feature, assuming something has changed at the NIC level:

http://support.microsoft.com/kb/942861


Trouble killing sessions then immediately dropping users in Oracle 11g XE

When you issue an ALTER SYSTEM KILL SESSION, Oracle merely marks the session as killed and does the actual work of killing the session asynchronously.  That may take just a couple seconds, it may take many hours if the session has an uncommitted transaction that did a lot of work that now has to be rolled back or if the session needs to stay around in a killed state in order to notify the client that their session was terminated.  It may, therefore, require a substantial amount of waiting before a user could be dropped.

You could do something like this where your loop will wait indefinitely for all the sessions to disappear.

DECLARE
  l_cnt integer;
BEGIN
  EXECUTE IMMEDIATE 'alter user abc_user account lock';
  FOR x IN (SELECT *
              FROM v$session
             WHERE username = 'ABC_USER')
  LOOP
    EXECUTE IMMEDIATE 'alter system kill session ''' || x.sid || ',' || x.serial# || '''';
  END LOOP;

  -- Wait for as long as it takes for all the sessions to go away
  LOOP
    SELECT COUNT(*)
      INTO l_cnt
      FROM v$session
     WHERE username = 'ABC_USER';
    EXIT WHEN l_cnt = 0;
    dbms_lock.sleep( 10 );
  END LOOP;

  EXECUTE IMMEDIATE 'drop user abc_user cascade';
END;

Generally, though, I'd question the problem you're trying to solve.  I can't think of many times that it would make sense to drop a user that has active sessions at the time you decide to issue the drop.  In the vast majority of cases, the presence of active sessions strongly implies that the account should not be dropped.


Your data:

CREATE TABLE dbo.WEIRD_ORDERING
(
ID1 INT NULL,
ID2 INT NULL
);

INSERT INTO dbo.WEIRD_ORDERING VALUES
(242, NULL),
(243, NULL),
(244, NULL),
(252, 242),
(254, NULL),
(255, NULL),
(256, NULL),
(292, NULL),
(308, NULL),
(311, NULL),
(313, 311),
(314, 311),
(323, NULL),
(324, 311),
(335, 242),
(340, NULL),
(341, NULL),
(358, NULL),
(372, NULL),
(373, NULL),
(377, NULL),
(378, 358),
(379, 358),
(380, 358),
(381, 358);

This gives you results in the desired order:

SELECT ID1, ID2
FROM
(
    SELECT ID1, ID2, ID1 AS ORDERING_COLUMN
    FROM dbo.WEIRD_ORDERING
    WHERE ID1 <= ID2 OR ID2 IS NULL

    UNION ALL

    SELECT ID1, ID2, ID2 AS ORDERING_COLUMN
    FROM dbo.WEIRD_ORDERING
    WHERE ID1 > ID2 OR ID1 IS NULL
) q
ORDER BY ORDERING_COLUMN, ID1;

db fiddle


Okay, so after following the clues given by loicmathieu and jstell, and digging it up a little, these are the things I found out about MongoDB using WiredTiger storage engine. I'm putting it here if anyone encountered the same questions.

The memory usage threads that I mentioned, all belonged to 2012-2014, all pre-date WiredTiger and are describing behavior of the original MMAPV1 storage engine which doesn't have a separate cache or support for compression.

The WiredTiger cache settings only controls the size of memory directly used by the WiredTiger storage engine (not the total memory used by mongod). Many other things are potentially taking memory in a MongoDB/WiredTiger configuration, such as the following:

WiredTiger compresses disk storage, but the data in memory are uncompressed.
WiredTiger by default does not fsync the data on each
commit, so the log files are also in RAM which takes its toll on
memory. It's also mentioned that in order to use I/O efficiently,
WiredTiger chunks I/O requests (cache misses) together, that also
seems to take some RAM (In fact dirty pages (pages that has
changed/updated) have a list of updates on them stored in a
Concurrent SkipList).
WiredTiger keeps multiple versions of records in its cache (Multi
Version Concurrency Control, read operations access the last
committed version before their operation).
WiredTiger Keeps checksums of the data in cache.
MongoDB itself consumes memory to handle open connections, aggregations, serverside code and etc.

Considering these facts, relying on show dbs; was not technically correct, since it only shows the compressed size of the datasets.

The following commands can be used in order to get the full dataset size.

db.getSiblingDB('data_server').stats()
# OR
db.stats()

This results is the following:

{
    "db" : "data_server",
    "collections" : 11,
    "objects" : 266565289,
    "avgObjSize" : 224.8413545621088,
    "dataSize" : 59934900658, # 60GBs
    "storageSize" : 22959984640,
    "numExtents" : 0,
    "indexes" : 41,
    "indexSize" : 7757348864, # 7.7GBs
    "ok" : 1
}

So it seems that the actual dataset size + its indexes are taking about 68GBs of that memory.

Considering all these, I guess the memory usage is now pretty expected, good part being it's completely okay to limit the WiredTiger cache size, since it handles I/O operations pretty efficiently (as described above).

There also remains the problem of OOM, to overcome this issue, since we didn't have enough resources to take out mongodb, we lowered the oom_score_adj to prevent OOM from killing important processes for the time being (Meaning we told OOM not to kill our desired processes).


See the documentation:
ALTER SEQUENCE dogs_id_seq SET SCHEMA ani;


SQL Profiler plus Include Actual Execution Plan

FWIW, the missing index info is there is is just Profiler that doesn't bother to show it. If you dig into the underlying XML you will see it. Ans since Profiler has been deprecated and MS haven't done any work on it since 2010, we can't expect any change here. If you use Extended Events, the "new" (if we consider 2008 being new) trace engine, then you will find that the missing index is there as well. But the GUI for XE doesn't show the graphical representation for the plan in the first place, only the raw XML. I suggest you follow Erik's recommendation, most probably a more productive path.


If you are running with binary logs enabled this can be checked with relatively high reliability.

First, to see whether binary logs are indeed enabled, run:

SHOW BINARY LOGS;

If they're enabled, you should get an output like this:

+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000244 |  15462544 |
| mysql-bin.000245 | 102622775 |
+------------------+-----------+

Otherwise you would get an error message.

Now, if binary logs are enabled, then any successful commit is also written to binary logs. I say "commit", but the truth is any successful operation, even on non transactional tables such as MyISAM, is written there. But, to be honest, to have any certainty about the outcome of your queries, I do hope for your sake you're using a transactional engine such as InnoDB, or else you cannot be sure of anything.

OK, so asssuming now that you have binary logs turned on, and that your tables are transactional (hopefully InnoDB), then a successful completion of your queries is expected to be written to the binary logs.

You must now hunt down the relevant binary log and look for the query there. If you find the query - good! If not - it's most probably not there. I'll explain shortly.

Which binary log contains your query? Look at the binary log files themselves, typically in your data directory. Look for their timestamps. When the power came up, a new binary log was created. Find it. Your queries are most probably in the binary log one before that one. This is a guess. It could also be one before that, etc. But it's a good guess.

Now, using the mysqlbinlog utility, execute, from command line, something like this:

mysqlbinlog mysql-bin.000245

Replace file name with the one you suspect to contain the query.

This will output all queries in this binary log file into standard output. On Unix, use grep to find your query:

mysqlbinlog mysql-bin.000245 | grep "something which identifies the query"

On Windows, good luck. Open with notepad++ or something and search manually.

Is the query there? Great - you know it has been committed.

Is the query not there? Need to check on sync_binlog param. Is it 1? Then query not in binary log ==> query not committed.
But if sync_binlog is not 1, there could still be a chance that the query has been committed yet not in the binary log, as the crash may have happened just after the commit and just before the binary log was flushed to disk. You then need to revert to other means.

Those being: (and hopefully, again, you're using InnoDB): look for a single row that can identify outcome of the query. With InnoDB you get "all or nothing". If you can be certain of a single row affected by the query - you can be sure that the query has completed.

edit: of course, if slow log is enabled, you can expect such long query to be logged there after completion as well...

Good luck!


You might prefer something like this:

select *
from foobar
where (subject,term) in ( ('STAT','111')
                         ,('STAT','222')
                         ,('ENGLISH','555')
                         ,('COMM','444')
                         ,('COMM','333')
                         ,('STAT','222')
                         ,('STAT','666')
                        );

DBFiddle here


If you are using transactions, you can rollback your transaction.

If you are looking to restore your database, the answer is no. Providing that you are in Full Recovery model and have log backups, you can restore to a point in time, such as LSN, Time or Marked Transaction, but you cannot roll forward your restore on a session by session basis as this could leave the database in an inconsistent state.

Imagine your session deleted some rows from a table...What if another session had created those rows and you did not roll forward the other session? It just wouldnt work.

You could consider using Database Snapshots moving forward. This will give you a point-in-time copy of the database which you can use to "undo" data modifications. For example if your session deleted some rows, you could do an INSERT into your database, SELECT from snapshot


Postgres inherit from one role and not another

I solved this by nesting the two roles together.

    CREATE role elevated_role nologin noinherit;

    GRANT ALL PRIVILEGES ON ALL tables IN SCHEMA db to elevated_role;
    GRANT usage ON SCHEMA db to elevated_role;

    CREATE role normal_role nologin noinherit;
    GRANT usage ON SCHEMA db to normal_role;
    GRANT select on ALL tables in SCHEMA db to normal_role;

    GRANT elevated_role TO normal_role;

    CREATE new_user with password 'some_pass';
    GRANT normal_role TO new_user;

This allows me to manage the users in a group by just adding and deleting them from the group but gives them separate roles that they have to explicitly set if they want to insert, delete, etc.


Assuming I understand what you're looking to return, and assuming subscriber.id is unique in the table (seems likely), here is a SQL Server answer (though it should be adaptable to MySQL):

select
    isnull(sum((case when s.ispostpaid = 0 and s.subscriber_status = 'active' then 1 else 0 end)), 0) as prepaid,
    isnull(sum((case when s.ispostpaid = 1 then 1 else 0 end)), 0) as postpaid
    from subscriber s;

This selectively counts the rows of interest with one table access instead of an expensive cross join and grouping in the original query. The use of ISNULL is to still return counts of 0 if the table is empty.

Using NHibernate, I believe this could also be written using ICriteria... but it's been a while for me on that. Check out my answer here, as it might help if that's what you're looking for.


Getting both Having and Not Having in same row

You can use a case expression like:

SELECT count(case when cnt = 1 then 1 end) as one_order
     , count(case when cnt > 1 then 1 end) as more_than_one_order
FROM (
    select customer_id, count(1) as cnt from orders GROUP BY customer_id
) as T

You can also use a filter clause for the count aggregates:

SELECT count(1) filter (where cnt = 1) as one_order
     , count(1) filter (where cnt > 1) as more_than_one_order
FROM ...

For the third attribute you can either reuse the aggregates:

select count(1) filter (where cnt = 1) as ...
     , count(1) filter (where cnt > 1) as ...
     , count(1) filter (where cnt = 1) / count(1) filter (where cnt > 1) as ...

or add another level of nesting:

select one_order
     , more_than_one_order
     , one_order / more_than_one_order
from (
    SELECT count(1) filter (where cnt = 1) as one_order
         , count(1) filter (where cnt > 1) as more_than_one_order
    FROM (
        select customer_id, count(1) as cnt
        from orders
        GROUP BY customer_id
    ) as T1
) as T2


Optimize PostgreSQL for a lot of INSERTS and bytea updates

For insert performance, see speeding up insert performance in PostgreSQL and bulk insert in PostgreSQL.

You're wasting your time with JDBC batching for insert. PgJDBC doesn't do anything useful with insert batches, it just runs each statement. <-- This is no longer true in newer PgJDBC versions, which can now batch prepared statements to reduce round-trip times considerably. But it's still better to:

Use COPY instead; see PgJDBC batch copy and the CopyManager. As for number of concurrent loaders: Aim for a couple per disk, if the operations are disk I/O bound. Eight is probably the most you'll want.

For your "production mode" I suggest loading a sample of data, setting up the queries you expect to run, and using explain analyze to investigate performance. For testing purposes only, use the enable_ params to explore different plan selections. Set the query planner cost parameters (random_page_cost, seq_page_cost, effective_cache_size, etc) appropriately for your system, and make sure shared_buffers is set appropriately.  Continue to monitor as you add a simulated production workload, using the auto_explain module, log_min_duration_statement setting, the pg_stat_statements extension, etc.

For details, see the PostgreSQL user manual. I suggest popping back here when you have a more concrete problem with explain analyze query execution details, etc.


Easy Postgresql master/slave replication with automatic failover on Linux?

Have a look at repmgr. It's a free software product that is meant to manage failovers and switchovers with PostgreSQL built-in replication.


What is the rationale of dropping all indexes for an altered view in SQL Server?

Kendra Little lays out some good theories for why this happens in her recent blog post: Altering an indexed view in SQL Server drops all indexes

  This behavior makes some sense when I think about how indexed views are implemented

  When you create an index on a view (we’re talking views using classic disk-based tables here), it materializes the view — in other words, it stores the data as if it is a table. That data is stored in rows on 8KB pages. These pages can fill up. So if you add a column to a view, just like adding a column to a table, the database engine needs to rewrite those pages to add the new column to each and every row (depending on datatypes and nullability, etc etc).

  Also, it’s quite possible that when altering an indexed view, the alteration makes the view no longer adhere to the many rules required for indexed views.

Altering the view could completely change the structure of the data - number of columns, number of rows, the size of the rows, etc.  So basically the table would need to be dropped and recreated anyway.

I find the second point, about how likely a change is to run afoul of the indexed view restrictions, to be particularly compelling.

Like Kendra mentions later, and others have mentioned in the comments here, it would be nice if one were simply blocked from altering indexed views.  But that change will likely never happen due to backwards compatibility concerns.


The problem is in your WHERE clause.  This piece of code in your WHERE clause for example.

and osnArtikel.SpletObjava='D'

is going to restrict your output whenever osnArtikel.SpletObjava is NULL.  Including whenever there wasn't a match on it.  (your OUTER results)

One method to fix it is to move your restrictions into the ON clause.  Not generally something you want to do since the ON clause is a JOIN but with OUTER JOINs it's not unreasonable.

select stranka.SifraStranke,
--  stranka.DomacaStran,
--  strFaktura.SifraGrucePopusti,
--  popust.OdstotekPopusta,
--  popust.SifraGruceArtikla,
--  artProdaja.SifraArtikla,
    osnArtikel.SifraArtikla,
    cene.ProdajnaCena * ((100-popust.OdstotekPopusta)/100)

from STRStrankeSplosno stranka      -- customer basic data table
left join
    STRStrankeFaktura strFaktura    -- customer financial data table
    on stranka.SifraStranke=strFaktura.SifraStranke
    and strFaktura.StevilkaSifranta=2
    and strFaktura.SifraGrucePopusti<>''
left join
    ARTPopustiStranke popust        -- customer discount data table for each discount group
    on strFaktura.SifraGrucePopusti=popust.SifraGruceStranke
    and popust.StevilkaSifranta=2
left join
    ARTArtikliProdaja artProdaja    -- item discount group data table
    on popust.SifraGruceArtikla=artProdaja.SifraGrucePopust
    and artProdaja.StevilkaSifranta=2
full outer join
    ARTArtikliOsnovniP osnArtikel   -- item basic data table
    on osnArtikel.SifraArtikla=artProdaja.SifraArtikla
    and osnArtikel.SpletObjava='D'

cross apply (
    select top 1 cene.ProdajnaCena
    from ARTArtikliCeneCP cene      -- item prices data table
    where cene.SifraArtikla=osnArtikel.SifraArtikla
    and cene.SifraCenovnegaPodrocja='MC'
    order by cene.DatumZacetkaVeljavnosti desc
    ) cene
where stranka.DomacaStran<>''
and stranka.StevilkaSifranta=2
order by stranka.SifraStranke, osnArtikel.SifraArtikla

Another method is to include checks for the column being NULL in your WHERE clause.  This may or may not be the way you want to go because if the column  you are checking is also nullable you could end up with some unintended results.  The best method is to check if a non-null column (primary key column for example) is nullable.

select stranka.SifraStranke,
--  stranka.DomacaStran,
--  strFaktura.SifraGrucePopusti,
--  popust.OdstotekPopusta,
--  popust.SifraGruceArtikla,
--  artProdaja.SifraArtikla,
    osnArtikel.SifraArtikla,
    cene.ProdajnaCena * ((100-popust.OdstotekPopusta)/100)

from STRStrankeSplosno stranka      -- customer basic data table
left join
    STRStrankeFaktura strFaktura    -- customer financial data table
    on stranka.SifraStranke=strFaktura.SifraStranke
left join
    ARTPopustiStranke popust        -- customer discount data table for each discount group
    on strFaktura.SifraGrucePopusti=popust.SifraGruceStranke
left join
    ARTArtikliProdaja artProdaja    -- item discount group data table
    on popust.SifraGruceArtikla=artProdaja.SifraGrucePopust
full outer join
    ARTArtikliOsnovniP osnArtikel   -- item basic data table
    on osnArtikel.SifraArtikla=artProdaja.SifraArtikla

cross apply (
    select top 1 cene.ProdajnaCena
    from ARTArtikliCeneCP cene      -- item prices data table
    where cene.SifraArtikla=osnArtikel.SifraArtikla
    and cene.SifraCenovnegaPodrocja='MC'
    order by cene.DatumZacetkaVeljavnosti desc
    ) cene
where stranka.DomacaStran<>''
and stranka.StevilkaSifranta=2
and (osnArtikel.PrimaryKeyColumn IS NULL
        OR osnArtikel.SpletObjava='D' )
and (strFaktura.PrimaryKeyColumn IS NULL
    OR (strFaktura.StevilkaSifranta=2
        and strFaktura.SifraGrucePopusti<>'')
    )
and (popust.PrimaryKeyColumn IS NULL
    OR popust.StevilkaSifranta=2)
and (artProdaja.PrimaryKeyColumn IS NULL
    OR artProdaja.StevilkaSifranta=2)
order by stranka.SifraStranke, osnArtikel.SifraArtikla


What's the point of the relationship diamond in Entity-Relationship diagrams?

As James K already said, there is more than one set of rules for forming ERDs, and more than one answer to your question.  But, to deal with your question from a generic point of view, here goes.

Sometimes the diamond is irrelevant.  The line between two boxes depicts the relationship, and often says everything that needs to be said.  Here are some cases where the diamond makes the diagram more legible.

First, relationships with attributes.  Some ERD diagrammers allow attributes to describe relationships in addition to entities.  For example, if an enrollment is a relationship between a student and a course, then the grade the student got might be an attribute of the relationship.  It is certainly not an attribute of just the student, or of just the course.  If you follow this practice,  and if you list attributes inside of boxes, it behooves you to put a diamond in for the enrollment relationship and to put the grade attribute inside of the diamond.

Some ER diagrammers prefer to make enrollment into an entity, instead of leaving it as a relationship.  In that case, the diamond becomes a box.

Second, some relationships are not binary, but ternary, quaternary, or n-ary.  That is, instead of one line connecting two boxes, there are several lines connecting 3, 4, or more boxes.  In this case, those lines all come together somewhere.  It behooves you to put a diamond in where they all come together.

As an example, an airline reservation might have a line connecting it to the passenger, another line connecting it to the flight, and a third line connecting it to the airport where the passenger will board.

Again, some diagrammers prefer to to make n-ary relationships into entities.  And again, that means using a box in place of a diamond.

There's a fancy word for turning a relationship into an entity.  It's called "reifying".

Having said all that, the majority of ERDs I've ever made for myself have no diamonds on them.


Update Column from Recursive CTE (MySQL/MariaDB)

You can use any query typo with the created (with statements
The update can be de by joining the original with the cnstructed

CREATE tABLE tbl_1min(id int, close int,ema9 varchar(30))

INSERT INTO tbl_1min VALUES (1,1,'test')

SET @NDays := 9;
WITH RECURSIVE anchor AS
    (
        SELECT
            a1.id,
            a1.close,
            a1.ema9
        FROM
            tbl_1min a1
),
x AS
    (
        SELECT
            anchor.id,
            anchor.close,
            x2.ema9 AS PrevEma,
            anchor.ema9,
            anchor.ema9 AS NewEma
        FROM
            anchor
        INNER JOIN
            anchor x2
        ON
            anchor.id - 1 = x2.id
        WHERE anchor.id = @NDays -- FIRST SEED ROW
UNION
   SELECT
    anchor.id,
    anchor.close,
    x.PrevEma,
    anchor.ema9,
    (((2 / (1 + @NDays)) * (anchor.close - x.PrevEma)) + x.PrevEma) AS NewEma
    FROM
        x
    JOIN anchor
    ON anchor.id = x.id + 1   -- `X` WALKS ITSELF
    )
UPDATE tbl_1min t1
        INNER JOIN
    x ON t1.id = x.id
SET t1.ema9 = x.NewEma;

SELECT * FROM tbl_1min

id | close | ema9
-: | ----: | :---
 1 |     1 | test

db<>fiddle here


Why is the secondary selective index not used when the where clause filters on `value()`?

The declaration of singleton in the path expression of the index enforces that you can not add multiple <Number> elements but the XQuery compiler does not take that into consideration when interpreting the expression in the value() function. You have to specify [1] to make SQL Server happy. Using typed XML with a schema does not help with that either. And because of that SQL Server builds a query that uses something that could be called an "apply" pattern.

Easiest to demonstrate is to use regular tables instead of XML simulating the query we are actually executing against T and the internal table.

Here is the setup for the internal table as a real table.

create table dbo.xml_sxi_table
(
  pk1 int not null,
  row_id int,
  path_1_id varbinary(900),
  pathSQL_1_sql_value int,
  pathXQUERY_2_value float
);

go

create clustered index SIX_T on xml_sxi_table(pk1, row_id);
create nonclustered index SIX_pathSQL on xml_sxi_table(pathSQL_1_sql_value) where path_1_id is not null;
create nonclustered index SIX_T_pathXQUERY on xml_sxi_table(pathXQUERY_2_value) where path_1_id is not null;

go

insert into dbo.xml_sxi_table(pk1, row_id, path_1_id, pathSQL_1_sql_value, pathXQUERY_2_value)
select T.ID, 1, T.ID, T.ID, T.ID
from dbo.T;

With both tables in place you can execute the equivalent of  the exist() query.

select count(*)
from dbo.T
where exists (
             select *
             from dbo.xml_sxi_table as S
             where S.pk1 = T.ID and
                   S.pathXQUERY_2_value = 314 and
                   S.path_1_id is not null
             );

The equivalent of the value() query would look like this.

select count(*)
from dbo.T
where (
      select top(1) S.pathSQL_1_sql_value
      from dbo.xml_sxi_table as S
      where S.pk1 = T.ID and
            S.path_1_id is not null
      order by S.path_1_id
      ) = 314;

The top(1) and order by S.path_1_id is the culprit and it is [1] in the Xpath expression that is to blame.

I don't think it is possible for Microsoft to fix this with the current structure of the internal table even if you were allowed to leave out the [1] from the values() function. They would probably have to create multiple internal tables for each path expression with unique constraints in place to guarantee for the optimizer that there can only be one <number> element for each row. Not sure that would actually be enough for the optimizer to "break out of the apply pattern".

For you who think this fun and interesting and since you are still reading this you probably are.

Some queries to look at the structure of the internal table.

select T.name,
       T.internal_type_desc,
       object_name(T.parent_id) as parent_table_name
from sys.internal_tables as T
where T.parent_id = object_id('T');

select C.name as column_name,
       C.column_id,
       T.name as type_name,
       C.max_length,
       C.is_sparse,
       C.is_nullable
from sys.columns as C
  inner join sys.types as T
    on C.user_type_id = T.user_type_id
where C.object_id in (
                     select T.object_id
                     from sys.internal_tables as T
                     where T.parent_id = object_id('T')
                     )
order by C.column_id;

select I.name as index_name,
       I.type_desc,
       I.is_unique,
       I.filter_definition,
       IC.key_ordinal,
       C.name as column_name,
       C.column_id,
       T.name as type_name,
       C.max_length,
       I.is_unique,
       I.is_unique_constraint
from sys.indexes as I
  inner join sys.index_columns as IC
    on I.object_id = IC.object_id and
       I.index_id = IC.index_id
  inner join sys.columns as C
    on IC.column_id = C.column_id and
       IC.object_id = C.object_id
  inner join sys.types as T
    on C.user_type_id = T.user_type_id
where I.object_id in (
                     select T.object_id
                     from sys.internal_tables as T
                     where T.parent_id = object_id('T')
                     );


Index on Persisted Computed column needs key lookup to get columns in the computed expression

Why is a Key Lookup required to get A, B and C when they are not referenced in the query at all? I assume they are being used to calculate Comp, but why?

Columns A, B, and C are referenced in the query plan - they are used by the seek on T2.

Also, why can the query use the index on t2, but not on t1?

The optimizer decided that scanning the clustered index was cheaper than scanning the filtered nonclustered index and then performing a lookup to retrieve the values for columns A, B, and C.
Explanation
The real question is why the optimizer felt the need to retrieve A, B, and C for the index seek at all. We would expect it to read the Comp column using a nonclustered index scan, and then perform a seek on the same index (alias T2) to locate the Top 1 record.
The query optimizer expands computed column references before optimization begins, to give it a chance to assess the costs of various query plans. For some queries, expanding the definition of a computed column allows the optimizer to find more efficient plans.
When the optimizer encounters a correlated subquery, it attempts to 'unroll it' to a form it finds easier to reason about. If it cannot find a more effective simplification, it resorts to rewriting the correlated subquery as an apply (a correlated join):

It just so happens that this apply unrolling puts the logical query tree into a form that does not work well with project normalization (a later stage that looks to match general expressions to computed columns, among other things).
In your case, the way the query is written interacts with internal details of the optimizer such that the expanded expression definition is not matched back to the computed column, and you end up with a seek that references columns A, B, and C instead of the computed column, Comp. This is the root cause.
Workaround
One idea to workaround this side-effect is to write the query as an apply manually:
SELECT
    T1.ID,
    T1.Comp,
    T1.D,
    CA.D2
FROM dbo.T AS T1
CROSS APPLY
(
    SELECT TOP (1)
        D2 = T2.D
    FROM dbo.T AS T2
    WHERE
        T2.Comp = T1.Comp
        AND T2.D > T1.D
    ORDER BY
        T2.D ASC
) AS CA
WHERE
    T1.D IS NOT NULL -- DON'T CARE ABOUT INACTIVE RECORDS
ORDER BY
    T1.Comp;

Unfortunately, this query will not use the filtered index as we would hope either. The inequality test on column D inside the apply rejects NULLs, so the apparently redundant predicate WHERE T1.D IS NOT NULL is optimized away.
Without that explicit predicate, the filtered index matching logic decides it cannot use the filtered index. There are a number of ways to work around this second side-effect, but the easiest is probably to change the cross apply to an outer apply (mirroring the logic of the rewrite the optimizer performed earlier on the correlated subquery):
SELECT
    T1.ID,
    T1.Comp,
    T1.D,
    CA.D2
FROM dbo.T AS T1
OUTER APPLY
(
    SELECT TOP (1)
        D2 = T2.D
    FROM dbo.T AS T2
    WHERE
        T2.Comp = T1.Comp
        AND T2.D > T1.D
    ORDER BY
        T2.D ASC
) AS CA
WHERE
    T1.D IS NOT NULL -- DON'T CARE ABOUT INACTIVE RECORDS
ORDER BY
    T1.Comp;

Now the optimizer does not need to use the apply rewrite itself (so the computed column matching works as expected) and the predicate is not optimized away either, so the filtered index can be used for both data access operations, and the seek uses the Comp column on both sides:

This would generally be preferred over adding A, B, and C as INCLUDEd columns in the filtered index, because it addresses the root cause of the problem, and does not require widening the index unnecessarily.
Persisted computed columns
As a side note, it is not necessary to mark the computed column as PERSISTED, if you don't mind repeating its definition in a CHECK constraint:
CREATE TABLE dbo.T
(
    ID integer IDENTITY(1, 1) NOT NULL,
    A varchar(20) NOT NULL,
    B varchar(20) NOT NULL,
    C varchar(20) NOT NULL,
    D date NULL,
    E varchar(20) NULL,
    Comp AS A + '-' + B + '-' + C,

    CONSTRAINT CK_T_Comp_NotNull
        CHECK (A + '-' + B + '-' + C IS NOT NULL),

    CONSTRAINT PK_T_ID
        PRIMARY KEY (ID)
);

CREATE NONCLUSTERED INDEX IX_T_Comp_D
ON dbo.T (Comp, D)
WHERE D IS NOT NULL;

The computed column is only required to be PERSISTED in this case if you want to use a NOT NULL constraint or to reference the Comp column directly (instead of repeating its definition) in a CHECK constraint.


Scripting an application role with a hashed password

You can connect using the DAC (Dedicated Administrator Connection), and pull the password column from sys.sysowners. First, connect using:

ADMIN:Server\Instance

Then:

SELECT password_hash = [password]
  FROM sys.sysowners
  WHERE name = N'MyAppRole';

This view is only visible when using the DAC, and the column is not exposed in the parent view that is visible (sys.database_principals). Please be careful with the DAC, of course.

That all said, this doesn't help you. CREATE APPLICATION ROLE is much different from CREATE LOGIN, in that you can't supply a hashed password, only plain text. And don't even think about reverse engineering the hashed value, because modern versions of SQL Server use elaborate methods to encrypt a password. In fact if you try this yourself you will see that a different hash is created every time, even in the same statement:

SELECT PWDENCRYPT(N'foo'), PWDENCRYPT(N'foo');

Results (on my machine, this time), noting that yours will vary:

0x0200185968C35F22AF70...   0x0200D6C77A1D84A8467F...

So, I would recommend either:

Storing the application password in source control somewhere, or wherever you store other system passwords currently, and using that to generate the script to deploy the app roles to another server (or source control the entire CREATE APPLICATION ROLE script); or,
Using regular roles instead of application roles.


move last three characters of column to a new column

Create your new column, then do something like:

UPDATE mytable
SET subcase = RIGHT(cases,3)
   ,cases = LEFT(cases,10)


SSIS File System Task (Copy File Operation) OverWriteDestination functionality

If you have OverwriteDestination set to False and your destination file already exists, then you will get an error stating something along the lines of

[File System Task] Error: An error occurred with the following error message: "The file 'XXXXXXX' already exists.".

That is the default behavior if you do not chnage anything else.


Very slow WHERE IN query on very large table - postgresql 9.6

To Speed up the query,

We may use join instead of subquery

Try this query:

SELECT a1.event_id
FROM attendees a1
JOIN attendees a2
    ON a1.attendee_id = a2.attendee_id
        and a2.event_id = $eventId

To make is faster check that if you can create CLUSTER on that table with index on Primary key

Note: cluster a table according to an index reference


Is it normal for MySQL to gradually increase memory usage over time?

I can't address the MySQL specifics, but in general, yes, memory usage of database engines will tend to increase over time. As queries are processed, if the data isn't in memory, the engine will load the data from disk into memory. Then, as long as there are no other pressing needs to use that memory, the data will be left there in case it is queried again.
So as a database engine starts up, it'll use a moderate amount of memory. (Some will allocate a "minimum memory" just to have it ready for when they need it.) Then, as it runs, the memory usage will gradually increase, until it reaches either:

an amount configured as the "maximum memory" within the DBMS, or
the maximum amount the operating system will allow it to take

Only then will it start ejecting data from memory to make room for more data coming in from disk.
The specifics vary from DBMS to DBMS, and I've used some terms here that are inspired by SQL Server's terminology. But the general concepts should hold for most engines.
I suspect you'll get other answers with better MySQL details, or a better explanation of memory usage and caching and pools. But I hope this summary is still useful.


Median absolute deviation for time series outlier detection in Amazon Redshift

I believe this is very doable with a CTE or subquery!

MAD is can be calculated by composing several Redshift functions:

the median of a list of values
absolute value of the difference between each value and that median
the median of those values

I wrote this in the form of:

WITH
medians AS (
  SELECT
    t.values,
    MEDIAN(t.values) OVER () as median_value,
    ABS(t.values-MEDIAN(t.values) OVER ()) AS absolute_deviation
  FROM table AS t
  GROUP BY t.values
)
SELECT
  MEDIAN(absolute_deviation) OVER () AS median_absolute_deviation
FROM medians

Hope this helps


Does SQL Server 2008 store the creation date of execution plans?

It isn't stored in sys.dm_exec_cached_plans, nor is it buried anywhere in the plan XML that I can find. There is useful information in other DMVs however.

For stored procedures we can get the time a plan was cached from sys.dm_exec_procedure_stats:

SELECT TOP(250)
    p.name AS [SP Name]
    , ps.execution_count
    , ps.cached_time
FROM
    sys.procedures p WITH (NOLOCK)
INNER JOIN
    sys.dm_exec_procedure_stats ps WITH (NOLOCK)
ON  p.[object_id] = ps.[object_id]
WHERE
    ps.database_id = DB_ID()
ORDER BY
    ps.cached_time DESC
OPTION
    (RECOMPILE);

For ad-hoc queries, there is the creation time in sys.dm_exec_query_stats:

SELECT TOP(250)
    st.[text] AS [QueryText]
    , qs.execution_count
    , qs.creation_time
FROM
    sys.dm_exec_cached_plans cp WITH (NOLOCK)
INNER JOIN
    sys.dm_exec_query_stats qs WITH (NOLOCK)
ON  qs.plan_handle = cp.plan_handle
CROSS APPLY
    sys.dm_exec_sql_text(cp.plan_handle) st
WHERE
    cp.objtype = N'Adhoc'
ORDER BY
    qs.creation_time DESC
OPTION
    (RECOMPILE);

According to @SqlKiwi... the change from creation_time to cached_time was just because procedures and triggers were added in 2008 and the opportunity was taken to choose a more descriptive name. The created/cached time reflects the last compilation, not the creation time of the original plan.


Does PostgreSQL allow multiple grantees in GRANT commands?

Yes it does, but that will turn into a maintenance headache down the road.

Read http://en.wikipedia.org/wiki/Role-based_access_control for justification for assigning rights to roles over individual users.

Simply create a role and assign users to it, and then grant privileges to that role.

create role some_group;

grant some_group to mary, bob, sue;

grant select, update on a_table to some_group;


Column or global variable ROWNUM not found

Lets see what we have here. Inside the parentheses we have a derived table (often called a subquery although this is a more general term), which is then aliased with the name C:

...
(
    SELECT
        CONO15, ACTR15, FLG115,  MEMB15 AS PAYOWNER,
        PYTP15 AS PAYTYPE, PYRF15 AS PAYREF,
        ROW_NUMBER() OVER (
            PARTITION BY MEMB15, PYTP15, PYRF15
            ORDER BY  PYTP15 ASC) AS ROWNUM
    FROM TKTDTA.PY015L1
    WHERE ROWNUM = 1
) C  ...

Column ROWNUM is not a column of the base table (PY015L1) but defined in the SELECT clause. And there lies the problem. The logical processing of a query (and any subquery including a derived table) has a specific order which is (some parts not included):

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

So WHERE is before SELECT and cannot use any new columns defined there. That's the reason for the error.

You might ask? Isn't ROWNUM a real column? Haven't we just defined it?

Yes we did but it has a certain scope that can be used. C table is also a real table (it's just not a base table). We can use them after they has been defined, so ROWNUM can be used  in the following clauses (ORDER BY which is after SELECT) and outside the parentheses, wherever the C table can be used. Both table C and column ROWNUM are real tables and columns but only after they have been defined. It's like Pinocchio ("See dad, I'm alive! And I'm a boy, I'm a real boy!"). He is a real boy but only at the end of the story, after he has been transformed).

In your case, what is probably wanted is to remove the WHERE ROWNUM = 1 and use the column in the following clauses, ON or WHERE. I'd guess the ON clause:

LEFT JOIN
(
    SELECT
        CONO15, ACTR15, FLG115,  MEMB15 AS PAYOWNER,
        PYTP15 AS PAYTYPE, PYRF15 AS PAYREF,
        ROW_NUMBER() OVER (
            PARTITION BY MEMB15, PYTP15, PYRF15
            ORDER BY  PYTP15 ASC) AS ROWNUM
    FROM TKTDTA.PY015L1
) C ON  C.CONO15 = A.CONO09 AND C.PAYREF = A.PAYREF
    AND C.ROWNUM = 1


An efficient way to compare two large data sets in SQL

Using EXCEPT is in my opinion the way to go here, but you might want to reconsider the use of the temporary table.  By doing so you are effectively duplicating your data in memory, which will slow you down. If the indexes you need exist on the source tables (as I suspect), just compare the appropriate SELECTS:

SELECT StoreKey,ProductKey FROM table WHERE sales BETWEEN date1 AND date2
EXCEPT
SELECT StoreKey,ProductKey FROM table WHERE sales BETWEEN date3 AND date4


Add a new Column and define its position in a table

No. Rebuilding the table is the only way. See this Connect Item for confirmation.

You could use SSMS to script this for you if you trust the somewhat buggy table designer.

Apart from that you could declare a view with the desired column order as a way of grouping logically related columns together.


Postgres Incremental Backup (Continuous Archiving WAL)

In order to restore a backup, you need to have the base archive of all the data files, plus a sequence of xlogs. An "incremental backup" can be made, of just some more xlogs in the sequence. Note that if you have any missing xlogs, then recovery will stop early.

So it's not clear here exactly what you've done, unless you changed the level of detail you're mentioning part way through your list. When you make a copy of more segments that have been put into the archive directory after adding more data, you need to ensure that all the data has been archived: using pg_start_backup and pg_stop_backup usually does this for you, but you don't mention it the second time. You need to at least do a pg_switch_xlog to have the current xlog segment immediately archived.

If you think that recovery is not consuming enough xlog segments, look at the recovery log to see if it tried to take them all. And have your recovery command make some sort of mark on which xlog files were taken.


How to use HTTP Connection Manager in Script Component? (Not Script Task)

i know it's been a while since the question but i found a possible solution.

This link provides an explanation and examples of how to use the connections available in the connection manager enabled for the Script Component in the SSIS https://msdn.microsoft.com/en-us/library/ms136060.aspx

The portion of code that we are interested is this:

IDTSConnectionManager100 connMgr;
HttpClientConnection100 hcc;

IDTSComponentMetaData100 compMetadata = this.ComponentMetaData; //Just for output purposes.

connMgr = this.Connections.MyWebServer;

hcc = (HttpClientConnection100)cm.AcquireConnection(null);
compMetadata.FireInformation(1, "Message: ", "URL: " + hcc.ServerURL + " User: " + hcc.ServerUserName + " Pwd: " + hcc.GetServerPassword(), "", 0, ref fail);


Yes, you need to catalog the backups at their new location.

You should run crosscheck also on the backups, so the entries pointing to the old location become EXPIRED.


mysql query - how to delete duplicates based on highest int?

Why does the DELETE Query not work ?

Whenever a table is used against itself in a query other than SELECT, rows tend to disappear during the optimization phase. I wrote about that back on Feb 22, 2011 : Problem with MySQL subquery

  What can you do then ?

Create a new offers table to see what the table would look like if the DELETE were actually done:

DROP TABLE IF EXISTS offers_after_delete;
CREATE TABLE offers_after_delete LIKE offers;
INSERT INTO offers_after_delete
SELECT B.* FROM
(SELECT userId,site,MAX(lastPriceBid_rounded) highest_amount
FROM offers GROUP BY userId,site) A
INNER JOIN offers B USING (userId,site);

Please note that I based the delete operation on a distinct user per unique site. Now, browse through offers_after_delete. If the table looks correct to you, then delete like this:

DROP TABLE IF EXISTS offers_after_delete;
CREATE TABLE offers_after_delete LIKE offers;
INSERT INTO offers_after_delete
SELECT B.* FROM
(SELECT userId,site,MAX(lastPriceBid_rounded) highest_amount
FROM offers GROUP BY userId,site) A
INNER JOIN offers B USING (userId,site);
ALTER TABLE offers RENAME offers_old;
ALTER TABLE offers_after_delete RENAME offers;
DROP TABLE offers_old;

Please rehearse this on a DB Staging Server.

Give it a Try !!!


Typically this kind of decisions are taken with product managers. They are the ones who can say things like "customers should be able to read 2 years old information, but not necessarily older than that".
MySQL (or any other DBMS, as far as I know) doesn't provide a way to find out when a row was last read.
But there are ways to find out related information. This won't solve your problem, but hopefully you'll find useful indications.

Some tables have a column with the timestamp of the last update. Usually the default value is CURRENT_TIMESTAMP, but check: if there is no default value, I wouldn't consider the column reliable. You can't be sure that the application always updates the column.
You can temporarily add triggers AFTER UPDATE and AFTER DELETE. Triggers can write the value of the primary key into a separate table, using INSERT IGNORE. Keep them going for some time, and then check the oldest row that was updated/deleted. Writing such triggers manually is impossible, but this can easily be automated.
If you use Percona Server or MariaDB, you can enable the user_statistics plugin. It tells you how many times each table and index is read/written. If you're lucky, you'll find out that some tables are never read and can be archived. Even if not, the number of read rows per hour could possibly give you a hint (or not... this is highly dependent on your database logic, so I can't give more advice on this).
If you suspect that a portion of rows is never read, try a trick: read them with a SELECT three times. If the first query was sensibly slower than the second and third, those rows were not cached. Repeat the experiment at different days. It's a very empirical and unreliable test, use it as a hint and nothing more.
If the slow log is enabled, it logs the queries that take more than long_query_time seconds. You can set long_query_time=0 to log all the queries (this is a good practice for many reasons). You can then grep all queries that mention a certain table. You can make a script to run them all, and remember the oldest read row. Careful, you'll need to run SELECTs but not INSERT, UPDATE, etc.


T-SQL - Script all tables creation, contraints, indexes, keys and triggers

To clarify.  You want to generate a script for all objects in a database and then for a select set of tables, have it generate the script to create the data?

Just do it in two steps.  One pass with the Generate Scripts as you have described, selecting all the objects you want to generate the create scripts for.

Then do it again, this time for just the tables you want, but make sure to select the Advanced (on the "Set Scripting Options" section) --> Types of data to script == "Data Only"

You can then further select either all scripts to a single file or a single file per object.

If you want to do this on a regular basis (or more dynamcially), you can interact with the Generate Scripts engine through scripts. This is a good article on getting started.  https://www.mssqltips.com/sqlservertip/1833/generate-scripts-for-database-objects-with-smo-for-sql-server/


How to increase column limit of a table in SQLite?

You would have to obtain the Source Code for SQLLite, change the value of SQLITE_MAX_COLUMN, recompile the code and distrbute the resulting executable.

However, more than 2000 columns in a single table sounds wrong to me.
Do you have lots of repeated fields in each row?  That's poor Relational design.

If necessary, create a second table with the same primary key as the first and add your additional fields into that.  You would have to issue two select statements to retrieve the values, though, because SQLITE_MAX_COLUMNS also applies to select statements, so you can't just join the two tables together.


does INSERT INTO ... ON DUPLICATE KEY UPDATE executes even if the row has not changed?

MySQL executes ON DUPLICATE KEY UPDATE the same way it executes UPDATE statements:

It checks the contents of each row (and columns) to be updated and if they are identical to the supplied, it does not do any update. It still has to check them though.

So the result in your case (where you send 3 rows to be inserted) would be:

3 rows to be inserted
3 key collisions

0 rows inserted
3 to be updated

1 row actually updated
2 identical (no update)

A few things about your syntax:

you don't need to update the UNIQUE key.
you can use VALUES(column) in the UPDATE part.
you can combine the multiple inserts into one:

INSERT INTO user
  (ID, Name, Surname)
VALUES
  (1, 'John', 'Conor'),
  (2, 'Foo',  'Bar'),
  (3, 'Foo',  'Baz')
ON DUPLICATE KEY UPDATE
  Name = VALUES(Name),
  Surname = VALUES(Surname) ;


Using json_object_keys() like you found yourself already (or jsonb_object_keys() for jsonb), you can do this with simple inline SQL. I suggest a LATERAL subquery.

If, like your example suggests, there is exactly one key inside the key  'blocks', the solutions is simple:

SELECT *
FROM   tbl, json_object_keys(document->'blocks') AS part
ORDER  BY document->'blocks'->part->>'first_name';

The comma is short notation for CROSS JOIN LATERAL.

If there can be any number of keys, you need to be more verbose:

SELECT *
FROM   tbl
LEFT   JOIN LATERAL (
   SELECT part
   FROM   json_object_keys(document->'blocks') part
   WHERE  part ~ '^participant-information_primary__Primary__'  -- your pattern here
   LIMIT  1  --  just to be safe
   ) s ON true
ORDER  BY document->'blocks'->part->>'first_name';

The LEFT JOIN preserves rows where no matching key is found at all.

Related:

What is the difference between LATERAL and a subquery in PostgreSQL?


SQL SERVER 2014 - SELECT INTO, ORDER BY - automatic clustered index?

No, the ORDER BY applies to the SELECT query and not to the table created by SELECT INTO.
Using ORDER BY is unnecessary in this statement, because it doesn't even guarantee the rows are inserted into the new table in that order.
See INTO Clause (Transact-SQL)


Postgres CTE query for a single row not optimized

Common Table Expressions are treated as an “optimization fence” by PostgreSQL: It will never push down predicates from the main query into the CTE nor collapse any joins across the CTE boundary. Instead, it will generally evaluate the whole CTE as it is, materializing the result; the main query will then access the temporary table generated from the CTE.

So yes, your query would likely benefit from converting the CTE into a subquery.

Note that an actual view (created by CREATE VIEW) does not act as an optimization fence. The view’s definition will be included in the query using it, then optimized as usual. For CTEs, there has been discussion about making the optimization fence behaviour optional so that they could be used “just” to make queries more readable. However, as of version 9.5, this hasn’t been implemented yet.


To get all members of all roles:

SELECT r.rolname as username,r1.rolname as "role"
FROM pg_catalog.pg_roles r JOIN pg_catalog.pg_auth_members m
ON (m.member = r.oid)
JOIN pg_roles r1 ON (m.roleid=r1.oid)
WHERE r.rolcanlogin
ORDER BY 1;

Add r1.rolname='council_stuff' to filter on only that one.

Be aware that users themselves are "roles" in postgresql parlance (there used to be "users and groups" in older versions, but this have been unified into roles).

Here the difference is made between a user and a role with rolcanlogin boolean column, which corresponds o the interpretation that a user is a role that has the LOGIN permission.

The two JOIN can be turned into LEFT JOIN to add the users who aren't member of any role:

SELECT r.rolname as username,r1.rolname as "role"
FROM pg_catalog.pg_roles r LEFT JOIN pg_catalog.pg_auth_members m
ON (m.member = r.oid)
LEFT JOIN pg_roles r1 ON (m.roleid=r1.oid)
WHERE r.rolcanlogin
ORDER BY 1;


How safe are PostgreSQL triggers against attacks?

Security in ANSI relational databases is normally based on layers and one of those is role based access.  A business case that no one will ever need to delete or edit data in some tables will probably need to be revised when someone makes an oopsie and bad data is entered.

Any code that is written whether it's in a trigger or stored procedure has a chance of being misused but this requires access. A better solution than writing triggers that prevent basic operations on data is to control access and permissions by creating roles for each type of user and granting permissions appropriate to those roles.


Oracle RAC, unable to connect to node vip

The issue turned out to be with the VIP addresses not being allowed to be accessed via port 1521 due to our firewall policy. These IPs are now permitted and the problem has been resolved. thanks to Balazs for all the help/


Innodb: Operating system error number 1117 in a file operation

from System Error Codes (1000-1299)

ERROR_IO_DEVICE
1117 (0x45D)
The request could not be performed because of an I/O device error.

It seems that you had a problem with your disks


You can check the value of the CLUSTER_DATABASE parameter:

SQL> select name, value from v$parameter where name = 'cluster_database';

NAME                  VALUE
--------------------- ---------------------
cluster_database      TRUE

According to the Oracle Database Reference:

  CLUSTER_DATABASE is an Oracle RAC parameter that specifies whether or not Oracle RAC is enabled.


array_length if array is empty behaviour

It looks like PostgreSQL returns NULL for empty arrays. Try:

SELECT
    array_length(ARRAY[]::bigint[], 1),
    array_length(ARRAY[]::bigint[], 1) IS NULL,
    array_lower(ARRAY[]::bigint[], 1),
    array_upper(ARRAY[]::bigint[], 1)

You get:
null|true|null|null

Looks weird, but that's just the way it is. The workaround is to use COALESCE:

SELECT
    COALESCE(array_length(ARRAY[]::bigint[], 1), 0)

returns 0.

Tried this on PostgreSQL 9.4.


How to find out if rows are refenced from other tables?

You may need to alter this slightly for postgres (is is MS SQL syntax) but something like:

SELECT id
FROM   contacts
LEFT OUTER JOIN FROM case_contacts ON case_contacts.contact_id = contacts.id
LEFT OUTER JOIN FROM case_payments ON case_payments.contact_id = contacts.id
LEFT OUTER JOIN FROM invoices      ON      invoices.contact_id = contacts.id
WHERE case_contacts.contact_id IS NULL
AND   case_payments.contact_id IS NULL
AND        invoices.contact_id IS NULL

or:

SELECT id
FROM   contacts
WHERE  id NOT IN (SELECT contact_id FROM case_contacts)
AND    id NOT IN (SELECT contact_id FROM case_payments)
AND    id NOT IN (SELECT contact_id FROM invoices)

should list the IDs that are not referred to in those three tables. The query planner *should * see these as equivalent and optimise accordingly, but if not then the former will be more efficient (the latter risks running the three sub-queries once for every row in contacts).


An address is a inseparable entity consisting of Street, City, State, Zip and Country. Those should all go into one table. There might be a reason to have an additional e.g. country table to store countries the person visited, but the address table should always contain at least the above five fields as a street without the rest is meaningless.

For phone numbers it makes sense to have a separate table. The fax numbers are usually stored in the same table differentiated by a type. That makes future extensions a lot simpler, but you could got with separate a fax table too. That would allow you to store additional information like e.g. if that fax number can receive color facsimiles.


Your error is telling you that the amount of columns you said you'd fill does not match the amount of columns you actually are giving data for. Make sure the amount of columns specified after timesheet matches the amount of columns filled after Values.

Additionally you may want to check if the source, description, location_name and suggestion columns allow NULLS. You can also test if it works if you replace the NULL values with dummy values (like an empty string or a 0).


List of database users: can you run C# Code in SQL Server Management Studio?

The only language1 supported in SQL Server is T-SQL.  Having said that, it's pretty simple to use T-SQL to get the details you need.

/*
    Description: Display the Active Directory group
    membership permission path for all users
*/
SET NOCOUNT ON;
DECLARE @t TABLE (
    [account name] varchar(255)
    , [type] varchar(255)
    , [privilege] varchar(255)
    , [mapped login name] varchar(255)
    , [permission path] varchar(255)
);

INSERT INTO @t
EXEC xp_logininfo;
DELETE
FROM @t
WHERE type = 'group';
DECLARE @cmd nvarchar(max);
DECLARE cur CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC FOR
SELECT 'EXEC xp_logininfo @acctname = ''' + name + ''', @option = ''members'';'
FROM master.sys.server_principals
WHERE type = 'G';
OPEN cur;
FETCH NEXT FROM cur INTO @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO @t
    EXEC sp_executesql @cmd;
    FETCH NEXT FROM cur INTO @cmd;
END
SELECT *
FROM @t t
ORDER BY t.[account name];
CLOSE cur;
DEALLOCATE cur;

The results returned show users with access to the SQL Server, and shows the group they are a member of that is providing that access.

You'll need membership in the sysadmin fixed server role or membership in the public fixed database role in the master database with EXECUTE permission granted to execute xp_logininfo.  You'll need standard user rights in Active Directory to enumerate the items in the directory.

1 - R is supported in certain configurations, and Python is supported in SQL Server 2017 and above.  Generally speaking, you'll want to use T-SQL.  Depending on your acceptable security posture, you may be able to use C# or Visual Basic to create a SQLCLR stored procedure that can be called from T-SQL in SQL Server 2005 and above.


How do you you suppress a specific warning in the postgresql log output?

There is no way to suppress a specific message.  You can change "log_min_messages" to suppress logging of the entire class of WARNING messages, which you pointed out you don't want to do.  But you can target a bit more specifically by using ALTER USER ... IN DATABASE ... SET LOG_MIN_MESSAGES ... to do this just for one specific user and database, if that helps you.

If you want to target more finely than that, and you are willing to compile your own PostgreSQL to do it, you could easily modify the source code to demote this specific message from a WARNING to a NOTICE (or lower), but that is a pretty heavy-handed approach.  There are two different places in the source that produce this message for COMMIT, and I'm not sure which one would apply to you.

Other than that, using log mining tools which can be configured to ignore these messages may be your best option.


For converting a failed master into a new replica, you could look into pg_rewind.  I have not used it, but my understanding is that this is its entire reason for being.  I think it would be simpler and safer to simply throw away the failed master and reclone from the new master with pg_basebackup, but that could certainly be a bit hard on the network bandwidth.

For keeping configuration files in sync, I don't think it would be reasonable to create an elaborate tool just for PostgreSQL.  There are already-built elaborate tools for configuration file management, such as Chef, CFEngine, and Puppet, and they should work for PostgreSQL config files the same way as they work for apache, sendmail, etc.  Personally, every time I deploy a config change to production master (maybe twice a year?) I just ask myself "Should this change also go to the replica?" and if the answer is "yes" I go make the change.  If I wanted to go beyond that, I would use an established configuration management tool.  But there is one trick I use for backing up the config files.  When you use pg_basebackup -Ft -z -D backup_dir for a single-tablespace database, it creates a directory backup_dir with just a single base.tar.gz file in it.  If I change a config file and don't feel like taking a new base backup, I just copy the config file into that directory to live beside the base.tar.gz file.  If you have a restore/clone script, it is pretty easy to have it copy any 'loose' files it finds in the backup directory to overwrite any that it just unpacked from tar.


Does using Activity Monitor have an effect on server performance?

YES. Beware of the "observer effect."

This article is about you using trace and extended events directly, but Activity Monitor uses similar technology under the covers - it doesn't have any magical, no-cost ways of collecting performance information.

There are several monitoring tools out there that do a better job at this than Activity Monitor, some more so than others. There are also free scripts like sp_whoisactive and sp_BlitzFirst that are more honest with you about what's really going on on your servers (see this article). I strongly suggest you try some of those out and don't open Activity Monitor or Profiler at all.