How can I generate the code-containing comments like mysqldump? I'm needing to duplicate the mysqldump command in Python, and I am trying to figure out how automatically generate the code comments like mysqldump generates.

If you're wondering why I am doing such a stupid thing, it's because I am creating a utility that will make daily dumps of the production server for use in dev environments, but I must anonymize all of the fields that contain private data during the dump.  It will also change API keys to test accounts, change passwords for important user accounts, etc.  (Obviously this is not our main backup system, but something tailored for use by the devs, so that they can always use a safe snapshot of the production database to work with.)

I'm able to do a lot of this already, but I can't figure out how to automatically generate the correct "code comments" (e.g., "/*!40101 SET @saved_cs_client = @@character_set_client */;") that mysqldump includes before/after the CREATE TABLE statement and the INSERT statements.  Obviously I could hard code them, but that just feels wrong, and is potentially short-sighted in terms of portability of code and longevity of use.

Thanks for any ideas you can share!

Well you can review what mysqldump does on git and try working on it!! Though I'd not go this way...

Few things to suggest:

You want to hide column names

--> use --no-create-info to dump only data. (onlydata.sql)

--> Have standard table definitions ready and intact for dev server. (definition.sql)

--> Refreshing dev => load definition.sql and then onlydata.sql

Just to note that you can also restrict datadump with --where clauses and even limit that to dump only a few rows.

mysqldump --no-create-info --where " true limit 10000" | gzip > dump.sql.gz

.

You want to remove few values from the dump? Well writing tool for searching strings will again cause long time to parse!

--> You might consider setting up a special slave on which you make your changes and then do a full datadump.

--> You can prepare SQL commands to run after dump load finishes as follows:
update secrettable set secretcolumn='xxxxxx' where secretcolumn='reallysecretthing';


Ghost Cleanup settings I'm running a high transactional database (~175k transactions / Minute on average,
almost 9M records per hour added and removed)

Up till recently this hasn't been too much of an issue as we've been at ~7.5M records added and removed but with the latest influxes of data the ghost cleanup doesn't appear to be able to keep up with cleaning up the unused space on tables / indexes.

A few days ago we reached 53 GB of 'Unused Space' across 16 tables (mostly 2 of them) so as a result started looking into the ghost cleanup process to find it runs once every 5 seconds and runs over 10 pages.

My current solution is that early morning I am running three threads of the following command:

DECLARE @2hours datetime = dateadd(hour,2,getutcdate())

WHILE getutcdate() < @2hours
BEGIN
    DBCC FORCEGHOSTCLEANUP ('DBNAME') WITH  NO_INFOMSGS
END

to catch up with the backlog from the night before (when most of our deletes take place)

I'm wondering if there's any way to change the default settings from 5 seconds and 10 pages to say every second or run over 20 pages, is there any way of doing that or should I continue just spinning up multiple cleanup procs to clear out the data, or if there's any other actions that can assist with this

Re-indexing runs on most effected indexs atleast once a week (most are every other day)

SQL Server 2012 Enterprise SP3_CU8 (upgrading to CU9 tomorrow) on AlwaysOn High Availabilty Cluster also with replication (distribution on a separate server)

I'm wondering if there's any way to change the default settings from 5 seconds and 10 pages to say every second or run over 20 pages

No there is none. Atleast I dont know as of now :-)

  if there's any other actions that can assist with this

From Paul Randal's blog - One method people sometimes consider is to force ghost cleanup to clean everything by performing a table or index scan (thus queuing all the deleted records up for the ghost cleanup task).

select * from [your_problem_table] with (index = Index_that_has_large_Deletes)

Is there a possibility for you to partition the table and purge the old partition instead of doing deletes ? FYI .. SQL Server 2016 and up allows you to truncate individual partitions as well.

Also, you can (TEST and implement) - disable ghost clean-up (trace flag 661) and then rebuild index WITH ONLINE = ON option since you are using Enterprise edition.

If  you are using AlwaysON (with async) with replication, make sure to enable Trace flag 1448 - allows the replication log reader to move forward even if the asynchronous secondary replicas have not acknowledged the reception of a change.

Make sure to read Deletes that Split Pages and Forwarded Ghosts from Paul White to see if your tables have triggers or LOB columns that would slow down.


Error 2013 in MySQL Workbench when I perform any operation on a certain table when using mysql workbench I perform any operation on the table "my_table" I get an error:

Error Code: 2013 Lost connection to MySQL server during query

Previously I stopped the execution of a restore dump and the log showed the message:

"the table may be in an inconsistent state"

How can I solve this problem?

If you kill a restore mid flow, then it is very likely you will leave a table in an inconsistent state.

The first thing you should try is to rerun the restore and allow it to complete.


Does PostgreSQL do any kind of caching of query results on its own? Suppose I do this ten thousand times in quick succession:
SELECT * FROM table WHERE id = $1;

That is, not as a prepared statement which is executed, but as individual queries.
Will PG actually execute the query each and every time, or will it somehow keep track of the fact that it was recently run, and that nothing has changed in between, and instead of running it again, simply return the last result which it has kept around in RAM?
Or do I need to do this client-side with my own application code?
Many years ago, I heard of "memcached" and stuff like that, but could never figure out how to use it, so I never did. I don't use any kind of "abstraction layer" either which might do this on its own, so I'm purely wondering if PG does this internally and automatically as part of some general optimization/resource-saving mechanism.

It will execute it each time.  It will cache the data it needs to do the execution (assuming that is small enough to stay in cache), but not the results.  Who knows, maybe the results have even changed, what with other people being allowed to do inserts, updates, and deletions at the same time as you are looping over your 10,000 executions.


How to use OPENQUERY to properly execute a Stored Procedure that updates a linked server table? I'm having a hard time trying to figure this out. I'm using OPENQUERY to execute Stored Procedures on a linked server. I managed to find a way to do it, by using the following:

SELECT *
FROM   OPENQUERY(
[LINKEDSRV\SQLSRV],
'SET FMTONLY OFF; SET NOCOUNT ON; EXEC [Database_Name_Example].Data.usp_GetNames 5,''S''')
GO

The reason I use SET FMTONLY OFF and SET NOCOUNT ON is because when I tried the code above without them, this message appeared:

  Msg 7357, Level 16, State 1, Line 1 Cannot process the object "EXEC
  [Database_Name_Example].Data.usp_GetNames 5,'S'". The OLE DB provider
  "SQLNCLI" for linked server "LINKEDSRV\SQLSRV" indicates that either
  the object has no columns or the current user does not have
  permissions on that object.

Reading a lot online helped me find the answer. This blog for example: http://www.sommarskog.se/share_data.html#OPENQUERY

So far so good, until I stomped into a Stored Procedure that use SELECT and UPDATE some tables on the linked server. When I execute the OPENQUERY I get the results from the SELECT statement but I noticed that it does not update the table.

The Stored Procedure goes like this:

CREATE PROCEDURE Data.usp_GetNames (@year tinyint=NULL, @online char(1)=NULL)
if @online = 'S'
  BEGIN
    select nam_codig, nam_desc from Names
    where nam_status = 'P'
    order by nam_date

    UPDATE Names
    SET nam_status = 'S'
    where nam_status = 'P'
END

When I execute it directly in the linked server it updates the table, so the second time I execute it, there will be no more nam_status = 'P'

But if I execute it with OPENQUERY the results are the same, it doesn't UPDATE it just select the data.

What can I do to allow the permissions?

Thank you!!!

OpenRowset is primarily designed for retrieving data, though you can use it to insert and execute stored procedures. I would recommend that you switch to CLR function for executing the remote stored procedure. Heres a simple code you can compile to a .net assembly.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Server;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using System.Collections;
using System.Data.SqlTypes;
using System.ComponentModel;

namespace ETL
{
    public class SQLServer
    {

        [SqlFunction(Name = "ExecSQL", SystemDataAccess = SystemDataAccessKind.Read, DataAccess = DataAccessKind.Read)]
        public static SqlString ExecSQL(string server, string Database, string cmdSQL)
        {
            string rValue = "0";
            using (var conn = new SqlConnection(string.Format(@"Server={0};DataBase={1};Integrated Security=SSPI", server.ToString(), Database.ToString())))
            using (var command = new SqlCommand(cmdSQL.ToString(), conn)
            {
                CommandType = CommandType.StoredProcedure
            })
            {
                command.CommandTimeout = 800;
                conn.Open();
                command.ExecuteNonQuery();
                rValue = "1";
            }
            return rValue;
        }
    }
}

Good Luck :)


Stored procedure with recompile I have this stored procedure..when i told the developers that it is not recommended to use with Recompile option they replied "This was because this SP can be called with a number of different params and we wanted the optimizer to grab a new plan with each call (not ideal, but it was an attempt to get it to run more reliably faster each time vs. using an old cached plan)"

Is what they saying correct ?
Is there any way they can do that with no recompile

   create PROCEDURE [dbo].[VERIFIER_QUEUE]
   @cas_name varchar(20) = NULL,
   @instance_name varchar(50) = NULL,
   @verifier_id int = NULL,
   @applicant_type VARCHAR(20) = NULL

    WITH RECOMPILE
    AS

   BEGIN
SET NOCOUNT ON

DECLARE @cas_name_x varchar(20)
DECLARE @instance_name_x varchar(50)
DECLARE @verifier_id_x int
DECLARE @InstanceId INT
...............

Michael Green is right: the devs are trying to thwart parameter sniffing, which happens when SQL Server compiles a plan that is great for one set of parameter values, but horrible for others.

You'll want to use OPTION (RECOMPILE) on the statement(s) with issues, not WITH RECOMPILE on the procedure. And I wouldn't recommend the local variables "trick" - it just makes the code messier; better to use OPTIMIZE FOR UNKNOWN on modern versions if that's the method that works best in your scenario. (For a whole lot more on this topic, see this great post by Paul White.)

Also, if many parameters are optional (so the query has things like WHERE col = @param or @param IS NULL), this is what I call "the kitchen sink" - sometimes dynamic SQL can be a much more effective solution. You didn't show the rest of your code, only that you were already using the local variables trick, but it will essentially look like this:

DECLARE @sql NVARCHAR(MAX) = N'SELECT ... FROM ... WHERE 1 = 1';

IF @cas_name IS NOT NULL
  SET @sql += N' AND cas_name = @cas_name';

IF @instance_name IS NOT NULL
  SET @sql += N' AND instance_name = @instance_name';

IF @verified_id IS NOT NULL
  SET @sql += N' AND verifier_id = @verifier_id';

...

SET @sql = @sql + N' OPTION (RECOMPILE);';
PRINT @sql;

EXEC sys.sp_executesql @sql,
  N'@cas_name VARCHAR(20), @instance_name VARCHAR(50), @verifier_id INT, ...',
  @cas_name, @instance_name, @verifier_id, ...;

This approach of only adding clauses for parameters that are actually supplied protects you from caching plans based on different sets of parameters (for example, if I supply @FirstName on first execution, the seek plan on that column that gets cached isn't going to help when I ask for @LastName LIKE N'%s%'). The OPTION (RECOMPILE); at the end protects you from plans that can vary greatly based on the values of the same parameters from execution to execution (for example, WHERE name LIKE N'%s%' should yield a different plan shape than WHERE name LIKE N'Q%').

This typically works best with the server setting optimize for ad hoc workloads, which you can read about here and here. Essentially what this does is prevents your plan cache from filling up with all these slight plan variations, unless they are used more than once. (Yes, with OPTION (RECOMPILE), the point is moot; however, the server setting can't hurt for the rest of your ad hoc query workload, and I've never come across a downside to having it on.)

This is pretty safe from SQL injection, since you don't have to worry about concatenating user input into SQL strings (all parameters are strongly typed), but it can't hurt to read these topics on dynamic SQL:

Protecting Yourself From SQL Injection - Part 1
Protecting Yourself From SQL Injection - Part 2
The Curse and Blessings of Dynamic SQL (Erland Sommarskog)
Dynamic Search Conditions in T-SQL (Erland also)

I have videos about my solution to "the kitchen sink" here and here as well as a blog post about it.


How to display values excluding maximum value based on column that is duplicated I am facing a challenging issue where I need to print all fields from a specfic column based on a duplicate column that excludes the maximum value

My query plan:

select event_no,
       bs_id
from   bs_bank_mast
where  event_no in (select   event_no
                    from     bs_bank_mast
                    group by event_no
                    having   count(*) > 1)
order by event_no ASC

My output:

event_no       bs_id

1692163        40672
1692163        41974
1692163        40672
1692163        41974
1723264        67460
1723264        67499
1723264        68357
1723265        67929
1723265        67383
1723266        67735
1723266        67423
1723266        67969
1723266        68164
1723266        67501

event_no       bs_id

1692163        40672
1723264        67460
1723264        67499
1723265        67929
1723266        67735
1723266        67423
1723266        67969
1723266        68164

So basically everything but the last max value from bs_id is printed

The query I tried:

select event_no,
       bs_id
from   bs_bank_mast
where  event_no in (select   event_no
                    from     bs_bank_mast
                    group by event_no
                    having   count(*) > 1)
and    event_no < max(bs_id)

The error I get is:

  Bad use of aggregate in this context

Please will you provide the assistance for what I need.

You cannot use an aggregated on WHERE clause. Instead, you could join your table with your grouped result set and discard maximum value.

select t1.event_no,
       t1.bs_id
from   bs_bank_mast t1
join   (select   event_no, max(bs_id) max_bs_id
        from     bs_bank_mast
        group by event_no
        having   count(*) > 1) t2
on     t2.event_no = t1.event_no
and    t1.bs_id <> t2.max_bs_id;

event_no | bs_id
-------: | ----:
 1692163 | 40672
 1692163 | 40672
 1723264 | 67460
 1723264 | 67499
 1723265 | 67383
 1723266 | 67735
 1723266 | 67423
 1723266 | 67969
 1723266 | 67501

dbfiddle here


How move MySql table to another disk? According to this it is impossible to use alter table for moving tables. So if I understand correctlly - to move table it is neccessary to do:

Create new table
Copy all row from old one
drop old table
Rename new table created.

First of all - it these steps are correct?
Second - is there more beautiful way to this operation?

SHOW CREATE TABLE real;  -- to avoid typos
edit it change name to new and to add DATA_DIRECTORY= ... or whatever
perform the CREATE TABLE new ...
INSERT INTO new SELECT * FROM real; -- single, fast, step.  But not instantaneous
RENAME TABLE real TO old, new TO real;
DROP TABLE old;

Step 5 is the main added "elegance".  It is atomic and instantaneous; it won't interrupt the live system.  Also, it give you a chance to test the new real before DROP TABLE old.

If this is InnoDB, you must have innodb_file_per_table ON before step 3.  Was it ON when the old real was created?  If not, ibdata1 will not free up any space on the old disk.  Resolving this takes several more steps.

(My steps should work fine for a MyISAM table.)


Determining which isolation level is appropriate This is a homework question.

  For the following transactions state the isolation level that will
  maximize throughput without lowering the integrity of the database.
  Explain the answer.

  Change the course identified by coursed_id = ’CPSC1350’ from one
  department to a different one.

  The Courses table contains information about courses: their id, their name, the
  department that offers it, the id of its instructor, and the maximum
  number of students who can take it (max_size). Courses(coursed_id:
  string, cname: string, dept: string, instructor_id: string,
  max_size:integer) – Primary Key: coursed_id – Foreign Key:
  instructor_id references Instructors

  Assume that PostgreSQL is used.

I believe the transaction can be done using read committed because

dirty reads are not OK because there is an update going on, thus a read is involved
non-repeatable reads are OK because it is unlikely someone else is changing the value
phantoms are OK because there are no SELECT statements

Am I on the right track?

The question seems like a puzzle which appears very simple, but maybe not so simple ,or it just pretends to be complex... I'll try my best answering it as I understand it. I apologize if I misunderstood some obvious hints.

With PostgreSQL, there is no real Read uncommitted - you get Read committed. Quoting the documentation:

  In PostgreSQL, you can request any of the four standard transaction isolation levels, but internally only three distinct isolation levels are implemented, i.e. PostgreSQL's Read Uncommitted mode behaves like Read Committed. This is because it is the only sensible way to map the standard isolation levels to PostgreSQL's multiversion concurrency control architecture.

The question doesn't sound very clear to me because isolation levels matter when you have simultaneous queries, and no other queries mentioned, so don't blame me too much if I miss something.

If we put remaining isolation levels in the order "Read committed"->"Repeatable read"->"Serializable", the overhead grows accordingly. So we need to check them in the same order, and once we are satisfied, there is no need to check remaining levels (they will be fine, but with more overhead). As far as I understand, UPDATE Courses set dept = 'New department' coursed_id = ’CPSC1350’ does the required work. From Postgre documentation  that describes Read committed isolation level,

  UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows: they will only find target rows that were committed as of the command start time. However, such a target row might have already been updated (or deleted or locked) by another concurrent transaction by the time it is found. In this case, the would-be updater will wait for the first updating transaction to commit or roll back (if it is still in progress). If the first updater rolls back, then its effects are negated and the second updater can proceed with updating the originally found row. If the first updater commits, the second updater will ignore the row if the first updater deleted it, otherwise it will attempt to apply its operation to the updated version of the row. The search condition of the command (the WHERE clause) is re-evaluated to see if the updated version of the row still matches the search condition. If so, the second updater proceeds with its operation using the updated version of the row.

I'd say I'm satisfied with such behaviour in this case, so I'll go with READ COMMITTED unless there are other restrictions not mentioned in the question.


Postgres anonymous block execution of generated dynamic sql to drop databases I have backup databases I wish to drop. I can generate a list :

select 'drop database ' || datname || ' ;' from pg_database where datname like 'bak_%'

getting me

drop database bak_20180101 ;
drop database bak_20180102 ;

etc

but when I try

Do $$Begin execute 'drop database $1 ;' Using  'bak_20180404_2014_12' ; End$$ ;

that gets me syntax error at or near "$1"

and when I try

Do $$Begin execute 'drop database bak_20180404_2014_12' ; End$$ ;

that gets me DROP DATABASE cannot be executed from a function or multi-command string

Is there a simple way to drop a generated list of databases?
(and what was wrong with my Execute Using)

You can't pass identifiers as parameters, you need to create a dynamic string. The best way to do that is to use format()

Do $$
Begin
   execute format('drop database %I ', 'bak_20180404_2014_12');
End
$$ ;

You can combine that with a loop:

do
$$
declare
  l_rec record;
begin
  for l_rec in select datname from pg_database where datname like 'bak_%'
  loop
     execute format('drop database %I ', l_rec.datname);
  end loop;
end;
$$
;

Edit

I always forget that you can't drop a database like that. But in psql you can easily run the result of a query as a script:

postgres=> select 'drop database '||datname||';' from pg_database
postgres-> \gexec

Note the missing ; at the end of the select statement. When you terminate the query with \gexec instead of ; the query's result will be executed as a script.

Another option is to simply spool the output to a file, so that you can inspect the drop statements before running them:

postgres=> \t
Tuples only is on.
postgres=> \o drop.sql
postgres=> select 'drop database '||datname||';' from pg_database;
postgres=> \o
postgres=> \i drop.sql


Does column store index reduce data space ? How this is possible? I was testing for performance of one of my queries. I was trying different indexes. When I applied a column-store index on my table, the table size reduced by 70-80 %. How is this possible?

A columnstore arranges the data on disk differently to how a "normal" table does it. The column's values are split into segments of just over one million values. Each segment is compressed. Since a single column's values can show a lot of repitition (think "country code" or "product name") the compression ratio can be significant.

Read performance can improve from several factors. First, only the columns required in the query are read off disk. Second, compression means much less IO for a given number of values compared to rowstore. Third, aggregate functions can be performed in what's called "batch mode," which is optimised for CPU cache utilisation.

Compression is also available for rowstores. My experience is that CPU utilisation increases but IO drops, for a nett improvement in elapsed query time. This was for moderately large databases performing reporting & analytics.

Of course, your mileage may vary.


What disrupts the inherit logic order of autoincrement that leads to illogical ordering of ids in the absence of an ORDER clause? On occasion I come across a database table on a project where when no sorting is applied (ORDER clause) that the ids are not in their inherit logical order.

1
178
3
144
2

Under the presumption that no one went out of their way and started manually changing the id column values for any reason and that the id column is set to autoincrement what would lead to this scenario and should whatever that cause/those causes should be an issue to be addressed for security or data integrity purposes?

I'll just elaborate on @a_horse_with_no_name 's comment. A table (relation) is a set of rows (tuples). By definition, a set is unordered. Asking what the next row is without specifying what you mean by next (ORDER BY), is like asking what colour joy has (well when it comes to power tools everyone knows that it is green and black ;-).

Assume that no rows reside in memory and that another connection reads id=186 into memory. Now your query asks for:

select id from T limit 1;

What would you consider the most sensible thing for the DBMS to do, return 186 from memory, or read a bunch of rows from disk to return the lowest id?

What's the benefit of treat a table as unordered one might ask? The optimizer tries to execute the query as efficient as possible. It can re-arrange the query in a number of ways, as long as it does not violate the semantics of the query. If it would have to take the ordering into consideration, it would severely cripple the freedom to choose the most efficient plan for the query.


pg_dump of table and its transitive dependencies Sometimes I'd like to export just a single table and its dependencies, so I can do some experiments with it on a test database. Is there a way to do that, other than dumping the full db schema with pg_dump and any table dependencies manually?

For example let's say I have a table employees which has a single foreign key to companies.id and uses a few custom enums or other types. Let's also assume companies is self-contained, i.e. does not depend on anything else. So what I'd like to have is some way of dumping  employees, both schema and all (or a subset) of the data and automatically get the required type definitons, and the schema for company as well as all referencered entries.

Is there a way to do this easily with pg_dump or some other tool?

uses a few custom enums or other types.

No. That's because those enum types can be used in different tables and such. PostgreSQL doesn't have a dependency tracker to make this work. The only exception to this that I know is sequences, -t will recreate sequences.

That said, if you recreate the database once, you only ever have to run pg_dump -t employees -c, the -c flag will drop the table and recreate it. This will unfortunately not work with foreign key constraints pointing to employees


Is there a "soft" ORDER BY / GROUP BY? SQL-Fiddle

http://sqlfiddle.com/#!9/c82c87b/1

Table Definition

First of all, this is my table:

 CREATE TABLE `stackoverflow` (
`id` VARBINARY( 36 ) NOT NULL COMMENT 'GUID generated by PHP',
`time` TIME NOT NULL COMMENT 'Current time(stamp)',
`type` VARCHAR( 10 ) NOT NULL COMMENT 'start/stop',
`reference` VARBINARY( 36 ) NOT NULL COMMENT 'multiple starts/stops to one reference',
PRIMARY KEY ( `id` )
) ENGINE = MYISAM

Data

This is some example-data:

INSERT INTO `stackoverflow` (
`id` ,
`time` ,
`type` ,
`reference`
)
VALUES
('03bd8e91-b9aa-4d18-be47-9e9cce903cfd', '11:00:00', 'start', '76afe924-08aa-431b-904a-66290c50da6a'),
('ef10860a-7666-4ca0-95b6-79ef2d5b3f75', '11:01:00', 'start', 'fd064ef5-462f-489c-ae14-3cb766eb80c4'),
('9bc72e24-a0d4-43a3-86ab-973c331e2958', '11:02:00', 'stop', 'fd064ef5-462f-489c-ae14-3cb766eb80c4'),
('a245cda3-1196-4dba-832e-0474fd0eb0bf', '11:03:00', 'start', '05324e7b-a358-48bb-9779-08cf60038bb8'),
('488c67e6-c21d-4356-9578-49e857259345', '11:06:00', 'stop', '76afe924-08aa-431b-904a-66290c50da6a'),
('11c0e4ac-e7e9-418a-841f-650ced3e8343', '11:12:00', 'stop', '05324e7b-a358-48bb-9779-08cf60038bb8');

It is basically about timers - every timer (reference) has multiple starts and stops. Every entry is either a start or a stop.

Querying Data

To display these timers in HTML, I need to order the data like this:

SELECT * FROM stackoverflow ORDER BY reference DESC, time ASC;

The result looks like this:

  ef10860a-7666-4ca0-95b6-79ef2d5b3f75  11:01:00    start   fd064ef5-462f-489c-ae14-3cb766eb80c4
  9bc72e24-a0d4-43a3-86ab-973c331e2958  11:02:00    stop    fd064ef5-462f-489c-ae14-3cb766eb80c4
# 03bd8e91-b9aa-4d18-be47-9e9cce903cfd  11:00:00    start   76afe924-08aa-431b-904a-66290c50da6a
# 488c67e6-c21d-4356-9578-49e857259345  11:06:00    stop    76afe924-08aa-431b-904a-66290c50da6a
  a245cda3-1196-4dba-832e-0474fd0eb0bf  11:03:00    start   05324e7b-a358-48bb-9779-08cf60038bb8
  11c0e4ac-e7e9-418a-841f-650ced3e8343  11:12:00    stop    05324e7b-a358-48bb-9779-08cf60038bb8

Questions

How do I get the rows, marked with a # into first position?
I think ordering by reference is wrong, but how do I keep them together? Just ordering by time would produce wrong results. It would be great, if these pairs of references could stay together.
Do I need a virtual table or a sub-query?

Additional information

Meanwhile I fixed this  “‘SQL’ problem” in PHP with multiple separate SQL operations, as detailed below:

get all references

SELECT DISTINCT reference FROM stackoverflow;

for each reference

SELECT * FROM stackoverflow WHERE reference = :reference ORDER BY time;

for each row
-> generate HTML "timer"

Result:

# 03bd8e91-b9aa-4d18-be47-9e9cce903cfd  11:00:00    start   76afe924-08aa-431b-904a-66290c50da6a
# 488c67e6-c21d-4356-9578-49e857259345  11:06:00    stop    76afe924-08aa-431b-904a-66290c50da6a
  ef10860a-7666-4ca0-95b6-79ef2d5b3f75  11:01:00    start   fd064ef5-462f-489c-ae14-3cb766eb80c4
  9bc72e24-a0d4-43a3-86ab-973c331e2958  11:02:00    stop    fd064ef5-462f-489c-ae14-3cb766eb80c4
  a245cda3-1196-4dba-832e-0474fd0eb0bf  11:03:00    start   05324e7b-a358-48bb-9779-08cf60038bb8
  11c0e4ac-e7e9-418a-841f-650ced3e8343  11:12:00    stop    05324e7b-a358-48bb-9779-08cf60038bb8

The order is now as expected - it would still be great if there is a MySQL solution for this. :)

Note: The exact MySQL version is 5.1.61.

SELECT s.*
FROM stackoverflow s, ( SELECT reference, MIN(time) time
                        FROM stackoverflow
                        GROUP BY reference
                       ) o
WHERE s.reference = o.reference
ORDER BY o.time ASC, reference DESC, time ASC;

fiddle

Everything is simple. You want to sort groups by the datetime of the most aged record in the group. The subquery obtains this info, and it is used in main query by joining it to each record in a group. For to understand replace s.* with * in output and analyze the result.


Oracle XE 12c won't configure - getting the "port 5,500 in use" error message I have the infamous "port 5,500 in use" error message while trying to start/configure Oracle XE 12c.

I have searched for this problem and every solution I have found has been the same - make sure the hostname matches between /etc/hostname and /etc/hosts. I have done that, using grep -w to confirm the strings are the same, and they are, but the problem is not resolved.

/etc/hostname

iv4x.rdu.csb

/etc/hosts

127.0.0.1   iv4x.rdu.csb localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

The difference I see with the above is I didn't have a non-loopback IP address in /etc/hosts.  I attempted using those addresses reported by "ip a", but all of those failed as well, e.g.:

/etc/hosts

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.122.1   iv4x.rdu.csb

I should add that I have used netstat to search for anything using that port and I found nothing. I have also tried configuring Oracle to use different ports and received the same error message for any port that I tried.

I would appreciate any suggestions, thanks.

I found the solution with the assistance of our DBA team.

The problem was I using a fully qualified hostname in the /etc/hosts file. I had interpreted the solutions I found that cited "hostnames must match" to include the domain name as well.

If your hostname is xxxx.yyyy.zzzz, then you add just "xxxx" to /etc/hosts, per the instructions provided:

127.0.0.1    xxxx.yyyy.zzzz    anything-else   xxxx

In my case, I added just "iv4x" to the loopback line.


I think this is what you want:

SELECT
    i.item,
    q.query_dates,
    s.value,
    s.data_entry_date
FROM
    qdate AS q
  CROSS JOIN
    ( SELECT DISTINCT item
      FROM svalue
    ) AS i
  OUTER APPLY
    ( SELECT TOP (1) s.value, s.data_entry_date
      FROM svalue AS s
      WHERE s.item = i.item
        AND s.data_entry_date <= q.query_dates
      ORDER BY s.data_entry_date DESC
   ) AS s
ORDER BY
    item,
    query_dates ;

Now about efficiency, I doubt there is way to make this blazing fast with a big table. Even with the rather small tables (600 dates, 6K items), you still want a result set of 3.6M rows.

An index on (item, data_entry_date) INCLUDE (value) will probably help a lot but still, it's 3 million rows of results. Are you going to display them? Or save them in another table for further analysis?

If there is another table with items, it would also be probably better to replace the i derived table with that table.


When does SSIS SQL Server Configuration write to the configuration table? I am using SQL Server configuration in an SSIS pacakge. Other than when you create/edit the SQL Server configuration entry in the SSIS Package Configurations, are there other times that SSIS writes to the SQL Server table?

Reason I am asking is because what happens if I modify the connection manager the SQL Server Configuration is using by pointing it to a different database (same table name).

Will the SSIS Pacakge Configurations notice this right away or at run time or when the package is re-opened and then correctly create or update the SSIS Configuration table in the new Database that the connection manager is now pointing to? Or do I have to go into SSIS Pacakge Configurations and then back out so that it will refresh?

The only actions that will cause the values in your configuration table to be updated within SSIS will be when you are in the Configuration editor menu. Clicking OK there will cause the values to be written to the table.

Now, if you have an Execute SQL Task that has UPDATE statement in it, that would obviously change the values in the table but those would only become effective for the next run. Once a package is actually running, the configuration table could be dropped as it doesn't periodically poll the table to investigate values.

If you modify the value of a Connection Manager in your package, assuming that CM is also under Configuration, the next time the package attempts to validate itself at the package level, it'll reach out to the configuration resource (table, XML, etc) and overlay with the configured values. I think I remember that if you are just right clicking and executing a task within a package, it doesn't make the hop but don't quote me on that.


What's the difference between POINT(X,Y) and GeomFromText("POINT(X Y)")? I'd like to store some geometric positions in my MySQL database. For this I use the POINT datatype. Almost everywhere I read that the function GeomFromText should be used to insert data in the table.

However, I found out that POINT(X,Y) also works. I didn't find any description why GeomFromText should be used instead of POINT.

For example I have the following simple relation:

CREATE TABLE Site (
    SiteID      BIGINT UNSIGNED,
    Position    POINT
);

And I can insert values using the following two variants:

INSERT INTO Site (
    1,
    GeomFromText( 'POINT(48.19976 16.45572)' )
);

INSERT INTO Site (
    2,
    POINT(48.19976, 16.45572)
);

When I view the table (SELECT * FROM Site) I see the same binary blob for the location, and when I view the coordinates (SELECT *, AsText(Position) FROM Site) I also see the same values.

So why should GeomFromText be used? Are there any (known) performance differences between these two variants? How is this solved in other database systems than MySQL?

There are two different binary formats related to the MySQL spatial extensions, the "well-known binary" (WKB) format from the standards, and the MySQL internal GEOMETRY data type.

Prior to MySQL 5.1.35, functions like POINT() didn't return the MySQL internal data type; they returned WKB... so prior to then, you had to do this:

INSERT INTO t1 (pt_col) VALUES (GeomFromWKB(Point(1,2)));

But now, as in your example, this works:

INSERT INTO t1 (pt_col) VALUES(Point(1,2));

To the developers' credit, when they changed Point() and similar functions to (more sanely) return GEOMETRY objects, they allowed GeomFromWKB() and similar functions to actually accept either WKB or MySQL Geometry data as input even though the functions are intended to accept WKB as input.

The fact that the 1st method works (in spite of being technically wrong) on newer servers and the 2nd method doesn't work at all prior to MySQL 5.1.35 might explain why examples were written using the approach you've seen -- to avoid the issue entirely.  Otherwise... I've got nothing, here.

Concatenating and then parsing text seems intuitively slower and more error-prone than functions that accept proper variables as input, so I can't think of any reason to craft concatenated strings and use the text-based functions.

http://dev.mysql.com/doc/refman/5.1/en/creating-spatial-values.html#gis-wkb-functions

http://dev.mysql.com/doc/relnotes/mysql/5.1/en/news-5-1-35.html


Can't log into mysql shell When I try to log into the mysql shell with:

mysql -u root

then I get:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

How do I log onto the mysql shell?

I have been googling this left and right and trying all those proposed solutions on stackoverflow and elsewhere but nothing seems to work.

I have tried adding -p at the end. Same error.

I have tried going by this:
https://stackoverflow.com/questions/11657829/error-2002-hy000-cant-connect-to-local-mysql-server-through-socket-var-run
The first answer, I already have mysql-server installed.
The second answer doesn't work.
The third answer, i cannot implement step no. 3:

mysql -u root

and in the second step the process just hangs with message:

mysqld_safe Logging to syslog.
2017-09-26T16:30:40.563159Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2017-09-26T16:30:40.566955Z mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.

So the question is, how do I log onto the mysql shell?

Please, review my.cnf files (see oficial documentation for file locations https://dev.mysql.com/doc/refman/5.7/en/option-files.html).

Search under the [client] section if exist an entry called socket, i.e:

[client]
port=3306
socket=/tmp/mysql.sock

Replace it with the right path to mysql.sock and try again.
To get the mysql.sock, use ps -FU mysql -H | more and get the --socket value


Can PostgreSQL use multiple partial indexes per query? I have read that PostgreSQL can use multiple indexes in general, but in the specific case of a query that ranges across two indexes, will it use both?  If so, will they be loaded sequentially or together?

For example, if this query spans across two partial indexes by column_1, how will partial indexes be used if at all, and how is the index data loaded and discarded:

SELECT 1 FROM sample_table WHERE column_1 > 50 AND column_2 < 50000

Very short version: Yes, sometimes.

PostgreSQL can use bitmap index scans to combine multiple indexes.

A predicate like

WHERE a > 50 AND a < 50000

is a specialisation of the more general form:

wHERE a > 50 and b < 50000

for a = b.

PostgreSQL can use two indexes here, one for each part of the predicate, and then bitmap AND them. It doesn't matter if they happen to be on different ranges of the same column.

This is much less efficient than a single index, and may not be useful for some queries, but it's possible.

The bigger problem is that PostgreSQL's partial index support is not very bright. Irrespective of whether there's one or two indexes it might just not figure out that it can use the index at all.

Demonstration setup:

CREATE TABLE partial (x integer, y integer);
CREATE INDEX xs_above_50 ON partial(x) WHERE (x > 50);
CREATE INDEX xs_below_50000 ON partial(x) WHERE (x < 5000);
INSERT INTO partial(x,y) SELECT a, a FROM generate_series(1,100000) a;

OK, what will Pg prefer for given queries?

regress=> EXPLAIN SELECT y FROM partial WHERE x > 50 AND x < 50000;
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Index Scan using xs_above_50 on partial  (cost=0.29..1788.47 rows=50309 width=4)
   Index Cond: ((x > 50) AND (x < 50000))
(2 rows)

regress=> EXPLAIN SELECT y FROM partial WHERE x > 20 AND x < 50000;
                          QUERY PLAN
--------------------------------------------------------------
 Seq Scan on partial  (cost=0.00..1943.00 rows=50339 width=4)
   Filter: ((x > 20) AND (x < 50000))
(2 rows)

regress=> EXPLAIN SELECT y FROM partial WHERE x > 100 AND x < 50000;
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Index Scan using xs_above_50 on partial  (cost=0.29..1787.45 rows=50258 width=4)
   Index Cond: ((x > 100) AND (x < 50000))
(2 rows)

regress=> EXPLAIN SELECT y FROM partial WHERE x > 100 AND x < 20000;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Index Scan using xs_above_50 on partial  (cost=0.29..710.71 rows=19921 width=4)
   Index Cond: ((x > 100) AND (x < 20000))
(2 rows)

What if we try to force a bitmap index scan just to find out if Pg can use one, even if it's not worth doing for this particular simple case and small sample?

Try:

regress=> SET enable_seqscan  = off;
SET
regress=> SET enable_indexscan  = off;
SET
regress=> SET enable_indexonlyscan  = off;
SET
regress=> EXPLAIN SELECT y FROM partial WHERE x > 100 AND x < 20000;
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Bitmap Heap Scan on partial  (cost=424.48..1166.30 rows=19921 width=4)
   Recheck Cond: ((x > 100) AND (x < 20000))
   ->  Bitmap Index Scan on xs_above_50  (cost=0.00..419.50 rows=19921 width=0)
         Index Cond: ((x > 100) AND (x < 20000))
(4 rows)

Hm. Nope. Not combining the indexes there. It might be able to but simply not think it's worth scanning a second index, though.

What about a query that ORs two predicates instead?

regress=> EXPLAIN SELECT y FROM partial WHERE x > 100 OR x < 200;
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Bitmap Heap Scan on partial  (cost=1905.29..3848.29 rows=99908 width=4)
   Recheck Cond: ((x > 100) OR (x < 200))
   ->  BitmapOr  (cost=1905.29..1905.29 rows=100000 width=0)
         ->  Bitmap Index Scan on xs_above_50  (cost=0.00..1849.60 rows=99908 width=0)
               Index Cond: (x > 100)
         ->  Bitmap Index Scan on xs_below_50000  (cost=0.00..5.73 rows=193 width=0)
               Index Cond: (x < 200)
(7 rows)

Here PostgreSQL has ORed both indexes to find a match, then done a heap scan and recheck.

So yes, PostgreSQL can combine multiple partial indexes, at least for some queries, where it is useful to do so.

But if I RESET the planner overrides...

regress=> RESET enable_seqscan;
RESET
regress=> RESET enable_indexscan ;
RESET
regress=> RESET enable_indexonlyscan ;
RESET
regress=> EXPLAIN SELECT y FROM partial WHERE x > 100 OR x < 200;
                          QUERY PLAN
--------------------------------------------------------------
 Seq Scan on partial  (cost=0.00..1943.00 rows=99908 width=4)
   Filter: ((x > 100) OR (x < 200))
(2 rows)

... Pg will realise it's faster to just sequentially scan the table.


Why I am seeing NULL value in a table where the schema defines as NOT NULL field? So I am checking a friend's project. I got a table where every field is defined will NOT NULL constraint but I am still finding so many fields are NULL. I cannot understand what might be the reason to happen this kind of phenomenon?

Because as I have understanding if you pass NULL value in a field which is defined as NOT NULL than it does not let you submit that entry.

Thank you.

P.S. This is a very big, perfectly working pharmacy management system.

Are you sure those "empty" cells are set to NULL?

It could be that they have inserted empty strings to get past the Not Null restriction. Code like this will bypass a Not Null setting:

INSERT INTO <table> (Pat_LNAME, Pat FmName, Pat_email) values ('', ' ', '  ');

Notice, in the LNAME field, I pass an empty string '' (two single quote marks). In the second, I pass in a space, ' '. In the third I pass two spaces '  '. All three will get past a NOT NULL rule.

But this code WOULD fail:

INSERT INTO <table> (Pat_LNAME, Pat FmName, Pat_email) values (null, ' ', '  ');

Or any insert that didn't have a Pat_lname field included.

In SQL Server Management Studio, your view would show you Null columns like this:

Note the yellow background and the word NULL to show there's no data.

Remember, NULL does not equal empty string.


Difficulty removing files from filegroup I am trying to delete all files from my database from an unused filegroup. There are two files totaling aroun 1.5TB.
I can see that there is now approx 400mb still in these files but I cant see what it is or how to remove it.

select * from sys.allocation_units a --where data_space_id = 6
inner join sys.partitions b on a.container_id = b.hobt_id --type = 1
where data_space_id = 6

--gives me the following result set

SELECT * INTO #tmp_GridResults_1
FROM (
SELECT N'72057781944647680' AS [allocation_unit_id], N'1' AS [type], N'IN_ROW_DATA' AS [type_desc], N'72057777021059072' AS [container_id], N'6' AS [data_space_id], N'0' AS [total_pages], N'0' AS [used_pages], N'0' AS [data_pages], N'72057777021059072' AS [partition_id], N'741132535' AS [object_id], N'9' AS [index_id], N'1' AS [partition_number], N'72057777021059072' AS [hobt_id], N'0' AS [rows], N'0' AS [filestream_filegroup_id], N'0' AS [data_compression], N'NONE' AS [data_compression_desc] ) t;
SELECT [allocation_unit_id], [type], [type_desc], [container_id], [data_space_id], [total_pages], [used_pages], [data_pages], [partition_id], [object_id], [index_id], [partition_number], [hobt_id], [rows], [filestream_filegroup_id], [data_compression], [data_compression_desc]
FROM #tmp_GridResults_1

DROP TABLE #tmp_GridResults_1
GO

x

select * from sys.allocation_units a --where data_space_id = 6
inner join sys.partitions b on a.container_id = b.hobt_id --type = 1
inner join sys.objects c on c.object_id = b.object_id
where data_space_id = 6

--returns nothing

So it appears I have an object in the database with no rows taking up 400mb which has no record in sys.objects

I have tried adding a small file to the filegroup and performing an emptyfile operation on the files I want to remove and then performaing a dbcc shrink emptyfile however i get "page could not be moved because it was deallocated during shrink" The page refers to the object contained in allocation units but not in sys.objects

DBCC checkdb fails with "CREATE FILE encountered operating system error 665 (could not be completed sue to  a file system limitation) while attempting to open or create the physical file

Any ideas on how to progress, I would really like to remove these files or at least shrink them down to a much smaller size

Try running DBCC UPDATEUSAGE against the database as SQL may be reporting incorrect data on the space used in the database.

See: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-updateusage-transact-sql?view=sql-server-2017

If this alters any space usage metadata then you could retry the EMPTYFILE again, then remove the files if it is successful.


Encryption in PostgreSQL Does PostgreSQL support data encryption features at transactional and the table level of database? If yes, how?

Oracle provides this via database and network encryption. Oracle provides encryption at table, tablespace level. Does PostgreSQL also supports this?

This is a complex question and you will get a complex answer.

There is no reason you can't put a tablespace on an encrypted filesystem.  Additionally pgcrypto gives you a toolkit for doing data encryption but data encryption in the db is hard and there are too many ways keys can be disclosed to do this without review and assistance.  This isn't at the table level but rather at the value level, and pgcrypto gives you a plethora of options there.  Please see the pgcrypto documentation for details.


Why does a SELECT with no WHERE cause a full table scan, (or does it)? I have the following table with ~2000000 rows

CREATE TABLE TableA (idA integer PRIMARY KEY, idB TEXT UNIQUE)

From time to time, I need to process each rows, the order does not mater.

So I process 200 rows at a time, (around a transaction)

SELECT iaA, idB FROM TableA  ORDER BY ROWID asc LIMIT 200

but when I checked the query with EXPLAIN QUERY PLAN I noticed that it was doing a table scan.

0    0    0    SCAN TABLE TableA USING INTEGER PRIMARY KEY (~1000000 rows)

I am especially curious about the ~1000000 rows

It does not matter if I select 1 row or 1000 rows, I still do a table scan with ~1 million rows.

Is this not the most efficient way of querying the table for 'any' data?

As I said, the order does not matter, the data has to be processed.

Edit: If I run the query with no ORDER BY

SELECT * FROM TableA LIMIT 200

I get fairly similar results.

SCAN TABLE TableA USING COVERING INDEX sqlite_autoindex_TableA_1 (~1000000 rows)

EXPLAIN QUERY PLAN shows only an estimate, and the LIMIT clause is ignored when doing this estimation.
(And this number is so misleading that recent versions of SQLite do not show it.)

A plain SELECT * FROM TableA LIMIT x is the fastest way to get x rows from a table. SQLite computes result rows on demand, so it will not read the entire table but stop scanning after x rows.

Table scans are likely to be inefficient when you are searching for a single row or a few rows, or when you have a filter that greatly reduces the number of rows to be returned. But when the query is going to return all rows anyway, it is not possible to reduce the number of rows that actually need to be read from disk, so the best way to reduce disk I/O is to avoid touching anything but the table itself. (In this case, a covering index can replace a table.)


How do I perform daily, monthly SQL Server 2005 maintenance? Team,

I have SQL Server 2005 and I am new to DBA role and wondering if there is any T-SQL Script/Stored Procedure out there that they will help me accomplish the following on regular basis - Daily, Weekly, Monthly Yearly.

Hardware Utilization and Performance
(Memory, Available Disk Storage, and then CPU Utilization)
Database and Server Configuration
(Database Settings, Database Status, Database Back Up Status - Completion, Max
Server Memory Settings)
Database Maintenance
(SQL Server Logs, File Growth, Mirroring Monitor , Back up completion, Index
Fragmentation)

Thank You.

Hardware Utilization and Performance (Memory, Available Disk Storage, and then CPU Utilization)

If your site already has SCOM deployed, use SCOM, as is capable of monitoring SQL Server. Otherwise, pick something off the shelve, eg. Bosun

  Database Maintenance (SQL Server Logs, File Growth, Mirroring Monitor , Back up completion, Index Fragmentation)

Use Ola's Index Maintenance and Backup scripts.


"ERROR: column rel.relhasoids does not exist" with PostgreSQL 12 and PgAdmin 4.3 I get the following error when trying to select rows from a table:
ERROR:  column rel.relhasoids does not exist
LINE 1: SELECT rel.relhasoids AS has_oids

Configuration is:

Linux RHL8
NAME="Red Hat Enterprise Linux"
VERSION="8.2 (Ootpa)"
postgresql12-server-12.3
psql (12.3)
PgAdmin 4.3 en 4,5

The column relhasoids has been dropped from the system catalog pg_class in PostgreSQL v12, because the long deprecated WITH OIDS clause has been removed from CREATE TABLE.
Obviously you are using an old version of pgAdmin 4 that has not got the message yet.
Upgrade to a newer version of pgAdmin, and the error should vanish.


How to relate 2 user profile tables to a single login table? I have an Employee table , and another table named Student. Now I have a third table named the Login.
Employee has emp_id as PK , and other specific attributes for an employee.
Student has std_id as PK , and other specific attributes for a student.

The login table has user_id as PK , but the user_id should also be FK from both employee and student. Means that if there is a profile for an Employee or Student then there will be a record for them in the login table. But I am not able to link the user_id of the Login table to both Employee and Student at the same time in a one-to-one relationship.

Could you please help me how to that. I am using MySQL for this.

A Login maybe a Student or an Employee.

It sounds like you trying to define a FK relationship from Login to both Student and Employee, this is not correct.  You should be defining a FK relationship from Student to Login and Employee to Login

Edit for clarification:

If the PK emp_id for Employee and PK std_id for Student are the FK field then there can only be 1 for each given Login (user_id value).


How do I sum data over 3 month ranges? I have a report where I need to add totals for a column and group them by company selected. I have to do this in T-SQL for 4 ranges:
0-3 Months, 3-6 months, 6-12 months, >12 months
I am confused about how this would be implemented in T-SQL.
There is a balance column on which I have to total.
The problem I am not able to understand is summary over those date ranges. How can this be done in T-SQL?

The user should input the company choice and a date and based on it, I have to generate summary of balance column within the ranges specified, which were
0-3 Months, 3-6 months, 6-12 months, >12 months

I am thinking of it this way: I am thinking of it this way:
Make ranges of date, so : suppose @date is user selected date
then:

DECLARE @daterange1start DATE = @date
DECLARE @daterange1end DATE = (SELECT DATEADD(m,3,@daterange1start))
DECLARE @daterange2start DATE = @daterange1end
DECLARE @daterange2end DATE = (SELECT DATEADD(m,3,@daterange2start))
DECLARE @daterange3start DATE = @daterange2end
DECLARE @daterange3end DATE = (SELECT DATEADD(m,6,@daterange3start)) '

And then using case

SUM (CASE WHEN  dueDate BETWEEN @daterange1start AND @daterange1end THEN    Balance ELSE 0 END) AS '0-3 Months',
SUM (CASE WHEN dueDate BETWEEN @daterange2start AND @daterange2end THEN Balance ELSE 0 END) AS '3-6 Months',
SUM (CASE WHEN dueDate BETWEEN @daterange3start AND @daterange3end THEN Balance ELSE 0 END) AS '6-12 Months''

Am I thinking of it the right way in terms of a correct solution?

Thank you.

You could categorise your dueDate values and then use PIVOT like this:

WITH categorised AS (
  SELECT
    Balance,
    Range = CASE
      WHEN dueDate < DATEADD(MONTH,  3, @Date) THEN '0-3 Months'
      WHEN dueDate < DATEADD(MONTH,  6, @Date) THEN '3-6 Months'
      WHEN dueDate < DATEADD(MONTH, 12, @Date) THEN '6-12 Months'
      ELSE '>12 Months'
    END
  FROM
    dbo.YourTable
  WHERE
    Company = @Company
    AND dueDate >= @Date
)
SELECT
  [0-3 Months],
  [3-6 Months],
  [6-12 Months],
  [>12 Months]
FROM
  categorised
PIVOT
  (
    SUM(Balance)
    FOR Range IN ([0-3 Months], [3-6 Months], [6-12 Months], [>12 Months])
  ) AS p
;


Why is my Amazon RDS instance so slow? I currently run a Windows Server with SQL Server Express. I want to follow best practice and separate the application from the database, so I've created an RDS instance and exported my database.

But... it's painfully slow. I'm running a test query from my local machine that selects 12000 rows - it takes less than 1 second against my original setup but 16 seconds against RDS. Surely it can't be question of specification; my small RDS instance actually has a better spec. than my Windows Server.

What could be going on here?

Just a guess as there isn't enough information to really figure this out but it could be network latency. You're saying that local connection to local DB is fast but local to remote DB is slow. All other things being equal the only difference is the network connection between you (local) and the remote DB.

Generally AWS is pretty fast and has a stable network but you might notice latency issues for more chatty protocols. I'm not too familiar with the wire protocol for SQL Server but I know that Oracle for example is a relatively chatty protocol so it could be similar. As such you'll see much better performance when the network connection between the client and the server (the database) are on a low latency network with a minimal amount of hops.

Another issue could be if your local internet connection doesn't have much bandwidth (say a 1 Mb/s DSL) and you try streaming back a lot of rows from the remote database.

You should repeat your test from a Windows instance that's close to the RDS instance. I don't think you're allowed to remote into the RDS instance itself but you should be able to spin up a Windows instance that's in the same availability zone. That should be close enough to what it's like in a real world deployment.

Also, if you're planning on using the remote Amazon RDS instance as the database for an application that's not running on Amazon then you really need to test out the performance in advance. The intended use case is having your application running on EC2 so that you're on a low latency network with the DB. It would still work remotely but the performance will we wildly different.


Speed inserts in database with a table with +47 million data per each year, containing more than 10 years? I'm moving my database to mysql but there is one table that is pretty big (more than 47 million rows for each year) and it takes years to insert all the data.

I already tried everything I found in the forum, partitioning, index, fulltext...and is true it works better but its far away from efficient.

For giving more background: The data base is about hydrological data recorded during the past ten years. In my data base there are three tables: one keeping the objects to be saved, another to save the sensors of each object and the last one and the one which is causing the big problem is where I keep hourly all the information collected for each sensor (this table will have 47 million data for each year int the database)

I'm using .NET for the transactions.

Is there any way I can performed the inserts in less than 10 hours?

Thank you very much!

Simple this one.
Create a staging table with the same columns as your main table. No indexes on the staging table please as this will slow it down.
Insert your rows into the staging table via .NET as normal.
Then run:

BEGIN TRAN
INSERT INTO main_table (cols here)
    SELECT cols here from staging_table
TRUNCATE TABLE staging_table
COMMIT

You can still run that SQL inline via .net, or simply use a stored proc.
Either way, the insert into the main table will be in a SINGLE insert, rather than .nets single insert for each row (RBAR).
As the above method will insert data into the main table in one go, the indexes are updated once. We use this to great effect in our warehousing databases.

As your inserting a lot of data into the main_table, an alternative is to drop all the indexes on the main_table, insert your data, then re-create the indexes. This too is a very efficient (read fast) way.


What events still occur within a transaction that gets rolled back? Is it true that all data modifications are undone when the transaction they are a part of gets rolled back?

For example if a cursor that just executed a stored procedure 100 times that updated a table on each iteration, would all those updates get rolled back?

Would a DDL statement get rolled back?...such as DROP TABLE or CREATE VIEW?
What about DROP DATABASE?

I know certain statements still execute though like PRINT "MESSAGE".

I'm just trying to get an idea of what kinds of events still occur.

For statements that are allowed to execute within an explicit transaction (i.e. BEGIN TRAN, or heaven forbid IMPLICIT_TRANSACTIONS is ON), I am only aware of the following two that will not be affected by a ROLLBACK (though technically both are somewhat "cheating" in a sense):

DML statements against table variables (variable, even table variables, do not participate in transactions)

statements executed via a linked server and OPENQUERY (even if using a loop back server definition to connect to the current instance) if you have the linked server property of "remote proc transaction promotion" disabled (so that it doesn't attempt to enlist in the current transaction).
OPENQUERY (with that one option disabled) works because it makes a separate connection.  There are other, similar methods of making a separate connection within T-SQL. David Browne, in a comment, mentioned extended stored procedures. We can also add SQLCLR methods (no matter what T-SQL object type they are exposed as) to that list, but only if making a regular / external connection, and specifying "enlist=false;" in the connection string. We can probably also add the OLE Automation stored procedures (i.e. sp_OA*), but I am not 100% sure of these.

There might be one or two statements that can be executed within an explicit transaction but cannot be rolled back, and I think I maybe ran into that many years ago, but I don't recall what it is and could have been user error (some other user, of course ;-), so you would have to consult the documentation for specifics of each statement / operation and/or test as many as you can (sometimes the documentation is incorrect ... it happens).

Not sure if this counts or not as it isn't an explicit change, but is not something that goes back to what it was before the transaction started (because it doesn't even make sense that it could): Tibor Karaszi noted in a comment that "consuming an identity/sequence value" could qualify. I have tested and confirmed that sequences do not give back the requested values.
Similarly, another value that is database state, and not specifically user data, that is not affected by a rollback is the current / last-used timestamp value (this is per-database and is returned by the system variable @@DBTS; also confirmed via test)

Not sure if these count or not as they are neither an explicit change nor even the state of the database, but their values are not affected by a rollback: in a comment, David Browne mentioned SESSION_CONTEXT, which is a property of each session. Along those same lines, we can include the related / similar CONTEXT_INFO

if a cursor that just executed a stored procedure 100 times that updated a table on each iteration, would all those updates get rolled back?

Yes, this is the purpose of explicit transactions: to group statements together into an atomic unit of work.

Would a DDL statement get rolled back?...such as DROP TABLE or CREATE VIEW? What about DROP DATABASE?

Some (maybe most) yes. Some no. You probably should test. For example, here is some DDL that does roll-back:
USE [tempdb];

IF (OBJECT_ID(N'dbo.DropMe') IS NOT NULL)
BEGIN
  DROP TABLE dbo.DropMe;
END;

CREATE TABLE dbo.DropMe (col1 INT);

BEGIN TRAN;

DROP TABLE dbo.DropMe;

ROLLBACK TRAN;

SELECT * FROM dbo.[DropMe];

But, conversely, here is some DDL that cannot be executed within an explicit (or implicit) transaction:
CREATE DATABASE [DropMe];
BEGIN TRAN;
DROP DATABASE [DropMe];
/*
Msg 574, Level 16, State 0, Line XXXXX
DROP DATABASE statement cannot be used inside a user transaction.
*/
ROLLBACK;

So, just take the two examples above and rework them for other statements that you are either curious about or need a definitive answer on.


SQL View for Items not sold last six months I want to create a view where I would like to get a list of Items that are not sold in last 6 months. I tried couple of approaches but none is working correct.
Please guide me. Here are 2 approaches:
I cannot make temp tables as this is a view?

select distinct a.ItemID, a.TranDate Into #Itemslast6months
from SalesDataMart a
where TranDate >= Dateadd(Month, Datediff(Month, 0, DATEADD(m, -6, current_timestamp)), 0)

Select distinct m.ITMNO_0 into #ItemsNotSoldLast6Months
from ITEMMASTER  as m
Where not exists (select ItemID
                    from #Itemslast6months as B
                    where m.ITMNO_0 = B.ItemID)

Select n.ITMNO_0, s.[Description], max(s.TranDate) last_Transaction_Date
from #temsNotSoldLast6Months n
join SalesDataMart s on n.ITMNO_0 = s.ItemID
group by n.ITMNO_0, s.[Description]

2nd Approach:

 WITH ItemsSoldLast6Months (ItemID, TranDate)
      AS
      (
        select distinct a.ItemID, a.TranDate
        from SalesDataMart a
        where TranDate >= Dateadd(Month, Datediff(Month, 0, DATEADD(m, -6, current_timestamp)), 0)

      )

  WITH ItemsNotSoldLast6Months (ItemNO)
      AS
      (
      Select distinct m.ITMNO_0
from ITEMMASTER  as m
Where not exists (select ItemID
                    from ItemsSoldLast6Months as B
                    where m.ITMNO_0 = B.ItemID)

    )

Select n.ITMNO_0, s.[Description], max(s.TranDate) last_Transaction_Date
from #temsNotSoldLast6Months n
join SalesDataMart s on n.ITMNO_0 = s.ItemID
group by n.ITMNO_0, s.[Description]

Is this what you're looking for?

DECLARE @ItemMaster TABLE (ItemNo INT)
DECLARE @SalesDataMart TABLE (ItemId INT,TranDate DATE)

insert into @ItemMaster (ItemNO) values (1)
insert into @ItemMaster (ItemNO) values (2)

insert into @SalesDataMart (ItemId, TranDate) values (1,'2017-02-17')
insert into @SalesDataMart (ItemId, TranDate) values (2,'2016-02-17')
SELECT *
FROM @ItemMaster itmmst
WHERE NOT EXISTS (
        SELECT *
        FROM @SalesDataMart
        WHERE itemid = itmmst.itemno
            AND Trandate >= Dateadd(month, - 6, sysdatetime())
        )


How to create an index to be picked by query I have created multiple indexes one after another, but my query doesn't want to pick them :(

CREATE INDEX x
  ON gan.paid (project_id, country_iso_code, source, date(created_at));
CREATE INDEX x
  ON gan.paid (project_id, country_iso_code, text(source), date(created_at));
CREATE INDEX x
  ON gan.paid (project_id, text(country_iso_code), text(source), date(created_at));

Query Plan:

WindowAgg  (cost=46024.07..52771.64 rows=24252 width=63) (actual time=3038.104..3072.217 rows=99983 loops=1)
 ->  GroupAggregate  (cost=46024.07..52468.49 rows=24252 width=63) (actual time=2284.136..2982.067 rows=99983 loops=1)
    Group Key: key
    ->  Sort  (cost=46024.07..46535.85 rows=204709 width=63) (actual time=2284.109..2805.815 rows=198575 loops=1)
          Sort Key: key
          Sort Method: external merge  Disk: 13960kB
          ->  Seq Scan on paid gap  (cost=0.00..20265.45 rows=204709 width=63) (actual time=0.024..215.813 rows=198575 loops=1)
                Filter: ((project_id = 1) AND ((country_iso_code)::text = 'gb'::text) AND ((source)::text = 'website'::text) AND (created_at <= (now())::date) AND (created_at >= ((now())::date - 100)))
                Rows Removed by Filter: 214895
Planning time: 0.233 ms
Execution time: 3082.612 ms

Query:

SELECT sum(views) vies
     , sum(likes) likes
     , key
     , count(*) OVER() full_count
FROM gan.paid
WHERE project_id = 1
    AND country_iso_code = 'gb'
    AND source = 'website'
    AND created_at::date >= now()::date - 100
    AND created_at::date <= now()::date
GROUP BY key;

Table definition:

CREATE TABLE gan.paid (
    id               integer pk
    key              text
    project_id       integer
    source           varchar
    country_iso_code varchar
    visits           integer
    likes            integer
    created_at       date
);

How can I create an index that will speed up this query?

This is the part of the execution plan where you expect an index being used:

      ->  Seq Scan on paid gap  (cost=0.00..20265.45 rows=204709 width=63) (actual time=0.024..215.813 rows=198575 loops=1)
            Filter: ((project_id = 1) AND ((country_iso_code)::text = 'gb'::text) AND ((source)::text = 'website'::text) AND (created_at <= (now())::date) AND (created_at >= ((now())::date - 100)))
            Rows Removed by Filter: 214895

What we have to look at is the number of rows this node returns (198575) and the number it does not return (Rows Removed by Filter: 214895).  This means that the conditions in your WHERE clause are not really selective - they specify nearly half of the whole table.

This is a situation where indexes seldom help - they usually said to kick in when the percentage of the returned rows is estimated to be under 10% (others say less, like 5%).

You can try the same query with a more selective set of conditions (practically, setting a narrower date range).  In this case, an index may help - the first one you list above, for example.  Depending on the distribution of the data (the different values of project_id, source, and country_iso_code, and the proportion of all rows having the specific values), a different order of the columns might be preferable (for example, created_at::date as the first column).  If the other columns are not selective enough in any case (for example, all rows have a project ID of 1), it can happen that you need created_at::date only.

At the same time, your problem seems not to be the (lack of) indexes, but that the query is sorting too much data.  This node:

->  Sort  (cost=46024.07..46535.85 rows=204709 width=63) (actual time=2284.109..2805.815 rows=198575 loops=1)
      Sort Key: key
      Sort Method: external merge  Disk: 13960kB

shows that the work_mem setting is too low - the query needs at least 13960 kB more memory for this sort.  Do a

SET work_mem TO xMB;

where x is {your current setting} + 16 MB.  This will spare you about 2 seconds.

Notes:

casting varchar to text does not help with your indexes.
Is this query just a part of a bigger picture?  If it isn't, I'd think a bit more about what I am planning to do with the nearly 100k rows returned.  It might mean a (slight) re-desing of your logic.


Does ActualLogicalReads info in query execution plan reports reads generated by scalar udf? Sql Server 2016 and 2014 SP2 introduced new performance information on query execution plan nodes. MSDN: https://blogs.msdn.microsoft.com/sql_server_team/added-per-operator-level-performance-stats-for-query-processing/

One of introduced performance statistics is ActualLogicalReads. This is information which was available earlier with SET STATISTICS IO option. Unfortunately SET STATISTICS IO doesn't take into account reads generated by
user defined scalar functions (link).

I would like to know:

if SET STATISTICS IO and information included in query execution plan returns the same values?
if reads generated by user defined scalar functions are reported in logical reads presented in query execution plan?

Like Martin said, this is easy enough to test out. Let's use the Stack Overflow data dump as an example. If you want to download your own copy, head over here.

If we hit the Badges table with a scalar valued function with this definition:

CREATE FUNCTION dbo.ScalarFunction ( @uid INT )
RETURNS BIGINT
    WITH RETURNS NULL ON NULL INPUT,
         SCHEMABINDING
AS
    BEGIN
        DECLARE @BCount BIGINT;
        SELECT  @BCount = COUNT_BIG(*)
        FROM    dbo.Badges AS b
        WHERE   b.UserId = @uid
        GROUP BY b.UserId;
        RETURN @BCount;
    END;
GO

And then run a query that uses the scalar valued function:

SELECT TOP 1000 u.DisplayName,
       dbo.ScalarFunction(u.Id)
FROM   dbo.Users AS u;

The query plan looks like this.

And the STATS IO output looks like this:

Table 'Users'. Scan count 1, logical reads 76, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

And the plan XML looks the same:

 <RunTimeCountersPerThread Thread="0" ActualRows="1000" ActualRowsRead="1000" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="6" ActualCPUms="6" ActualScans="1" ActualLogicalReads="76" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />

So even though we know our function hits the Badges table and does a bunch of work, none of it registers either in STATS IO or in the query plan. Scalar valued functions (and to a large degree MSTVFs) continue to black out performance counters in modern versions of SQL Server.

To compare, if we just run the function body like this for a single UserId

SELECT  COUNT_BIG(*)
FROM    dbo.Badges AS b
WHERE   b.UserId = 22656
GROUP BY b.UserId;

The STATS IO output looks like this.

Table 'Badges'. Scan count 7, logical reads 107872, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The plan looks like this.

And the plan XML looks like this.

 <RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
 <RunTimeCountersPerThread Thread="1" ActualRows="2291" ActualRowsRead="2835393" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="93" ActualCPUms="93" ActualScans="1" ActualLogicalReads="17810" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
 <RunTimeCountersPerThread Thread="2" ActualRows="2193" ActualRowsRead="2839900" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="94" ActualCPUms="93" ActualScans="1" ActualLogicalReads="17810" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
 <RunTimeCountersPerThread Thread="3" ActualRows="2330" ActualRowsRead="2884732" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="94" ActualCPUms="94" ActualScans="1" ActualLogicalReads="18084" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
 <RunTimeCountersPerThread Thread="4" ActualRows="2134" ActualRowsRead="2835817" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="93" ActualCPUms="93" ActualScans="1" ActualLogicalReads="17810" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
 <RunTimeCountersPerThread Thread="5" ActualRows="2180" ActualRowsRead="2846565" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="93" ActualCPUms="93" ActualScans="1" ActualLogicalReads="17810" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
 <RunTimeCountersPerThread Thread="6" ActualRows="2472" ActualRowsRead="2964762" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="95" ActualCPUms="95" ActualScans="1" ActualLogicalReads="18546" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />

Since we know that scalar valued functions run once per row, we can safely assume that none of the IO used by the scalar function show up in the query plan for the calling query. This goes for CPU and other resources, as well.


Question on best practice - keep empty partitions at both ends of your table? I was reading ALTER PARTITION FUNCTION best practices which says -

  Always keep empty partitions at both ends of the partition range to guarantee that the partition split (before loading new data) and partition merge (after unloading old data) do not incur any data movement. Avoid splitting or merging populated partitions. This can be extremely inefficient, as this may cause as much as four times more log generation, and may also cause severe locking.

Questions :

Is it sufficient to ensure that there will be no data movement by ensuring that the new boundary corresponds to a future date or do I really need the empty partition ?
I think certain optimizations are done if the partition is empty, but
is it correct to say that the major cost is in the data movement
itself?
Is there anything new in SQL 2014 that could help out (we are moving from 2008 to 2014 .. yes I am aware that SQL Server 2019 is out ..but we will eventually get there) ?

I saw a lot on Managed Lock Priority (e.g. https://blogs.technet.microsoft.com/dataplatforminsider/2013/08/16/improved-application-availability-during-online-operations-in-sql-server-2014/); however, that seems to apply only to Table Partition Switch and Online Index Rebuild, but not to an ALTER PARTITITON FUNCTION.

Is it sufficient to ensure that there will be no data movement by ensuring that the new boundary corresponds to a future date or do I really need the empty partition ?

If your partition function is RANGE RIGHT, then yes.  A partition split moves rows in the existing partition that belong in the new partition.  The new partition is the partition that contains the new boundary value.  So in RANGE RIGHT if the new boundary value is after all the rows in the table, no rows need be moved.

Hopefully you choose RANGE RIGHT to begin with so you don't have to have boundary values like 2015-12-31 11:59:59:999.

Depending on the indexes, the head partition may need to be scanned to discover that no rows need to be moved.


How to most efficiently find out if a record has child records? I am writing a query that returns a single record from the parent table. I would like to also return in this query if it has any children. This is a one to many relationship.

parent:
 -parent_id
 -name

child:
-child_id
-name
-parent_id

My first instinct is to write the following query:

select name, (select count(child_id) from child c  where c.parent_id=p.parent_id) children
     from parent p
     where name like 'some name'

But I was wondering if there was a more efficient way to do this, since I don't actually care about the count, just whether or not it has children. Any pointers?

Don't forget that Postgres has a boolean datatype. The following is the most succinct way to express the query:

select
  parent_id,
  name,
  exists (select from child where parent_id = p.parent_id) as has_children
from parent p;

https://dbfiddle.uk/?rdbms=postgres_10&fiddle=86748ba18ba8c0f31f1b77a74230f67b


Is adding index on bit column significantly slow down inserts? I have a table with about 1 million to 5 million records. Small part of that records have one of bit column set to 'TRUE'. Need to fast find that records. I think that index can speed up searching on this column, but i afraid about INSERTs. Hence my question.

Database is working like sort of data warehouse, so there is many SELECTs and small (up to 10-20 per day) but quite large INSERTs (up 200 thousand records at once). I'm afraid about longer time of that imports to database.

An index on a bit for 1 million records is useless. The optimizer will never ever use it, you'll just pay for maintaining it. A much better alternative is to add this bit as the leftmost key on the clustered index.

But I will make a blind shot in the dark and guess that what you have is a queue pattern: records are dropped in the table with the bit set to 'TRUE' (ie. 'needsprocessing = true') and then a background process looks for these records, does some processing, and updates the bit to FALSE. This is a omnipresent pattern, also affectionately know as the 'performance disaster recipe pattern'. I would recommend dropping the records in to the table and dropping a notification (could be as simple as the newly inserted record ID), at the same time, into a queue. See Using Tables as Queues.


OCIEnvCreate failed to create environment handle after installing oracle_fdw I'm want use Postgresql 9.3 in CentOS 7 and add extention oracle_fdw.
but when use this extenstion i get error

OCIEnvCreate failed to create environment handle after installing oracle_fdw.

my roadmap:

install postgresql 9.3 from postgres rpm.
install oracle instant client 11.2g with rpm files from oracle site.
install  oracle-instant client11.2-basic & oracle-instantclient11.2-sqlplus  &
oracle-instantclient11.2-devel.

I use this question : OCIEnvCreate failed to create environment handle after installing oracle_fdw

my variables :

/etc/profile :
export PG_HOME=/usr/pgsql-9.3
export ORACLE_HOME=/usr/lib/oracle/11.2/client64
export TNS_ADMIN=$ORACLE_HOME/network/admin
export PATH=$ORACLE_HOME/bin:$PG_HOME/bin/:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME:$PG_HOME/lib:$LD_LIBRARY_PATH

tnsnames.ora :

I created /usr/lib/oracle/11.2/client64/network/admin/tnsnames.ora:

BEH =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.102.107)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
    )
  )

When I use the sqlplus command line, I can connect using sqlplus user/pass@BEH and can select from tables.

Using psql in my Postgres database:

CREATE EXTENSION oracle_fdw;
      resutlt : Query returned successfully.

CREATE SERVER beh FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver 'AMI_BEH');
   OR
   CREATE SERVER beh FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//172.18.102.107:1521/ORCL');
      result : Query returned successfully.

CREATE USER MAPPING FOR postgres SERVER beh OPTIONS (user 'ORACLE_USER',password 'ORACLE_PASS');
      result : Query returned successfully.

CREATE FOREIGN TABLE bl
   ("OLD_DYDJ" character varying(20) ,
    "DYDJ" character varying(20) ,
    "MC" character varying(128) )
   SERVER beh
   OPTIONS (table 'DYDJ_DY');
      result : Query returned successfully.

SELECT * FROM bl;
      result : error connecting to Oracle: OCIEnvCreate failed to create environment handle.

I checked that I can use sqlplus as the postgres user (su - postgres) and I can connect to oracle with the sqlplus command.

I found this link http://permalink.gmane.org/gmane.comp.db.postgresql.general/166914 , so I checked:

SELECT pg_backend_pid(); result : 3854
ps -p3854 -oppid=        result : 1902
cat /proc/1902/environ | xargs -0 -n1
   result :
      PG_GRANDPARENT_PID=1
      USER=postgres
      PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin
      PWD=/
      PGLOCALEDIR=/usr/pgsql-9.3/share/locale
      LANG=en_US.UTF-8
      PGSYSCONFDIR=/etc/sysconfig/pgsql
      SHLVL=1
      HOME=/var/lib/pgsql
      LOGNAME=postgres
      PGDATA=/var/lib/pgsql/9.3/data
      _=/usr/pgsql-9.3/bin/postgres

If you install from an rpm then it's installed as a service.

If you start postgresql from a custom script as user postgres then the process will catch the environment variables but if you start postgresql as a service it won't.

You should look for how to pass environment variables to systemd (or sysv) services.

I used this example for systemd services. CentOS 7 uses systemd.

In /etc/systemd/system create postgresql.service.d directory and place inside a file orafdw.conf with your environment variables and restart (not sure about only reload). Make sure the directory name match your postgresql service name.

Something like...

[Service]
Environment=ORACLE_HOME=/path/to/home
Environment=TNS_ADMIN=/path/to/tnsadmin
...
(All the variables you want)

Regards,

Walter


Moving SQL to a hosted server I'm in the process of moving a large (7GB-9GB) database to a hosted server. the old server uses SQL 2008 R2 and the new one is using SQL 2016. I'm trying to find the best way to reduce the downtime for this migration. I've looked into mirroring and replications and don't think either really fits the situation. I'm hoping I'm wrong and there's something I can use that I'm just not looking at properly.

Is it possible to set up a mirror or something in this manner and then once I drop the primary just disable it?

I have full access to both servers but may need to have firewall ports opened if needed.

For 7GB to 9GB of database, I would advise you to just use backup and restore strategy. This is the most simplest and effective way of migrating small database. I highly recommend you look at dbatools to help you automate your migration (it also migrate your objects like logins). The most important part of your migration is you practice your rollback and making sure application/users can still connect to your database after migration. Always test before you go live.


Change built-in default privileges in PostgreSQL? Introduction.

When I create a database,

postgres=# CREATE DATABASE test2 OWNER test2;

it is created with an empty privileges column:

   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 test2     | test2    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

Now, it is important to note that this is not the same situation as it having no privileges granted whatsoever. In the latter case, the column contains just {} (which by the way displays as empty, too, in postgres=# \l, AFAIR).

An empty field means that PostgreSQL uses built-in default privileges.
{} means that there are literally no privileges to the database.

Research.

In our case, these built-in defaults happen to be:

=Tc/test2  +  test2=CTc/test2

How do I know that? I issued:

postgres=# GRANT CONNECT ON DATABASE test2 TO test1;

... and that resulted in:

   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 test2     | test2    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/test2            +
           |          |          |             |             | test2=CTc/test2      +
           |          |          |             |             | test1=c/test2

... that is the implicit defaults plus test1=c/test2 granted explicitly.

Problem & question.

While I have no problem with =CTc granted to owner in these defaults, I'd like not to have =Tc granted to PUBLIC.

Sure, I can just REVOKE ALL [...] FROM PUBLIC after creation, but is there a way to automatize it?

Afterthought.

Really, why aren't these defaults as I want them by default? Is it a popular practice to run one PostgreSQL server for one app, not just one database on the server per app, even if it is a small one?

E.g. default installation of phpPgAdmin "crashes" when it stumbles upon a database it has no connect privs, I had to modify the source a bit. And despite numerous bug reports, it's been doing so for at least six months now (I can't remember any date of the reports, but one of them stated that six months had passed).

The CONNECT privilege for PUBLIC looks somewhat special:

\l
template1   | postgres | UTF8     | Hungarian, Hungary | Hungarian, Hungary | postgres=CTc/postgres

That is, user test (which is the member of PUBLIC only) cannot connect to template1:

postgres=# \c template1 test
Password for user test:
FATAL:  permission denied for database "template1"
DETAIL:  User does not have CONNECT privilege.
Previous connection kept

Now let's create a database on top of this:

CREATE DATABASE test3 TEMPLATE template1;

And connect to it with test:

postgres=# \c test3 test
Password for user test:
You are now connected to database "test3" as user "test".
test3=> \c postgres postgres

Just a bit of context:

postgres=# SELECT version();
                           version
-------------------------------------------------------------
 PostgreSQL 9.1.4, compiled by Visual C++ build 1500, 32-bit
(1 row)

With this in mind and the fact that ALTER DEFAULT PRIVILEGES does not provide means the change the defaults on the database level, I would say there is no way to do this automatically.  If you have to create lots of databases, probably a setup script would come handy.


Warnings: Why are statements with limit clause unsafe even if there isn't a limit? I'm a bit irritated because of some warnings in mysql errorlog.
Example 1:

[Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.
The statement is unsafe because it uses a LIMIT clause.
This is unsafe because the set of rows included cannot be predicted.
Statement: UPDATE `table` SET `status` = '1' WHERE `ID` = '15800' LIMIT 1

My colleague wrote these statements and I always wondered about the purpose of limiting a statement while using explicit ID. Nonetheless, this warning also occurs by not using LIMIT.
Example 2:

[Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.
The statement is unsafe because it uses a LIMIT clause.
This is unsafe because the set of rows included cannot be predicted.
Statement: DELETE FROM table WHERE id = 426888

What could be wrong in this case?

You have LIMIT without ORDER BY. Therefore, it is meaningless.

Sets do not have implied orders without ORDER BY. The error message tells you this:

  This is unsafe because the set of rows included cannot be predicted

Does your DELETE have a trigger or such with a LIMIT?


Index for column with two possible value I use PostgreSQL 9.4.

I have a database where a table have index in column which can contains  two possible value only(gender: male/female). When I execute a query with condition on this column index was not use.
It's bad idea use index in such column?

If index useless in this case, will I have update for this index when add new record. In other words: Will I have increase insert speed if I remove this index?

SQL for index:

CREATE INDEX index_participations_on_gender
ON participations
USING btree
(gender COLLATE pg_catalog."default");

My query:

SELECT
  participations.last_name,
  participations.first_name,
  participations.birthday,
  participations."number",
  participations.chip_id,
  participations.gender
FROM
  public.participations
WHERE
  participations.gender = 'male';

Rows in the table: 64982

Thanks.

The optimizer evaluates the possible gains of using indexes vs doing a full scan (filtering unwanted rows on the fly). As the ratio of filtered and total rows gets closer to 1 the benefit of using index decreases. The exact tipping point is dependent on the actual data, query, etc. so it's hard to say an exact number when it becomes useless. Generally speaking a 50/50 split is not sufficient for a B+TREE index to work efficiently and most cases full scan will be preferred over using indexes. (Unless it can be used for index-only scans)

The index is still getting updated regardless of its usefulness in queries.


Which BI solutions cooperate with Postgres database and aren't too expensive? I'm looking for solution letting me and my team track changes in Postgres database with daily updates. Data doesn't have to be updated instantly, once a day is enough. We use Excel/Google Sheet reports based on CSV files generated from Postgres at this moment and we're looking for more handy solution.

If you are comfortable with excel you could import the data directly with an odbc connection.

If you wanted olap capability you can do that with a tabular model within excel.

In general most BI solutions will work with any relational database via an OLE or ODBC connection. If the product doesnt have a native adapter you could use a 3rd party ETL tool to move the raw data to a db that is supported.


Active Expensive Queries and Recent Expensive Queries Meaning I am trying to understand the active expensive queries and recent expensive queries in Activity Monitor. What is expensive means here? Does it means expensive in term of cpu, block, memory, reads?

It depends on what column you sort by. To dig deeper, I suggest you read the source code that these are on. I blogged about AM a while ago, here is a part from that blog post:
The “Recent Expensive Queries” pane:
This shows the most expensive queries, based on what column you sort on, executed since the last snapshot. If you have, say, a 10 second snapshot interval, you will only see the queries executed during these 10 seconds. AM uses a procedure named #am_get_querystats to collect the information. There are a few things going on inside this procedure, but at the most basic level, it uses sys.dm_exec_query_stats and sys.dm_exec_requests to get queries from cache and currently executing queries. It then does some processing and store the result in temp tables so we later can sort on different columns depending on what metric we are interested in. I suggest that you spend some time with the source code if you want to dig deeper.
The “Active Expensive Queries” pane:
This is very straight forward. It executes a query which uses sys.dm_exec_requests joined to a few other DMVs.
Here's a link to my blog post: http://sqlblog.karaszi.com/explaining-activity-monitor/


Is it possible to trace the password the application is attempting to use to connect? I have an application attempting to connect to SQL Server. The application connects using a SQL Server account. The password for this account was accidentally changed. Now the application is attempting to connect to SQL Server using the wrong password.

Login failed for user ''. Reason: Password did not match that for the login provided. [CLIENT: ]

Ideally, we can just change the account/password the application is using to connect. If this were not an option, is it possible to trace the password the application is attempting to use to connect?

The short answer is you can't see the password being used to connect.  That being said, you can restore a backup copy of the master database to a test instance, get the hashed password, and then apply it with the HASHED option of ALTER LOGIN.

The password hash can be retrieved from the restored master database with:

SELECT LOGINPROPERTY('your-sql-login', 'PasswordHash');

The password can then be applied to the other instance, substituting the value from the script above:

ALTER LOGIN your-sql-login
WITH PASSWORD = 0x01000CF35567C60BFB41EBDE4CF700A985A13D773D6B45B90900 HASHED;


SQL Server Always On or Mirroring & Log Shipping We have two data centers with a 2 ms ping time between them connected by a site to site VPN. In the primary data center we have 2 servers, in the secondary data center we have a single database server. All server are identical in spec and configuration running Windows 2012 R2 and we have two SQL Server 2016 Enterprise licences. One for the main site + fail over and one for the secondary site.

The question is what technology should we use? always on availability groups with synchronous replication between the two nodes in the main data center and asynchronous replication to the secondary data center. The other option is to use mirroring in the main data center and then log shipping to the secondary data center. The only caveat is that we need to use transparent data encryption (TDE) hence the enterprise licences.

Our ops team is currently 2 people and growing but no fully assigned DBA as yet, I know how to setup both technologies but have only used mirroring and log shipping in production.

What would people recommend, the always on seems great but I have read some articles saying you need a lot of DBA resource to look after it where mirroring and log shipping is a lot easier.

If you were referring to Database Mirroring when you say mirroring (as opposed to SAN mirroring), then don't. It has been deprecated since 2012 now so it is a potential candidate for complete removal in the future releases.

Go with Always On Availability Groups. The difference in DBA overhead should be mostly the same. What is different, IMHO, is the learning curve. Log shipping is dead simple to learn, implement and operate. That's largely because it was originally created by MSIT DBAs (decades ago) as a simple, flexible DR solution which was subsequently productized. DB Mirroring applies the same concepts with a few powerful enhancements (e.g. shipping blocks instead of backups gives a sizeable perf boost).

Always On AG extends these concepts but added dependencies that users now need to learn to use and manage (e.g. WSFC). It's not rocket science but may take some effort to pickup if you're not already familiar with them. However, once you figure it out, the benefits are pretty significant (grouping resources, readable secondaries, much higher transfer perf are among the top ones).


Do I need to have enterprise server in order to have multiple data sources in SSRS I am currently running a single server setup of SSRS on MSSQL Server 2008 R2 Standard Edition and would like to know if I can have multiple data sources in this type of setup.

The reason I ask this is that I currently have a different SSRS setup (single server) where the primary reporting server sits in one state (physical, as in cross country) it then pulls data from several different data sources, some local, some sitting in a completely different state, it then renders the data and pushes it all to the client locations which are in a completely different state then the SSRS or SQL server data sources (Think a very large triangle with SSRS at one corner, SQL at another, and Clients at the third).

Our goal is to move to an Enterprise setup eventually, but as a stop gap I would like to move the SSRS and all SQL connections to the same physical data center so as to reduce bottlenecks. My problem is that I have been told from local sources that SSRS needs to have an Enterprise edition version of SQL Server in order to maintain multiple Data Sources. As it is not possible (nor feasible) to have all data sitting on one database (XD), this would require a further license upgrade on this server which is likely not to be approved due to a further GDW project on the horizon.

Cheers

There is no specific requirement for Enterprise Edition, though Express will not work either, which leaves Standard as your realistic option.

See Features Supported by the Editions of SQL Server 2008 R2.

Express Edition does have limitations:

Allowed data source SQL Server edition is SQL Server Express only.

The is no Remote and non-relational data source support.

Web and Workgroup have similar limitations.

You can see from that list there is no specific limitation against the number of Data Sources.