All Questions

19,360 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
18 votes
0 answers
5k views

PostgreSQL trigram GIST vs. GIN indexes

I have a PostgreSQL 9.1 database that contains 10M+ rows and some text fields that need similarity and %word%-like searches, so I decided to use trigram indexes. Initially, I started to use GIN ...
P.Péter's user avatar
  • 891
13 votes
1 answer
17k views

Conceptual data model for a billing system

I am working on an existing MySQL database. My task is to create a new data model for a billing system on that existing application. I have created a conceptual data model (click to enlarge): ##...
Rathish Kumar B's user avatar
13 votes
1 answer
2k views

MySQL deadlock: upsert query acquiring gap lock twice?

I am trying to debug a deadlock scenario which has been puzzling me for quite some time now. I am wondering if someone can shed any light into how this can happen. Here is the deadlock output: ------...
Agniva De Sarker's user avatar
11 votes
1 answer
9k views

DBeaver launch script from file

I'm trying to find a good alternative to SQL Developer for performing queries and scripts on Oracle instances. I find DBeaver to be an interesting tool, it has a lot of great features, BUT it seems ...
Samuryte's user avatar
  • 111
10 votes
1 answer
1k views

Convert query in words to relational algebra

I have a follow-up question to a question I have previously asked on SO. Instead of the query from my initial question, I want to convert the following into relational algebra: List the names and ...
bawse's user avatar
  • 101
10 votes
1 answer
831 views

SSMS 2014 (and 2012) CTRL+TAB requires pressing enter

Sometime last week my CTRL+TAB behavior changed. I used to hit CTRL+TAB, release, then I would be in my previous document tab. Now I hit CTRL+TAB, release, and the nav window is still open requiring ...
Jay Otterbein's user avatar
9 votes
0 answers
2k views

AWS RDS postrgres massive disk use, small tables

I can't figure out why our AWS postgres server has consumed all of it's space. We just had to up the storage space allocated to it, but can't find any hint from postgres that it's using that much ...
ChrisJ's user avatar
  • 591
9 votes
0 answers
4k views

Working on a decision tree database design

I'm doing the conceptual model for a Decision tree database. The goal is store any Decision tree. I have four entity types: DecisionTree, Node, Branch and Leaf. Each Decision tree is composed of ...
Amater90's user avatar
9 votes
0 answers
1k views

Extended Events - sqlserver.process_login_finish Not Firing?

We're trying to see whether a client is able to reach a database server, as it's receiving error: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. The error ...
Riley Major's user avatar
  • 1,873
9 votes
0 answers
941 views

How to minimize access exclusive table locks while reducing bloat or ALTER (ing) a table?

In some circumstances, I've been told not to do a VACUUM FULL (or CLUSTER) of a table that is in production, because that will exclusively lock it for longer than wished. The same might apply to ...
joanolo's user avatar
  • 13.4k
9 votes
0 answers
705 views

Inconsistent equality checking of Unicode text in Postgres 9.5?

Using PostgreSQL 9.5. It seems like Postgres is being inconsistent with how it compares strings similar to لى. A unique constraint is considering some strings equal that GROUP BY is considering ...
sudo's user avatar
  • 991
9 votes
1 answer
1k views

Oracle Ref Partitioning: Deadlock due to child table row migration

My ordering application uses Oracle 11g Database. This DB has a primary table ORDERS and multiple child tables like ORDER_DETAILS, PLAN etc. ORDERS table is LIST partitioned on STATUS column and all ...
Sauchin's user avatar
  • 265
9 votes
1 answer
2k views

Change Tracking across several Tables

I'm looking into providing DB level change tracking within my system. I need to be able to track changes at an entity level, rather than just individual tables. Right now, we have triggers on each of ...
Obsidian Phoenix's user avatar
9 votes
2 answers
3k views

verify that mongos server is connected to config servers

I've been writing a backup script for sharded replica-sets and it's almost done. Except I can't seem to get it to successfully start the balancer backup after everything's all said and done. Here's ...
Alexej Magura's user avatar
9 votes
2 answers
2k views

Postgresql with just index storage

I have a PostgreSQL table with a key (bigint) and a value (double). The table has tens of billions of rows. I have a single btree on the (key,value) for aid lookups by key. The table is never updated. ...
user3000172's user avatar
9 votes
1 answer
1k views

Select * from statement execute very slowly, innodb io read speed is low

I have a very simple query " select * from ap_statistic " running in my servers. the servers have the same hardware and software configuration (CPU 8 core, mem :32G, OS: redhat 5.5, mysql version: 5....
Matt Suo's user avatar
9 votes
1 answer
1k views

Database Stuck 'In Recovery' with command XTP_DB_RECOVERY

We recently converted a table to memory optimized data. Our backups have all ballooned (3x300GB files to 3x600GB files for Full, 3x50GB to 3x250GB for Diffs), and startup has been getting ...
InMemoryHardships's user avatar
8 votes
1 answer
158 views

Why does only one version of these procedures get blocked under moderate load?

The table: CREATE TABLE [dbo].[Session] ( [SessionId] UNIQUEIDENTIFIER NOT NULL, [CID] INT NULL, [DEST] VARCHAR (50) NOT NULL, [...
sean's user avatar
  • 316
8 votes
0 answers
3k views

pg_restore --clean not working because cascade drop

I'm working with a copy (DB-B) of a database (DB-A) I keep up to date by running daily: pg_restore -U postgres -d someDB --clean -j 2 -L WhatToImportList admin.dump But I started noticing duplicate ...
Gabriel's user avatar
  • 347
8 votes
0 answers
1k views

SQL Server 2012+ & Service Broker: SSBDT Spam ending conversations after sending message to NULL conversation_handle

I'm mostly posting this for community awareness, but feel free to give feedback if you think there is a solution other than coding around the problem as we are doing. Crossposting this from my bug ...
G.Smith's user avatar
  • 123
8 votes
0 answers
3k views

How do I rename a table with minimal downtime in a production postgres database?

I understand that renaming a table in postgres boils down to a simple catalog update. However, it also requires an ACCESS EXCLUSIVE lock to ensure transactions are safe. What would be the ideal way ...
Ragesh's user avatar
  • 181
8 votes
0 answers
2k views

How to measure the memory usage of a Mongodb aggregation?

I recently started running into the Sort exceeded memory limit of... exception on an aggregation pipeline in MongoDB. I got around this by setting AllowDiskUse=True for the aggregation. However, I ...
Dónal Flanagan's user avatar
8 votes
1 answer
4k views

SQLPackage : Import or Export SQL Azure BACPAC without users

I know how to use SQLpackage.exe. I can export from SQL Azure and create a bacpac. I can also Import to SQL Server via SQLPackage.exe. I want to export or import without the users in the database. The ...
Peter PitLock's user avatar
8 votes
0 answers
15k views

Generate UUID v4 automatically in MySQL

Is it possible to generate a UUID version 4 (completely random) in MySQL? I would like to use it as a primary key. Use Case Many systems will insert to the table, some may specify a UUID when ...
Justin Howard's user avatar
8 votes
1 answer
2k views

MySQL: Is the read committed isolation level safe with mixed binary log format?

When using the MIXED format with the READ COMMITTED isolation level, am I safe? The docs state (http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html#isolevel_read-committed) If you use READ ...
Bell's user avatar
  • 181
8 votes
1 answer
4k views

MySQL query to create a Matrix view using pivot

I have a table with the following fields: p1 p2 s1 s2 where p1 -> player1, p2 -> player2, s1 -> game1, s2 -> game2 The number of players can vary from 3 - 10. The table have the results between them....
dimoss's user avatar
  • 181
8 votes
1 answer
134 views

Does MySQL/InnoDB use table caches when dealing with foreign tables?

TL;DR: Does a table referred to via schema.table get held in table cache? I have a large number of client databases that our application connects to based on an http request. These are used for every ...
RickMeasham's user avatar
8 votes
1 answer
2k views

Receiving error when running DBCC CheckDB - multiple IAM pages

Running DBCC CHECKDB (dbname) WITH NO_INFOMSGS, ALL_ERRORMSGS results in this error: Table error: Multiple IAM pages for object ID 813245952, index ID 1, partition ID 72057594073972736, ...
Eric Johnson's user avatar
8 votes
1 answer
1k views

RDS MYSQL instance rebooted and won't load INNODB table

So my production db in RDS apparently either crashed or was rebooted during a version update, and upon being restarted mysql couldn't load one of the innodb tables: as evidenced by the following error ...
Camden Narzt's user avatar
8 votes
1 answer
2k views

Service Broker: Queue Monitor is dropped after poison message

I have searched everywhere and there isn't an answer online. There is one similar question on stackoverflow but it's not exactly the same and it has no accepted answer. When I setup event ...
Sergey's user avatar
  • 131
8 votes
2 answers
121 views

Changes access method for non-correlated subquery

Oracle 11g R2 Unfortunately our application has per row security "features". We have a query that looks about like this: Bad, slow: SELECT someRow, someOtherRow FROM bigTableA a WHERE EXISTS ( ...
rfusca's user avatar
  • 1,569
7 votes
1 answer
9k views

How to estimate reindexing time?

How do you estimate the total time reindexing an entire table will take in PostgreSQL? I have a large PostgreSQL database, the primary table of which has 15 million rows with 100 columns and 200 ...
Cerin's user avatar
  • 1,345
7 votes
0 answers
126 views

SQL Server: how Row Goal affects the same index operation?

I am tuning some SQL performance in the SQL Server 2019, and I ran into issues with one query that involves the issue with the row goals. The simplified query is: select TOP 1 ISNULL(RUNID,-1) from ...
Jan Drozen's user avatar
7 votes
0 answers
197 views

Find a previous statement or held locks in a blocking situation

I'm using XE blocked_process_report to detect and analyse the blocking. But since it's a point-in-time situation, I only see the blocking leader's statement currently being run and the incompatible ...
Zikato's user avatar
  • 4,468
7 votes
1 answer
316 views

Do any of the graph based/aware databases have good mechanisms for maintaining referential integrity?

Do any of the graph-based/graph-aware databases (Neo4j, ArangoDB, OrientDB, or other) have mechanisms for maintaining referential integrity on a par with those offered by relational databases? I'm ...
pvgoran's user avatar
  • 171
7 votes
1 answer
13k views

Restore mongoDB by --repair and WiredTiger

We accidentally deleted the directory rm -rf /data/db which was our MongoDB path, and thanks to extundelete, we recovered it and got the directory /data/db. Here are our files in the directory, and ...
国贵棠's user avatar
7 votes
0 answers
84 views

Authoritative Extend Event object list?

I am looking for an authoritative list of objects for extended events, I want to know what version an object becomes available and some clues about what the value really represents. I have googled ...
James Jenkins's user avatar
7 votes
0 answers
6k views

Collation in variables

After having many issues with ISO-8859-1 and reading a lot about collations I finally understand that I need to use charset UTF8 collate utf8_unicode_ci. So I configure my whole application with these ...
Alwin Kesler's user avatar
7 votes
2 answers
1k views

Foreign key to multiple tables via forwarding table

Problem statement I have a table with a column whose values are foreign keys, but the target table of the foreign key differs from row to row. The relevant table can be determined from the key value ...
wchargin's user avatar
  • 171
6 votes
0 answers
241 views

Right way to set up a secondary citus worker

I am experimenting with the postgres-addon citus. So far I have setup a cluster with one coordinator and three worker nodes. Now I want to setup a secondary for one of the worker nodes. So far I have ...
Nils's user avatar
  • 149
6 votes
0 answers
3k views

SQL Server Network Packet Size

During the analysis of network traffic from Citrix clients to our database server, we noticed that we had a lot of RETRANSMITs occurring during the transport of TCP packets. The packets were being ...
John K. N.'s user avatar
  • 17.6k
6 votes
0 answers
12k views

How to change array_agg() to return empty array on empty input in PostgreSQL?

I am trying to write a PostgreSQL array_agg() variant that returns the empty array '{}' instead of NULL. Trying to avoid the performance hit of custom aggregate functions with CREATE AGGRGATE, I tried ...
tinlyx's user avatar
  • 3,500
6 votes
0 answers
3k views

Why is it that "aggregate functions are not allowed in FROM clause of their own query level"?

The former gives me the error message "aggregate functions are not allowed in FROM clause of their own query level" whereas the latter is permitted. Unless I am much mistaken the latter is equivalent ...
Tom Ellis's user avatar
  • 1,569
6 votes
0 answers
680 views

What are PostgreSQL Security Labels?

What are PostgreSQL Security Labels and how/where to use them compared to GRANT?
mFeinstein's user avatar
6 votes
0 answers
622 views

Is setting a foreign key as primary key in a One-to-One relationship a good idea?

I have a table user and a table user_statistic with a one to one (1:1) relationship. Note that we have an identifying relationship between user and user_statistic. Which is best practice for ...
jeremieca's user avatar
  • 177
6 votes
0 answers
1k views

What can cause slow planning times for "select 1"?

I have a database that is used by a Production application. It's an db.m3.medium on Amazon RDS. We've noticed that a lot of queries are starting to get slow that typically shouldn't be slow (e.g. ...
TheCloudlessSky's user avatar
6 votes
0 answers
254 views

Using the Schema Generation Wizard to regenerate a previously-generated schema

Lately, I've been trying to clean up a group of projects that my company received from an outside contractor. Among them is an SSAS cube, with an associated data warehouse. The contractors generated ...
Julian Goldsmith's user avatar
6 votes
0 answers
7k views

Getting "Cannot open datafile for read-only" for an intentionally DROPped InnoDB table

It's been too long to remember how we got here accurately, but we get the following error on MySQL start (though it does still start the service with InnoDB): [ERROR] InnoDB: Cannot open datafile for ...
Christopher McGowan's user avatar
6 votes
0 answers
1k views

MongoDB background index builds block secondary

We recently experienced a disruptive incident in a MongoDB 3.2 cluster related to index creation. Scenario: Several indexes were created on the primary using background: true. Indexes were ...
Arjen Meek's user avatar
6 votes
1 answer
9k views

How to add `--sslAllowInvalidCertificates` on mongodb uri?

I have a mongodb server requires x.509 certificate. So I have to use --sslPEMKeyFile and --sslCAFile on mongo shell command to connect to that server. It works fine. In my dev environment, I am using -...
Joey Yi Zhao's user avatar

15 30 50 per page
1
2 3 4 5
388