Start Sql Server agent when sql server starts

There are couple of steps that you can do to address the problem that you faced :

Have a startup stored procedure using sp_procoption that runs and checks and alerts you that SQL Agent is not running.

IF EXISTS (  SELECT 1
       FROM MASTER.dbo.sysprocesses
       WHERE program_name = N'SQLAgent - Generic Refresher')
BEGIN
 SELECT @@SERVERNAME AS 'InstanceName', 1 AS 'SQLServerAgentRunning'
END
ELSE
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'yourDBMailProfile',
@recipients = 'dbateam@somecompany.com',
@body = 'Please check the status of SQL Agent. It is not running !',
@query = 'SELECT @@SERVERNAME AS [InstanceName], 0 AS [SQLServerAgentRunning]',
@subject = 'SQL Agent is not running',
@attach_query_result_as_file = 0 ; -- set it to 1 to receive as txt file attachment
END

Set SQL Agent service to restart


The ALTER TABLE statement works for me. The error message indicates that the column sign_up_ip has a DEFAULT value attached to it, which is not valid for the new data type inet. Drop the default of the column first, then convert the data type. Then add a new default (if applicable).

Even if it would seem valid. The manual, chapter "Changing a Column's Data Type":

  PostgreSQL will attempt to convert the column's default value (if any)
  to the new type, as well as any constraints that involve the column.
  But these conversions might fail, or might produce surprising results.
  It's often best to drop any constraints on the column before altering
  its type, and then add back suitably modified constraints afterwards.

There is no cast registered between varchar and inet. That's why you had to add the USING clause with an explicit conversion. Postgres makes it work via input / output conversion. But that's not possible for the saved DEFAULT expression.

You can do it all in a single DDL command to minimize friction with concurrent transactions (if that should matter):

ALTER TABLE request_logs
   ALTER COLUMN sign_up_ip DROP default
 , ALTER COLUMN sign_up_ip type inet USING ip::inet
 , ALTER COLUMN sign_up_ip SET DEFAULT '192.168.1.1';  -- arbitrary example

Using your actual column name sign_up_ip instead of the symbolic ip in your code example. Tested in Postgres 10.

If your column should hold any varchar values not valid as type inet, your conversion still fails, but this time with something like:

ERROR:  invalid input syntax for type inet: "1234.123.123.123"

That's one reason why doing all in a single statement (or at least in a single transaction) is a good idea: Everything is back to the original state after the error and the automatic ROLLBACK.


That is invalid SQL for Oracle - you can't apply sum on a boolean expression (it works on MySQL because it treats boolean expressions as numbers). You need to use a CASE for that:

SELECT pdate,
       COUNT(status) total,
       count(case when status='successful' then 1 end) successful,
       count(case when status='failed' then 1 end) failed,
       count(case when status='refunded' then 1 end) refunded,
       count(case when status='successful' then 1 end)/COUNT(status)*100 successful_percent,
       count(case when status='failed' then 1 end)/COUNT(status)*100 failed_percent,
       count(case when status='refunded' then 1 end)/COUNT(status)*100 refunded_percent
FROM payments
GROUP BY pdate;

This works because count() (like every other aggregate) ignores NULL values. The expression case when status='successful' then 1 end yields null for any status other than 'successful' and a not-null value or those that are successful, effectively counting only those that have the indicated status.


Can we use alias name in LIKE statement?

There are at least 3 different ways to do that. I will show all of them and add a link to sqlfiddle for each, but I strongly suggest you to adopt the last one.

You can wrap the query in another query and do the filtering "one level higher":

select * from (
  SELECT  id
      , (SELECT office_name
         FROM   office_master
         WHERE  office_id = wan_reports.name
        ) AS ofname
      , (SELECT City_Name
         FROM   City_Master
         WHERE  City_Id = wan_reports.location
        ) AS lname
      , wan_ip
      , (SELECT office_class
         FROM   office_master
         WHERE  office_id = wan_reports.name
        ) AS class
      , last_online
      , last_check
      , attempts
  FROM    wan_reports
) tmp
WHERE   1 = 1
        AND (id LIKE 'N%'
             OR ofname LIKE 'N%'
             OR lname LIKE 'N%'
            );

http://sqlfiddle.com/#!9/8c2a3/2

You can use HAVING (it is officially not supported as HAVING is supposed to work only on aggregated values, but it works really only as late WHERE in MySQL:

SELECT  id
      , (SELECT office_name
         FROM   office_master
         WHERE  office_id = wan_reports.name
        ) AS ofname
      , (SELECT City_Name
         FROM   City_Master
         WHERE  City_Id = wan_reports.location
        ) AS lname
      , wan_ip
      , (SELECT office_class
         FROM   office_master
         WHERE  office_id = wan_reports.name
        ) AS class
      , last_online
      , last_check
      , attempts
FROM    wan_reports
HAVING   1 = 1
        AND (id LIKE 'N%'
             OR ofname LIKE 'N%'
             OR lname LIKE 'N%'
            );

http://sqlfiddle.com/#!9/8c2a3/6

Usually the best is using joins instead of subqueries:

SELECT  id
      , office_name AS ofname
      , City_Name AS lname
      , wan_ip
      , office_class AS class
      , last_online
      , last_check
      , attempts
FROM    wan_reports
JOIN  office_master on (office_id = wan_reports.name)
JOIN  City_Master on (City_Id = wan_reports.location)
WHERE   1 = 1
        AND (id LIKE 'N%'
             OR office_name LIKE 'N%'
             OR City_Name LIKE 'N%'
            );

If there is a possibility that the matching office/city will not exist, then you need to change the corresponding inner join to a left join. That would return NULL in joined columns in case no match was found.

http://sqlfiddle.com/#!9/8c2a3/5

Subqueries have a history of poor performance in MySQL. It is becoming better witch each new version but in the optimizer can still handle joins much better. And as you can see the join version is actually shorter for writting. You only have to use "original" columns names in WHERE even if you assign them aliases for output.


Using Percentile_Disc to compute Median/Interquartiles

Median functions vary in a few ways, particularly when there's an even number of items.

I've blogged about medians, Aaron Bertrand has, Paul White has... I'm in good company. :) I'd recommend you find Aaron's material, which shows better ways to do median than PERCENTILE_CONT.

PERCENTILE_DISC will only choose a value from the list. This is fine if you have an odd number of values, but what if your list is (1, 2, 3, 4)? And then, do you choose 2 or 3? It's valid, but you should define what you need.

If you go with PERCENTILE_CONT, the answer is 2.5.

But if your set is (1, 2, 3, 3) then some would argue that your answer should be weighted by the two threes.

So you should make sure that you've defined what you mean by median. :)


Can Unicode columns include non-Unicode values in SQL Server?

Is it safe to assume columns of NVARCHAR, NTEXT, NCHAR, BIT, INT, DECIMAL, FLOAT, and DATETIME all MUST be UNICODE...

Only the XML and N-prefixed types (NCHAR, NVARCHAR, and NTEXT [which has been deprecated since SQL Server 2005 was released so please do not use it]) are Unicode. Those other types you mentioned are not strings and are not stored as strings, hence they are not relevant to this question.

  ... and therefore WILL NOT have any characters unable to be converted from UTF-16LE to UTF-8...

This is not exactly a valid question. Unicode characters are Unicode characters regardless of their encoding, whether it is UTF-8, UTF-16LE, UTF-16BE, UTF-32LE, or UTF-32BE. Now, it is possible for the data itself to contain invalid sequences, such as invalid Surrogate Pairs. But then those aren't valid characters in the UTF-8 or UTF-32 encodings either.

  ... simply by exporting the values to a .txt file and resaving them with the UTF-8 Encoding prior to importing them to MySQL?

Well, you need to be sure to save the initial export file with a Unicode-encoding. So you would use either the -N or -w options with BCP.

Also, make sure that you are doing more than just changing the Byte Order Mark (BOM) of the file and are actually converting the Unicode / UT-16LE characters to UTF-8.


MySQL/innodb trying to lock uncommitted row from parallel transaction, deadlock in result

Here, the MySQL is not trying to lock uncommitted rows, but it locks all rows from table upto auto-incremented value if index on specific column is not defined ( using which we are deleting row).

Reproduce :

Transaction 1 :

mysql> SET autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1(name) values ('shit1') ;
Query OK, 1 row affected (0.01 sec)

mysql> select * from test1;
+----+-------+
| id | name  |
+----+-------+
|  1 | shit1 |
+----+-------+
1 row in set (0.00 sec)

mysql> delete from test1 where name = 'shit1' ;
Query OK, 1 row affected (10.97 sec)

Now Let's try to delete record from Transaction 2 using column Name as well as Primary key.

Transaction 2 :

mysql> SET autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1(name) values ('shit2') ;
Query OK, 1 row affected (0.01 sec)

mysql>  select * from test1;
+----+-------+
| id | name  |
+----+-------+
|  2 | shit2 |
+----+-------+
1 row in set (0.00 sec)

mysql> delete from test1 where name = 'shit2' ;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

mysql> delete from test1 where name = 'shit2' ;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> delete from test1 where id=2 ;
Query OK, 0 rows affected (0.01 sec)

Now We have created Index on name column :

Transaction 1 :

mysql> create index IDX_NAME on test1(name);
Query OK, 0 rows affected (10.82 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from test1;
+----+-------+
| id | name  |
+----+-------+
|  3 | shit2 |
+----+-------+
1 row in set (0.00 sec)

mysql> insert into test1(name) values ('shit1') ;
Query OK, 1 row affected (0.01 sec)

mysql> delete from test1 where name = 'shit1' ;
Query OK, 1 row affected (0.00 sec)

Transaction 2 :

mysql> SET autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql>  select * from test1;
+----+-------+
| id | name  |
+----+-------+
|  3 | shit2 |
+----+-------+
1 row in set (0.00 sec)

mysql> insert into test1(name) values ('shit2') ;
Query OK, 1 row affected (0.00 sec)

mysql>  select * from test1;
+----+-------+
| id | name  |
+----+-------+
|  3 | shit2 |
|  5 | shit2 |
+----+-------+
2 rows in set (0.00 sec)

mysql> delete from test1 where name = 'shit2' ;
Query OK, 2 rows affected (0.00 sec)


Okay, I thought that was a familiar message.  That's from LINUX saying "process ran out of memory."

Well, there's not a lot more to say. The mysql process itself runs out of memory and is terminated by the OS. I dn't have a good feel for the implementation, but it appears that it's trying something like pulling the whole table into memory to do the select *.


Why I am seeing NULL value in a table where the schema defines as NOT NULL field?

Are you sure those "empty" cells are set to NULL?

It could be that they have inserted empty strings to get past the Not Null restriction. Code like this will bypass a Not Null setting:

INSERT INTO <table> (Pat_LNAME, Pat FmName, Pat_email) values ('', ' ', '  ');

Notice, in the LNAME field, I pass an empty string '' (two single quote marks). In the second, I pass in a space, ' '. In the third I pass two spaces '  '. All three will get past a NOT NULL rule.

But this code WOULD fail:

INSERT INTO <table> (Pat_LNAME, Pat FmName, Pat_email) values (null, ' ', '  ');

Or any insert that didn't have a Pat_lname field included.

In SQL Server Management Studio, your view would show you Null columns like this:

Note the yellow background and the word NULL to show there's no data.

Remember, NULL does not equal empty string.


When using expdp to create a snapshot of an oracle schema, how do I ensure that no post-export additions are retained upon using impdp to revert?

As always with Oracle, there are many ways to reach your goal. You might want to check out flashback database. This enables you to restore your database in a previously saved state and might be the easiest in your case. Using Flashback Database and Restore Points

Drop user cascade could also do the trick, followed by impdb as you described. What was the problem with the drop user action?

Try flashback database.


Does ORDER BY on a clustered key affect performance?

It almost certainly will affect performance.

If you just do a query like

Select *
From Table
Order by PrimaryKey

It likely won't affect anything at all.

Bear in mind, though, that this only determines the order of the rows at the leaf level of the clustered index.  If you do JOINs, or use other indexes that avoid key lookups, then the ORDER BY will generate additional work.

If every index and every table that you will ever JOIN or reference when querying this table are all ordered by the same key (in the same direction) then it probably won't impact performance.  Outside that very limited scenario, you will see a hit.

Only use ORDER BY when the order of the results actually matters.  In my experience it's seldom necessary.


Speed up creation of Postgres partial index

BRIN index

Available since Postgres 9.5 and probably just what you are looking for. Much faster index creation, much smaller index. But queries are typically not as fast. The manual:

  BRIN stands for Block Range Index. BRIN is designed for handling very
  large tables in which certain columns have some natural correlation
  with their physical location within the table. A block range is a
  group of pages that are physically adjacent in the table; for each
  block range, some summary info is stored by the index.

Read on, there is more.
Depesz ran a preliminiary test.

The optimum for your case: If you can write rows clustered on run_id, your index becomes very small and creation much cheaper.

CREATE INDEX foo ON run.perception USING brin (run_id, frame)
WHERE run_id >= 266 AND run_id <= 270;

You might even just index the whole table.

Table layout

Whatever else you do, you can save 8 bytes lost to padding due to alignment requirements per row by ording columns like this:

CREATE TABLE run.perception(
  id               bigint NOT NULL PRIMARY KEY
, run_id           bigint NOT NULL
, frame            bigint NOT NULL
, by_anyone        bigint NOT NULL
, by_me            bigint NOT NULL
, owning_p_id      bigint NOT NULL
, subj_id          bigint NOT NULL
, subj_state_frame bigint NOT NULL
, obj_type_set     bigint
, by_s_id          integer
, seq              integer
, by               varchar(45) NOT NULL -- or just use type text
);

Makes your table 79 GB smaller if none of the columns has NULL values. Details:

Configuring PostgreSQL for read performance

Also, you only have three columns that can be NULL. The NULL bitmap occupies 8 bytes for 9 - 72 columns. If only one integer column is NULL, there is a corner case for a storage paradox: it would be cheaper to use a dummy value instead: 4 bytes wasted but 8 bytes saved by not needing a NULL bitmap for the row. More details here:

How do completely empty columns in a large table affect performance?

Partial indexes

Depending on your actual queries it might be more efficient to have these five partial indices instead of the one above:

CREATE INDEX perception_run_id266_idx ON run.perception(frame) WHERE run_id = 266;
CREATE INDEX perception_run_id266_idx ON run.perception(frame) WHERE run_id = 267;
CREATE INDEX perception_run_id266_idx ON run.perception(frame) WHERE run_id = 268;
CREATE INDEX perception_run_id266_idx ON run.perception(frame) WHERE run_id = 269;
CREATE INDEX perception_run_id266_idx ON run.perception(frame) WHERE run_id = 270;

Run one transaction for each.

Removing run_id as index column this way saves 8 bytes per index entry - 32 instead of 40 bytes per row. Each index is also cheaper to create, but creating five instead of just one takes substantially longer for a table that's too big to stay in cache (like @Jürgen and @Chris commented). So that may or may not be useful for you.

Partitioning

Based on inheritance - the only option up to Postgres 9.5.
(The new declarative partitioning in Postgres 11 or, preferably, 12 is smarter.)

The manual:

  All constraints on all children of the parent table are examined
  during constraint exclusion, so large numbers of partitions are likely
  to increase query planning time considerably. So the legacy inheritance
  based partitioning will work well with up to perhaps a hundred partitions;
  don't try to use many thousands of partitions.

Bold emphasis mine. Consequently, estimating 1000 different values for run_id, you would make partitions spanning around 10 values each.

maintenance_work_mem

I missed that you are already adjusting for maintenance_work_mem in my first read. I'll leave quote and advice in my answer for reference. Per documentation:

  maintenance_work_mem (integer)

  Specifies the maximum amount of memory to be used by maintenance
  operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. It defaults to 64 megabytes (64MB). Since only one of these
  operations can be executed at a time by a database session, and an
  installation normally doesn't have many of them running concurrently,
  it's safe to set this value significantly larger than work_mem. Larger
  settings might improve performance for vacuuming and for restoring
  database dumps.

  Note that when autovacuum runs, up to autovacuum_max_workers times this memory may be allocated, so be careful not to set the default
  value too high. It may be useful to control for this by separately
  setting autovacuum_work_mem.

I would only set it as high as needed - which depends on the unknown (to us) index size. And only locally for the executing session. As the quote explains, a too-high general setting can starve the server otherwise, because autovacuum may claim more RAM, too. Also, don't set it much higher than needed, even in the executing session, free RAM might be put to good use in caching data.

It could look like this:

BEGIN;

SET LOCAL maintenance_work_mem = 10GB;  -- depends on resulting index size

CREATE INDEX perception_run_frame_idx_run_266_thru_270 ON run.perception(run_id, frame)
WHERE run_id >= 266 AND run_id <= 270;

COMMIT;

About SET LOCAL:

  The effects of SET LOCAL last only till the end of the current
  transaction, whether committed or not.

To measure object sizes:

Measure the size of a PostgreSQL table row

The server should generally be configured reasonably otherwise, obviously.


Curious Database building Problem (ER Diagram)

This appears to be a fairly standard multi-site case.  Model the data as if there is only one location.  This will get the basic model down correctly.  Then add a location column to the tables where location is important.  Options you may want to use are:

Simple foreign key.  (Transactions for incoming/outgoing stock.)
Multi occurrence foreign key. (Transfer records.)
Primary key column.  (Inventory records.)
Splitting tables into core data and location specific data (Customers, product details.)

  I have the AUTOSHOP "stocks" PRODUCT, AUTOSHOP "has" CUSTOMER, CUSTOMER  "purchase" SERVICE, CUSTOMER "purchase" SALE, SALE "uses" PRODUCT, SUPPLIER "supplies" STOCK plus all the necessary attributes for each one. is that correct

I would remove AUTOSHOP entirely, you should attributes that are appropriate for LOCATION.  Depending on how you are modelling PRODUCT, it may be fine.  The AUTOSHOP stock PRODUCT relationship, likely should be an INVENTORY table.  SALES (Invoices) usually have line items which may be either SERVICES or PRODUCT.  I find it is better to model in the general and specialize as needed.  The EER model is intended to make specialization simpler.

From a business perspective, I see three related subsystems: Sales, Inventory, and Purchasing.  These three systems will all share Product, and Location data.  In a single site system Location would not be required, or at least would not need to relate to the other tables.


It's possible to recover a deleted pg_hba.conf linux postgresql file?

If you are using Postgres 10, then this is possible through the view  pg_hba_file_rules

For earlier versions, I am not aware of any way to retrieve that.


PostgreSQL update and delete property from JSONB column

Question 1
There should be no signicant drawbacks. As the value is converted back to jsonb anyhow I would guess it would be more efficient to keep it that way the whole time.

Question 2
Just replace your function with the following (only the part WHERE key NOT IN ... added):

CREATE FUNCTION jsonb_merge(JSONB, JSONB)
RETURNS JSONB AS $$
WITH json_union AS (
    SELECT * FROM JSONB_EACH($1)
    UNION ALL
    SELECT * FROM JSONB_EACH($2)
) SELECT JSON_OBJECT_AGG(key, value)::JSONB
     FROM json_union
     WHERE key NOT IN (SELECT key FROM json_union WHERE value ='null');
$$ LANGUAGE SQL;


You can try moving the data by rebuilding indexes. There are some limitations to this, for example, your clustered index rebuild will have to be done OFFLINE (a limitation of ONLINE rebuilds) and any non-clustered indexes can be rebuilt online as long as the LOB data is not part of the index key or as an include column.

Also, during the index build operation, the transaction log can be backed up and truncated, provided you do not run the index operation in an explicit transaction.

Microsoft has a guide on calculating the space required for here. They also have some general recommendations such as switching to BULK-LOGGED recovery to reduce the amount of transaction log data. See here for guidelines from Microsoft.

One of the advantages to this is you can move an index at a time, so you can do this over multiple maintenance windows if need be.


Disabling IPV6 on Always on availability group

My question is will there be any problems if I unchecked IPV6 from the Adapter properties

Do not disable it, specially if you are not having any issue. WSFC does use IPV6 both for internal communication and can also be used for public network communication. Plus even if you disable IPV6 it is highly unlikely that cluster would not work or start giving some issue. I am sure that even if you disable IPV6 the WSFC would work but may be other network functionality might be affected since network is beyond scope of discussion I am not sure what might be affected but WSFC will work.

I would quote from Failover Clustering and IPv6 in Windows Server 2012 R2

  Should IPv6 be disabled for Failover Clustering?

  The recommendation for Failover Clustering and Windows in general,
  starting in 2008 RTM, is to not disable IPv6 for your Failover
  Clusters. The majority of the internal testing for Failover
  Clustering is done with IPv6 enabled. Therefore, having IPv6 enabled
  will result in the safest configuration for your production
  deployment.


Based on the above query, I have a solution for you

SET @rnk=0; SET @rank=0; SET @curscore=0;
SELECT score,userid,rank FROM
(
SELECT AA.*,
    (@rnk:=@rnk+1) rnk,
    (@rank:=IF(@curscore=score,@rank,@rnk)) rank,
    (@curscore:=score) newscore
FROM
(
    SELECT
        userid,
        floor(sum(ans = word) / count(id) * 100) as score
    FROM fb_asl.quiz
    WHERE
        DATE(`time`) BETWEEN
        CURDATE() + INTERVAL (-3) DAY AND
        AND CURDATE() + INTERVAL (-3) DAY
        AND ans IS NOT NULL
    GROUP BY userid
) AA ORDER BY score DESC) A;

Let me demonstrate it with some sample data

SAMPLE DATA

use test
DROP TABLE IF EXISTS scores;
CREATE TABLE scores
(
    id int not null auto_increment,
    score int not null,
    primary key (id),
    key score (score)
);
INSERT INTO scores (score) VALUES
(50),(40),(75),(80),(55),
(40),(30),(80),(70),(45),
(40),(30),(65),(70),(45),
(55),(45),(83),(85),(60);

SAMPLE DATA LOADED

mysql> DROP TABLE IF EXISTS scores;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE scores
    -> (
    ->     id int not null auto_increment,
    ->     score int not null,
    ->     primary key (id),
    ->     key score (score)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO scores (score) VALUES
    -> (50),(40),(75),(80),(55),
    -> (40),(30),(80),(70),(45),
    -> (40),(30),(65),(70),(45),
    -> (55),(45),(83),(85),(60);
Query OK, 20 rows affected (0.02 sec)
Records: 20  Duplicates: 0  Warnings: 0

mysql>

WHAT SAMPLE DATA LOOKS LIKE

mysql> SELECT * FROM scores;
+----+-------+
| id | score |
+----+-------+
|  1 |    50 |
|  2 |    40 |
|  3 |    75 |
|  4 |    80 |
|  5 |    55 |
|  6 |    40 |
|  7 |    30 |
|  8 |    80 |
|  9 |    70 |
| 10 |    45 |
| 11 |    40 |
| 12 |    30 |
| 13 |    65 |
| 14 |    70 |
| 15 |    45 |
| 16 |    55 |
| 17 |    45 |
| 18 |    83 |
| 19 |    85 |
| 20 |    60 |
+----+-------+
20 rows in set (0.00 sec)

mysql>

MY SOLUTION AGAINST THE SAMPLE DATA

SET @rnk=0; SET @rank=0; SET @curscore=0;
SELECT score,ID,rank FROM
(
    SELECT AA.*,BB.ID,
    (@rnk:=@rnk+1) rnk,
    (@rank:=IF(@curscore=score,@rank,@rnk)) rank,
    (@curscore:=score) newscore
    FROM
    (
        SELECT * FROM
        (SELECT COUNT(1) scorecount,score
        FROM scores GROUP BY score
    ) AAA
    ORDER BY score DESC
) AA LEFT JOIN scores BB USING (score)) A;

MY SOLUTION EXECUTED AGAINST THE SAMPLE DATA

mysql> SELECT score,ID,rank FROM
    -> (
    ->     SELECT AA.*,BB.ID,
    ->     (@rnk:=@rnk+1) rnk,
    ->     (@rank:=IF(@curscore=score,@rank,@rnk)) rank,
    ->     (@curscore:=score) newscore
    ->     FROM
    ->     (
    ->         SELECT * FROM
    ->         (SELECT COUNT(1) scorecount,score
    ->         FROM scores GROUP BY score
    ->     ) AAA
    ->     ORDER BY score DESC
    -> ) AA LEFT JOIN scores BB USING (score)) A;
+-------+------+------+
| score | ID   | rank |
+-------+------+------+
|    85 |   19 |    1 |
|    83 |   18 |    2 |
|    80 |    4 |    3 |
|    80 |    8 |    3 |
|    75 |    3 |    5 |
|    70 |    9 |    6 |
|    70 |   14 |    6 |
|    65 |   13 |    8 |
|    60 |   20 |    9 |
|    55 |    5 |   10 |
|    55 |   16 |   10 |
|    50 |    1 |   12 |
|    45 |   10 |   13 |
|    45 |   15 |   13 |
|    45 |   17 |   13 |
|    40 |    2 |   16 |
|    40 |    6 |   16 |
|    40 |   11 |   16 |
|    30 |    7 |   19 |
|    30 |   12 |   19 |
+-------+------+------+
20 rows in set (0.00 sec)

Give it a Try !!!


What to do when facts change frequently in a star schema for data warehouse?

If I don't need to track history then just add/update/delete the fact records as the Quotation Lines change.

If I do need to track history then add effective start and end dates to the fact table.

See Timespan Accumulating Snapshot Fact Tables

Credit goes to Nick White in this thread.


what shortcomings does couchbase have, compared to mongodb?

You are indeed correct!

It is very simple to deploy Couchbase instances and cluster them.  This is because Couchbase has a single node type which minimizes configuration, like you've mentioned.

What you might not know is that Couchbase has a built in caching layer, eliminating the need to install other caching services to sit next to your database.  This puts an emphasis on performance, which is critical in modern database.

UPDATE

I am a Developer Advocate at Couchbase, and listed a few advantages for you.


SQL server 2014 Transaction Log Backup fails

I Installed SQL Server 2014 Service Pack 1 (SP1) and that resolved the issue.


The cause of the error is SEGMENT CREATION DEFERRED.

This feature of Oracle was introduced ~11gR2.  It allows large applications to CREATE TABLE for everything in the application without using disk space.  The application will use 0 bytes for the tables that the customer won't use. (eg they aren't licensed for the feature(s) that uses those tables)

This feature has the side-effect of allowing you to CREATE TABLE on a tablespace which you don't have a quota.

to fix

Your CREATE USER needs to include a QUOTA clause in order to add data to that tablespace.  You could easily ALTER USER to add a quota.

There is also the over powering UNLIMITED TABLESPACE system privilege.  It grants the user QUOTA UNLIMITED on all tablespaces (including SYSTEM and SYSAUX -- which you shouldn't use).  This privilege seems to go against the security concept of "least privileges".  Use it sparingly.


Why is the padding on months in Oracle 9 characters?

The "why" goes back to the fact that when Oracle was originally built, output almost always went to some sort of fixed-width medium (either a terminal window or a physical printer).  That meant that everyone's general preference that output widths be fixed since it made sure that everything was in alignment without the programmer or the application having to do any work.  Today, of course, things aren't rendered in fixed width fonts all that often, the output of SQL statements is rarely sent to a printer, and applications have controls that make it very easy to present data in grids without anyone having to worry about blank padding output to make it align.  But the behavior of functions like TO_CHAR was defined back in the olden days so when the preferred behavior changes, the only way to handle that without breaking backwards compatibility is to provide new format masks that provide the newly desired behavior.


How to debug "IO Error: End of TNS data channel" errors?

You could check the alert log, as described in the Stack Overflow Q & A:

How to find and tail the Oracle alert log

...where the question author tardate says:

  I posted some bash and Perl scripts to find and tail the alert log on my blog some time back, and I'm surprised to see that post still getting lots of hits.
  The technique used is to lookup background_dump_dest from v$parameter.

The accepted answer by cagcowboy adds:

  If the background_dump_dest parameter isn't set, the alert.log will be put in $ORACLE_HOME/RDBMS/trace.


Safest way to perform mysqldump on a live system with active reads and writes?

All Data is InnoDB
This is what will give you an exact point-in-time snapshot of the data:
mysqldump -uuser -ppass --single-transaction --routines --triggers --all-databases > backup_db.sql

--single-transaction produces a checkpoint that allows the dump to capture all data prior to the checkpoint while receiving incoming changes. Those incoming changes do not become part of the dump. That ensures the same point-in-time for all tables.
--routines dumps all stored procedures and stored functions
--triggers dumps all triggers for each table that has them
All Data is MyISAM or Mix of InnoDB/MyISAM
You will have to impose a global read lock, perform the mysqldump, and release the global lock
mysql -uuser -ppass -Ae"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" &
sleep 5
mysql -uuser -ppass -ANe"SHOW PROCESSLIST" | grep "SELECT SLEEP(86400)" > /tmp/proclist.txt
SLEEP_ID=`cat /tmp/proclist.txt | awk '{print $1}'`
echo "KILL ${SLEEP_ID};" > /tmp/kill_sleep.sql
mysqldump -uuser -ppass --single-transaction --routines --triggers --all-databases > backup_db.sql
mysql -uuser -ppass -A < /tmp/kill_sleep.sql

Give it a Try !!!
UPDATE 2012-06-22 08:12 EDT
Since you have <50MB of total data I have another option. Instead of launching a SLEEP command into the background to hold the global read lock for 86400 sec (that 24 hr) just to get the process ID and kill outside, let's try setting a 5 second timeout in mysql rather than in the OS:
SLEEP_TIMEOUT=5
SQLSTMT="FLUSH TABLES WITH READ LOCK; SELECT SLEEP(${SLEEP_TIMEOUT})"
mysql -uuser -ppass -Ae"${SQLSTMT}" &
mysqldump -uuser -ppass --single-transaction --routines --triggers --all-databases > backup_db.sql

This is a cleaner and simpler approach for very small databases.


Secure way to connect: Windows Authentication or SQL Server Authentication and why?

Windows Authentication is going to be the most secure means, as opposed to SQL Server Authentication.  The reason behind this is because with Windows Authentication, the OS handles the auth as opposed to SQL Server handling it with its respective method.

When you use Windows Auth, SQL Server just uses the authentication token that is stored when you log into Windows (the OS).  This token-based security is more secure than SQL Server's native username/password security.

In other words if you can, choose Windows Authentication.  There are times though when you cannot get around SQL Server authentication (legacy application, third-party vendor, etc.).


Test Driven Design for postgres procedures

Test-Driven-Development (TDD) is an ideology. You can usually poke holes in implementations; and generally you sacrifice convenience and efficiency in any implementation of TDD -- which is why you have to sell it, and why it's not intuitive. You're running up against a problem which is one of the terminus ad quem of testing. You're likely trying to be a purist and often people get lost in the woods in that pursuit.

When you create a website, generally you don't test whether or not the framework, is producing a valid HTTP response. Such a test resides in a different layer, and you should only use frameworks and libraries that have a rigorous test suite that test their own domain. Anything you do to test a valid HTTP response should come up in end-to-end testing (with something like Protractor).

Think of PostgreSQL as a "framework" at a lower level that manages your data. You need to test how you get things in and get things out. Most of what PgTAP does is absolutely overkill. A ton of it is validating the schema -- how would that even work with TDD? Take..

CREATE TABLE foo ( a int PRIMARY KEY, b text, c uuid );

You're going to test

foo exits and is a table
a is an interger
b is a text field
c is a uuid

All of that is well tested to be the result of,

CREATE TABLE foo ( a int PRIMARY KEY, b text, c uuid );

You're going to drive yourself nuts testing that. If you need to validate two schemas are the same, you can simply pg_dump --schema and diff the two. You can actually generate, with great ease, a full schema test suite that tests everything.

As far as what you need to test: any procedural code in the database. Generally we do this with test-data as PostgreSQL does, or by simply having a test-database which your framework can connect to and test the procedural code like everything else.

Shy of that, you want Fat Model, Skinny Controller (put as much as your data-manipulation logic in the model) so you can use it outside of your web app, and testing that should be done like anything else in whatever language you prefer.


Is it possible to match two different Data types in PostgreSQL?

Yes, you can do this with either ANY as mentioned in @ypercube's comment, or with <@ (the containment operator)

SELECT *
FROM departments
JOIN departments_users
  ON departments._id = ANY(department_id);
 _id | departmentname | active | _id | department_id
-----+----------------+--------+-----+---------------
   1 | aa             |        |   1 | {1,2,3}
   2 | bb             |        |   1 | {1,2,3}
   3 | cc             |        |   1 | {1,2,3}
(3 rows)

If that doesn't work with an index you may need.

SELECT *
FROM departments
JOIN departments_users
  ON department_id @> ARRAY[departments._id];


How to check which users have been created by specific stored procedure in sql server?

You can't without storing something uniquely identifying, particularly if (when) users/logins are dropped and recreated by other processes than the stored procedure.

Perhaps you could store just the principal_id and create_date from sys.server_principals, and the same from sys.database_principals; nothing identifying in particular, but the pair together should be a good indication that these are the right rows.


How to prove "A->B,C then A->B and A->C" from Armstrong Axioms

Just two steps: you can use reflexivity for B C -> B and then transitivity from A -> B C and the previous dependency. Analogous solution for the second case.


Pages to MB, multiple and divide or just divide?

It's simple math. Those common equations are the same (not seem to give the same answer).

Simpler notation is the only benefit of the second one.

The first one just exlpains from where is the number 128 (8KB per page divides by 1024KB for MB size).


Need help in putting the single quote and constant timestamp in plsql

To add single quotes in a string literal, just double it, or use the chr function.

E.g. 'hello '' world' represents the string hello ' world. 'hello '||char(39)||' world' is the same. '''' is a string literal representing a single single-quote.

To get your fixed end time, just concatenate it.

select ''''||to_date(:From_date,'dd-mon-yyyy')||'''' from Scehma.table1;
select ''''||to_date(:To_date,  'dd-mon-yyyy')||' 23:59:59''' from Scehma.table1;


Animals hierarchy use-case: multiple farms, multiple species

The best approach would be neither of the two options you've proposed.  Instead you should create your own proprietary surrogate key using either a monotonically increasing integer or a GUID if that is how you like to do these things.  This key column would be NOT NULL on your animal table (obviously).

You need to keep all of the external IDs that are provided to you (internal/national) and use these to establish the mother_id and father_id of each new animal that you add to your database according to the complex rules that you've described.

This approach allows your system to take advantage of declarative referential integrity and ANSI SQL joining while preserving the mixed bag of external IDs that are provided to you for reference/reporting etc.


Comparing table structure and contents using pg_dump output from two different databases

Simplest way would be to dump the schema as plain sql and use text diff.

Or you can use tool like apgdiff which produces ddl statements representing the difference between the two database schemas.


Case-insensitive order while preserving the case of the returned strings

The solution is simple: don't use a join. This also removes the need for a DISTINCT.

select f.name
from friends f
where exists (select *
              from cities c
              where f.city_id = c.id)
order by lower(f.name);

Online example: https://rextester.com/SNA62411


Logshipping from SQL Enterprise 2012 SP1 to 2012 SP3?

Question is - can I restore from SQL 2012 SP1 to 2012 SP3 and use standby mode? Or is that counted as a different version?

Yes you can restore the user database backup and this would be possible because version remains same, although this is NOT the recommended approach. Ideally the service pack level should be same. After you have done the restore please plan to upgrade the primary server to SP3 ASAP.

  And if so, can I log ship from enterprise to standard for example?

Yes, subject to condition the backup does not includes enterprise features data or the time you took backup of enterprise DB it should not be using any enterprise features other wise restore on standard will fail. To see whether enterprise features are used query enterprise DB with

select * from sys.dm_db_persisted_sku_features


Symlinking InnoDB is definitely not a good idea for regular maintenance of InnoDB.
I wrote posts on why not to do this:

Aug 07, 2011 : MySQL InnoDB data file directly on a disk slice (fixed-size)?
Mar 12, 2012 : How to set a specific directory location for individual MySQL database?

However, if this strictly for the purpose of shrinking a large table, I guess you can symlink the whole data directory where the temp table is to go, but you will have to personally script the shrinkage.
For example

Suppose the table to shrink is mydata.mytable
datsdir is /var/lib/mysql

Your procedure would be
STEP 01: You should create a database called mydatatemp. Do not put any data in it just yet.
STEP 02: Move the /var/lib/mysql/mydatatemp over to another disk.
STEP 03: Create a Symlink /var/lib/mysql/mydatatemp over to the folder on the other disk
STEP 04: chown -R mysql:mysql against the external folder and against the symlink.
STEP 05: Now perform the data shrinkage as follows:
CREATE TABLE mydatatemp.mytable LIKE mydata.mytable;
INSERT INTO mydatatemp.mytable SELECT * FROM mydata.mytable;
DROP TABLE mydata.mytable;
ALTER TABLE mydatatemp.mytable RENAME mydata.mytable;

To recap, do the following (assuming /tmpdata is the external disk)
STEPS 01-04
chown mysql:mysql /tmpdata
ln -s /tmpdata /var/lib/mysql/mydatatemp
chown -R mysql:mysql /var/lib/mysql/mydatatemp

then run STEP 05
CAVEAT
This would be rather high risk, but it should work. Please test this with an empty table first like this:
USE mydatatemp
CREATE TABLE mrnorm (id int not null, name varchar(20) primary key (id));
INSERT INTO mrnorm (name) VALUES ('one'),('two'),('three');
SELECT * FROM mrnorm;

If these SQL command worked, go to OS and do this:
cd /tmpdata
ls -l

You should see two files:

mrnorm.frm
mrnorm.ibd

Also, test if mrnorm can be moved
CREATE DATABASE helloworld;
ALTER TABLE mydatatemp.mrnorm RENAME helloworld.mrnorm;

Go to the OS and run
cd /tmpdata
ls -l

The table should be gone. Now, do this:
cd /var/lib/mysql/helloworld
ls -l

The table should be there
END OF TEST
Keep in mind that the rename of the table would essentially be controlled copy of the table from one disk to another disk with a proper adjusting of the data dictionary section of the system tablespace in ibdata1. This could take some time.


Understanding “bitmap heap scan” and “seq scan”

The planner thinks the bitmap scan is faster.  If you want to know which is actually faster, run them and see.  To get the best info, turn on track_io_timing and run the query with EXPLAIN (ANALYZE, BUFFERS).  If your system has slow clock access, you might want to add TIMING OFF.

There is no magic source of truth we can reveal to you just by looking at your post.  If there were, we would make the planner use it; and then it would never be wrong.


Format function returning minutes instead of months?

The Format function argument is a .net format string, where mm is minutes and MM is months. You specified minutes, so minutes is what you got.

There are example format strings at Standard Date and Time Format Strings. As an alternative to MM/dd/yyyy you can also use just d which is the Short Date Format specifier (detailed on the linked page).


Postgresql and MS Access unable to insert new data

Either insert DEFAULT (unquoted) for that column, or specify the list of columns being inserted and omit the primary key column from that list.


Is it possible to create a dynamic join?

Basically it's a crosstab query:

PostgreSQL Crosstab Query

The dynamic result type is a problem, though.

  ... is it possible to create a dynamic query which will output the right thing regardless of the number of runs?

No. Currently (including Postgres 9.6) not possible with a single SELECT statement. Not unless you know the return type at call time at the latest.

SQL is a strictly typed language. Postgres demands to know the row type that is going to be returned from the query. Either provided by user input in one or the other form, or from information in the system catalog (registered tables and types). Deducing the return type from involved data dynamically is strictly not possible.

Then why does SELECT * FROM tbl work?
Because there is a row type registered for every table (view, materialized view etc.) in the system catalogs, and Postgres knows which columns to return by looking that up.

Which ever way you try, you need two round trips to the database server for your query with dynamic result type:

Determine the number of columns in the result and build the SELECT statement accordingly.
Execute said statement returning the now well known row type.

Alternatives would be:

Return two arrays instead: One containing column names, the other containing values.
Return a document type (json, hstore, ...).
Return the maximum possible number of columns and fill dangling columns with NULL values.

I have answered similar questions on SO before:

Dynamic alternative to pivot with CASE and GROUP BY
Refactor a PL/pgSQL function to return the output of various SELECT queries


Do agile software development approaches apply to SQL?

Yes. There is a whole web site devoted to it: Agile Data

I'm using it now for a brand new project.

Also see: Should you design the database before the application code is written?.

My answer there doesn't capture what I do now: 2 week sprints, good enough for that sprint. As per the Agile Data site.

I find it a good practice. It focuses on the here and now, not "we might need this". So you manage a well scoped delivery. Note: I still model using a whiteboard and meatsacks first, and it's in 5NF.


Prevent explicit insert into serial columns

In Postgres 10 or later, consider an IDENTITY column instead. Like:
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY

See:

What are Identity Columns?
Auto increment table column

Original answer for older versions:
Updateable View
In Postgres 9.4, views are automatically updatable per column. I.e., as long as basic conditions are met, columns are automatically updatable for plain references to underlying columns. Expressions are not. We can exploit this feature:
CREATE TABLE test_table(
  id serial PRIMARY KEY
, foobar text
);

CREATE VIEW test_view AS
SELECT id * 1 AS id  -- id unchanged but not updatable!
     , foobar
FROM   test_table;

By multiplying id with 1, the value is unchanged but the column is not automatically updatable any more.

REVOKE INSERT / UPDATE privileges on test_table from your users.
Make another role holding those privileges own the view.
GRANT INSERT / UPDATE on test_view to your users.

Now they can do everything, but they cannot manually set or alter the value in id. It's your choice how to handle DELETE.
This simple and quick solution works out of the box in Postgres 9.4. Automatically updatable views were introduced with Postgres 9.3, but all columns need to be updatable in that version for the feature to work.
In Postgres 9.3 or older, provide an INSTEAD OF INSERT trigger or an unconditional ON INSERT DO INSTEAD rule.
While writing rules / triggers manually, you don't need the id * 1 trick. You might want to use this even in Postgres 9.4+ to fine-tune functionality. Example:
CREATE VIEW test_view1 AS TABLE test_table;

CREATE OR REPLACE RULE ins_up AS
 ON INSERT TO test_view1 DO INSTEAD
INSERT INTO test_table (foobar) VALUES (NEW.foobar);

Now, INSERT on the view is possible, but not yet UPDATE or DELETE. Write more rules if you want that.
SQL Fiddle  (Postgres 9.6)
Important difference: while the automatically updatable view rejects attempts to INSERT / UPDATE values in id with an exception, the demonstrated RULE simply ignores values for id and proceeds without exception.
Simple trigger
Alternatively, you could simply overwrite the serial column with the next value from its sequence unconditionally:
CREATE OR REPLACE FUNCTION force_serial_id()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   NEW.id := nextval(pg_get_serial_sequence(quote_ident(TG_TABLE_NAME), 'id'));
   RETURN NEW;
END
$func$;

That's simpler and cheaper and less error-prone than trying to be smart about it. quote_ident() to safely escape otherwise illegal names (also defends against SQL injection).
Like @dezso commented, this burns two numbers per row in normal operation with a serial column because a default is fetched before the trigger function kicks in. Typically, gaps in a sequence should not be a problem (to be expected anyway), but you can avoid the side effect by removing the DEFAULT from the column. Then you rely on the trigger exclusively.
You could fine-tune the UPDATE case with a separate trigger and a condition on the trigger itself WHEN (OLD.id <> NEW.id). Syntax example:

How to prevent a PostgreSQL trigger from being fired by another trigger?

Note the use of pg_get_serial_sequence(), which won't break like your original for non-basic identifiers. Think of "MyTable" or a non-default sequence name. Still assuming the column name id which I personally never use since it's not descriptive.


Why do primary keys have names of their own?

Primary keys (and other unique constraints) are implemented as indexes, and are dealt with in exactly the same way - it doesn't make sense from the programmer's point of view to have separate code paths for PKs and indexes (it would double up the potential for bugs).

Other than being referred to by foreign keys, a PK is just a unique constraint which is in turn implemented as an index, so changing the properties of a PK is just the same as changing the properties of any other index. Also having an explicit name means they can be referred to in query hints like any other index.


What You make right - it is backup all files from /var/lib/mysql

all other steps - wrong

Your current error (in PHPMyAdmin with phantom tables) - because You have Your database folder with .frm files for table structure, and empty (new) ibdata.

What You can do now - revert all back (restore files in it original place)
Then You can:
- check error log file, for error message
- start change innodb_force_recovery from 1 to 6


Error running PostgreSQL query from SQL Server 2016

select main.ResellerId, main.vCompanyName , x."count", x.vReportTypeName
from wlt_tblReseller AS main
LEFT JOIN   ( select (count (ipkReportTypeId)) AS "count", vReportTypeName, RootResellerId
              from wlt_tblReseller _all
              inner join wlt_tblClient clients  on clients.ifkParentResellerId = _all.ResellerId
              inner join wlt_tblReport_CompanyMaster reporslogs on reporslogs.ifkCompanyId = clients.ClientId
              inner join wlt_tblReports_TypeMaster rpt_types on rpt_types.ipkReportTypeId = reporslogs.ifkReportTypeId
              where
                    rpt_types.bStatus =1
                and bIsStatic =1
                and vReportTypeName  is not null
              group by ipkReportTypeId,vReportTypeName
            ) AS x ON x.RootResellerId = main.ResellerId
WHERE IsMiniReseller = 0
  and ResellerId <> 1
  and vReportTypeName is not null
order by  vCompanyName desc

PS. Add table aliases to all column names for to remove potential ambiguity...


I managed to solve this using this query:
update
   myTable
set
   entities =
   (
      select
         to_jsonb(array_agg (new_values))
      from
         (
            select
(
               case
                  when
                     arr -> 'content' ->> 'align' = 'leftAlign'
                  then
                     jsonb( COALESCE( jsonb_set(arr, '{content}', jsonb(arr ->> 'content') - 'align' || jsonb('{"align": "left"}'), true), '{}' ) )
                  when
                     arr -> 'content' ->> 'align' = 'rightAlign'
                  then
                     jsonb( COALESCE( jsonb_set(arr, '{content}', jsonb(arr ->> 'content') - 'align' || jsonb('{"align": "right"}'), true), '{}' ) )
                  when
                     arr -> 'content' ->> 'align' = 'centerAlign'
                  then
                     jsonb( COALESCE( jsonb_set(arr, '{content}', jsonb(arr ->> 'content') - 'align' || jsonb('{"align": "center"}'), true), '{}' ) )
                  else
                     arr
               end
) as new_values
            from
               jsonb_array_elements( entities ) as arr
               group by
                  arr -> 'content' ->> 'align',
                  arr
         )
         as new_values
   )
;


Your ALTER DEFAULT PRIVILEGES doesn't grant to "postgres" (unless "postgres" is also a member of "readwrite") and only grants for tables created by "postgres" (assuming you were logged on as "postgres" at the time you ran it).

Execute the ALTER DEFAULT PRIVILEGES again, this time while logged in as "confere".  And either grant "readwrite" to "postgres", or change the TO part of the ALTER DEFAULT PRIVILEGES.


How do you delete achieve log image copies that are already backed up?

all and until time does not work together, choose one:

delete copy of archivelog until time 'sysdate-1' backed up 1 times to disk;

or

delete copy of archivelog all backed up 1 times to disk;


what are the impacts if I convert one of the column to primary key or add a primary key to an existing table

Having primary key is absolute necessity for any table.

But obviously before you do that you need to see whether adding a primary key will affect the application itself. If written in ASP.Net it will require model update. Also without primary key entity model looks at it as a view, which is mostly used for displaying data. Adding primary key would cause a conflict and you would have to explicitly create a new view.

If you already have a clustered index on some column, adding a primary key on another column would simply create a primary key constraint. Where as if you do not have a clustered index (heap table), and create a primary key it will implicitly create a clustered index as well on that column.

Probably worst option would be creating a new table and migrating data, simply due to the fact that you would loose all the indexes(if any) were on the table,also if the table had some relationship with the other table you wouldnt be able to delete it. Or if explicitly some one before you added some security rights on the table, would be lost.

If possible try to add primary key on already created table, and existing column.


Does the order of columns in a table's definition matter?

Does the logical ordering of columns in a table have any impact on their physical order at the storage layer? Yes.

Whether it matters or not is a different issue that I can't answer (yet).

In a similar manner to that described in the frequently linked article from Paul Randal on the anatomy of a record, let's look at a simple two column table with DBCC IND:

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

USE master;
GO

IF DATABASEPROPERTY (N'RowStructure', 'Version') > 0 DROP DATABASE RowStructure;
GO

CREATE DATABASE RowStructure;
GO

USE RowStructure;
GO

CREATE TABLE FixedLengthOrder
(
    c1 INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
    , c2 CHAR(10) DEFAULT REPLICATE('A', 10) NOT NULL
    , c3 CHAR(10) DEFAULT REPLICATE('B', 10) NOT NULL
);
GO

INSERT FixedLengthOrder DEFAULT VALUES;
GO

DBCC IND ('RowStructure', 'FixedLengthOrder', 1);
GO

The output above shows that we need to look at page 89:

DBCC TRACEON (3604);
GO
DBCC PAGE ('RowStructure', 1, 89, 3);
GO

In the output from DBCC PAGE we see c1 stuffed with the character 'A' before c2's 'B':

Memory Dump @0x000000000D25A060

0000000000000000:   10001c00 01000000 41414141 41414141 †........AAAAAAAA
0000000000000010:   41414242 42424242 42424242 030000††††AABBBBBBBBBB...

And just because, lets bust open RowStructure.mdf with a hex editor and confirm the 'A' string preceeds the 'B' string:

Now repeat the test but reverse the order of the strings, placing the 'B' characters in c1 and the 'A' characters in c2:

CREATE TABLE FixedLengthOrder
(
    c1 INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
    , c2 CHAR(10) DEFAULT REPLICATE('B', 10) NOT NULL
    , c3 CHAR(10) DEFAULT REPLICATE('A', 10) NOT NULL
);
GO

This time our DBCC PAGE output is different and the 'B' string appears first:

Memory Dump @0x000000000FC2A060

0000000000000000:   10001c00 01000000 42424242 42424242 †........BBBBBBBB
0000000000000010:   42424141 41414141 41414141 030000††††BBAAAAAAAAAA...

Again, just for giggles, lets check the hex dump of the data file:

As Anatomy of a Record explains, the fixed and variable length columns of a record are stored in distinct blocks. Logically interleaving fixed and variable column types has no bearing on the physical record. However, within each block the order of your columns does map to the order of bytes in the data file.

CREATE TABLE FixedAndVariableColumns
(
    c1 INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
    , c2 CHAR(10) DEFAULT REPLICATE('A', 10) NOT NULL
    , c3 VARCHAR(10) DEFAULT REPLICATE('B', 10) NOT NULL
    , c4 CHAR(10) DEFAULT REPLICATE('C', 10) NOT NULL
    , c5 VARCHAR(10) DEFAULT REPLICATE('D', 10) NOT NULL
    , c6 CHAR(10) DEFAULT REPLICATE('E', 10) NOT NULL
);
GO

Memory Dump @0x000000000E07C060

0000000000000000:   30002600 01000000 41414141 41414141 †0.&.....AAAAAAAA
0000000000000010:   41414343 43434343 43434343 45454545 †AACCCCCCCCCCEEEE
0000000000000020:   45454545 45450600 00020039 00430042 †EEEEEE.....9.C.B
0000000000000030:   42424242 42424242 42444444 44444444 †BBBBBBBBBDDDDDDD
0000000000000040:   444444†††††††††††††††††††††††††††††††DDD

See also:

Column order doesn’t matter… generally, but – IT DEPENDS!


Is there a way to determine what caused the last growth operation of my database (MDF file)?

You could use the undocumented function sys.fn_dblog filtering by the collumn transaction name like this:

SELECT [Transaction Name]
FROM sys.fn_dblog(NULL,NULL)
WHERE [Transaction Name] IS NOT NULL;

On the query result you should look for SetFileSize

The line before the SetFileSize will show the last event that occured to cause the growth.


MariaDB 10.2 InnoDB internal data dictionary problem

Unfortunately, MariaDB 10.2 changed the data format in a way which breaks compatibility with 10.1 - issue MDEV-12873 - so although the upgrade will "succeed", you no longer have any access to your data.

Apparently 10.2.7 will fix this bug; in the mean time, it looks as if you just have to revert to your most recent backup. (To add insult to injury, the failed "upgrade" also prevents direct reversion, so you need to dig out a full backup and restore from that.) It's pretty troubling for me that this managed to slip in to a "stable release".


Is there a way I can speed up queries against this particular view?

There might be hardware issues, too - how should we know? But there are certainly issues with the query.

First of all, remove DISTINCT from your VIEW definition. It's doing nothing at all (but complicating and slowing things down). Related answer on SO with explanation:

PostgreSQL - Slow query joining on a VIEW

Arriving at this (cleaned up) query:

SELECT i.addr      AS ip_address
     , a.some_info AS network_info
     , COUNT(b.ipaddr) AS ip_info_count
FROM   ipaddrs           i
LEFT   JOIN ipaddr_info  b  ON i.addr = b.ipaddr
LEFT   JOIN network_info a  ON i.addr << a.network
GROUP  BY 1,2;

Next, the query looks suspiciously like it's going very wrong. Two uncorrelated joins can multiply rows:

Two SQL LEFT JOINS produce incorrect result

With 150k rows in each table, there is potential for a huge (unintended) Cartesian product. My educated guess, you really want this:

SELECT addr        AS ip_address
     , a.some_info AS network_info
     , b.ip_info_count
FROM   ipaddrs i
LEFT   JOIN (
   SELECT  ipaddr AS addr, count(*) AS ip_info_count
   FROM    ipaddr_info
   GROUP   BY 1
  ) b USING (addr)
LEFT   JOIN network_info a ON i.addr << a.network

I suspect that GROUP BY is also not needed in the outer SELECT now.
Besides fixing the count, this might also be faster by several orders of magnitude, avoiding the proxy cross-join.

You could first join to ipaddrs (especially if you have predicates filtering rows from it) and then aggregate, or first aggregate in the subquery like displayed. Usefulness of this variant largely depends on data distribution. It's great for few ipaddr with big counts. Details:

Slow queries related to subqueries using aggregation

Finally, you need index support. Equality between ipaddr and addr is covered by the default btree indexes of the PRIMARY KEY. The query on the whole table is probably using a sequential scan anyway.

For the "is contained by" operator << operator you'll need a GIN or GiST index. The best option would be the new inet_ops GiST index operator class in Postgres 9.4 (supports data types inet and cidr):

CREATE INDEX ON network_info USING gist (network inet_ops);

Not sure if the index can be used in a plain INNER (or OUTER) join. Can't test right now. Maybe you need correlated subqueries or a LATERAL join to utilize the index:

SELECT addr AS ip_address
     , a.network_info
     , b.ip_info_count
FROM   ipaddrs i
LEFT   JOIN (
   SELECT  ipaddr AS addr, count(*) AS ip_info_count
   FROM    ipaddr_info
   GROUP   BY 1
  ) b USING (addr)
LEFT   JOIN LATERAL (
   SELECT some_info AS network_info
   FROM   network_info
   WHERE  network >> i.addr
   ) a ON TRUE;

Advice for indexing in older versions:

PostgreSQL field data type for IPv4 addresses


Figured out the problem last weekend. After trying to run a repair on my SQL Server 2012, it reported that the user Administrator doesn't have the proper privileges in order to proceed with the installation, so I had to do the following:

Provide Full Control through Group Policy Management Editor in Computer configurations -> Policies -> Windows Settings -> Security Settings -> Local Policies -> User Rights Assignment to:

Back Up files and directories
Debug programs
Manage auditing and security log

More on this error here:
SQL Server installation fails


Are there known parallelism issues with Sql Server 2016?

Problem was due to use of the Vmware CPU Hot Plug feature in combination with Sql Server 2016.

This feature disables vNUMA and is not recommended by Microsoft or Vmware (see document Architecting Microsoft Sql Server on Vmware vSphere).

Switching this feature off and then rebooting the server resolved this issue and it resulted in average query performance of over 50% as well as showing improved utilization of CPUs.

Since we were running this Vmware setting when using Sql Server 2012 and did not experience freezing/hanging queries, my guess is something in 2016 makes this environmental setup more problematic. It could be the new default "soft Numa" configuration in 2016.

Btw...the query plan did not seem affected by this Vmware setting.  You can however see a difference when you run select * from sys.dm_os_memory_nodes;  With Numa switched off there is an added "Node 1" and the virtual address space reserved in node 0 goes from 100% of max mem with no Numa to 50% of total max mem when Numa awareness in vmware is turned on.


Dumping a database into a file from terminal freezes

mysql is for interacting with the database.

mysqldump is what you want for this task.


Adding coordinates to table (forcing 1:1 association without unique identifiers on either side)

PostgreSQL uses PostGIS for spatial stuff. You're in good hands, out of all the RDBMs GIS extensions PostGIS is the most powerful.

Use the Loader_Generate_Script. It'll return a table with a geometry column.

Then you just have to run

ALTER TABLE tiger.census_table ADD random_geom geometry(4326, POINT);
UPDATE tiger.census_table
  SET random_geom = ST_GeneratePoints(census_table.geom,1);

That's the easy way. It's a 5 min job.

You usually don't want a random point though. You usually want the centroid to be useful.


You can get something similar to Oracle's CARDINALITY hint by strategically using TOP and a user defined function called MANY() developed by Adam Machanic. Let's work through a few examples. I'm using the freely available AdventureWorks database. Suppose that I really need to control the number of rows returned by the th derived table in the following query:
SELECT
    p.Name
    , th.ProductId
    , th.Quantity
    , th.ActualCost
FROM Production.Product p
INNER JOIN (
    SELECT ProductId, Quantity, ActualCost
    FROM Production.TransactionHistory
) th ON p.ProductID = th.ProductID;

As is, I get an estimate of 113443 rows:

If I need to lower the estimate from th I can use TOP along with the OPTIMIZE FOR query hint to set a row goal. Here's one way to do it:
DECLARE @row_goal BIGINT = 9223372036854775807;
SELECT
    p.Name
    , th.ProductId
    , th.Quantity
    , th.ActualCost
FROM Production.Product p
INNER JOIN (
    SELECT TOP (@row_goal) ProductId, Quantity, ActualCost
    FROM Production.TransactionHistory
) th ON p.ProductID = th.ProductID
OPTION (OPTIMIZE FOR (@row_goal = 1));

We can see that the estimate is just 1 row:

I set @row_goal to the largest possible BIGINT value to avoid changing the results. The OPTIMIZE FOR query hint instructs the optimizer to optimizer the query as if @row_goal is equal to 1. I will get the same results but the query will be optimized differently.
Increasing a cardinality estimate is trickier. We can't just increase the value for TOP because the optimizer will realize that it won't return enough rows. However, we can use the MANY() function to add rows to the estimate. Note that the MANY() function will always return 0 rows but the row estimate from it changes with the input parameter. Suppose that you need to increase the row estimate from the derived table by 10X. One way to accomplish that:
SELECT
    p.Name
    , th.ProductId
    , th.Quantity
    , th.ActualCost
FROM Production.Product p
INNER JOIN (
    SELECT TOP (9223372036854775807) ProductId, Quantity, ActualCost
    FROM Production.TransactionHistory
    LEFT OUTER JOIN dbo.Many(10) AS m ON 1=1
) th ON p.ProductID = th.ProductID;

We can see that the estimate is 10X the base table:

The superfluous TOP was added to prevent the optimizer from moving the tables around. Without it the MANY() function can be applied to the wrong place in the plan.
It is possible to combine the two techniques if you want a precise overestimate instead of just multiplying the number of rows by a factor. For example, let's suppose that you really need the estimate of the derived table to be exactly 1000000 rows. One way to accomplish that:
DECLARE @row_goal BIGINT = 9223372036854775807;

SELECT
    p.Name
    , th.ProductId
    , th.Quantity
    , th.ActualCost
FROM Production.Product p
INNER JOIN (
    SELECT TOP (@row_goal) ProductId, Quantity, ActualCost
    FROM Production.TransactionHistory
    LEFT OUTER JOIN dbo.Many(10) AS m ON
        1=1
) th ON p.ProductID = th.ProductID
OPTION (OPTIMIZE FOR (@row_goal = 1000000));

We can see that the estimate is 1000000 rows:

I need to caution you that these are advanced techniques which often aren't needed for query optimization. If you'd like to learn more I recommend watching Clash of the Row Goals presented by Adam Machanic.

dbo.Many function
-- By Adam Machanic, reproduced with permission
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'Many' AND OBJECT_SCHEMA_NAME(object_id) = 'dbo')
    DROP FUNCTION dbo.Many
GO
CREATE FUNCTION dbo.Many(@n INT)
RETURNS TABLE AS
RETURN
(
    WITH
    a(x) AS
    (
        SELECT
            *
        FROM
        (
            VALUES
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)
        ) AS x0(x)
    )
    SELECT TOP(@n)
        1 AS x
    FROM
        a AS a1,
        a AS a2
    WHERE
        a1.x % 2 = 0
)
GO


Show all privileges given to a schema or user in postgres

Let me guess: you are an Oracle user, right? Because you mix up “user” and “schema”.
In PostgreSQL, the right to create tables, views, functions, operators, data types and the like is not properties of the user (or “role” in PostgreSQL).
You manage this with privileges on schemas: if there is a schema where the user has the CREATE privilege, the user can create any object he or she wishes in that schema. If you want to keep a PostgreSQL user from creating objects, you don't give them CREATE on any schema. Since the public schema by default gives CREATE to everybody, you'd have to
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

which is a good idea anyway for security reasons.
There are only few privileges that are tied to the user, because they pertain to global objects or no objects at all:

CREATEDB: the right to run CREATE DATABASE
CREATEROLES: the right to run CREATE ROLE
REPLICATION: the right to establish a replication connection; that is for example needed for pg_basebackup


Choice of authentication approach for financial app on PostgreSQL

I think you're conflating authentication and authorization.

I completely agree that keeping the security model in the DB is wise, especially as LedgerSMB is designed with access from multiple clients in mind. Unless you plan on going 3-tier with a middleware layer it makes perfect sense to have users as database roles, especially for something like an accounting app.

This does not mean you have to authenticate users against the database using a PostgreSQL-supported authentication method. Your database users, roles and grants can be used for authorization only if you like.

Here's how it works for a web ui for example:

jane connects to web ui server and authenticates using whatever method is desired, say HTTPS X.509 client certificate handshake and DIGEST auth. The server now has a connection from a user it accepts is really jane.
The server connects to PostgreSQL using a fixed username/password (or Kerberos or whatever you like), authenticating its self to the db server as the user webui. The db server trusts webui to authenticate its users so webui has been given appropriate GRANTs (see below).
On that connection the server uses SET ROLE jane; to assume the authorization level of the user jane. Until RESET ROLE; or another SET ROLE is run, the connection is operating with the same access rights as jane and SELECT current_user() etc will report jane.
The server maintains the association between the database connection on which it has SET ROLE to jane and the web session for user jane, not allowing that PostgreSQL connection to be used by other connections with other users without a new SET ROLE inbetween.

You are now authenticating outside the server, but maintaining authorisation in the server. Pg needs to know what users exist, but doesn't need passwords or authentication methods for them.

See:

SET SESSION AUTHORIZATION
SET ROLE
GRANT

Details

The webui server controls the queries run, and it's not going to let jane run raw SQL (I hope!) so jane can't RESET ROLE; SET ROLE special_admin_user; via the web ui. For added safety I'd add a statement filter to the server that rejected SET ROLE and RESET ROLE unless the connection was in or entering a pool of unassigned connections.

You're still free to use direct authentication to Pg in other clients; you can mix and match freely. You just have to GRANT the webui user the rights to SET ROLE to users who can log in via the web and then give those users any normal CONNECT rights, passwords, etc you want. If you want to make them web-only, REVOKE their CONNECT rights on the database (and from public).

To make such an authentication/authorisation split easy I have a special role assume_any_user that I GRANT every newly created user to. I then GRANT assume_any_user to the real username used by things like a trusted web front-end, giving them the rights to become any user they like.

It's important to make assume_any_user a NOINHERIT role, so the webui user or whatever has no privilges by its self and can only act on the database once it's SET ROLE to a real user. Under no circumstances should webui be a superuser or DB owner.

If you're connection pooling, you can use SET LOCAL ROLE to set role only within a transaction, so you can return connections to the pool after COMMIT or ROLLBACK. Beware that RESET ROLE still works, so it still isn't safe to let the client run whatever SQL they want.

SET SESSION AUTHORIZATION is the related but stronger version of this command. It doesn't require role membership, but it's a superuser only command. You don't want your web ui connecting as a superuser. It can be reversed with RESET SESSION AUTHORIZATION, SET SESSION AUTHORIZATION DEFAULT or SET SESSION AUTHORIZATION theusername to regain superuser rights so it isn't a privilege-dropping security barrier either.

A command that worked like SET SESSION AUTHORIZATION but was irreversible and would work if you were a role member but not superuser would be great. At this point there isn't one, but you can still separate authentication and authorisation pretty well if you're careful.

Example and explanation

CREATE ROLE dbowner NOLOGIN;
CREATE TABLE test_table(x text);
INSERT INTO test_table(x) VALUES ('bork');
ALTER TABLE test_table OWNER TO dbowner;

CREATE ROLE assume_any_user NOINHERIT NOLOGIN;
CREATE ROLE webui LOGIN PASSWORD 'somepw' IN ROLE assume_any_user;

CREATE ROLE jane LOGIN PASSWORD 'somepw';
GRANT jane TO assume_any_user;
GRANT ALL ON TABLE test_table TO jane;

CREATE ROLE jim LOGIN PASSWORD 'somepw';
GRANT jim TO assume_any_user;

Now connect as webui. Note that you can't do anything to test_table but you can SET ROLE to jane and then you can access test_table:

$ psql -h 127.0.0.1 -U webui regress
Password for user webui:

regress=> SELECT session_user, current_user;
 session_user | current_user
--------------+--------------
 webui        | webui
(1 row)

regress=> SELECT * FROM test_table;
ERROR:  permission denied for relation test_table

regress=> SET ROLE jane;
SET

regress=> SELECT session_user, current_user;
 session_user | current_user
--------------+--------------
 webui        | jane
(1 row)

regress=> SELECT * FROM test_table;
  x
------
 bork
(1 row)

Note that webui can SET ROLE to jim, even when already SET ROLEd to jane and even though jane hasn't been GRANTed the right to assume the role jim. SET ROLE sets your effective user ID, but it doesn't remove your ability to SET ROLE to other roles, that's a property of the role you connected as, not your current effective role. Consequently you must carefully control access to the SET ROLE and RESET ROLE commands. There is, AFAIK, no way to permanently SET ROLE for a connection, truly becoming the target user, though it'd certainly be nice to have.

Compare:

$ psql -h 127.0.0.1 -U webui regress
Password for user webui:

regress=> SET ROLE jane;
SET

regress=> SET ROLE jim;
SET
regress=> SELECT session_user, current_user;
 session_user | current_user
--------------+--------------
 webui        | jim
(1 row)

to:

$ psql -h 127.0.0.1 -U jane regress
Password for user jane:

regress=> SET ROLE webui;
ERROR:  permission denied to set role "webui"
regress=> SET ROLE jim;
ERROR:  permission denied to set role "jim"

This means that SET ROLE isn't exactly the same as logging in as a given role, something you must keep in mind.

webui can't SET ROLE to dbowner since it hasn't been GRANTed that right:

regress=> SET ROLE dbowner;
ERROR:  permission denied to set role "dbowner"

so by its self it's pretty powerless, it can only assume the rights of other users and only when those users have web access enabled.


How to keep records of past week visits in a web app?

The weekly_stats table can have one record for every week, i.e. something like:

CREATE TABLE weekly_stats (
  week_start date PRIMARY_KEY, -- e.g. '2018-03-05'
  article_id int unsigned,
  view_count int unsigned DEFAULT 0,
  CONSTRAINT week_stats_fk1 FOREIGN KEY (article_id)
    REFERENCES `article` (`article_id`) ON DELETE CASCADE ON UPDATE CASCADE
);

Your application will then have update the weekly_stats.view_count every time an article is viewed. If the record for that particular week doesn't exist, the application will have to create it.

If you want to delete old data, which may not be necessary: Instead of a cron job, you could use a MariaDB event. Set it up to only delete records older than some limit, e.g. 365 days.


Converting outer apply with Correlated subquery in SQL Server to SQLite

SQLite does not support lateral or correlated joins.  You can work around that using a join condition.  For example:

create table t1 (id int, name text);
create table t2 (id int, t1id int references t1(id), name text);
insert into t1 values (1, 'a'), (2, 'b'), (3, 'c');
insert into t2 values (1, 1, 'a1'), (2, 1, 'a2'), (3, 2, 'b1');

Now we'd like to look up at most one row from t2, sorted by name.  Here's one approach that will work in SQLite:

select  *
from    t1
left join
        t2
on      t2.id =
        (
        select  id
        from    t2
        where   t1.id = t2.t1id
        order by
                name desc
        limit   1
        )

Example at SQL Fiddle.


You probably meant AND Operator.OperatorID = RouteOperators.OperatorID

I'd suggest using the JOIN...ON (...) syntax, it makes the query more readable.

SELECT o.Phone,o.Email
FROM Route r
INNER JOIN RouteOperators ro
    ON r.RouteNumber = ro.RouteNumber
INNER JOIN Operator o
    ON ro.OperatorID = o.OperatorID
WHERE Route.Start = 7628
OR Route.Destination = 7628

You might also want to consider introducing foreign key constraints.


You don't really migrate a replication topology.  You simply recreate it among the new servers.  Script out your replication configuration and re-create it among the new servers.  So backup, and restore, followed by replication setup with ordinary initialization of the subscriber from a new snapshot.

It's possible, but more complicated, to skip the initialization of the subscriber, if you can ensure that the subscription tables were synchronized at the point-in-time of the restore. See Initializing a subscription with an alternative method.

But if you have the downtime, just clean up and recreate the replication topology from scratch on the new servers.  You should script the new replication setup and test it several times before the cutover.


Why MongoDB collections are dropped automatically?

I have gone through your error log and find out the MongoDB Bug affected version error , which are able to see in your error log such as:

  "mongodb failed to create WiredTiger bulk cursor: Device or resource
  busy"

As per MongoDB jira documentation here as it is Server Bug having affected version was 2.8.0-rc0.

The Bug fix version of MongoDB is 2.8.0-rc1.

And the second Bug error, which is

  2018-08-28T12:44:46.138+0530 I CONTROL [signalProcessingThread] got
  signal 1 (Hangup), will terminate after current cmd ends

As jira documentation here It's affected Bug in MongoDB version 3.0.12.

As per @RamonFernandez from jira documentation, seems like mongod is receiving the SIGHUP signal and terminating, which is expected behavior. You need to check who or what is sending this signal.


Is it "legal" to CREATE and DROP #SomeTable more than once?

No, the parser won't let you create the same #temp table twice in the same batch (and this has nothing to do with SSMS). It doesn't even matter if only one copy of the #temp table could ever be created; for example, in the following conditional logic, which to humans could obviously only ever execute one branch, SQL Server can't see that:

IF 1 = 1
BEGIN
  CREATE TABLE #x(i INT);
  DROP TABLE #x;
END
ELSE
BEGIN
  CREATE TABLE #x(j INT);
  DROP TABLE #x;
END

  Msg 2714, Level 16, State 1, Line 8
  There is already an object named '#x' in the database.

And to prove it's not SSMS complaining at compile time (a common misconception):

DECLARE @sql NVARCHAR(MAX) = N'IF 1 = 1
BEGIN
  CREATE TABLE #x(i INT);
  DROP TABLE #x;
END
ELSE
BEGIN
  CREATE TABLE #x(j INT);
  DROP TABLE #x;
END';

EXEC sp_executesql @sql;

Yields the exact same error, even though SSMS does not try to parse or validate dynamic SQL before sending it to the server via sp_executesql.

The fix, of course, is to re-use the same #temp table instead of dropping, use a different #temp table each time, or don't use #temp tables in the first place.

This is not something you should ever expect SQL Server to handle better. In other words, get used to whatever workaround you decide on.

See also this related answer on Stack Overflow that gives an alternate explanation:

Can I recreate a temp table after dropping it?


SQL Server - Query Over Linked Server to IBM DB2 throws an error

Well, I did a lot of searching, found some information on the SQL0204 error, which led me to this support page:

https://www-01.ibm.com/support/docview.wss?uid=nas8N1016377

which has instructions on what commands to run on the server if you see that SQL0204 error.

I forwarded that on to the partners IT guy, who tried it (and a few other commands according to him) and it started working.

I will try to see if I can get him to send me what other commands he ran so I can make this answer more complete.

Edit:
In case the linked page ever disappears, here is a screen shot of the information:


Do you actually need to persist the prev and next columns in the data? Why not use Window functions on retrieval of data to get what you need from your base table? I've made a bit of a guess on what your schema looks like in it's most basic form but this should help you. If not post CREATE statements for all tables involved and I'll edit the answer.

SELECT  MonthDate
        , CarId
        , KM
        , LAG(KM) OVER (PARTITION BY CarId ORDER BY MonthDate) Prev
        , LEAD(KM) OVER (PARTITION BY CarId ORDER BY MonthDate) [next]

FROM    PrevNextFullList

If you do need to persist the columns then use this statement to create a VIEW

UPDATE You would need to upgrade to SQL 2012 to do this though.


Do varchar/text/longtext columns in MySQL occupy full size in filesystem even for partially filled cells?

'abcd' in CHAR(72) CHARACTER SET ascii occupies 72 bytes on disk.
'abcd' in CHAR(72) CHARACTER SET utf8 occupies 3*72 bytes on disk.
'abcd' in CHAR(72) CHARACTER SET utf8mb4 occupies 4*72 bytes on disk.
'abcd' in VARCHAR(72) occupies 1+4 bytes on disk.
'abcd' in TINYTEXT occupies 1+4 bytes on disk.
'abcd' in TEXT occupies 2+4 bytes on disk.
'abcd' in LONGTEXT occupies 4+4 bytes on disk.

Update:  In some versions, InnoDB will store only 4 bytes for the CHAR cases.

Notes:
The 1,2,4 is for length; VARCHAR could be 2 in some situations.
The CHARACTER SET is important in all the cases, but does not impact the space occupied by 'abcd' except for CHAR.

@akuzminsky -- You are wrong about the *3.  CHAR(N) and VARCHAR(N) can hold up to N characters in the declared CHARACTER SET.  That will become up to 3*N bytes for CHAR or 1+3*N for VARCHAR.

TEXT is limited to 65535 bytes (plus 2-byte length).

There is overhead on top of the lengths described above.  In InnoDB, there are record flags, transaction ids, blocking factors, pointers to overflow areas, etc, etc.  So, if you use this kind of arithmetic, it will underestimate the disk space used.

MyISAM is more frugal; it has at least 1 byte of overhead per record; in some cases, only 1.


Grouping data in many to many relationships to improve performance

It may be worth looking at things from the other direction.

I suspect that you have a large number of PCs that are set up with exactly (or almost exactly) the same set of software. You may want to consider establishing a set of PC "profiles": a set of software that's going to be standard for hundreds (or even thousands) of PCs.

This has most of the same benefits and drawbacks as your current plan, but might impact more rows more easily. And, it might make certain kinds of changes easier as well: in many cases, a new/updated piece of software will ultimately be deployed to all the machines; you can create a new profile, and move PCs to it as the software change is applied.

Functionally, this would look much the same as what you're talking about; I simply think the perspective helps nail tings down a bit.

If you are going to create application groups, I would not try to generate groups based on what's on PCs today directly. It's useful information, but should be looked at carefully, to identify meaningful groupings, and not just "accidental" ones. It's better to create four different groups where each one actually contains software that has a logical reason to all be on the same machine together, than to create just two groups, each of which contains some things that may be found together, but not on purpose.

Finally, note that this sort of thing always involves a trade-off. You may get better performance because you're dealing with smaller tables, but your queries will involve more tables and/or more joins, and be more complicated. The end result might actually be worse performance.


Table with only one column because of many-to-many relationship?

The alias table will save space because you do not repeat duplicated aliases in the person_alias table.

If in most of the cases aliases are unique, then this won't matter much, but if you have a lot of duplicated aliases (say every second person uses 'Zaphod Beeblebrox' as a alias) then this could save a lot of space.

However, if you allow a single person to rename their alias without affecting all other (duplicated) aliases, then removing the alias table completely could makes sense.

You could de-normalize further and store all aliases for a person in an array column in the person table removing the person_alias table as well. But in that case it will be quite complicated to define a unique index that ensures that every alias is unique for that person. If you (almost) always retrieve all aliases for a person together with that person that might be slightly more efficient.

But if you want to ensure uniqueness of the aliases per person in the database, stick with the person_alias table.


attach csv file on email sent from postgres

Since you've already picked perl the easiest way might be to use MIME::Lite


rm -f ibdata1 ib_logfile*

Well there's your problem. You deleted your INNODB table space.

You'll need to restore from backup.

Just curious: where did you get the idea that you should remove those files?


This isn't clutter. You need to figure out why the audit is failing, and resolve it.


Creating a index on a date column that's already populated

There is no downside in creating an index at a later moment. The index will perform the same way like it would when it was created when the table was created.

You mentioned that the table has already 10 million rows. Therefore I would suggest that you create it during a 'calm' period. This because Oracle needs to lock the table. This cannot be done if there is a transaction that does an insert, update, or delete in the table.


How are the following situations handled in MySQL

UPDATEs and DELETEs will at very least apply a row level lock that prevents other transactions from affecting that row until the locking transaction is complete. If the LOCK takes 'too' long to resolve the transaction it can result in a TIMEOUT or a DEADLOCK scenario. Transactions are applied in a synchronous manner based upon when the command reaches the database.


Calculation is something which you need to analyse an figure out with some calculation on basis of that. Kindly go through the below discussion which will help you out in deciding what values you can assign:

What is a deterministic method for evaluating a sensible buffer pool size?

Also, would recommend to go through below article for indepth explanation:

http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/07/07/understanding-the-vas-reservation-aka-memtoleave-in-sql-server.aspx


Is `GRANT CREATE SESSION to "MYROLE"` working in Oracle 12 database?

You might be connected to wrong container database, probably CDB.

Here is a demo which is working fine for me.

I am connected to orclpdb, a pluggable database as sysdba.

[oracle@oracdb ~]$ sqlplus sys@orclpdb as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Sep 20 15:26:59 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create role myrole;

Role created.

SQL> grant create session to myrole;

Grant succeeded.

SQL> create user testuser identified by testuser;

User created.

SQL> grant myrole to testuser;

Grant succeeded.

SQL> conn testuser/testuser@orclpdb
Connected.
SQL>


create database table with a owner in my SQL

There is no concept such as OWNER in mySQL for databases or for tables.

You will need to enforce security in the traditional way with granting very specific user privileges.

for e.g. for inserts: GRANT INSERT ON database.* TO 'username'@'hostname' ;


rman is the way to go for backup and recovery for protection against physical corruption. For documentation check Oracle® Database 2 Day DBA
11g Release 2 (11.2)
In the current release of SQLDeveloper backup and recovery is integrated in the GUI. For download of SQLDeveloper see Oracle SQL Developer It is a free product, with active development.

In SQLdeveloper in the View menu select DBA to get access to the dba tools like RMAN and DATAPUMP. Very nice tool. Don't forget that the backup files are written by the Oracle database server processes. This means that the backups are written on the database server, not on your client. Also, currently it just generates a backup script. You should run the generated script using RMAN.

An other way to run and configure the backup is by using dbconsole or grid control.

Using datapump is an other way to protect against data loss. Using this enables you to read back the data as it was during the export. Might be usefull for test sets that have to be refreshed with a specific version of the data. Not really usefull if you want to recover production transactions to the last moment before disaster stroke.


How do I run a stored procedure only on newest data loaded?

Community wiki answer - please edit to improve:

Don't load directly to dbo.Data_Pass.

Instead, load to a staging table, and run the SP against that table. Then insert the final, validated data into dbo.Data_Pass.


Did Microsoft change query optimizer regarding number of files and parallelism

That was an urban legend that Microsoft's Bob Dorr debunked back in 2007:

  This information has been taken out of context and propagated
  incorrectly as SQL Server has a thread per database file so adding
  more files to a database can increase I/O performance.   Adding more
  files and properly aligning them with storage can increase the I/O
  performance for a variety of reasons but achieving new I/O threads per
  file is NOT one of them.

Read the post for much more detail.


Resource Governor does not manage I/O, it is just system resources for CPU and Memory.  Therefore, I don't believe there would be any way to get I/O per workload group.

Quote from BOL on Resource Governor:

  Resource Governor enables you to specify limits on the amount of CPU and memory that incoming application requests can use.


Assuming that sql_id has a unique or primary key constraint, you can do this with FULL JOIN (or LEFT JOIN if there are no new "inserts" in table B, or just JOIN if there are no inserts and no deletions):

SELECT
    COALESCE(a.sql_id,b.sql_id) AS sql_id,
    CASE WHEN a.operation = b.operation THEN 'matched'
         WHEN a.operation <> b.operation THEN 'value changed'
         WHEN b.operation IS NULL THEN 'a deleted'
         WHEN a.operation IS NULL THEN 'b inserted'
    END AS status,
    a.operation AS a_operation,
    b.operation AS b_operation
FROM
  a FULL JOIN b
      ON a.sql_id = b.sql_id ;


You just need to issue:

database dbname

Similar to an alter session set current_schema in oracle.


In sys.dm_os_memory_cache_entries view, what does original cost mean?

From the documentation for sys.dm_os_memory_cache_entries, this represents the combined CPU + I/O cost that the optimizer uses both for the value of the plan (and those metrics) and also to determine how quickly to evict those plans from the cache. Though, for your use case, I suspect current_cost may be more relevant. Be curious to see what comes next - how exactly are you going to use this information?


PostgreSQL merging identical tables from separate databases into single database

Your method is easily improved, however you're mostly there.

When you run this

pg_dump [OLD_DATABASE] --schema-only > dbschema.sql

Instead do

pg_dump [OLD_DATABASE] --schema-only --section pre-data > pre.sql

Then load your dumped data, produced by this (from all the servers)

pg_dump [OLD_DATABASES] --disable-triggers --data-only --inserts > old_db_data.sql

But, then you need to add the add the constraints.. So go back to your original database and run

pg_dump [OLD_DATABASE] --schema-only --section post-data > post.sql

And, then load post.sql. This will add the constraints.


How do you determine the direction of a relationship?

A Foreign Key always refers to a Unique Index or Primary Key.  So the "one" side is always the one where the related columns are a key.