PostgreSQL extended statistics I have a 1 TB read-only database where performance is critical. It's difficult to predict queries since they are dynamically generated by the users (the whole thing is basically a visualization platform atop a large collection of medical studies, and users select what they want to visualize). Queries can often be complex and involve 10+ joins. I recently learned about the extended statistics feature, but I find little information online about when best to use it (other than what's in the documentation).
The DB is pretty well normalized, but makes extensive use of materialized views which are de-normalized. Is there any performance penalty or other issue with creating extended statistics (dependency and top n) for all pairwise columns? It would result in, say, 500 statistics on some 70 tables. Time for analyze or inserts is not of relevance, only read performance. Also, is there a tool or code snippet to help me do this?
I'm using Postgresql 12 and it's optimized as far as possible w.r.t. indexing.

I think your best bet would be to set in place a mean to monitor the stats of relevant tables and indexes counters:
https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ALL-TABLES-VIEW
Specially the:
   seq_scan bigint

   Number of sequential scans initiated on this table

   seq_tup_read bigint

   Number of live rows fetched by sequential scans

   iseq_scan bigint

   Number of sequential scans initiated on this table

   seq_tup_read bigint

   Number of live rows fetched by sequential scans

   idx_scan bigint

   Number of index scans initiated on this table

   idx_tup_fetch bigint

   Number of live rows fetched by index scansdx_scan bigint

   Number of index scans initiated on this table

   idx_tup_fetch bigint

   Number of live rows fetched by index scans

https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ALL-INDEXES-VIEW
idx_scan bigint

Number of index scans initiated on this index

idx_tup_read bigint

Number of index entries returned by scans on this index

idx_tup_fetch bigint

Number of live table rows fetched by simple index scans using this index


How to GRANT permission to execute stored procedure? I have an AD group that is setup as a Windows Authenticated SQL Login on my SQL Server. On DatabaseA it has the db_datareader and public roles. So the users of this AD group only have read access to the entities in the database.

Inside of DatabaseA I have a stored procedure dbo.Proc1 on which I granted the execute permission for the same AD group.

When a user of that AD group connects to this server, they don't see the entity dbo.Proc1.

Is there any other permission I need to provide to the Windows Authenticated SQL Login (AD group) that I'm missing?

I do see, if I look at the properties of DatabaseA, that there's an execute permission at that level which has NOT been granted yet.

(Note: I only want the users of this AD group to have the ability to execute dbo.Proc1. I don't want them to be able to do anything that isn't considered a "read-only" type of action.)

in Excel when they go to Data -> Get Data -> From Database -> From SQL Server Database

That's PowerQuery. Just ran through that on Excel, and stored procedures don't appear, but Table-Valued Functions do. And you can call a stored procedure, but it won't show up in the query designer. You just enter 'exec dbo.Proc1' in the SQL Statement dialog:


Restoring a database Say I have a database Live on some instance. I have created a backup of it and now I need to restore it into a new database named Dev.
In SSMS I am choosing a device Live.bak, then in destination I am changing the name to Dev and in Files tab I see that database and log files are also renamed Dev.mdf and Dev_log.ldf. When I proceed the Live database changes its status to Restoring.

Now I tried to uncheck tail log option and it restored normally. Is it safe way to restore the database? I mean I don't want any changes to Live. Why it tries to restore Live database. I am confused.

Instead of using the SSMS GUI interface to restore databases, you should create a T-SQL script for the restore statement.

This serves two purposes:

You can confirm the target database name, the location of the restored database files, etc prior to the start of the restore.
You can re-run the restore statement as needed in future without re-using the GUI.  This ensures you do the restore the same way each time.

You can create a script from the "Restore Database" dialog-box as show in this screenshot:

This will generate a restore statement like the one below, which I've wrapped for readability:

USE [master]
RESTORE DATABASE [test_master_restore]
FROM  DISK = N'D:\SQLServer\Backups\master_db.bak' WITH  FILE = 1
    ,  MOVE N'master' TO N'D:\SQLServer\MV\MSSQL10_50.MV\MSSQL\DATA\test_master_restore.mdf'
    ,  NOUNLOAD
    ,  STATS = 5


Disable Binary Logs in MySQL 8.0 How can I disable binary logging on MySQL 8.0?

I am needing to import a large MySQLDump file, and don't want to fill the Binary logs with many GB of data. In the past I would have set log_bin=0 in the my.cnf and restarted the database.

But with MySQL 8.0 I have tried various settings, but everytime I log onto the database, and run:

SELECT @@global.log_bin;

and it keeps returning 1

I have tried:

log_bin = 0
log_bin = OFF
#log_bin = 1

Is it possible to disable to the binary logs?

In addition to specifying the corresponding command line argument, you can also add an option to my.cnf:

Edit my.cnf and add skip-log-bin in the [mysqld] section.
Restart mysqld
Enjoy your lack of binlogs

Note that this will remain in effect until you remove the option from the configuration file.


How to restore SQL Server database just by dbName and physical path I have several database backups and I want to restore all of them to a server.

Can someone point me in the right direction with the SQL script to modify the logical file names to the correct path and not the ones carried over with the database??

Restore FILELISTONLY FROM DISK='E:\New folder\Thursday - DB_Order.bak'

RESTORE DATABASE Business_Data_TSQL
FROM DISK='E:\New folder\Thursday - DB_Order.bak'
WITH
MOVE 'DBOrder_Data' TO 'E:\New folder\Business_Data.mdf',
MOVE 'DBOrder_Log' TO 'E:\New folder\Business_Data_log.ldf'

Using query 1 I get the logical name then those names need to manually be set for query 2, but it’s really difficult when there is more than a single database.

Please help me to write a script where I just pass the physical path of backup file and new database name, then a restore of that database occurs.

If have any questions, please ask. Thanks in advance.

Here is something that you can use. It is not fully automated but this is definitely very useful piece of script.

SET NOCOUNT ON;

DECLARE
    @DBName NVarchar(100),
    @BackupFile NVarchar(1000),
    @DeviceFrom NVarchar(1000),
    @DeviceTo NVarchar(1000),
    @LogicalName NVarchar(1000),
    @PhysicalName NVarchar(1000),
    @SQL NVarchar(MAX),
    @RowsToProcess integer,
    @CurrentRow integer,
    @Comma NVarchar(25);

SET @DBName = 'DB_Order'; --  Change this for each database
SET @BackupFile = 'E:\New folder\Thursday - DB_Order.bak'; --  Change this for each database

SELECT @DeviceFrom = SUBSTRING(physical_name, 1,
CHARINDEX(@DBName + '.mdf',
physical_name) - 1)
FROM master.sys.master_files
WHERE name = @DBName AND FILE_ID = 1;

SET @DeviceTo = 'E:\New folder\'; -- Change this if you are changing your restore location
SET @SQL = 'RESTORE DATABASE ' + @DBName + ' FROM DISK = ''' + @BackupFile + ''' WITH ';
SET @CurrentRow = 0;
SET @Comma = ',';

DECLARE @FileList TABLE (
    RowID int not null primary key identity(1,1)
    ,LogicalName NVARCHAR(128)
    ,PhysicalName NVARCHAR(260)
    ,Type CHAR(1)
    ,FileGroupName NVARCHAR(128)
    ,Size numeric(20,0)
    ,MaxSize numeric(20,0)
    ,FileId BIGINT
    ,CreateLSN numeric(25,0)
    ,DropLSN numeric(25,0)
    ,UniqueId uniqueidentifier
    ,ReadOnlyLSN numeric(25,0)
    ,ReadWriteLSN numeric(25,0)
    ,BackupSizeInBytes BIGINT
    ,SourceBlockSize BIGINT
    ,FilegroupId BIGINT
    ,LogGroupGUID uniqueidentifier
    ,DifferentialBaseLSN numeric(25)
    ,DifferentialBaseGUID uniqueidentifier
    ,IsReadOnly BIGINT
    ,IsPresent BIGINT
    ,TDEThumbprint VARBINARY(32) -- Remove this line for SQL Server 2005
    );

INSERT INTO @FileList
EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @BackupFile + '''')
SET @RowsToProcess = @@RowCount;

WHILE @CurrentRow < @RowsToProcess
BEGIN
    SET @CurrentRow= @CurrentRow + 1;
    BEGIN
    IF @CurrentRow = @RowsToProcess
        SET @Comma = ',STATS=1';
    END
    SELECT @LogicalName = LogicalName,@PhysicalName = PhysicalName FROM @FileList WHERE RowID=@CurrentRow;
    SET @PhysicalName = Replace(@PhysicalName,@DeviceFrom,@DeviceTo);
    SET @SQL = @SQL + 'MOVE ''' + @LogicalName + ''' TO ''' + @PhysicalName + '''' + @Comma + '';
END

SELECT @SQL;
--EXEC(@SQL); -- Execute when you are ready.

You can use SQLCMD / Batch file to pass in the parameters of your DB name and physical file name and try to automate it.


ORA-39002, ORA-39168 on IMPDP, Object path GRANT was not found I'm trying to impdp a schema with just one table on it. The impdp parfile looks like this:

LOGFILE=scott_impdp.log
DUMPFILE=scott.dmp
SCHEMAS=scott
REMAP_SCHEMA=scott:scott2
REMAP_TABLESPACE=scott:scott2
EXCLUDE=grant
PARALLEL=8
CLUSTER=N
TRANSFORM=disable_archive_logging:Y

However, I'm getting these errors:

ORA-39002: invalid operation
ORA-39168: Object path GRANT was not found.

If I'm not mistaken, EXCLUDE=grant is valid syntax according to https://docs.oracle.com/database/121/SUTIL/GUID-DC7668E1-C846-48C5-A0D5-F4659EC119BB.htm#SUTIL911

From that link:
Specifying EXCLUDE=GRANT excludes object grants on all object types and system privilege grants.

Removing EXCLUDE=grant seems to work, but why would that break the impdp in the first place?

EDIT:
My expdp parfile looks like this:

LOGFILE=scott_expdp.log
DUMPFILE=scott.dmp
SCHEMAS=scott
PARALLEL=8
CLUSTER=N
COMPRESSION=ALL
FLASHBACK_TIME=systimestamp
REUSE_DUMPFILES=Y
EXCLUDE=statistics

This happened because you specified EXCLUDE=GRANT on export as well.

This is expected behavior - according to Oracle.

The workaround is to not specify the same EXCLUDE in expdp and impdp.

See the below (with support access):

DataPump Import (IMPDP) Fails With ORA-39168 (Doc ID 430702.1)

I have just tested this on version 18c and 19c as well, in those newer versions it worked fine without any error.


Select count question with where clause I have a initial table where I have the following rows: ID_0, geom and utilisatio (where the value varies from 1 to 7).

I am trying to create a table where I would have the count of a specific utilisatio value (associated to an ID_0) for every ID_0 that is within a range of 400m. So far I have come up with:

Select a.id_0, a.geom, count (b.utilisatio) as util_1
into new_table
from first_table as a
    left join first_table as b ON ST_Dwithin (a.geom, b.geom, 400)
where a.id_0 <> b.id_0
group by a.id_0, a.geom
;

Now my problem is that I don't actually know where to add the following constraint to get only the specified utilisation code:

where utilisatio = 1

I am a beginner self-taught user of Postgres so I am sorry if this question is basic.

As always, the documentation is an excellent reference.  In here, it shows WHERE condition and condition is defined (further down the page) as

  where condition is any expression that evaluates to a result of type boolean. Any row that does not satisfy this condition will be eliminated from the output. A row satisfies the condition if it returns true when the actual row values are substituted for any variable references.

HAVING, as John pointed out in a comment, is for filtering on aggregates after they have been calculated, while WHERE is for filtering the rows that are gathered at the start. For example:

SELECT
  type, COUNT(type)
FROM table
GROUP BY type
HAVING COUNT(type) > 1

would return a set of rows where the COUNT(type) result ended up being more than 1..  The filtering done by HAVING takes place after the DB has already grouped the entire table by TYPE and calculated all COUNT() values. On the other hand

SELECT
  type, COUNT(type)
FROM table
WHERE type = 'EXAMPLE'
GROUP BY type

would return one row (where type is EXAMPLE) and the count of how many rows contained that type. The filtering here takes place AS THE DB COLLECTS THE ROWS up front .. Any row that doesn't have type 'EXAMPLE' is thrown out. Only one COUNT() is calculated in this instance.

One more complex example to show that the two are evaluated separately and can be combined in any query.

SELECT
  col_1, col_2, col_3, SUM(col_4), COUNT(col_4), AVG(col_4)
FROM table
WHERE col_2 IN ('A', 'B', 'C')
  AND col_3 > 3
GROUP BY col_1, col_2, col_3
HAVING COUNT(col_4) > 2
  AND AVG(col_4) > 10.5


Mongodb: slow shard balancing I've decided to shard a collection with 500M+ documents into 24 shards. All shards are on the same machine without replicas as was advised in this title (core sharding). The shard key is {"_id":1}. I encountered, that chunk migration is extremely slow. It have been processing 2 days and progress is about 30%. I tried to move chunks manually. I stopped the balancer and executed commands from this title But it seems to me that nothing happened. I started the balancer again and it continue to migrate chunks in the same way. Is there any method to make it faster?

Math first: you moved 150M documents in 2 days, which is roughly 860 documents per second including metadata and indices, where reading and writing all occurs on the same machine. That is not what I would call slow. The description coming to my mind is "lightning fast". ;)

Since there is no real distribution of the write load, an easy way to speed things up is to add two or more machines.

A few notes: sharding production data on non-replica shards is dangerous, to say the least. If one of the shards fails, the data contained is permanently unavailable until you get the shard up and running again. Plus, since there was no server to write to for a specific key range, values of that range can not be written. If the shard was a replica set, the failure would lead to the election of a new primary, to which all writes and (depending on the configuration) most or even all reads would go.

_id can be used as a shard key, however it should be hashed.


When possible, are clustered indexes that are declared as unique more performant than clustered indexes that aren't declared as unique? When applicable, will using the UNIQUE constraint for a clustered index help improve performance in any way under any scenario?

Adding to Tibor's answer, from the optimizer's perspective, key information can be very useful. This sometimes manifests in obvious ways like avoiding a Stream or Hash Aggregate operator to perform a redundant grouping operation.

Less obviously, the presence of a declared and enforced key can allow the optimizer to explore logical alternatives that are only valid when a key or functional dependency is known to the optimizer. The optimizer does not report when it could not explore a particular option due to the lack of a unique index.

From an execution performance point of view, one example of uniqueness helping performance is when a b-tree seek becomes a singleton lookup rather than a seek plus range scan:

For a unique key or constraint, the execution engine knows it will either find a row at the b-tree seek point, or it will not. When the index is not unique, the engine must scan forward or backward from the seek point to find any further matches. There is an overhead to doing that as I demonstrate in my article Enforcing Uniqueness for Performance.

In general, you are much better off enforcing a key than not. This protects data integrity as well as the other benefits mentioned above.

Related: Should I mark a composite index as unique if it contains the primary key?


Oracle - Date of Selection on table how to get last date when someone perform selection on table? Last accessed in terms of select statement only not last updated date

The only reliable way that I know of is to use auditing. If you just want to audit a single table:

AUDIT SELECT ON <tablename>;

but there are many other options. See the documentation at http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_4007.htm#SQLRF53737 for full details.

NB You must set the parameter AUDIT_TRAIL to a value other than NONE to actually enable the auditing to take place.


High availability sql server, secondary replicas can't be connected on the Availability group I created an availability group (two nodes) , then my secondary replica isn't connected like on the picture. I verified on error logs and i have this error . how can I give permission to my secondary replica ? thank you.

The AG are using "Database Mirroring" Endpoint to communicate between all nodes.
Your SQL Service account need to have "connect" permission on the endpoint.
You also need to grant "ConnectSQL" to the other node service account (or the Server - AITS\PLSBDATEST2$-  if using local service account).

So to recap, on PLSBDATEST1, you will have a login for PLSBDATEST2$ and you will grant permission to the AG endpoint to it. on PLSBDATEST2, you will have a login for PLSBDATEST1$ with same permission.

That should do the job.


how to import an existing database into SQL server 2012? I am brand new to SQL, SQL Server 2012 and SQL Databases. I have an example database already created for me that I want to import into my SQL Server.

I notice that there is a Books_log with it too. What is it? (I appreciate your answer but not my main question tho).

I've tried importing it by using SQL Server Management Studio. I created a new database, then I right-clicked it and selected Tasks, then Import Data, then the Import and Export Wizard Opened, then I gave it my best guess and selected Flat File Source as my Data Source, then I clicked on Browse and selected the Books.mdf database. Then at this point I feel like there is something wrong because when I moved to the Columns or Preview tab this is what it looks like:

Anyways, I clicked on Next. I chose the .Net Framwork Data Provider for SqlServer as my Destination. Then clicked on Next and this is what I got:

What am I doing wrong?

Books_log is the log file for the database, i.e. all changes you make to the database are logged to be able to rollback, etc. To be able to use an existing mdf, you can use attach, right click on "Databases", click attach and select your mdf.


Preventing query to stay in pool How can I prevent my finished queries to stay in pool?

Below is the image of my database and a lot of my finished queries are staying and I want them to be removed but don't know how.

This is my code that I made to sent the data from my database using Express:

Here is my articles.js file that sends the data as json.

router.get('/articles', (req, res) => {
  let sql = "SELECT * FROM ARTICLES";
  myDB.query(sql, (error, results) => {
    if (error) {
      console.log(error);
    } else {
      res.send(results)
    }
  })
});

and this is my connection.js that creates the connection to MySQL and Express

    const mysql = require('mysql');
const myDB = mysql.createPool({
    connectionLimit: 10,
    host: 'REMOTE_DATABASE_ADDRESS',
    user: 'hmidev',
    password: 'hmidev',
    database: 'VD_NEW_WEBSITE'
});

myDB.getConnection((error, connection) => {
    if (error) {
        console.log(error);
    } else {
        console.log(`Successfully connected at VD_NEW_WEBSITE`);
    }
});

module.exports = myDB;

Connections left in a connection pool will show up as "Sleep" in PROCESSLIST.  The "Time" will continue to grow until grabbed by a new "connect".


mysql 5.6 replication log-slave-updates doesn't work We have a setup of master -> replication -> replication
the dbs are: mysql 5.7 -> 5.6 -> 5.7

on the replication 5.6 we have the following my.cnf:

[mysqld]
default-storage-engine=InnoDB
innodb_file_per_table=1
performance-schema=0

max_connections=4096
group_concat_max_len=8196
innodb_flush_log_at_trx_commit=0
query_cache_limit=128M
query_cache_size=128M
max_allowed_packet=268435456
open_files_limit=29000
#log_timestamps = SYSTEM

slow-query-log=1
#log = /var/log/mysql_query.log

server-id               = 2

log-bin

replicate-do-db = db_main
log-slave-updates

slave-skip-errors = all

however the bin-log on the server is not being updated. The relay-log IS being update. server-id on the master 5.7 is 1 and on the last 5.7 slave is 3.
all servers have log-slave-updates and upon checking with mysqladmin var|grep update we see log_slave_updates ON.

What are we missing?

Thanks!

First, you might want to explicitly set those options to on and restart the instance (enabling binary logging requires restart)
log_slave_updates=ON
log_bin=mysql-binlog

Second, you are not supposed to replicate from a master which is at a higher version to a slave at a lower version because of incompatible changes in binary log formats across versions: https://dev.mysql.com/doc/refman/5.7/en/replication-compatibility.html


Unable to migrate local database to RDS instance I recently created an MySQL instance on Amazon RDS. Unfortunately, I'm not able to connect to the instance from the MySQL command line. I will brief you the details.

1) I want to move/migrate my MySQL database from local machine to this RDS instance.

I did a bit of searching in stackoverflow & found few links.

https://stackoverflow.com/questions/11731714/how-do-i-import-a-local-mysql-db-to-rds-db-instance

https://stackoverflow.com/questions/18565634/how-to-import-mysql-dump-into-amazon-rds?rq=1

http://trisummittechnologies.com/importing-mysql-data-into-rds/

https://stackoverflow.com/questions/11769605/php-site-not-connecting-to-mysql-on-amazon-rds

2) I have an RDS instance with the following details.

Endpoint: end.awsabc.com
My RDS db master username: iammaster.
I don't know the password.
RDS db name: rdsdb
local machine db name: localdb
name of SQLdump file of localdb: localdump.sql

3) These are the commands that I used in command prompt:

mysql -h end.awsabc.com -u iammaster-p  rdsdb< D:\localdump.sql

ERROR 2003: Cannot connect to MySQL server on abcdtrash.rds.com

Then I tried another method: This is the command I typed in the command prompt:

C:\wamp\bin\mysql\mysql5.6.17\bin>mysqldump -u root localdb| mysql --host=end.awsabc.com --user=iammaster rdsdb

Error: Access denied.

Is this because I don't have a password?? Can anyone tell me how to accomplish the task?

I prefer using MySQL workbench. It's much more easier & user friendly than the command line way.

It provides a simple GUI.

MySQL workbench or SQL Yog.

These are the steps that I did:

Install MySQL Workbench.
In AWS console, there must be a security group for your RDS instance. Add an inbound rule to that group for allowing connections from your machine. It's simple. Add your IP-address.
Open MySQL workbench, Add a new connection.
Give the connection a name you prefer.
Choose connection method- Standard TCP/IP
Enter your RDS endpoint in the field of Hostname.
Port:3306
Username: master username (the one which which you created during the creation of your RDS instance)
Password: master password
Click Test Connection to check your connection.
If connection is successful, click OK.
Open the connection.
you will see your database there.
Now you can export your mysqldump file to this database. Go to-> Server. Click Data Import.
You can check whether the data has been migrated by simply opening a blank SQL file & typing in basic SQL commands like use database, select * from table;

That's it. Viola.


MySQL 5.5 - view all queries executed on certain table I need to explore queries, which were executed on certain table.

For example, in Oracle we could investigate the V$SQL_TEXT, maybe something similar exists in MySQL 5.5?

The "general log", if turned on, records all queries touching all tables.
The "slow log", if turned on with long_query_time=0, records all queries, plus performance-related info, for all tables.

In either case, you would need to filter down to the desired table.  This cannot be done with a simple grep since the log formats involve multiple lines of text.  Still, it is better than nothing.

(There is no feature of MySQL to monitor a single table or database or user.  This Answer applies to all(?) versions of MySQL.)


Why is my query taking so long if my field is correctly indexed? I’m trying to figure out why my query is taking so long.  I have indexed my property_id field but still get awful results when querying it.  I have the following Explain and wonder if anyone can shed some light on it?

production=> explain analyze select * from item_events where property_id = 503;

 QUERY PLAN
------------------
Bitmap Heap Scan on item_events  (cost=576.74..57210.03 rows=22750 width=259) (actual time=137.932..97996.513 rows=23140 loops=1)
  Recheck Cond: (property_id = 503)
  Heap Blocks: exact=15371
  ->  Bitmap Index Scan on index_item_events_on_property_id_and_captured_at  (cost=0.00..571.05 rows=22750 width=0) (actual time=123.178..123.178 rows=23141 loops=1)
        Index Cond: (property_id = 503)
Planning time: 0.154 ms
Execution time: 98141.818 ms
(7 rows)

I have the following index that exists:
“index_item_events_on_property_id_and_captured_at” btree (property_id, captured_at)

And here is explain(analyze, buffers):

production=> explain (analyze, buffers) select * from item_events where property_id = 503;
                                                                              QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on item_events  (cost=576.74..57210.03 rows=22750 width=259) (actual time=559.704..97900.064 rows=23140 loops=1)
   Recheck Cond: (property_id = 503)
   Heap Blocks: exact=15371
   Buffers: shared hit=6523 read=8942
   ->  Bitmap Index Scan on index_item_events_on_property_id_and_captured_at  (cost=0.00..571.05 rows=22750 width=0) (actual time=556.513..556.513 rows=23140 loops=1)
         Index Cond: (property_id = 503)
         Buffers: shared hit=1 read=93
 Planning time: 0.150 ms
 Execution time: 97934.782 ms
(9 rows)

If you have a 5400 RPM hard drive, this is almost exactly how much time you would expect it to take to read 8942 blocks if there was no effective read-ahead or read re-ordering (as implemented by the kernel, with some collaboration from PostgreSQL).  You can confirm if the time goes to disk reads by turning track_io_timing on before doing the EXPLAIN (ANALYZE, BUFFERS).

Even with a single 5400 hdd, I've seen it get increased performance, up to 2x, by cranking up effective_io_concurrency to a large value, like 50 or 100.

If you repeat the query, is it faster the second time?  Is your goal to make this work from a hot-cache or a cold-cache?  How much RAM do you have?


How do I fix #1062 error received while importing EE database to localhost from remote server I am trying to import a MySQL database into the localhost database that was exported from the remote database and I am receiving a #1062 error (which is weirdly in French!):

#1062 - Duplicata du champ '1' pour la clef 'PRIMARY'

Google translate: # 1062 - Duplicate field '1' for key 'PRIMARY'

The code that it highlights is as follows:

--
-- Dumping data for table `exp_accessories`
--

INSERT INTO `exp_accessories` (`accessory_id`, `class`, `member_groups`, `controllers`, `accessory_version`) VALUES
(1, 'Expressionengine_info_acc', '1|5', 'content|members|admin_content|design|tools_communicate|homepage|addons_fieldtypes|content_files|admin_system|tools_data|addons|tools|tools_logs|tools_utilities|addons_accessories|content_files_modal|myaccount|addons_modules|addons_plugins|content_publish|content_edit|addons_extensions', '1.0'),
(2, 'Cartthrob_acc', '1|5', 'content|members|admin_content|design|tools_communicate|homepage|addons_fieldtypes|content_files|admin_system|tools_data|addons|tools|tools_logs|tools_utilities|addons_accessories|content_files_modal|myaccount|addons_modules|addons_plugins|content_publish|content_edit|addons_extensions', '1.0');

I am a novice with SQL and I can't see what it is on about. I can't see a duplicate in the primary key, the accessory_ids appear to be 1 and 2? Am I missing something here? And why is there a duplicate here and not on the remote server?

Many thanks

Gabe

Your local table is likely not empty, thus the error when conflicting with existent data. If you do not want to keep any of the current data, you need to delete all rows before the insert. One fast way to do that is with:

TRUNCATE TABLE exp_accessories;

Usually, mysqldump will drop the table before import, like this:

DROP TABLE IF EXISTS `exp_accessories`;

Regarding French, client sessions can change the default language setting by doing SET lc_messages = 'en_US'; and receive error messages in a different language.


Improving a search query I have a query for search function.

Basically search function allowed user to define what they "have" and "want". Then this query will filter out all the possible result which created by other user.

For example, I have apple (with good quality) and I want orange (with poor quality). So the result will display all user that have orange (with poor quality) and want apple (with good quality).

The search query a bit long and I try to simplify as below:

This stored procedure will get a user-defined table (ItemID & Quality) as parameter

@WantUdt AS HaveItemUdt READONLY,
@HaveUdt AS HaveItemUdt READONLY

Search query (user can define more than one items and quality, so I use IN):

SELECT * from tbl_Trade WHERE TradeID IN

 (SELECT TradeID from tbl_Want w INNER JOIN

  (SELECT TradeID FROM tbl_Have
  WHERE HaveID IN (SELECT ItemID FROM @HaveUdt) AND
  Quality IN (SELECT QualityID FROM @HaveUdt)) as h  --to filter [have],

 ON w.TradeID = h.TradeID

  WHERE WantID IN (SELECT ItemID FROM @WantUdt) AND
  Quality =IN (SELECT QualityID FROM @WantUdt)    --to filter [want]
)

Above query works as expected. However, I am having performance issue. I try do stress test by execute this stored procedure for multiple times within specific time (few seconds), and my SQL Server 2008 Express database seems can't effort and generate a timeout error

  Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

I guess it is because the query above use too many IN CLAUSE.

Is there any way to improve this query?

EDIT

Attached with the actual execution plan and statistic

(1 row(s) affected)
Table '#05C6342A'. Scan count 30813, logical reads 39101, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_Dota2ItemCustomSchema'. Scan count 14025, logical reads 26490, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_TradeItemHave'. Scan count 3182, logical reads 6476, physical reads 72, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_Dota2ItemSchemaExtend'. Scan count 0, logical reads 24134, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_Dota2ItemSchema'. Scan count 0, logical reads 29132, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#06BA5863'. Scan count 20899, logical reads 28266, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_TradeItemWant'. Scan count 4456, logical reads 8974, physical reads 42, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_Trade'. Scan count 1, logical reads 48, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_UserProfile'. Scan count 1, logical reads 205, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Execution Plan Image

The UDT has lot of scans as per the query plan,please try to create UDT with clustered index (primary key)

https://stackoverflow.com/questions/4451052/how-to-add-an-index-or-primary-key-to-a-user-defined-table-type-in-sql-server


What is DBCC SHRINKFILE actually doing? I have a database which was 11TB in size. I recently truncated over 5TB of data from this database.

(I am fully familiar with all the reasons why you would not normally shrink a database please)

I'm curious about what the DBCC SHRINKFILE command is actually doing because when I run the command to shrink a file which is about 650000MB in size with 45% free space it doesn't seem to actually move any pages around.

The code in question is:

USE [CAF]
GO
DBCC SHRINKFILE (N'FILENAME' , 650239)
GO

When I monitor the performance metrics of the server on executing DBCC SHRINKFILE I see the following

% Active time of the disk immediately jumps to 100%.
The sqlservr.exe and system processes both start reading at a continuous 2MB/s
each (sometimes peaking at 5MB/s).
The disk queue length jumps to exactly 1
Disk iops jump to about 250 (note the disks in tests can achieve 6000iops)
CPU remains idle

Even if I tell DBCC SHRINKFILE to shrink the file by a single MB and let it run for 30 minutes, it does not complete.

Everything I've read suggests that DBCC SHRINKFILE should take pages from the end of the file and move them to free space near the beginning but even at the worst performance it should take no more than a few minutes to move 1MB worth of pages

What is DBCC SHRINKFILE actually doing, it just doesn't make sense to me?

Everything I've read suggests that DBCC SHRINKFILE should take pages
  from the end of the file and move them to free space near the
  beginning, What is DBCC SHRINKFILE actually doing?

The steps that DBCC SHRINKFILE goes through:

DbccSpaceReclaim Reclaim free  space. E.G. by purging empty extents.
DbccFilesCompact move the non-LOB pages to before the point specified as the second parameter, as close to the beginning of the data file as possible
DbccLOBCompact move the LOB pages to before the point specified as the second parameter, as close to the beginning of the data file as possible

During the DbccFilesCompact & DbccLOBCompact pages are moved from after the parameter passed to the DBCC SHRINKFILE statement to before the parameter, as close to the data pages as possible.
 if it is possible to do so

AFTER all these page movements, the actual truncation happens and you see the size change.

Source

Monitoring the DBCC SHRINKFILE process can give you more information on which of these three takes the longest / uses the most resources (but I have noticed DbccFilesCompact running while Lob Pages where being moved when testing, will have to do some investigating there).

According to this blogpost, Shrinking files is done in batches of  ~32 pages per transaction. As to get the ability to resume the shrink when stopping the process.

The statement that when shrinking your database files, you can resume approx. where you left off is also confirmed in this answer.

It seems like nothing happened when cancelling the command, but pages would have been moved in these smaller batches. The size difference is only noticed at the end due to the truncation happening at the end of the DbccCompact events.

Fully logged

When looking at the log file while a shrink is happening, this is also confirmed due to many transaction being logged:

LOP_BEGIN_XACT
LOP_MODIFY_ROW
LOP_MODIFY_ROW
... --many more LOP_MODIFY_ROW records
LOP_COMMIT_XACT
LOP_BEGIN_XACT
...

We are seeing all these LOP_MODIFY_ROW records because of the fact that shrinking is fully logged in the transaction log. Which has additional overhead.

If you don't want all the logging happening at once or you need more time in between shrink operations you could run the shrink in batches, with or without WAITFOR delays.

Another side effect of the multiple transactions is that blocking can occur on multiple occasions while shrinking or trying to shrink your file.

Again, to mitigate blocking you can try shrinking in smaller batches, with time in between shrink.

The first shrink you should try is with: TRUNCATEONLY as to not move the pages and simply try and release space to the OS.

DBCC SHRINKFILE (N'FILENAME' , TRUNCATEONLY)

  The database contains lots of binary files and XML data in the MBs how
  will that impact the shrink?

Shrinking Lob data can take a very long time due to additional index / table scan's to update the pointers. Take a look at this post by Paul Randal

In short, you could see the shrink taking a long time processing DbccLobCompact.

This is due to a pointer in the pages on the table pointing to the 'OFF ROW' values (Lob data) but not the other way around. When moving Lob Data due to the shrink, we have to update the pointer in the on row pages resulting in a lot of extra overhead.

The overhead is similar for nonclustered indexes.

@TiborKaraszi also mentioned:

  A couple of more considerations (apart from the LOB aspect). Shrink
  need an X lock, on which it will wait on forever. Moving a heap page
  means updating all NC indexes that points to all rows that were
  affected by this heap-page-movement.

Meaning that the reason for your shrink operation taking so long is that your shrink operation might be blocked by queries & that it can block other queries while it is running.

Additionally, shrinking heaps can be expensive due to the NC index updates.

TL;DR

All in all shrinking larger files with larger objects will take longer due to scanning and updating the pointers in the underlying clustered / nonclustered indexes.

Even if 1mb of lob data is moved, the objects would have to be read and updated accordingly. The speed of this will also be based on your IO subsystem.

Another valid reason could be that your query is blocked due to the exclusive locking behavior.

While the shrink is running you could check for blocking with this query:

SELECT
est.TEXT,
er.blocking_session_id,
er.last_wait_type,
er.reads,
er.cpu_time,
er.total_elapsed_time
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS est
WHERE blocking_session_id != 0;

Is shrinking a single big lob record an all or nothing operation?

Another reason where I was not entirely sure of is that an entire Lob object and it pages will be moved as far to the front of the data file as possible, even if not all pages are over the threshold defined in the DBCC SHRINKFILE.

EDIT: Tested this and this statement is false, only the Lob pages above the threshold are moved with DBCCLobCompact even if not all the pages of one record are above this threshold.

When testing with DBCC IND, we only see a few lob pages at the end of the file being moved, even if they belong to one record.

Extra notes

Shrinking files is single threaded
The better alternative for shrinking that Paul Randal points out in
one of his blogposts:

  Create a new filegroup
  Move all affected tables and indexes into the new filegroup using the    CREATE INDEX … WITH (DROP_EXISTING = ON) ON syntax, to move the
  tables and remove fragmentation from them at the same time
  Drop the old filegroup that you were going to shrink anyway (or    shrink it way down if its the primary filegroup)

Shrinking will cause heavy fragmentation


MySQL Replace function throwing error I am trying to update ^G (Ctrl G) in a table, below is scenario-

CREATE TABLET TEST(COLA INT,DESCRIPTION VARCHAR(20));

I have crtl G in my data for description field, so to replace it with a \,I am running following command-

UPDATE TEST
SET DESCRIPTION=REPLACE(DESCRIPTION,CHAR(92),CHAR(7))
WHERE DESCRIPTION like '%' CHAR(07) '%';

It runs fine,converts the ^G into \ but it gives a warning as well

TRUNCATED INCORRECT INTEGER VALUE '%'

Can somebody please help me understand this warning.

That's odd, When I run your statements on 5.5 I get a syntax error:

mysql> CREATE TABLE TEST(COLA INT,DESCRIPTION VARCHAR(20));
Query OK, 0 rows affected (0.02 sec)

mysql> UPDATE TEST
    -> SET DESCRIPTION=REPLACE(DESCRIPTION,CHAR(92),CHAR(7))
    -> WHERE DESCRIPTION like '%' CHAR(07) '%';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near 'CHAR(07) '%'' at line 3

Anyway, the proper way to do what you want to achieve would be concatenating '%' and the \G, that is:

UPDATE TEST
SET DESCRIPTION=REPLACE(DESCRIPTION,CHAR(92),CHAR(7))
WHERE DESCRIPTION like CONCAT('%',CHAR(07),'%');


Calling a SQL Server job within another job Is it possible to call a SQL Server job to run within another job?

I know we can add all steps of Job 1 to Job 2, but I prefer not to do that. First the Job 2 is already quite big and second I couldn't find a copy-paste option to copy steps between jobs, so it would be time consuming to add the steps manually.

Any suggestion is appreciated.

Option 1

In Job2, create a job step of type TSQL Command. In the contents, have it run the existing job (sp_start_job)

EXECUTE msdb.dbo.sp_start_job 'Job1'

That is going to run asynchronously, thus after it starts calls the stored procedure, it will return and perform the next step in the job. It will not wait for that started job to complete. If the called job fails, it will not percolate back to the invoking job.

Option 2

Right click on Job1 and script to new query window. Repeat that with Job2 and then thread the job steps from 1 into 2 as/where needed. Far less clicking than recreating the wheel and hopefully less error prone.


SQL Server Management Studio is not available When I installed SQLManagementStudio_x64_ENU.exe I am not able to connect to my server. The server name is not visible in either image.

Is there any issue in my installation?

to connect to local server and default instance just use any of these ., local, localhost or YourMachineName.

If you are trying to connect to a named instance on your local machine just use any of the above followed by \InstancenName.

i.e  .\InstanceName, local\InstanceName, localhost\InstanceName or YourMachineName\InstanceName.

Note

If you want to connect to the default instance you just use the machine name, if you are trying to connect to a Named Instance then you have to use the machine name and the Instance Name.


Referring to function parameters inside a Postgres function I have a function like this in Postgres 9.5:

CREATE OR REPLACE FUNCTION my_func (p1 date, p2 integer, p3 integer, p4 integer) RETURNS
SETOF some_datum AS $func$
        BEGIN
                RETURN QUERY
                with t as (
                    select p4, postcode, p1, rent * 12 * 100 / nullif(price, 0) as result
                      FROM crosstab (
                        $$
                          select postcode::text, indicator::int, value::float
                          from my_data
                          where (indicator = p2 or indicator = p3) and date = p1
                          order by 1,2
                        $$)
                      AS ct (
                        "postcode" text,
                        "price" float,
                        "rent" float)
                )
                select * from t where result is not null;
        END
$func$ LANGUAGE plpgsql;

When I load it with PSQL I get back the error:

ERROR:  column "p2" does not exist
LINE 4:                           where (indicator = p2 ...
                                                     ^
QUERY:
                          select postcode::text, indicator::int, value::float
                          from my_data
                          where (indicator = p2 or indicator = p3) and date = p1
                          order by 1,2

I tried to prefix the p2 etc with my_func. but it doesn't work.
How can I refer to a function parameter in that context?

Crosstab just sees a string, so send a good string.

CREATE OR REPLACE FUNCTION my_func (p1 date, p2 integer, p3 integer, p4 integer) RETURNS
SETOF some_datum AS $func$
        BEGIN
                RETURN QUERY
                WITH t AS (
                    SELECT p4, postcode, p1, rent * 12 * 100 / nullif(price, 0) as result
                      FROM crosstab (
                        format(
                          $$
                            SELECT postcode::text, indicator::int, value::float
                            FROM my_data
                            WHERE (indicator = %s or indicator = %s)
                              AND date = '%s'
                            ORDER BY 1,2
                          $$,
                          p2,
                          p3,
                          p1
                        )
                      )
                      AS ct (
                        "postcode" text,
                        "price" float,
                        "rent" float)
                )
                select * from t where result is not null;
        END
$func$
LANGUAGE plpgsql;

Btw, if you're going to used named params, p1, p2, p3, etc are a bad idea. They're already $1, $2, $2. I would suggested at least something a little easier to follow like indicator1, indicator2


Assumptions / Clarifications

No need to differentiate between infinity and open upper bound (upper(range) IS NULL). (You can have it either way, but it's simpler this way.)

NULL vs. infinity in PostgreSQL range types

Since date is a discrete type, all ranges have default [) bounds.
The manual:

The built-in range types int4range, int8range, and daterange all use a
canonical form that includes the lower bound and excludes the upper
bound; that is, [).

For other types (like tsrange!) I would enforce the same if possible:

Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL

Solution with pure SQL
With CTEs for clarity:
WITH a AS (
   SELECT range
        , COALESCE(lower(range),'-infinity') AS startdate
        , max(COALESCE(upper(range), 'infinity')) OVER (ORDER BY range) AS enddate
   FROM   test
   )
, b AS (
   SELECT *, lag(enddate) OVER (ORDER BY range) < startdate OR NULL AS step
   FROM   a
   )
, c AS (
   SELECT *, count(step) OVER (ORDER BY range) AS grp
   FROM   b
   )
SELECT daterange(min(startdate), max(enddate)) AS range
FROM   c
GROUP  BY grp
ORDER  BY 1;

Or, the same with subqueries, faster but less easy too read:
SELECT daterange(min(startdate), max(enddate)) AS range
FROM  (
   SELECT *, count(step) OVER (ORDER BY range) AS grp
   FROM  (
      SELECT *, lag(enddate) OVER (ORDER BY range) < startdate OR NULL AS step
      FROM  (
         SELECT range
              , COALESCE(lower(range),'-infinity') AS startdate
              , max(COALESCE(upper(range), 'infinity')) OVER (ORDER BY range) AS enddate
         FROM   test
         ) a
      ) b
   ) c
GROUP  BY grp
ORDER  BY 1;

How?
a: While ordering by range, compute the running maximum of the upper bound (enddate) with a window function.
Replace NULL bounds (unbounded) with +/- infinity just to simplify (no special NULL cases).
b: In the same sort order, if the previous enddate is earlier than startdate we have a gap and start a new range (step).
Remember, the upper bound is always excluded.
c: Form groups (grp) by counting steps with another window function.
In the outer SELECT build ranges from lower to upper bound in each group. Voilá.
Or with one less subquery level, but flipping sort order:
SELECT daterange(min(COALESCE(lower(range), '-infinity')), max(enddate)) AS range
FROM  (
   SELECT *, count(nextstart > enddate OR NULL) OVER (ORDER BY range DESC NULLS LAST) AS grp
   FROM  (
      SELECT range
           , max(COALESCE(upper(range), 'infinity')) OVER (ORDER BY range) AS enddate
           , lead(lower(range)) OVER (ORDER BY range) As nextstart
      FROM   test
      ) a
   ) b
GROUP  BY grp
ORDER  BY 1;

Sort the window in the second step with ORDER BY range DESC NULLS LAST (with NULLS LAST) to get perfectly inverted sort order. This should be cheaper (easier to produce, matches sort order of suggested index perfectly) and accurate for corner cases with rank IS NULL. See:

PostgreSQL sort by datetime asc, null first?

Related answer with more explanation:

Compare multiple date ranges

Procedural solution with plpgsql
Works for any table / column name, but only for type daterange.
Procedural solutions with loops are typically slower, but in this special case I expect the function to be substantially faster since it only needs a single sequential scan:
CREATE OR REPLACE FUNCTION f_range_agg(_tbl text, _col text)
  RETURNS SETOF daterange AS
$func$
DECLARE
   _lower     date;
   _upper     date;
   _enddate   date;
   _startdate date;
BEGIN
   FOR _lower, _upper IN EXECUTE
      format(
         $sql$
         SELECT COALESCE(lower(t.%2$I),'-infinity')  -- replace NULL with ...
              , COALESCE(upper(t.%2$I), 'infinity')  -- ... +/- infinity
         FROM   %1$I t
         ORDER  BY t.%2$I
         $sql$, _tbl, _col)
   LOOP
      IF _lower > _enddate THEN     -- return previous range
         RETURN NEXT daterange(_startdate, _enddate);
         SELECT _lower, _upper  INTO _startdate, _enddate;

      ELSIF _upper > _enddate THEN  -- expand range
         _enddate := _upper;

      -- do nothing if _upper <= _enddate (range already included) ...

      ELSIF _enddate IS NULL THEN   -- init 1st round
         SELECT _lower, _upper  INTO _startdate, _enddate;
      END IF;
   END LOOP;

   IF FOUND THEN                    -- return last row
      RETURN NEXT daterange(_startdate, _enddate);
   END IF;
END
$func$  LANGUAGE plpgsql;

Call:
SELECT * FROM f_range_agg('test', 'range');  -- table and column name

The logic is similar to the SQL solutions, but we can make do with a single pass.
SQL Fiddle.
Related:

GROUP BY and aggregate sequential numeric values

The usual drill for handling user input in dynamic SQL:

SQL injection in Postgres functions vs prepared queries

Index
For each of these solutions a plain (default) btree index on range would be instrumental for performance in big tables:
CREATE INDEX foo on test (range);

A btree index is of limited use for range types, but we can get pre-sorted data and maybe even an index-only scan.


Database in simple recovery to save space This old SQL Server 2000 instance has a 14Gb disc for data and a 30Gb disc for backups.

Even though it is old, it still has tons of users doing transactions so the log file of the only database that is on this server fills the data drive everyday.

I figured I would put it in simple recovery and flush the transaction log, but I wonder what it impacts.

Without a T-log you cannot use your backup to recover to any other point in time than the time of the full backup, right ? What are the most important impacts apart of this I should be aware of when doing such a change to a database ? What else than losing a day of data (it is backup up daily to tape) might happen when I'm not generating t-logs ?

Thanks in advance

First a quick note on moving to SIMPLE recovery.  This is a business decision.  It is not in any way a technical decision.  Your business needs to weigh the cost of staying in FULL (which may mean a new server with additional space, archiving current data to free up some space, etc) vs the cost of losing up to a day or more of data.  Once they have decided then you can proceed.

Based on your question and the comments I see one of two scenarios:

You are in FULL recovery mode but are not taking log backups.

At this point you need to start taking log backups on a regular basis.  In all probability this will actually save you space.  If you take a log backup to clear the log less than once a day (Note: FULL backups do not release log space) then your transaction log is probably larger than it needs to be.  Get your backups in order then figure out how big your transaction log actually needs to be.  In all probability it will be quite a bit smaller than it currently is giving you additional space for backups.  Worst case move your backups to tape more frequently than you currently are.

You are in FULL recovery mode but are taking log backups.

If this is the case then you may need to increase the frequency of your log backups.  This will not incur additional space requirements since the log files will just be smaller (there is some small overhead for header info but it's not that significant).  This will allow your log to clear faster and stay smaller.  Assuming (and this is a big assumption) that you don't have one or more individual transactions that are requiring the log space.  If that is the case then only changing your code to decrease the size of your transactions is going to let you get away with a smaller log file.  Even if you switch to SIMPLE recovery you are still going to require log space for the largest single transaction (or largest combination of simultaneous transactions).  If you shrink your log file and run a transaction that needs more space than is avialable it's just going to grow the log again.

Again switching between FULL and SIMPLE recovery is a business decision.  Making that kind of decision without consulting the business (and then being wrong) is a good way to get yourself fired.


Oracle Hierarchical query: with two node attributes NodeId and NodeType I have the following use case where I want to make use of hierarchical queries to get the desired result.

In my use case I have two types of node say 'A' and 'B'. So the unique identifier of node is its ID and Type.

Now the problem is when two nodes with same Id and different type, when I call connect by clause only on id I get child for other types of nodes also (if id of that node is same).

create table TreeTest (
nodeid integer,
nodetype char(1),
parentid integer,
parenttype char(1)
);

data in table:

   1, 'A', NULL, null
   2, 'A', 1, 'A'
   3, 'A', 1, 'A'
   2, 'B', NULL, null
   3, 'B', 2, 'B'

Now I am firing the following query (which is not giving the correct result)

SELECT * FROM TREETEST
START WITH PARENTID = 1
CONNECT BY PRIOR nodeid = PARENTID;

I tried following query also, but again wrong result

SELECT * FROM TREETEST
START WITH PARENTID = 1 AND PARENTTYPE = 'A'
CONNECT BY PRIOR nodeid = PARENTID AND NODETYPE = PARENTTYPE;

Kindly provide the correct query and kindly also explain why second query is not working.

Expected output:

2   A   1   A
3   A   1   A

You're missing a PRIOR for the second condition in the connect by.

SQL> select * from TREETEST
     start with PARENTID = 1 and PARENTTYPE = 'A'
     connect by
          prior NODEID = PARENTID
      and prior NODETYPE = PARENTTYPE; -- note the PRIOR here too

    NODEID N   PARENTID P
---------- - ---------- -
         2 A          1 A
         3 A          1 A

Your query looks a bit odd to me, but that might very well be legit. I would have expected the first line in your sample data to be your root node, and thus a query like this:

SQL> select * from TREETEST
     start with NODEID = 1 and NODETYPE = 'A'
     connect by
          prior NODEID = PARENTID
      and prior NODETYPE = PARENTTYPE;

    NODEID N   PARENTID P
---------- - ---------- -
         1 A
         2 A          1 A
         3 A          1 A

And if you didn't want the parent to show up in the result set:

SQL> select * from TREETEST
      where level > 1       -- don't return the root node
      start with NODEID = 1 and NODETYPE = 'A'
      connect by
           prior NODEID = PARENTID
       and prior NODETYPE = PARENTTYPE;

    NODEID N   PARENTID P
---------- - ---------- -
         2 A          1 A
         3 A          1 A

Some references from the Hierarchical Queries documentation:

  PRIOR is a unary operator and has the same precedence as the unary + and - arithmetic operators. It evaluates the immediately following expression for the parent row of the current row in a hierarchical query.

If you look at the railroad diagram there, you'll see the syntax is:

  starts with condition connect by [nocycle] condition [AND condition]*

And in this case, for the connect by part, each condition is a simple comparison condition with the syntax:

  expression operator expression

The PRIOR operator doesn't apply to the whole condition, it is part of the expression(s) in the conditions. I.e. it's as if it was parsed like this:

connect by ( PRIOR(child_column) = parent_column )

(PRIOR can also appear on the right-hand side.)

Your example query:

CONNECT BY PRIOR nodeid = PARENTID AND NODETYPE = PARENTTYPE;

is interpreted as:

CONNECT BY ( PRIOR(nodeid) = PARENTID  ) AND ( NODETYPE = PARENTTYPE );

The second condition (after the AND) applies to the type columns of the current row only, it doesn't reference the parent row at all. The PRIOR is part of the first condition only, and doesn't somehow influence to the second.


AWS Postgres RDS "storage-full" and not responding. How to bring back online? I cannot reboot nor stop the DB in AWS console. I cannot connect it with PG Admin. My Java app also print error like this.
org.postgresql.util.PSQLException:
Connection to xxxx.us-east-1.rds.amazonaws.com: 5432 refused.
Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

And And I just realize the storage has been decreasing all the way to zero after the DB is down. I find it really astonishing since the amount of QPS is sparse and low. How can the storage decrease so steadily?

Turns out in this "storage-full" status you cannot connect to the instance. Only thing you can do is to increase storage.
https://aws.amazon.com/premiumsupport/knowledge-center/rds-autoscaling-low-free-storage/


Stored Procedure null parameter within where clause I want to use a parameter within where clause only if it's value is provided by strongly typed dataset, this is what I am trying at the moment, I get right results when I provide parameter3 and no results when I don't provide it's value.

What I desire is when I provide no value for parameter3, it should not use it in the query, as it's value is null, and I want to see all of the results in the query, not where Paramerter3 = null ones:

ALTER procedure [dbo].[GetData]
(
    @Parameter1 varchar(256),
    @Parameter2 varchar(256),
    @Parameter3 int = null
)
AS

SELECT
    *
FROM
    Table1
WHERE
    Table1.URL LIKE '%' + @Parameter1 + '%'
    AND Table1.ID = @Parameter2
    AND (@Parameter3 IS NULL OR Table1.ID2 = @Parameter3)
ORDER BY
    Table1.Title

Edit: I tried Thomas answer and executed like this:

EXEC @return_value = [dbo].[GetData]
                              @Parameter1 = N'asda',
                              @Parameter2 = N'asda',
                              @Parameter3 = null

SELECT  'Return Value' = @return_value
GO

I also updated the stored procedure as Thomas said.

SELECT *
FROM Table1
WHERE Table1.URL LIKE '%' + @Parameter1 + '%' AND Table1.ID = @Parameter2
AND
(
    @Parameter3 is null
    or Table1.ID2 = @Parameter3
);

Take a look at the above example.  If you change your AND clause to a nested OR clause specifying your initial expression as well as @Parameter3 is null.  That will then demand that the nested expression is true if @Parameter3 is NULL.


Backing up to Nul on an SQL Mirror I've seen and read a lot of questions about the transaction log and I know that backing it up to nul is usually frowned upon, however my understanding is that this is from a data recovery perspective.  As there is a lot of transient data passing through this system the logs are changed quite rapidly and can grow quite large.  In order to save disk space I do not want to keep data that has no intrinsic value.

The Setup
We have two SQL servers running SQL Server Mirroring, these are important from a usage (up-time) perspective but the data is transient and for the most part unimportant.

We have a 24 hour RPO on the servers but a 5 minute RTO.

What I Do
In order to manage the transaction log size I run a log backup every 15 minutes to backup the log file to NUL as below.

  BACKUP LOG [DatabaseName] TO DISK = 'NUL:';

So my question is thus: Other than not being able to recover data are there any other potential issues that could occur by backing up to NUL in a mirrored environment?

If you are complying with all the RPO and RTO requierments by having database mirroring in place, with daily full backups then there are no data consistency  issues with making backups to NUL. So the answer to your question is No.

But (there is always a but) you are not having a secondary restore path if a full backup is corrupted and both the servers go down and your restore options are always the point in time when the daily backup is made.

By making normal log backups and storing them for 48 hours you can restore past a single corrupted full backup which is not possible to do with differential backups.


Storing timeseries data with dynamic number of columns and rows to a suitable database I have a timeseries pandas dataframe which dynamically increases the columns every minute as well as adds a new row:
Initial:
timestamp                100     200     300
2020-11-01 12:00:00       4       3       5

Next minute:
timestamp                100     200     300   500
2020-11-01 12:00:00       4       3       5     0
2020-11-01 12:01:00      14       3       5     4

The dataframe has these updated values and so on every minute.
so ideally, I want to design a database solution that supports such a dynamic column structure. The number of columns could grow to over 20-30k+ and since it's one minute timeseries, it will have 500k+ rows per year.
I've read that relational db's have a limit on the number of columns so that might not work here, but also, since I am setting the data for new columns and assigning a default value(0) to previous timestamps, I lose out on the DEFAULT param that's there on MySQL.
Eventually, I will be querying data for 1 day, 1 month to get the data for the columns and their values.
Please suggest a suitable database solution for this type of dynamic row and column data.

Usually a dynamic data problem like this can be solved by storing the dynamic portion of the schema in its own table, transposed as rows.
For example you can have an Intervals table where one column is called Interval and another column called Values. Interval would store 100, 200, 300 etc for each instance of values for that interval.
You could either store the Timestamp as a column in this table too, or my recommendation would be to normalize Timestamp into its own table with a TimestampId that is a foreign key field in your Intervals table.
Implementing your schema this way then allows you to not worry about how many dynamic Intervals are created since this it's a row based generic solution.


Custom Aggregate Postgresql C extension with input as initial condition Is there a way to write a custom aggregate Postgresql C extension function that takes an input from the user and sets it as the initial condition? If so, how?

I’ve written several Postgresql Extensions in the past. The inputs are always columns of the table, but now it is requirement —for theoretical reasons- that I need to make an aggregate extension dependent on one user input that changes a lot. In particular, this values cannot be stored in the database either.

Edit 1:  For example, say I want to run the function

aggregate fancy_select( int tmp_accum_value, int column, int *fancy_math_stuff){

return (row *** fancy_math_stuff)
}

where *** is a fancy math operation that uses the entire fancy_math_stuff array and said array is passed on the first call but it is not stored as column in the database. tmp_accum_value is the intermediary state of the accumulation.

If you need to pass an argument to an aggregate function, just do it. That argument does not have to be a table column.

For example:

SELECT myagg(col1, 'initial value') FROM atable;

True, you could also call the function as

SELECT myagg(col1, col2) FROM atable;

but nobody forces you to do that, if you want the second argument to be a constant used for initializing then state.


Take a Column, Split it into New Columns Example table:

  ID    CB        CB2   CB3   CB4
  ----  --------  ----  ----  ----
  1     亀 龜 龜
  2     竜 龒

Note: Each character is separated by a space.
Note: The number of characters in CB varies.

I would like to move each character in column CB (after the first) to its own, separate, column, so that no more than one character is in each column.

Like this:

  ID    CB    CB2   CB3   CB4
  ----  ----  ----  ----  ----
  1      亀    龜    龜
  2      竜    龒

(SQLite)

Worked with an example of CB column with maximum character size of 3 sqllite(WeB),just a demonstration how to split characters.

   CREATE TABLE TABLE1 (ID number,CB text(3),CB2 text(1),CB3 text(1),CB4 text(1));

INSERT INTO TABLE1 VALUES(1,'ABC','','','');
INSERT INTO TABLE1 VALUES(2,'DE','','','');

  select id,
CASE
 WHEN LENGTH(CB) >= 1 THEN SUBSTR(CB,1,1)
 END CB,

CASE
 WHEN LENGTH(CB) >= 2 THEN SUBSTR(CB,2,1)
 END CB2,

CASE
 WHEN LENGTH(CB) >= 3 THEN SUBSTR(CB,3,1)
 END CB3

from
table1

Note:Not a solution,you can work out from this idea.

Updating on same table

   --table creation
CREATE TABLE TAB1 (ID integer,CB text(5),CB1 text(1),CB2 text(1),CB3 text(1),CB4 text(1));
 --- Insert sample rows
 INSERT INTO TAB1 VALUES(1,'ABCDE','','','','');
 INSERT INTO TAB1 VALUES(2,'DE','','','','');
 INSERT INTO TAB1 VALUES(3,'KLM','','','','');

---update rows
  UPDATE tab1
  SET
    cb  = CASE WHEN length(cb) > 1  THEN substr(cb,1,1)  END,
    cb1 = CASE WHEN length(cb) >= 2  THEN substr(cb,2,1) END,
    cb2 = CASE WHEN length(cb) >= 3  THEN substr(cb,3,1) END,
    cb3 = CASE WHEN length(cb) >= 4  THEN substr(cb,4,1) END,
    cb4 = CASE WHEN length(cb) >= 5  THEN substr(cb,5,1) END
  WHERE id IN ( SELECT id FROM tab1);

If you have two tables just join by id(inner) and update target table by using case statement


Should I create a separate user table for different web products within the same platform? We have a web product for young professional that gives them the possibility to create their page to show their professional identity.
So a table users that has both information about the user (email, password, name) including their credentials and information about their page (premium or not, page address, theme)

Now we want to offer the possibility for recruiters to signup to our platform to browse through candidates. A recruiter can also be a user with a page but does not have to.

Now our two approaches:

A/ Create a table recruiters with name and credentials of the recruiter and a column user_id to connect with the ID of the table users if they have created a site.

Benefits : The product can be easily developed separately, by two different teams.
Inconvenient : Duplicates of the name and credentials if the recruiter is also a user.
We would need to either update both credential when one is updated or to let them have two different email/password combination, one for their user account, one for their recruiter account.

Database structure:

users
ID name email password group_id premium theme page_address

recruiters
ID name email password company_id user_id

B/ Add the recruiters to the users table with a different group_id and move all the information about the users page in another table (premium or not, page address, theme). We would also have a third table for the recruiter containing any information specific to them.

Benefits : One table with all the credentials.
Inconvenient : If we reach millions of users, any query among recruiters will have to take a tiny subset among a huge table. Also : lots of join to get the site information for every user.

Database structure:

users
ID name email password group_id

pages
user_id premium theme page_address

recruiters
user_id company_id

C/ Any other solution?

Thank you for your inputs!

Tristan

A user is a user, which is different than a person.

A user is a role played by a person. A recruiter is also a role played by a person.

create table party(
  id serial primary key,
  name text not null
);

create table role_type(
  id char(1) primary key,
  name text not null unique
);

insert into role_type values
('u', 'user'),
('r', 'recruiter');

/* use Table Inheritance to add role-specific details. */
create table party_role(
  party_id integer not null references party(id),
  role_type char(1) not null references role_type(id),
  ...
  primary key (party_id, role_type)
);

/*  create a user: */

insert into party values
(1, 'Neil');
insert into party_role values
(1, 'u');

/* create someone that is both a recruiter and user: */
insert into party values
(2, 'Tristan');
insert into party_role values
(2, 'u'),
(2, 'r');


How to attach .mdf file to SQL Server 2005 without an .ldf file? I want to attach a database to SQL Server 2005 but I have only its .mdf file

How can I attach it?

E.g like this:

CREATE DATABASE AdventureWorks2012 ON (FILENAME = '{drive}:\{file path}\AdventureWorks2012_Data.mdf') FOR ATTACH_REBUILD_LOG;


Pentaho Data Integration: 'Execute SQL statement' to create trigger I'm trying to create a mysql trigger via an Execute SQL Statement step in my transformation. Below are the commands I'm attempting to run.

DROP TRIGGER IF EXISTS standing_actions_swap_before_insert_row_tr;
DELIMITER $$
CREATE DEFINER=`user`@`%` TRIGGER standing_actions_swap_before_insert_row_tr BEFORE INSERT ON `standing_actions_swap`
FOR EACH ROW
BEGIN
    SET NEW.id = CONCAT(NEW.acad_career, NEW.status, NEW.action);
END
$$
DELIMITER ;

Unfortunately, for whatever reason, while the following works in various other db software suites (e.g. Sequel Pro, Aqua Data Studio), I always get an error in Pentaho Data Integration. I've tried omitting delimiters and checking and unchecking the Execute as a Single Statement box - unfortunately, it still no worky.

Any help is appreciated. Thanks!

Edit:

Here's is the stack trace:

2015/05/15 16:13:13 - Recreate ac.student_actions_swap before_insert trigger.0 - ERROR (version 5.3.0.0-213, build 1 from 2015-02-02_12-17-08 by buildguy) : An error occurred, processing will be stopped:
2015/05/15 16:13:13 - Recreate ac.student_actions_swap before_insert trigger.0 - Couldn't execute SQL: DELIMITER $$
2015/05/15 16:13:13 - Recreate ac.student_actions_swap before_insert trigger.0 - CREATE DEFINER=`user`@`%` TRIGGER standing_actions_swap_before_insert_row_tr BEFORE INSERT ON `standing_actions_swap`
2015/05/15 16:13:13 - Recreate ac.student_actions_swap before_insert trigger.0 - FOR EACH ROW
2015/05/15 16:13:13 - Recreate ac.student_actions_swap before_insert trigger.0 - BEGIN
2015/05/15 16:13:13 - Recreate ac.student_actions_swap before_insert trigger.0 -     SET NEW.id = CONCAT(NEW.acad_career, NEW.status, NEW.action)
2015/05/15 16:13:13 - Recreate ac.student_actions_swap before_insert trigger.0 -
2015/05/15 16:13:13 - Recreate ac.student_actions_swap before_insert trigger.0 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER $$
2015/05/15 16:13:13 - Recreate ac.student_actions_swap before_insert trigger.0 - CREATE DEFINER=`user`@`%` TRIGGER standing_actions_swap_before_inse' at line 1
2015/05/15 16:13:13 - Recreate ac.student_actions_swap before_insert trigger.0 - ERROR (version 5.3.0.0-213, build 1 from 2015-02-02_12-17-08 by buildguy) : Error initializing step [Recreate ac.student_actions_swap before_insert trigger]
2015/05/15 16:13:13 - recreate_standing_actions_swap_trigger - ERROR (version 5.3.0.0-213, build 1 from 2015-02-02_12-17-08 by buildguy) : Step [Recreate ac.student_actions_swap before_insert trigger.0] failed to initialize!
2015/05/15 16:13:13 - Recreate ac.student_actions_swap before_insert trigger.0 - Finished reading query, closing connection.
2015/05/15 16:13:13 - Spoon - ERROR (version 5.3.0.0-213, build 1 from 2015-02-02_12-17-08 by buildguy) : recreate_standing_actions_swap_trigger: preparing transformation execution failed
2015/05/15 16:13:13 - Spoon - ERROR (version 5.3.0.0-213, build 1 from 2015-02-02_12-17-08 by buildguy) : org.pentaho.di.core.exception.KettleException:
2015/05/15 16:13:13 - Spoon - We failed to initialize at least one step.  Execution can not begin!
2015/05/15 16:13:13 - Spoon -
2015/05/15 16:13:13 - Spoon -
2015/05/15 16:13:13 - Spoon -   at org.pentaho.di.trans.Trans.prepareExecution(Trans.java:1149)
2015/05/15 16:13:13 - Spoon -   at org.pentaho.di.ui.spoon.trans.TransGraph$27.run(TransGraph.java:3989)
2015/05/15 16:13:13 - Spoon -   at java.lang.Thread.run(Thread.java:695)
2015/05/15 16:13:13 - recreate_standing_actions_swap_trigger - ERROR (version 5.3.0.0-213, build 1 from 2015-02-02_12-17-08 by buildguy) : Errors detected!
2015/05/15 16:13:13 - recreate_standing_actions_swap_trigger - ERROR (version 5.3.0.0-213, build 1 from 2015-02-02_12-17-08 by buildguy) : Errors detected!

You can omit the DELIMITER commands and enable the option "Send SQL as single statement":

CREATE FUNCTION Tes()
BEGIN
  SELECT * FROM actors;
END


SQL Server - How to achieve READCOMMITED and NOLOCK at the same time? If I have a User table:

id | name   | age
1  | Mateus | 27

The first transaction executes an update, and leaves the transaction open, without committing or rolling back:
update User set name = 'John' where id = 1;

Meanwhile, the second transaction executes a select:
select * from User where id = 1;
This command will wait until the first transaction releases the lock, either by commit or rollback, unless the second transaction uses a table hint with(nolock), like so:
select * from User with(nolock) where id = 1;
That will return the record without locking the transaction, but it will return the uncommitted value John instead of the original Mateus.

From what I know, there are only two ways to return a locked record without locking the current transaction, one can use with(nolock) that will return the record but with the uncommitted value, and with(readpast) that will just not return the record.

Is there a way I can return the record, without locking the table, and returning its "old" values?

What you're looking for is an optimistic isolation level, like Snapshot Isolation, or Read Committed Snapshot Isolation.

Code example:

USE Crap;

CREATE TABLE dbo.users (id INT, username NVARCHAR(40));

INSERT dbo.users ( id, username )
VALUES ( 1, N'Jimbo' )

/*To turn on Snapshot*/
ALTER DATABASE Crap SET ALLOW_SNAPSHOT_ISOLATION ON;

/*To turn on RCSI*/
ALTER DATABASE Crap SET READ_COMMITTED_SNAPSHOT ON;

UPDATE dbo.users
SET username = 'Dimbo'
WHERE id = 1;

/*Snapshot needs this, RCSI doesn't*/
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
SELECT *
FROM dbo.users AS u
WHERE u.id = 1;

Things to be aware of:

Row versioning uses space in tempdb (except SQL Server 2019 when Accelerated Database Recovery is configured - versions are stored with the user database, either in-row or in the Persisted Version Store)
There can be race conditions where you depend on locking for queueing

Differences

One important difference between Snapshot Isolation and RCSI is inside transactions:

Under Snapshot Isolation, BEGIN TRAN marks the point when all queries inside the transaction will read from the version store.
Under RCSI, each statement after BEGIN TRAN will read the version store as of when the statement executes.

Another difference is that Snapshot Isolation can be applied to modification queries, where RCSI can't. More precisely, SI detects write conflicts and rolls one of the conflicting transactions back automatically. Updates under RCSI do not use row versions when locating data to update, but this only applies to the target table. Other tables in the same delete or update statement, including additional references to the target table, will continue to use row versions.


How to identify all queries in a pending transaction in Oracle? Let's say I have a pending transaction that shows in select * from v$transaction. Is there a way to identify all queries executed so far in the pending transaction?

I'm pretty sure you can only view the most recent SQL of the transaction. Oracle records transactions at the block level, not at SQL level, so this information is not retained.

To see the most recent SQL, join the SES_ADDR column of v$transaction with the SADDR column of v$session. e.g.

SELECT a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk, b.status
  FROM v$session a, v$transaction b
  WHERE a.saddr = b.ses_addr
  order by b.used_urec;

You can then join the sql_id from v$session with v$sql to see the most recent SQL for the session. e.g.

select sql_id,sql_text from v$sql where sql_id in
    (select sql_id from v$session where sid=&sid);

You'd need to join the sessionid with the dba_hist_active_sess_history table to see more SQL executed by the session. Even then it will not neccessarily be a complete list, as this view works by periodic polling.

You could use tracing or auditing if you wanted a list of all SQLs for specific sessions, but again this would not be recorded at the transaction level. Perhaps you could compare this result with the start_date or start_scn from v$transaction to figure out which SQLs belonged to which transaction.


How to make sure that simultaneous INSERT and SELECT on one table won't make a deadlock? I have the following table in my SQL Server 2008 database:

CREATE TABLE [dbo].[SomeTable]
(
   [Id] [bigint] IDENTITY(1,1) NOT NULL,
   [Column1] [varchar](30) NOT NULL,
   [Column2] [varchar](30) NOT NULL,
   [Column3]  [varchar](50) NOT NULL,
   CONSTRAINT [PK_SomeTable] PRIMARY KEY CLUSTERED
   (
  [Id] ASC
   )
 )

One process inserts data into this table, another process reads all data from this table. Both processes work simultaneously.

one process:
INSERT INTO dbo.SomeTable VALUES ('col1', 'col2', 'col3')

another process:
SELECT Id, Column1, Column2, Column3 FROM dbo.SomeTable

I see that second query does a full table scan, but I need to get all data from this table for further processing(it won't be a very big table, because it will be cleaned periodically. it will contain a 1K-2K rows).

Are there any way to make sure that deadlocks will never happen for this two queries?

Deadlocks will always happen at some point under the default locking strategy. However, it is unlikely in your given scenario because of the straight table scan.

However, it's more likely if you have several concurrent INSERTs and SELECTs.

Using NOLOCK means dirty reads and isn't best practice. Everyone seems to suggest them though...

The alternative is to use snapshot isolation modes: the SELECT will read the last committed data, rather than being blocked by the INSERT.


Get the nth distinct column value ordered by another column I want to get the row with the nth distinct value in a column, as ordered by another column.

In more real terms, I want the 1000th distinct email_address as ordered by created_at, and the email_address column does not have a unique constraint.

I believe I have done this correctly with the following query:

SELECT `email_address`
    FROM footable
    GROUP BY `email_address`
    ORDER BY `created_at` ASC
    LIMIT 1
    OFFSET 1000

But I am not totally confident that is giving me the right value.

Is the above query correct for this purpose? If not, how should I solve this?

You need two changes:

a major one: Using an non-aggregated column in a GROUP BY query will yield unpredictable result - it's a pity that default setting s in MySQL allow this type of query, and good that it is corrected in 5.7 version.
The problem with your query is that an email address can appear in many rows - with many different created_at values - and the question is: Which of these different values will MySQL use for the ORDER BY? The answer is any one of the them. It's usually the first it will find but "first" here can mean different things, depending on the execution plans and available indexes. So, the results will not be determinate and may change from one execution to another.
You can use MIN(created_at) instead for the ORDER BY and the issue  is solved.
a minor one: OFFSET x means "skip x rows". Replace 1000 with OFFSET 999:

SELECT email_address
    FROM footable
    GROUP BY email_address
    ORDER BY MIN(created_at) ASC
    LIMIT 1
    OFFSET 999 ;


ERP (Oracle) real time backup I work in a small company, that runs an ERP that holds about 600MB of data.  My task at hand is, to find a way to always have real time backup of the erp (Oracle 11g) that can be retrieved easily (so that the company keeps working).  Right now, the erp is hosted on one IBM Server (Windows 2008 R2), and we keep backups of the previous day.  The company that supports the erp, says "Go IBM Storage, it is awesome and we know how to do it", but my question is: is an IBM Storage device really safe? Sure, it has 2 controllers, 2 psus, disks in raid, but can i depend on it? if something happens on the device itself (not some disk, or a psu, or even the pc that will access it itself as there will be 2 of them), ain't the company out of business (without even the backups of the previous day?).  I found and proposed to the support company the Active Data Guard (Oracle software for data mirroring), but they don't use it at all (my guess is that it is cheaper than storage solution and they won't make much by selling just one pc and some software).  What would you suggest?

Fault-tolerant storage, a stand-by database (Data Guard in your case), and the database backup are three different solutions to different types of problems. Depending on how much downtime you can afford, you may need to implement all three.


sql server 2014 - Find gaps in sequence on 2 different fields group by third field I need to detect the gaps between the sequences for each different seller considering that sequence is in 2 different rows considering only supplied range (avoiding numbers below the first record and numbers above the last record):

I have a table with orders where relevant fields are:

    SellerID | OrderID |   OID   |
    seller1  | 123456  |         |
    seller1  | 123457  |         |
    seller1  | 123458  | 123460  |
    seller1  | 123459  | 123460  |
    seller2  | 234567  |         | (first record)
    seller1  | 123455  |         |
    seller2  | 234568  | 234570  |
    seller2  | 234569  | 234570  |
    seller1  | 123463  |         |
    seller1  | 123466  |         |
    seller1  | 123453  |         | (first record)
    seller2  | 234572  |         | (last record)
    seller1  | 123470  |         | (last record)

I expect a result like this:

    seller 1 | 123454
    seller 1 | 123461
    seller 1 | 123462
    seller 1 | 123464
    seller 1 | 123465
    seller 1 | 123467
    seller 1 | 123468
    seller 1 | 123469
    seller 2 | 234571

it is quite easy also for me to find gaps in a single row and for single seller:
I used this query that I repeat for every seller:

Declare @SellerID nvarchar(50)='seller1'
SELECT s1.OrderID
FROM Orders s1
LEFT JOIN Orders s2
ON s1.OrderID = s2.OrderID -1
WHERE s2.OrderID IS NULL
and SellerID=@SellerID
order by s1.OrderID asc

but I do not know how to modify in way to check for the sequence in the 2 columns

moreover, is there a way to achieve this in a single query instead of repeating for each seller?

You can use CROSS APPLY to "unpivot" multiple columns. This query will give you a list of orders (unpivoted). I've added a third column, which is the LEADing (i.e. next) OrderID, if there is any:

SELECT o.SellerID, x.OrderID,
       LEAD(x.OrderID, 1) OVER (
           PARTITION BY o.SellerID
           ORDER BY x.OrderID) AS _nextOrderID
FROM Orders AS o
CROSS APPLY (
    SELECT o.OrderID
    UNION ALL
    SELECT o.OID AS OrderID WHERE o.OID IS NOT NULL
    ) AS x(OrderID);

This gives you:

SellerID   OrderID     _nextOrderID
---------- ----------- ------------
seller1    123453      123455
seller1    123455      123456
seller1    123456      123457
seller1    123457      123458
seller1    123458      123459
seller1    123459      123460
seller1    123460      123460
seller1    123460      123463
seller1    123463      123466
seller1    123466      123470
seller1    123470      NULL
seller2    234567      234568
seller2    234568      234569
seller2    234569      234570
seller2    234570      234570
seller2    234570      234572
seller2    234572      NULL

Now, if we subtract the OrderID from the _nextOrderID, we get the gap (how many orders are "missing"):

SELECT o.SellerID, x.OrderID,
       LEAD(x.OrderID, 1) OVER (
           PARTITION BY o.SellerID
           ORDER BY x.OrderID)-x.OrderID AS _gap
FROM Orders AS o
CROSS APPLY (
    SELECT o.OrderID
    UNION ALL
    SELECT o.OID AS OrderID WHERE o.OID IS NOT NULL
    ) AS x(OrderID);

.. which looks something like this:

SellerID   OrderID     _gap
---------- ----------- -----------
seller1    123453      2
seller1    123455      1
seller1    123456      1
seller1    123457      1
seller1    123458      1
seller1    123459      1
seller1    123460      0    <- 0 because you have two OrderID=123460
seller1    123460      3
seller1    123463      3
seller1    123466      4
seller1    123470      NULL
seller2    234567      1
seller2    234568      1
seller2    234569      1
seller2    234570      0
seller2    234570      2
seller2    234572      NULL

So where _gap<=1, we don't want to return any rows. Where _gap=4, we want to insert 3 rows, from OrderID+1 to OrderID+3. There are a few ways to do this, but I'm going to stick with CROSS APPLY here as well.

I'm putting the query above in a subquery (called sub in my example), and for each row in that result, I'm going to CROSS APPLY any dummy table. If you expect large gaps, you may want to create a separate table with a single IDENTITY column for this purpose, but I'm just going to use the Orders table along with a ROW_NUMBER():

SELECT sub.SellerID, sub.OrderID+n.rownum AS OrderID
FROM (
    SELECT o.SellerID, x.OrderID,
           LEAD(x.OrderID, 1) OVER (
               PARTITION BY o.SellerID
               ORDER BY x.OrderID)-x.OrderID AS _gap
    FROM Orders AS o
    CROSS APPLY (
        SELECT o.OrderID
        UNION ALL
        SELECT o.OID AS OrderID WHERE o.OID IS NOT NULL
        ) AS x(OrderID)
    ) AS sub
CROSS APPLY (
    --- For each row in sub, where _gap>1, return
    --- (_gap) number of rows, starting with 1, 2, 3, ..., (_gap-1).
    SELECT TOP (sub._gap-1)
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rownum
    FROM Orders    -- or any dummy table
    ) AS n
--- Only where there's actually a gap:
WHERE sub._gap>1;

Finally, at the top, we add n.rownum to sub.OrderID to get the missing OrderID.

Here's the final output:

SellerID   OrderID
---------- --------------------
seller1    123454
seller1    123461
seller1    123462
seller1    123464
seller1    123465
seller1    123467
seller1    123468
seller1    123469
seller2    234571


How to filter a value in json field type on Postgres 9.2? My json field data is like this:

{"active":true,"id":"xxxxxxxxxxxxxxxxx","settings":{"secret":"xxxxxxxxxxxxxxxxxxxxxxxxxx","token":"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx","expires":"2019-12-16 01:11:23"},"plan":"Sample"}

Then I tried to query the field like this:

select * from integrations.accounts where field -> 'id' = 'xxxxxx';

But it gives me an error of:

  SQL Error [42883]: ERROR: operator does not exist: json -> unknown

I found that the arrow operator (->) is not supported in version 9.2:

  Unsupported versions: 9.3 / 9.2

Is there any alternative way to do this?

You would need the ->> operator instead:

select * from integrations.accounts where field ->> 'id' = 'xxxxxx';

But these operators were added with Postgres 9.3. Postgres 9.2 is just too old (and unsupported by now). It had barely more than the data type json itself. There seems to be a third-party extension "Json Enhancements for PostgreSQL 9.2":

https://clarkdave.net/2013/06/adding-json-enhancements-to-postgresql-9-2/
https://www.pgxn.org/dist/json_enhancements/doc/json_enhancements.html#Json.Enhancements.for.PostgreSQL.9.2

But I have never tried it and would much rather upgrade to a current, supported version of Postgres instead. See:

https://www.postgresql.org/support/versioning/


Oracle - Why create several service_name in service_names parameter? SERVICE_NAMES specifies one or more names by which clients can connect
  to the instance. The instance registers its service names with the
  listener. When a client requests a service, the listener determines
  which instances offer the requested service and routes the client to
  the appropriate instance.

What is the reason for I modify the parameter service_names like this

Service_names = 'service_one','service_two'

Rather than

Service_names = 'service_one'

Because in the same database you have multiple applications, multiple workloads,... and services help to:
 - identify sessions when troubleshooting performance issue
 - manage resources for those with higher or lower priority
 - balance between several nodes
 - offload some read-only workloads to a standby database
...


Detect if the family record is incomplete to avoid partial deletion MS SQL SERVER and ORACLE.

I have a table Acct table that has acct_id and parent_acct_id attribute.
Parent_acct_id on children account points to acct_id on parent account. If no parent, then parent_acct_id = null.
Family only has two levels, no grandparent/grandchildren.

Now I want to delete some accounts. It has a temp(acct_id, save) table to store the data that need to be deleted.

I want to avoid partial deletion so I need a query to detect if the temp table has the entire family or just a part of family.

For example,

I already have a query to populate 'yes' value from parent to children or from children to parent. I only need a query to mark children as 'yes' if parent doesn't exist or mark parent as 'yes' when missing children.

I only came up with two separate queries but I want to optimize to only use 1 query.

--parent in the temp table but children are not
UPDATE MAIN SET save = 'yes'
FROM  temp_table MAIN
WHERE save IS NULL AND EXISTS
    (SELECT 1
    FROM acct
    WHERE acct.parent_acct_id= MAIN.acct_id
    AND NOT EXISTS
        (SELECT 1 FROM temp_table temp WHERE acct.acct_id = temp.acct_id))

--children are in the temp table but parent is not
UPDATE MAIN SET SAVE =   'yes'
FROM temp_table MAIN
WHERE SAVE IS NULL AND EXISTS
    (SELECT 1
    FROM acct
    WHERE MAIN.acct_ID = acct.acct_ID
    AND acct.parent_acct_id IS NOT NULL
    AND NOT EXISTS
        (SELECT 1 FROM temp_table temp WHERE acct.parent_acct_id = temp.acct_id))

Any thoughts appreciated!

Why further optimize two separate queries into one query? It only makes the query less readable, and more difficult to understand, or to make changes when this  is needed (in the future):

UPDATE MAIN SET save = 'yes'
FROM  temp_table MAIN
WHERE save IS NULL AND EXISTS
    (SELECT 1
    FROM acct
    WHERE acct.parent_acct_id= MAIN.acct_id
    AND ( NOT EXISTS
        (SELECT 1 FROM temp_table temp WHERE acct.acct_id = temp.acct_id)
    ) OR EXISTS
    (SELECT 1
    FROM acct
    WHERE MAIN.acct_ID = acct.acct_ID
    AND acct.parent_acct_id IS NOT NULL
    AND NOT EXISTS
        (SELECT 1 FROM temp_table temp WHERE acct.parent_acct_id = temp.acct_id)
    ))


pg_dump 9.2.x command does not work with pg_dump 9.2.3 This is how i used to create all the time backups of my databases with PostgreSQL 9.2.x:

pg_dump -Fc -Z 6 -U postgres mydatabase > 2013-xx-xx_xxxxx.db

However on a machine where PostgreSQL with version 9.2.3 is running I get all the time the error "too many command line arguments" starting with "-Z". If I remove -Z he is complaining about "-U"?
What is wrong?

Okay there is definitely sth. wrong. I removed argument for argument until i just started pg_dump. Then i added argument by argument and it worked. So I opened another command line window and tried the same: First "too many arguments", after calling only "pg_dump" and adding further arguments it worked...

I'd venture that it has nothing to with pg_dump version but that it may be an invisible spurious character on the command line that is causing this mess. As a demo, if I type this in cmd.exe, it works as expected at first:

  pg_dump -U postgres postgres >out.db

But now if I come back on this command with Arrow-Up, position the caret just before the -U option, add a non-breaking space by hitting Alt+255 on the numeric keypad, and validate this command, it yields the error you mention:

  pg_dump: too many command-line arguments (first is "-U") Try "pg_dump
  --help" for more information.


Error using SQL Server 2019 Import and Export Data (64-bit) I'm getting an error immediately upon starting the SQL Server 2019 Import and Export (64-bit) wizard, and I'm looking for ideas.  The error is:
The SSIS Data Flow Task could not be created. Verify that the DTSPipeline.dll is available and registered. The wizard cannot continue and it will terminate.

Additional information:

Cannot create a task from XML for task "", type ", {D9DA2996-AD06-4166-9C0C-C53E29834AF2}" due to error 0xC0000002 "".
 ({50E060E2-93A0-4F72-9DB6-F692E561312A})

A screen shot of the error is below.
I have tried everything I can think of or find online, including:

register DTSPipeline.dll and DTS.dll with regsvr32 from the 150 folder
install latest CU of SQL Server 2019
repair SQL Server 2019 using the installer
uninstall everything that mentions SQL Server from the control panel
re-install SQL Server 2019 after uninstalling everything
try again prior to installing latest CU
try again after installing latest CU after re-install of SQL Server 2019
try with Run As Administrator
re-install Microsoft.DataTools.IntegrationServices.exe
install latest SSMS

Nothing changes it. Even after totally removing SQL Server 2019 and re-installing.
I can't think of what else to try.

SSIS is installed

Hmm interesting it's calling out SSIS. This is just going out in a limb, but can you use the SQL installer to ensure SSIS is installed and then retry: https://docs.microsoft.com/en-us/sql/integration-services/install-windows/install-integration-services?view=sql-server-ver15
Hopefully that fixes the underlying issue.


Postgresql running total I have a bunch on time entries, they have entre_dates and hours associated with them like so.

drop table if exists data  cascade;
create table time_table (id int, entry_date date, hours numeric);
insert into time_table
values
(1, to_date('01-01-2019', 'dd-mm-yyyy'), 1),
(2, to_date('01-02-2019', 'dd-mm-yyyy'), 2),
(3, to_date('01-03-2019', 'dd-mm-yyyy'), 3),
(4, to_date('01-04-2019', 'dd-mm-yyyy'), 4),
(5, to_date('01-05-2019', 'dd-mm-yyyy'), 5),
(6, to_date('01-06-2019', 'dd-mm-yyyy'), 6);

For each time entry, I would like to sum all the hours that occur on and before that entry date to get a running total.

Here's my attempt but I have no idea what I'm doing:

SELECT t1.entry_date, t1.hours, htd.running_total
FROM time_table t1
INNER JOIN (
    SELECT SUM(t2.hours) AS running_total
    FROM time_table t2
    WHERE t2.entry_date <= t1.entry_date
) AS htd ON t1.id = t2.id;

I'm trying to get the following result:

Date       | Hours | Running Total
-----------------------------------
01-01-2019 | 1     | 1
01-02-2019 | 2     | 3
01-03-2019 | 3     | 6
01-04-2019 | 4     | 10
01-05-2019 | 5     | 15
01-06-2019 | 6     | 21

What's the correct way to do this query?

Try this

    drop table if exists data  cascade;
    create table time_table (id int, entry_date date, hours numeric);
    insert into time_table
    values
    (1, to_date('01-01-2019', 'dd-mm-yyyy'), 1),
    (2, to_date('01-02-2019', 'dd-mm-yyyy'), 2),
    (3, to_date('01-03-2019', 'dd-mm-yyyy'), 3),
    (4, to_date('01-04-2019', 'dd-mm-yyyy'), 4),
    (5, to_date('01-05-2019', 'dd-mm-yyyy'), 5),
    (6, to_date('01-06-2019', 'dd-mm-yyyy'), 6);

    select *,
       sum(hours) over(order by entry_date asc rows between unbounded preceding
     and current row) as running_total
     from time_table

db fiddle here: https://www.db-fiddle.com/f/ojsazNcvgfaX66CzdRDRXm/1


SQL Server - How to update two tables linked by foreign key with FROM statement? I am a complete SQL novice and need some help on some basic syntax on how to perform an update on two tables which are linked by foreign keys.

Here is an example:

User

+-----+--------+----------+
| Id  | Active | Username |
+-----+--------+----------+
| 100 |      1 | bobby    |
+-----+--------+----------+

Orders

+-----+--------+-------------+--------+
| Id  | Active | OrderNumber | UserId |
+-----+--------+-------------+--------+
| 200 |      1 |         123 |    100 |
| 201 |     1  |         789 |    100 |
+-----+--------+-------------+--------+

Let's say I have a user which is used in another table as the foreign key UserId.  I want to write UPDATE statements that will set the user to inactive and any associated orders using that user id to inactive as well.

Here is what I have tried so far:

-- sets the User to inactive
UPDATE User SET Active = 0
WHERE <some complicated check>

-- this is where I have problems
-- how do I set the associated orders for that particular user to inactive as well?
-- I have tried...
UPDATE Orders SET Active = 0
FROM User u, Orders o
WHERE o.UserId = u.Id

But that is clearly not quite right. I can't just use the user ids in the second UPDATE statement because I don't know what users my "complicated check" will return.

Any help would be greatly appreciated.

All you want to do is filter on the Active column of the User table being 0.  I'd also recommend using the join syntax, but that's not necessarily relevant to your question.

UPDATE Orders
SET Active = 0
FROM Orders o
JOIN User u on o.userID = u.ID
WHERE u.Active = 0
AND o.Active != 0