Estimated row size and estimated data size is incorrect I have a 9 million record table that is about 452MB in size (disregarding NC indexes). Below is physical stats and the columns

As a simple example, if I dump the table into a temp table, the actual plan shows a whopping 42GB estimated and actual data size. See image below.

I suspect this is due to the two varchar(4000) columns which is causing the estimated row size to be larger than they are. However, I really don't think 42GB are being transfered - not sure how that would be possible.. I don't know if I'm seeing performance issues because of this but the query plan looks scary because of these wrong estimates. Why is the query plan so wrong on the estimated and actual table scans?

SQL Server just uses a ballpark estimate for varchar that each value will on average be half the column capacity.

So each varchar(4000) will be estimated at 2,000 bytes. You have two of them so that explains the bulk of the 4,496 bytes estimate.

It seems that in reality you could reduce the maximum size of these columns.

This would reduce the estimated row size. Having an accurate estimated row size can help when there are memory consuming operators in the plan for getting an appropriately sized memory grant.


Downloading image data type contain to desktop SQL Server 2008 I am very new to SQL Server 2008 and question may look very basic to gurus.

Following in the problem universe.

I have a huge (600 GB size) SQL Server 2008 table that stores pdf files in a column of type Image
Need is to download all the images from database table to my desktop (not on to the server).
I can't run C#, ASP.NET etc in the environment and has to rely on SQL Server Management Studio for the job (I see a option called power shell but not sure how to use it)

Table looks like following

Table name : Attachments

Columns :

FileID (PK, int, not null)
Owner (int, null)
file_name (varchar(50), null)
file_type (varchar(50), null)
file_size (int, null)
file_date (datetime, null)
file_BIN (image, null)

This answer provides the following Simple Talk article with instruction and information on how to extract this data.  Unfortunately, there is no cut and dried method for natively converting this data because of the hurdles in storing this kind of data within a database table.

For more discussion on this practice and the issues surrounding it, I suggest you read this answer.


Mongo error : Could not lock auth data update lock. on root user i have a mongodb on 2.6.9.
my connected user is root:

mongo --username myadmin --password thepassword --port  27018 --host 127.0.0.1 admin
MongoDB shell version: 2.6.9
connecting to: 127.0.0.1:27018/admin
mongos> db.getUsers()
[
{
    "_id" : "admin.myadmin",
    "user" : "myadmin",
    "db" : "admin",
    "roles" : [
        {
            "role" : "root",
            "db" : "admin"
        },
        {
            "role" : "dbAdminAnyDatabase",
            "db" : "admin"
        },
        {
            "role" : "readAnyDatabase",
            "db" : "admin"
        },
        {
            "role" : "clusterAdmin",
            "db" : "admin"
        }
    ]
},

So i am root, but when i want update a user:

mongos> db = db.getSiblingDB('myuser')
myuser
mongos> db.updateUser(  'myuser' ,{ pwd: 'hg76vZa091', roles: ['dbOwner','dbAdmin','readWrite'] } )
2015-10-06T09:00:00.100+0000 Error: Updating user failed: Could not lock auth data update lock. at src/mongo/shell/db.js:1155

So i try to grant userAdmin to myadmin, and it's not working.

mongos> use admin
mongos> db.grantRolesToUser(     "myadmin",     [       { role: "userAdminAnyDatabase", db: "admin" }     ] )
2015-10-06T08:46:40.987+0000 Error: Could not lock auth data update lock. at src/mongo/shell/db.js:1287

Root user cannot grand user ? cannot update user ? How i can do it ?
Maybe i miss some query in my script. I try

use admin

or
    use root

before grant or update user, but it's not working. Any idea?

You are actually using root and admin database by your query
'use root' or 'use admin'

use root will not change your user it will just change your database.

So to add user or role .you need to login by root.As you have mentioned in very first line actually you are login as "myadmin " on database "admin"


Advantages of "middle" tables in one-to-many relationships Entities:

Lists
Leads

What I am trying to achieve is a scenerio where in the relationship between lists and leads the following constraint is applied: A list can have many leads, but a lead can only belong to one list.

Standard way of doing this is a simple non-identifying one-to-many relationship.

Another way I'm doing this is to create a hybrid one-to-one and a one-to-many relationship.

In both cases a list can contain many leads, but a lead can only exist in one list. Does anyone see any advantages or disadvantages to the second method? Any differences between the two methods? Any alternatives?

There are very few scenarios in which you would probably benefit from your second design:

One is if there is a sub-typing scenario where only certain a certain sub-type had the relationship (and the super-type doesn't) and the sub-type has other additional fields, besides the foreign key.

Another might be where the existence and value of the foreign key needs to be controlled for security reasons at a different level of granularity than the rest of the entity containing the foreign key.  Most DBMSs can handle security per table.  Most don't handle security per column.


cause of / how to fix a table with excessive reserved pages I have several tables with clustered indexes in azure PaaS sql server which have blown out to be much bigger than their data, with reserved page count several times bigger than the actual amount of data in the table.
These tables are insert only (no deletes or updates), although from time to time rows are inserted, which then fail insert due to PK violation.

is this an issue? - azure is reporting that all the 'reserved' space in this database is actually in use (somewhat expected from what i understand about what reserved means)
is there a known cause for this blowout? - a 10gb table consuming 70gb of space is a bit excessive.
is there a way to clean this up?
is there a strategy i can employ to prevent this happening again?

an example

partition_id    object_id   index_id    partition_number    in_row_data_page_count  in_row_used_page_count  in_row_reserved_page_count  lob_used_page_count lob_reserved_page_count row_overflow_used_page_count    row_overflow_reserved_page_count    used_page_count reserved_page_count row_count
72057594050904064   274100017   1   1   1082295 1086782 5527998 0   0   0   0   1086782 5527998 61559096

This is an object with known extreme fragmentation (>90%) however my personal expectation is that a bad layout on disk shouldn't cause huge amounts of wasted space, just excessive seek latency due to minimising contiguous reads.

Also, assuming that individual pages under random insert fill up, then split into two pages half full, then each page should oscillate between 50% and 100% (average 75%) resulting in an approx 25% storage overhead for a highly fragmented table/index.

Please examine the level of fragmentation of those indexes with below query. Index fragmentation could be the culprit.

SELECT
 DB_NAME() AS DBName
 ,OBJECT_NAME(ps.object_id) AS TableName
 ,i.name AS IndexName
 ,ips.index_type_desc
 ,ips.avg_fragmentation_in_percent
 FROM sys.dm_db_partition_stats ps
 INNER JOIN sys.indexes i
 ON ps.object_id = i.object_id
 AND ps.index_id = i.index_id
 CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), ps.object_id, ps.index_id, null, 'LIMITED') ips
 ORDER BY ps.object_id, ps.index_id

If you rebuild your indexes, you will reclaim that extra space. You will reduce the size of those indexes and the size of the overall database. Perform index maintenance with the following script:

DECLARE @TableName varchar(255)

 DECLARE TableCursor CURSOR FOR
 (
 SELECT '[' + IST.TABLE_SCHEMA + '].[' + IST.TABLE_NAME + ']' AS [TableName]
 FROM INFORMATION_SCHEMA.TABLES IST
 WHERE IST.TABLE_TYPE = 'BASE TABLE'
 )

 OPEN TableCursor
 FETCH NEXT FROM TableCursor INTO @TableName
 WHILE @@FETCH_STATUS = 0

 BEGIN
 PRINT('Rebuilding Indexes on ' + @TableName)
 Begin Try
 EXEC('ALTER INDEX ALL ON ' + @TableName + ' REBUILD with (ONLINE=ON)')
 End Try
 Begin Catch
 PRINT('Cannot do rebuild with Online=On option, taking table ' + @TableName+' down for douing rebuild')
 EXEC('ALTER INDEX ALL ON ' + @TableName + ' REBUILD')
 End Catch
 FETCH NEXT FROM TableCursor INTO @TableName
 END

 CLOSE TableCursor
 DEALLOCATE TableCursor

Create an Azure Runbook to defragment indexes regularly and avoid using more storage space.


Performing data update operations while backing up a large SQL Server database I have a large (in the tens of millions of records) database that I am going to perform a full database backup on.

However, the database is large enough that transactions can start before and during, as well as commit during and after the backup takes place.

For example:

T0 = Transaction A start
T1 = Full database backup start
T2 = Transaction B start (will not deadlock with A)
T3 = Transaction A commit/rollback (does not matter, does it?)
T4 = Full database backup end
T5 = Transaction B commit/rollback (again, does not matter, does it?)

T0          T1          T2          T3          T4          T6
||----------||----------||----------||----------||----------||---------->

My understanding is that no locks are used during a backup (although other performance problems may arise due to say, high I/O), but I'm not sure what I can guarantee what will be committed or not.

Also, my concern isn't that the database will be in an inconsistent state, but rather, what that state will be (even if it's not deterministic, if there's a set of rules that can be consistently applied) and how it got there (for example, how much of the data file is used along with the transaction log to create a backup file)?

Essentially, the backup will be of the state of the database when it finishes the data-reading portion of the backup (so all of the data will be backed up), plus whatever amount of transaction log is required to ensure transactional consistency (the start time of the included log is MIN(most recent checkpoint time, oldest active transaction start time)). Paul Randal covers this here (with aid of a diagram, which makes it all so much easier). In your example, A would be committed (or rolled back if a ROLLBACK TRANSACTION was issued instead of a COMMIT) and B would be rolled back (regardless of the end result of that transaction).

(The other reason you try and do backups at a quiet time, aside from I/O contention, is that all of the transaction log generated during a backup normally has to be included with the backup.)

The recovery phase of a database restore takes all the committed transactions from the log included in the backup and applies them to the database, and rolls back all the un-committed transactions. (This is why WITH RECOVERY/WITH NORECOVERY is important. WITH RECOVERY and you can use the database, but you can't apply any further log backups, you need to restore it WITH NORECOVERY in order to roll in log backups. Recovery breaks the log chain by rolling back uncommitted transactions.)

Further reading:

30 SQL Server backup myths
Debunking a couple of myths around full backups
Logging and recovery in SQL Server


Repartition PostgreSQL tables without losing data I already have my table partitioned as follows:

CREATE TABLE customer
(
  c_custkey integer NOT NULL,
  c_name character varying(25) NOT NULL,
  c_address character varying(40) NOT NULL,
  c_city character(10) NOT NULL,
  c_nation character(15) NOT NULL,
  c_region character(15) NOT NULL,
  c_phone character(15) NOT NULL,
  c_mktsegment character(10)
) PARTITION BY List(c_region) ;

create table customer_1 PARTITION of customer for values in ('EUROPE');
create table customer_2 PARTITION of customer for values in ('AFRICA');
create table customer_3 PARTITION of customer for values in ('AMERICA');
create table customer_4 PARTITION of customer for values in ('MIDDLE EAST');
create table customer_5 PARTITION of customer for values in ('ASIA');

My partitions are filled with data. I want to repartition my table without losing data. How to do that with PostgreSQL?

Essentially you create a new partitioned table and fill it with the data from the old one, for example with INSERT INTO ... SELECT. Then you can drop the old table and rename the new one. That requires downtime.

If you mind downtime, create a new database from a schema dump of the old one, except that your partitioned table is now partitioned differently. Then set up logical replication between the two databases and switch over as soon as the replica has caught up.


MongoDB - Does WiredTiger benefit from virtual cpus cores the same way it does with physical cores? According to https://docs.mongodb.com/manual/administration/production-notes/#id5 The WiredTiger storage engine is multithreaded and can take advantage of additional CPU cores.

We will be running mongoDB in a VMware vSphere enviroment and I am tasked with allocating resources. Does that statement hold true when it comes to virtual CPU cores?

Thanks!

MongoDB - Does WiredTiger benefit from virtual cpus cores the same way
  it does with physical cores?

As per my understanding the MongoDB documents defines based on the physical cores.

As blog notes by @UmaPanda here this topic is always a perpetual debate to define the proper ratio of physical CPU to virtual CPU in virtualized environment.

First you understand the basic calculation:

The number of physical cores i.e. pCPU available on a host:
(# of processor Sockets) X (# of Cores) = # of Physical Processor (i.e pCPU)
The number of logical cores if hyper-threading is enabled on the host:
(# of Physical Processors i.e pCPU) x (2 cores) = # Virtual Processors (vCPU)
Total CPU resorces required for virtual machines at peak:
(# total number of virtual machines X average peak CPU utilization per system)

kindly note that hyper-threading does  not actually double the available of Physical CPU. It works by providing a second execution thread to a processor core. So, a processor with 4 physical cores with hyper threading will appear as 8 logical cores for scheduling purpose only.

  The WiredTiger storage engine is multithreaded and can take advantage
  of additional CPU cores.

The MongoDB document also states that here Specifically, the total number of active threads (i.e. concurrent operations) relative to the number of available CPUs can impact performance:

Throughput increases as the number of concurrent active operations
increases up to the number of CPUs.
Throughput decreases as the number of concurrent active operations
exceeds the number of CPUs by some threshold amount.

The threshold depends on your application. You can determine the optimum number of concurrent active operations for your application by experimenting and measuring throughput. The output from  mongostat provides statistics on the number of active reads/writes in the (ar|aw) column.

And also from MongoDB blog notes here MongoDB will deliver better performance on faster CPUs. So, one of the MongoDB performance best practices is that the core speed is more important than the number of cores.

For further your ref here , here , here and here


Way to update many (>3000) records with unique data, using a character limited web form Okay, as you can probably tell from the last part of the title, this is a non-ideal situation.

I'm using a piece of industry-specific web-interface software that I guarantee no one has ever heard of, and while the functionality it offers to do mass data entry is meager, it does allow me to run SQL queries directly on its underlying MS SQL database through an 8,000 character-limited web form. That's the most direct access I have to the database.

I need to update a particular field in about 3,700 records with unique data for each. As per this answer, I've been doing the following:

UPDATE guest
  SET info4 = CASE guestid
    WHEN 12345 THEN 'BNID: 917546553'
    WHEN 67890 THEN 'BNID: B04695465'
    [etc, etc.]
    ELSE info4
  END

(The info4 field is currently empty for all records.) I can fit about 200 cases into one query before hitting the character limit and having to run a new update with the next chunk of data.

Things I have discovered so far:

Not including the ELSE statement at the end will empty the field on all records that don't fulfill a case.
Before committing, I get "Rows affected: [# of rows in the table]" instead of the number of rows that will actually get new data.

I understand I should be putting a WHERE guestid IN (12345, 67890) at the end to avoid running the update on every row, but that's going to be hellish to avoid running over my character limit with each chunk of update statement.

As it happens, I am currently taking my source data from an Excel spreadsheet, pasting it all into Notepad++, running find/replaces on it to insert the necessary SQL syntax, and then chunking it up as per the character limit.

So, in a nutshell, my question is: have I explored this to the extent of what is possible already? Is there something big and obvious I'm missing, or should I be going about this in a completely different way? Bonus points for fewer characters per case!

The SET can be made a bit simpler:

SET info4 = 'BNID: ' +
  CASE guestid
    WHEN 12345 THEN '917546553'
    WHEN 67890 THEN 'B04695465'
      [etc, etc.]
                          --- no ELSE here
  END

WHERE info4 IS NULL       --- and this is needed

Saves 6 characters per case.

If you can create a table (even a temporary one) you could also use:

--- run once
CREATE TABLE #tmp
( guestid INT NOT NULL
, i4 VARCHAR(200)
, PRIMARY KEY (guestid)
) ;

--- as many times as needed
INSERT INTO #tmp VALUES
(12345,'917546553'),
...
(67890,'B04695465');

and then:

UPDATE guest
  SET info4 = 'BNID: ' + i4
FROM guest
  JOIN #tmp
    ON #tmp.guestid = guest.guestid
WHERE guest.info4 IS NULL


Postgres performance testing, caching and flushing I been doing some testing on various postgres SQL queries.

Testing often involves changing the syntax of queries, altering table joins, or occasional completely re-writing the query.

I've noticed that I sometimes get an 'apparent' big performance increase. I'll run a query, it'll take (say) 60 seconds to run, I'll make a minor change and it'll then take (say) 5 seconds to run.

At first I though that this was because my minor tweak had improved the performance. I've since realised that actually there must be some caching going on, (to see this, try running a 60 second query, and then running it again a few seconds later - it will always run quicker the 2nd time), I assume this is because the data has been cached locally somewhere so when the data needs reading a 2nd time it's already to hand.

I'm sure this a useful performance feature, but it does make it very hard to spot genuine performance improvements when tweaking a query. Is it possible to flush the cache before each execution to ensure that each test starts from the same position?

Thanks

PostgreSQL relies heavily on the OS cache as well as its own cache.  So you would have to clear the PostgreSQL cache (restart the postgres service) and clear the OS cache (either restart the machine, or follow the method in the comments to clear without restarting if you are Linux).

But, why do you want to do it this way?  If the query is parameterized, you could just change the parameters each time so they refer to a different part of the data.  If the query is not parameterized, then why is the data it needs getting driven out of the cache between runs on the production machine?

When the different parameterizations have different performance due to different result sizes, the most definitive solution is to write a driver program that repeats the query several times with random (but realistic) parameterizations, and hopes that the parameter-induced variation averages out between your tuning settings.  This is a pain, but when I have had to resort to it I generally came to wish I had to done it sooner.  This is better than doing each query from a cold cache, because in the real production situation you are unlikely to have a fully cold cache.  Certain parts of the data will be in common to all parameterizations, and that part will be "hot" enough to always be in the cache.


Updating table schema with correct datatype in sqlite I know that importing from CSV into SQLite treats all fields as text. According to How to import csv file to sqlite with correct data types, one can use

UPDATE MyTable SET MyColumn = CAST(MyColumn AS INTEGER)

to update whole colum type.  That seems to work, however that doesn't seem to be reflected in the output of TYPEOF() nor in PRAGMA table_info(table_name)

sqlite> CREATE TABLE testtb (a TEXT, b TEXT);
sqlite> INSERT INTO testtb VALUES ('1', '2');
sqlite> UPDATE testtb SET b = CAST(b AS INTEGER);
sqlite> SELECT TYPEOF(b) FROM testtb;
text
sqlite> PRAGMA table_info(testtb);
0|a|TEXT|0||0
1|b|TEXT|0||0

So how can I make the changes actually show up  when querying table's meta information (I'm lacking a proper phrasing here, but hopefully this makes sense) ?

If you want really want to, just use your first table as a staging table before you go ahead and create the final table with correct datatypes & data:
sqlite> PRAGMA table_info(testtb);
0|a|TEXT|0||0
1|b|TEXT|0||0
sqlite> create table testtb2 (a INT, b INT);
sqlite> insert into testtb2 (a,b) select a,b from testtb ;
sqlite> select * from testtb2;
1|2
sqlite> PRAGMA table_info(testtb2);
0|a|INT|0||0
1|b|INT|0||0
sqlite>

At the end of the day you have to remember (to some extent) that sqlite doesn't actually care about datatypes.

SQLite is very flexible with regard to datatypes.
Some commentators say that SQLite is "weakly typed" and that other SQL
databases are "strongly typed". We consider these terms to be
inaccurate and purgative. We prefer to say that SQLite is "flexibly
typed" and that other SQL databases are "rigidly typed".
The key point is that SQLite is very forgiving of the type of data
that you put into the database. For example, if a column has a
datatype of "INTEGER" and the application inserts a text string into
that column, SQLite will first try to convert the text string into an
integer, just like every other SQL database engine. Thus, if one
inserts '1234' into an INTEGER column, that value is converted into an
integer 1234 and stored. But, if you insert a non-numeric string like
'wxyz' into an INTEGER column, unlike other SQL databases, SQLite does
not throw an error. Instead, SQLite stores the actual string value in
the column.


Offsetting reporting Load from one server to another I have a SQL Server 2008r2  database ( Let's call it "BIG_DB") at 200GB.  This server gets hit hard.  It is the back end for an application, BIG_DB is a  source of a lot of reporting.

There are about 10 tables that are being queried for reports constantly and I would like to offset that load to another server( Let's call it "OFFSET").  The Boss wants a maximum 2 minute latency between the   BIG_DB AND OFFSET on these  10 tables.  This is my proposed solution:

When an insert or update happens on any one of those 10 tables on BIG_DB, I write that to a corresponding table ("UPDATE_TABLE") with a similar name and same column list + default GETDATE() column on BIG_DB.
Then have OFFSET grab the info on BIG_DB.UPDATE_TABLE and place it into the OFFSET database with the same table setup as BIG_DB.
I then have OFFSET serve the reports.

I have only one geographic location, servers are only in house. BIG_DB is OLTP. Is there a better solution? Do I go with replication, SSIS, or straight T-SQL with a linked server? or other?

One issue with your trigger implementation is that if the reporting DB or DB Server goes down for any reason or stops accepting requests, your prod systems will stop as well.  Also if you make schema changes note that you'll need to do that on the reporting table as well.  Your solution will work, it just isn't ideal.  SSIS won't help with the schema changes but at least if the reporting server can't accept requests prod will still keep working.

Replication is nice too if you have a primary key on the table and meet all the replication requirements.  Just note that replication (IMO at least) requires more experience to fully understand than a pure AlwaysOn or SSIS solution.  Replication requires agents and extra security.

SSIS could work at the frequency you're using it for, maybe it just isn't ideal.  If you're OK with losing in flight transactions when the server reboots you can look at Service Broker which will be much more near real time but Service Broker has a even higher learning curve than AlwaysOn.

Personally I would stick with Replication or your current SSIS solution.  Both require some tuning and testing but both have their advantages as well.  SSIS at least won't deal with all the security and agents that replication requires.

Are you open to using 2014 standard edition read only replica in an AG?  It gives you a lot more options, let's you query the entire database, gives you a nice near real time complete copy, and isn't too difficult to setup but absolutely much more difficult to setup than a trigger.  Edit: Sorry I just saw that you tagged SQL 2008 R2.


Change Tracking cleanup Is there a way to know when cleanup of change tracking tables occurs? How to check the last occurrence of a cleanup process?

BACKGROUND:

I have Change Tracking on database with retention set to 1 minute:

ALTER DATABASE [test] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 1 MINUTES)

I do some updates in my test table and then wait a bit longer than the retention period (lets say 3 minutes). As the cleanup process is asynchronous, I do understand that there NO guarantee that Change Tracking tables will be cleaned up after 1 minute. It can happen at any time that is > 1 min.

I found this procedure to call the cleanup process manually:

EXEC sp_flush_commit_table_on_demand 100000

But it does not work as expected.. (Change Tracking rows are still in place after retention period)

I reproduced this using the code below, and essentially, yes, if you wait long enough, and auto cleanup is on, it will clean itself out. It appears to be bound the a minimum - that is, the change_retention setting is more of a suggestion than a command.

Admittedly, I haven't tested with real-world table activity, etc., but the links below contain more information about what the job does - it's more of a black box, really, than anything.

Links:

MS Change Tracking

Kendra Little on Change Tracking

Song Lyrics - Courtesy of Disney

Here's what I came up with.

use master;
go

drop database cttest;
go

create database cttest;
go

use cttest;
go

create table dbo.t
(id int identity(1,1) primary key--can't track without it
, c1 int
, c2 varchar(100));
go

-- pre ct
insert t values (123, 'Yo ho, yo ho')
insert t values (234, 'We pillage, we plunder, we rifle and loot.')
insert t values (345, 'Drink up me ''earties, yo ho.')
insert t values (456, 'We kidnap and ravage and don''t give a hoot.')
insert t values (567, 'Drink up me ''earties, yo ho.')
go

-- check the table
select * from t;
go

-- enable change tracking for this DB
alter database cttest set change_tracking = on (change_retention = 1 minutes)
go

alter table dbo.t enable change_tracking with (track_columns_updated=off);
go

-- this should be a tracked change
insert t values (678, 'Yo ho, yo ho, a pirate''s life for me.');
go

-- show the change tracking record
-- so i waited 10 minutes, and then...
select * from changetable(changes dbo.t, NULL) as ct;
go

-- is this table even tracked ?
SELECT object_name(object_id),* FROM sys.change_tracking_tables

-- make another change
update t set c2 = 'Yo ho, yo ho, a pirate''s life for me.' where c1 = 123;
go

-- show the change tracking record
select * from changetable(changes dbo.t, NULL) as ct;
go

-- make more changes
insert t values (789, 'We extort and pilfer, we filch and sack.')
insert t values (8910, 'Drink up me ''earties, yo ho.')
insert t values (91011, 'Maraud and embezzle and even highjack.')
insert t values (101112, 'Drink up me ''earties, yo ho.')
go

-- show the change tracking record
select * from changetable(changes dbo.t, NULL) as ct;
go

-- make another change
update t set c2 = 'Yo ho, yo ho, a pirate''s life for me.' where c1 = 123;
go 15

-- show the tracked items
select * from changetable(changes dbo.t, NULL) as ct;
go

-- force a <ahem> flush
EXEC sp_flush_commit_table_on_demand 100000;
go

/*
Received message
(0 row(s) affected)
The Change Tracking manual cleanup procedure for database cttest has been executed
*/

-- show the tracked items
-- still there !
select * from changetable(changes dbo.t, NULL) as ct;
go

-- ok then, we'll turn it off !
alter database cttest set change_tracking (auto_cleanup=off);
go

-- force a <ahem> flush
EXEC sp_flush_commit_table_on_demand 100000;
go

-- show the tracked items
-- still there !
select * from changetable(changes dbo.t, NULL) as ct;
go

-- ok then, we'll turn it back on, and wait
alter database cttest set change_tracking (change_retention = 1 minutes, auto_cleanup = on)
go


Moving Databases to New Datacenters My company is moving our infrastructure to new data center and I am trying to figure out the best way to keep the database on the new server in sync with the current production database until the new environments are ready to go live.  Not being a full-time DBA, I did some research and from what I have read, it looks like a transnational replication setup would best suite our needs.

Some details: the production DB is around 90 GBs in size and using Robocopy it took about 9 hours to move a copy of it to one of the new servers.  The current production database will need to stay online and accessible throughout the entire migration process.  It's in simple recovery, so database mirroring is unavailable.

Is a transactional replication the best method to keep the databases in sync?

My plan:

(Done) Transfer the current database and log to the new server and attach it to the new instance of SQL Server
Setup a distributor on our development database machine and publish to it from the production database
Create a subscriber on the new database machine which will accept updates that are pushed out from the distributor, once each night

There are two things on my mind.  Transactional replication requires that each published table have a primary key and a lot of the tables in the production database do not have primary keys defined.  I don't think this will be too big of an issue since my main concern is just getting the databases in sync.  We'll test the different applications that the use the database at a later data, but I'd like to make sure it's not a serious issue.  Secondly, do I need to also move any associated system DBs from the original instance, such as master?  We're moving to an Active Directory setup in the new environment, so I don't care about the users and such, but I am unsure about the necessity of the system DBs.

And just in general, am I grasping these concepts correctly?

Your current situation :

90 GB database that you want to move to a new data center.
Database is in simple recovery.
You are thinking of using T-Rep to keep the data in sync.
There are many tables in the database that do not have Primary Key - this is a requirement for any tables to get published.
You already have a backup copied to the destination data-center.

I see many drawbacks in your approach :

T-Rep is not easy to setup as compared to other technologies. If there is any schema changes then it requires a new snapshot.
If you are going with T-REP then you have to make schema changes - add Primary Key to tables that dont have.
Doing any change to your existing database, your application has to be fully tested to avoid any unexpected behavior.
If your database is having large number of transactions and depending on network bandwidth between the 2 datacenters, there will be replication latency as well.

Based on my experience, below is my recommendation :

Change your database to full recovery. This is not a major impact as compared to creating PKs.
Ship the full backup of the source database - take backup with compression and enable instant file initialization. This will help you to cut down the restore time on destination data-center.
Restore database on the destination server WITH NORECOVERY.
Implement Logshipping and initialize it from the backup.
Have logshipping ship the logs every 1 min.
During the day of failover, take a last tail log backup on source and restore it on the destination using WITH RECOVERY. This will bring the destination database online.
Once the destination database is online, you have to sync up users.
You should change your web.config to point it to the new server.

You do not have to move any system databases. Make sure you do all the prep work before hand - script out logins, jobs, ssis packages, etc and create them on the destination server.

Refer to this answer for post restore steps and other best practices.

Note: You can implement Database mirroring (SYNC or ASYNC depending on the edition of sql server you are using) as well, but logshipping is just simple to implement and if you test it, it wont disappoint you. I have moved successfully terabyte database from one datacenter to another using the above technique and it works perfectly fine.

  The current production database will need to stay online and accessible throughout the entire migration process.

There will always be a downtime and you have to schedule it. Even if you invest paying high amount of money in solutions like clustering, when you failover, there will be some downtime. You have to balance out how much your company can invest in having a near to zero downtime vs what is available with some acceptable downtime.


MySQL replication - slave update I have a master-slave setup of MySQL.  If I make any changes in the slave database...

Will it mess up the sync in any way ?
Will the changes get overwritten from the master during the next replication event ?
Can I make above (2) option happen ?

Answers to your questions are as below:

1) Will it mess up the sync in any way

Answer: Yes it will mess up your replication process as slave reads from relay log and updates on Server.
For example: If a row has been added to slave and which is not there in Master then "INSERT INTO ..." query will fail which can cause replication process being stopped.

2) Will the changes get overwritten from the master during the next replication event, Can I make above (2) option happen ?

Answer: NO. but there is a alternative you can use pt-table-sync tool

This tool changes data, so for maximum safety, you should back up your data before using it. When synchronizing a server that is a replication slave with the --replicate or --sync-to-master methods, it always makes the changes on the replication master, never the replication slave directly. This is in general the only safe way to bring a replica back in sync with its master;

Note: It is a good idea to set slaves as read_only
This variable may help prevent some unsafe writes from occurring on slaves, but only if users are not needlessly granted the SUPER privilege.


PostgreSQL strategies to deal with disk filling up I'm using PostgreSQL (8.4) to store data produced by an application making frequent inserts (in the table structure described below).

The database keeps growing with time and, since the newer data is more relevant than the older data (in this particular application), deleting the older rows is a reasonable solution (either based on lower id or older input_datetime, which is more or less the same).

To prevent issues related from this database (the only database running on this server) from affecting the rest of the system, I've put the PostgreSQL data directory on its own partition (ext3, on a Linux system). Nevertheless, when this partition becomes full, this causes a number of problems.

I'm thinking of deleting older data regularly (e.g. DELETE FROM data_group WHERE id <= ... via a cron job) to deal with this.

Firstly, my understanding of VACUUM (as performed by auto-vacuum, which is on) is that, while it doesn't necessarily give back the disk space to the OS (like VACUUM FULL would), it still allows some new data to be inserted within the disk space already used (that is, the DELETEs don't necessarily affect the file size, but they still free space in PostgreSQL's own data structures). Is this correct? (I've noticed VACUUM FULL caused a few problems with the application itself, probably because of the locks it uses.)

If so, it also appears that SELECT pg_database_size('my_database') reflects the size used on disk, which doesn't necessarily reflect what's available for further inserts. Is there another way to estimate how much space is available for new inserts?

In addition, when it's too late and the partition is filled at 100%, running this DELETE statement causes this error and crashes the PostgreSQL service:

  PANIC:  could not write to file "pg_xlog/xlogtemp.7810": No space left on device

The PostgreSQL daemon stopping is of course a major issue (and there is no other disk to move the cluster to on this machine).

Are there general strategies to prevent this sort of problem from occurring (knowing that disk space is constrained within a given partition, but that it can be acceptable to delete older data)? I would like to automate as much of this as possible, without root or postgres (or PostgreSQL admin) intervention.

CREATE TABLE data_group (
    id SERIAL PRIMARY KEY,
    name TEXT,
    input_datetime TIMESTAMPTZ
);

CREATE TABLE data_item (
    id SERIAL PRIMARY KEY,
    group_id INTEGER NOT NULL REFERENCES data_group(id) ON DELETE CASCADE ON UPDATE CASCADE,
    position INTEGER NOT NULL,
    data BYTEA
);

On one hand, you can have a look at one of my previous answers to see how you can keep a table size more or less steady.  There you will find a solution with triggers - of course, this can be solved using a cron job as well.  In the latter case I would first check if the row number exceeded a certain limit and the either delete the oldest rows or drop a partition.

On the other hand, as you already noticed, one has to take care of the disk space where pg_xlog is.  When it gets full, it is not that easy to recover...  But checking your database settings you can have a fair estimation how much space you need:

  There will always be at least one WAL segment file, and will normally
  not be more than (2 + checkpoint_completion_target) *
  checkpoint_segments + 1 or checkpoint_segments + wal_keep_segments + 1
  files. Each segment file is normally 16 MB (though this size can be
  altered when building the server). You can use this to estimate space
  requirements for WAL. Ordinarily, when old log segment files are no
  longer needed, they are recycled (renamed to become the next segments
  in the numbered sequence). If, due to a short-term peak of log output
  rate, there are more than 3 * checkpoint_segments + 1 segment files,
  the unneeded segment files will be deleted instead of recycled until
  the system gets back under this limit.

If you don't have replication set up, the maximum is 3 * checkpoint_segments + 1 (times 16 MB).  A typical replicationless setup will need something under 10 GB for pg_xlog, I think.


Review my logical model for gaps We need to centralize our ability to manage the various schedules that our banking centers keep since it is a manual nightmare right now.    Do you see any obvious gaps in this?

A schedule is defined by a set of Days of the Week, open times and close times.
A logical record may look like this:

service_location    service_location_type  day_of_week  open_time  close_time
Main Street Branch  Lobby                  Monday       8am        5pm
Main Street Branch  Lobby                  Tuesday      8am        5pm
Main Street Branch  Lobby                  Wednesday    8am        12pm

I want to be able to reuse Schedules but am worried something here is a a bit off.

Since your plan is to re-use schedules I would recommend a change in your thinking as follows:

a) Each service location can have one or more schedules - called location schedules (basically this is an association between a location, a predefined schedule and other data)

b) Each location schedule can have a priority (which allows you to create a custom schedule that overrides the default schedule)

c) Each location schedule has a start date and an end date when its active - which allows you to define a custom schedule which is active for only a specific period of time

d) A schedule has a name and description

e) A schedule has one or more schedule details - day of week, opening time, closing time

I have included an explanatory data model (MySQL)


Lost connection to MySQL server during query issue SELECT UNIX_TIMESTAMP(a.timestamp) AS timestamp, a.adid, a.adtitle, a.cityid, a.price,
a.area, a.addesc, a.subcatid, ct.cityname, p.picfile, scat.subcatname, cat.catid, cat.catname
FROM xzclf_ads a USE INDEX (id_index)
INNER JOIN xzclf_cities ct ON a.cityid = ct.cityid
INNER JOIN xzclf_subcats scat ON a.subcatid = scat.subcatid
INNER JOIN xzclf_cats cat ON scat.catid = cat.catid
LEFT OUTER JOIN xzclf_adxfields axf ON a.adid = axf.adid
LEFT OUTER JOIN xzclf_adpics p ON a.adid = p.adid AND p.isevent = '0'
LEFT OUTER JOIN xzclf_featured feat ON a.adid = feat.adid AND feat.adtype = 'A'
WHERE scat.catid = 10
AND a.enabled = '1'
AND a.verified = '1'
AND a.expireson >= NOW()
AND a.paid <> '0'
AND (feat.adid IS NULL OR feat.featuredtill < NOW())
GROUP BY a.adid
ORDER BY a.timestamp DESC
LIMIT 0, 20

It gives an error: Lost connection to MySQL server during query
Is anything wrong with this query?

There's a good chance the mysql crashed while trying to execute your query. First check the error log to see if it did. Common reason for mysql crash is memory exhaustion and tmp dir space depletion.


Given the functional dependencies of a relation is there more then one way to normalize it? I"m studying normalization and have been reading through descriptions and examples online. One Concept that isn't clear to me is if the process can be automated at any point. I understand that a system can't tell you if you schema makes any real-world sense, but it seems likely to me that if you feed a program some arguments say, the functional dependencies, it would be able to do some steps for you.

Say you have a relationship R(A,B,C,D)

and you tell your program that A,B->C and A->D and that the primary key is (A,B)

Now if my understanding serves me correctly it should be possible for a program to identify that A->D violates 2NF because D depends on A and A is a subset of the primary key. That the proper decomposition would be R1(A,B,C) and R2(A,D).

The main book i'm learning from and my instructor indicate these normalization's are done all by hand. Is this correct or can some parts be automated?

Normalization to Boyce-Codd Normal Form, 5NF or 6NF (and therefore to any "lesser" NFs) certainly can be automated. Google for Bernstein's alogrithm for a simple example of how.

There is frequently more than one way in which a set of dependencies can be normalized to any given Normal Form. Again, there are formal methods that can help you choose. Typically we want a database that is not just normalized but also dependency-preserving.


Is there a way to reference a column dynamically within a trigger? I asked a similar question a few months back and I'm improving upon my solution!

I have an Oracle database where I would like to trigger a procedure to do additional work on a column upon updating of another.

For example, say that if I mark an item on sale, I would like the database to automatically take 20% off the price.  So:

| ID | ITEM                     | PRICE | ONSALE | DISCOUNT1 | DISCOUNT2 |
|----|--------------------------|-------|--------|-----------|-----------|
| 1  | T-Shirt                  | $20   | F      | .15       | .3        |
| 2  | Sweatshirt               | $30   | F      | .25       | .5        |
| 3  | Jeans                    | $40   | F      | .1        | .4        |
| 4  | Fashionably Ripped Jeans | $120  | F      | .05       | .1        |

What if I wanted the trigger to, say, programmatically select DISCOUNT1 or DISCOUNT2, how would I do this?

:NEW.PRICE := :NEW.PRICE -
    (:NEW.PRICE * :NEW.DISCOUNT(do tricky math to get a random number));

I was not able to solve this issue in the method I had wanted.  I used an if statement as suggested by Serg.

if ((:NEW.ONSALE = 'T') AND (:OLD.ONSALE = 'F')) then
    :NEW.PRICE := :OLD.PRICE - :OLD.PRICE * :NEW.DISCOUNT1;
elsif ((:NEW.ONSALE = 'T') and (:OLD.ONSALE = 'T')) then
    :NEW.PRICE := :OLD.PRICE - :OLD.PRICE * :NEW.DISCOUNT2;
end if;

This was a very simplified example but I couldn't figure out a better way to do this.


Does MSSQL Server need more space than the size of the data itself for importing? How much more? Does MSSQL Server need more space than the size of the data itself for importing? How much more? How to tell?

Recently tried copying data into MSSQL server (2012)

Microsoft SQL Server Management Studio          11.0.3156.0
Microsoft Analysis Services Client Tools        11.0.3130.0
Microsoft Data Access Components (MDAC)         6.2.9200.16384
Microsoft MSXML                                 3.0 5.0 6.0
Microsoft Internet Explorer                     9.10.9200.22387
Microsoft .NET Framework                        4.0.30319.18449
Operating System                                6.2.9200

using BCP and was seeing errors like

  Error = [Microsoft][ODBC Driver17 for SQL Server][SQL Server]The transaction log for database 'EpicShd' is full due to 'CHECKPOINT'.

and

  Error = [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The transaction log for database 'EpicShd' is full due to 'ACTIVE_TRANSACTION'.

When looking at the DB itself, it threw...

  Database cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details. (Microsoft SQL Server, Error: 945)

For context, the total size of the data (at least the TSVs that BCP was copying in) was ~10G, while the total storage space on the DB server was 28G, so I would think there would not be a problem. I am bringing in multiple tables each being 500MB-1GB and doing it in set of 1-2 at a time.

Does copying data into MSSQL server take more space than the data itself? How much (temporary storage)? How can I tell (not much experience with DBA)?

The error messages are indicating that the log file is full. SQL Server has two files (at least) for every database; one is the data file and one is the log file. On your particular server, it is possible that the log file is on a disk that doesn't have enough space for the log file while the data file may be on a disk where it has plenty of space.
If the database is in Simple recovery mode, simply execute a full backup and the log file will clear. However, you may need to find out where the log file is located and make sure the disk has enough space to house the log file while performing the BCP. You can do this by right-clicking on the database and going to Properties. Then click on Files on the left side. This will show you the physical path for the log file (.ldf).


I just exited SSMS while restoring a UAT database, am I screwed? I just flinched and closed out of habit while waiting for the restore to reach 100%. Did I just break everything? Is it possible to view the progress of the restore?

Update:  I was in a bit of a panic, so I roped in my team lead. The database was now stuck in the restoring state and that script didn't work so we just dropped it and started over. It went okay.

Was that necessary? In the UI for a restore there is a period of time before the percentage complete is displayed. I had closed the application before that... not sure if that matters.

This is baked into sp_Whoisactive as well without needing an ad-hoc script.

It's the percent_complete column, over to the right (removed some superflous columns like sql_text and waitinfo):


Getting difference of a column between first and last row with count number I have table mytable as shown below

CREATE TABLE mytable( id, created_at, total_number )
AS VALUES
    ( 17330  , '2018-05-24 19:25:29'::timestamp, 26909 ),
    ( 46331  , '2018-05-25 00:57:34',            26914 ),
    ( 72131  , '2018-05-26 00:48:12',            26944 ),
    ( 102583 , '2018-05-27 00:53:50',            26972 );

I would like to get total_number difference between first and the last

I got my first and last row with this query

(SELECT * FROM mytable
    ORDER BY created_at ASC LIMIT 1)
    UNION
    (SELECT * FROM mytable
    ORDER BY created_at DESC LIMIT 1)

My query should return this results. How can I achieve it?

difference        number_of_rows   avg
(26972-26909=63)  4                63/4

Another variation. Here the function first_value is used to get both the first and last total_number (different directions):

select abs(lst - fst) as diff, number_of_rows
     , abs(lst - fst) / number_of_rows as average
from (
    select count(1) over () as number_of_rows
         , first_value(total_number) over (order by created_at) fst
         , first_value(total_number) over (order by created_at desc) lst
    from tbl
    fetch first 1 rows only
) as t;

An alternative is to specify the window frame with unbounded following and use the function last_value:

select abs(lst - fst) as diff, number_of_rows
     , abs(lst - fst) / number_of_rows as average
from (
    select count(1) over () as number_of_rows
         , first_value(total_number) over (order by created_at) fst
         , last_value(total_number) over (order by created_at
                                          rows between current row
                                                and unbounded following
                                         ) lst
    from tbl
    fetch first 1 rows only
) as t;

Perhaps it is worth noting that the default window frame is range between unbounded preceding and current row, so in the last example, we need to override that to truly see the last row. All rows in the sub-select are identical, so we can pick one randomly (used in both examples).

I believe limit 1 now is part of the standard, but I'm used to fetch first ... so I'll stick to that

In this situation, distinct could have been used instead of limit or fetch ...

from (
    select distinct count(1) over () as number_of_rows
         , ...
    from tbl
)


SQL Deadlock graph - Find exact statement holding the lock and the time it started I'm trying to troubleshoot a complex deadlock issue.

There are 2 separate processes (service and agent job) which often concurrently execute the same SP but with different @BatchID parameters. This SP is called within an explicit BEGIN TRAN. There are 30 different Insert/Update statements within the SP. The 2 processes deadlock 5 times a day on the same table/index, around the same time each day, and the service process is always the victim. The table has many unnecessary/redundant indexes and a couple of Insert/Update Triggers. Lock Escalation is Disabled.

Deadlock graph:

Deadlock statements:

We know that the survivor/victim are SQL sessions (SPID), NOT SQL statements.

We also know that the UPDATE statements shown in Frame1 of both sessions are involved in the Deadlock. And How are they involved ? They are both REQUESTORS of the U lock on the Index Key. Am I correct so far..

But which statements are the OWNERS ? And when did they start holding the X lock on the Index Key?
One of the standard recommendations for resolving/reducing deadlocks is to Shorten Transactions.
But if there are a hundred similar statements, without knowing WHICH statement started holding the lock and WHEN, it's not easy to go about shortening a transaction..

Question 1: But which statements are the OWNERS ?

Statements don't own locks.  Transactions own locks.

    And when did they start holding the X lock on the Index Key?

There's nothing in the deadlock graph or DMVs that will tell you that.  That information is only available in a trace event or an XEvent, at the moment the lock is acquired.

    Is a Key Lock held only on the rows which satisfy the UPDATE's WHERE clause, or is it held on All values that match the value in the Index

UPDATE uses a U lock to read, then converts to an X lock to update.

    Would it go away by adding (TABLOCK) to the UPDATEs

Probably.  One key strategy to deadlock resolution is to lock earlier and bigger.  Deadlocks occur only when two sessions acquire compatible locks, and then later attempt to acquire incompatible locks.  If the first locks in the transaction are incompatible, then the second session will wait until the first commits to acquire its first lock.

One easy way to get a lock at the right granularity is to use an application lock, with sp_getapplock.  If you decide that transaction A and B can't run concurrently, then just make each acquire the same application lock at the beginning, and they will run sequentially.


Run a SQL statement on all databases monitored by OEM I want to write a select SQL statement but I want it to run on all databases being monitored by Oracle Enterprise Manager.

I also would like to know how to run 'alter statements' on all database being monitored by the OEM. For eg. I would like to block/remove all users that have left the company from the database.


WHERE IN query to very large table is slow I currently have a table with around 300,000,000 rows with the following columns: user_id, item_id, item_value, and item_add_date

I also have a very long list of about 7000 user_id values which are associated with a date range.

I'm trying to query the really large table in order to obtain all rows where the user_id is in my list and where the item_add_date falls within the provided range.

My current approach is programatically looping through the list of ids and running the following query:

FROM all_items
WHERE user_id in [insert_user_id_programatically]
AND item_add_date >= [insert_start_date]
AND item_add_date <= [insert_end_date]

which returns the results I want but takes about one hour per query, and for 7000 queries would take almost a year to run. Is there a faster way of achieving this? I'm not familiar with SQL and do most of the data manipulation for this project in python normally.

EDIT:
Here is the output for EXPLAIN (ANALYZE, BUFFERS, VERBOSE):
https://pastebin.com/GYL8JWk4

Sometimes large in clauses can be improved by rewriting them as a join against a values clause.

so instead of

select *
from all_items
where user_id in (1,2,3,4,5)
 ...

you can use

select ai.*
from all_items ai
   join (
       values (1),(2),(3),(4),(5)
   ) t(id) on t.id = ai.user_id
where item_add_date >= ...
  and item_add_date <= ...;

An index on (user_id, item_add_date) should improve the performance


sqlnet.ora or tnsnames.ora changes require reboot? Anytime you change Oracle's sqlnet.ora or tnsnames.ora files, does the system require a reboot?  In my instance, I only have the Oracle client installed on the machine I'm referring to, but out of curiosity what would it mean for an Oracle server installation?

In general, no.  A tnsnames.ora change shouldn't require a reboot but some applications will read and parse the tnsnames.ora at startup to be able to present a drop-down list of servers to the user, for example, and will cache whatever was read when the application started up rather than re-reading the file.  Depending on the situation, it might be easiest to reboot a Windows client rather than figuring out how to kill and restart any applications that might have the data cached.

A sqlnet.ora change might benefit from a reboot for the sake of consistency though it is not required.  If you are doing something like enabling dead connection detection on a server by setting sqlnet.expire_time, for example, it probably makes sense to reboot the server to make sure that the setting applies to all connections rather than just new connections-- if you're trying to debug why a particular dead connection is still hanging around, knowing that the server was restarted and that you're not looking at some artifact of a connection that was opened prior to the setting being made would generally be helpful.


Behavior of data in indexes based on fill factor Let's say you have a database where the default fill factor is 20.  Whenever data is inserted, does it only create pages filled up to 20%?

From my understanding, when the data is inserted there will be roughly 20% of data in the pages.  When the data is updated, however, it will expand to more than 20% of the index, upto filling it and generating a page split, right?

Fill factor only comes into play when an index is created or rebuilt.  It is the amount of consumption for the index of the leaf level pages that are filled during these operations. (see the note below for more clarification on affected page levels)

When there is a DML command to data (INSERT, UPDATE, and/or DELETE), it will happen to the corresponding affected indexes.  In other words, if you have a page that is 20% filled and you insert data into that page, the page will contain more than 20% of the data (let's say 35% just for example's sake).  Do another insert, now the page is 64% filled.  Rebuild the index, and the leaf level pages will now relatively contain the space percentage that you specify (or implicitly the default value for the server).

(Note, when you don't specify PAD_INDEX to be ON, fill factor is only applied to leaf level pages.  But when you set PAD_INDEX = ON, the fill factor will be taken into account for intermediate-level pages of the index.  The default is OFF)

The reason to adjust fill factor (instead of using the default 100/0) is so that you minimize page splits when inserting or updating data.  But keep in mind, nothing is for free.  The lower the fill factor, the more space data would normally take up.  If you keep an 80% free page space for your indexes, they will consume a relatively larger amount of disk space, which can lead to more reads.

  From my understanding, when the data is inserted there will be roughly 20% of data in the pages. When the data is updated, however, it will expand to more than 20% of the index, upto filling it and generating a page split, right?

When data is inserted it will insert into the appropriate indexes at the appropriate page.  This could and most likely would very well cause the page consumption to be higher than the fill factor.

A page split will happen when new data is added to a full index page.  SQL Server will then split the page and approximately place half of the data from the full page into a new page.  Again, fill factor does not come into play here.

A legitimate reason to lower fill factor would be to minimize page splits, therefore minimizing index page fragmentation.


Rent weekly cost database design I have a database which contains a table BUILDING with in each row details about some building, another table BUILDING_UNIT contains rows with details about a single building unit which refers with a foreign key to the belonging BUILDING.ID.
The BUILDING_UNIT table also refers to a table CATEGORY which tells whether the BUILDING_UNIT is of category A,B,C,D again with a foreign key pointing to CATEGORY.ID.

Now the final cost of renting the building unit depends on its building, category and on the number of days it is rented and specific period of the year.
We only rent them weekly so I might as well use weeks only however I'd like it to be as flexible as possible in the future.

I cannot convince myself on a table which can represent this situation.

Do I have to use a table with coefficients for each day of the year and then a table with coefficients for A,B,C,D and then a table with coefficients for each Building and then somehow calculate a result?

Is there some standard and recognized implementation for problems of this type?

Thank you

EDIT: Notice the solution should abstract from the formula for calculating the cost which might change in the future.
However I might be asked to make a specific week of the year, for building unit X inside building Y to cost 300$ while the week after 600$.
Generally building units inside the same building and in the same week cost the same, however that might change in future so I'd like to treat already all specific cases.

You need to have a rentals table which has the following minimum columns:

id - unique identifier for the rental an autoincrement field
roomid - FK to the id of the room
startdate and enddate - of the rental
weekcoefficient - computed from either the startdate or enddate to be enable a lookup to week coefficient cost table (while its not adviseable to store computed columns, this may be necessary for performance reasons)
buildingid (optional) - FK to the id of the building (allows a direct lookup from the rental to the building without having to go through room table for performance reasons)
categoryid (optional) - similar explanation to the buildingid above to lookup the category coefficient table. This column may also be useful later in case the categories of the building change over time as it tracks the historical record for the category of the building at the time of allocation

UPDATE: To cater for the computation of the room coefficients with dependence on the building in which the room is located

To compute the different room coefficients you can have either:

a table with 3 columns: a number corresponding to output from WEEKOFYEAR function on a specific date, buildingid,  the coefficient (will have 52 values x the number of buildings)
Write a stored function which takes a date and matches the coefficient if there are few values specified by a range or if the combinations have some logic to them based on other features or data (any more than 10 different combinations makes this a nightmare)

Either approach above will require you to save the coefficient in the rentals table for historical reporting/auditing purposes and also maintain a note on what data was used for computation at that time


MariaDB Maxscale REST API can't show result hi i have already setup maxadmin on maxscale can access from rest api this is my configuration

    .....
[CLI_Service]
type=service
router=cli

[CLI_Listener]
type=listener
service=CLI_Service
protocol=maxscaled
address=192.168.101.107
socket=default

[MaxAdmin_Inet]
type=listener
service=CLI_Service
protocol=HTTPD
address=192.168.101.107
port=8010

but when i test the url like this

curl --include --basic --user "admin:mariadb" http://192.168.101.107:8010/v1/servers

the result is like this

HTTP/1.1 200 OK
Date: Wed, 13 May 2020 11:33:15 GMT
Server: MaxScale(c) v.1.0.0
Connection: close
WWW-Authenticate: Basic realm="MaxInfo"
Content-Type: application/json

Commands must consist of at least two words. Type help for a list of commands

whether i miss about the configuration ?

already got for default configuration when you need enable rest api maxscale

the tag must be [maxscale] and this is part when you need enable rest api for maxscale

[maxscale]
...
admin_host=192.168.101.107
admin_port=8989
admin_auth=1
admin_enabled=1

admin_host as your physical ip server

and then after you restart maxscale service, command maxctrl list servers or anything else can't run normally they need run command like this maxctrl -h 192.168.101.107:8989 list servers

and then you need test rest api

curl --include --basic --user "admin:mariadb" http://192.168.101.107:8010/v1/servers

and this is the results

HTTP/1.1 200 OK
Connection: Keep-Alive
Content-Length: 13156
Last-Modified: Wed, 13 May 2020 05:56:59 GMT
ETag: "0"
Date: Wed, 13 May 20 08:42:51 GMT
Content-Type: application/json

{
    "links": {
        "self": "http://192.168.101.107:8989/v1/servers/"
    },
    "data": [
        {
            "id": "server67",
            "type": "servers",
            "relationships": {
                "services": {
                    "links": {
                        "self": "http://192.168.101.107:8989/v1/services/"
                    },
                    "data": [
                        {
                            "id": "Read-Write-Service",
                            "type": "services"
                        }
                    ]
                },
                "monitors": {
                    "links": {
                        "self": "http://192.168.101.107:8989/v1/monitors/"
                    },
                    "data": [
                        {
                            "id": "MariaDB-Monitor",
                            "type": "monitors"
                        }
                    ]
                }
            },
            "attributes": {
                "parameters": {
                    "address": "192.168.101.67",
                    "socket": null,
                    "protocol": "MySQLBackend",
                    "port": 3306,
                    "extra_port": 0,
                    "authenticator": null,
                    "monitoruser": null,
                    "monitorpw": null,
                    "persistpoolmax": 0,
                    "persistmaxtime": 0,
                    "proxy_protocol": false,
                    "ssl": "false",
                    "ssl_cert": null,
                    "ssl_key": null,
                    "ssl_ca_cert": null,
                    "ssl_version": "MAX",
                    "ssl_cert_verify_depth": 9,
                    "ssl_verify_peer_certificate": false,
                    "disk_space_threshold": null,
                    "rank": "primary",
                    "serv_weight": "1"
                },
.......................


Laravel MySQL relation difficulties I have a "users" table:

And an organisations table ("orgs"):

And this is the pivot table:

I have some relations:

On the "users" table, I have a function called "orgs()" which is a belongsToMany relation to "orgs" (org_user,org_id,user_id).

On the "orgs" table, I have a function called "users()" which is a hasMany relation to "users" (org_user,org_id_user_id).

Now, in Laravel, when I do:

$orgUser = new OrgUser;
$orgUser->org_id=$org->id;
$orgUser->user_id=$user->id;
$orgUser->role_id=10;
$orgUser->save();

I get the following error:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`eamorr`.`org_user`, CONSTRAINT `org_user_role_id_foreign` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`)) (SQL: insert into `org_user` (`org_id`, `user_id`, `role_id`, `updated_at`, `created_at`) values (3, 1, -1, 2015-07-22 10:29:04, 2015-07-22 10:29:04))'

I would have thought I could just do something like:

$org->users()->attach(...)

or similar instead of having to reference the "org_user" table directly?

Does anyone have any experience at this?

Here's the User.php model:

namespace App;

use Illuminate\Auth\Authenticatable;
use Illuminate\Contracts\Auth\Authenticatable as AuthenticatableContract;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;

class User extends Model implements AuthenticatableContract {

    protected $table = 'users';
    public $timestamps = true;

    use Authenticatable;
    use SoftDeletes;

    protected $dates = ['deleted_at'];

    public function orgs()
    {
        return $this->belongsToMany('\App\Org', 'org_user', 'org_id', 'user_id')->withPivot('role_id');
    }

    public function files()
    {
        return $this->hasMany('\App\File', 'user_id');
    }

    public function clients()
    {
        return $this->hasMany('\App\User', 'user_user', 'owner_id', 'client_id');
    }

    public function tags()
    {
        return $this->hasMany('\App\UserTag', 'user_id');
    }

}

And here's the Org.php model:

namespace App;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;

class Org extends Model {

    protected $table = 'orgs';
    public $timestamps = true;

    use SoftDeletes;

    protected $dates = ['deleted_at'];

    public function files()
    {
        return $this->hasMany('\App\File', 'org_id');
    }

    public function locations()
    {
        return $this->hasMany('\App\Location', 'org_id');
    }

    public function tags()
    {
        return $this->hasMany('\App\OrgTag', 'org_id');
    }

    public function users()
    {
        return $this->hasMany('\App\User', 'org_user', 'org_id_user_id')->withPivot('role_id');
    }

}

And here's the OrgUser.php model:

namespace App;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;

class OrgUser extends Model {

    protected $table = 'org_user';
    public $timestamps = true;

    use SoftDeletes;

    protected $dates = ['deleted_at'];

}

What you expected to have actually exists in Eloquent. In order to attach/detach related models you need to call attach/detach methods on the relations and pass a single ID or array of IDs:

$user->roles()->attach($roleId);
$user->roles()->attach([$roleId1, $roleId2]);
$role->users()->attach($userId);
$role->users()->attach([$userId1, $userId2]);

See some more info at http://laravel.com/docs/5.1/eloquent-relationships#inserting-re lated-models - the examples are pretty close to your case, as it's also about user-role many-to-many relationship.


Select All Records From Table That Do Not Exist In Secondary Table This should be straight forward, but I am getting my tail kicked with the proper syntax.

I have this syntax

Select DISTINCT
    bm.Location
    ,bm.[Beer (Upcoming Menu)]
FROM
    ___SampleRequired bm
LEFT JOIN
    ___Sample td
    ON LTRIM(RTRIM(bm.[Beer (Upcoming Menu)])) = LTRIM(RTRIM(td.Beer_name))
WHERE
    bm.Type LIKE '%Mandate%'
    AND bm.Location = 'StapleChase'

which I want to show all beer that exist in the SampleRequired Table that DO NOT exist in the Sample Table.  When I execute the above query I get 24 results returned.  My expected returned result set is 23 because

BLUE MOON BELGIAN WHITE

exists in both of the tables.

How should I change this query so that I ONLY get the results that exist in SampleRequired that DO NOT exist in Sample?

***Had to use pastebin bc SO complained my post was mostly code...sorry

https://pastebin.com/jH6eX2du

Using NOT EXISTS

Select DISTINCT
    bm.Location
    ,bm.[Beer (Upcoming Menu)]
FROM
    ___SampleRequired bm
WHERE NOT EXISTS
   (SELECT 1
    FROM ___Sample td
    WHERE LTRIM(RTRIM(bm.[Beer (Upcoming Menu)])) = LTRIM(RTRIM(td.Beer_name)))
AND bm.Type LIKE '%Mandate%'
AND bm.Location = 'StapleChase'

Also, Max Vernon has a nice answer comparing LEFT JOIN vs NOT EXIST It's also more common to see this in the wild versus the LEFT JOIN method where you also need to add table.key IS NULL in the WHERE clause. i.e td.Beer_name IS NULL

And, Martin Smith comparing NOT IN vs NOT EXIST.


TSQL substitute for MAX() string ranking I have a table with repeating values (col1) and I need to return col2 based on a ranking system. In my example the ranking system is handled by MAX() giving me the highest value. I then return the full row where col2 is of the highest value. Simple. But what if instead of numbers in col2 I have a set of strings, 'dog','cat','bird'. I want only the dog row for distinct values in col1. If there is no 'dog' row I'll take the 'cat' row. And finally if no cat I take the 'bird' row.

I'm looking for the string equivalent of MAX where I can rank the strings. Do I have to write a UDF? Can I do it with set based operation instead?

CREATE TABLE TESTX (
    COL1 VARCHAR(56),
    COL2 VARCHAR(56),
    COL3 VARCHAR(56),
    COL4 VARCHAR(56)
)

INSERT INTO TESTX (COL1,COL2,COL3,COL4) VALUES (1,1,3,4)
INSERT INTO TESTX (COL1,COL2,COL3,COL4) VALUES (1,3,3,4)
INSERT INTO TESTX (COL1,COL2,COL3,COL4) VALUES (2,2,3,4)
INSERT INTO TESTX (COL1,COL2,COL3,COL4) VALUES (2,2,3,4)
INSERT INTO TESTX (COL1,COL2,COL3,COL4) VALUES (3,1,1,1)
INSERT INTO TESTX (COL1,COL2,COL3,COL4) VALUES (3,2,2,2)
INSERT INTO TESTX (COL1,COL2,COL3,COL4) VALUES (3,3,3,3)

SELECT * FROM TESTX

WITH CTE_X AS (
   SELECT
      COL1, MAX(COL2) AS COL2
   FROM TESTX
   GROUP BY COL1)
SELECT DISTINCT
    A.COL1, A.COL2, A.COL3, A.COL4
FROM
    TESTX A
JOIN
    CTE_X B ON A.COL1 = B.COL1 AND A.COL2 = B.COL2

Use RANK() function

with CTE_X as
(
  select *,
  RANK() over (partition by Col1
             order by Col2 DESC) rnk
  from t

) select * from CTE_X  where rnk = 1 ;

For reference (BOL) http://msdn.microsoft.com/en-us/library/ms176102.aspx


Error: Unable to write inside TEMP environment variable path I am installing PostgreSQL 9.2.4-1-windows.exe on Windows 7 64 with Service Pack 1.

I get the following, rather famous it seems, error:

  There has been an error.

  Unable to write inside TEMP environment variable path.

This occurs immediately upon launching.  I see the postgresql splash, then this error occurs.

Everything I have found on the interwebs so far, i have tried.  It includes:

Disabled all anti-virus software.  I have also stopped everything in the startup.
Checked file associations for .VBS scripts.  Nothing irregular (Windows Script Host registered)
Windows Script Host is Enabled.
I've tried installing as Administrator and other users that have admin rights.
I have created the key HKLM\SOFTWARE\PostgreSQL\Installations manually and give it full rights.
I have given Everyone full access to C:\Users\[username]\AppData\Local\Temp and c:\temp

Basically everything in the top 10 google hits.

I am working with the 32 bit version, but the 64 bit version install fails with the same error.  I'm able to install successfully on other machines with similar config.

What else can I try?

Install log file:

  Log started 09/20/2013 at 11:55:16
  Preferred installation mode : qt
  Trying to init installer in mode qt
  Mode qt successfully initialized
  Executing C:\Users\chad\AppData\Local\Temp/postgresql_installer_67b274715d/temp_check_comspec.bat
  Script exit code: 0

  Script output:
  "test ok"

  Script stderr:

  Could not find registry key HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.2 Data Directory. Setting variable iDataDirectory to empty value
  Could not find registry key HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.2 Base Directory. Setting variable iBaseDirectory to empty value
  Could not find registry key HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.2 Service ID. Setting variable iServiceName to empty value
  Could not find registry key HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.2 Service Account. Setting variable iServiceAccount to empty value
  Could not find registry key HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.2 Super User. Setting variable iSuperuser to empty value
  Could not find registry key HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.2 Branding. Setting variable iBranding to empty value
  Could not find registry key HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.2 Version. Setting variable brandingVer to empty value
  Could not find registry key HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.2 Shortcuts. Setting variable iShortcut to empty value
  Could not find registry key HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.2 DisableStackBuilder. Setting variable iDisableStackBuilder to empty value
  [11:55:21] Existing base directory:
  [11:55:21] Existing data directory:
  [11:55:21] Using branding: PostgreSQL 9.2 (x86)
  [11:55:21] Using Super User: postgres and Service Account: NT AUTHORITY\NetworkService
  [11:55:21] Using Service Name: postgresql-9.2
  Executing cscript //NoLogo "C:\Users\chad\AppData\Local\Temp\postgresql_installer_67b274715d\prerun_checks.vbs"
  Script exit code: 1

  Script output:
  Input Error: There is no script engine for file extension ".vbs".

  Script stderr:
  Program ended with an error exit code

  Error running cscript //NoLogo "C:\Users\chad\AppData\Local\Temp\postgresql_installer_67b274715d\prerun_checks.vbs" : Program ended with an error exit code

I was having this issue on Windows 7 64-bit and the following solution is what worked for me (via http://igordcard.blogspot.com/2012/03/unable-to-write-inside-temp-environment.html):

Why
To understand why this error was happening, I went to my TEMP folder and read the log from the PostgreSQL installer: C:\Users\myuser\AppData\Local\Temp\bitrock_installer.log

Which mentioned an interesting detail:
Input Error: There is no script engine for file extension ".vbs".

So, I picked up another file from the TEMP folder, the actual script used by the installer:
C:\Users\myuser\AppData\Local\Temp\prerun_checks.vbs

And tried to run it through the console using cscript:
Input Error: There is no script engine for file extension ".vbs".

I researched a bit more and discovered that the type of file associated with the vbs extension had been changed. In my case, by Notepad++. After I installed Notepad++, I associated some file extensions with it (including vbs), thus causing problems when detecting the type of the script (which shouldn't happen).

Solution
Run regedit, navigate to HKEY_CLASSES_ROOT\.vbs and change the (Default) key back to the string VBSFile. In my case it had been previously altered to Notepad++_file.


Moving a PostgreSQL database to a different AWS RDS instance I have an AWS RDS instance with multiple PostgreSQL databases in there including an application database and a monitoring database. The problem is that the monitoring database is consuming lots of resources and causes performance problems in the whole instance, so I'd like to have the application database and the monitoring database in different AWS RDS instances.

I have been investigating the issue and I've found that people are interested in having all their databases running on the same instance, while what I'm looking for is the other way around, so I'm wondering if I'm missing something.

The solutions that I've found are basically as follows:

Use an AWS RDS snapshot

This should be easy to do, but snapshots are at the instance level and I don't want to copy all databases, but just a single one.
Use the AWS Database Migration Service

The database migration service looks like a good approach because it uses a replication instance to make sure that all data written to the database while the migration task is running is captured. Unfortunately, I don't see how to use it for a single database.
Create a new instances and use pg_dump/pg_restore manually.

This approach seems the easiest one, but I'd need to make sure that either the application or the monitoring service are stopped while this is running.

So far, it seems to me that the right solution is the third one for its simplicity. Do you see any advantage/disadvantage that I might have overlooked?

You can safely replicate the whole instance using DMS, then remove the unnecessary DBs on both resulting clusters.  After this, you will most possibly have excessive storage on the new instance - I think (but never tried) you can just do another such migration to a fitting (smaller) instance type.


Autoincrement primary key in CREATE TABLE ... AS SELECT I created table using a complicated select query via CREATE TABLE ... AS SELECT.... How can I add an autoincrement primary key in this query?

For example:

create table `user_mv` select `user`.`firstname` as
   `firstname`,
   `user`.`lastname` as `lastname`,
   `user`.`lang` as `lang`,
   `user`.`name` as `user_name`,
   `group`.`name` as `group_name`
from `user`
  inner join `user_groups` on (`user`.`user_id`=`user_groups`.`user_id`)
  left  join `group` on (`group`.`group_id`=`user_groups`.`group_id`)
where `user`.`lang`=`group`.`lang`

This query creates a table that contains firstname, lastname, lang, username, group_name columns. I want it to also have an id column that is an autoincrement primary key.

Is there any way to do this by changing this query?
I know I can do that by altering table after executing this query, but if there is any way to do this directly in the create table statement, I'd like to know how to do that.

CREATE TABLE `user_mv` (id INT AUTO_INCREMENT PRIMARY KEY) SELECT `user`.`firstname` as
   `firstname`,
   `user`.`lastname` as `lastname`,
   `user`.`lang` as `lang`,
   `user`.`name` as `user_name`,
   `group`.`name` as `group_name`
from `user`
  inner join `user_groups` on (`user`.`user_id`=`user_groups`.`user_id`)
  left  join `group` on (`group`.`group_id`=`user_groups`.`group_id`)
where `user`.`lang`=`group`.`lang`;

As long as you don't have an 'id' column in your SELECT, this appears to do exactly what you want.  The columns in the select will be appended to the right of the columns you declare.

http://dev.mysql.com/doc/refman/5.6/en/create-table-select.html (also in previous versions).


Quite large (400k) mysql database design for multiple users I'm seeking advice of experienced admins.

I'm working on a website, where you solve word anagrams. If it solved it should never be displayed again.

Wordbase contains ~400k entries. What would be the most effective solution to storing such data?

One way could be:

+---------+------------------------+
| word_id | user1 | user2 | user...|
+---------+------------------------+
|  1      | null  |  null |  1     |
|  2      | 1     |  null |  null  |
|  ...    |       |       |        |
|  400000 | null  |  1    |  null  |
+---------+------------------------+

Where let's say 1 = solved.

But wouldn't it become a monster quite quickly?
(+even a simple query of extending it by a new user takes forever)

Other solution is to store every solved word_id for all users, but then, it can be 6-digits for every entry and growing massively and rapidly aswell.

Also which engine would be more effective in this example? MyISAM or    InnoDB?

Use (word_id, user_id) table to store solved combinations, that way you won't store the NULLs. How many words will an average user solve? 5-20 maybe? 400k * 20 = 8M rows, thats nothing (the two column table is quite compact).

Do not even try to alter the table on each new user, that would stop working really quickly.

I suggest InnoDB for anything new unless there is a specific reason against it.


How to see unclosed PostgreSQL connection while using psycopg2? I run python code:

con = psycopg2.connect(some_dsn)
cur = con.cursor()
cur.execute(some_sql)
res = cur.fetchone()
#con.close() <-- connection is not closed

Then I try:

ps auxf | grep postgres

or

SELECT * FROM pg_stat_activity

both does not display correspond information.
How to see that unclosed connection?

Python answer

Given a psycopg2 connection object called conn, initialized by, e.g.:

import psycopg2

conn = psycopg2.connect(database="my_database",
                        user="username",
                        password=password,
                        host="localhost",
                        port="5432")

If I'd like to see if conn is closed or not, what I usually do in python 3 is;

conn.closed

If the .closed method returns 1, then the connection is closed.
If the .closed method returns 0, then the connection is still opened.

As stated in the documentation:

  closed

  Read-only integer attribute: 0 if the connection is open, nonzero if
  it is closed or broken.

https://www.psycopg.org/docs/connection.html

You can also see that by just typing in the name of your connection in a python console and watch at the closed key at the very end;

conn
Out[4]: <connection object at 0x7fe3d042e2b8; dsn: 'user=postgres
        password=xxx dbname=my_database host=localhost port=5432', closed: 0>

conn.close()

conn
Out[6]: <connection object at 0x7fe3d042e2b8; dsn: 'user=postgres
        password=xxx dbname=my_database host=localhost port=5432', closed: 1>

You can have more help on the connector methods by using:

help(conn)

Notice

It's really important to close a connection once you are done with your changes. Do not forget to commit() them before, of course, otherwise your changes would be lost as if you performed a rollback as stated in the documentation of psycopg2:

  close()

  Close the connection now (rather than whenever del is executed). The
  connection will be unusable from this point forward; an InterfaceError
  will be raised if any operation is attempted with the connection. The
  same applies to all cursor objects trying to use the connection. Note
  that closing a connection without committing the changes first will
  cause any pending change to be discarded as if a ROLLBACK was
  performed (unless a different isolation level has been selected: see
  set_isolation_level()).

Source: https://www.psycopg.org/docs/connection.html

psql and system tools answer

From psql, you can also list connections.

For example by using this command:

select pid as process_id,
       usename as username,
       datname as database_name,
       client_addr as client_address,
       client_hostname,
       client_port,
       application_name,
       backend_start,
       state,
       state_change
from pg_stat_activity;

or as you simply stated select * from pg_stat_activity;.
The interesting thing to notice here is the value of client_port.

The former command being modified after: https://dataedo.com/kb/query/postgresql/list-database-sessions

In any cases, the question and answer given here:    What exactly is Psycopg2?
may be helping understanding that it's barely impossible to differentiate a psycopg2 connection from a standard connection from outside Python using psql; a connection open with psycopg2 will look like any other opened connections.

But...

Now, if you open a psycopg2 connection and you carefully watch at the output of the aforementioned psql command, you will of course see an extra line while the connection is opened (compared to a closed psycopg2 connection).
In an example on my system, the line which corresponds to the connection open by psycogp2 looks like:

 process_id | username | database_name | client_address | client_hostname | client_port |     application_name     |         backend_start         | state  |         state_change
------------+----------+---------------+----------------+-----------------+-------------+--------------------------+-------------------------------+--------+-------------------------------
      12693 | postgres | my_database   | 127.0.0.1      |                 |       59218 |                          | 2020-03-22 10:58:12.143291+01 | idle   | 2020-03-22 10:58:12.149866+01

It opened a connection on client_port 59218.
As I said, from here, you cannot differentiate from any other connections.

Then, if I look at the output of ps -ef | grep "PID\|59218" (59218 being the client_port of the previous psql output) I have:

$ ps -ef | grep "PID\|59218"

UID        PID  PPID  C STIME TTY          TIME CMD
postgres 14663 31224  0 10:59 ?        00:00:00 postgres: 10/main: postgres my_database 127.0.0.1(59218) idle

Again, this looks exactly the same as any other connection, so I cannot know which application has opened this connection.

But you can further explore the outputs of netstat:

$ sudo netstat -ap | grep -i "Proto\|59218"
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 localhost:59218         localhost:postgresql    ESTABLISHED 859/python3
tcp        0      0 localhost:postgresql    localhost:59218         ESTABLISHED 14663/postgres: 10/
(...)

Where localhost:postgresql corresponds to localhost:5432 (this can be seen by activating/deactivating the -P flag of lsof.) Here you can also see the same postgres PID as the one in the ps output above.
So postgresql is ready to accept IO operations with python3 on the client port 59218 which was opened. This port closes when the psycopg2 connection is closed.

So you can try to figure out that client_port for the connection between python3 and postgresql, e.g. by:

sudo netstat -ap | grep -i "python3" | grep "postgres"

and then grep for its value in the output of either psql and ps.

Notice

I also tried to use the sockstat tool:

$ sudo sockstat | grep -i "PID\|5432\|59218"
USER     PROCESS              PID      PROTO  SOURCE ADDRESS            FOREIGN ADDRESS           STATE
username python3              859      tcp4   127.0.0.1:59136           127.0.0.1:5432            ESTABLISHED
postgres postgres             14663    tcp4   127.0.0.1:5376            127.0.0.1:59218           ESTABLISHED

Here, I unfortunately don't know what is this 59136 port used by Python itself in the source address, nor the 5376 port used by postgres as both doesn't match the default port specified by my pscopg2 connection which is 5432.
There is probably underlying protocols/connections being used in socket connections and I'm not an expert in networking to explain those.

I also found this thread: https://serverfault.com/questions/128284/how-to-see-active-connections-and-current-activity-in-postgresql-8-4

It also mentions an interesting tool called pg_top` but which doesn't completely answer your question.


SQL Errorlog Constant FlushCache Messages A server that is part of a SQL 2017 Always on availability group (BAG) I have noticed an increase of these messages. I understand these are messages that appear in the log as standard since 2012 (trace flag prior to 2012) but over the last 2 days they are appearing every few minutes, there are no backups running or maintenance jobs at these times
The server is acting as the secondary non readable fail over partner and the primary server is not exhibiting the same behavior.

09/04/2020 10:52:24,spid82s,Unknown,FlushCache: cleaned up 2303 bufs with 1881 writes in 81090 ms (avoided 11 new dirty bufs) for db 7:0
09/04/2020 10:52:07,spid52s,Unknown,last target outstanding: 2 avgWriteLatency 86
09/04/2020 10:52:07,spid52s,Unknown,average writes per second:  17.70 writes/sec             average throughput:   0.19 MB/sec I/O saturation: 13073 context switches 15434

There is also a warning in the system error log about disk issue, seconds after this the availability group failed over ever since the FlushCache messages have increased.

Has anyone experienced anything similar or have any advice, my sysadmin is also looking at the SAN and VMware estate.
Had a sudden thought could these be caused by autogrowth activities?

Switch to indirect checkpoints (generally, set recovery interval time to 60 seconds, at least for user databases).
I wrote about this issue here and here, and you should read those posts in full before making the change, though it’s pretty much a no-brainer IMHO.
The impact in our environment was drastic and immediate, and it was simple to prove the change was responsible. Every database with the symptom went from 30-60 second checkpoints, and errorlogs full of these messages, to sub-second checkpoints and no more errorlog entries.
There are probably mitigation techniques for your workload, too, for example see this post by Itzik Ben-Gan but making checkpoints more efficient is a quicker and easier path. Your storage and VM folks aren’t off the hook, though; there is definitely a disk issue they need to address (but I disagree that these FlushCache messages were the root cause of any failover; more likely they are just a different victim/symptom).


How can I catch the output of DBMS_OUTPUT.PUT_LINE when executing a block using [Oracle.DataAccess.Client]? In my oracle Database if have the following table:

Create table Test_call_count (
    count number(10)
);
insert into Test_call_count values (0);

Now I can run the following PowerShell script

if ($ora_loaded -eq $null)
{
    $ora_loaded = [System.Reflection.Assembly]::LoadWithPartialName("Oracle.DataAccess")
}

$ConnectionString = "Data Source=YOUR_TNS;User ID=YOUR_ID;Password=YOUR_Password"

function Execute-NonQuery
{
    param (
        $sql
    )

        $conn = new-object Oracle.DataAccess.Client.OracleConnection
        $conn.ConnectionString = $ConnectionString
        $cmd = new-object Oracle.DataAccess.Client.OracleCommand($sql,$conn)
        $conn.open()
        $cmd.ExecuteNonQuery()
        $conn.close()
}

Execute-NonQuery "begin update  Test_call_count set count = count + 1;DBMS_OUTPUT.PUT_LINE('Test...'); end;"

Looking at the the database I can verify, that count increments with each call.

But is there a way to extend this, so that I can get the data send to DBMS_OUTPUT ?

DBMS_OUTPUT.GET_LINES is the way to get them back programmatically. Call it the same way as any other procedure. Or for a single line (which is probably easier to code) DBMS_OUTPUT.GET_LINE


Finding all possible minimal covers I have a relation schema R = {A, B, C} and the following functional dependencies:

A → B
A → C
B → A
B → C
C → A
C → B

How many different minimal covers can I derive from this relation schema? I have found the following, but I am not really sure if those are all:

A → C
B → C
C → A
C → B

Also, I am not sure if there is some rule on how to know if one has found all possible minimal covers.

Thanks a lot for any help!

In general, there are different canonical covers of a set of functional dependencies, and a canonical cover is called minimal if it has less dependencies of any equivalent cover.

So, for instance, in your example the cover:

A → B
B → C
C → A

is minimal, because it has 3 dependencies and it is not possible to find a cover with less dependencies.

There are several definitions and algorithms of cover. For instance, the Chapter 5 of "The Theory of Relational Databases" of Maier, D. Computer Science Press, 1983, describes several kinds of cover (nonredundant, canonical, minimal, optimal, annular), and different algorithms to find them, but for what I know nobody has given a formal algorithm to find all the possible minimal cover.


How to create SQL Server backup from Windows Server to Linux Client with SQLCMD? I've got sqlcmd installed on an Ubuntu LTS instance and I'm able to connect successfully to Windows SQL Server and do queries and such.
sqlcmd -S 192.168.0.100 -U SA -P secret

I need to be able to create a backup of the database (preferably as a gigantic db-backup.sql).
It appears that the BACKUP command can only save a .bak file to the remote Windows SQL Server, but I want to save it locally on the Linux client that's actually running sqlcmd.
BACKUP DATABASE [MyDatabaseName]
    TO DISK = N'./MS-SQL-Server-Test-DB.bak'
    WITH NOFORMAT, NOINIT,
    NAME = N'MS-SQL-Server-Test-DB Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

How can I backup to the local client instead of the remote server?
(I have full sudo access on the Linux client, but only sqlcmd db backup user and SA user access to the Windows Server).
Note: It's Windows Server 2019 or something like that. It doesn't have ssh installed, and I don't think I can get it installed.

the backup is done by the sql server process in a place reachable from the server.
the options usually are:

a local path
a networkpath (like a samba or cifs share)
an cloud storage

So what you are asking can't be done in the way you want.
As alternative you can script the content:
https://docs.microsoft.com/it-it/sql/ssms/scripting/generate-scripts-sql-server-management-studio?view=sql-server-ver15
Schema and data.