For absolute performance, is SUM faster or COUNT? This relates to counting the number of records that match a certain condition, e.g. invoice amount > $100.

I tend to prefer

COUNT(CASE WHEN invoice_amount > 100 THEN 1 END)

However, this is just as valid

SUM(CASE WHEN invoice_amount > 100 THEN 1 ELSE 0 END)

I would have thought COUNT is preferable for 2 reasons:

Conveys the intention, which is to COUNT
COUNT probably involves
a simple i += 1 operation somewhere, whereas SUM cannot count on
its expression to be a simple integer value.

Does anyone have specific facts about the difference on specific RDBMS?

You mostly answered the question yourself already. I have a few morsels to add:
In PostgreSQL (and other RDBMS that support the boolean type) you can use the boolean result of the test directly. Cast it to integer and SUM():
SUM((amount > 100)::int))

Or use it in a NULLIF() expression and COUNT():
COUNT(NULLIF(amount > 100, FALSE))

Or with a simple OR NULL:
COUNT(amount > 100 OR NULL)

Or various other expressions. Performance is almost identical. COUNT() is typically very slightly faster than SUM(). Unlike SUM() and like Paul already commented, COUNT() never returns NULL, which may be convenient. Related:

Query optimization or missing indexes?

Since Postgres 9.4 there's also the aggregate FILTER clause. See:

Return counts for multiple ranges in a single SELECT statement

It's faster than all of the above by around 5 - 10 %:
COUNT(*) FILTER (WHERE amount > 100)

If the query is as simple as your test case, with only a single count and nothing else, you can rewrite:
SELECT count(*) FROM tbl WHERE amount > 100;

... which is the true king of performance, even without index.
With an applicable index it can be faster by orders of magnitude, especially with index-only scans.
Benchmarks
Postgres 13
db<>fiddle here
Basically the same results as for Postgres 10 below.
(I added a test without the new parallelism.)
Postgres 10
I ran a new series of tests for Postgres 10, including the aggregate FILTER clause and demonstrating the role of an index for small and big counts.
Simple setup:
CREATE TABLE tbl (
   tbl_id int
 , amount int NOT NULL
);

INSERT INTO tbl
SELECT g, (random() * 150)::int
FROM   generate_series (1, 1000000) g;

-- only relevant for the last test
CREATE INDEX ON tbl (amount);

Actual times vary quite a bit due to background noise and specifics of the test bed. Showing typical best times from a bigger set of tests. These two cases should capture the essence:
Test 1 counting ~ 1 % of all rows
SELECT COUNT(NULLIF(amount > 148, FALSE))            FROM tbl; -- 140 ms
SELECT SUM((amount > 148)::int)                      FROM tbl; -- 136 ms
SELECT SUM(CASE WHEN amount > 148 THEN 1 ELSE 0 END) FROM tbl; -- 133 ms
SELECT COUNT(CASE WHEN amount > 148 THEN 1 END)      FROM tbl; -- 130 ms
SELECT COUNT((amount > 148) OR NULL)                 FROM tbl; -- 130 ms
SELECT COUNT(*) FILTER (WHERE amount > 148)          FROM tbl; -- 118 ms -- !

SELECT count(*) FROM tbl WHERE amount > 148; -- without index  --  75 ms -- !!
SELECT count(*) FROM tbl WHERE amount > 148; -- with index     --   1.4 ms -- !!!

db<>fiddle here
Test 2 counting ~ 33 % of all rows
SELECT COUNT(NULLIF(amount > 100, FALSE))            FROM tbl; -- 140 ms
SELECT SUM((amount > 100)::int)                      FROM tbl; -- 138 ms
SELECT SUM(CASE WHEN amount > 100 THEN 1 ELSE 0 END) FROM tbl; -- 139 ms
SELECT COUNT(CASE WHEN amount > 100 THEN 1 END)      FROM tbl; -- 138 ms
SELECT COUNT(amount > 100 OR NULL)                   FROM tbl; -- 137 ms
SELECT COUNT(*) FILTER (WHERE amount > 100)          FROM tbl; -- 132 ms -- !

SELECT count(*) FROM tbl WHERE amount > 100; -- without index  -- 102 ms -- !!
SELECT count(*) FROM tbl WHERE amount > 100; -- with index     --  55 ms -- !!!

db<>fiddle here
The last test in each set used an index-only scan, which is why it helped for counting one third of all rows. Plain index or bitmap index scans cannot compete with a sequential scan when involving roughly 5 % or more of all rows.
Old test for Postgres 9.1
To verify I ran a quick test with EXPLAIN ANALYZE on a real life table in PostgreSQL 9.1.6.
74208 of 184568 rows qualified with the condition kat_id > 50. All queries return the same result. I ran each like 10 times in turns to exclude caching effects and appended the best result as note:
SELECT SUM((kat_id > 50)::int)                      FROM log_kat; -- 438 ms
SELECT COUNT(NULLIF(kat_id > 50, FALSE))            FROM log_kat; -- 437 ms
SELECT COUNT(CASE WHEN kat_id > 50 THEN 1 END)      FROM log_kat; -- 437 ms
SELECT COUNT((kat_id > 50) OR NULL)                 FROM log_kat; -- 436 ms
SELECT SUM(CASE WHEN kat_id > 50 THEN 1 ELSE 0 END) FROM log_kat; -- 432 ms

Hardly any real difference in performance.


What exactly does PostgreSQL (or other databases) do internally when you "prepare" a query rather than just running it directly? When I face a huge number of repeated INSERTs in a loop, I tend to first create a "prepare skeleton query" prior to the loop, and in the loop, I simply "execute" this prepared query and send it all the values. I heard long ago, and can understand in some abstract sense, that this is more optimized than just having the loop with a normal parameterized query in it.
However, I don't understand what exactly PG is doing that makes it so much faster. If it even is so much faster. I frankly have never done any real benchmarks to compare the two different methods.
Also, shouldn't PG be "warmed up" to a query once you repeat it several (let alone many) times in very short succession, and perhaps do the same thing as I do manually, but internally, when I just use a normal parameterized query?
I find myself constantly second-guessing what the database is up to internally. I have no real grasp of how "smart" it is. I fear that I'm doing a lot of things which are meaningless because it's already taken care of internally by its smart code.
Maybe "preparing" and "executing" is an archaic practice which has no real benefit these days?
It sounds like PG is basically allocating resources to "prepare" for upcoming huge amounts of similar INSERT queries, but I don't understand what exactly it would do differently compared to just executing them one by one. I also don't understand what the point would be of a non-INSERT prepared query/statement.
PS: Just to clear up any confusion: I never use non-parameterized queries, whether they are prepared or not. A lot of people confuse "parameterized queries" and "prepared statements". Even I called the latter "prepared queries" in this question...

Actually, the difference between a parameterized statement and a prepared statement is not that big in PostgreSQL: the former is internally an unnamed prepared statement, and the main difference is that it is automatically deallocated when the next unnamed prepared statement comes along.
When you prepare a statement, PostgreSQL remembers that statement in your current session (nothing is shared between sessions).
For the first five executions, PostgreSQL will generate a custom query plan that takes the parameter values into account. After that, PostgreSQL will generate a generic plan that is independent of the query parameters, and if that generic plan is estimated to be no more expensive than the previous custom plans, it will be used from that point on.
For insert statement like you describe, PostgreSQL will always switch to the generic plan. The main advantage there is that the query doesn't have to be planned any more. With simple statements, planning time can exceed execution time, so the savings can be considerable.
PostgreSQL only caches query plans for prepared statements and statements in PL/pgSQL functions.
The other advantage of prepared (or parameterized) statements is that they avoid the danger of SQL injection.
If you have to insert a lot of rows, using COPY would be even faster.


How do I rollback a dropped table using log? I have dropped a table from a SQL Server 2008 database.

How do I unroll the transaction log to recover the table and latest contents?

The database recovery model is "Full".

Is there any way or third party applications for rollback the dropped table using log?

Third party software are :

ApexSQL Log. It has free trial as well.

Toad for SQL Server - It has Log Reader management which Rolls back transactions in the transaction log without the need to restore from a backup

Native to SQL Server are below methods :

you can restore your Database from a backup, and then RESTORE Transaction LOGS to a point in time with the STOPAT = '6/10/2013 12:30AM' argument.

Read this excellent post from Paul Randall - Using fn_dblog, fn_dump_dblog, and restoring with STOPBEFOREMARK to an LSN


SQL Server Deadlock on delete I have a query like this:

with xx as
              (
              SELECT [FilePath], ROW_NUMBER() over (partition by [FilePath]  order by [FilePath]) as RowNumber
              FROM [dbo].[foo] where (a = 'a1' and b = 'b1') or (a = 'a2' and b = 'b2')) delete xx where RowNumber>1

(a and b, together constitute composite non-clustered index.
Other non-clustered index is on a different single column)

Which is executing parallel from different instances, but the where clause will separate the data on which it runs without overlaps. Other than that, inserts are also running on it, which may or may not affect the result of this query.

Inserts are typical, like

insert into [dbo].[foo] values('a1', 'b1', 'c1', 'd1')

Now I've started receiving the following error message:

Transaction (Process ID 199) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

What can be the possible cause of deadlock here?
Is the order of taking the lock on the indexes while insert and while delete the reason?
Thanks in advance.

There is the possibility that you are getting lock escalations onto the table level, or page locks on the clustered index page. A deadlock graph will show that, try starting a SQL Server trace grabbing just the Deadlock graphs from the server and check those

You can also delete in batches which will improve concurrency and if the delete is handling large number of rows might run faster and allow you to backup and truncate the log and lower the changes of deadlocking.

DECLARE @ROWCOUNT int = 1
WHILE @ROWCOUNT > 0
 BEGIN delete top (10000) from xx where RowNumber >1
 set @rowcount = @@rowcount
END


Remove a SQL Server Patch only visible in View Installed Update List Kindly help me regarding our issue with a Clustered Environment's applied patch.

The situation is: We removed a SQL Patch: GDR 6248 for SQL Server 2012 (KB3194721) (64 bit). When we successfully removed the patch on the first node, we rebooted it. Now when we tried to remove it on the second node, we can't proceed to uninstall the patch as there are missing files.

We attempted to remove the patch again using some ready msi files on our repository. We successfully removed the patch. SQL Management Studio detects the correct service pack, which is SP3.

The issue now is on the OS where the patch we're trying to remove is still on the View Installed Updates List. Please advise how to remove it? What steps to take that is least harmful to the clustered setup?

OS Version: Windows Server Enterprise 2012
Database Version: SQL Server 2012 SP3

It sounds like an orphaned registry entry. The steps in this article outline how to back up the uninstall key and delete unwanted entries:

Click Start, then click on Run and type regedit in the Open field. Then press enter on your keyboard.
Navigate to the Registry key: HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\Uninstall. You do this by clicking on the plus sign next to each key in the path to expand the path below it. For example you will click on the plus next to HKEY_LOCAL_MACHINE, Software, Microsoft, Windows, CurrentVersion, and finally Uninstall.
Right click on the the Uninstall key and select the Export option. (If you are using Win98/95/ME, you should clik on the Registry menu and then select Export). This will open up a dialog box where you can select a directory and name for the file you will export to. Make the Save in: field set to your Desktop and in the File name: field enter uninstall_programs and press the Save button. This will create a backup of your Uninstall registry key on your desktop in case of a problem.
Now that we have a backup created, we will proceed with deleting the entries for the programs that we would no longer like to appear in your Add/Remove Programs list. As long as the Uninstall key is expanded you will see below it a listing of programs. Search for the program you would no longer like to appear in the Add/Remove Programs list and right click on the name of the program and then click on the Delete key. When it asks for confirmation, click on the Yes key.
Once the key is deleted, exit regedit, go back into your Add/Remove Programs option in the Control Panel and see if the program is now gone from the listing. If it is not listed, then you have deleted the entry from the Registry correctly. If on the other hand, the listing looks incorrect you can restore the backup you created by double-clicking on the uninstall_programs.reg icon found in your desktop. When it prompts you if you would like to merge the contents, you would reply Yes. Then you should repeat steps 1 through 5 to remove the desired program.


Innodb UPDATE performance We recently switched one of our tables to innodb and now we are experiencing very slow UPDATE execution times. An update which used to take 0.010-0.030 seconds can now take over 70 seconds. Some queries are dropped because they cannot acquire a lock within the default 50 seconds limit (I realize we can raise this limit).

The table in question has only one index, the primary key itself which is a mediumint value.
The table has about 1 million rows.
All UPDATE's in this context involve a single row. Usually 4-5 columns from that row are affected in every query.

Current my.cnf is pasted below. Do you see anything which could particularly cause a bad UPDATE performance for innodb?

[mysqld]
set-variable=local-infile=0

datadir=/db/mysql/data
socket=/var/lib/mysql/mysql.sock
#log = /var/log/mysqld.log
log-error = /var/log/mysqld.error.log
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

skip-locking
key_buffer = 1G
query_cache_size = 256M
thread_cache_size = 128
table_cache = 2048
max_connections = 400
query_cache_limit = 1024M
log_slow_queries = /var/log/mysql-slow.log
long_query_time = 1
skip-bdb
skip-locking
skip-name-resolve

innodb_buffer_pool_size=1G
innodb_additional_mem_pool_size=20M
innodb_flush_log_at_trx_commit=2
#innodb_log_file_size=250M
innodb_log_buffer_size=8M
innodb_lock_wait_timeout=50

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

UPDATE:
innodb_log_file_size: 5242880
have_innodb: YES
"WHERE" clause always looks only for one column, which is the primary key.

UPDATE - July 26, 2012:
We upgraded our database to mysql 5.5. Now innodb updates are pretty fast, less than 0.010 seconds in our specific case. And, variance is pretty low. My take from this is: innodb should be used with real caution on mysql 5.0.

PERSPECTIVE #1
When you update by Primary Key only in InnoDB, there is a rare but possible occasion when the clustered index (aka gen_clust_index) can get locked.
I once answered three posts from one individual on this subject

Trouble deciphering a deadlock in an innodb status log
Reasons for occasionally slow queries?
Will these two queries result in a deadlock if executed in sequence?

Please read through these carefully. The poster of these question found his own workaround based on the seeing InnoDB Clustered Index Locking behavior. Sadly, he did not post what the workaround was.
In addition, when you see the queries running slow, log into mysql and run SHOW ENGINE INNODB STATUS\G and starting looking for locks in the Clustered Index.
PERSPECTIVE #2
I see you commented out innodb_log_file_size. You have it at 5MB, the default. Since innodb_buffer_pool_size is set to 1G, innodb_log_file_size needs to be at 256M. Click here to go about setting innodb_log_file_size to 256M.
PERSPECTIVE #3
I see you are not using innodb_file_per_table. You may want to use it in order to have table updates done specifically for the one table with a million rows. Click here to see how to Clean Up InnoDB infrastructure to use innodb_file_per_table.


Dropping duplicate|redundant Unique Constraint from FILESTREAM table I have a table with a FILESTREAM column, and it has two unique constraints specified for the same FILESTREAM column, ie:

ALTER TABLE [dbo].[TableName]
    ADD CONSTRAINT [UQ__TableName__33C4988760FC61CA]
    UNIQUE NONCLUSTERED ([GUID_Column]);
GO

ALTER TABLE [dbo].[TableName]
    ADD CONSTRAINT [UQ__TableName__33C49887145C0A3F]
    UNIQUE NONCLUSTERED ([GUID_Column]);
GO

I'd like to drop one of the unique constraints, as they are duplicates.  However, when I try and drop one of the two duplicate constraints, I receive the following error.

  A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column.

Anyone know how to remove one of the two constraints?

I was able to recreate this problem.

CREATE DATABASE Archive
ON
PRIMARY ( NAME = Arch1,
    FILENAME = 'c:\data\archdat1.mdf'),
    FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = Arch3,
    FILENAME = 'c:\data\filestream1')
 LOG ON  ( NAME = Archlog1,
         FILENAME = 'c:\data\archlog1.ldf')
GO
CREATE TABLE Archive.dbo.Records
(
    [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,
    [Chart] VARBINARY(MAX) FILESTREAM NULL
)
GO

ALTER TABLE Archive.dbo.Records WITH NOCHECK
  ADD CONSTRAINT testfilestream UNIQUE (ID)
GO
ALTER TABLE Archive.dbo.Records
DROP CONSTRAINT testfilestream

Best I can tell this is a bug and (1) SQL Server has a hard rule relating to tables with FILESTREAM columns that says "if they try to drop ANY UNIQUE constraint on the ROWGUIDCOL column in a table with FILESTREAM, don't let them" and/or (2) There only ever is one UNIQUE constraint on a column and making another is like making an alias.  So when you try and drop one, you're really dropping both.

As to how to solve it. Unfortunately, you can't add a second ROWGUIDCOL type column to a table, so that prevents a fix that's internal to the existing table.  You'll probably have to create a new table with the same schema, (minus the extra unique constraint), copy the data from the current table to the new table, then drop the old table and rename the new one back to the old name.


SQL Server 2014 vulnerabilities reported by Nessus I have some vulnerabilities reported for my SQL Server 2014 instance:

  List of RC4 cipher suites supported by the remote server :

 High Strength Ciphers (>= 112-bit key)

   TLSv1
      RC4-MD5                      Kx=RSA         Au=RSA      Enc=RC4(128)             Mac=MD5
      RC4-SHA                      Kx=RSA         Au=RSA      Enc=RC4(128)             Mac=SHA1

  The fields above are :

 {OpenSSL ciphername}
  Kx={key exchange}
  Au={authentication}
  Enc={symmetric encryption method}
  Mac={message authentication code}
  {export flag}

  SSLv3 is enabled and the server supports at least one cipher.

  Nessus determined that the remote server supports SSLv3 with at least one CBC
      cipher suite, indicating that this server is vulnerable.

  It appears that TLSv1 or newer is supported on the server. However, the
  Fallback SCSV mechanism is not supported, allowing connections to be "rolled
  back" to SSLv3.

My OS is Windows Server 2012, and use Nessus to scan my Server. How can I fix the above vulnerabilities?

It looks like Nessus reports these vulnerabilities even when the RC4 is not enabled for any SQL Server endpoint. For SSL 3.0 SQL Server 2014 supports TLS 1.2 as of CU7, while previous versions support TLS 1.0.

I suppose that the Nessus report has to do with the possibility of algorithm downgrade from TLS to RC4. This page describes how to disable RC4 completely.

However, some of my servers are still reported by Nessus with this vulnerability, even if the RC4 cypher is disabled (default for Windows 2012 R2), so I suppose it might be a false positive on Nessus' end.


Need help to design ER diagram I need to design an ER diagram for a centralized healthcare management system for my college mini-project.

Firstly, I came up with the following ER diagram.

ER-diagram-1

While designing the tables in the database, I found the patient table to be redundant as every person is a patient in our case so, I came up with the following ER-diagram.

ER-diagram-2

Now, I am confused about which ER diagram shall I use for the project.
Can you please me with the ER diagram? Thanks in advance.

I would take the approach for diagram 1. When approaching people in a database, the patients are essentially objects. They don't log in, they only have data. A doctor (or any employee) would be able to log in as users, so would have fields in their table like a login_id, password, and such. A point of confusion may come up when you have a doctor that is also a patient, but then they would have an employee_id\user_id AND a separate patient_id.

As a side note, I would move the connection for medications to be attached to the treatment actions. That would associate a drug with a prescription event, which would be connected to a patient and doctor. Drug prescriptions are events in and of themselves, not necessarily a long-term association to just a patient.


Multi cores and MySQL Performance Importance of RAM is an established fact but far less material is available about the importance of cores and multithreading when it comes to the usage of CPU by MySQL. I am talking about the difference of running MySQL on 4cores vs 6cores vs 8cores and so on.

Do different storage engines use CPU differently?

When it comes to MySQL, there is no comparison among storage engines except that it falls into two basic categories:

Transactional (ACID-compliant)
Non-Transactional

MySQL features the use of several storage engines

InnoDB (default as of MySQL 5.5)
MyISAM (default prior to MySQL 5.5)
ARCHIVE
BLACKHOLE
CSV
MEMORY
MRG_MYISAM
FEDERATED
NDB a.k.a. MySQL Cluster

As for the storage engines listed, the only ones that have ACID-compliance are InnoDB and NDB. Why is this importatnt to mention? Two reasons:

Other storage engines are simply not benefited with the presence of more cores, other than basic disk I/O, CPU usage, and overall throughput.
The code for each non-transactional storage engine, which dictates basically 14 internal operations regardless of storage engine, was not designed to leverage the accessing of multiple cores.

InnoDB under MySQL 5.5, InnoDB Plugin), and Percona Server's XtraDB have options you can set in order to access multiple cores (Percona Server has been doing so longer). In fact, Percona injects about 30,000 lines of code specifically for performance enhancement of InnoDB with each new GA release of MySQL source code. We can be sure Oracle has included its own enhancements from their own think tank to run within InnoDB for multicore operation (since MySQL 5.1.38).

With the need to perform MVCC on data in conjunction with row/page locking, transaction performance can now be instrumented, measured, and configured.

If there is one thing I have learned about using multiple cores, it is that you must tune InnoDB effectively and not just rely on InnoDB out of the box.

UPDATE 2011-09-20 08:03 EDT

With regard to InnoDB benefiting from all cores, we need to keep things in persepctive. The cores must also tend to other matters (OS, Disk, Memory, Applications, Monitoring, etc.) in the Database Server. For those with modest budgets, many tend to have a Database Server also provide NFS, monitoring from Munin, app support for JBoss, PHP, and the list goes on. If you want MySQL, more specifically InnoDB, to use more cores, the Database Server must be dedicated exclusively to MySQL and the OS/Disk/Memory must tend to MySQL only. Given this perspective, InnoDB will engage more cores withtout a doubt.

As for InnoDB Plugin, it was mentioned simply to show earlier initiatives to have a better InnoDB on the part of MySQL (eh, Oracle. Sorry, still doesn't roll off the tongue yet). New variables to summon more core activity became evident from MySQL 5.1.38.

For example, innodb_read_io_threads and innodb_write_io_threads (both since MySQL 5.1.38) allocate the specified number of threads for reads and writes. Default is 4 and maximum is 64. The default and max settings being so different (4 - 64) shows that InnoDB is as multithreaded and core intensive as you configure it !!!

Addressing the needs of the MySQL community to access more cores with InnoDB was lead by Percona. Consequently, MySQL started to follow suit. I have to admit that Oracle (yuck) made the necessary improvements for more core activity.


Best way to keep the last N rows by a certain column Let's say I have a table of employees and I want to record their monthly salary inside a table and I only want the latest 12 rows to be there.

I know I can do something like this every time I want to insert a salary record:

INSERT INTO salary (employee_id, amount, timestamp) VALUES (id, amount, current timestamp);
DELETE FROM salary WHERE employee_id=id ORDER BY timestamp ASC OFFSET 12;

But is there a better approach?

Akina suggested:

  Update the most ancient record instead of insert/delete. The amount of records will be constant, and you'll store the most recent records.

That seems like it could be really inefficient? As I am developing an application, I need to check if there are 12 rows or not, if there isn't then insert, if there is, I need to update all the rows in the table and move the data on the bottom row and update the first row - that'll take a lot of queries. I need to use automation, I need a better approach to improve performance.

I tried to read from the official reference and search the google to find clear details about INSERT INTO ON DUPLICATE and REPLACE, but unfortunately I can't understand how they could help in my situation.

Can someone give me an example approach please?

Create a table (done 1 time)

CREATE TABLE salary
( employee_id INT NOT NULL,
  amount DECIMAL(10, 2) DEFAULT NULL,
  ts DATETIME DEFAULT '1000-01-01 00:00:00'
              ON UPDATE CURRENT_TIMESTAMP );

Insert 12 records for new employee (done 1 time when creating a new employee)

INSERT INTO salary (employee_id)
SELECT @new_employee_id
FROM ( SELECT 1 UNION SELECT 2 UNION .. UNION SELECT 12 ) numbers;

Add new data (done monthly)

UPDATE salary
SET amount = @new_amount
WHERE employee_id = @employee_id
ORDER BY ts ASC LIMIT 1;


index using btree_gin on smallint/bigint I try to use a btree_gin index on smallint column, but the index is never used for queries. According to documentation (http://www.postgresql.org/docs/9.5/static/btree-gin.html) it should work. If I change the column-type to int the index is used, if it is bigint it does not work either.

I would like to use btree_gin as the index is much smaller. In this example the table is 442MB, btree_bin index is 14MB, normal btree index is 224MB in size.

Steps to reproduce:

create table test (year smallint, somedata varchar);
insert into test (year, somedata) select (random()*50) + 1970 as year, 'something' as somedata from generate_series(1,10000000);
create extension btree_gin;
create index on test using gin (year);

The query (type smallint, index is not used):

explain select * from test where year = 2000;
                          QUERY PLAN
---------------------------------------------------------------
 Seq Scan on test  (cost=0.00..179055.00 rows=192333 width=12)
   Filter: (year = 2000)

Change the type to int:

alter table test alter column year type int;

Now the index is used:

explain select * from test where year = 2000;
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Bitmap Heap Scan on test  (cost=467.50..56278.04 rows=50000 width=14)
   Recheck Cond: (year = 2000)
   ->  Bitmap Index Scan on test_year_idx  (cost=0.00..455.00 rows=50000 width=0)
         Index Cond: (year = 2000)

This happens with Postgres 9.5.0 on Debian strech and Postgres 9.4.2 on Centos 6.4.

Could anybody please explain how to use btree_gin on smallint columns? Thank you

It looks like btree_gin is not as clever about reasoning about datatypes as the regular btree code is.  If you explicitly cast your query condition constant to be of the exact same type as the index, then it will work.

explain select * from test where year = 2000::smallint;
                                QUERY PLAN
----------------------------------------------------------------------------------
 Bitmap Heap Scan on test  (cost=467.50..56278.04 rows=50000 width=12)
   Recheck Cond: (year = '2000'::smallint)
   ->  Bitmap Index Scan on test_year_idx  (cost=0.00..455.00 rows=50000 width=0)
         Index Cond: (year = '2000'::smallint)


Mysql Master-Slave replication catchup and bin_logs I have set up MYSql Replication using 2x 128gig ram 24 core cloud servers. we have a massive live database that cannot experience down time due to the high volume of data input.

I have found while dealing with the master slave is if I restart the service the bin_log and position change. Thus the slave needs to be updated and even then will be missing the data for the period it did not have the new bin_log set.

Dealing with my database which is over 400 gigs big, and is required to be live 99% of the time I cannot afford to Lock tables, Dump, transfer and import.

The slave is also very impotent as all reads are done from it and the data is highly relevant to current user interaction and as such cannot experience missing data or down time.

Moreover I am using mysql 5.5

So my questions are as follows:

1) What else if anything can cause the bin_log on the master to change.
2) How can I get my slave to consolidate and catch up the missing data without redoing the whole slave.

Any help will be highly appreciated.

1) Any INSERT, UPDATE, ALTER etc type Query executed on the Master will be written to the Binary log, causing the MASTER_LOG_POS to increase

Furthermore, if the Master is receiving updates from another Master (in a Master to Master setup), AND if you have LOG_SLAVE_UPDATES enabled these will be written to the Binary Log as well.

Each time you restart the service, the Binary Log will be flushed, incrementing it by 1.

2) On the Slave, Run SHOW SLAVE STATUS, and make a note of the MASTER_LOG_FILE. Now check, does this file exist on the Master still? If it doesn't then you have a problem, and will need to rebuild the slave from a copy of the master (e.g. MYSQLDUMP)

Assuming it does exist you should be able to just issue a START SLAVE command, and it will start catching up (hopefully)

Next run SHOW SLAVE STATUS again and check the SLAVE_IO_RUNNING and SLAVE_SQL_RUNNING. Are they both YES then all is good. Look for the SECONDS_BEHIND_MASTER to give you an idea of how far behind it is.

If either of them is NO look at the Error (LAST_IO_ERROR or LAST_SQL_ERROR) to get an idea of what the problem is.


Performance Recommendations for Large Volume of Data Oracle Table:

Fields: 55
Roughly 10 are Used for Querying
Single Record Length: 512 bytes
Records: 3-5 million records per day
Period of Time: 13 months to be available, > 13 months to be purged daily or monthly.

Questions:

What is the best mechanism for us to maintain this large volume of data and be able to query with good performance ( partitions ? Monthly tables ? )
Tests on indexing the data have not yielded good results, any suggestions on creating one or more indexes ?
Date format for these records is YYYYMMDDHHMMSSCSS
example:    20190710093054579
Current coding s forcing a SUBSTR or Like to be used in the query
example: SELECT * FROM TABLE WHERE SUBSTR(SYSDATE,1,8)='20190710'

How can we optimize the performance of querying perhaps for a whole day with other fields in the criteria ?

Is it possible to index this properly ? Or should the date and time be separated into 2 fields ?

We have observed positive results by using between 20190710000000000 and 2019071999999999  but performance is still problematic.

It's important to know what sort of queries you are running.  Are they mostly based on the time of the record, or something other field?  That will inform what sort of approach you should take here.  Maybe your indexes should actually be multi-indexes for example.

What's your EXPLAIN statement say for your queries?  That's really one of the best ways to see where exactly things are slowing down and attack it.

Edit: Sorry, I just saw the SUBSTR query, I believe you can make indexes based on substrings of a column.  Have you done that?  That IS different from just having an index on the column itself vs. having a index on the substring(1,8) of said column.


I dropped MySQL's `performance_schema` database, how can I create it? While fixing a ibdata/log problem, I accidentally dropped my performance_schema database, I'd like to create a new one.

mysql> SHOW VARIABLES LIKE 'perf%';
+---------------------------------------------------+---------+
| Variable_name                                     | Value   |
+---------------------------------------------------+---------+
| performance_schema                                | ON      |
| performance_schema_events_waits_history_long_size | 10000   |
| performance_schema_events_waits_history_size      | 10      |
| performance_schema_max_cond_classes               | 80      |
| performance_schema_max_cond_instances             | 1000    |
| performance_schema_max_file_classes               | 50      |
| performance_schema_max_file_handles               | 32768   |
| performance_schema_max_file_instances             | 10000   |
| performance_schema_max_mutex_classes              | 200     |
| performance_schema_max_mutex_instances            | 1000000 |
| performance_schema_max_rwlock_classes             | 30      |
| performance_schema_max_rwlock_instances           | 1000000 |
| performance_schema_max_table_handles              | 100000  |
| performance_schema_max_table_instances            | 50000   |
| performance_schema_max_thread_classes             | 50      |
| performance_schema_max_thread_instances           | 1000    |
+---------------------------------------------------+---------+
16 rows in set (0.06 sec)

Those variables appear to be fine to me.

The following question asks the same thing, however the user concludes that they were able to create it by following documentation that I could not find such instructions in.

mysql: deleted performance_schema, is it a problem?

Any thoughts?

Tables in the performance_schema database are a collection of views and temporary tables that do not store data permanently. The mysql_upgrade command will restore the performance_schema database

From the shell

mysql_upgrade --user=root --password=password


How to hide/disable tables without dropping them to check redundancy? I have to maintain and extend an old legacy system which contains webservice methods and database tables that are no longer used. Since I’m not entirely sure that the tables are really redundant, I’m afraid to drop them.

Is there any other way to achieve the same effect (tables cannot be used any more) without dropping them? My idea was to transfer them to a different schema (e.g., Deleted) from the current default, dbo.

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'Deleted')
BEGIN
   EXEC('CREATE SCHEMA Deleted')
END

ALTER SCHEMA Deleted TRANSFER dbo.TableName;

Is there any other option or are there any drawbacks to the schema approach?

A couple of other options are to just rename the tables, or if they have clustered indexes, you can disable the clustered index.


From the DMVs, can you tell if a connection used ApplicationIntent=ReadOnly? I have an Always On Availability Group set up, and I want to make sure my users are using ApplicationIntent=ReadOnly in their connection strings.

From the SQL Server via DMVs (or Extended Events or whatever), can I tell if a user connected with ApplicationIntent=ReadOnly in their connection string?

Please don't answer with how to PREVENT connections - that's not what this question is about. I can't simply go stopping connections, because we have existing applications that are connecting without the right string, and I need to know which ones they are so I can work with the developers and users to get it fixed gradually over time.

Assume that users have multiple applications. For example, Bob connects with SQL Server Management Studio, and with Excel. He connects with SSMS when he needs to do updates, and Excel when he needs to do reads. I need to make sure he's using ApplicationIntent=ReadOnly when he connects with Excel. (That's not the exact scenario, but it's close enough to illustrate.)

Picking up on the sqlserver.read_only_route_complete Extended Event mentioned by Kin and Remus, it's a nice Debug event, but it doesn't carry a great deal of information with it - just route_port (eg 1433) and route_server_name (eg sqlserver-0.contoso.com) by default.  This would also only help determine when a read-only intent connection was successful.  There is a read_only_route_fail event but I couldn't get it to fire, maybe if there was a problem with the routing URL, it didn't seem to fire when the secondary instance was unavailable / shutdown as far as I could tell.

I have however had some success joining that up with the sqlserver.login event and causality tracking enabled, along with some actions (like sqlserver.username ) to make it useful.

Steps to Reproduce

Create an Extended Events session to track relevant events, plus useful actions and track causality:

CREATE EVENT SESSION [xe_watchLoginIntent] ON SERVER
ADD EVENT sqlserver.login
    ( ACTION ( sqlserver.username ) ),
ADD EVENT sqlserver.read_only_route_complete
    ( ACTION (
        sqlserver.client_app_name,
        sqlserver.client_connection_id,
        sqlserver.client_hostname,
        sqlserver.client_pid,
        sqlserver.context_info,
        sqlserver.database_id,
        sqlserver.database_name,
        sqlserver.username
        ) ),
ADD EVENT sqlserver.read_only_route_fail
    ( ACTION (
        sqlserver.client_app_name,
        sqlserver.client_connection_id,
        sqlserver.client_hostname,
        sqlserver.client_pid,
        sqlserver.context_info,
        sqlserver.database_id,
        sqlserver.database_name,
        sqlserver.username
        ) )
ADD TARGET package0.event_file( SET filename = N'xe_watchLoginIntent' )
WITH (
    MAX_MEMORY = 4096 KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 30 SECONDS,
    MAX_EVENT_SIZE = 0 KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = ON,   --<-- relate events
    STARTUP_STATE = ON      --<-- ensure sessions starts after failover
)

Run the XE session (consider sampling as this is a Debug event), and collect some logins:

Note here sqlserver-0 is my readable secondary and sqlserver-1 the primary.  Here I'm using the -K switch of sqlcmd to simulate read-only application intent logins and some SQL logins.  The readonly event fires on a successful read-only intent login.

On pausing or stopping the session I can query it and attempt to link up the two events, eg:

DROP TABLE IF EXISTS #tmp

SELECT IDENTITY( INT, 1, 1 ) rowId, file_offset, CAST( event_data AS XML ) AS event_data
INTO #tmp
FROM sys.fn_xe_file_target_read_file( 'xe_watchLoginIntent*.xel', NULL, NULL, NULL )

ALTER TABLE #tmp ADD PRIMARY KEY ( rowId );
CREATE PRIMARY XML INDEX _pxmlidx_tmp ON #tmp ( event_data );

-- Pair up the login and read_only_route_complete events via xxx
DROP TABLE IF EXISTS #users

SELECT
    rowId,
    event_data.value('(event/@timestamp)[1]', 'DATETIME2' ) AS [timestamp],
    event_data.value('(event/action[@name="username"]/value/text())[1]', 'VARCHAR(100)' ) AS username,
    event_data.value('(event/action[@name="attach_activity_id_xfer"]/value/text())[1]', 'VARCHAR(100)' ) AS attach_activity_id_xfer,
    event_data.value('(event/action[@name="attach_activity_id"]/value/text())[1]', 'VARCHAR(100)' ) AS attach_activity_id
INTO #users
FROM #tmp l
WHERE l.event_data.exist('event[@name="login"]') = 1
  AND l.event_data.exist('(event/action[@name="username"]/value/text())[. = "SqlUserShouldBeReadOnly"]') = 1

DROP TABLE IF EXISTS #readonly

SELECT *,
    event_data.value('(event/@timestamp)[1]', 'DATETIME2' ) AS [timestamp],
    event_data.value('(event/data[@name="route_port"]/value/text())[1]', 'INT' ) AS route_port,
    event_data.value('(event/data[@name="route_server_name"]/value/text())[1]', 'VARCHAR(100)' ) AS route_server_name,
    event_data.value('(event/action[@name="username"]/value/text())[1]', 'VARCHAR(100)' ) AS username,
    event_data.value('(event/action[@name="client_app_name"]/value/text())[1]', 'VARCHAR(100)' ) AS client_app_name,
    event_data.value('(event/action[@name="attach_activity_id_xfer"]/value/text())[1]', 'VARCHAR(100)' ) AS attach_activity_id_xfer,
    event_data.value('(event/action[@name="attach_activity_id"]/value/text())[1]', 'VARCHAR(100)' ) AS attach_activity_id
INTO #readonly
FROM #tmp
WHERE event_data.exist('event[@name="read_only_route_complete"]') = 1

SELECT *
FROM #users u
    LEFT JOIN #readonly r ON u.attach_activity_id_xfer = r.attach_activity_id_xfer

SELECT u.username, COUNT(*) AS logins, COUNT( DISTINCT r.rowId ) AS records
FROM #users u
    LEFT JOIN #readonly r ON u.attach_activity_id_xfer = r.attach_activity_id_xfer
GROUP BY u.username

The query should show the logins with and without application read-only intent:

read_only_route_complete is a Debug event so use sparingly.  Consider sampling for example.
the two events together with track causality offer the potential to fulfil  your requirement - further testing necessary on this simple rig
I did notice if the database name was not specified in the connection, things didn't seem to work
I tried to get pair_matching target to work but ran out of time.  There is some potential for development here, something like:

ALTER EVENT SESSION [xe_watchLoginIntent] ON SERVER
ADD TARGET package0.pair_matching (
    SET begin_event = N'sqlserver.login',
        begin_matching_actions = N'sqlserver.username',
        end_event = N'sqlserver.read_only_route_complete',
        end_matching_actions = N'sqlserver.username'
    )


Why repetition in join queries Very dumb question, but bear with me, I am learning and am interested in the principles behind.

So: I have a users table and a pages table. In this case, 1 user and 4 pages. If I join the two tables, I get four times the user data, along with the pages data. The result is little legible, when I see a result stream like this I think wtf?

I know 'databases are just made this way'. I'd like to learn some context and meaning.

I think that you are confusing the presentation layer with the database layer.

In the presentation layer, repetitive information would be removed or reduced, perhaps a group header indicating the user and then a details section indicating the pages associated with that user.  This is the way that the information would best be presented to a person, in any case.

However, the dataset that underlies that presentation would look as you describe with the user information repeated for each page.  You get the same repetition of data if you reverse the joins.

SELECT P.page, U.user FROM Pages P INNER JOIN Users U ON U.user_id = P.user_id.

is equivalent to

SELECT P.page, U.user FROM Users U INNER JOIN Pages P ON P.user_id = U.user_id

So yes, it is a little illegible but it's meant to be fast and accurate.  Formatting the data relies on a deeper understanding of what the data actually means and desired presentation.


Data Recovery in Mysql Let the data have been backed up at time interval of 2 hr and taking backup at time "A" and after 1 hr Database crashes so how can I recover in MySql from Logs.

First You have to restore your backup file which was taken at time A , then
you have to read all MySQL binary logs using mysql utility mysqlbinlog.

For example, if your log files were numbered 1 to 49 before the backup and logs 50 to 52 were written after the backup,you will need to process logs 50 to 52 after restoring the backup. If your binary logs are named with a prefix of bin, the log processing command looks like this:

shell> mysqlbinlog bin.000050 bin.000051 bin.000052 | mysql

All the binary log files that you want to process should be handled in a single mysqlbinlog command. There may be inter-file dependencies that will not be satisfied if you process them separately.

If a given binary log file was in the middle of being written during backup, you must extract from it only the part that was written after the backup, plus all log files written after that. Also, if you accidentally drop a table (or database, or some other act that causes data corruption), then you want to restore your backup and the incremental activity in binary logs.

If you do not want to re-execute the dropped table, you can run everything up to the bad command by capturing the binary log file only up to that point. To handle partial-file extraction, mysqlbinlog supports options that enable you to specify the time or log position at which to begin extracting log contents:

The --start-position option can be used to specify extraction beginning at a given log position.

After restoring a backup file, use the position numbers to process the binary log file. For example, you would use commands something like these:

mysqlbinlog --start-position=23456 binlog.000004 | mysql -uroot -p


SQL - Select Rows in a Certain Order I'm new here - if I missed anything in the question please let me know.

I'm trying to build a people directory like LinkedIn has:

http://www.linkedin.com/directory/people-a

I don't want to fetch all the rows with name field starting with A and build the link list like LinkedIn. Is there any way in MySQL so I can only fetch rows in this sequence:

1st, 100th,101st,200th,201st,300th,301st,400th,401st, Last

That means I am trying to get two consecutive rows after a certain gap including the first and last item. The ids are not in nice uniformly increasing order.

Say my initial query SELECT * FROM businesses where name like 'a%' order by name returns ids like this:

1,3,5,6,8, 9,12,33,45,66,77,88,100,103,120,133,155,166,177,178,198

Above is if I want to get all the rows. But instead I'm trying to get only the items after a certain distance. For example if I want to pick after every 5 items:

1,(skip 4),9,12,(skip 4), 88,100,(skip 4),166,177,(skip 1),198

So skip 4 items and take next two. Is this possible without fetching all the rows?

I'm not sure if the above is the best way to achieve what I'm looking for. If you have better solution please let me know.

SELECT * FROM businesses where name like 'a%' order by name this query returns about 50K rows. But I don't need all of them. I'm trying to build people directory that is nested several levels. So, on the first page I'll show links like this:

Page1: http://example.com/people/a
Links:
   Adam - Alan => http://example.com/people/a/1   //here I skipped say 1000 people after Adam and before Alan
   Albert - Amy => http://example.com/people/a/2   // Here I skipped another 1000 people
   ...

Page2: http://example.com/people/a/1
    Here I show all those 1000 people profile links that I skipped in page1.

This is only two level. If you visit the above linkedin link, you'll see 3 or 4 levels of nesting.

This is pretty clumsy but will perhaps give you some thoughts. The idea is to number the rows and pick from there using modulo:

select x from (
    select @n := @n + 1 as n, x
    from t, ( select @n := 0 ) as dummy
) as u
where mod(n,6) between 0 and 1 or @n = n

Edit: As observed by @hassansin @n will have the value of the last n. Therefore the union of the last n can be replaced with the predicate @n = n


Longer time to bring SQL online after node failover We had a strange issue post our regular OS patching for SQL servers.
Per best practices, we apply patch on Passive and do node failovers to make current passive, active and vice versa to complete patching.
Generally node failover is seamless and completes under a min. However recently we had issue where it took 4 minutes to bring SQL online after the node failover:
I am checking logs and events but could not find the reason: below is the findings so far:
Note: The SQL server is running on VM

No increased in SQL server activity
Databases are part of DB mirroring
No increase in user connections or user queries running longer in that duration
VLF under 500 for all databases
NO CPU/Memory pressure and LPIM is enabled.
The process which was seemed to killed time out long running during failover was EXEC sp_server_diagnostics 20 running for past 86745234 secs

Please assist what else should i be checking to find the root cause?
Edit- I tried analyzing the cluster log and can see sql offline was initiated but i am not sure where it spend atleast 4 mins internally to actually shut down sql and bring it back. after 4 mins sql error log had all enteries for databases being brought up approx 10 secs. So it looks DB might not hve any involvement here for slowing down process.
Edit- some VLF info when checked currently

Most likely your problem is caused by the databases going through recovery to redo or undo transactions that haven't been hardened to data files.
Avoid recovery all together
Before doing planned server reboots or failovers of an FCI, particularly one with a lot of memory, I like to run a CHECKPOINT on every database. This minimizes the time spend on a clean shutdown of all databases, and (when databases aren't shut down cleanly) minimizes crash recovery time on restart.
I use sp_ineachdb from the First Responder Kit to do this:
EXEC DBA.dbo.sp_ineachdb 'CHECKPOINT;`;

If you hate free code that makes your life easier, you could do something with dynamic SQL:
DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'CHECKPOINT ' + QUOTENAME(name) + '; '
FROM sys.databases;

EXEC sys.sp_executesql @stmt = @sql;

But make recovery faster by doing less work
And of course, as Erik, Darling mentioned in the comments, make sure your VLFs are in order and properly sized. Scanning those VLFs during crash recovery is what can cause you all the pain you're seeing. For planned maintenance you can CHECKPOINT to minimize or eliminate crash recovery. But if you have... uhhh.... a crash and failover unexpectedly, that crash recovery is still going to happen, and there's not much you can do about it.
Be less direct
I've also had tons of luck with indirect checkpoints. We've rolled this out across our entire environment to much success.
And lobby the powers that be for an upgrade
SQL Server 2019 includes a feature called Accelerated Database Recovery, which can speed up recovery process, particularly when there are long-running, large transactions. ADR is not just for recovery after a crash, but also helps in other scenarios where the transaction log needs to be recovered–including Availability Group secondary redo and Failover Cluster Instance failovers.


Age range on age range in WHERE to heavy - POSTGRES I have 2 columns in the database for events.

agelow (int)
agehigh (int)

How it's structured:

agehigh is being used for "and under" which is represented by 0 in the column
the high age is placed in the agelow column
The "and over" is represented by 99 and is not a problem since it has the agehigh and the agelow in the right columns.
all ages are represented with 99 in both columns.

I am trying to write a WHERE that can pull all event ages if no ages are passed in but also need to be able to pull events just for one age passed in or for an age range passed in. The age range is making this a big headache.

Example

If I pass in agelow 5 and agehigh 15, I would need to grab all events that are

available for anyone from 5 up to 15 which includes the "5 and under", "5 and up", "15 and under" and all ages.

Here is the WHERE part:

WHERE
((events.age_low >= agelow AND events.age_high <= agehigh) OR
 (events.age_low <= agelow AND events.age_high >= agehigh) OR
 (events.age_low <= agelow /* LOW (99 for all) */ AND events.age_high >= agelow /* LOW (99 for all) */) OR
 (events.age_low <= agelow /* LOW (99 for all) */ AND events.age_high = 99 /* DEFAULT 99 */) OR
 (events.age_low >= agelow /* LOW (99 for all) */ AND events.age_high = 0 /* DEFAULT 0 */) OR
 (events.age_high >= agehigh /* HIGH (0 for all) */ AND events.age_low <= agehigh /* HIGH (0 for all) */) OR
 (events.age_low >= agehigh /* HIGH (0 for all) */ AND events.age_high = 0 /* DEFAULT 0 */) OR
 (events.age_low <= agehigh /* HIGH (0 for all) */ AND events.age_high = 99 /* DEFAULT 99 */) OR
 (events.age_low = 99 AND events.age_high = 99));

Here is a small selection of the age columns:

CREATE TABLE events(age_low,age_high)
AS VALUES
  (  99 ,  99 ),
  (   3 ,  12 ),
  (   1 ,   6 ),
  (   5 ,   0 ),
  (  99 ,  99 ),
  (  99 ,  99 ),
  (  99 ,  99 ),
  (   5 ,  11 ),
  (   5 ,   0 ),   -- this is 5 and below
  (  16 ,  99 ),   -- this is 16 and up
  (   1 ,   4 ),
  (  13 ,  18 ),
  (  99 ,  99 ),   -- this is all ages
  (   5 ,   0 ),
  (  99 ,  99 );

One approach to improve readability would be to use a CTE to add a couple of calculated columns..  However, this approach could also be slow, depending on the size of the table..  But it would look something like this

WITH true_ages AS (
  SELECT
    <cols>,
    CASE WHEN age_high < age_low THEN age_high -- handle case of 'x and below'
         WHEN age_low = 99 and age_high = 99 THEN 0 -- handle case of 'all ages'
         ELSE age_low END as true_low,
    CASE WHEN age_low < age_high and age_high = 99 THEN 999 -- handle case of 'x and above'
         WHEN age_low = 99 and age_high = 99 THEN 999 -- handle case of 'all ages'
         ELSE age_high END as true_high
  FROM <table>
) SELECT
  <cols>
FROM true_ages
WHERE <target_age> BETWEEN true_low and true_high;

You could potentially add some WHERE clause in the CTE to reduce the result set initially, but that might add unnecessary complexity.


postgresql: permission denied for sequence _i_seq I have a schema of a table in PostgreSQL that looks like:

CREATE TABLE IF NOT EXISTS  data.pmacct (

    i                       BIGSERIAL PRIMARY KEY,

And there are users:

A: superuser privileges
B: read/write privileges
C: read privileges

And I have made the following modifications in regards to the privileges (as user A)

CREATE SCHEMA data ;
ALTER DATABASE ip_spotlight SET search_path TO data ;
REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA data FROM PUBLIC ;
ALTER DEFAULT PRIVILEGES IN SCHEMA data GRANT SELECT ON TABLES TO C ;
ALTER DEFAULT PRIVILEGES IN SCHEMA data GRANT ALL ON TABLES TO B ;

as user postgres

GRANT USAGE ON SCHEMA data TO netops ;
GRANT USAGE ON SCHEMA data TO netopsapp ;
GRANT SELECT ON ALL TABLES IN SCHEMA data TO C ;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA data TO C ;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA data TO C ;
GRANT ALL ON ALL TABLES IN SCHEMA data TO B ;
GRANT ALL ON ALL SEQUENCES IN SCHEMA data TO B ;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA data TO B ;

But I am getting the following error message as user B

  ERROR:  permission denied for sequence pmacct_i_seq

Could you please enlighten me what I have done wrong and user B is not able to do an INSERT statement into the data.pmacct table ?

Try to make changes  on all tables
Answer from Alex Soto

Since you're changing the ownership for all tables, you likely want views and sequences too.  Here's what I did:

Tables:

for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" YOUR_DB` ; do  psql -c "alter table \"$tbl\" owner to NEW_OWNER" YOUR_DB ; done

Sequences:

for tbl in `psql -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = 'public';" YOUR_DB` ; do  psql -c "alter table \"$tbl\" owner to NEW_OWNER" YOUR_DB ; done

Views:

for tbl in `psql -qAt -c "select table_name from information_schema.views where table_schema = 'public';" YOUR_DB` ; do  psql -c "alter table \"$tbl\" owner to NEW_OWNER" YOUR_DB ; done

You could probably [DRY][1] that up a bit since the alter statements are identical for all three.


Transforming table to third normal form I have a table like this: L(A, B, C, D, E) and functional dependencies are:

AB -> CDE
C -> D
D -> B
D -> E

I need to transform this table into 3NF. I think it's not even in 2NF. I found 3 candidate keys:

first one is obviously AB
from D->B we can change AB->CDE to AD->BCE. So another candidate key is AD
using similar logic we get another key AC

(I am not sure if I did this correct).

From D -> E (I think there are more dependencies like that) I assume that this table is not in 2NF. What is the correct way to split this table up to get 3NF?

I would take following approach.

From left side of the relations we see 3 primary keys:

Key AB with AB->CDE relation is represented in LAB table.
Key C with C->D relation is represented in LC table.
Key D with D->E and D->B relations are represented in LD table.

Does it make sense?

CREATE TABLE LD (
  D INT,
  B INT, /* B can be omitted here, and derived from AB */
  E INT,
  CONSTRAINT LD_PKEY PRIMARY KEY ( D )
);

CREATE TABLE LC (
  C INT,
  D INT,
  CONSTRAINT LC_PKEY PRIMARY KEY ( C ),
  CONSTRAINT LC_D_FKEY FOREIGN KEY ( D ) REFERENCES LD

);

CREATE TABLE LAB (
  /* Attributes D and E are dependent on C, so we don't store them here */
  A INT,
  B INT,
  C INT,
  CONSTRAINT LAB_PKEY PRIMARY KEY ( A, B ),
  CONSTRAINT LAB_C_FKEY FOREIGN KEY ( C ) REFERENCES LC
);


Get count based on 15 minutes of interval I have a table containing Call records:

tbl_calls

cl_Id
cl_StartDate
cl_endDate

I am passing two parameters @StartDate and @EndDate to my stored procedure.

My requirement is to get the count of Call Records between every 15 minutes of duration.

For example, if:

@StartDate = '2015-11-16 00:00:00.000',
@EndDate = '2015-11-16 23:59:00.000'

Output should be:

Date                        Count
2015-11-16 00:00:00.000      10(Count of startDate between '2015-11-16 00:00:00.000' AND '2015-11-16 00:15:00.000')
2015-11-16 00:15:00.000       7(Count of startDate between '2015-11-16 00:15:00.000' AND '2015-11-16 00:30:00.000')
2015-11-16 00:30:00.000      50(Count of startDate between '2015-11-16 00:30:00.000' AND '2015-11-16 00:45:00.000')

upto @EndDate

I've tried the following query, however it doesn't work properly.  Is there a better way to do this?

DECLARE @StartDate DATETIME = DATEADD(DAY,-1,GETUTCDATE()),
        @EndDate DATETIME = GETUTCDATE()

SELECT New
FROM
    (SELECT
         (CASE
             WHEN cl_StartTime BETWEEN @StartDate AND
                         DATEADD(MINUTE, 15, @StartDate)
                THEN 1
                ELSE 0
          END) AS New
     FROM
         tbl_Calls WITH (NOLOCK)
     WHERE
         cl_StartTime BETWEEN @StartDate AND @EndDate) AS Inners
GROUP BY
    New

Let me know if you need further details.

This is a classic example of how a "Numbers table" can really help get the results you need.

Essentially, you create a table containing the 15 minute increments you desire, then join your table to obtain an aggregate number of calls for each 15 minute increment.

In example, I'm using temporary tables for both tables.  You'd likely want to make the #Intervals table permanent.

Create the testbed, and populate some sample data:

USE tempdb;

IF (OBJECT_ID('tempdb..#Calls') IS NOT NULL)
DROP TABLE #Calls;

CREATE TABLE #Calls
(
    CallID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , CallStart DATETIME NOT NULL
    , CallEnd DATETIME NOT NULL
);

;WITH cte AS
(
    SELECT rn = ROW_NUMBER() OVER (ORDER BY o.object_id, o1.object_id)
        , rn1 = ROW_NUMBER() OVER (PARTITION BY o.object_id
                                     ORDER BY o1.object_id)
    FROM sys.objects o, sys.objects o1
)
INSERT INTO #Calls (CallStart, CallEnd)
SELECT DATEADD(MINUTE, c.rn, DATEADD(DAY, -1, GETDATE()))
    , DATEADD(MINUTE, c.rn + c.rn1, DATEADD(DAY, -1, GETDATE()))
FROM cte c;

IF (OBJECT_ID('tempdb..#Intervals') IS NOT NULL)
DROP TABLE #Intervals;

CREATE TABLE #Intervals
(
    DateStart DATETIME NOT NULL
    , DateEnd DATETIME NOT NULL
);

;WITH cte AS
(
    SELECT TOP(35040) /* approx. number of 15 minute intervals in a year */
        rn = ROW_NUMBER() OVER (ORDER BY o.object_id, o1.object_id) * 15
    FROM sys.objects o
        , sys.objects o1
        , sys.objects o2
)
INSERT INTO #Intervals (DateStart, DateEnd)
SELECT DATEADD(MINUTE, c.rn, '2015-10-01T00:00:00')
    , DATEADD(MINUTE, c.rn + 15, '2015-10-01T00:00:00')
FROM cte c;

Show the rows in both tables:

SELECT *
FROM #Intervals i
ORDER BY i.DateStart;

SELECT *
FROM #Calls c
ORDER BY c.CallStart;

Join both tables to get the aggregate count of Calls between the 15 minute date ranges:

SELECT i.DateStart
    , i.DateEnd
    , TotalCalls = COUNT(1)
FROM #Calls c
    INNER JOIN #Intervals i ON c.CallStart >= i.DateStart
        AND c.CallStart < i.DateEnd
GROUP BY i.DateStart
    , i.DateEnd
ORDER BY i.DateStart;

On my test platform, I get the following results from the three select statements:

Looking at your comment on @Thofle's answer, it looks like you want to see all time intervals, even if there were no calls during the given interval.  To accomplish that, you can simply modify the select query to use a LEFT JOIN, and COUNT(...) the number of Calls rows, like:

SELECT i.DateStart
    , i.DateEnd
    , TotalCalls = COUNT(c.CallID)
FROM #Intervals i
    LEFT JOIN #Calls c
        ON i.DateStart <= c.CallStart
            AND i.DateEnd > c.CallStart
GROUP BY i.DateStart
    , i.DateEnd
ORDER BY i.DateStart;


Query to return fields of distinct values per key In the process of building a data warehouse we are creating some aggregation tables for a reporting engine. We would like to build a single aggregation table from the fact table that can exist as list of all possible values of certain fields that are present in the fact table.

Assume that all of the source data is in a single flat Fact table. Imagine something like this:

|Org   |Gender| Age|State|
--------------------------
|     1|     M|   1|   FL|
|     1|     F|   2|   VA|
|     1|     M|   3|   CA|
|     1|     M|   2|   NJ|
|     2|     M|   2|   VA|
|     2|     M|   3|   VA|
|     2|     M|   5|   VA|
(Times 400+ million, but for only about 100 Orgs...)

I would like to create an aggregation table that has the following output structure:

|Org   |Gender| Age|State|
--------------------------
|     1|     M|   1|   FL|
|     1|     F|   2|   VA|
|     1|  NULL|   3|   CA|
|     1|  NULL|NULL|   NJ|
|     2|     M|   2|   VA|
|     2|  NULL|   3| NULL|
|     2|  NULL|   5| NULL|

So that for each Org, the non-NULL DISTINCT list in each column represents the list of distinct values in the Fact table for each column. I can't use a DISTINCT operator because that will look for the list of distinct combinations of fields, not where each field is distinct and the number of rows for each Org is at most the number of distinct values for that Org.

Added comments:
This is for a Data Warehouse, so the source data started off in the transactional relational database that is the companies working database. It's then denormalized into a Fact table and set of Dimension tables that support the possible values of other data. I don't have access to the original relational DB for this project.

Purpose:
But we are now putting an ad-hoc reporting interface on top of the data warehouse and in order to efficiently query the Fact and Dimension tables we have to build some aggregation tables so that the selection fields of the reporting interface don't have to query the hundreds of millions of rows in the fact+dimension tables to find the possible values for each org to present in select dropdowns after the Org is chosen.

I've done a lot of googling and haven't found much, but I don't even know what you would call this kind of structure. "Columnar distinct?"

The database I'm using is SQL Server 2012, if it matters.

Without knowing anything about the source data, perhaps this would do what you want?

USE Test;
GO
CREATE TABLE GENDER
(
    ORG INT NOT NULL
    , GENDER VARCHAR(1) NOT NULL
);

CREATE TABLE AGE
(
    ORG INT NOT NULL
    , AGE TINYINT
);

CREATE TABLE STATES
(
    ORG INT NOT NULL
    , STATENAME VARCHAR(255)
);

INSERT INTO Gender (ORG, GENDER) VALUES (1, 'M');
INSERT INTO Gender (ORG, GENDER) VALUES (1, 'F');
INSERT INTO Gender (ORG, GENDER) VALUES (2, 'M');
INSERT INTO Gender (ORG, GENDER) VALUES (2, 'F');
INSERT INTO Gender (ORG, GENDER) VALUES (3, 'M');
INSERT INTO Gender (ORG, GENDER) VALUES (3, 'F');

INSERT INTO AGE (ORG, AGE) VALUES (1,27);
INSERT INTO AGE (ORG, AGE) VALUES (1,28);
INSERT INTO AGE (ORG, AGE) VALUES (1,29);
INSERT INTO AGE (ORG, AGE) VALUES (1,30);
INSERT INTO AGE (ORG, AGE) VALUES (2,37);
INSERT INTO AGE (ORG, AGE) VALUES (2,38);
INSERT INTO AGE (ORG, AGE) VALUES (2,39);
INSERT INTO AGE (ORG, AGE) VALUES (2,40);
INSERT INTO AGE (ORG, AGE) VALUES (3, 2);

INSERT INTO STATES (ORG, STATENAME) VALUES (1,'FL');
INSERT INTO STATES (ORG, STATENAME) VALUES (1,'GA');
INSERT INTO STATES (ORG, STATENAME) VALUES (1,'MN');
INSERT INTO STATES (ORG, STATENAME) VALUES (1,'NM');
INSERT INTO STATES (ORG, STATENAME) VALUES (2,'FL');
INSERT INTO STATES (ORG, STATENAME) VALUES (2,'MN');
INSERT INTO STATES (ORG, STATENAME) VALUES (2,'NM');
INSERT INTO STATES (ORG, STATENAME) VALUES (3,'FL');
INSERT INTO STATES (ORG, STATENAME) VALUES (3,'GA');
INSERT INTO STATES (ORG, STATENAME) VALUES (3,'NM');

CREATE TABLE FACTS
(
    ORG INT NOT NULL
    , GENDER VARCHAR(1) NULL
    , AGE INT NULL
    , STATENAME VARCHAR(255) NULL
);

INSERT INTO FACTS (ORG, GENDER, AGE, STATENAME)
SELECT ORG, GENDER, NULL, NULL
FROM GENDER
GROUP BY ORG, GENDER
UNION ALL
SELECT ORG, NULL, AGE, NULL
FROM AGE
GROUP BY ORG, AGE
UNION ALL
SELECT ORG, NULL, NULL, STATENAME
FROM STATES;

SELECT *
FROM FACTS
ORDER BY ORG;

The results:

This will create a FACTS table that has all the data from several source tables.  As @ypercube and @jon-seigel said, this really doesn't make much sense; perhaps we are missing something compelling about your setup.

If this is not what you were expecting, please provide the source tables, and any other pertinent details.


How to ensure that only unique row combinations are present? I have three tables which can be called set, item and assignment. The assignment table assigns an item to a set, thereby my goal is to ensure that each combination of items is unique. Hence, if item A and item B form set 1, there is no second set allowed which is formed by item A and item B. A combination of item A, item B and item C, however, is distinct and hence ok.

My original approach was using a hash with a unique constraint for each set which is formed by the IDs of the associated items. This hash would then be updated by a foreign table trigger on each insert/update operation on the assignment table. However, I failed in implementing such an update function and don't even know whether it's possible or not.

Does anybody know whether this is the way to go or whether there's an easier solution to my problem?

Triggers will not work on some scenarios. Let set1(A,B), set2(A,B,C) Now one cann't move to set2(A,B,D) by dropping set2-C and adding set2-D assignments because DB must go through prohibited set1(A,B), set2(A,B) state.
You should implement custom API, stored procedures or something with proper transactions which will check only final target state. Also those procs should place locks on updated set rows during the transaction. Because really what is updated is set.


SQLite, ASCII A-Z Check Constraint I have the following table which I'm trying to limit the column "prefix" to ASCII alphabetical characters. However, I am still able to insert other characters after using the following constraint. Why is it not working?

CREATE TABLE test
(
    id INTEGER NOT NULL PRIMARY KEY,
    prefix TEXT NOT NULL,
    CHECK(prefix NOT LIKE '%[^a-zA-Z]%')
)

Using Python's sqlite3 package and DB Browser for SQLite

The LIKE operator in SQL does not work like regular expressions. The pattern matches allowed by this operator are very simple, compared to the powerful regexp. Actually, you have two metacharacters: % means (0 or more of anything), which would be the equivalent of a .* in a regexp, and _ meaning (1 of anything), which would be the equivalentof . in a regexp. And that's it.

This means your CHECK condition, as originally written, is not actually checking what you expect. The only thing it will complain about are statements like:

INSERT INTO test
VALUES (37, 'hello [a-zA-Z] impossible');

that contain the literal [a-zA-Z].

You can actually use a REGEXP operator and write:

CHECK (prefix REGEXP '^[a-zA-Z]+$')

However, to be able to use it, you first need to install the sqlite3-pcre pcre regex extension for sqlite3, on a Linux machine (there may be a Windows alternative, but I've not been able to find it). You can find more information and instructions at Stack Overflow's How do I use regex in a SQLite query?.

Other databases such as PostgreSQL would let you use RegExp using the ~ operator. You can check it at SQLFiddle.

SQLite doc references and tutorials:

SQLite Like: Querying Data Based On Pattern Matching
The LIKE, GLOB, REGEXP, and MATCH operators


Why is there no max(uuid)/min(uuid) function? Why can I use a UUID to sort rows:
SELECT uuid_nil()
ORDER BY 1;

But I cannot compute the maximum value:
SELECT max(uuid_nil());

[42883] ERROR: function max(uuid) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.

I know that I can cast to a character type or ORDER BY and LIMIT 1. I'm just curious as to why I have to use a workaround.

I'd say that nobody saw any use in calculating the maximum of UUIDs, and I cannot see one either.
That said, it is easy to use CREATE AGGREGATE to define your own max, if you really need that.


OPENJSON running once per row of an INNER JOIN unnecessarily? I have a table MP_User with 147 entries, and in order to query it, I am passing in JSON containing an array of "query objects", each of which has a unique ID and either an email address or a mobile number to match to user(s).  These get converted to one row each and matches with the users able through a JOIN operation.  User results need to be returned along with the unique ID.  Although the following query does work for this:
WITH QryCte AS (
    SELECT * FROM OPENJSON('[{"UniqueId":"0xy-5244249247924974924C","MobileNo":null,"Email":"joe.bloggs@domain.co.uk"},{...},...]') WITH (UniqueId nvarchar(max), MobileNo nvarchar(255), Email nvarchar(255))
)
SELECT
    usr.[user_id],
    usr.[firstname],
    usr.[surname],
    qry.[UniqueId] AS [temp_user_match_unique_id]
FROM
    [dbo].[mp_user] usr
    INNER JOIN QryCte qry ON (qry.[MobileNo] IS NOT NULL AND usr.[mobile_no] = qry.[MobileNo]) OR (qry.[Email] IS NOT NULL AND usr.[email] = qry.[Email])

... it seems to run very inefficiently on our Azure SQL database, taking 5-10 seconds to run (it's much quicker on my local SQL Server DB, but that's probably because of increased resources as the execution plan is the same).  The execution plan reveals:

... that the OPENJSON has been run 147 times, once for each row of the MP_User table, presumably because of the INNER JOIN.  However, I don't understand why SQL Server does this.  The result of OPENJSON isn't going to change and you'd think that making it the table in a CTE would give SQL Server the hint that it just needs to execute it once and cache the result table that can then be joined to in the CTE's SELECT statement.
How can I make SQL Server just run the OPENJSON once, put the result into a memory table, and join to that?  Is it possible to do in a CTE or do I need to use procedural code where I explicitly define the table variable and assign it?  I'd rather avoid that if possible as the CTE form looks more elegant I think.

I ended up having to solve this by creating a table variable to hold the results of OPENJSON and joining on that variable.  This forces SQL Server to actually create a temp table containing the query data rather than repeatedly running OPENJSON.  Apparently a CTE doesn't do that, even though I thought it did.  Presumably this applies to any table-valued function, actually.
This works, and results in OPENJSON only being run once (when deriving the data to fill the table variable with):
DECLARE @jsonQueryText nvarchar(max) = '[{"UniqueId":"0xy-5244249247924974924C","MobileNo":null,"Email":"joe.bloggs@domain.co.uk"},{...},...]';
DECLARE @Qry TABLE(UniqueId nvarchar(max), MobileNo nvarchar(255), Email nvarchar(255));
INSERT INTO @Qry SELECT * FROM OPENJSON(@jsonQueryText) WITH (UniqueId nvarchar(max), MobileNo nvarchar(255), Email nvarchar(255));

SELECT
    usr.[user_id],
    usr.[firstname],
    usr.[surname],
    qry.[UniqueId] AS [temp_user_match_unique_id]
FROM
    [dbo].[mp_user] usr
    INNER JOIN @Qry qry ON (qry.[MobileNo] IS NOT NULL AND usr.[mobile_no] = qry.[MobileNo]) OR (qry.[Email] IS NOT NULL AND usr.[email] = qry.[Email])


Fastest query for selecting arrays that contain duplicates I'm using Postgres 9.5 and I have a column, named phones, of type text[].
I need to find all rows where this column contains duplicates.

I've found this extremely useful set of functions https://github.com/JDBurnZ/postgresql-anyarray and I could use this particular function https://github.com/JDBurnZ/postgresql-anyarray/blob/master/stable/anyarray_uniq.sql

select * from user_info
where anyarray_uniq(phones) <> phones

I was wondering though if there is a faster way of achieving what I want.
Maybe unnesting the array and using the window functionality would be better? Although I can find my way around SQL, I'm new to Postgres' specific best practices, so any help is welcome.

Is this better suited for CodeReview?

Among the fastest ways to run such a query is with a specialized index:

create index on user_info ((anyarray_uniq(phones) <> phones)) where
    anyarray_uniq(phones) <> phones;

That way the work of doing the comparisons happens when the records are inserted or updated, not when they are selected.  (To get this to work, you will have to mark as anyarray_uniq as IMMUTABLE, but as far as I can tell this is an accurate way to mark it)


Slow ranking values in MySQL I have a table of around 30 million retail products, with various INT columns that describe sales counters and pricing at different retailers.

I'm supposed to display well over 70 different rankings on every individual product's page.

Example: #23 best seller in China, #732 best seller in USA, #13 cheapest price in Norway, etc.

I've thought of various solutions to this problem:

Create ranking fields - I guessed it wouldn't work since just a single drop in price would mean updating millions of rows to reduce their ranking field by 1 for example.
Use COUNT - COUNT the amount of products cheaper than X : display ranking. I'm still considering this, however a single query takes 200 ms on average, so running 70 of them looks like it would take ages.

Really looking forward to advice from experienced database admins on this topic, as my knowledge feels very basic.

Additional information:

Hardware is dual Xeon CPUs, 15k RPM hard drives, 32 GB DDR3, LEMP stack, running latest Percona, all tables are XtraDB engine.

Edit: Posting CREATE statement as requested

CREATE TABLE `products` (
  `id` varchar(64) COLLATE utf8_bin NOT NULL,
  `retailer_id` int(10) unsigned NOT NULL,
  `sales_usa` int(10) unsigned NOT NULL,
  `sales_eu` int(10) unsigned NOT NULL,
  `sales_asia` int(10) unsigned NOT NULL,
  `sales_africa` int(10) unsigned NOT NULL,
  `sales_france` int(10) unsigned NOT NULL,
  `sales_germany` int(10) unsigned NOT NULL,
  `sales_russia` int(10) unsigned NOT NULL,
  `sales_china` int(10) unsigned NOT NULL,
  `sales_india` int(10) unsigned NOT NULL,
  `sales_uk` int(10) unsigned NOT NULL,
  `sales_spain` int(10) unsigned NOT NULL,
  `sales_norway` int(10) unsigned NOT NULL,
  `sales_sweden` int(10) unsigned NOT NULL,
  `sales_japan` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Sample ranking statement:

SELECT `id` FROM `products` WHERE `sales_japan` > 12345

After which I perform a mysql_num_rows() on the result to get that product's ranking in Japan for example.

Create an EVENT to periodically UPDATE a stats table that stores the "last_ID" to which each of the 70 rankings was last pulled, and the current counts for each.

Each UPDATE on the stats table entries (one per ranking), should join the real table with the entries you are counting, and have the respective criteria, setting stats.count to stats.count+real_tbl.COUNT(*), AND adding WHERE criteria to only consider WHERE id > stats.last_id. You will therefore also be setting stats.last_id to now = MAX(realtbl.id)

Each time the update runs, it will be limited to counting only entries added since the last run, and it will find them by the primary key index (ultra fast).


Enable Lock Pages in Memory for SQL Server on VM We have several SQL 2005 instances that are in a process of being virtulizaed and upgraded to SQL 2008 R2 running VMWare vSphere 5.1.

Our VMWare admin told me that in our org they do not over allocate memory to the guests. Per him though VMWare recommend to enable "Lock Pages in Memory" for SQL server running on virtual machines.

Does "Lock Pages in Memory" is enabled for SQL Server running on virtual machines?

I believe the general consensus these days is that if you are deploying onto a modern OS such as Windows 2008 R2, and the server is dedicated to SQL Server, then lock pages in memory is generally not required. The modern OS is much better at managing memory than earlier versions, even Windows 2003. However, it'll be prudent to test both scenarios.


One table with 2 foreign key mapping same primary key SELECT
  status,
  schedule_switch.Switch_Employee_ID,
  schedule_switch.Requesting_Employee_ID,
  employee_info.FirstName,
  DATE_FORMAT(schedule_switch.date, '%m-%d-%Y') AS switchdate,
  DATE_FORMAT(time_slot.date, '%m-%d-%Y') AS date,
  start_time,end_time
FROM
  work_schedule
    join time_slot on work_schedule.Slot_ID = time_slot.Slot_ID
    join manager_info on manager_info.Manager_ID = time_slot.Manager_ID
    join schedule_switch on work_schedule.Schedule_ID = schedule_switch.Schedule_ID
    join employee_info on schedule_switch.switch_Employee_ID = employee_info.Employee_ID
    join employee_info on schedule_switch.requesting_Employee_ID = employee_info.Employee_ID
where
  status is null
order by
  switchdate;

I am stuck with this query. I need to get the first name of switch_employee_id and requesting_employee_id both connected to one primary key Employee_ID in the employee_info table.

Try this

SELECT
    status,
    schedule_switch.Switch_Employee_ID,
    schedule_switch.Requesting_Employee_ID,
    empl_info1.FirstName,
    empl_info2.FirstName, //added the modified join select column
    DATE_FORMAT(schedule_switch.date, '%m-%d-%Y') AS switchdate,
    DATE_FORMAT(time_slot.date, '%m-%d-%Y') AS date,
    start_time,
    end_time
FROM work_schedule
    JOIN time_slot ON work_schedule.Slot_ID = time_slot.Slot_ID
    JOIN manager_info ON manager_info.Manager_ID = time_slot.Manager_ID
    JOIN schedule_switch ON work_schedule.Schedule_ID = schedule_switch.Schedule_ID
    JOIN employee_info empl_info1 ON schedule_switch.switch_Employee_ID = empl_info1.Employee_ID
    // modified your join
    JOIN employee_info empl_info2 ON schedule_switch.requesting_Employee_ID = empl_info2.Employee_ID
WHERE status IS NULL
ORDER BY switchdate;


How to cast to int array in Postgresql? I would like to cast from ARGV[] which is text to int array in PostgreSQL where I marked the pseudocode by TODO in the code.
Code in PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit:

CREATE TABLE measurements (
        measurement_id SERIAL PRIMARY KEY NOT NULL,
        measurement_size_in_bytes INTEGER NOT NULL
);

CREATE TABLE events (
        event_id SERIAL PRIMARY KEY NOT NULL,
        measurement_id INTEGER NOT NULL,
        event_index_start INTEGER NOT NULL,
        event_index_end INTEGER NOT NULL
);

CREATE OR REPLACE FUNCTION insaft_function()
    RETURNS TRIGGER AS
$func$
BEGIN
  -- TODO Loop until TG_ARGV[0] empty
INSERT INTO events (measurement_id, event_index_start, event_index_end)
SELECT NEW.measurement_id, TG_ARGV[0]::int[], TG_ARGV[1]::int[];
  -- END TODO
RETURN NULL; -- result ignored since this is an AFTER trigger
END
$func$ LANGUAGE plpgsql;

CREATE TRIGGER insaft_measurement_ids
AFTER INSERT ON measurements
FOR EACH ROW EXECUTE PROCEDURE insaft_function("{101, 111, 121}", "{101, 111, 121}");

which I can start by

INSERT INTO measurements (measurement_size_in_bytes) VALUES (888);

I know that the mistake is in casting here TG_ARGV[0]::int[] into intarray which I would like loop through until the intarray is empty.
There may exists better way to do such loop inserts.

ErwinBrandstetter's code output

Code

DROP TABLE IF EXISTS measurements, events, file_headers;

CREATE TABLE measurements (
        measurement_id SERIAL PRIMARY KEY NOT NULL,
        measurement_size_in_bytes INTEGER NOT NULL
);

CREATE TABLE events (
        event_id SERIAL PRIMARY KEY NOT NULL,
        measurement_id INTEGER NOT NULL,
        event_index_start INTEGER NOT NULL,
        event_index_end INTEGER NOT NULL
);

DROP TRIGGER IF EXISTS insaft_ids ON measurements;
DROP FUNCTION IF EXISTS insaft_function();

CREATE OR REPLACE FUNCTION insaft_function()
    RETURNS TRIGGER AS
$func$
DECLARE
   m int[];
BEGIN
   FOREACH m SLICE 1 IN ARRAY TG_ARGV::int[]
   LOOP
      INSERT INTO events (measurement_id, event_index_start, event_index_end)
      SELECT NEW.measurement_id, m[1], m[2];  -- Postgres array starts with 1 !
   END LOOP;
   RETURN NULL; -- result ignored since this is an AFTER trigger
END
$func$ LANGUAGE plpgsql;

CREATE TRIGGER insaft_ids
AFTER INSERT ON measurements
FOR EACH ROW EXECUTE PROCEDURE insaft_function('{{101,201},{201,300}}');

Running

sudo -u postgres psql detector -c "INSERT INTO measurements (measurement_size_in_bytes) VALUES (77777);"

getting

ERROR:  invalid input syntax for integer: "{{101,201},{201,300}}"
CONTEXT:  PL/pgSQL function insaft_function() line 5 at FOREACH over array

How can you cast from ARGV[] which is text to int array in PostgreSQL?

While passing integer numbers, you can either cast the whole array:
TG_ARGV::int[]

Or you can cast an element, then it must be the element type:
TG_ARGV[0]::int

I used it that way in my answer to your previous question:

SELECT in trigger function in two tables

However, you are not passing integer numbers, but the text representation of integer arrays: an integer array literal - with illegal syntax, too: values must be enclosed in single quotes, double quotes are for identifiers:
FOR EACH ROW EXECUTE PROCEDURE insaft_function("{101, 111, 121}", "{101, 111, 121}");
FOR EACH ROW EXECUTE PROCEDURE insaft_function('{101, 111, 121}', '{101, 111, 121}');

Since you are not passing integer but integer array literals, you cannot do either in your code. Consider (using a clearer example with distinct numbers):
SELECT (ARRAY['{101, 111, 121}', '{201, 211, 221}'])[1]::int[];
SELECT (ARRAY['{101, 111, 121}', '{201, 211, 221}'])::text[];
SELECT (ARRAY['{101, 111, 121}'::int[], '{201, 211, 221}'])::int[];
SELECT (ARRAY['{101, 111, 121}'::int[], '{201, 211, 221}'])[1][1];
SELECT (ARRAY['{{101, 111, 121},{201, 211, 221}}'])[1];
SELECT (ARRAY['{{101, 111, 121},{201, 211, 221}}'])[1]::int[];

@Chris provided more for that.
Solution for your function
I suggest you either pass all integer numbers or a 2-dimensional array literal. Demonstrating code for the latter:
'{{101,111,121},{201,211,221}}'

Or rather (the array pivoted, based on my educated guess):
'{{101,201},{111,211},{121,221}}'

So, this is one parameter:
CREATE TRIGGER ...
FOR EACH ROW EXECUTE PROCEDURE insaft_function('{{101,201},{111,211},{121,221}}');

As for the loop, use FOREACH m SLICE 1 IN ARRAY:

Postgres - array for loop

Function could look like this:
CREATE OR REPLACE FUNCTION insaft_function()
    RETURNS TRIGGER AS
$func$
DECLARE
   m int[];
BEGIN
   FOREACH m SLICE 1 IN ARRAY TG_ARGV[0]::int[]  -- reference 1st param
   LOOP
      INSERT INTO events (measurement_id, event_index_start, event_index_end)
      SELECT NEW.measurement_id, m[1], m[2];  -- Postgres array starts with 1 !
   END LOOP;
   RETURN NULL; -- result ignored since this is an AFTER trigger
END
$func$ LANGUAGE plpgsql;

But I have my suspicions there might be a simpler overall approach. This makes only sense if you have many tables that need a similar trigger, just with different integer numbers ...


Status fields: ALTER TYPE vs. INSERT INTO? I have a simple table with a status column:

create type status as enum (
  'approved',
  'pending_approval',
  'rejected'
);

create table document (
  id          serial primary key,
  uuid        uuid unique not null default uuid_generate_v4(),
  title       text not null,
  content     text,
  status      status not null,
  created_at  timestamptz not null default now(),
  updated_at  timestamptz not null default now(),
  deleted_at  timestamptz
);

This is the beginning of a small workflow-esque application I'm doing. Statuses, however, can be defined by the users of the application. What are the cons of using ALTER TYPE instead of creating a new table and using JOIN? I really don't want to add a JOIN to my queries just because of a status field.

What are the cons of using ALTER TYPE instead of creating a new table and using JOIN? I really don't want to add a JOIN to my queries just because of a status field.

This is your typical pro/con enum vs normalization question.

ENUM type

Great if the range is fixed or only requires modification by the DBA.
Get a really nice query syntax which optimized well (bottom assumes status is an ENUM)

SELECT * FROM foo WHERE status = 'INVALID';

Sorts well and permits easy range within the type. All enums are sorted and have ordinality
Store in an 4 byte-size
Can eliminate a JOIN
PostgreSQL has ENUM types, not macros (like MySQL). This means you don't take a hit in maintenance. If you have five tables using your ENUM type you can still manage just that one type and not five tables.

Normalized / JOIN method

Always preferred if the user needs to be able to modify the domain/data
Always preferred if the domain/data needs to shrink (remove values)
Will provide easier modification with an ORM
More traditional
Can be smaller than ENUM (for instance smallint is two bytes, enum is always four bytes).


MMS is assigning same alias to two data nodes of different shards and replicasets I am using MMS to monitor a 13 node MongoDB Cluster(3 Config Servers, 4 MongoS, 2 replica sets with 2 members + 1 arbiter each). MMS was working properly since its installation, but now it is not monitoring the shards & replica-sets properly.

It is monitoring only 4 nodes(1 Primary 1 Secondary and 2 arbiters) out of 6 nodes and auto-removing the other replica-set(primary + secondary) every time I am adding them manually. Also for the replica-set it is monitoring it is taking the primary secondary and arbiter as independent nodes(even though it is showing correct TYPE for them in the monitoring window).

When I checked Host Mapping it was surprised to see that it was showing the same alias name for both the Primary nodes and same for both the secondaries. I guess this is the reason it is not showing those 2 nodes in the monitoring window.

I manually removed those entries of same alias names from the Host Mapping window but it is auto-updating it after few minutes.

Why is it considering the two different nodes as same and taking the same alias name for them ? Is it a bug ?

MMS is working well for Config-servers and MongoS nodes.

My MMS group is "MMS@Ork"

My Data nodes are as follows:

Server1 -> Primary of Replica-set1 + Secondary of Replica-set2(2 Nodes) Server2 -> Primary of Replica-set2 + Secondary of Replica-set1(2 Nodes) Arbiters of both replica-sets are on separate machines. I have kept one port number common for both the primaries and other port number common for both the secondaries.

Version of all the nodes in the cluster is 2.4.9 and that of MMS monitoring-agent is 2.3.1

From the MMS Dashboard; what is the MMS Agent Log saying?

Have you looked in the mms-monitoring agent log locally on the server?

/var/log/mongodb-mms/monitoring-agent.log

Update

Please add a screen shot of how you are configuring the host

Update 14:02

Can you ping S143, S144 and mongo-client2 from the server where the mms-agent is installed?

Can you telnet to those servers from the mms-agent machine?

ex

telnet S143 37007
telnet S144 37007
telnet mongo-client2 37007

If you can't, then perhaps there is a network problem between the server with the MMS agent and the three mongods you listed above.


Try Below Index :

CREATE INDEX IDX_CMB ON wp_posts(ID,post_type,post_date);

Basically, this index will used for indexing GROUP BY clause WHERE clause, create this index & check explain plan & execution time.


Linked Server from On-Premise to Azure SQL Database I am using:
SSMS-18.4
SQL Server-SQL 2019 CU3
Windows 10

I was able to create a linked server successfully from on-premise SQL 2017 to the Azure SQL database without exposing my password.

--Read the password from text file
DECLARE @password VARCHAR(MAX)
SELECT  @password = BulkColumn
FROM    OPENROWSET(BULK 'C:\Azure SQL Database - Where is my  SQL Agent\password.txt', SINGLE_BLOB) AS x

--Drop and create linked server
IF EXISTS(SELECT * FROM sys.servers WHERE name = N'AzureDB_adventureworks')
EXEC master.dbo.sp_dropserver @server=N'AzureDB_adventureworks', @droplogins='droplogins';
EXEC master.dbo.sp_addlinkedserver
 @server = N'AzureDB_adventureworks',
 @srvproduct=N'',
 @provider=N'SQLNCLI',
 @datasrc=N'ugdemotargetserver.database.windows.net',
 @catalog=N'adventureworks';

EXEC master.dbo.sp_addlinkedsrvlogin
 @rmtsrvname=N'AzureDB_adventureworks',
 @useself=N'False',
 @rmtuser=N'taiob',@rmtpassword=@password;
GO

But the password is not getting the correct value.  I am getting a login failure.

Some of the error message:

Login failed for user 'taiob'.
(.Net SqlClient Data Provider) Server
Name: .\SQL2019
Error Number: 18456
Severity: 14
State: 1
Line Number: 1

I confirmed that it is reading the correct password from the file by running the following code:

DECLARE @password VARCHAR(MAX)
SELECT  @password = BulkColumn
FROM    OPENROWSET(BULK 'C:\Azure SQL Database - Where is my  SQL Agent\password.txt', SINGLE_BLOB) AS x
SELECT @password

I am using the same file in other places using PowerShell without any issues.

#Replace with password file location
$password = Get-Content "C:\Azure SQL Database - Where is my  SQL Agent\password.txt"

If I hardcode the password it works fine.  If I print the variable I can see the value is correct. It is not a firewall issue as I can directly connect from the same SSMS that I am running the code from.

Depending on how you create the text file, it can be in all sorts of formats.  Unicode or ANSI. With or without a Byte order Mark.  With or without trailing whitespace and line feeds, either posix or Windows style.

JSON is simpler that way.

For instance if you run cmd.exe

c:\>echo CowboYs4EVR! > password.txt

and then dump the bulk column

SELECT BulkColumn
FROM    OPENROWSET(BULK 'C:\temp\password.txt', SINGLE_blob) AS x

You'll see

0x436F77626F79733445565221200D0A

An ANSI tring with a trailing space and trailing CRLF, which you'll need to trim, like this:

set @password = trim(replace(replace(@password, char(13),''),char(10),''))

If you write the file from PowerShell it's likely to be unicode, etc.

But if you store the password in a json file instead, SQL Server will do the file format parsing for you.

eg something as simple as:

C:\temp>echo ["CowboYs4EVR!"] > password.json

and

DECLARE @password VARCHAR(MAX)
SELECT  @password = json_value(BulkColumn, '$[0]')
FROM    OPENROWSET(BULK 'C:\temp\password.json', SINGLE_CLOB) AS x


How to troubleshoot db/app latency during transaction log backup I have an application that uses a SQL server (2008 R2) database and we are periodically having performance problems with the application and it coincides with our 15-minute transaction log backups.

The application controls latency sensitive industrial machinery and we are finding that when the system has a high transaction volume occurring, the application has queries that run too long (>5 seconds for a brief period of time). These queries only run slow at the same time when the transaction log backups are happening so it appears to be related to the transaction log backups. When the system is performing without problems, our transaction log backups take 2-4 seconds and when we are under a heavier load and we have problems they are taking 6-7 seconds. That apparently is just enough to cause some FIFO message dispatch queues to fill up on the application.

First of all I was under the impression that transaction logs should be pretty transparent to the application, with no locking or anything else going on. Does this point to some kind of IO contention as being invoved if we are seeing database latency when we are doing the transaction log backup. What are the pros and  cons of moving to something like a 5-minute transaction log backup cadence instead of 15-minute?

Disk backend is a NetApp FAS2220 with a bunch of 600 GB 10k SAS drives. The DBA is convinced that this is an application problem and not a database problem so I need to know how to troubleshoot this to either being a problem with the application or the database.

TLDR: Database or application latency seen under heavy load during transaction log backup. How to troubleshoot and resolve?

It sounds like your transaction log probably has a lot of VLFs on it which causes the SQL Server to take a hit when doing the transaction log backups.  You can find this out by running

use {MyDatabase}
GO
DBCC LOGINFO
GO

The important part to look at is the number of rows which are returned.  If there are more than about 100 rows returned then you've got problems (please comment with the number of rows that are returned).

The fix is pretty easy.  Make a note of the size of the transaction log.  Shrink the transaction log down to as small as possible (the above command will return 2 rows).  This is done using the DBCC SHRINKFILE command.  You may need to run the shrink command a few times to get it small enough.  Try waiting a minute between shrink commands for the logs to cycle and run a log backup just before running the shrinkfile command.

DBCC SHRINKFILE (2,1)
GO

Then once the transaction log file is as small as possible grow the file in 8000 meg chunks using either the GUI in SQL Server Management Studio or by using the ALTER DATABASE command.

ALTER DATABASE MyDatabase
MODIFY FILE (name='MyLogFile', FILESIZE=8000MB)
GO

If you need the file to be larger than 8000 Megs do 8000 megs in size, then 16000 megs in size, then 24000 megs in size always growing 8000 megs at a time.  Compared to the prior size of the transaction log grow the low to the 8000 meg mark past the size that the log was.

If this doesn't do the trick let me know.


How do I restore my Heroku pg_dump on DigitalOcean? Simply-put, I'm trying to copy my database from a Heroku server over to my new DigitalOcean server.

This is what I've done so far:

Download (custom format) backup via Heroku
Upload the dump file to DigitalOcean
pg_restore -d postgres -1 /etc/postgresql/9.5/main/a190c52e-15f6-4fab-8a14-323275c6e174

... And then I get errors...

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 213; 1259 17190747 TABLE account_account rknsgnrsrzgwiv
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "account_account" already exists
    Command was: CREATE TABLE "public"."account_account" (
    "id" integer NOT NULL,
    "timezone" character varying(100) NOT NULL,
    "la...

It looks like you have already restored this data once before, so the objects already exist.  And trying to load it into the database named 'postgres' is a dubious practice anyway.

You should create a new database with a different name and load the data into that new database instead of into 'postgres'.  Or maybe you meant to use the -C option, in which case it will create and use the database which is named inside the dump file as the database to load the data into (in which case the database named in -d is only used to connect to in order to issue the CREATE DATABASE command).

If you really want to load this data to the database named 'postgres', you could use the -c option (without -C), which will drop the existing objects before recreating them.