Database atomic operations implementation The question is about queries that are not wrapped in 'begin-commit' block, but about plain inserts and updates that are atomic in PostgreSQL, MySQL (innodb engine at least). So how is this implemented internally?

A transaction is started for each statement that occurs outside of an explicit transaction block. Whether a commit is automatically issued following the statement is dependent on the RDBMS configuration. MySQL has the autocommit option, SQL Server has IMPLICIT_TRANSACTIONS, PostgreSQL is always auto commit.

PostgreSQL:

  In the standard, it is not necessary to issue START TRANSACTION to
  start a transaction block: any SQL command implicitly begins a block.
  PostgreSQL's behavior can be seen as implicitly issuing a COMMIT after
  each command that does not follow START TRANSACTION (or BEGIN), and it
  is therefore often called "autocommit". Other relational database
  systems might offer an autocommit feature as a convenience.

InnoDB:

  In InnoDB, all user activity occurs inside a transaction. If
  autocommit mode is enabled, each SQL statement forms a single
  transaction on its own. By default, MySQL starts the session for each
  new connection with autocommit enabled, so MySQL does a commit after
  each SQL statement if that statement did not return an error.

SQL Server:

  SQL Server operates in the following transaction modes.

  Autocommit transactions - Each individual statement is a transaction.

  Explicit transactions - Each transaction is explicitly started with
  the BEGIN TRANSACTION statement and explicitly ended with a COMMIT or
  ROLLBACK statement.

  Implicit transactions - A new transaction is implicitly started when
  the prior transaction completes, but each transaction is explicitly
  completed with a COMMIT or ROLLBACK statement.


Cleaning up cut and pasted outlook email addresses I often have to create or update e-mail distribution lists in SSMS, where the requestor will ask "please add Anne Hathaway, Albert Einstein, and Ash Ketchum to the DL.  Get their email from Outlook."

Once I put their names in outlook and push the "Check Names" function, it validates they're still in the system.  Copying this list then produces the following output.

Anne Hathaway < Anne.Hathaway@Email.com >; Albert Einstein < Albert.Einstein@email.com >; Ash Ketchum < Ash.Ketchum@mail.com >

It's a manual process to remove the original name and < > signs, which of course could open itself to error.

How can I set a variable that, when I paste in the raw data straight from outlook ,it cleans it up so that it gets rid of everything left of the < up to but not including the next encountered ; ?

I'd like the variable to print like so.
'Anne.Hathaway@email.com; Albert.Einstein@email.com; Ash.Ketchum@mail.com'

You can use a recursive common table expression to iterate through the string variable:

DECLARE @outlook nvarchar(max)='Something.something@email.com;Anne Hathaway < Anne.Hathaway@Email.com >; Albert Einstein < Albert.Einstein@email.com >; Ash Ketchum < Ash.Ketchum@mail.com >';

WITH semi AS (
    SELECT CAST(NULL AS nvarchar(255)) AS part, @outlook AS remain
    UNION ALL
    SELECT CAST(LEFT(remain, CHARINDEX(';', remain+';')-1) AS nvarchar(255)),
           CAST(SUBSTRING(remain, CHARINDEX(';', remain+';')+1, LEN(remain)) AS nvarchar(max))
    FROM semi
    WHERE remain!='')

SELECT +LEFT(addr, CHARINDEX('>', addr+'>')-1)
FROM (
    SELECT LTRIM(SUBSTRING(part, CHARINDEX('<', part)+1, LEN(part))) AS addr
    FROM semi
    WHERE part IS NOT NULL
    ) AS sub;

Ok, so what's going on here is that the "semi" common table expression splits the string using the semicolon character (and if one of your names has a semicolon in it, you're out of luck).

Once we've split every name/address into its own row, the bottom SELECT gets only the string between the < and > characters.

If you want to go a little crazy with it, you could use XML parsing to concatenate all those lines back on a single row:

WITH semi AS (
    SELECT CAST(NULL AS nvarchar(255)) AS part, @outlook AS remain
    UNION ALL
    SELECT CAST(LEFT(remain, CHARINDEX(';', remain+';')-1) AS nvarchar(255)),
           CAST(SUBSTRING(remain, CHARINDEX(';', remain+';')+1, LEN(remain)) AS nvarchar(max))
    FROM semi
    WHERE remain!='')

SELECT SUBSTRING(CAST((
    SELECT '; '+LEFT(addr, CHARINDEX('>', addr+'>')-1)
    FROM (
        SELECT LTRIM(SUBSTRING(part, CHARINDEX('<', part)+1, LEN(part))) AS addr
        FROM semi
        WHERE part IS NOT NULL
        ) AS sub
    FOR XML PATH(''), TYPE) AS nvarchar(max)), 3, 10000);

To round off the answer, if you have access to a SQL Server 2017 instance, this is just a few lines of code:

SELECT STRING_AGG(RTRIM(LTRIM(REPLACE(REPLACE(b.[value], '<', ''), '>', ''))), '; ')
FROM STRING_SPLIT(@outlook, ';') AS a
CROSS APPLY STRING_SPLIT(a.[value], ' ') AS b
WHERE b.[value] LIKE '%@%';


File is getting generated into folder, But not a csv file For the below SP, normal text file is getting generated instead of csv file.
ALTER PROCEDURE [dbo].[GenerateCDR_Test]
AS
BEGIN
DECLARE @FileName varchar(50),

 @bcpCommand varchar(2000)

SET @FileName = 'C:\TransactionsData\'
              + ('Tansactions_'+  (CONVERT(VARCHAR, GETDATE(), 112) + '_'
             + CAST(DATEPART(HOUR, GETDATE()) AS VARCHAR) + '_'
             + CAST(DATEPART(MINUTE,GETDATE()) AS VARCHAR) + '_'
             + CAST(DATEPART(SECOND, GETDATE()) AS VARCHAR))  + '.csv');

SET @bcpCommand = 'BCP '+'"SELECT * FROM [LinkVisaCard].[dbo].[CDRGeneration]"' +' queryout '

SET @bcpCommand = @bcpCommand + @FileName + ' -U abc -P password -w'

EXEC master..xp_cmdshell @bcpCommand
END

On your BCP command use the -t switch to specify which field delimiter you want to use
https://docs.microsoft.com/en-us/sql/relational-databases/import-export/specify-field-and-row-terminators-sql-server?view=sql-server-ver15


Creating indexes on AutoId and time column My table structure is as follows

Events(
  AutoId int identity(1,1) //Primay Key, clustered index
  EventTime   datetime          // non unique, non clustered index
  FileName    nvarchar(1000)
  SystemName  nvarchar(1000)
  other columns
)

values in time column is almost increasing

Queries:
  List all events for last 7 days, last 1 month
  List all events where order by EventTime and SystemName or FileName like something

EventTime is present in most of the queries either through order by or filter clause.

Data Variations:
  Events table can have a two or three million rows
  It can have data for past one year

Non clusterd index on EventTime column is not always used because it can't filter enough data.

What all options can I try to improve overall query performance?

For history tables such as this one, it's often appropriate to create the clustered index with a date field as the leading column, and have the primary key index nonclustered.

In this case, since you know EventTime is non-unique, you can add the AutoId column to the key to make it unique (instead of relying on a uniquifier).

This is how the clustered index would be created (after first dropping the existing primary key constraint):

CREATE UNIQUE CLUSTERED INDEX IX_Events_EventTime_AutoId
    ON [Events](EventTime, AutoId);

And then recreate the primary key:

ALTER TABLE [Events] WITH CHECK
    ADD CONSTRAINT PK_Events
        PRIMARY KEY NONCLUSTERED(AutoId);

This setup allows for efficient date-range-based queries like the examples you've provided, and still doesn't create too much fragmentation to the base table as the clustering key is generally ever-increasing.


MySQL stores BLOB in innodb buffer pool (innodb_buffer_pool_size)? I have several databases all in INNODB, 50GB are BLOB columns, only 700MB are not BLOB data. Tools as mysqltuner ask me to set innodb_buffer_pool_size = 51GB.

Does MySQL store the BLOB in RAM? If yes, how can I configure MYSQL so it doesn't use innodb_buffer_pool_size to store BLOB?

Part 1 of the answer to your question is no, you can't selectively tell MySQL what not to store in the InnoDB buffer pool.  It's an integral part of how InnoDB manipulates table data.

Part 2 is that you don't really want to or need to, because it's smart enough to work out what should stay there if the pool is not large enough to retain your entire data set.

Incidentally, I might have mentioned previously... don't trust tuning scripts.

At best, they may give you "interesting things" to investigate and read up on, but it's absolutely impossible to properly "tune" a MySQL server without understanding the nature of the data that's on that server... and, you're just as likely to tweak your performance downward as well as up.

InnoDB doesn't load anything into the buffer pool that isn't in some way useful.  Leave a newly-restarted MySQL server alone without running any queries, and the buffer pool will sit there, allocated but mostly empty.  SELECT from a table, and some number of the pages from that table's tablespace on disk will be loaded into the pool, but not usually all of them.  Which pages depends on which rows you retrieve, what indexes are needed to find them, whether it's doing read-ahead, etc.

UPDATE a table and the pages with the rows you update will be loaded into the pool, modified in the pool, and then written back to disk in the background.  INSERT and DELETE bring pages into the pool and ultimately flush them back to disk also.

But InnoDB keeps track of how "useful" what's currently in the buffer pool is, how recently it was needed, whether any changes have already been written back to disk or not, and will evict pages to make room for other pages when the pool is full.  Many of my production servers have an InnoDB buffer pool that is smaller than the server's data set and essentially 100% full 100% of the time, but there's no performance impact from that when a large amount of the data hasn't been accessed for some time.

If a page in the pool hasn't been changed since it was loaded, or if it has been changed but since that time the changes have been written back to disk, and it hasn't been needed for a while, that chunk of the pool is readily reusable for something else the next time InnoDB wants to load something back into the pool from disk... so it's "in use" but still "available."

Unless you're frequently accessing all 50GB of those blobs, the size recommendation from the tuning script is overblown.  Of course, in an ideal world, your pool would be larger than all of your tables and indexes and you'd have an all-in-memory database that only had to access the disk to flush changes out... but in the real world of real data, the chances are pretty good that a large portion of your data is not frequently accessed, and there's no way for a simple script to anticipate those conditions.


MYSQL: Merge data from one column to another I have a table named my_jobs that I am trying to copy a column named "location" into two other columns called "city" and "state." The city and state data need to be extracted in their own respective columns. The city and state columns are already created. The "location" column has multiple different city and state information separated by a comma (Mountain View, CA). I've tried using:

Replace INTO my_jobs (city)
SELECT substring_index(location, ", ",1)
FROM my_jobs;

But that adds new rows and doesn't merge the data into the existing fields.

You need to update your table not insert

update my_jobs set city=substring_index(location, ", ",1) ,
state=SUBSTRING(location, LENGTH(location)-2, LENGTH(location))

If you want more explanation add a comment


WITH cte AS ( SELECT *,
                     DENSE_RANK() OVER ( PARTITION BY interviewee_uid
                                         ORDER BY interviewer_uid) iid,
                     ROW_NUMBER() OVER ( PARTITION BY interviewee_uid,
                                                      interviewer_uid
                                         ORDER BY idate DESC) did
              FROM datatablename )
SELECT *
FROM cte
WHERE did = 1
  AND iid <= 3;

fiddle

UPDATE.

Previous query contains logical error.

Edited variant:

WITH
cte1 AS ( SELECT interview_id, interviewee_uid, interviewer_uid, idate,
                 ROW_NUMBER() OVER (PARTITION BY interviewee_uid,
                                                 interviewer_uid
                                    ORDER BY idate DESC) did
          FROM test ),
cte2 AS ( SELECT interview_id, interviewee_uid, interviewer_uid, idate,
                 ROW_NUMBER() OVER (PARTITION BY interviewee_uid
                                    ORDER BY idate DESC) did
          FROM cte1
          WHERE did = 1
)
SELECT interview_id, interviewee_uid, interviewer_uid, idate
FROM cte2
WHERE did <= 3;

fiddle (with demonstration that previous query is wrong).


Connecting 4 tables - finding some differences between them I have 4 tables in a Oracle database:

SOL_PUBLIC.SOL_CHEST@RACDB (PUB):

ID     ...
100    ...
200    ...
300    ...

SOL_PUBLIC.COORD@RACDB (PUB):

ID FK_ID_CHEST  x  y
1  100          11 11
2  200          22 22
3  300          33 33

SOL_CHEST (PROD)

ID     ...
100    ...
200    ...
300    ...

SIM_SOL.COORD (PROD)

ID FK_ID_CHEST  x  y
1  100          11 11

so, some rows are missing from PROD part.
I want to get all rows missing from PROD.COORD table that are in PUB.COORD table.

I first tried to get all 4 tables "in the same place"

select *
    from SOL_CHEST PROD,
        SOL_PUBLIC.SOL_CHEST@RACDB PUB
    inner join SOL_PUBLIC.COORD@RACDB coord_pub
        on coord_pub.FK_ID_CHEST=PUB.ID
    inner join SIM_SOL.COORD coord_prod
        on coord_prod.FK_ID_CHEST=PROD.ID

but it gives me

[Error] Execution (67: 40): ORA-00904: "PROD"."ID": invalid identifier

how to do this (connecting the 4 tables and get the rows missing from prod.coord that are in pub.coord)
Thanks!

you don't need to join all 4 tables, this will show the missing rows

select id, fk_id_chest, x, y from SOL_PUBLIC.COORD@RACDB
minus
select id, fk_id_chest, x, y from SIM_SOL.COORD


Insert with function result - prevent duplicates I have a MS Sql function which has some logic to process the next ID of an element (concats letters with numbers based on some business logic, also based on a select count()).

Somehow, during the last days (in production environment), since the system is being used by more concurrent users we got some doubles.

The existing logic is very simple:

insert into XXXX (A,B,C,D,E)
select dbo.GiveMeNewID(getdate()), @ParamB, @ParamC, @ParamD, @ParamE

Somehow, the GiveMeNewId gave back the same ID in two parallel requests.

This funcion is used at the moment in different places, inside one Stored Procedure and also from an "dynamic" SQL build from a "C# client".

I do not want to implement a "singleton" layer in some middle man software.

Any ideas on how to solve this issue?
Thanks.

P.S: for testing I had the following lines inside the SQL function in order for me to run different the insert in different windows.

/*********************************************************/
declare @dtStart datetime=getutcdate()
while datediff(second,@dtStart,getutcdate()) < 10
begin
    set @dtStart = @dtStart
end
/*********************************************************/

In my test scenario I think I am able to solve this problem by writting:

  insert into XXXX with (tablock, xlock) (A,B,C,D,E) .....

At least now I am not able to replicate duplicated entries, but I am not sure if I am creating some problems where I am not aware.

If you think this is not a solution, please let me know.


How to group rows while filtering out values per each group? Imagine you have the following setup:

CREATE TABLE namechanges (
  id text,
  new_name text,
  change_date timestamp
);

INSERT INTO namechanges VALUES
  ('x', 'alice', '2020-03-01'),
  ('y', 'Bob T.', '2020-03-03'),
  ('x', 'Alice', '2020-03-05'),
  ('z', 'charlie', '2020-03-07'),
  ('x', 'Alice C.', '2020-03-09'),
  ('z', 'Charlie Z.', '2020-03-11')

How would look like a query that would retrieve just the current name for each id and return the following?

| id  | max        |
| --- | ---------- |
| z   | Charlie Z. |
| y   | Bob T.     |
| x   | Alice C.   |

Here's the example above on DB Fiddle if you want to play with it: https://www.db-fiddle.com/f/ugNcXhRyb44KTpqjFXmKDW/0

In Postgres this can be solved using distinct on ()

select distinct on (id) *
from namechanges
order by id, change_date desc;

This is typically faster than using aggregation or Window functions.

Online example


Removing some English token words (such as “are” or “or” etc.) from the system stoplist of full text search? One of our table has full text catalog on it. It contains some data in a language other than English which makes some words such as "are", "or" useful. There is no stop list defined but these words are considered as system stop lists therefore won't appear in the index keywords. How can I remove these words from the system stop list and add them to the indexed keywords?

Thank you.

You can turn off the system stoplist and create your own, eg

ALTER FULLTEXT INDEX ON dbo.yourTable SET STOPLIST = OFF

CREATE FULLTEXT STOPLIST myCustomStoplist;

ALTER FULLTEXT STOPLIST myCustomStoplist ADD 'someWord' LANGUAGE 'English';

ALTER FULLTEXT INDEX ON dbo.yourTable SET STOPLIST = myCustomStoplist

If you create an empty stoplist, it means all words are considered, or to put it another way, words that were previously considered noise words are no longer.  You will need to add some words to avoid index bloat, and as mentioned in my other post, if you really leave the word 'a' out of your stoplist, searches for 'a' will return most of your documents, somewhat defeating the object of full-text index ( ie tightly scoped searches ).


Recover data from accidental update I have done an incorrect update and hence I restored the DB with a backup from the previous day. I have not taken a copy of the corrupted data before restoring the backup. Is there any possibility to get back the data lost between the creation of back up file and restoring the file?

Thanks for saving my job

If you did not take a log backup between the full yesterday and your restore today, then no, everything changed since yesterday is gone.

In a best case "accidently deleted data" scenario, the process is to take a log backup to capture the "tail"of the log, restore a full backup of the DB to an alternate location, restore log backups using STOPAT to get to the point right before the accidental delete and copy the data.

If you don't have the room to restore an extra copy, or the accidental deletion is severe enough that you must restore over the existing database, then it is twice as important to backup up the tail of the log.


Availability Group Listener - Dedicated Server? Should an AG Listener be set up on it's own server? If so, does it require it's own SQL Server license?

The listener is part of the Availability Group and will reside in the resource group of the Availability Group in the Windows Server Failover Cluster. Thus it is not possible to have a specific listener server as the listener will move with the owner of the resources in WSFC.


ORA-00904: Invalid Identifier when selecting from view I am attempting to view a view with:

select * from DISCOUNT_AMOUNTS;
ERROR at line 1:
ORA-00904: "SALE_GIFT_VOUCHER_AMOUNT_AMOUN": invalid identifier

I then check the view to see what is there in view.

DESC DISCOUNT_AMOUNTS;
 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------------- -------- ------------------------------------------------------------
 LINE_ITEM_AUDIT_ID                                                                        NOT NULL NUMBER(18)
 CREATED_DATE                                                                              NOT NULL DATE
 SALE_AMOUNT_AMOUNT                                                                                NUMBER
 SALE_PROMPT_PAY_AMOUNT_AMOUNT                                                                      NUMBER
 SALE_QUANTITY_AMOUNT_AMOUNT                                                                        NUMBER
 SALE_RETAILER_AMOUNT_AMOUNT                                                                        NUMBER
 SALE_MANUFACTURE_AMOUNT_AMOUNT                                                                     NUMBER
 SALE_REBATE_AMOUNT_AMOUNT                                                                          NUMBER
 SALE_TRADE_IN_AMOUNT_AMOUNT                                                                        NUMBER
 SALE_REWARD_RETL_PROM_AMOUNT_A                                                                     NUMBER
 SALE_REWARD_MANU_PROM_AMOUNT_A                                                                     NUMBER
 SALE_REWARD_CC_AMOUNT_AMOUNT                                                                       NUMBER
 SALE_GIFT_VOUCHER_AMOUNT_AMOUN                                                                     NUMBER

I then check it directly with single quotes of

select 'SALE_GIFT_VOUCHER_AMOUNT_AMOUN' from DISCOUNT_AMOUNTS;

which returns successfully.

I then attempt:

SQL> alter view DISCOUNT_AMOUNTS compile;
View altered.

Reattempt

select * from DISCOUNT_AMOUNTS;
ERROR at line 1:
ORA-00904: "SALE_GIFT_VOUCHER_AMOUNT_AMOUN": invalid identifier

This is not making sense to me.  I can compile, it exists in view, but it won't select in wild character search.  What else can I try?  It is 30 characters as well and running command as user who created the view.

This is the create or replace view statement.  Am on 12.1, which is 30 characters I believe. 12.2 is 128 for identifiers.

CREATE OR REPLACE VIEW DISCOUNT_AMOUNTS
AS
SELECT
LINE_ITEM_AUDIT_ID,
CREATED_DATE,
SALE_AMOUNT_AMOUNT,
SALE_PROMPT_PAY_AMOUNT_AMOUNT,
SALE_QUANTITY_AMOUNT_AMOUNT,
SALE_RETAILER_AMOUNT_AMOUNT,
SALE_MANUFACTURE_AMOUNT_AMOUNT,
SALE_REBATE_AMOUNT_AMOUNT,
SALE_TRADE_IN_AMOUNT_AMOUNT,
SALE_REWARD_RETL_PROM_AMOUNT_A,
SALE_REWARD_MANU_PROM_AMOUNT_A,
SALE_REWARD_CC_AMOUNT_AMOUNT,
SALE_GIFT_VOUCHER_AMOUNT_AMOUN
FROM
(SELECT LINE_ITEM_AUDIT_ID,
DISCOUNT_TYPE_ID,
DISCOUNT_AMOUNT,
CREATED_DATE
FROM LINE_DISCOUNT_AUDIT
) PIVOT ( SUM(DISCOUNT_AMOUNT) AS AMOUNT FOR (DISCOUNT_TYPE_ID) IN (
1 AS SALE_AMOUNT,
2 AS SALE_PROMPT_PAY_AMOUNT,
3 AS SALE_QUANTITY_AMOUNT,
4 AS SALE_RETAILER_AMOUNT,
5 AS SALE_MANUFACTURE_AMOUNT,
6 AS SALE_REBATE_AMOUNT,
7 AS SALE_TRADE_IN_AMOUNT,
8 AS SALE_REWARD_RETL_PROM_AMOUNT,
9 AS SALE_REWARD_MANU_PROM_AMOUNT,
10 AS SALE_REWARD_CC_AMOUNT,
11 AS SALE_GIFT_VOUCHER_AMOUNT));

Your test case works perfectly as-is: DB Fiddle

Is there another object called LINE_DISCOUNT_AUDIT in the database? There may be a synonym in play here. Recreate the view and qualify LINE_DISCOUNT_AUDIT with the schema name (eg: YOURUSER. LINE_DISCOUNT_AUDIT).


Grouping records based on intervals of time I have a table with the following schema, and I need to define a query that can group data based on intervals of time (Ex. records per minute) and then provide the sum of the changes to the SnapShotValue since the previous group. At present, the SnapShotValue always increments so I only need the sum of differences. Can anyone help with a SQL Server T-SQL query that might do this? I am open to changing the schema, but this is what I currently have.

Schema

CaptureTime   (datetime)
SnapShotValue (int)

Sample data

1 Jan 2012 00:00:00,   100
1 Jan 2012 00:00:30,   125
1 Jan 2012 00:01:00,   200
1 Jan 2012 00:01:30,   300
1 Jan 2012 00:02:15,   400
1 Jan 2012 00:02:30,   425
1 Jan 2012 00:02:59,   500

Desired Query Result

1 Jan 2012 00:01:00,   225 -- Sum of all captured data changes up to the 1 minute mark
1 Jan 2012 00:02:00,   500 -- Sum of all captured data changes up to the 2 minute mark
1 Jan 2012 00:03:00,   125 -- Sum of all captured data changes up to the 3 minute mark

select dateadd(minute, 1+datediff(minute, 0, CaptureTime), 0),
       sum(SnapShotValue)
from YourTable
group by dateadd(minute, 1+datediff(minute, 0, CaptureTime), 0)

SE-Data

datediff(minute, 0, CaptureTime) gives you the number of minutes since 1900-01-01T00:00:00.

dateadd(minute, 1+datediff(minute, 0, CaptureTime), 0) adds the number of minutes since 1900-01-01T00:00:00 to 1900-01-01T00:00:00 ending up with a datetime with only minutes.

The 1+ is there because you wanted the next minute.

To do the same with a 5 minute interval you need to do some calculations. Divide the minutes with 5 and multiply with 5 gives you the minutes rounded down to a 5 minute precision. This works because the result of an integer division in SQL Server is an integer.

dateadd(minute, 5 + (datediff(minute, 0, CaptureTime) / 5) * 5, 0)


DB2 SQL: How to select all days in a date range even if no data exists for some days I am really really new to the whole programming world.

I have found this previously. However I need rolling dates and into a SQL i am not aware what it is. Example of the SQL im using:

Select dd.date, dd.week, sum(p.policy_count) Policies,
from db.policies p
join global.dim_date dd on dd.date = p.trans_date
Where dd.date between (select week - 112 days from global.TODAY_DATE) and (select week - 1 day from global.TODAY_DATE)
Group by dd.date, dd.week

I tried plugging in the recursive CTE from the other thread but it errors out the whole time and I ma not sure of how to adapt it to DB2 language and make it rolling.

Any help much appreciated. Thanks

EDIT: I have been playing around and the temporary workaround is that we have a separate date table that in itself is counted as a value and can thus be joined as a union all to allow the 0 values to stand. However the closest I came without that is with:

select
dd.date, coalesce(sum(x.policy_count),0) policy_count, sum(x.POLICY_GROSS) TY_Gross, sum(x.POLICY_COMMISSION) TY_Comm

from global.dim_date as dd left join
( select p.trans_date, sum(p.policy_count) policy_count, sum(p.POLICY_GROSS) TY_Gross, sum(p.POLICY_COMMISSION) TY_Comm

from db.policies p join db.agents a on a.agent = p.agent

where p.trans_date between (select week - 112 days from global.TODAY_DATE) and (select week - 1 days from global.TODAY_DATE)
group by p.trans_date ) AS x on x.trans_date = dd.date

Where dd.date between (select week - 112 days from global.TODAY_DATE) and (select week - 1 day from global.TODAY_DATE)

group by dd.date

This does not work because it rejects p.policy_gross and p.policy_commission and returns the coalesce values as 1 instead of 0.

As per comment below I am not sure how to identify tables and I have asked around and no one knows what DB2 it is, I emailed the devs so will update that soon

EDIT 2: Tables

Dates = global.dim_date dd
Policies = db.policies p
Agents = db.agents a

When I add extra constraints after 'where dd.date..' e.g.

Where dd.date between (select date - 112 days from global.TODAY_DATE)
and (select date - 1 day from global.TODAY_DATE)
and a.agent in ('xxxxx','yyyyy')

When before all available dates, even with zeros, were returned are now omitted and i believe its because of the and clause.

You say you have a date table.  You don't give a name so I shall refer to it as DateTable and the column in it as TheDate.  You need to select from this table and LEFT OUTER JOIN to it.  That way all dates in the range will have a row in the output with NULL for rows where you don't have "real" data.  You can convert these to zero using COALESCE.  I'll use your first posted query as an example:

Select
    dd.date,
    dd.week,
    sum(COALESCE(p.policy_count, 0)) Policies
from DateTable dt  -- Your date table
left outer join magenta.policies p -- left join includes all dates and any polices that exist
    on p.trans_date = dt.TheDate
left outer join global.dim_date dd
    on dd.date = p.trans_date
-- use DateTable in the WHERE to get the full range of time.
Where dt.TheDate between (select week - 112 days from global.TODAY_DATE)
                  and (select week -   1 day from global.TODAY_DATE)
Group by
    dd.date,
    dd.week

Sorry, don't have a DB2 instance to hand to test it.  Forgive any typos I may have introduced.


Why can't "NEXT VALUE FOR" be used in a case statement I was writing an INSTEAD OF INSERT trigger and hit an error on this code:

CASE
   WHEN Inserted.KeyId IS NOT NULL THEN Inserted.KeyId
   ELSE NEXT VALUE FOR SomeSchema.NextKeyId
END

The error was:

  NEXT VALUE FOR function cannot be used within CASE, CHOOSE, COALESCE, IIF, ISNULL and NULLIF.

Reading up shows that this is a fairly hard limitation, which I guess I can accept, but I would like to know why this limitation in is place.

Seems odd to me that it can't just go call that method for the part of the set that matches that branch of the case statement.

Can someone explain why a CASE statement does not allow NEXT VALUE FOR?

To fully answer your question one needs to know intimate details of the SQL Server SQL engine implementation, and most (all?) people who have that knowledge likely have signed all sorts of NDAs and confidentiality agreements. What follows is my speculation.

Typically the scope of NEXT VALUE is the entire SQL statement where it is referenced. This probably means that the sequence value is generated and made available independently of other features of that SQL statement. This has a semantic conflict with the conditional use of NEXT VALUE in the situations described by the error message. In other words, the user doesn't know if the next sequence value has or has not been consumed, depending on some nondeterministic condition, but at the same time it has already been generated independently.

So my guess is that the restriction aims to prevent this semantic ambiguity. What you can do instead is (in pseudocode)

vNext = NEXT VALUE FOR mySeq;
...
CASE
  WHEN Inserted.KeyId IS NOT NULL THEN Inserted.KeyId
  ELSE vNext
END

which doesn't have that ambiguity -- it is explicitly said that one sequence value is consumed every time, though it may or may not be actually used.


How to Oracle DBLink encryption I have the environment that I explain below and I need to encrypt the data that is sent between my two nodes, I have been reading but I can not find the right solution, I explain:
I have an Oracle Database 12c in which I have Advanced Security implemented through the netmgr but the encryption between the databases does not work, when putting a capture of the packages with tcpdump is shown in plain text when I use dblink, these dblinks are they use to load information from different sources (Oracle, PostgreSQL and SQL Server ... the latter using Oracle Gateway). How can I encrypt the communication between the source and the destination and vice versa to use the dblinks? Thank you very much.

The solution that worked for me was to use Advanced Security, with netmgr or directly edit the sqlnet.ora by adding the parameters:

SQLNET.ENCRYPTION_SERVER = REQUIRED

SQLNET.ENCRYPTION_TYPES_SERVER = (AES256)

SQLNET.ENCRYPTION_CLIENT = REQUIRED

SQLNET.ENCRYPTION_TYPES_CLIENT = (AES256)

Once this is done it is necessary to restart the listener, for access to heterogeneous data base the sslmode = require parameter must be added in odbc.ini for postgresql, the ssl access must be previously configured in the postgres server and a registry with hostssl in the file pg_hba.conf ... I have not tried for sql server yet.
Regards


How to cancel query as non-superuser? I'm a non-superuser on a PostgreSQL 9.1 database, and I've just created a bad trigger function that won't stop. I can see the process, e.g.:

thedb=> SELECT * FROM pg_stat_activity WHERE procpid = 45678;
-[ RECORD 1 ]----+-------------------------------------------------------------------
datid            | 596281
datname          | thedb
procpid          | 45678
usesysid         | 596282
usename          | myuser
application_name | QGIS
client_addr      | 1.2.3.4
client_hostname  |
client_port      | 12345
backend_start    | 2015-04-16 13:45:27.482691+12
xact_start       | 2015-04-16 14:17:34.633156+12
query_start      | 2015-04-16 14:17:34.633665+12
waiting          | f
current_query    | UPDATE ...

But I can't stop it:

thedb=> SELECT pg_terminate_backend(45678);
ERROR:  must be superuser to signal other server processes

Is there anything that I can do?

You must either be the superuser or logged in as the same user who owns the session you wish to cancel.

So connect as user myuser and you'll be able to pg_cancel_backend, or, if that doesn't respond, pg_terminate_backend.


How to get the timezone from a PostgreSQL timestamp This is probably a very simple question, but it's hard to Google because it matches too many different questions.

I want to get the timezone (or offset) of timestamps saved in my database.

For example, right now in my database I have

     expired_at
---------------------
 2018-04-28 00:00:00
 2018-03-28 08:00:00
 2018-02-28 05:00:00

I want to find all expired_at that has the PST offset.

So my pseudo-code would be something like this

SELECT expired_at FROM table WHERE expired_at IS IN TIMEZONE('PST')

Can I get some help. Thanks!

PS. Basically I am trying to fix a data integrity issue by finding all UTC timestamp and updating them to be PST. The database is screwed up right now with a mix of many different timezones.

The documentation on Postgres timestamps says:

  For timestamp with time zone, the internally stored value is always in
  UTC (Universal Coordinated Time, traditionally known as Greenwich Mean
  Time, GMT). An input value that has an explicit time zone specified is
  converted to UTC using the appropriate offset for that time zone. If
  no time zone is stated in the input string, then it is assumed to be
  in the time zone indicated by the system's TimeZone parameter, and is
  converted to UTC using the offset for the timezone zone.

Contrary to what a reasonable person might expect when reading timestamp with time zone, this Postgres datatype doesn't actually store a timezone. Internally, the value is always converted to UTC.

The information about the input timezone is lost when saving the value to the database. As far as I know, there is no way to get the information you seek.


Is a weekly rebuild of indexes a good idea? We had a SQL expert suggest rebuilding our table indexes weekly.  We're currently doing this Saturday night and it causes lots of timeouts during the rebuild process.  I'd like to avoid the timeouts if possible, so is it a good idea to do the weekly index rebuild?

We're running MS SQL server 2014.

UPDATE
We get this error during the rebuild "System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. "

Thanks!

Dont just blindly go for rebuilding all the indexes. There is a much intelligent way of doing it. (hint: SQL Server Index and Statistics Maintenance by Ola hallengren)

This is an official guideline from Microsoft (and its a good starting point - instead of blindly rebuilding all the indexes):

click here for enlarge

Also, if you are using sql server enterprise edition then Rebuild is an ONLINE operation (REBUILD ONLINE = ON)

Also, read : Online Index Rebuild - Can Cause Increased Fragmentation -  when it is allowed to run with MAX DOP > 1 and ALLOW_PAGE_LOCKS = OFF directives.

As a side note: Paul Randal has a nice checklist of VLDB Maintenance best practices from his MS days :-)

The benefit of using Ola's script is that it has logging functionality [logs to dbo.CommandLog table]  which over time will become a gold mine and you can get real value out of it.

e.g.

If you are deploying index maintenance, then you can use dbo.CommandLog to find out what indexes are frequently getting fragmented often, the time taken to defragment them and if you join to sys.indexes then you can tie up FILL FACTOR and adjust it.  Check - monitoring-for-endless-index-defragmenting.
Also, if you have DatabaseIntegrityCheck - CHECKDB solution, then you can find out how long it takes to perform CHECKDB.


PostgreSQL multi-column unique constraint and NULL values I have a table like the following:

create table my_table (
    id   int8 not null,
    id_A int8 not null,
    id_B int8 not null,
    id_C int8 null,
    constraint pk_my_table primary key (id),
    constraint u_constrainte unique (id_A, id_B, id_C)
);

And I want (id_A, id_B, id_C) to be distinct in any situation. So the following two inserts must result in an error:

INSERT INTO my_table VALUES (1, 1, 2, NULL);
INSERT INTO my_table VALUES (2, 1, 2, NULL);

But it doesn't behave as expected because according to the documentation, two NULL values are not compared to each other, so both inserts pass without error.

How can I guarantee my unique constraint even if id_C can be NULL in this case?
Actually, the real question is: can I guarantee this kind of uniqueness in "pure sql" or do I have to implement it on a higher level (java in my case)?

You can do that in pure SQL. Create a partial unique index in addition to the one you have:

CREATE UNIQUE INDEX ab_c_null_idx ON my_table (id_A, id_B) WHERE id_C IS NULL;

This way you can enter for (id_A, id_B, id_C) in your table:

(1, 2, 1)
(1, 2, 2)
(1, 2, NULL)

But none of these a second time.

Or use two partial UNIQUE indexes and no complete index (or constraint). The best solution depends on the details of your requirements. Compare:

Create unique constraint with null columns

While this is elegant and efficient for a single nullable column in the UNIQUE index, it gets out of hand quickly for more than one. Discussing this - and how to use UPSERT with partial indexes:

PostgreSQL UPSERT issue with NULL values

Asides

No use for mixed case identifiers without double quotes in PostgreSQL.

You might consider a serial column as primary key or an IDENTITY column in Postgres 10 or later. Related:

Auto increment table column

So:

CREATE TABLE my_table (
   my_table_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY  -- for pg 10+
-- my_table_id bigserial PRIMARY KEY  -- for pg 9.6 or older
 , id_a int8 NOT NULL
 , id_b int8 NOT NULL
 , id_c int8
 , CONSTRAINT u_constraint UNIQUE (id_a, id_b, id_c)
);

If you don't expect more than 2 billion rows (> 2147483647) over the lifetime of your table (including waste and deleted rows), consider integer (4 bytes) instead of bigint (8 bytes).


Trouble with differential backup - full backup not being recognized I have recently implemented backups using https://ola.hallengren.com/.

Backups

EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES, -%Archive', @Directory = N'H:\sqlbackup', @BackupType = 'FULL',@Verify = Y', @CleanupTime = 360, @CheckSum = 'Y', @LogToTable = 'Y'

EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES, -%Archive', @Directory = N'H:\sqlbackup',@BackupType = 'DIFF',@Verify = 'Y',@CleanupTime = 192,@CheckSum = 'Y',@LogToTable = 'Y'

EXECUTE [dbo].[DatabaseBackup]@Databases = 'USER_DATABASES',@Directory = N'H:\sqlbackup',@BackupType = 'LOG', @Verify = 'Y',@CleanupTime = 48,@CheckSum = 'Y',@LogToTable = 'Y'

Schedule:

Full - once weekly
Diff - daily
log - hourly

Servers:

2 X sql server 2016, compatibility level set to (sql server 2012)
1 X sql server 2016, compatibility level set to (sql server 2016)

PROBLEM:

Suddenly, differential started failing on 2 servers.

Error is :
Cannot perform a differential backup for database "aspnetdb", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.

However, the DB is registering the last full backup. This is a full backup I manually took after last failure like this. Recovery mode is "simple"

This occurs on 1 server with compatibility 2012, and on 1 with compatibility 2016

on 1 server, differential does not fail but size = to full backup

That same time the job starts failing on the other 2 server, on this server the job completes but the file size suddenly jumps to be the same as full backup.

Clues

when I kick off the full backup job manually, then differential can be run manually. Then it runs OK next night, then next day fails in the way to describe
I am not excluding simple-recovery model DBs from Log backup explicitly
I have changed some DBs to simple recovery model since backup was setup, but DB server was not restarted
I modified the olg script abit, to have different clean up time per backup type
log backups are working OK on the servers where diff fails
log backups are showing as completed on the server where the diff completes but file is large, but the log backup files are not on disk - silent failure. The last full backup is just before the 1st differential backup that generates the large file

Examining backup history for 1 DB:
the "is copy" & "is_snapshot" backups are triggered by azure backup services of the actual VM where the sql server runs

SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
msdb.dbo.backupset.is_copy_only,
msdb.dbo.backupset.is_snapshot,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date

ANY CLUES FOR ME ?

You probably have a full backup before the ones that fails, like a snapshot backup or a VDI backup). Check the backup history tables in msdb and the errorlog file for clues.


I have to find the records which contain 6-A, 6-B, 6-C...6-Z. using SQL? I have a table named TABLE_1 and column test which has records in characters like 'AKLJNAAAAAAAIOUHIO', 'PIOJPMBBBBBBBPIO', 'OJPOIDDDDDDDPQP'...

I have to find the records which contains 6-A, 6-B, 6-C ... 6-Z using SQL.

How can I do that?

Clean, but the naive version* will most likely perform better.

with t(i) as (select 0 union all select i+1 from t where i<25)
select      *
from        TABLE_1
where       exists (select null from t where test like '%' + replicate(char(ascii('A')+t.i),6) + '%')

*

select      *
from        TABLE_1
where       test like '%AAAAAA%'
        or  test like '%BBBBBB%'
        or  test like '%CCCCCC%'
        ...


MySQL replication for non-standard port (3306) I want to do MySQL replication using MySQL 5.6.14 under MS Windows 2008 R2, the port for Master and Slave should be 1000 (master database on PC1, slave database installed on PC2).

I added the following parameters for master My.ini

[mysqld]
server-id=1
log-bin=black-bin.log
datadir=d:\mysql\master\
innodb_flush_log_at_trx_commit=1
sync_binlog=1

Then I edited slave My.ini adding the following parameters:

[mysqld]
server-id=2
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
datadir=d:\mysql\slave

After that I logged in into Master database using root user and executed the following commands

CREATE USER repl_user@slave_ip;
GRANT REPLICATION SLAVE ON *.* TO repl_user@slave_ip IDENTIFIED BY 'password';

To Initialize Replication I logged in into slave database and executed the following commands:

CHANGE MASTER TO MASTER_HOST='maset_ip',
 -> MASTER_USER='repl_user',
 -> MASTER_PASSWORD='password';
start slave;

The replication works fine if the port for the master is 3306 but if the port is changed then it does not work anymore. My question is: how to make slave connect to port 1000? I tried to add the following parameter to slave my.ini  master-port=1000 then restarted the slave SID but it did not work (service did not start, error message: unknown parameter master-port). Please advise, thanks.

You need to add the port to my.ini for the Master

[mysqld]
server-id=1
port=1000
log-bin=black-bin.log
datadir=d:\mysql\master\
innodb_flush_log_at_trx_commit=1
sync_binlog=1

and restart mysql

First run this

STOP SLAVE;
SHOW SLAVE STATUS\G

From the output

use Relay_Master_Log_File as CurrentLogFile
use  Exec_Master_Pos as CurrentLogPosition

As was mentioned in the comment, run CHANGE MASTER TO on the Slave like this

CHANGE MASTER To
MASTER_HOST='ipaddrofmaster',
MASTER_PORT=1000,
MASTER_USER='repl_user',
MASTER_PASSWORD='password'
MASTER_LOG_FILE='CurrentLogFile',
MASTER_LOG_POS=CurrentLogPosition;
START SLAVE;

Give it a Try !!!


What's row size in mysql? What does row size mean in mysql? I have a mysql db and table in myisam and it shows

Space usage Type    Usage
Data    1.3     GiB
Index   429.1   MiB
Total   1.7     GiB

Row Statistics Statements   Value
Format  static
Collation   utf8_unicode_ci
Rows    22,000,001
Row length ø    63
Row size ø  83 B
Next autoindex  22,000,002
Creation    Jul 06, 2014 at 10:48 AM
Last update     Jul 06, 2014 at 10:54 AM
Last check  Jul 06, 2014 at 10:54 AM

I don't understand what does the row size  mean. I tried searching on Google, but didn't find much explanation about it.

Can I use it to get the final table size after inserting N rows, by doing

row size * N rows  = table size ?

Is it correct?

Let's say I am in testing phase, so I dont have much data in my table. Can this formula be used to get the table size after inserting n rows while in production phase?

Yes it's the average size of each row in your table.

row size * N rows = table size

Row size = 83 B

Rows = 22,000,001

Row Size times Number of rows:

83 * 22000001 = 1826000083

Convert result in Giga Bytes:

1826000083 / 1024 / 1024 / 1024 = 1.7

Total = 1.7 GiB

Max


Fastest method for inserting large number of records from query between tables I have a query that returns 14 million results that I need to store in a table, the SELECT query itself returns in a little over 2 minutes but INSERT statement takes well over 30 minutes (which is unacceptable). We are running this in an Azure Database currently set as a Premium tier database, and I need to keep this process all contained within SQL (no exporting to file etc).

Sample Query:

SELECT
v.Param1,
v.Param2,
lt.Param3,
lt.Param4
INTO #tempTable
FROM <View> v
  join LargeTable lt
    on lt.nVarCharCol = v.nVarCharCol

INSERT INTO DestTable SELECT Para1, Param2, Param3, Param4 FROM #tempTable

Things I have tried so far:

Turned off indexes on DestTable
Batching inserts
Creating indexes on temp tables

What is the fastest/best way to insert these query records into a table?

Any recommendations to speed this process up?

After trying numerous solutions, this is the best I could come up with

DECLARE @BatchSize INT = 50000
DECLARE @page INT = 0

SELECT
 IDENTITY(int,1,1) as id
 v.Param1,
 v.Param2,
 lt.Param3,
 lt.Param4
INTO #tempTable
FROM <View> v
  join LargeTable lt
   on lt.nVarCharCol = v.nVarCharCol

WHILE(1=1)
BEGIN
 BEGIN TRANSACTION
  INSERT INTO DestTable SELECT Para1, Param2, Param3, Param4 FROM #tempTable
  WHERE id > @BatchSize * @Page AND id <= @BatchSize * (@Page + 1)
 IF @@ROWCOUNT = 0
  BREAK;
 SET @Page = @Page + 1
 COMMIT TRANSACTION
END

Also be sure to keep in mind that regardless of how fast your query or T-SQL statement is Azure SQL Databases have an artificial I/O ceiling called DTUs. I found that there was a substantial difference in performance between the different performance levels with the exact same T-SQL statement.

More information about DTUs can be found here:
SQL Database options and performance


How can instructors add users to their classes and at the same time not know all of the institution users? I have the following entities

Institutions - with institutionID and userID
Institution Users - with institutionUserID
Institution Classes - with institutionUserID, institutionID
Institution Class Users - with fields classID, institutionUserID and isInstructor

I'd like the user responsible (instructor) for one Institution Class to manage its Institution Class Users. By managing I mean to be able to even add a new Institution Class Users. At the same time, I wouldn't want this teacher to know all of the Institution Users.
What comes to my mind is the usage of two other entities: Institution Departments and Institution Department Users. This would enable to shrink the list of available users for that considering that classes would then be held by the department - Institution Classes and Institution Class Users would then change to Institution Department Class and Institution Department Class Users.
How to proceed in this scenario?

Yes you could add three additional tables called Departments, DepartmentUsers and DepartmentClasses.
Departments would have a primary key field departmentId (and probably some other fields like departmentName, etc).
DepartmentUsers would be a linking table between Departments and InstitutionUsers so it would store the departmentId and institutionUserId (as foreign keys). (This table determines which users are in which department.)
DepartmentClasses would be the same as above, except it would link Departments to InstitutionClasses, so it would store the departmentId and institutionClassId. (This table determines which classes are in which department.)
Then for a particular class you can see only the applicable users (within the department) who can be added to it via InstitutionClasses join to DepartmentClasses join to Department join to DepartmentUsers.
(Then the institutionUserID field in your InstitutionClasses table only represents who's already been added to the class. You may want to rename the column to something more explicit like registeredInstitutionUserID.)


Modelling users and groups - but with multiple types of groups I'm modelling a user management system in PostgreSQL that is fairly typical in that it consists of users, groups and permissions (and the relationships between them).

However, its seems I'm dealing with different kinds of groups: some that represent an organization (e.g. a company) and some that represent a role or differing permission levels within an organization (e.g. admin). The organizations represent tenants, or customers, in the business domain.

I currently see two possibilities: (a) create a separate table for organizations, or (b) add a boolean column to the group table called "is_organization" and use it to distinguish the two types.

Is there a best practice for modelling these type of system?  Thank you.

Create a Company table. Create/Verify you have a Group table and a GroupCompany table. Those will need populated on addition of a company to the system. You may want to create a template of groups common for a new company. Users that are added to the system are added to a group. Each group is tied to a company by the forementioned GroupCompany table. You can add a GroupType field to categorize company groups across the whole system.

Adding a company to your system helps you scale it across many organizations. It should be standard on most systems you architect. When the requirement comes in that we have a new client but they want all their data reported separately, you are covered.

Good Luck


Cannot log in to Mongo from command line - using MongoDB 3.0 and WiredTiger NOTE: I have tried not setting bind_ip and also setting it to 0.0.0.0 -- neither one fixed my issue.

This is my mongod.conf file:

systemLog:
    destination: file
    path: "/root/mongodb.log"
    logAppend: true

net:
    port: 25015

processManagement:
    fork: true

setParameter:
    enableLocalhostAuthBypass: true

storage:
    dbPath: /root/mongo_data
    engine: wiredTiger

The first thing I did was SSH in to the DigitalOcean droplet where it is running and do the following:

mongo --port 25019

I was able to connect, so I know it picked up the options in the mongod.conf file.

The second thing I did was issue the following command:

> use admin;
switched to db admin
> db.createUser(
    {
        user: "siteUserAdmin",
        pwd: "eqt-dfts-mad-ghbr-ovd-g",
        roles: [{role: "userAdminAnyDatabase", db: "admin"}]
    }
)
Successfully added user: {
    "user" : "siteUserAdmin",
    "roles" : [
        {
            "role" : "userAdminAnyDatabase",
            "db" : "admin"
        }
    ]
}

So that was apparently a success!

Next I exited from the mongo shell and then logged in to the mongo shell again as siteUserAdmin with credential from above.
Then I created a user and a database for the app I'm building:

> use MyDatabase;
switched to db MyDatabase
> db.createUser(
    {
        user: "my_user",
        pwd: "cu-npp-re-jpor-mtw-e-s",
        roles: [
            {role: "readWrite", db: "MyDatabase"},
            {role: "dbAdmin", db: "MyDatabase"}
        ]
    }
    )
Successfully added user: {
    "user" : "vantage",
    "roles" : [
        {
            "role" : "readWrite",
            "db" : "AdVantage"
        },
        {
            "role" : "dbAdmin",
            "db" : "AdVantage"
        }
    ]
}

So that was also apparently a success!

I then exited the mongo shell and then logged in to the mongo shell again as my_user with credentials from (4).  I confirmed that I was able to create a collection called demotest in MyDatabase:

> use MyDatabase;
switched to db MyDatabse
> db.createCollection("demotest")
{ "ok" : 1 }
> show collections;
demotest
> show dbs;
MyDatabase 0.000GB
admin      0.000GB
local      0.000GB
> exit
bye

So far so good.

I then switched to a terminal on my local machine.  My goal is to be able to connect to this MongoDB instance from my local machine and from an app that is running somewhere else on the internet.  So I issued the following command on my local machine:

PROMPT> mongo --host 102.216.73.164 -u my_user -p cu-npp-re-jpor-mtw-e-s --authenticationDatabase MyDatabase --port 25019
MongoDB shell version: 2.6.6
connecting to: 102.216.73.164:25019/test
2015-05-13T21:03:18.963-0400 Error: 18 { ok: 0.0, errmsg: "auth failed", code: 18 } at src/mongo/shell/db.js:1210
exception: login failed

Here is what I don't understand:

why did it try to connect to the test database?
why doesn't it work?

I've had the same authentication issue on an MMS deployed db (though it has nothing to do with it).

it tries to connect to /test by default. If you want another database, just specify it, e.g. 102.216.73.164/foo.
upgrade your mongo shell to 3.0.3 (you currently use 2.6.6). That solved the problem for me.


Columns count in SELECT query and SQL Server buffer cache Let's assume that I have a table with 30-50 millions records. It has ~40 columns. Currently when I request data it always requests all columns because of legacy ORM that we use.

I know that if we specify fewer result columns in SELECT query, then it will be easier for SQL Server as it doesn't need to load them from disk and return to client. But what about SQL Server buffer cache?

Lets say my application executes such query:

SELECT Column1,Column2,...,Column40
FROM ...
WHERE OwnerID = 42

So it will load all columns for these records, cache them in memory, and return to client.

And next query after it will be

SELECT Column1,Column2
FROM ...
WHERE OwnerID = 42

So it won't load other columns and return much less data, but will it matter if anyway they are loaded from cache and not from disk, as we loaded all columns in previous query. Will it help SQL Server or it won't matter at all because of SQL cache? How much will be difference in terms of complexity for SQL Server?

I can't remove first query for now, so I'm trying to optimize next queries for the same entity and trying to figure out a good ways.

Thanks!

SQL Server stores all the columns for one row together in a single disk page.  (It's more complex than this but for int and char etc. this is more-or-less true.)  To retrieve any column's value the whole page is read into RAM.  So once you have any column available for a given row, all of the columns for that row are available.

There are several buffers / caches involved in responding to a query.  One is the great big page bufferpool where pages read from disk are held in memory so execution plans can act on them.  And yes, if one query causes a page to be loaded into the bufferpool all subsequent queries will use the same page from the bufferpool and not have to suffer the IO overhead.  This is an incidental performance gain, however, and shouldn't be relied upon because SQL Server may choose to evict any page from the bufferpool at any time it needs the space.

Another set of buffering occurs when the output from your query gets sent over your connection to your application.  This is (mostly) on a per-connetion basis so duplicating the work duplicates the resources required (more or less).

Another cost is the optimisations you miss out on when you return all columns.  Covering indexes and "INCLUDES" columns are meaningless.  Every read will have to retrieve the underlying clustered index (or heap) page.  It's a fair bet that SQL Server will not be using specific indexes it might otherwise have chosen because a scan-the-cluster plan is cheaper than an index-and-cluster plan.


attach csv file on email sent from postgres I have the following code to send emails and wanted to see if it's possible to attach a csv file?
CREATE OR REPLACE FUNCTION public.send_email_html(text, text, text, text)
 RETURNS integer
 LANGUAGE plperlu
AS $function$
    use Net::SMTP;

    my ($recipientString, $sendFrom, $subject, $message) = @_;
    my @recipients = split /;/, $recipientString;
    my $smtp=Net::SMTP->new("localhost");

    $smtp->mail($sendFrom);
    foreach(@recipients)
    {
        $smtp->recipient($_);
    }
    $smtp->data();
    foreach(@recipients)
    {
        $smtp->datasend("To: $_\n");
    }

    $smtp->datasend("Subject: $subject\n");
    $smtp->datasend("Content-Type: text/html;\n");
    $smtp->datasend("$message\n");
    $smtp->quit();

    return 1;
$function$
;

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


I/O is frozen on Database & also I/O was resumed on Database on daily basis in log with complete Backup I would like to say that as on daily basis i got the message in my log file.

First The I/O is frozen on Database (DatabaseName) message appears in log. The message is like

  I/O is frozen on Database (DatabaseName).No User action is required.
  However, if I/O is not resumed promptly, you could cancel the backup.

And then I/O was resumed on Database(DatabaseName) message appears like

  I/O was resumed on Database (DatabaseName). No user action is
  required.

And finally the backup has done of Database with successfully with like this message

  No User Action is Required

It is My production Audit database and having the software Environment is like:

Windows Server 2012 R2
SQL Server 2012
VMware Tools

My production Database "Recovery Model" is "Full" recovery model. And from which login ID in SQL Server, the Maintenance Plan Backup was setup.That has "sysadmin" privileges for all databases.

The Backup has setup through "Maintenance Plan" Backup at specific time schedule like 4.00 P.M. at every day. And Always Backup went through successfully at their schedule interval.

Why such kind of log message generate in at schedule backup time.
Is this kind of message will impact to my Production Database performance.
Will be any issues in (Backup/Restore) data consistency in my production database.

Any suggestion will be appreciated.

I believe its just a message and not an error unless and until the task is getting impacted when this message is logged in the error log:

We also encountered the same for one of our production server, where windows admin team enabled the SQL Server backup through VSS (Volume Shadow Copy Services). And when this process runs it freezes I/O temporarily to take shadow copy and release it back once process is complete.

Major cause being "I/O on the database is frozen in order for any backup service using Virtual Device Interface (VDI)  and commiting a sql  snapshot backup."
as mentioned here

For a better understanding how this process works please read How It Works: SQL Server - VDI (VSS) Backup Resources2

Therefore i believe you should start looking for process like Volume Shadow Copy (VSS) OR Data Protection Manager (DPM) running on the server and discuss with system admin team for what is enabled and to ensure nothing is impacted.


Privileges discrepancy When I am logged in as rds_user and have following privileges:
GRANT SELECT, INSERT, UPDATE, DROP ONnews.* TO 'rds_user'@'%'

My code runs INSERT command just fine, but when it gives ProgrammingError it executes UPDATE instead. However, UPDATE command errors out with
1142 (42000): UPDATE command denied to user 'rds_user'@'XX.XX.XX.XX' for table 'data'

data is a table in db news

I tried granting privilege again and flushed privileges, still same error. I am using Python's sql connector to send the commands. Genuinely confused on what is going

You're logged in as rds_user, but you may not actually be logged in as 'rds_user'@'%'.

Privileges are actually granted not just per-username, but per username/host combination.  When you log in, you may be logging in as rds_user associated with a specific host, and that user may have less privileges.

Log in and run SELECT CURRENT_USER(); to see which user you're actually logged in as.

Run SELECT * FROM mysql.user WHERE user = 'rds_user'; to see all the users you could be logged in as.


MongoDB YAML file problems on Windows I managed to get MongoDB running as a three member replica on Windows 8, but I had problems with configuration files. If I used the new (MongoDB 2.6) file format (YAML) and used the replica set name option as:

replication:
    replSetName: rs1

(or any of the other variants I had tried in YAML format) I would get "Invalid option: replication" error during the installation. When I switched to old conf. file format and used:

replSet=rs1

I had no problems.
I am very (very!) new to all this, so I am wondering if I was doing something wrong or is it Mongo 2.6 - Windows problem. Thanks!

I was not able to replicate, so here's my successful YAML config file (Windows 7 64 bit, MongoDB 2.6.1):

replication:
    replSetName: "rs1"
    oplogSizeMB: 512
storage:
    dbPath: "data"
    smallFiles: true
systemLog:
    destination: file
    path: "data/mongod.log"

How I started it:

C:\Users\Adam\mongo\2.6.1\bin>mongod.exe -f mongod-yaml.conf
2014-06-03T23:23:12.528+0100 log file "C:\Users\Adam\mongo\2.6.1\bin\data/mongod
.log" exists; moved to "C:\Users\Adam\mongo\2.6.1\bin\data/mongod.log.2014-06-03
T22-23-12".

And then (after an rs.initiate()):

rs1:PRIMARY> db.serverCmdLineOpts()
{
        "argv" : [
                "mongod.exe",
                "-f",
                "mongod-yaml.conf"
        ],
        "parsed" : {
                "config" : "mongod-yaml.conf",
                "replication" : {
                        "oplogSizeMB" : 512,
                        "replSetName" : "rs1"
                },
                "storage" : {
                        "dbPath" : "data",
                        "smallFiles" : true
                },
                "systemLog" : {
                        "destination" : "file",
                        "path" : "data/mongod.log"
                }
        },
        "ok" : 1
}

Note that I had issues when I used tabs to indent, which were resolved when using spaces.  Case sensitivity was also a problem, but mainly due to muscle memory from the old config options.


Deadlock issue on vCAC6 on SQL Server We have a 2 node AAG setup with vCaC6 application running on it. Quite often, I get deadlock alerts from the server, and by the time I open the activity monitor, the deadlocks are no longer present.

I assumed that they were getting resolved automatically. But after Googling a little bit, I came across a VMware article which mentioned that this was a known problem and by design. The factors mentioned by Vmware are

1) Slow networks with large latency numbers between Manager Service (Application Server) and Model Manager (Repository) and DB
2) Unreliable networks with intermittent network/connectivity failures
3) Misconfiguration or underpowered MS SQL DB servers
4) Any factors that would slow down or prevent completing SQL DB stored procedures calls from remote clients, or result in the MS SQL DB server changing lock escalation levels from row to table.

I checked if there were any network waits happening on the server and could not find any.

This is what I find from the vCAc6 database, when I run sp_who2. Lot of these.

I also noticed this in a VMware KB article which says "The deadlock and retry is a standard part of the product and usually not a problem. Every site is different in load and configuration. In this case we identified a blocking scenario that would probably occur in other sites. However, this has not been reported and testing has not revealed this level of activity"

Does this mean, there is nothing to fix this issue? I dont think the AAG itself is under powered. What other ways can I explore to check if there are any network latencies between the application server and the Database?

Thanks!

SQL Server has tooling to provide information on the deadlocks and what was happening at the time.  You can use TRACE FLAG 1222 to capture the details of the deadlock in the error logs.

Of course, you can make a better analysis tool by following Jonathan Kehayias's detailed discussion at:

Handling Deadlocks in SQL Server

In my environment I am always checking for deadlocks and other problems. I would recommend using extended events to capture and store the details of the deadlocks. By reading the XML data for the deadlock you can get better insight on the problems you are facing.

I would recommend using extended events to capture and store the details of the deadlocks.  For a GUI method of setting this up see:

Monitor Deadlock Using Extended Events in SQL Serve 2008 and Later

You will never be 100% free from deadlocks, but you can trim down the problems by using the captured data to resolve many of the deadlocks.


What is the difference between covering and overlapping constraints use in DBMS? What is the difference between covering and overlapping constraints use in DBMS?

In reference with below Employee Entity Relational Model

Overlap Constraints : Can "Karthik" (employee) be an Hourly_Emps as well as a Contract_Emps entity? (Allowed/Disallowed) - In this scenario the hourly employee karthik cannot be a contract employee - Disallowed - This explains the Overlap constraints.
Covering Constraints : Is every hourly employee and every contract employee an employee of this organization? Answer: Yes, all the contract/hourly employees belong to the master employees entity - This explains the Covering constraints.


MongoDB/MySQL - auto increment vs object ID I am in the process of evaluating MongoDB and writing some basic PHP applications around it. Typically in my PHP/MySQL applications, I would create an ID field and set it to auto-increment on each row insert. I would then use this ID to relate to/join tables together.

However in MongoDB, there is no auto-increment (unless you create function to do it, I believe), only an objectID. Because of this, i'm unsure of how to relate collections together.

Is it worth writing an auto-increment function (as explained here: http://docs.mongodb.org/manual/tutorial/create-an-auto-incrementing-field/), or is it common practice to store the ObjectID?

An example would be the following MySQL query:

SELECT * FROM cars WHERE car_type = '2'

But in MongoDB....

db.cars.find( { car_type : 507c35dd8fada716c89d0013 } );

Is the above standard practice? Am I using Mongo incorrectly by creating an auto-increment field?

Darius

EDIT: I think I am so deeply rooted in MySQL that I can't imagine an application without auto-incrementing IDs. Is it safe to store objectIDs (just as you would store a MySQL AI ID)? Is this bad for readability, since you would have to look up that objectID just to see what it relates to?

In MySQL, or almost any other RDBMS, it is common to have an ID for the row (object), so you use it in other tables to make a link between the tables (Relations, or PK and FK if you prefer). This is part of the normalization process that is recommended for most types of DB design [there are exceptions though].

In MongoDB however, the general way or design is to denormalize. i.e. the document (row) is stored with all its related objects.

To make it more clear:

In MySQL you would have two tables: cars and car_types. In cars you only store the ID of the type of this car.

In Mongo DB, you would have one collection called cars, and the document in this collection would have a sub-document that contains the type's info. (You still can have a separate collection for types, but you don't store only objectID in car's collection)

HTH


Why does MySQL + Windows Server = crashed tables periodically? Windows Server 2008 R2 x64
MySQL Server 5.6.14 x64

Is anyone else having an issue in where some tables are marked as crashed on a Microsoft Windows server? We have a couple of Windows servers (not by choice) and a half a dozen Linux servers running MySQL. For some reason, the tables on the Windows servers get marked "crashed" every so often. We never have these problems on our Linux (Ubuntu) servers.

2014-01-23 10:11:42 1868 [ERROR] MySQL: Table '.\elite_prod\ffcont' is marked as crashed and should be repaired
2014-01-23 10:11:42 1868 [ERROR] MySQL: Table '.\elite_prod\bldr' is marked as crashed and should be repaired
2014-01-23 10:11:43 1868 [ERROR] MySQL: Table '.\elite_prod\frtfwd' is marked as crashed and should be repaired
2014-01-23 10:11:44 1868 [ERROR] MySQL: Table '.\elite_prod\histry' is marked as crashed and should be repaired

It seems we have to deal with this issue every couple of weeks or so. Very frustrating.

From the error message I can quickly tell the following:

The database is elite_prod
ffcount,bldr,frtfwd, and histry are all MyISAM tables

Because you are using the MyISAM Storage Engine, you have two major handicaps:

HANDICAP #1

Changes to MyISAM are cached differently (regardless of the OS)

The index pages for MyISAM tables are cached in the MyISAM Key Buffer (sized by key_buffer_size)
Index changes are flushed to the .MYI by the MyISAM Storage Engine
Data are never cached as there is no engine-centric buffering for data
Data changes are flushed to the .MYD by the OS (OUCH !!!!)
I discussed this before

What are the main differences between InnoDB and MyISAM?
Few Insert queries rollbacked due to system crash
My database keeps crashing on startup how can i repair it without it running?

HANDICAP #2

Microsoft Windows is terrible at caching disk changes. Even running FLUSH TABLES; in MySQL in a Windows environment is not a panacea. IMHO anyone using PostgreSQL or Oracle should be able to voice this same complaint about Windows. I will leave it to the SQL Server gurus to answer how they find Windows caching in terms of SQL Server.

ANALYSIS

MyISAM maintains a count of open file handles against the tables.

If the mysqld process or the Windows Server crashes, every MyISAM that had open file handles will retain the open file handle count internally.

When you access a MyISAM table for the first time since mysqld was started, it should have a zero file handle. Otherwise, you get that error message marked as crashed and should be repaired. This explains the tables periodically coming up as crashed crashed.

See my post MyISAM table keeps crashing. What are my options?

SUGGESTIONS

You could switch those tables to InnoDB and let the InnoDB Buffer cache everything, or at least better caching. I would still worry about Windows in this respect because the option innodb_flush_method is disabled in the Windows version of MySQL. I say disabled because the MySQL Documentation says:

  Controls the system calls used to flush data to the InnoDB data files and log files, which can influence I/O throughput. This variable is relevant only for Unix and Linux systems. On Windows systems, the flush method is always async_unbuffered and cannot be changed.

If you want leave the tables as MyISAM, please go back to Ubuntu because it is a bit more diligent when it comes to flushing disk changes, especially when it has enough RAM. Even a terabyte of RAM will not do a thing for MyISAM tables in Windows.


How to use default value of data type as column default? How can I specify the default value for a column in the DDL? I was pretty sure that the following method worked a while ago:

CREATE TABLE test
(
  col_1 CHAR(12) NOT NULL,
  col_2 INTEGER  NOT NULL WITH DEFAULT,
  col_3 CHAR(12) NOT NULL WITH DEFAULT
);

I would just like to define that the DB should use the default value for the column data type (like in my example above), without specifying which value exactly.

The default default value for any new table column is the default value of the data type.

And the default default value for data types is NULL - which is the case for all basic data types in Postgres. But any valid value is allowed for custom types or domains.

The manual on CREATE TYPE:

  A default value can be specified, in case a user wants columns of the
  data type to default to something other than the null value. Specify
  the default with the DEFAULT key word. (Such a default can be
  overridden by an explicit DEFAULT clause attached to a particular column.)

The manual on CREATE DOMAIN:

  The default expression will be used in any insert operation that does
  not specify a value for the column. If a default value is defined for
  a particular column, it overrides any default associated with the
  domain. In turn, the domain default overrides any default value
  associated with the underlying data type.

You only use the DEFAULT clause for columns in CREATE TABLE to specify a different default. Else, you do nothing. WITH DEFAULT like you display in the question is not valid in Postgres

To reset a column default to the default default of the data type (typically NULL), drop the specified default value of the column.

The manual on ALTER TABLE:

ALTER [ COLUMN ] column_name DROP DEFAULT

NOT NULL constraints are related but completely independent. A column defined NOT NULL and with no custom DEFAULT (and no not-null default for the type) requires you to provide a not-null value for every INSERT.

Aside: you probably do not want to use the data type char(12) in Postgres.

Any downsides of using data type “text” for storing strings?
Would index lookup be noticeably faster with char vs varchar when all values are 36 chars


Finding the reason of the lost data on oracle database My application uses Oracle database. At the same time Oracle ERP application and another Windows application (like mine) use same database. Everything looks fine and works synchronized but sometimes the Survey table lose data because of unknown reason!

I thought I delete it mistakenly in a case and added a backup query onto my delete query. Before run delete query, I copy the data to SurveyBackUp table. Yesterday the problem occurred again and I checked my backup table but it was empty! So, it looks like; the lost data is not deleted by my query. Is there any way to detect which query deletes the data?

Use Oracle's "tool" called Log miner.

restore some older backup of your database
identify ROWID (physical location) of record which was accidentally deleted
restore all archived redo logs
use log miner to get the SQL which deleted the row
log miner will also tell you culprits program name and hostname


PosgtreSQL - Change multiple fields collation in one query I have small database with few text fields with "default" collation. I don't want to recreate database. What is the query to alter all problematic fields at once?

To change the single one I can use

ALTER TABLE a_table_name ALTER a_column_name TYPE text COLLATE a_collate;

There isn't a single SQL command that will do that for you.  You can write a loop in a different language, using the results from

SELECT table_name, column_name
  FROM information_schema.columns
  WHERE table_schema IN ('your_schemas')
    AND data_type = 'text'
    AND collation_name IS NULL;

or similar.


investigate contents of a sqlite3 file I have created a new, clean firefox profile from scratch, and added couple of bookmarks. These, I have learned, are stored in places.sqlite file inside the profile directory. This file seems to be too big (1.2 MB) and I am wondering why:

$ ls -lh places.sqlite
-rw------- 1 martin martin 1.2M Sep  8 07:24 places.sqlite

When I open it with sqlite3 from the commandline, I see there are couple of tables:

$ sqlite3 places.sqlite
SQLite version 3.7.13 2012-06-11 02:05:22
sqlite> .tables
moz_anno_attributes  moz_favicons         moz_items_annos
moz_annos            moz_historyvisits    moz_keywords
moz_bookmarks        moz_hosts            moz_places
moz_bookmarks_roots  moz_inputhistory

but none of them contain much data (I have tried SELECT * FROM <TABLE> ; for all of them).

Further, if I dump the entire database into file dump.sqlite:

sqlite> .output dump.sqlite
sqlite> .dump

The file has only couple of KB:

$ ls -lh
total 1.2M
-rw------- 1 martin martin  39K Sep  8 19:01 dump.sqlite
-rw------- 1 martin martin 1.2M Sep  8 07:24 places.sqlite

I am aware of the vacuum command in sqlite3. I did that, but it has no effect. The database file is still same size.

How can I investigate why the database file is so big, and what does it contain ?

I had a file that was around 30 MB, and issued the vacuum full (not the normal VACUUM as it has the same effect that the Postgres behavior) for reducing its size. After the operation the file got sized 22 MB. I'm pasting the stats of each execution, which shows the differences in between (you will see more failures due to size for the VF).

sqlite> vacuum;
Successful lookaside attempts:       3690
Lookaside failures due to size:      913
Lookaside failures due to OOM:       0

sqlite> vacuum full;
Successful lookaside attempts:       6509
Lookaside failures due to size:      1621
Lookaside failures due to OOM:       0

The command .dbinfo will provide some information as the database page size and its count. Database pages are variable as specified in the file format doc page (this link contains all the internal structures).

For investigating the contents you can use sqlite_stats1 (the link contains the specifications of this table) table, which will be created after the ANALYZE:

analyze;
select * from sqlite_stat1;


Should I add new column or new table I have an existing table called exampletable1, now i have been given a new list of data called "medicines" it only have: male,female as its value. I'm having a problem whether to add another varchar column or to create another table and pass a foreign key to the exampletable1

The cardinality of 2  (male,female) is way too low.

Although you are using InnoDB and could have foreign keys, the parent table would have only two rows. It's not worth the processing power. Therefore, do not add another table. Indexing it is not worth it because a cardinality of 2 simply screams at the Query Optimizer "DO NOT USE THE INDEXES". Foreign Keys to a table with two values is not worth it.

Do not add a VARCHAR(1) because that is really two bytes.

There are four(4) options to use one byte

You could use CHAR(1) with 'M' and 'F' as the only values
You could use TINYINT with 0 for Male and 1 for Female
You could use TINYINT with 1 for Male and 0 for Female
You could use ENUM('M','F') (this might be two bytes)


What is an industry standard method for verifying openquery connection is open prior to running queries? I'm a programmer, but this question is more specifically related to databases so I'm posting it among this community.  It could very well cross over into stackoverflow as well.

I'm seeking an approved industry standard method of verifying an openquery connection is properly connected and responding appropriately before proceeding with running queries.  The environment that is applicable to me would be nice to know if there is a specific answer, but I'm also looking for a generic approach as well if there is one for others and myself to benefit from.  My environment here uses Microsoft SQL 2005 and Oracle in the openquery.

I found this post in a blog which shows how this can be done, but I'm not sure if there is a more efficient method for achieving the same results and without false indication or failure.

For Linked Servers in SQL Server ...

Run a SELECT @@SERVERNAME or some such in a stored procedure that contains a BEGIN TRY/CATCH block.

The stored procedure limits the scope in case it fails and for re-use
The TRY/CATCH is to suppress any error to the calling query
The stored proc uses an output parameter


"Ranking" values in an ORDER BY clause? I have the following table:

                                     Table "public.employee_employee"
     Column      |         Type          |                           Modifiers
-----------------+-----------------------+----------------------------------------------------------------
 id              | integer               | not null default nextval('employee_employee_id_seq'::regclass)
 name            | text                  | not null
 slug            | character varying(50) | not null
 title           | text                  | not null
 base            | numeric(10,2)         |
 gross           | numeric(10,2)         |
 overtime        | numeric(10,2)         |
 benefits        | numeric(10,2)         |
 total           | numeric(10,2)         |
 other           | numeric(10,2)         |
 year            | smallint              | not null
 jurisdiction_id | integer               | not null
 notes           | text                  |
Indexes:
    "employee_employee_pkey" PRIMARY KEY, btree (id)
    "employee_employee_jurisdiction_id" btree (jurisdiction_id)
    "employee_employee_slug" btree (slug)
    "employee_employee_slug_like" btree (slug varchar_pattern_ops)
    "employee_name_title_idx" gin (to_tsvector('english'::regconfig, (name || ' '::text) || title))
Check constraints:
    "employee_employee_year_check" CHECK (year >= 0)
Foreign-key constraints:
    "jurisdiction_id_refs_id_9e093e72" FOREIGN KEY (jurisdiction_id) REFERENCES jurisdiction_jurisdiction(id) DEFERRABLE INITIALLY DEFERRED

Here's a simplified sample of some mock data for the id, jurisdiction_id, year, and total columns:

1 | 1 | 2014 | 100.00
2 | 1 | 2012 | 105.00
3 | 1 | 2011 | 110.00
4 | 2 | 2013 | 115.00
5 | 2 | 2012 | 120.00
6 | 2 | 2011 | 125.00
7 | 3 | 2012 | 130.00
8 | 3 | 2011 | 135.00
9 | 4 | 2011 | 140.00

Or, presented another way, each unique jurisdiction_id has the following year values:

1: 2014, 2012, 2011
2: 2013, 2012, 2011
3: 2012, 2011
4: 2011

I'd like to "rank" these values the following way:

(1, 2014), (2, 2013), (3, 2012), (4, 2011) would be ranked first.
(1, 2012), (2, 2012), (3, 2011) would be ranked second.
(1, 2011), (2, 2011) would be ranked third.

Then, within each rank, the rows would be sorted by total DESC.

Here's the "SELECT (id, jurisdiction_id, year, total) ..." output I'm looking for (with line breaks between each rank):

9 | 4 | 2011 | 140.00
7 | 3 | 2012 | 130.00
4 | 2 | 2013 | 115.00
1 | 1 | 2014 | 100.00

8 | 3 | 2011 | 135.00
5 | 2 | 2012 | 120.00
2 | 1 | 2012 | 105.00

6 | 2 | 2011 | 125.00
3 | 1 | 2011 | 110.00

I have to think this is doable in PostgreSQL, but I don't even know the correct "term" for what I'm trying to do, so googling around has been a bit tough.

select
rank() over (partition by jurisdiction_id order by year desc) AS rank,
*
from your_table
order by rank,total desc;

rank() over (...) is a window function, see: window functions in postgresql


Database design - passenger, driver, wallet. How to handle multiple foreign keys with Id and type fields? I have to design a database schema for new project. I will use PostgreSQL for the implementation of the schema.

Right now I'm stuck in a scenario where I need to handle foreign keys based on type and id. Here are the tables:

Passenger:

PassengerID
FirstName
Email
DOB
Address

Driver:

DriverID
CNIC
Vehicle
License
Status

Wallet:

WalledID
Amount
UserId
UserType

Problem: I want that every wallet row should have UserId as DriverId or PassengerID and in the type field the name of the table.

How can I achieve this with PostgreSQL? I have heard that it is possible from polymorphic association but don't know how to implement it.

You cannot have that directly, but you can do the following:

Have two foreign key columns driverid and passengerid in wallet that reference the respective tables.
Add a check constraint that force one of the foreign key columns NULL and the other NOT NULL based on usertype.

CHECK (usertype = 'driver'    AND driverid IS NOT NULL AND passengerid IS NULL
    OR usertype = 'passenger' AND driverid IS NULL AND passengerid IS NOT NULL)

If you want, add a view on wallet that uses coalesce to map both foreign key columns to a single one.


Creating Indexed View GROUP BY Epoch Date I have a few big tables with about 6 billion rows that I was looking to optimize.  Clustered key is Epoch (unix date time which is the number of seconds that has passed after 1970) and customer ID.  This table records usage data per customer per product type.

For example, if this were for a Telco, TypeID 1 is a local call and the value is how many minutes used for that customer.  TypeID2 is a international call and is the value how many minutes were used in that hour for that customer.  Let's say TypeID3 is a special discounted rate for domestic calling.

The data is stored in 1 hour intervals.  I want the indexed view to store the aggregated 24 hour value so when we run a query for 1 day per customer, it has to only look up 1 row in the indexed view instead of 24 rows in the base table.

This is the base table:

ColRowID (bigint)
AggregateID (int)
Epoch (int)
CustomerID (int)
TypeID  (tinyint)
ErrorID (smallint)
Value (int)

We don't care about Aggregate or RowID for our reporting purposes, so I figure the indexed view will look like this:

CREATE VIEW [ixvw_AggTbl]
WITH SCHEMABINDING
AS
SELECT Epoch, CustomerID, TypeID, ErrorID, SUM(Value)
FROM DBO.BaseTbl
-- GROUP BY Epoch  (what goes here?? Epoch/86400?  If I do that I have to
-- put Epoch/86400 in the SELECT list as well)

EDIT:

Sample base data ( i left out the columns we don't need in this case, just assume the ID columns are there).  Each "TypeID" will have a value assigned to it, which the value can be 0.

For example,

    Epoch / Customer ID / TypeID / Value
    /* Epoch 90,000 is day 2 1am */

90000 (1am  day 2) / 1 / 1 / 200
90000 (1am  day 2) / 1 / 2 / 100
90000 (1am  day 2) / 1 / 3 / 120

/* Customer ID 2 as well */
90000 (1am  day 2) / 2 / 1 / 100
90000 (1am  day 2) / 2 / 2 / 50
90000 (1am  day 2) / 2 / 3 / 310

... (repeat for 30,000 customers)

/* Customer ID 1 2am day 1) */
93600 (2am day 2) / 1 / 1 / 150
93600 (2am day 2) / 1 / 2 / 0
93600 (2am day 2) / 1 / 3 / 550

/* Customer ID 2 2am day 2) */
93600 / 2 / 1 / 80
93600 / 2 / 2 / 150
93600 / 2 / 3 / 300
... (repeat for 30,000 customers)

Let's assume all the other VALUE columns are 0 for the remainder of the day since the system went down and no one could use their phones after 2am.  I want my indexed view to record the value column aggregated per day, per customerID and TypeID.

Sample would be:

172800 (Day 3 midnight) / 1 / 1 / 350  --Cust ID 1 aggregated all type id 1 in the past 24 hours
172800 (Day 3 midnight) / 1 / 2 / 100
172800 (Day 3 midnight) / 1 / 3 / 670
172800 (Day 3 midnight) / 2 / 1 / 180  --Cust ID 2 now
172800 (Day 3 midnight) / 2 / 2 / 200
172800 (Day 3 midnight) / 2 / 3 / 610
--Repeat by adding 86400 to the epoch to gather the summary data of the rows for the previous day.

I think there's some misunderstanding about what you're attempting to do here.

Since your current design is to return all 24 rows from the base table, presumably all the supplementary fields are returned as well (to display in a grid, or something).

In order to fully aggregate the Value column, all the supplementary columns cannot be included in the SELECT list. Alternatively, if those columns are included in the GROUP BY clause, the view would represent only a partial aggregation, as there would be one row for each unique combination of the columns in the GROUP BY column list.

The only way I see something like this being useful is if the supplementary columns aren't included in the view, and there is some other process that requires only the daily aggregated values, without the base row data. Such a view could be defined like this:

CREATE TABLE [dbo].[BaseTbl]
(
    ColRowID bigint NOT NULL,
    AggregateID int NOT NULL,
    Epoch int NOT NULL,
    CustomerID int NOT NULL,
    TypeID tinyint NOT NULL,
    ErrorID smallint NOT NULL,
    Value int NOT NULL,

    PRIMARY KEY CLUSTERED(Epoch, CustomerId)
);
GO

CREATE VIEW [dbo].[ixvw_AggTbl]
    WITH SCHEMABINDING
AS
    SELECT
        t.Epoch / 86400 AS EpochDay,
        CustomerID,
        TypeID,
        SUM(t.Value) AS TotalValue,
        COUNT_BIG(*) AS __RowCount
        FROM [dbo].[BaseTbl] t
        GROUP BY
            t.Epoch / 86400,
            CustomerID,
            TypeID;
GO

CREATE UNIQUE CLUSTERED INDEX IX_ixvw_AggTbl
    ON [dbo].[ixvw_AggTbl](EpochDay, CustomerID, TypeID);

Unfortunately, you can't go farther and convert the EpochDay column to an actual date within the indexed view because DATEADD is non-deterministic (see Aaron's comment below for why), so you'd have to convert it in the actual SELECT query against the view. But that's not too difficult.

In any event, as I said before, I'm not sure how useful this would be for your specific application.


Apparently, my CLR assembly function is causing deadlocks? Our application needs to work equally well with an Oracle database or a Microsoft SQL Server database. To facilitate this, we created a handful of UDFs to homogenize our query syntax. For example, SQL Server has GETDATE() and Oracle has SYSDATE. They perform the same function but they are different words. We wrote a wrapper UDF called NOW() for both platforms which wraps  the relevant platform specific syntax in a common function name. We have other such functions, some of which do essentially nothing but exist solely for the sake of homogenization. Unfortunately, this has a cost for SQL Server. Inline scalar UDFs wreak havoc on performance and completely disable parallelism. As an alternative, we wrote CLR assembly functions to accomplish the same goals. When we deployed this to a client they started experiencing frequent deadlocks. This particular client is using replication and high availability techniques and I'm wondering if there's some sort of interaction going on here. I just don't understand how introducing a CLR function would cause problems like this. For reference, I've included the original scalar UDF definition as well as the replacement CLR definition in C# and the SQL declaration for it. I also have deadlock XML that I can provide if that helps.

Original UDF

CREATE FUNCTION [fn].[APAD]
(
    @Value VARCHAR(4000)
    , @tablename VARCHAR(4000) = NULL
    , @columnname VARCHAR(4000) = NULL
)

RETURNS VARCHAR(4000)
WITH SCHEMABINDING
AS

BEGIN
    RETURN LTRIM(RTRIM(@Value))
END
GO

CLR Assembly Function

[SqlFunction(IsDeterministic = true)]
public static string APAD(string value, string tableName, string columnName)
{
    return value?.Trim();
}

SQL Server Declaration for CLR Function

CREATE FUNCTION [fn].[APAD]
(
    @Value NVARCHAR(4000),
    @TableName NVARCHAR(4000),
    @ColumnName NVARCHAR(4000)
) RETURNS NVARCHAR(4000)
AS
EXTERNAL NAME ASI.fn.APAD
GO

What version(s) of SQL Server are you using?

I do recall seeing a slight change in behavior in SQL Server 2017 not too long ago. I will have to go back and see if I can find where I made a note of it, but I think it had to do with a schema lock being initiated when a SQLCLR object was being accessed.

While I am looking for that, I will say the following regarding your approach:

Please use the Sql* types for input parameters, return types. You should be using SqlString instead of string. SqlString is very similar to a nullable string (your value?, but it has other functionality built in that is SQL Server-specific. All of the Sql* types have a Value property that returns the expected .NET type (e.g. SqlString.Value returns string, SqlInt32 returns int, SqlDateTime returns DateTime, etc).
I would recommend against this entire approach to begin with, whether or not the deadlocks are related. I say this because:

Even with deterministic SQLCLR UDFs being able to participate in parallel plans, you are most likely going to get performance hits for emulating simplistic built-in functions.
The SQLCLR API does not allow for VARCHAR. Are you ok with implicitly converting everything to NVARCHAR and then again back to VARCHAR for simple operations?
The SQLCLR API does not allow for overloading, so you might need multiple versions of functions that do allow for different signatures in T-SQL and/or PL/SQL.
Similar to not allowing for overloading, there is a big difference between NVARCHAR(4000) and NVARCHAR(MAX): the MAX type (having even a single one of them in the signature) make the SQLCLR call take twice as long as not having any MAX type in the signature (I believe this holds true for VARBINARY(MAX) vs VARBINARY(4000) as well). So, you need to decide between:

using only NVARCHAR(MAX) to have a simplified API, but take the performance hit when you are using 8000 bytes or less of string data, or
creating two variations for all / most / many string functions: one with MAX types, and one without (for when you are guaranteed to never go over 8000 bytes of string data in or out). This is the approach I chose to take for most of the functions in my SQL# library: there is a Trim() function which likely has one or more MAX types, and a Trim4k() version which never has a MAX type anywhere in the signature or result set schema. The "4k" versions are absolutely more efficient.

You are not being careful to emulate functionality given the example in the question. LTRIM and RTRIM only trim spaces, while .NET String.Trim() trims white space (at least space, tabs, and newlines). For example:

  PRINT LTRIM(RTRIM(N'      a       '));

Also, I just noticed that your function, both in T-SQL and in C#, only uses 1 of the 3 input parameters. Is this just a proof of concept, or redacted code?


Configuring slave from EC2 master to Amazon RDS slave I am configuring replication from EC2 master to Amazon RDS instance.

After starting the slave, I don't see any errors but I see slave_IO:

_thread is connecting.

Master version:5.6.23
Slave Version:5.6.19

show slave status \G

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: XXXXXXXXXXX
                  Master_User: XXXXXX
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: cli-bin.000032
          Read_Master_Log_Pos: 713
               Relay_Log_File: relaylog.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: cli-bin.000032
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table: mysql.plugin,innodb_memcache.cache_policies,mysql.rds_sysinfo,mysql.rds_replication_status,mysql.rds_history,innodb_memcache.config_options
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 713
              Relay_Log_Space: 618
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 0
                  Master_UUID:
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)

show global variables

mysql> show global variables like '%old_passwords%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | 0     |
+---------------+-------+
1 row in set (0.01 sec)

mysql> show global variables like '%secure_auth%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| secure_auth   | OFF   |
+---------------+-------+
1 row in set (0.00 sec)
====================

The problem is Slave_IO_state is connecting and Slave_io_thread is connecting state but replication is not happening.

Community Wiki answer generated from question comments by jynus:

You may not have opened the right ports or provided the REPLICATION SLAVE permissions:

Using Replication to Export MySQL 5.6 Data

Also see this answer:

Replication from EC2 Master to RDS Slave - Connecting to master


Testing if an element in a PostgreSQL array? Is there an operator in PostgreSQL to test whether an element is in an array?

Currently, I'm doing such tests in a slightly complex way by first constructing a singleton array for the element and then using the <@ operator between arrays.

SELECT ARRAY[1] <@ ARRAY[1,2,3];

( SELECT 1 <@ ARRAY[1,2,3]; does not work).

Is there a more succinct/clear way?

You need to use the ANY construct!

To answer your question, I did the following:

Created a table:

CREATE TABLE arr_test
(
  my_name VARCHAR(15),
  my_int_array INTEGER []
);

Added a couple of sample records:

INSERT INTO arr_test VALUES ('fred', '{1, 2, 3, 5, 7, 9}');
INSERT INTO arr_test VALUES ('bill', '{34, 45, 56, 67}');

Ran my query:

SELECT * FROM arr_test
WHERE 34 = ANY (my_int_array);

Result:
my_name     my_int_array
   bill    {34,45,56,67}

The fiddle is available here. I hope this answers your question - if not, please let me know!