Oracle 12c query performance varies based on schema/user I have a strange (to me) performance issue. Based on the schema that runs the query I received very different performance. Here's my setup.

Oracle 12c DB
    SAMPLE schema/user
       WIDGET table (300,000 rows/10 columns of varchars, nvarchars, dates, and numbers)
    USER1 schema/user
    USER2 schema/user
    DBA1 schema/user
--SELECT granted on WIDGET table to USER1 and USER2.
--DBA1 already has access to all table accross all schemas and has many more privs.

When I execute either of the following:

select * from SAMPLE.WIDGET;
select count(1) from SAMPLE.WIDGET;

It takes about 0.5 seconds in SAMPLE schema or DBA1 schema. It takes about 10 seconds in USER1 and USER2 schema.

So I'm looking for the following:

Under what condition(s) can query performance in Oracle differ based on the user you are logged in to when you execute a query?

You can try to check what the current consumer group for the different sessions are. I.e. log in as SAMPLE and USER1 and run

SELECT SID,SERIAL#,USERNAME,RESOURCE_CONSUMER_GROUP FROM V$SESSION
    WHERE USERNAME IN ('SAMPLE','USER1','DBA1');

You can also check what the actual plans are assigned as DBA:

SELECT * FROM DBA_RSRC_CONSUMER_GROUP_PRIVS
    WHERE GRANTEE IN ('SAMPLE','USER1','DBA1');

This is designed to grant different resources for users (and admins) and might explain what you see. The plan should actually be shared across all executing users (it remembers the user which parsed it first). Just to be sure, auto trace the execution for the two different users.


Why is MongoDB user with the only privilege as "find" able to insert and update collection? I want my user to have only find action privilege on a specific collection. However, when I create the user with the privilege, restart the mongod server and run the mongo shell again with -u -p and --authenticationDatabase parameters and run insert command on the collection, it successfully inserts the document in collection. Similarly, update command also works seamlessly for this user and the corresponding collection.

Here's the code for creating the user-

use testauth
db.createUser({user: "user", pwd: "password", roles: [{role: "readWrite", db: "testauth", privileges: [{resource: {db: "testauth", collection: "readonlycol"}, actions: ["find"]}]}]})

Followed by this, I restart the mongod server with --auth and run the mongo shell with authentication parameters.
When I go ahead and try to insert a document in the collection,  it works -

> use testauth
> db.readonlycol.insert({"key": "value"})
WriteResult({ "nInserted" : 1 })

update also seems to work. The only command which doesn't work is remove. What am I doing wrong?

according to https://docs.mongodb.com/manual/reference/built-in-roles/
the "readwrite" is to blame ...

readWrite
Provides all the privileges of the read role plus ability to modify data on all non-system collections and the system.js collection.
The role provides the following actions on those collections:

collStats
convertToCapped
createCollection
dbHash
dbStats
dropCollection
createIndex
dropIndex
find
insert
killCursors
listIndexes
listCollections
remove
renameCollectionSameDB
update

Although the list also contains the remove - right .


can not execute LIMIT on sql Developer I am trying to execute following on sqlDeveloper

SELECT salary FROM emp ORDER BY salary DESC LIMIT 4, 1;

But it is throwing following error:

ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
*Cause:
*Action:
Error at Line: 1 Column: 44

Oracle 11g doesn't support the LIMIT clause, though the impending 12c release is rumored to support it.

Anyway, you can do this using an analytic windowing function:

select * from
(
  select salary, row_number() over (order by salary desc) as rn
  from emp
)
where rn = 4;

You can also do this using rownum, but I find the above way to look cleaner.

Example SQL Fiddle for you to mess around with.


Why does my Group Replication plugin fail in MySQL 5.7.24? I've been using the group_replication plugin with MySQL without issues before 5.7.24. With this version, the server starts up without the plugin because of failure.
I confirmed it manually (see steps below) and it's definitely the new version it doesn't work with. I'd consider inspecting the file myself, but a binary doesn't offer much hacking opportunity.

Background on the plugin source: Since it doesn't come included with mysql-server but is well documented, I have obtained it from the Community Edition.

OS: Ubuntu 16.04.3 LTS (Xenial Xerus)

Steps to reproduce:

Have /usr/lib/mysql/plugin/group_replication.so
Verify version is 5.7.23

$ mysql --version
mysql  Ver 14.14 Distrib 5.7.23, for Linux (x86_64) using  EditLine wrapper

Install plugin successfully in 5.7.23

mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (1 min 1.16 sec)

Upgrade to 5.7.24 (uninstall everything to do with 5.7.23 do make sure it gets loaded):

dpkg -l | grep mysql
apt-get remove libmysqlclient20:amd64 mysql-client-core-5.7 mysql-common mysql-server
apt-get install mysql-server
$ mysql --version
mysql  Ver 14.14 Distrib 5.7.24, for Linux (x86_64) using  EditLine wrapper

Witness plugin failing to load in 5.7.24:

mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
ERROR 1126 (HY000): Can't open shared library '/usr/lib/mysql/plugin/group_replication.so' (errno: 2 /usr/lib/mysql/plugin/group_replication.so: undefined symbol: _Z25group_replication_cleanupv)

Any idea what I can do to fix it, so I can bring replication back up?

As a workaround, I've managed to get the plugin to load by re-obtaining it from the newest MySQL Community Edition. This is likely to break again at some point.

I'm marking this as closed because the reason was incompatibility with the specific version, and there's nothing to be done apart from hope it gets included in the default MySQL edition. Hopefully this is useful for other people.


How to use array variable in query in PostgreSQL Create table t1 ( xcheck varchar[], name text );

CREATE OR REPLACE FUNCTION fn_acgroup(xch varchar[])
  RETURNS record AS
  DECLARE xrc as record;

  execute 'select name from t1 where xcheck @> ''' || xch :: varchar[] || ''';' into xrc;
return xrc;
END;

In table t1 having array value and text. I am calling this(fn_acgroup) function with array as its parameter value. In that function, I am checking the array value with passed array if it matches then I will return the name. In that case I get error as operator does not exist: text || character varying[]. Am I doing anything wrong? How can I solve this problem?

You manage to get it wrong in just too many ways. To begin with, there is no need for dynamic SQL at all.

The best way to "solve this problem" is probably to start with the basics. Read the excellent manual about CREATE FUNCTION, PL/pgSQL and SQL functions.

A PL/pgSQL function could look like this (one of several variants):

CREATE OR REPLACE FUNCTION fn_acgroup(xch varchar[], OUT xrc) AS
$func$
BEGIN
SELECT INTO xrc name FROM t1 WHERE xcheck @> xch;
END
$func$ LANGUAGE plpgsql

Or, simpler as plain SQL function:

CREATE OR REPLACE FUNCTION fn_acgroup(xch varchar[])
  RETURNS text AS
$func$
SELECT name FROM t1 WHERE xcheck @> $1;
$func$ LANGUAGE sql;

Or study some of the plpgsql examples that have been posted here.


Upgrade SQL Server 2014 Failover Cluster to SQL Server 2016 Availability Group on Windows Server 2016 In our current set up we have 2 node SQL failover cluster in data center DC1 with mirroring enabled. And similarly in DC 2 a 2 node SQL failover cluster. DC1 acts as primary via DB mirroring in one point of time and DC2 mirror.
 Above runs on Windows server 2012R2

How can I convert the above setup from sql cluster to get cluster so that can leverage always on. I know this is sort of s big question but needed to know how can this be achieved with some overview and I can do some poc on that. We are thinking to upgrade to sql 2016 on Windows 2016 if possible.

Please guide for any real time experiences or blogs I can go through to do such migration.

Edit-- Per Answer below which needs the DR server at different DC to be out for route seems to be last option but i will still test to figure out the timings.

Also, is it possible to have setup like configure LS on current prod server with 4 node new built multi site cluster for alwayson AG? And during cutover we can simply stop the LS job on prod and convert the LS secondary on 4 node cluster to alwayson AG configuration

To migrate from two Failover Clustered Instances (FCI) to an Availability Group (AG) that spans four Availability Replicas is fairly straight forward but somewhat of a lengthy process. Also, some downtime is required to point your application to the Availability Group Lister and manually fail over to one of the Secondary Replicas.

This is a high level overview of the process so use the links provided to plan:

Disable Database Mirroring
Completely uninstall the FCI and destroy Windows Cluster at DC2

Uninstalling a SQL Server Clustered Instance

Install Standalone SQL Instances on the two nodes at DC2
Join the two new stand-alone servers at DC2 to Cluster at DC1 and configure the AG

Combine a failover cluster instance and Availability Groups
Configure Availability Group on a multisubnet cluster

Take down time to fail over to to a Secondary Replica in DC2 and update Application to point to Listener
Remove FCI from AG

Remove a secondary replica from an Always On Availability Group

Completely uninstall the FCI at DC1
Repeat Step 3 at DC1
Add new stand-alone instances to AG

Add a secondary replica to an Always On Availability Group

All of this can be done on SQL Server 2014. There are some benefits to upgrading to 2016 but I would recommend you read this to help plan as there are a lot of similar steps to upgrade.


Is this schema usable for multi-tenant clients?How to remove multi-cascade paths? I am  newbie to database design. I want to build multi-tenant application that will have the following schema for its data:

School(*schoolid*, schoolname, schooltype);

Department(*deptid*,deptname,schoolid);  //schoolid is foreign key

Staff(*staffid*,staffname,contactno,age,courseid, schoolid);  //schoolid,courseid are foreign keys

Course(*courseid*,coursename,coursetype,schoolid);  //schoolid is foreign key

DSC(deptid,staffid,courseid,six,nine); //six,nine are months indicating duration
of the course to which courseid referes.All ids are foreign keys

SD(staffid,deptid,ten,two,three); //ten,two and three are times at which
particular staffid is free.All ids are foreign keys

The fields within asterisks(*) are primary fields. I want to have
schoolid in School, Department, Staff, Course tables for security concerns and can't remove it from there. What can be more optimized structure for multi-tenacy in this regard? When I use SQL Server 2012 to build this schema, it *gives multi-cascade cycles error. *.
Any suggestions?

To achieve multi-tennancy you should limit the number of relationships you have to the factor that determines the tennant.

For example, each of Course, Department and Staff have a foreign key to School.  However, these child tables are also interrelated.  Is it necessary for Staff to have a schoolid?  You can get to the school a staff member works for by following the foreign key to Course.

Similarly, you have grand-child tables (SD and DSC) which are relating tables in curious ways.  What has "months indicating duration of the course" to do with which staff member is teaching the course?  Is it really necessary for DSC to relate to Staff?  Also, you appear to have repeating groups defined in your SD and SDC tables.  I believe you may have a normalization issue.

By over-relating tables you create cascade cycles.  Consider the following ERD as an alternative:

Here there is only one potential cycle to be concerned with, instead of the several that are in your current model.  Fewer potential cycles means less extra work that might need to be done with triggers instead of declarative referential constraints.  This keeps your system simpler and more maintainable.


SSIS OnError propagating when it shouldn't I have an SSIS package with a data flow task that uses a script component as its data source. The script component retrieves its data from a web service. The call to the web service sometimes fails, so I want to be able to retry a few times before failing the package.

My situation is very similar to the one described here. I followed the steps outlined there, but my For Loop still fails even though the Propagate variable on the Data Flow Task is set to False.

My question is: are there any situations you are aware of that would cause an error to propagate to a parent container even when the Propagate variable says not to?

Some more details about the error: it is caused by the web service call timing out. (There is some problem with our connection to the web service that we have not yet been able to diagnose. The web service provider shows that they receive the request and respond, but our SSIS never gets the response back, and instead waits until it times out.) This raises a System.TimeoutException in the Script, which in turn causes an SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.

I suggest you approach this issue differently. If you suppress errors, you lose insight into the myriad errors other than a connection time-out that can occur. Nothing worse than a silent error that causes you to lose data or load bad data for... how long until anyone will ever find this with no alerts and no SSIS failures?

Handle the error in the code itself. Use TRY CATCH blocks to execute your code to an expected stopping point. In your case you want to specifically handle a System.TimeoutException by trying to connect again. For any other exception the package should fail. By handling the issue in the code you can allow true failures to bubble up through the SSIS failure propagation, while addressing the specific errors you expect or have encountered gracefully in the script component itself.


How to create SQL Agent job to automatically restore a database from latest backups? There are 3 different folders which store respectively full, differential, and log backups. Different types of backups occur in different schedules. Now I need to create a new job which will periodically restore the database in order to assure the consistency of the backups. There are some third party tools available but I do not want to use them. Now, I can't figure out how to specify in the Job Step script to choose the last full/dif/log backups from their folders? The thing is there are multiple files already in the folders.

Solution to the problem is described:
https://www.mssqltips.com/sqlservertip/1584/auto-generate-sql-server-restore-script-from-backup-files-in-a-directory/


Is there a way, in a single SQL statement to ensure that all items in a list are present in some column? What I'm looking for is a way of verifying that all of a list of items (let's say 'FOO', 'BAR' and 'BAZ') are all in a given table.column.  This would be something like SELECT something FROM sometable WHERE somevalue IN ('THIS', 'THAT', 'OTHER THING') ... except it's sort of the inverse:

Ideally the statement would give me everything from ('FOO', 'BAR', 'BAZ') NOT IN sometable.somecolumn then I could use that as my check (assert that my SQL query returns an empty set or take the resulting set and complain that each of these is an "unrecognized" key).  (A brute force approach would be to insert all of the terms/tags/items into a temporary, single column table and perform a JOIN ON my actual table's column for the set of rows which are NOT IN it.

I'm sure I'm missing some syntactic trick that'll seem obvious once I see an example.

In this particular case my table structures are something like:

CREATE TABLE items (id     INTEGER PRIMARY KEY, item TEXT UNIQUE NOT NULL);

CREATE TABLE tags  (tag_id INTEGER PRIMARY KEY, tag  TEXT UNIQUE NOT NULL);

CREATE TABLE item_tag (item_id integer, tag_id integer,
                               FOREIGN KEY(item_id) REFERENCES items(id),
                               FOREIGN KEY(tag_id) REFERENCES tags(tag_id),
                               PRIMARY KEY (item_id, tag_id));

... but I'm simply trying to come up with a template for taking an arbitrary number of "tags" and ensure that all of them are valid entries the "tags.tag" column.  (The application will have to support arbitrarily complex queries for subsets of these tags to return DISTINCT subsets of the items; but I want to raise an error on any non-existent tag before building the JOIN's WHERE expression).

Obviously I could just loop over the tags doing a separate SELECT tag_id FROM tags where tag=? ... entailing numerous round trips to the database.   But this seems silly when I could send all of them to some sort of query in a single statement.

If I understand what you're trying to achieve, then I think what you want is a CTE with a UNION list of all the possible tags you want to compare against, then RIGHT JOIN  the tag table against the CTE.

Example -

WITH ListOfTags (Tag) AS (SELECT 'Foo' UNION SELECT 'bar' UNION SELECT 'other' UNION SELECT 'thing')
SELECT  *
FROM    item_tag    i
INNER JOIN  tags    t
    ON i.tag_id = t.tag_id
RIGHT JOIN ListOfTags   l
    ON  l.Tag like t.tag
WHERE   i.item_id is null

This might not be the most elegant way of doing it, but it might work for you


JSON storage requirements for MySQL I could not find storage requirements for JSON type columns in MySQL documentation

How much storage space allocated for JSON type column in InnoDB? Are JSON columns  the same as BLOB?

JSON have optimised storage format compare with old method (save as TEXT), but no any other information You will not find,

because JSON size always depends from JSON data, some of them could be more optimised, some less, plus different length, plus ...

  Optimized storage format. JSON documents stored in JSON columns are
  converted to an internal format that permits quick read access to
  document elements. When the server later must read a JSON value stored
  in this binary format, the value need not be parsed from a text
  representation. The binary format is structured to enable the server
  to look up subobjects or nested values directly by key or array index
  without reading all values before or after them in the document.

It is like - what zip archiver ratio? Is it same for txt and jpg?

some more information there - https://dev.mysql.com/doc/refman/5.7/en/json.html


Multi-homed SQL Server with High Availability Groups We have two servers (SQL-ATL01, SQL-ATL02) that make up a Failover Cluster, each running as part of a  SQL Server High Availability Group (HAG). Each server has two network cards. One is a 10Gbit card that is directly connected to the other server and is used for Synchronizing the HAG on a 192.168.99.x subnet. The other is a 1Gbit card that is used to connect the DB servers to a switch to communicate with the application servers on a 10.0.0.x subnet. The Listener is pointed to the  192.168.99.x subnet.

We want to add a third server (SQL-NYC01) in another physical location to the cluster and run it as an Async replica part of the HAG, but the VPN only routes traffic on the subnet on the 1Gbit network.

Is there any way to set up the Failover Cluster and High Availability Group to tell it:

Send synchronous replica traffic for SQL-ATL01 <--> SQL-ATL02 over 192.168.99.x
Send asynchronous replica traffic for (SQL-ATL01, SQL-ATL02) <--> SQL-NYC01 over 10.0.0.x

Or do we have to have all replica traffic going in and out on the same IP address/subnet?

Is there any way to set up the Failover Cluster and High Availability
  Group to tell it: Send synchronous replica traffic for SQL-ATL01 <-->
  SQL-ATL02 over 192.168.99.x Send asynchronous replica traffic for
  (SQL-ATL01, SQL-ATL02) <--> SQL-NYC01 over 10.0.0.x

Yes.

All AG replication traffic goes over TCP/IP connections made to the database mirroring endpoint on the instance hosting the primary replica.

The mirroring endpoint is, by default, listening on all IP addresses.  If you configure the mirroring endpoint to listen on only one of a server's IPs, then when you create the AG you must register the ENDPOINT_URL using that IP address or a hostname that resolves to only that IP address.

But, in your desired scenario you must have the ATL servers' database mirroring endpoints listening on both IP interfaces (or else NY wouldn't be able to connect).  So if you currently have the ATL nodes joined to the AG with their ENDPOINT_URL set to the 192.x IP addresses, you'll have to change that to add the NY node.

But you can ensure that the ATL-ATL traffic goes over the 192 network by using host file entries on the ATL servers, as @Tony Hinkle suggests.


Postgres: Can I associate index with FK constraint? Postgres FK constraint and index are independent.

Recently I was looked for unused indexes and found trick to eliminate indexes needed for constraint enforcing by:

 WHERE NOT EXISTS (SELECT 1
                   FROM pg_constraint c
                   WHERE c.conindid = pg_stat_user_indexes.indexrelid)

We created FK constraints independently from indexes.

Is it possible to somehow associate index to FK constraint so pg_constraint.conindid will point from FK to index?

Something like:

ALTER TABLE my ALTER CONSTRAINT my_your_fk USING INDEX my_your_fk_i;

UPDATE I see something related in Postgres docs:

ALTER TABLE distributors DROP CONSTRAINT distributors_pkey,
ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx;

I am not 100% certain what you are asking.

Your query shows the association of foreign key constraints to indexes at the target.

There is no association between indexes on the source and foreign key constraints.

You can use this query to find all foreign keys which have no index at the source that supports fast updates and key modifications on the target table:

SELECT conrelid::regclass, conname
FROM pg_constraint AS c
WHERE c.contype = 'f'
  AND NOT EXISTS (SELECT 1 FROM pg_index AS i
                  WHERE i.indrelid = c.conrelid
                    AND (i.indkey::smallint[])[0:cardinality(c.conkey)-1] @> c.conkey);

The strange condition will test if the first index columns are identical to the columns in the foreign key definition.


Why are NULLs sorted first? Why is it that when we have a NULL value in a column and we order by the value ascending, the NULLs are sorted first?

select 1 as test
union all
select 2
union all
select NULL
union all
select 3
union all
select 4
order by test

results in

NULL
1
2
3
4

I keep thinking that NULL meant "Indeterminant" or possible "Unknown".  If that's true, wouldn't they sort last, since the value could be greater than all other values?  (Or is this a sorting option somewhere?)

I'm on SQL Server 2008R2, but I suspect this is true across all SQL Servers, and probably across all RDBMSs.

BOL: A value of NULL indicates that the value is unknown. A value of NULL
  is different from an empty or zero value. No two null values are
  equal. Comparisons between two null values, or between a NULL and any
  other value, return unknown because the value of each NULL is unknown.

NULL means unknown. No other interpretation is valid.

  If that's true, wouldn't they sort last, since the value could be
  greater than all other values?

There is no could be. There is no potential value. Unknown is unknown is unknown.

As to why it appears first, rather than last, this is not catered for by published SQL standards and is unfortunately left to the discretion of the RDBMS vendor:

  Wikipedia: The SQL standard does not explicitly define a default sort order for
  Nulls. Instead, on conforming systems, Nulls can be sorted before or
  after all data values by using the NULLS FIRST or NULLS LAST clauses
  of the ORDER BY list, respectively. Not all DBMS vendors implement
  this functionality, however. Vendors who do not implement this
  functionality may specify different treatments for Null sorting in the
  DBMS.


SQL Server Agent - Run Query at the end of each month with date variable I am running SQL Server 2008 R2, and I need to create a new job that will basically run a query at the start of each month (at the 1st of each month at 01:00 AM).

Here is the query:

INSERT INTO [SupportTracker].[dbo].[DashboardRecords]
SELECT [bg_id]
      ,[bg_short_desc]
      ,[bg_reported_date]
      ,[bg_status_updated_date]
      ,[us_firstname]
      ,[us_lastname]
      ,[LastUpdatedUserFirstname]
      ,[LastUpdatedUserLastname]
      ,[st_name]
      ,[pr_name]
      ,[ct_name]
      ,[pj_name]
      ,[AssignedUserFirstname]
      ,[AssignedUserLastname]
      ,[bg_project]
      ,[no_of_hours]
      ,[BugType]
      ,[SubType]
      ,[Device]
      ,[pj_parent_id]
FROM [SupportTracker].[dbo].[ViewIssueListwBugTypeNDevice]
WHERE
     bg_reported_date between '2012-12-01 00:00:00.000' AND '2012-12-31 23:59:59.999'
     AND bg_id NOT IN (SELECT bg_id FROM [SupportTracker].[dbo].[DashboardRecords])
ORDER BY bg_reported_date ASC

My problem is at the WHERE clause. These two dates have to change every month.

If we are on the 01-01-2013 01:00:00:00, I need these 2 dates to be:
2012-12-01 00:00:00.000 and 2012-12-31 23:59:59.999
If we are on the 01-02-2013 01:00:00:00, I need these 2 dates to be:
2013-01-01 00:00:00.000 and 2013-01-31 23:59:59.999.

Basically I need to capture parts of the database and save it in the table, as it was at the end of each month...

Thanks

To find the first and last days of the month:

DECLARE @MonthStart DATETIME
DECLARE @Date  DATETIME
DECLARE @next DATETIME
-- create the required date. because today is the 21st i subtracted month.
-- since you're running the job on the first day of the new month,
-- you may want to change MONTH to DAY. however, if the job fails that change
-- that may cause you some problems
SET @Date = DATEADD(MONTH,-1,DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))

-- get the first day of the month of the date specified
SET @MonthStart = DATEADD(DAY, 1, @Date - DAY(@Date) + 1) -1

-- get the first day of next month
SET @Next = DATEADD(MONTH,1,@MonthStart)

-- prove to yourself that these dates are correct (sanity check)
SELECT @MonthStart, @next

I've laid it out like this for you so that you can follow the logic, you can edit to your needs.

As an aside, the NOT IN convention is somewhat of an antipattern and some experts recommend against using BETWEEN.

LEFT JOIN

INSERT INTO [SupportTracker].[dbo].[DashboardRecords]
SELECT [bg_id]
      ,[bg_short_desc]
      ,[bg_reported_date]
      ,[bg_status_updated_date]
      ,[us_firstname]
      ,[us_lastname]
      ,[LastUpdatedUserFirstname]
      ,[LastUpdatedUserLastname]
      ,[st_name]
      ,[pr_name]
      ,[ct_name]
      ,[pj_name]
      ,[AssignedUserFirstname]
      ,[AssignedUserLastname]
      ,[bg_project]
      ,[no_of_hours]
      ,[BugType]
      ,[SubType]
      ,[Device]
      ,[pj_parent_id]
FROM [SupportTracker].[dbo].[ViewIssueListwBugTypeNDevice] b
LEFT JOIN [SupportTracker].[dbo].[DashboardRecords] d
    ON b.bg_id = d.bg_id
WHERE b.bg_reported_date >= @MonthStart and b.bg_reported_date < @Next
    AND d.[bg_short_desc] IS NULL

(this assumes [bg_short_desc] is a non-nullable field in the [DashboardRecords] table.

EVEN BETTER would be

NOT EXISTS

INSERT INTO [SupportTracker].[dbo].[DashboardRecords]

SELECT [bg_id]
      ,[bg_short_desc]
      ,[bg_reported_date]
      ,[bg_status_updated_date]
      ,[us_firstname]
      ,[us_lastname]
      ,[LastUpdatedUserFirstname]
      ,[LastUpdatedUserLastname]
      ,[st_name]
      ,[pr_name]
      ,[ct_name]
      ,[pj_name]
      ,[AssignedUserFirstname]
      ,[AssignedUserLastname]
      ,[bg_project]
      ,[no_of_hours]
      ,[BugType]
      ,[SubType]
      ,[Device]
      ,[pj_parent_id]
FROM [SupportTracker].[dbo].[ViewIssueListwBugTypeNDevice] b
WHERE b.bg_reported_date >= @MonthStart and b.bg_reported_date < @Next
    AND NOT EXISTS (SELECT 1 FROM [SupportTracker].[dbo].[DashboardRecords] d
        WHERE b.bg_id = d.bg_id);

Also, why are you ordering your inserts? Ordering would be more useful when you're selecting from the DashboardRecords table, right?


Is there a good way to link tables and sequences? I'm pretty sure you can't do this like you can with foreign keys but I'd like to document at the database level (even if it's just as a bit of free text somewhere) these relationships.

Is there some way of adding comments to tables or something like that?  I've noticed comments in the sql used to create things are often preserved but not sure how you edit these without recreating the whole object or how you view them without dumping ddl.

I'm interested in doing this in sqlserver and oracle.  I'm primarily doing edits from the command line/scripts but often look at the databases using SSMS and Sql developer.

The reason I'm interested in this is to let other developers know how to use the database - both future devs + the ones I'm currently working with.  We've got 3 applications using the same db and unfortunately its missing primary key and foreign key constraints in a lot of areas, whilst putting those in will go a long way to making it easier to understand, this would also help a lot aswell.

One hacky way I can think of doing it is adding an extra column to each table with the sequence name but I dont really like that - think its a bit misleading.

Easy in Oracle:

comment on table mytable is 'This is a table comment';

comment on column mytable.mycolumn is 'This is a column comment';

You can then view the comments using the USER_TAB_COMMENTS and USER_COL_COMMENTS data dictionary views.

This answer covers it for SQL Server.


How to get row_count diagnostic after EXPLAIN ANALYZE in Postgres? I normally retrieve the row_count of a query by doing GET DIAGNOSTICS row_count (normally within a function).

But if I want to run EXPLAIN ANALYZE on a query AND get row_count, I can't see any easy way to do that other than parsing the string output, because then GET DIAGNOSTICS row_count would return the number of rows in the EXPLAIN output.

Is there any built in way to do this other than parsing the string?

Having come to this conclusion that there isn't an easy built-in way, here is my quick solution for this:

Create this function to capture the explain analyze plan result:

CREATE OR REPLACE FUNCTION get_explain_analyze(p_sql text)
RETURNS TABLE("QUERY PLAN" text)
AS
$BODY$
BEGIN
  RETURN QUERY EXECUTE 'EXPLAIN ANALYZE '||p_sql;
END;
$BODY$
LANGUAGE plpgsql;

Run this to get the row count (only tested for select/create temp table statements - regex to the 2nd rows= number):

SELECT substring("QUERY PLAN" from 'rows=.+rows=(\d+)') AS row_count
FROM get_explain_analyze('SELECT 1')  -- query here
LIMIT 1;


Column Level Encryption in SQL Server 2016 When using the column level encryption approach, do we need to explicitly close the symmetric key in stored procedures/views or its not required? how it's handled for the subsequent openings ?

eg:

CLOSE SYMMETRIC KEY SQLSymmetricKey

Taking into consideration the remarks of CLOSE SYMMETRIC KEY (highlighting mine)

  Open symmetric keys are bound to the session not to the security
  context. An open key will continue to be available until it is either
  explicitly closed or the session is terminated.

Security best practices usually suggest having a security mechanism (symmetric key) open for the minimum amount of time that is it necessary and only in the scope (stored procedure) necessary.

Referencing the example code in DECRYPTBYKEYAUTOCERT, there are basically two ways to decrypt column encrypted data.

OPTION ONE

Typically what you'd do in a stored procedure where you open the key,
decrypt the data using DecryptByKey and then close the key before
returning control.

OPTION TWO

Can be used in a view that utilizes the DecryptByKeyAutoCert
function to automatically decrypt the data without having to
explicitly open the key.  (Requires VIEW DEFINITION permission on the symmetric key and CONTROL permission on the certificate.)

--Create the keys and certificate.
USE AdventureWorks2012;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mzkvdlk979438teag$$ds987yghn)(*&4fdg^';
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mzkvdlk979438teag$$ds987yghn)(*&4fdg^';
CREATE CERTIFICATE HumanResources037
   WITH SUBJECT = 'Sammamish HR',
   EXPIRY_DATE = '10/31/2009';
CREATE SYMMETRIC KEY SSN_Key_01 WITH ALGORITHM = DES
    ENCRYPTION BY CERTIFICATE HumanResources037;
GO
----Add a column of encrypted data.
ALTER TABLE HumanResources.Employee
    ADD EncryptedNationalIDNumber varbinary(128);
OPEN SYMMETRIC KEY SSN_Key_01
   DECRYPTION BY CERTIFICATE HumanResources037 ;
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber
    = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);
GO
--
--Close the key used to encrypt the data.
CLOSE SYMMETRIC KEY SSN_Key_01;
--
--There are two ways to decrypt the stored data.
--
--OPTION ONE, using DecryptByKey()
--1. Open the symmetric key
--2. Decrypt the data
--3. Close the symmetric key
OPEN SYMMETRIC KEY SSN_Key_01
   DECRYPTION BY CERTIFICATE HumanResources037;
SELECT NationalIDNumber, EncryptedNationalIDNumber
    AS 'Encrypted ID Number',
    CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
    AS 'Decrypted ID Number'
    FROM HumanResources.Employee;
CLOSE SYMMETRIC KEY SSN_Key_01;
--
--OPTION TWO, using DecryptByKeyAutoCert()
SELECT NationalIDNumber, EncryptedNationalIDNumber
    AS 'Encrypted ID Number',
    CONVERT(nvarchar, DecryptByKeyAutoCert ( cert_ID('HumanResources037') , NULL ,EncryptedNationalIDNumber))
    AS 'Decrypted ID Number'
    FROM HumanResources.Employee;


Msg 7391, Distributed Transactions (DTC) on SQL Server This may be DBA related or developer related, I honestly don't know.

I have two Database servers:

A: SQL Server 2005 (Trigger that uses DBMail on Server B)
B: SQL Server 2008 R2 (DBMail configured)

I have a table in Server A where email notifications are queued and I created an "after insert" trigger to execute sp_send_dbmail using Server B like so...

USE SampleDB
ALTER TRIGGER [dbo].[mail_inserted]
   ON [dbo].[tblEmailQueue]
   AFTER INSERT
AS
BEGIN
    declare @MailTo nvarchar(max), @MailSubject nvarchar(max), @MailBody nvarchar(max)
    -- Initialize vars
    select @MailTo=MailTo from inserted
    select @MailSubject=MailSubject from inserted
    select @MailBody=MailBody from inserted

    -- Send email via DbMail on ServerB
    exec [ServerB].msdb.dbo.sp_send_dbmail --this runs fine by itself, but not via trigger
        @profile_name = 'Test Profile'
        ,@from_address = 'noreply@test.com'
        ,@recipients = @MailTo
        ,@subject = @MailSubject
        ,@body = @MailBody
END

Here's where the weirdness begins that I need help with, mind you this seems to be a very poorly documented (or severely lacking thereof) feature.

Now I can execute that last statement with some sample data and everything works just fine. But when I try to insert into the table like so...

insert dbo.tblEmailQueue (MailTo,MailFrom,MailSubject,MailBody) values
    ('to-me@test.com','from-somebody@test.com','Subject','Body')

I get the following ERROR...

OLE DB provider "SQLNCLI" for linked server "ServerB"
    returned message "The partner transaction manager has disabled its support for
    remote/network transactions.".
Msg 7391, Level 16, State 2, Procedure mail_inserted, Line 15
The operation could not be performed because OLE DB provider "SQLNCLI" for
    linked server "ServerB" was unable to begin a distributed transaction.

Perhaps I'm not understanding how Server A is connecting to Server B under the context of the trigger? Do I need to setup a special Login or something? I'm stumped.

I would imagine its either:

EXEC sp_serveroption @server = 'ServerB',@optname = 'remote proc transaction promotion', @optvalue = 'false' ;

  Use this option to protect the actions of a server-to-server procedure
  through a Microsoft Distributed Transaction Coordinator (MS DTC)
  transaction. When this option is TRUE (or ON) calling a remote stored
  procedure starts a distributed transaction and enlists the transaction
  with MS DTC.

Or could be an issue with MSDTC configuration, which is a laborious and lengthy checklist to run through, documented fully in Recommended MSDTC settings for using Distributed Transactions in SQL Server.


Unable to truncate transaction log, log_reuse_wait_desc - AVAILABILITY_REPLICA This morning i was woken up by a transaction log full alert on one of our database.   This server is an alwayson cluster and also a transactional replication subscriber.
I checked log_reuse_wait_desc and it showed logbackup.  Someone had accidentally disabled the logbackup jobs 4 days earlier, I re-enabled the log backup job and the log got cleared.  Since it was 4am I thought I will go to office later that morning and shirnk the log as it has grown to 400GB.

10AM-  Im in office and I check the log usage before shrinking and it was around 16%.  I was surprised and check the log_reuse_wait_desc, which showed replication.  I was confused cause this was a replication subscriber.  We then saw that the db was enabled for CDC and thought that might be the cause, so disabled CDC and now the log_reuse_wait_desc shows AVAILABILITY_REPLICA.

The log usage meanwhile still steadily growing and its at 17% now.  I check the alwayson dashboard and check the sent and redo queue and both are virtually zero.  I am not sure why the log reuse is showing as   AVAILABILITY_REPLICA and unable to clear the log.

Any idea why this is happening?

If you do this:

SELECT * FROM sys.databases

And the log_reuse_wait_desc shows AVAILABILITY_REPLICA, that means SQL Server is waiting to send log data to one of your Always On Availability Group replicas. One of the replicas may be lagging behind due to a slow network, or it may be down altogether.

If you check the AG dashboard and it shows no queues, you may have been a victim of thread exhaustion. It's a known issue that the AG dashboard stops updating after worker thread exhaustion. You'll need to check the status on each replica directly rather than relying on the primary. Nick's note in that Connect item says that you can just alter a replica's properties to restart replication, but that doesn't always work (especially if you have hundreds of databases on a replica with a large amount of data that needs to be sent, and restarting replication can just cause the worker thread exhaustion again.)

If the last guy set up an AG replica and it's not supposed to exist anymore, then it's time to remove that AG and/or replica. Just be careful that apps aren't pointing to the listener name in order to connect to your SQL Server.


Postgresql split date range by business days then aggregate by month I have the following rows in a table (dd-mm-yyyy).

Start      | End        | Value
---------------------------------
01-01-2019 | 31-03-2019 | 64
01-02-2019 | 30-04-2019 | 126
01-03-2019 | 31-05-2019 | 66

I would like to divide the values by number of working days (just mon-fri, not holidays) between the start and end dates, then aggregate the values that occur in each month in postgres.

Based on this year's working days per month being:

Month  | Working Days
--------------------
Jan 19 | 23
Feb 19 | 20
Mar 19 | 21
Apr 19 | 22
May 19 | 23

The 1st row has 64 working days in Jan, Feb, Mar - so it's value per working day is 1. And has an aggregate value of 23 in Jan, 20 in Feb, 21 in Mar.
The 2nd row has 126 working days in Feb, Mar, Apr - so it's value per working day is 2. And has an aggregate value of 40 in Feb, 42 in Mar, 44 in Apr.
The 3rd row has 66 working days in Mar, Apr, May - so it's value per working day is 1. And has an aggregate value of 21 in Mar, 22 in Apr, 23 in May.
If we sum all the aggregate values for each month for each row we should get the result below.

Month   | Value
----------------
01-2019 | 23
02-2019 | 60
03-2019 | 84
04-2019 | 66
05-2019 | 23

So it's like grouping by month but the values are weighted by the number of working days per month.

is it possible to do this in postgres?

Assuming the table has a primary (or unique) key, then you can calculate the "value per day" using this:

select to_char(dt, 'yyyy-mm') as month,
       value,
       value::numeric / count(*) over (partition by id) as value_per_day
from data
  left join generate_series(start, "end", interval '1 day') as t(dt) on true
where extract(dow from dt) not in (0,6) ;

generate_series() generates rows for each day between start and end, and the where clause removes the weekends.
id is the unique identifier for the original rows it is required to be able to count the number of days "per row". With that result we can now aggregates the final result:

select month, sum(value_per_day)
from (
  select to_char(dt, 'yyyy-mm') as month,
         value,
         value::numeric / count(*) over (partition by id) as value_per_day
  from data
    left join generate_series(start, "end", interval '1 day') as t(dt) on true
  where extract(dow from dt) not in (0,6)
) t
group by month
order by month;

Online example: https://rextester.com/KPGOFU25611


Export multiple tables among servers We currently have an SSIS package that exports about 70 large tables nightly, with truncate and load at the destination. Our tables keep changing, we sometimes add some tables and remove some and editing the SSIS package accordingly is tiring.

Is there a way to have a text file or a table with the list of table names in it and have an SSIS package to loop the table and perform the export? That way, it is easier to handle dynamic list of tables that get exported.

Any links, pointers are appreciated.

what you are looking for is to dynamically create tables in the destination.
or in other words Copying tables from Source to destination programmatically

here is a very useful article that transfers tables from access to sql dynamically
but you can take the same concept and apply it on your source as well.

https://stackoverflow.com/questions/6298806/how-do-i-programmatically-get-the-list-of-ms-access-tables-within-an-ssis-packag


Subquery Applying Incorrect Predicate? I have a query which contains a subquery. When executed on a local copy of my database it runs as normal and executes within < 1 second.

I've noticed however that if I run this same query on our server the inner query behaves like a correlated subquery, taking around 90 seconds, and the predicate applied to a clustered index scan for data which relates to the subquery appears to be incorrect - it contains the alias used in the outer query, even though all aliases used in the inner query are different, and instead of 1 execution for the index scan it shows hundreds.

I've noticed that this behaviour only applies when the query executes serially - if I lower the cost threshold for parallelism on the server and it goes parallel then this behaviour does not appear and the query runs correctly. Notably, the query runs correctly in my local environment whether serial or parallel, and the predicate applied there looks fine.

Both the local environment and the server are running SQL Server 2016 SP1 CU7 (Enterprise on the server, Developer locally), and I've looked at the different SET options and verified that they are using the same compatibility level (SQL 2012).

Is there anything that would explain the difference of behaviour here?

The problem is not that the plan is different, it's that it's not a correlated subquery and is being executed as one - the inner query is incorrectly referencing the outer query when the query executes serially, but not when it goes parallel on the same server.

The server is set to 2012 compatibility mode because when we upgraded, the new cardinality estimator caused some problems with a few queries and we haven't gotten around to fixing those yet so have remained on 2012 compatibility mode to use the legacy cardinality estimator.

All 4 plans can be viewed here.

Looking at the query and then at the clustered index scan on the deposit payment table, this shouldn't be using the predicate it is using as it references the outer query, which unless I'm misunderstanding, is incorrect. It also shouldn't be executing that operator the 200+ times that it is.

On the same server, if I lower the cost threshold for parallelism so that it goes parallel, it applies a different predicate to that operator where it does not reference the outer table and does not behave like a correlated subquery. This is the same behaviour I see if I run the query locally (either running serially or parallel).

The big difference in the serial plan that I can see is this expensive TOP operator that has been introduced by the optimizer:

The TOP is probably there due to row goals, there is a 4-part series on this at SQLPerformance.com:

Setting and Identifying Row Goals in Execution Plans
Row Goals, Part 2: Semi Joins
Row Goals, Part 3: Anti Joins
Row Goals, Part 4: The Anti Join Anti Pattern

(Part 1 and maybe part 2 are relevant for this query.)

I can't confirm for sure in your case because you're using 2016 and the showplan properties that expose row goal information weren't added until 2017 CU3 (and were added in 2016 SP2, just released this week). You can see them if you show the output from running the original, serial query on the server adding the following (taken from part 1 of the series above):

OPTION (QUERYTRACEON 3604, QUERYTRACEON 8607, QUERYTRACEON 8612);

Estimates are different all over the place, so that tells me that data and/or stats are different, but probably mostly stats since local and server both give the same actual rows.

I suggest a rewrite, changing IN to EXISTS, simplifying the subquery (a lot of redundant work there), and removing the GROUP BY (which is unnecessary busy work, and may be the culprit here):

SELECT p.id_prov,
       p.name,
       ps.val
FROM dbo.provider_payment pp
INNER JOIN dbo.provider p
  ON p.id_prov = pp.id_prov
INNER JOIN dbo.payment_settings ps
  ON ps.id_prov_payment = pp.id_prov_payment
WHERE pp.payment_method = 'PROTX'
  AND ps.setting = 'VendorName'
  AND p.is_deleted = 0
  AND p.status = 'Y'
  AND EXISTS
  (
    SELECT 1
      FROM dbo.deposit_payment dp
      WHERE dp.id_prov = p.id_prov
        AND dp.payment_method = pp.payment_method
        AND dp.create_date > '2018-01-01'
  );

Updating stats can't hurt, but let us know if the rewrite helps. You could also try hinting the newer cardinality estimator using OPTION (QUERYTRACEON 2312); and in case you are hit by anything that has since been fixed in the optimizer you can also try 4199.

Leaving the local variance out, the two plans on the server have the same predicate on the scan of deposit_payment, except that the parallel plan uses a probe for id_prov. So I really don't think the predicate has anything to do with any performance differences:

The only difference between server and local is that the local serial plan handles the id_prov filter later, in a hash match. Exercise to the reader to determine if that's better or worse. :-)

I would focus on writing simpler queries without unnecessary and/or redundant logic (and perhaps introducing a supporting index on deposit_payment to help this type of query without a scan/predicate) rather than trying to discover bugs. :-)


Mysql replication between 3 + 1 nodes Suppose I have the following configuration for mysql 5.6 replication.
Nodes
A
B
C
D
Nodes A,B,C have wsrep_cluster_address set to respective ip's of nodes A,B and C. Then node D has wsrep_cluster_address set to respective ip's of nodes A,B,C and D.
Then when data is written to nodes A,B,C it is replicated to A,B,C,D, but when data write happens on D - it won't get replicated to A,B,C - is that right ?
Also, what happens when data is written on D and same record is changed on either A,B or C - does it get overwritten based on time of the actual change ?
Really the goal of D is just to have a constantly replicated copy in another zone in case a disaster happens.

Have a cluster with nodes A,B,C, but not D.
Have D be a Slave to one of the nodes.  Do not configure it as a wsrep node.  Do not write to it.
More
A cluster with 3 or more nodes provides HA and failover if any one node goes down.
If those 3 nodes are in the same datacenter, then they are vulnerable to a disaster that takes down the DC.  (That can happen.)
If those 3 nodes are in 3 geographical locations, the cluster can survive the lost of any one of them.  Hence, it is better than 3 in a single DC.
When something happens with a cluster, all the alive nodes vote to see what the situation is.  If more than 50% can talk to each other, they will declare that they are the (degraded) cluster, and that the other node(s) will need repairing when (if) they reconnect.
If you have 3 nodes in one DC and a 4th in another DC, the cluster will not survive an outage of either DC.
If those two DCs lose connectivity, but everything other than the network stays up, the DC with 3 nodes won't be able to see the 4th; they will kick it out of the cluster.  The lone #4 will see that it can't get 50%+, so it stops accepting writes (at least).
Back to your question...
A 3-node cluster in one DC; a 4th node in another DC.  If the 3-node DC goes dark, you could manually build a new cluster around the 4th.  But that is far from automated.  You could, however, say that a DC going down (and not coming back up 'soon') is so rare that you are willing to build this topology.  And, if the DC never comes back up (tornado, flood, earthquake, tsunami), you would have a copy of the data in #4.  Better than nothing.
So, if you go with 3+1, I recommend that the 3 is a fully functioning Galera cluster.  (Or it could even be 2 live + 1 garbd.)  Then #4 would be an ordinary replica that is asynchronously replicating from one of the 3 nodes.
With that config, Galera does not know or care about #4, and is not burdened by long-distance syncing of gcache.
It's a tradeoff between vulnerability and speed.  You can't solve both.  Hopefully, I have given you enough understanding of what can be done so that you can decide what "keeps you up at night" the least.


Property Size is not available for database I recently restored a database to the same instance it was backed up from (SQL Server 2008 R2 Enterprise) and found that I couldn't access the database properties.

I have done the following:

Checked the database owner was set correctly using sp_helpdb.
Changed the database owner to sa. Not a fix.
Changed the database owner back to my sysadmin user. Not a fix.
Issued DBCC updateusage against the affected database. Not a fix.
Run DBCC CheckDB on a restored copy to another instance. No corruption found. The restored copy (from the same backup file) did not throw any errors when accessing the database properties window.

Can anyone help?

The error message I get when trying to view properties is:

  Cannot show requested dialog. (SqlMgmt)
  Property Size is not available for Database '[DBNAME]'.
  This property may not exist for this object, or may not be retrievable due to insufficient access rights.  (Microsoft.SqlServer.Smo)

I am a sysadmin on this instance.

Update: As suggested I created a new user, made it sysadmin and changed the database owner to it. Not a fix unfortunately. I will see if a profiler trace yields anything useful.

Update: Aaron - the original database was renamed and taken offline but is still on that instance. The backup of that database was then restored using the original name. The filenames of the new database files are different from the original as they live in the same folder as the original mdf/ldf. The restored db is currently driving our critical apps as normal.

I have resolved this by restarting the SQL Server service, unfortunately/fortunately.

My initial idea was to detach/attach the database. Not that I thought this would actually fix the problem, I just read that in a forum somewhere. This had no effect.

I regrettably did not do what  PJ Mahoney suggested re: running a trace. I hope I see this issue again so I can find out if a trace reveals anything. Thanks for the suggestion.

At least answering my own question means I don't have to see another suggestion about changing the database owner ;P

Thanks


How do I decompose ctid into page and row numbers? Each row in a table has a system column ctid of type tid that represents the physical location of the row:

create table t(id serial);
insert into t default values;
insert into t default values;

select ctid
     , id
from t;

ctid  | id
:---- | -:
(0,1) |  1
(0,2) |  2

dbfiddle here

What's the best way of getting just the page number as from the ctid in the most appropriate type (eg integer, bigint or numeric(1000,0))?

The only way I can think of is very ugly.

SELECT (ctid::text::point)[0]::bigint AS page_number FROM t;

Your fiddle with my solution.

@bma already hinted something similar in a comment. Here is a ...

Rationale for the type

ctid is of type tid (tuple identifier), called ItemPointer in the C code. Per documentation:

  This is the data type of the system column ctid. A tuple ID is a
  pair (block number, tuple index within block) that identifies the
  physical location of the row within its table.

Bold emphasis mine. And:

  (ItemPointer, also known as CTID)

A block is 8 KB in standard installations. Maximum Table Size is 32 TB. It follows logically that block numbers must accommodate at least a maximum of (calculation fixed according to comment by @Daniel):

SELECT (2^45 / 2^13)::int      -- = 2^32 = 4294967294

Which would fit into an unsigned integer. On further investigation I found in the source code that ...

  blocks are numbered sequentially, 0 to 0xFFFFFFFE.

Bold emphasis mine. Which confirms the first calculation:

SELECT 'xFFFFFFFE'::bit(32)::int8 -- max page number: 4294967294

Postgres uses signed integer and is therefore one bit short. I couldn't pin down, yet, whether the text representation is shifted to accommodate signed integer. Until somebody can clear this up, I would fall back to bigint, which works in any case.

Cast

There is no cast registered for the tid type in Postgres 9.3:

SELECT *
FROM   pg_cast
WHERE  castsource = 'tid'::regtype
OR     casttarget = 'tid'::regtype;

 castsource | casttarget | castfunc | castcontext | castmethod
------------+------------+----------+-------------+------------
(0 rows)

You can still cast to text. There is a text representation for everything in Postgres:

  Another important exception is that "automatic I/O conversion casts",
  those performed using a data type's own I/O functions to convert to or
  from text or other string types, are not explicitly represented in
  pg_cast.

The text representation matches that of a point, which consists of two float8 numbers, that cast is lossless.

You can access the first number of a point with index 0. Cast to bigint. Voilá.

Performance

I ran a quick test on a table with 30k rows (best of 5) on a couple of alternative expressions that came to mind, including your original:

SELECT (ctid::text::point)[0]::int                              --  25 ms
      ,right(split_part(ctid::text, ',', 1), -1)::int           --  28 ms
      ,ltrim(split_part(ctid::text, ',', 1), '(')::int          --  29 ms
      ,(ctid::text::t_tid).page_number                          --  31 ms
      ,(translate(ctid::text,'()', '{}')::int[])[1]             --  45 ms
      ,(replace(replace(ctid::text,'(','{'),')','}')::int[])[1] --  51 ms
      ,substring(right(ctid::text, -1), '^\d+')::int            --  52 ms
      ,substring(ctid::text, '^\((\d+),')::int                  -- 143 ms
FROM tbl;

int instead of bigint here, mostly irrelevant for the purpose of the test. I didn't repeat for bigint.
The cast to t_tid builds on a user-defined composite type, like @Jake commented.
The gist of it: Casting tends to be faster than string manipulation. Regular expressions are expensive. The above solution is shortest and fastest.


MySQL to use all 20 cores I have I am currently setting up a website that uses a MySQL database having more than 40 million rows of data. I am using one server for files and separate mysql server. Two servers have 20 cores, 64G ram.
Meanwhile I could see while executing longest SQL queries my MySQL server is using two cores maximum hence it is taking more than 38 seconds to execute my longest query. See the result below.

User@Host: dev_data @ localhost []
Query_time: 38.113460 Lock_time: 0.000514
Rows_sent: 10 Rows_examined: 48683733

How can I configure my MySQL server so that it uses all the 20 cores to handle the query? MySQL version is 5.6.
Atop result below.

tl;dr You can't force MySQL to use a lot of cores.

40 million (4 crore) rows is a large, but not huge, database for MySQL. It is well within the capability of that software. You don't have to resort to desperate measures to get MySQL to work with that amount of data. You do have to index it correctly though.

MySQL can use a lot of CPU cores as of recent releases. But it doesn't use them to accelerate single queries; that sort of workload is not embarrassingly parallel.   You can't get it to use a large number of cores for a single query. It simply doesn't work that way.

When it uses many cores, it uses them to handle simultaneous workload from many different connections. But of all those connections hit the same tables, it won't be able to use a lot of cores even if they are available.

At any rate, you should upgrade to version 5.7. It uses multithreading for more things than 5.6.

You will be able to improve performance with appropriate indexing for your query workloads. We can't advise you about that because you haven't offered any specifics about existing tables, indexes, queries, or simultaneous access by multiple clients.

Your server, with 20 cores and 64G of RAM, is overprovisioned in cores and RAM for MySQL, and for the sort of workload you mention.  You didn't mention your server's I/O capabilities. I guess that replacing your mechanical disk drives with SSD drives using PCIe interfaces will make the most difference. But that's a guess based on little information.

My blunt advice: sell that massive server, or put it into service as a hypervisor host to use for lots of virtual machines for other things.  It's overkill for MySQL.

Get a server with 16Gb and four fast cores. Use the extra money to provision it with as many PCIe SSD drives as you need for your application. Don't consider purchasing SSD drives smaller than about 0.2 terabyte; they are slower than the larger ones.  Index your tables properly for your queries. For tables of the size you mention, don't worry about core utiization; instead worry about I/O.

You can probably try all this out for a few tens of dollars on a cloud virtual machine (Amazon, Rack Space, Digital Ocean) so you don't waste money on another overprovisioned server.


Is there any name for common sense / knowledge integrity constraints? The Wikipedia page on data integrity lists 4 types of integrity constraints: entity integrity, referential integrity, domain integrity, and user-defined integrity.

Is there a name for integrity constraints that stem from common sense / knowledge?

Example :

I have one table with 6 columns containing some data about hospital admissions:

hospital_admission_id (integer field)
patient_id (integer field)
patient_date_of_birth (date field -> I am aware that it should be in some patient table but ignore this in this example)
date_of_hospital_admission (date field)
date_of_hospital_discharge (date field)
hospital_admission_note (text field: a relatively long text by the medical staff summarizing some data about the patient when he was admitted at the hospital)

One integrity constraint stemming from common sense / knowledge is that the patient more than one year old at the time of hospital admission (i.e. date_of_hospital_admission - patient_date_of_birth > 1 year), hospital_admission_note shouldn't say that the patient is a neonate.

Another integrity constraint from common sense / knowledge is that a patient cannot be admitted in the hospital if he already is in the hospital (i.e. some checks would have to be done around date_of_hospital_admission and date_of_hospital_discharge).

I feel that:

It's not an entity integrity constraint since it's not an issue around primary keys.
It's not a referential integrity constraint since it's not an issue around foreign keys.
It's not a domain integrity constraint since it's not about checking whether values are in some list.
I guess it could be qualified as a user-defined integrity constraint but I thought they might be some more precise technology to specifically designate integrity constraints stemming from common sense / knowledge and not just a specific user's preferences.

You're going beyond the scope of referential constraints with this (good) question. What you are looking for is more to do with Ontologies and Archetypes than RDBMS constraints. You could also check out this page which is on "Intelligent Databases" and gives as an example, medical records.

Check out the OpenEHR (Open Electronic Health Record here) whereby the system goes beyond data storage (essentially AIUI, your question). It's a fascinating area, touching on machine learning and artifical intelligence. It is far from a trivial problem - and in particular with medical records with all sorts of different regulatory frameworks and privacy concerns. Doctors, surgeons, dentists and pharmacists have different "views" of a patient - how to tease all these out is a major issue in the field. It will come as no surprise that the OpenEHR system is one of many competing in this fascinating and challenging area.

From the OpenEHR (what is page)

  What is openEHR?

  Abstract architecture image openEHR is a virtual community working on
  interoperability and computability in e-health. Its main focus is
  electronic patient records (EHRs) and systems.

  The openEHR Foundation has published a set of specifications defining
  a health information reference model, a language for building
  'clinical models', or archetypes, which are separate from the
  software, and a query language. The architecture is designed to make
  use of external health terminologies, such as SNOMED CT, LOINC and
  ICDx. Components and systems conforming to openEHR are 'open' in terms
  of data (they obey the published openEHR XML Schemas), models (they
  are driven by archetypes, written in the published ADL formalism) and
  APIs. They share the key openEHR innovation of adaptability, due to
  the archetypes being external to the software, and significant parts
  of the software being machine-derived from the archetypes.

  The essential outcome is systems and tools for computing with health
  information at a semantic level, thus enabling true analytic functions
  like decision support, and research querying.


MySQL optimized query Now I came across an issue, I have table as below:

Task Id       StudentId    Marks
  1                1         12
  1                2         15
  1                3         17
  2                1         15
  2                3         20
  3                1         8
  3                3         5
  3                5         17

I was required to get a lists of studentid who has max and marks in each task. So the required output is:

TaskId               StudentId      Marks

 1                    3            17
 2                    3            20
 3                    5            17

Now I created a temporary table to store max.marks of each task.

TaskId  Max.Marks

 1     17
 2     20
 3     17

Then I joined the temporary table to the main table and fetched the row with marks equal to max.marks of that task.

Can anyone suggest a better query without any sub-queries and temporary table?

Thanks

Instead of temporary table, use a subquery (a derived table), then join it to the original marks table:

SELECT
    t.TaskId, t.StudentId, t.Marks
FROM
        tableX AS t
    JOIN
        ( SELECT
              TaskID
            , MAX(Marks) AS MaxMarks
          FROM
              tableX
          GROUP BY
              TaskID
        ) AS m
            ON  m.TaskId = t.TaskId
            AND m.MaxMarks = t.Marks ;

If you have an index on (TaskId, Marks, StudentId), it will help efficiency.

You can also use this variation:

SELECT
    t.TaskId, t.StudentId, t.Marks
FROM
        ( SELECT DISTINCT
              TaskId
          FROM
              tableX
        ) AS dt
    JOIN
        tableX AS t
            ON  t.TaskId = dt.TaskId
            AND t.Marks =
                ( SELECT
                      tm.Marks
                  FROM
                      tableX AS tm
                  WHERE
                      tm.TaskId = dt.TaskId
                  ORDER BY
                      tm.Marks DESC
                  LIMIT 1
                ) ;


SQL Server transaction replication agent error We are running transaction replication on SQL Server 2008 R2 SP3. Distribution agents are reporting :

  "Agent  is retrying after an error. 3 retries attempted"

every 10 minutes. Tried changing the query timeout values, restarted SQL Server agent and stopped and restarted the distribution agent. There are no errors in the job log, SQL Server error logs or windows event logs that appear relevant.

Note: This answer is based on OP's comment under the question. So credit goes to them for finding the solution.

Faced this issue my self, which is how I ended up here.
Like @PMSawyer said, there was a negative value in the run_duration column of sys.jobhistory for the Agent job.

The following query can be used to find if there are any negative values.

  SELECT * FROM msdb.dbo.sysjobhistory
  WHERE job_id = (
  SELECT job_id FROM msdb.dbo.sysjobs
  WHERE name = 'INPUT_YOUR_AGENT_JOB_NAME_HERE'
  ) AND run_duration < 0
  ORDER BY run_date DESC

If this query produces an output, it is very likely that that is what is  causing the problem. The negative value will need to be removed by purging the agent job's history, the following query can be used for that.

  msdb.dbo.sp_purge_jobhistory
  @job_name = 'INPUT_YOUR_AGENT_JOB_NAME_HERE'

The output would look something like: 12 history entries purged..

Purging the job history doesn't affect replication in any way, in case that's a concern.

Also, in my case the reason this negative value came to be was that the server time had changed in accordance with Day Light Savings. The server clock and moved an hour behind.

On an unrelated note, I don't know fancy techniques like linking to OP's comment that provided the basis for this answer, if anyone cares enough to do that, it'd be great.


Difference between commit and checkpoint I was confused about the difference between a commit operation and a checkpoint. I referred to some of the articles like This Article

But they are advanced for my understanding. I mean both appear to be same i.e. saving data like a "Save" action. If we rollback, we can rollback only to the most recent checkpoint/commit. Am I right in my statements? Can someone explain?

A commit is a user initiated action that tells the database (Oracle in this case) that the transaction is completed and that the changes may be committed and any locks/resources released. Normally the changes are committed to in memory data buffers and to the redo log buffer.

A checkpoint though is a database initiated action that writes all of the data to the actual physical disk file based on the changes recorded in the redo log buffer. Some good articles can be found here and here.


How to connect multiple users/schemas in Oracle 11g? I'm almost new user to databases, so I think I'm doing something wrong.

I have the database created.
I created 3 users, 1 it's an Admin, 2 are just users.
I created 2 roles, 1 for the Admin and 2 for the other users.

I'm learning that the schemas in Oracle are different from SQL Server. In SQL Server I created tables assigned to a schema, for example: CREATE TABLE schema1.table1 and then I was be able to see them all with users asigned to that DB; with Oracle, I think a schema is the sum of the sequence, synonyms, etc that a user has. (If not, please correct me)

I want to achieve that in Oracle, so I created the users/schemas; after that I got connected with user1 and created the tables with that user; later, I got connected with user2 and created the tables related to the user.

Now, when I'm trying to create the alter relating user1 tables and user2 tables with admin, it says I don't have enough privileges.

ALTER I'm trying to do:

ALTER TABLE user1.PhoneTable
    ADD (CONSTRAINT C_001 FOREIGN KEY (Status)
            REFERENCES user2.ClientTable (Status) ON DELETE SET NULL);

I created those users because if I wanna see all the tables related to the Phone(user1), I got connected to that user and that should be all, I just wanna have the DB with some order.

Admin privileges:

GRANT
    CREATE SESSION,
    UNLIMITED TABLESPACE,
    CREATE TABLE,
    DROP ANY TABLE,
    CREATE CLUSTER,
    CREATE SYNONYM,
    CREATE PUBLIC SYNONYM,
    CREATE VIEW,
    CREATE SEQUENCE,
    CREATE DATABASE LINK,
    CREATE PROCEDURE,
    CREATE TRIGGER,
    CREATE MATERIALIZED VIEW,
    CREATE ANY DIRECTORY,
    DROP ANY DIRECTORY,
    CREATE TYPE,
    CREATE LIBRARY,
    CREATE OPERATOR,
    CREATE INDEXTYPE,
    CREATE DIMENSION,
    CREATE ANY CONTEXT,
    SELECT ANY DICTIONARY,
    CREATE JOB,
    ALTER ANY TABLE,
TO myAdmin;

User privileges:

GRANT
    CREATE session,
    CREATE table,
    CREATE view
    CREATE procedure,
    CREATE synonym,
    ALTER ANY table,
    ALTER view,
    ALTER procedure,
    ALTER synonym,
    DROP table,
    DROP view,
    DROP procedure,
    DROP synonym
TO myUsers;

You need to grant the REFERENCES privilege on the reference table to user2.
(See GRANT, Table Privileges section. Note that this cannot be granted to a role, must be granted to the user directly.)

Here's a demo:

SQL> create user user1 identified by user1;
User created.
SQL> grant create session, create table, unlimited tablespace to user1;
Grant succeeded.

SQL> create user user2 identified by user2;
User created.
SQL> grant create session, create table, unlimited tablespace to user2;
Grant succeeded.

SQL> create table user1.ref_table (id number primary key);
Table created.
SQL> insert into user1.ref_table values (1);
1 row created.
SQL> insert into user1.ref_table values (2);
1 row created.
SQL> commit;
Commit complete.

SQL> grant references on user1.ref_table to user2;
Grant succeeded.

SQL> connect user2/user2;
Connected.
SQL> create table oth_table (thing number, fk number);
Table created.
SQL> alter table oth_table add(constraint fk1 foreign key (fk)
  2      references user1.ref_table on delete set null);
Table altered.

SQL> insert into oth_table values (42, 1);
1 row created.
SQL> insert into oth_table values (256, 2);
1 row created.
SQL> commit;
Commit complete.

SQL> connect user1/user1;
Connected.
SQL> delete from ref_table where id = 1;
1 row deleted.
SQL> commit;
Commit complete.

SQL> connect user2/user2;
Connected.
SQL> select * from oth_table;

     THING     FK
---------- ----------
    42
       256      2

If you want admin to do all the work, it'll need the CREATE ANY INDEX privilege, and you still need the REFERENCES grant to user2.

Here's how that could work:

SQL> create user user1 identified by user1;
User created.
SQL> create user user2 identified by user2;
User created.
SQL> create user admin identified by admin;
User created.

--- Grants
----------------
SQL> grant create session, create table, unlimited tablespace to user1;
Grant succeeded.
SQL> grant create session, create table, unlimited tablespace to user2;
Grant succeeded.
SQL> grant create session, create any table,
  2        create any index, alter any table to admin;
Grant succeeded.

--- Create reference table as admin
----------------
SQL> connect admin/admin
Connected.
SQL> create table user1.ref_table (id number primary key);
Table created.

--- Do the "references" grant
----------------
SQL> connect / as sysdba
Connected.
SQL> grant references on user1.ref_table to user2;
Grant succeeded.

--- Create the second table as admin
----------------
SQL> connect admin/admin
Connected.
SQL> create table user2.oth_table (foo number, bar number);
Table created.

--- Add the constraint
----------------
SQL> alter table user2.oth_table add(constraint fk
  2      foreign key (bar) references user1.ref_table(id)
  3      on delete set null);
Table altered.


Finding records not present across 3 tables - Sqlite3 From the diagram provided, what I'm trying to achieve is to get all the Users of a particular workspace that are NOT associated with a task within user_task.

user_workspace table contains two FKs: user_id (from User) and workspace_id (from Workspace)

user_task table contains two FKs: user_id (From user) and task_id (from Task)

Here's the SQL I'm tinkering with to give a sense of what I'm trying to achieve:

SELECT * FROM User INNER JOIN user_workspace ON (user_workspace.user_id = User._id)
LEFT OUTER JOIN user_task ON (user_task.task_id = Task._id)
WHERE (user_workspace.workspace_id= ?)
AND (user_task.task_id= ? AND user_task.user_id IS NULL)
ORDER BY User.name ASC

I think I'm close but I can't seem to get the exact result I want. Thanks for reading.

"Find all the Users of a particular workspace that are not associated with a specific task"

You need to move the AND user_task.task_id=? condition from the WHERE clause to the related ON.

(I think you also have a typo. ON user_task.task_id = Task._id should be ON  user_task.user_id = User._id):

SELECT User.*
FROM User
  INNER JOIN user_workspace
    ON  user_workspace.user_id = User._id
  LEFT OUTER JOIN user_task
    ON  user_task.user_id = User._id
    AND user_task.task_id = ?                <-- moved here
WHERE user_workspace.workspace_id = ?
  AND user_task.user_id IS NULL
ORDER BY User.name ASC ;

You could also use NOT EXISTS. I find the code more readable:

SELECT User.*
FROM User
  INNER JOIN user_workspace
    ON  user_workspace.user_id = User._id
WHERE user_workspace.workspace_id = ?
  AND NOT EXISTS
      ( SELECT *
        FROM user_task
        WHERE user_task.user_id = User._id
          AND user_task.task_id = ?
      )
ORDER BY User.name ASC ;


Should SQL Server statistics be included in versioning? Recently I ran the SQL Server Tuning Advisor. It suggested to add some statistics. Currently we have no statistics checked in our database versioning.

My question is: Should these suggested statistics be added to the versioning (like SVN or git)?

I am not sure this is a good idea, because they were created based on the usage of this particular database server. The alternative I currently like better is to run the Tuning Advisor on each server separatly, add the statistics and don't include them in schema compares.

What are the best practices? What are your negative/positive experiences?

If I am manually creating statistics then yes I would source control those, like I would any other database object. That way if I roll out a fresh environment from source I know it has everything required to function.

Why would I create manual statistics? Well I’d do this if I need control over the maintenance of them, for example if I am using table partitioning then I will manually create statistics and set them to incremental build and with no recompute. This way I can update the statistics manually as part of my ETL, just after I have switched in a new partition of data.


Rebuild dataguard configuration when standby is broken I have an Oracle Data Guard configuration with two servers:

oradb (physical)
oradbstb (standby)

Both are running with Oracle 11g R2.

A few days ago I had problems with the standby (the server was turned off and the primary files were not received ... which were subsequently deleted).

I would like to know how to reconfigure the standby server from scratch. What would be the steps that I must carry out?

On the primary database I make a full backup with rman every Saturday and the rest of the days I make a backup of archives also with rman.

How should I go about restoring my standby database again?
What steps should I follow?

I wouldn't try to "restore" it.  I'd just blow it away and rebuild from scratch, just like you did when you built it the first time.

And take steps to prevent the problem from repeating.


Postgres not null performance issues According to Performance Insights in Amazon RDS, the following query is causing some high wait times in my postgresql database:

SELECT (1) AS "a"
FROM "employee_employee"
WHERE ("employee_employee"."year" = 2018
  AND "employee_employee"."jurisdiction_id" = 537
  AND "employee_employee"."ual" IS NOT NULL)
LIMIT 1

This query comes from the python Django ORM and is written like this:

context["hide_ual"] = not records.filter(ual__isnull=False).exists()

I have indexes built for year and jurisdiction_id fields. The ual field is a numeric type and is very often NULL. I'm tempted to create a separate way to identify if a jurisdiction even has ual associated with it, so I can stop constantly checking to see if ual values exist. Before I do that, is there a better way to improve speed within the database?

You could create a partial index:

CREATE INDEX ON employee_employee (year, jurisdiction_id) WHERE ual IS NOT NULL;


Within a PL/SQL procedure, wrap a query or refcursor in HTML table It seems really easy if you are using SQL*Plus to use

SQL> set markup html on;

and get some lovely results to the SQL*Plus window.  we have an oracle job that runs overnight and sends an email of results to a number of people.  I would like to wrap a sql statement in an HTML table to be in that message.  What is the best way of doing that?

It is possible to generate xml with dbms_xmlgen, eg:

select dbms_xmlgen.getxml('select * from dual') as xml from dual;

XML
---
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <DUMMY>X</DUMMY>
 </ROW>
</ROWSET>

Using getxmltype instead, it is then possible to apply an xslt transform, perhaps like this:

select dbms_xmlgen.getxmltype('select * from dual').transform(XMLType(
'<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="html"/>
<xsl:template match="/">

      <xsl:for-each select="/ROWSET/ROW[1]/*">
       <xsl:value-of select="name()"/>
      </xsl:for-each>

     <xsl:for-each select="/ROWSET/*">

       <xsl:for-each select="./*">
        <xsl:value-of select="text()"/>
       </xsl:for-each>

     </xsl:for-each>

  </xsl:template>
</xsl:stylesheet>')).getclobval() as html from dual;

HTML
----

DUMMY

X

nb credit to Tom Kyte for the xslt from his blog. See also this other post for a slightly more advanced version.


Viability of surrogate key based from existing column as primary key I'm designing the schema for a database and have a question about primary key choice. There aren't any natural keys available in the tables we're looking at now although there's interest from my team in using more "readable" keys to see relations between tables a bit easier.

The current proposed solution is to have a primary key that is an existing column + a random number. For example: we have a PERSON table that has a a Name column. The PersonID is the primary key with it being the Name + some number. So the person named Alex has the PersonID Alex1.

We're looking at potentially several thousand entries. Will this hinder performance using a string as a primary key? Is there any reason to use or not to use a more "readable" key?

It's probably worth noting that most systems our team is used to working have no more than a small handful of tables where bouncing around and seeing relations directly isn't too difficult. This project we're mapping out know will have a dozen to potentially several dozen tables. Also, we use MySQL and often enough team members use PHPMyAdmin to enter data directly which I think is the main reason there's a push to have readable keys.

You could do this - but I wouldn't. Here's why:

I generally try to avoid using "human meaningful" values as keys - because humans may decide they want the value to be something else. Since your key is partly a human meaningful value, there will be a temptation to change it.

For instance, "Alex" decides they want to use their full name, "Alexos". Do you change the PK to "Alexos1", or leave it as "Alex1"? With cascading updates, it's not hard to update PKs - but, it can cause locking issues, if there are a lot of foreign keys tied to it.

Next, there's the "random number" you want to add to the name to create your key.

If it's truly random, you have to check for collisions, and set some upper limit (which could have to change, if you exhaust the possible values when tied to a particular name).
If it's not random, but tied to the underlying data in some way, you may have to calculate it (for instance, if the next "Alex" should be "Alex2").
If it's actually unique (for instance: if you have a auto-increment integer column, uid, that's actually unique, and you combine the name and uid columns to make name_id), it's easy to build (as long as the name column can't have digits at the end of it - if it can, then collisions are possible again), but:

you may start to lose the easy linkage - if you've got "Alex326", "Alex3156", and "Alex12581", you may get to a point where matching the number 3156 in both tables would be just about as easy as locating "Alex3156".
it's overkill, because uid is already a perfectly good key value.

The big pro for your team would be the ability to connect data rows in two different lists. If you're dealing with Excel, this can make sense; in a SQL database, it really doesn't. If you want to match the rows of two different tables, just run a query that JOINs them.

I mention a few potential "cons" above. Some of them tie back to more generic DB design concerns.

The issue of whether to change the key if the name value changes, and of creating a combined key out of name and a uid column - both entail denormalizing the data, in effect.
String-valued keys almost always consume more disk space than unique integer keys. This will affect every index on the table; they'll consume more space, too. And, the more space your data takes up, the more time it takes to for each query to run. (Admittedly, in practice, with a relatively small table, the size of your key may not cause a noticeable difference to your users; but at several thousand rows, the difference will be there).

I would be inclined to use an auto-increment integer ID in the table, and get the users used to pulling the data back via queries that join the relevant tables, instead of relying on keys that people can "eyeball" in separate table dumps. If necessary, create views for them to hide the joins.

This may make data entry via PHPMyAdmin a little harder, initially. However, users will adapt - for instance, copying and pasting the ID into a linked record, if necessary. Designing a database to make data entry tasks a smidgen easier isn't generally a great strategy - in most cases, the benefit you're looking for in having the data in a database in the first place is more a matter of using it once it's in your system, than just getting it entered.


Cannot restore database because of stuck rollback When I try to restore my database with

RESTORE DATABASE MyDatabase
FROM DISK = '\path\to\my\backup.bak'
WITH REPLACE, RECOVERY

It says

  Exclusive access could not be obtained because the database is in use.

After some googling I tried this instead:

ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE MyDatabase
FROM DISK = '\path\to\my\backup.bak'
WITH REPLACE, RECOVERY
GO

However, this seemed to take forever and when I eventually cancelled the command it told me that

  Nonqualified transactions are being rolled back. Estimated rollback completion: 0%.

The message repeated multiple times and the progress was always 0%.

I'm not a DBA --sadly my company does not employ any-- and I have no clue where to go from here.

If you can restart the SQL Server service, then:

Stop the SQL Server service
Delete the old data/log files for MyDatabase
Start the SQL Server service again
At that point, you can delete the (offline) database
Do your restore


postgresql fulltext slow I am trying PostgreSQL for the first time, but am finding the fulltext search very slow.

My database has a table with 5 columns and 5 million rows

CREATE TABLE table_test (
    id integer NOT NULL,
    titre character varying(210),
    annee integer,
    pays character varying(50),
    resume_article text
);
INSERT INTO table_test(titre, annee, pays, resume_article) VALUES
('Blanche-Neige et les Sept Nains', '1937', 'USA', 'L''action débute lorsqu''un jour, la Reine interrogeant son miroir, celui-ci lui répond que la plus belle du royaume est dorénavant la princesse Blanche-Neige'),
('Les Petits Mouchoirs', '2010', 'France', 'Max, riche propriétaire d''un hôtel-restaurant, et sa femme Véro invitent chaque année leurs amis dans leur maison au Cap Ferret pour célébrer l’anniversaire d''Antoine et le début des vacances.'),
('La Reine des neiges', '2013', 'USA', 'Le roi et la reine partent immédiatement voir les trolls dont le roi est capable de guérir Anna.'),
('Au service de Sa Majesté', 'UK', '1937', 'Un mauvais garçon new-yorkais, fuit au Royaume unis en empruntant l''identité de sa victime présumée, un Canadien.');

My tests

// 1st test
ALTER TABLE table_test ADD COLUMN fulltext tsvector NOT NULL DEFAULT '';
UPDATE table_test SET fulltext = (to_tsvector('french', COALESCE(titre, '')) || to_tsvector('french', COALESCE(resume_article, '')));
CREATE INDEX IF NOT EXISTS fulltext_all ON table_test USING GIN(fulltext);

SELECT * FROM table_test WHERE fulltext @@ to_tsquery('reine') AND pays='USA'
//Result in 12/15 seconds

// 2nd test
ALTER TABLE table_test ADD COLUMN fulltext tsvector NOT NULL DEFAULT '';
UPDATE table_test SET fulltext= to_tsvector('simple',"titre" || ' ' || "resume_article");
CREATE INDEX IF NOT EXISTS fulltext_all ON table_test USING GIN(fulltext);

SELECT * FROM table_test WHERE fulltext @@ to_tsquery('reine') AND pays='USA'
//Result in 15/20 seconds

SELECT * FROM table_test WHERE (titre ILIKE '%reine%' OR resume_article ILIKE '%reine%') AND pays='USA'
//Result in 5/6 seconds

And if the result is more than 1000 rows, I need to wait 2 or 3 minutes.

    CREATE TABLE table_test (
    id integer NOT NULL,
    titre character varying(210),
    annee integer,
    pays character varying(50),
    resume_article text
    tsv tsvector NOT NULL,
    CONSTRAINT table_test_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
CREATE INDEX idx_pays ON table_test USING btree(pays);
CREATE INDEX idx_tsv ON table_test USING gin(tsv);

EXPLAIN ANALYZE SELECT * FROM table_test WHERE tsv@@ to_tsquery('french', 'maison');

"Bitmap Heap Scan on table_test  (cost=3980.87..363013.72 rows=430047 width=212) (actual time=675.558..37781.252 rows=429066 loops=1)"
"  Recheck Cond: (tsv @@ '''maison'''::tsquery)"
"  Rows Removed by Index Recheck: 2172041"
"  Heap Blocks: exact=41111 lossy=79485"
"  ->  Bitmap Index Scan on idx_tsv  (cost=0.00..3873.36 rows=430047 width=0) (actual time=486.134..486.134 rows=429066 loops=1)"
"        Index Cond: (tsv @@ '''maison'''::tsquery)"
"Planning time: 0.506 ms"
"Execution time: 37848.042 ms"

When I try this query without EXPLAIN ANALYSE, it's 2 minutes to wait. Can anyone explain where my mistakes are?

How do you know it's the full text search that's slow? It is very likely that the slow down is caused more by AND pays='USA' You do not have an index on it and with 5 million records 10 seconds seems fair enough.

Create an index on the pays field. This will speed things up provided that the pays field has good  cardinality.


How to change only time while keeping the date same in timestamp type I have timestamp column used for scheduling calendar events. I would like to change only the time part while retaining the date part untouched.

How can I possibly do it? I'm on postgresql

Current:

2017-07-01 00:00:00

To:

2017-07-01 17:00:00

Add the desired INTERVAL

select date + interval '5 hour'

Have a look at: Date/Time Functions and Operators on Postgres documentation.

select a.MyDate + interval '5 hour' as Result_Date
from (select '2017-07-01 00:00:00'::date myDate) a

|result_date        |
|:------------------|
|2017-07-01 05:00:00|

dbfiddle here


Audit Plugin on Ubuntu: CONTROL [main] Failed global initialization: FileNotOpen: Failed to open "/path" : exit status 1 We use MongoDB 3.2.11 and try to enable Audit.

  New in version 2.6.

  MongoDB Enterprise includes an auditing capability for mongod and
  mongos instances. The auditing facility allows administrators and
  users to track system activity for deployments with multiple users and
  applications.

This is our startup configuration mongodb.conf

auditLog:
  destination: file
  filter: '{ atype: { $in: [ "createCollection", "dropCollection", "createDatabase",
    "createIndex", "renameCollection", "dropDatabase", "dropIndex", "createUser",
    "dropUser", "dropAllUsersFromDatabase", "updateUser", "grantRolesToUser", "revokeRolesFromUser",
    "createRole", "updateRole", "dropRole", "dropAllRolesFromDatabase", "grantRolesToRole",
    "revokeRolesFromRole", "grantPrivilegesToRole", "revokePrivilegesFromRole", "enableSharding",
    "shardCollection", "addShard", "removeShard", "shutdown", "applicationMessage",
    "authCheck" ] } }'
  format: BSON
  path: /opt/mongouser/store/mongodb-audit
net:
  port: 27000
operationProfiling: {}
processManagement:
  fork: "true"
replication:
  replSetName: rs-testname
security:
  authorization: enabled
  keyFile: /opt/mongouser/store/auto-mms/keyfile
storage:
  dbPath: /opt/mongouser/store/mongodb-data/rs-testname_1
  directoryPerDB: true
  engine: wiredTiger
  wiredTiger:
    engineConfig:
      directoryForIndexes: true
systemLog:
  destination: file
  path: /opt/mongouser/store/mongodb-data/rs-testname_1/mongodb.log

mongodb.log (the only entry). Here the strace output with startup.

2017-02-23T16:20:54.430+0000 F CONTROL  [main] Failed global initialization: FileNotOpen: Failed to open "/opt/mongouser/store/mongodb-audit/"
 : exit status 1

Output (stdout/stderr):

about to fork child process, waiting until server is ready for connections.
forked process: 56468
ERROR: child process failed, exited with error number 1

directory perms. The data dir and audit dir have the same permissions. The data dir works.

 # ls -ald mongodb-*
drwxrwxrwx 2 mongouser mongouser   17 Feb 23 16:19 mongodb-audit
drwxrwxrwx 3 mongouser mongouser   37 Dec  7 11:14 mongodb-data
drwxr-xr-x 6 mongouser mongouser 4096 Dec  7 11:24 auto-mms

I added maximum verbosity and there is still only one line in mongodb.log

systemLog:
  destination: file
  path: /path/mongodb.log
  verbosity: 5

about the Linux box. With a search engine you find similar errors only for Windows based Systems.

$ cat /etc/lsb-release
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=14.04
DISTRIB_CODENAME=trusty
DISTRIB_DESCRIPTION="Ubuntu 14.04.5 LTS"

any idea what's wrong?

You can give it a try using the following changes (without the trailing /):

path: /opt/mongouser/store/mongodb-audit

instead of:

path: /opt/mongouser/store/mongodb-audit/


Integrating MongoDB metrices with Cloud watch We are hosting mongodb in AWS environment and planning to create incident in Servicenow using AWS Cloud watch alarm facility

Has anyone integrated MongoDB Monitoring metrices with Cloud watch to create Incident ?

As i am completely new to AWS, i got stuck with the integration of mongodb metrices with cloud watch

Any link/lead would be highly appreciated.

Thanks,

As per @AndrewAslinger blog here The Amazon CloudWatch is a useful tool for monitoring your compute resources, services, and applications running in AWS. Amazon automatically publishes metrics for you when you use many different AWS Services.

As aws documentation here Amazon CloudWatch is basically a metrics repository. An AWS service—such as Amazon EC2—puts metrics into the repository, and you retrieve statistics based on those metrics. If you put your own custom metrics into the repository, you can retrieve statistics on these metrics as well.

You can configure alarm actions to stop, start, or terminate an Amazon EC2 instance when certain criteria are met. In addition, you can create alarms that initiate Amazon EC2 Auto Scaling and Amazon Simple Notification Service (Amazon SNS) actions on your behalf. For more information about creating CloudWatch alarms, see Alarms.

For further your ref here, here and here


Keyboard shortcut to execute statement at cursor in pgAdmin In MySQL Workbench, one can use the keyboard shortcut Ctrl + ENTER to execute the statement at cursor (delineated with semi colons).

Is there a similar shortcut in pgAdmin?

For SQL queries that span on only one line, I use a voice command in Dragon NaturallySpeaking:

Sub Main
    SendKeys "{End}"
    Wait(0.3)
    SendKeys "+{Home}"
    Wait(0.3)
    SendKeys "{F5}"
End Sub

but I don't have any solution for SQL queries that span although more than one line.

Since there seems to be no way to define such a keyboard shortcut, I filed a feature request: https://redmine.postgresql.org/issues/1439 , which got rejected:

Issue #1439 has been updated by Dave Page.
Status changed from New to Rejected
We follow the pgAdmin 3 behaviour in this regard - when you execute a query (or explain etc), if any text is selected, only that text is executed. If nothing is selected, the entire query (or set of queries) are executed.

Since amazingly with pgAdmin one has to login to view issues / feature request, here is a screenshot:


What data is duplicated when MySQL/MariaDB BLOB columns are copied? Let table_1 be created as follows:

CREATE TABLE table_1 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    some_blob BLOB
);

Let table_2 be created as follows:

CREATE TABLE table_2 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    some_blob BLOB
);

What I want to know is, after I run this table-copying query

INSERT INTO table_2 (id, some_blob) SELECT id, some_blob FROM table_1;

will the actual text within each some_blob field of the table_1 table be duplicated and stored on disk, or will the DB have only duplicated pointers to the disk locations containing the BLOB data?

One argument for why BLOB copying must involve the duplication of actual content reasons as follows:

  Duplication of BLOB content is necessary because changes to BLOB data in table_1 should not also take place in table_2. If only the disk pointers were duplicated then content changes in one table would be reflected in the other table, which violates the properties of a correct copy operation.

Now I present an alternative method that the DB could implement to satisfy this copy operation. This alternative shows the above argument is not necessarily true. The DB could only duplicate disk pointers during the execution of the given INSERT statement, then whenever an UPDATE occurs which seeks to modify the BLOB data in one of the tables, the DB would only then allocate more space on disk to store the new data which is part of the UPDATE query. A BLOB data segment then is only deleted when there no longer exists any disk pointers to it, and a particular BLOB data segment could potentially have many disk pointers pointing to it.

So which of these strategies does MySQL/MariaDB use when executing the given INSERT statement, or does it use a different strategy?

Why I am asking this question

Currently I am running a couple of UPDATE queries which are copying large amounts of BLOB data from one table to another in the same database (over 10 million rows of BLOB data). The queries have been running for a while. I am curious about whether the performance is so slow because some of the columns I am comparing are poorly indexed, because these queries are literally copying over the content instead of disk pointers, or perhaps because of both of these reasons.

I use an INSERT in the question's example because this simplifies the concept of database internals that I am trying to understand.

Separate copy.

Copying just a disk pointer would necessitate "reference counting", locks in places where there are none now, etc.

You could implement such by keeping the BLOBs in a separate table and somehow link to them.  You could use MD5 (or other digest) for de-dupping.  However, I suspect the benefit would be outweighed by the complexity and potential for bugs.

"for a long time" -- If there are many rows being copied in the same transaction, note that there is extra effort to prepare for ROLLBACK in the case of a crash.


Blank Login In sp_who2 Result There are some rows in the result of sp_who2 which their Login column value is null (see image below) while the login_name column of sys.dm_exec_sessions is not blank and this sometimes happens conversely, what is the reason of this behavior?

I suggest you stop using sp_who2, there are better ways to look at that information.  Even you do not want to use third party script you are better off joining following dmvs and get what you need.

sys.dm_exec_sessions
sys.dm_exec_requests
sys.dm_exec_sql_text
sys.dm_exec_query_plan
sys.dm_os_tasks

I use sp_whoisactive by Adam Machanic and will not try to write anything by myself.

To answer your question session id 428 is running in parallel meaning multiple threads and you can see it using below query.  Login name is showing only for exec_context_id =0 which is the parent thread.

SELECT er.session_id,
       er.status,
       er.command,
       er.blocking_session_id,
       er.wait_type,
       th.os_thread_id,
       os.cpu_id,
       ot.exec_context_id,
       ot.task_state,
       st.text
FROM   sys.dm_os_threads th
inner join sys.dm_os_tasks ot on th.worker_address = ot.worker_address
inner join sys.dm_exec_requests er on ot.session_id = er.session_id
inner join sys.dm_os_schedulers os on th.scheduler_address = os.scheduler_address
       CROSS apply sys.Dm_exec_sql_text(er.sql_handle) st
WHERE  er.session_id IN (SELECT session_id
                         FROM   sys.dm_os_tasks
                         GROUP  BY session_id
                         HAVING Count(exec_context_id) > 1
                         )


Tools to migrate from SQLite to PostgreSQL I need to continuously migrate from SQLite to PostgreSQL. By continuously, I mean that I will reimport the SQLite database into PostgreSQL every day. It would be nice if I could make changes to the tables in SQLite as I please without having to manually make any changes to the PostgreSQL database. I will run the migration from Python, but it could be any external tool, which can be controlled from command line. The tool needs to be available for Linux, and it would be nice if it also ran on Windows.

Have you looked into using ETL tools? They are external and I believe pentaho runs on both windows and linux and its free.
pentaho's site