Optimize a query on two big tables I have a very important query in my system that is taking too long to execute due to huge amount of data on tables. I'm a junior DBA and i need the best optimization I can get for this. Tables have approximate 80 million rows each.

Tables are:

tb_pd:

   Column            |  Type   | Modifiers | Storage | Stats target | Description
---------------------+---------+-----------+---------+--------------+-------------
 pd_id               | integer | not null  | plain   |              |
 st_id               | integer |           | plain   |              |
 status_id           | integer |           | plain   |              |
 next_execution_date | bigint  |           | plain   |              |
 priority            | integer |           | plain   |              |
 is_active           | integer |           | plain   |              |
Indexes:
    "pk_pd" PRIMARY KEY, btree (pd_id)
    "idx_pd_order" btree (priority, next_execution_date)
    "idx_pd_where" btree (status_id, next_execution_date, is_active)
Foreign-key constraints:
    "fk_st" FOREIGN KEY (st_id) REFERENCES tb_st(st_id)

tb_st:

 Column |          Type          | Modifiers | Storage  | Stats target | Description
--------+------------------------+-----------+----------+--------------+-------------
 st_id  | integer                | not null  | plain    |              |
 st     | character varying(500) |           | extended |              |
Indexes:
    "pk_st" PRIMARY KEY, btree (st_id)
Referenced by:
    TABLE "tb_pd" CONSTRAINT "fk_st" FOREIGN KEY (st_id) REFERENCES tb_st(st_id)

My query is:

select s.st
from tb_pd p inner join
     tb_st s on p.st_id = s.st_id
where p.status_id = 1 and
      p.next_execution_date < 1401402110830 and
      p.is_active = 1
order by priority, next_execution_date
limit 20000;

With the indexes I have, the best I got was:

Limit  (cost=1.14..263388.65 rows=20000 width=45)
   ->  Nested Loop  (cost=1.14..456016201.43 rows=34627017 width=45)
         ->  Index Scan using idx_pd_order on tb_pd p  (cost=0.57..161388942.77 rows=34627017 width=16)
               Index Cond: (next_execution_date < 1401402110830::bigint)
               Filter: ((status_id = 1) AND (is_active = 1))
         ->  Index Scan using pk_st on tb_st s  (cost=0.57..8.50 rows=1 width=37)
               Index Cond: (st_id = p.st_id)

I cannot understand the explain very well but it's not using the idx_pd_where to filter the where clause. The idx_pd_where have all the columns used in where clause.

More info about data:
status_id is 95% = 1
is_active is 90% = 1
next_execution_date is in millis and varies a lot. The value compared is the moment of the execution (current time in millis)

Should I create separate indexes for each filtered column or use any different kind of indexes? Maybe some configuration on DBMS?

This is a tricky one. Your main condition is on next_execution_date, but the output is sorted by priority first. The conditions on status_id and is_active only play a minor part.

Better index

Your index idx_pd_order is not a big help, since filtering on non-leading columns of a multi-column index is not very efficient. Postgres is using it - still a lot better than a sequential scan. Details here:
Is a composite index also good for queries on the first field?

idx_pd_where might be a better choice, but not a good one, either. The leading column status_id is not selective at all and just bloats the index. Same goes for the trailing column is_active. And priority is not in the index and has to be fetched from the table, making an index-only scan impossible.

I suggest this partial, multi-column index to start with. (But keep reading!)

CREATE INDEX idx_pd_covering ON tb_pd (next_execution_date, priority, st_id)
WHERE  status_id = 1 AND is_active = 1

Since we are only ever interested in rows with status_id = 1and is_active = 1 exclude other rows from the index right away. Size does matter.
The remaining (crucial) condition is on next_execution_date, which must come first in the index.
priority and st_id are only appended for a possible index-only scan (Postgres 9.2+). If that doesn't take, remove the columns from the index to make is smaller.

Special difficulty

We can now use idx_pd_covering to find qualifying rows quickly, unfortunately we have to look at all qualifying rows to collect the ones with highest priority. As the query plan reveals, Postgres estimates to process 34627017 rows. Sorting 35M rows is going to cost big. That's the tricky part I mentioned at the start. To demonstrate what I am talking about, run EXPLAIN on your query with and without priority in ORDER BY:

SELECT s.st
FROM   tb_pd p
JOIN   tb_st s USING (st_id)
WHERE  p.status_id = 1
AND    p.is_active = 1
AND    p.next_execution_date < 1401402110830
ORDER  BY priority, next_execution_date
LIMIT  20000;

That's your query, formatted only slightly simplified. You should see a huge difference.

Solution

The solution depends on the number of distinct values for priority. For lack of information and for demo purposes I am going to assume only three. Priority 1, 2 and 3.

With a trivial number of distinct priority values, there is a simple solution. Create three partial indexes. All of them together are still smaller than your current indexes idx_pd_order or idx_pd_where (which you might not be needing any more).

CREATE INDEX idx_pd_covering_p1 ON tb_pd (next_execution_date, st_id)
WHERE  priority = 1 AND status_id = 1 AND is_active = 1;

CREATE INDEX idx_pd_covering_p2 ON tb_pd (next_execution_date, st_id)
WHERE  priority = 2 AND status_id = 1 AND is_active = 1;

CREATE INDEX idx_pd_covering_p3 ON tb_pd (next_execution_date, st_id)
WHERE  priority = 3 AND status_id = 1 AND is_active = 1;

Use this query:

SELECT s.st
FROM  (
   (
   SELECT st_id
   FROM   tb_pd
   WHERE  status_id = 1
   AND    is_active = 1
   AND    priority  = 1
   AND    next_execution_date < 1401402110830
   ORDER  BY next_execution_date
   )
   UNION ALL
   (
   SELECT st_id
   FROM   tb_pd
   WHERE  status_id = 1
   AND    is_active = 1
   AND    priority  = 2
   AND    next_execution_date < 1401402110830
   ORDER  BY next_execution_date
   )
   UNION ALL
   (
   ...
   AND    priority  = 3
   ...
   )
   LIMIT  20000
   ) p
JOIN   tb_st s USING (st_id);

This should be dynamite.

Strictly speaking, the final order is not guaranteed without an additional ORDER BY clause in the outer query. In the current implementation, the order from the inner query is preserved as long as the outer query is as simple as that. To be sure, you could join right away (which may be a bit slower):

)
SELECT s.st
FROM   tb_pd p
JOIN   tb_st s USING (st_id)
WHERE  p.status_id = 1
AND    p.is_active = 1
AND    p.priority  = 1
AND    p.next_execution_date < 1401402110830
ORDER  BY p.next_execution_date
)
UNION ALL
(
...
)
LIMIT  20000;

.. or carry along priority and next_execution_date to order once more in the outer query (to be absolutely sure), which is probably slower, yet.

All parentheses are needed! Related answer.
This query just reads tuples from the top of above partial indexes, no sort-step needed at all. All rows are pre-sorted, efficient to boot.
UNION ALL queries without a final ORDER BY can stop as soon the number of requested rows in the top-level LIMIT have been fetched. So if there are enough rows in the top priorities, subsequent legs of the UNION ALL query are never executed. This way, only the smaller partial indexes have to be touched.
JOIN to tb_st later, should be more efficient.
Again, the column st_id is only appended to the index in the hope for an index-only scan. If that works for you, the whole query does not even touch the table tb_pd at all.

General solution for any number of distinct priority values

We have solved this before. There is a complete recipe to automate the creation of partial indexes and a function .. the works:
Can spatial index help a "range - order by - limit" query

Optimize table

Since you are trying to optimize performance and your table is big, I suggest a slightly altered layout for your table tb_pd:

   Column            |  Type
---------------------+--------
 pd_id               | integer
 st_id               | integer
 next_execution_date | bigint
 priority            | integer  -- or smallint? -- or "char"?
 status_id           | smallint -- or "char"
 is_active           | boolean

This occupies 52 bytes per row on disk, while your current design needs 60 bytes. Indexes profit as well. Details:
Configuring PostgreSQL for read performance

Of course, all the basic advice for performance-optimization applies as well.

About "char":

  The type "char" (note the quotes) is different from char(1) in that it
  only uses one byte of storage. It is internally used in the system
  catalogs as a simplistic enumeration type.


MySQL General log not working on Mac OS X I want to log all queries to the database running on my MacBook Pro (Mac OS X 10.8.1). I created a file /etc/my.cnf with the following content:

[mysqld]
general_log=1
general_log_file=/Users/wdb/mysql.log

I restarted MySQL and tried some queries on the mysql command line, but nothing gets logged. The file is not getting created. I also created the file myself afterwards to test with touch, but the file remains empty.

When I check, MySQL did read my settings:

mysql> show variables like '%general_log%';
+------------------+----------------------+
| Variable_name    | Value                |
+------------------+----------------------+
| general_log      | ON                   |
| general_log_file | /Users/wdb/mysql.log |
+------------------+----------------------+
2 rows in set (0.01 sec)

Is there anything else I need to do? I suppose any query I type on the command line should get logged, right?

I am using MySQL 5.5.24

The problem is indeed as @Phil hinted at. To fix it, set the permissions for the _mysql user:

> cd ~
> touch mysql.log
> sudo chown _mysql:wheel mysql.log

Restart MySQL after that and the log file will be used.


Failing query, don't understand why I have this query, which I don't know why it doesn't work, even though if I change some parameters, it works just fine. Here is the query:

SELECT round(ST_Distance_Sphere(
                       (SELECT coordinates FROM houses WHERE id = 7)),
                       (SELECT wkb_geometry FROM coastline ORDER BY wkb_geometry <->
                       (SELECT coordinates FROM houses WHERE id = 7)) LIMIT 1)
                       )) AS DISTANCE;

  ERROR:  syntax error at or near "LIMIT"
  LINE 4: (SELECT coordinates FROM houses WHERE id = 7)) LIMIT 1)

This works:

SELECT round(ST_Distance_Sphere(
                 ((ST_GeomFromText('POINT(10.806527 54.101916)',4326))),
                 (SELECT wkb_geometry FROM coastline ORDER BY wkb_geometry <->
                 ((ST_GeomFromText('POINT(10.806527 54.101916)',4326))) LIMIT 1)
                 )) AS DISTANCE;

I tried removing LIMIT, but it leads me to problems with the parentheses, and there is where I get lost.

ST_Distance_Sphere has first argument (SELECT coordinates FROM houses WHERE id = 7) then you close ), so (SELECT wkb_geometry... is second argument for round, which I suppose is not what you want.

So I believe you just got lost in parentheses - try rewriting code "in ladder" so every parenthesis has closing one in same indentation...

based on your working query - are you trying to do this?

SELECT round(
  ST_Distance_Sphere(
    ((SELECT coordinates FROM houses WHERE id = 7 LIMIT 1)
  ,
  (SELECT wkb_geometry FROM coastline ORDER BY wkb_geometry <-> (SELECT coordinates FROM houses WHERE id = 7) LIMIT 1))
)) AS DISTANCE;


How to check what Database Engine is installed on the DataBase server that I have acces to run queries on? I want to check what type of sql is running on a Datasase server that I can access. I only have access to a web interface and a list of tables.

Through the interface I can run queries on the tables that are present on a list.

How can I get more information about the server and the version that the server is running. I have no idea about the IP or the PORT that the server is running.

I want to know if the server is MySQL, Mircosoft SQL Server, Oracle SQL, Postgre SQL or other sql server.

The website that I am talking about is this one:
w3schools.com SQL editor.

EDIT 2: although for some the command select sqlite_version() works for me it does not work. This is the screenshot of the response.

EDIT 3 :
On Chromium Browser the command is working properly. However on Firefox Browser the command did not work.

I also mention that I am on running Linux.

What do you think could be the reason why on Firefox and on Chrome I get different results ?

I assume that your web interface lets you issue SQL commands. If so, you can use:

SELECT version();

PostgreSQL

If you are on a PostgreSQL database, you get a response similar to

PostgreSQL 9.6.1 on x86_64-apple-darwin14.5.0, compiled by Apple LLVM version 7.0.0 (clang-700.1.76), 64-bit

MySQL

If you are on a MySQL database, the answer looks like

5.7.12-log

Oracle

If you are on an Oracle database, you'll get an error message:

ORA-00923: FROM keyword not found where expected

(The ORA-xxxx tells you you're on Oracle). To find out which specific version, try:

SELECT banner as "oracle version" from v$version

You'll get a response like:

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0  Production
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

Microsoft SQL Server

If you're on MS SQL Server, the response will also be an error, and look like:

'version' is not a recognized built-in function name.

In that case, you can try:

SELECT @@version ;

And you'll get, as a response, something looking like:

Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)
    Oct 28 2016 18:17:30
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)

SQLite

If you're on a SQLite database, you'll get an error message when you try SELECT version():

 could not prepare statement (1 no such function: version)

In that case, you can try:

 SELECT sqlite_version()

And the response will look like:

3.14.0


How to find out how much i/o resources a task, session, request or transaction is currently using? How to find out how much i/o resources a task, session, request or transaction is currently using?

by task I mean the DMV called DMV sys.dm_os_tasks.
by session I mean the DMV sys.dm_exec_sessions

Sometimes a session has a request, by request I mean the DMV sys.dm_exec_requests
or a session might have a transaction instead - sys.dm_tran_database_transactions

How can I link these things with I/O usage?

I had a look at the DMV called DMV sys.dm_os_tasks .

The DMV sys.dm_os_tasks has some interesting columns to find out more about processes currently running in sql server.

sys.dm_os_tasks - not showing pending_io_byte_count

However, when I try to identify how much I/O each of my tasks is dealing with the pending I/O in bytes is always zero.

This is an example of the results of my query while I was trying to monitor some CXPACKETS WAITS.

Pending_IO_count seems to be fine, but pending_IO_by_counts are always zero.

what could be an alternative?
I want to measure the I/O usage of each parallel process.

in fact it could be just the I/O usage of each session or request.

even sessions without request sometimes are good to see, because they might be blocking other processes.

If you are looking for the actual physical I/O for a query, you should look in the XML showplan for the 'ActualPhysicalReads' element:

schemas.microsoft.com/sqlserver/2004/07/showplan

However, those physical reads are likely only going to occur the first time the plan is cached. Subsequent runs are more likely to pull the pages from memory, not from disk.

HTH


ORDER from 2 tables to achieve a specific order returned by an API There's an external API that returns image similarities, based on an "image ID" and a "image version" (a same image can have multiple versions).
The similarity ORDER is given by the API.

The API returns something like this:

+---------+---------+
|   id    | version |
+---------+---------+
|  37967  |    2    |
|    236  |    1    |
|  37967  |    1    |
|   1413  |    2    |
+---------+---------+

Then I need to retrieve entries in a MySQL database (containing 2 tables), and keep the same ORDER as the one returned by the API.

That's where I'm having a problem. Here are the 2 tables:

"img" MySQL table:
+---------+-----------------------+
|   id    | lots of other columns |
+---------+-----------------------+
|    236  |         data          |
|   1413  |         data          |
|  37967  |         data          |
+---------+-----------------------+

"vers" MySQL table:
+---------+---------+
|   id    | version |
+---------+---------+
|    236  |    1    |
|   1413  |    1    |
|   1413  |    2    |
|  37967  |    1    |
|  37967  |    2    |
|  37967  |    3    |
+---------+---------+

the closest result I can get is by using ORDER BY FIELD, but it's still not the same ORDER as the one returned by the API.

My query:

SELECT i.id, v.version
FROM img i
LEFT JOIN vers v ON i.id=v.id
WHERE ((i.id=37967 AND v.version=2)
OR (i.id=236 AND v.version=1)
OR (i.id=37967 AND v.version=1)
OR (i.id=1413 AND v.version=2))
ORDER BY FIELD(i.id, 37967,236,37967,1413), FIELD(v.version,2,1,1,2)

Results:
+---------+---------+
|   id    | version |
+---------+---------+
|  37967  |    2    |
|  37967  |    1    |
|    236  |    1    |
|   1413  |    2    |
+---------+---------+

As you can see, the order is not exactly the one returned by the API :(

Any help would be appreciated, thank you all in advance.

(copied from the chat)

  MySQL version is 5.6.40

  Max records is 100.

The method to parse JSON string to enumerated table (up to 100 records) applicable to version 5.6.40.

set @JSON='{"imgResults":[{"id":"37967","version":"2"},{"id":"236","version":"1"},{"id":"37967","version":"1"},{"id":"1413","version":"2"}]}'

This is example source JSON string.

It may be transferred to server as query parameter to the above query which assigns the value to a variable or inserted immediately into the query text as string literal.

Also It may be transferred to server as query parameter to the below query which parses it, i.e. intermediate user-defined variable is optional.

Now we parse JSON to a table (emulation of JSON_TABLE function available since 8.0 version):

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(json, '"id":"', num+2), '"id":"', -1), '"', 1) id,
       SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(json, '"version":"', num+2), '"version":"', -1), '"', 1) version,
       MIN(num) idx
FROM (SELECT @JSON json) source,            -- source JSON string
( SELECT numbers1.num*10+numbers2.num num   -- create numbers table, 0..99
  FROM ( SELECT 0 num UNION
         SELECT 1 UNION
         SELECT 2 UNION
         SELECT 3 UNION
         SELECT 4 UNION
         SELECT 5 UNION
         SELECT 6 UNION
         SELECT 7 UNION
         SELECT 8 UNION
         SELECT 9 ) numbers1,
       ( SELECT 0 num UNION
         SELECT 1 UNION
         SELECT 2 UNION
         SELECT 3 UNION
         SELECT 4 UNION
         SELECT 5 UNION
         SELECT 6 UNION
         SELECT 7 UNION
         SELECT 8 UNION
         SELECT 9 ) numbers2 ) numbers
 GROUP BY id,version
 -- ORDER BY idx;

Output:

id    | version | idx
:---- | :------ | --:
37967 | 2       |   0
236   | 1       |   1
37967 | 1       |   2
1413  | 2       |   3

You may join this parser into your query as a source data subquery and use its idx field for ordering the final result in the same order in which the records are posessed in source JSON.


mysql launch fails after upgrade to 5.7.11 After upgrading a Mac to OS X 10.11.3, mysql was gone, so I downloaded and installed 5.7.11. It installed as usual but fails to launch with;

(com.oracle.oss.mysql.mysqld[1560]): Service exited with abnormal code: 1

in the system log. It respawns and fails repeatedly!

Any suggestions appreciated!

mysql.local.err  Thank you so much @Rick James. The log showed that some of the my.cnf values were failing, about 5 IIRC!  WTF? I expect there were old values from the previous install which are no longer valid.

After I commented them out, the server started up fine.


Downgrading SQL Server database and moving it to another computer I have created database on my computer using SQL Server 2019, and now i want to move it to another computer that has SQL Server 2014 installed on it.

Do I have to download SQL Server 2014 and downgrade the database or there is another solution for it? What should I do?

There's no such thing as downgradning the physical database. You would have to (use a tool that) script out your objects (CREATE commands) and the data (as INSERTs or some BCP files). And hope that you don't use anything on your 2019 that isn't available on 2014.

Such tools can be BACPAC, SSDT, "Generate Scripts" in SSMS.


Point in time recovery from the same database - what does it mean? I have two questions. I have a database in full recovery mode. Lets call transaction log an LDF file for short.

SQL Server allows me to restore to point in time in the past when restoring from database to itself. Does this even make sense? When I tried it did not yield expected results. Why is this even allowed if it doesn’t work? Can it have any practical purposes?
When playing with the LDF files I discovered a curiosity: Consider having a database in simple mode. Then I switch it to full recovery mode. Then I insert some 10000 rows into the database, check the LDF and find out that the LDF file size did not increase. When I take a full backup and then insert 10000 rows again into the database only then the LDF file increases. Is this some sort of optimization? NOTE: I had LDF file shrinked before both operations.

Thanks for all explanations.

The process of restoring a database only ever goes forward in time, never backward 1. So restoring a database to a point in time involves starting from a full backup, and then applying transaction log backups up to/containing the point in time to which you wish to restore.

The idea is that the ability exists to revert the database to a specific state that existed previously. This could be required if the entire database is lost (disaster) and it has to be reconstituted from nothing but the backups; or, "something bad" could have happened, either maliciously, or due to corruption, and it has to be restored in-place.

If you're having issues with getting this to work, we'll need more information about how you're going about doing it.
As mentioned in the comments, a database in FULL or BULK_LOGGED remains in SIMPLE mode internally until a full backup is taken, which gives a "base" from which the database can be restored. I've seen this called "pseudo-simple" mode.

The behaviour you saw with regards to the log file size was likely due to the above, although it depends on how the rows were INSERTed. In SIMPLE (and pseudo-simple) recovery, if the log file starts getting full, a CHECKPOINT will be initiated, which will internally free space in the log file as part of the process. So if the INSERTs were done in separate transactions, this would not grow the log. Doing all the INSERTs in a single transaction would grow the log, as the log records required to roll back the whole transaction cannot be cleared until the transaction commits or rolls back.

1 Not counting things like restoring in STANDBY mode, crash recovery, etc.


Convert to 2 decimals and keep leading zero The following piece of code is resulting in a value with 4 decimals.
COALESCE (SUM( CASE WHEN PLL_TRANSACTIONTYPE = 'TAX' and PLL_REVENUETYPE = 'OTHER' then CAST(ROUND(PLL_AMOUNT,2) as decimal (9,2))
WHEN PLL_REVENUETYPE    = 'TAX' THEN CAST(ROUND(PLL_TAXAMOUNT,2) as decimal (9,2))
WHEN PLL_ISINCLUSIVETAX = '-'   THEN CAST(ROUND(PLL_NETAMOUNT,2) as decimal (9,2))
ELSE CAST(ROUND(PLL_NETAMOUNT,2)as decimal (9,2)) END ), 0) * -1.00 AS AMOUNT

I found many suggestions on the web, but I haven't been able to round the amount with 2 decimals.
Also, when I do a SQMCMD to export the query to a CSV, it will drop the leading zero if the amount is 0.00
Any help would be appreciated.

You don't need both cast and round.
For example:
declare @val decimal(9,2) = '849.909839'
select @val [val]
, cast('849.909839' as decimal(9,2))*-1.00 [cast_first]
, cast(convert(float,'849.909839') *-1.00 as decimal(9,2)) [cast_last]

If you run this, you can see the problem. It's in the else clause. you should do a cast after you multiply by -1.00.


Auto Update Statistics threshold SQL Server 2016 since SQL Server 2016 the default auto statistics update threshold changed in that way that it uses a smaller threshold depending on the table size. In our application we periodically update statistics with fullscan. With this new behavior the auto update statistics runs after a smaller modification rate and uses only small sample rate. Until our maintenance job will update statistics again with fullscan next time, the auto statistics update runs and resets the ‘fullscan’ results. This could cause a performance problem in our application.
Is there any way to use the old behavior with auto statistics update without going back to a lower compatibility level?

Until our maintenance job will update statistics again with fullscan
next time, the auto statistics update runs and resets the ‘fullscan’
results

This is the side effect of updating statistics with fullscan on a weekly basis, it becomes more of a guessing game when your statistics are going to be automatically updated.
As mentioned in the comments and since you hopefully are on a patch level of SQL Server 2016 SP1 CU4 or above, setting the persisted sample rate higher can work for the tables that you are updating WITH FULLSCAN on a weekly basis.
You could try working with a lower sample rate than fullscan but still higher than the default sample rate for these tables to reduce the time it takes to update.
Another setting that can help due to your auto update statistics taking a longer time when a higher sample rate is used is to enable ASYNC_STAT_UPDATES. Enabling asynchronous stat updates makes your queries not wait for your statistic updates when enough changes have been made, causing them to use the old statistics which have a high sample rate.
The caveat is that the lowest level possible to enable asynchronous statistic updates is for your entire database. It would be wise to test this setting in development.
UPDATE STATISTICS dbo.tablename WITH SAMPLE 1 PERCENT, PERSIST_SAMPLE_PERCENT = ON;
UPDATE STATISTICS dbo.tablename WITH SAMPLE 5 PERCENT, PERSIST_SAMPLE_PERCENT = ON;
UPDATE STATISTICS dbo.tablename WITH FULLSCAN, PERSIST_SAMPLE_PERCENT = ON;

To enable asynchronous statistic updates:
ALTER DATABASE [DatabaseName] SET AUTO_UPDATE_STATISTICS_ASYNC ON WITH NO_WAIT;

Remember that when you truncate the table the sample rate is no longer persisted and when new indexes and their corresponding statistics are created that they will have the default sample rate.
Another idea would be to update your statistics more often for the tables that have more changes if your server has the resources to do so.


Efficient group by on sorted CSV using PostgreSQL FDW I have a large CSV file sorted on the first column. Here is an extremely simplified version):

a 7
a 4
b 6
c 3
c 9
c 2

I want to group by the first column and sum the second.

How do I tell the PostgreSQL foreign data wrapper (fdw) to assume that the file is sorted on the first column, thereby not scanning the entire file before producing the output?

I am using version 11.1

Update: The above example is dramatically simplified. In the real life use case the first column has a very high number of values. It is the Google ngrams data set where first column is a phrase, second is the year, third is the occurrences in literature published that year. I want phrases with total count over all years greater than say 10,000. A simple python program can stream the result efficiently. So why not PostgreSQL? FDW could simulate an index over the table if informed of the sort order, just as it simulates the table from the CSV file, so I asked if it does something like that.

My main concern is efficiency. I previously did this using Unix command line tools such as awk but I would like to avoid juggling Unix scripts which are pretty much specific hand crafted execution plans. Save me PostgreSQL!!

I'm afraid not.

There is no such thing as a "sorted csv" file format, all you can have is a csv file that just happens to be sorted. I could take your example file and move the top line to the bottom and it would still be a valid csv.

If the fdw made that trusted your assertion and you made a mistake it would return incorrect results which is obviously unacceptable.

So, you will either want to import that file if you are doing lots of processing, convert it to a sorted db file format that fdw supports or pre summarize the data if that is all you are doing.


OPENXML to get content by using element and attribute name In this SQL Server OPENXML example:
declare @xmldata xml

set @xmldata =
'<?xml version="1.0"?>
<wddxPacket version="1.0">
    <data>
        <var dimension="task"><string>Task 1</string></var>
        <var dimension="startdate"><string>2020-11-14</string></var>
    </data>
    <data>
        <var dimension="startdate"><string>2020-11-15</string></var>
        <var dimension="task"><string>Task 2</string></var>
    </data>
</wddxPacket>'

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xmldata

SELECT *
FROM OPENXML(@hDoc, '//wddxPacket/data',2)
        WITH(
            task          varchar(200) 'var[1]/string',
            startdate     varchar(200) 'var[2]/string'
    )

EXEC sp_xml_removedocument @hDoc

This is the current output:
task         startdate
------       ----------
Task 1       2020-11-14
2020-11-15   Task 2

But I really want this output:
task         startdate
------       ----------
Task 1       2020-11-14
Task 2       2020-11-15

To identify the content, I cannot use "var" alone, because all elements of interest are named "var". I cannot use position [1] or [2], because the position numbers are inconsistent in the XML, resulting in the current mixed-up output. How do I identify the elements based on "var" and the "dimension" attribute names of "task" or "startdate"?

You need to use a different XPath expression to match the attribute values, eg var[@dimension="task"]/string.
Like this:
declare @xmldata xml

set @xmldata =
'<?xml version="1.0"?>
<wddxPacket version="1.0">
    <data>
        <var dimension="task"><string>Task 1</string></var>
        <var dimension="startdate"><string>2020-11-14</string></var>
    </data>
    <data>
        <var dimension="startdate"><string>2020-11-15</string></var>
        <var dimension="task"><string>Task 2</string></var>
    </data>
</wddxPacket>'

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xmldata

SELECT *
FROM OPENXML(@hDoc, '/wddxPacket/data',2)
        WITH(
            task          varchar(200) 'var[@dimension="task"]/string',
            startdate     varchar(200) 'var[@dimension="startdate"]/string'
    )

EXEC sp_xml_removedocument @hDoc

outputs
task    startdate
------- ------------
Task 1  2020-11-14
Task 2  2020-11-15


What are possible ways to migrate SQL Server DB to PostgreSQL DB? Currently I am assigned to complete the task of migrating several small to large databases (from 10GB to 200GB) from SQL Server to PostgreSQL RDBMS.

I would like to ask the community to share experience of migration.

Are there free ways to migrate? I am aware of creating SSIS solution
but it seems that there I have to migrate table by table
Which tool have you successfully used to complete such tasks?

Thank you

You can try to use Foreign Data Wrapper and tds_fdw

Or you can try pgloader.

Both work perfectly, are easy to set up and are packaged on any serious Linux distributions.


Join relation on itself and pull distinct rows I have relation A (reviewer_id, book_id, trivalFields)
Suppose at only 1 or 2 reviews exist for the book_id.
I would like a result set that
review_id (first), review_id (second), book_id.
Example:

  johnson, smith, 'horton hears a who'
  johnson, smith, 'green eggs and ham'.

But not

  johnson, smith, 'horton hears a who'
  smith, johnson,  'horton hears a who'
  johnson, smith, 'green eggs and ham'.
  smith, johnson,  'green eggs and ham'.

I know how do something like this with brute strength code - but how to do it in pure SQL??

PostgreSQL - by the way.

SELECT t1.reviewer_id AS first,
       t2.reviewer_id AS second,
       t1.book_id
FROM table AS t1
LEFT JOIN table AS t2 ON t2.book_id = t1.book_id
                     AND t2.reviewer_id > t1.reviewer_id

or

SELECT MIN(reviewer_id) AS first,
       CASE WHEN COUNT(reviewer_id) > 1
            THEN MAX(reviewer_id)
            ELSE NULL
            END AS second,
       book_id
FROM table
GROUP BY book_id


If NoSQL stands for "Not only SQL", is SQL a subset of NoSQL? The definition is a bit confusing - basically I'm asking if SQL is a subset of the NoSQl family:

I'm asking this because "Not-only" means NoSQL is much larger, but still includes SQL as a part of it.
On the other hand, since we can't do typical sql operations like joins in a NoSQL database, then SQL is not part of nosql!
I'm wondering which holds true?

TL;DR
The main distinction between database systems is not the language used to query the database, but rather the consistency model of the system. The Venn diagram should be two intersecting sets - SQL is not a proper subset of NoSQL, but rather its own data access language which may or may not be complemented by other techniques. SQL would be a proper subset of database query languages.

There is NoSQL, OldSQL and NewSQL.
NoSQL used to mean "Not SQL" but now is (more) likely to mean "Not only SQL" - as many of the providers of NoSQL systems are (trying to) bolt-on/graft SQL interfaces over their Key-Value (KV), Document or Graph stores.
OldSQL systems are essentially the main RDBMS providers' offerings (Oracle, SQL Server, Sybase, PostgreSQL, MySQL...). On page 13 of this presentation (.pdf), Michael Stonebraker purports to show that OldSQL only spends 4% of the time doing "useful" work - also to be found here:

His contention is that one should split OLTP work and OLAP work between different systems - OLTP should be done by shared nothing sharded architectures such as, suprise, surprise, his own system, VoltDB and that OLAP should be done by columnar store (also, see here) type architectures (such as Vertica, in which he also had a role). It is worth noting that Stonebraker, as well as being very successful in the commercial arena is also huge in academia and has won a Turing award - Computer Science's "Nobel prize"!
NewSQL is (IMHO) the most interesting of the systems to spring (figuratively speaking) like the Phoenix reborn from the ashes of OldSQL. Their USP is that they are HTAP systems (Hybid Transactional and Analytical Processing).
These are distributed systems which can support both OLAP and OLTP queries simultaneously due to the data being spread over several nodes - which can be on or in the same rack, data centre or continent and/or be globally distributed between and amongst cloud providers to increase resilience and redundancy - expect to add ~ a '0' to the cost for every fractional '9' you add to your uptime provision.
They use a consensus algorithm (usually Raft or Paxos) to coordinate nodes' data and sharding is transparent - even to the systems' DBA's. The three exemplars of such systems would be CockroachDB, TiDB and YugaByte.
It is interesting to note that, while having a certain level of success, these systems are not (yet?) household names. The "big boys" are fighting back with columnar store and KV offerings bolted/grafted on to their own systems. Of particular interest in this debate is that these systems themselves sit "on top" of a KV store (usually RocksDB - although CockroachDB is developing their own one in Go called Pebble). PostgreSQL also has documents (JSONB) and a KV store.
To answer the question, the real distinguishing feature between systems is not the interface one uses to query the data (which can and does range from direct C language programming (imperative) to SQL (declarative) - and flavours/adaptations thereof), but rather the transaction consistency model.
These models are either ACID (consistent) vs. BASE (available) and the key is where these systems fit with respect to the CAP theorem. Also, KV stores can support some or all ACID transaction characteristics.
OldSQL and NewSQL value consistency above all else ('CP' under the CAP system) - their argument is that, for example, a banking system with inconsistent results is a recipe for disaster (true!).
However the NoSQL aficionados would suggest that not all systems require cast-iron consistency. For example, ordering a book from Amazon (say) using a BASE database ('AP' in CAP terms) - might (incorrect stock level shown) be a few days late, or even cancelled - but the upside is faster queries and easier operation (no consensus to maintain).
This is the crux of the distinction between database systems - 'CP' or 'AP'! CP will always either give you the correct answer (majority of nodes still available) or no answer, whereas AP systems will normally respond (even if only one node is up) but your answer may not have taken other nodes' data updates into account when responding (i.e. network links between them down...).
I hope this is a satisfactory "first-pass" at an answer - it's a large topic and, for db nerds, absolutely fascinating. I would urge you to read around it (Wiki is a good start, but no substitute for primary sources). In particular, I would advise you to look in detail at the underlying architectures of CockroachDB and TiDB and see how they shard and move data from node to node while maintaining consistency.
There are comprehensive lists of various NoSQL systems here and here (best IMHO). There's also a popularity ratings site here (but avoid the comparisons - they just repeat the blurb from the systems' websites - often out-of-date).
Final word, there are "mixed-model" (or multi-model) databases (ArangoDB and OrientDB - both have F/LOSS and Enterprise versions - OrientDB now part of SAP), but there's a reason why they're not household names. ArangoDB doesn't support ANSI SQL, but rather its own flavour - AQL and neither does OrientDB support ISO SQL.
The best multi-model db that I've worked with is PostgreSQL which has KV and document stores as mentioned above. You can use SQL to query these and join with "ordinary" tables. Work is ongoing to add OpenCypher - an open graphical query language standard - to it (see here and here).
Final, final word: PostgreSQL also has a Columnar Store extension and a Time Series extension - both of which are very important (sub)-classes of database system. Both extensions are just that, extensions and not forks - you can use "normal" SQL (i.e. the full range of PostgreSQL's very standards compatible SQL) with these extensions.
So, we can see that while NoSQL is, in some cases, but not all (many systems designers are happy to remain as simple KV stores for example), adding SQL capabilities, SQL is fighting back, adopting and adapting to new and/or more sophisticated data storage and retrieval requirements - some of which I haven't even touched on (GIS systems...). So to say that NoSQL fully encompasses SQL would be an incomplete picture...
As pointed out in comments to the question itself, the SQL standard now tackles non-relational paradigms and this will only increase into the future! Also addressed by other answers - worth reading (1 & 2)!


Configuring Availability group between Two SQL Server Failover Cluster Instances I am having two Windows clusters. Two SQL cluster instances are there in each two node cluster (

Data centre 1 :Windows Cluster 1-SQL2016CLUS1

Node 1
Node 2
SQL 2016 failover cluster instance1 -SQL2016DB01
SQL 2017 failover cluster instance2 -SQL2017DB01

Data Centre 2: windows cluster 2-SQL2016CLuS2

Node 3
Node 4
SQL 2016 failover cluster instance1 -SQL2016DB02
SQL 2017 failover cluster instance2 -SQL2017DB02

Now I want to configure "Availability Groups" between two SQL 2016 cluster instances(SQL2016DB01 & SQL2016DB02)
How to configure it?

For configuring "Distrubuted Always On Availability groups" We need to create AOAG right(with in same data centre)? How to create AOAG, because i have only one instance

You can try a “distributed availability group”:

  A distributed availability group is a special type of availability group that spans two separate availability groups. The availability groups that participate in a distributed availability group do not need to be in the same location. They can be physical, virtual, on-premises, in the public cloud, or anywhere that supports an availability group deployment. This includes cross-domain and even cross-platform - such as between an availability group hosted on Linux and one hosted on Windows. As long as two availability groups can communicate, you can configure a distributed availability group with them.


Unique index for subquery with RANK() function I have the following tables:

CREATE TABLE Revision (
    RevisionId INT PRIMARY KEY IDENTITY,
    UserName NVARCHAR(256) NOT NULL,
    DateTime DATETIME NOT NULL DEFAULT GETDATE()
)

CREATE TABLE MyEntity (
    MyEntityId INT NOT NULL,
    RevisionId INT NOT NULL FOREIGN KEY REFERENCES Revision (RevisionId),
    Deleted BIT NOT NULL DEFAULT 0,
    Name NVARCHAR(256) NOT NULL,
    Body NVARCHAR(MAX) NOT NULL,
    PRIMARY KEY (MyEntityId, RevisionId)
)

That is, the MyEntity table has all changes to an entity. When an entity is created, modified, or deleted, a new record is inserted into MyEntity and into Revision, so that the entire history is tracked.

I would like a view to have the latest version of each entity:

CREATE VIEW MyEntityLatest WITH SCHEMABINDING AS
SELECT
    Latest.MyEntityId,
    Latest.Name,
    Latest.Body
FROM dbo.MyEntity
INNER JOIN (
    SELECT
        MyEntityId,
        RevisionId,
        Name,
        Body,
        RANK() OVER (PARTITION BY MyEntityId ORDER BY RevisionId DESC) AS RevisionIdDesc
    FROM MyEntity
    WHERE Deleted = 0
) AS Latest
    ON dbo.MyEntity.MyEntityId = Latest.MyEntityId
    AND dbo.MyEntity.RevisionId = Latest.RevisionId
WHERE Latest.RevisionIdDesc = 1

However, I would like to create an unique index (and hence constraint) such that the Name is unique for the latest revision only. I cannot create an index on the view, because of the subquery in the view.

How can I accomplish this?

Relying on the order of RevisionId to determine the latest revision is incredibly dangerous, and points to a design issue here.

Since you're using SQL Server 2008+, what I would recommend is to add a bit flag IsLatest to the MyEntity table to identify the latest revision. Then, create a unique filtered index to constrain each entity to only have a single latest revision (technically, this isn't required given what you asked for, but it's a Really Good Idea):

CREATE UNIQUE NONCLUSTERED INDEX UC_MyEntity_IsLatest_MyEntityId
    ON [dbo].[MyEntity](IsLatest, MyEntityId)
        WHERE (IsLatest = 1);

Then use the same technique to enforce the constraint on the name:

CREATE UNIQUE NONCLUSTERED INDEX UC_MyEntity_IsLatest_Name
    ON [dbo].[MyEntity](IsLatest, Name)
        WHERE (IsLatest = 1);

You'll need to manage the flag by turning it off from the "previous latest" revision before inserting the "new latest" revision, which is not much extra work. I'll leave it up to you to determine how this should work with the Deleted flag involved.

This solution enforces the constraint by design, and will greatly simplify the view definition, which you may not even need to index separately. (Indexing the Body column which is nvarchar(MAX) is a bit suspect, but that's a separate issue.)

Note: I included the IsLatest column in the key of the indexes on purpose, as the optimizer isn't smart enough to implicitly include the value of that column from the filter predicate (since the predicate is arbitrary, I assume it doesn't even try). This is what will allow the index to be used when you specify WHERE IsLatest = 1 in a query.


How to Restore MongoDB Database After Having Deleted DB Files From Directory I'm running MongoDB 2.6 on Ubuntu 14.04 and I accidentally deleted db file in /var/lib/mongodb.

I cannot restart the MongoDB instance:

mongo: error SEVERE: unable to open file number 5.

If I purge and re-install will I be able to restore the data? Any help is appreciated.

3-rd Party Tool

You might want to consider recovering the files using the PhotoRec tool.

Reference: PhotoRec Homepage

  PhotoRec is file data recovery software designed to recover lost files including video, documents and archives from hard disks, CD-ROMs, and lost pictures (thus the Photo Recovery name) from digital camera memory. PhotoRec ignores the file system and goes after the underlying data, so it will still work even if your media's file system has been severely damaged or reformatted.

Prerequisites

First of all you will have to ensure that no more writes are performed against the disk in your Ubuntu system, otherwise you might be overwriting the deleted files.

Step by Step Guide

Then you can consider following the step by step instructions provided on their website.

  This Recovery example guides you through PhotoRec step by step to recover deleted files or lost data from a reformatted partition or corrupted file system. For lost/deleted partitions or deleted files from a FAT or NTFS file system, try TestDisk first - it's usually faster and TestDisk can retrieved the original file names. Translations of this PhotoRec manual to other languages are welcome.

  ...

  Under Unix/Linux/BSD, you need to be root to run PhotoRec (ie. sudo testdisk-6.13/photorec_static)

Reference: PhotoRec Step By Step

Short Quick Version

Start PhotoRec from a USB medium or other disk than where your database files reside.
Select disk.
Hit Proceed
Select partition.
Hit either
 a. Search
 b. Options
 c. File Opt
 d. Quit (well not really, unless you are unsure)
After hitting Search the tool will try and to determine the file system. If you know your filesystem type, select the appropriate one.
Select either the Free space option or the Whole option depending on your requirements.
Select where your recovered files should be copied to.

  Tip: Copying to another drive (USB, eSata, other disk than where the database resides) will increase the probability of not destroying the file you are trying to recover.

Hope that the files are displayed as recovered in the summary.

The files on your disk might have already been overwritten and unrecoverable. But good luck anyway.

  Disclaimer
  I am in not way affiliated with PhotoRec or any of its employees.
  No guarantee given that the provided solution will result in recoverable files. You are trying this procedure at your own risk.

Future Considerations

Implement a backup solution (script/3-rd party tool/...)
Test your backup solution
Test a restore of database backup which were created with the backup solution

Reference: MongoDB Backup Methods (MongoDB Site)


Psql refusing connections after restart I began receiving these errors consistently after running a script that collects and stores data in psql:

  Could not connect because of FATAL:  remaining connection slots are
  reserved for non-replication superuser connections

I am running psql on Ubuntu.  So I decided to restart psql with:

sudo service postgresql restart

Now however, psql has shut down completely

Could not connect because of could not connect to server: Connection refused
        Is the server running on host "localhost" (127.0.0.1) and accepting
        TCP/IP connections on port 5432?

I am completely new to maintaining psql on a server and would be grateful for any advice.

Where do I even begin to solve this problem? How can I get psql to start again without losing any data that is in this massive database?

I began receiving these errors consistently after running a script that collects and stores data in psql:

Your max_connections is too low, or that script isn't dying or releasing the connection.

If you're getting Connection refused after you restart, then perhaps you have multiple copies of postgresql installed on the same machine and you mean to connect to a different port (like 5433). If you're using Ubuntu or Debian run pg_lsclusters and paste the output.


Oracle remove and place (re-install) database on 10g I need to put together a plan to execute some change scripts to a Oracle 10g database, which will add some values, change some values a drop a table and create a new table.

I have a step for creating a DMP of the original database before the changes and for running a script that will execute all the changes.
But I need a back-out plan if things go wrong.
I was wondering what is the bets step(s) to drop the database and import the old DMP file?

I know about the sqlplus command:

imp <touser>/<password>@<host_address>/<service> file=<filename>.dmp fromuser=<fromuser> touser=<touser> log=backup_import.log

But what is the best way to drop the existing database? Is it just

drop user <schema_user> cascade;

Thanks in advance.

K

The best way to drop database is to use DBCA graphic tool. Or you can use this sequence of commands:

shutdown immediate
startup exclusive mount
alter system enable restricted session;
drop database;

But instead I suggest using RMAN backup and restore.


Will auto vacuum analyzer delay visibility of bulk insertion or updation I am reading records from table A and doing some calculations and inserting and updating the table B. I am doing yearly migration from 2017 to now. For2017-2019 I am using different query(Function  A) and 2020 jan to now different query(Function  B). My migration has started at 9:57 and all the migration are over by 16:37. I am looping the year and months through service. Both the function s returning true to the service and I couldn’t find and bugs in postgres logs as well.But there are no inserted records in the table B. But auto-vacuum analyzer is running even more than 12 hours after the migration over which is showing background started time as  9:58 which is my migration started time. Someone please advise me what could be the reason and how can I solve it. Since I am new bee to the postgres and I am a software engineer not a DBA not sure how can I solve.

The reason is synchronous commit. Postgres has introduced this to increase the performance. The client gets results before actually it got committed. That’s why table did not get updated , getting time to update. I was checking before it get updated
Source: https://www.mydatahack.com/top-10-configuration-parameters-to-tune-postgresql-for-better-performance/


How to use a value from insert in conditional insert in postgres insert into addresses(street, city, user_id)
select
      'street1','LA', 2
where exists (select * from users where users.id = $$$$$ AND users.storeaddress='true'))

I want to insert address for a user only if the storeaddress value for him is 'true'.

I want to sql to automatically use 2 in place of $$$$$. How to do this in sql?

If I simply do user_id in place of $$$$$ I get

HINT:  There is a column named "user_id" in table "addresses", but it cannot be referenced from this part of the query.

You can use a CTE to supply the values:

with data (street, city, user_id) as (
  values ('street1','LA', 2)
)
insert into addresses(street, city, user_id)
select *
from data
where exists (select *
              from users
              where users.id = data.user_id
                and users.storeaddress = true);

Or alternatively a derived table:

insert into addresses(street, city, user_id)
select *
from (
   values ('street1','LA', 2)
) as data (street, city, user_id)
where exists (select *
              from users
              where users.id = data.user_id
                and users.storeaddress = true);


Simultaneous SQL full backups of newly created databases I would like to take a backup of newly created databases but within a minute if the user creates three databases the script can able to take only one database but for one new database which was created 15 mins before it is working.
Anything I need alters to be able to take simultaneous multiple new database backups within the specified time (here the script job runs every 15 mins)?
Below is the script.
-- Alert on the new database(s) created in the past 15 mins
-- Replace with your recipients in line #24

-- declare the variable for interval Minutes to detect the new database(s)
DECLARE @IntervalInMinutes TINYINT -- check new database(s) in the past 15 mins(s)
SET @IntervalInMinutes = 15 -- replace with the number of mins you wish to check for new database(s)

select GETDATE()
-- check for new database(s)
IF EXISTS(
    SELECT 1
    FROM master.sys.databases db
    WHERE DATEDIFF(MINUTE, db.create_date, GETDATE()) <= @intervalInMinutes
        AND db.name <> 'tempdb')
BEGIN -- new database(s) exist(s)

DECLARE @DBname varchar(64)

SELECT  @DBname =  [name]
    FROM master.sys.databases db
    WHERE DATEDIFF(MINUTE, db.create_date, GETDATE()) <= @intervalInMinutes
        AND db.name <> 'tempdb'

DECLARE @Path varchar(64)
declare @dates varchar(64)
set @dates = CONVERT(date, GETDATE(),112);
SET @Path  = 'H:\Fullbackups\'+ @DBname +'_'+ @dates+'.sqb'

BACKUP DATABASE  @DBname TO
DISK =@Path
WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
--GO

P.S. I have not provided the dbmail and alert script here.

You can use a CREATE_DATABASE Event Notification to monitor database create events and this solution even works on Sql Express (where the Sql Agent doesn't exist). This link shows you how to set up SQL Mail for SQL Server Express.
I modified this example from Jonathan Kehayias, which was tracking autogrowth, to instead audit the create database event.
-- Using msdb prevents the need for certificate signing the
-- activation procedure to execute sp_send_dbmail across
-- databases
USE [msdb];
GO

-- Drop the notification if it exists
IF EXISTS (
        SELECT *
        FROM sys.server_event_notifications
        WHERE NAME = N'CaptureDatabaseCreateEvent'
        )
BEGIN
    DROP EVENT NOTIFICATION CaptureDatabaseCreateEvent ON SERVER;
END

-- Drop the route if it exists
IF EXISTS (
        SELECT *
        FROM sys.routes
        WHERE NAME = N'DatabaseCreateEventRoute'
        )
BEGIN
    DROP ROUTE DatabaseCreateEventRoute;
END

-- Drop the service if it exists
IF EXISTS (
        SELECT *
        FROM sys.services
        WHERE NAME = N'DatabaseCreateEventService'
        )
BEGIN
    DROP SERVICE DatabaseCreateEventService;
END

-- Drop the queue if it exists
IF EXISTS (
        SELECT *
        FROM sys.service_queues
        WHERE NAME = N'DatabaseCreateEventQueue'
        )
BEGIN
    DROP QUEUE DatabaseCreateEventQueue;
END

--  Create a service broker queue to hold the events
CREATE QUEUE [DatabaseCreateEventQueue]
    WITH STATUS = ON;
GO

--  Create a service broker service receive the events
CREATE SERVICE [DatabaseCreateEventService] ON QUEUE [DatabaseCreateEventQueue] ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO

-- Create a service broker route to the service
CREATE ROUTE [DatabaseCreateEventRoute]
    WITH SERVICE_NAME = 'DatabaseCreateEventService'
        ,ADDRESS = 'LOCAL';
GO

-- Create the event notification to capture the events
CREATE EVENT NOTIFICATION [CaptureDatabaseCreateEvent] ON SERVER
    WITH FAN_IN
FOR CREATE_DATABASE TO SERVICE 'DatabaseCreateEventService'
    ,'current database';
GO
--  Alter the queue to use the activation procedure
ALTER QUEUE [DatabaseCreateEventQueue]
   WITH STATUS=ON,
      ACTIVATION
         (STATUS=ON,
          PROCEDURE_NAME = [ProcessDatabaseCreateEvents],
          MAX_QUEUE_READERS = 1,
          EXECUTE AS OWNER);
GO

I then tweaked his example stored procedure that consumes the event.
Make sure you change this line in the stored procedure to be your email address
@recipients = 'YourEmailAddress'
DROP PROCEDURE [dbo].[ProcessDatabaseCreateEvents];
GO

CREATE PROCEDURE [dbo].[ProcessDatabaseCreateEvents]
    WITH EXECUTE AS OWNER
AS
DECLARE @message_body XML;
DECLARE @message_sequence_number INT;
DECLARE @dialog UNIQUEIDENTIFIER;
DECLARE @email_message NVARCHAR(MAX);

WHILE (1 = 1)
BEGIN
    BEGIN TRANSACTION;

    -- Receive the next available message FROM the queue
    WAITFOR (
            RECEIVE TOP (1) -- just handle one message at a time
            @message_body = CAST(message_body AS XML) FROM dbo.DatabaseCreateEventQueue
            )
        ,TIMEOUT 1000;-- if queue empty for 1 sec, give UPDATE AND GO away

    -- If we didn't get anything, bail out
    IF (@@ROWCOUNT = 0)
    BEGIN
        ROLLBACK TRANSACTION;
        BREAK;
    END

    DECLARE @EventType VARCHAR(128);
    DECLARE @ServerName VARCHAR(128);
    DECLARE @PostTime VARCHAR(128);
    DECLARE @DatabaseName VARCHAR(128);
    DECLARE @TextData VARCHAR(max);
    DECLARE @GrowthPages INT;

    SELECT @EventType = @message_body.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(128)')
        ,@ServerName = @message_body.value('(/EVENT_INSTANCE/ServerName)[1]', 'varchar(128)')
        ,@PostTime = CAST(@message_body.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime') AS VARCHAR)
        ,@DatabaseName = @message_body.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(128)');

    -- Generate formatted email message
    SELECT @email_message = 'The following DatabaseCreate event                                      occurred:' + CHAR(10) + CAST('ServerName: ' AS CHAR(25)) + @ServerName + CHAR(10) + CAST('PostTime: ' AS CHAR(25)) + @PostTime + CHAR(10) + CAST('DatabaseName: ' AS CHAR(25)) + @DatabaseName
    -- Send email using Database Mail
    EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SqlServerEmailProfile'
        ,
        -- your defined email profile
        @recipients = 'YourEmailAddress'
        ,-- your email
        @subject = 'DatabaseCreate Event Notification'
        ,@body = @email_message;

    --  Commit the transaction.  At any point before this, we could roll
    --  back. The received message would be back on the queue AND the
    --  response wouldn't be sent.
    COMMIT TRANSACTION;
END

Then, I ran a simple create database
USE [master]
GO
DROP DATABASE IF EXISTS [TestCreateDb]
GO
CREATE DATABASE [TestCreateDb]

After a few seconds or so, I received the email with the event data in it. You can modify the event consuming stored procedure to capture and process whatever you need, such as initiating the backup immediately.


I want to get all the weeks of a month with the start and end dates of those weeks without creating a table My SQL is:

SELECT date_trunc('week', '2020-06-01'::timestamp)::date ||'-'||
(date_trunc('week', '2020-06-01'::timestamp)+ '6 days'::interval)::date;

However, using this code it shows only one week's output (2020-06-01-2020-06-07). The actual output I want is:

 (2020-06-01-2020-06-07,
 2020-06-08-2020-06-14,
 2020-06-15-2020-06-21,
 2020-06-22-2020-06-28)


SQl query to create a report based on multiple relational tables I have an e-commerce website and I'm in the process of creating a sales report. These reports need to be divided by year and month.

Each sale made on the website will generate a relational link between multiple tables, where I need to get some values to display the proper report. Besides showing just what items were sold, I also need to show, for example, how many of those sales were made using credit card, money, etc... How many of them were shipped via postal office, third part services, etc.

Before I continue, keep in mind I'm not an advanced user in dataBases/BackEnd, so any improvement/suggestions is very welcome.

I'm using PHP/SQL on the backend and a MySql database with a model as the following image:

The basic information I need to know is (all of these are the total for each month):

Value: The total amount of sales;
Sales: Total sales (based on orderStatus);
Payment: Total sales using each payment type;
Delivery: Total sales using each delivery type;
Reported sales: Total sales reported by user (based on orderStatus);
Canceled sales: Total sales canceled (based on orderStatus);

I was able to built this report, but It seems to be very poor and/or not the right way to do this. It felt very messy, so this is why I'm here to ask your help to guide me on how to build this type of query and to improve my code.

To create the segment by Year/Month I'm using this code:

SELECT DISTINCT YEAR(created) as 'year' FROM order
for ($i=1; $i<=12; $i++) { ... }

And this is the code I'm running inside the for to get the sales for each month:

//Range between 3-6 are status of OK (shipped, delivered, taken)
$query = "
    FROM order a, orderStatus b
    WHERE YEAR(a.created) = '$year' AND MONTH(a.created) = '$i'
    AND b.id = a.id_status AND b.status >= 3 AND b.status <= 6
";

$totalAmount = sql("SELECT SUM(a.totalValue) as 'totalValue' $query"); //Total amount in $
$totalSales  = sql("SELECT COUNT(a.id) as 'totalSales' $query");

//Total by delivery type
$totalDelivery = sql("SELECT COUNT(a.id_delivery) as 'totalDelivery' $query AND a.id_delivery = 1");
$totalTaken    = sql("SELECT COUNT(a.id_delivery) as 'totalTaken' $query AND a.id_delivery = 2");

//Total by payment type
$totalOnline = sql("SELECT COUNT(a.id_payment) as 'totalOnline' $query AND a.id_payment = 1");
$totalCard   = sql("SELECT COUNT(a.id_payment) as 'totalCard' $query AND a.id_payment = 2");
$totalMoney  = sql("SELECT COUNT(a.id_payment) as 'totalMoney' $query AND a.id_payment = 3");

//Total canceled/Reported sales
$totalCanceled = sql("SELECT COUNT(a.id) as 'totalCanceled' FROM order a, orderStatus b WHERE YEAR(a.created) = '$year' AND MONTH(a.created) = '$i' AND b.id = a.id_status AND b.status == 7");
$totalCanceled = sql("SELECT COUNT(a.id) as 'totalCanceled' FROM order a, orderStatus b WHERE YEAR(a.created) = '$year' AND MONTH(a.created) = '$i' AND b.id = a.id_status AND b.status == 8");

As you can see, the sql works, but it's not very optimal. Also, It feels like I'm repeating myself a lot.

For example, I created a variable with the most common piece of code used in each query, most of them need to match an specific range of orderStatus, and I need to get the total count of those informations. With the exception of the canceled and reported sales, which are of a different orderStatus. Besides that, i'm creating a manual check on the delivery and payment status, it could be somehow automated.

The way I'm doing will also return every type of result, for example, if there were no payment with money, it will execute the query and return 0. This isn't needed, as I can check for a valid and existing information on the FrontEnd. If this can be done, great, but if not, it's not a big problem to get only when there is data stored.

So, how can I optimize this code, or even my database structure in order to have a better result/query to generate these sales report?

This can be written as a single query.
I have guessed the last line.

SELECT
   MONTH(a.Created) AS `Month`
 , SUM(a.totalValue) as totalValue  -- Total amount in $
 , COUNT(a.id) as totalSales
 , SUM(CASE WHEN a.id_delivery = 1 AND b.status >= 3 AND b.status <= 6 THEN 1 END) as totalDelivery
 , SUM(CASE WHEN a.id_delivery = 2 AND b.status >= 3 AND b.status <= 6 THEN 1 END) as totalTaken
 , SUM(CASE WHEN a.id_payment = 1 AND b.status >= 3 AND b.status <= 6 THEN 1 END) as totalTaken
 , SUM(CASE WHEN a.id_payment = 2 AND b.status >= 3 AND b.status <= 6 THEN 1 END) as totalCard
 , SUM(CASE WHEN a.id_payment = 3 AND b.status >= 3 AND b.status <= 6 THEN 1 END) as totalMoney
 , SUM(CASE WHEN b.status IN (7, 8) THEN 1 END) as totalCanceled
FROM order a, orderStatus b
WHERE YEAR(a.Created) = ?
 AND b.id = a.id_status
GROUP BY MONTH(a.Created)
ORDER BY 1


How do you create a Geographic Point with MariaDB and MySQL? PostGIS provides a simple cast to construct a geography type from floating point lat, and long

SELECT ST_Point(1,2)::geography;     -- standard version (MM) less precision
SELECT ST_MakePoint(1,2)::geography; -- PostGIS version makes 2d, 3dz, 4d

However, despite the docs in MySQL clearly telling there is a difference between Cartesian and geodetic points, I can't find out how to construct a geography cord in MySQL or MariaDB?

MySQL

In MySQL 8+, the point is constructed the same way and whether or not it's geographic (geodetic) or geometric (carestian) is a function of the SRID and the POINT has the same type. So you'll want to use,

SELECT ST_SRID(Point(1,2), 4326);

You can set the SRID with ST_SRID

MariaDB

With MariaDB you're SOL. MariaDB still lacks the form that takes a second argument to set the SRID.

See also

How do I create a point with an SRID in MySQL?


Remote Query Cost - High Row Count I have a query that calls a remote table through Linked Server.
However, in the execution plan the Remote Query is 71% of total cost and the time taken to run is over 4mins. It returns all rows in the remote table (Actual Number of Rows value is over 1 million).

If I run the remote query separately, the Actual Number of Rows value is 2.

Why does it load all rows?

Below is the subquery part that calls the remote tables...

If I run the remote query part (the subquery) in a new window, the Actual Number of Rows is 2, and time taken is 1 second...

In versions of SQL Server earlier than 2012 (plus some patching), Linked Server permissions needed SA privs to use statistics information when processing remote queries. That could be what's messing you up.

For more information, see Conor Cunningham's talk on distributed queries here.


What is the definition of a bad index? How do we decide? What logic we should have? There are queries on the internet that finds bad indexes

Their logic is so simple though

If write count > read count = bad index

Here one example query

    SELECT  OBJECT_NAME(s.object_id) AS 'Table Name',
        i.name AS 'Index Name',
        i.index_id,
        user_updates AS 'Total Writes',
        user_seeks + user_scans + user_lookups AS 'Total Reads',
        user_updates - ( user_seeks + user_scans + user_lookups ) AS 'Difference'
FROM    sys.dm_db_index_usage_stats AS s WITH ( NOLOCK )
        INNER JOIN sys.indexes AS i WITH ( NOLOCK ) ON s.object_id = i.object_id
                                                       AND i.index_id = s.index_id
WHERE   OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
        AND s.database_id = DB_ID()
        AND user_updates > ( user_seeks + user_scans + user_lookups )
        AND i.index_id > 1
ORDER BY 'Difference' DESC,
        'Total Writes' DESC,
        'Total Reads' ASC ;
-- Index Read/Write stats for a single table
SELECT  OBJECT_NAME(s.object_id) AS 'TableName',
        i.name AS 'IndexName',
        i.index_id,
        SUM(user_seeks) AS 'User Seeks',
        SUM(user_scans) AS 'User Scans',
        SUM(user_lookups) AS 'User Lookups',
        SUM(user_seeks + user_scans + user_lookups) AS 'Total Reads',
        SUM(user_updates) AS 'Total Writes'
FROM    sys.dm_db_index_usage_stats AS s
        INNER JOIN sys.indexes AS i ON s.object_id = i.object_id
                                       AND i.index_id = s.index_id
WHERE   OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
        AND s.database_id = DB_ID()
        AND OBJECT_NAME(s.object_id) = 'AccountTransaction'
GROUP BY OBJECT_NAME(s.object_id),
        i.name,
        i.index_id
ORDER BY 'Total Writes' DESC,
        'Total Reads' DESC ;,

However is the issue this simple? Can we call all write > read = bad index?

Are there any better logic having SQL query that can define bad indexes?

Lets ignore indexes that ensures integrity of data such as unique constraints

Lets assume that indexes are updated and used every minute

I am using SQL server 2014 and here the results this query generates

Ty

However is the issue this simple?

Absolutely not. Unfortunately it can vary a lot between applications so while there are many "rules of thumb" like the one you state none of them are universally applicable apart from "an index that is never used is a waste".

  Can we call all write > read = bad index?

If that index is for enforcing integrity (i.e. it is a unique index or is supporting a foreign key) then that fact would generally trump a performance requirement. Also, the writes could be mostly at times where it doesn't matter (a bulk update in the middle of the night perhaps) or it could be really important that those reads are as fast as possible so slow writes are a price worth paying.

When thinking about these matters always consider the time period that any figures you are looking at cover. It could be that an index looks under-used this week, but next week is the start of a new reporting period so the users are performing a different set of actions and the index suddenly looks far more useful.

Never use rules-of-thumb as hard-and-fast rules to live by. Use them as suggestions and consider the results carefully with your knowledge of your specific application(s)'s needs.

  can you expand your answer and add more details?

Not without writing a whole book unless you have more specific questions, this is a very wide area. Many whole book have been written on the subject in fact... http://use-the-index-luke.com/ is a good place to start.


Agent service account shows as not clustered One of the SQL Server 2008 R2 instances I administer is running on a two node Windows Cluster (Windows Server 2008 R2 Enterprise)
When I run the DMV sys.dm_server_services, SQL Server Service Account shows as Y under the 'is_clustered' column.
However, Agent Service Account is an 'N' for the same field 'is_clustered'. Is it something that I should be worried about and what might be causing this? Does this mean that Agent will not fail over?
Thank you

No, it is nothing to be worried about - the Agent isn't considered a clustered service to SQL Server simply because it can't fail over on its own.

You should test a failover and be confident that SQL Server Agent will start on the other node and that jobs will run etc. (sometimes there can be local dependencies that won't exist on the other node(s)), but the DMV itself should not be a concern. I checked several clusters in our environment and they all say Y for the SQL Server service and N for the other services:

I can assure you that these clusters fail over correctly and that SQL Server Agent works on all nodes.


Select * from (every table in an subselect) I was trying to get an select * from (for column table result) every table_name with the result bellow, how can i achieve this?

(SELECT a.table_name
  FROM all_cons_columns a
  JOIN all_constraints c ON a.owner = c.owner
                        AND a.constraint_name = c.constraint_name
  JOIN all_constraints c_pk ON c.r_owner = c_pk.owner
                           AND c.r_constraint_name = c_pk.constraint_name
 WHERE c.constraint_type = 'R'
   AND c_pk.table_name = 'MY_TABLE_NAME')

like:

suppose that the result above was:

TABLE_NAME
TABLE_LOG_NOD
TABLE_LOG_CASH
TABLE_LOG_PLUG

i want to return an select * from (only one row of that table)every one of those tables listed.
Beware i cannot use variable, or any other thing related to PL/SQL, so only SQL.

Sorry for my bad english.
I hope it was clear.
Thank you anyone in advance.

This is not possible without using PL/SQL or a loop to generate the SQL in the program you're using to execute the SQL.


A tool to convert PostgreSQL database dumps between versions? On different computers/servers we have different versions of PostgreSQL installed and can't upgrade all of them yet.

Are there any tools which would allow to convert a database dump from the newer PostgreSQL version to an older one?

Are there tools or options to create a pg_dump file that is compatible to earlier versions of PostgreSQL

No there are no options or tools to create a backwards compatible pg_dump files.

Depending on how far back the version the data needs to be ported to the dump file may work there is no guarantee

A work around this limitation  would be run pg_dump using the -P --plain and --schema-only  modify the  resulting SQL file to make it compatible.

Restore the file.

Then run pg_dump again using -P --plain and -a --data-only options then restore the file.


separate data from one column to 2 columns I have three columns viz Name, Value,  Valueid.

I need to split the contents of value into two columns Valueof1 and Valueof2 based on the remaining two columns. Valueid has only two values either 1 or 2.

╔═══════╦═══════╦═════════╗
║ Name  ║ Value ║ Valueid ║
╠═══════╬═══════╬═════════╣
║ test1 ║   132 ║       1 ║
║ test2 ║   322 ║       2 ║
║ test3 ║   342 ║       1 ║
║ test1 ║   433 ║       2 ║
║ test2 ║   123 ║       1 ║
║ test3 ║   244 ║       2 ║
╚═══════╩═══════╩═════════╝

I'd like to see that data displayed like this:

╔═══════╦═══════╦═════════╦══════════╦══════════╗
║ Name  ║ Value ║ Valueid ║ Valueof1 ║ Valueof2 ║
╠═══════╬═══════╬═════════╬══════════╬══════════╣
║ test1 ║   132 ║       1 ║      132 ║      433 ║
║ test2 ║   322 ║       2 ║      123 ║      322 ║
║ test3 ║   342 ║       1 ║      342 ║      244 ║
║ test1 ║   433 ║       2 ║      132 ║      433 ║
║ test2 ║   123 ║       1 ║      123 ║      322 ║
║ test3 ║   244 ║       2 ║      342 ║      244 ║
╚═══════╩═══════╩═════════╩══════════╩══════════╝

sample data

;DECLARE @Data Table(Name  VARCHAR(20), Value INT, Valueid  INT
)
;INSERT INTO @Data
SELECT 'test1', 132, 1 UNION ALL
SELECT 'test2', 322, 2 UNION ALL
SELECT 'test3', 342, 1 UNION ALL
SELECT 'test1', 433, 2 UNION ALL
SELECT 'test2', 123, 1 UNION ALL
SELECT 'test3', 244, 2

Sql script

;WITH CTE
AS
(
    SELECT Name,[1] AS Value1,[2] AS Value2 FROM
    (
    SELECT * FROM @Data
    ) AS SRC
    PIVOT
    (
    MAX(Value) FOR Valueid IN([1],[2])
    )
    AS Pvt
)

SELECT c.Name,
       d.Value,
       d.Valueid,
       c.Value1,
       c.Value2
FROM CTE c
RIGHT Join @Data d
ON c.Name=d.Name

Expected Result http://rextester.com/AYGM10195

╔═══════╦═══════╦═════════╦══════════╦══════════╗
║ Name  ║ Value ║ Valueid ║ Valueof1 ║ Valueof2 ║
╠═══════╬═══════╬═════════╬══════════╬══════════╣
║ test1 ║   132 ║       1 ║      132 ║      433 ║
║ test2 ║   322 ║       2 ║      123 ║      322 ║
║ test3 ║   342 ║       1 ║      342 ║      244 ║
║ test1 ║   433 ║       2 ║      132 ║      433 ║
║ test2 ║   123 ║       1 ║      123 ║      322 ║
║ test3 ║   244 ║       2 ║      342 ║      244 ║
╚═══════╩═══════╩═════════╩══════════╩══════════╝


Postgres 11 - *prioritized* where clause in group table below is given:

create table cafes (
  id uuid not null
    constraint cafes_pkey primary key,
  identity uuid not null,

  is_closed boolean,
  is_illegal boolean
)

The problem is to select one cafe from each group (grouped by identity), which:

priority #1: has is_closed = true
priority #2: has is_illegal = true

That is, if we have a closed cafe, we select it. If there are no such cafes in group, we search by is_illegal = true.

Any ideas how to solve this problem? :)

Booleans can be ORDERED BY just like other types, with false coming before true when no direction is given.

You could use this in a DISTINCT ON, or in a LATERAL join.

SELECT DISTINCT ON (identity), *
from cafes
ORDER BY identity, is_closed DESC NULLS last
       , is_illegal DESC NULLS last


Creating tables PDO Sqlite Hello guys i have worked on mysqli but not pdo sqlite i don't know what syntax is to create tables here if not exists this is my code please tell me why am i getting error on last line.

$Database = new PDO('sqlite:'.$DB_Path);
$Database->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$Database->exec(
        "CREATE TABLE IF NOT EXISTS Requests
          (
            ID INT( 11 ) AUTO_INCREMENT PRIMARY KEY,
            Sender VARCHAR( 225 ),
            Recipient VARCHAR( 225 )
          )
      ");

It is most likely because you have specified the size for INT: ID INT( 11 ). Why do you need it? Try this syntax (add NOT NULL for Primary key):

CREATE TABLE IF NOT EXISTS Requests
      (
        ID INT NOT NULL PRIMARY KEY AUTOINCREMENT,
        Sender VARCHAR( 225 ),
        Recipient VARCHAR( 225 )
      )


Maintenance Plan for all Online databases doesn't execute for all databases Using SQL Server 2008 R2, I have a maintenance plan that has a basic Backup All Databases (ignore offline) task, and clean up after 7 days.

However, I note that sometimes I lack backups for (seemingly) random databases.

Even though the script says all databases, bar offline, is there some parameter set where databases won't be backed up? E.g. No changes to database since last backup, or some other conditions?

The particular database in question at this time has been updated in the last 24 hours, but hasn't been backed up in the past week.

edit: the recovery model is set to Simple, which is the same for most other databases on the server, so it's probably not that.
I've tried to look for other documentation, but I can't seem to find any technical details, only high level business/sales white papers.

In your Backup task, is your Backup Type set to Full?  If it is set to Differential then a backup may not be taken of every database.


Array of one value, for every integer in another field? I'm stumped on this one, I have the following:

Color  Qty  Length
('Blue', 5, 50),
('Blue', 1, 500),
('Blue', 1, 250),
('Green', 4, 175),
('Green', 2, 35),
('Green', 2, 80)

I need an array that groups by color, showing the length for each qty, by that I mean:

Blue: 50,50,50,50,50,500,250
Green: 175,175,175,175,35,35,80,80

I've tried all sorts of GROUP_CONCAT/CONCAT combinations but I am stuck on getting the length column to repeat for every qty.

I have a sqlfiddle here.

Is it even possible? :)

Figured this out if anyone comes across this and wants the solution:

SELECT color, TRIM(TRAILING ',' FROM GROUP_CONCAT(REPEAT(CONCAT(length,','),qty) SEPARATOR '') )
FROM buyer_advert
GROUP BY color

This makes a nice array like I wanted.


PostgreSQL - pgAdmin error creating a server I have the latest versions of PostgreSQL and its GUI-frontend, pgAdmin, installed.
When I try to register a new server a fatal error appears when pgAdmin tries to connect to the newly created server - password authentication failed for user "postgres".
I'm using a non-default port which was properly updated in the postgresql.conf file so I don't believe that is causing problems (restarted postgres too, as it so politely asks you to).
postgres processes are running properly:

Here you can examine the configuration settings I am using to register the server:

I've tried a few passwords, including my root password but none of them appear to be working. Maybe the user has to be created separately?

This is the first time I'm encountering anything besides SQL Server or Access so please bear with me if the question and its possible solution seem far too trivial.
I'm running Linux Mint 15, 32-bit.
I will provided additional information, if needed of course.

You need to enter the password of the database user, not the OS user. The "root password" is not applicable. Do you know the password of the database user postgres?

Most systems are set up allowing peer authentication for local connections, meaning a system user can log into the database with a database user of the same name without password. localhost is not a local connection. Its privileges are determined by host entries in your pg_hba.conf file.

Since you seem to be running Postgres and pgAdmin on the same server, I suggest you try a local connection without password. Quoting the pgAdmin manual:

  On Unix based systems, the address field may be left blank to use the
  default PostgreSQL Unix Domain Socket on the local machine, or be set
  to an alternate path containing a PostgreSQL socket.

Bold emphasis mine.
Of course, you have to run pgAdmin as the system user postgres for this to work.

This related answer on SO has a lot more details and links.


Generate table of all databases and schema with a single psql command In PostgreSQL is it possible to generate a list of all databases and all schema with a single psql command?

To be concrete, let me provide an example.

I have two databases in a PotgreSQL database cluster: accounts and people. In the accounts database there are these schema  (beyond the default): hr and finance. In the people database there are these schema (beyond the default): hr, admin, backup.

Is there a single psql command I can run that generates results something like this:

database | schema
-----------------
accounts |  hr
accounts |  finance
people   |  hr
people   |  admin
people   |  backup

Or is the only way to generate this information to connect multiple times and run separate queries?

In PostgreSQL is it possible to generate a list of all databases and
  all schema with a single psql command?

No. The list of schemas of a database is private to that database and cannot be obtained without being connected to it.
The list of databases is available with \l or select * from pg_database.


Database for efficient retrieval of data from large data set I am designing a new system that will be dealing with large data set. This system is expected to have over 10 million records. A user of the system needs to check for the availability of the name he wants to register. If nothing is returned, that means the name is available, the user can then proceed to register the name.

The database needs to be optimized for search. I was considering using mysql, but i don't know if there are better options. Thanks

All you need here is to make the username a unique index and MySQL will (generally) handle this very well.

Unfortunately, I had to write 'generally' because there can be many many other factors on your system that could slow this down. I recommend having MySQL 5.5 and the table be InnoDB. Proper indexing and configuration will go a long way in helping you.

The short of it is, MySQL/InnoDB is easily able to handle 10 million rows.


Postgres backup PITR strategies Note:-Might be the question sounds too broad. But, I believe ,it's very good platform(stackexchange) and I can get advice from worldwide expert DBA here. Since, I'm not able to make decision or choose the right option.

Que: I'm new to postgres and need to make PITR solution for our continuously growing database. Need to plan incremental backup solution as well since database size is increasing at rapid speed. If we can't afford to lose any data and also need to have PITR, which one should be best solution:

Have an standby and in case of any failure/disaster take full backup(pg_basebackup) of the standby db cluster and do PITR from that backup(recovery.conf -time).

continuously taking incremental backups+log backups.
use any 3rd party tool to take incremental as well as log backup of postgres db.

Please let me know which option should be better or any other option if anybody has then suggest that option as well.Also, let me know if any performance impact can happen with a database in case we have multiple standby for that database or any negative impact of keeping a standby.

-Thanks in advance!

There are many possibities!

PITR vs STANDBY

PITR & Standby are complementary solutions:

Standby is interesting to recover from:

physical problem: server crash, datacenter unavailabity
and offer quick switchover procedures

PITR will help you in case of data damage:

  If someone performs an operation that damages your data (DROP TABLE , UPDATE with a wrong WHERE clause ...). Then, you will be able to restore your database before this operation.

Implementation

PITR implementation

There are many tools that can help you to perform PITR, I have already used:

barman
wal-e

And there are also:

PgBackREst
...

This article lists and compares several LINUX backup tools.

My bast advices, regarding PITR implementation, are:

Use existing tools (and don't try to write a new one)
Test your backups
Monitor your backups (and see the monitoring point fails)!

standby implementation

As it's native, there's no need for additional tooling on the implementation-side.

But:

monitor it correctly
Write and test your switch procedures (and re-test them at regular intervals, to prevent that the procedure is not accurate when needed!)


Character vs Integer primary keys I'm designing a database with multiple lookup tables containing possible attributes of the main entities.  I'm thinking of using a 4 or 5-character key to identify these lookup values rather than an auto-incrementing integer so that when I store these attribute IDs on the main tables I'll see meaningful values rather than just random numbers.

What are the performance implications of using a character field as a primary key rather than an integer?

I'm using MySQL if that matters.

[Edit]
These lookup tables have new records added infrequently.  They are manually maintained, and the character-based keys are manually created as well.  Here's an example:

      CUISINES
 ID      Description
-----  --------------
CHNSE  Chinese
ITALN  Italian
MXICN  Mexican

It depends on your engine. Common wisdom is that reads are cheap, a few bytes here and there will not significantly impact the performance of a small to medium size database.

More importantly, it depends on the uses to which you will put the primary key. Integer serials have the advantage of being simple to use and implement. They also, depending on the specific implementation of the serialization method, have the advantage of being quickly derivable, as most databases just store the serial number in a fixed location, rather than deriving it with Select max(ID)+1 from foo on the fly.

The question becomes: how does a 5 character key present a "meaningful value" to you and to the application? How is this value created, and does it take more or less time than finding an incrementing serial number. While there is a trivial amount of space saved in some integers, the vast majority of systems will ignore this space savings.

There are no performance implications, save that the character scheme requires that there never be an automatic engine, as your "keys" are underivable. For your specific domain, don't bother with artificial keys, and just use Chinese, Japanese and Thai as key names. While you cannot guarantee uniqueness over any possible application, in your scope it is much more reasonable to use them instead of horrible and forced 5-character abbreviations. There are no significant performance impacts until you get to the millions of tuples.

Alternatively, if you're just tracking by country of origin, and not specific regional cuisines (Cantonese, Sichuan, Sicilian, Umbrian, Calabrian, Yucatecan, Oaxacan, etc.), you could always just use ISO 3166 codes.

  If I have 10,000 recipes doesn't the difference between a 5-character and 20-character key start to add up?

Space is cheap. When you're talking 10,000,000 recipes that you're doing OLAP operations on, then, maybe. With 10k recipes, you're looking at 150k of space.

But again, it depends. If you have many millions of records, and are doing joins on them, then it makes sense to denormalize the lookup for something this trivial (into a materialized view). For all practical purposes, the relative join efficiency on a modern machine between a 5 character key and variable length key is so similar to be identical. Happily, we live in a world of plentiful CPU and plentiful disk. The nasty ones are too many joins and query inefficiency, rather than character-by-character comparison. With that said, always test.

P&T things of this level are so database-dependent that generalizations are extremely difficult. Build two sample models of the database, populate them with the estimated numbers of records, then see which one is faster. In my experience, character length doesn't make a huge difference compared with good indexes, good memory configurations, and other critical performance tuning elements.


Avoiding multiple `or` expressions I have the following oracle SQL and its works and all but it's quite ugly with all of the ors. Is there a more concise way of doing this?

SELECT * FROM foobar WHERE
  (SUBJECT ='STAT' and TERM ='111') or
  (SUBJECT ='STAT' and TERM ='222') or
  (SUBJECT ='ENGLISH' and TERM ='555') or
  (SUBJECT ='COMM' and TERM ='444') or
  (SUBJECT ='COMM' and TERM ='333') or
  (SUBJECT ='STAT' and TERM ='666')
  ...

You might prefer something like this:

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

DBFiddle here


What is the most performant way to group by and join high cardinality columns in column based table? I currently have a SQL Server, 20 column fact table that could grow even wider into the hundreds. The current row count is about 9 million records. The indexing strategy is currently row-based. The query joins to every lookup table totaling about a dozen joins (most columns in the table are FKs). This is an analytical table that is generated nightly. Query filters are ad hoc so row-indexing most permutations is not feasible. I would like to explore removing these joins and using a columnstore index, since for the most part, only a handful of filter columns will be specified per query request.
Given I have three tables:
Table 1: SomeFactTable (~9 million records)
| Column         | Description          | Cardinality                   |
| --------       | --------------       | --------------                |
| Id             | int32 PK             | Unique                        |
| Timestamp      | datetime, non-null   | medium                        |
| LookupTableA_ID| int32, non-null, FK  | Medium-high (40k records)     |
| LookupTableB_ID| int32, non-null, FK  | Low (dozens records)          |
| NumberA        | int32, non-null      | Very high (8 million records) |

Table 2: LookupTableA (~40,000 records)
| Column      | Description            | Cardinality    |
| --------    | --------------         | -------------- |
| Id          | int32 PK               | Unique         |
| Description | varchar(255), non-null | high           |

Table 3: LookupTableB (~50 records)
| Column      | Description            | Cardinality    |
| --------    | --------------         | -------------- |
| Id          | int32 PK               | Unique         |
| Description | varchar(255), non-null | high           |

I have two questions.

How can I combine the benefits of a columnstore index with also grouping on the high cardinality NumberA column?

For example, given this query, it performs in about 100ms (which is great!). If I uncomment the group by NumberA, then the performance degrades to ~900ms (good, but I'd like to do better).
SELECT
  max(Id) Id
  ,[Timestamp]
FROM
  [dbo].[SomeFactTable]
group by
  [Timestamp]
  --,NumberA
ORDER BY
  [Timestamp] ASC
OFFSET 500 ROWS FETCH NEXT 20 ROWS ONLY

Most of the time I need to order by one or more of the lookup tables which requires a join. Sometimes these tables have many records (e.g. LookupTableA) and sometimes they only have a handful (e.g. LookupTableB). When there are many records, then the performance degrades. When there are only a handful, then the performance does not degrade. How can I maintain high performance when the table has many records?

As an example, this is a join with medium cardinality. It also has the high cardinality column from question 1. The query returns in about 2,900 ms.
SELECT
  max(SomeFactTable.Id) Id,
  [Timestamp],
  [Description]
FROM
  dbo.SomeFactTable
  INNER JOIN dbo.LookupTableA ON SomeFactTable.LookupTableA_ID = LookupTableA.Id
group by
  [Timestamp],
  [Description],
  NumberA
ORDER BY
  [Description] ASC,
  [DateAbsent] ASC
OFFSET 500 ROWS FETCH NEXT 20 ROWS ONLY

This is a join with low cardinality. It also has the high cardinality column from question 1. The query returns in about 1,000 ms (and returns even much faster if NumberA wasn't grouped).
SELECT
  max(SomeFactTable.Id) Id,
  [Timestamp],
  [Description]
FROM
  dbo.SomeFactTable
  INNER JOIN dbo.LookupTableB ON SomeFactTable.LookupTableB_ID = LookupTableB.Id
group by
  [Timestamp],
  [Description],
  NumberA
ORDER BY
  [Description] ASC,
  [DateAbsent] ASC
OFFSET 500 ROWS FETCH NEXT 20 ROWS ONLY

I only have a little bit of experience with columnstore indexing, but my experience has been awesomely positive so far. I'm assuming when you say the cardinality of the SomeFactTable.NumberA column  is ~8 million of the ~9 million records, that you mean they share the same value. If so, then I think a columnstore index would work great for you on it, because it compresses the data of the columns it encompasses, and ~8 million / ~9 million records would be about a 90% compression ratio.
Again I'm no expert but I'd recommend testing a columnstore index on just the (NumberA) column vs one on all the fields you might group on, i.e. (Timestamp, Description, NumberA).


List Top Daily Rentals Per Customer using a CTE I've been trying to learn CTEs and having a bit or trouble getting the hang of them.  I wrote a query against the Sakila Sample database that lists information about horror movie rentals for each day.

Here is the overblown (and redundant) SQL that I came up with:

SELECT CONCAT(CU.last_name, ', ', CU.first_name) AS customer,
        A.phone,
        F.title,
        date(R.rental_date) AS rental_date
 FROM sakila.rental R
     LEFT JOIN sakila.inventory I ON R.inventory_id = I.inventory_id
     LEFT JOIN sakila.film F ON I.film_id = F.film_id
     LEFT JOIN sakila.film_category FC on F.film_id = FC.film_id
     LEFT JOIN sakila.category C ON FC.category_id = C.category_id
     LEFT JOIN sakila.customer CU ON R.customer_id = CU.customer_id
     LEFT JOIN sakila.address A ON CU.address_id = A.address_id
 WHERE CU.customer_id in
       (SELECT CU.customer_id
        FROM rental R
        LEFT JOIN sakila.customer CU ON R.customer_id = CU.customer_id
        LEFT JOIN sakila.inventory I ON R.inventory_id = I.inventory_id
        LEFT JOIN sakila.film F ON I.film_id = F.film_id
        LEFT JOIN sakila.film_category FC on F.film_id = FC.film_id
        LEFT JOIN sakila.category C ON FC.category_id = C.category_id
        WHERE C.name = "Horror"
        GROUP BY CU.customer_id
        HAVING COUNT(CU.customer_id) >= 3)
 AND C.name = "Horror"
 ORDER BY customer, title, rental_date DESC;

And here are some of the results in my database client (Navicat for MySQL):

Is there a way to rewrite the query using a CTE?

It seems like just the type of query that a CTE would be perfect for, if only I could figure it out!

Thanks!

# horror film rental(answer)
select cus_dtl.name, cus_dtl.phone, hf_cus.title, hf_cus.rental_date, hf_cus.name
from (select c.customer_id, concat(c.first_name, ',', c.last_name) as name, a.phone
      from customer as c, address as a
      where c.address_id = a.address_id) as cus_dtl, (select r.customer_id, r.rental_date, hf_inve.title, hf_inve.name
                                                      from rental as r, (select i.inventory_id, hf_title.film_id, hf_title.title, hf_title.name
                                                                         from inventory as i, (select f.title, horror_FID.film_id, horror_FID.name
                                                                                from film as f, (select fc.film_id, horror.name
                                                                                from film_category as fc, (select category_id, name
                                                                                from category
                                                                                where name = 'horror') as horror
                                                                                where fc.category_id = horror.category_id) as horror_FID
                                                                                where f.film_id = horror_FID.film_id) as hf_title
                                                                         where i.film_id = hf_title.film_id) as hf_inve
                                                      where r.inventory_id = hf_inve.inventory_id) as hf_cus
where hf_cus.customer_id = cus_dtl.customer_id
order by cus_dtl.name, hf_cus.title, hf_cus.rental_date desc;

using sub queries..


User login error when trying to access secured SQL Server database We have a username that was recently renamed from one username to another (think getting married). The Active Directory admin renamed the user because "it has always worked in the past".

One vendor package we use uses the built-in MS SQL Server security. Each module has three groups:

xxView = View Only
xxUser = Add, Update rows (cannot delete)
xxAdmin = Delete rows

So we can add a person to one of these groups an they get the appropriate access. I don't have the actual error message in front of me anymore, but it said that they are not authorized to table CriticalVendorTable. It worked before the rename. The admin removed the person from each group and re-added them. Still no go. I even restarted the server and it still doesn't work. My best guess is that there is UUID (or unique id) somewhere that is causing problems.

The vendor's response is to delete the user and then re-add them. I have only had time to do some brief searching, but I found this page; AD User SID Mis-mapping. Would this be worth trying? Would it be better to just delete the user and recreate them?

Both options should work. The article you referrenced should deal with this exact scenario, but if the login is present for you to delete and re-add then go with that route. It is quicker and easier by far.