Why does sql server perform better after restart We had a performance issue that we solved by restarting the sql server service.
That's obviously not the way we want to continue handling our performance issues.

When a sql server performs better after restart the probable causes to me are

wrong cached query plans
memory pressure

To rule out wrong query plans being cached query plans we first dropped the query cache with

DBCC FREEPROCCACHE;

but the problem persisted.

I am still not sure about memory pressure. Page life expectancy goes down during the bad performance period to 50s. But cache hit ratio is 100%.

Are the numbers suggesting memory pressure here or are there other possible reasons?

I put a beautiful red line where we restarted the sql server service.

At the time of the issue we did not have more requests than usual.

If you want to know why it's better after you restart, you need to track what was running when all those lines got squiggly.

When you clear the plan cache, it doesn't help queries currently executing, and it doesn't guarantee that the next plan you cache will be good. I'd advise against doing that until you know exactly what's going on.

Take a look at the part of your monitoring tool that tells you which queries were running when you had a problem. If your monitoring tool doesn't tell you that, it's time to find a new one.

Next time it's happening, use a script like sp_WhoIsActive to catch currently executing queries.


set postgres instance as readonly with no master I have a pg 10 instance that I want to behave as a read only/replica/standby instance.

There is no master, only that read only database and I want the query select pg_is_in_recovery(); to return true to satisfy a monitoring check.

I have tried add a recovery.conf file in the data dir with standby_mode = 'on' as content then SELECT pg_reload_conf(); but I still get false to select pg_is_in_recovery();

office-accounting=# SELECT pg_read_file('recovery.conf');
    pg_read_file
---------------------
 standby_mode = 'on'+

(1 row)

office-accounting=# select pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 f
(1 row)

You probably haven't set up replication properly. The recovery.conf file needs something like this:
standby_mode = 'on'
primary_conninfo = 'host=127.0.0.1 port=5888 user=replicator password=xxx'
trigger_file = 'postgresql_xxx_12345.trigger'

The port should/can be a port that isn't used. The cluster will try to connect there - and fail but it will come online and allow read-only connections as if it is a slave. Which it is actually, with its imaginary master being down for the moment and indefinitely.
A side effect that you probably want to avoid - disable logging perhaps? - is that the log will be full of errors, of the streaming replication connect failures:

2017-12-05 22:19:08 UTC [18096-1] FATAL:  could not connect to the
primary server: could not connect to server: Connection refused       Is
the server running on host "127.0.0.1" and accepting      TCP/IP
connections on port 5888?
2017-12-05 22:19:13 UTC [18099-1] FATAL:
could not connect to the primary server: could not connect to server:
Connection refused        Is the server running on host "127.0.0.1" and
accepting         TCP/IP connections on port 5888?
2017-12-05 22:19:18 UTC
[18103-1] FATAL:  could not connect to the primary server: could not
connect to server: Connection refused         Is the server running on host
"127.0.0.1" and accepting         TCP/IP connections on port 5888?


How to tell what user has synced I have set up merge replication web sync to sync a publication from a SQL Server 2012 database.

I am trying to make some parameterised filters using SUSER_SNAME() or HOST_NAME().

When I do a sync is it possible to find somewhere what the values of SUSER_SNAME() and HOST_NAME() actually were for that sync? E.g. in the system tables.

I will need to experiment with security to work out what settings will affect these values, but I will need to find out what these values actually are to verify my settings.

Not really anything obvious.

The easiest would be if you have a backup you could restore to view it.

Otherwise you'll properly try - either yourself, daunting, or via commercial tools to find a tool that lets you explore the transaction log for the given changes.
For example something like: http://www.apexsql.com/sql_tools_log.aspx

I've not tried out such tools myself so I cannot vouch for them.
I usually get by either by having backups to search, or in some cases, I've set up change tables to log changes to critical data.


Determine if a resultset exists quickly I want to determine if a resultset exists very quickly. At the moment, I'm doing a count - this is taking roughly 55ms, which is unfeasable.The table has ~100k records - I don't care if it has 2, 5, 100k rows that fit a query; I care if it has 0 or 1. Maybe 2 in certain situations.

Is there a way to do this? Would limiting a count using ROWCOUNT (so it only counts about the first 2 rows it finds) speed up the count at all?

If you need just to check existence I'd go with
SELECT 1 FROM DUAL WHERE EXISTS (SELECT NULL FROM [your query]) ...

For "top-n" queries there are quite a few alternatives - https://oracle-base.com/articles/misc/top-n-queries . Note, some of them may have side effects ; for instance, where rownum < :value can change optimizer mode to FIRST_ROWS.


generate new column from another table column, treating nulls are unique I have a table that looks like the following:

    |device_id | login_id |
    -----------------------
    |    1     |    NULL  |
    |    2     |     A    |
    |    3     |     A    |
    |    4     |    NULL  |
    |    5     |     B    |
    |    5     |     C    |
    -----------------------

I want to generate a new column (person_id) like the following. The column essentially generates a (person) unique identifier. Every new NULL value gets its own unique_id and every distinct non-null login_id gets its own unique person_id.

    |device_id | login_id | person_id |
    -----------------------------------
    |    1     |    NULL  |     1     |
    |    2     |     A    |     2     |
    |    3     |     A    |     2     |
    |    4     |    NULL  |     3     |
    |    5     |     B    |     4     |
    |    5     |     C    |     5     |
    -----------------------------------

Is there a way to do this in pure PostgreSQL without going through a series of queries and temp tables? I'm using PostgreSQL 9.4.  Any help is much appreciated.

Adding the column:

alter table device_login
  add column person_id int ;

The query is a bit complicated, due to the nulls. First group the not null logins per login and keep each null seprately

with a as
( select min(device_id) as device_id, login_id
  from device_login
  where login_id is not null
  group by login_id
union all
  select device_id, login_id
  from device_login
  where login_id is null
)

then use dense_rank() to assign separate numbers to each group

, b as
( select dl.device_id, a.login_id,
         dense_rank() over (order by a.device_id, a.login_id) as rnk
  from a join device_login as dl
      on a.login_id = dl.login_id
      or a.login_id is null and a.device_id = dl.device_id
)

and finally update the table:

update device_login as dl
set person_id = b.rnk
from b
where b.login_id = dl.login_id
   or b.login_id is null and b.device_id = dl.device_id ;

Tested at SQLfiddle.


psycopg2 connect error: cannot pass more than 100 arguments to a function? I am trying to pass json object as a parameter to my stored procedure. I tried many things and solved couple of error atlast I am stuck with the error :
Error :
psycopg2 connect error: cannot pass more than 100 arguments to a function
LINE 1: SELECT * FROM insert_usr_details_in_all_tables('{','"','N','...
i tried json.dumps, json.loads etc but unfortunately nothing is working for me if i am executing the postgresql from scripts with same input it works fine. below is my stored procedure..

CREATE OR REPLACE PROCEDURE public.insert_usr_details_in_all_tables(
    jsonobject text)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
    skill_s  Text[] := '{}';
    selectedSkill TEXT;
    edu TEXT;
    email TEXT;
    phone TEXT;
    usr_name TEXT;
    clg TEXT;
    lstEmployer TEXT;
    gendr TEXT;
    skill_row_count  INTEGER;
    usr_id INTEGER;
    skl_id INTEGER;
    intEmailExist INTEGER;
    expen DECIMAL;
BEGIN

    -- Getting name
    SELECT jsonobject->'Name' into usr_name;

    -- Getting education
    SELECT jsonobject->'Education' into edu;

    -- Getting phone number
    SELECT jsonobject->'phone_number' into phone;

    -- Getting experience
    SELECT (jsonobject->>'Exp')::DECIMAL INTO expen;

    --Getting college
    SELECT jsonobject->'College' into clg;

    -- Getting last employer
    SELECT jsonobject->'CurrentEmployer' into lstEmployer;

    --Getting gender
    SELECT jsonobject->'Gender' into gendr;

    -- Getting Email
    SELECT json_array_length(jsonobject->'Email') into intEmailExist;
    IF intEmailExist > 0 THEN
        email:=array(SELECT json_array_elements_text(jsonobject->'Email'));
    END IF;

    -- Insert user details in 'Extractor_usr_details' table.
    INSERT INTO public."Extractor_usr_details"(name, phone_number, email, exp, education, college, "currentEmployer", gender)VALUES ( usr_name, phone, email, expen, edu, clg, lstEmployer, gendr) returning "id" into usr_id;

    skill_s := array(SELECT json_array_elements_text(jsonObject->'Skills'));
    FOREACH selectedSkill IN ARRAY skill_s LOOP
        INSERT INTO public."Extractor_skills" (skill) SELECT selectedSkill WHERE NOT EXISTS (SELECT id FROM public."Extractor_skills" WHERE skill = selectedSkill)RETURNING id into skl_id;
        INSERT INTO public."Extractor_usr_skills" (skill_id, user_id) SELECT skl_id, usr_id WHERE NOT EXISTS (SELECT id FROM public."Extractor_usr_skills" WHERE skill_id = skl_id AND user_id = usr_id);
    END LOOP;
    COMMIT;
END;
$BODY$;

and here is my python code to call the procedure..
   try:
   usrdetails = {'Name': 'Prashant Chandel', 'Education': 'B. Tech', 'Exp': 3.6, 'phone_number': '', 'College': '', 'CurrentEmployer': ['Tata Tele Services'], 'Gender': '', 'Skills': ['Spring boot', 'springioc', 'Css']}
        abc = json.dumps(usrdetails)
        print(type(abc))
        ps_connection = psycopg2.connect(user="postgres",
                                         password="demo",
                                         host="127.0.0.1",
                                         port="5432",
                                         database="postgres")

        cursor = ps_connection.cursor()
        cursor.execute("call insert_usr_details_in_all_tables(?)" [abc])

    except (Exception) as err:
        print("\npsycopg2 connect error:", err)
        conn = None
        cur = None

The argument to callproc needs to be a list of parameters. Therefore, if you pass in
abc=json.dumps(stringObject)
cursor.callproc('insert_usr_details_in_all_tables', abc)

It will split the string. Instead use:
cursor.callproc('insert_usr_details_in_all_tables', [abc,])


Why is mysqldump not saving newly added databases? I am using the following command to backup all mysql databases in script that is run as cronjob.

mysqldump -u root -p'***' --all-databases > backupdb.sql

I have added new databases recently and I noticed that the new databases are not saved in the backupdb.sql.  What am I missing?

1) check your last 10 lines of dump file

 tail dump.sql

it should show your dump file properly completed or not.

2) grep the database name in dump file

 cat dump.sql | grep newdb

with the above 2 steps you can find is your backup is completed propery or not and the new database exists or not.


Upgrading from SQL Server 2008 R2 SP1 to SP3 I have to install the Service Pack 3 on an SQL instance running with the SP1. No test, the install is directly on the production server. Well this is not a complex task and the topic has been already ask for other version, but I'd like to be sure to not forget some steps and having feed back from experienced dba is always welcome. Here my custom planned checklist :

1.list all bugs that have been addressed in the Service Pack.
ie:https://support.microsoft.com/en-us/kb/2509302

2.Take a virtual machine backup and backup all the databases.

3.Disable SQL Server agent.

4.Apply the Sp.

5.Restart server/instance if needed.

6.Check for the Service Pack level of the SQL Instance.

7.Check for all entries in SQL Server Error Log about the Service Pack update and ensure there are no errors written.

8.Refering step N°1, check for eventuals bug issues.

9.Enable SQL Server Agent.

From the client side :

Check for any compatibility problem between his software and the SP3.
After the install, check for client connectivity between DB/software.

Is there any step you'll find pertinent to add/remove/modify to this ?

This seems to be a pretty exhaustive list in your question. Service Packs are generally speaking safe affairs - but every so often things can go wrong.

Ideally you would run on a test instance and make sure things are good. Since you can't do that - making sure you take backups is key.

Stopping the SQL Server agent isn't necessary - but if you have many jobs and are trying to make sure a job doesn't kick off just before you start, etc - that is a good plan. Again not necessary, though.

I also like to read the notes on the CUs released after a Service Pack and look for details about potential issues. It doesn't happen all the time, but sometimes a SP ends up breaking something and that then gets fixed in a later CU.  In your case you are going with a fairly late release because Microsoft hasn't been generally releasing CUs - only security updates for that product with the current release now 3 versions higher. You can read the list of versions and builds and service packs and fixes post service packs here.


PostgreSQL equivalent query which has order by clause alone Please help me to get equivalent PostgreSQL query for the below oracle query

SELECT sum(COLUMN1),sum(COLUMN2)
FROM Table_name
ORDER BY COLUMN3;

The equivalent is simply:

SELECT sum(COLUMN1),sum(COLUMN2)
FROM Table_name;

As the order by is useless in any rdbms.


Shrink logs in between user transaction in SQL Server I have a database where there are like billions of rows and I need to delete the data based on some condition.

Initially I was trying to delete the code in one transaction and log starts growing to the hard disk and fails, then I tried to do it in chunks.

SET NOCOUNT ON;

DECLARE @r INT;

SET @r = 1;

WHILE @r > 0
BEGIN
  BEGIN TRANSACTION;

  DELETE TOP (2000000) -- this will change
    Databasename.dbo.Tablename
    WHERE loading_time < '08/27/2014 4:00:00'

  SET @r = @@ROWCOUNT;

  COMMIT TRANSACTION;
 -- printing if transaction is commited
  print 'done'
  CHECKPOINT
-- shrinking file after transaction is comminted
  --DBCC SHRINKFILE (2, TRUNCATEONLY); -- CHECKPOINT;    -- if simple
  -- BACKUP LOG ... -- if full
END

Is this the right way to delete data and delete logs, in between user transaction because when I did it I get an error ;

  Cannot perform a shrinkfile operation inside a user transaction. Terminate the transaction and reissue the statement.

Update I have change the code to include checkpoint, but even after adding check point, after 3 hours logs get filled and full rollback takes place.
What should i do to delete ?

Don't shrink the log file - just ask yourself if you'll need to recover to part way through the process, and use this to decide whether to use Full or Simple.

If you're going with Simple, then checkpointing regularly will be enough. Leave the log file large and then consider what to do with it after the whole process. If you shrink it each time it'll only grow for the next iteration.

If you're going with Full, then do a lot of backups, figuring out how to get them off somewhere. Again, leave the log file big and figure out if you need to shrink it at the very end.

Edit based on comments below: making sure the database being checkpointed is the same one as has the growing log file is important too. :)


Creating a partial unique constraint for MySQL I have the same question as asked in a previous post: PostgreSQL multi-column unique constraint and NULL values. But the solution there is not applicable as I am using MySQL instead of PostgreSQL.

My table looks like this:

CREATE TABLE `my_table` (
  `id` bigint(20) NOT NULL,
  `col1` bigint(20) NOT NULL,
  `col2` bigint(20) NOT NULL,
  `col3` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

I want the three columns col1, col2 and col3 to be unique. But a normal unique constraint

UNIQUE KEY `unique_constraint` (`col1`,`col2`,`col3`)

still allows me to insert identical rows whose col3 is NULL. I want to forbid entries like these:

1  | 1 | 1 | NULL
2  | 1 | 1 | NULL

I was searching for a partial unique constraint in MySQL as proposed in the post above but apparently there are not supported in MySQL 5.5.

Can somebody give me a hint how to solve this best in mysql, I am also hoping there will be a performant solution!

Thanks from a newbie in mysql (coming from PostgreSQL)!

The only option that comes to mind is a BEFORE INSERT trigger. In 5.5 you could use SIGNAL:

DELIMITER $$
CREATE TRIGGER bi_foo BEFORE INSERT on foo
FOR EACH ROW
BEGIN
  IF (ISNULL(NEW.col3) AND (SELECT COUNT(*) FROM foo
     WHERE col1 = NEW.col1 AND col2 = NEW.col2 AND col3 IS NULL) > 0)
  THEN
    SIGNAL SQLSTATE '45000'
     SET MESSAGE_TEXT = 'Cannot insert this item due to unique constraint',
         MYSQL_ERRNO = 1001;
  END IF;
END$$
DELIMITER ;

[sqlfiddle]

But pre 5.5, you would do this something like this:

DELIMITER $$
CREATE TRIGGER bi_foo BEFORE INSERT on foo
FOR EACH ROW
BEGIN
  IF (ISNULL(NEW.col3) AND (SELECT COUNT(*) FROM foo
     WHERE col1 = NEW.col1 AND col2 = NEW.col2 AND col3 IS NULL) > 0)
  THEN
    SET NEW='Error: Cannot insert this item due to unique constraint';
  END IF;
END$$
DELIMITER ;

[sqlfiddle]


What's the best paradigm or design pattern for exception handling in a mission-critical DB driven web application? I want to design a bullet-proof, fault tolerant, DB driven web application and was wondering on how to architect it.

the system will have a asp.net UI, web services middle tier and SQL2005 back end. The UI and Services will communicate using JSON calls.

I was wondering on how to ensure transactions are committed and if not for any error to bubble up and be logged. ideally the action to be retried a couple times after 5 minute intervals, like an email app does.

I was planing to use try catch blocks in SQL and was wondering what the interface (or contract if you will) would look like between the SQL stored procs and the Services that call them would look/ function. this interface will play 2 roles one is to pass params for the proc to function and return expected results. the next will be for the proc to return error information. maybe somethign like error number and error message.

my quagmire is how to i structure this intelligently so that the services expect and react accordingly to both data and error info returned from procs and handle each accordingly?

is there a framework for this because it seems very boiler plate?

Of course! Like most interesting problems in mission-critical applications, this was actually solved by IBM in the 1970s, but the IT industry is always chasing the shiniest new thing and has no respect for the great engineers of the past. I am referring of course to message oriented middleware, or as it was known back in the day, TPF. The principle of this is actually very simple, and has been proven time and time again, by banks, airlines, telcos, you name it.

The fundamental operations are, to place a message on the queue, and to take a message off the queue. So when your application needs to do some work, it packages that as a message and places it on the queue for the next service. The TPM then dequeues that message and attempts to get work done on it. If this fails, the message simply goes back onto the queue to be tried again, and if it continues to fail, gets shunted across to the queue that handles failures. The message bus also handles routing, load balancing and all that good stuff. You can buy these capabilities off the shelf: MQSeries and Tibco Rendezvous are the "serious" ones, but there are plenty of others around, many of which are compatible with JMS so you aren't tied into one vendor (of course if you go JMS then you are also bound to the lowest common denominator). You spec your message bus to be ultra-reliable and fault-tolerant, then you just hang the services off the back of it, and put your app on the front. SOA is another example of an old idea that's been reheated with new buzzwords...


Unable to output data from tables in pgAdmin4 due to missing oid operator I am currently running Postgresql 10.6 locally which I interface with using PgAdmin 4.12, up until today everything was running fine. However today I ran the following query in the pgAdmin query editor:

SELECT * FROM test_table LIMIT 100

and got the following error:

ERROR:  operator does not exist: - oid at character 125
HINT:  No operator matches the given name and argument type. You might need to add an explicit type cast.
STATEMENT:  SELECT at.attname, at.attnum, ty.typname
        FROM pg_attribute at LEFT JOIN pg_type ty ON (ty.oid = at.atttypid)
        WHERE attrelid=-1519044407::oid AND attnum = ANY (
            (SELECT con.conkey FROM pg_class rel LEFT OUTER JOIN pg_constraint con ON con.conrelid=rel.oid
            AND con.contype='p' WHERE rel.relkind IN ('r','s','t', 'p') AND rel.oid = -1519044407::oid)::oid[])

The strange thing is when I run the same command on tables which were created yesterday, the data is output into the Pgadmin data output window successfully. I also tried running the same command with psql:

psql -U postgres -d geodata -c 'SELECT * FROM test_table LIMIT 100'

which was also successful. I am able to create tables in Pgadmin just not output them directly. Any new table I create and then output ends with the top error, the only difference is the oid changes. I have uninstalled Pgadmin and made sure to delete all leftover folders and reinstalled with no change.

Does anyone have any ideas what the problem is? Is the issue coming from pgadmin or is my Postgresql server corrupted in some way?

Never seen a negative OID before. This is a first-class "crime scene"!

... attrelid=-1519044407::oid ...
... rel.oid = -1519044407::oid ...

Facts

0.

2^32 - 1519044407 = 2775922889

And we have verified that OID 2775922889 indeed exists in your DB. Tests:

SELECT * FROM pg_class WHERE oid = 2775922889;
SELECT * FROM pg_class WHERE oid = '-1519044407';
SELECT * FROM pg_class WHERE relname = 'test_table';

1.

The manual about object identifier types:

  The oid type is currently implemented as an unsigned four-byte integer.

2.

Postgres cast accepts signed integer anyway (!)

The Postgres I/O conversion from string literals, as well as the cast from integercurrently (pg 12) accepts negative integer values / literals as input anyway. Seems to just binary coerce a signed four-byte integer to unsigned four-byte integer and vice versa. Worth keeping in mind at least.

These, oddly, work:

test=# SELECT '-1519044407'::oid, '-1519044407'::int::oid;
    oid     |    oid
------------+------------
 2775922889 | 2775922889

Leads to different representation when casting to int versus bigint:

test=# SELECT (oid '2775922889')::int
test-#      , (oid '2775922889')::bigint;
    int4     |    int8
-------------+------------
 -1519044407 | 2775922889  -- !!

3.

The manual on Numeric Constants:

  Note that any leading plus or minus sign is not actually considered part of the constant; it is an operator applied to the constant.

4.

The cast operator :: takes precedence over the unary minus operator (-).

Conclusions

1.

I have never seen OID numbers in that range in system catalogs before, and I have been working with all kinds of big databases. You have a problem in your DB (cluster).

Improved with comments from Daniel Vérité:

Either you are burning OID numbers at an insane rate - already 2.8 billion numbers. ~ 1.5 billion remain until OID wraparound. Do you have any tables created with WITH OIDS? (Nobody should any more. The feature is deprecated and removed in Postgres 12.) Or some code excessively creating / dropping new objects? The OID counter is per instance, not per database, so all dbs contribute to OID consumption.
There is a comment in the source code for GetNewOidWithIndex for how OID collisions are dealt with after wraparound. Collisions incur a minor performance penalty.

Or somebody/something messed with your system catalogs.

2.

If the above query was generated by pgAdmin4, there is a serious bug.

Maybe that did not surface, yet, as nobody had OIDs in that range in the system catalogs, yet?

Seems like it operates with the integer representation of OIDs, and naively pastes those as numeric literals including the sign by mistake in queries. A string literal would work: '-1519044407'::oid. Or parentheses would make it work: (-1519044407)::oid.

But this does not:

-1519044407::oid

Because:

1519044407 is taken as numeric literal and initially coerced to integer.

The cast operator :: takes precedence over the sign operator - and the integer is cast to the (wrong!!) oid.

Finally, Postgres tries to apply the sign operator and, luckily, fails with the reported error message:

ERROR:  operator does not exist: - oid at character 125

If it would not fail there, serious nonsense might occur.

db<>fiddle here

I have posted a note to the pgadmin-hackers list.
A related bug has been logged before there, too. (Access with Postgres community account.) It was traced back to a psycopg2 issue using the wrong data type for OID in 32 bit versions. Should be fixed in psycopg2 version 2.8.4 (which pgAdmin4 depends upon).


What is the appropriate index architecture when forced to implement IsDeleted (soft deletes)? Currently, we have an existing database and application that is fully functional. I do not have the ability to change the architecture at this point. Today, each table in the database has an "IsDeleted" NOT NULL BIT field with a default of '0'. When the application "deletes" data, it simply updates the IsDeleted flag to 1.

What I'm having trouble understanding is how the indexes on each of the tables should be structured. Right now, every query/join/etc always implements the IsDeleted check. It's a standard that our developers must follow. That being said, I'm trying to determine if all of my clustered primary key indexes on each of the tables need to be altered to include the primary key AND the IsDeleted BIT field. Also, since EVERY query/join/etc. must implement the IsDeleted check, is it an appropriate assumption that EVERY SINGLE index (non-clustered as well) should include the IsDeleted field as the first field of the index?

One other question I have is around filtered indexes. I understand that I could put filters on the indexes such as "WHERE IsDeleted = 0" to reduce the size of the indexes. However, since every join/query will have to implement the IsDeleted check, would that prevent the filtered index from being used (since the IsDeleted column is used in join/query)?

Remember, I do not have the ability to change the IsDeleted approach.

The easiest approach here is to leave your keys and clustered indexes alone, and use filtered indexes for your non-clustered indexes.

Additionally you can migrate some large tables to partitioned heaps or partitioned clustered-columnstores (SQL Server 2016+), leaving the primary key and unique indexes un-partitioned.  This would enable you to push the non-key columns for IsDeleted rows to a seperate data structure, which could additionally be compressed differently or stored on a different filegroup.

And make sure the developers use a literal instead of a parameter to filter the IsDeleted rows out. With a parameter SQL Server has to use the same query plan for both cases.

EG

SELECT ... WHERE ... AND IsDeleted=0

And not:

SELECT ... WHERE ... AND IsDeleted=@IsDeleted

Using a paramter will prevent use of filtered index, and can get you into trouble with parameter sniffing.


Use SUM In Update Statement I am attempting to use SUM() in my SET statement for my UPDATE.  When I try this, I get an error of

  Msg 157, Level 15, State 1, Line 234
  An aggregate may not appear in the set list of an UPDATE statement.

What should I alter in my CTE so that I can run this update?

;WITH CTE AS
(Select EC, CN, CID, EntryDate
        FROM OPENQUERY(192.55.108.214,'select
                              count(es.estimatenumber) As EC
                              ,cm.an CN
                              ,cm.am CID
                              ,es.entrydate EntryDate
                              from etalac es
                              INNER JOIN bacalac cm
                              ON es.CID = cm.am
                              GROUP BY cm.an, cm.am, es.entrydate'))
UPDATE d2016
SET TotalNumber = SUM(EC)
FROM CTE cte
INNER JOIN datafor16 d2016
ON cte.CID = ed2016.CID
WHERE CAST(entrydate As Date) BETWEEN '01/01/2016' AND '01/22/2016';

You can do the aggregate in a subsequent cte, and update from there.

;WITH CTE AS
(Select EC, CN, CID, EntryDate
        FROM OPENQUERY(192.55.108.214,'select
                              count(es.estimatenumber) As EC
                              ,cm.an CN
                              ,cm.am CID
                              ,es.entrydate EntryDate
                              from etalac es
                              INNER JOIN bacalac cm
                              ON es.CID = cm.am
                              GROUP BY cm.an, cm.am, es.entrydate'))
, uCTE as (
  select cid, sumEC=sum(EC)
    from cte
    where CAST(entrydate As Date) BETWEEN '01/01/2016' AND '01/22/2016'
    group by cid
)
UPDATE d2016
  SET TotalNumber = sumEC
FROM uCTE cte
  INNER JOIN datafor16 d2016
    ON cte.CID = d2016.CID


Where to download SQL Server 2019 Express LocalDB Installer MSI from SQL Server 2019 is released but I there doesn't appear to be a LocalDB version available for download. Does anyone know if there will be a LocalDB release or if 2017 is the last version?

If somebody stumbles upon this, here is the full download URL for SQL Server 2019 LocalDB


How to move folder of SQL Server LocalDB instances from the default location? I get multiple errors with LocalDB (startup error, can't create DB from SQL Server Management Studio and also multiple issues when trying to restore a backup) and none of the fixes I found are helping anything. It seems to me that all errors have their root in permissions not set up correctly.

Now a simple DDL table creation script works fine from SQL Server Management Studio and creates the database in the root of my user account's folder (as explained here). The Instances folder however (located at D:\Users\[My name]\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances despite when installing the SQL Server Express with Advanced Services I specified the DATA folder in another location - where only standard SQL Server Express .mdf files are stored, but not LocalDB instances) seems problematic regarding permissions (in contrary to the account root) therefore and also for having my DB files in a location among my projects I'd like to move the Instances folder to another place. Is this possible? I haven't even found a corresponding registry entry.

I'm using SQL Server Express 2012 and also SSMS of the same version (latest updates installed).

Any help would be appreciated.

Edit:
The Database Settings page KookieMonster mentioned previously threw an error for me, not being able to edit nor see any configuration there. I let Windows Update check for updates again and there were some updates for SQL Server Express that weren't installed (although I'm sure I selected everything to install before), so I installed them. This made it possible for me to go to the settings page.

I've tried to set up the LocalDB instance's root folder to somewhere else but I get an access denied error for every folder I tried, namely

  Alter failed for Settings
  'Microsoft.SqlServer.Management.Smo.ObjectKeyBase'.
  (Microsoft.SqlServer.Smo)

  ------------------------------ ADDITIONAL INFORMATION:

  An exception occurred while executing a Transact-SQL statement or
  batch. (Microsoft.SqlServer.ConnectionInfo)

  RegCreateKeyEx() returned error 5, 'Access is denied.' (Microsoft SQL
  Server, Error: 22002)

I get this even if the folder has Full control set for Everyone! Even more amusing the only folder that works, one that is set as a default (the user account's root) has no special permissions applied - just the ones my user has (just as any other folder I tried to set). In effect this means that all my LocalDB databases' files are saved to my account's root folder.

See my comment above. I used Process Monitor to trace the registry access and found it was getting an Access Denied while trying to write:

3:16:40.8405491 PM sqlservr.exe    8756    RegCreateKey    HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11E.LOCALDB\MSSQLServer   ACCESS DENIED   Desired Access: Write

I got around this by opening the Registry Editor and giving Everyone full access to HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11E.LOCALDB\MSSQLServer. Then I changed the default location and removed the Everyone access once it was done. This made the following extra keys that weren't there before:

DefaultData

DefaultLog

BackupDirectory


Will removing a foreign key constraint affect performance? I have a units table and a reports table. A unit has many reports. So reports table has a foreign key unit_id which corresponds to the id of the unit. Hence, there is a foreign key constraint in reports table.

I began to realize that sometimes reports need to be created even if the unit doesn't exist for them yet. So when I tried to create a report without a unit, I obviously got this error:

 ERROR: insert or update on table "reports" violates foreign key constraint "reports_unit_id_fkey"
  Detail: Key (unit_id)=(7295) is not present in table "units".

My question is if I remove this foreign key constraint and enable reports to be created without a unit, will this affect performance when I try to get all reports associated with a unit?

SELECT * FROM reports WHERE unit_id=7295;

The only other option I have is to insert the reports into some orphans table, but then I would have to copy those orphans over when the unit is created, which is additional work.

At time of writing (PostgreSQL 9.4 and below), PostgreSQL's optimiser doesn't use foreign key constraints to prove anything or allow for extra optimisations. The only impact removing one can have on performance is a performance improvement due to the removal of foreign key checks.

It's a data integrity enforcement tool, not a performance tool.


What is the best database design for this situation? I'm working on building a business application for my company and struggling to pick the most appropriate database design for a particular situation.  Let's say I have the following entities:

Approval

Id
Status
...

ApprovalComment

Id
ApprovalId
Comment

Order

Id
...

Invoice

Id
...

There obviously might be multiple types of approvals and multiple objects that require approvals.  What would be the most appropriate of the following options for designing the tables:

OPTION 1

Have one approvals table with null foreign keys:

Approvals

Id PK
Status
OrderId FK NULL
InvoiceId FK NULL

ApprovalComments

Id PK
ApprovalId FK
Comment

In this case I would have to add a column for every object that needs an approval

OPTION 2

Have a parent Approvals table with common fields, and a child table for each object that needs an approval:

Approvals

Id PK
Status

ApprovalComments

Id PK
ApprovalId FK
Comment

OrderApprovals

ApprovalId PK FK
OrderId FK

InvoiceApprovals

ApprovalId PK FK
InvoiceId FK

OPTION 3

Have an approvals table for each object:

OrderApprovals

Id PK
OrderId FK
Status

OrderApprovalComments

Id PK
OrderApprovalId FK
Comment

InvoiceApprovals

Id PK
InvoiceId FK
Status

InvoiceApprovalComments

Id PK
InvoiceApprovalId FK
Comment

I know these are all valid solutions, but I can't decide which one would be best for adding different types of approvals in the future.  Any thoughts?

A rule of thumb I use, is that it's bad database design to have to alter a table to accomodate a code change or a new feature if it can be planned for in the future.

That said, I would use a variant of Option 1

Approvals
    Id PK
    ApprovalTypeID FK
    Status

ApprovalComments
    ID PK
    ApprovalId FK
    Comment

ApprovalTypes
    ID PK
    Name

Now when adding new types of objects that need approval, you only need to insert a row into ApprovalTypes instead of altering a table to add a column.


How can I import latitude and longitude data into a database using Mysql Workbench? I am currently trying to get some latitude and longitude data to import into a database using the data import wizard in mysql workbench.  the problem is that the floating point values that get imported are modified by mysql workbench, or whatever query it's preforming, so they are useless.  Example:

40.8152 turns into 40.0000.

I cannot figure out why this is happening.  I have the columns set to DECIMAL(6,4), but they still get modified when imported.

Anybody have any ideas as to what might be causing this and how i can get these to import correctly?

Edit:

I have also tried FLOAT, FLOAT(6,4), DOUBLE and DOUBLE(6,4) with the same results.  It either removes the trailing decimal values completely or sets them to 0.

Well, I couldn't figure out how to do it in Mysql Workbench, but I have a workaround.

Use:

mysqldump -u mysqlUser -p DatabaseName TableName > table_data

To get data out of the table you want to use, then use:

mysql -u mysqlUser -p DatabaseName TableName < table_data

To get the data into the desired table.  This will retain the correct values.


Database for Orders/Inquiries with "custom" Product Data At the moment i practice with SQL Databases and created a simple Python tool to manage Order and Inquiry with a simple GUI for testing.

Now I have a question i didn't found a good solution for:

There are standard values for Products like a price and costs etc. These could vary from Order to Order (volume effects etc.).
So i need to save them on a different place.

What is a common way to do that?

Having an extra table for Products used in Orders where i copy all standard values from the Product Database and change them there if the price or anything needs to be changed in an order?

Yes, If you create an extra table for storing ordered product information that would be helpful to you.

Make sure you insert all entries in orderedproduct table with reference to order id so it would be easily selected as and when we require all order details with product information.


PostgreSQL database connection time how to check how much time PostgreSQL database takes for making connection?

Try with this query does the trick:

select *
from pg_stat_activity
where datname = 'mydatabasename';

Info:

See this closely related answer.

pg_stat_activity is a view in the pg_catalog schema.

You can query it by SELECTing from it like any other table, eg SELECT * FROM pg_stat_activity. The manual page you linked to explains its columns.

You'll sometimes find yourself wanting to join on other tables like pg_class (tables), pg_namespace (schemas), etc.

pg_stat_activity does not expose information about back-end memory use. You need to use operating-system level facilities for that. It does tell you the process ID, active user, currently running query, activity status, time the last query started, etc. It's good for identifying long-running idle in transaction sessions, very long running queries, etc.

Frankly, PostgreSQL's built-in monitoring is rather rudimentary. It's one of the areas that's not that exciting to work on, and commercial clients aren't often willing to fund it. Most people couple tools like check_postgres with Icinga and Munin, or use Zabbix or other external monitoring agents.

In your case it sounds like you really want pg_stat_statements, and/or PgBadger log analysis with suitable logging settings and possibly the auto_explain module.

Credits to: Craig Ringer


Mongodb replace default _id with string I replace MongoDB default _id with type string. And now I want to sort my collections, the result _id after sort is:

1
10
100
101
2
20
200

I want the result like sorting number which is:

1
2
3
4
5

What's my option? Is there any way to sort the string like number? Or I have to change my _id type?

What's my option? Is there any way to sort the string like a number?

Yes, you have an option to sort string like a number, but in all your queries you need to convert your string field to an integer field just for sorting and it is not recommended.

 db.collection.aggregate(
[
    {
        '$addFields': {
            '_id': {
                '$toInt': '$_id'
            }
        }
    }, {
        '$sort': {
            '_id': 1
        }
    }
]
)

  Or I have to change my _id type?

Yes, you need to change the type of _id field from string to integer as a permanent solution. Since _id filed is an immutable field, you cannot directly convert the data of _id field from string to integer. you need to remove all your documents and insert with the corrected data type.


Oracle query is slower the second time I have a general question about something I'm seeing in performance tuning in Oracle.  I have a query that I'm testing.  It's too big and complex for me to provide a simple example, sorry!  (I tried lowering it down, but lost the symptom)

The first time I run the query, it's 2 seconds.  Subsequent times it's 3, 4, or even 5 seconds.  This happens consistently - if I change the spacing, I get 2 seconds again, then it goes back up.

This is the opposite of what I would expect - after the first time, the database doesn't need to parse the query, and blocks should be in read into the buffer cache.  The only thing I can think of is that it is writing temp data somewhere, in such a way that it is faster to write and read it than it is to just read it.  But that of course makes no sense.

Does anyone know why this is happening?  Or any red flags in the execution plan that would cause something like this?

FYI, this is a SELECT, with multiple joins, aggregation, and views.  No internal PL/SQL.

Thank you

@Colin It's unlikely another process could be affecting this Dev machine, at least so consistently.  Not sure what dynamic sampling is.  Same thing as cardinality feedback?

@druzin No bind variables, but no predicate changes either - same hash.

@mustaccio Good idea, although I suspect it's something to do with implicit temp tables or caching.  I'll give it a try.

Cases like this can usually be solved for certain by enabling trace events 10046 (if you have authority to do so), and run tkprof on the resulting trace files:

alter session set events '10046 trace name context forever, level 8';
select /* try one */ -- add your SQL here and invoke it;
select /* try two */ -- add your same SQL here and invoke it;
show parameter user_dump_destination

Invoke it a few times, and then turn tracing off, log off to ensure the trace files get closed:

alter session set events '10046 trace name context off';
exit;

Then find your trace file, and run tkprof on it, assuming your trace file name is ${ORACLE_SID}_ora_39110.trc:

cd $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace
tkprof ${ORACLE_SID}_ora_39110.trc ${ORACLE_SID}_ora_39110.trc.txt
vi ${ORACLE_SID}_ora_39110.trc.txt

The output of tkprof will show you execution statistics (logical reads, physical reads, etc). And you can look at the actual trace file if the tkprof output doesn't help.

If that doesn't help, you can enable 10053 event, and enjoy wading through thousands of lines of output; but sometimes you need to do that.

But first things (well easier things) first; do as others suggested; see if table statistics are up to date, optimizer settings, etc.  If you are using the exact same criteria each execution, could be the optimizer is "on the border" on plans.  I would ensure you have collected system statistics; see the documentation on dbms_stats.gather_system_stats pl/sql package.


Azure SQL Server Database Error: 18456, Severity: 14, State: 1 Why can't I login to my SQL Server database on Azure, and how do I correct this? No one in our company has changed the password of our Azure SQL Server database administrator user, yet I'm getting the error noted below when I use SQL Server Management Studio 2017 on my local PC and try to connect to my Azure database as I always have:

Server Name: XXXX.database.windows.net
Error Number: 18456
Severity: 14
State: 1
Line Number: 65536

According to Microsoft (https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error?view=sql-server-2017), Error 18456 is a logon failure. I'm not sure what the Severity value means. But as for the State value of 1, it means, "Error information is not available. This state usually means you do not have permission to receive the error details. Contact your SQL Server administrator for more information."

Unfortunately, the Azure support website is down so I can't investigate further.

Azure is having a pretty major outage right now!

  Microsoft has warned that a "subset of customers in South Central US"
  may experience Azure problems today after cooling issues sent the
  servers scurrying for the shutdown button.

  The warning was first raised by Microsoft at 09:29 UTC as pretty much
  everything in the South Central US region went offline thanks to a
  temperature spike that caused servers to automatically shut down to
  avoid damage.

I'd suggest following @AzureSupport to stay updated.

  Unfortunately, the Azure support website is down so I can't investigate further.

Is a pretty good clue as to what's going on.


Can't read password from config using --defaults-extra-file in MySQL I am trying to backup all databases automatically using a batch script. The password is read from a config file called config.cnf. Below is the batch script:

@echo off
C:\MySQL\bin\mysqldump.exe --defaults-extra-file=config.cnf -u root --all-databases > bak_%DATE%.sql 2> sql_error.log

Contents of config.cnf:

[mysqldump]
password = mypassword

When running the batch the following error messages are returned:

Could not open required defaults file: config.cnf
Fatal error in defaults handling. Program aborted
mysqldump: Got error: 1045: "Access denied for user 'root'@'127.0.0.1' (using password NO)" when trying to connect

For some reason, the config.cnf is not being read and the execution of the script fails. I am not sure why this is happening.

Edit: Version of the database is MySQL version 14.16 Distrib 5.2.4 MariaDB.
Running the batch script as Administrator still results in same error message, as does also modifying the config file, as well as modifying the group to [client]. Found an alternative solution, which is answered below.

It looks like the access issue due to permissions level.
Try running the batch file as administrator by right clicking on it and then selecting Run as administrator ,Try wrapping your password with in '' if it contains special characters,Try removing any extra space that might be getting added,also please find few more solutions to similar problem here :

As per examples from http://dev.mysql.com/doc/refman/5.7/en/option-files.html
The password should look like:

[client]
# The following password will be sent to all standard MySQL clients
password="my password"


Percona Toolkit Installation Error [root@localhost ~]# rpm -ivh percona-toolkit-2.2.7-1.noarch.rpm
  error: Failed dependencies:   perl(DBD::mysql) >= 1.0 is needed by
  percona-toolkit-2.2.7-1.noarch    perl(Time::HiRes) is needed by
  percona-toolkit-2.2.7-1.noarch    perl(IO::Socket::SSL) is needed by
  percona-toolkit-2.2.7-1.noarch

How to solve above dependencies?

I got this same error on an Amazon Linux box (AWS EC2). Here's what I did to get Percona toolkit installed.

# Installs Percona's Perl deps...
yum install perl-Time-HiRes
yum install perl-DBD-MySQL
yum install perl-IO-Socket-SSL     # I actually didn't need this one,
                                   # but based on your error you probably will.

# Install Percona toolkit
rpm -Uhv http://www.percona.com/redir/downloads/percona-toolkit/LATEST/RPM/percona-toolkit-2.2.7-1.noarch.rpm

I'd suggest getting the URL for the Percona toolkit RPM from their website under the "Download Latest" link. (Latest for me at the time of this post was 2.2.7.) The website offers RPM and deb formats; just choose RPM, and then use the link it shows.

I know this doesn't address the OP's problem as described, but it does address his root problem (can't get Percona toolkit installed). I agree with Peter's answer in that a package manager like yum or apt should be used... if for no other reason than handling nasty dependency hell, but I don't know Percona's yum or apt repo... or if they even still have one.

Edit: Also, those are just the Percona toolkit dependencies that I had missing on my box. You may have more or less. Another EC2 instance of mine already had all of the Perl dependencies installed, so all I needed to do was run the rpm command. A Percona repo sure would be great to have!


Ordering a parent table by SUM() of colums of two of its child tables I know that title might sound confusing, but i will try to explain my problem as much as i can.

I have four tables:

companies{
id,
name,
address
}

stores{
id,
name,
address,
company_id
}

invoices{
id,
total,
date_time,
store_id
}

receipts{
id,
total,
date_time,
store_id
}

As you can see, store is linked to a company and both invoices and receipts are linked to a store.
I am having a problem writing a query which will return a list of stores by a company name, ordered by their turnover.

For example:

SELECT s.name as storename, SUM(i.total) as turnover
FROM companies c
  INNER JOIN stores s on s.company_id = c.id
  INNER JOIN invoices i ON i.store_id = s.id
WHERE c.name = 'Com1'
AND QUARTER(i.date_time) = 4
AND YEAR(i.date_time) = 2019
GROUP BY s.name
ORDER BY turnover DESC

I know how to order a store by a sum(invoices.total) or by sum(receipts.total), but i struggle to find a way to combine the two. I wish for end result to be something like this.

+-----------+----------+
| store_name| turnover |
+-----------+----------+
|    store1 | 1231.43  |
|    store2 | 2342.34  |
|    store3 | 4323.21  |
+-----------+----------+

Where turnover will be the SUM of SUM(invoices.total) and SUM(receipts.total)

SELECT s.name as storename, i.turnover + r.turnover as turnover
FROM companies c
INNER JOIN stores s on s.company_id = c.id
INNER JOIN ( SELECT store_id, SUM(total) as turnover
             FROM invoices
             WHERE QUARTER(date_time) = 4
               AND YEAR(date_time) = 2019
             GROUP BY store_id ) i ON i.store_id = s.id
INNER JOIN ( SELECT store_id, SUM(total) as turnover
             FROM receipts
             WHERE QUARTER(date_time) = 4
               AND YEAR(date_time) = 2019
             GROUP BY store_id ) r ON r.store_id = s.id
WHERE c.name = 'Com1'
ORDER BY turnover DESC

?


MySQL server feature gap on Windows and Linux Is there any gap or difference in features of MySQL Server on Windows and Linux? Feature refers to below aspects:

common DBMS functionalities such as Stored Procedures, Updateable Views, Distributed Transactions
differences in behavior caused by system features.
differences in configuration that have a impact on performance such as Shared Memory on Windows.

No, in terms of features there is no difference between Windows and Linux versions of mysql. There are certain configuration options that may make sense on one of them only, for example see identifier case sensitivity, that you have to consider when migrating databases between the 2 platforms. Mysql documentation has a section on limits on Windows platform that lists those differences that may cause real headaches.

Obviously, there may be performance differences and tuning differences, but those have nothing to do with features.


Change database file path with SQL not running I've an urgent problem.

Due to some heavy delete queries, my database transaction log has grown and it has filled the disk (126GB). I've found out that backup plan wasn't working during last month.

Now, the database is recovering.

  Recovery of database 'ABCD' (5) is 1% complete (approximately 45395 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.

I've added a new disk to the server and I would like to move the .ldf file and then restarting the recovery of database.

Is it possible?

Do not restart sql server service or try to offline the db

What you can do is ADD a secondary log file using

ALTER DATABASE [dbname] ADD LOG FILE ( NAME = N'dbname_log2', FILENAME = N'M:\dbname_log2.ldf' , SIZE = 20480KB , FILEGROWTH = 1024KB )

Check log file usage :

DBCC SQLPERF(LOGSPACE);

This way the db can be online and you can start taking log backups (or if point in time restore is not required, then change to simple recovery) to truncate the log file.


PostgreSQL WAL file database system identifier wrong I'm having a problem restoring a copy of a PostgreSQL database to another server.

I have taken a backup using the pg_basebackup tool and can restore that but when I restore WAL logs from the archive I get this error

WAL file is from different database system: WAL file database system identifier is 6355383437015733568, pg_control database system identifier is 6366125487137379747

Though the source database reports it's identifier as   WAL file is from different database system: WAL file database system identifier is 6355383437015733568, pg_control database system identifier as 6366125487137379747

I am 100% certain the the WAL archive is from the correct source it just looks like the WAL files have the wrong id . any ideas how to resolve ?  a pg_xlogreset in the source db made no difference

Version 9.6 on Amazon Linux all fully patched

Thanks

Can it be what archive already filled with WAL from another installation? Postgres documentation specifies that archive_command should not overwrite WAL files in archive (https://www.postgresql.org/docs/9.6/static/continuous-archiving.html):

  The archive command should generally be designed to refuse to overwrite any pre-existing archive file. This is an important safety feature to preserve the integrity of your archive in case of administrator error (such as sending the output of two different servers to the same archive directory).

So if archive is set up correctly it will refuse to override WAL and restore will fail.


Can you import a Postgresql 9.5.10 db into a Postgresql 10.9 server? I would like to know if it is safe to import a dump made from a database running on a PostgreSQL server 9.5.10 to a PostgreSQL server 10.9.

Basically I am able to do the import. It does not fail. But how can I be sure nothing in the schema has been lost somehow ? We are talking Production here.

What I have done:

I have dumped the db from the current RDS server (9.5):

/usr/lib/postgresql/9.5/bin/pg_dump -h server-psql-9.5.amazonaws.com -U xxxxxx --no-password -Fc xxxxxxx -f psql-9-data.dmp

Using pg_restore version 9.5 I have created the restore file:

pg_restore -l xxxxxxxxxxx | sed '/COMMENT - EXTENSION/s/^/; (filtered out) /' > db_restore

I have dropped the database at the new RDS server (this worked using psql version 9.5 on a psql server version 10):

psql -X -h server-psql-10.amazonaws.com -U xxxxxxxxxxxx -d xxxxxxxxxxx -f drop_all.sql

Using pg_restore version 10 I have restored the database  using the dump of the 9.5 db:

pg_restore -h server-psql-10.amazonaws.com -U xxxxxxxxxxx -d xxxxxxxxxxxxx --no-password --no-owner --no-privileges -L  db_restore psql-9-data.dmp

Using pg_dump version 10 I have dumped the schema of the 10 db:

pg_dump -h server-psql-10.amazonaws.com -U xxxxxxxxxx --no-password -Fp xxxxxxxxxxx -s -f psql-10-schema.dmp

Using pg_dump version 9 I have dumped the schema of the 9.5 db:

/usr/lib/postgresql/9.5/bin/pg_dump -h server-psql-9.5.amazonaws.com -U xxxxxxxxxxxxx --no-password -Fp xxxxxxxxxxx -s -f psql-9-schema.dmp

Then I used diff to compare the two schema dumps and it shows there is a lot of difference between the schema of 9.5 and 10. That is why I don't feel safe AT ALL doing this. Why arent the schemas identical ?

If I eliminate the step of creating a restore file and I import the dump directly (as suggested in the comments) I get this error:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3613; 0 0 COMMENT EXTENSION plpgsql
pg_restore: [archiver (db)] could not execute query: ERROR:  must be owner of extension plpgsql
    Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

pg_restore: [archiver (db)] Error from TOC entry 3614; 0 0 COMMENT EXTENSION citext
pg_restore: [archiver (db)] could not execute query: ERROR:  must be owner of extension citext
    Command was: COMMENT ON EXTENSION citext IS 'data type for case-insensitive character strings';

pg_restore: [archiver (db)] Error from TOC entry 3615; 0 0 COMMENT EXTENSION "uuid-ossp"
pg_restore: [archiver (db)] could not execute query: ERROR:  must be owner of extension uuid-ossp
    Command was: COMMENT ON EXTENSION "uuid-ossp" IS 'generate universally unique identifiers (UUIDs)';

The error message is harmless, it only means that you imported the dump as a non-superuser and the extensions already existed.

There are potential problems with using a non-superuser (ownership usually cannot be restored), but since you didn't get any such error messages, you are fine.

The only important consideration is that you need to use pg_dump from version 10 to dump the 9.5 database, not the 9.5 pg_dump. While v10 knows how to dump a 9.5 database so that it can be loaded into v10 properly, 9.5 lacks that knowledge.


Home rolled gridview search results shows rows that are supposed to be ignored Note: I tried posting this on Stack Overflow but didn't get any good bites.  I'm still researching this but wanted to see if any db czars can assist here.

I'm in the process of trying to troubleshoot some SQL 2012 code in an ASP/C# gridview search that I rolled myself.  I have a search box that searches on specific fields but the twist here is that I only want results after a certain row.

In this database I have a sequence command that autogenerates a number in a column called 'record_number' for every entry in this table and only want results after record #500.  The entries are created manually via a different page.

Right now my search code is as follows:

"SelectCommand="SELECT [record_number], [column_a], [column_b], [column_c],
    [column_d], [column_e], [column_f], [column_g], [column_h],
    [column_i], [column_j]
  FROM [schema].[table_name] WHERE record_number >= 500
  AND ([column_a] LIKE '%' + @column_a + '%')
   OR ([column_c] LIKE '%' + @column_c + '%')
   OR ([column_e] LIKE '%' + @column_e + '%')
   OR ([column_g] LIKE '%' + @column_g + '%')
   OR ([column_h] LIKE '%' + @column_h + '%')
   OR ([column_j] LIKE '%' + @column_j + '%')"

I can post my SelectParameters if needed or anything else.  The challenge I'm facing is when I run a search for just the letter 'c' leveraging this code, the results that post have a record_number less AND greater than than 500. At this point I'm spinning my wheels as I'm not seeing where the problem lies.

If more information is needed by the masses, please let me know.

EDIT

Forgot this part - I've tried moving around the commands and consolidating the commands but no go.

@Mark is absolutely right. AND has higher precedence, so what your query is really saying is:

WHERE record_number >= 500 AND (column_a LIKE '%something%')

OR

column_c LIKE '%something else%'

OR

column_e LIKE ...

What you want it to say is:

WHERE record_number >= 500

AND

column_a LIKE '%something%' OR column_c LIKE '%something else%' OR ...

The way you do this is through parentheses. So:

WHERE record_number >= 500
AND
(column_a LIKE '%something%' OR column_c LIKE '%something else%' OR ... )

However I also agree with Mark's idea that this is wrong on many levels. Why have you hard-coded the query to not care about the first 499 rows? What happens if the user doesn't enter a parameter for one of the columns? Do you expect this to perform well when your table gets large and you are essentially forcing scans to find all of the matching rows?


Access fails to group data! In this example Query,

SELECT [Account Transactions].ID, [Account Transactions].Transaction_Amount, *
FROM [Account Transactions]
GROUP BY [Account Transactions].ID, [Account Transactions].Transaction_Amount;

This Idea is to Group Account Transaction.ID's and capture the Sum of tracaction_amount for all transaction of the same ID.

The code was generated by Access 2016 and when executed I get:

  Can not group on Fields with '*'

The interesting thing is that I can generate a report with the very same grouping and it works perfectly.

The grouping may be the same, but you have an asterisk (shortcut for select all field, but the sequence is unkown what prevents grouping and aggregation) at the end of your selected fields that causes the error.

If you want to get the sum of Transaction_Amount per ID you just group by ID not by Transaction_Amount

SELECT
    Sum([Account Transactions].Transaction_Amount) As SumAmount
  , [Account Transactions].ID
FROM
    [Account Transactions]
GROUP BY
    [Account Transactions].ID


Error when using WITH (DROP_EXISTING = ON) Okay so I've been condensing files off of some tiny files and moving them onto an FG that I created for these miscellaneous tables. I've been moving these tables by recreating the indexes with the WITH (DROP_EXISTING = ON) option and it's been working great. However this last time it gave me an error. Here's the script I'm running
CREATE Unique CLUSTERED INDEX PK_MyTable
    ON dbo.MyTable (MyTableID, RowStatus)
    WITH (DROP_EXISTING = ON)
    ON FG_Misc;

When I run this I get the following error message.

Msg 1902, Level 16, State 3, Line 1
Cannot create more than one clustered index on table 'dbo.MyTable'. Drop the existing clustered index PK_MyTable' before creating another.

I don't see any reason why this is throwing this error on this particular table but not any others I've been working on.

This can happen when the current primary key is nonclustered, and there is also a clustered index:

CREATE TABLE dbo.MyTable
(
    MyTableID integer NOT NULL,
    RowStatus tinyint NOT NULL,
    SomeOtherKey integer,

    CONSTRAINT [PK_MyTable]
        PRIMARY KEY NONCLUSTERED (MyTableID, RowStatus),

    CONSTRAINT [CUQ_MyTable]
        UNIQUE CLUSTERED (SomeOtherKey)
);

CREATE Unique CLUSTERED INDEX PK_MyTable
    ON dbo.MyTable (MyTableID, RowStatus)
    WITH (DROP_EXISTING = ON);

  Msg 1902, Level 16, State 3, Line 14
  Cannot create more than one clustered index on table 'dbo.MyTable'. Drop the existing clustered index 'CUQ_MyTable' before creating another.

It is valid to change a primary key from nonclustered to clustered using DROP_EXISTING syntax:

CREATE TABLE dbo.MyTable
(
    MyTableID integer NOT NULL,
    RowStatus tinyint NOT NULL,
    SomeOtherKey integer,

    CONSTRAINT [PK_MyTable]
        PRIMARY KEY NONCLUSTERED (MyTableID, RowStatus),
);

CREATE Unique CLUSTERED INDEX PK_MyTable
    ON dbo.MyTable (MyTableID, RowStatus)
    WITH (DROP_EXISTING = ON);


STRING_SPLIT with a multiple-character separator? SQL Server 2016 introduced STRING_SPLIT which is very fast and an excellent replacement for any homemade implementation people would roll before 2016.

Unfortunately, STRING_SPLIT only supports a single-character separator, which isn't always enough. Does anyone know of a good implementation that allows for using multiple characters in the separator?

Well, you can always use REPLACE to add a single-character delimiter to the argument before passing it in. You just need to choose a character that is unlikely/impossible to appear in the actual data. In this example, let's say your original data uses three pipes as a delimiter; I chose a Unicode character at random to substitute:

DECLARE
  @olddelim nvarchar(32) = N'|||',
  @newdelim nchar(1)     = NCHAR(9999); -- pencil (✏)

DECLARE @x nvarchar(max) = N'foo|||bar|||blat|||splunge';

SELECT * FROM STRING_SPLIT(REPLACE(@x, @olddelim, @newdelim), @newdelim);

I blogged about this in more detail here:

Dealing with the single-character delimiter in STRING_SPLIT

Addressing a comment:

  bad solution. what if original string is like 'abc||pqr|||rst||123' (dynamic and can contain anything). desired o/p is  'abc||pqr' and 'rst||123' but your solution will give 'abc' 'pqr' 'rst' '123'

Okay, let's take your input and see if my solution gets the wrong output.

DECLARE
  @olddelim nvarchar(32) = N'|||',
  @newdelim nchar(1)     = NCHAR(9999); -- pencil (✏)

DECLARE @x nvarchar(max) = N'abc||pqr|||rst||123';

SELECT * FROM STRING_SPLIT(REPLACE(@x, @olddelim, @newdelim), @newdelim);

Result is:

abc||pqr
rst||123

And not what you must have assumed (but didn't test) this:

abc
pqr
rst
123

If your data is in a table, you could create a view so that you don't have to factor that expression into all of your queries.

If that doesn't work, because you might have a pencil (✏) in your data, and you can't find a single character in the 1,111,998 available Unicode characters that won't be in your data set, you'll have to skip STRING_SPLIT(), since it is hard-coded to accept a single character delimiter (separator
Is a single character expression).

Alternatives have been answered here dozens of times before, many before STRING_SPLIT() existed. Those methods still work.

I go over many alternatives, and also discuss the limitations in STRING_SPLIT(), in this series (I also discuss why you might consider not doing this in T-SQL using any method at all):

Split strings the right way – or the next best way
Splitting Strings : A Follow-Up
Splitting Strings : Now with less T-SQL
Comparing string splitting / concatenation methods
Performance Surprises and Assumptions : STRING_SPLIT()
STRING_SPLIT() in SQL Server 2016 : Follow-Up #1
STRING_SPLIT() in SQL Server 2016 : Follow-Up #2


SQL+ Select highest 5 number of entries and display in ascending order so far i am able to select the highest 5 entries by putting them in descending order

SELECT LAST_NAME, COMMISSION_PCT

FROM EMPLOYEES
WHERE JOBID = 'SALES' AND ROWNUM <= 5
ORDER BY COMMISSION DESC
/

This works fine for selecting the highest entries but how would i put them in ascending order? (WITHOUT CHANGING THE OUTPUT TO THE 5 lOWEST VALUES)
thanks

No, that does not work fine for selecting highest entries. Your query grabs the first 5 rows in any order, and orders them by commission, it is a very common mistake with rownum.

You need to order by first and after that you can use rownum:

select * from
(
  SELECT LAST_NAME, COMMISSION_PCT
  FROM EMPLOYEES
  WHERE JOBID = 'SALES' order by commission_pct desc
)
where rownum <= 5
order by commission_pct;

Or:

select last_name, commission_pct from
(
  SELECT LAST_NAME, COMMISSION_PCT, rank() over (order by commission_pct desc) as rn
  FROM EMPLOYEES
  WHERE JOBID = 'SALES'
)
where rn <= 5
order by commission_pct;

Or 12c+:

select * from (
select last_name, commission_pct
FROM EMPLOYEES WHERE JOBID = 'SALES'
order by commission_pct desc fetch first 5 rows only)
order by commission_pct;


MySql - Changing expire_logs_days without restarting the server I'm using MySql 5.5.
Is it possible to change expire_logs_days and have the changes take effect without restarting the server?

thanks @dezso

here is how I changed it: SET GLOBAL expire_logs_days = 4;


Should I enable db_writer in public facing website I've come across an issue when using Entity Framework and trying to delete an object.

The SQL-Server DB that it's trying to delete from, and the Login it is using, has a role membership of public only.  If I check the db_datawriter box, the data is deleted as expected.

I'm completely new to SQL-Server admin, would enabling this option pose any security risk to our website?

Yes it would be a security risk. db_datawriter gives the account access to all tables in the database. This means that anyone getting control of the web user can delete all content.

A better solution would be to create an new role in the database. Assign the entity framework user to this role and only grant the bare minimum privileges needed to run the app to the role. Ideally, the Entity Framework would only read tables via GRANT SELECT (maybe even only some columns) and perform all writes via stored procedure with GRANT EXECUTE enabled.

An even better solution would be to not use Entity Framework at all. Like every other ORM on the planet - it is horrible way to access a database. Tell your programmers to learn SQL instead of fooling about with Object Oriented database access frameworks.


using count from subquery results to order parent query results I have this query:
       select * from mbk_file
       where user_id = $1
       order by (
           select count(file_id) from mbk_file_label
           where file_id = mbk_file.id
       ) DESC

I noticed that changing DESC to ASC didnt change the order, even though I would expect it should. So that tells me that the subquery is not really doing anything useful.
I have these tables:
-----mbk_file------
id   bigint

-----mbk_file_label-----
id   bigint
file_id  (from above table)

where there is a 1 to many between the 1st and 2nd tables.
I don't think I need a group by clause, I just need a count of all the rows where file_id is from the 1st table?
I think I need to do something like this instead:
         select *, (
                 select count(file_id) from mbk_file_label
                 where file_id = mbk_file.id
           ) as xxx
           from mbk_file
           where user_id = $1
           order by xxx DESC

but I still don't understand how file_id will vary by row..
let me explain with data:
---mbk_file----   // has 3 records total
1
2
3

---mbk_file_label
1  1
1  2
1  3
1  4       // file 1 has 4 matching records

2  1
2  3       // file 2 has 2 matching records

3  2
3  3
3  4       // file 3 has 3 matching records

so what I want to do is rank the mbk-files, based off of how many matching records there are in mbk-file-label.
So the rank/order for select result for mbk-files would be:
1
3
2

since 1, has 4 matching records, file 2 has 2 matching records and file 3 has 3 matching records.  I hope this makes sense because it's pretty simple but i don't know how to create the query fml :(

Your ORDER BY is this:
order by (
           select count(file_id)....
       ) DESC

The count will return a (constant) integer, such as 300. Which means you are effectively saying ORDER BY 300 DESC (where 300 is the number of file_id values for the specified user_id). This isn't using a column to sort, but a constant.
Sorting by a constant does nothing. You presumably expect your results to be ordered based on a value in a column (or a calculation derived from a column), but that's not what the query does


is there a max number of rows in a SQL Server 2008/2012 and MySQL table? Im evaluating a RDBMS for a bank app solution and i need to be sure about these performance parameter. There is a lot of info on internet that is confusing me :O.

Max number of concurrent connections per instance.
Max number of records per table (if there is exist a limit).
Max number of transactions per second.

Every suggestion it will be welcome

Microsoft has excellent documentation on the limitations of SQL Server

MySQL has a similar resource (warning - PDF) on their site.

As it stands, your question is far to broad and will likely be put on hold until you can narrow down your requirements.


How to update complex jsonb column? I have a table with following definition:

create table json_test (
    filter_data jsonb);

and i insert to it values like this:

'{"task_packets": [
    {
        "state": "PROCEEDING",
        "task_id": 1001
    },
    {
        "state": "REVERTING",
        "task_id": 1002
    }
]}'

Now I want to update this jsonb column to:

'{"task_packets": [
    {
        "state": "DONE",
        "task_id": 1001
    },
    {
        "state": "REVERTING",
        "task_id": 1002
    }
]}'

i.e. I want to change state for value with specified task_id inside array of task_packets. I suggest somehow to use jsonb_set() function in combination with #- operator (first delete value from array, then append to it with updated state).
How can I do it?

Like I commented, this would be more efficient with a normalized DB layout, with a table like this

CREATE TABLE task_packets (
  task_id int PRIMARY KEY
, state text NOT NULL
-- or: state_id int NOT NULL REFERENCES state(state_id) ...
);

Among other things, we can have a PK constraint enforcing unique task_id numbers. And the UPDATE you want is trivial.

But to answer the question asked:

To SELECT:

SELECT *
FROM   json_test jt
     , LATERAL (
   SELECT jsonb_set(filter_data
                  , '{task_packets}'
                  , jsonb_agg(CASE WHEN elem->>'task_id' = '1001'
                               THEN jsonb_set(elem, '{state}', to_jsonb(text 'DONE'))
                               ELSE elem
                             END)) AS filter_data_new
   FROM   jsonb_array_elements(filter_data->'task_packets') elem
   ) tp
WHERE  jt.filter_data @> '{"task_packets": [{"task_id": 1001}]}';

I suggest a LATERAL join, among other things to exclude the possibility of multiple matching rows that might be lumped together incorrectly in a plain join.

To UPDATE:

UPDATE json_test
SET    filter_data =
   (
   SELECT jsonb_set(filter_data
                  , '{task_packets}'
                  , jsonb_agg(CASE WHEN elem->>'task_id' = '1001'
                                THEN jsonb_set(elem, '{state}', to_jsonb(text 'DONE'))
                                ELSE elem
                              END))
   FROM   jsonb_array_elements(filter_data->'task_packets') elem
   )
WHERE  filter_data @> '{"task_packets": [{"task_id": 1001}]}';

The same can be implemented with a correlated subquery in the UPDATE (or in the SELECT as well).

To make this fast for big tables, be sure to have an appropriate index, ideally a jsonb_path_ops index:

What's the proper index for querying structures in arrays in Postgres jsonb?


Constrain input to a few different strings Hi I can't seem to get a constraint working the way I expect in postgreSQL.
From within pgadmin I execute the following SQL query.

-- Check: "TypeCheck"

-- ALTER TABLE "ComLog" DROP CONSTRAINT "TypeCheck";

ALTER TABLE "ComLog"
  ADD CONSTRAINT "TypeCheck" CHECK ("Type" SIMILAR TO 'email|post|IRL|minutes');
COMMENT ON CONSTRAINT "TypeCheck" ON "ComLog" IS 'One of email|post|IRL|minutes';

When executed this is converted to.

-- Check: "TypeCheck"

-- ALTER TABLE "ComLog" DROP CONSTRAINT "TypeCheck";

ALTER TABLE "ComLog"
  ADD CONSTRAINT "TypeCheck" CHECK ("Type" ~ similar_escape('email|post|IRL|minutes'::text, NULL::text));
COMMENT ON CONSTRAINT "TypeCheck" ON "ComLog" IS 'One of email|post|IRL|minutes';

I expect this to limit my input for the Types column to one of email post IRL or minutes. However when entering table data this constraint fails when i enter one of these types.
The Types column is of character type.
Does anyone know how to fix this.
Thanks.

Change your constraint to

CHECK (type IN ('email','post','IRL','minutes'))

This will be converted by the parser into:

CHECK (type = ANY( ARRAY['email','post','IRL','minutes']))

That should do what you are looking at.

However I have to wonder if it wouldn't be better to do this:

CREATE TABLE comlog_types (
     type text
);

And then add a foreign key to enforce the constraint.  This would make it easier to add types in the future.


Is processing required dimensions enough or to process the entire cube? I have four large dimensions and one of the dimension is not going to be changed, also the particular dimension didn't need to be processed.

So, processing the three dimensions individually updates the cube or do I need to process the entire cube every time (which is processing all the dimensions) ?

I think you are confusing things.

First of all, processing the cube and processing the dimensions is sort of separate. I think you mean "processing the entire database".

If you process only dimensions, and not the cube, the measures will never be updated.

You could Process Update the dimensions which wouldn't unprocess the cube but would drop the indexes and flexible aggregations so you would need to Process Index in the same transaction (to avoid the indexes being created on data that may have changed since the process dimension).

Also if you fullprocess dimensions, the cubes where the dimensions are linked will be in an "unprocessed" state.

On the other hand. You don't necessarily need to process the entire database, If you are absolutely sure nothing in one of the underlying dimension tables changes you can get away with just processing some of the dimensions and the cube without processing the entire database.

On the other hand, what makes you ask this question? It's certainly not something I would recommend as there are probably better solutions, such as processing incremental which won't do a lot if nothing has changed.


SQL Server database level audit filtering does not work I have SQL Server 2014 Enterprise Edition installed. Due to privacy considerations, SELECT statements on private data need to be logged, except for a specific domain user, used by a service which writes to the database.

Creating a database level audit specification works well. E.g.

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-01]
FOR SERVER AUDIT [My_Audit]
ADD (SELECT ON SCHEMA::[dbo] BY [public])
WITH (STATE = OFF)
GO

causes all SELECT statements from everyone to be written to the log file. When I replaced SCHEMA::[dbo] with [MyTable], the log was correctly filtered for SELECTs on MyTable only.

But when I replaced public by specific user name MYDOMAIN\myuser, the log entries are not filtered for the given user name as I would have expected.

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-01]
FOR SERVER AUDIT [My_Audit]
ADD (SELECT ON SCHEMA::[dbo] BY [MYDOMAIN\myuser])
WITH (STATE = OFF)
GO

Every SELECT issued by any user was logged instead.

I queried sys.server_principals to check whether the given user was a valid principal name, but it actually showed up in the result.

What is wrong with my second audit specification?

Edit: It seems like if the CREATE statement in question has been silently discarded. When issuing Script Database Audit Specification as ... in SQL management studio, the original statement is written to the query editor window.
However, changing the value to others than user names (e.g., replacing public by dbo) worked well.

In my case, MYDOMAIN\myuser was a server principal, not a database principal. Only the latter ones can be used in database audit definitions.

Database principals can be either database users, or roles (see documentation). I created a new database role to be used in the audit definition. Then I created the logins for the users in question and chose the apt role membership under User mapping.

Hint: For some reasons, it seems necessary to define the role prior to adding the logins for the users. Adding existing logins as role members did not work for me.


Transaction log changes not synchronizing with AlwaysOn SQL Server 2012 Enterprise in an AlwaysOn configuration.  The Availability Group for this specific DB is synchronized and healthy.

I had to add a second transaction log file (logfile2) to the primary.  The file did not replicate until I restarted the instance (was doing other maintenance).
After the instance was restarted the transaction log appeared at the primary and secondary.  12 hours have passed since the instance was restarted and the log files show the same time/datestamp AND file size.  Good so far ...

It appears however that transactions hitting logfile2 on the primary are not being synchronized to the secondary for over 20 minutes.  In addition to the normal transaction log backup that runs every 2 hours, which includes a CHECKPOINT command, I had to install a SQL agent job that performs a
CHECKPOINT on the DB every 15 minutes.  The reasoning is that transactions going to logfile2 are languishing there and not getting applied to the database; so, I force the flush of the log files to the database, which then synchronizes to the secondary.

Can anyone shed some light on this behavior?
(Please hold your comments on having multiple log files.  Unless you've walked a mile in my shoes ...)

Can anyone shed some light on this behavior?

The behavior you're describing is not working as described. The end result is the same but the inner workings are different than what you're thinking.

The Availability Group for this specific DB is synchronized and healthy.
I had to add a second transaction log file (logfile2) to the primary. The file did not replicate until I restarted the instance (was doing other maintenance). After the instance was restarted the transaction log appeared at the primary and secondary. 12 hours have passed since the instance was restarted and the log files show the same time/datestamp AND file size.

Restarting the instance didn't actually fix anything. If the databases were synchronized then the log blocks were hardened on the secondary. This doesn't mean there were redone yet, jus that they were hardened to disk.
By restarting the instance you've effectively forced redo to complete to a point and thus creating the second log file. If it is taking this much time to redo log blocks, there may be an issue with how much load the secondary could handle and this is one of the ways you'll see it manifest.
The last item that is indicative of this behavior is a blocked redo thread, which is most likely the cause.

It appears however that transactions hitting logfile2 on the primary are not being synchronized to the secondary for over 20 minutes.

Then the AG wouldn't be synchronized, it'd change to synchronizing. This is more likely what I descried above, where the log blocks were hardened and weren't yet redone (blocked redo thread).
Assuming the log blocks were sent, the AG is synchronized, and the redo thread was not blocked... this could be due to the transaction still being open. Until the transactions is committed (trancount = 0) your queries on the secondary won't see it as it isn't a yet committed transaction.
Assuming the transaction was committed (trancount = 0), the default isolation level of read committed is mapped to the snapshot isolation level on the secondary. This could also be due to isolation level changes and the query running.

In addition to the normal transaction log backup that runs every 2 hours, which includes a CHECKPOINT command, I had to install a SQL agent job that performs a CHECKPOINT on the DB every 15 minutes. The reasoning is that transactions going to logfile2 are languishing there and not getting applied to the database; so, I force the flush of the log files to the database, which then synchronizes to the secondary.

This is not how the process works. Checkpoint itself doesn't flush log records, they may need to be flushed from the log buffer because of WAL (Write Ahead Logging) enforcement for the data pages being flushed but it will do nothing to the log file. The setup done with the agent job and checkpoint is not helping one bit.
The unit of transport is a log block, not individual records. When log blocks end and are flushed to disk, if the database is part of AlwaysOn Availability Groups it is copied into (depending on version) another buffer and eventually sent through the transport when the secondary checks in. This is a gross over simplification but shows how it works.
What to do next?

Checked for blocked redo threads on the secondary.
Make sure the AG is synchronized and not synchronizing
Make sure the transactions have @@TRANCOUNT = 0
Make sure the secondary has enough disk IOPs to handle the load without growing the redo queue size


How to properly align columns in SQLite? I recently began learning with SQLite, and I am trying to output the data of a table into the Terminal (macOS). However, the result after I've set .mode column or .mode tab is not properly aligned when some columns have no data, such in the second entry.

For example, in column mode where Donna doesn't have age and place data:

name            surname             age         place
--------------  ------------------  ----------  ------------------
John  Smith  34          assistant manager
Donna  Patterson

and in tab mode:

name surname  age   place
John Smithonian     34 assistant manager
Donna Patterson

but the desirable result is:

name            surname             age         place
--------------  ------------------  ----------  ------------------
John            Smith               34          assistant manager
Donna           Patterson

Is there anything I am not aware of? All the examples in the book I study from depict all columns properly aligned.

The same thing happens with tab mode.

I am working through Terminal.app on a mac with the pre-installed 3.8 version of SQLite

In later tests, I discovered that if my columns are all filled with information, it is possible to have all data aligned or not. It depends on the information. When I added a Phone Number column to one of the examples, the nice arrangement was lost.

With .mode tab the output is not guaranteed to be aligned because af the nature of the TAB characters. On the other hand .mode column should be working fine.

Since it looks like empty fields for NULL values are creating problems, you can change the way NULL values are displayed with .nullvalue SOMESTRING. I personally like .nullvalue [NULL].

Summing it up:

sqlite> .mode column
sqlite> .headers on
sqlite> .nullvalue [NULL]
sqlite> SELECT * FROM people;
name            surname             age         place
--------------  ------------------  ----------  ------------------
John            Smith               34          assistant manager
Donna           Patterson           [NULL]      [NULL]

Also be sure to check the .width option to set the number of characters per column (negative values to right-align, 0 to reset) in case you need it.


MongoDB map-reduce impacts insert performance on source collection I have a "hot" MongoDB(4.0.5) capped collection with a timestamp (and several other) indexes.  Many applications (Java, NodeJS & C++) insert (never update) records into this collection at a rate of ~50 inserts/second.  Normally these inserts are very quick and take only a few milliseconds.

When I run a map-reduce query on the most recent 5 minutes of data, calls to insert new records into the source collection are blocked or delayed.  New inserts during the map-reduce call can be delayed > 10 seconds which is roughly the time that it takes to run the map-reduce operation.  The output from the map-reduce operation is written to the same database.

What can I do to tune MongoDB or the map-reduce operation so that inserts can continue to flow into the capped collection without delay while the map-reduce operation runs?  I need to get the summary analysis in near real-time so I don't want to wait until the insert load is reduced before running the analysis.

I do not believe that this is a system level resource constraint.  I have monitored CPU, network, i/o and memory during the map-reduce call and CPU idle time remains > 50% and network and i/o latency remains low.  IO/wait times remain under 5%.

Maybe setting the nonAtomic: true flag could help.

Or you can try the Map-Reduce alternatives:

Views
Tailable Cursors
Change Streams


Does HBase support spatial functionality? I see mention for spatial functional in HBase. For example "HBaseSpatial: A Scalable Spatial Data Storage Based on HBase".

What spatial functionality does HBase support and where is this documented?

HBase is simply a non-relational database that runs on HDFS which is essentially a filesystem abstraction layer on top of Hadoop which is map-reduce framework.

  Apache HBase provides Bigtable-like capabilities on top of Hadoop and HDFS.

So the breakdown is like this,

HDFS is a distributed file system that is well suited for the storage of large files. Its documentation states that it is not, however, a general purpose file system, and does not provide fast individual record lookups in files.
HBase, on the other hand, is built on top of HDFS and provides fast record lookups (and updates) for large tables. This can sometimes be a point of conceptual confusion. HBase internally puts your data in indexed "StoreFiles" that exist on HDFS for high-speed lookups.

Moreover, HBase doesn't support data types,

  HBase supports a "bytes-in/bytes-out" interface via Put and Result, so anything that can be converted to an array of bytes can be stored as a value. Input could be strings, numbers, complex objects, or even images as long as they can rendered as bytes.

So If you want to store GIS objects you would simply ignore HBase and use the underlying Hadoop MapReduce framework with Spatial Hadoop.

HBaseSpatial is just a dead research project. There are millions of them. It's quite possible the source code was never published. The only entry on GitHub for it is here. That's from the author of the paper too, Ningyu Zhang


Copying (hundreds of) tables from one server to another (with SSMS) I have several hundred (currently 466, but ever growing) tables I have to copy from one server to another.

I have never had to do this before, so I'm not sure at all on how to approach it. All the tables are in the same format: Cart<Eight character customer number>

This is part of a larger project of which I am merging all these Cart<Number> tables to one Carts table, but that's a whole different question altogether.

Does anyone have a best-practice method I can use to copy all these tables over? The database names on both servers are the same, if that helps. And as I said earlier, I have the sa account so I can do whatever is necessary to get the data from A to B. Both servers are in the same server farm, as well.

Here's a quick & dirty approach that only needs a linked server, in each direction, with sufficient privileges, collation compatibility, and data access enabled. You run this on the source linked server to generate the dynamic SQL that will be executed on the destination linked server.

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'SELECT * INTO [database].dbo.' + QUOTENAME(name)
  + N' FROM [source_linked_server].[database].dbo.' + QUOTENAME(name) + N';'
FROM sys.tables
WHERE name LIKE N'Cart[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]';

PRINT @sql; -- this will only print 8K, enough to spot check
--EXEC [destination_linked_server].master.sys.sp_executesql @sql;


How does a database use the SYS schema even when we change the password? From the thread What is the difference between sys and system accounts in Oracle databases?, I understand that sys schema is supposed to be used only by the database.

But still an administrator can change the password for the schema.

Can the database connect without the password? If not, how does it connect even after the password is changed?

The database doesn't bother authenticating itself to itself.

As the database does not use the password identification strategy when performing operations, changing the SYS password only impacts how humans can use the account.


Is there a timeout for a query over database link? Edit/Preface:
This question has been migrated from SO as I am particularly interested on the question on timeouts on DB link queries. The provided workaround from SO is somewhat OK, but I'm really interested in the question itself.

Motivation:
I've had one query running "forever" (more than 2 days, until I killed the session), which was using a database link.
Problem seemed to be that the remote database became unavailable and for some yet unkown reason no ORA-02068 was raised (not to be discussed here) and the query just waited and waited.

(The query is issued by a dbms_scheduler job, which executes a procedure in a PL/SQL package. As a consequence, the job was also stuck. But that's not of special interest for the core of this question)

I've simulated this situation by putting one of my test DBs in quiesce mode and queried it over a database link. As anticipated, the query was waiting until either manually canceled or the remote DB was unquiesced.

Question:
I have no control over the remote database's behavior and up-time, so I am in search for some possibility to set a timeout on a query which is using a database link.

I've already looked into profiles (CPU_PER_CALL etc), sqlnet.ora parameters, adding local naming parameters directly into the connect string (such as adding (connect_timeout=10) to the database link definition), running a command with ... for update wait 1, but they either work for busy or idle sessions, but not for sessions in wait.

So I am in search for some option on the "local" side of the database link, which kind of sets a timeout for queries over database links.
Some solution like alter session set xyz or select ... from a@b "wait 100" --(yes, I know this syntax doesn't exist) would be appreciated, as I have no DBA rights on these particular DBs.

I'm currently on 10gR2 but upgrading to 11gR2 in a few weeks, so ideas for any of these versions will be useful.

Since you're using dbms_scheduler, you can set the max_run_duration attribute of the job to some limit, and then have the scheduler email you if that event is raised.  Behind the scenes Oracle uses queueing tables (which can enable you to create jobs that fire when an event is raised, if you wanted to take the extra steps to do more automation around your response).  But basically any job that runs over the max_run_duration you setup will raise event type: JOB_OVER_MAX_DUR

The email piece is built in 11gr2, see here for good writeup.

Hope that helps.


Bloated transaction log and .bak backup size confusion I recently inherited a poorly-managed SQL server.

One of the databases is in rough shape. Here are the details:

The .mdf file is a modest 369MB.
The .ldf file is a bloated 29.7GB.
The recovery model is set to FULL.
The data file is backed up nightly without issue, and is 365MB.
The transaction log has NEVER been backed up.

I've restored the nightly backup to the same SQL server and poked around in it to make sure data looks to be intact. When I restore that nightly .bak backup file it recreates the .mdf and .ldf files at 369MB and 29.7GB, respectively.

Being decent with SQL, but not what I would call an expert by any means, I have a few follow up questions on this situation that I am hoping to get feedback on:

How is a 369MB .bak file restoring both a 369MB .mdf file and a 29.7GB .ldf file?
Any suggestions on what to do regarding the recovery model and bloated .ldf file? We are only concerned about doing backups once nightly - we don't need to do differential backups at this time - but it's something I will tackle later.

How is a 365 .bak file restoring both a 369MB .mdf file and a 29.7GB
  .ldf file?

It's not restoring the data from the log file, or backing it up, it's just creating it with the same size as it was when backed up.  Check the free space and it should be 99% free.

  Any suggestions on what to do regarding the recovery model and bloated
  .ldf file? We are only concerned about doing backups once nightly - we
  don't need to do differential backups at this time - but it's
  something I will tackle later.

If you are only worried about doing backups once a night then change the recovery model to simple.  If you leave it in full you will need to perform log backups as well.  Once you switch the recovery model or start performing log backups you could do a one time shrink to get back the space.


Install SSDT for VS 2017 - Missing Integration Services I have downloaded and installed SSDT from Microsoft docs. The install was successful and I now have SSDT for VS 2017 installed - version 15.8.9 (as per screenshot)

However, I cannot create an SSIS project. There is no option in SSDT to do so.

When I try to open an old version of an SSIS package I get the below 'Unsupported'...

What else am I missing?

Repairing the installation of SSDT resolved the issue.


Postgres 11 JSONB - How to get all distinct values of a field across all elements I have a table called Shipments:
CREATE TABLE shipments
(
      shipmentId numeric,
      metadata jsonb
);

Inside the metaData column there can exist an array of JSON objects named stops which might look something like this:
{
  "stops": [
    {
      "stopId": 1,
      "stopType": "Origin"
    },
    {
      "stopId": 2,
      "stopType": "Destionation"
    },
    {
      "stopId": 3,
      "stopType": "Transit"
    }
  ]
}

I'm trying to query across the table and get all the distinct stopType values. I can get all the distinct stopType values for the first index in the stops array via this simple query:
select distinct metadata->'stops'->0->>'stopType' from shipments

This almost gives me what I need:
╔══════════╗
║ stopType ║
╠══════════╣
║ Origin   ║
╚══════════╝

But what I want is:
╔══════════════╗
║ stopType     ║
╠══════════════╣
║ Origin       ║
║ Destination  ║
║ Transit      ║
╚══════════════╝

Thanks in advance for your help!

Use jsonb_array_elements_text:
SELECT DISTINCT a.e ->> 'stopType' AS "stopType"
FROM shipments AS s
   CROSS JOIN LATERAL jsonb_array_elements_text(s.metadata->'stops') AS a(e);