Postgres: extracting multiple bit-columns from single 'flags' column after join with 2nd table

You could merge the subqueries using this model:

SELECT bool_or(B.tags&1<>0) as "has_children_tag_1",
       bool_or(B.tags&2<>0) as "has_children_tag_2",
       bool_or(B.tags&4<>0) as "has_children_tag_3",
       bool_or(B.tags&8<>0) as "has_children_tag_4"
FROM A LEFT JOIN B ON A.id = B.parent_id
WHERE [conditions]

bool_or is an aggregate function that evaluates to true if at least one value is true, so that should lead to the same results as the multiple count(*)>0 in the question, except as one single subquery.

To push these columns into an otherwise unrelated query, you may add the above subquery as a CTE in a WITH clause, and just put it in the FROM list. For instance, modifying your original query:

WITH flags AS
(SELECT bool_or(B.tags&1<>0) as "has_children_tag_1",
       bool_or(B.tags&2<>0) as "has_children_tag_2",
       bool_or(B.tags&4<>0) as "has_children_tag_3",
       bool_or(B.tags&8<>0) as "has_children_tag_4"
FROM A LEFT JOIN B ON A.id = B.parent_id
WHERE [conditions] )
SELECT
  id,
  aaaa,
  bbbb,
  tags,
  has_children_tag_1,
  has_children_tag_2,
  has_children_tag_3,
  has_children_tag_4
FROM flags,A
LEFT JOIN Z
    ON A.id = Z.id


Why select statement requests exclusive locks?

Always consider locktype when analyzing locks.

In this case locktype = virtualxid, i. e. virtual transaction id. This is an identifier that is local for a backend. PostgreSQL uses such identifiers for read-only transactions to save "normal" transaction ids, that are global to all backends. (You can read why it is doing so, although it is not relevant to the question.) For read-write transaction you will have locktype = transactionid instead.

So, this row in pg_locks basically says that a backend process has an exclusive lock on transaction id of the transaction it is currently executing. PostgreSQL uses this lock to allow other transactions to wait for ours transaction to finish. Citing the doc:

  When a process finds it necessary to wait specifically for another
  transaction to end, it does so by attempting to acquire share lock on
  the other transaction's ID (either virtual or permanent ID depending
  on the situation). That will succeed only when the other transaction
  terminates and releases its locks.


This is called a parameterized statement. At the basic level, such a query is executed in two stages: first a query with parameter markers (those question marks) is compiled on the server side, then the actual parameter values are bound to the query and the query is run, returning only results that match the criteria (obviously).


Why do I get error 1054 using trigger on INSERT saying that column isn't on the field list?

This works:

CREATE TRIGGER insertarNovedades AFTER INSERT ON GENEROS
FOR EACH ROW
    INSERT INTO NOVEDADES (novedad) VALUES (CONCAT('Nuevo genero: ', new.genero));

Manual has this example and the following explanation:

mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
     -> FOR EACH ROW SET @sum = @sum + NEW.amount; Query OK, 0 rows affected (0.06 sec)

  The statement following FOR EACH ROW defines the trigger body; that is, the statement to execute each time the trigger activates, which occurs once for each row affected by the triggering event. In the example, the trigger body is a simple SET that accumulates into a user variable the values inserted into the amount column. The statement refers to the column as NEW.amount which means “the value of the amount column to be inserted into the new row.”


I found the solution for my situation...

I went into Printer Settings & checked the "Always Use Driver Settings" under the "Override Application Default Settings." This caused the printer to always print in the correct orientation.


Optimise huge Postgres table for fast read

Add a second column which will hold a hash of the text value.  Create the index on the hash.  Even if there is a hash collision there will be only a few rows to read and perform a full comparison on the text vlaues.


SQL Server: Cannot resolve collation conflict between "Latin1_General_CI_AS" and "[garbage]"

UPDATE

I finally had time to test this and was able to reproduce the problem. While not respecting collation precedence (noted in my original answer below) was a problem, it was not this problem (though both could be caused by the same underlying bug). Here is what I can now confirm:

The O.P.'s test script (via Gist) does not produce this problem as it's missing a critical piece: forcing the collation of one of the NVARCHAR "Name" columns in the Component and Statement tables to be different than the other.
The actual error is a collation mismatch between the two "Name" columns in the UNION ALL.
The collation mismatch in the UNION ALL should have ended the query before it even got to the CONCAT function, and it would have if both columns were VARCHAR, but if at least one column is NVARCHAR, then the CONCAT function prevents the query from terminating properly.
There are two ways in which the query can behave improperly when CONCAT is involved (at least in this UNION ALL scenario):

If the column from the subquery (containing the UNION ALL with the collation mismatch) is not the first parameter of the CONCAT function, then it will terminate with a misleading error message, stating that the error is in the "concat operation" instead of being in the "UNION ALL operation" (oh, and let's not forget about the garbage collation name in the error message!)
If the column from the subquery (containing the UNION ALL with the collation mismatch) is the first parameter of the CONCAT function, then it will  actually succeed, using the database's default collation for the value returned by the CONCAT function. (see final test case below)

The CONCAT built-in function was fixed as of SQL Server 2014 as neither this behavior, nor the incorrect behavior shown in the "Original Answer" (see below) is reproducible starting with that version (and I tested with 2014, 2016, 2017, and 2019).

-- DROP TABLE #Mix;
CREATE TABLE #Mix
(
  [VC1]  VARCHAR(50)  COLLATE SQL_Latin1_General_CP437_CS_AS,
  [VC2]  VARCHAR(50)  COLLATE Azeri_Cyrillic_100_CS_AS_WS,
  [NVC1] NVARCHAR(50) COLLATE Frisian_100_CS_AI_KS,
  [NVC2] NVARCHAR(50) COLLATE Sami_Sweden_Finland_100_CI_AI
);
INSERT INTO #Mix ([VC1], [VC2], [NVC1], [NVC2]) VALUES (0xB0, 0xDE, 0xDE, 0xDE);
SELECT * FROM #Mix;
/*
VC1    VC2    NVC1    NVC2

░      Ю      Þ       Þ
*/

SELECT CONCAT(N'Both VARCHAR', sub.[WhatEva])
FROM   (
    SELECT [VC1]
    FROM #Mix
    UNION ALL
    SELECT [VC2]
    FROM #Mix
) sub([WhatEva]);
/*
 Msg 457, Level 16, State 1, Line XXXXX
Implicit conversion of varchar value to varchar cannot be performed because the
   collation of the value is unresolved due to a collation conflict between
   "Azeri_Cyrillic_100_CS_AS_WS" and "SQL_Latin1_General_CP437_CS_AS" in
   UNION ALL operator.
*/

SELECT CONCAT(N'At least one NVARCHAR', sub.[WhatEva])
FROM   (
    SELECT [VC1]
    FROM #Mix
    UNION ALL
    SELECT [NVC1]
    FROM #Mix
) sub([WhatEva]);
/*
-- 2012
Msg 468, Level 16, State 9, Line XXXXX
Cannot resolve the collation conflict between "{db_default_collation}" and
   "堓.ꚤ鍛翹.堓.툀堗.툀堗.쓀姧.꺱䱷..꺱䱷......꺱䱷..툘堗.帎鍲翹.堓..錿翹..."
   in the concat operation.

-- 2014, 2016, 2017, 2019
Msg 451, Level 16, State 1, Line XXXXX
Cannot resolve collation conflict between "Frisian_100_CS_AI_KS" and
   "SQL_Latin1_General_CP437_CS_AS" in UNION ALL operator occurring in SELECT
   statement column 1.
*/

-- SUCCESS!?!?!?! Should be an error!!!
SELECT CONCAT(sub.[WhatEva], N':At least one NVARCHAR') AS [ConcatSuccessWTF?],
       SQL_VARIANT_PROPERTY(CONCAT(sub.[WhatEva], N':At least one NVARCHAR'),
                            'collation') AS [ResultingCollation]
FROM   (
    SELECT [VC1]
    FROM #Mix
    UNION ALL
    SELECT [NVC1]
    FROM #Mix
) sub([WhatEva]);
/*
-- 2012
ConcatSuccessWTF?          ResultingCollation

░:At least one NVARCHAR    {db_default_collation}
Þ:At least one NVARCHAR    {db_default_collation}

-- 2014, 2016, 2017, 2019
Msg 456, Level 16, State 1, Line XXXXX
Implicit conversion of nvarchar value to sql_variant cannot be performed because the
   resulting collation is unresolved due to collation conflict between
   "Frisian_100_CS_AI_KS" and "SQL_Latin1_General_CP437_CS_AS" in UNION ALL operator.
*/

  is this a known bug

Not sure if it was noted in any public forum, but it must have been noticed internally (to Microsoft) since it was fixed as of the next version (i.e. SQL Server 2014), though not in any Service Pack as I tested on SQL Server 2012, SP4 GDR (11.0.7462.6).

  How do I properly resolve this issue

If you, or anyone else, is still using SQL Server 2012 and running into this, it is best to resolve the collation conflict at the source, which is in the UNION ALL operation. Just pick the table with the column that has the collation that you do not want, and apply the COLLATE clause there such that the UNION ALL operation succeeds on its own, even if no CONCAT function is being used. This is better than specifying the COLLATE in the CONCAT function because that is after the fact of the UNION ALL, which in this case should be failing, but is allowed to succeed due to the bug in CONCAT.

ORIGINAL ANSWER

The Collation problem (separate from the error message resulting from the Collation problem) is due to the CONCAT built-in function not honoring Collation Precedence, and hence requiring all input parameters to be of the same Collation. Clearly you have one input parameter that is not of the same Collation as the rest. That parameter is cte.Name, which you have currently fixed via the COLLATE keyword.

We can simulate this scenario as follows. You can run it from any database. The default Collation for the Database that I am executing the following code in is: SQL_Latin1_General_CP1_CI_AS.

CREATE TABLE #TT (Col1 NVARCHAR(50) COLLATE SQL_EBCDIC278_CP1_CS_AS);
INSERT INTO #TT values ('something');

SELECT CONCAT('now this is ', tmp.Col1)
FROM #TT tmp;
/*
Msg 468, Level 16, State 9, Line 17
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and
    "SQL_EBCDIC278_CP1_CS_AS" in the concat operation.
*/

And the following two queries even show that the Collation is evaluated per each input parameter, with the first input parameter setting the Collation to be used:

SELECT CONCAT('now this is ', tmp.Col1, N' else' COLLATE Latin1_General_100_CI_AS)
FROM #TT tmp;
/*
Msg 468, Level 16, State 9, Line 23
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and
    "SQL_EBCDIC278_CP1_CS_AS" in the concat operation.
Msg 468, Level 16, State 9, Line 23
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and
    "Latin1_General_100_CI_AS" in the concat operation.
*/

SELECT CONCAT('now this is ' COLLATE Latin1_General_100_CI_AS, tmp.Col1, N' else')
FROM #TT tmp;
/*
Msg 468, Level 16, State 9, Line 30
Cannot resolve the collation conflict between "Latin1_General_100_CI_AS" and
    "SQL_EBCDIC278_CP1_CS_AS" in the concat operation.
Msg 468, Level 16, State 9, Line 30
Cannot resolve the collation conflict between "Latin1_General_100_CI_AS" and
    "SQL_Latin1_General_CP1_CI_AS" in the concat operation.
*/

This conflict can be fixed in two ways:

Don't use the CONCAT built-in function. The main benefit of CONCAT is that it doesn't require each parameter to be a string type; it will handle the conversion to string internally. This is convenient if you have several non-string items to concatenate. But if you only have strings, then it doesn't provide any benefit, and probably even hurts performance to pass all of that stuff into the function. AND, by not using CONTACT, Collation Precedence will take over, and in most cases resolve the conflict automatically.

SELECT 'now this is ' + tmp.Col1
FROM #TT tmp;
-- now this is something

In this case, Collation Precedence will determine that the Collation of the tmp.Col1 column overrides the Collation of the string literal (which uses the "current" database's default Collation).
Use the COLLATE clause (as you are already doing). There is nothing wrong with this approach as this is one of the uses of the COLLATE keyword.

-- Force the Collation of the column in the temp table to match the "current" database:
SELECT CONCAT('now this is ', tmp.Col1 COLLATE SQL_Latin1_General_CP1_CI_AS)
FROM #TT tmp;
-- now this is something

-- Force the Collation of the string literal to match the column in the temp table:
SELECT CONCAT('now this is ' COLLATE SQL_EBCDIC278_CP1_CS_AS, tmp.Col1)
FROM #TT tmp;
-- now this is something

In these two cases, the Collation to use is determined by the first input parameter, and either that one needs to be explicitly set to the Collation of the second parameter (bottom example) which in this case is taken from the columns definition. Or, the second parameter needs to be explicitly set to match the Collation of the first parameter (top example) which in this case is taken from the databases's default as it is a string literal.


you can do this with help of MDX Custom filer on filter option when we generated report for cube.

there you write query regaring dimension table and fact table with help of select measures.[attribute] on rows from cube name where conditon apply  . and so on...


unable to add a range to a tag using sh.addTagRange command

Shard key zone (tag) ranges require specifying minimum and maximum bounds for a range of shard key values.

Since your shard key values are currently strings, they will be compared using binary order rather than numerical order. The error message is correctly indicating that a minimum value of "7" (string) is not less than a value of "11" (string). For example, given the strings "1", "7", and "11" the expected ascending sort outcome using a binary string comparison is "1", "11", and "7". This is roughly alphabetical order, but more specifically strings in MongoDB are stored as UTF-8 bytes.

As you are intending on using zone sharding, for this test deployment it would be better to have numeric shard key values so you can use the range of 1-7 for your zone. You could remove and replace existing documents or just add more with the desired shard key values.

I assume you aren't concerned about the cardinality of your shard key for a test cluster, but for a real deployment you may want to choose a more appropriate shard key if ShardMapId will only have a small range of values.

Note: tag-aware sharding was succeeded by Zone sharding in MongoDB 3.4. The sh.addTagRange() shell helper has been aliased to the modern equivalent of sh.updateZoneKeyRange(), but I would recommend using the newer helpers to be safe. You should also ensure that your shell version() matches your server db.version().


Missing .msi files gives error while repairing SQL Server 2008R2

The script provided by Microsoft in the Knowledge Base article:

How to restore the missing Windows Installer cache files and resolve problems that occur during a SQL Server update

...found the missing files and allowed me to advance a bit:

Save the script to your desktop
Run it using CMD

cscript scriptname.vbs scriptoutput.txt

Find all the copy commands in the text file and use them to populate your Windows installer folder. Those missing files are causing your issue.

EDIT

The step 3 needs a little more explaining:
The text file produced from the VBS script contains various recommendations and mostly suggests to copy the files from certain locations (more on that later) to the %windir%\installer path where they should be in order for any upgrade/patch/repair process to work.

In my case, I had sqlserver 2008R2 SP1 so the output textfile basically told me "I need certain vanilla files and certain SP1 files"

Here is the script asking for vanilla files, which can be found in a stock sqlserver2008r2 installer DVD

Copy "C:\Source\Sources SQL 2008\SQL 2008 R2\SQL2008R2_source\x64\setup\sql_engine_core_inst_msi\sql_engine_core_inst.msi" C:\Windows\Installer\2d620c2b.msi

And here is the script asking for SP1 files :

Copy "g:\10bb0cd00ccb163360\x64\setup\sql_engine_core_inst_msi\sql_engine_core_inst.msp" C:\Windows\Installer\2d8607e0.msp

SQLSERVER will remember where it found those files the first time it had to use them, but this location might not exist anymore if, as in my case, someone did tons of cleaning on the server and deleted all of you precious files.

You will need to modify the first part of the path so it matches the location of your extracted DVD and of your extracted SP1 patch. To extract the SP1 patch, you can run the following command in a CMD prompt

c:\path\to\file\SQLServer2008R2SP1-KB2528583-x64-ENU.exe /x

Notice, the /x at the end, this command will open a small window asking where you want the content to be extracted.


Concurrent backups in MS SQLServer 2008 R2

SQL Server backups are serial on a per-database basis.

This page gives you which administrative tasks can and cannot be run concurrently Concurrent Administrative Operations.


Improve Performance on GROUP BY - large table PostgreSQL

IN can take a list of values (like you have it) or a set. Long lists don't scale well. See:

Optimizing a Postgres query with a large IN
How to use ANY instead of IN in a WHERE clause with Rails?

Try:

SELECT origin, min(minutes) -- you'll also want to show origin
FROM   tbl
JOIN   unnest($origin_array) o(origin)      USING (origin)
JOIN   unnest($dest_array)   d(destination) USING (destination)
GROUP  BY origin;

Where $origin_array can be an array of the appropriate type int[] or an  array literal like '{100225471,100236548,10263073}'::int[]

The optimal index for this query should be:

CREATE INDEX ON tbl (origin, destination, minutes);

Appending minutes only makes sense if you get index-only scans out of it (introduced with pg 9.2, improved in later versions). But since your table is mostly read-only, it seem like the perfect use case. See:

Can Postgres use an index-only scan for this query with joined tables?

You later commented:

  I want to get the closest (min(minutes)) facility (destination) for each place (origin).

The above query does not achieve that. Only returns the shortest time for any qualifying destination. Use instead:

SELECT DISTINCT ON (origin)
       origin, minutes, destination
FROM   tbl
JOIN   unnest($origin_array) o(origin)      USING (origin)
JOIN   unnest($dest_array)   d(destination) USING (destination)
ORDER  BY origin, minutes;

Depending on cardinalities and value frequencies, this alternative may be faster:

SELECT *
FROM   unnest($origin_array) o(origin)
LEFT   JOIN LATERAL (
   SELECT minutes, destination
   FROM   tbl
   WHERE  origin = o.origin
   AND    destination = ANY ($dest_array)  -- equivalent to IN([long list])
   ORDER  BY minutes
   LIMIT  1
   ) t ON true;

One subtle difference: the last query returns origins from the input array with no match in tbl at all. May or may not be desirable - or even necessary. The last query is my favorite.

Further reading on how to optimize queries of this kind:

Select first row in each GROUP BY group?
Optimize GROUP BY query to retrieve latest record per user

Plus, urgently consider upgrading to a current version of Postgres, like a_horse already suggested. Performance for big data has been improved  substantially since Postgres 9.4.

You mentioned lists of up to 2000 items. Combining two long list like that is problematic since it results in a Cartesian product of 2000 x 2000 = 4000000 possible combinations. If that can happen - sure you need to consider that many combinations? That won't be fast.


Why can't the transaction log shrink during a backup?

The transaction log is used during the backup process to store all the changes that were committed while the backup was being taken.  These change are applied to the backup file so that the backup is consistent as of the time it finished (only committed transactions).

But three days for a full?  I backup a Terabyte database every day and it only takes 90 minutes (compression, multiple files, SQL native).  Given your version, you should look into backing up to multiple files at the same time (or speeding up your backup storage).

Answer: the transaction log is in use by the backup process.

Because you were sending this to the vendor, I suspect that you sent it directly from your local SQL Server to the Vendor on a fileshare (especially since you say it only takes 90 minutes to backup locally).

For this scenario, I would recommend that you backup locally first (and if you were in FULL/BULK recovery mode, maybe with the COPY_ONLY flag) and then send it to the vendor.  You won't tie up your SQL Server with this backup and you gain the opportunity to compress the backup using 7zip or similar (which should easily cut it down by 50%).


Determining which isolation level is appropriate

The question seems like a puzzle which appears very simple, but maybe not so simple ,or it just pretends to be complex... I'll try my best answering it as I understand it. I apologize if I misunderstood some obvious hints.

With PostgreSQL, there is no real Read uncommitted - you get Read committed. Quoting the documentation:

  In PostgreSQL, you can request any of the four standard transaction isolation levels, but internally only three distinct isolation levels are implemented, i.e. PostgreSQL's Read Uncommitted mode behaves like Read Committed. This is because it is the only sensible way to map the standard isolation levels to PostgreSQL's multiversion concurrency control architecture.

The question doesn't sound very clear to me because isolation levels matter when you have simultaneous queries, and no other queries mentioned, so don't blame me too much if I miss something.

If we put remaining isolation levels in the order "Read committed"->"Repeatable read"->"Serializable", the overhead grows accordingly. So we need to check them in the same order, and once we are satisfied, there is no need to check remaining levels (they will be fine, but with more overhead). As far as I understand, UPDATE Courses set dept = 'New department' coursed_id = ’CPSC1350’ does the required work. From Postgre documentation  that describes Read committed isolation level,

  UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows: they will only find target rows that were committed as of the command start time. However, such a target row might have already been updated (or deleted or locked) by another concurrent transaction by the time it is found. In this case, the would-be updater will wait for the first updating transaction to commit or roll back (if it is still in progress). If the first updater rolls back, then its effects are negated and the second updater can proceed with updating the originally found row. If the first updater commits, the second updater will ignore the row if the first updater deleted it, otherwise it will attempt to apply its operation to the updated version of the row. The search condition of the command (the WHERE clause) is re-evaluated to see if the updated version of the row still matches the search condition. If so, the second updater proceeds with its operation using the updated version of the row.

I'd say I'm satisfied with such behaviour in this case, so I'll go with READ COMMITTED unless there are other restrictions not mentioned in the question.


What can be the purpose of this trigger?

You are correct; since this trigger is an INSTEAD OF INSERT trigger that simply does an insert of inserted data into the table the trigger is defined on, the only outcome will be to insert the data as if the trigger did not exist.

As with any and all changes, before you delete this trigger in production, ensure you test the change in your development/test environment.

Interestingly, if the table includes an IDENTITY column, you won't be able to create the trigger.  Consider this repro, which runs on SQL Server 2016+:

USE tempdb;
DROP TABLE IF EXISTS dbo.insert_test;
CREATE TABLE dbo.insert_test
(
    i int NOT NULL
        CONSTRAINT PK_insert_test
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , d varchar(100) NOT NULL
);
GO
CREATE TRIGGER insert_test_trigger ON dbo.insert_test
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.insert_test (i, d)
    SELECT i, d
    FROM inserted;
END
GO

INSERT INTO dbo.insert_test (d)
VALUES ('test');

SELECT *
FROM dbo.insert_test;

Results:

  Msg 544, Level 16, State 1, Procedure insert_test_trigger, Line 8 [Batch Start Line 24]
  Cannot insert explicit value for identity column in table 'insert_test' when IDENTITY_INSERT is set to OFF.


How can get this value with json sql server?

You treat question_id as if it was a child to options when it is in fact a sibling to options.

You can use json_value in the column list to get the value for question_id.

select O.choice_id,
       json_value(@json, '$.question_id') as question_id
from openjson(@json, '$.options')
     with (
            choice_id varchar(8000) '$.choice_id'
          ) as O;

If you have an array of objects on the root you would first need to shred on $ to get one row per object and then use a cross apply to get the objects.

declare @json nvarchar(max)
set @json = N'
[
   {
      "solution":"xxxxxxxxxxxxxxxxxxxxx",
      "options":[
         {
            "choice_id":205073,
            "choice":"aaaa"
         },
         {
            "choice_id":205074,
            "choice":"bbbb"
         },
         {
            "choice_id":205075,
            "choice":"cccc"
         },
         {
            "choice_id":205076,
            "choice":"dddd"
         }
      ],
      "question_id":12345
   },
   {
      "solution":"xxxxxxxxxxxxxxxxxxxxx",
      "options":[
         {
            "choice_id":205073,
            "choice":"aaaa"
         },
         {
            "choice_id":205074,
            "choice":"bbbb"
         },
         {
            "choice_id":205075,
            "choice":"cccc"
         },
         {
            "choice_id":205076,
            "choice":"dddd"
         }
      ],
      "question_id":22345
   }
]'

select json_value(T1.value, '$.question_id'),
       T2.choice_id
from openjson(@json, '$') as T1
  cross apply openjson(T1.value, '$.options')
              with (choice_id varchar(8000))as T2;


MySQL table not working after a few days of use

Part of the problem is that you are scanning most of 20K rows and locking the rows to be deleted as you go.  The scan, itself, is time consuming.

If you are using MyISAM, that is a big issue -- switch to InnoDB.

Don't use TIMEDIFF, it will get you in trouble for large differences.  I will assume that l.timestamp was some time in the past and that r.timeout is how many seconds before it "times out".

DELETE  l
    FROM  locking l,
          locking_rules r
    WHERE  l.origin = r.origin
      AND  r.name!='*'
      AND  l.timestamp > NOW() - INTERVAL r.timeout SECOND
    LIMIT 100

And have

l: INDEX(origin, timestamp)

If the DELETE is running too slowly, either increase the frequency of running it, or increase the LIMIT.  But I would not use more than 1000.

OK, the LIMIT will probably spit at you.  So, I'll provide another possibility:

DELETE  l
    FROM  locking l,
    JOIN  ( SELECT l2.id
              FROM locking l2
              JOIN locking_rules r  ON r.origin = l.origin
                                   AND l.timestamp > NOW() - INTERVAL r.timeout SECOND
              WHERE r.name!='*'
              LIMIT 100
           ) s  ON s.id = l.id;

But this will also suffer from scanning too much of locking to find the few rows that need deleting.

So, let's start over and break the task into two steps:

Find 100 ids to delete.  This is just a SELECT, so it has much less impact.
Delete those 100 (or fewer) via the PRIMARY KEY -- This is much less invasive.

thus:

CREATE TEMPORARY TABLE t
    SELECT l2.id
      FROM locking l2
      JOIN locking_rules r  ON r.origin = l.origin
                           AND l.timestamp > NOW() - INTERVAL r.timeout SECOND
      WHERE r.name!='*'
      LIMIT 100;
DELETE  l, t
    WHERE l.id = t.id;

Let's prevent the various connections from doing the "same" code at the "same" time.  (In pseudo client code:)

if (GET_LOCK('messy delete) == 1)
{
    perform the above code
    RELEASE_LOCK('messy delete');
}

It may be that the sporadic hangup is due to two DELETEs stumbling over each other, perhaps even having a 'domino effect' cascading into other connections.


Query "all of" across many-to-many relation

You can do it with "fancy Postgres" features - much easier than "fancy MS SQL features" ;)

You can aggregate all the group IDs into an array and then compare that.

If with "all of" you mean those users that are assigned to exactly those groups, you can use something like this:

SELECT u.id
FROM users u
  JOIN user_group ug on ug.user_id = u.id
group by u.id
having array_agg(ug.group_id order by ug.group_id) = array[1,2,3];

Note that the = operator for arrays depends on the order [1,2,3] is a different array than [3,1,2] that's why array_agg() uses an order by and the values in the array are sorted as well.

If with "all of" you mean those users that are assigned to at least those groups (but could be assigned to more) then you can use a simple "contains" operator:

SELECT u.id
FROM users u
  JOIN user_group ug on ug.user_id = u.id
group by u.id
having array_agg(ug.group_id order) @> array[1,2,3];

The "contains" operator @ does not depend on the order of the elements.

If you need to return the complete row from the users table, you can do the aggregation in a derived table and join to that:

SELECT u.id
FROM users u
  JOIN (
    SELECT user_id
    FROM user_group
    GROUP BY user_id
    HAVING array_agg(group_id) @> array[1,2,3]
  ) ug on ug.user_id = u.id

The second query can also be done using standard SQL:

SELECT u.id
FROM users u
  JOIN (
    SELECT user_id
    FROM user_group
    WHERE group_id in (1,2,3)
    GROUP BY user_id
    HAVING count(distinct group_id) = 3
  ) ug on ug.user_id = u.id;

This solution has the disadvantages that you need to synchronize the values for the IN list and the count(..) = 3 expressions when you change the list of IDs


Database Design for Questions and Answers

We have an application at our workplace that does a similar thing. It works by having a table that contains a list of all possible questions like such:

CREATE TABLE QUESTIONS
(
   ID INT NOT NULL PRIMARY KEY,
   SUMMARY NVARCHAR(64) NOT NULL UNIQUE,
   DESCRIPTION NVARCHAR(255) NULL
);

Then you have an ANSWERS and a QUESTIONAIRES table defined using the same structure above. Once you have these two tables you then define a table to hold the list of question/answer possibilties as such:

CREATE TABLE QUESTION_ANSWERS
(
   ID INT NOT NULL PRIMARY KEY,
   QUESTION INT NOT NULL REFERENCES QUESTIONS(ID),
   ANSWER INT NOT NULL REFERENCES ANSWERS(ID)
);

Once you have these you can then create a table to contain the responses as such:

CREATE TABLE RESPONSES
(
   QUESTIONAIRE INT NOT NULL REFERENCES QUESTIONAIRES(ID),
   RESPONSE INT NOT NULL REFERENCES QUESTION_ANSWERS(ID)
);

This will give you maximum flexibility allowing you to add new questions and answers without having to change your database design frequently. It can get a bit complicated if you need to version the questions/answers but this should give you a good foothold to work from.

I hope this helps you.


When you get into it -- really get into it -- storing componentized address data is an extremely complicated problem because of all the disparate and varied systems in use globally.

I think whatever you develop needs to be balanced between flexibility, and storing only what your business needs to store.

The biggest piece of the puzzle here is to move all address-related fields out of the Customers table -- addresses are entities unto themselves.

The space cost may not be relevant in a very small system (maybe), but this is more about a technical debt issue. If you need to start adding more address-related fields, you would have to keep adding more and more of them to the Customers table. Sooner or later, you'll realize that this is an inflexible design -- if you need to use multiple addresses for a given customer (billing & shipping addresses being the classic example), now you're in a world of hurt without normalizing, as you can't reuse the existing structure to store the required data.

At an absolute minimum, create a new table Addresses, and then reference address_id from Customers. If you want to go the multiple address route eventually, even doing just this step (as opposed to sticking with the current design) will save a massive headache later.

The address line could go in either the Addresses table directly for simplicity, or in a separate Address_Lines table to handle multiple lines. (The latter is usually preferred.)

After that, a general minimum for being able to slice and dice your data in a meaningful way is to construct normalized Countries, Regions (aka provinces/territories/etc.), and Cities tables, with only the latter appearing as a field in the Addresses table. This lets you ask business questions like "how many products did we sell in city X?" and "how many products did we sell in region Y?". (Note: depending on where you operate, what data you have, and how the data will be sliced, you may require a 4th table in there between Regions and Cities.)

If you need to get more granular ("how many products did we sell to customers on street X?"), then you'll have to start componentizing the address lines themselves, which is the really difficult part. Usually, though, a business won't ask this kind of question. Given that I don't even see a postal code field, I'm guessing this is not something you care about.


SELECT (CASE WHEN N.wbs_item_id IS NULL  THEN 'Grand Total' ELSE N.name END) AS '',
(CASE WHEN N.Materials IS NULL  THEN 'Total(Materials)', ELSE N.name END) AS '',
(CASE WHEN N.Labour IS NULL  THEN 'Total(Labour)' ELSE N.name END) AS '',

Total AS ''
FROM(
SELECT (CASE WHEN F.name='Materials' THEN F.name END ) AS 'Materials',
(CASE WHEN F.name='Labour' THEN F.name END ) AS 'Labour',wbs_item_id,SUM(Total) AS 'Total'
FROM(
SELECT b.wbs_item_id,w.name,w.parent_id,b.Total AS 'Total'
FROM wbs_items w,budgets t
LEFT JOIN  budget_items b  ON  b.wbs_item_id = w.id  AND b.id=t.id
)E,(SELECT name,id FROM wbs_item  WHERE parent_id IS NULL )F

WHERE E.wbs_item_id = F.id)N
    GROUP BY Materials,Labour,wbs_item_id WITH ROLLUP;


As you already have data in the table, you cannot add a NOT NULL field, because the existing data will already violate the NOT NULL constraint.

You therefore have 2 choices:

a) Add the column with a DEFAULT value...

ALTER TABLE dbo.Tmp_tbl_post_category ADD NgoId INT DEFAULT 1;

...then run an update statement to correct the values to what they should actually be

b) Truncate the data from the table, add the column and then re-populate the table...

TRUNCATE TABLE dbo.Tmp_tbl_post_category;

ALTER TABLE dbo.Tmp_tbl_post_category ADD NgoId INT;

INSERT INTO dbo.Tmp_tbl_post_category...


Why is intarray's push `+` so much faster than array-to-element concatenation `||`?

It seems as if || is doing a lot more for multidimensional arrays.

For instance with || catching the following case

SELECT ARRAY[ARRAY[1]] || 5;
ERROR:  argument must be empty or one-dimensional array

Whereas, with + the array is flattened..

SELECT ARRAY[ARRAY[1]] + 5;
 ?column?
----------
 {1,5}
(1 row)

In addition it seems intarray does not handle nulls.

SELECT ARRAY[null]::int[] + 5;
ERROR:  array must not contain nulls

It also seems || uses PG_RETURN_DATUM instead of passing by reference which seems to be what + uses with PG_RETURN_POINTER.


Activity Monitor (SSMS) timeout error on Sql server instance

You're running out of RAM on the server. One of the comments refers to lowering your Max Server Memory setting. For an instance with only 4GB RAM (which is not really enough to run SQL Server), your Max Server Memory should be set to 2048MB at most, possibly even lower.


Its good to restore from an earlier backup copy.If you dont have one..below are the steps that can be done to repair suspect database..this has been explained with more examples by Paul Randal here :Creating, detaching, re-attaching, and fixing a SUSPECT database

Overview of steps

set suspect database offline
Copy those mdf ldf files to some location
drop the old suspect database
create a new database with same name and same file layout
set this new database offline
delete the newly created mdf,ldf files and copy old corrupted files
now try bringing the database online and try repairing with DBCC

below are the scripts

CREATE DATABASE DEMOSUSPECT

ALTER DATABASE DEMOSUSPECT SET OFFLINE

--- you have to copy and drop the old database files prior to this
----NOW DELETE THE mdf and ldf files created for new database

--copy those corrupted files into new location
alter database demosuspect set online

 --check status once
SELECT DATABASEPROPERTYEX (N'DemoSuspect', N'STATUS')

--tyr putting the database into emergency mode and use DBCC
ALTER DATABASE [DemoSuspect] SET EMERGENCY;
GO
ALTER DATABASE [DemoSuspect] SET SINGLE_USER;
GO
DBCC CHECKDB (N'DemoSuspect', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO

-- Check the state
SELECT DATABASEPROPERTYEX (N'DemoSuspect', N'STATUS') AS N'Status';
GO


Easiest option, if you aren't relying on features specific to MyISAM or other storage engines: change your tables to the InnoDB storage engine, and use the --single-transaction option with mysqldump.

This tells mysqldump to perform all the work within a single transaction, causing InnoDB to give it a consistent point-in-time snapshot of the data, without blocking other data access (including writes).

However, MySQL DDL operations are apparently not transactional, and altering table structures during a backup can cause them to be missing from the backup with this option.

http://www.mysqlperformanceblog.com/2012/03/23/best-kept-mysqldump-secret/


What exactly is metadata? How is metadata different than regular data?

Metadata is data that describes your data.  For example "Kenneth" is my name.  It would be stored in a column called FirstName (for example).  The metadata would be things like

Variable length string with a max length of 50
First name of the user
Last updated on
Last updated by

Analytics is more

How many Kenneth's are there in the data.
What is the average number of questions by someone with the first name of Kenneth.


Philosophy on stored procedures that perform updates: is a 'granular' update approach sufficient?

Stored procs are the programming API of the database, the methods you invoke to mutate the system state. As such, I prefer to have SPs names reflect the business outcome desired rather than the implementation - change_of_address rather than update_customer. Each SP has the parameters to perform that business change and no others. Each does one thing and does it well.
If the business supports two processes that both update a common column then so be it. That column is an input to each of those SPs. Say there's a web based "change profile" and a mobile-only "register new phone". They both write to customer.phone_number. I would have separate SPs for each business purpose.
There is no measurable overhead to having many small, tightly focused stored procedures. Changes to data types are automatic on the next recompile. Changes to column names are simple search-and-replace. SSMS's built-in dependency analysis can help, too. If you use local variables that mirror columns use the column's name e.g. customer_name is read into @old_customer_name.


Express union which is dependent on other results

If I understood the question well, your task can be solved by using recursive queries. Oracle (see  and SQLServer (2005 and higher) both support it. Surely, different RDMS vendors use a slightly different syntax.


SQL Server Query Plan XML: QueryPlanHash Length

I think this is happening because that one hash is an odd number of characters. A valid VARBINARY is going to need to have an even number of "pairs" to correctly represent the data. So... you should be able to solve this by removing the 0x, putting a '0' at the beginning, grabbing the right 18 characters, and then casting it to VARBINARY.

CONVERT(VARBINARY(MAX), RIGHT('0' + SUBSTRING('0xED4F225CC0E97E5', 3, 20), 18), 2)

If you want something more robust, good luck, because you'd need to divide by 2 as an integer, and get the modulo of 2, and then "do the right thing" to figure out how big your data should be.


Mssql database old bakup restored on production server

is there any chance I can get back my original database so that I can bring back the production database to original state

There is NO possibility to do the UNDO/ROLLBACK action from the restore, you got only way to find recent backups that performed before restore operation.

Seems it's been more than 10 hours, if the database not in Full recovery mode and No Log backups, the translations whatever been posted during this 10 hours and happening now would be lost, i think you need quick action plan on this, without wasting time to find UNDO/ROLLBACK methods try to recover from recent backups by taking current state offline to avoid further lose.


Step 1 :

It's a best practise that first to upgrade MySQL 5.6 to MySQL 5.7, then from MySQL 5.7 to MySQL 8.0.

Step 2 :

Please go through MySQL 8.0 changelog to take changes in your database for deprecated & removed features otherwise upgrade end up in so many errors
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/

Step 3 :

Before, upgrading to MySQL 8.0, run upgrade checker trail using mysqlshell.
mysqlsh mysql_user@192.168.X.X:3306 -e "util.checkForServerUpgrade();" > upgrade_alert.txt

Step 4 :

Final step for upgrade should be done logical only to avoid errors in inplace upgrade.


Get inserted row count when master table isn't modified

If this is still desirable for you (although not THAT vital), this is a solution to it:

CREATE TABLE rows_affected_in_session (
  session_pid int NOT NULL,
  session_time timestamptz NOT NULL,
  table_name text NOT NULL,
  rows_affected int NOT NULL,
  PRIMARY KEY (session_pid, session_time, table_name));

CREATE OR REPLACE FUNCTION f_update_rows_affected_in_session (p_table regclass, p_rows_affected int) RETURNS void AS $BODY$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM rows_affected_in_session WHERE session_pid = pg_backend_pid() AND session_time = current_timestamp AND table_name = p_table::text) THEN
    INSERT INTO rows_affected_in_session (session_pid, session_time, table_name, rows_affected)
    VALUES (pg_backend_pid(), current_timestamp, p_table::text, p_rows_affected);
  ELSE
    UPDATE rows_affected_in_session SET rows_affected = rows_affected + p_rows_affected
    WHERE session_pid = pg_backend_pid() AND session_time = current_timestamp AND table_name = p_table::text;
  END IF;
END;
$BODY$ LANGUAGE PLPGSQL SECURITY DEFINER;

CREATE OR REPLACE FUNCTION f_rows_affected_in_session() RETURNS TABLE (table_name text, rows_affected int) AS $BODY$
  SELECT table_name, rows_affected
  FROM rows_affected_in_session
  WHERE session_pid = pg_backend_pid() AND session_time = current_timestamp
  ORDER BY 1
$BODY$ LANGUAGE SQL SECURITY DEFINER;

You use it by calling the function f_update_rows_affected_in_session(<table>, <rows_affected>) after every successful insert within triggers (which you can use ROW_COUNT diagnostics), and then use the function f_rows_affected_in_session() to retrieve the rows affected within the session (you can determine in it exactly which child tables were affected, or sum the results for a grand total). Example:

SELECT f_update_rows_affected_in_session('mytable'::regclass, 1);
SELECT f_update_rows_affected_in_session('mytable'::regclass, 1);
SELECT f_update_rows_affected_in_session('mytable'::regclass, 1);
SELECT * FROM f_rows_affected_in_session();


Creating indexes with t-sql scrips vs rebuild indexes in maintenance plan

Removing the impact of updating your non-clustered indexes during the inserts is a great way to improve bulk insert performance and something we do in my own environments.  However, I think a better alternative to you than dropping the table and/or indexes, then recreating them post-insert is to instead disable then rebuild the index.  This process removes some over head of the index creation, but accomplishes the same goal of getting the indexes out of the way.

The command to do this would be:

Disable:  ALTER INDEX foo ON bar DISABLE
Re-enable/rebuild: ALTER INDEX foo ON bar REBUILD

Depending on how you execute your ETL loads, you can build this as two steps and script out the index disable and rebuild (SQL 2008 syntax):

--Disable Indexes
DECLARE @v_sql nvarchar(max);
SELECT @v_sql = '';
SELECT @v_sql += 'ALTER INDEX '+QUOTE_NAME(name,'[')+' DISABLE;'+char(10)
FROM sys.indexes
WHERE object_id=OBJECT_ID('bar') AND type =2;

EXEC sp_executesql @v_sql;

--Rebuild Indexes
DECLARE @v_sql nvarchar(max);
SELECT @v_sql = '';
SELECT @v_sql += 'ALTER INDEX '+QUOTE_NAME(name,'[')+' REBUILD WITH(ONLINE=ON);'+char(10)
FROM sys.indexes
 WHERE object_id=OBJECT_ID('bar') AND type =2;

EXEC sp_executesql @v_sql;

Note, by declaring type=2, we are only disabling/rebuilding non-clustered indexes.  You don't want to disable/rebuild your clustered indexes as part of this process.


can't connect to pgbouncer administration database

Try with host in your connection string. For example, if you used listen_addr = 127.0.0.1 in your config file, then use:

psql -h 127.0.0.1 -p 6543 -U postgres pgbouncer


SQL to choose a price between two points in time

Quick-and-dirty, not tested on a live instance.

For each row of Event data we need one row from Price - the one which happened most recently, but before the Event's timestamp. TSQL supports the top 1 .. order by notation. By embedding the Price lookup as a sub-query in a SELECT list it will be executed once per row in Event. Predicating Price on Event's values will ensure the most recent is returned. Something like this:

select
    e.event_id,
    e.event_time,
    ( select top 1     -- return one value
        p.price
      from Prices as p
      where p.price_time <= e.event_time -- ensure the price change happened at or before the event
      order by p.price_time desc  -- top ensure "top 1" picks the price with the gretest i.e. most recent, timestamp
    ) as price
from Events as e;

The sub-query will run once per row in Events, so performance may be compromised for very large sets. Make sure there are indexes on Price.price_time.


ROUTINE TO SELECT FOM A TABLE AND UPDATE ANOTHER TABLE

The update command for Table 1 to update all names score

Would look like

UPDATE table1 as t1
INNER JOIN (SELECT DISTINCT(name) AS distname, SUM(points)-SUM(penalizes) AS score
  FROM table2
  GROUP BY name) t2
ON t1.distname = t2.distname SET t1.score = t2.score;

But better would be a trigger, which update only the rows in table1, username is actualy updated or inserted

DELIMITER $$

CREATE TRIGGER after_table2_Insert
    AFTER INSERT
    ON tabel2 FOR EACH ROW
BEGIN
    UPDATE table1 as t1
    INNER JOIN (SELECT DISTINCT(name) AS distname, SUM(points)-SUM(penalizes) AS score
      FROM table2
      Where name = NEW.name
      GROUP BY name ) t2
    ON t1.distname = t2.distname SET t1.score = t2.score;

END$$

DELIMITER ;

And something similar for updating table2

DELIMITER $$

CREATE TRIGGER after_table_update
AFTER UPDATE
ON table FOR EACH ROW
BEGIN
        UPDATE table1 as t1
        INNER JOIN (SELECT DISTINCT(name) AS distname, SUM(points)-SUM(penalizes) AS score
          FROM table2
          Where name = NEW.name
          GROUP BY name ) t2
        ON t1.distname = t2.distname SET t1.score = t2.score;
END$$

DELIMITER ;


Percona is a very popular and reputed company. Their products also meet some compliance. So there is nothing to say NO. But you mentioned that this is for Bank. Many banks have their own compliance. So, verify that you are authorized to use 3rd party software.

But the native mysqldump is not good for taking the large backup set. So its good to use percona's xtra backup or mydumper.


PostgreSQL: Pass table as argument in function

The parameter _source in the MWE (minimal working example) is not referenced anywhere. The identifier source in the function body has no leading underscore and is interpreted as constant table name independently.
But it would not work like this anyway. SQL only allows to parameterize values in DML statements. See:

Error when setting n_distinct using a plpgsql variable

Solution
You can still make it work using dynamic SQL with EXECUTE in a plpgsql function:
CREATE TYPE dataset AS (id integer, t timestamp, x float);

CREATE TABLE source OF dataset (PRIMARY KEY(Id));  -- add constraints in same command

INSERT INTO source VALUES
    (1, '2016-01-01 00:00:00', 10.0)
   ,(2, '2016-01-01 00:30:00', 11.0);

CREATE OR REPLACE FUNCTION process(_tbl regclass)
  RETURNS SETOF dataset AS
$func$
BEGIN
RETURN QUERY EXECUTE 'SELECT * FROM ' || _tbl;
END
$func$  LANGUAGE plpgsql;

SELECT * FROM process('source');  -- table name as string literal

See:

Table name as a PostgreSQL function parameter

Or search for related questions and answers on site.
To make it work for any given table:
CREATE OR REPLACE FUNCTION process2(_tbl anyelement)
  RETURNS SETOF anyelement AS
$func$
BEGIN
RETURN QUERY EXECUTE 'SELECT * FROM ' || pg_typeof(_tbl);
END
$func$  LANGUAGE plpgsql;

SELECT * FROM process2(NULL::source);  -- note the call syntax!!

Detailed explanation:

Refactor a PL/pgSQL function to return the output of various SELECT queries


No, this is not supported. And using the Oledb provider from a .net application is not supported either, use the Ado.net provider (System.Data.SqlServerCe)


What are the undocumented values for referencing_class in sys.sql_expression_dependencies?

This deficiency in the docs was raised as an issue in August 2019, with MS comment being

We've created an internal work item (1595947) to investigate and
potentially update the article.
We're going to close to this item and track with our internal work
item

Until such time as MS update the article (or accept a PR against the docs), here is a probably-partial list of the other possible values:

referencing_class referencing_class_desc
----------------- ----------------------
7                 INDEX
9                 STATISTICS

7 = Filtered index (note that regular unfiltered indexes do not feature in this system view at all)
9 = Filtered statistics

This Microsoft Support Document shows referencing_class = 7 in relation to filtered indexes.
Based on the Remarks section there are no others probably.


Can we export data from PostgreSQL to xlsx file?

PostgreSQL can't do this natively, but you can convert the CSV output to an XLSX file with a simple perl script.

csv2xlsx:

For this script to work, install Excel::Writer::XLSX and Text::CSV perl modules, either through apt-get/yum, or through sudo perl -MCPAN -e 'install Excel::Writer::XLSX' and sudo perl -MCPAN -e 'install Text::CSV'.

#!/usr/bin/perl -w
use strict;
use warnings;
use Excel::Writer::XLSX;
use Text::CSV;

my ($filename) = @ARGV;
open my $io, "-" or die "$!";

# Create a new Excel workbook
my $workbook = Excel::Writer::XLSX->new( $filename ) or die "$!";

# Add a worksheet
my $worksheet = $workbook->add_worksheet();

# Data
my $fdata = $workbook->add_format();
$fdata->set_border(1);
$fdata->set_border_color('gray');
$fdata->set_font( 'Segoe UI' );
$fdata->set_size( '8' );

# Titles
my $ftitle = $workbook->add_format();
$ftitle->copy ($fdata);
$ftitle->set_bg_color( '#B0CBF0' );

# Dates
my $fdate = $workbook->add_format();
$fdate->copy($fdata);
$fdate->set_num_format('dd/mm/yyyy' );
my $fdatetime = $workbook->add_format();
$fdatetime->copy($fdata);
$fdatetime->set_num_format('dd/mm/yyyy hh:mm:ss' );

$worksheet->set_column('A:ZZ', 15);

my $csv = Text::CSV->new({ binary => 1, eol => $/ });

my $line = 0;
while (my $row = $csv->getline($io)) {
  my @fields = @$row;
  my $col = 0;
  if($line == 0) {
    foreach my $fld (@fields) {
      $worksheet->write_string( 0, $col++, $fld, $ftitle );
    }
  } else {
    my $col = 0;
    foreach my $fld (@fields) {
      if ($fld =~ /^[0-9]/) {
        if($fld =~ /^-?(0(\.[0-9]{1,14})?|[1-9][0-9]{0,13}(\.[0-9]{1,14})?)$/) {
          $worksheet->write_number( $line, $col, $fld, $fdata );
        } elsif ($fld =~ /^[0-9]{4}-[0-9]{2}-[0-9]{2}$/) {
          $worksheet->write_date_time( $line, $col, "${fld}T", $fdate );
        } elsif ($fld =~ /^[0-9]{4}-[0-9]{2}-[0-9]{2}[ T][0-9]{2}:[0-9]{2}:[0-9]{2}(\.[0-9]+)?$/) {
          $fld =~ s/ /T/;
          $worksheet->write_date_time( $line, $col, $fld, $fdatetime );
        } else {
          $worksheet->write_string( $line, $col, $fld, $fdata );
        }
      } else {
        $worksheet->write_string( $line, $col, $fld, $fdata );
      }

      $col++;
    }
  }
  $line++;
}

Usage example:

psql -c "COPY (SELECT 1 as id, 'foo' as bar, now()::date as date) TO STDOUT CSV HEADER" template1 |\
  ./cvs2xlsx /tmp/ABC


Which file in "MySQL-5.0.27-win32" folder do i need to backup?

http://dev.mysql.com/doc/refman/5.1/en/windows-installation-layout.html

It would be the data folder that you'd want to back up for the data, but you might want to back up other folders depending on your setup.


Should we have OrderId and separate OrderNumber for E-Commerce Website or we can have only field for both?

You can do either or both, it doesn't really matter much, other than for some minor performance tuning things as Akina points out.
But yes you're allowed to have two fields that are unique, but only one primary key can exist, so you'd have to choose if that will be your OrderId or your OrderNumber, or a composite key that includes both fields.
Typically if the OrderNumber is a numeric data type field and unique then most people would just use this as the primary key, there would be no need for an OrderId (as this would be a column with a redundant purpose). Otherwise if the OrderNumber was something more complex in format, e.g. "124-72-2101-yum" then having a separate field for OrderId as your primary key would probably be a better choice.
The only other thing to consider, is I see you mentioned using UUID for OrderId. If you were to make a UUID field as your primary key (and keep the clustered index on it) there would be performance degradation when trying to JOIN your other tables to it (depending on how big your tables were). You theoretically could make it the primary key without making it the clustered index, and again if your OrderNumber field is of a numeric type then you can make that the clustered index for performance reasons, but at that point you might as well just use OrderNumber as your primary key too and get rid of the OrderId field.
An INT or BIGINT column provide just as much benefits as UUID in regards to being a primary key, but with much better performance. The only benefit UUID carries over them is somewhat global uniqueness which can be useful in certain contexts (like a mobile app that needs to function offline and be consolidated with the main database later on). Though there are ways to achieve this only using INT and BIGINT data types as your primary keys too, it just requires a little more implementation work. But even then my personal preference would be to use INT or BIGINT and put in the little bit of extra work upfront, to not worry about continually trying to optimize performance of my queries later on. And even UUID has a small chance for collisions / producing the same ID twice too (across systems), so it's not perfect.


Keep both accented and unaccented versions in a tsvector column?

My first impulse was this: Use an expression index on the unaccented strings only. Don't store tsmeta in the table redundantly. So you also don't need the trigger. And certainly don't bloat the index with original and unaccented string. This way your table and index are smaller and faster on read and write.

However, The index expression needs to be IMMUTABLE. setweight() and to_tsvector(regconfig,text) are IMMUTABLE, that's good. But unaccent() is only STABLE. Read the detailed assessment here first:

Does PostgreSQL support “accent insensitive” collations?

If you have multiple use cases, create the function wrapper f_unaccent() as instructed over there.

For just the case at hand, we can wrap the whole tsvector creation in a single IMMUTABLE function:

CREATE OR REPLACE FUNCTION f_tsmeta(_a text, _b text)
  RETURNS tsvector AS
$func$
  SELECT setweight(to_tsvector('english', public.unaccent('public.unaccent', _a)), 'A')
      || setweight(to_tsvector('english', public.unaccent('public.unaccent', COALESCE(_b, ''))), 'B')
$func$  LANGUAGE sql IMMUTABLE;

Assuming that the additional module unaccent is installed in the schema public as explained in the linked answer.

Since name is defined NOT NULL, we don't need COALESCE for that column.

Then the index can look like this:

CREATE INDEX companies_search_idx ON companies USING gin (f_tsmeta(name, description));

Used in queries like:

SELECT * FROM companies
WHERE f_tsmeta(name, description) @@ to_tsquery('english', 'foo');

On second thought, weights are not included in a GIN index. So it may pay after all to include the pre-computed value in the table. The manual:

  GIN indexes store only the words (lexemes) of tsvector values, and not
  their weight labels. Thus a table row recheck is needed when using a
  query that involves weights.

With the demonstrated expression index, the expression has to be re-evaluated when using weights, which adds extra cost.

Redundant column or expression index, it should be an improvement to integrate the unaccent dictionary into a custom TEXT SEARCH CONFIGURATION directly and build index (and table column) using that, as demonstrated by Evan in this related answer:

Creating a case-insensitive and accent/diacritics insensitive search on a field

Simplifies things and should be more efficient.


To answer my own question for anyone in the same situation: Although the DBCC CHECKTABLE ... with ALLOW_DATA_LOSS option very nicely fixed the problem and removed the corrupt pages in a test-environment, it was decided to redo the migration. That was possible because the source-data turned out to be static: nothing had changed there...


For 7GB to 9GB of database, I would advise you to just use backup and restore strategy. This is the most simplest and effective way of migrating small database. I highly recommend you look at dbatools to help you automate your migration (it also migrate your objects like logins). The most important part of your migration is you practice your rollback and making sure application/users can still connect to your database after migration. Always test before you go live.


Merge two huge tables keeping only the unique rows

The basic problem can be solved with various simple queries. Considering all columns:

CREATE TABLE tbl3 AS
TABLE tbl1
UNION TABLE tbl2;

Given this additional information:

  All columns except the id column should be considered for the unique check.

And:

  I don't need to preserve the ID column.

Just drop the id column, then you can proceed with the simple query above.

I would import to temporary tables (much faster, less overhead) and only write the final result (tbl3) to a regular table - in one session because temporary tables are dropped automatically at the end of the session.

CREATE TEMP TABLE tbl1 ( <columns from above, without id> );
COPY tbl1 FROM '/path/to/file1';

CREATE TEMP TABLE tbl2 ( <columns from above, without id> );
COPY tbl2 FROM '/path/to/file2';

Alternatively, to preserve the input tables across sessions, you could use unlogged tables.

For best performance create and fill the target with CREATE TABLE AS and add the PK constraint in the same transaction:

BEGIN;

CREATE SEQUENCE tbl3_tbl3_id_seq;

CREATE TABLE tbl3 AS
SELECT nextval('tbl3_tbl3_id_seq'::regclass)::int AS tbl3_id, *
FROM  (TABLE tbl1 UNION TABLE tbl2 ) sub;

ALTER TABLE tbl3
   ADD CONSTRAINT tbl3_pkey PRIMARY KEY(tbl3_id)
 , ALTER COLUMN tbl3_id SET DEFAULT nextval('tbl3_tbl3_id_seq'::regclass);

ALTER SEQUENCE tbl3_tbl3_id_seq OWNED BY tbl3.tbl3_id;

COMMIT;

Replace all occurrences of "tbl3" with our desired table name.

Detailed explanation in this related answer:

What causes large INSERT to slow down and disk usage to explode?

I added a serial column (tbl3_id) as surrogate PK to the target table. Adding the actual PK constraint at the end (of the same session) is the fastest way.

Related:

Optimizing bulk update performance in PostgreSQL

Before you do it, test whether double precision is the best data type for all those columns. Chances are, some of them could be integer (cheaper for whole numbers) or must really be numeric (loss-less). If so, adapt your temp tables to begin with.


Equivalent to json_strip_nulls for Postgres 9.4

I think this can be done using an custom aggregate and existing jsonb functionality:

The following aggregate will "merge" two jsonb values into one. Essentially the same as a || b (where a and b are jsonb values) but as an aggregate for multiple rows.

create aggregate jsonb_merge(jsonb)
(
    sfunc = jsonb_concat(jsonb, jsonb),
    stype = jsonb
);

With that aggregate it's possible to write a recursive function that removes keys with null values:

create function strip_nulls(p_input jsonb)
  returns jsonb
as
$$
  select jsonb_aggregate(o)
  from (
    select jsonb_build_object(t.k, case jsonb_typeof(t.v) when 'object' then strip_nulls(t.v) else t.v end) o
    from jsonb_each(p_input) as t(k,v)
    where t.v::text <> 'null'
  ) t;
$$
language sql;

The inner select turns a jsonb value into a set of key/value pairs and removes the null values recursively. Then the resulting values are aggregated back into a jsonb object.

select strip_nulls('{"one": null,
                     "two": 2,
                     "three": {"four": 1, "five": null,
                               "six": {"seven": 7, "eight": null}
                              }
                    }'::jsonb)

returns:

strip_nulls
-----------------------------------------------------
{"two": 2, "three": {"six": {"seven": 7}, "four": 1}}

The result can be cast back to json

I don't have a Postgres 9.4 installation around for testing, but I think I only used functions available in 9.4.


Repeat an action for X number of PostgreSQL tables

Thanks to the hint given in the comments by @a_horse_with_no_name I've found the answer in the documentation for the DO command: https://www.postgresql.org/docs/9.6/static/sql-do.html

CREATE TABLE new_table ();
DO $$DECLARE r record;
BEGIN
    FOR r IN SELECT table_name FROM information_schema.tables
         WHERE table_schema = 'public' AND table_type = 'BASE TABLE' AND table_name <> ALL (string_to_array('merge_table,spatial_ref_sys', ','))        LOOP
        -- Stuff that is specific to what I want to do
        EXECUTE 'CREATE TEMP TABLE temp_table AS SELECT * FROM ' || quote_ident(r.table_name) || ' NATURAL FULL OUTER JOIN new_table';
        EXECUTE 'DROP TABLE new_table CASCADE';
        EXECUTE 'CREATE TABLE new_table AS SELECT * FROM temp_table';
        EXECUTE 'DROP TABLE temp_table CASCADE';
        EXECUTE 'DROP TABLE ' || quote_ident(r.table_name) || ' CASCADE';
        -- End of the specific stuff
    END LOOP;
END$$;

Thanks to @a_horse_with_no_name for the suggestion on simplifying the exclusion of specific tables.

EDIT:

I found an even better way to exclude specific tables which at least works for my purposes. What I do is that I run the following query before I import any of the tables that I wish to perform the repeated action on:

DO $$BEGIN
    DROP TABLE IF EXISTS tables;
    CREATE TABLE tables (table_name text);
    INSERT INTO tables (
        SELECT table_name
         FROM information_schema.tables
         WHERE table_schema = 'public'
          AND table_type = 'BASE TABLE'
        );
END$$;

This creates a table containing the names of all the tables (including itself). More tables can of course be added manually with INSERT INTO tables VALUES ('new_table');.
Then in the WHERE clause of the FOR loop, instead of doing table_name <> ALL (string_to_array('merge_table,spatial_ref_sys', ',')) I do table_name NOT IN (SELECT table_name FROM tables). This is much easier to maintain than changing the string whenever new tables to be excluded are introduced.


In a recruiting website, should I force a choice between one table for jobseekers and employers or 2 separate tables?

From a user experience point of view, it would be much easier to support only one "login method" without having to select which type of login a user wants to do (e.g. a dropdown for job seeker/employer). This would also mean all usernames would need to be unique.

If you separate the tables, you can still have optimal queries, they just might have an extremely small join associated with them (or just create a denormalized view of both job seekers & employers and you have best of both worlds!).

I think the real question you should ask is:  Do job seekers & employers have fundamentally different data associated with them or is there a large amount of overlap?


Possible to CREATE EVENT NOTIFICATION with a different owner?

I thought that ALTER AUTHORIZATION might offer a way to transfer ownership, but no, according to the docs (emphasis mine):

  Ownership of the following entities cannot be transferred: <many things> and event notifications.

So, script them, drop them, and re-create them under a more generic account that is not tied to an employee.

This is similar to things like the ANSI_NULLS setting for tables (which I dealt with recently re: persisted computed columns).


Analyse MySQL General Query Log in Real-time?

If you are really interesting in monitoring via the general log, there are two major approaches you can try:

APPROACH #1 : Rotating Logs

Let's say you have the following configuration in my.cnf:

[mysqld]
general-log
general-log-file=/var/log/mysql_general.log

You could rotate the general log with the following script:

MYSQL_CONN="-uroot -prootpassword"
DT=`date +"%Y%m%d"`
OLDLOG=mysql_general_${DT}.log
cd /var/log
mysql ${MYSQL_CONN} -e"SET GLOBAL general_log = 'OFF';"
cp mysql_general.log ${OLDLOG}
echo -n > mysql_general.log
mysql ${MYSQL_CONN} -e"SET GLOBAL general_log = 'ON';"

As for your monitoring, you would scan the text file as you already do. You can always look back at previous copies of the general.log as archives.

APPROACH #2 : Make the General Log a MyISAM table

Try setting up the general log as MyISAM.

First let's configure the table for logging to tables

[mysqld]
general-log
log-output=TABLE

Next, MySQL supplies the table mysql.general_log. It looks like this:

mysql> show create table mysql.general_log\G
*************************** 1. row ***************************
       Table: general_log
Create Table: CREATE TABLE `general_log` (
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `thread_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `command_type` varchar(64) NOT NULL,
  `argument` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'

Of course, the general log as a CSV table is hardly useful. You can do two things:

Turn it into a MyISAM table
Index it however you need, starting with the event_time

Here is that code:

SET @old_log_state = @@global.general_log;
SET GLOBAL general_log = 'OFF';
ALTER TABLE mysql.general_log ENGINE = MyISAM;
ALTER TABLE mysql.general_log ADD INDEX (event_time);
SET GLOBAL general_log = @old_log_state;

When the smoke clears, mysql.general_log now looks like this:

mysql> show create table general_log\G
*************************** 1. row ***************************
       Table: general_log
Create Table: CREATE TABLE `general_log` (
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `thread_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `command_type` varchar(64) NOT NULL,
  `argument` mediumtext NOT NULL,
  KEY `event_time` (`event_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='General log'
1 row in set (0.00 sec)

Events get recorded in this table instead of the text file. Naturally, you want to rotate this as well. Here is that script

SCRIPT=/tmp/rotate_general_log_table.sql
MYSQL_CONN="-uroot -prootpassword"
DT=`date +"%Y%m%d"`
OLDLOG=general_log_${DT}
echo "SET GLOBAL general_log = 'OFF';" > ${SCRIPT}
echo "ALTER TABLE mysql.general_log RENAME mysql.${OLDLOG};" >> ${SCRIPT}
echo "CREATE TABLE mysql.general_log LIKE mysql.${OLDLOG};" >> ${SCRIPT}
echo "SET GLOBAL general_log = 'ON';" >> ${SCRIPT}
mysql ${MYSQL_CONN} < ${SCRIPT}

As for your monitoring, you would query the mysql.general_log table like this:

SELECT * FROM mysql.general_log WHERE argument LIKE '...';

Perhaps yo u may want to check the last 4 hours

SELECT * FROM mysql.general_log
WHERE event_time >= (NOW() - INTERVAL 4 HOUR)
AND argument LIKE '...';

CAVEAT

If you want both the text file and the table, then configure them both like this:

[mysqld]
general-log
general-log-file=/var/log/mysql_general.log
log-output=TABLE,FILE

Of course, you would have to implement the rotation of both.

I have written other past posts about mysql.general_log:

Apr 04, 2013 : Stress test MySQL with queries captured with general log in MySQL
Feb 11, 2012 : MySQL general log
Jan 08, 2012 : How to enable MySQL general log?
Jun 30, 2011 : How do I output MySQL logs to syslog?

Give it a Try !!!


You could try DROP INDEX [ CONCURRENTLY ] name

CONCURRENTLY

Drop the index without locking out concurrent selects, inserts,
updates, and deletes on the index's table. A normal DROP INDEX
acquires exclusive lock on the table, blocking other accesses until
the index drop can be completed. With this option, the command
instead waits until conflicting transactions have completed.
Here's the documentation for postgres 9.2:
http://www.postgresql.org/docs/9.2/static/sql-dropindex.html

Note: This feature is not available before postgres 9.2.

Here's the documentation for postgres 9.1:
http://www.postgresql.org/docs/9.1/static/sql-dropindex.html
This is similar to ONLINE = ON option in SQL Server:
http://technet.microsoft.com/en-us/library/ms176118.aspx


Using RowLock to enforce Once-And-Only-Once

You can use the OUTPUT clause to see if the chain of events should be "fired":

My test-bed:

CREATE TABLE dbo.OrderedChainFlag
(
    IsChainFired BIT NOT NULL
        CONSTRAINT DF_OrderedChainFlag_IsChainFired
        DEFAULT ((0))
    , OrderID INT NOT NULL
);

CREATE INDEX IX_OrderedChainFlag_Firing
ON dbo.OrderedChainFlag(OrderID, IsChainFired);

GO

INSERT INTO dbo.OrderedChainFlag (OrderID)
VALUES (123456);

Run this to see the output:

UPDATE OrderedChainFlag
SET IsChainFired = 1
OUTPUT inserted.IsChainFired, inserted.OrderID
WHERE OrderId = 123456
    and IsChainFired = 0;

Use a DataReader to read the results of the UPDATE statement; if a row is returned by .Read() you know the table was updated, and effectively, you can run the chain of events.

Running the UPDATE statement above will only ever provide output if it actually updated the IsChainFired column to 1.  You can prove that by running the update statement twice.  The first one shows output, the second one does not.

Since this one statement is atomic, one-and-only-one update will succeed, without affecting lock escalation, assuming you have a good index on OrderId and IsChainFired, and that index is configured WITH (ALLOW_ROW_LOCKS = ON).

Second option, which is a slight variation on the above, would be to not specify the next OrderID to be processed; instead simply select the next one that has IsChainFired = 0:

CREATE INDEX IX_OrderedChainFlag_IsFired
ON dbo.OrderedChainFlag(IsChainFired);

UPDATE TOP(1) OrderedChainFlag
SET IsChainFired = 1
OUTPUT inserted.IsChainFired, inserted.OrderID
WHERE IsChainFired = 0;

The UPDATE statement in this case returns the OrderID for a single row directly from the IX_OrderedChainFlag_IsFired index.

I inserted over 750,000 rows into the OrderedChainFlag table in my test-bed, and ran the above UPDATE TOP(1) statement, with the index I mention in place, and got the following plan:

A (arguable) bonus here is you could potentially return more than TOP(1) rows for processing, perhaps using multiple threads in your Web Service.

You should probably also read this question, along with the excellent answers on concurrency.


Two-way encryption in PostgreSQL version 9.1 database

PostgreSQL's built-in two-way encryption functions are documented here. The functions that come in the encrypt/decrypt pairs are the two-way encryption options.


By index file, you mean you place indexes on a dedicated filegroup that maps to the file in question?

Yes, it could indeed be smaller. Indexes can become fragmented over time (inserts and updates need to split pages when inserting into a range) causing free space on your pages over that defined by your fillfactor. If your fillfactor is say 80%, this is not maintained as pages are split, meaning your free space on a page could be 50%. When the index is rebuilt, your pages will be 80% full again, meaning less pages (space) is required for the index as a whole.

Do you mean your physical file reduced in size when viewed in windows explorer? The above describes the space used by an individual index. If your physical file has gone smaller, you must also have a process that shrinks the file to reclaim the lost space (autoshrink on or a job that shrinks?). In general, this is not good practice as your file will only have to grow again as more data is added and your indexes become fragmented again.


Free space of mdf and ldf does not match with database free space

This doesn't really seem all that crazy, but note that some of the UI dialogs might not have completely up-to-date information (this is why we have things like DBCC UPDATEUSAGE), and rounding can also be involved in some of those calculations. Finally, the dialogs show you total space for the whole database, but unallocated space is only calculated for the data files, not the log.

Let's coalesce some things.

Database properties and shrink database show the same thing (not that you should ever be in the shrink database UI anyway!).
Database file properties show 17 + 75 = 92 which, with rounding before addition, is probably the same 91.31 in 1.
For space allocated, shrink for individual files show 16.38 + 74.94 = 91.32 - again, probably some rounding there, otherwise exactly matching 1.
For space available, shrink for individual files is the only place where I suspect a real discrepancy, and this is because the UI is inconsistent about where it gets its data, and some of these places are subject to the caching that necessitates DBCC UPDATEUSAGE.

Let me take a look at what these different dialogs run for my local copy of AdventureWorks2012 (with certain tables enlarged from this script).

EXEC sp_spaceused;

This returns (first resultset only):

database_size    unallocated space
-------------    -----------------
   1545.81 MB          6.67 MB

Essentially runs this, which - I've confirmed via trace - is roughly the same query executed from the database properties and database shrink dialogs (I've carved out the irrelevant parts from the stored procedure, and added an outer query to represent the math that SSMS does for display):

SELECT database_size = DbSize*8.0/1024 + LogSize*8.0/1024,
  [unallocated space] = (DbSize-SpaceUsed)*8.0/1024
FROM
(
  SELECT
    (SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df
       WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize],
    SUM(a.total_pages) AS [SpaceUsed],
    (SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df
       WHERE df.type in (1, 3)) AS [LogSize]
  FROM sys.partitions p
    join sys.allocation_units a on p.partition_id = a.container_id
    left join sys.internal_tables it on p.object_id = it.object_id
) AS x;

This returns a match:

database_size    unallocated space
-------------    -----------------
    1545.8125             6.671875

These dialogs all show this information correctly. Database Properties dialog:

Shrink Database dialog:

The shrink file dialogs, on the other hand, run a slightly different query (again this is carved/adapted for convenience):

SELECT SUBSTRING(name, CHARINDEX('_',name)+1, 4),
  [Currently allocated space] = size/1024.0,
  [Available free space] = (Size-UsedSpace)/1024.0
FROM
(
  SELECT s.name,
    CAST(FILEPROPERTY(s.name, 'SpaceUsed') AS float)*CONVERT(float,8) AS [UsedSpace],
    s.size * CONVERT(float,8) AS [Size]
  FROM sys.database_files AS s
  WHERE (s.type IN (0,1))
) AS x;

Note, too, that in addition to getting size data from a function instead of a DMV, the predicates have not been updated for new file types, like filestream/hekaton.

Results:

        Currently allocated space    Available free space
----    -------------------------    --------------------
Data                         1517                  7.9375 -- wrong
Log                       28.8125               25.671875 -- wrong

The problem is the FILEPROPERTY() function, which is not guaranteed to be up to date (even after DBCC UPDATEUSAGE(0); is run; more below). This ends up with this misleading information on the dialogs:

Note, again, that 6.67 MB was never really accurate, since this is only measuring the total database size - the number of pages allocated, completely disregarding the log.

In all honesty, if you want accurate reporting of space used in the database, stop using the mickey mouse UIs which run all kinds of different queries to figure this out, and stop using the shrink file dialogs for retrieving information. These are clearly subject to stale data problems in certain cases. Run an actual query against a source you can trust. Here is what I prefer:

DECLARE @log_used DECIMAL(19,7);
CREATE TABLE #x(n SYSNAME, s DECIMAL(19,7), u DECIMAL(19,7), b BIT);
INSERT #x EXEC('DBCC SQLPERF(LogSpace);');
SELECT @log_used = u FROM #x WHERE n = DB_NAME();
DROP TABLE #x;

DECLARE @data_used DECIMAL(19,7);
SELECT @data_used = SUM(a.total_pages)*8/1024.0
FROM sys.partitions AS p
INNER JOIN sys.allocation_units AS a
ON p.[partition_id] = a.container_id;

;WITH x(t,s) AS
(
  SELECT [type] = CASE
    WHEN [type] IN (0,2,4) THEN 'data' ELSE 'log' END,
    size*8/1024.0 FROM sys.database_files AS f
)
SELECT
  file_type = t,
  size = s,
  available = s-CASE t WHEN 'data' THEN @data_used ELSE @log_used END
FROM x;

This query returns three numbers that should look very familiar, and one that should not:

file_type    size           available
---------    -----------    ----------
data         1517.000000     6.6718750
log            28.812500    17.9008512

Note that DBCC SQLPERF is also slightly prone to issues with space usage, for example after running:

DBCC UPDATEUSAGE(0);

The above query yields this instead:

file_type    size           available
---------    -----------    ----------
data         1517.000000     8.0781250
log            28.812500    17.8669481

sp_spaceused now yields matching numbers as well (1545.81 MB / 8.08 MB), even though - again - that is only the space available in the data file(s), and the database property and database shrink dialogs are "accurate" as well (but the shrink file dialogs are still way off - FILEPROPERTY() does not seem to be affected by UPDATEUSAGE at all):

Oh, and might as well show what Windows Explorer thinks of these files, so you can relate to the calculations made to determine MB:

How accurate this all needs to be, of course, depends on what you're going to do with the information.


ORA-12705: Cannot access NLS data files or invalid environment specified with jmeter test

As per Oracle FAQ

Setting the NLS_LANG environment parameter is the simplest way to specify locale behavior for Oracle software. It sets the language and territory used by the client application and the database server. It also indicates the client's character set, which corresponds to the character set for data to be entered or displayed by a client program.

So you can try explicitly setting user.country and user.language Java system properties, either add the next lines to system.properties file (lives under "bin" folder of your JMeter installation:
user.country=us
user.language=en

or pass these properties via -D command-line arguments like:
./jmeter -Duser.country=us -Duser.language=en -n -t .....

References:

How to change the LOCALE (Language and Country) and TIMEZONE for the IBM Technology for Java (IT4J) VM on the IBM i
Configuring JMeter
Apache JMeter Properties Customization Guide


Is there anything a table-level constraint declaration can do that a column-level one can not do?

You've forgotten about other types of constraint than primary key (also applies to unique, check, and foreign key constraints) but that's basically it.

A column-level constraint can only reference the column that it is declared next to. A table-level constraint can reference multiple columns.


Can I fix this insertion anomaly without using a composite key?

You can use a CHECK CONSTRAINT and scalar-function to validate if the pet and contract combo is correct based on the pet owner and contract client being the same ID.

Check out this db<>fiddle for an example of this. Also, see here for more information on CHECK CONSTRAINT.

The other alternative is to create your PK on ID columns and create a UNIQUE constraint on your composite keys, effectively making them surrogate keys.

I would probably opt for a composite key or unique constraint myself, check constraints really only enforce the business/app logic on INSERT/UPDATE to prevent the mismatch scenario, whereas keys can provide other benefits.

This is simply a solution that wasn't a composite key as per your question.


Mongo E11000 duplicate key error collection after mongo 3.4 to 3.6 upgrade

First, per the documentation you should always upgrade the Secondaries first, finishing with the primary node as per MongoDB upgrade process.

Second, since 3.6.13, MongoDB added a field userId adding a UUID into any user created to avoid _id collision such as what you seem to have encountered. See https://jira.mongodb.org/browse/SERVER-38984.


psycopg2 is a wrapper around libpq, written in C, to expose a Python DB-API compatible API to Python programs. It implements Python objects in C that call libpq functions. It has a thin Python module wrapper around it to load it and provide some of the interface functionality that's easier to write in pure Python.

Anything that implements the DB-API to connect to an external database can be reasonably called a database driver or database adapter. The two are pretty much interchangeable. Any DB-API implementation must be a Python module. Python modules are programming language libraries, in that they're collections of re-usable code. Some are also C shared libraries, in that they're compiled C code that gets linked into the cPython executable.


Discover who changed SQL Server service state

This appears to be answered here:

https://stackoverflow.com/questions/496632/is-it-possible-to-log-who-started-or-stopped-a-windows-service/496675

Moral of the story is event viewer/ system logs should show you


SQL 2 Columns of Different Types in Same Table - Working With 'Uknown' Amount

SELECT t1.*
FROM custOrders t1, custOrders t2
WHERE t1.number_orders < t2.number_orders
  AND t2.name = 'x';

PS. custOrders.name must be defined as unique by according index.


Trouble installing percona 5.6 on Google compute engine debian

I was having the exact same problem on an AWS t1.micro that has about .6GB of memory. It would go through the entire setup process then fail on initializing mysqld. As it turns out, that instance did not have enough memory to get percona server 5.6 going. I brought up a faster instance running 3.75GB of memory and it started without issue.

If I were to speculate, I would guess you are on the f1-micro instance which is why you are getting the "Cannot allocate memory" errors. Try the g1-small instance and see if that is enough to get mysql running.

Update:
I was/am using Percona's default configuration and I was able to successfully install on a Digital Ocean droplet with 1GB of memory. So, it seems as though the magic number of needed memory is between .6GB and 1GB.


Scalability limitations of PostgreSQL and MySQL

There's no simple answer to your question, but here are a few things to think about.

First, scale isn't the only thing to worry about.  What you do with your data is.  If you have 500 tables 30 TB of data, and you are doing simple OLTP with very little reporting, I don't think you will have too many problems.  There are 32TB databases on PostgreSQL out there.  However, at the same time performance will degrade somewhat because it is having to hit disk on everything.  Similarly if you have 50TB if data but have a commonly hit set of about 100GB, then you can build a server with enough RAM to keep that part of the db in memory and you are golden.

On the other hand if you are trying to take the mode (most common value) out of 1TB of data, it doesn't matter what system you are using, this is going to be painful with or without sharding. (Edit:  Sharding may, in fact, make this problem worse.)

The major problems you will run into with huge db's on MySQL and PostgreSQL involve the fact that neither supports intraquery parallelism.  In other words a query is run as a single block by a single thread, and it can't be broken down into pieces and run separately.  This is most often an issue when running large analytical queries over large amounts of data.  This is where Postgres-XC and Green Plum come to the rescue since they separate storage from execution, and can do this at the coordinator level.  Note that Postgres-XC and Green Plum essentially use sharding internally but the coordinators enforce all consistency globally.

With intraquery parallelism you can break up the query, have different processors/disk I/O channels run parts of it, and report back pieces of the result set to be assembled and passed back to the application.  Again, this is usually most helpful in analytic rather than transaction processing loads.

The second thing is that some systems, like Vertica or Greenplum store columns of information together.  This makes the system harder to use from an OLTP perspective and decreases performance there, but it drastically increases performance for large analytic workloads.  So this is a workload-specific tradeoff.

So the answer is that once you get above 1-2 TB in size you may find yourself faced with a number of tradeoffs between systems and workloads.  Again this is specific to databases, size of working sets, etc.  However at this point you really have to go with snowflake systems, i.e. ones unique and tailored to your workload.

This of course means that the limits are not generally quantifiable.

Edit:  I have now worked with a 9TB database which handles a mixture of decision support and transactional processing workloads in PostgreSQL.  The single largest challenge is that if you have questions that hit large portions of the data set, you will have to wait a while for the answer.

However with careful attention to the fundamentals (including indexes, autovacuum, how these work on the low level, etc) and sufficient computing resources, these are entirely manageable (and I estimate would be manageable well into the 30TB range in Pg).

Edit2:  Once you head to 100TB though what works will depend on your data set.  I am working on one right now that will not scale into this range because it will hit the 32TB per table limit in PostgreSQL first.


Concurrency Issues when multiple users uses Portal (MySQL and PHP)

The framework calls somewhere, somehow the command mysqldump, and there you need to give him the --lock-tables=false option.


Regular data transfer from SQL Server to MySQL

You do have Change Tracking in SQL 2012 version and you can use that to set up alerts for key data changes. And regarding, getting data from MySQL, you can create a linked server from MSSSQL server pointing to MySQL and read the data directly using OPENQUERY to sync the data between both.

See this link to set up linked server:

https://www.mssqltips.com/sqlservertip/4570/access-mysql-data-from-sql-server-via-a-linked-server/

That would be the easiest way I believe. There are other ways too as I know.


Can a SQL Server 2005 msdb database be restored as a User database?

Yes, you can, even on the same instance.

-- I assume you've already done something like this:
BACKUP DATABASE msdb TO DISK = '<backup_location>\msdb.bak' WITH INIT;

RESTORE DATABASE msdb_copy FROM DISK = '<backup_location>\msdb.bak'
  WITH REPLACE, RECOVERY,
  MOVE 'MSDBData' TO '<data folder>\msdb_copy.mdf',
  MOVE 'MSDBLog' TO '<data folder>\msdb_log.ldf';


Does oracle database suffer from parameter sniffing issue

Yes it does.
Starting with version 11.1, we have Adaptive Cursor Sharing with bind sensitivity and bind awareness, which allows the optimizer to produce multiple optimal execution plans for the same SQL statement, depending on the bind values.

Purpose of Adaptive Cursor Sharing
With bind peeking, the optimizer
peeks at the values of user-defined bind variables on the first
invocation of a cursor. The optimizer determines the cardinality of
any WHERE clause condition as if literals had been used instead of
bind variables. If a column in a WHERE clause has skewed data,
however, then a histogram may exist on this column. When the optimizer
peeks at the value of the user-defined bind variable and chooses a
plan, this plan may not be good for all values.
In adaptive cursor sharing, the database monitors data accessed over
time for different bind values, ensuring the optimal choice of cursor
for a specific bind value. For example, the optimizer might choose one
plan for bind value 10 and a different plan for bind value 50. Cursor
sharing is "adaptive" because the cursor adapts its behavior so that
the optimizer does not always choose the same plan for each execution
or bind variable value. Thus, the optimizer automatically detects when
different execution of a statement would benefit from different
execution plans.

This does wonders on paper and in presentations, but in the real world, there is still room for improvement.
Shortly: the problem with ACS is that it does not work immediately for new values. It uses the already existing execution plan optimal for other values, and after that, on a repeated attempt, it may choose a different plan. We may not have time to wait for the first sub-optimal attempt for the new bind value to finish, as it may take orders of magnitude longer depending on the actual statement and data quantity/distribution.
https://oracle-base.com/articles/11g/adaptive-cursor-sharing-11gr1


To get undo/data file size:

SELECT TABLESPACE_NAME, LOGFILE_GROUP_NAME, FILE_NAME, FILE_TYPE, EXTRA,
INITIAL_SIZE/1000000 size_init_mb,
MAXIMUM_SIZE/1000000 size_max_mb,
TOTAL_EXTENTS - FREE_EXTENTS AS extents_used,
(TOTAL_EXTENTS - FREE_EXTENTS) * EXTENT_SIZE/1000000 AS mb_used,
FREE_EXTENTS * EXTENT_SIZE/1000000 AS mb_free
FROM INFORMATION_SCHEMA.FILES WHERE EXTRA IS NOT NULL;

To change MAX_SIZE of datafile, all it need is to set the INITIAL_SIZE

ALTER TABLESPACE ts_1
ADD DATAFILE 'data_4.dat'
INITIAL_SIZE 1G
ENGINE=NDBCLUSTER;

Even though the documentations are pointing that this datafile can be located by checking the datadir in config, but somehow i can't find it in my system (by default it is at /var/lib/mysql/).


Using DISTINCT in window function with OVER

This gives the distinct count(*) for A partitioned by B:

dense_rank() over (partition by B order by A)
+ dense_rank() over (partition by B order by A desc)
- 1


Return xml sequences where an attribute doesn't contain a specific character

An easy way to do this is to use the nodes method to get right to the address attribute and check for your @ sign.

The problem with the way you're looking now is that it's only checking that any email address has an @ in it. Parsing the XML nodes out lets you check individual emails for it.

DECLARE @x XML
    = '<xml>
<customer name="Max"><email address="me@you.com" /></customer>
<customer name="Erik"><email address="erik@your-mom.com" /></customer>
<customer name="Brent"><email address="brentcom" /></customer>
</xml>';

SELECT x.c.value('@address', 'VARCHAR(100)') AS [email]
FROM   @x.nodes('/xml/customer/email') AS x(c)
WHERE  x.c.exist('@address[contains(., "@")]') = 0;

If you need to query an actual table with an XML column like this, you'd just CROSS APPLY the nodes method like thusly:

SELECT x.c.value('@address', 'VARCHAR(100)') AS [email]
FROM @x_table AS xt
CROSS APPLY xt.x.nodes('/xml/customer/email') AS x(c)
WHERE  x.c.exist('@address[contains(., "@")]') = 0;

If you want to bring all the <customer>...</customer> XML for that "row" back, you can walk the axis back. Just be aware that walking back can make performance a bit woogy for large XML blocks.

SELECT x.c.query('..')
FROM @x_table AS xt
CROSS APPLY xt.x.nodes('/xml/customer/email') AS x(c)
WHERE  x.c.exist('@address[contains(., "@")]') = 0;

Another way of doing it is:

SELECT @x.query('/xml/customer[email/@address[not(contains(., "@"))]]') answer

Moving the square brackets to wrap around the email node effectively make that the WHERE clause applied to the customer node.  Translating this XQuery to English looks like:

  Get me all xml/customer nodes with an email node which has an address attribute
  which does not contain the @ symbol


Export SQL query output to a file in a scheduler job

How about a slightly different approach?

Put that code into a .SQL file, let's call it p.sql:

spool p.txt

SELECT /*csv*/
  TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') AS "CurrentDTM",
  df.tablespace_name AS "Tablespace Name",
  SUBSTR(df.file_name,INSTR(df.file_name,'RMS_'),20) AS "File Name",
  Round(df.bytes/1073741824,2) AS "Size (GB)",
  decode(e.used_bytes,NULL,0,Round(e.used_bytes/1073741824,2)) AS "Used (GB)",
  decode(f.free_bytes,NULL,0,Round(f.free_bytes/1073741824,2)) AS "Free (GB)",
  decode(e.used_bytes,NULL,0,Round((e.used_bytes/df.bytes)*100,1)) AS "% Used"
FROM DBA_DATA_FILES DF,
     (SELECT file_id,sum(bytes) used_bytes FROM dba_extents GROUP by file_id) E,
     (SELECT sum(bytes) free_bytes,file_id FROM dba_free_space GROUP BY file_id) f
WHERE e.file_id (+) = df.file_id
  AND df.file_id  = f.file_id (+)
  AND df.tablespace_name = 'RMS_DATA'
ORDER BY df.tablespace_name,df.file_name;

spool off
exit

I'm on MS Windows, so - create a .BAT file; let's call it p.bat:

sqlplus sys/pwd as sysdba @p.sql

Finally, use your operating system scheduling program (Task Manager on my Windows) and schedule p.bat to run as often as you want, e.g.

let it run daily at 22:00
action = Start a program (navigate to p.bat)
optionally (I'd suggest you to use it), set "Start in" directory

Should work; at least, works for me.


Computed column cannot be persisted because the column is non-deterministic

You need to use a deterministic style when converting from a string representation.

You were not using a deterministic style with the conversion from string to date.

You were unnecessarily specifying a style when converting from date to datetime2.

There is a confusing mixture of date/time data types in the question.

This works (producing a datetime column):

ALTER TABLE dbo.test
ADD UTCTime AS
    ISNULL
    (
        EventTime,
        DATEADD
        (
            SECOND,
            PosixTime,
            CONVERT(datetime, '19700101', 112)
        )
    )
    PERSISTED;

As Aaron mentioned (we were answering concurrently), you do not need to persist a deterministic column to index it.


@Pat I have been facing the same issue for a long time, but a few minutes ago found way around it.

First of all DO NOT try to restore by right-clicking on an empty database. What you need to do is right-click on Databases and from the menu select Restore Database. In that UI you can use the Files option and the UI won't freeze.

Note: By doing this SQL will create your DB and restore into it in one go.

Hope it helps.


How to set SQL Server index pages per fragment?

First and foremost you should assess the impact of fragmentation. Much too often fragmentation is painted as the ultimate evil cause of all the server problems w/o any consideration of its actual impact. Fragmentation impacts several aspects:

Slow down scans due to impact on read-ahead efficiency and small IO size (page vs. extent)
Less efficient IO due to low rows per page because of frequent splits
Lower buffer pool cache hit ration (ie. lower page life expectancy) due to same as above
Higher cost impact of INSERTs due to frequent splits

All above are bad, but here is the real deal: you can have 97% fragmentation w/o any of the above symptoms being present. High fragmentation can indeed cause all these problems, but only if your specific workload actually interacts with fragmentation in a manner that causes these symptoms to appear.

I would recommend using Waits and Queues methodology to locate your actual performance bottlenecks and asses them appropriately. You may very well confirm that fragmentation has a serious impact and that would require schema and possibly application changes to address the problem, but you may discover that the problem lies somewhere else and the solution is completely different.

And lastly: you may also discover that $5k spent on more RAM and a few good SSDs alleviate completely the problems you have. Before you shrug in horror at such a remedy (what, no root cause being addressed? anathema!) consider the risk and costs associated with an application change...


Connection Manager in SSIS 2017 Package NOT Saving the Password

By default SSIS saves and encrypts sensitive data (passwords, etc.) with something called UserKey. This means that no-one else is able to use the connection with saved password other than the person who previously saved the SSIS-package.

This can be changed through the SSIS package properties -> ProtectionLevel. You should select "EncryptAllWithPassword" and set a password to "PackagePassword" property and save the package. Now when you open the package next time, SSDT will ask you to enter the password. When entered, connection should be working just fine.