Schema design for privacy settings in MongoDB I have a set of documents that can have various privacy settings around them:

They can be completed public (any registered user) can view them
They can be viewable only by people following you (this
"Followers" array is stored in each User's document)
They can be private to the person who published the document as well.
They can have custom privacy which allows you to name individual users who can view the document. In addition, you can allow groups of users to also view the document (e.g. there could be a group called "Sample Group" which has 20 users in it. You could allow said group to view the tile.)

I am lost as to how to implement a schema for this efficiently in MongoDB and would love some insight into best practices for implementing this schema.

Your documents can contain some inline privacy section-document or reference to that document in some security collection (for example) where you can specify whether to access.
On the assumption of your problem it can be looks like:

{
    "status":"private", // one of allow-all, private, deny-all
    "deny":[_id('user'),...],
    "allow":[_id('user'),..]
}

where "status" is the main policy for document witch will be handled with your code.
It is just an example. It can be done with many different ways.

Cheers.


Rules related to Transaction count I was reading the examples on MSDN for TRY-CATCH blocks.

The code listing for example B is as follows:

BEGIN TRANSACTION;

BEGIN TRY
    -- Generate a constraint violation error.
    DELETE FROM Production.Product
    WHERE ProductID = 980;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

What I don't understand is why they would bother to check the @@TRANCOUNT in the CATCH block. Is it really possible for the transaction count to be < 1 in this code snippet, or is the guard clause just an example of the principle that examples should be exemplary?

Its a defensive programming practice to only do an action (rollback) if it's possible to do.


How to reset the autoincrement field in Mysql data base? How to reset the autoincrement field in Mysql data base?

You can reset the auto-increment column by using the following command:

ALTER TABLE table_name AUTO_INCREMENT = 10;

You can retrieve the most recent automatically generated AUTO_INCREMENT value with the LAST_INSERT_ID() SQL function.

If you wish to set auto-increment value to be 1, you can also truncate the table data.

TRUNCATE TABLE table_name;

I hope this answer will help you. Thanks.


Aggregate rows of type array I need to aggregate rows with a single column of type INT[] into a single array of type INT[].

The following results in an error "could not find array type for data type integer[]":

create temp table vals (j int[])
on commit drop;
insert into vals
values ('{1,2}'), ('{1,5}'), ('{3,6}');
select array_agg(j) from vals;

The result that I need from the example is a single row that looks like {1,2,1,5,3,6}

The order of records is unimportant as long as the paired numbers remain adjacent to each other in the final array.

I'm out of ideas. How can I get the result that I need?

(PostgreSQL 9.4, Ubuntu 15.04)

Order of source rows irrelevant, source is array column

Your updated requirements are much simpler for two reasons:

We don't need to establish any order among source rows.
And the array already establishes order among array elements.

Simple table:

CREATE TABLE vals (j int[]);

Simple query:

SELECT ARRAY(SELECT elem FROM vals, unnest(j) elem) AS arr;

Order of source rows relevant, source are integer columns

Not as simple. Based on this table:

CREATE TABLE vals (i int, j int);

There are various ways to preserve order. I think this should be among the fastest - with an ARRAY constructor:

SELECT ARRAY(
   SELECT t.elem
   FROM  (SELECT i, j FROM vals ORDER BY i, j) v
        , LATERAL (VALUES (v.i), (v.j)) t(elem)
   ) AS arr;

My first draft had ORDER BY i, j after the LATERAL join, which would not guarantee the order of columns. I pulled the ORDER BY into a subselect to produce the correct order now. This works, but it's not documented. To be absolutely sure, you have to add an explicit ORDER BY later:

SELECT ARRAY (
   SELECT elem
   FROM   vals v, LATERAL (VALUES (1, v.i), (2, v.j)) t(ordr, elem)
   ORDER  BY v.i, v.j, ordr
   ) AS arr;

About guaranteed order:

PostgreSQL unnest() with element number

About the VALUES expression in a LATERAL join:

SELECT DISTINCT on multiple columns

Another way with - this time with array_agg():

SELECT array_agg(elem) AS arr
FROM  (
   SELECT i, j, 1 AS ordr, i AS elem FROM vals
   UNION ALL
   SELECT i, j, 2 AS ordr, j AS elem FROM vals
   ORDER BY i, j, ordr
   ) sub;

The ARRAY constructor is faster, array_agg() is easier to integrate with multiple result columns.

SQL Fiddle.

The query in your test case would actually work in Postgres 9.5 (currently beta, to be released in the course of 2015). Quoting the release notes for pg 9.5:

  Allow array_agg() and ARRAY() to take arrays as inputs (Ali Akbar, Tom
  Lane)

However, you get a 2-dimenstional array instead, not your desired result.  You can easily implement the same (but slower) aggregate function in older versions:

Is there something like a zip() function in PostgreSQL that combines two arrays?


How to Enable Windows Authentication in Firebird 2.5 I am trying to enable Windows Authentication on a remote Firebird 2.5 database.  I modified the Firebird config file and changed the authentication method to 'MIXED', restarted the Firebird services but so far have still not been able to use Windows credentials.  Is there additional steps to get this working?

Few basic things things to check -

Check user name length. Like in few other databases, this could be failing if the Windows user name length is greater than the permitted limit. From FireBird's documentation this seems to be 31.
Try using Trusted mode and if needed enforce Windows authentication.

Is this a web application connecting which is connecting to this database? If so what's the browser being currently used.


How to concatenate psql variables in tablename? I am trying to concatenate some fixed string with a variable to build a table name.  This simple example demonstrates the problem.  The variable "suffix" resolved properly, but "prefix" does not because I am unable to figure out how to tell psql that "ws" is not part of the variable name.

What is the proper syntax so that the resultant query selects from "my_ws_group" ?

localhost ~ > psql -v prefix=my -v suffix=group
psql (8.4.4.10, server 8.0.2)
WARNING: psql version 8.4, server version 8.0.
         Some psql features might not work.
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

dev=> select * from :prefix_ws_:suffix;
ERROR:  syntax error at or near ":"
LINE 1: select * from :prefix_ws_group;
                      ^
dev=>

I prefer to use psql 8.4 because that is most compatible with Redshift.

psql's pattern matching is not strong feature, so your query cannot be performed.

When parsing your string, psql finds the :prefix_ws_ since it cannot match/find :prefix, but that variable isn't defined by -v option, so psql ignores it. Next, psql finds :suffix, then replaces to group since it is defined.

Additionally if you write "select * from :prefix:suffix", psql can replace both variables such as "select * from mygroup".


SQL Server Linked Server performance: Why are remote queries so expensive? I have two database servers, connected via Linked Servers. Both are SQL Server 2008R2 databases, and the linked server connection is made via a regular "SQL Server" link, using the current login's security context. The linked servers are both in the same datacentre, so the connection shouldn't be an issue.

I use the following query to check which values of the column identifier are available remotely, but not locally.

SELECT
    identifier
FROM LinkedServer.RemoteDb.schema.[TableName]

EXCEPT

SELECT DISTINCT
    identifier
FROM LocalDb.schema.[TableName]

On both tables are non-clustered indexes on the column identifier. Locally are around 2.6M rows, remotely only 54. Yet, when looking at the query plan, 70% of the execution time is devoted to "executing remote query". Also, when studying the complete query plan, the number of estimated local rows is 1 instead of 2695380 (which is the number of estimated rows when selecting only the query coming after EXCEPT).

When executing this query, it takes a long time indeed.

It makes me wonder: Why is this? Is the estimation "just" way off, or are remote queries on linked servers really that expensive?

The plan you have at the moment looks like the most optimal plan to me.

I don't agree with the assertion in the other answers that it is sending the 2.6M rows to the remote server.

The plan looks to me as though for each of the 54 rows returned from the remote query it is performing an index seek into your local table to determine whether it is matched or not. This is pretty much the optimal plan.

Replacing with a hash join or merge join would be counterproductive given the size of table and adding an intermediate #temp table just adds an additional step that doesn't seem to give you any advantage.


Starting points for creating a mysql database proxy I'm not actually entirely sure if this is possible, but I'd like to build piece of software that behaves a lot like a proxy between a client and a mysql server.
Essentially, I want this software to expose a connection string the same way a normal mysql database would, and listen to the same port.  When it receives traffic (mysql queries), it would apply some modifications to them before handing them off to the actual mysql database.
Is this possible? If so, where would I even start in building this? Finally, are there any recommendations on how to do this properly?
I'm also open to exploring off-the-shelf options if something like this already exists.

there two or three such software packets available, but usually you use this two split the incoming packages after rules to different servers.
It is in the begining , sowhat difficult to configure, but there a a lot of tutorials
https://proxysql.com/  or https://github.com/mysql/mysql-proxy


Issue connecting to remote SQL Server database through VPN I need to connect to a remote SQL Server database of a client to do some syncing and other stuff. They use a VPN connection and have provided me the VPN address, RDP to SQL Server, username and password.

VPN Address: *...
RDP to SQL Server: ..**.* : 4001
Username: *************gonal
Password: **********
DB name: IS_*****

What I managed to do was connect with a new VPN connection I created with the VPN address, username and password that were given to me.Worked fine on a linux and a windows machine.

The main issue I seem to be having is when I try establish a connection to their DB. I use DBeaver on Linux and HeidiSQL on windows. Both give me errors, see below:

DBeaver (Linux):

HeidiSQL (Windows):

Is there something I am missing? I'm trying everything and would appreciate any help at all. Could it be a firewall issue? Thanks in advance!

I'm speculating that since you were given RDP access and a username/account to the database server, the security credentials in question are Active Directory credentials.

DBeaver on Linux likely cannot use AD credentials (unless the environment taps into AD for authentication on Linux, in which case this isn't your issue).  I don't know if trying to authenticate via SQL Authentication using AD Credentials will throw the error you've provided, but it may be the cause.  I don't use DBeaver, so again this is just speculation.

HeidiSQL requires that you check the box next to Use Windows authentication as shown below:

Your screenshot covers up this area of the window so I haven't an idea if you're using this option or not.  Regardless, the caveat here is that that the Windows machine you are running HeidiSQL on has to be on the domain in question, and you need to have logged into said machine using the RDP credentials provided to you.  Basically, if you're using a Windows machine from DomainA, VPN into DomainB, and then run HeidiSQL and check this box, you're still going to get an error authenticating because it's using the authentication token created by the account used to log into the machine on DomainA and not the account needed to log into DomainB.

Again, the errors you're showing don't necessarily mean this is your issue, but it could be one reason why you're not able to connect remotely.

To start troubleshooting differently, I suggest you RDP to the server with a client that supports NLA/TLS connections (e.g. authentication occurs before an RDP session is opened) and see if you can log into SQL Server locally on the server.  If you can log in locally, start checking other potential issues for remote connection issues and go from there.

If you provide more context in your question, I'll be happy to modify my answer accordingly as well.


Airport Database Design guidance required I am trying to make a generic database for airport management and DBMS would be oracle 11g xe. Since, i am amateur and still learning so i have tried to kept the problem statement simple with limited entities involved:

Airport serves a city but every city may not have an airport. An airline has office(ticketing booth) at airports they cover and hence an airport may have many airline offices. Airport accommodates airplanes and airplanes may or may not belong to a certain airline(considering private jets). Airports may have one runway or more than one. Runway has a name and length. Every airport has a manager. Manager can cross view the other data but he can alter data for airport under his belt only- this i think will be applied via front-end application using 'APEX'.

Here is the relational schema i have worked so far:

City (CityID{PK}, CityName, CountryName)

Airport (AirportID{PK},AirportName, ICAO, IATA,ManagerID{FK},CityID{FK})

AirportRunway (AirportID{FK}, RunwayID{FK}, RunwayName, length)

Airline (AirlineID{PK}, AirlineName, IATA, ICAO)

AirportAirlineOffice (AirlineID{FK}, AirportID{FK}) both foreign keys make a primary key.

Airplane (AirplaneID, Model, Make, AirlineID{FK})

Manager (ManagerID{PK}, username, password, ManagerName)

I am confused over manager relation considering what i am trying to implement. Do i need to make relationsship of manager with other entities as well? Do i need to make AirlineOffice as entity or it is good as it is. And what changes do i need to do so that i can convert this schema to 3rd normalized form!

edit: here is my ERD:

You mention every airport has a manager so the manager table should have AirportID as an FK. The Office table should have AirportId and AirlineID as FKs


How to update a column with values from the same newly inserted row in a trigger? Using SQL Server 2014, I have this:

Table1
Tab1_id        int identity(1,1) primary key
Tab1_val1      smallint
Tab1_val2      smallint
Tab1_valInfo   varchar(10) -- not yet implemented, will be used only for presentation

 Tab1_id |   Tab1_val1 |   Tab1_val2 | Tab1_valInfo
---------|-------------|-------------|--------------
       1 |          25 |          19 | 0025-0019
       2 |           0 |           5 | 0000-0005
       3 |          12 |           3 | 0012-0003

Now with my pretty short SQL experience, what I'm trying is:

From an application where the only input values required for inserting data are Tab1_val1 and Tab1_val2, then, create a trigger which updates Tab1_valInfo column.

create trigger utr_ValInfo on Table1
after insert as
begin

    declare @size int = 4;

    declare @valInfo varchar(10) =
        select right('0000' + convert(varchar, /* insertedRow.Tab1_val1 */), @size) from inserted
        + '-' +
        select right('0000' + convert(varchar, /* insertedRow.Tab1_val2 */), @size) from inserted;

    update Table1 set Tab1_valInfo = @valInfo where /* this is the last inserted row*/;

end;

A single insert may involve multiple rows.

I hope what I'm trying to achieve is clear.

Try this

create trigger utr_ValInfo on Table1
after insert as
begin

    declare @size int = 4;

    UPDATE Table1

    SET
         Tab1_valInfo  = right('0000' + convert(varchar, i.Tab1_val1), @size)
            + '-' +
             right('0000' + convert(varchar, i.Tab1_val2), @size)

    FROM Inserted i

    WHERE
         Table1.Tab1_id= i.Tab1_id;
end;

and don't declare varchar in convert without length


Help performance tuning master/detail (email like inbox) SQL query I have spent the last couple days searching, watching videos, and I think I've gotten as far as I can get just fumbling my way through. I am looking for more specific direction given my example below.

I have two tables I'm working with. MessageThreads (400k records) & Messages (1M records). Their schemas are shown below.

MessageThreads indexes

https://gist.github.com/timgabrhel/0a9ff88160ebc9e40559e1e10ecc7ee4

Messages indexes

https://gist.github.com/timgabrhel/d649074cbe82016e8a90f918c58c4764

I am trying to improve the performance of our primary "inbox" query. Think of your email provider's inbox. You'll see a list of threads, some new, some read, sorted by date, and also gives you a preview of the most recently sent message, whether it was to or from you. Finally, there is an element of paging on this query. By default, we want 11 items. 10 for the page to display, and +1 to know if there's more on the next page.

For some of our long time users, they can have up to 40K messages.

This query has seen many different forms over the last few days, but this is where I've gotten to. I've given OUTER APPLY a try, but I'm seeing worse execution time & statistics.

SET STATISTICS IO ON; /* And turn on the Actual Excecution Plan */

declare @UserId bigint
set @UserId = 9999

; WITH cte AS (
    SELECT
        ROW_NUMBER() OVER (ORDER BY SendDate DESC) AS RowNum,
        MT.MessageThreadId,
        MT.FromUserHasArchived,
        MT.ToUserHasArchived,
        MT.Created,
        MT.ThreadStartedBy,
        MT.ThreadSentTo,
        MT.[Subject],
        MT.CanReply,
        MT.FromUserDeleted,
        MT.ToUserDeleted,
        LM.MessageId,
        LM.Deleted,
        LM.FromUserId,
        LM.ToUserId,
        LM.[Message],
        LM.SendDate,
        LM.ReadDate
    FROM MessageThreads MT
    -- join the most recent non-deleted message where this user is the sender or receiver
    LEFT OUTER JOIN
    (
        SELECT RANK() OVER (PARTITION BY MessageThreadId ORDER BY SendDate DESC) r, *
        FROM [Messages]
        WHERE (FromUserId=@UserId OR ToUserId=@UserId)
        AND (Deleted=0)
    ) LM ON (LM.MessageThreadId = MT.MessageThreadId AND LM.r = 1)
    --WHERE MT.ThreadSentTo=@UserId OR MT.ThreadStartedBy=@UserId
)
SELECT
    cte.*,
    UserFrom.FirstName AS UserFromFirstName,
    UserFrom.LastName AS UserFromLastName,
    UserFrom.Email AS UserFromEmail,
    UserTo.FirstName AS UserToFirstName,
    UserTo.LastName AS UserToLastName,
    UserTo.Email AS UserToEmail
FROM cte
LEFT OUTER JOIN Users AS UserFrom ON cte.FromUserId=UserFrom.UserId
LEFT OUTER JOIN Users AS UserTo ON cte.ToUserId=UserTo.UserId
WHERE RowNum >= 1
AND RowNum <= 11
ORDER BY RowNum ASC

Statistics for above query (execution time ~2 seconds in SSMS). This execution time is acceptable, but the statistics feel less than desirable, and even more so upon reviewing the actual execution plan.

The execution plan is linked here
https://gist.github.com/timgabrhel/f8d919d5728e965623fbd953f7a219ef

One huge hiccup I've spotted is the 400k row Index Scan on the MessageThreads table. Presumably this is because the primary SELECT X FROM MessageThreads query doesn't have a filter on it. When I apply a predicate to it (uncomment the WHERE from the query), the statistics greatly improve (below), but the time jumps from ~2 seconds to ~18 seconds in SSMS.

The problem area on the query is the MessageThreads predicate

https://gist.github.com/timgabrhel/1383ff9362567fdf41ba011dead63ceb

Thank you in advance!

A few thoughts:

Your WHERE clause needs a supporting index

WHERE MT.ThreadSentTo=@UserId OR MT.ThreadStartedBy=@UserId really needs two indexes to be efficient - one on the ThreadSentTo field, and one on the ThreadStartedBy field. Otherwise, the SQL engine will be performing a full table scan to retrieve the correct threads.

Use OFFSET... NEXT N ROWS ONLY instead of ROW_NUMBER()

Starting in SQL 2012, a new construct was added to SQL Server for handling paging. This works like this:

DECLARE @PageNumber int = 20
DECLARE @RowsPerPage int = 15

SELECT *
FROM MyTable T
INNER JOIN MyDetailTable D
    ON T.MyTableID = D.MyTableID
OFFSET (@PageNumber - 1) * @RowsPerPage ROWS
FETCH NEXT @RowsPerPage ROWS ONLY

In this case, the query will skip the first 285 ((20-1)*15) rows, and retrieve the next 15 rows. This is a faster paging method than the older RowNumber() filter for normal paging.


Throw more detail and more sample data.

you cannot pull all the data.you have to pull data of some specific date which you pass as parameter.

here consider @DateTable  is parameter.or you pass detail what you want

you can try some thing like this,

declare @DateTable table(ReportDate datetime)
insert into @DateTable values('2017-06-02'),('2017-06-03')

create table #t (colTime datetime)
insert into #t
select  dateadd(hour,number,ReportDate)  from @DateTable
cross apply(select distinct number  from master..spt_values where number>0 and number<25
)ca

--select * from #t

--analyze this
--select *
--from #t t
--left join table1 v
--on t.colTime=dateadd(hour, datediff(hour, 0, v.ModifiedDate), 0)

select t.colTime
,count(id)
from #t t
left join table1 v
on t.colTime=dateadd(hour, datediff(hour, 0, v.ModifiedDate), 0)
group by t.colTime

drop table #t


SQL Server 2005 collation issue during installation Every time I try to install SQL Server 2005 on to a Windows 7 machine it installs properly but the collation installed is sql_latin1_general_as instead of sql_latin1_general_cp1_ci_as. I have read somewhere that this might be due to the fact that the regional setting for that machine is set to English - United Kingdom, so I double checked it to make sure that regional settings is set to English - United States. Still, after the installation I checked the server properties from Management Studio and saw the collation installed is sql_latin1_general_ci_as instead of sql_latin1_general_cp1_ci_as.

What am I doing wrong here? Are there any other diagnostics steps to find out why this is happening?

there is no collation "sql_latin1_general_as"
there is no collation "sql_latin1_general_ci_as"
collation chosen during install does depend on the control panel settings
The default collation for a clean install would be "SQL_Latin1_General_CP1_CI_AS" for US English:
The default for UK English is "Latin1_General_CI_AS"
SQL collations are legacy and should only be chosen for in-situ upgrades (IIRC)

Basically, choose "Latin1_General_CI_AS" (you have a choice) and make sure you read correctly before posting


Recovery time effects of Availability Groups We did different kinds of basic Tests and AlwaysOn passed in many tests. We finally did a heavy write test for AlwaysOn and it gave surprising results.

The actual Test details are here, the goal is to see if AlwaysOn availability group can accommodate a high write load.

I have two VMs each running on 8 cores and 17GB of RAM allocated to SQL Server.
We wrote a script to generate reasonably good write I/O (in 20 threads).
Each thread basically inserts 24 MB of data into a table and deletes in an endless loop.

Within 15 minutes of the test run, the estimate for recovery time on automatic failover reached 12 minutes which is pretty bad. We tried a failover to confirm if it really takes 12 minutes, it took around 5 minutes which is still too high.  Also if we continue the test for three hours recovery ETA is almost 3 hours and it is taking hours to recover on failover (clearly this should not have been the case if it was a cluster failover, because all transactions are committed transactions).

So couple of things..

It is very clear that the synchronous secondary replica is not able to keep-up with the load primary is generating (even though both machines are of same configuration). And the side effect of this is the log on primary will keep on growing (even we take log backups it can't truncate the log).

We know that the secondary uses one thread per every 4 CPU-cores for doing redo, which looks like a clear limitation. If the primary is running 100 threads to generate load, the secondary can't use that many threads anyway.

Additionally, the primary does all its transactions in-memory and leaves the actual data file writes to checkpoints. However, it seems that secondary has to read all transactions from the physical log drive and redo. The log pool on secondary which is supposed to make this process faster? But it is not doing a good job in this scenario.

Finally questions to AlwaysOn experts:

Does anyone know how the redo process exactly happens?

is it cached ?
is the buffer pool involved at all ?

Does the secondary use log pool to cache the log entries for redo?
What is the size of log pool? Can it grow up to the max memory available?
When redo happens, the redo thread reads the pages to buffer pool and maintains them as if it is a normal transaction?
If secondary is not able to keep-up how come AlwaysOn articles say recovery time is a few seconds?

This makes the high availability part of Availability Groups questionable, since these recovery times are unsustainable.

[Edit by asker] Clarifications, since people seem to think this is answered, the transactions at primary are indeed acknowledged (i.e. the log is hardened), since the state of the secondary is always "synchronized". So it is not a problem with the hardening of the log. So it is the redo process that is taking forever upon failover. This means that AlwaysOn will always take longer to recover than without it for any load that generates log > redo threads capacity.

It is very clear that the synchronous secondary replica is not able to
  keep-up with the load primary is generating (even though both machines
  are of same configuration). And the side effect of this is the log on
  primary will keep on growing (even we take log backups it can't
  truncate the log)

In synchronous mirroring/alwayson the secondary must acknowledge that it hardened (written to disk) the log before the primary's commit is allowed to continue. The primary is then free to truncate/reuse it's own log as it needs. If you cannot truncate the primary it means the secondary is not synchronized. This would point toward a problem with the ability to ship the log to the secondary and write it to disk. The two obvious bottlenecks would be the network speed and the secondary's log file storage. Both are easy to measure and diagnose, as they're straight forward USE (utilization, saturation, errors) OS level metrics.

Note that I never mentioned recovery (secondary's redo). If the problem is indeed that the secondary is not able to synchronize then redo is not playing any real role here.


Create Meaningful Visualization for Large Set of Tables I'm trying to create a meaningful, usable visualization for my company's ERP database. I've extracted a skeleton of the database so I can make changes without modifying the production environment. Here are some facts:

The DB includes almost 1300 tables, approximately 500 of which have data.
Our ERP provider hasn't added any relationships to the tables.
This is an MSSQL database.

Right now we only want to generate a visualization for the tables we actually use. My question is multifold:

Is there any automated way of suggesting relationships between the tables, or will this have to be done manually?
~500 tables is too large to have in one document while being easily navigable. We're considering breaking this up into multiple diagrams for each logical unit of data (e.g. Customers, Items, Billing, Raw Materials, Inventory, etc). Is there another way that would provide logical separation with manageably sized documents?
What is the best way to generate a diagram of the tables? I know MS SSMS has a built-in function for generating diagrams, but what other options exist? The final document could be printed large sheet paper, PDF, etc.

It's possible to write a query using the database DMVs, that dynamically tries to guess relations between tables by matching column names.

Here's a very simple draft, although it will only work if your primary key column names are unique for each primary key table (i.e. if the primary keys are called "id" in all the tables, this won't fly). Also, you'll have to make a few modifications if you want the script to work with composite keys (multiple columns in the primary key).

WITH pk
AS (
    SELECT ix.[object_id], ixcol.column_id, ix.is_primary_key
    FROM sys.indexes AS ix
    INNER JOIN sys.index_columns AS ixcol ON ix.[object_id]=ixcol.[object_id]
    WHERE ix.is_primary_key=1),

     keycols
AS (
    SELECT tbl.[object_id], tbl.[name] AS tblName, col.[name] AS colName, pk.is_primary_key
    FROM sys.tables AS tbl
    INNER JOIN sys.columns AS col ON tbl.[object_id]=col.[object_id]
    LEFT JOIN pk ON tbl.[object_id]=pk.[object_id] AND col.column_id=pk.column_id)

SELECT pk.tblName AS pkTable, pk.colName AS pkCol, fk.tblName AS fkTable, fk.colName AS fkColumn
FROM keycols AS pk
INNER JOIN keycols AS fk ON
    pk.is_primary_key=1 AND
    pk.colName=fk.colName AND
    pk.[object_id]!=fk.[object_id];

Once you've established relations between the tables, you might be able to identify "clusters" of tables that you can group on separate diagrams. If there are different schemas in the database, this might be what you're looking for.

If you want to draw diagrams in SSMS, you'll have to actually create foreign key constraints between the tables, and I wouldn't really recommend doing that in a vendor database without their approval. Then again, you don't have to save the diagram.

I do all my diagramming in Powerpoint, but that's hardly the answer you're looking for. :)


Trigger and temp table or materialized view I'm looking for the better/best solution to compare data in a table before update and after. This can be done with trigger and temp/global temp table or with read only m-view as far as I know.
Would like to ask DBA community which solution is better, more efficient etc... The table is not big, less then 1000 rows. It may grow in the future, but not much.
Which is better for big tables, with million(s) rows please?
Thank you all.

If flashback does not work for you for some reason, you could add a column to your table to save the original value of the indicator and a trigger of the sort:

create trigger t
before update of ind_col on your_table
for each row
begin
 if :old.ind_save_col = '' then
   :new.ind_save_col := :old.ind_col;
 end if;
end;
/

(code not tested). Your nightly process would then copy rows where ind_col != ind_save_col, then reset ind_save_col.


What will be the order of statement execution in this use case? Say I execute select * from users at t1 which gets finised at t3. At t2(in between t1 and t3) update comes for some of the records in users table say
update users set address='xyz' where name = 'abc'. Will select statement acquire shared lock on all rows in advance or it will acquire for each row
independently when it starts to read that row ?

My understanding is whosoever(among select or update) acquires the shared or exclusive lock earlier it will execute it other will wait ?

I am using Mysql innodb .

It depends on the transaction_isolation.

It depends on whether the SELECT includes FOR UPDATE or IN SHARE MODE.

The SELECT is inside a transaction.  Normally, the SELECT will see the table as of the instant the transaction started.  Other queries may update/insert/delete rows that the SELECT is reaching for, but SELECT won't see them.  This puts a burden on InnoDB to keep old/new copies of row around for cases like this.

If you say FOR UPDATE, the other transaction will probably be delayed, awaiting the "intention to change" lock to be released.

Or it might lead to a deadlock.  InnoDB is good at seeing if the situation can't be resolved by waiting.

Perhaps you would like to discuss a specific case?


Can't add or update child, Foreign Key Error I'm kinder new in MySQL.
I was trying to insert value in a relational database.
I know that i have to insert in parent table before doing it in child table.
Assume we have tables named STATE, COUNTRY.

START TRANSACTION;
INSERT INTO country (countryName, nationality,countryPrefix)
SELECT * FROM (SELECT 'Togo' AS countryName, 'Togolese' AS nationality, '228' AS countryPrefix) AS tmp
WHERE NOT EXISTS (
    SELECT countryName FROM country WHERE countryName = 'Togo'
) LIMIT 1;
SELECT country.idcountry FROM country WHERE country.countryName = 'Togo';
INSERT INTO state (stateName, country_idcountry)
SELECT * FROM (SELECT 'Region Maritime' AS stateName, '12' AS country_idcountry) AS tmp
WHERE NOT EXISTS (
    SELECT stateName FROM state WHERE stateName = 'Region Maritime'
) LIMIT 1;
COMMIT;

Below image is the screenshot of the error i encountered.

this message comes of you trying to insert data, that are not exist in the connected tabel

In you case

Your try to insert 12 as county_id_contry which doesn't exist in the table country.

So you have to insert 12 in your country table first, before you add to the state table


To which phase of database design is Database Denormalization formally placed Formally speaking, to what stage of the database design process does database denormalization belong--logical design? or physical design? The process of joining relations into one, adding redundant attributes, etc seems like a logical design. However, denormalization seems to be done after the physical design of the db, which seems to make it fall under physical design. Which leads me to the question, where does denormalization belong?

DB design is rather a cyclic process than linear so we can come back to the logical design after the physical phase again and again.


Oracle Universal Installer: Install completes, but in (Not responding) state I am running the universal installer for 64-bit ODAC 12c Release 1 (12.1.0.1.0) for Windows x64 on Windows Server 2012 R2 box. Screenshot of Oracle version below.

I have completed the installer properly many times, but this time I have run the installer with correct parameters (this has been a repetitive task) and the installer completes, except in (Not Responding) state.

I have checked the logs here C:\Program Files\Oracle\Inventory\logs and the last line looks as my other successful installs.

INFO: no. of sets of tools to be run: 0
INFO: ca page to be shown: false
INFO: exitonly tools to be excuted passed: 0
INFO:
*** End of Installation Page***
The installation of Oracle Data Access Components for Oracle Client was successful.

Did this in fact mean that Oracle has installed correctly?

I wonder if there is a modal window that is under your current installer window that is waiting for you to click finished/done...


Is there any way to test which clustered index is better for specific queries? We've been querying and processing a growing table of events in our system, which was fine without specific indexes for now. However we've noticed a decrease in performance and are wondering what we can do to improve that.

I've created a few test tables to test different indexes:

SELECT TOP 1000000 * INTO IndexTest1
FROM Events

SELECT TOP 1000000 * INTO IndexTest2
FROM Events

SELECT TOP 1000000 * INTO IndexTest3
FROM Events

Next I added different clustered indexes on columns that are often used to query the events or process them. Most of the time we use the Timestamp or the EventType.

CREATE CLUSTERED INDEX TimestampEventType
ON IndexTest1 (Timestamp, EventType)

CREATE CLUSTERED INDEX Timestamp
ON IndexTest2 (Timestamp)

CREATE CLUSTERED INDEX EventType
ON IndexTest3 (EventType)

Next I tested two different types of queries. However I currently have no way of noticing which performs best.

SELECT * FROM IndexTest1
WHERE EventType = 'String'

SELECT * FROM IndexTest2
WHERE EventType = 'String'

SELECT * FROM IndexTest3
WHERE EventType = 'String'

SELECT * FROM IndexTest1
WHERE Timestamp >= '2018-03-14' AND Timestamp <= '2018-03-20' AND EventType = 'String'

SELECT * FROM IndexTest2
WHERE Timestamp >= '2018-03-14' AND Timestamp <= '2018-03-20' AND EventType = 'String'

SELECT * FROM IndexTest3
WHERE Timestamp >= '2018-03-14' AND Timestamp <= '2018-03-20' AND EventType = 'String'

What I expected:
I expected IndexTest1 to perform best for both queries, seeing as it is the only one that includes both EventType and Timestamp in its clustered index.

Results second query set:
When turning on the live query statistics, I get varying times for execution for all queries, ranging between 8 - 20 seconds.

When looking at the execution plans I do notice that IndexTest1 has a much lower Estimated I/O Cost and Estimated Operator Cost for the second set of queries than IndexTest2 & IndexTest3. The difference is relatively large, IndexTest1 has an I/O Cost of about 0.003 whereas IndexTest2 & IndexTest3 are around 25 and 40 respectively.

Question: Can I conclude that for the second query set IndexTest1 is the best index?

Results first query set: When looking at the execution plans for the first set of queries IndexTest3 actually has the best performance (although barely). All of them are around an I/O cost of 40, with IndexTest2 being the worst and IndexTest 3 being the best.

Questiong: Does that mean that the indexes I added have no real effect on the first query set? And if so, why would that be if EventType is included in the clustered index of IndexTest1?

I have very little experience with testing indexes and creating them. Is there any other way I can test which index would perform best for the above queries? Or is there something else I should try out?

Or does anyone have any reading material they could link me to for more information about indexes and testing them?

The Clustered Index should be defined with a narrow and ever increasing value (this is why so many of them use an auto-incrementing integer).

Having worked with large tables like this before, your best bet is to continue to use an auto-incrementing primary key as the definition for the clustered index.  Then create non-clustered indexes that support your queries, with the understanding that other ad-hoc queries will probably perform terribly.  You get the added benefit that the index on your large table should never need defragmentation.

Given that, creating a non-clustered index on Timestamp, EventType should give sufficient performance.  You typically want the most specific column referenced first, and timestamps are good candidates for this, especially if you always (or almost always) include it in your query.

The advantage of non-clustered indexes is of course that they are typically much narrower than their base table and so require much less memory.

CREATE NONCLUSTERED INDEX TimestampEventType
ON IndexTest1 (Timestamp, EventType)

You can also benefit from casting to the proper datatype (I'm assuming DATETIME)

SELECT * FROM IndexTest1
WHERE Timestamp >= CAST('2018-03-14' AS DATETIME) AND Timestamp <= CAST('2018-03-20' AS DATETIME) AND EventType = 'String'

Finally, check that statistics are up to date for your query and that the execution plan is actually doing an index seek or range scan with a key lookup back to the main table.


Mysql Slow Log when using UNION ALL? I have following query coming up in mysql-slow log, however i am not sure how to optimized this query and make it much faster. Any ideas on this ?
I have already used indexes in following in the user tables and other tables. however its still not right. All my tables are in InnoDB table types.

SELECT  count(*) as total
    FROM  users_interest
    WHERE  user_to='64500'
      AND  new='Y'
    UNION  ALL
SELECT  count(*)
    FROM  users_view
    WHERE  user_to='64500'
      AND  new='Y'
    UNION  ALL
SELECT  count(*)
    FROM  mail_msg
    WHERE  user_to='64500'
      AND  folder='1'
      AND  new='Y'
    UNION  ALL
SELECT  count(*)
    FROM  user
    WHERE  activated='Y'
      AND  mode NOT IN('suspended','hellban','errorban','deleted','hidden' )
      AND  (gender!='M')
      AND  user_id NOT IN (
        SELECT  user_from
            FROM  users_block
            WHERE  user_to='64500' )
      AND  user_id NOT IN (
        SELECT  user_from
            FROM  users_block
            WHERE  user_from='64500' )
      AND  online=1
    UNION  ALL
SELECT  count(*) as total_recent
    FROM  user
    WHERE  activated='Y'
      AND  mode NOT IN('suspended','hellban','errorban','deleted','hidden')
      AND  (gender!='M')
      AND  user_id NOT IN (
        SELECT  user_from
            FROM  users_block
            WHERE  user_to='64500' )
      AND  user_id NOT IN (
        SELECT  user_from
            FROM  users_block
            WHERE  user_from='64500' )
      AND  last_visit >= DATE_SUB(NOW(), INTERVAL 60 MINUTE);

# Time: 160124 22:46:38
# User@Host: xyz_user[xyz_user] @ localhost []  Id: 194710
# Schema: xyz_dev  Last_errno: 0  Killed: 0
# Query_time: 10.199606  Lock_time: 0.000048  Rows_sent: 81  Rows_examined: 2358518  Rows_affected: 0
# Bytes_sent: 4158
SET timestamp=1453655798;

Please provide SHOW CREATE TABLE.

Indexes needed:

users_interest:  (user_to, new)
users_view:  (user_to, new)
mail_msg:  (user_to, folder, new)
users_block:  (user_from) and (user_to, user_from)
user:  (activated, online) and (activated, last_visit)

The construct NOT IN ( SELECT ... ) performs poorly; change it to a LEFT JOIN ... WHERE ... IS NULL.  For example:

SELECT ... FROM user
    WHERE user_id NOT IN ( SELECT user_from
                         FROM users_block WHERE user_to=1 )
      AND other_stuff

-->

SELECT ... FROM user AS u
    LEFT JOIN user_from AS ub  ON u.user_id = ub.user_from
    WHERE ub.user_from IS NULL
      AND other_stuff

Or (I don't know whether this is better):

SELECT ... FROM user AS u
    WHERE NOT EXISTS ( SELECT * FROM users_block
                     WHERE user_to=1 AND users_from = u.user_id )
      AND other_stuff

A suggestion:  Instead of having 5 rows of unlabeled counts, do something like

( SELECT 'hidden count' AS total, COUNT(*) AS ct ... )
UNION ALL
( SELECT 'blocked to  ' AS total, COUNT(*) AS ct ... )
...

so that each count is labeled.


Multi-tenant database We are building a database for a SaaS web application. We have been told to use a multi-tenant approach and to expect over 100,000 tenants to be using the system over the next 5 years, so we're expecting a very large amount of data. We're anticipating average of 20,000 companies per tenant and 40,000 people per tenant. Hosted in Azure using SQL Azure Federation services.

My question is regarding the table structure for companies and people. A company can be a vendor, customer, contractor, prospect or financial institution.  A person can be a vendor, customer, contractor, employee or prospect.

In previous databases, I have have used a master table containing the common fields and secondary tables to hold the detail pertaining to the type of company or person.

For example:

CompanyMaster (ID, Name, Website, ParentID, etc)
CompanyVendor (CompanyID, CreditLimit, CreditTerms, etc)
CompanyContractor (CompanyID, RatePerHour, RatePerMile, etc)

Similar situation for Person's table.

With regards to performance and best practice, is it better to do it this way or split them into separate tables and not use a master table?

CompanyVendor     (ID, Name, Website, ParentID, CreditLimit, CreditTerms, etc)
CompanyContractor (ID, Name, Website, ParentID, RatePerHour, RatePerMile, etc)

I have a CTO telling me that they handled 3 million transactions per day using the multi-tenant database at his previous employer. He says it's all about the indexes and I know it's more than that.

We will be hiring an Azure Partner and most likely hire/contract a DBA. I have had experience with databases that had 100K companies and 300K people using the model above, but I thought due to potential size of the multi-tenant solution that it may be better to split them into smaller tables.

100,000 tenants, so we're anticipating average of 20,000 companies per tenant and 40,000 people per tenant. Hosted in Azure using SQL Azure Federation services

You do realize that your number comes up to 2 billion entries?

I'm afraid the answer is that there are several ways to accomplish what you want to achieve.  You can have a master table with your company information and then sub tables containing the users under each company.  Keep in mind on incoming queries, the more joins you have to make, the longer the response time.

This is a massive design effort that will not be easily answered in this format and should be reviewed by a design team in several disciplines.

I would recommend splitting them into different logical groups either by tenant, company etc.... The main reason being any DUI ( Delete, Update, Insert) will lock the records as it runs which can potentially cause problems


What is the mysql.db table used for? Related to my MySQL issue

I have a user with USAGE grant in mysql.user, but in mysql.db this user has Select, Insert, Update, Delete. Thus the user can successfully query the database.

I can't find any information about how this mysql.db works, is it like some kind of cached permission in there ? Will a mysqld restart flush it ?

mysql> show grants for user@'xx.xx.xx.%';
+-------------------------------------------------------------------------------------------------------------------------------+
| Grants for user@xx.xx.xx.%                                      |
+-------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user'@'xx.xx.xx.%' IDENTIFIED BY PASSWORD 'xxx' REQUIRE SSL |

 mysql> select host,db,user, select_priv,update_priv,delete_priv  from
 mysql.db where  user='user';
 | host         | db   | user    | select_priv | update_priv |  delete_priv |
 | xx.xx.xx.1   | myDB | user    | Y           | Y           | Y

From the docs, mysql.db is the table that handles database-specific GRANTS. That is to say, if you explicitly indicated a database in your GRANT command, it would show up in this table:

GRANT SELECT, INSERT ON foo.* TO `bar`@`localhost`;

So the user bar@localhost would have SELECT and INSERT marked as 'Y' in the mysql.db table.

To remove entries from this table, likewise you need to specify the database:

REVOKE SELECT, INSERT ON foo.* FROM `bar`@`localhost`;

Issuing a REVOKE INSERT ON *.* statement (all databases) will not affect this table.

Also, a DROP USER statement will clean up all the entries in the various mysql.* tables that deal with user authentication, such as mysql.db.


How to install MySQL server on Mac (OS X 10.6.8) from command line? How to install MySQL server on Mac (OS X 10.6.8) from command line?  If I donwload the .dmg file, is it possible to install it from command line?

If you have homebrew installed, then :

brew install mysql

Else install homebrew it might be useful also to install other projects


Optimize query plan when your results are either a few rows or hundreds of rows? The most frequent query typically uses a variable that has hundreds of rows, but also runs often with a variable that has only a few rows (it's always one or the other, nothing in between). SQL sporadically picks up the query plan for the query against the few rows, resulting in spiking CPU when that plan runs against hundreds of rows, and my guess is it's after statistics update (auto stats are ON, I'm turning them off and adding a job to have it done at low peak rather than automatically at random times). Aside from pinning the plan in query store for the higher number of rows, is there a way to have both queries run successfully?  Azure SQL V12.

It's a Parameter Sniffing problem, so you have a few options:

You can consider using sp_create_plan_guide. Creates a plan guide for associating query hints or actual query plans with queries in a database.

Enables or disables parameter sniffing. The default is ON. Setting PARAMETER_SNIFFING to OFF is equivalent to enabling Trace Flag 4136.

OPTION RECOMPLE avoids plan caching and the risk of reusing an inappropriate plan at the cost of compilation upon each execution.That is not a concern for infrequently executed queries but can be very expensive for an OLTP workload where the query is executed many times per second.

OPTIMIZE FOR UNKNOWN This hint directs the query optimizer to use the standard algorithms it has always used if no parameters values had been passed to the query at all. In this case the optimizer will look at all available statistical data to reach a determination of what the values of the local variables used to generate the queryplan should be, instead of looking at the specific parameter values that were passed to the query by the application. Take a look at this post.


what is bigger than a longblob? the longblob is the biggest data type of Mysql.  If I want to save something bigger than 4gb (longblob) how can I do?.

I'm talking about Mysql v-5.+

Using LONGBLOB can be very risky in light of the fact that the biggest max_allowed_packet is only 1GB.

Back in August 2006, someone asked a similar question. in which Peter Zaitsev said:

  http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html

  As you can see in theory it is about 4GB.

  It is however also limited by max_packet_size which is 16M by default.

  I would be very careful using blobs larger than 100MB.   MySQL will
  need some 3 times of this size of memory allocated on the server for
  blob processing.

I am not a PostgreSQL DBA, but I would have to venture the following suggestion:

If you must store objects that big, you should go with PostgreSQL because it has a storage infrastructure called the The Outside Attribute Storage Technique (TOAST). This allows rows not to be too big for SQL processing.
If you must use MySQL, you are much better off storing URL File Links than the BLOB itself.


computed columns, index, clustered index and covering index? We all know the we can only have one clustered view per table, cool. But apparently you don't have values of computed columns on leaf even when using a clustered index and they are computed each time.
So, my first question is, is that correct that what I just said?

Also, if you create a clustered index, okay let me just quote..
"If you create a clustered index on a computed column, the values of the column will
physically exist in the corresponding table rows, because leaf pages of the clustered
index contain data rows."
Yeah, okay, but why create clustered index on computed column only? I thought if you create a clustered index on any column the data exists on leaf?
So, my Ques 2 is would the value of computed column exists (persist) in table if only we create a clustered index on that particular column? (question 2 continues) If that is indeed the case, what if we have more than one computed columns? surely, we can't create more than one clustered index, than how do we make their value persistent?

Now moving on, what about the covering indices? First, we don't really need covering (or we can't really create) covering index on while creating clustered index because all the data would already be on leaf, right? So, there's no point of including any other column?
 So, thirdly I'd like to ask, am I correct about what I just said in my previous statement?

Now if we are creating a covering index, (non-clustered right?) and we include the computed column(s), then would they we persisted? would they be "physically stored" or calculated again? (and that was my 4th ques. by the way.)

Finally, (5th ques) if we can achieve the same (make computed columns persist) by all these ways (or some of these ways, I don't really know which one, that's why I am asking the question in first place!) then, what would be the best method to adopt and why?

ps : I am sorry if for asking 5 question in just once, but they are all terribly related, and I didn't want to ask same thing over and over again in 5 different questions. I hope you understand! :)

EDIT : well in response to comments, right now I am using SQL server 2008, but if you could provide how the situation would differ if using anything else (my sql or whatever) that would be added benefit for not only me but also for community. :)

Computed Columns can be stored in the data page in one of two ways. Either by creating them as PERSISTED or by including them in the clustered index definition.

If they are included in the clustered index definition then even if the columns are not marked as PERSISTED then the values will still be stored in each row. These index key values will additionally be stored in the upper level pages.

If the computed column is imprecise (e.g. float) or not verifiable as deterministic (e.g. CLR functions) then it is a requirement for the column to be marked as PERSISTED in order to be made part of an index key.

So to give an example

CREATE TABLE T
(
A INT,
C1 AS REPLICATE(CHAR(A),100) PERSISTED,
C2 AS REPLICATE(CHAR(A),200),
C3 AS CAST(A AS FLOAT) PERSISTED,
C4 AS CAST(A + 1 AS FLOAT)
)

CREATE UNIQUE CLUSTERED INDEX IX ON T(C2,C3)

C1 will be stored in just the data page rows as it is marked as
PERSISTED but not indexed.
C2 will be stored in both the rows on the data page and the index higher levels as it is an index key column.
C3 will be stored as for C2. As it is imprecise it is a requirement to mark it as PERSISTED however.
C4 won't be stored anywhere as it is neither marked as PERSISTED nor indexed.

Similarly all computed columns referenced in non clustered index definitions as key columns need to be stored at all levels of the index as they are part of the index key. There is the same requirement regarding precise/deterministic results.

Fails

CREATE NONCLUSTERED INDEX IX2 ON T(A,C4)

With the error.

  Cannot create index or statistics 'IX2' on table 'T' because the
  computed column 'C4' is imprecise and not persisted. Consider removing
  column from index or statistics key or marking computed column
  persisted.

To include it as part of the non clustered index key it must also be stored in the clustered index data pages. However

Succeeds.

CREATE NONCLUSTERED INDEX IX2 ON T(A) INCLUDE (C4)

Computed columns that are only INCLUDEd columns are persisted to the NCI leaf page and do not have the requirement that they also be persisted in the data page.


Poor execution plan leads to query time increasing dramatically I have a relatively complex query like the one that follows
select * from (
  select
      distinct on("car")
      entity.id as "car",
      metric as "last_visit"
  from now() ref_date
  cross join (values (142), (432), (583), (448), (381)) entity(id)
  left join (
    select
        gv.car_id as _entity_id,
        gv.start as _tc_past,
        gv.start as _keep_max_of,
        g.name as metric
    from garage_visits gv
    left join garage_tree_flat gtf on gtf.descendant_id = gv.garage_id
    left join garage g on g.id = gtf.parent_id
    where true
    and price != 0
    and  g.type = 2
  ) x
  on x._entity_id = entity.id
    and _tc_past < ref_date
    and _tc_past >= ref_date - interval '12 weeks'
    order by "car", _keep_max_of desc
) t
where t."last_visit" is not null;

My problem is that the query running time will vary tremendously based on the number of elements that are present in cross join (values (...)).
Namely, I have the following average running times depending on how many values there are:

# of car_id values
avg running time

1
25ms

2
30ms

3
35ms

4
40ms

5
4900ms

6
4900ms

7
4900ms

Checking the execution plan for both versions (up to 3 values or more than 3 values) shows that the culprit is the index scan on garage_visits gv. This table contains several million rows.
In the case of up to 3 car_ids, the index condition on garage_visits is as follows:
Node Type: Index Scan
Index Name: ix_gv_car_id
Index Cond: (gv.car_id = "*VALUES*".column1)
Actual Duration: 0.687ms

In the case of more than 3 car_ids, the index condition on garage_visits is as follows:
Node Type: Index Scan
Index Name: ix_gv_garage_id
Index Cond: (gv.garage_id = gtf.descendant_id)
Actual Duration: 3386.068ms

As we can observe, with up to three values, the query planner decides to filter garage_visits by car_id, and outputs only these rows, which is extremely fast. But with more than three car_id values, the planner decides against using this information and instead filters garage_visits by gtf.descendant_id instead, which dramatically changes the execution time because there are many more gtf.descendant_id values than there are car_ids values.
Is there a way to keep using the ix_gv_car_id index but with more than 3 car_id values? It looks like it's simply a wrong choice here from the query planner and it's frustrating to see that this query could run really fast (even for 30 or 50 values) but instead spends a lot of wasted time.

You can force it not use that index by rewriting the join condition to gtf.descendant_id = gv.garage_id + 0.  Whether it will then switch to the good plan, or just switch to a different bad plan is not something we can predict.  You will have to try it and see.
If you want use to analyze why it is making the wrong choice, you will have to show us the complete plans, not just your interpretations of them.


Specify Server for DBMS_Scheduler Job in Policy Managed RAC A unit test requires a dbms_scheduler job to run on the same RAC node as the unit test is being run from.  I know that with an Admin managed database this could be done by creating a service that limited the available instances and then using that service in a job class the job uses. My question is, how can this be done in 11.2 with policy management?

Pools can be created that have only a single server in them and databases can be assigned to multiple pools, but as I understand it, a server can only be assigned to a single pool.  Therefore, a service can't be created that uses a single server and still have other services that use a pool defined with multiple servers including that one.

I also know that services can be created as either SINGLETON or UNIFORM, but since SIGNLETON doesn't provide for allowed servers or even preferred servers, I'm not sure how this would help.

Surely I am missing something that makes this all possible.

dbms_scheduler.set_attribute is what you're looking for - it forces a job to run on a given node (specified by instance_id):

begin
dbms_scheduler.create_job (
job_name=>'PHIL',
job_type=>'plsql_block',
job_action=>'NULL;',
repeat_interval=>'freq=minutely;interval=2');

end;
/

begin
dbms_scheduler.set_attribute('PHIL','instance_id',1);
end;
/

dba_scheduler_jobs.instance_id will be NULL unless you have explicitly set it with set_attribute.

I realise that this isn't what you asked, but it's easier than messing with pools!


How to delete all rows from all tables in postgresql? Is there any SQL query to delete all rows from all tables in postgresql?

Method 1: create a file with a set of sql-statements to truncate each table, then execute those statements from the file.

testdb=# \t
Tuples only is on.
testdb=# \o truncate_all_tables.sql
testdb=# SELECT 'TRUNCATE ' || table_name || ';' FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE';
testdb=# \o
testdb=# \t
Tuples only is off.
testdb=# \i truncate_all_tables.sql
TRUNCATE TABLE_NAME CASCADE
TRUNCATE TABLE_NAME CASCADE
TRUNCATE TABLE_NAME CASCADE
TRUNCATE TABLE_NAME CASCADE
TRUNCATE TABLE_NAME CASCADE

Method 2: create a database dump with schema only, recreate the database and restore the dump.

# pg_dump -U postgres -v -Fc -s -f testdb.dump testdb
# dropdb -U postgres testdb
# createdb -U postgres testdb
# pg_restore -U postgres -v -d testdb testdb.dump


What are some likely causes of high Client:ClientRead AAS in AWS RDS? I have a particular query, its a simple select with an inner join. Seems to be pretty well indexed. But it's registering as a worst offender in performance insights. With a 0.13 on CPU and a 4.5 on Client:ClientRead.
All the documentation and articles I can find indicate this is common for bulk data copying. But I don't think this is the case. Since it's such a basic select. It's being called around 70 times per second. Which isn't even that high compared to others I have in the 500 calls per second, and it's only serving back about 7 rows, 10 columns each.
So other than bulk data copying, what other factors might be giving me a high AAS for clientRead?

Posting an answer in the hopes that it helps anyone else seeing this issue. Client:ClientRead is best summarized as the time waiting on the client to read the data, and can include not just time transmitting but also time waiting for the client to be ready to transmit. Important to note for NodeJS users.
NodeJS, for better or worse, has that single-threaded event loop. So when we opened up our new app to a few hundred users and were still only using a single NodeJS replica of the service, that one service was queueing hundreds to thousands of events to loop through. So it would end up queuing all of these async calls to the DB and while it was working like crazy, it started taking seconds to get around to the callbacks, and that time was stacking up under this Client:ClientRead statistic.
As soon as we started firing up replicas of that service, that statistic dropped drastically, and we started seeing DB CPU waits be our problem. That's outside the scope of this question so I won't go in to it. But it's good to know the Client:ClientRead, when not dealing with transmitting large datasets back to the client, is likely explained by the calling service taking it's time to get back around to reading the data. If you're using Node in a K8s environment, it's likely time to scale up your replicas.


Where clause from OUTER APPLY using LEFT function What would be a good alternative to improve my where clause that currently uses the LEFT function?

Should I create a persisted computed column and an index to it?

SELECT --columns--
FROM DesvioDesenhoSubstitutivoProposto DSP
LEFT JOIN DesvioDesenhoSubstitutivo DS ON DS.idSubstituicaoDesenho = DSP.idSubstituicaoDesenho OUTER APPLY
  (SELECT TOP 1 D.Dendis
   FROM Interfaces.dbo.SSOLQ068 D
   WHERE DS.codigoDesenhoSubstitutivo = LEFT(D.NDISCOMPLFAB, LEN(D.NDISCOMPLFAB)-1)
   GROUP BY D.Dendis) AS DESEN

This is the current execution plan for this part:

PS: query has been simplified for posting.

How about:

WHERE D.NDISCOMPLFAB LIKE DS.codigoDesenhoSubstitutivo + '_'

...which will allow an index on (NDISCOMPLFAB, Dendis) to be used effectively.

Please check that the column types are the same kind between D.NDISCOMPLFAB and DS.codigoDesenhoSubstitutivo, such as both nvarchar or both varchar. If they are different, use:

WHERE D.NDISCOMPLFAB LIKE CAST(DS.codigoDesenhoSubstitutivo AS ...) + '_'

...where the ... is whatever the type of NDISCOMPLFAB is.

Also, the GROUP BY clause should be redundant because of the TOP 1.


Matching a column from a table to another table I have 2 tables in a Oracle database and if I do:

SELECT ID,ID_EXTERN_FOR_JOB FROM SOL_PUBLIC.SOL_SUBSOL_CHESTIONARE@RACDB
WHERE ID=1207;

I get:

ID      ID_EXTERN_FOR_JOB
1207    -1

On the other table, if I do:

SELECT ID,ID_EXTERN_FOR_JOB FROM SOL.SOL_SUBSOL_CHESTIONARE
WHERE ID_EXTERN_FOR_JOB=1207;

I get

ID      ID_EXTERN_FOR_JOB
2009    1207

And I want to find out all the rows that don't match this "rule" of having the ID_EXTERN_FOR_JOB from SOL in ID FROM SOL_PUBLIC and I don't know how to do that. Thanks!

Try this one:

SELECT  PRIV.id_extern_for_job
,       PRIV.id
,       PUB.id_extern_for_job
FROM    SOL_PUBLIC.SOL_SUBSOL_CHESTIONARE@RACDB     PUB
,       SOL.SOL_SUBSOL_CHESTIONARE                  PRIV
WHERE   PRIV.id_extern_for_job = PUB.id
AND     PRIV.id <> PUB.id_extern_for_job;


SQL Server BigInt vs Decimal I need to choose a data type for SQL Server( BigInt vs Decimal) on the basis of performance and querying (10 digit number)

If you don't need fractions, choose bigint. In your case it will be a byte smaller:

10 digit decimal is 9 byte: https://msdn.microsoft.com/it-it/library/ms187746.aspx

bigint is 8 byte: https://msdn.microsoft.com/it-it/library/ms187745.aspx

Plus, it is faster (it has no variable part, and it is an integer type, much faster processed by cpu).


Replace some rows in one table with rows of another table I have two tables and a set of primary key values. I want to replace all rows of the first table whose primary keys are in this set with the corresponding (having the same primary key) rows of the second table.

(The two tables have the same structure.)

How to do this with MySQL and PHPMyAdmin or (worse) command line?

You can assume that both tables have a row for every value of the primary keys in the set.

UPDATE table1, table2
SET table1.field1 = table2.field1,
    table1.field2 = table2.field2,
--  .....
    table1.fieldN = table2.fieldN
WHERE table1.id = table2.id
  AND table1.id IN ('id1', 'id2', /* ... */ 'idN' )

Or you can use

REPLACE table1
SELECT *
FROM table2
WHERE table1.id IN ('id1', 'id2', /* ... */ 'idN' )

but it can give undesired interferention if there is any additional unique index except primary one.


What does Error: 1204, Severity: 19, State: 4 mean? I've found this error in our SQL log:

  "The instance of the SQL Server Database Engine cannot obtain a LOCK
  resource at this time. Rerun your statement when there are fewer
  active users. Ask the database administrator to check the lock and
  memory configuration for this instance, or to check for long-running
  transactions. Error: 1204, Severity: 19, State: 4."

From last 5 days this exception is coming in our staging SQL Server. There is no other process running still it is publishing error every night.

Select @@Version

Microsoft SQL Server 2014 - 12.0.4213.0 (X64) Jun 9 2015 12:06:16 Copyright
Microsoft Corporation Enterprise Evaluation Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

Does anyone know how to fix this? How to diagnose such error and find who is real culprit for it.

Explanation:

This error occurs when there are not enough system locks to complete the current command. SQL Server then attempts to obtain a LOCK block to represent and control the desired lock. When dynamically configured, the lock limit is determined by the available memory. When statically configured, the lock limit is determined by the sp_configure setting.

Action:

you have two options earthier wait for sometime and re-execute the same command(s), or reconfigure locks value by executing the following commands

sp_configure locks, XXXXXX     where XXXXXX value higher than the original one
GO
RECONFIGURE WITH OVERRIDE
GO

Regarding troulbeshooting this error please refer to the log and execute the following command:

SELECT @@SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name';

This will show you the needed info, and you can check Activity Monitor too

some useful links:
http://www.sqlservercentral.com/Forums/Topic141154-92-1.aspx
https://technet.microsoft.com/en-us/library/aa258769(v=sql.80).aspx


Different execution plan with Rownum <NUM I have a query like this:

select *
  from (select ecards0_.CDMAINAPPTYP ,
               . . .
               ecards0_.CDISSUMTHD   as col_23_0_
          from PSAM952.KCCARDS ecards0_
          left outer join PSAM952.KWV_CUSTOMER_INFO ecustomer3_
            on ecards0_.CFCIFNO = ecustomer3_.Cus_No, PSAM952.KFTEMPLATE
         ecardtempl1_, PSAM952.KWV_CUSTOMER_INFO ecustomer2_
         where ecards0_.TMPLTID = ecardtempl1_.TMPLTID
           and ecards0_.CBC_CFCIFNO = ecustomer2_.Cus_No
    and ecards0_.CDISUDT <= TO_DATE('2016/06/30 1:00:01', 'YYYY/MM/DD HH:MI:SS')
    and ecards0_.CDISUDT >= TO_DATE('2016/06/01 1:00:01', 'YYYY/MM/DD HH:MI:SS')
           and (ecards0_.CDMAINAPPTYP in (4, 3))
         order by ecards0_.CDISUDT DESC)
 where rownum <= 20

When I use where rownum <= 20 the query cost in the execution plan is lower than when I don't use that where clause. Also the execution plans are different.
I can't understand why?

Plan with where clause:

Plan without where clause:

By artificially limiting the number of rows returned, it's doing 90% less work (based off cardinality, bytes, etc.).

The result is that the cost-based optimizer decided to do a nested loop instead of a hash join.

The optimizer is smart enough to limit the rows returned (using table and index statistics, foreign keys, etc.) all the way up the stack, so the nested loop will be a far more efficient join for only 200 rows (hash joins are memory hungry, but more efficient at scale).


What actually is a SQL clause? This may sound like an overly simple question but I'm not finding it easy to locate a proper answer.

To the question "What are sql clauses?" most of the resources on internet simply provide a list of clauses and explain what they do.

But I'm trying to understand in abstract terms how it is defined. Like a generic notation that captures all kinds of queries that can be written and how clauses fit in there.

Are there properties common to all clauses? Why is UNION called an operator whereas HAVING is a clause?

In general terms, a clause is just a logical chunk of a SQL statement - and, usually, is a chunk that is (theoretically) optional.

I'm a SQL Server guy, so I'll be thinking in those terms.

SELECT GETDATE() is a valid SQL Server SELECT statement.  It'll return the current date and time.

If I add a FROM clause:

SELECT GETDATE()
  FROM sys.objects

I'll get back n rows (where n is the number of rows in the sys.objects table), each of which will have the same (unnamed) column, showing the current date and time.

You can add a WHERE clause to limit the number of rows you bring back; a GROUP BY clause to either deduplicate your data, or to allow you to put aggregate values in the SELECT list; a HAVING clause (usually to eliminate rows based on aggregate data); an ORDER BY clause to sort the data - but, none of those have to be there.

You'll note I said "SELECT list" when referring to the list of columns returned. I've never heard this referred to as a clause - presumably, because it does have to be there.

An operator, on the other hand is used to combine or compare two complete items. Just like the + operator lets you combine numbers (1 + 2) or strings ([firstname] + ' ' + [lastname]), the UNION, EXCEPT, DIFFERENCE, and INTERSECT operators let you combine two SQL statements.

I'm probably oversimplifying, but thinking in these terms should at least head you in the right direction.


Automate Backup of SQL Server Database Structure I have a data warehouse which isn't huge only about 16Gb in a Microsoft SQL Server Standard Edition 2014 database. However, now that it forms part of our production system I don't like developing on it because it can affect our end users.

Therefore what I would like to do is automate a nightly process to:

Backup the structure of warehouse1
Restore the structure to a warehouse2 on the same server

So in effect I end up with a mirror of warehouse1 database structure in Warehouse2 database. I can then use this database for development.

As all of the data is retrieved from other locations I don't want to back the data up I just want to do the structure.

Does this seem like the most appropriate method of achieving what I want to do or is there an alternative? If this does sound right would you be able to suggest how I would go about achieving this?

Thanks in advance,

Andrew

Since you are using SQL Server 20141 you could look at using DBCC CLONEDATABASE to create a statistics-only copy of the warehouse database.

See support.microsoft.com for details on the command; however it essentially boils down to:

DBCC CLONEDATABASE (warehouse1, warehouse2);

From the page linked above:

  All files in the target database will inherit the size and growth settings from the model database. File name convention: The file names for the destination database will follow the source_file_name _underscore_random number convention. If the generated file name already exists in the destination folder, DBCC CLONEDATABASE will fail.

You could wrap this in code that drops the current clone, if one exists, then creates the new clone.  This could be added to a SQL Server Agent Job that executes nightly, or weekly, as required.

1 - you'll need service pack 2 or above for SQL Server 2014


Degrading PostgreSQL 8.4 write performance Load testing of postgresql 8.4 for OLTP application suitability resulted in unpleasant situation when throughput of the database significantly degraded over time from thousands of write transactions per second to almost zero. Write transactions are in given case insert/update/delete database transactions. The load driver used for testing the database executed SQL queries in parallel threads and used prepared statement and connection pooling. Postgres performance degraded in a couple of minutes after the first run of the test, and the problem was reproducible with only 2 parallel client threads. Subsequent test executions showed degraded throughput since the beginning. The degradation has been detected only in case of write transactions - select transactions were not affected. After some time or after server restart the problem is reproducible - test achieves high throughput and then degrades again. Linux top does not show any postgres processes performing any significant work, CPU usage during the test after degradation is <1%, io waits are also normal.
Machine used for the test is:

Red Hat Enterprise Linux AS release 4 (Nahant Update 6)
8 CPU @ 2GHz
16GB RAM
WAL and data are on separate SSD drives

Server is initially configured as dedicated OLTP transaction processing:
Options changed from default:

max_connections = 150
shared_buffers = 4GB
wal_buffers = 16MB
checkpoint_segments = 80
maintenance_work_mem = 2GB

Modified kernel params:

kernel.shmmax = 8932986880
kernel.shmall = 2180905
kernel.sem = 500 64000 200 256

Disabling and tuning autovacuum did not give any results.
Any ideas how to fight this problem?

Assuming it's not a VACUUM problem, it sounds like you've got an unindexed FOREIGN KEY. During the bulk load, do you see an increase in CPU or disk IO?

For giggles, can you please post the output from a VACUUM VERBOSE ANALYZE table_name? An EXPLAIN ANALYZE INSERT ... may also be useful along with as much of the relevant schema.


join two tables and limit results by an ordered list problem:
given is a table with language support (translations)
a user can configure a list of languages (order is priority)
now i have to get all rows which are part of app and the locale is in the list of preconfigured languages for a user;
only ONE result per property shall be returned
so for example the user configured de-AT, de-DE, de, en as preferred user settings
NOTE: not all values need to be defined!
the user requests all data for app='test'
the expected result is
 property, value
('father', 'Oida'),
('lastName', 'Nachname'),

Table for Multilanguage Data
CREATE TABLE IF NOT EXISTS `i18n` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `app` VARCHAR(20) NULL,
    `locale` VARCHAR(10) NULL,
    `property` VARCHAR(20) NULL,
    `value` VARCHAR(1000) NULL,
    PRIMARY KEY (`id`)
);

example values in i18n table
INSERT INTO `i18n` (app, locale, property, value)
VALUES
('test', 'de-AT', 'father', 'Oida'),
('test', 'de-DE', 'father', 'Papa'),
('test', 'de', 'father', 'Vater'),
('test', 'en', 'father', 'father'),
('test', 'fr', 'father', 'père'),
('test', 'en','lastName', 'last name'),
('test', 'de','lastName', 'Nachname'),
('test', 'fr','lastName', 'nom de famille'),

my idea was to put user config into its own table (if its possible without this it would be even better)
Table for User Config
CREATE TABLE IF NOT EXISTS `i18n_cfg` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `user` VARCHAR(20) NULL,
    `locale` VARCHAR(10) NULL,
    `priority` INT NULL,
    PRIMARY KEY (`id`)
);

example values in i18n_cfg table
INSERT INTO `i18n_cfg` (locale, user, priority)
VALUES
('de-AT', 'lm', 1),
('de-DE', 'lm', 2),
('de', 'lm', 3),
('en', 'lm', 4),
('de', 'hr', 1),
('en','hr', 2),

i'm not really getting to a point
i get only one result or way too much
NOTE using mysql 5.7 (if it would work on sql server too - with minor changes - it would be perfect)

you need to join table with priority and use ROW_NUMBER function to sort rows for each property, then you just need to leave rows with row number = 1:
WITH CTE AS (
SELECT t1.property
,t1.value
,ROW_NUMBER() OVER (PARTITION BY t1.property ORDER BY t2.priority) AS rn
FROM i18n t1
JOIN i18n_cfg t2 ON t1.locale = t2.locale
WHERE t1.app = 'test'
AND t2.user = 'lm'
)
SELECT property, value
FROM CTE
WHERE rn = 1

solution without window functions and variables:
SELECT t1.property
,(SELECT t2.value
  FROM i18n AS t2
  JOIN i18n_cfg AS t3 ON t2.locale = t3.locale
  WHERE t2.property = t1.property
  AND t2.app = 'test'
  AND t3.user = 'lm'
  ORDER BY t3.priority
  LIMIT 1) AS value
FROM
(
SELECT DISTINCT property
FROM i18n
WHERE app = 'test'
) AS t1

link to dbfiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=cf1d3f4757c3d84d3f5ad2bea0966a3c


Instead of using "Copy-and-paste" to copy from the user-interface; use a T-SQL script to copy the rows from one table to another.

Setup a test-bed with two tables, one will be the source, the other the destination:

USE tempdb;

CREATE TABLE dbo.StatusTable1
(
    StatusNumber NUMERIC(18,10)
);

CREATE TABLE dbo.StatusTable2
(
    StatusNumber NUMERIC(28,2)
);

INSERT INTO dbo.StatusTable1 (StatusNumber)
VALUES (1370.00000000)
    , (2850.00000000)
    , (426.00000000)
    , (289.00000000)
    , (2000.00000000);

this part copies rows from StatusTable1 to StatusTable2:

INSERT INTO dbo.StatusTable2 (StatusNumber)
SELECT st.StatusNumber
FROM dbo.StatusTable1 st;

Show the content of both tables:

SELECT *
FROM dbo.StatusTable1;

SELECT *
FROM dbo.StatusTable2;

Cleanup the test-bed:

DROP TABLE dbo.StatusTable1;
DROP TABLE dbo.StatusTable2;

The results:


Postgres-XL adding GTM Proxy seems to do nothing I've set up a Postgres-XL cluster using this recipe:

GTM:
hostname=host1
nodename=gtm

Coordinator：
hostname=host2
nodename=coord1

Datanode1：
hostname=host3
nodename=datanode1

Datanode2：
hostname=host4
nodename=datanode2

When I ran a load test against it, the GTM would fallover. I tweak settings until the GTM didn't fall over but only reported errors - thus kept on working after the load test.

I the added a GTM Proxy. I did not do init all but rather only init the proxy.  When I restarted the cluster, the GTM reported that the GTM proxy was up and running.  When I looked at the GTM proxy's log, it looked like it started up and was connected.

But when I ran the load test again, I got the same result with no log entries for the GTM proxy.  Thus it seems like the GTM Proxy didn't pick up the load processing as I expected it to do.

I don't know how to trouble shoot this. Any pointers on where to look next?

(I don't know what extra info to post here)

This is known bug/feature: pgxc_ctl does not know which proxy should be used by each coordinator / datanode (it does not assume that it is supposed to use a local one).

GTM proxy does not 'pick' the config; you need to instruct others to use the proxy.

You can fix it manually by editing postgresql.conf located in data directories for your datanodes and coordinators.

Find gtm_host and gtm_port and replace real GTM values with IP and port of your proxy. Or you can add these lines to the end of the file if this makes your scripts easier. Reload datanodes and coordinators to update their config (pg_ctl reload -D /data/directory/path)

Ideally you should have a proxy configured on every physical host, and instruct your datanode and coordinators to use the local proxy.


How to reclaim disk space on PostgreSQL? I have local installation of 9.1 database with few tables which had cca. 300 mio records and the database grew to about 20 GB. Afterwards I issued delete from command to delete all records from it (I should have used truncate, but I didn't know that). So I did full vacuum on my db to reclaim disk space, but it just doesn't help. My problem looks identical to this one, but there is no solution provided. I have already checked this thread and documentation on "recovering disk space", but still can't find a solution. I use this code to get size of all tables

 SELECT nspname || '.' || relname AS "relation",
 pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
 FROM pg_class C
 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
 WHERE nspname NOT IN ('pg_catalog', 'information_schema')
 AND C.relkind <> 'i'
 AND nspname !~ '^pg_toast'
 ORDER BY pg_total_relation_size(C.oid) DESC
 LIMIT 15;

Totalling to less than 1GB, however

SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database

still shows about 20 GB. Any advice much appreciated.

Although you did not state it, I assume from your references to documents that you have followed that you have done a VACUUM FULL on the database and/or affected tables. You also didn't specify what postgresql version you are using - I will assume it is > 9.0 (VACUUM FULL behaved differently before this).

VACUUM FULL will rewrite affected tables into new files, then delete the old files. However, if any process still has the old file open, the operating system will not actually delete the file - until the last process has closed it.

If practical, restarting the database would ensure that all open files get closed.

If that is not practical, then you may be able to verify if this is your problem, and find out which process has the files open.

If using Linux (or most other Unix-like systems) you can use the 'lsof' command to get a listing of all files open in all processes. Files which are open but which have since been deleted will have '(deleted)' appended to the filename. So, you can grep the output of lsof, looking for deleted files, like this:

sudo lsof -u postgres | grep 'deleted'

If that identifies processes which still have the old files open, you can use pg_terminate_backend to terminate that process:

SELECT pg_terminate_backend(xxx);

where xxx is the PID of the process, found in the lsof output.

If using Windows, the same principle could apply, because postgres opens files using the FILE_SHARE_DELETE flag, which allows it to delete files that are open in another process. The 'handle' command is the rough equivalent of lsof, although I am not sure if you can tell if the files are deleted or not so some additional work might be required.

It is another question as to why any such processes would be hanging on to old file handles. However in the thread you quoted in your question, Tom Lane seems to imply that it can happen.


What's the best way to migrate a database to a server of a lower SQL version? I have Database A on SQL Server 1 that is SQL Server version 2016.
I need to migrate it to Server 2 that is SQL Server version 2012.
Database A is pretty small, only about 5 GB total.

I don't think there's a way to migrate it via a Full backup, so what would be the best way to move the database?...BCP?...or is there better methods?

BCP by itself may not be sufficient as it won't address non-table objects like stored procedures. To migrate the schema, one option would be to use a Visual Studio database project importing it from the 2016 instance and deploying it to your 2012 instance. Afterwards, two options to import your data rows are BCP and the Import/Export wizard in SSMS.


In Mysql 5.5, why is the upper bound of a character range not inclusive? So I'm trying to query on a character range, i.e. all manufacturers with names starting with a or b:

SELECT manufacturer FROM warehouse WHERE manufacturer >= 'a' AND manufacturer <= 'b'

or:

SELECT manufacturer FROM warehouse WHERE manufacturer BETWEEN 'a' and 'b'

For both of these queries, I only get back manus with names starting with a. If I changed the range to a-c, I would get back all with a-b. Why is the upper bound of the range not inclusive? My main issue with this is how can I include all 'Z's in the range. A-Z would not include any z's in this case.

The manual here: http://dev.mysql.com/doc/refman/5.5/en/comparison-operators.html#operator_between
... seems pretty self explanatory. Unfortunately things are not working the way they should.

The weird part is, this works when I create a small test table. However it does not work properly on my 15 million row table.

Perhaps you can try REGEXP with the BINARY operator

SELECT manufacturer FROM warehouse
WHERE manufacturer REGEXP BINARY '^[ab]';

if you want a range (say from A-M)

SELECT manufacturer FROM warehouse
WHERE manufacturer REGEXP BINARY '^[A-M]';

case sensitivity ranges

SELECT manufacturer FROM warehouse
WHERE manufacturer REGEXP BINARY '^[A-Ma-m]';

Notice I used the ^ regular expression operator. This searches "from the beginning of the string".

As for your question, why does BETWEEN 'a' and 'b' fail to get all b's?

Look at what you are asking for in the query: Every manu that is >= 'a' and <= 'b'.

Does 'a' return?. If you have a manu named 'a', then yes.
Does 'aa' return?. If you have a manu named 'a', then yes.
Does 'azzzzzzzzzzzzzzz' return?. I think you know the answer to this one.
Does 'b' return?. If you have a manu named 'b', then yes.
Does 'ba' return?. Even if you had a manu named 'ba', then no.

Although inefficient, here is a query that would have worked:

SELECT manufacturer FROM warehouse
WHERE LEFT(manufacturer,1) IN ('a','b');

UPDATE 2013-02-11 15:22 EDT

I have a nice suggestion: Create an index that only stores two characters of manufacturer.

Perhaps this:

ALTER TABLE manufacturer
ADD INDEX manufacturer_prefix (manufacturer(2));

If you have any index of the entire manufacturer field, drop that index. You can then do effective two-character range searches.


Permissions To Update Legacy DTS Packages I am trying to grant access to allow users to view and update existing legacy DTS packages. The problem is that they cannot see the needed nodes under Management to begin working on them. I've tried granting every possible permission, including making them db_owners of the MSDB but nothing so far has worked. If I grant CONTROL at the server level the nodes become visible but this is certainly not a sustainable set of permissions.

Attached are 2 images. One of the nodes as I see them as one as they seem them. I've prowled around the web trying to identify the minimum rights needed but have so far not come up with the right formula.

Does anyone know what the minimum rights for this process are or am I stuck making them temporary super-users?

This is what I see

This is what they see

Thanks!

The only way we were able to find a way around this was to grant the user SysAdmin rights. Even with CONTROL at the server level, it is not available.


Granting permissions to SSIS for developer I installed SSIS a couple of months after installing SQL Server 2014.
I granted permissions to users per MSDN and rebooted SSMS and SSIS.
I can login remotely from my client without issue, but have sysadmin rights.

The other user cannot even see instance when trying to log in via client
Only way for her is to log in remotely to server, right click as administrator.

Any thoughts or suggestions on why this is?

Edit

It's a developer creating stored procedures and wants to store packages. There is going to be approximately 50 packages when complete with this project. She uses SSIS. I'm not that familiar with SSIS at this time.

Permissions to assign her are going to be based on what deployment model she is going to use within your environment. SSIS 2012/2014 added an additional model: Project and Package

For Package Deployment model she has the option of deploying the packages to msdb. Granting her permissions for this type of deployment you will grant her permissions to an SSIS role. If she is the only person that is going to be working with the packages you can simply grant her db_ssisltduser. This will provide her permissions to do anything she needs with the packages she creates and deploys (making her the owner of those packages). You can read through the article linked to get an idea of more.

For Project Deployment model you will need to work with the SSISDB catalog. With this it is just granting permissions at a database-level role. If this is not already created on the instance you will need to do this for her. Once it is created you should see a database that will have a specific role called ssis_admin, this role is special to the catalog database and is not a normal role found in every database.

The database will show up under the Databases node in Object Explorer, just go to Security>Roles>Database Roles and find the ssis_admin role. Within the properties of that role you can add her login.


Design for an Asset Management System I'm attempting to design a database to track IT Fixed Assets and Consumable Parts; what is the best practice for this?

Should I design separate tables for Fixed Assets and Consumables, or should I have just one table containing rows for both types of items, with a Category field?

If the Fixed Assets and Consumables have some identical columns, they can be in one table. Then create a category like table.


Duration of reclaiming disk space on very large tables (2.5+ billion rows) I'm using PostgreSQL 9.4.20 and have 1 table which at 1 point contained 2.5 billion rows. We've started migrating data and reduced the table size down to about 280 million rows.

If possible we'd like to reclaim the used disk space.I read up on VACUUM FULL and know that it will result in a full table lock during the operation. My question here is: given that a VACUUM of the table will take about 12000 - 13000 seconds, is there any way to estimate how long a VACUUM FULL will take give the current and previous size of the table?

Included is one of the VACUUM VERBOSE outputs during the deletion process:

INFO:  vacuuming "public.redacted"
INFO:  scanned index "redacted_prop_id_3df0d554fdfe92ee_idx" to remove 85063130 row versions
DETAIL:  CPU 105.78s/340.93u sec elapsed 3633.81 sec.
INFO:  scanned index "redacted_pkey" to remove 85063130 row versions
DETAIL:  CPU 35.37s/269.95u sec elapsed 1147.31 sec.
INFO:  "redacted": removed 85063130 row versions in 811189 pages
DETAIL:  CPU 12.00s/17.68u sec elapsed 342.43 sec.
INFO:  scanned index "redacted_prop_id_3df0d554fdfe92ee_idx" to remove 39116682 row versions
DETAIL:  CPU 101.30s/346.25u sec elapsed 3576.71 sec.
INFO:  scanned index "redacted_pkey" to remove 39116682 row versions
DETAIL:  CPU 40.21s/237.76u sec elapsed 1219.00 sec.
INFO:  "redacted": removed 39116682 row versions in 879306 pages
DETAIL:  CPU 22.85s/40.58u sec elapsed 817.57 sec.
INFO:  index "redacted_prop_id_3df0d554fdfe92ee_idx" now contains 1597286875 row versions in 1
DETAIL:  121144639 index row versions were removed.
4169907 index pages have been deleted, 3055311 are currently reusable.
CPU 0.03s/0.00u sec elapsed 0.30 sec.
INFO:  index "redacted_pkey" now contains 1597377401 row versions in 5999520 pages
DETAIL:  124175137 index row versions were removed.
1209655 index pages have been deleted, 894966 are currently reusable.
CPU 0.01s/0.00u sec elapsed 0.17 sec.
INFO:  "redacted": found 107389052 removable, 820955017 nonremovable row versions in 7045430 oes
DETAIL:  0 dead row versions cannot be removed yet.
There were 13483069 unused item pointers.
0 pages are entirely empty.
CPU 382.60s/1408.62u sec elapsed 12543.69 sec.
INFO:  analyzing "public.redacted"
INFO:  "redacted": scanned 30000 of 15055610 pages, containing 3170455 live rows and 0 dead rosample, 1591104467 estimated total rows

I chose a different approach to reclaiming the diskspace. A lot of data was used for the pkey and prop_id_3df0d554fdfe92ee_idx indexes. It was possible to halt all writes to the table, during which time I issued a REINDEX command for the individual indexes.

After rebuilding the indexes enough diskspace was reclaimed to allow a secondary table to be created based on the contents of the original table. Afterwards the original table was deleted and the second table renamed.


Reports with recurring events DB Schema in SQL Server I'm trying to create a scheduling reporting feature where a user can schedule a report to be received "every 1st Monday of the month" or "every last day of the month" or "every week on Tuesday".

I saw an answer that talks about this somewhat, but I think it's more complicated than I need it.  Calendar Recurring/Repeating Events - Best Storage Method.
 My reports will always be starting immediately (not starting on a specific date) and it won't be rendering to a calendar. All I need to display is what options were chosen so it can be edited (what I mean to say is I don't need to display specific instances of a scheduled report but do need to display the rule.) The rules need to be stored in the DB and I'm at a loss on where to even begin. Any help would be greatly appreciated!

Take a look at the way SQL Server handles this for SQL Agent job schedules. Specifically, read through the columns prefixed freq_* as these determine the frequency of execution.

You could use the same or similar table schema to store frequency information about your reports. You could even display 'next scheduled date' information quite easily if you also stored the creation date of the schedule for a particular report.

There is a good article on SQLServerCentral that dives into this and should help you develop a solution that would suit.


Sql Server Management Studio for 2008 Express? I just installed Win7 on a fresh laptop and want to put a lightweight MS Sql Sever on it.  VS2010 already installed SS2008 Express.  So now I just need a management studio for it.

What would you recommend?  I see there is a Management Studio Express, and have also read that you can install Management Studio from the SS2008 full discs.  Is there a preferred route to take?

If you are licensed to use the full Management Studio, I would install that. It has more features that will come in handy if you ever need to manage other SQL Server instances other than your local development/Express version. If you're not licensed or you know this is not a requirement, you can download Management Studio Express from here:

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

Yes, that's for 2008 R2; probably better to get the most recent release, which will work just fine with 2008.

Whether you end up using the full version or the Express version, can't hurt to install Service Pack 1 once SSMS is installed:

http://www.microsoft.com/download/en/details.aspx?id=26727

(Probably more appropriate for the full version, since there is a greater chance that issues are fixed since it includes more features.)


How long will it take to migrate a 5 GB Database to Amazon RDS I just need an estimation.

How long will / can it take to move a 5 GB big database from an external source to amazon RDS. What is best practise to do it?

I just moved succesfully a few days ago a small test database (wordpress) from an external webhost to amazon. But without compression etc.

I`d say it would take 4-5 hours to move a 5 GB database, but I have not so much experience and I am unsure.

Thanks.

The usual answer: It depends

I'm assuming that you've read this guide on how to import data into an Oracle RDS (I chose Oracle because it's what I know, and you don't say what instance type you're using): http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Oracle.Procedural.Importing.html

There's similar guides for MySQL: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Importing.NonRDSRepl.html

Have a look at the guides and do some tests, they should provide you with some idea of timescales.


Why would you need to put a domain name when install a Oracle database? When you install an Oracle database or instance, you are able to assign a domain name.

An Example:

SID: orcl

Domain: world.com

Service: orcl.world.com

Why would it be necessary to add or configure a domain?

It allows you to have more than one DB with the same name in the same network. If you have only one it can be the network domain.


PostgreSQL - triggers to fire only on replicated db Is it possible to fire triggers only on a read-only Postgres replicated database?

I want triggers to fire only on the replicated database - not on the master (to avoid load). This is to track - say - all updates/deletes.

Would enabling replica trigger be the way to go?
On the primary/master, the triggers remain disabled, but in the replicated db, we can have the triggers fired?

The wal2json plugin is an option, but triggers seem a safer bet.

You can do that with logical replication, but not with streaming (physical) replication, because in the latter case the standby server is read-only.

For logical replication, you have to define the trigger as replica trigger:

CREATE TRIGGER tgname ... ON TABLE atable ...;
ALTER atable ENABLE REPLICA TRIGGER tgname;