Scaling Users in MongoDB Our team has been tasked with a fairly open ended request for a MongoDB with multiple users that can access and submit documents to collections for a graduate class. The team is all new to MongoDB and are unsure how to handle the user storage side of things.

The internal team discussion is for: a) create a new limited role user in the database level as each user signs up to the service or b) creating a user collection and store users and hashed passwords there.

Does anyone have any advice on which method is better and why?

If you absolutely need to give direct database access to someone, then go with option a) and all that it entails.

Else, then go with option b).

Option a) is a poor choice because, as of right now (MongoDB 4.2), permission granularity is still at collection level. This means that if users have write access to the same collection, they can overwrite documents of each other.

Even for your use case, if each user has their own collection or database, this would only work well with few users (less than five thousand active users). I suppose you could have a million users this way, even a billion, but MongoDB sharding doesn't work this way, and will never scale properly.


How to verify disk hit by a query I want to know how to verify disk hits by a select query in PostgreSQL 9.4 on CentOS 6.5. Also please let me know how far enabling the log_statement_stats in the postgresql.conf will help me.

I need to understand the 'filesystem blocks in/out' value in the logs when I switch the above parameter to on.

For example, please check the log snippet:

2016-02-03 14:12:45 PST LOG:  statement: select * from test_stats_collect ;
2016-02-03 14:12:45 PST LOG:  QUERY STATISTICS
2016-02-03 14:12:45 PST DETAIL:  ! system usage stats:
        !       0.069489 elapsed 0.027995 user 0.002999 system sec
        !       [0.131979 user 0.146977 sys total]
        !       0/0 [3280/26440] filesystem blocks in/out
        !       0/5 [19/4866] page faults/reclaims, 0 [0] swaps
        !       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
        !       20/116 [125/174] voluntary/involuntary context switches

The numbers reported by log_statement_stats come directly from the getrusage system call (assuming your OS has one).  The numbers in square brackets are total for the session so far, the other numbers are deltas between the start and stop of the statement.

The filesystem block size is not normalized to be the same as the PostgresSQL block size.  It is in whatever units of block size your OS reports in getrusage.


How to improve MySQL Server Performance..? As a MySQL DBA most of the times we are supposed to optimize a poorly performing MySQL Servers.

Now my question is where to start from like we will need to find out many things as

1.Find the duplicate indexes.
2.Find unused indexes on the basis of selectivity.
3.Monitor the Server Parameters(What should be important parameters).
4.Execute MySQL Server performance tuning script.
5.Slow logs

So what should be order of examining the server and what should be exact things that should be monitored/analysed for improving the Performance.

Finding Duplicate Indexes

Back in January 2012, @gbn answer a question about duplicate indexes where he presented 2 views that came from Ronald Bradford's blog. I combined the two views into a single query to present duplicate indexes as follows:

SELECT
    ndx1.TABLE_SCHEMA,ndx1.TABLE_NAME,
    CASE
        WHEN ndx1.COLUMNS = ndx2.COLUMNS
        AND (ndx1.IS_UNIQUE = ndx2.IS_UNIQUE)
        THEN GREATEST(ndx1.INDEX_NAME, ndx2.INDEX_NAME)
        ELSE ndx1.INDEX_NAME
    END REDUNDANT_INDEX_NAME,
    GROUP_CONCAT(DISTINCT
        CASE
            WHEN ndx1.COLUMNS = ndx2.COLUMNS
            AND (ndx1.IS_UNIQUE = ndx2.IS_UNIQUE)
            THEN LEAST(ndx1.INDEX_NAME, ndx2.INDEX_NAME)
            ELSE ndx2.INDEX_NAME
        END
    ) INDEX_NAME
FROM
(
    SELECT
        TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,INDEX_TYPE,
        IF(NON_UNIQUE, 'NO', 'YES') IS_UNIQUE,
        GROUP_CONCAT(CONCAT('`',COLUMN_NAME,'`')
        ORDER BY IF(INDEX_TYPE='BTREE',SEQ_IN_INDEX,0), COLUMN_NAME
        ) COLUMNS
    FROM
        information_schema.STATISTICS
    GROUP BY
        TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,INDEX_TYPE,NON_UNIQUE
) ndx1 INNER JOIN
(
    SELECT
        TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,INDEX_TYPE,
        IF(NON_UNIQUE, 'NO', 'YES') IS_UNIQUE,
        GROUP_CONCAT(
        CONCAT('`',COLUMN_NAME,'`')
        ORDER BY IF( INDEX_TYPE = 'BTREE'
        , SEQ_IN_INDEX
        , 0)
        , COLUMN_NAME
        ) COLUMNS
    FROM
        information_schema.STATISTICS
    GROUP BY
        TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,INDEX_TYPE,NON_UNIQUE
) ndx2
ON ndx1.TABLE_SCHEMA = ndx2.TABLE_SCHEMA
AND ndx1.TABLE_NAME = ndx2.TABLE_NAME
AND ndx1.INDEX_NAME != ndx2.INDEX_NAME
AND ndx1.INDEX_TYPE = ndx2.INDEX_TYPE
AND CASE
WHEN ndx1.COLUMNS = ndx2.COLUMNS
AND (ndx1.IS_UNIQUE = 'NO'
OR ndx1.IS_UNIQUE = ndx2.IS_UNIQUE)
THEN TRUE
WHEN ndx1.INDEX_TYPE = 'BTREE' -- when BTREE
AND INSTR(ndx2.COLUMNS, ndx1.COLUMNS) = 1
AND ndx1.IS_UNIQUE = 'NO'
THEN TRUE
ELSE FALSE
END
GROUP BY ndx1.TABLE_SCHEMA,ndx1.TABLE_NAME,REDUNDANT_INDEX_NAME
;

Obviously, the indexes with the least column per grouping need to be eliminated

Find unused indexes on the basis of selectivity.

I have not done much with unused indexes in my developer days. I try to make only necesssary indexes that match the following clauses:

WHERE
GROUP BY
ORDER BY

In the event you have to cleanup a database by hunting down unused indexes, please read these:

Use Percona-Server rather than MySQL because Percona-Server has addition information_schema tables that record index usage since mysql startup.

http://www.mysqlperformanceblog.com/2009/06/26/check-unused-keys-a-tool-to-interact-with-index_statistics/
https://stackoverflow.com/a/3243517/491757

A very ancient tool called mysqlidxchk

Monitor the Server Parameters (What should be important parameters)

Innodb_buffer_pool_pages_dirty*100.0/Innodb_buffer_pool_pages_total : Pct of InnoDB Buffer Pool That Needs to Be Flushed (I would keep my eye on server load if this exceeds 5%)
(100.0 * (Delta(Innodb_buffer_pool_read_requests) - Delta(Innodb_buffer_pool_reads))) / Delta(Innodb_buffer_pool_read_requests : Read Efficiency Of InnoDB
100.0 * (1.0 - (Delta([Key_reads]) / Delta([Key_read_requests])) : Read Efficiency Of MyISAM (should be above 90%, anything less look into caching data with memcached or switching to InnoDB)

This is just a sample of the kind of global status values to monitor. Please read MySQL Documentation on the Server Status Variables.

Execute MySQL Server performance tuning script

Most straightforward script is mysqltuner.pl Just get it and run it

# wget mysqtuner.pl
# perl mysqltuner.pl

Slow logs

Slow logs can be quite helpful in a low-traffic environment. Unfortunately, I have seen too many occurrences of the following

High number of DB Connections
All Connections Running the Same Type of Query
Query in one connections blocking dozens of others needing the same table or row (even with InnoDB due to deadlocking issues associated with the Clustered Index when doing UPDATEs)

Given this scenario, I have queries that work standalone with blazing speed grind to a halt when an inundiation of queries needing common tables.

IMHO the slow query log actually does you no good becase it records completed queries that are regarded as slow. What you really want to do is catch long-running queries in the act of being long-running. Therefore, I would recommend using pt-query-digest to pool the processlist (or tmpdump) for queries running amok. I wrote a post back in December 2011 on how to script a crontab job that polls the processlist every 20 minutes using mk-query-digest (pt-query-digest can be inserted in it place).


Oracle 11gR2: ORA-01102: cannot mount database in EXCLUSIVE mode I am running Oracle 11gR2 on an AIX box.  I did a rename database and change DB ID using NID.  I can restart db in a nomount mode, but I can not mount it by the following error.

ORA-01102: cannot mount database in EXCLUSIVE mode
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01507: database not mounted

I started DB by using a new pfile.  I notice that the NID utility did not change the old DB folder names in ASM.  So this rename database effort will not be perfect.

I have already removed some easy things when I Googled this error, such as removing the lk* files under $ORACLE_HOME/dbs.

Any suggestions for me to start the database after the renaming command of NID?

Thank you so much!

ORA-01102 cannot mount database in EXCLUSIVE mode

  Cause: Some other instance has the database mounted exclusive or shared.
  Action: Shut down the other instance or mount in a compatible mode.

Find whether you have any running Oracle process.

$ ps -ef | grep smon

Kill the process then try to start again.

Since I dont have the procedure which you have followed to perform DB rename and DBID change I can not say whether there is problem with your procedure.

I would recommend to follow Oracle Knowledge Document to perform such task.
How to Change the DBID and the DBNAME by using NID


Solving intra parallel query deadlocks We are experiencing deadlocks in our production environment. Seems to be the victim and winner are the same spid's. We rebuild/reorganize indexes daily and we dont find any missing indexes.

Have you guys ever encountered this scenario and if so how did you guys solve it?  Any suggestions are appreciated.

Here's the deadlock graph:

Additionally, here's

the deadlock XML: https://justpaste.it/6xasd
and the query plan: https://www.brentozar.com/pastetheplan/?id=ryBbs-XPQ

We are on SQL Server 2017 SP2.

First off, intra-query parallelism deadlocks are always a SQL Server bug.  There's not much you can do to fix them, other than updating SQL Server, opening a support case, suppressing parallelism (eg adding a MAXDOP 1 hint), or trying to get a query that uses a simpler plan.
In this case in EF, if you can, set UseDatabaseNullSemantics to true.  Otherwise the EF query translator generates TSQL queries that emulate the C# null comparison semantics.  That will clean up the NULL handling in the query.
Other than that, this query appears to be a view, joined with another table, and then a paging query on top of that.  So possibly can be simplified if you can figure out what the intent is.


Postgresql - multi-tenant database - backup/restore single database I am thinking about development of simple CRM for florists. Florists (company with 1-5 employees) will have local installed app on their PCs (call it "Floppa"). Floppa will be communicating to Windows Communication Foundation (WCF) service hosted on my remote server. For each of customer there will be running one instance of WCF service. Business layer under WCF service will be communicating to Postgresql database dedicated to specific customer.

I would like to use continuous archiving. If I understand well WALs will contain logs for every change in every database in cluster.

If one of customers will accidentally deletes/change some data and will ask me for recovery "today's morning state of data" how could I quickly comply his request? According my current knowledge I would need to do:

Stop WCF service (to prevent work on db which will be replace later)
Restore whole cluster to "today's morning state" on another computer
Dump customer's morning state database
Replace customer's database by dumped one
Start WCF service

Is that correct? Or is there any better solution to this backup/restore issue? What happen if I will create one database per cluster? What will be the drawbacks? I guess that one of advantages will be much more faster recovery.

What will differ if count of customers will be 10, 50, 100, 250? How Postgresql handles hundreds of clusters?

Thanks.

Restoring the database will take as long as it takes.  There is no way to know with the information you provide.  If each individual database is quite small, step 2 could be acceptably fast with dozens or maybe even hundreds of databases in the cluster.

Having many database clusters will incur more overhead.  There is a certain minimal size of a cluster (around 21MB for the usual initdb run) which can be amortized over all databases in the cluster. You will have to manage many wal streams, and if you most archiving is driven by an archive_timeout that means you will have many more files in total by dividing them.  You will likely run out of available semaphores, but you can recover some of them by lower the max_connections setting.  On modern OS, you could probably configure the system
to allow a lot of semaphores.  You should be able to test some of this stuff on your own infrastructure with some relatively simple scripting.

But if you do run one cluster for each customer and have 100s of customers, there is no reason you couldn't spread them over multiple machines.


Do valid to/from columns on a fact table make sense? I recently came across "valid from" and "valid to" columns on a fact table. Obviously this is common for dimensions but I've not seen them on fact tables before and can't find any info suggesting they're widely used.

The table granularity is analogous with bank account transactions, so a new fact is generated when a deposit is made, or an amount withdrawn. The reason given for inclusion of the columns is the need for consistent reporting (if we run this report today it must be the same as it was a week ago) combined with poor quality of source systems/data; users are able to go into the source system and say "this deposit was actually only £10, not £100". When this happens a second fact row is inserted and the original row is expired.

In my mind, a new fact row should be inserted to reverse the original in order to maintain the history (applying -£100 in the example) and the updated fact should be inserted (+£10). It feels like working with the valid to/from columns introduces too much complexity for users when reporting as well as the risk of error (summing up both active and expired facts).

Does anyone have experience of this and are there any references which specifically cover it (blog posts, articles or even books)?

The validity depends on how users want to look at the data.  You are looking at it as just a transaction fact. Other types of fact tables include periodic snapshots and accumulating snapshots.  If you want to see all the times that someone corrected a row to help decrease erroneous entries, the effective dates may be appropriate so it's clear that the transaction was updated. This creates a fact table that is somewhat similar to a type 2 SCD.

The Kimball Group has an article that directly addresses your question.

Here's a Kimball Group design tip  that talks about effective dated accumulating snpashot fact tables.

You may be correct that you should just add transactions that reverse the original row. That sounds like it could be a valid solution if you just need to see the transactions and sum them up. This is how I've seen most accounting data work. Kimball says the effective dated fact tables may be useful for quickly calculating account balances at a paritcular point in time, especially for tracking slowly changing balances. But this is a fairly rare case. I think your concern that it will be confusing for users is also valid. You have to decide if that can be overcome by education and if its worth it for the added analytical capabilites in the data.

I haven't had to do this much in my data warehousing experience because most of my facts were simple transactions or periodic snapshots. But I have created several effective dated bridge tables for many-to-many relationships.


Should a support incident be opened for every stack dump, or just ones that cause an outage? Assuming that the stack dump in question can't be mapped to a know issue, if a stack dump doesn't seem to cause a problem and everything is working afterwards, should a support incident be opened for it?  Or should incidents be opened only when it causes an outage or loss of functionality?

It's fairly cut and dry: If you want to actually work on the issue, collect data, uninstall/reinstall, isolate, test, potentially provide more dumps, potentially use things like appverifier and debugdiag or have a valid repro, then by all means open a ticket so it can be looked into and worked on.

If you just want to ship off a stack dump, with no other data, and have someone look at it in a vacuum, then I would not open a ticket as it'll have no traction and go nowhere. Neither party will be happy.

That's the short and sweet version.


Massive joins Vs Updating temp table I'm working on a query that essentially aggregates a slew of tables together.

The first method I tried was to inner join all of the tables into one nice big query that pretty much looked like this:

select
  a.col1
, a.col2
, a.col3
...
, b.col1
...
...
, l.col1
, l.col2
from myprimarytable a
join secondtable b on a.key = b.key
join third table c on a.key = c.key
...
join thelasttable l on a.key = b.key

This took forever to run. However, a second solution that looked like this:

create table #output (
  primkey char(40)
, mycol1 char(1) null
, mycol2 decimal(20,1) null
...
, mylastcol varchar(max) null
)

insert into #output
select
  a.col1
, a.col2
...
from myprimarytable a

update #output set
 mycol3 = b.col1
, mycol4 = b.col2
...
from #output a
join secondtable b on a.primkey = b.key
...

My question:

Why does the temp table with updates take so much less time ( about 30x faster in my particular case), than one with all of the joins in one go?

SQL Server has an upper bound on creating efficient query plans given a moderately complex query involving a lot of joins - there isn't a single upper bound or magic formula to determine when a query is complex enough to cause a problem; it is very case-by-case and involves baselining from some known expectation (people sometimes think a certain query shouldn't take 30 seconds, but sometimes that's really the best and most optimal outcome for that context). In other cases, SQL Server is simply overwhelmed and can't (at least when not given infinite time) quickly come up with an optimal plan. So it will spend some time coming up with a "best plan I can find in this time" and then it will show "Reason for early termination: timeout" and you get what you get. You can force SQL Server to spend more time coming up with a plan using trace flag 2301 (which Paul White mentions and links to a more thorough piece in this answer), but more time may not prove to help that much (and more compile time may not be a good thing for end user performance on the whole). Another alternative is to break up the logic so SQL Server can focus on optimizing smaller, less complex queries.

Think of it like eating a pie - you can probably eat a pretty good-sized piece in one sitting, but if your goal is to eat the whole pie, you might want to spread it out across multiple settings.

For SQL Server, in some cases it can do better with chunks of that logic separated. This doesn't mean you should simplify all joins into #temp table waterfalls, but in some extreme cases it is a valid workaround.


Data Synchronization process in Database Mirroring and Alwayson Groups SQL Server I have few questions on Database Mirroring and Alwayson, i am reading about always on and i found below data synchronization process from primary replica to secondary replica in msdn,

Quesitions:

Without any HA like DBM or AG, will there be any log buffer present in SQL Server WAL mechanism?
Is there any log buffer and log capture which also present in Database Mirroring, and Is that same as in Always on Groups?
What is log pool here?
Why two different name Log flush and Log Hardening?
Can any one provide a link for database mirroring sync process.
In DBM or AG if i am using Synchronous mode, How does application can know Acknowledge Commit is success on both replicas will relational engine take care of that or storage engine take care about that.

Looking forward to learn more, Thanks in Advance!

Without any HA like DBM or AG, will there be any log buffer present in
  SQL Server WAL mechanism?

As SQLAuthority Blog Here  The book online definition of the LOGBUFFER seems to be very accurate. On the system where I faced this wait type, the log file (LDF) was put on the local disk, and the data files (MDF, NDF) were put on SanDrives. My client then was not familiar about how the file distribution was supposed to be. Once we moved the LDF to a faster drive, this wait type disappeared.

  Is there any log buffer and log capture which also present in Database
  Mirroring, and Is that same as in Always on Groups?

Comparing to Database Mirroring (DBM), AlwaysOn required some serious rework in the counters and in the structure of counters, despite the fact that the transfer of the data is more or less following the very same path as in DBM. Reason for this is:

The fact that we can handle more than one secondary replica
That an Availability Group can contain more than one database.

Another few new counters got added to SQL Server:Databases. These counters give information about the SQL Server Log Cache of each individual database which is playing an instrumental role when transferring data to the secondary replicas or on the secondary for redo operations.

The counters are named:

Log Pool Cache Misses/sec – showing the number of log blocks which could not be found in the Log Cache
Log Pool Disk Reads/sec – Number of Reads issued by the Log Cache to read log blocks from disks
Log Pool Requests/sec – Number of log block reads per sec

In fact Always On is a bit different than database mirroring (DBM) with respect to sending the log blocks to the secondary replica(s).   DBM flushes the log block to disk and once completed locally, sends the block to the secondary.

for more details you can find Here and Here

  What is log pool here?

As literally log pool used in AlwaysOn availability group in SQL server Database for system monitor.

The SQLServer:Databases object in SQL Server contains performance counters that monitor transaction log activities, among other things. The following counters are particularly relevant for monitoring transaction-log activity on availability databases:

Log Flush Write Time (ms), Log Flushes/sec, Log Pool Cache Misses/sec, Log Pool Disk Reads/sec, and Log Pool Requests/sec.
For you ref Here

  Why two different name Log flush and Log Hardening? Can any one
  provide a link for database mirroring sync process.

The primary replica makes the primary databases available for read-write connections from clients. The primary replica sends transaction log records of each primary database to every secondary database. This process - known as data synchronization - occurs at the database level. Every secondary replica caches the transaction log records (hardens the log) and then applies them to its corresponding secondary database. Data synchronization occurs between the primary database and each connected secondary database, independently of the other databases. Therefore, a secondary database can be suspended or fail without affecting other secondary databases, and a primary database can be suspended or fail without affecting other primary databases.

Log is flushed on the secondary replica for hardening. After the log flush, an acknowledgement is sent back to the primary replica.

Once the log is hardened, data loss is avoided.

you can also monitor log harden through Dashboard Like right click on dashboard

for your ref Here and Here

  In DBM or AG if i am using Synchronous mode, How does application can
  know Acknowledge Commit is success on both replicas will relational
  engine take care of that or storage engine take care about that.

Through TSQL query in primary replica we know that synchronization is update in both replica or not.

select * from master.sys.dm_hadr_database_replica_states;
Go

I am also attaching the screen shot here

Here synchronization_state is 2.

2 = Synchronized. A primary database shows SYNCHRONIZED in place of SYNCHRONIZING. A synchronous-commit secondary database shows synchronized when the local cache says the database is failover ready and is synchronizing.


How can a MySQL delete statement affect a table with over 30 millions records? I have to delete about 28 million records from a InnoDB table in MySQL.
I am wondering what happens if I am doing a select statement on the same table during the delete operation.
I guess the delete will take some time to complete.

I am using MySQL version 5.6.
I don't want to delete all records I will have a where condition or maybe having.
Thank you for your time.

Instead of deleting a large percentage of the rows you might better negate your WHERE-condition and INSERT/SELECT the remaining rows into a new table. Then DROP the original table and RENAME new to old (or TRUNCATE and re-INSERT). This way there's only a very small period (DROP/RENAME) where the table is not available. It's a very common process in a Data Warehouse where you deal with really large numbers of rows.

You need to run both the INSERT/SELECT and the DROP TABLE within a single transaction, so no other session is able to do any DML between the INSER and the DROP. I don't know the exact mySQL/InnoDb syntax and transaction behavior, but this is a skeleton (of course you need to test before):

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; --
START TRANSACTION;
LOCK TABLES newTab WRITE;
INSERT INTO newTab SELECT * FROM oldTab;
DROP TABLE oldTab;  -- might need a COMMIT?;
RENAME newTab TO oldTab;
COMMIT; -- ?


How are other computers able to connect to my database I have installed the software on my clients system where sql server was already installed. I have installed another named instance.

I am able to login to the SQL server from another computers, when I check the settings if found that.

TCP IP of my named instance is Disabled
SQL Browser is running
There is no UDP port open for sql browser i.e 1434
SQLSever.exe is not added as exception in Firewall
Firewall settings are off in the Quickheal antivirus
I am using connection string as "computername\instancename" to connect

I wish to know how the are other computers able to connect when Port 1434 is off

UDP 1434 is only for the discovery of database and port. If you still have named pipes enabled and provide the correct name or IP Adress of the host where your database is running and it runs on default port 1433 you will be able to connect. With right username + password.


Does subqueries "short-circuit" based on other conditions? I have a slightly costly case when exists (...) then 1 else 0 end sub-query in the select clause. The sub-query is only really needed if another column in the main query has a specific value (can think of it as a has_child flag).

As the main query returns tens of thousands of rows and runs frequently, it's a prime target for optimisation.

I have tried adding has_child = 1 into the case when clause, but that did not change the explain plan at all (understandably). But real query test feels quicker. Unfortunately, I cannot gather statistics on the production database, so I have no idea if the improvement is real or not.

So is Oracle actually able to skip/stop the join/sub-query if the condition guarding it is already false?

The explain plan looks like:

Yes, they do.

select
  case
    when (1 = 0 and (select 1/0 from dual) = 1)
    then 1
    else 0
  end as c
from dual;

         C
----------
         0

Here 1 = 0 is false, so the second argument of the AND is not executed, luckily for us, because it would be a division by zero. With 1 = 1 the second part is executed and we receive the expected error:

select
  case
    when (1 = 1 and (select 1/0 from dual) = 1)
    then 1
    else 0
  end as c
from dual;

ERROR at line 3:
ORA-01476: divisor is equal to zero


Alter table with casting Is there a way of run an alter table command with cast on MySQL databases?

I know that is possible to do it with Postgresql and Sql servers, but I didn't find anything about it when searching the documentation..

My database is very large and I want to convert Decimal to BigInt, so I'm trying to do it with a single command and avoid workarounds..

You can do it using a single statement.

CREATE TABLE T (val decimal(18,3));

INSERT INTO T VALUES (1.25);
INSERT INTO T VALUES (12.526);
INSERT INTO T VALUES (1000.25);

SELECT val FROM T;

|      val |
| -------: |
|    1.250 |
|   12.526 |
| 1000.250 |

ALTER TABLE T MODIFY COLUMN val bigint;

SELECT * FROM T;

|  val |
| ---: |
|    1 |
|   13 |
| 1000 |

dbfiddle here


Amazon Redshift - The difference between Query Slots, Concurrency and Queues? We are building a business intelligence system, and we have a huge PostgreSQL Database (DB) where we make all the info processing, and a Redshift Data Warehouse (DWH) where we store the data and execute queries.

The backend is built in Java Server Faces (JSF), and before, the queries were made lineal. Some views were taking more than a minute to finish queries and load the info in screen, so we decided to use threads in Java and make the queries asynchronous.

So we have prepared the necessary queries for each view to be run parallel from our EC2 app machine, to our Redshift DWH, and run the threads but views still take long to load, sometimes even longer.

We found in the documentation:

http://docs.aws.amazon.com/redshift/latest/dg/cm-c-executing-queries.html

http://docs.aws.amazon.com/redshift/latest/dg/c_troubleshooting_query_performance.html

http://docs.aws.amazon.com/redshift/latest/dg/r_wlm_query_slot_count.html

That redshift by default receive 5 queries at same time, but that is a setting we can change.

There are 3 main things to take into account: query slots, concurrency and queues. We have understood this:

A queue is like a thread in Java. A query arrives and is designated to the "less loaded" queue, and it waits for its turn to be resolved. We can have as many queues as we like. A queue has some memory allocated (we guess divided equally?)
In a queue we can assign user groups or queries groups. But in short-term, that's a lot of classification work in our queries we can't do right now.

A concurrency is the amount of queries that a queue can run in parallel. By default is 5.

And a query slot is the amount of memory a query can use. It's related to concurrency as we understood it. The more concurrency a queue has, the less memory in each query slot it has.

We have tried to have 3 queues, each one with concurrecy 5, but performance still slow.

So, if we have understood correctly, having that some viewes make up to 25-28 queries, and the total amount of loading time is around 60s, how can we leave the settings the queries can be resolved faster?

I think your understanding of query queues is a little off.

  A queue is like a thread in Java. A query arrives and is designated to the "less loaded" queue, and it waits for its turn to be resolved.

The decision of where to put a query is independent of how busy a queue is; queries are allocated based on the rules you've set up: http://docs.aws.amazon.com/redshift/latest/dg/cm-c-wlm-queue-assignment-rules.html

  We can have as many queues as we like.

Not quite, check out http://docs.aws.amazon.com/redshift/latest/dg/cm-c-defining-query-queues.html. The relevant line is

  The maximum total concurrency level for all user-defined queues, not including the reserved Superuser queue, is 50.

So the concurrency is definitely limited, but that's a reasonable restriction because each concurrency slot reserves some of the cluster's resources.

  A queue has some memory allocated (we guess divided equally?)

By default memory is split equally among queues, but you can assign memory at the granularity of 1% of cluster memory. See the above link about defining query queues.

  In a queue we can assign user groups or queries groups. But in short-term, that's a lot of classification work in our queries we can't do right now.

Creating user groups and setting query groups is actually pretty simple, see http://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_GROUP.html and http://docs.aws.amazon.com/redshift/latest/dg/r_query_group.html.

If you didn't set up user groups or query groups, then my guess is that you didn't see any improvement after adding the extra queues because all the queries still ran in a single query queue. (In fact, the extra queues would simply take resources from the queue running your queries.) Since all your queries are running from the same user and for the same purpose, it makes sense that they should all resolve to the same query queue. A better solution might be to have only a single queue, but increase the concurrency level. (Keep in mind that a query queue's resources are divided equally among all concurrency slots, even if they're not being used, so a concurrency level of 50 means no query gets more than 2% of the total resources.)

All that being said, redshift is a higher-latency system than many other database solutions. If you're only looking to run lots of small queries, then it might not be the best fit for your problem.


Oracle 10g: query takes long time and adding index does not help I have a query which takes indefinite time, taking almost 60% of CPU time for 3 hours then simply hangs the PLSQL process.
What I do is:

Get "explain plan" for the query
I noticed a "table scan" in the "explain plan" graph
I added an index for the specific column
I refreshed statistics for this table

I now get "explain plan" but the figures are the same and no execution optimization.

Any ideas?

The optimizer may still look at the table scan to be a good idea.  To insure the optimizer uses the index, have you tried adding a hint to your query to insure the optimizer uses the index?  And echoing the earlier response, more information to work with would be nice.


Manually increase AUTO_INCREMENT column by one I was wondering if there is a simple way to manually increase the auto increment vaulue of a MySQL column by one.

The use case would be to keep the indexes in sync of two tables. In my case they usually both get a new entry at the same time. But sometimes only one of them gets a new entry. This causes the IDs to desync, which is not what I want. Due to limitations in the code I cannot retrieve the ID of any inserted rows, as there's a fair amount of limitations on the API I use to communicate with the database. I can however execute queries to my hearts content. So a simple query to bump the value by one would be much apprechiated!

I make no promises with this one, but you might get away with the following:

For a table mytable in the mydb database

SET @src_db = 'mydb';
SET @src_tb = 'mytable';
SELECT CONCAT('ALTER TABLE ',@src_db,'.',@src_tb,' AUTO_INCREMENT=',auto_increment+1)
INTO @sql FROM information_schema.tables
WHERE table_schema=@src_db AND table_name=@src_tb;
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

After running this, you can verify with

SHOW CREATE TABLE mydb.mytable\G

I just tried this with a table ganeshbora.customer on my Windows laptop (5.7.12)

SAMPLE CODE

SET @src_db = 'ganeshbora';
SET @src_tb = 'customer';
SHOW CREATE TABLE ganeshbora.customer\G
SELECT CONCAT('ALTER TABLE ',@src_db,'.',@src_tb,' AUTO_INCREMENT=',auto_increment+1)
INTO @sql FROM information_schema.tables
WHERE table_schema=@src_db AND table_name=@src_tb;
SELECT @sql;
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;
SHOW CREATE TABLE ganeshbora.customer\G

SAMPLE CODE EXECUTED

mysql> SET @src_db = 'ganeshbora';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @src_tb = 'customer';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE TABLE ganeshbora.customer\G
*************************** 1. row ***************************
       Table: customer
Create Table: CREATE TABLE `customer` (
  `cust_id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(20) DEFAULT NULL,
  `lastname` varchar(20) DEFAULT NULL,
  `address` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`cust_id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SELECT CONCAT('ALTER TABLE ',@src_db,'.',@src_tb,' AUTO_INCREMENT=',auto_increment+1)
    -> INTO @sql FROM information_schema.tables
    -> WHERE table_schema=@src_db AND table_name=@src_tb;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @sql;
+---------------------------------------------------+
| @sql                                              |
+---------------------------------------------------+
| ALTER TABLE ganeshbora.customer AUTO_INCREMENT=15 |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> PREPARE s FROM @sql;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE s;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DEALLOCATE PREPARE s;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE TABLE ganeshbora.customer\G
*************************** 1. row ***************************
       Table: customer
Create Table: CREATE TABLE `customer` (
  `cust_id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(20) DEFAULT NULL,
  `lastname` varchar(20) DEFAULT NULL,
  `address` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`cust_id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql>

As shown, the auto_increment of the table was incremented from 14 to 15. Of course, I did this with all privileges:

mysql> SELECT user(),current_user();
+---------------------------+---------------------------+
| user()                    | current_user()            |
+---------------------------+---------------------------+
| rolandomysqldba@localhost | rolandomysqldba@localhost |
+---------------------------+---------------------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS;
+--------------------------------------------------------------------------------+
| Grants for rolandomysqldba@localhost                                           |
+--------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'rolandomysqldba'@'localhost' WITH GRANT OPTION |
+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

So, if you have the necessary grants to do this ...

GIVE IT A TRY !!!

UPDATE 2018-06-12 14:57 EDT

A comment from JohnathanFite brings up a valid point:

  How is this going to be safe for multiple users at a time? I can see using it to fix it when it's out of sync, but at least warn the OP that running this in a multi-user environment is going to cause problems

While this answer will fix the AUTO_INCREMENT in a perfect world, we don't live in a perfect world. You would need application downtime (1-2 min) to freeze writes and current auto_increment values. If you attempt my answer in a live system, you can quickly screw up auto_increment values because rollbacks of INSERTs against a table with auto_increment values will produce gaps. In many cases, the gap increment may be more than 1 due to bulk inserts that rollback.

Please read my old post from May 12, 2015 : MySQL auto increment column increases after insertion error occurs to see how auto_increment gaps are generated.


Is it possible to refresh PgAdmin 4 query without restarting a new query after disconnect? Sometimes I leave a PgAdmin session alone for a while and when I come back to issue a query, I get an error

Server closed the connection unexpectedly.
        This probably means the server terminated abnormally
        before or while processing the request.
******* Error **********

So, I know I just need to reconnect but it seems like I have to close the query window and reconnect and open a new query window for things to work.

Is it possible to reconnect and use the same query window?

It has been suggested that I click the execute button again after the error comes, and PgAdmin will reconnect the instance and execute the query. However, that does not work. PgAdmin is not reconnecting no matter how many times I try to re-execute.

Re execute the query without closing it. It will give the result. it is working for me


Can this method for unit testing a stored procedure be improved? When I have written unit tests for my stored procedures, they usually look something like this:

/* Unit test for stored procedure which runs a query */

/* Define #temp table with structure of query results */

/* Define and set variables for stored procedure parameters */

/* If stored procedure queries from a particular table...*/

/* Truncate table and populate with sample data */

/* Call stored procedure using variables but insert results into table */

/* INSERT #temp EXEC usp_TestProc @P1=1, @P2 = 'X' */

/* Query #temp table to confirm that all fields have certain values */

/* Query #temp table to confirm that certain number of rows exist */

/* If results are incorrect, display error message */

While this will work, it feels clunky somehow and is very difficult to maintain over the course of a long-term project where the requirements are constantly changing.

Is there a better way (or standard way) to test a stored procedure
like this?
Is the difficulty of maintenance simply part of the job and worth
the effort in order to have the unit tests? I have to say that at
most of my jobs, the DBAs rarely do unit testing in the first place.

I have written a series of blogs on unit testing select stored procedures using DBTestUnit that might be of use.


I can not get Postgres to listen on anything but localhost I have been on this for MANY hours and can not understand why Postgres will not listen on anything other than localhost. I an running on Ubuntu 18.04 clean install + Django, Postgres and nginx. All up and running fine. I am now trying to get external db access working. I can access the user and db from localhost by not the server's external IP. I have checked the listen_addresses and the host entries so many times I can't even remember. Restart Postgres, check. Restart server, check. Yes, there is a firewall running, but I can't even log in using psql and the server's IP. I can if I use localhost...

Anyone think of something I haven't tried yet??

csadmin@BulletinBoardServer:~$ netstat -anpt |  grep LISTEN
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 0.0.0.0:80              0.0.0.0:*               LISTEN      -
tcp        0      0 127.0.0.53:53           0.0.0.0:*               LISTEN      -
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      -
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      -
tcp6       0      0 :::80                   :::*                    LISTEN      -
tcp6       0      0 :::22                   :::*                    LISTEN      -
csadmin@BulletinBoardServer:~$ sudo tail -20 /etc/postgresql/10/main/pg_hba.conf
# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all     all     0.0.0.0/0       md5
#host    all             all             127.0.0.1/32            md5
#host    cs250phpdata    cs250student    96.***.47.54/0     md5
#host   cs250phpdata    cs250student    new***cs.com/0      md5
# IPv6 local connections:
host    all     all     ::/0            md5
#host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5
csadmin@BulletinBoardServer:~$ sudo cat /etc/postgresql/10/main/postgresql.conf |grep listen_
#listen_addresses = 'localhost'         # what IP address(es) to listen on;
listen_addresses = '*'          # Added 8/31/18 to allow external access.
csadmin@BulletinBoardServer:~$ sudo ufw status verbose
Status: active
Logging: on (low)
Default: deny (incoming), allow (outgoing), disabled (routed)
New profiles: skip

To                         Action      From
--                         ------      ----
22/tcp (OpenSSH)           ALLOW IN    Anywhere
80,443/tcp (Nginx Full)    ALLOW IN    Anywhere
5432                       ALLOW IN    Anywhere
22/tcp (OpenSSH (v6))      ALLOW IN    Anywhere (v6)
80,443/tcp (Nginx Full (v6)) ALLOW IN    Anywhere (v6)
5432 (v6)                  ALLOW IN    Anywhere (v6)

csadmin@BulletinBoardServer:~$

I just wanted to make sure that this showed up as solved.

I had unknowingly installed a version 9.5 and a version 10. I had been editing the version 10 config, not knowing that both versions were running.

sudo service postgresql stop
sudo pg_renamecluster 10 main main_pristine
sudo pg_upgradecluster 9.5 main
sudo service postgresql start
sudo pg_dropcluster 9.3 main

Make sure you have copies of your postgresql.conf and pg_hba.conf files before you start as these got returned to default state when I did the upgrade.

Now it makes A LOT of sense why my config changes weren't taking effect ;-)

Many thanks to Colin 't Hart for the invaluable clue!


SQL to update table with records from second table (but only those with distinct value in a given column) Using PostgreSQL 10, I have two tables (say T1 and T2) where I would like to INSERT into columns A and B of table T1 records using columns D and E of table T2 while satisfying the UNIQUE constraint of column A on table T1.

Basically, ...

Find records in table T2 where a value is present in column D of table T2 that is not present in column A of table T1.
Take at most one record for each distinct column D value identified in (1) (doesn't matter which one if multiple records exist) and insert it (along with one or more other columns from table T2) into table T1.

Table illustration:

T1:
  UID : primary key, autoassigned by database sequence
  A   : Unique constraint (distinct values from column D of Table T2)
  B   : Data field #1
  C   : Data field #2
T2:
  UID : primary key
  D   : Multiple values
  E   : Data field #1
  F   : Data field #3

Based on research thus far and searches of somewhat similar questions, I believe that I'm looking for something akin to the following:

INSERT INTO T1 ("A","B")
SELECT T2."D", T2."E"
FROM T2 LEFT INNER JOIN LATERAL (...) ON (...)
WHERE T1."UID" IS NULL;

I'm unclear how to formulate the query to perform the desired result. For example, given the following data, I'd like the query to INSERT either {w,e} or {w,g} into columns {A,B} of Table #1:

T1:                   T2:
     UID | A | B | C      UID | D | E | F
    -----+---+---+---    -----+---+---+---
      0    x   a   b      100   w   e   f
      1    y   c   d      200   w   g   h
                          300   x   a   i
                          400   y   c   j

T1: (post-transaction):
     UID | A |  B  | C
    -----+---+-----+---
      0    x    a    b
      1    y    c    d
      2    w   e|g   NULL

Thank you in advance.

Update (2018-08-06 9:40 AM EDT): Changed post-transaction value in column C of T1 for newly inserted record to NULL to avoid ambiguity.

Use NOT EXISTS to check that no matching record already exists in t1 and the row_number() window function to pick only one row per t2.d.

INSERT INTO t1
            (a,
             b)
            SELECT x.d,
                   x.e
                   FROM (SELECT t21.d,
                                t21.e,
                                row_number() OVER (PARTITION BY t21.d) rn
                                FROM t2 t21
                                WHERE NOT EXISTS (SELECT *
                                                         FROM t1 t11
                                                         WHERE t11.a = t21.d)) x
                   WHERE x.rn = 1;

I don't know how t1.c should be calculated, that's not clear from your post. So I left this part out. But maybe this helps to solve the main problem.


Efficiently comparing prices in different currencies I want to make it possible for user to search products within a price range. User should be able to use any currency (USD, EUR, GBP, JPY, ...), no matter what currency is set by the product. So, the product price is 200USD and, if the user searches the products that costs 100EUR - 200EUR, he still may find it. How to make it fast and effective?

Here is what I have done until now. I store the price, currency code and the calculated_price that is the price in Euros (EUR) that is the default currency.

CREATE TABLE "products" (
  "id" serial,
  "price" numeric NOT NULL,
  "currency" char(3),
  "calculated_price" numeric NOT NULL,
  CONSTRAINT "products_id_pkey" PRIMARY KEY ("id")
);

CREATE TABLE "currencies" (
  "id" char(3) NOT NULL,
  "modified" timestamp NOT NULL,
  "is_default" boolean NOT NULL DEFAULT 'f',
  "value" numeric NOT NULL,       -- ratio additional to the default currency
  CONSTRAINT "currencies_id_pkey" PRIMARY KEY ("id")
);

INSERT INTO "currencies" (id, modified, is_default, value)
  VALUES
  ('EUR', '2012-05-17 11:38:45', 't', 1.0),
  ('USD', '2012-05-17 11:38:45', 'f', '1.2724'),
  ('GBP', '2012-05-17 11:38:45', 'f', '0.8005');

INSERT INTO "products" (price, currency, calculated_price)
  SELECT 200.0 AS price, 'USD' AS currency, (200.0 / value) AS calculated_price
    FROM "currencies" WHERE id = 'USD';

If user is searching with other currency, let's say USD, we calculate the price in EUR and search the calculated_price column.

SELECT * FROM "products" WHERE calculated_price > 100.0 AND calculated_price < 200.0;

In this way we can compare prices very fast, because we don't need to calculate the actual price for every row, because it's calculated once.

Bad thing is that at least every day we have to re-calculate the default_price for all rows, because the currency rates has been changed.

Is there a better way to do deal with this?

Isn't there any other clever solution? Maybe some mathematical formula? I have an idea that the calculated_price is a ratio against some variable X and, when the currency changes, we update only that variable X, not the calculated_price, so we even don't need to update anything (rows)... Maybe some mathematician can solve it like this?

Here's a different approach for which recomputing the calculated_price is just an optimization, as opposed to being strictly necessary.

Suppose that in the currencies tables, you add another column, last_rate, which contains the exchange rate at the time the calculated_price was last updated, no matter when this happened.

To quickly retrieve a set of products with a price point between, say, 50 USD and 100 USD that include the desired results, you could do something like that:

  SELECT * FROM products
   WHERE calculated_price > 50.0/(:last_rate*
    (SELECT coalesce(max(value/last_rate),1) FROM currencies
      WHERE value>last_rate))
   AND calculated_price < 100.0/ (:last_rate*
    (SELECT coalesce(min(value/last_rate),1) FROM currencies
      WHERE value<last_rate))

where :last_rate contains the EUR/USD exchange rate at the time of the last update. The idea is to increase the interval to take into account the maximum variation of every currency. The increase factors for both ends of the interval are constant between rates updates, so they could be pre-computed.

Since the rates change only slightly over short periods of time, the above query is likely to give a close approximation of the final result. To get the final result, let's filter out the products for which the prices have slipped out of the bounds due to the changes in rates since the last update of calculated_price:

  WITH p AS (
   SELECT * FROM products
   WHERE calculated_price > 50.0/(:last_rate*
    (SELECT coalesce(max(value/last_rate),1) FROM currencies
      WHERE value>last_rate))
   AND calculated_price < 100.0/ (:last_rate*
    (SELECT coalesce(min(value/last_rate),1) FROM currencies
      WHERE value<last_rate))
  )
  SELECT price,c.value FROM p join currencies c on (p.currency=c.id)
     WHERE price/c.value>50/:current_rate
       AND price/c.value<100/:current_rate;

where :current_rate is the more up-to-date rate with EUR for the money choosen by the user.

The efficiency comes from the fact that the range of rates is supposed to be small, the values being close together.


How to grant user to dbms_crypto ?? Oracle (User which created inside SYSTEM) Couldn't find user

I tried this

grant execute on sys.dbms_crypto to "DB_Project";

But error: ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"

Also tried this:

grant execute on sys.dbms_crypto to SYSTEM."DB_Project";

ERROR: 00000 -  "SQL command not properly ended"

How to solve???

In your first attempt it would appear that the unknown user issuing the command doesn't himself have access sys.dbms_crypto.  In the second case, privs are GRANTed to either users or roles.  Neither users nor roles are themselves part of some other schema, as suggested by your qualification of "DB_Project" with the schema SYSTEM is invalid syntax for a GRANT.

Also beware that you have enclosed "DB_Project" in double-quotes.  This will force case sensitivity on the name, which is a Very Bad Practice in oracle.  If you create an object name enclosed in double-quotes, you will always and forever have to refer to in in double quotes and the exact case in which it was created. Create the name without double-quotes and it will be case INsensitve .. you will always be able to refere to it in any case you want, as long as you do not enclose it in  double-quotes.


Blocked Process Report I have tons of BPR in .xel file (XE) and I want to be able to view them in a graph form and dump them all in SQL Server table, is there any way to do so?
Instead of the 5 sec threshold is there any way I can see the reports coming in  LIVE?

This is only a partial answer as your requirements for viewing the reports in graph form are a bit vague for me, but that could also be because I tend not to aggregate blocking reports; instead I enable their collection on an as-needed basis.

This answer addresses the 5 second threshold.  As already pointed out by @Aaron Bertrand in the comments, blocking is a natural and expected behavior of SQL Server.  You can adjust the duration that blocking needs to occur before a query shows up in the Extended Event blocking report by adjusting the blocked process threshold (s) value via sp_configure.

The default value is 0, and while misleading isn't number of seconds, it means Off; all values greater than 0 refer to seconds.  The minimum number of seconds that blocking is required before queries will show up in the report is 1 second per my experience.  Also, take note that there are no guarantees when collecting blocked processes via the blocked process report.  As noted in the aforementioned article:

  The blocked process report is done on a best effort basis. There is no guarantee of any real-time or even close to real-time reporting.


What indexes to use for GroupWise max I have a user table and group table, and a many to many relationship between them.

I have a vote table (one to many relation between group and vote) And a subitem table (one to many between vote and subitem)

And I have a sub_item_actions table which is supposed to record which user performed what kind of action on which subitem. It has columns:

userid
grp_id
subitemid
vote_id
action type (enum)

For action type, it has the following values: delete update cast uncast

User can cast and uncast a subitem. For a user to 'belong' to a subitem , the latest cast or uncast action that the user has performed on a subitem should be of type cast. This means user has last cast on a subitem and has not uncast it yet.

I want to get all users that 'belong' to a sub item.

This is the query I am using :

select
    act.*
from (
       select sub_item_id, user_id, max(created_at) as newestAction
       from vote_sub_item_action use
       where sub_item_id = 10
         AND action_type IN ('ACTION_CAST_VOTE', 'action_uncast_vote')
       GROUP BY user_id
     ) as x
   inner join
     vote_sub_item_action as act
   on  act.user_id = x.user_id
   and act.created_at = newestAction
   and act.action_type = 'action_cast_vote'
   and act.sub_item_id = x.sub_item_id;

How should I index this table ?

I am using mysql (version 5.7)

Based on the information you have provided, here is my suggestion on how to get good performance when querying the latest users that have cast a vote on a given sub item (performance is assumed to be your goal, given that you are asking about what indexes to add.)

Create the following table:

CREATE TABLE vote_sub_item_cast_uncast (
vote_sub_item_id INT UNSIGNED NOT NULL,
user_id INT UNSIGNED NOT NULL,
created_at TIMESTAMP NOT NULL,
vote_sub_item_action_id INT UNSIGNED NOT NULL,
PRIMARY KEY (`vote_sub_item_id`,`user_id`,`created_at`),
CONSTRAINT `VoteSubItemAction_VoteSubItemCastUncast_fk`
    FOREIGN KEY (`vote_sub_item_action_id`)
    REFERENCES `vote_sub_item_action` (`id`),
CONSTRAINT `VoteSubItem_VoteSubItemCastUncast_fk`
    FOREIGN KEY (`vote_sub_item_id`)
    REFERENCES `vote_sub_item` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

The foreign keys are not required, but are there largely to help describe what columns point to which parent tables.

Whenever you insert into the vote_sub_item_action table, you must also insert into this new vote_sub_item_cast_uncast table, IF and ONLY IF, the action type is action_uncast_vote or action_cast_vote.

Then, you will be able to run the following query:

SELECT
vsicu.*
FROM vote_sub_item_cast_uncast vsicu
LEFT OUTER JOIN vote_sub_item_cast_uncast later_vsicu
ON later_vsicu.vote_sub_item_id = vsicu.vote_sub_item_id
AND later_vsicu.user_id = vsicu.user_id
AND later_vsicu.created_at > vsicu.created_at
INNER JOIN vote_sub_item_action vsia
ON vsia.id = vsicu.vote_sub_item_action_id
WHERE vsicu.vote_sub_item_id IS NULL
AND vsia.action_type = 'action_cast_vote'
AND vsicu.vote_sub_item_id = 10

The strategy employed here is the (second) one describe in the manual, for finding the groupwise maximum of a given column.

The primary key of the new table will be all the index you need. From your example, you are supplying the vote_sub_item_id as a constant, which is why that column is listed first. Then each user_id can have as many entries as there are unique created_at values (I think you have sub-second resolution in 5.7, but if not, that means at most once cast/uncast per second - be aware of this, and that your current query has an issue when querying such cases.)

The LEFT OUTER JOIN vote_sub_item_cast_uncast allows us to do an anti-join, and will return at most one row for each distinct user_id for the given vote_sub_item_id. From there, the INNER JOIN vote_sub_item_action will bring us back to the that table, allowing you to select the columns you need.

Why not just an index

I don't believe an index would be able to help, because of your IN clause, which functions as an OR and generally destroys the ability to use an index effectively. Your query itself is valid, and but I do not believe it would be fast for large amounts of data, due to that IN statement.


Schemas and user rights I have 2 schemas, lets say sch1 and sch2. Each owned by different user.

I do want to grant SELECT rights on sch2.MyTable (but just this one object) to sch1 procedures/views AND allow to reference sch2.MyTable in sch1 foreign key constraints.

I do NOT want to make sch2.MyTable directly accessible to those, who uses sch2 procedures/views, but want to allow indirect access.

Basically sch2 is much more important. Owners of sch2 have control of sch1 as well, but not vice a versa. On the other hand sch1 has some data that is dependant on sch2 and they should be able to see them and should not allow delete data from sch2, while sch1 still has some dependencies on them.

Is that possible (maybe through synonyms)?

For no it seems- no, not possible. Unless take off part about "Views and Constraints" OR "different schema owners".

You need to look into using certificates to sign the stored procedures.  This will allow users to access anothers tables.


Multiple PostgreSQL running as primary server on pgpool I'm facing a problem with my pgpool setup. Currently, this is my requirement based on the pgpool documentation here :

Server A  : Install with PostgreSQL database. Run as primary server

Server B  : Install with PostgreSQL database. Run as secondary server

Server C  : Install with pgpool and pgpoolAdmin. Run as web server.

The problem starts when I'm accessing the pgpoolAdmin through Apache web server.

Both of the PostgreSQL databases are running as primary servers. It is supposed to run one as standby and the other as primary, for when failover occurs. Maybe I've messed up something when configuring this set up, but I don't know what it is. Can someone help me with this?

EDIT : Below is my pgpool.conf file

master_slave_mode = on
master_slave_sub_mode = 'stream'
wd_life_point = 3
wd_lifecheck_query = 'SELECT 1'
backend_socket_dir = '/tmp'
backend_hostname0 = '192.168.1.134'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/usr/local/pgsql/data'
backend_flag0= 'ALLOW_TO_FAILOVER'
backend_hostname1 = '192.168.1.136'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/usr/local/pgsql/data'
backend_flag1= 'ALLOW_TO_FAILOVER'

Old post, but as I have just been through the pain of configuring pgpool, and have encountered similar problems, I'll answer here.

I have exactly the same setup, but I made the choice to go through this excellent tutorial (in 6 parts ouch). It intends to build a full pgpool/pgsql HA infrastructure, where there is no single point failure with one pgpool server, as two pgpool are installed along two postgresql primary and standby servers in streaming replication.

Anyway it is quite easy to adapt in order to keep pgpool one one separate server like I did.

Regarding our problem here, I think it could be related to an incomplete primary/standby setup. And anyone having this problem shall try to setup streaming replication at postgresql level before trying to attach new node with pgpool (particularly when you have a database that is not empty).

In postgresql.conf I would check REPLICATION section on both servers and make sure that the standby server has hot_standby = on. Make sure also that you have correct recovery.conf file on standby server.


Manually entering data using Management Studio How can I enter data through the GUI interface rather than an insert command in SQL Server Management Studio 2012? I can't find it anywhere whereas there used to be an option in the previous versions. There used to be a table GUI where you could point to a certain record and add/modify date. Can't find it in 2012.

You should be able to add new rows by right-clicking the table that you're interested in, and then selecting Edit Top 200 Rows.

If required, the value for this can be increased from 200 via the tools menu: Tools -> Options -> SQL Server Object Explorer -> Value for Edit Top <n> Rows command.

Presumably, the reason that they limit the number of rows returned is because this is not a robust way to manipulate data. The risk that you'll press the wrong key and accidentally overwrite important data is quite high.

If this isn't a live, production-critical database, and you're interested in regularly working with the data in a tabular/spreadsheet view, you may want to consider linking Microsoft Access to your database.


PostgreSQL synchronized commit performance I'm setting up a pair of PostgreSQL servers. Because cash is short I can only afford one high class server and some crapy backup box. If the expensive high end box should catch fire, operations can live with the slower backup system for a while.

I was wondering how much performance does the backup server need? It receives WAL and only has to apply it (I prosume that is the way syncrhonized commits work?). Is WAL always easy to apply, or would for example a delete with many checks on foreign key constraints, also have to do these constraint checks on the slow backup box?

Is the PostgreSQL WAL logical or image based? I'm not looking for absolute numbers, more a kind of answer like: "Applying WAL over TCP in sync commit mode on the hot backup will be cheap in any szenario, except for blahblah".

Don't do this with only one slave.  If you do you are making your application more subject to failure, not less.  Basically if you do this, and you lose your slave, your master will hang.  You say your backup box is crappy, and I assume that means reliability-wise too, so this is more likely than losing your master.  This is probably not what you want.  Do not use synchronized commit option with replication and only one slave.

Synchronized commits in replication means that the master waits for the slave to acknowledge that the WAL was applied before noting that the commit happened and passing that back to the client.  If you don't get the configured quorum, you wait for it, possibly for.... a..... long..... time......

Do it async.  Use fsync set to on.  You should be golden.


Unable to connect to specific instance of Oracle RAC I did several operations by connecting successfully to all 4 instances but suddenly during one of the jobs, it failed with a TNS error. I checked the connect from SQL and one of the nodes is giving an error.

This is puzzling as I have done more than 20 operations just before this by connecting to specific instances, and on the 21st run this suddenly occurred.

I never made any changes anywhere and this just occurred suddenly! Any ideas?

SQL> connect system/oracle@realDB_1 as sysdba;

ERROR:

ORA-12520: TNS:listener could not find available handler for requested type of server

SQL> connect system/oracle@realDB_2 as sysdba;

Connected.

SQL> connect system/oracle@realDB_3 as sysdba;

Connected.

SQL> connect system/oracle@realDB_4 as sysdba;

Connected.

SQL> connect system/oracle@realDB_1 as sysdba;

ERROR:

ORA-12520: TNS:listener could not find available handler for requested type of
server

If your TNS entry is incorrect for your current configuration, for example you specify (SERVER = SHARED) in your connection descriptor, but you have not configured shared servers, you will receive this error.

But since you said you had not changed anything and this error had occurred suddenly, I am going to assume your configuration is correct. In that case, the database instance may have reached the limit for the processes it can start. You should check the alert log for ORA-00020 errors, and query the highest ever (since the last restart) process utilization of your instance:

select max_utilization, limit_value from gv$resource_limit where inst_id = 1 and resource_name = 'processes';

If you find ORA-00020 errors, or max_utilization equals to limit_value in the output of the above query, the instance reached the limit at some point, and you may need to increase the value of the processes parameter or investigate the reason for having such number of processes.


Postgres initialization of pg_basebackup Hi Im running on a centos 7 machine, Im in the process of migrating an old postgres database to the new server, it has like 10TB of data so Im trying it with pg_basebackup. The new server has an independent disk with enough storage to handle the operation, but it's not the primary disk. I would like to set the PGDATA into this big data disk.

I have followed this steps:

Install postgres-12 and postgres12-contrib
Mount the data-disk into the new machine
Create an empty pgdata directory in the data disk. All with user postgres.
Use /usr/bin/pg_basebackup -h IP_OF_OLD_MACHINE -D /mnt/disks/data-disk/pgdata -P -U USERNAME --wal-method=stream

I think pg_basebackup ended successfully, data it took several hours but finished without prompting any error at all.

total 132K
-rw-------. 1 postgres postgres  230 Mar 18 12:36 backup_label
drwx------. 7 postgres postgres 4.0K Mar 18 12:43 base
-rw-------. 1 postgres postgres   30 Mar 19 00:13 current_logfiles
drwx------. 2 postgres postgres 4.0K Mar 19 00:13 global
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 log
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_commit_ts
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_dynshmem
-rw-------. 1 postgres postgres 4.8K Mar 18 12:36 pg_hba.conf
-rw-------. 1 postgres postgres 4.7K Mar 18 12:36 pg_hba.conf~
-rw-------. 1 postgres postgres 1.6K Mar 19 00:13 pg_ident.conf
drwx------. 4 postgres postgres 4.0K Mar 18 12:36 pg_logical
drwx------. 4 postgres postgres 4.0K Mar 18 12:36 pg_multixact
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_notify
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_replslot
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_serial
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_snapshots
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_stat
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_stat_tmp
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_subtrans
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_tblspc
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_twophase
-rw-------. 1 postgres postgres    3 Mar 19 08:27 PG_VERSION
drwx------. 3 postgres postgres 4.0K Mar 18 12:36 pg_wal
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_xact
-rw-------. 1 postgres postgres   88 Mar 18 12:36 postgresql.auto.conf
-rw-------. 1 postgres postgres  24K Mar 19 08:39 postgresql.conf

Ok, now im trying to initialize a postgres server with this data, the one i've just brought.

This are my attempts, looking the documentation:

/usr/pgsql-12/bin/postgresql-12-setup initdb -D /mnt/disks/data-disk/pgdata
systemctl: invalid option -- 'D'
failed to find PGDATA setting in -D.service

/usr/pgsql-12/bin/postgresql-12-setup initdb --pgdata=/mnt/disks/data-disk/pgdata
systemctl: unrecognized option '--pgdata=/mnt/disks/data-disk/pgdata.service'
failed to find PGDATA setting in --pgdata=/mnt/disks/data-disk/pgdata.service

Is there some mistake in the process?

Update:
As @pifor told me i added the PATH to the postgres bash_profile and then started to recognize PG_DATA. The problem now is that initdb aims to find an empty folder, and its not empty due to the pg_basebackup

A physical backup (like pg_basebackup) results in an already initialized directory.  You don't run initdb.  You just start it up, possibly after changing the config files in whatever way is appropriate for your situation.

You probably should have configured this for streaming though with a named slot though, or it will have fallen behind while the backup was being taken, but that would be a different question.

Why not just move the drive array from one server to the other?  Is the desire to change the storage hardware as well as the server hardware?


How to get all roles that a user is a member of (including inherited roles)? Let's say I have two Postgresql database groups, "authors" and "editors", and two users, "maxwell" and "ernest".

create role authors;

create role editors;

create user maxwell;

create user ernest;

grant authors to editors; --editors can do what authors can do

grant editors to maxwell; --maxwell is an editor

grant authors to ernest; --ernest is an author

I would like to write a performant function that returns a list of the roles (preferably their oid's) that maxwell belongs to, something like this:

create or replace function get_all_roles() returns oid[] ...

It should return the oids for maxwell, authors, and editors (but not ernest).

But I am not sure how to do it when there is inheritance.

You can query the system catalog with a recursive query, in particular pg_auth_members:

WITH RECURSIVE cte AS (
   SELECT oid FROM pg_roles WHERE rolname = 'maxwell'

   UNION ALL
   SELECT m.roleid
   FROM   cte
   JOIN   pg_auth_members m ON m.member = cte.oid
   )
SELECT oid, oid::regrole::text AS rolename FROM cte;  -- oid & name

The manual about the cast to object identifier type regrole.

BTW 1: INHERIT is the default behavior of CREATE ROLE and doesn't have to be spelled out.

BTW 2: circular dependencies are not possible. Postgres disallows that. So we don't have to check for that.


How to generate time in milliseconds in PostgreSQL stored procedure? I have been reading the Postgres documentation about timestamps, but I am still a little bit confused.

I need to store moments in times, measured in milliseconds, between the current time and midnight, January 1, 1970 UTC (like those defined in Java, but I am not using Java).

If I read the documentation properly, I believe I have to use CURRENT_TIMESTAMP, because it includes the timezone. Correct?

Can someone explain how to create a stored procedure that will convert this timestamp into the milliseconds I want (and the reverse function too)?

Motivation: I understand one could use something like EXTRACT on CURRENT_TIMESTAMP when selecting rows, but this conversion would be more expensive than storing the time in milliseconds. I am ready to store my moments into a bigint. It would be faster to compare moments or to compute time differences.

I understand one could use something like EXTRACT on CURRENT_TIMESTAMP when selecting rows

You don't need extract in order to get the data:

select *
from your_table
where timestamp_column >= timestamp '2007-08-24 18:34:01';

will happily use an index on moment_in_time and will be very efficient - in fact there will be no difference in performance compared to

select *
from your_table
where milliseconds_column >= 1187906400000;

Now look at the two statements and tell me for which one you immediately understand what the query will return.

If you do need those milliseconds in your application then you can always do something like this:

select some_col,
       other_col,
       extract(epoch from timestamp_column) * 1000 as millis
from your_table
where timestamp_column >= timestamp '2007-08-24 18:34:01';

And again, I'm almost 100% certain that you won't see any performance difference to:

select some_col,
       other_col,
       milliseconds_column
from your_table
where  milliseconds_column >= 1187906400000;

Plus: if you do store the milliseconds and you want to display the real date, you always need to apply an additional conversion:

select some_col,
       other_col,
       to_char(timestamp_column, 'yyyy-mm-dd') as the_date
from your_table
where timestamp_column >= timestamp '2007-08-24 18:34:01';

vs.

select some_col,
       other_col,
       to_char(to_timestamp(milliseconds_column / 1000), 'yyyy-mm-dd) as the_date
from your_table
where  milliseconds_column >= 1187906400000;

The second one one be slower than the first one, but it's more typing and makes the code harder to read.

Edit:

   how to create a stored procedure that will convert this timestamp into the milliseconds I want (and the reverse function too)?

You don't need to write your own function:

convert timestamp to milliseconds: extract(epoch from current_timestamp) * 1000 + extract(milliseconds from current_timestamp)
convert milliseconds to timestamp: to_timestamp(1187980443530 / 1000)

So to wrap it up:

Go with a timestamp column and don't worry about performance.


Custom sorting of a query The application I am working on displays some tasks based on status, and date (in that order). However, for a particular status, the sort condition should be inverted. For example, a table which looks like this :

 id    | status   | planned_date
-------+----------+--------------
 1     | inactive | 2015-03-12
 2     | active   | 2015-03-13
 3     | inactive | 2015-03-13
 4     | inactive | 2015-03-14
 5     | active   | 2015-03-12
 6     | active   | 2015-03-16
 7     | active   | 2015-03-17

Should be returned as

 id    | status   | planned_date
-------+----------+--------------
 5     | active   | 2015-03-12
 2     | active   | 2015-03-13
 6     | active   | 2015-03-16
 7     | active   | 2015-03-17
 4     | inactive | 2015-03-14
 3     | inactive | 2015-03-13
 1     | inactive | 2015-03-12

Note: For this example to be simple, only two statuses are used, but this is an enumeration of 6 different values for this field; new, pending, active, inactive, cancelled, completed. Also, there are other fields that need to be sorted: planned_date, and priority (a numeric value from -3 to 3, 0 being "normal"). I am mentioning this only as a FYI, as I don't believe it to be much relevant, and that the general idea can be understood with the simple example above.

To sort by the status field (an enum) I simply perform

SELECT id, status, planned_date
FROM tbl_tasks
WHERE ...
ORDER BY status ASC;

Now, how do I sort the planned_date field (ASC for active status, and DESC for inactive status)?

You could use CASE WHEN operator for solving this:

ORDER BY status,
    CASE status
        WHEN 'active' THEN planned_date - now()
        WHEN 'inactive' THEN now() - planned_date
    END


Outer Joins to find records with no matching associated entries I'm trying to find all foo records which do not have any bar records that match a given criteria.

create table foo (
  id integer
);

create table bar (
  foo_id integer,
  name varchar(255)
);

insert into foo (id) values (1),(2),(3),(4),(5),(6);
insert into bar (foo_id, name) values
 (1,"name1"),
 (2,"name1"),
 (3,"asd"),
 (4,"name2"),
 (5,"name2"),
 (3,"name1");

Based on this .. only record 6 matches the criteria since its not tagged with name1 or name2.

select
  foo.*
from foo
  left outer join bar on (foo.id = bar.foo_id)
where
  bar.name not in ("name1","name2") or bar.name is null;

What am I missing?

If you want to stick with left join, here is how it should be. The condition you want to exclude is placed on the left join .. on part and you keep the where .. is null check:

select
    foo.*
from
    foo
  left outer join bar
    on  foo.id = bar.foo_id
    and bar.name in ('name1', 'name2')
where
    bar.name is null ;

It can also be done with not exists:

select
    foo.*
from
    foo
where
    not exists
      ( select *
        from bar
        where foo.id = bar.foo_id
          and bar.name in ('name1', 'name2')
      ) ;


Rolling Back Stored Procedures in Entity Framework I currently working a on project that operates within a transaction in entity framework. Before now all of the changes were made using a DataContext. I need to add a call to a stored procedure using that same context. My question is will the stored procedure be rolled back as well in the case of an exception.

Below is how the SP is structured

CREATE PROCEDURE dbo.UpdateObject
@ObjectId VARCHAR(20),@ObjectStatus VARCHAR(20)='Yes'
AS
SET NOCOUNT ON;

DECLARE @object_status_enddate  DATE;
DECLARE @object_optout VARCHAR(10);
DECLARE @current_date  DATE=REPLACE(CONVERT(VARCHAR,GETDATE(),111),'/','-');

BEGIN TRY
    SELECT TOP(1)
     @object_status_enddate=a.EndDate,@object_optout=a.OptOut
    FROM xxx a
    WHERE ObjectId=@ObjectId
    ORDER BY LastModified DESC;
    --TERM EXISTING STATUS
    WITH CurrentStatus AS
    (
    SELECT TOP(1) *,
    ROW_NUMBER() OVER (ORDER BY LastModified DESC) AS RN
    FROM XXX
    WHERE ObjectId= @ObjectId
    )
    UPDATE CurrentStatus SET EndDate = @current_date, LastModified = CURRENT_TIMESTAMP

    --ADD NEW STATUS
    INSERT INTO [dbo].[XXX](ObjectId,ObjectIndicator,StartDate,EndDate,LastModified,ModifiedBy,OptOut)
    SELECT @ObjectId,@ObjectStatus,@current_date,@object_status_enddate,CURRENT_TIMESTAMP,USER,@object_optout
END TRY
BEGIN CATCH
    --SOMETHING WENT WRONG
    DECLARE @ErrorMessage NVARCHAR(MAX),@ErrorSeverity INT,@ErrorState INT;
    SELECT @ErrorMessage=ERROR_MESSAGE()+' Line '+CAST(ERROR_LINE() AS NVARCHAR(5)),@ErrorSeverity=ERROR_SEVERITY(),@ErrorState=ERROR_STATE();
    RAISERROR(@ErrorMessage,@ErrorSeverity,@ErrorState);
END CATCH;
GO

The rollback will rollback all opening (active) transaction. In case using Entity Framework, EF always auto create an root (outermost) transaction, so that the rollback inside a stored proc will rollback the transaction created by EF as well and it will cause error.

There is a way to get over, using Save point -> see https://msdn.microsoft.com/en-us/library/ms188378.aspx

I've applied the template save point for EF and it works well.


Best database design for website ranking I am working on a project in which I need to rank websites in two way - Global rank and their rank in a particular country. There are various factors based on which both are calculated. There are ~200 countries for which rank needs to be calculated.

I am using MySQL

I have two questions -

Global rank

Whenever any new website is added -

Either I need to recalculate the ranks for all the website if I store the website rank in database. This requires a lot of processing each time a new website is added.
or the other option is to assign a score to each website and whenever query is made assign rank by doing order by based on score, and returning its position. This doesn't seem scalable way as order by is expensive operation.

Country Rank database and updation

How should I store per country rank of each website ?
How to update all these ranks when a new website is added ? Here the updation will be even more costly than in case of Global rank.
Should I generate it on-the-fly when queried and then cache the result ?

How do websites like play store show personalized and country based results ?

Will you be ranking the billions of web sites?  If so, then on-the-fly and recompute-all are out of the question.

Instead, have a background task working on the problem.  For display, show the info that is in the table, possibly out of date by a day or two (depending on how long it takes to recalc).

Put the ranking in a separate table, with only website_id and rank.  This will make replacing the entire table after the recalc atomic and fast.  Perhaps a second table with website_id, country_code (CHAR(2) CHARACTER SET ascii), and rank.

You have not explained how the ranking is calculated, so I can't give you any insight in how to do it as a background task.


Create pgAdmin database on local machine I am trying to create databases on my local machine using pgAdmin. When I try to connect to a new server using the "New server registration" entry below it gives me the "Server doesn't listen" error (picture below).

I had tried the same procedure on my previous machine and it worked without any trouble. Any thoughts would be very helpful. I've tried some different combinations and nothing has worked yet. Thanks in advance for the help.

PgAdmin is just a tool to work on existing database. @FabrizioMazzoni is right - you have to install PostgreSQL server first, and the you can use PgAdmin to work on it. New Server Registration is a form to connect to an existing database server, not to create one.


Job showing the running sign but not currently running I have a script to get the currently running jobs:

-- get the running jobs
--marcelo miorelli
-- 10-dec-2013
SELECT sj.name
,DATEDIFF(SECOND,aj.start_execution_date,GetDate()) AS Seconds
FROM msdb..sysjobactivity aj
JOIN msdb..sysjobs sj on sj.job_id = aj.job_id
WHERE aj.stop_execution_date IS NULL -- job hasn't stopped running
AND aj.start_execution_date IS NOT NULL -- job is currently running
--AND sj.name = 'JobName'
and not exists( -- make sure this is the most recent run
    select 1
    from msdb..sysjobactivity new
    where new.job_id = aj.job_id
    and new.start_execution_date > aj.start_execution_date )

when I run that on my server it says that there is no job currently running, however, when I look at the Job Activity Monitor, I see the picture below.

The second step of this job, runs a powershell script using a proxy.
you can see the second step here.

any reason why it would show the second step of my job as running?
how can I fix it?

Use script instead to check status of currently running job.

SELECT  job.name,
        job.job_id,
        job.originating_server,
        activity.run_requested_date,
        DATEDIFF(MINUTE, activity.run_requested_date, GETDATE()) as Elapsed,
        case when activity.last_executed_step_id is null
             then 'Step 1 executing'
             else 'Step ' + convert(varchar(20), last_executed_step_id + 1)
                  + ' executing'
        end
FROM    msdb.dbo.sysjobs_view job
        JOIN msdb.dbo.sysjobactivity activity ON job.job_id = activity.job_id
        JOIN msdb.dbo.syssessions sess ON sess.session_id = activity.session_id
        JOIN ( SELECT   MAX(agent_start_date) AS max_agent_start_date
               FROM     msdb.dbo.syssessions
             ) sess_max ON sess.agent_start_date = sess_max.max_agent_start_date
WHERE   run_requested_date IS NOT NULL
        AND stop_execution_date IS NULL


PostgreSQL Role Inheritance behavior in AWS RDS I was familiarizing myself with how PostgreSQL (9.6) role management works and ran into a case that seemed to go against my understanding of the documentation.

I have an RDS instance and a master user with the rds_superuser role.  I used this role to create a "group" role called 'readwritegroup'. This role has NOLOGIN and CREATEDB granted to it so that anyone who is added to the group may inherit those permissions but cannot actually be used to do things on.

Using my master user I created a database and a new schema inside of it.  I granted CREATE and USAGE on the schema to the 'readwritegroup" role.  I then created and added 2 separate users to the 'readwritegroup' with only INHERIT as their grants.

I then used the master user to run the following on the schema:

ALTER DEFAULT PRIVILEGES IN SCHEMA test
GRANT ALL PRIVILEGES ON TABLES TO "readwritegroup";

Using these two users I successfully created 1 table for each user and then attempted to SELECT from each user's table using the other user. So they are the owner of their respective tables. They are unable to see each other's tables however. (permission denied)

In fact, even my master user is unable to GRANT any permissions on the tables that they own (though I can change the ownership to my master user).

Is there any way to circumvent the authority of object ownership?  I don't really understand why the ALTER DEFAULT PRIVILEGES bit does not correctly modify the behavior to allow other users in the group to SELECT (or whatever grants are used).

Found the answer in this post: How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?

The default privileges are set for the executing user unless otherwise specified with the FOR ROLE modifier.


SQL Server Latching from Multithreading/Parallel Inserts and Hash Partitioning We are conducting parallel, multithreading inserts in SQL server tables, and want to reduce latching.

What are the disadvantages of utilizing hash partitioning to reduce latching?  Is it reduced query slowness from essentially querying all these split up partition tables?

We have around 120 Table Inserts Per Second, financial system.

Other Notes: SQL 2016 System will use around 50 GB of SSD Hard drive space a year.
Currently, have 50 Core processors, and 150 GB of RAM.

Platform is not built, so no baseline to test; but I need to develop testing plan and strategies.

Hash Partitioning Example:
http://www.madeiradata.com/how-to-solve-the-tail-insert-problem-2/

CREATE PARTITION FUNCTION pf_hash (TINYINT)
AS RANGE LEFT FOR VALUES (0,1,2,3,4,5,6,7,8);

CREATE PARTITION SCHEME ps_hash
AS PARTITION pf_hash ALL TO ([PRIMARY]);

CREATE TABLE dbo.UserEntries_RegularWithHash
(
Id BIGINT IDENTITY NOT NULL,
UserId INT NOT NULL ,
CreatedDate DATETIME2 NOT NULL,
HashId AS CAST(Id % 9 AS TINYINT) PERSISTED NOT NULL,
CONSTRAINT PK_UserEntries_RegularWithHash
PRIMARY KEY CLUSTERED (Id,HashId)
)
ON ps_hash(HashId);

Queries that don't specify the partitioning column will need to touch all partitions. This is particularly an issue with a table partitioned using a computed hash column because the hash value isn't commonly specified in queries. Although you can specify the HashId value, it's not natural to do so. Examples:

--touches all 10 partitions
SELECT *
FROM dbo.UserEntries_RegularWithHash
WHERE Id = @Id;

--touches 1 partition
SELECT *
FROM dbo.UserEntries_RegularWithHash
WHERE Id = @Id
AND HashId = CAST(@Id % 9 AS TINYINT);

Latch contention against a non-partitioned table with an incremental key generally occurs only at very high insert rates.

Latch contention will not happen on a healthy machine at 120 inserts per second. David Browne-Microsoft suggested 10,000 inserts per second is the point at which latch contention is a concern. That's probably a good a SWAG as any, as it will depend on factors such as row size and hardware. I think one can assume a rate of several thousand per second will be needed before latch contention is a consideration. At that point you might consider In-Memory OLTP tables, and/or bulk/batch inserts.

I've heard it said that premature optimization is the root of all evil. In this case, I would certainly not introduce partitioning solely to avoid latch contention.


Failed To Insert Data Into Tables Using Postgres-XL and 4 Datanode Recently, I have installed Postgres-XL-9.5r1.5 in a R630, 16 cores, 16 GB RAM.
I configured 1 GTM, 1 Coordinator, and 4 Datanodes.
I created a simple table sales_small_cat

CREATE TABLE sales_small_cat
(
   date_sales            timestamp    NOT NULL,
   store_sid             integer      NOT NULL,
   branch_sid            integer,
   small_cat_sid         integer      NOT NULL,
   total_receipt_amount  integer,
   qty_sales_nonpromo    numeric,
   qty_sales_promo_rep   numeric,
   qty_sales_promo_avp   numeric,
   total_sales           numeric,
   pi                    numeric,
   is_festive            boolean,
   is_store_closed       boolean
)
DISTRIBUTED BY (store_sid)
TO NODE (dnode1,dnode2,dnode3,dnode4);
ALTER TABLE sales_small_cat
   ADD CONSTRAINT pk_sales_small_cat
   PRIMARY KEY (store_sid, small_cat_sid, date_sales);

I want to insert dummy data using

WITH
trxDates AS ( SELECT generate_series('2017-01-01'::DATE,'2017-01-01'::DATE,'1 day'::INTERVAL) AS trx_date),
idmStores AS ( SELECT generate_series(1,100,1) AS store_sid),
scat AS ( SELECT generate_series(1,40,1) AS scat_sid),
temp AS (
  SELECT trx_date,store_sid,round(random()*10+1) AS branch_sid,scat_sid
  FROM trxDates CROSS JOIN idmStores CROSS JOIN scat
),
temp2 AS (
  SELECT
    *, round(random()*1000) AS receipt_amount,
  random()*1000 AS non_promo, random()*1000 AS promo_rep, random()*1000 AS promo_avp
  FROM temp
),
finale AS (
  SELECT *,(non_promo+promo_rep+promo_avp) AS total_sales, random()*1000 AS purchase_index, false AS is_festive, false AS is_store_closed
  FROM temp2
  ORDER BY 1,2,4
)
INSERT INTO sales_small_cat
SELECT * FROM finale;

And I get

An error occurred when executing the SQL command:
WITH
trxDates AS ( SELECT generate_series('2017-01-01'::DATE,'2017-01-01'::DATE,'1 day'::INTERVAL) AS trx_date),
idmStores AS ( SELECT generate_series...

ERROR: unrecognized node type: 119

Execution time: 0.02s
1 statement failed.

I traced the log of datanode below:

[postgres@serveragci ~]$ tail -f pgxc/nodes/dn_master/dnode1/pg_log/datanode-20170508.log
ERROR:  unrecognized node type: 119
STATEMENT:  Remote Subplan
ERROR:  unrecognized node type: 119
STATEMENT:  Remote Subplan
LOG:  disconnection: session time: 0:15:19.504 user=postgres database=postgres host=192.168.101.111 port=37161
LOG:  disconnection: session time: 0:16:52.181 user=admindb database=test host=192.168.101.111 port=37209
LOG:  connection received: host=192.168.101.111 port=37432
LOG:  connection authorized: user=admindb database=test
ERROR:  unrecognized node type: 119
STATEMENT:  Remote Subplan

Is it a bug or my mistake?
Please, enlight me.
Thank you very much.

Finally, I found it as my mistake because I am still affected by ordinary PostgreSQL (9.4) behaviour. After I added ::INTEGER and LIMIT 1000000 in the DML below, the data insertion is a success.

...
temp2 AS (
  SELECT
    *, round(random()*1000)::INTEGER AS receipt_amount,
  random()*1000 AS non_promo, random()*1000 AS promo_rep, random()*1000 AS promo_avp
  FROM temp
),
...

  ORDER BY 1,2,4
)
INSERT INTO sales_small_cat
SELECT * FROM finale LIMIT 1000000;

Frankly, I am still confused. Why does Postgres-XL need the LIMIT clause?


SQL 2008 R2 creates user/schema when Windows user creates tables We added a server login and database user that map a Windows Group to a SQL 2008 R2 instance using the following script, with the names changed for anonymity:

USE master
go
CREATE LOGIN [DOMAIN\AppUsers] FROM WINDOWS
WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
go
USE AppDb
go
CREATE USER [DOMAIN\AppUsers] FOR LOGIN
[DOMAIN\AppUsers]
go
EXEC sp_addrolemember N'db_owner', N'DOMAIN\AppUsers'
go

When the DOMAIN\User1 account logs onto the app, User1 queries tables in the dbo schema just fine because User1 is a member of DOMAIN\AppUsers, but this app allows the user to create tables too. When creating these tables without specifying a schema, SQL Server does the following:

Creates a 'DOMAIN\User1' user in AppDb that uses a 'DOMAIN\User1' login not listed in SSMS\Security\Logins for the instance.
Creates a 'DOMAIN\User1' schema in AppDb.
Creates those tables using in the new 'DOMAIN\User1' schema.

I am completely baffled by these results. Here are my questions:

I would expect the table creation to fail rather than create additional objects. Can someone point me to the part of Books Online that explains this?
Why doesn't the server create a 'DOMAIN\AppUsers' schema and add the new tables to that schema if it is going to add schemas?
Also, how does the database use a login not shown in SSMS\Security\Logins?
Looking at the 'DOMAIN\User1' user in SSMS\Databases\AppDb\Security\Users, the user icon has a small red arrow pointing down. What does that mean?

We are just starting to use Windows Authentication within an organization that preferred SQL Authentication for simplicity, so I am sure my question comes from being ignorant of the differences. This code was written long before we considered using Windows Authentication, so I sure we need to improve our understanding of creating new schemas when logged on using Windows Authentication as anyone other than the database owner.

In case you can't tell, I am the one pushing for the use of Windows Authentication over SQL Authentication. If we don't get to a solid understanding of this, we will revert back to SQL Authentication.

This has always happened, back to SQL Server 2000.
Without schema, how does SQL Server know you want to put it in the dbo schema?

The only way to specify a default schema is to :

use SQL logins (not Windows)
run as "sysadmin"

Neither of these is acceptable

Best practice is to always qualify schema for every object reference for DDL and DML. There are clear performance benefits because of plan re-use.

Also, deliberate schema use is better for SQL Server 2005:

tables in Data
other tables in Archive, Staging etc
code in schemas per client permissions: Desktop, WebGUI etc

Using the dbo schema is so last millenium :-) Links:

Schema design - best practices?
https://dba.stackexchange.com/q/7257/630


EXECUTE stmt USING expression I am writing an INSTEAD OF trigger on a view. I need to split a string into 4 40 character long parts to insert into separate columns. I want to use substr() to break the variable up into these parts, but when I try to create the trigger I get the error:

  SQL State: 42703
  Vendor Code: -206
  Message: [SQL0206] Column or global variable SUBSTRING not found.
  Cause . . . . . :   SUBSTRING was not found as a column of table *N in *N and was not found as a global variable in *N. If the table is *N, SUBSTRING is not a column of any table or view that can be referenced.
  Recovery  . . . :   Do one of the following and try the request again:
  -- Ensure that the column and table names are specified correctly in the statement.
  -- If this is a SELECT statement, ensure that all the required tables were named in the FROM clause.
  -- If the column was intended to be a correlated reference, qualify the column with the correct table designator.
  -- If the column was intended to be a global variable, qualify the name with the schema where the global variable exists or ensure the schema is in the path.

This page indicates that I can use an expression in my USING clause as long as it is within a compound SQL statement. As I understand it, the SQL between BEGIN and END in a trigger would be considered a compound SQL statement.

  "An expression other than host-variable can only be used when the EXECUTE statement is used within a compound SQL (compiled) statement."

CREATE OR REPLACE TRIGGER MYLIB.INFO_INSERT
INSTEAD OF INSERT ON MYLIB.INFO
REFERENCING NEW AS N
FOR EACH ROW
MODE DB2SQL
BEGIN
    DECLARE...
    ...
    set insert_into_info = 'INSERT INTO MYLIB.' || info || ' (
      CICUST, CIINF1, CIINF2, CIINF3, CIINF4
    ) VALUES (' || NEW_ID || ', ?, ?, ?, ? )';

    PREPARE s1 from insert_into_info;
    EXECUTE s1 using substring(paddedComments, 1, 40), substring(paddedComments, 41, 80),
        substring(paddedComments, 81, 120), substring(paddedComments, 121, 160);
END;

I have found examples for Oracle of using functions in the USING clause, but no iSeries or DB2 examples, although there are enough hints in the descriptions of the EXECUTE statement to keep me hoping. I am running z/OS 7.2 if that is relevant.

I've been searching around but haven't managed to figure this out; can anyone get me going in the right direction? I did also try to do this with an array so I wouldn't have to break up the string at the end, but I could not manage to get an array type declared and used within this trigger either.

EXECUTE ... USING requires host variable names, not expressions, while SUBSTRING(whatever) is an expression. You will need to declare four host variables, assign results of the SUBSTRING(...) expressions to them, then use those variables in the EXECUTE statement.

Alternatively, you could move expressions to the dynamic statement itself:

set insert_into_info = 'INSERT INTO MYLIB.' || info || ' (
      CICUST, CIINF1, CIINF2, CIINF3, CIINF4, CISAD1, CISAD2,
      CISCTY, CISSTA, CISZIP, CISCRY
    ) VALUES (' || NEW_ID || ', substring(?, 1, 40), ' ||
    'substring(?, 41, 80)' || ...
...
EXECUTE s1 using paddedComments, paddedComments, ...