texts: Finding Columns causing errors in SQL Developer

texts:
I'm relatively new to Oracle SQL and trying to import CSVs using SQL Developer. I've been getting errors such as

Insert failed for rows 1351 through 1400
ORA-01438: value larger than specified precision allowed for this column

Is there a way to determine the Columns that this error message concerns (?), so I can examine the values in the CSVs to see what is causing the error and how I should change the DDL.
Thanks!


texts: SQL Server Management Studio 2016 (Release 16) keeps on crashing

texts: After updating to Release 16, my SSMS keeps on disconnecting automatically and brings up the Connect to Database Engine pop-up (screenshot below). Additionally, I keep on getting the "System.OutOfMemoryException" when I run queries, even though my machine has 16GB RAM and is only currently using about 4GB. These errors happen with simple queries like SELECT GETDATE(); Any suggestions on what could be the issue?


texts:
I am trying to connect my node server with mongodb with ssl options enabled.

mongod.conf

storage:
  dbPath: /var/lib/mongodb
  journal:
  enabled: true

# where to write logging data.
systemLog:
  destination: file
  logAppend: true
  path: /var/log/mongodb/mongod.log

# network interfaces
net:
  port: XX
  bindIp: 127.0.0.1,XXX.XX.XX.XXX
  ssl:
    mode: requireSSL
    PEMKeyFile: /etc/ssl/PEMKEYFILE.pem
    PEMKeyPassword: "PASSWORDFORPEM"
    CAFile: /etc/ssl/CAFILE.pem

security:
  authorization: enabled

replication:
  oplogSizeMB: 1024
  replSetName: "REPLSET"

node server conf file

MONGO_URL: '<pathToMongoDb>/<db>',
        MONGO_REPLICA_SET_READ_PREFERENCE: 'nearest',
        MONGO_REPLICA_SET: {
            ssl: true,
            sslValidate: true,
            sslKey: fs.readFileSync('/etc/ssl/mongodb.pem'),
            sslCert: fs.readFileSync('/etc/ssl/mongodb.pem'),
            sslCa: fs.readFileSync('/etc/ssl/CAFILE.pem'),
            replicaSet: 'REPLSET',
            auto_reconnect: false,
            poolSize: 24,
            socketOptions: {
                keepAlive: 1000,
                connectTimeoutMS: 30000
            }
        },
        MONGO_SERVER: {
            ssl: true,
            sslValidate: false,
            checkServerIdentity: false,
            sslKey: fs.readFileSync('/etc/ssl/mongodb.pem'),
            sslCert: fs.readFileSync('/etc/ssl/mongodb.pem'),
            sslCA: fs.readFileSync('/etc/ssl/CAFILE.pem'),
            poolSize: 24,
            socketOptions: { keepAlive: 300000, connectTimeoutMS: 30000 },
            reconnectTries: Number.MAX_VALUE,
            reconnectInterval: 1000
        },
        MONGO_CHECK_SERVER_IDENTITY: false,

mongoose file

mongoose.connect(config.MONGO_URL,{
  db: {
      native_parser: true,
      readPreference: config.MONGO_REPLICA_SET_READ_PREFERENCE
  },
  server: config.MONGO_SERVER,
  replset: config.MONGO_REPLICA_SET,
  user: config.MONGO_USER,
  pass: config.MONGO_PASSWORD,
  auth: {
      authdb: config.MONGO_AUTHDB
  }

})

I have created the CA certificate and and signing certificate but the node server is returning me with the error :

  MongoError: failed to connect to server [XX.XXX.XXX.XX:port] on first connect

  MongoError: self signed certificate in certificate chain

I am able to connect to the mongo using shell using the following command

mongo XX.XX.XX.XXX:XX/<db> -u <user> -p <pass> --authenticationDatabase=admin --ssl --sslPEMKeyFile=/etc/ssl/PEMKEYFILE.pem --sslCAFile /etc/ssl/CAFILE.pem --sslAllowInvalidHostnames`

Have asked the same question on SO but was told to post it here as peple here might help.
Please suggest me where I am going wrong.


texts:
Given the syntax rules shown below

Access: IF(Condition, ReturnIfTrue, ReturnIfValue)
T-SQL: CASE WHEN Condition THEN ReturnIfTrue ELSE ReturnIfFalse END

What would the below be formatted to T-SQL be?

(((IIf(IsNull(Production!Product_Title) Or Production!Product_Title Like "Process*",
"Not Started", "Started"))="Not Started") And ((Person.Type) Is Not Null))


texts: Setting locale for Chinese characters in Postgresql

texts:
I am designing a Postgresql database that contains Chinese characters in certain tables (.e.g login, comments). Most tables are perfectly fine with ASCII characters.

From Postgresql Locale Support, the locale of the database can be set during creation. But setting a non-default C locale will degrade performance over sorting. Is it possible to specify locale per table or per column in Postgresql?


texts: Nested case statements vs multiple criteria case statements

texts:
Had an interesting discussion with a colleague today over optimizing case statements and whether it's better to leave a case statement which has overlapping criteria as individual when clauses, or make a nested case statement for each of the overlapping statements.

As an example, say we had a table with 2 integer fields, column a and column b. Which of the two queries would be more processor friendly? Since we're only evaluating if a=1 or a=0 once using the nested statements, would this be more efficient for the processor, or would creating a nested statement eat up that optimization?

Multiple criteria for the case statement:

Select
case
    when a=1 and b=0 THEN 'True'
    when a=1 and b=1 then 'Trueish'
    when a=0 and b=0 then 'False'
    when a=0 and b=1 then 'Falseish'
    else null
end AS Result
FROM tableName

Nesting case statements:

Select
case
    when a=1 then
        case
            when b=0 then 'True'
            when b=1 then 'Trueish'
        end
    When a=0 then
        case
            when b=0 then 'False'
            when b=1 then 'Falseish'
        end
    else null
end AS Result
FROM tablename


texts: Is to possible to forbid access to tables in own schema - Oracle?

texts:
Lets assume that we have the following situation:
We have an Oracle schema/user: USER_TEST (with the "create session" privilege).
SYS as SYSDBA creates couple Tables on schema USER_TEST. Is it possible to forbid access for the user USER_TEST to tables on his schema?


texts: Beginner questions about SQL and relational algebra

texts:
I'm doing some exercices waiting for the exam. I have some doubt.

RELATIONAL ALGEBRA

  These are the table:

    Newspaper (Cod_n, Name_n, Publisher)

    Article (Cod_a, Title, Topic, Cod_N)

  Request : Find out the name of the newspapers that have published a topic deal with motorcycle at least

  My solution: π_name ( σ_topic = "motorcycle" (newspaper naturaljoin article))

  Book's solution: π_name (newspaper naturaljoin σ_topic = "motorcycle" (article))

Are the solution equivalent? Is the only difference between them the "cost of the operation" ?

  The table is the same of the previous exercice.

  Request: Find out the name of the newspapers that have never published a topic deal with motorcycle

  My solution:   π_name_n (newspaper naturaljoin (σ_topic notegual "motorcycle" (article)

  Book's solution:   π_name_n(newspaper naturaljoin (π_cod_n(newspaper) - π_cod_n (σ_topic = "motorcycle" (Article)

  Are the solution equivalent?

SQL

  These are the table:

    Student(matriculation_number, surname, name)

    Exams(Student, subject, grades, date)

  Request: Find out students (showing matriculation number) that have passed two exams after 1/1/2000 at least

  My Solution:

    select distinct matriculation_number

    from student join exams on matriculation_number = student

    where data > '2000-01-01'

    EXCEPT

    select distinct matriculation_number

    from student join exams on matriculation_number = students

    where data =< '2000-01-01'

  Book's solution:

    select distinct e1.student

    from exams e1 join e2 on e1.student = e2.student

    where e1.subject < > e2.subject

    and e1.date > '2000-01-01'

    and e2.date > '2000-01-01'

Is the solutions equivalent?

Thank you in advantage for your time.
I'm sorry I don't know how to insert relational algebra's symbol.


texts: Migration from SQL Server 2012 Ent to SQL Server 2014 STD

texts:
We recently migrated from a traditional hosting environment with a back end of SQL Server 2012 ENT to an Azure VM running SQL Server 2014 STD. We did all the cool stuff with BPEs on SSD, etc. The problem is, performance is down.  Specs of the old "server" and the new "VM" are identical (in theory, it is azure...).

Question being - I have run the following scripts on staging DBs in the new environment and seen improvement, but I am generally afraid to run these scripts in production.  Is there any real risk in running the following?  It seems to solve the problem and get SQL Server 2014 to work with the data much better.

EXEC sp_MSforEachTable 'ALTER INDEX ALL ON ? DISABLE'
GO

EXEC sp_MSforEachTable 'ALTER INDEX ALL ON ? REBUILD'
GO

EXEC sp_MSforeachtable @command1='UPDATE STATISTICS ? WITH FULLSCAN';
GO


texts: Write a query which add two fields of a document and compare their sum to third field in mongo

texts:
I have one document, say:

{
        "_id" : ObjectId("538b7128cf16e3ea5a1ab23c"),
        "pgDownloadTime" : NumberLong(9776),
        "pgAccessTime" : NumberLong("1401647400058"),
        "expiredTime" : NumberLong("1401647468678"),
        "contentType" : 1,
        "articleId" : "35909002",
}

My query will be:

db.pageTrendLog.findOne({"expiredTime" : {$lte : "pgDownloadTime"+"pgAccessTime"}});

How can I write this query?


texts: How can I time SQL-queries using psql from command line?

texts: There is already a question "How can I time SQL-queries using psql?" but I am missing answer how to do that from command line. How to run script with (optional) timing from command line – without \timing [on|off] in the script, please?


texts: High PAGELATCH_* and WRITELOG waits. Are they related?

texts:
We are seeing very high PAGELATCH_EX and PAGELATCH_SH wait types along with high WRITELOG waits. I've diagnosed the query causing the PAGELATCH waits and can eliminate them by reducing the insertion rate into a busy clustered primary key defined with an IDENTITY value. I understand that this phenomenon is known as last page insert latch contention.

However my question is when a new record is inserted, does SQL Server take an exclusive PAGELATCH_EX on a buffer page, insert the record to the buffer page, write the record to the transaction log and then release the exclusive PAGELATCH_EX as detailed https://www.microsoft.com/en-ie/download/details.aspx?id=26665 Page 24. Or does it write the record to the transaction log first before taking the PAGELATCH_EX as detailed "Resolving PAGELATCH Contention on Highly Concurrent "INSERT Workloads - Background information SQLCAT's Guide to: Relational Engine

If the record is written to log outside of the latching mechanism then I can rule out slow writes to disk as a cause of high PAGELATCH waits. But if the latch is held until the record is hardened to log then I should probably take WRITELOG into consideration.

Also would having multiple non-clustered indexes cause the PAGELATCH_* latch to be held for longer i.e if a table has a clustered and multiple non clustered indexes are latches added and released to each of the index buffer pages concurrently?

Update 1
After reading confio-sql-server-writelog-wait slide two and general WAL architecture. I am now of the understanding that the "Record a log entry that the row has been modified" step detailed in both white papers is referring to SQL Server logging a change in the transaction log cache, not disk. Once the transaction is complete or buffer full all records are immediately flushed to disk.


texts: Dynamic Partition on tabular model 1200 using visual basic 2015 in visual studio

texts: I am using visual studio 2015, sql server 2016 and tabular model 1200. I am trying to create a dynamic partitions on the existing cube by creating SSIS package using script task and visual basic coding. So can any one specify the approach to create a dynamic partition every month and process the created partition every night. I searched through online and I didn't find any example with this environment.


texts:
SQL>  select FILE_NAME,TABLESPACE_NAME from dba_temp_files;
                                   *

  ERROR at line 1: ORA-01157: cannot identify/lock data file 201 - see
  DBWR trace file ORA-01110: data file 201: '/oradata_nocg/temp01.dbf'

In the database I don't have '/oradata_nocg/temp01.dbf', I am not doing any operation related temp tablespace  Why it is not allowing run above select query. ?


texts:
Copied to file from (XAMPP) /Applications/XAMPP/xamppfiles/var/mysql
 to /Applications/AMPPS/var (AMPPS)

But I having this error.

2017-01-25 18:01:55 1860 [Warning] InnoDB: Cannot open table pronc_wp/wp_users from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.

Not only wp_users tables. All tables is give to me this error.


texts:
I want to preface this post by saying that I am a complete newbie with SQL and any help will be highly appreciated.

For some project I am working with high frequency stock data, below is a small sample of what it looks like in the SQLite database

SYMBOL  DATE        TIME        PRICE
AAPL    19990602    15:59:54    46,50000
AAPL    19990602    15:59:59    46,56250
AAPL    19990602    16:00:00    46,56250
AAPL    19990603    09:30:03    46,87500
AAPL    19990603    09:30:04    46,81250
AAPL    19990603    09:30:05    46,84375

I do not want to analyze this on a second basis but rather on a 5 minute basis. I want to take the 5 minute averages from this day. So I want to take the average from

9:30:00 - 9:34:59 -> 5 min average price for 9:35:00 (first point)
9:35:00 - 9:39:59 -> 5 min average price for 9:40:00
...
15:55:00 - 15:59:59 -> 5 min average price for 16:00 (last point)

My first idea (and possibly not the most efficient one) was to make a list of each second with the corresponding index of the 5 minute interval it belongs to

TIME    SEGMENT
09:30:00    1
09:30:01    1
09:30:02    1
   …        …
09:34:59    1
09:35:00    2
09:35:01    2

I was wondering if anyone of you could answer this question or point me in the right direction towards which SQLite commands I should look into as this is my first encounter with SQlite and I feel a bit overwhelmed.


texts: Are postgres connection able to manage multiple requests at the same time?

texts:
I have an application using node Postgres driver to connect to my Postgres 12.
The pool size is currently 1.
Is it possible, for one connection, to have multiple requests sent before receiving the responses?
Figure two requests:

request A will last 10 seconds to be processed by the database,
request B will last 1 second.

If this is possible, that request B will be answered before request A?


texts: Postgres psql requires " FROM database.table" in sql statement

texts:
As a beginning learner of of Postgres I have created two different installations of a database where one requires a select statement to use ...FROM database.table... and the other only requires ...FROM table.... This was discovered in a Symfony project that could point to either installation. The project fails when pointing to the installation requiring ...FROM database.table... How can the requirement to include the database name with a table name be removed, and where might that requirement have come from?
Background:
The project began with a MySQL database. Since I wanted to learn Postgres I followed some great material found online to convert a MySQL database to Postgres. The MySQL data was in Windows, Postgres was installed on an Ubuntu 18 VM. (The conversion created the Postgres version on the VM.) The VM's database was dumped with pg_dump, the resulting sql file copied to Windows and restored there. All of this in MySQL 8 & Postgres 12.
When the Symfony project points to the VM's database, all is fine and dandy. Pointing to the Windows copy fails because it cannot find any given table.
On the VM I can use psql to query using ...FROM table.... In Windows with psql, even though SELECT current_database(); returns the name of the database, I must use ...FROM database.table... in a query.


texts: Why does this Entity Framework query perform so bad in MySQL?

texts:
Let me give you a little setup.  We have a InnoDB table on MySQL 5.1 with close to 20 million records, no foreign keys, and proper indexes for queries we do.  We are using the latest 6.3.5 MySQL release for the .NET Entity Framework.  Normally we are use to dealing with SQL Server and the Entity Framework, but on this project we decided to give MySQL a try.

I have some theories on the issue but let me first do the code setup

EF LINQ Query

var failsForAcct1001 = db.Failures.Where(x => x.AccountId == 1001);
/* farther down and later on in the code */
return failsForAcct1001.OrderBy(x => x.FailureId).Take(50);

MySQL Code Generated

Please ignore the column names they aren't nessisary to understand the problem

select
    External1.FailureId,
    External1.col2,
    External1.col3,
from (select
          Inner1.FailureId,
          Inner1.col2,
          Inner1.col3,
      from Failures Inner1
      where External1.AccountId = 1001
    ) External1
order by External1.FailureId
limit 50

This SQL being generated is very similar to what happens in SQL Server, and SQL handles it with out a problem.  And this isn't just an Entity Framework issue, when I take this query and execute in MySQL Workbench it also times out.  However the following query returns in under a half second.

select
    External1.FailureId,
    External1.col2,
    External1.col3,
from Failures External1
where External1.AccountId = 1001
order by External1.FailureId
limit 50

I assume this performance problem has to do with the fact that MySQL is trying to execute the inner query which pulls back all the records in the database and then tries to sort and take 50, instead of doing this all as one operation like the second query does.

I really just want to confirm my observations, and ask if there is anything I can do to get the first SQL statement to execute faster, without modifying the query in any way.


texts: Veeam HotAdd causing Worker Thread Exhaustion

texts:
I have a SQL 2012 Always ON AG with 2 nodes and a file share witness.  We have 207 Customer DBs in the AG on a box with 12 Cores and Instance configured for 60 GB RAM.  I have 1 Secondary configured for Synchronous Mode locally.

Each night we do full backups of those 207 DBs on the secondary.  What is strange is we also do Veeam backups of the SQL Server (not any application-aware processing or anything as I want to handle backup chain myself) each night we end up with worker thread exhaustion which terminates the AG and 30 seconds later it comes back.  I can’t imagine the Veeam backup process or the VMWare Snapshotting process would use any SQL worker threads but maybe I am missing something?

Why does Veeam HotAdd cause worker thread exhaustion? Have you ever come across this?  I even changed the Veeam backup time and the worker thread exhaustion time moved with it.


texts: Why is this (postgres) composite gin(scalar, scalar, array gin__int_ops) index not using the array criteria?

texts:
I have a table
create table r_prot_search(
    openprot_release_id integer,
    annotation_group_sig text,
    dataset_ids integer[]
);

With a composite index:
create index my_index on r_prot_search using gin(
  openprot_release_id,annotation_group_sig,dataset_ids gin__int_ops
);

The table contains a huge number of rows, and the following query is not filtering using the criteria on column "dataset_ids"
explain
  select count(*)
  from r_prot_search q
  where q.openprot_release_id = 75
    and q.annotation_group_sig = '117:118'
    and 195 = any (q.dataset_ids);

Aggregate  (cost=2211461.49..2211461.50 rows=1 width=8)
  ->  Bitmap Heap Scan on r_prot_search q  (cost=9619.83..2211459.47 rows=810 width=0)
        Recheck Cond: ((openprot_release_id = 75) AND (annotation_group_sig = '117:118'::text))
        Filter: (195 = ANY (dataset_ids))
        ->  Bitmap Index Scan on idxps_ds_2_15  (cost=0.00..9619.62 rows=837562 width=0)
              Index Cond: ((openprot_release_id = 75) AND (annotation_group_sig = '117:118'::text))

Any idea how to improve performance ?


texts: Before and After update trigger on same table

texts:
I have a table with 4 columns qty, qtydiff, price and value.

I want a after update trigger that fires when qty row values change (more than one rows).

The Before trigger should read and store the old values from qty and After trigger should subtract old value from new value on qty column, get the difference value in qtydiff and multiply that with price column and update the result in the value column.

The code below is just a concept code that I have not tested yet, I am not sure it would work or not but is there a way to get before and after update to work in the same trigger?

 CREATE TRIGGER [dbo].[Price_Modified]
       ON [dbo].[STOCK]
       BEFORE UPDATE ON STOCK
    AS BEGIN
        SET NOCOUNT ON;
        IF UPDATE (Qty)
        BEGIN
            SELECT qty
            FROM STOCK
        END
AFTER UPDATE
    AS BEGIN
        SET NOCOUNT ON;
        IF UPDATE (Qty)
        BEGIN
            UPDATE Stock
            SET value = price * qtydiff
            FROM STOCK
            WHERE qtydiff = oldvalue - newvalue
        END
    END


texts: How to configure port for listening to traffic meant for PostgreSQL

texts:
When setting up postgreSQL with a webserver (all on the same machine), is it critical to have 0.0.0.0:5432 open, or is just 127.0.0.1:5432 enough? Why or why not?

Overall, I'm trying to understand the role ports play in forwarding traffic to the pg service.

The background is that I'm trying to configure pgbouncer (a connection pooler) to work with postgresql, so need to ensure the relevant ports are open. However before I do that, I need to understand the way networking works at this level.


texts: Is this a good case for multiple SQL Server instances on a VM?

texts:
We have a suite of applications utilizing SQL Server have the following characteristics:

Users create/destroy DBs via a UI, so DBs appear/disappear without DBA assistance.
DBs of the same name can be created for each of the applications (because they're related to the same subject matter and the apps are used by the same set of people)
DBs are many, but generally small.  For example ~70 DBs totaling 28 GB, ~130 DBs totaling 2 GB.
Only a handful of DBs are in use at any one time.

I've been asked about configuring a VM with three instances to support these three applications.  And, while DBAs generally run screaming when the question of multiple instances on a server comes up, I'm wondering whether this is a scenario where multiple instances would be, if not blessed, tolerated.

I do have large physical servers that I run multiple instances on.  But, not VMs.  So, I'm comfortable managing the multiple instances in this fashion although the idea of doing on a VM made me a bit queasy.

Thoughts?


texts:
I am installing SQL Server 2017 on my laptop

I already have SQL Server 2008 Express

I downloaded and installed SQL Server 2017 Developer but when i tried to connect to it the instance i created did not exist for some reason

So I decided to uninstall 2017 and reinstall the express 2017

i uninstalled it

but when i try to reinstall 2017 i get this error

unable to install sql server (setup.exe) exit code 1638

I googled and look at other questions and i found that the reason behind this error is that 2017 is already installed

checked Control Panel uninstall

and i found this

when I try to uninstall it i get this

then this error

what am i doing wrong

how to install any instance of SQL 2017??


texts: Sequential scan with large sub-query filter "never" ends

texts:
I'm using postgres 9.2.4, and run the following query:

explain analyze select * from bubu where id not in
     (select bubu_id from kuku limit 33554431);
                                                             QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on bubu  (cost=913175.15..1041265.77 rows=1761465 width=160) (actual time=37565.575..42569.266 rows=596 loops=1)
   Filter: (NOT (hashed SubPlan 1))
   Rows Removed by Filter: 3511745
   SubPlan 1
     ->  Limit  (cost=0.00..829289.07 rows=33554431 width=8) (actual time=20.528..22363.943 rows=33554431 loops=1)
           ->  Seq Scan on kuku  (cost=0.00..830246.84 rows=33593184 width=8) (actual time=20.528..18741.263 rows=33554431 loops=1)
 Total runtime: 42579.485 ms
(7 rows)

And then the second query, which never ends:

explain analyze select * from bubu where id not in
   (select bubu_id from kuku limit 33554433)

The query without limit also gets stuck

explain analyze select * from bubu where id not in
   (select bubu_id from kuku)

Explain of the stuck query:

explain select * from bubu where id not in
   (select bubu_id from kuku)

                                  QUERY PLAN
------------------------------------------------------------------------------
 Seq Scan on bubu  (cost=0.00..2137396495180.43 rows=1761465 width=160)
   Filter: (NOT (SubPlan 1))
   SubPlan 1
     ->  Materialize  (cost=0.00..1129436.76 rows=33593184 width=8)
           ->  Seq Scan on kuku  (cost=0.00..830246.84 rows=33593184 width=8)
   (5 rows)

The query I actually need is

delete from bubu where id not in
     (select bubu_id from kuku)

Is there any PostgreSQL parameter I can tune to avoid this issue?


texts: Can simultaneous updates and deletes on a set of tables lead to AccessExclusiveLock being taken?

texts:
I am using Postgres 9.3.

I have 2 tables : authn_session and customer. Every authn_session belongs to a customer (and hence has a customer_id as a column which is a FK to customer's id).

Note : These tables contain additional FK references to other table(s) and indexes.

Now, I start 2 different transactions which do the following in the below mentioned order :

Tx :1

BEGIN;
UPDATE customer
    SET customer__created_by =
            (case when customer__created_by = 1 then 5
             else customer__created_by end),
        customer__modified_by =
            (case when customer__modified_by = 1 then 5
             else customer__modified_by end);
UPDATE authn_session
    SET authn_session__created_by =
            (case when authn_session__created_by = 1 then 5
             else authn_session__created_by end),
        authn_session__modified_by =
            (case when authn_session__modified_by = 1 then 5
             else authn_session__modified_by end);

Tx : 2

BEGIN;
DELETE FROM authn_session
    WHERE authn_session__guid IN ('abc3344-ab12-4444-9fdd-f4c5a6f7f210');
DELETE FROM customer
    WHERE customer__id != 0
      AND customer__id = 3
      AND customer__name = 'C2'
      AND customer__domain_name = 'a2.com';

Now, When I look at the locks, using the query mentioned below, one particular row interests me :

locktype | relation     |mode                |tid| vtid| pid |  granted
tuple    | authn_session|AccessExclusiveLock |   | 11/5| 47894| TRUE

Query :

SELECT locktype, relation::regclass, mode, transactionid AS tid,
virtualtransaction AS vtid, pid, granted
FROM pg_catalog.pg_locks l LEFT JOIN pg_catalog.pg_database db
ON db.oid = l.database WHERE (db.datname = 'mY-db' OR db.datname IS NULL)
AND NOT pid = pg_backend_pid();

Now, according to the postgres documentation AccessExclusiveLocks are granted only in case of :

  Acquired by the ALTER TABLE, DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL, and REFRESH MATERIALIZED VIEW (without CONCURRENTLY) commands. This is also the default lock mode for LOCK TABLE statements that do not specify a mode explicitly.

I am not doing any of those things explicitly. Then why is my second process (running the second transaction - I checked it's value in DB) getting an AccessExclusiveLock? And why does the pg_activities_blocked view say that the second process (Delete) is blocked by the first (UPDATE)?

BTW, the result of running these two queries simultaneously (one statement from each query at a time) leads to a deadlock . Am I missing anything that could lead to the second process getting an AccessExclusiveLock on the tuple?


texts:
When i try to restore a database from a dump file using the following command:

 \i C:/pathname/dumpfile.txt

from within my sql shell, it runs the first few lines of the dump file and then I get the following error:

  ERROR:  permission denied to create "pg_catalog.databasename"
  DETAIL:  System catalog modifications are currently disallowed.

This shows the beginning of the dump file. I think the CREATE TABLE command triggers the error.

I'm using postgreSQL Version 11.1.
Any ideas how to solve this?


texts: Force MS SQL Service Account to re-authenticate

texts:
So here's the deal:
- SQLserver.exe running as domain\myserviceaccount

The (network) backups are running based on the SQLserver.exe service, so the network share is accessed as domain\myserviceaccount.

Now, I made some changes to the share (secured with an AD Security Group and domain\myserviceaccount is a member of it). When I run as or map the drive with domain\myserviceaccount everything works fine. However, the backups are failing. The only way to have this to work so far, is to restart the sqlserver.exe service.

Am I missing something here? I tried rebooting the file share server to make sure all sessions were cleared and also tried logging on as myserviceaccount on that server, nothing. Tried a klist purge, 0.

Any idea?
Thanks


texts: Significant performance differences executing alter and grant statements between users with same permissions

texts:
I am experiencing significant performances differences between users with the same permissions when executing alter and grant statements.
I have an automated build system that runs a series of SQL scripts for database changes - mostly alter table, alter procedure, with relevant grant statements.
While trying to change some things in my build process (including running under a different user account), I noticed that the current account running the scripts executes them up to 100x faster than any other user - even users with exactly the same permissions, and belonging to the same Active Directory group. I've tested SQL logins and Windows Authentication, with both results being basically identical.
The build process is run through a C# script in all cases. The script properly opens the connection only once, executes all the statements, then closes. This happens the same for all users. The script itself is called by a batch file, which in turn is called by a CI tool.
The average execution time for the current build user running this script is 12ms per statement, while the average execution time for any other user is 275ms per statement.
What possible causes could be contributing to this performance difference?


texts: Nested stored procedures with use of transactions

texts:
I have a question regarding this tsql-code found in this thread regarding "nested transactions" (unfortunately my reputation is not large enough to comment in that thread):
In what cases a transaction can be committed from inside the CATCH block when XACT_ABORT is set to ON?

Here is a procedure with transactions-check and a savepoint if the @@trancount > 0
But I don't see the difference the savepoint makes, when there is a "raiserror-statement" in the catch block of the procedure. Will this not force the calling code to rollback to the beginning of the calling code, thus ignoring the savepoint?

create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction usp_my_procedure_name;

        -- Insert into table:
           INSERT INTO TABLE1 'Data from usp_my_procedure_name'

        -- Make an error:
           SELECT 1/0

lbexit:
        if @trancount = 0
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction usp_my_procedure_name;

        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
    end catch
end
go

If this procedure is called in a nested way like this

CREATE OuterProc AS
    BEGIN TRY
     BEGIN TRANSACTION

     INSERT INTO TABLE1 'Data from OuterProc code'

     EXEC usp_my_procedure_name

     COMMIT TRANSACTION
    END TRY

    BEGIN CATCH
     ROLLBACK
    END CATCH
END

If I execute the OuterProc, the data 'Data from OuterProc code' will not get inserted.
Then what is the point of the savepoint?

It works if I remove the raiserror-part of the code. Then the 'Data from OuterProc code' gets inserted..

So my question is - what is the point of the savepoint when there is a raiserror-statement in the procedures catch-block?


texts: Why isn't my new index/table being created on the filegroup I specify?

texts:
Results from @@Version

Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64)
Sep 21 2011 22:45:45
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Here is the database definition

CREATE DATABASE [Scratch] ON  PRIMARY
( NAME = N'Scratch_mdf', FILENAME = N'N:\MSSQL\TEST\Primary\Scratch_mdf.mdf' , SIZE = 17664KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
 FILEGROUP [DATA]  DEFAULT
( NAME = N'Scratch_dat1a', FILENAME = N'N:\MSSQL\TEST\Data\Scratch_dat1a.ndf' , SIZE = 14539584KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
( NAME = N'Scratch_dat1b', FILENAME = N'N:\MSSQL\TEST\Data\Scratch_dat1b.ndf' , SIZE = 12016128KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
 FILEGROUP [INDEX]
( NAME = N'Scratch_idx1', FILENAME = N'N:\MSSQL\TEST\Index\Scratch_idx1.ndf' , SIZE = 92864KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
( NAME = N'Scratch_idx2', FILENAME = N'N:\MSSQL\TEST\Index\Scratch_idx2.ndf' , SIZE = 84416KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
( NAME = N'Scratch_idx3', FILENAME = N'N:\MSSQL\TEST\Index\Scratch_idx3.ndf' , SIZE = 84416KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
( NAME = N'Scratch_idx4', FILENAME = N'N:\MSSQL\TEST\Index\Scratch_idx4.ndf' , SIZE = 92864KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
 FILEGROUP [PM_G0]
( NAME = N'PM_data0', FILENAME = N'N:\MSSQL\PDB1\Data\PM_data0.ndf' , SIZE = 768000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 LOG ON
( NAME = N'Scratch_log1', FILENAME = N'N:\MSSQL\TEST\Log\Scratch_log1.ldf' , SIZE = 833024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
GO

ALTER DATABASE [Scratch] SET COMPATIBILITY_LEVEL = 100
GO

Here is the code to create the table and to add a clustered index.  In both cases I'm specifying filegroup PM_G0 but when I check its on filegroup DATA.

CREATE TABLE Scratch.dbo.STUPID_TEST (dummycol int) ON [PM_G0];
CREATE CLUSTERED INDEX ix_test ON stupid_test(dummycol) ON PM_G0;
-- Tried the filegroup name with/without brackets just in case

I'm checking the location of the first heap, then clustered index by using this query:

SELECT o.name objectname, fg.name filegroupname, sdf.name as datafile,i.name indexname, partition_id, partition_number, [rows]
FROM sys.partitions p
INNER JOIN sys.objects o ON o.object_id=p.object_id
INNER JOIN sys.tables t ON o.object_id = t.object_id
INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id
INNER JOIN sys.filegroups fg ON fg.data_space_id = i.data_space_id
INNER JOIN sys.database_files sdf ON sdf.data_space_id=fg.data_space_id
WHERE o.name = 'STUPID_TEST'

Interesting test.  I changed the default filegroup to [index].  Confirmed that [index] was in fact the default index in sys.filegroups then dropped and re-created my clustered index and it still went to the [DATA] filegroup.  Code below

ALTER DATABASE Scratch MODIFY FILEGROUP [index] DEFAULT;
GO
DROP INDEX dbo.STUPID_TEST.IX_test;
CREATE CLUSTERED INDEX ix_test ON stupid_test(dummycol) ON [PM_G0];
GO


texts:
Using Java & JDBC I am storing Strings into a JSONB column in Postgres.  When inserting this works great.

When retrieving values, I am running low on memory in Java because I am retrieving larger quantities and each json/string is about 1MB.  What I would like to do is write a query like this:

SELECT compress( myJsonColumn ) FROM myTable WHERE ...

I have looked around a bit for a compress method in Postgres, but haven't found one.  Any pointers would be appreciated.  Thanks.

Clarification: Just to be clear, I don't want to compress the data in the database.  I think that Postgres will handle this gracefully, my concern is mostly java-heap-space.

I have three components in my architecture.

Clients who need the uncompressed json (and who will handle the uncompression themselves)
A Service that serves multiple clients.  I am trying to protect the service's memory.  The service never needs to see the uncompressed json.
The DB.  I want to store the json in a jsonb field, not as binary so that I can write new queries against the data later if required.


texts:
in a web application where people talk to each other , there will be  a lot of messages , suppose for simplicity that each row in a table is a message ,

do you think if a have 5 million messages that one table is enough for them all ?
or should i have 5 tables of one million each ?

the server used will be a simple vps or small dedicated server with 8Gb of ram ,two cores processor 2.5Ghz frequency , ssd ,


texts: What GUI tools can be used for PostgreSQL in Mac OS X?

texts:
I have recently started using PostgreSQL with my rails app.

I was looking for a SQL GUI to the PostgreSQL database (for Mac).

Anything better than pgadmin3?


texts:
I have a small label printing software that print sequential numbers. It selects the last serialnumber on sql db's and add one.

The problem I have is that I need now two users to print sequential serial numbers.

SQL Select statement:

sql_query.CommandText = "SELECT TOP 1 cast (serialnumber as nvarchar) as serialnumber,
                                indexnumber, versao
                         FROM  etiquetas
                         where partnumber Like '" & partnumber & "'
                         ORDER BY serialnumber DESC"

Sql Insert Statement:

 "if not exists (select serialnumber
                 from etiquetasklc.dbo.etiquetas
                 where partnumber like '" & partnumber & "'
                 and serialnumber like '" & serialnumber & "')
  begin
      INSERT INTO etiquetasklc.dbo.etiquetas(Nome_Posto, data, Lote, serialnumber, partnumber,
                                             Versao, indexnumber, Semana, QRGerado, operador)
      VALUES( '" & posto & "','" & data & "','" & ordemfabrico & "','" & serialnumber & "','" & partnumber & "',
              '" & partnumber2 & "','" & indexnumber & "','" & weekyear & "','" & codigo & "','" & operador & "')
  end "

The problem occurs when they hit the print button simultaneous.

Can anyone help me

I'm no coder and the SQL is wrote based on my limited skills


texts: MySQL / MariaDB SSL Connection without verify CA

texts:
I need to cryptography a connection between client and server.

This is config in my server

MariaDB [(none)]> show variables like "%ssl%";
+---------------------+----------------------------+
| Variable_name       | Value                      |
+---------------------+----------------------------+
| have_openssl        | YES                        |
| have_ssl            | DISABLED                   |
| ssl_ca              |                            |
| ssl_capath          |                            |
| ssl_cert            |                            |
| ssl_cipher          |                            |
| ssl_crl             |                            |
| ssl_crlpath         |                            |
| ssl_key             |                            |
| version_ssl_library | OpenSSL 1.0.1t  3 May 2016 |
+---------------------+----------------------------+

Its possible to authenticate with SSL without verify the client certificate with server CA (ssl_ca) ?


texts: SQL Server White Papers vs Books Online - What's the difference and where are the white papers?

texts:
I've heard people reference white papers.  After doing a preliminary search for SQL-Server white papers, there are various sites with random pdfs.  I'm looking or white papers that will help me gain a deeper knowledge of SQL Server.

How are white papers different from books online?  Are white papers just a deeper dive into a technical issue?

Does Microsoft have an organized place for white papers? If so, where please?

Thank you for your help!


texts:
So I was cleaning some indexes,with some queries I made, and some that I found over the internet with ineficient indexes ( lots of updates and no seeks, scans, etc.)

But something that I'm not sure is, almost all primary keys have no seeks, scans or lookups and thousands of updates.

I think this is a normal behavior since we have most of these keys only to have an ID on a table.

Am I right?

Like this one:

ID_X Seeks=0, Scans=12, Lookups=8, Updates=31453


texts:
On SQL Server I want to update a 0/1 interger value for a specific set of records. For some reason this takes very long (let it run for 2 hours, still not finished). I can't figure out what is wrong or what to tweak.

The query:

 update workorder set INCTASKSINSCHED=0 where siteid = 'RAILINFRA' AND HISTORYFLAG=0 AND ISTASK=0;

There are 282320 workorder records, only 103565 are for the RAILINFRA site. So it should be very, very fast.

In SQL Management studio I have this as an estimated execution plan. You can see a huge cost at the workorder_ndx1 index. I'm not sure why that is...

I tried to count the number of records, this works very fast:

SELECT COUNT(*) FROM  workorder WHERE   siteid ='RAILINFRA' AND HISTORYFLAG=0 AND ISTASK=0;

1 row: 5430
Execution time 0sec

The select statement took just under 1 sec, and has a different execution plan:

SELECT * FROM  workorder WHERE   siteid ='RAILINFRA' AND HISTORYFLAG=0 AND ISTASK=0;

Gives the 5430 rows

This is the information of the indices used:

workorder_ndx1 (clustered)

SITEID
WONUM

workorder_ndx24 (non-clustered)

HISTORYFLAG
ISTASK
WONUM

There is only one index with the to be updated field INCTASKINSCHED

workorder_ndx38 (non-clustered)

WONUM
INCTASKINSCHED
SITEID

I do have the XML's of the executionplans (slqplan-files), let me know if you need more information from them.

I suspect there is somekind of a loop that it uses an index that needs updating, but not sure why that should be the workorder_ndx1 index, because it doesn't include the INCTASKINSCHED field.


texts:
Let say I set a min and max key for shards using range tags. Later I find out that the tag range I defined will not balance. Then I have to change the tag range value.

I have searched MongoDB's documentation, which only mentions add and remove tags, nothing about updating or changing the tag range.

Then I experimented by updating the range values in the config database tag collection.

mongos> db.tags.find()
{ "_id" : { "ns" : "test.lab.range", "min" : { "_id" : 1 } }, "ns" : "test.lab.range", "min" : { "_id" : 1 }, "max" : { "_id" : 100 }, "tag" : "rangeTime1" }
{ "_id" : { "ns" : "test.lab.range", "min" : { "_id" : 100 } }, "ns" : "test.lab.range", "min" : { "_id" : 100 }, "max" : { "_id" : 200 }, "tag" : "rangeTime2" }
mongos> db.tags.update({_id:{ "ns" : "test.lab.range", "min" : { "_id" : 100 } }},{$set:{min : {_id : 150}}})
mongos> db.tags.update({_id:{ "ns" : "test.lab.range", "min" : { "_id" : 1 } }},{$set:{max : {_id : 150}}})
mongos> db.tags.find()
{ "_id" : { "ns" : "test.lab.range", "min" : { "_id" : 1 } }, "ns" : "test.lab.range", "min" : { "_id" : 1 }, "max" : { "_id" : 150 }, "tag" : "rangeTime1" }
{ "_id" : { "ns" : "test.lab.range", "min" : { "_id" : 100 } }, "max" : { "_id" : 200 }, "min" : { "_id" : 150 }, "ns" : "test.lab.range", "tag" : "rangeTime2" }

That worked well, but the tags id wasn't updated. Notice that even when min is updated to 150 at rangeTime2, id is not updated, which makes the tags collection seem inconsistent.

It is kind of dirty but sadly it is works.

Is this the right way to update tag range? Is there a better way?


texts:
Over the past 3 weeks we have been having bottle-necking issues at our MySQL server. I have been using MonYOG to watch the processlist in an attempt to get a grasp on the issues. We are aware that some of the queries and processes we are running in our code is less than optimized, but I am not completely convinced that is the main source of our issues. I feel our server should be able to overcome these issues.

Our tables are a mix of innodb and myISAM. I am by no means a DBA and do not pretend to be one so I am not sure what engine is the best in our current environment. We are pretty read heavy, but mix in a great deal of updates and inserts as well. I was seeing a lot of locked tables which made me think innodb might be better since it does row lock instead of table lock. I converted a few of our tables from MyISAM over to innodb to take advantage of some of the settings that are available. The developers are using a great deal of complex joins as well.

Here is what we are running in our my.cnf:

[mysqld]
datadir=/var/lib/mysql
port=3306
socket=/var/lib/mysql/mysql.sock
user=mysql

key_buffer_size=512M

innodb_file_per_table
innodb_buffer_pool_size=6GB
#the following line is causing some odd errors when doing db dump
#innodb_log_file_size=128M
innodb_log_buffer_size=8M
innodb_additional_mem_pool_size=32M

max_allowed_packet=16M
join_buffer_size=8M
sort_buffer_size=8M
max_connections=500
wait_timeout=500
skip-name-resolve
thread_cache=256
table_cache=256
tmp_table_size=48M
max_heap_table_size=48M

query_cache_size=64M

#logging of slow queries
log-slow-queries=/var/log/mysql-slow-query.log

# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
#old_passwords=1

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
#pid-file=/var/run/mysqld/mysqld.pid

I changed the current "innodb_buffer_pool_size" to 6GB last week and that made a huge improvement to the bottle-necking. We have actually only really started seeing issues again today. Traffic has increased this week since we have schools starting and football season starting as well.

I have all kinds of statistical information I can supply you if needed. I am not sure what else to supply at this time.

If I cannot get this figured out I will be forced to call in a consultant. I am hoping someone will be able to give me a hand with some of these settings.

Thanks in advance.

------------------ EDIT--------------------

Here is some additional information requested:

MySQL Version - 5.0.95
OS version - CentOS 5.6 (will be upgrading to 6.4 in the coming weeks)

Server - Dell PowerEdge R710
CPU - Dual Quad Core 2.8GHz
RAM - 12GB
This is a dedicated machine. The only thing running on this box is MySQL


texts:
I have a table named as transaction,in that table trans_type column is there.I want to check the trans_type like bellow

if(trans_type = 'AR','AD','AF','AG') then
trans_type:='A';
elsif trans_type = 'BA','BS','BD','BG' then
trans_type:='B';
end if;

How to write this one by using decode function? Kindly tell me answer.


texts: Best approach to delete and update a big amout of rows in MySQL

texts:
I'm currently developing a REST API that connects to a MySQL database and does a bunch of queries.

In one of those queries I happen to have to delete a bunch of rows from a table and each of those rows have a unique identifier.

And basically I have two approaches to do so: (that I know).

Build a bunch of DELETE FROM table WHERE id=value; statements and execute them sequentially.
Build only a DELETE FROMstatement but with a massive OR chain.

I don't have a ton of experience handling databases and any suggestion or advice would be greatly appreciated.

EDIT:

I just noticed that I also have something similar happening with a UPDATE FROM statement where I set a lot of records to the same given value, so any advice on that too would be really nice.


texts: What does enabling containment on server incur?

texts:
Having read in a book of Patrick LeBlank "Microsoft® SQL Server® 2012 Step by Step":

"Benefits and limitations of contained databases

As with most features, there are benefits and limitations to contained database security. Most of the limitations are specific to containment’s operability with other SQL Server features. If database containment is enabled, you cannot use change data capture, change tracking, and replication"

I wonder:
does setting "Enable contained Databases" to True on server

incur limitations on the whole server, the instance of server or specific database created as contained one?

Apparently, it is only to contained database but, then, why it should be enabled server-wide first?
and what does it incur (for example, if I enable it on server but did not enable it on any particular database)?


texts: Cannot Decrypt the encrypted columns from the database backup on local machine

texts:
I've a SQL server 2014 running on one of our server. We're in the process of implementing security steps for our databases. I've encrypted a column in one of the table in the database on the server. The issue is when I restore the backup on my local SQL server and run a query to decrypt the column data it gives me null values. On the other end when I decrypt the column data on the main server it works fine. I found a thread on this forum which states to do the following when restoring the encrypted database on different server.

use master
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'StrongPassword'

CREATE CERTIFICATE BackupCert
WITH SUBJECT = 'Backup Certificate';

-- Create a symmetric key
CREATE SYMMETRIC KEY FileName
WITH ALGORITHM = AES_128
ENCRYPTION BY CERTIFICATE BackupCert
GO

OPEN SYMMETRIC KEY FileName
DECRYPTION BY CERTIFICATE BackupCert
GO

UPDATE [TestDB].[dbo].[Order_Customer_tab]
SET [Process_File_Name] = ENCRYPTBYKEY(key_guid('FileName'), [Process_File_Name])
GO

OPEN Master Key decryption by PASSWORD = 'StrongPassword'

select Process_File_Name
, CONVERT(nvarchar,DECRYPTBYKEY(Process_File_Name))
 from [TestDB].[dbo].[Order_Customer_tab]

I tried doing above still no luck.

Can anybody point me in the right direction? Any help is greatly appreciated.

Thanks


texts: Turning off auto growth for particular file

texts:
We have a primary file group with several files each on different disks.  One of the disks is nearing capacity and as such we have had to switch off the auto growth for that file.  Is there anything I need to consider when I do this?

I don't believe I can force a table to be in a particular file when they are all in the same filegroup, so from an admin point of view I haven't been able to think of anything.


texts: Question on Oracle rowid column in every table

texts:
I want to know how to use the object number that extract from rowid column? It's seems that this oracle number is not the same as object_id in dba_objects view. Anyone can help me with it please? Thanks.

sys@ORCL>l
  1  SELECT rowid, DBMS_ROWID.Rowid_object(ROWID)   "OBJECT",
  2     DBMS_ROWID.Rowid_relative_fno(ROWID) "FILE",
  3     DBMS_ROWID.Rowid_block_number(ROWID) "BLOCK",
  4     DBMS_ROWID.Rowid_row_number(ROWID)   "ROW",
  5     employee_id, first_name
  6  FROM   hr.employees
  7* WHERE  department_id = 30
sys@ORCL>/

ROWID              OBJECT       FILE      BLOCK    ROW EMPLOYEE_ID FIRST_NAME
------------------ ---------- ---------- ---------- ---------- ----------- --------------------
AAAR5pAAFAAAADPAAO  73321          5    207     14         114 first_name
AAAR5pAAFAAAADPAAP  73321          5    207     15         115 first_name
AAAR5pAAFAAAADPAAQ  73321          5    207     16         116 first_name
AAAR5pAAFAAAADPAAR  73321          5    207     17         117 first_name
AAAR5pAAFAAAADPAAS  73321          5    207     18         118 first_name
AAAR5pAAFAAAADPAAT  73321          5    207     19         119 first_name

6 rows selected.

sys@ORCL>select * from dba_objects where object_id = 73321;

no rows selected

sys@ORCL>


texts: What is the differences between to and = in set search_path?

texts:
I have different schemas in my database like,

public
Hotel_Management
Hospital_Management

My default schema is Public. So I try to change from default schema to another schema.
I know how to change the schema from one to another.
set search_path to Hotel_Management;

But my doubt is, What is the real difference between to and = in set search_path?.
For example:
Set search_path to Hotel_Management;

and
Set search_path = Hotel_Management;

Thanks in Advance...


texts:
What is an "unmentionable word" to SQL?

I am reading sys.query_store_query_text (Transact-SQL) and I see the following, so I google {SQL "unmentionable" word}

  has_restricted_text
  bit
  Query text contains a password or other unmentionable words.

Other then 3 links that take me back to where I found it, I don't find anything that appears to show a relationship to a password or anything that SQL might care about.


texts: Does MySQL guarantee atomic upgrade from read lock to write lock?

texts:
If a client session is currently holding a read lock (to allow concurrent reads while already preventing writes), and then acquires a write lock on the same table(s), does MySQL guarantee that the lock is continuous?

The Manual states:

  If a session issues a LOCK TABLES statement to acquire a lock while already holding locks,
  its existing locks are released implicitly before the new locks are granted.

And as far as I can see, it states nothing explicitly about "upgrading" a lock from read to write. As in

mysql> LOCK TABLE `awesome` READ;
mysql> SELECT ... FROM `awesome` ...
mysql> LOCK TABLE `awesome` WRITE;  # lock continuous, or dropped+reacquired?
mysql> INSERT INTO `awesome` ...
mysql> UNLOCK TABLES;

If I'm being paranoid about the verbiage in the documentation, I have to assume that there will be a brief moment between the two lock states where my session temporarily holds no lock at all, during which a concurrent session could potentially modify the table, bringing it into a state inconsistent with what I've seen in the SELECT phase.

I cannot rely on exceptions thrown by duplicate key violations, as there is no useful unique key (and it would probably quadruple the size of the table if MySQL even supported it). For the same reason, transactions wouldn't really help me, but I cannot use them anyways, as the table in questions is using the MyISAM engine for performance reasons. Locking really seems like my best bet, only the documentation seems a little conspicuous on the transitioning details.

And insights on how MySQL internally handles transitioning from read lock to write lock if the set of tables is the same?

The big-wooden-hammer-method would of course be to issue a single write lock up front. But that would force all selects to wait, obviously. OTOH, as I understand it, an infinite number of sessions can read-lock the same table concurrently (implicitly or explicitly) with no implications on select performance at all. The only thing the read lock does is preventing updates from hitting the table, which is exactly what I need in the first stage.


texts: Querying All Records of All Users who have any instance of purchase behavior in their history

texts: I am trying to write a query that shows me all user ids that in any instance of usage have a particular type of "purchase credit" associated with their account.  I have one table, "users" that shows user information, ie, user ids, names, birthdate, etc. and then I have another table that shows all users all instances of credit purchases.  I want to pull all credit purchases (not just the type I am looking up) from all users who in any instance of purchase have this one kind of credit purchase in their record.  Any help would be much appreciated!


texts: Get the most recently completed step for each SQL job

texts:
I'm trying to get a list of jobs and their most recently completed step from the MSDB database. For a single job, this is pretty straight forward, something like:

 SELECT TOP 1 j.job_id, j.name, h.step_name, h.run_date, h.run_time, h.step_id
 FROM msdb.dbo.sysjobs j
 INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
 WHERE j.name = 'my favorite job'
 ORDER BY h.run_date DESC, h.run_time DESC, h.step_id DESC

But how can I get a list of ALL jobs and their most recently completed step?

Note this is SQL 2000, so I can't use PARTITION OVER.
I also can't do a SELECT MAX() because its not a single RunDateTime field, I have to sort descending by two fields (OrderDay, OrderTime).

Edit: Probably not possible, but I'd love to be able to something like:

SELECT j.job_id, ...
FROM msdb.dbo.sysjobs j
INNER JOIN (SELECT TOP 1 step_name, run_date, run_time, step_id
            FROM msdb.dbo.sysjobhistory
            WHERE job_id = j.job_id
            ORDER BY run_date desc, run_time desc, step_id desc) as H

or something, but it doesn't seem to like syntax like that...


texts: Optimizing table for timeseries Postgres data table

texts:
I have the below table which maintains a timeseries result. The row only becomes relevant when the signal is true, When signal is false, it just marks that for that particular timestamp we got a result but it is not a valid one, so the res and other columns just contains null values. When signal is null, it marks that we are yet to receive result for this timestamp. The signal is very sparse in nature, it is only true for maybe less than 7% of the records. Also the inserts made to this table are not ordered according to timestamp, older dates could arrive at later time.
CREATE TABLE public.res
(
 pid integer NOT NULL,
 aid integer NOT NULL,
 cid integer NOT NULL,
"time" timestamp without time zone NOT NULL,
 signal boolean,
 price numeric,
 res double precision[] NOT NULL,
 ...<Many more columns of numeric/numeric array data types>
 CONSTRAINT res_pkey PRIMARY KEY (pid, aid, cid, "time")
)

This table can contains millions of records and is growing exponentially as my database is growing. I want to optimize this table. So have following questions

Is each row the same size? Or since the row only makes sense if Signal is true, can it be dynamically sized? Hence keeping the overall size of the table low? A min row size would contain (pid,aid,cid,time,signal,price) and the max will additionally contain (res and the remaining columns)? Is it possible to this in Postgres with its dataTypes? I do not want to create separate tables because run time joins could be very expensive when there are millions of records.

This SO answer says "In effect NULL storage is absolutely free for tables up to 8 columns." , I have many more columns

Any other suggestion that you might have to deal with such problems?

I read about timescaleDB, but since the records do not get inserted in order of timestamps does it has any advantage over Postgres in this usecase?

Thanks


texts: Postgres 10 not returning disk space after dropping indexes

texts:
I had several indexes that totaled ~1TB, according to pg_relation_size(). I dropped them.

I expected to get a TB of disk space back at the OS level, but this did not happen. Maybe 15GB total (I didn't note the precise number at the time).

I used the following query to determine the sizes which I pulled from the wiki (https://wiki.postgresql.org/wiki/Index_Maintenance):

SELECT
    t.tablename,
    indexname,
    c.reltuples AS num_rows,
    pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
    pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
    CASE WHEN indisunique THEN 'Y'
       ELSE 'N'
    END AS UNIQUE,
    idx_scan AS number_of_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x
           JOIN pg_class c ON c.oid = x.indrelid
           JOIN pg_class ipg ON ipg.oid = x.indexrelid
           JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid AND psai.schemaname = 'public' )
    AS foo
    ON t.tablename = foo.ctablename
WHERE t.schemaname='public'
ORDER BY 1,2;

I used the value in the "index_size" column. The size matched my expectations based on the fields in the index and the size of the table.

For example, the remaining index on one of the tables is for the primary key and is, according to that query, 147GB on a 448GB table containing 7 billion records. Given that there are only a few fields, this seems like a reasonable size to me.

It's not table data so a VACUUM FULL/pg_repack should not be necessary. lsof shows no open deleted files. I can't even address them at this point.

How do I reclaim this space? Why didn't it come back immediately?


texts: Calling stored procedure with select and update statement

texts:
I have a requirement to return a batch of records and then mark the batch complete. I was thinking to acheive this with a select and the update. Something like

create procedure testproc
as

select x,y,z from Table

update Table set retreived = 'Y'

go

The question I have, is what happens if the procedure executes, returns the select but the connection breaks before the batch is retreived. Does the batch still get updated?

Otherwise can anyone think of a better way to acheive this with stored procs?


texts:
here is the error I get:

C:\WINDOWS\system32>"C:\Program Files\Microsoft SQL Server\120\LocalDB\Binn\sqlservr.exe" -m
2017-09-27 16:09:26.50 Server      Error: 17058, Severity: 16, State: 1.
2017-09-27 16:09:26.50 Server      initerrlog: Could not open error log file 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\ERRORLOG'. Operating system error = 32(The process cannot access the file because it is being used by another process.).
2017-09-27 16:09:26.80 Server      Error: 17058, Severity: 16, State: 1.
2017-09-27 16:09:26.80 Server      initerrlog: Could not open error log file 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\ERRORLOG'. Operating system error = 32(The process cannot access the file because it is being used by another process.).

Any hint to help me would be great. Thank you!


texts:
I have a SELECT INTO statement writing to a database which is in SIMPLE recovery mode, and is not tempdb. Will this use tempdb at all?

The SQL is something like

USE TGTDB
GO

SELECT * INTO DestinationTable FROM SRCDB.dbo.SourceTable
GO

Will this use tempdb at all? We are running into contention issues.


texts: UPDATE table element using data and id from another table

texts:
I need to update an element the row for id=N in table A with derived data from Table B.

Table A has uid as its primary key, and contains DATE element foo.

Table B has a_uid as an element, for each of which there are multiple rows for DATE element b_foo.

Conceptually, what I want to do is

UPDATE A SET foo=MAX(b_foo) WHERE uid = a_uid

Table A:

uid INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
foo DATE

Table B:

b_uid INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
a_uid INT(10) UNSIGNED,
b_foo DATE

I feel like this should be more obvious than I'm finding it to be; maybe it's because it's a COVID Monday? Regardless, many thanks for help!


texts:
Is there a way to start SQL Server directly from SSMS?

I ask because I often go directly into SSMS, only to realize that I haven't started the SQL Server service yet. Which means either running a different app or messing around with the command prompt.


texts: Should I add a clustered Index on a foreign key that is not unique?

texts:
I have access to a table that has a primary key unique clustered index on an nvarchar(36).
I need to create another table that hangs our own data off that unique clustered index in another table and use the field from the table above as the foreign key reference (these 2 fields will be the fields that JOIN the 2 tables). In this table we keep a record of the data as it changes, so we get repeated rows for the FK reference where the previous row has been end dated.
The question I have is would it be best to put the clustered key on the foreign key (that’s the field we’ll always join on) or is that bad practice because it’s not unique and also the order that rows would get added may not be sequential?
If uniqueness is an issue would you consider it a good approach to use the foreign key and the default start date column to make it unique?
Would no clustered index but a non clustered index (filtered for NULL end dates) be the best approach, problem is – there are a lot of columns in the FK table and I’d need to include them all in the NC index that might take it over the limit.
Further info as requested:
We're moving from one system supplier to another, but locally we hang extra data off the suppliers systems, The tables are as follows:
Main table that holds the ExternalID as the PK clustered index, when we go live this will hold 1.4 million rows
CREATE TABLE [dbo].[Inpatient_Activity](
    [RowID] [nvarchar](50) NOT NULL,
    [ExternalID] [nvarchar](36) NOT NULL,
    [Spell_ExternalID] [nvarchar](36) NULL,
    [Activity_Type] [nvarchar](128) NOT NULL,
    [Accepted] [bit] NULL,
    [Ward] [nvarchar](128) NOT NULL,
    [Bed] [nvarchar](50) NULL,
    [Activity_Start] [datetime] NOT NULL,
    [Activity_End] [datetime] NULL,
    [LOA_Outcome] [nvarchar](128) NOT NULL,
    [Reason_Leave_Was_Ended] [nvarchar](128) NOT NULL,
    [Expected_End] [datetime] NULL,
    [Home_Leave_Reason] [nvarchar](128) NOT NULL,
    [Discharge_Delay_Reason] [nvarchar](128) NOT NULL,
    [Updated] [datetime] NOT NULL,
    [DATVER] [int] NOT NULL,
 CONSTRAINT [PK_ipActivity] PRIMARY KEY CLUSTERED
(
    [ExternalID] ASC
)
) ON [PRIMARY]

As we're moving systems I need to make the table below point at the new one, the old one had an id int identifier but that now seems a bit pointless hence I'm asking the question above.
AT the moment the table below has 174793 rows, but as we move forward every row in the top table will get at least one row in this table of not 2, 3 or 20 rows.
[SERVICE_POINT_STAYS_PATIENT_STATUS](
    [ID that will link to the table above] nvarchar(36),
    [ESTIMATED_DISCHARGE_DATE] [datetime] NULL,
    [REASON_ID] [int] NULL,
    [START_DTTM] [datetime] NOT NULL,
    [END_DTTM] [datetime] NULL,
    [USER_NAME] [varchar](50) NULL,
    [STATUS_ID] [int] NULL,
    [SPECT_REFNO] [numeric](10, 0) NULL,
    [REFERRED_WARD] [numeric](10, 0) NULL,
    [REFERRED_WARD_TIME] [datetime] NULL,
    [DISCHARGE_DESTINATION] [varchar](500) NULL,
    [EDD_VERIFIED] [char](1) NULL,
    [EDD_START_DTTM] [datetime] NULL,
    [DISCHARGE_TODAY] [varchar](150) NULL,
    [DISCHARGE_TODAY_DTTM] [datetime] NULL,
    [REFER_SPECT_DTTM] [datetime] NULL,
    [DECISION_TO_ADMIT_DTTM] [datetime] NULL,
    [DECISION_TO_ADMIT] [varchar](10) NULL,
    [REFER_SPECT_REFNO] [numeric](10, 0) NULL,
    [EDD_CHANGED_REASON_ID] [int] NULL,
    [MODIF_DTTM_TRIGGER] [datetime] NULL,
    [GRAVIDA] [varchar](50) NULL,
    [BABY_WEIGHT] [varchar](50) NULL,
    [GESTATION] [varchar](50) NULL,
    [ANTE_POST_NATAL] [varchar](50) NULL,
    [POST_OP_WARD_SPONT_REFNO] [numeric](10, 0) NULL,
    [THEATRE_STATUS_ID] [int] NULL,
    [THEATRE_STATUS_DTTM] [datetime] NULL


texts:
I am using SQL Server (2008 to 2016) on Windows. I have multiple servers, with multiple instances for one of them and a lot of databases (Over 1000). All databases are dumped on multiple storage in each server with maintenance plan.

I want to dump all databases on one SAN Volume (different folders)to reduce the number of storage to backup with NetBackup.

Did you see reason to don't do that ? (Network restriction, backup time ...) I imagine do this by change backup directory with network link, maybe not the good way ?


texts: What can cause statistics to get out of line?

texts:
I've just worked through a problem at a clients site which it turned out was caused by the statistics being wrong which caused the Optimizer to time out. Running exec sp_updatestats fixed the problem and all is now good.

What I am a bit confused about now is how did the statistics get out of line in the first place?

The database has both auto_create_stats and auto_update_stats switched on. So SQL Server should have kept the statistics up to date without any intervention.

So why did it fail in this instance?

This client had recently upgraded their database server. They handled it themselves, so I'm not exactly sure what procedure they went through, but I can't imagine it was anything more complicated than backing the database up on the old server and restoring it on the new one. Could this have caused the glitch somehow?


texts: How to create a new DB with same data from another DB in oracle 10g

texts:
I trying to copy entire data from old database to newly created database in oracle 10g.
please any one tell me the steps to do the process.


texts: T-SQL script doesn't delete files when run as an agent job - does if run manually

texts:
I'm trying to write a script to copy the last 'good set' of SQL Server backup files to another drive (it's a long story - we're using a suppliers R1Soft for backup and it won't allow us to exclude folders when we're talking about ~1M files).

The idea behind the script is that it:

Grabs the last backup location from SQL Server
Deletes the file from the separate drive
Runs Ola Hallengrens script to run a backup
Grabs the last backup location again (so now it's the one just made)
Copies that file to the separate drive

The problem that i'm having is that everything runs ok if i'm running the agent job manually, but when it comes to have the job run as scheduled, it does the copy part at the end, but doesn't do the initial delete - so all the old files stay on the drive and have to be deleted manually.

There's nothing in the logs that indicate what could be happening (no failures or error messages). I'm assuming that it's a permissions issue - what user does the agent job run as when scheduled? Maybe it's the user that I'm logged in as thats used when running it manually?

Either that, or is there a better way of doing it? I don't really want to run the backup twice as there's around 1TB of data to back up.


texts: Execution plan index suggestion - difference between similar queries

texts:
I am trying to analyze queries on this table:

CREATE TABLE [dbo].[Values](
    [tid] [smallint] NOT NULL,
    [t] [datetime2](3) NOT NULL,
    [v] [real] NOT NULL,
 CONSTRAINT [PK_Values_Unique] PRIMARY KEY CLUSTERED
(
    [t] ASC,
    [tid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

I am reviewing some Estimated Execution Plans using SQL Server Management Studio, first query.

SELECT *
FROM [dbo].[Values]
WHERE  [tid] = 1

Displaying Estimated Execution Plan for this query yields:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Values] ([tid])
INCLUDE ([timestamp],[v])

Which, I can understand, it wants to have a quick look up by tid, altough I don't fully why the INCLUDE is suggested.

Then I tried a second query (another tid), which is pretty the same with another tid:

SELECT *
FROM [dbo].[Values]
WHERE  [tid] = 1 or [tid] = 2

But now, SSMS, doesn't suggest the previous index.

Why does SSMS suggests an INDEX for the first query but not for the second now although they are pretty the same ?

Should adding this suggested INDEX be helpful for both types of queries?

EDIT: This table has over 2 * 10^9 rows.


texts:
Let's say there is an Oracle database A and another Oracle database B. Our database is A.

Is there a way to block database links from B to A?


texts: Granting permission to the service account

texts:
I am currently granting permissions to a service account on a database. I want to ensure if the granting db_datareader and db_datawriter database roles is the same as my grant command.

Adding service account to db_datareader and db_datawriter database roles.

EXEC sp_addrolemember N'DB_DATAREADER', N'INT\svc-w-corerefdata-de';
EXEC sp_addrolemember N'DB_DATAWRITER', N'INT\svc-w-corerefdata-de';

Creating a role, granting permissions to it and adding the service account to the role.

IF DATABASE_PRINCIPAL_ID('AlterPermCoreRefData') IS NULL
    BEGIN
        CREATE ROLE [AlterPermCoreRefData] AUTHORIZATION [dbo]
    END

GRANT EXECUTE, ALTER, SELECT, DELETE, INSERT, UPDATE ON database::[CoreReferenceStaging] TO [AlterPermCoreRefData]

EXEC sp_addrolemember 'AlterPermCoreRefData', 'INT\svc-w-corerefdata-de'


texts: Best approach for extending a foreign relationship for an existing table

texts:
I'm not sure the title accurately reflects my question. I have an existing Rate table that has an identity column key and contains a [Rate] column. col1 + col2 + col3 don't uniquely identify a row:

+--------+------+------+------+------+
| RateId | col1 | col2 | col3 | Rate |
+--------+------+------+------+------+

I have a new table that needs to match a rate. The new table has 3 of the required columns to make a match but those columns will return a number of rows in the Rate table. To uniquely identify a rate for the new table I need to match on MaterialTypeId and UnitTypeId where UnitTypeId can be null.

+------------+------+------+------+----------------+------------+
| MaterialId | col1 | col2 | col3 | MaterialTypeId | UnitTypeId |
+------------+------+------+------+----------------+------------+

What is the best approach to resolving this? I could add the two additional columns to the Rate table but that would not be relevant for the current uses of the Rate table, i.e. the two additional columns would be null for all existing rows.

I could introduce an intermediate table and join on MaterialTypeId & UnitTypeID which would return multiple rows and then join using the existing columns to uniquely identify the rate:

+--------+----------------+------------+
| RateId | MaterialTypeId | UnitTypeId |
+--------+----------------+------------+

Is that the correct approach?


texts: Compare two tables with same structure, but differing membership numbers

texts:
I have two tables with the same structure and most of the data is the same. I also have a third table that contains ids and names. The ids are connected to the membership numbers discussed below.

The databases are from the current and previous versions of a website. I need a query that searches all three tables for the names and membership numbers, compares the ids and membership numbers, then only displays the records that have membership numbers that do not match. I also need to exclude '0' as an unmatched result.

This matching only needs to happen between the two identically structured tables. The third table is only used to connect a unique name to each of the records returned from the two membership number tables. I have included my updated SQL query below.

SELECT
    u.id,
    u.name,
    a.membership_no,
    j.membership_no
FROM
    n2z7m3_users AS u
LEFT JOIN n2z7m3_kiduka_accounts AS a ON (u.id = a.membership_no)
LEFT JOIN n2z7m3_kiduka_accounts_j15 AS j ON (
    a.membership_no = j.membership_no
)
WHERE a.membership_no NOT IN (
    SELECT
        j.membership_no
    FROM
        n2z7m3_kiduka_accounts_j15 AS j
);

Users table structure and sample data:

+------+----------------+
| id   | name           |
+------+----------------+
| 6000 | Dave Sanderson |
+------+----------------+
| 6001 | Bill Willis    |
+------+----------------+
| 6002 | John Hancock   |
+------+----------------+

Membership numbers table structure and sample data from accounts table one:

+--------------------------------+
| id   | user_id | membership_no |
+----------------+---------------+
| 6000 | 6000    | 6000          |
+----------------+---------------+
| 6001 | 6001    | 6001          |
+----------------+---------------+
| 6002 | 6002    | 6002          |
+----------------+---------------+

Membership numbers table structure and sample data from accounts table two:

+--------------------------------+
| id   | user_id | membership_no |
+----------------+---------------+
| 6000 | 6000    | 6010          |
+----------------+---------------+
| 6001 | 6001    | 6020          |
+----------------+---------------+
| 6002 | 6002    | 6030          |
+----------------+---------------+

The membership_no=6010 from the second accounts table would be the difference from the first accounts table. The user_ids are the same, but the membership_nos are different. This is what I am needing to find. All of the instances where the membership_nos are different using the ids from the users table and user_ids from the accounts tables.


texts: For performance mornitoring, i would like to have a mysql query which gives the calculated output of TPS. I think tps= Com_commit + Com_rollback / second , but I don't know the query to calculate these formula in mysql. Same related question for TPS had been asked but query was not provided there, so why I ask here. Kindly show me how to calculate values from status in MySQL/MariaDB.


texts: What are pros and cons of Ola’s scripts vs using maintenance plan?

texts:
Will you please help me understand pros and cons of using Ola’s solution over maintenance plan? I have prepared a presentation based on SQL Pass (http://www.pass.org/DownloadFile.aspx?File=ebae1b31) which I will present.

I am also preparing few scenarios which Ola’s solution addresses and maintenance plan solution doesn’t. Can you all please help me explain this more technically?

By the way, we are managing almost 150+ servers (mix of 2008/2012/2014/2016) with Ola’s solution on at least 75% of them. I liked this article by Brent Ozar. But in one the comments, Brent has recommended to use script based solution for the number of servers we have. https://www.brentozar.com/archive/2012/04/maintenance-plans-roombas-suck-good-way/


texts: Can I use row-level-security for application users?

texts:
For my webservice I use a PostgreSQL database which supports row-level-security to prevent users accessing rows of a table they are not supposed to read/access. I would like to use this functionality to prevent a user accessing data from another user (either due to a bug, altered webservice request, SQL injection, etc).

I understand the purpose is actually meant for a database user, but I am wondering if it is wise or common to create a shadow-user in the database for every "application" user as well. (the database is of course not directly accessed and rather through a WSGI, therefore the shadow user)

FYI: This is technically a XY-problem. For example, my actual intention is to prevent that a user requested his profile and accidentally or corner-case data from another profile gets send back. Row-Level access looked promising


texts: Dynamic Data Masking performance overhead

texts: I'm thinking of using the new dynamic data masking that has been introduced with SQL Server 2016 as part of our GDPR project . Does anyone else have experience of it? I am particularly interested if there is a performance overheard.


texts:
We have a table containing around 500k rows. The database table is supposed to grow to million of records.

This is how the table looks like:

CREATE TABLE public.influencers
(
    id integer NOT NULL DEFAULT nextval('influencers_id_seq'::regclass),
    location jsonb,
    gender text COLLATE pg_catalog."default",
    birthdate timestamp without time zone,
    ig jsonb,
    contact_info jsonb,
    created_at timestamp without time zone DEFAULT now(),
    updated_at timestamp without time zone DEFAULT now(),
    categories text[] COLLATE pg_catalog."default",
    search_field text COLLATE pg_catalog."default",
    search_vector tsvector,
    ig_updated_at timestamp without time zone,
    CONSTRAINT influencers_pkey PRIMARY KEY (id),
    CONSTRAINT ig_id_must_exist CHECK (ig ? 'id'::text),
    CONSTRAINT ig_username_must_exist CHECK (ig ? 'username'::text)
)

And these are some of the queries we need to perform efficiently:

SELECT  "public"."influencers".*
FROM "public"."influencers"
WHERE (ig->'follower_count' IS NOT NULL)
ORDER BY (ig->'follower_count') DESC
LIMIT 9
OFFSET 0

SELECT *
FROM "public"."influencers"
WHERE (ig->'follower_count' >= '5000')
LIMIT 9

SELECT SUM(CAST(ig ->> 'follower_count' AS integer))
FROM "public"."influencers"
WHERE (ig->'follower_count' >= '5000')
  AND (ig->'follower_count' <= '10000')
  AND (ig->'follower_count' IS NOT NULL)

ig -> follower_count are numeric values.

I read that GIN indexes are mainly intended for searching through composite items (text) so I'm guessing the best index to use would be a BTREE. Would that be correct?


texts: I'd like to make this relational database into a star schema. Any help is appreciated

texts: So basically, I want to make this relational database into a star schema. This would revolve around a sales fact table. Although I am confused as how to do so because there are two ways in which a sale is made. Either through a store sale or internet sale. That part is kind of messing with how I would build my facts and dimensions table. Do I just make the sales fact table include the store id and leave it as null if it were a internet sale, or do I make those two items a dimension such as internet sale dim and store sale dim?


texts: How to handle temporal datatype columns when doing upgrade from 5.5 to 5.6

texts:
I want to upgrade my MySQL 5.5.40 server to 5.6.28. But as per the documentation we need to alter the tables having columns which are using date or datetime or timestamp data types as storage requirement for these data types changed and to allow fractional parts for these temporal data types.

https://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html

ALTER TABLE tbl_name FORCE;

But how can we confirm that storage is altered. Is there any query or something to check that table columns are updated. What are the consequences if we don't alter the table and just done an in place upgrade from 5.5 to 5.6?


texts: Indexing SQL Server datetime Columns for Performance

texts:
Will adding an index to a datetime column increase performance if a WHERE myDateColumn BETWEEN @startDate and @endDate clause is frequently applied to that datetime column?

If yes, should the index be clustered or non-clustered and why?


texts: Select rows where one row meets a condition

texts:
I have a table with 3 columns:

UserId  ProductId    Status
1       100          1
1       1            0
2       100          0
4       100          0
5       1            1
5       100          1
6       2            1

I want to select all rows that have ProductId = 100 at least once per user and the user has at least one more record other than with ProductId = 100.

Expected results:

UserId  ProductId    Status
1       100          1
1       1            0
5       1            1
5       100          1

I solved it like this by referring to another solution:

select "ProductId", "UserId", "Status"
from "MyTable"
WHERE EXISTS (
    SELECT 1 FROM "MyTable" WHERE "ProductId" = 100
)

But it returns all users where even one record is 100.


texts:
Good day.  We have an 8 core server for mariadb 10.1 on Centos 7. IT's a production database as well as a replication master. I am not sure if it's using more than one of the cores.

We also have a one-core virtual server acting as a slave.

Searching on this tells me that most queries are about using more than one core per query, which are repeatedly answered with NO, that's not possible.  I get that.

I just want to know if multiple queries can be apportioned out to multiple cores, whether configuration settings are required to do this, and how to tell if it's working.

I also need to know whether slave throughput could be improved by adding cores.

Thanks,

Ed Greenberg


texts:
I'm trying to determine the most effect way to do an upsert in SSIS with CSV source 100+ columns

1 x CSV Source (loop through multiple CSVs same structure 100 + columns)
1 x Staging Table Destination

I have come up with 3 approaches:

Using the SSIS lookup component, if record doesn't exist insert otherwise call some SQL script to do the update (row by row) so could be slow.
Calling a stored procedure with a merge statement, so CSV to stored procedure (will be row by row) so slow. Also there will 100+ params
Using another staging table that gets cleaned out before insert..Then calling a stored procedure to merge the 2 staging tables

I'm leaning towards approach #3 as it's the simplest and cleanest.

Thoughts?


texts: Query to remove duplicate calculated values

texts:
I have a client running SQL Server 2008 R2 SP1, and I can't seem to write a query that has distinct calculated values. I could change the schema to store the value being calculated, but I was curious if there was a way to do this from within a query without modifying schema (for scenarios where client schema modification is not possible)?

City Table:

+-------+----------+--------------------------+----------+
|  id   |   name   |       displayName        | countyID |
+-------+----------+--------------------------+----------+
| ...   | ...      | ...                      | ...      |
| 833   | Portland | Portland, Ashley, AR     | 3323     |
| 21388 | Portland | Portland, Clackamas, OR  | 5439     |
| 21655 | Portland | Portland, Multnomah, OR  | 5462     |
| 21726 | Portland | Portland, Washington, OR | 5470     |
+-------+----------+--------------------------+----------+

County Table

+------+------------+----------------+---------+
|  id  |    name    |  displayName   | stateID |
+------+------------+----------------+---------+
| ...  | ...        | ...            | ...     |
| 5439 | Clackamas  | Clackamas, OR  | 38      |
| 5462 | Multnomah  | Multnomah, OR  | 38      |
| 5470 | Washington | Washington, OR | 38      |
+------+------------+----------------+---------+

State Table

+-----+-------+---------------+
| id  | State | StateFullName |
+-----+-------+---------------+
| ... | ...   | ...           |
| 38  | OR    | Oregon        |
+-----+-------+---------------+

Query:

   select top 100 City.id as cityID
    ,City.name + ', ' + [State].[State] as label
    ,[State].[State] as stateAbbrev
    ,[State].StateFullName as stateName
from [City]
left join [Country] on (City.countyID = Country.id)
left join [State] on (Country.stateID = [State].id)
where City.name + ', ' + [State].[State] like 'Portland, OR'

Results:

+--------+--------------+-------------+-----------+
| cityID |    label     | stateAbbrev | stateName |
+--------+--------------+-------------+-----------+
|  21388 | Portland, OR | OR          | Oregon    |
|  21655 | Portland, OR | OR          | Oregon    |
|  21726 | Portland, OR | OR          | Oregon    |
+--------+--------------+-------------+-----------+

The desired query would return only one row for distinct values of the calculated column "label". As I mentioned earlier, I'm curious if there's a solution that doesn't involve schema modification since that may not always be possible.


texts: Error while duplicating Oracle database after failover, using 11.2.0.4

texts:
I've recently updated oracle from version 11.2.0.1 to 11.2.0.4 and my redundant system is no longer able to recover from failover.

I'm using scripts both on primary and standby node to get back to a state where it is possible to install them as a redundant system again.

It is possible to run the reset script over and over again in all states exept after a failover is performed. The only change is the upgrade to 11.2.0.4. it worked like a charm in 11.2.0.1

I am using Data Guard Broker to maintan the redundancy.

The setup procedure is complicated and is hard to explain here, so I am just asking if someone have faced the same error, and might have a solution how to recover from such situation.

At least, this is the output where it fails: (Backup step is performed prior to duplicate action)

2014-11-14 17:08:55 : Recovery Manager: Release 11.2.0.4.0 - Production on Fri Nov 14 17:08:48 2014
2014-11-14 17:08:55 : Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
2014-11-14 17:08:55 : connected to target database: REF15 (DBID=276671931)
2014-11-14 17:08:55 : RMAN> CONNECT AUXILIARY *
2014-11-14 17:08:55 : 2> RUN {
2014-11-14 17:08:55 : 3> SET UNTIL sequence = 159 thread = 1;
2014-11-14 17:08:55 : 4> ALLOCATE AUXILIARY CHANNEL CH1 TYPE DISK;
2014-11-14 17:08:55 : 5> DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK DORECOVER;
2014-11-14 17:08:56 : 6> RELEASE CHANNEL CH1;
2014-11-14 17:08:56 : 7> }
2014-11-14 17:08:56 : 8> exit;
2014-11-14 17:08:56 : connected to auxiliary database: REF15 (not mounted)
2014-11-14 17:08:56 : executing command: SET until clause
2014-11-14 17:08:56 : using target database control file instead of recovery catalog
2014-11-14 17:08:56 : allocated channel: CH1
2014-11-14 17:08:56 : channel CH1: SID=25 device type=DISK
2014-11-14 17:08:56 : Starting Duplicate Db at 14-NOV-14
2014-11-14 17:08:56 : released channel: CH1
2014-11-14 17:08:56 : RMAN-00571: ===========================================================
2014-11-14 17:08:56 : RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
2014-11-14 17:08:56 : RMAN-00571: ===========================================================
2014-11-14 17:08:56 : RMAN-03002: failure of Duplicate Db command at 11/14/2014 17:08:51
2014-11-14 17:08:56 : RMAN-05501: aborting duplication of target database
2014-11-14 17:08:56 : RMAN-20206: log sequence not found in the repository
2014-11-14 17:08:56 : Recovery Manager complete.

Update:
Log sequence number (in this case: 159) is fetched from the following query:

    select max(sequence#) from v$archived_log;

Thanks (originally posted in std Stack Exchange)


texts: sqldeveloper ORA-12505. TNS: listener does not currently know of SID given in connect descriptor

texts:
There are similar questions asked and answered in this network related to the error message given in the question, however this problem is a little different from each.

I have installed oracle 12c in my ubuntu can successfully connect to my hr user with sqlplus hr/hr@pdborcl through command prompt.

oracle@ubuntu:~$ sqlplus hr/hr@pdborcl
SQL*Plus: Release 12.1.0.2.0 Production on Mon Nov 16 21:57:24 2015
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Last Successful login time: Mon Nov 16 2015 21:24:32 +05:45
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>show user;
USER is "HR"

I am however trying to use sqldeveloper for ease. In the create connection option in sqldeveloper when I give the following credentials

I get the following error.

    ORA-12505. TNS: listener does not currently know of SID given in connect descriptor.

However connection with

Username: sys
Password: ******
SID:orcl

And same all other credentials work fine.

My tnsname.ora has

PDBORCL =
  (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = pdborcl)
   )
 )

ORCL =
 (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = orcl)
  )
 )


texts: Why Does the Update Statement also updates indexes?

texts:
I am currently learning about SQL queries and how indexes work with them, and I ran into a situation that I don't understand. Let's say I have this query for that updates a tables' values with another table and they both have a dozen columns with a hundred thousand rows each:

UPDATE TA
SET TA.Column = TB.Column,
    TA.ColumnTwo = TB.ColumnTwo,
    TA.ColumnThree = 3
...
FROM TableA TA
INNER JOIN TableB TB ON TA.ColumnFour = TB.ColumnFour

Table TA has 9 indexes on it while Table TB only has 2. This update query would take longer than 5 minutes to update only a few hundred rows that matches the condition TA.ColumnFour = TB.ColumnFour. I assumed that it was because of the extra indexes since the query execution plan shows 9 Index Update (1 for each index). I removed the duplicate/unecessary indexes and I was able to lower the number of indexes to 4 but the query would still run slow.

My question is why does the update statement also updates indexes and how would I be able to prevent the query from creating the multiple Index Update?


texts:
How do I build a t-sql query to grant access to users on multiple databases in multiple instances?

eg:

Instance 1 - Database A
Instance 2 - Database B

How do I grant access to both databases to a single windows user?

My query should also check the SQL Instance if the database is present in that "Instance" before granting access.


texts: Partition : understanding files and file groups

texts:
We are trying to partition a large history table. Being new to partitioning, I was trying to understand the concept of files and file groups. File groups can be set up in following three ways :

1. Each file group on different disk
2. File groups on same disk
3. One file group on one disk with all files

MSDN says that you can take advantage of parallel I/O access in first method, if the disk has RAID set up, thus enhancing performance. Second method does not have additional performance benefit, however, maintenance is faster as each file group separates out the file from rest of the database. I searched for performance benefits of the third method.  I did not get the definite picture. For the third method, since the files are not divided in file groups, will it affect maintenance, thereby increasing downtime?  Does it have any performance gain over the second method?


texts: Storing JSON in Postgres as BYTEA or other types

texts:
I need to have a generic key-value data-store in Postgres and Sqlite3. I originally planned to store this as a JSONB type but the SQL statements I execute needs to be compatible with both Postgres and Sqlite3. Since sqlite doesn't support jsonb, I planned to use BYTEA instead. So something like this:

Originally:

CREATE TABLE foo (
    id INT PRIMARY KEY,
    data JSONB
)

Now

CREATE TABLE foo (
    id INT PRIMARY KEY,
    data BYTEA
)

This is not the best but it's still fine since I thought I could just cast BYTEA to JSON during query. That is, I thought I could do something like this:

SELECT data::json ->> 'name' AS name from foo where id = $1;

But looks like I cannot cast BYTEA to JSON type (ERROR:  cannot cast type bytea to json). This post describes how to convert BYTEA to JSON. But I'm worried that the decode() and convert_from() functions are going to be expensive.

So now I'm considering storing this data as VARCHAR instead of BYTEA, which eliminates the need for the decode() and convert_from() functions.

I'm wondering what the best option is here and if there's any better methods given the constraints I described.


texts: "WITH REPLACE" Restore failing with same logical filenames

texts: Does using WITH REPLACE when the logical filenames match (to avoid using WITH MOVE) fail when the backup is from a different version of SQL?  I have a backup from SQL 2008 but when I attempted to restore it using WITH REPLACE on a SQL 2012 folder, despite the Logical filenames matching on the database backup and the database I'm replacing, it's failing giving me the 'directory lookup' error.


texts: Output another word instead of the word in the table in a query

texts:
Suppose I have a table where I have a field which can have to values: "enabled" and "disabled".
Suppose want my query to output "unblocked" instead of "enabled" and "blocked" instead of "disabled". How to do that in Oracle?


texts: How to display name on a column chart itself in SSRS, not on a legends

texts: How can I display legends name on a column chart itself in SSRS?


texts: How to create via sql and detect via ERRORLOG SQL Server 2008 Deadlocks

texts:
I am designing a monitoring program using HP Sitescope SIS.

The goal is detect deadlocks.

In doing so, I created a deadlock on an existing but nearly unused SQL Server 2008.

I used the following link:
http://panigorthi.blogspot.com/2012/01/how-to-create-deadlock-in-sql-server.html

Now the query has been running for over 23 hours. Would you then say the SQL Server cannot detect the deadlock?

How can I create a deadlock it can detect and kill?

What is an example output in the ERRORLOG when the deadlock is resolved?


texts:
I'm having the following query while updating a particular record.

I've a user Column Family CF as follow:-

class User(Model):
    user_id = UUID(default=uuid.uuid4,primary_key= True)
    date = DateTime(primary_key=True,default=datetime.now())
    model = Text()
    gender = Text()
    imei = Text()

Now I want to filter users by gender, model and imei. So I made 3 models as follow. Note I could have defined model, gender and imei as secondary indexes, but secondary indexing has exponentially lower performance than normal querying, as there's nothing clever about it to back performance. Anything above a thousand records will seriously impair performance, hence I made 3 separate CF.

class UserByGender(Model):
    gender = Text(primary_key=True)
    date = DateTime(primary_key=True,default=datetime.now())
    user_id = UUID(index=True)

class UserByModel(Model):
    model = Text(primary_key=True)
    date = DateTime(primary_key=True,default=datetime.now())
    user_id = UUID(index=True)

class UserByImei(Model):
    imei1 = Text(primary_key=True)
    date = DateTime(primary_key=True,default=datetime.now())
    user_id = UUID(index=True)

I'm able to query and filter the total users by gender, model and imei. But the problem arises when I need to either update/delete a particular record.

While updating imei of a particular user, given the user_id,I do the following

I run the following query.

imei_user_list = UserByImei.objects.get(user_id = 'some_user_id').update(model='updated value')
#Note: over here I'm again filtering by a secondary index. Is it worth?

I know the PK cannot be updated. Hence I thought of a solution, that I'll delete it first and then re-write the record, but deletes in cassandra rely on Tombstones to support the eventual consistency model.

How do I perform update operations on such model? Or how can I re-design my CF such that I'm able to update a record AND read the records(based on my different filters) as well ?