SQL column=ALL(query): Equivalent meaning I know that the expression:

WHERE column <> ALL(subquery)

could have been written as:

WHERE column NOT IN(subquery)

What would be the equivalent for

WHERE column = ALL(subquery)

I don’t think there would be any differences between dialects of SQL which support the ALL operator.

If not mistaken you can use a not exists predicate and negate the operator for ALL in the subquery. Something like:

SELECT ...
FROM T
WHERE T.x = ALL ( select y from U )

corresponds to

SELECT ...
FROM T
WHERE NOT EXISTS ( select 1 from U
                   WHERE NOT T.x = U.y )

I.e.

WHERE NOT EXISTS ( select 1 from U
                   WHERE T.x <> U.y )

In other words, for the predicate = ALL to hold there must NOT EXISTS a tuple that does satisfy NOT =

Note that this might not hold if nulls are taken into consideration. Example:

with t (n) as ( values (1),(1),(2) )
select * from t as t1
where not exists (select n from t as t2 where t1.n <> t2.n )

and:

with t (n) as ( values (1),(1),(2) )
select * from t where n = all (select n from t)

Both return 0 rows. However:

with t (n) as ( values (1),(1),(2),(null) )
select * from t where n = all (select n from t)

return 0 rows whereas:

with t (n) as ( values (1),(1),(2),(null) )
select * from t as t1 where not exists (select n from t as t2 where t1.n <> t2.n )

return 1 row.

Three-valid logic makes things a whole lot more complicated, and one needs to be even more careful than usual when doing query rewrites.

FWIW, there is an really interesting project that applies Homotopy Type Theory (HoTT) to pseudo SQL:

http://cosette.cs.washington.edu/

The idea is to be able to prove whether two queries are equvalent, and if not provide an example of situations where they are not. Any vendor that adopts this to there dialect of SQL will have an killer app for sure.


Two types of data, so two type of databases? For a social network site, I need to propose a DB. The application is written in Java & will be hosted on VPS(s) initially.

Broadly classified there is two type of data to be stored at backend:

 1. dynamic lists which are:

     - frequently appended to
     - frequently read
     - sometimes reduced

 2. fixed set of data keyed by a primary key(sometimes modified).

 "For serving any page, I need to have access to both kind of data!"

As demanded by every other SN site, we need to consider for easy scaling in the future, but in addition to that our team & resources are also very very limited. We would like to start with a 1 or 2 medium sized VPS(s) & add more servers as data & load grows.

Personally I usually prefer something that is used by a large community, so ofcourse MySQL is big option but it doesn't fit our entire needs. It could be used for 2nd kind of data(among the list above) ie for storing fixed set of columns/data but not ideal for storing dynamic lists(ie 1st kind).
So should I use a 2nd database just to fit in only that type of data (two database each containing only data best suited for them)? (Some suggested Cassandra to store the 2nd kind of data.)
 What is the way to go ?

If the two families of data are related then I would generally recommend keeping the in the same database - it makes maintaining integrity easier, for instance taking a reliable and consistent backup is (depending on database engine of course) a single operation.

Of course if the two families of data are very loosely coupled anyway this need not be a concern, and using different databases or even completely different technologies for each becomes more likely to be a good solution particularly as you scale out (your first hardware scaling being too put each family of data on its own (virtual) machine. Remember though that if you do split the data over more than one storage technology you are increasing the amount of knowledge needed to maintain the system.

In MSSQL or similar you could break data with significantly different IO patterns into separate file groups in the same database and store them on different drives if needed for performance - this way all the data can be dealt with atomically for transaction or backup purposes and so forth while still being segregated for performance reasons.


Add more memory to Server? I have a Dell 2900 Server with 16 GB of memory running SQL Server 2005.

All 3 of the databases I have configured on the Server all have the maximum memory allocated to each Database. The primary database runs my production environment of our BAAN application, the other two databases run a development environment.

Besides reducing the amount of memory used in the other 2 Databases, is there a command I can issue to show the memory used by each database?

Try the query below:

SELECT (count(*)*0.0078125) AS 'Pages in Buffer pool'
    ,CASE database_id
        WHEN 32767 THEN 'ResourceDb'
        ELSE db_name(database_id)
        END AS Database_name
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY 1 DESC

This will show you the amount of space each DB has occupied in the buffer pool. Note that there are other factors in SQL that also use up the memory apart from the buffer pool.


Find out if two rows came from same transaction in Oracle I am using Oracle 11.1 and would like to figure out if two rows in one table where inserted by the same transaction.

Is there an easy sql statement to map a row to an transactions id? The table is an insert only table.

What are my options? Some baisc solution would do, I don't really want some extra tool to analyze the log files.

Thanks

You can get the SCN for a row with the ORA_ROWSCN pseudocolumn

Unless you have set row-level dependency tracking for the table, this will report the SCN of the last change to the block the row is in, which may not be much use. You can turn on row-level dependency tracking at create table time only, so you may need to drop and re-create your table.

Note that row-level tracking increases the size of each row by 6 bytes.

with rowdependencies:

create table foo rowdependencies as
select level as id from dual connect by level<=10000;

select count(distinct ora_rowscn) from foo;

COUNT(DISTINCTORA_ROWSCN)
-------------------------
1

begin
  for r in (select id from foo) loop
    update foo set id = id where id=r.id;
    commit;
  end loop;
end;
/

select count(distinct ora_rowscn) from foo;

COUNT(DISTINCTORA_ROWSCN)
-------------------------
10000

without rowdependencies (norowdependencies is the default):

create table bar as
select level as id from dual connect by level<=10000;

select count(distinct ora_rowscn) from bar;

COUNT(DISTINCTORA_ROWSCN)
-------------------------
1

begin
  for r in (select id from bar) loop
    update bar set id = id where id=r.id;
    commit;
  end loop;
end;
/

select count(distinct ora_rowscn) from bar;

COUNT(DISTINCTORA_ROWSCN)
-------------------------
16


MariaDB - Replication - Unable to login remotely So I've been trying to configure MariaDB replication for our server. Unfortunately I cannot get it replicating correctly.
So I've followed the MariaDB guide to the letter to get replication set up. Master and slave are configured and the slave is set connect to the master. The slave shows the error as:

Lost connection to server at 'waiting for initial communication packet', system error:110 "connection timed out".

I've noticed that I am unable to login to the master from the slave with -u root -p -h xxx.xxx.xxx.xxx. Though the servers are able to ping one another and all ports are allowed between the two, I can ssh between them and transfer files. Both replication and root users have the required permissions to login remotely and locally.
So I trawled google and implemented some changes to try and further diagnose the issue, such as bind address changes and max packet size. But no luck. I also noticed I am unable to login to the master from the master if I specify its IP address rather than just localhost. I get the error:

ERROR 1045 (28000):access denied for user 'root'@'xxx.xxx.xxx.xxx' (using password: YES).

Any ideas will be much appreciated!
Master is running the following:
Ubuntu server 16:04, MariaDB 10.0.38
Slave is running:
Ubuntu server 20.04, MariaDB 10.5.4

I think this is a good clue to what is wrong:

I also noticed I am unable to login to the master from the master if I
specify its IP address rather than just localhost.

It seems as if you don't have the right host specified for the user you have created. In MariaDB and MySQL when you create a user you also need to specify a "host". This can e.g. be a specific IP address to allow connections from that IP address only, or it can be a wildcard to allow connections from all hosts, or a partial IP address combined with a wildcard:
CREATE USER replication_user@'%' IDENTIFIED BY 'super_secret_password';


Understanding document size in MongoDB My collection.stats():

{
    "ns" : "csdbprotobuf.archive",
    "count" : 429787895,
    "size" : 48374032272,
    "avgObjSize" : 112,
    "storageSize" : 56527326864,
    "numExtents" : 47,
    "nindexes" : 1,
    "lastExtentSize" : 2146426864,
    "paddingFactor" : 1,
    "systemFlags" : 1,
    "userFlags" : 1,
    "totalIndexSize" : 19917512720,
    "indexSizes" : {
        "_id_" : 19917512720
    },
    "ok" : 1
}

paddingFactor is 1, avgObjSize is 112. But the average document size, which I can see using Object.bsonsize() function, is 73.4 bytes. I have no updates, only inserts and deletes. How can I optimize space consumption? How much RAM does each document consume: 112 or 73.4 bytes?

Upd: How many documents can be stored in a one page: <page size>/<avgObjSize from db.stats()> or <page size>/<avg size from Object.bsonsize()>, i.e 4096/112 or 4096/73.4?

To answer your question, I start out with an empty collection csdbprotobuf.archive on a stand-alone server that has nothing currently in cache

db.serverStatus({workingSet:1})

"workingSet" : {
    "note" : "thisIsAnEstimate",
    "pagesInMemory" : 10,
    "computationTimeMicros" : 4736,
    "overSeconds" : 81
},

Create the database and insert 10000 documents, an integer _id field and a string some name that has nine characters

use csdbprotobuf
t=db.archive
for(var i = 1; i <= 10000; i++){t.insert({a : i, name : 'some name'})}

Run an explain to load my data into memory called pre-heating

t.find().explain()
> t.find().explain()
{
    "cursor" : "BasicCursor",
    "isMultiKey" : false,
    "n" : 10000,
    "nscannedObjects" : 10000,
    "nscanned" : 10000,
    "nscannedObjectsAllPlans" : 10000,
    "nscannedAllPlans" : 10000,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 78,
    "nChunkSkips" : 0,
    "millis" : 1,
    "server" : "precise64_work:27000",
    "filterSet" : false
}

What is the status of memory?

    "workingSet" : {
        "note" : "thisIsAnEstimate",
        "pagesInMemory" : 363,
        "computationTimeMicros" : 2136,
        "overSeconds" : 600
    },

Initally I had 10 pages of data in memory, after the pre-heat using .explain(), I now have 363 pages in memory. Started out with 31MB of resident memory and now have 34MB which give me a difference of 3MB or 3072KB.

10000 documents / 3072KB = 3.255kb per document.

10000 documents / 353 pages of data = 28 documents per page

Using MongoDB's db.serverStatus({workingSet:1}) command, I was able to determine (approximately) how many documents fit onto a page.

It is important to note though, that each document in MongoDB is totally different. This is because of the polymorphic nature of its schema design. So just trying to determine exactly how much space one document is going to take is probably not what you are looking for.

Just because one document takes up 3KB doesn't mean that the next document won't take up twice that much or even more. This is not the way a traditional RDBMS works as you would know how much space each row can use.


Can a 2NF relation be M:M? I have recently learned about 1NF, 2NF, and 3NF.  I understand the definitions and the differences.  I also had learned earlier about removing M:M relationships from a conceptual model by using bridging 1:M entities (but at that time I was not aware of normal forms).  The cool thing that I see now about normalization is that if you start with one big messy relation, the normalization steps automatically take care of the M:M for you, so you don't have to consciously think "ok I am bridging away my M:M."

However, I decided to ask myself hypothetically WHICH level of normalization specifically is responsible for the removal of M:M relationships.  I know it is not 1NF, because I could easily come up with examples with are both 1NF and M:M.  However, in all the simple examples I contrived, bringing them to 2NF made the M:M go away.  But, I am not sure this is definitive since I seem to be not very creative in coming up with exhaustive examples.  So I pose this question: is there a 2NF relation which exists and is still M:M, which needs to go through 3NF to have the M:M removed?  Or does 2NF consistently disallow M:M by itself?

Thanks!

UPDATE: Let me try to explain myself better.  Consider this simple example:

Table BOOK_AUTHOR

| ISBN       | Title     | AuthorID  | AuthorName|
|------------|-----------|-----------|-----------|
|  0001      |  Book1    | A01       | King      |
|  0001      |  Book1    | A02       | Tolkien   |
|  0002      |  Book2    | A01       | King      |
|  0003      |  Book3    | A02       | Tolkien   |
|------------|-----------|-----------|-----------|

This is in 1NF and the PK is {ISBN, AuthorID}.

To go to 2NF we remove the partial dependencies ISBN->Title and AuthorID->AuthorName and end up with:

BOOK_AUTHOR (ISBN, AuthorID)
BOOK (ISBN, Title)
AUTHOR (AuthorID, AuthorName)

Now we have two real "entity" tables, BOOK and AUTHOR, plus the artificial bridge entity BOOK_AUTHOR.  And we got there just by going to 2NF.

However, if I had started in a different manner and made an ER with BOOK and AUTHOR and a M:M relationship between them, I'd have had to create the artificial BOOK_AUTHOR table myself, which happened above automatically at 2NF.  My question: does it always happen at 2NF, or do you sometimes need to get to 3NF to create the bridge?

However, now that I spell my own question out more I think I see a huge error in it.  I was asking at which xNF does the bridge get created, but the fact of the matter is that my 1NF example above is nothing more than a massive bridge.  Going to 2NF doesn't "create the bridge"... it's more like it creates the land on either side of the bridge by pulling the real entities out of the bogus huge table.  So going to 2NF (and higher) seems to be less about bridging and more about removing data redundancies (which is of course how it was presented to me in the first place)!

The normal forms don't really have anything to do with many-to-many relationships.  If you happen to lose some as a byproduct of the normalization process, that's fine, but you won't generally do so.  If we consider that we have two tables: Salesman and Product which each have ID fields as their primary key and we have a third table called Specializes which shows which Salesmen specialize in selling which products.  This Specializes table would represent the many-to-many relationship since each salesman can specialize in multiple products and each product can be specialized in by more than one salesmen.  It would probably look something like this (excuse the awkward formatting, we can't do real tables on StackExchange):

| SalesmanID | ProductID |
|------------|-----------|
|  1         |  1        |
|  1         |  2        |
|  2         |  1        |
|  2         |  2        |
|  2         |  3        |
|------------|-----------|

Obviously, the lack of nulls and repeated rows means that this table is in 1NF.  In this table, the only candidate key is {SalesmanID, ProductID} and as such, there are no non-prime attributes.  It also contains no non-trivial functional dependencies.  Thus, it is necessarily in 2NF, 3NF, and BCNF.  I'm also going to assert without proof that it's in 4NF, 5NF, 6NF, and DKNF (to avoid having to explain all of the details thereof).  So really, no normal form removes many-to-many relationships, nor are they meant to.  The purpose of normal forms is not to remove many-to-many relationships (and I am not actually clear on why you would want to) but rather to remove potential insert anomalies, update anomalies, and deletion anomalies.  The primary role of the normal forms is to ensure that each piece of information is represented in a database table precisely once.  Having the same information embedded in multiple places leads to problems.  But that has nothing to do with many-to-many relationships.

I think that you mean to be asking a slightly different question about something like situations where a many-to-many relationship is embedded in a table which also tries to contain other information, such as if the table above contained the product name in addition to the product number (where name is functionally determined by number).  A table like that would either violate 2NF (if the name did not also functionally determine the number) or Boyce Codd Normal Form (if the name did functionally determine the number).

You could also perhaps be thinking of a different situation: when we have two unrelated 1:M relationships in the same table, such as if we were to add a third column to identify which language or languages each salesman speaks.

| SalesmanID | ProductID | Language |
|------------|-----------|----------|
|  1         |  1        | English  |
|  1         |  2        | English  |
|  2         |  1        | Spanish  |
|  2         |  2        | Spanish  |
|  2         |  3        | Spanish  |
|  2         |  1        | French   |
|  2         |  2        | French   |
|  2         |  3        | French   |
|------------|-----------|----------|

As you can see, that table is quite problematic, since we need 6 entries to express that Salesman 2 specializes in 3 products and speaks 2 languages.  This is a fourth normal form violation.

Edit:

Upon clarification, it's clear that what he's asking about is a table like the Specializes table, but with extra information about the salesmen and the products, essentially, a table which contains two entity sets and their many-to-many relationship in a single table.  So to answer that question directly, yes, you can have lousy tables like that which are in 3NF.  The normal form which guarantees that that won't happen is Boyce-Codd Normal Form (BCNF).  Here's an example of a lousy table like that which is vulnerable to all kinds of anomalies (insert, update, and delete), but is in 2NF and 3NF.

| SalesmanName | SalesmanID | ProductID | ProductName |
|--------------|------------|-----------|-------------|
|   Alex       |  1         |  1        |  Thingy     |
|   Alex       |  1         |  2        |  Whatsit    |
|   Barb       |  2         |  1        |  Thingy     |
|   Barb       |  2         |  2        |  Whatsit    |
|   Barb       |  2         |  3        |  Whoosit    |
|--------------|------------|-----------|-------------|

So, looking at this table, it's obviously in 1NF.  Further, we can identify the non-trivial functional dependencies very straightforwardly.  SalesmanName -> SalesmanID.  SalesmanID -> SalesmanName.  ProductID -> ProductName.  ProductName -> ProductID.  Next we need to identify the candidate keys.  There are four: {SalesmanName,ProductID}, {SalesmanName,ProductName}, {SalesmanID, ProductID}, and {SalesmanID, ProductName}.  As such, we have no non-prime attributes.  Thus, we are necessarily in 2NF (no functional dependencies between non-prime attributes) and 3NF (no non-trivial functional dependencies where the left-hand-side is not a super key and the right hand side contains a non-prime attribute).  However, we are not in BCNF because there do exist non-trivial functional dependencies whose left-hand-side is not a superkey.

Any similar situation will also always not be in Boyce-Codd Normal form because there will be some non-trivial functional dependency whose left-hand-side is not a superkey.  Any table like this will essentially have two entity sets each of whom have some attributes.  Basically, it will have a left entity set and a right entity set.  The left entity set will have some attributes which uniquely identify each left entity and the right entity set will have some attributes which uniquely identify each right entity.  Those will be involved in functional dependencies.  However, they will each not be candidate keys because you'll have to combine them to get a candidate key for the whole table.  As such, they won't be superkeys and there will be a Boyce-Codd Normal Form violation.  So BCNF will stop it cold.  Anything less than that will only catch some cases.  Really, if you only remember one normal form, it should be BCNF.


Continuous archiving: is it possible to use multiple machines? Is it possible to use the archive_command to send to multiple machines?  Is it just as simple as separating with a && since it is essentially just a command line?

command1 && command2

I assume this could have a bad effect if one command failed, for example if command1 failed then command2 wouldn't run; and if command2 failed it would return non-zero for the whole thing and Postgres would re-try to send the WAL which would mean it would re-run command1.

I am hoping there is a more official solution to the problem. My goal is to have one backup inside the data center, and one backup outside the data center.

First, I always create a wrapper script when creating commands like that. That makes it easier to debug the command without having to signal postgres to reload anything. This also makes it easier to include logging from your archiver script.

What you need to figure out first is how you want your data replicated. The way you're doing it now seems ok, at least if you check if the destination file doesn't exist before copying it. That way it can be re-run as many times as you like and it will try to copy it to the external location.


Simultaneous calls to the same function: how are deadlocks occurring? My function new_customer is called several times per second (but only once per session) by a web application. The very first thing it does is lock the customer table (to do an 'insert if not exists'—a simple variant of an upsert).

My understanding of the docs is that other calls to new_customer should simply queue until all previous calls have finished:

  LOCK TABLE obtains a table-level lock, waiting if necessary for any conflicting locks to be released.

Why is it sometimes deadlocking instead?

definition:

create function new_customer(secret bytea) returns integer language sql
                security definer set search_path = postgres,pg_temp as $$
  lock customer in exclusive mode;
  --
  with w as ( insert into customer(customer_secret,customer_read_secret)
              select secret,decode(md5(encode(secret, 'hex')),'hex')
              where not exists(select * from customer where customer_secret=secret)
              returning customer_id )
  insert into collection(customer_id) select customer_id from w;
  --
  select customer_id from customer where customer_secret=secret;
$$;

error from log:

2015-07-28 08:02:58 BST DETAIL:  Process 12380 waits for ExclusiveLock on relation 16438 of database 12141; blocked by process 12379.
        Process 12379 waits for ExclusiveLock on relation 16438 of database 12141; blocked by process 12380.
        Process 12380: select new_customer(decode($1::text, 'hex'))
        Process 12379: select new_customer(decode($1::text, 'hex'))
2015-07-28 08:02:58 BST HINT:  See server log for query details.
2015-07-28 08:02:58 BST CONTEXT:  SQL function "new_customer" statement 1
2015-07-28 08:02:58 BST STATEMENT:  select new_customer(decode($1::text, 'hex'))

relation:

postgres=# select relname from pg_class where oid=16438;
┌──────────┐
│ relname  │
├──────────┤
│ customer │
└──────────┘

edit:

I've managed to get a simple-ish reproducible test case. To me this looks like a bug due to some sort of race condition.

schema:

create table test( id serial primary key, val text );

create function f_test(v text) returns integer language sql security definer set search_path = postgres,pg_temp as $$
  lock test in exclusive mode;
  insert into test(val) select v where not exists(select * from test where val=v);
  select id from test where val=v;
$$;

bash script run simultaneously in two bash sessions:

for i in {1..1000}; do psql postgres postgres -c "select f_test('blah')"; done

error log (usually a handful of deadlocks over the 1000 calls):

2015-07-28 16:46:19 BST ERROR:  deadlock detected
2015-07-28 16:46:19 BST DETAIL:  Process 9394 waits for ExclusiveLock on relation 65605 of database 12141; blocked by process 9393.
        Process 9393 waits for ExclusiveLock on relation 65605 of database 12141; blocked by process 9394.
        Process 9394: select f_test('blah')
        Process 9393: select f_test('blah')
2015-07-28 16:46:19 BST HINT:  See server log for query details.
2015-07-28 16:46:19 BST CONTEXT:  SQL function "f_test" statement 1
2015-07-28 16:46:19 BST STATEMENT:  select f_test('blah')

edit 2:

@ypercube suggested a variant with the lock table outside the function:

for i in {1..1000}; do psql postgres postgres -c "begin; lock test in exclusive mode; select f_test('blah'); end"; done

interestingly this eliminates the deadlocks.

I posted this to pgsql-bugs and the reply there from Tom Lane indicates this is a lock escalation issue, disguised by the mechanics of the way SQL language functions are processed. Essentially, the lock generated by the insert is obtained before the exclusive lock on the table:

  I believe the issue with this is that a SQL function will do parsing (and
  maybe planning too; don't feel like checking the code right now) for the
  entire function body at once.  This means that due to the INSERT command
  you acquire RowExclusiveLock on the "test" table during function body
  parsing, before the LOCK command actually executes.  So the LOCK
  represents a lock escalation attempt, and deadlocks are to be expected.

  This coding technique would be safe in plpgsql, but not in a SQL-language
  function.

  There have been discussions of reimplementing SQL-language functions so
  that parsing occurs one statement at a time, but don't hold your breath
  about something happening in that direction; it doesn't seem to be a
  high priority concern for anybody.

  regards, tom lane

This also explains why locking the table outside the function in a wrapping plpgsql block (as suggested by @ypercube) prevents the deadlocks.


SQL server replication - Does not ask for the snapshot folder SQL SERVER 2008

When I created a publisher for the first time, it asked for the snapshot folder path. I deleted that replication and tried to recreate the publisher. This time it doesn't even ask for the path for snapshot folder.

Why is that? and how can I force this to ask for a snapshot path.

If I change the path to one replication (that I am going to create, will it change for all the replications that is running on the sql instance?

Thanks for your help

SQL Server asks for a root snapshot folder path when you Configured Publishing and Distribution.

After configuring publishing and distribution, all subsequent publications created will use that snapshot folder but will have their own sub-folder within that folder.

If you wish to have your snapshot files for a particular publication be put into a different root snapshot folder, specify this by passing in the path to the @alt_snapshot_folder parameter of sp_addpublication or sp_addmergepublication when you create the publication.  Alternatively, this can be done from the Publication Properties dialog after you have created your publication and before generating a snapshot on the Snapshot page, Location of snapshot files section, Put files in the following folder.

If you have anymore questions, please let me know.

I hope this helps.


Can 2 tables lock each other if concurrent read/update do not affect same rows? pagelocks off Can 2 tables cause a deadlock, if the pagelocks are off, and the rows are not related.
E.g.

Query 1 runs for a few seconds

Select * from Orders where CustomerID = 1 and DateOrdered between @D1 and @D2

Query 2 starts to run, and ends, before Query 1 is finished, but with a different customerID

Update Orders set Quantity = Quantity + 10 where OrderID = 20 and CustomerID = 2

(These are just examples to prove a point please)

Now there is a PK_Orders which is a clustered index on the OrderID.
There is also a separate index (ix_Order_Customer) which is a non-clustered index, and contains CustomerID as one of the fields to index. This index has pagelocks enabled.

What I don't understand is how SQL (with traceID 1222) showed me in SQL Server logging that the PK_Orders caused a deadlock when the query which was executed did not even affect the same OrderIDs which were updated.

Extract from Log File Viewer:

 keylock hobtid=720229632 dbid=5 objectname=myDB.dbo.Orders
 indexname=PK_Orders id=lock1b0b23c0 mode=X associatedObjectId=720229632

and

 pagelock fileid=1 pageid=195848 dbid=5 objectname=myDB.dbo.Orders id=lock24442a40
 mode=S associatedObjectId=720229274

Can someone help explain how this is possible please, should the Deadlock not only kick in if there were intersecting rows here? I copied the 2 queries and ran them at the same time, yet I cannot recreate the error and in SQL Server Management Studio this error does not occur - the update goes through.

The error in SQL Event Log Viewer specifically says that PK_Orders is the cause here.

Usually deadlock happens when:

Query 1 tries to do an update - but before updating it needs to do a select. So first it applies READ lock (S - shared lock). Let's say it managed to lock a couple of pages.
Query 2 tries to do a select and sets a READ lock (S - shared lock) on some of these pages.
Query 1 now tries to do an update - so it tries to change READ lock to a WRITE lock (i.e. S (shared) to an X (exclusive) lock). It  manages to do it on certain pages but can't do it on all of them since some of the pages are locked by Query 2.
Query 2 in turn tries to obtain more pages - but they now have a X lock (WRITE lock).

In the end both queries are waiting for each other so SQL Server decides to kill one query with DEADLOCK exception.

By the way - issue is not Primary Key Index here.


Implications of changing SQL Server collation We are currently using SQL Server 2016 and currently the collation set on our database is SQL_Latin1_General_CP1_CI_AS. Our Analaysis services' collation is set to SQL_Latin1_General_CP1_CS_AS.

The recommendation from our vendor is that the relational database and the Analysis services collation should both be case sensitive.

We would like to understand if there are any downside to changing the collation on our relational database to be case sensitive as well, to match with the Analysis services.

I posted a detailed analysis of the implications of making any collation changes to an instance and/or database:

Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?

There are quite a few areas that could be affected, and it depends greatly on if you are talking just about one particular database, or the instance and one or more of the databases on that instance.

HOWEVER, perhaps in addition to asking what might the affect of such a change be, you should probably also be asking the vendor:

Why they are making this recommendation? If they do not have a very specific reason for it, then maybe you shouldn't do it.
What is the specific recommendation: is it to make the instance case-sensitive, or just this particular database?
Is the vendor installing objects into an existing database, or do they create their own? If they create their own database, they can ensure that it is case-sensitive by setting the collation when the database is created. And if they are installing into an existing database, they can simply use the COLLATE clause in the CREATE TABLE statements to override the default from the database; they can use the COLLATE clause in any expression to override the database's default collation.
Why are they using obsolete SQL Server collations instead of the newer, more appropriate Windows collations? For anyone using SQL Server 2008 or newer , Latin1_General_100_CS_AS_SC would be the preferred equivalent of SQL_Latin1_General_CP1_CS_AS.


Total logical reads per database on SQL Server Is there a way to get the total running logical reads\writes per database from last restart?  Trying to monitor what dbs are most used and if that changes over time.

You could use a query like this, with the caveat that it will also track backup activity as reads. I'm also not aware of a DMV that tracks logical reads in the way you want.
To account for the backups caveat, you may want to stick it in an Agent Job and log it to a table at the beginning and end of working periods, or some other cadence that makes sense.
If you have Query Store enabled on all of your databases, you could take a daily snapshot of runtime_stats to get closer to the truth. But that's a big if, and depending on your settings it may not capture all query activity.
SELECT
    run_date =
        GETDATE(),
    days_up =
        DATEDIFF
        (
            DAY,
            DATEADD
            (
                MILLISECOND,
                -vfs.sample_ms,
                GETDATE()
            ),
            GETDATE()
        ),
    database_name =
        DB_NAME(vfs.database_id),
    drive =
        LEFT
        (
            mf.physical_name,
            1
        ),
    mf.physical_name,
    size_on_disk_mb =
        vfs.size_on_disk_bytes / 1024 / 1024.,
    read_latency =
        (vfs.io_stall_read_ms
            / NULLIF(vfs.num_of_reads, 0)),
    mb_read =
        vfs.num_of_bytes_read
            / 1000000.,
    avg_mb_per_read =
        (vfs.num_of_bytes_read
            / NULLIF(vfs.num_of_reads, 0)) / 1000000.,
    avg_read_stall =
        vfs.num_of_reads
            / NULLIF(vfs.io_stall_read_ms, 0),
    write_latency =
        (vfs.io_stall_write_ms
            / NULLIF(vfs.num_of_writes, 0)),
    mb_written =
        vfs.num_of_bytes_written / 1000000.,
   avg_mb_per_write =
       (vfs.num_of_bytes_written
           / NULLIF(vfs.num_of_writes, 0)) / 1000000.,
    avg_write_stall =
        vfs.num_of_writes
            / NULLIF(vfs.io_stall_write_ms, 0)
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN sys.master_files AS mf
    ON  vfs.database_id = mf.database_id
    AND vfs.file_id = mf.file_id
ORDER BY size_on_disk_mb DESC;


Postgresql- how to rotate table partitions, trunc oldest every day? I need to create a partitioned table with 4 rotating partitions for 4 days, and every day truncate the oldest partition.

I know how to create table partitions on postgres, but I can't understand how to arrange daily rotation of several partitions.

I'm on postgresql 9.6, Amazon RDS instance which means no pg_cron to be able to run daily truncating.

I could write a python script and schedule it in cron on a side micro instance, but I'd preper to use internal postgres tools only.

First approach is creating partition every day which name matches date:

DROP TABLE IF EXISTS log_table;

CREATE TABLE log_table (
    logid         SERIAL,
    logdate         date DEFAULT Now()
) PARTITION BY RANGE (logdate);

CREATE TABLE log_table_2020_05_10 PARTITION OF log_table FOR VALUES FROM ('2020-05-10') TO ('2020-05-11');
CREATE TABLE log_table_2020_05_11 PARTITION OF log_table FOR VALUES FROM ('2020-05-11') TO ('2020-05-12');
CREATE TABLE log_table_2020_05_12 PARTITION OF log_table FOR VALUES FROM ('2020-05-12') TO ('2020-05-13');
CREATE TABLE log_table_2020_05_13 PARTITION OF log_table FOR VALUES FROM ('2020-05-13') TO ('2020-05-14');
CREATE TABLE log_table_2020_05_14 PARTITION OF log_table FOR VALUES FROM ('2020-05-14') TO ('2020-05-15');

And then every day you will have to drop the oldest partition and create the new one:

DROP TABLE log_table_2020_05_10;
CREATE TABLE log_table_2020_05_15 PARTITION OF log_table FOR VALUES FROM ('2020-05-15') TO ('2020-05-16');

The second approach with always-the-same-name of partitions will looks like this:

-- initial tables
DROP TABLE IF EXISTS log_table;

CREATE TABLE log_table (
    logid         SERIAL,
    logdate         date DEFAULT Now()
) PARTITION BY RANGE (logdate);

CREATE TABLE log_table_4 PARTITION OF log_table FOR VALUES FROM ('2020-05-10') TO ('2020-05-11');
CREATE TABLE log_table_3 PARTITION OF log_table FOR VALUES FROM ('2020-05-11') TO ('2020-05-12');
CREATE TABLE log_table_2 PARTITION OF log_table FOR VALUES FROM ('2020-05-12') TO ('2020-05-13');
CREATE TABLE log_table_1 PARTITION OF log_table FOR VALUES FROM ('2020-05-13') TO ('2020-05-14');
CREATE TABLE log_table_current PARTITION OF log_table FOR VALUES FROM ('2020-05-14') TO ('2020-05-15');

And then every day you will schedule something like:

-- drop the oldest partition
DROP TABLE log_table_4;

-- rename others
ALTER TABLE log_table_3 RENAME TO log_table_4;
ALTER TABLE log_table_2 RENAME TO log_table_3;
ALTER TABLE log_table_1 RENAME TO log_table_2;
ALTER TABLE log_table_current RENAME TO log_table_1;

-- create new one
CREATE TABLE log_table_current PARTITION OF log_table FOR VALUES FROM (date_trunc('day', Now())) TO (date_trunc('day', Now())+INTERVAL '1 day');


Index creation before or after loading data I have a situation where I need to create a table in Postgres and add approximately 3 million rows. Currently I am adding an index to one field after all the data is loaded, but I was wondering whether adding an index before loading the data (at the time the table is created) is better, worse, or neutral.

What is considered best practice for when indexes should be added to tables in Postgres? Does Postgres pause indexing during transactions?

Adding the index after the table is populated will be faster, perhaps hugely faster.  The only reasons to have the index first are:

Other transactions must not be allowed to see the table without the index (but in this case, try to create the table, populate the table, and create the index all in one transaction)
The index supports a constraint, and you want the constraint enforced during the loading process rather than at the end.
You don't care much about the performance difference, and creating the index upfront is just syntactically easier to do.


Which all system parameters to be considered for standard Vacuum process We want to run standard vacuum process on our production database which is over 100 GB and have millions of dead tuples.

Can anyone suggest what system parameters we need to keep in mind for setting cost-based vacuum settings? I mean like CPU/IO/Memory/Disk.

We cannot run vacuum full as the database should be up and running continuously so we just want to attain most appropriate value without affecting the system much.

The default settings will probably be fine.

That will limit the IO write usage to about 4MB/sec, that is, 4kB/msec = (8KB * vacuum_cost_limit / vacuum_cost_page_dirty / autovacuum_vacuum_cost_delay)

And RAM usage to about 48 MB (autovacuum_max_workers * maintenance_work_mem)


Select all from table using procedure oracle I am trying to execute the select command in a procedure.Initially i retrived all of the columns of the table in the procedure using cursor. The real problem comes when I am trying to access the elements of the coloumn. It is printing the names of the columns again without printing the elements of the columns and also with an error!

set serveroutput on;
CREATE OR REPLACE PROCEDURE show_all1
AS
cursor gen_row is
select COLUMN_NAME from ALL_TAB_COLUMNS where TABLE_NAME='TESTNIRMAL';
cursor gen1_row(i varchar) is
select i  from testnirmal;
a1 varchar2(22);
a2 varchar2(10);
BEGIN
open gen_row;
for data in(SELECT * from testnirmal)
loop
fetch gen_row into a1;
open gen1_row(a1);
fetch gen1_row into a2;
dbms_output.put_line(a2);
close gen1_row;
end loop;
close gen_row;
close gen1_row;
END show_all1;
/

DEF_ID and ID1 are the two columns in my table and the table name is testnirmal.
The procedure is created without any compilation errors.
But while executing the procedure I am getting this.

  DEF_ID
  ID1 BEGIN show_all1; END;

  ERROR at line 1: ORA-01001: invalid cursor ORA-06512: at "USER.SHOW_ALL1", line 20 ORA-06512: at line 1

You open two cursors  but close the outer one before the inner one

open gen_row; -- open the outer cursor...this semicolon seems odd
for data in(SELECT * from testnirmal)
loop
fetch gen_row into a1;
open gen1_row(a1);
fetch gen1_row into a2;
dbms_output.put_line(a2);
close gen1_row;
end loop;
close gen_row;--here you close the outer cursor
close gen1_row; --here you close the inner cursor when it is out of scope


Last accessed timestamp of a Netezza table? Does anyone know of a query that gives me details on the last time a Netezza table was accessed for any of the operations (select, insert or update) ?

Thanks

You could setup a history database (using nzhistcreatedb) which acts as a detailed access log and then consult the views and tables that it provides (e.g. $v_hist_table_access_stats).

If you haven't already got this in place then I'm not aware of any way to find out who accessed a table unless you trawl through the logs in /nz/kit/log/postgres/.


SQL Server grant to multiple domains I am working in a joint venture of four different contractors doing one big project. Many of the application will have to use the different ADFS servers each of the four companies have. Some are desktop application connecting directly to SQL which will be hosted in IaaS. Can SQL Server grant permissions to four different domains?

DB Links can be configured with different accounts for each DB Link. But it would still be an ugly mess as far as security and management.


What is an audit table? What are audit tables?

How are they useful?

I came across them reading this article.

One other important aspect of audit tables that hasn't been highlighted thus far, is that in addition to keeping track of who did what to which record (often including before and after snapshots) audit tables are write-once.

Records in an audit table may not be updated or deleted (see note), only inserted.  This is sometimes imposed using triggers or maybe just application logic, but it's important in practice because it gives you "proof" that nothing's been tampered with in a way which is difficult to detect.

Note: Cleaning out old records from an audit table requires special processes which often have to be approved by management or auditors.


How to create function with Return Type as (TABLE student_name string) I am creating function using pgAdmin4 and the CODE Basically something like

BEGIN
   RETURN QUERY
    SELECT * FROM teest."students";
END

I am just wondering, how do I set the TABLE ReturnType as
TABLE(applicant_id integer, ship_id integer) something like that. There's no TABLE return type there.

Thanks

Either by using returns table

create function get_students()
 returns table(applicant_id integer, ship_id integer)
as
$$
  select applicant_id, ship_id
  from test.students
$$
language sql;

create function get_student_names()
 returns table(student_name text)
as
$$
  select name
  from test.students
$$
language sql;

Or by using setof

create function get_students
 returns setof students
as
$$
  select *
  from test.students
$$
language sql;


Reasons to maintain FKs on an empty, deprecated table? So I have a few dozen empty tables with foreign keys to other, populated tables. These tables are not being accessed by any CRUD operations, but have about 6-12 months before they can be unlinked from Entity Framework applications (due to project timelines) and so are sticking around for a while.

These tables will definitely never be used again and any relationships are already documented / unnecessary to retain.

Does dropping these foreign keys have any benefits?

The FK would still need to check with the empty table to see if the key was used on a delete operation.  But this would be a quick check and never deny if the table is empty.


MS SQL Server accepts non-SSL connections even with Force Encryption enabled on the server side I am forcing encryption on my SQL Server. My intention is to reject any client connection that does not use SSL to connect. Am I on the right track?

Here are my detailed steps :

makecert -r -pe -n "CN=slc02xla.company.com" -b 01/01/2000 -e 01/01/2036 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12 c:\my.cer
I imported the same certificate into the trusted Root Certification Authorities Store
In SQL Server Configuration Manager, expandrf SQL Server Network Configuration, right-clicked Protocols for , and then selected Properties.
On the Certificate tab, selected the desired certificate from the Certificate drop-down menu, and then clicked OK.
On the Flags tab, selected Yes in the ForceEncryption box, and then clicked OK to close the dialog box.
Restarted the SQL Server service.

Am I missing anything else?

Yes, this is correct.
It's also described here Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager)
And maybe also this discussion helps: force-encryption-on-sql-server-not-working


Transfer data from database1 to database2 using task queue We have a SQL Server legacy database, db1, to put data of Project1. With change of certain tables(insert/update) in db1, we need to transfer the changes in another postgres database, db2 (used by Project2).

Presently the system is adhoc. With any change of table1 in db1, we keep the record (table1, id, actiontype) in a 'Queue' table, upon which a cron job runs every 5 minutes, which transfers data to db2 using the information in db1.Queue.

Problem is, following this way, we can't see real-time reflection in db2, i.e. data from db1 wouldn't immediately propagate to db2. We have to wait for the cron job to run.

Any suggestion to improve the solution highly appreciated.

Is distributed async task queue (e.g. Celery) a good option here?

You should be able to use triggers and dbilink to sync your data if you're prepared to install plperl on the postgresql end. If you are running version 9.1+ then you could instead try the foreign-data-wrapper functionality. There appear to be tds and fdw wrappers, though I've not used either myself.

Unless I'm missing something, an external queue manager sounds overkill.


Is disabling triggers to disable foreign key constraints a good idea? Background

We're moving a legacy application from a non-relational database to PostgreSQL.

Problem

We'd like to enable foreign keys on a lot of fields for future data integrity and so that reporting tools can easily determine which tables are linked. Some of the current data won't pass the FK check and because of the legacy app connecting to the database we can't turn on the FKs yet.

Temporary Solution

Some of the programmers are suggesting that we disable triggers on the database as shown here so that the FKs aren't enforced but will still be there for reporting tools.

Question

I'm afraid that this will cause a bunch of other un-intended consequences but I'm not sure what they are. Can anyone put my fears to rest or explain why this is a really bad idea? I'm assuming that one reason it is bad is that it would disable all triggers not just triggers that fire the FK constraints.

You can create the foreign key constraints with the option NOT VALID. Then they are declared, but are not enforced:

create table parent (id integer primary key);
create table child (id integer primary key, pid integer);

insert into parent values (1),(2),(3);
insert into child values (1,100),(2,200),(3,300); --<< invalid pid values!

alter table child
     add constraint fk_child_parent
     foreign key (pid) references p (id) NOT VALID; --<< succeeds


Can't find names with criteria that has special symbols in PostgreSQL I am running the following query which gives syntax error:

select distinct e.ahrims_id,e.card_num,e.name_related,e.d_name,e.father_name,
e.d_father_name from hr_employee e left join unit_identification_code uic
on e.unit_identificatin_code_id = uic.id where uic.code in (
'ZRMAAA', 'ZRMA10', 'ZRMA66', 'ZRMA11', 'ZRMA12', 'ZRMA13', 'ZRMA14', 'ZRMA15', 'ZRMA27',
'ZRMA53', 'ZRMB11', 'ZRMA20', 'ZRMA65', 'ZRMA21', 'ZRMA22', 'ZRMA23', 'ZRMA24', 'ZRMA25',
'ZRMA26', 'ZRMA52', 'ZRMA30', 'ZRMA60', 'ZRMA31', 'ZRMA32', 'ZRMA33', 'ZRMA34', 'ZRMA35',
'ZRMA28', 'ZRMA80', 'ZRMB13', 'ZRMA40', 'ZCCA39', 'ZRMA29', 'ZRMA43', 'ZRMA44', 'ZRMA45',
'ZRMA71', 'ZRMA09', 'ZRMA99', 'Z1200N', 'ZTEC14', 'ZRMA81', 'ZRMA82', 'ZRMA83', 'ZRMA85',
'ZRMA86', 'ZRMA87', 'ZRMA88', 'ZRMA89', 'ZRMA90', 'ZRMA92')
and
(e.name_related = '' || e.name_related like '.' || e.name_related like ','
|| e.name_related like '%x%' || e.d_name='' || e.d_name like '.' || e.d_name like ','
|| e.d_name like '%x%' || e.father_name='' || e.father_name like '.'
|| e.father_name like ',' || e.father_name like '%x%' || e.d_father_name=''
|| e.d_father_name like '.' || e.d_father_name like ',' || e.d_father_name like '%x%'
)

Following is the error message:

  ERROR:  syntax error at or near "like"
  LINE 3: ...= '' || e.name_related like '.' || e.name_related like ',' |...

I googled a lot, but did not find a good solution for this.
Please help if you can.
Thanks in advance.

|| is the operator to concatenate strings in SQL. You probably want OR.

Additionally: LIKE without a wildcard does not make sense. e.name_related like ',' is the same as e.name_related = ','. Multiple ORs are the same as IN so you can simplify your condition to:

(
  e.name_related IN ('', ',', '.') OR e.name_related like '%x%' OR
  e.d_name in ('', '.', ',') OR e.d_name like '%x%' OR
  e.father_name IN ('', '.', ',') OR e.father_name like '%x%' OR
  e.d_father_name IN ('', '.', ',') OR e.d_father_name like '%x%'
)


how to get a sorted result from mysql query? I am trying to get a sorted result from a MySQL query and I don't have any idea of making the query.

I have a example MySQL table as follows :

 id | cat_type
 1  |  free
 2  |  free
 3  |  free
 4  |  free
 5  |  paid
 6  |  paid
... |  ...
... |  ...
... |  ...
   and it goes on.

The result should be as follows from the above table :

 id | cat_type
 1  | free
 2  | free
 5  | paid

 3  | free
 4  | free
 6  | paid

... | free
... | free
... | paid
  and goes on(should be sorted)

The total result should be split as 3. In the first three result, first two should be free and the next one should be paid likewise all the remaining result should be sorted as two free and one paid.

The most important is that it should be sorted as two free and one paid.

Any idea how to accomplish this using a query?

I suppose you could do something like this from the command line/phpmyadmin:

Assuming the following table:

mysql> SELECT * FROM test;
+----+----------+
| id | cat_type |
+----+----------+
|  1 | free     |
|  2 | free     |
|  3 | free     |
|  4 | free     |
|  5 | paid     |
|  6 | paid     |
+----+----------+
6 rows in set (0.00 sec)

Make your custom ordering by selecting twice the table and using a variable as a counter:

mysql> SET @i := 0;
SET @j := 0.5;
SELECT id, cat_type FROM
(SELECT @i := @i + 1 as ordering, id, cat_type FROM test WHERE cat_type = 'free'
 UNION
 SELECT @j := @j + 2 as ordering, id, cat_type FROM test WHERE cat_type = 'paid')
AS base_table
ORDER BY ordering;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+----+----------+
| id | cat_type |
+----+----------+
|  1 | free     |
|  2 | free     |
|  5 | paid     |
|  3 | free     |
|  4 | free     |
|  6 | paid     |
+----+----------+
6 rows in set (0.00 sec)

This is technically incorrect, as it could explore the original tables not in id order, but assuming you are using InnoDB, a full table scan in index order should be performed.

Also, this is terribly unoptimized:

+----+-------------------+------------+------+---------------+------+---------+------+------+-----------------+
| id | select_type       | table      | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+----+-------------------+------------+------+---------------+------+---------+------+------+-----------------+
|  1 | PRIMARY           | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |   12 | Using filesort  |
|  2 | DERIVED           | test       | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where     |
|  3 | UNCACHEABLE UNION | test       | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where     |
| NULL | UNION RESULT      | <union2,3> | ALL  | NULL          | NULL | NULL    | NULL | NULL | Using temporary |
+----+-------------------+------------+------+---------------+------+---------+------+------+-----------------+
4 rows in set (0.00 sec)

To the point that if you wanted to do this in a large table, doing it programatically or using cursors would be a better idea.


Deadlock killing update statement when collided with select In my recent issues.. I have noticed a dead lock killing an update statement rather than a select command which was locked in it.

The SELECT's isolation level was at the database default, which is read committed snapshot isolation.

The SELECT is part of a transaction in our ETL.

Yes, SELECTs need locks, and they can win deadlocks if they're involved in a transaction that's done a lot of work leading up to that point.

To see it, go into a database with RCSI turned on, and create a couple of tables - only one of them will have data in it:

CREATE TABLE dbo.Table1 (ID INT IDENTITY(1,1), Things VARCHAR(50));
GO
CREATE TABLE dbo.Table2 (ID INT IDENTITY(1,1), Things VARCHAR(50));
GO
/* Only Table1 will have data: */
INSERT INTO dbo.Table1 (Things)
  SELECT 'Meaningless Stuff'
  FROM sys.all_columns
GO

Then start a transaction that does a lot of work on the table with data:

/* WINDOW #1: Start a transaction that does a lot of work */
BEGIN TRAN
UPDATE dbo.Table1
  SET Things = 'Different Stuff';
GO

Now start a NEW TAB in SSMS so that you're in a different session - we're going to call this Window #2 - and run:

/* WINDOW #2: Start a transaction that does
just a little work on an empty table: */
BEGIN TRAN
ALTER TABLE dbo.Table2 ADD NewCol VARCHAR(10);
GO

Both transactions in the two different windows are working on two different tables. So far, so good. Now continuing in Window #2, run an update on Window #1's table:

/* WINDOW #2: Everything's cool. Now try an update that gets blocked: */
UPDATE dbo.Table1 SET Things = 'Even more different stuff';

That update just hangs - he's blocked. Don't cancel him - leave him running. He's blocked by the Window #1 transaction. In RCSI, writers still block each other when they're trying to work on the same rows.

Switch back over to Window #1, and run:

/* WINDOW #1: Run a select */
SELECT * FROM dbo.Table2;

And count to 5. Within 5 seconds, SQL Server wakes up the deadlock monitor, who looks around, finds that these two queries are blocking each other, and...the select wins! The select returns data, and the update's window says:

Msg 1205, Level 13, State 56, Line 9
Transaction (Process ID 54) was deadlocked on lock resources
with another process and has been chosen as the deadlock victim.
Rerun the transaction.

Why? Because SQL Server by default chooses the victim by whichever transaction is the easiest to roll back. In our case, Window #1 had done a lot of work already, updating thousands of rows, whereas Window #2 had done just a tiny amount of work.

This is a contrived example in order to teach the lesson quickly - your scenario probably doesn't involve an ALTER TABLE command. Still, the same theory applies.


Help preparing for MySQL Master Slave Replication I am not in the best situation. I inherited an Ubuntu 14.04 8 GB RAM, 8 CPU MySQL 5.5 database server with almost 400 GB of business-critical data (stored on external SSD) contained within several thousand different databases. My database administration skills and experience are nascent. I want to create a backup of this data to set up MySQL Replication, but I need to create the backup with minimized impact and downtime.

These databases are individually backed up with mysqldump about every four hours. This unfortunately means that I have no single, point-in-time, logical or raw backup of the entire database server and to top it off, binary logging is not enabled on that server. But I do have the capability to individually restore these backups.

In total, there about 250,000 tables in the database server. Of those tables, about 90,000 use the myisam engine and about 160,000 use the innodb engine.

I know there will be some downtime but I would really just like to avoid having downtime of an unknown duration during which I am obliged fully backup the data and deploy replication at the same time.

In testing, I've given thought to or tried various approaches:

using Percona Xtrabackup
using mysqldump with a single transaction (for innodb) and no locks for the myisam tables
rsync'ing the mysql data directory, then gracefully shutting down the MySQL server, and rsync'ing the flushed out changes
converting the myisam tables to innodb, then doing a mysqldump or using xtrabackup
using my existing backups to start replication, then letting the slave catch up
restoring my existing backups, then syncing the changes with pt-table-checksum and pt-table sync
and the list can go on...

Without me providing excessive detail about my testing methods and results, I would like to know how you would approach this situation.

EDIT: In essence, my question is: With the goal of minimal downtime and given my scenario, how would you create a backup of the database server in anticipation of setting up MySQL Replication?

I would appreciate any advice, opinions, services, or resources you may have. Thank you.

Common question, common answer.  The first time you set it up it is painful.  But, if you do it one of several 'right' ways, the subsequent backups will be less painful.

LVM -- requires OS involvement.  But backup takes about 1 minute, regardless of data size.

Replication -- requires a full copy.  Xtrabackup can do it relatively painless for InnoDB, but blocks for MyISAM.  Corollary:  You should move away from MyISAM.

Disk sync (via rsync or whatever) -- takes long for 400GB, but it is clean.  You must shutdown MySQL during the operation.  (LVM would make it short.)

If you have an adequate backup, and can build a Slave from it, then that may be your best bet.  In the future, take backups from the Slave.  (Even better, clone one Slave from anothor.)

Yes, once a Slave exists, pt-table-checksums and pt-table-sync are a good idea.

Also, you need to move from 5.5 to 5.6 to 5.7.  It won't be long before 5.5 is no longer supported.


Using OLE DB Source SQL command vs desinging the query using the object tools In SSIS, we can use the OLE DB Source SQL command to run a T-SQL script on the database and then use the output as the source. For example the a complex query can be wrapped in the OLD DB source SQL command and the output can be directly written to a file or target table. This way, we don't have to design the dataflow to do the Merge joins, lookups, etc etc.

I'm wondering if there is any disadvantage on this approach vs, defining the sql query in SSIS design tool (using the available objects in toolbox). It seems, it takes less time to get the job done and possibly faster to run.

Appreciate if you share your idea on pros and cons of this approach.

Thanks.

I'm having a hard time thinking about any advantage to laying it out in SSIS other than being able to visualize the logic behind the query. Which is something I would expect developers to be able to do by looking at a query, not a design surface, anyway.

The main disadvantage of doing that is that you're effectively overriding/bypassing the Query Optimizer by dictating the physical operations yourself. If cardinality estimates change behind the scenes, the physical operations that exist in the SSIS design surface may no longer be optimal to service the query.

Had the query been serviced all at once through the normal pipeline:

You gain the insights and dynamicism of the Query Optimizer
It's much easier to develop, debug, and test
It's easy to get a sense of what the process is doing (I don't want to have to open 1,000 Property dialogs to figure out where the data is coming from)
Plan cache bloat is minimized, since fewer plans will end up there
The number of roundtrips to the server is minimized, thus improving performance
This is the expected way to do it, which greatly reduces the WTF Factor

I would really only encourage using the SSIS operators to do joins if the data is coming from multiple disparate sources (the real power of SSIS!), or if you can prove that the performance will be better doing it that way (subject to the caveats above).

Even so, I would still attempt to group things as much as possible into single queries (i.e., one query for sales data, one for inventory, etc.). Depending on your environment, all the business and query logic you need could be placed inside stored procedures, views, etc., of the source database(s); this greatly simplifies the amount of SQL code that ends up in SSIS, and I highly recommend doing this if you can.


How Many Times Does a CTE run If I have a cte such as the below code. How many times does the table People get queried against? I was under the impression that it was only called 1 time and stored in memory but some of my queries I have been running seem to be running a lot longer than they should be. Which leads me to believe that it may be hitting the People Table 3 times.

with ctegeneric as (select person from people where person = 'dumb')
Select * from ctegeneric
UNION ALL
Select * from ctegeneric
UNION ALL
Select * from ctegeneric

Putting aside the syntax was not correct (before the edit).

NO it does not get put in memory.

A proper example is a join. It will get call multiple times in a loop join.  On an expensive CTE called multiple times then materialize to #temp is the way to go.


attach database - and rebuild log AT NEW LOCATION I have a running database that I cannot take offline. I also have an mdf-file copy of that same database (snapshot of a HDD backup taken at earlier time), but no ldf file.

I need to attach this database MDF file under a new name on the same server.

So I need to attach the DB without the log file and rebuild the log file at a new location (b/c the current log file location is obviously in use).

How do I do that? I tried this, but didn't work:

CREATE DATABASE [dbname] ON
(FILENAME = N'E:\db_copy.mdf' ), --this file DOES exist
(FILENAME = N'E:\log_copy.ldf' ) --this file DOES NOT exist
FOR ATTACH_REBUILD_LOG

You cannot specify the location for the log file on ATTACH_REBUILD_LOG. Per https://msdn.microsoft.com/en-us/library/ms176061.aspx

  the ATTACH_REBUILD_LOG automatically creates a new, 1 MB log file.
  This file is placed in the default log-file location.

As a workaround what you could do in this instance is change the default location for the log files to a new folder, and attach the data file with the rebuild log. Once that is done set the default location back to it's prior setting. Then you can run and ATLER DATABASE command to set the log file to the path/filename that you desire, set the database offline, copy over the log file to that new location, and set the database online again. After that just clean up the old file and folder.


How to set the wal_level in AWS RDS Postgresql? How do I know which wal_level my AWS RDS Postgres 11.1 database has?

I checked 'parameter groups', but was unable to find the wal_level.

Once found, how do I change it?

To find out the wal_level you can use PSQL and execute: show wal_level;
You need to set rds.logical_replication parameter to 1. It will automatically change wal_level to logical.

I found some details here


What are the main disadvantages of adding relationship between tables at later date? During early development I tend to create tables using running numbers as Primary Key, although there is formally a real ID column for each table.
I tend to forget mapping the relationship between table and if I'm not wrong, this can be added later even after the table is no longer empty. This is also true for setting Index column.
I know this is probably not the best way to setup a new database for application but is there any permanent pitfall where the damage done might be irreversible which makes this habit potentially "deadly"?
Thanks.

is there any permanent pitfall where the damage done might be irreversible which makes this habit potentially "deadly"?

Short answer: No.
Very little you can do to your database is "deadly".
That said, changing Primary and Foreign keys is a pain in most DBMSs and should be avoided, if possible.
Also, you'll need indexes to support all of your new key fields (and the foreign key fields that reference them) and building all of those indexes is likely to take some time and may cause contention problems with a running application, so you'll probably want to do this in a Maintenance Window when you can keep everyone/everything else safely out of the way.

I tend to create tables using running numbers as Primary Key, although there is formally a real ID column for each table.

I'd say that was a Bad Idea.
If you have a Natural, Primary key already, then I'd recommend using it.
Why duplicate the effort to reinvent your own, surrogate key?  Plus, you have to create extra, unique Indexes on the Natural key anyway, which you get "for free" with the Primary Key.


SQL Insert multiple values with UNIQUE constraint I have a MS SQL Server 2012 and the following table:

CREATE TABLE dzp.contractid (
id bigint PRIMARY KEY IDENTITY(1,1),
VNR char(18) NOT NULL,
CONSTRAINT UC_VNR UNIQUE (VNR))

For context, VNR represents a unique contract number in string form.

I want to import a csv file with contract information, with possibly multiple rows per contract. To avoid querying over a char(18), I am trying to normalize the data upon loading using the INSERT-Trigger of the staging table.

Flow is:

csv-file --> staging table --> insert trigger --> normalized tables

In my trigger, I am attempting the following:

BEGIN TRY
    INSERT INTO dzp.contractid(VNR)
    SELECT VNR
    FROM dzp.accounts_stage
END TRY

BEGIN CATCH
-- ignore unique constraint violation error, raise otherwise
IF ERROR_NUMBER() <> 2627
    RAISERROR ('blah', 16, 1)
END CATCH

So what I hope to achieve is basically:

Get all the contract numbers from the staging table
Add all these numbers to the normalized table
Ignore the error that gets thrown when the unique constraint is violated
When working correctly, I now have a table with all the unique VNRs

Problem:

The Uniqueness-Constraint seems to be blocking the entire INSERT, not only the values that are actually duplicates.

Is there some way of inserting data row-wise, or tell the constraint to treat it on a row-by-row-basis?

Is this even the right approach? I'm happy to completely change the approach, since I cannot imagine I'm the first to have this issue... For context, this is essentially a reporting database that gets batch-fed once per day, with about 1m rows in total (from the csv-table), and has ~5 users running queries from time to time.

Thanks for your input!

SQL constraint violation affects the entire statement, you cannot ignore individual rows.

What you can do is insert only the rows that don't violate the constraint. There are several ways to do that, for example

INSERT INTO dzp.contractid(vnr)
SELECT DISTINCT vnr FROM dzp.accounts_stage s
WHERE NOT EXISTS (SELECT 1 FROM dzp.contractid WHERE vnr = s.vnr)

You could also use MERGE:

MERGE dzp.contractid AS t
USING dzp.accounts_stage AS s
ON (t.vnr = s.vnr)
WHEN NOT MATCHED BY TARGET
THEN INSERT(vnr) VALUES(s.vnr)


How to create a trigger that update the date field and time field upon modify of the row? I have created a table testtable inside the database testbase that have the following structure:

product_no (int, not null)
product_name (varchar(30), not null)
price (money, null)
expire_date (date, null)
expire_time (time(7), null)

However, how do I write a trigger such that it will update that particular row that has been modified (or updated with new information) and record the modification date into the expire_date field and the modification time into the expire_time field? (or if this is possible?)

It would be fairly easy but I would actually recommend changing the logic of the command that is inserting/updating the data so that it adds the additional information at this point.

However, should you wish to proceed with a trigger you could do something like this:

create trigger my_trigger
on my_table
after insert, update
as
begin
declare @dt datetime = getdate();
update a
set expire_date = CONVERT(DATE, @dt, 101),
    expire_time = CONVERT(TIME, @dt)
from my_table as a
join inserted as b
on a.product_no = b.product_no;
end
go

This uses the INSERTED table to find out the product_no of the rows that have been changed/created. You can find out more about triggers at the link below:

http://msdn.microsoft.com/en-us/library/ms189799.aspx

I hope this helps you.


Getting both Having and Not Having in same row I have a query which I get the customers that have more than 1 orders
However, I would like to get the number of customer that has only 1 orders as well and divide them like 1 orders / more than 1 order

So that my table will have 3 colums

one_order, more_than_one_order, divided

I tried different approaches like duplicating the query like =1 but it is not the proper way

Query

SELECT COUNT(*)
FROM (
select customer_id from orders GROUP BY customer_id HAVING COUNT(order_id)>1
) A

You can use a case expression like:

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

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

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

For the third attribute you can either reuse the aggregates:

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

or add another level of nesting:

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


What Backup Compression Rate Can I Expect for a Database with Mostly JPG Data? I am considering using a database to store one or more images (.JPG) associated with a record identifier and filename.

CREATE TABLE Images (
    RecordID BIGINT NOT NULL,
    ImageFilename VARCHAR(50) NOT NULL,
    ImageData VARBINARY(MAX) --expected avg. size: 256K
)

I anticipate that 90% of the space utilization on the database will be coming from the BLOBs. I know that SQL Server data compression will not compress the BLOB data through row or page compression. Can I expect any savings when compressing the backups, other than the compression taking place on the RecordID and ImageFilename fields?

I finally went ahead and got a test database loaded with data. Table structure is:

CREATE TABLE [dbo].[ImageTable](
    [CompositeID] [bigint] NOT NULL,
    [ImageID] [tinyint] NOT NULL,
    [FullImage] [varbinary](max) NULL,
    [ThumbnailImage] [varbinary](max) NULL,
    CONSTRAINT [XPK_ImageTable] PRIMARY KEY CLUSTERED ([CompositeID] ASC,[ImageID] ASC)
)

The table was loaded with approximately 2.8M records. The FullImage value was set, and the ThumbnailImage value was left as NULL for each record.

The database itself is 154GB in size.
The uncompressed backup is 154GB in size.
The compressed backup is 134GB in size.

Given that the two PK fields account for approximately 24GB (2.8M records * 9 bytes/record), it looks like the only savings from compression are being realized by compressing the PK fields.


How to JOIN two tables with substring? I update JOINed tables as

UPDATE table1 a
JOIN table2 b
ON a.col1 LIKE CONCAT('%', '.', b.col2)
SET a.foreign_id=b.foreign_id

but LIKE is very slow for JOIN. Is there another way based on faster substring functions to make the JOIN?

The leading wildcard in the like, not the function, is preventing the use of the table1.col1 index (assuming it is indexed).

For an indexed approach, the values can be store reversed...

update table1 set col1=reverse(col1);
alter table1 add key (col1);  -- if not already indexed

update table2 set col2=reverse(col2);

update table1 a
join table2 b    on a.col1 like concat(b.col2,'.%')
set a.foreign_id=b.foreign_id;

Of course, this comes with the downside that the column will need to be reversed for display...

select reverse(col1) from table1 where...

Here is a similar denormalized approach with the reversed value redundantly stored in the same table...

alter table table1 add column col1_rev -- col1 datatype specification here;
update table1 set col1_rev=reverse(col1);
alter table table1 add key (col1_rev);

alter table table2 add column col2_rev -- col2 datatype specification here;
update table2 set col2_rev=reverse(col2);
alter table table2 add key (col2_rev);

update table1 a
  join table2 b on a.col1_rev like concat(b.col2_rev,'.%')
    set a.foreign_id=b.foreign_id;

A normalized approach would be another table of unique table1.col1 values as stored and reversed...

create table table1_col1_rev as
select distinct col1
     , reverse(col1) as col1_rev
  from table1;

alter table table1_col1_rev
  add key (col1)
        , (col1_rev);

update table1 a
     , table1_col1_rev b
     , table2 c
   set a.foreign_id=c.foreign_id
 where b.col1_rev like reverse(concat('%.',c.col2))
   and a.col1=b.col1;


Optimize query that accoridng to plan produces over 500 billions rows - Actual result is only 400k rows I'm trying to improve DB performance. I'v noticed a query that according to the plan it produces a lot of rows but i know for fact there are no more than 400k rows in the table after that insert. Why there is such a big difference between actuall plan and the query result?
Query plan is here https://www.brentozar.com/pastetheplan/?id=rJWX327pB

I cannot understand why the query is so complex.

Query text taken from published plan:

with Object1 as
(
select Object2.Column1 as Column2
from Schema1.Object3 as Object2
left outer join Schema1.Object4 as Object5
    on Object2.Column1 = Object5.Column1
where Object5.Column1 is null
)
insert into Schema1.Object4
select * from Schema1.Object3
where Column1 in ( select Column2
                   from Object1 )

Let's think.

What do you do in CTE? you select records from Schema1.Object3 which have no according record in Schema1.Object4.

What records are inserted in main query? those which were selected in CTE.

So CTE seems to be excess, and the query may be simplified to

insert into Schema1.Object4
select Schema1.Object3.*
from Schema1.Object3
left outer join Schema1.Object4
    on Schema1.Object3.Column1 = Schema1.Object4.Column1
where Schema1.Object4.Column1 is null

or

insert into Schema1.Object4
select Schema1.Object3.*
from Schema1.Object3
where not exists ( select 1
                   from Schema1.Object4
                   where Schema1.Object3.Column1 = Schema1.Object4.Column1 )

Please check - does I'm right?


Create, Drop permissions I would like to grant create, select, update, drop permissions only for user USER_NAME for a particular schema SCHEMA_NAME. It is possible?

Try this:

GRANT select, update on all tables in schema SCHEMANAME to ROLE;
GRANT select, update on all sequences in schema SCHEMANAME to ROLE;
GRANT create on schema SCHEMANAME to ROLE;

You cannot provide grants to drop an object to any other user besides its owner. Only the superuser or owner has the permission to drop the object.


Table variable not Recognized in stored procedure The following stored procedure for a postgresql database attempts to insert into a temporary table 'result' of type result_load_status ,which is a table in database, and return this temporary table. When function is executed, the output window shows a message 'result does not exist'.
How to fix it or any other way it should be done?

Following is the definition of stored procedure:

create or replace function trial5(table_name text)
returns table(up_link double precision,time_stamp timestamp without time zone) as $$
declare b integer default 1;
declare c integer default 2;
declare result result_load_status;
begin
execute 'select ' || table_name ||'.up_link,' || table_name || '.time_stamp from' || table_name || ' where bar2= ' || b into result;
execute 'select ' || table_name ||'.up_link,' || table_name || '.time_stamp from' || table_name || ' where bar2= ' || c into result;

return query select * from result;
end

$$ language plpgsql;

Error Message:

  ERROR:  relation "result" does not exist
  LINE 1: select * from result

There are quite a few problems with this code:

Consecutive calls of select ... into result do no accumulate rows into result, each one overwrites whatever was previously in result.
A variable typed after a table (result of type result_load_status here) is meant to contain only one row of this type, not an entire resultset. For an entire resultset, use CREATE TEMPORARY TABLE or a cursor if possible.
select * from name_of_a_variable does not exist as a valid construct. A temporary table instead of a variable's name would work.

Anyway since accumulating results is already built in RETURN QUERY, you don't need any of the above.

Quoting from the documentation:

  RETURN QUERY appends the results of executing a query to the
  function's result set

So you may just write:

RETURN QUERY execute 'select ' || table_name ||'.up_link,' || table_name || '.time_stamp from' || table_name || ' where bar2= ' || b;

RETURN QUERY execute 'select ' || table_name ||'.up_link,' || table_name || '.time_stamp from' || table_name || ' where bar2= ' || c;

without any need for the problematic result variable.


How to run Sql Server stored procedure in Async mode? Is there a way to run SQL Server 2019 stored procedures with parameters asynchronously? Calls to this SP will be made several times a day, i.e. this is not one time requirement.
Service Broker can be one option, but this is 15+ years old technology and Microsoft did not try to enhance its functionality.
Agent job can be an option, but I want to submit a job on demand "as and when required". Plus, the  stored proc has parameters as well.
I came from Oracle background and there are multiple ways to submit a background job to the Database engine and it will run the job in Async manner.

The simplest way is to call sp_start_job and kick off an unscheduled SQL Agent Job.  Parameter values can be stored in a table that the job reads from before calling the procedure.


creating an inventory management system app for creating a inventory management app , i designed this database i want to store the product bought in the inventory as a positive value and the product sold as a negative value o nthe inventory so i can know the quantity remaining here is the database design

its first time creating a database , does it answer my need ?

and the product sold as a negative value

I personally wouldn't do this - it does not reflect reality. You can't have -25 widgets. You can have 10 in an inventory table and 15 in an orders (active) and/or sold (completed orders) table. You can have negative numbers - your bank account can have -25€ - this makes sense.

Take a look at http://www.databaseanswers.org/data_models/ for example inventory designs. -  Vérace

Your scheme has joining rings, it is excessive, and data may be inconsistent. It is possible to have a joined record pair with purshases.purshaseid = inventory.purshaseid and purshases.productid <> inventory.productid.

I think purshases.productid is excess and must be removed. Its value must be selected from joined record in inventory table. And the same with inventory-product-orders ring. - Akina


Stored Procedure produce data for a range of dates I have this table:

PRJ_ID | START      | FINISH
xxxx   | 2015-01-01 | 2016-01-01

I have two separates date start_range and end_range.

I would like to produce the following output using a stored procedure in SQL Server 2008 for example with this parameters start_range=2015-02-01 and end_range=2016-08-01:

PRJ_ID |   DATE     | STATUS
xxxx   | 2015-02-01 | ACTIVE
xxxx   | 2015-03-01 | ACTIVE
xxxx   | 2015-04-01 | ACTIVE
xxxx   | 2015-05-01 | ACTIVE
.
.
.
xxxx   | 2016-08-01 | NOT ACTIVE

Where the DATE field should be the first day of the month for each month from the start_range to the end_range

Having this parameters start_range=2015-02-01 and end_range=2016-08-01, we can generate a list of dates, recursively (see ;WITH cteDates).
This part could be replaced with a calendar table , if you have it.

DECLARE @dStart_Range AS DATE = '2015-02-01',
        @dEnd_Range AS DATE = '2016-08-01'

DECLARE @tPRJ AS TABLE
    (
        PRJ_ID CHAR(4) NOT NULL,
        START DATE NOT NULL,
        FINISH DATE NOT NULL,
        PRIMARY KEY CLUSTERED (PRJ_ID)
    );
INSERT INTO @tPRJ(PRJ_ID,START,FINISH)
VALUES('xxxx','2015-01-01','2016-01-01');

-- generate the list of dates
-- could be replaced by a calendar table if exists
;WITH cteDates
AS(
    SELECT @dStart_Range AS aDates
    UNION ALL
    SELECT DATEADD(MONTH,1,aDates)
    FROM cteDates
    WHERE
        aDates < @dEnd_Range
)
--SELECT * FROM cteDates

SELECT
    P.PRJ_ID AS [PRJ_ID],
    D.aDates AS [DATE],
    CASE WHEN P.START <= D.aDates AND P.FINISH>= D.aDates THEN 'ACTIVE'
         ELSE 'NOT ACTIVE' END AS [STATUS]
FROM
    cteDates AS D
    CROSS JOIN  @tPRJ AS P

And the result is:

PRJ_ID DATE       STATUS
xxxx   2015-02-01 ACTIVE
xxxx   2015-03-01 ACTIVE
xxxx   2015-04-01 ACTIVE
xxxx   2015-05-01 ACTIVE
xxxx   2015-06-01 ACTIVE
xxxx   2015-07-01 ACTIVE
xxxx   2015-08-01 ACTIVE
xxxx   2015-09-01 ACTIVE
xxxx   2015-10-01 ACTIVE
xxxx   2015-11-01 ACTIVE
xxxx   2015-12-01 ACTIVE
xxxx   2016-01-01 ACTIVE
xxxx   2016-02-01 NOT ACTIVE
xxxx   2016-03-01 NOT ACTIVE
xxxx   2016-04-01 NOT ACTIVE
xxxx   2016-05-01 NOT ACTIVE
xxxx   2016-06-01 NOT ACTIVE
xxxx   2016-07-01 NOT ACTIVE
xxxx   2016-08-01 NOT ACTIVE


MySQL: To use MYISAM or INNODB engine? (plot twist enclosed) We use INNODB for all of our tables, but I have one special table, a 100+ GB reference table (only read-queries against it).

We are loading changes to this table in a nightly batch job using LOAD DATA INFILE.

MyISAM is more compact and efficient for a reference table. But this would be the ONLY MyISAM table we use.

The workload on the rest of the tables is typical web application except for 1 table which has a heavy write load and normal read load.

Question 1 - Is it worth the tradeoff of dedicating memory to the MyISAM cache for just this one table, when I know I need a reasonably amount of memory dedicated to the InnoDB cache for the other workload usecases?
Question 2 - When I run LOAD DATA INFILE 'from_nightly_batch' REPLACE INTO TABLE mytable, will MyISAM lock the table for reads? Will InnoDB?  e.g. can I use the reference table while I do the nightly update in either engine?

Here is something to really think about

MyISAM only caches indexes, so there is always disk I/O. InnoDB caches data and indexes. If you could guarantee that every query you call against this one table can only be fetch necessary columns and all columns can be fetched from the index only (called a covering index)

Here are some nice links on Covering Indexes:

http://peter-zaitsev.livejournal.com/6949.html
http://www.mysqlperformanceblog.com/2006/11/23/covering-index-and-prefix-indexes/
http://ronaldbradford.com/blog/tag/covering-index/

MyISAM can do shared reads. So can InnoDB.

Please remember that MyISAM inserts, updates, and deletes perform full tables locks each time (that can be the critical path for disk I/O in making InnoDB wait its turn in certain circumstances). If the table is ready-heavy, you may want to use a dedicated MyISAM key cache.

Heavy INSERTs from LOAD DATA INFILE will lock the MyISAM table. InnoDB can tolerate it better but check the behavior of LOAD DATA INFILE to see if it locks the table regardless of Storage Engine.

The tradeoff depends upon how often you write to the MyISAM table.

PLEASE DO NOT JOIN THE MyISAM TABLE TO ANY INNODB TABLE !!! The locking is clumsy and MyISAM changes cannot rollback.


How to store and work from a Postgresql database on an external hard drive? I have installed PostgreSQL 13.1 on two computers, a laptop and desktop.
I created database using a Tablespace on a portable SSD drive under F:\MyProject\DB\PG_13_202007201\20350. So I expect all the database files to be in there somewhere.
I want to work on the database on the SSD drive which is fine on the laptop that created the database, but when I go to my desktop I can't figure out how to attach (SQL Server style) the existing database/tablespace and continue working on it.
Is there a way to work like this with Postgresql?

I suggest you to create a new cluster in your external drive. You can use initdb
to create a new PostgreSQL database cluster.
Here is a sample command;
C:\Program Files\PostgreSQL\13\bin\initdb --pgdata="F:\MyProject\pgdata\13\main"

After that you even can register your new cluster as a new postgresql server service by using pg_ctl like the one below;
C:\Program Files\PostgreSQL\13\bin\pg_ctl register --pgdata="F:\MyProject\pgdata\13\main"


SQL Server: query for getting TOP X groups of records which have highest subsequent values I'm querying each SQL Server DB CPU usage (based on query from here  -- Get CPU utilization by database (Query 29) (CPU Usage by Database)) every 60 seconds and storing this information in dedicated table for that purpose with columns:

id, dbname, date, usage

Now I'm interested in querying this DB for databases which are using most CPU usage but only if DB usage X subsequent reads where higher than Y (so I'm not interested in DB which had CPU usage spikes).

Example:

DB1:

time1 - 0
time2 - 35
time3 - 0
time4 - 1

DB2:

time1 - 9
time2 - 8
time3 - 7
time4 - 11

In this example I'm not interested in DB1 because it had only one CPU spike and it's average CPU usage is low. DB2 on the other hand has average higher CPU usage in subsequent reads and this type of DB I'm trying to find in my usage table.

Thank you

EDIT: My mistake. Version 2012

If you run this:

select dbname, AVG(usage), STDEV(usage) from <your table> group by dbname

You'll notice that while the average is the same for both databases, the one you'd be interested is the one where the standard deviation is lower.  So what you'd want to do is then filter this by a certain threshold average usage or higher and a certain standard deviation or lower.


SQLite function to specify exactly how the data is stored internally I have used the typeof() function within SQLite before (http://sqlfiddle.com/#!7/fcf0e/1), but I would like to go a little further this time.

I know that INTEGER can be stored internally as 1, 2, 3, 4, and 6 bytes.

What I am wondering is if I can use a function to identify how the value is actually stored internally?

I am looking for something more than just INTEGER, something like INTEGER 4-byte would be great or just 4-byte

The storage size has no effect at the SQL language level, so there is no SQL function to determine it.

However, the record format is documented; SQLite always uses the smallest serial type into which the value fits.


Does having many databases affect MySQL performance? I have huge unused databases in MySQL. Do they affect my MySQL engine performance even if they are unused? Do they consume memory on their own?

Thanks

The only performance hit I can see is on the INFORMATION_SCHEMA database (See my post How is INFORMATION_SCHEMA implemented in MySQL?)

If you have queries that poll for the existence of tables, gets the table sizes (Example: See my post MySQL Workbench Database Sizes), and things of this nature, MySQL will spend time sifting through the OS for such metadata. Getting metadata on InnoDB tables and partitioned MyISAM tables will make the INFORMATION_SCHEMA take the worst hit as it must open many file handles.

Other than that, you regular queries on your working dataset should be fine.


Best way to implement an insert-only table to prevent updating huge number of fields in SQL Server I'm going to have a table that holds pricing data of a lot of hotels, and this data will be updated regularly, say every one hour. I have a terrible experience for such a scenario, that we have to update a lot of rows, that causes deadlocks and leads to bad performance, so I'm going to avoid updating at all.
I think a good way is to insert every newly received price record, and specify some kind of version for them. So the table becomes something like this:
================================
RoomId | Date  | Price | Created
================================
1      | 29Dec | $1000 | 12:00
1      | 29Dec | $1200 | 12:30
1      | 29Dec | $1300 | 12:40
...

So when I'm going to query the current price, the row with the largest version (grouped by RoomId and Date) will be the desired result. The query is probably something like this:
SELECT * FROM (
    SELECT RoomId, Date, Price, ROW_NUMBER() OVER (PARTITION BY RoomId, Date ORDER BY Created DESC) AS rn
    FROM Prices
) s
WHERE rn = 1

I'm not sure about this way being the best way, and also not sure about the structure of table and the appropriate query to achieve that.
Any ideas would be appreciated.

So if you are using an ORM I'd suggest that in this particular case you point the actual operation to a stored procedure instead of allowing the ORM to create the insert/update/delete statements (although this is preferable in general).  If you're using Entity Framework, this is fairly simple.  If the ORM doesn't support using your own SQL for create/update/delete, don't use it.
So our requirements are:

For a given date, a room has one price.
Prices for a room/date can be changed

This leaves two options:

Create an audit table to store changes resulting from updates, set up our stored procedure to enforce the audit rules
Use the table to store the current a past values, set up our stored procedure to insert new rows only when necessary

The first option works, but if you need a lot of concurrency or need to query past prices as valid values, the second option works better.  It begins like this:
CREATE TABLE Room
(
  RoomId  INT NOT NULL
  /* Other stuff */
 ,CONSTRAINT PK_Room PRIMARY KEY (RoomId)
)
;

CREATE TABLE RoomPrice
(
  RoomId      INT           NOT NULL
 ,StartDt     DATE          NOT NULL
 ,Price       DECIMAL(9,2)  NOT NULL
 ,UpdateDtm   DATETIME2(2)  NOT NULL
 ,CONSTRAINT FK_RoomPrice_Charged_For_Room FOREIGN KEY (RoomId) REFERENCES Room (RoomId)
 ,CONSTRAINT PK_RoomPrice PRIMARY KEY (RoomId, UpdateDtm)
 ,CONSTRAINT CK_RoomPrice_Price_Is_GT_Zero CHECK (Price > 0.00)
)
;

Now the tricky part, which is ensuring everything behaves how we expect it should.  This is actually embedded in the table design, but it's not obvious without some explanation.  The rules are:

When a new row is inserted, it is the most recent version.  That price applies to all dates on or after the StartDate.
When a price changes, if the new StartDate is greater than or equal to all prior StartDates and greater than or equal to the current date1, the insert is valid.
When a price changes, if the new StartDate is greater than or equal to the current date but not greater than all prior StartDates, the insert is still valid, but all rows with a StartDate greater than the new StartDate are now invalid.

This is all handled by the following query.  To get a price for room @room as of date @date and point in time @dtm, we would use the following query (which should be wrapped in a stored procedure):
SELECT
  RP.Price
FROM
  RoomPrice RP
WHERE
  RP.RoomId = @room
    AND RP.UpdateDtm =
      (
        SELECT
          MAX(UpdateDtm)
        FROM
          RoomPrice
        WHERE
          RoomId = RP.RoomId
            AND UpdateDtm <= @dtm
            AND StartDt <= @date
      )

To set/update a price we would create a procedure like this (h/t to Erland Sommarskog's excellent blog post on error handling):
CREATE PROCEDURE RoomPrice_Set
  @RoomId     INT
 ,@StartDt    DATE
 ,@Price      DECIMAL(9,2)
AS
  SET NOCOUNT, XACT_ABORT ON;

  /* Need to put some time zone logic into this */
  IF @StartDt < SYSDATETIME() THROW 50001, 'Date must be greater than or equal to current date',1;
  ELSE
    BEGIN TRY
      BEGIN TRANSACTION
      INSERT INTO RoomPrice (RoomId,StartDt,Price,UpdateDtm)
      SELECT
        i.RoomId
       ,i.StartDt
       ,i.Price
       ,SYSDATETIME()
      FROM
        (VALUES (@RoomId, @StartDt, @Price)) AS i(RoomId, StartDt, Price)
      LEFT JOIN
        RoomPrice RP
          ON RP.RoomId = i.RoomId
              AND RP.UpdateDtm =
                (
                  SELECT
                    MAX(UpdateDtm)
                  FROM
                    RoomPrice
                  WHERE
                    RoomId = i.RoomId
                      AND StartDt <= i.StartDt
                )
      WHERE
        i.Price <> ISNULL(RP.Price,-1)

      COMMIT TRANSACTION
    END TRY

    BEGIN CATCH
      IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
      ;THROW
    END CATCH
GO

Fiddle here: http://sqlfiddle.com/#!18/c50573/8
1This should be the only scenario, it would be very frowned upon if prices changed after the fact.


When to use MySQL handler syntax? http://dev.mysql.com/doc/refman/5.5/en/handler.html

Seems to be very useful for PK lookups, but I haven't seen much information regarding this. What's the pros and cons of the handler syntax vs. ordinary SQL select for simple key-value queries? How does it compare (performance- and consistence-wise) to HandlerSocket?

This was a weird question. I say was because I addressed this in the past

Jun 17, 2011 : What are your use-cases for HandlerSocket?
Aug 12, 2011 : Which DBMS is good for super-fast reads and a simple data structure?

Basically, the HANDLER syntax is kind of handy if you want to read table data from table while bypassing locking and transactional isolation levels (Yes, this means you should be able to bypass InnoDB's MVCC protocols).


Rewriting query with subquery to improve performance My query looks like that

SELECT topics.name, topics.topic_id, topics.creator,
( SELECT COUNT(*) FROM answers
WHERE answers.topic_id = topics.topic_id)
AS replies FROM topics
ORDER BY topics.bumped DESC, topics.date DESC LIMIT 500

However it takes 0.6s, I'm guessing that's because of the subquery that counts answers. Any other way to achieve this effect with better performance?

topics: INDEX(bumped, date) -- in that order
answers: INDEX(topic_id)

Please provide SHOW CREATE TABLE if you need to discuss this further.

500 is a lot of results -- more than a self-respecting UI should return.  What will you do with them?

Also Consider this approach:

SELECT  topics.name, topics.topic_id, topics.creator, replies.ct
    FROM
    (
        SELECT  topic_id, count(1) AS ct
            FROM  answers
            GROUP BY topic_id
    ) AS replies
    JOIN  topics  ON replies.topic_id = topics.topic_id
    ORDER BY  topics.bumped DESC, topics.date DESC
    LIMIT  500

It may run faster because of reaching into answers only once.

If, however, there could be zero replies in some cases, you need a LEFT JOIN:

SELECT  topics.name, topics.topic_id, topics.creator, replies.ct
    FROM  topics
    LEFT JOIN (
        SELECT  topic_id, count(1) AS ct
            FROM  answers
            GROUP BY topic_id
              ) AS replies  ON replies.topic_id = topics.topic_id
    ORDER BY  topics.bumped DESC, topics.date DESC
    LIMIT  500

This does need a relatively new version of MySQL so that the derived table can automatically be given an index.

Which approach is fastest?  I can't tell; try them all.


SQLServer add a default value for a column I am using MS SQL Server. I have an "action" table with an "action_type" column that I want to set a default value of "to do" for. My DDL in MS SQL Studio errors! I have read many many articles trying to work out what I am doing wrong but nothing seems to work. The DDL is as follows, the table CREATE works, and the ALTER fails.

CREATE TABLE "action"
( "action" INT IDENTITY(1,1)
, "name" VARCHAR(250) NOT NULL
, "owner" VARCHAR(50)
, "action_type" VARCHAR(50) NOT NULL
, "due_date" DATETIME
, "done_date" DATETIME
, "successful" BIT NOT NULL
, "job" INT
, "contract" INT
, "person" INT
, "description" VARCHAR(MAX)
, "deliverable" VARCHAR(MAX)
, "outcome" VARCHAR(MAX)
, "source" VARCHAR(50)
, "notes" VARCHAR(MAX)
);
ALTER TABLE "action"
ADD CONSTRAINT "df_action_0" DEFAULT "To Do" FOR "action_type";

The ALTER statement returns error

  Msg 128, Level 15, State 1, Line 4 The name "To Do" is not permitted
  in this context. Valid expressions are constants, constant
  expressions, and (in some contexts) variables. Column names are not
  permitted.

Based on various articles I have tried replacing "To Do" with 'To Do', N'To Do', ("To Do") and others, nothing works.

It's your quotes. If you remove those, and change your literal to use single quotes, you should be fine. SQL Servers DEFAULT is SET QUOTED_IDENTIFIER ON and with this literals must be delimited by single quotation marks. So, you really only need to change this part:

DEMO

ALTER TABLE "action"
ADD CONSTRAINT "df_action_0" DEFAULT 'To Do' FOR "action_type";

Though, you will more commonly see double quotes not used in this manner, and instead your DDL would be written as:

CREATE TABLE action
( action INT IDENTITY(1,1)
, name VARCHAR(250) NOT NULL
, owner VARCHAR(50)
, action_type VARCHAR(50) NOT NULL
, due_date DATETIME
, done_date DATETIME
, successful BIT NOT NULL
, job INT
, contract INT
, person INT
, description VARCHAR(MAX)
, deliverable VARCHAR(MAX)
, outcome VARCHAR(MAX)
, source VARCHAR(50)
, notes VARCHAR(MAX)
);

ALTER TABLE action
ADD CONSTRAINT df_action_0 DEFAULT 'To Do' FOR action_type;