advice on modeling schema to have better access to foreign entity I am fairly new to modeling on MongoDB--please consider this simple example schema

const userSchema = Schema({
  name: String,
  username: String
});

const addressSchema = Schema({
    user: { type: Schema.Types.ObjectId, ref: 'User' },
    street: Schema.Types.String,
    city: Schema.Types.String,
    state: Schema.Types.String,
    zip: Schema.Types.String,
});

Since querying addresses by user will be a constant job, what is the right choice to make?

a) place an "addresses" field in User schema as

addresses: [{ type: Schema.Types.ObjectId, ref: 'User' }]

b) having a virtual property on User

userSchema.virtual('addresses', {
  ref: 'Address',
  localField: '_id',
  foreignField: 'user'
});

Is this a good use of virtual type, according to mongoose docs?

  are document properties that you can get and set but that do not get
  persisted to MongoDB. The getters are useful for formatting or
  combining fields, while setters are useful for de-composing a single
  value into multiple values for storage

It doesn't quite fall in the situation they describe, so I'm not sure if it applies.

The following schema may be better to match your requirement.

{
    name: String,
    username: String,
    address: {
        street: String,
        city: String,
        state: String,
        zip: String,
    }
}

Try to avoid having multiple collections like a relational database. Joining is a costly operation in NoSQL databases.

References:

6 Rules of Thumb for MongoDB Schema Design: Part 1

6 Rules of Thumb for MongoDB Schema Design: Part 2

6 Rules of Thumb for MongoDB Schema Design: Part 3


related column contents I've designed a mysql database made of several independent/unrelated tables to use in my office. Different tables hold information about clients (names, addresses etc), visits to the office (dates, reason etc) or other information like comments (related to a specific visit).
When a known client enters the office I can retrieve all the relevant data, insert the new visit data and possible comments about the visit.
I need to store visits in a specific order (1st visit 1st, etc). Sometimes I forget to store a visit and I have to enter the data later. The order of the visits gets messed up and I manually (update commands) correct it by changing the visits' autoincrementing primary key number (which represents the order). This way I get the right order of visits but comments (related to the changed primary key) cannot be retrieved.
Is there any way to make sure that changing the primary key would make the necessary changes to the rest of the database, so that everything would work as before the changes? Do relational 1:many or other implementations accomplish that? And how?
Please note that I'm an amateur, self-taugth "database designer".

First, yes it is possible to make sure changing a column in one table gets updated in all related tables. The concept is defined as a Foreign Key. In MySQL, the tables must be using the InnoDB storage engine. Here is the link to the 5.5 documentation on implementing foreign keys.

The basic gist is your child table (the table that relies on a foreign table's column value) needs to have this done to it:

ALTER TABLE bar ADD CONSTRAINT fk_foo FOREIGN KEY foo_id
 REFERENCES foo (foo_id) ON DELETE CASCADE ON UPDATE CASCADE

Where foo_id exists in both the child table bar and the parent table foo. The foo_id column must be defined exactly the same in both tables (same size, type and unsigned/signed status).

Secondly, I would highly suggest not basing your order of visits on the auto-increment value.  Auto-increment columns should have no meaning to your data.

If you have a date column, you should be able to get the order based on that column + the visitor, even if you add it manually at a later date. This would remove the 'need' of updating the autoincrement value.


The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values One of INSERT INTO script is written as following.

INSERT INTO tableName (Column1, Column2,....) VALUES (value1, Value2,...), (value1, Value2,...),....

Following is the error we are facing on parsing above insert statement

  Msg 10738, Level 15, State 1, Line 1007
  The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values.

My simple question is that, Can we change 1000 values limit ?


Tracking the update or changes in mysql table I need to stream the changes to elasticsearch to keep in sync with my database(mysql).
What will be best way to do this ? I have thought for below approaches

Using trigger( but my table will have billions of data and the
application will have frequent update so trigger may be costly) .

2  Using delta/history table (this will add the extra effort in
development and this will cause the lot many joins ) .

Is there any out of box solution or any other way which will have the better performance and less complexity.

This topic is very interesting for me now, so because it Up, some last information:

Replication is good, but what if You need replicate data to different technology Target System (for example Redshift)

I found and test few implementations for change data capture:

1) MariaDB MaxScale CDC - my personal test was unsuccessful (and builded from source and binary distribution return link for missed plugin). Ask a questions, wait for answer.

2) StreamSets - simulate slave, ship changes to Kafka MQ. Tested on real loading, work perfect

3) Yelp

4) Maxwell Daemon - under test now, example with NiFi - NiFI examples

5) Python Library - also simulate a slave for remote server

6) Lapidus - NodeJS implementations

Few other links also founded, some under active maintenance, some look like forgotten.

From the tests, StreamSets look very good, Maxwell also very interesting
MaxScale - sure it work, but may be available only under subscription support.

From "other" side - Talend as primary data integration tools, Redshift, memSQL.


Version error while restoring database Can anybody resolve the issue where I am trying to restore a database to a SQL Server 2008 server from a backup file taken from a SQL Server 2008 R2 server?

If you mean that you are trying to restore a database backup from a more recent version, such as SQL Server 2008 R2, to a previous version such as SQL Server 2008, then you cannot do such a restore.

You could restore a SQL Server 2008 database to a SQL Server 2008 R2, since the backup can be promoted to the new level.

If you need to move the data to a lower level server it will likely require scripting the database definitions to create a database on the earlier version of SQL Server.  Then the data will need to be copied to that server.

There are a number of posts on doing this, such as:

Restoring a backup to an older version of SQL Server

http://www.mssqltips.com/sqlservertip/2810/how-to-migrate-a-sql-server-database-to-a-lower-version/

EDIT:  Although there is no way just to attach the files and make it work, the good news is that the changes between SQL Server 2008 and 2008 R2 are few.  Therefore, you have a good chance of migrating code and data to the SQL Server 2008 server using techniques such as posted above without any major problems. (Other than the time and nuisance.)


How to confirm that chunk transfer is completed on a DB I have one case where i create db for Daily data. I have some collections in this db which are sharded.
My requirement is as follows:
Create every day DB
If There is a DB which is more than 7 days old take its dump in some location and delete it from live DB.

I have one question how can i check if chunk transfer/balancing was completed for this DB before taking its dump and deleting it.
I am concern if i delete the DB whose chinks were not transferred completed will it corrupt my system

Regards
Virendra Agarwal

To ensure there are no migrations in progress while taking your backup, you should stop the balancer via sh.stopBalancer(). Any in-progress migrations will be completed before the balancer is disabled.

If you want to ensure the balancer is stopped before taking your backup, you can run the following in the mongo shell, which should return true:

 !sh.getBalancerState() && !sh.isBalancerRunning()

After taking your backup, you need to remember to start the balancer again.


Pros and cons of master status table vs private status table? I am the only DBA at our company and am relatively new in the profession (having 2.5 years of experience now). The developers and I are currently having a discussion about how to properly design the database for a new piece of software being developed. They develop their applications in modules and believe that having a private status table for each module is best, where as I believe having one table to hold all internally generated states (and then possibly additional tables to give those states some meaning) would be best. The sample code below is written in postgresql, since that's what we use.

My approach:

CREATE TABLE status (
  status_id SERIAL PRIMARY KEY,
  status_code VARCHAR(3) UNIQUE NOT NULL, -- short description
  status_description VARCHAR(50) UNIQUE NOT NULL -- long description
);

CREATE TABLE member_status (
  status_id INT PRIMARY KEY REFERENCES status (status_id)
);

Their approach:

CREATE TABLE member_status (
  status_id SERIAL PRIMARY KEY,
  status_code VARCHAR(3) UNIQUE NOT NULL, -- short description
  status_description VARCHAR(50) UNIQUE NOT NULL -- long description
);
-- rinse and repeat for every object that may need a status

They only see "problems" with my approach (which have yet to be clearly articulated) and, while I don't see problems with their approach, I do believe it is not the best design.

Any input into which design is better and WHY would be appreciated.

The main problem with your approach occurs when you want to maintain referrential integrity to the status table.

Suppose you have a member table:

CREATE TABLE member (
  member_id SERIAL PRIMARY KEY,
  status_id int NOT NULL,
  ...
);

Now if you want to enfore referrential integrity to ensure each member has a valid status, you can't. This is because only a subset of the status rows applies to members. As a result, a member can be assigned a purchase_order_status and RI will still pass.

EDIT2

In a comment you clarified that in your solution member would have RI to the single-column member_status table. This suggests your design could land up having a large number of single column tables, just to control refferential integrity. Now any time you add a new status, you need to add it to 2 tables.

There's another problem with your master_status table.

status_code is unique. Meaning that purchase_order and sales_order cannot use the same codes. This seems to be an unnecessarily harsh restriction. However, droping the unique constraint risks duplicating codes within a category.

So the next thing you might consider is adding a status_type and enforcing the unique constraint accross both type and code. So your status_type attempts to fix the underlying problem by artifically splitting the master table into multiple tables.

Basically the problem boils down to: You are trying to consolidate multiple distinct sets of data into a single table because there is a similarity in that some of their columns overlap.

Joe Celko has written a fairly in-depth article on Lookup Tables in SQL.

EDIT On second thoughts, perhaps your approach is not that much like "One True Lookup Table" because you do acknowledge the need for additional tables where more detail is required. The RI issue still remains however.


Why does the secondary server not failover? I hope someone can help. I will try to summarise as best as I can.
We have four servers in a cluster with an AG set with Database Health Detection. Three servers are on site1 with the fourth on a separate DR site2. We are testing DR by cutting the link between the two sites.
As expected, when the link is cut, the three servers on site1 stay up and the server on site2 loses quorum and the database goes into recovery mode until the link is re-established.
If I perform a manual failover to site2 before we cut the link, the same thing happens and I have to force the quorum manually to bring the server back up on site2.
This isn't a big deal, but I would prefer it to stay up, so I added an Azure witness to the cluster. However testing has given us unexpected results.

With an Azure witness, if I shut down the 2 secondary servers on site1 before we cut the link (leaving just one server on each site), then cutting the link initialises an automatic failover to site2. This is great, but I want the other servers to be left up and running.
If I leave the 3 servers up on site1 up and we cut the link, the server on site2 loses Azure (even though it still has internet) and the server loses quorum.  That I don't understand at all.

Wouldn't the server in site2 think that it was the last remaining server and failover to become the primary?
Additionally, I've tried to find out what the exact events are that happen when a failover occurs.

Does the primary tell the secondary to failover and if so, how does that work if the link is cut? Surely it can't.
Or does the secondary realise that there is no handshake and assume it's now the Primary? If so, wouldn't I end up with both sites having a primary server?

I've tried searching for sources of information. Any help would be greatly appreciated.

Wouldn't the server in site2 think that it was the last remaining server and failover to become the primary?

No, because it is part of a group with only two votes, which is not a majority.  For all the DR side knows there are three healthy nodes on the primary side which have quorum.
When you gracefully shut down the primary side, Dynamic Quorum reconfigured the votes.
Quorum is explained here: https://docs.microsoft.com/en-us/windows-server/failover-clustering/manage-cluster-quorum


How the single SQL statement is isolated? All the books I've seen so far, talking about transactions show scenarios with several SQL statements involved.

But what's about single statements? What level of isolation do they have? Is it specified somewhere in a standard? Or does it depend on RDBMS and isolation level?

Let me show a couple of examples.

UPDATE table SET value = value + 1 WHERE id = 1;

This is a composite read-update-write operation. Can a parallel transaction change the value between read and write operations? Some book states that this operation is atomic (in multithreaded programming meaning) in the most of the RDBMS.
SELECT * FROM table t1 JOIN table t2 USING (id);

If the table is quite large (or query would have some complicated filtering clause), is it possible at some isolation level that t1.* and t2.* columns would differ due to parallel update?
SELECT * FROM table1 WHERE id IN (SELECT t_id FROM table2);

Is it possible that some records from table1 are removed after sub-select has been executed? (Here I assume that table2.t_id references table1.id with cascaded removal.)
CTE...

Links to the useful manuals that fully explain all the details of transactions are also appreciated.

For SQL Server:

  But what's about single statements?

In SQL Server, there is 4 transaction isolation levels (in pessimistic locking model). Default transaction isolation level is Read Committed, and locks are placed on a statement level. If you have a transaction that has 2 statements inside it that retrieve same data, and in the middle of that transaction (right after 1st statement but before 2nd statement started) some other transaction modifies the data that is retrieved by first transaction, then first transaction will return different data in 1st and 2nd statements.

That is known as non-repeatable read problem. This can be solved by increasing transaction isolation level to REPEATABLE READ where locking happens on transaction level (not on a statement level)

UPDATE table SET value = value + 1 WHERE id = 1;

  Can a parallel transaction change the value ?

No it can't. This is a single statement, and during it Exclusive lock is placed on rows that are being modified.
No other transactions or statements can modify these rows until our update statement is complete

SELECT * FROM table t1 JOIN table t2 USING (id);

  is it possible at some isolation level that t1.* and t2.* columns would differ due to parallel update?

Yes, in READ UNCOMMITED is possible (or when with (NOLOCK) hint is specified), since Shared lock is not applied. In other isolation levels - no, because Shared lock is being placed on rows from t1 and t2, and during it  rows can't be modified (Exclusive lock can't be acquired by other transactions)

SELECT * FROM table1 WHERE id IN (SELECT t_id FROM table2)

  Is it possible that some records from table1 are removed after sub-select has been executed?

In READ UNCOMMITTED is possible. In higher isolation levels - not possible, because during execution of this statement, Shared lock is placed on all selected rows of table1 and table2, and no other transaction is able to modify the rows of table1 and table2 (put Exclusive lock) until our select statement completes and Shared lock released from table1 and table2

Here you can read more information about SQL Server's isolation levels:
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-ver15


Should I add it as an additional column or a seperate table? I have a table in my SQL Server that's called "Regions", it has information about 9 regions and the 10th row holds the info about our Division.
Now that we will be merging another 2 divisions with its regions, Should I add them all in the same table like I have currently or start separating them by having a "Division" table and "Regions" table?

Which one is the best approach to pull reports from, like pulling documents implementing in specific regions from a specific division? how can I reference that, please advice.
Thanks in advance.

from your question i understand that you have a "One to many" relationship between The Division to the Regions,

the best way with this kind of relational data from my experience is to create a Division table and a Region table that holds the DivisionID.

that will also use less storage space because you won't need to write the division name in string, you can reference the int ID column value that uses less space than the string(char/varchar etc.)
:)
example

    CREATE TABLE  dbo.Division
(
    [ID] [int] NOT NULL IDENTITY(1,1) CONSTRAINT PK_Division PRIMARY KEY,
    [Name]  NVARCHAR(255) NOT NULL
)

CREATE TABLE  dbo.Region
(
    [ID] [int] NOT NULL IDENTITY(1,1) CONSTRAINT PK_Region PRIMARY KEY,
    [Name]  NVARCHAR(255) NOT NULL,
    DivisionID INT CONSTRAINT FK_DivisionRegion FOREIGN KEY REFERENCES dbo.Division (ID)
)

shimon.


how to view content of xbase procedures? I would like to view the code of my xbase stored procedures.

I recognize that show procedures gives me the list of procedures.

Can anyone instruct me on how to get the actual code for the stored procedures?

in Sybase ASE:

sp_helptext sp_name in your database can give you the code of the stored procedure
select * from sysobjects where type='P' in your database can list the stored procedures


SQL Rebuild Index, Recovery Model, and db log filesize? I have a database that we currently run transaction log backups throughout the day, every 30 minutes to be exact, and we run a full backup each day at 2am.

Every Saturday at 3am, we have a job setup to rebuild the indexes on all tables.

That being said, doing the index rebuild causes our transaction log to grow greatly. I'm toying with different ideas to alleviate the extra drive space needed (approximately 25gb post re-index).

I was considering setting the database recovery model to simple prior to the rebuild task, to prevent all of the index rebuilding from being logged, and then setting it back to full once the rebuild is complete.

Is anyone else using this method? Or can anyone provide insight/advice as to why this may be a bad idea? Or any tips about how to handle huge log files while performing db maintenance tasks?

Switching to SIMPLE means you break the log chain. When you 'revert' to FULL you need to start a new log chain, meaning you have to take a full backup and start taking again new log backups. The switch to simple, no matter how short, in effect creates a new 'epoch' in your backup chain, as any backup from before the switch to simple can no longer be applied to the database after the switch, nor the other way around.

So at this moment you have to stop and think: what is the business requirement that made you have FULL recovery model to start with? Whatever reason it is, is unlikely that it can be 'suspended' every Saturday at 3am, and is just unlikely that it will tolerate your 'epoch' situation in which you can restore back in time from Friday to Thursday, but you cannot from Saturday to Friday because Saturday is a new 'epoch'. In other words, if you have a business requirement for FULL recovery model, then you better not break it.

But if you do not have a business requirement for FULL recovery model, then you have room to play. And I do not mean to switch to SIMPLE, I mean use the 'other' recovery model: BULK_LOGGED. The reason your reindex  operations generate copious log is that they occur under FULL recovery model. Under BULK_LOGGED the index rebuild (both offline and online) are going to use minimally logged operations, see Operations That Can Be Minimally Logged:

  If the database is set to the simple or bulk-logged recovery model,
  some index DDL operations are minimally logged whether the operation
  is executed offline or online. The minimally logged index operations
  are as follows:

  CREATE INDEX operations (including indexed views).
  ALTER INDEX REBUILD or DBCC DBREINDEX operations.
  DROP INDEX new heap rebuild (if applicable).

So, if possible, switch the database recovery model to BULK_LOGGED and leave it as such.


Unique index on multiple columns vs single column index I have a table in the following structure. Now from a data integrity standpoint I want to add a joint uniqueness index on product_id and item_id. Now there can be around 100K product_id and 50k item_id. Generally my queries would like Select item_id from product_items where product_id = 'some-id' and item_type=2. Will the uniqueness index be sufficient in this case or should i add a separate index on product_id as well. Also does uniqueness order matter when creating unique index

     product_items
      :id,
      :product_id
      :item_id
      :item_type (int)
      :created_at
      :updated_at

Since your DBMS isn't specified, I'll respond in general terms.

Assuming your uniqueness index specifies product_id as the first column and item_id as the second, the index can be used either to locate a specific row based on product_id and item_id, or all rows with a given product_id.

An index can be used to speed searches when the query uses the first column defined as part of the index, and will locate rows based on the first n columns in the index if the WHERE clause (or JOIN conditions) use all of the first n rows.

So, if product_id is the first column listed in the index's creation, then it could be used any time you're locating records based on product_id. The index would not be used for SELECT product_id FROM product_items where item_id = 'some-id' and created_at > '2018-01-01', because item_id isn't the first column in the unique index.

NOTE: Your unique (non-clustered) index might well be scanned if the query was SELECT product_id FROM product_items WHERE item_id = 'some-id', because it would be a covering index. Since all the columns needed be the query exist in the index, the DB engine may be able to treat the index as if it were the product_items table itself, allowing it to just read the index, instead of the entire table and the full data rows. (If the unique index is also product_items's clustered index, then the index is the table).


Storing and retrieving raw XML in Oracle table I need to store and retrieve raw SOAP XML in Oracle. What is the simplest way to do that? Please give your suggestions.

You can store it as a straightforward VARCHAR2 column or use the data type of XMLType. This will make it easier to query using XQuery directly if that is something you envisage.

This is Oracle's XML developer guide - https://docs.oracle.com/cd/B28359_01/appdev.111/b28369/xdb_xquery.htm#CBAGCBGJ


You could just simply get the previous and next measure values with LAG and LEAD, and filter based on them:

select road_id, vertex_index, measure_val from (
  select
    r.road_id, r.vertex_index, r.measure_val, e.from_measure, e.to_measure,
    lag(r.measure_val, 1, r.measure_val) over
      (partition by e.road_id order by r.vertex_index) as prev_measure_val,
    lead(r.measure_val, 1, r.measure_val) over
      (partition by e.road_id order by r.vertex_index) as next_measure_val
  from
    road r join event e on (r.road_id = e.road_id)
) where prev_measure_val <= to_measure and next_measure_val >= from_measure;


How I can place connection metadata in order to trace my queries on RDS performance insights fot my postgresql db? In my infrastructure I need to trace my queries to the execution location from AMAZON performance insights in case of a huge database load so I can refactor them. Each query is dynamically generated hence in my codebase are hard to be traced, also we are in a refactorring process as well.
Therefore I want to place on each query extra metadata so I can trace them better from my RDS perfomrance insights panel in case of a high load. Do you have any idea how I can do this?
The trace needs to be in the following 3 axis:

Project
File
Line

So if I could provide this info at query execution level in my code I could trace them better.

In many cases, the application_name setting is shown for connections. If your database driver does not allow to set it when creating the connection, you can execute
SET application_name = '...';

at any time.
If your panel shows only the query text, then you have to add the information to the query text:
/* Hi, Mom! */ SELECT 42;


Finding Missing Inverse Relationships I'm struggling with a query which I feel should be straightforward but I just can't get my head around it.

We have a single table that holds relationships between different parties.  The table SHOULD contain a row for each relationship.  However, we've noticed that some relationships are missing and we want to rebuild those.  Essentially there should be two rows per relationship, the relationship and the inverse.

So, a healthy table should look like this...

Id | PartyId | OtherPartyId | RelType | RelStatus
1   1111      2211            1         1
2   2211      1111            1         1
3   3344      4444            1         2
4   4444      3344            1         2

But, we have a situation like this...

Id | PartyId | OtherPartyId | RelType | RelStatus
1   1111      2211            1         1
2   2211      1111            1         1
3   3344      4444            1         2
4   5555      2224            1         2
5   2224      5555            1         2

The inverse of the relationship between party 3344 and 4444 is missing, so I want to return that row only so that I can insert the inverse relationship.

The 'id' column is immaterial as it is only an identity column, but RelType and RelStatus are important as these need to be the same for the inverse relationship - example being that there could be a relationship that DOES exist between 4444 and 3344 but it is of a different type and status so we should ignore that (unless of course it has a missing inverse too!).

I tried to create a composite key system to try and identify the missing rels but I found it brought back duplicates in some instances:

            SELECT
        CASE WHEN [PartyId] < [OtherPartyId]
            THEN CAST([OtherPartyId] AS NVARCHAR(50)) + '.' + CAST([PartyId] AS NVARCHAR(50)) + '.' + CAST([RelType] AS NVARCHAR(50)) +  '.' + CAST([RelStatus] AS NVARCHAR(50))
            ELSE CAST([PartyId] AS NVARCHAR(50)) +  '.'  + CAST([OtherPartyId] AS NVARCHAR(50)) +  '.' + CAST([RelType] AS NVARCHAR(50)) +  '.' + CAST([RelStatus] AS NVARCHAR(50))
        END AS CompKey
        INTO #partyinvs
        FROM PartyRelationships;

        SELECT  CompKey
        FROM    #partyinvs
        GROUP BY CompKey
        HAVING  COUNT(*) < 2

I hope this makes sense?

Thanks,
David

Using EXCEPT could help you write this query.

In short, if any rows of the first select do not have matches with the inverse + the correct RelType  & RelStatus  , return them.

SELECT PartyId , OtherPartyId, RelType , RelStatus
FROM dbo.Table
EXCEPT
SELECT OtherPartyId , PartyId, RelType , RelStatus
FROM dbo.Table;

DB<>Fiddle

Remember that if there are duplicate rows (E.G. 3 rows with an inverse relationship) these will not be returned.

Example

DDL

CREATE TABLE dbo.bla(ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
                     PartyId int,
                     OtherPartyId int,
                     RelType int,
                     RelStatus int);

DML

INSERT INTO dbo.Bla(PartyId , OtherPartyId , RelType , RelStatus)
VALUES
(1111,2211           ,1       ,1),
(2211,1111           ,1       ,1),
(3344,4444           ,1       ,2),
(5555,2224           ,1       ,2),
(4444,3344           ,2      ,2),
(1111,2211           ,2      ,2);-- different type or status

Query

SELECT PartyId , OtherPartyId, RelType , RelStatus
FROM dbo.Bla
EXCEPT
SELECT OtherPartyId , PartyId, RelType , RelStatus
FROM dbo.Bla;

Result

PartyId OtherPartyId    RelType RelStatus
1111    2211            2         2
3344    4444            1         2
4444    3344            2         2
5555    2224            1         2

Another solution could be using NOT EXISTS, E.G. When you also need the Id field.

SELECT Id,PartyId , OtherPartyId, RelType , RelStatus
FROM dbo.Bla b
WHERE NOT EXISTS
(
SELECT *
FROM dbo.Bla b2
where b.OtherPartyId = b2.PartyId
AND   b.PartyId = b2.OtherPartyId
AND b.RelType = b2.RelType
AND b.RelStatus = b2.RelStatus
);

DB<>Fiddle


Best practices for configuring the Drive/Filegroup setup for Microsoft SQL 2008 I am a software developer for a small company. I wish we had a dba but that is not on the cards at this point. I was working on migrating our 75GB SQL Server 2000 database to SQL Server 2008 R2.

The new (Windows 2008 R2) server has 64GB RAM and 8 x 72GB 10k SAS drives. I made the suggestion of configuring the 8 drives into 4 RAID 1 drives, each with a single partition and separating out the data as follows:

OS
SQL Transaction Log
Largest database tables (take up about 30GB) in separate filegroups
The remainder of the database in a separate filegroup.

Our network guy said this would not work and that using RAID 10 would work best.

I had set up a test computer to test my suggestion. I tested several scripts to test the performance of significant read and write operations. The test system would be slow the first few executions, but then scripts that normally run 30 seconds would run in less than a second.

The network guy had also had me talk with a dba friend of his who also suggested 1 large RAID 10 who said there would be I/O issues using RAID 0 and having separate filegroups on separate partition.

We have now been using the new server on RAID 10 for over a year now and run fine, but I always feel like we might be missing out on some performance using what seems like a very basic configuration.

Was my idea a bad one? Are there best practices for this sort of thing?

I'm going to assume that, as TomTom went through in detail, some of your hardware acronyms are incorrect regarding your actual server (which you apparently already have).

I'm also assuming that your reference to a "75GB database" indicates you have one database of 75GB that's your primary DB.  This should have only one log file.

Assuming you have 8 disks on modern hardware, one generalized maximum performance setup would be:
2 drives in RAID1 (mirroring) for OS
4 drives in RAID5 for all data files and most log files other than the primary DB's log file.
2 drives in RAID1 (mirroring) for your primary DB's log file.

RAID5 is surprisingly fast on modern hardware, and 4 disks in RAID5 gives 3 disks worth of storage.

The primary DB's log file is by itself so that it really is sequential access - it should be the only file on that entire raidset.  If you had many log files, the drive heads would have to jump from one to the other, which is not sequential.

On reading the additional information in the updated question: a 72GB SAS drive clearly cannot hold a 75GB database even without accounting for growth.  Either a 3 or 4 drive RAID5 or a 4 drive RAID 10 is the bare minimum, given the new hardware information.  For any one specific case, try a few configurations and benchmark them - but with 64GB of RAM on a 75GB database, I'd expect almost the entire database to be in buffer, rendering disk performance much less important; write performance for the transaction log will be one of the first things to watch.


Getting total number of payments and percentage I have a payments table with the following fields:

id (int)
status (successful, failed, refunded)
amount (int)
pdate (Y-m-d format)

I need to write a query to get, for each day how many payments were successful, how many failed and how many were refunded, with the percentage for that day. My current query is this:

select pdate, status, count(*) as total
from payments
group by pdate, status

The query successfully returns how many payments were processed on each day and the status:

2019-04-01,  successful,  10
2019-04-01,  failed,       5
2019-04-01,  refunded,     5
2019-04-02,  successful,  12
2019-04-02,  failed,       5
2019-04-02,  refunded,     3

But I don't know how to make it calculate the percentage for each payment for each day. For example, for 2019-04-01 there were 20 payments so, the percentage of successful payments = 50%, failed = 25% and refunded = 25%.

How can I make it return this?

2019-04-01,  successful,  10, 50%
2019-04-01,  failed,       5, 25%
2019-04-01,  refunded,     5, 25%
2019-04-02,  successful,  14, 58.3%
2019-04-02,  failed,       4, 16.6%
2019-04-02,  refunded,     6, 25%

My problem is how to make it count the total number of payments on each day so I can calculate the percentage?

Thanks in advance!

SELECT pdate,
       COUNT(status) total,
       SUM(status='successful') successful,
       SUM(status='failed') failed,
       SUM(status='refunded') refunded,
       SUM(status='successful')/COUNT(status)*100 successful_percent,
       SUM(status='failed')/COUNT(status)*100 failed_percent,
       SUM(status='refunded')/COUNT(status)*100 refunded_percent
FROM payments
GROUP BY pdate


How to get a list of stored procedure along with their execution count in oracle 12c? Hi I am new to Oracle 12c, though I was working on SQL Server for a long time.

Is there a way to get a list of stored procedures with their execution count in oracle 12c.

We can get it using DMV in SQL Server (various versions). But I am looking for it in Oracle 12c.

You can list them with this query (similar to sys.procedures):

SELECT * FROM USER_PROCEDURES

For execution count, I think you will have to use Audit functionnalities.


pgAgent service for PostgreSQL does not run OS: Windows 10

PostgreSQL 11.4

I Installed PgAgent with Stackbuilder setup.

In Windows services; PostgreServer service is running. However, the pgAgent service is stopped. When I try to start pgAdmin service from there I get this error message:

The PostgreSQL Scheduling Agent - pgAgent service on Local Computer started and then stopped.
Some services stop automatically if they are not in use by other services or programs.

I have the pgAgent service to log On As -->LocalSystem  (Note, I have also tried to change it to an windows administrator account)

When opening pgAdmin 4 I can see the pgAgent Jobs tab as well as create jobs.

Acording to the description on this page ,one should create an extension as well. However, when pgAgent was installed , there wasen't copied any extension files to %\PostgreSQL\11\share\extension so I can not create the extension in pgAdmin

Any advice on how I get the pgAgent service to run my jobs I have createn in pg Admin?

EDIT: On This thread They state that editing pgpass.conf in c:/user/user/AppData/postgresql helped. I have no such folder nore file. The only postgres related folders I've found under C:\Users is C:\Users\user\AppData\Roaming\pgAdmin

EDIT2:

I opened CMD as an admin and run cd %APPDATA% to find correct folder.
I created the pgpass.conf inside newly created folder postgresql in appdata --> C:\Users\user\AppData\Roaming\postgresql\pgpass.conf.
I added that file as a system variable PGPASSFILE (Source1 and Source2).

That did not change anything either.

Cholud I do some editting in REDEDIT? Source3

I'm not sure what I did, but I have the service running now. I guess it is a combination of little of everything

Open CMD as administrator
browse to pgagent.exe

cd C:\Program Files\PostgreSQL\pgAgent\bin

Remove the pgAgent service (if pgAgent service is installed, but does not work)

pgAgent REMOVE pgAgent

Then I installed the service again.

pgAgent INSTALL pgAgent -u WindowsUser -p WindowsPassword hostaddr=127.0.0.1 dbname=postgres user=postgres password=postgePassword

(Not sure if the following steps are needed but) Open Windows services
There you can confirm the windows account that runs the service.
open up pgpass.conf in %APPDATA/postgresql and add the line

127.0.0.1:5432:*:windowsUsername:windowsPassword

add pgpass.conf as system variable PGPASSFILE


InnoDB Concurrent writes being ignored Sorry if this isn't the right place, this is my first time posting anything here. Anyway, I know I'm not supposed to but I can think of no other way.

I need to use a small MySQL table (70 rows x 6 columns) as a queue.

I'm writing a python application that requires a work queue to be shared between process windows (not sure what the proper name for them is).

Each job is repeatable, each use must be recorded and cleared at regular intervals (so each job is "weighted" so usage is evenly and fairly distributed). I attempted to base it on another work queue where each job is NOT repeatable, but it seems that multiple writes to the database (up to 19 at once per 12 seconds) are not properly being counted up.

Is there an alternative to doing something like this?

Perhaps some kind of cache sitting between python and MySQL that would convert many single "job + 1" updates into a singular "job + 19"?

I assumed being on a shiny new NVMe drive and a more than sufficient buffer_pool_size would make it plenty fast enough to handle that, but instead of counting up properly, over the course of 60 seconds it may reach 9, instead of 100+ where it should be.

"Don't queue it, just do it".  -- This is my advice on using queuing mechanisms in MySQL.  The effort to enqueue and dequeue small tasks is often more than simply doing the task.  However there are exceptions...

Is this a correct understanding of your "queue"?  You have exactly 70 tasks and they need to be done at times based on various external criteria.  That is, it is not really a typical queue, which can be thought of as "first in, first out".  However, sometimes you may have several of the 70 ready to go, but not enough resources to do all at once. ??

In that case, let's call it a "task status" table.  Dozens of updates per second feed status into the table.  Worker(s) issue a SELECT to see what to do now.  If the computation is too complex to do in a WHERE...ORDER BY...LIMIT 1, then simply readk all 70 and make the decision in a 'real' programming language.  (Yes, I have implemented such.)  Keep in mind that the focus of a database table is to hold raw data.  If you can process the data in a SELECT, that is just a bonus.

"Transactions"...  Keep in mind that transactions in InnoDB are not really designed to last more than a few seconds.  So, if the tasks take longer than that, you need to 'assign' a task to a 'worker'.  Better yet, have the 'worker' assign the task to itself.  Here's one efficient way:

-- with autocommit=1; that is, no need for transactional semantics
UPDATE tasks
    SET worker = $me
    WHERE worker IS NULL
    ORDER BY ...
    LIMIT 1;
if Rows_affected() > 0, then I got a task, else none to get.
SELECT task_id FROM tasks
    WHERE worker = $me;   -- find out which task I got.

When finished:

UPDATE tasks
    SET worker = NULL
    WHERE worker = $me;

Beneficial:

INDEX(worker)

Sorry if I have rambled off in the wrong direction; perhaps there are useful clues anyway.


SQL Server Ignoring Instance name when using port number of different instance I saw a strange case recently and still clueless on what should be the reasoning behind it. On one of my development server, two SQL server instances are running - one is default and other one is UAT instance.
Recently we made the change to the default port of both of these instances and same was communicated to development team and other stakeholders to start their change and test the same. In order to not impact their existing application, port 1433 was kept as it is on default instance. So, below are details of server and port to connect:
Connection for Default --> Server_Name,35683
Connection for Named(SIT) instance --> Server_Name\SIT,35685

Both the instances have similar databases however different set of data as they point to different environment.
One of senior developer was using the connection as Server_name\SIT,35683 meaning mismatch of instance and port. He was using SIT instance however port of default instance and it was connecting him to default instance.
This was caught when I was at his desk and we were testing some code and I noticed this because my change was not reflecting at default instance as I had made changes at SIT instance.
Can someone please explain this behavior and how to sort this out?

A port number specification overrides the instance name, effectively ignoring the instance name altogether. For example, you can connect using SSMS using "YourSqlServer\badname,1433 even if the instance name is invalid.

When the port number is omitted with an instance name, the client queries the SQL Server browser to determine the port needed.


Creating sequence for longer numeric values I need to create a database model that includes a field to store 20 digit unique values. Which will be used by many clients over an API.

The value must be 20 digit long, numeric and unique. Best approach seemed to create an auto increment big-int field, but big-int supports values up to 9223372036854775807 (19 digits). Using numeric(20,0) is useless since although numeric supports larger values, Postgres 8.4 do not let me create a sequence and raises error

CREATE SEQUENCE my_sequence_name INCREMENT BY 1 NO MINVALUE NO MAXVALUE START WITH 10000000000000000101;

  ERROR:  value "10000000000000000101" is out of range for type bigint

For data integrity, I must handle this within POstgresql. What is the efficient way to handle this?

I am using Postgresql 8.4 and Django 1.3

UPDATE: I guess it is better to make a clarification...
I am trying to find a way to solve this within DBMS. So any non-database solutions will be avoided unless I had no other choice. Rest is the reason of my question:

I am making an API which communicates with a Bank for 3D secure operations. The bank API required a 20 digit unique numeric identifier for the transaction, so I decided to create an ID value that I can both use for the bank API and my API that will use that table etc.

Stating from 10000000000000000101 is because, bank API want it to be a valid 20 digit integer value, so It must be at least 10000000000000000000.

Solving this within the DBMS is best for performance and data integrity. An idea I thought  was storing the value as an auto-increment big-integer in the database field, and manually parse it to string and make some make-up like:

bid = str(23434545) # value in the DB field
final_value = '1' + '0'* (19-len(bid)) + bid

which make final_value a 20 digit integer that starts with 1 and end with my auto-incremented value. and middle is filled with zeros.

But in case of dealing with both SELECT and INSERT queries, Keeping the logic out of the DBMS is bas, so so bad.

If you can live with losing some values to the maximum value, you could combine a sequence with a fixed offset to get the 20 digits. I would also define a check constraint on the table to to make sure that accidental inserts without using the default value insert the wrong value:

create sequence my_sequence_name;
create table foo
(
   id numeric(20,0) default 10000000000000000001 + nextval('my_sequence_name'),
   constraint check_range check (id between 10000000000000000001 and 99999999999999999999)
);

That will give you a maximum generated value of 19223372036854775808 which doesn't use the complete range of allowed values, but it might be enough for your needs.


How to use temporary variable in Update query? How can I set a variable while doing an Update statement?

I can't seem to figure out the syntax. I am using this @tp variable for holding calculated result and it will be used in when condition.

I want something like this:

UPDATE users IU
INNER JOIN activity_stats DAS
    ON (IU.user_id=DAS.for_user_id) SET
    @tp:= IU.total_subscription_time - (unix_timestamp()- IU.start_date),
    IU.time_period_left = CASE WHEN @tp > 0 THEN @tp ELSE 0 END,
    IU.used_time = (unix_timestamp() - IU.start_date),
    IU.status = CASE WHEN @tp > 0 THEN 'A' ELSE 'I' END,
    DAS.total_time_used = DAS.total_time_used + IU.used_time
WHERE IU.time_period_left > 0
  AND IU.status='A'

but it's saying the syntax is wrong:

According to the manual, the SET clause expects a list of column assignments, meaning that the left side of each assignment must be a column reference, not a variable reference.

However, you can use variable assignment in the right hand side, where an expression goes, for instance like this:

IU.used_time = (@usedtime := unix_timestamp() - IU.start_date),

In the line above, the result of unix_timestamp() - IU.start_date is assigned to @usedtime, then the entire variable assignment expression evaluates to the same value, which is eventually stored into the IU.used_time column.

The variable assigned this way can then be used in other expressions that follow. Your update statement, therefore, could be rewritten e.g. like this:

UPDATE
    users IU
    INNER JOIN activity_stats DAS
        ON (IU.user_id=DAS.for_user_id)
SET
    IU.used_time        = (@usedtime := unix_timestamp() - IU.start_date),
    IU.time_period_left = CASE WHEN (@timeleft := IU.total_subscription_time - @usedtime) > 0 THEN @timeleft ELSE 0 END,
    IU.status           = CASE WHEN @timeleft > 0 THEN 'A' ELSE 'I' END,
    DAS.total_time_used = DAS.total_time_used + @usedtime
WHERE
    IU.time_period_left > 0
    AND IU.status='A'
;

Note that, in addition to fixing the syntax in general, I have moved the IU.used_time column assignment to the top, so that the result of unix_timestamp() - IU.start_date could be re-used in the expression for @timeleft. You can keep your order of assignments, of course. Note, however, that IU.used_time in the last assignment

DAS.total_time_used = DAS.total_time_used + IU.used_time

may still hold the old value. So, even if you decide to keep the order of assignments, I would still recommend that you use an expression rather than a column reference there if you want to use an updated value in that assignment.

See a live demo of this syntax at dbfiddle.uk.


Get Data for user excluding another user blocked with its time interval Two tables

Message

  ID | Message | FromUserId | ToUserId  | DateCreated
    1  | M1      |     A      |   B       | 2017-10-05 12:00:00
    2  | M2      |     A      |   B       | 2017-10-05 12:05:00
    3  | M3      |     B      |   A       | 2017-10-06 12:05:00
    4  | M4      |     B      |   A       | 2017-10-06 12:05:00
    5  | M5      |     A      |   B       | 2017-10-07 12:05:00
    6  | M6      |     A      |   B       | 2017-10-07 12:05:00
    7  | M7      |     B      |   A       | 2017-10-08 12:05:00
    8  | M8      |     B      |   A       | 2017-10-08 12:15:00
    9  | M9      |     B      |   A       | 2017-10-08 12:18:00
   10  | M9      |     B      |   A       | 2017-10-09 12:05:00

BlockedUsers

ID | FromUserId | BlockUserId | StartBlockDate       | EndBlockDate
1  |    A       |   B         | 2017-10-06 11:55:00  | 2017-10-07 11:55:00
2  |    A       |   B         | 2017-10-08 10:05:00  | 2017-10-08 12:07:00
3  |    A       |   B         | 2017-10-09 11:55:00  | NULL

Now when B tries to retrieve messages it gets whole messages of Message table

but when A retrieves messages, its result should be

Message

ID | Message | FromUserId | ToUserId  | DateCreated
1  | M1      |     A      |   B       | 2017-10-05 12:00:00
2  | M2      |     A      |   B       | 2017-10-05 12:05:00
5  | M5      |     A      |   B       | 2017-10-07 12:05:00
6  | M6      |     A      |   B       | 2017-10-07 12:05:00
8  | M8      |     B      |   A       | 2017-10-08 12:15:00
9  | M9      |     B      |   A       | 2017-10-08 12:18:00

I tried with below query

Declare @UserId int = A
SELECT
  *
FROM
  dbo.Message AS m
WHERE
  NOT EXISTS
  (
    SELECT
      *
    FROM
      dbo.BlockUser AS bu
    WHERE
      m.DateCreated >= bu.BlockStartDate
      AND m.DateCreated < bu.BlockEndDate
    and bu.FromUserId = @UserId
  )
;

I am stuck with the condition for user being blocked and another user.

I took reference of question:get data excluding time intervals

but could not figure

The best way to handle this would be to make the default value for the EndBlockDate be an arbitrary date far in the future instead of NULL. For example, if your BlockedUsers table was like this:

BlockedUsers

ID | FromUserId | BlockUserId | StartBlockDate       | EndBlockDate
1  |    A       |   B         | 2017-10-06 11:55:00  | 2017-10-07 11:55:00
2  |    A       |   B         | 2017-10-08 10:05:00  | 2017-10-08 12:07:00
3  |    A       |   B         | 2017-10-09 11:55:00  | 2150-12-31 23:59:59

Then your query above would correctly filter the information.


MySQL InnoDB Insertion Faster Than MyISAM I wrote code that inserts about 15 millions rows into tables in MyISAM and InnoDB for comparsion.

MyISAM and InnoDB are set up with no optimization configuration in /etc/mysql/my.cnf.

And tables are created with no indexes.

Insertion are done using statements like

INSERT INTO Table (columnA, columnB, columnC) VALUES (?, ?, ?)..."
(10,000 (?, ?, ?)s)

db.Exec(stmt, valueArgs...)

with valueArgs being the actual VALUES
 It is widely believed that MyISAM is faster than InnoDB in insertion.

Baron Schwartz, author of the book High Performance MySQL, explained why in this post Mysql: Insert performance INNODB vs MYISAM.

However, in my experiment, I found out that InnoDB is actually faster than MyISAM. Could anyone give me some insights?

mysql  Ver 14.14 Distrib 5.7.30, for Linux (x86_64) using  EditLine wrapper

InnoDB has been faster than MyISAM at everything for at least a decade. This shouldn't be a surprise in 2020. There hasn't been a compelling case for using MyISAM in a very long time.

On a workload that involves any concurrency, the difference will be even bigger.

Also, note that Baron's post is from 2012 - and it was probably borderline in terms of accuracy even back then.


How to Choose an SQL Database for Simple Data Preparation & Analytics In the interests of not making this a shopping list: please advise on "how" to choose a the right tool, as opposed to which is the best. E.g:

Which questions should I be asking myself / client?
What features should I be looking for?

Draft use case:

easy setup / installation + easy to use
size: 5-10gb database with 10-30 tables of varying sizes
users: handful of beginner-intermediate SQL analysts
load: 10-20 short-ish SQL scripts per day
usually one user per table at any one time
aspiration that SQL scripts should not take long to run
'fast' and 'flexible' more important than 'safe'
abiliy to import/export from/to CSV
low cost(!)

Essentially, we are a small team of (somewhat) geographically distributed data analysts looking for platform to do data preparation - we are not app developers. Data is exported for analysis in MS Excel.

Options considered so far:

MySQL with phpMyAdmin on WAMP on my 3-yr old laptop, which was OK
for one user but slow for some queries on larger datasets
Google Cloud SQL - I have set up Tier D0 instance and connected
phpMyAdmin from Compute Engine but found it slower than my desktop
so far. Wasn't so easy to set up.
Visual Foxpro - in the past with similar issues to (1) but more flexible; expensive.
MS Access - some use but risk of 2gb size constraint
Valentina Studio with SQLite - still testing; documentation confusing

What suggestions does the community have? Particularly intrested in comments on:

Desktop vs. Server vs. Web/Cloud (cloud seems like overkill, but option to access database anywhere is attractive)
SQL vs. other approach / language (SQL is easy to learn)

What other questions should I be asking but am not?

Thanks in advance for your thoughts.

Questions to ask could include:

What fits well with your current skills? (That might make it 'easier' to use.)
Is your focus on getting something working soon, or on developing new skills?
What is the cost of your choices (money, time, effort, unfamiliarity, etc)?

With your list you do emphasize cost as a factor and the database is not expected to be huge.  So, you can use the free version of some vendor product (such as SQL Server Express), or use open source offerings (such as MySQL, PostgreSQL, etc).

But you will pay, of course, for any choice.

Once you know where you want to spend your resources (which only you can answer) you should be able to discard some of the options.


memory usage oracle query We see Direct path read temp frequently in the database. It started with one query and later followed up to all the queries running in database at that moment. I understand that data is read from temporary tablespaces and this event is usually caused by a sort operation that cannot be complete in memory and requires storage access. First response is to tune the query, but the same query runs better previous day or next day.

My question, is there a way to check how much memory is used by a query or process at that moment? Or anything related to finding out the memory usage would be great. I appreciate your help in advance. Queries are running parallel and it is dataware house environment.

You find all available metrics in

V$SQL_MONITOR - disk rate, requests and consistent gets per execution per session - here you also found if parallel execution is degraded
V$SQL_PLAN_MONITOR - disk and memory data per session, execution and plan line
V$ACTIVE_SESSION_HISTORY - 1 s sampled data per session, execution and plan line
V$PROCESS - current counters for process memory usage (join to session using PADDR = ADDR)

If you are familiar with execution monitor in Enterprise manager or other Oracle monitoring tools, they use exactly same views. You just need to join the data from them and model into an output you want.

There one more view useful for finding out parallel skew - V$PQ_TQSTAT.


How RDBMS solve C10K problems, it's also load balance? I know AP server meet C10K problem can use Load Balance to be solved.
But RDBMS server in my experience is very difficult to make load balance, especially for writing db, it should meet the client 10000 problem
Therefore there's a question 'How do RDBMS solve C10K problems?" It's also load balance?

This is an extremely broad question, but anyway:
Typically this is solved using connection pooling. To serve tens of thousands (or millions) of concurrent users, you typically need way less concurrent connections on the database ("hundreds"). This is especially true for web-applications.
Web-Applications (where you get that number of concurrent users) are typically also more read intensive than write intensive. So load-balancing read requests already takes away a lot of load.
For "traditional" Client/Server applications, you typically don't have that high number of concurrent database sessions, but for those, this is very often dealt with by scaling vertically: increase number of CPUs, increase RAM, increase number of harddisks to allow more concurrent work to be done (high-end servers with 512 CPUs and more and disk arrays with thousands of disks are not unheard of)
Different databases products provide different scale out strategies. Multi-Master setups (e.g. "shared nothing" architectures) are one way to do that. But the details on how that is implemented are completely dependent on the concrete DBMS product.


Access for multiple domain users to SQL Server using Windows Authentication We are delivering SQL Server 2012 SP1 Express edition as part of database with our application. We setup SQL Server express edition as silent installer that user can choose to install or not.

However, I am seeing problem with windows authentication when Domain\User1 setup  SQL Server and Domain\User2 on same machine tries to connect to the same instance created by User1. Though, User2 can log in to the instance created by User1, User2 can't create database or access database created by User1. Following is the error message shown when User1 tries to access database.

I know that we can add domain users when installing SQL Server manually. However, because we should setup SQL Server as silent installer with command-line parameter, We can't add other domain users. Is there anyway that we can allow multiple domain users to connect to database while still using Windows Authentication?

For troubleshooting purposes you could fine-tune your deployment after installing SQL Express by attaching this one-liner. Feel free to get more granular with security as needed--perhaps a more exclusive user group besides the local USERS group (and DB_OWNER--hopefully is overkill and can be something much less):

sqlcmd -E -S %COMPUTERNAME%\SQLEXPRESS -Q "CREATE LOGIN [BUILTIN\USERS] FROM WINDOWS; USE <your  database>;CREATE USER [BUILTIN\USERS];EXEC SP_ADDROLEMEMBER DB_OWNER,[BUILTIN\USERS]"

p.s. be sure and change  to the actual database name of the local SQL Express database. And if SQLEXPRESS is not the actual instance name--change that too. There are alternate syntax variants available as you progress up the SQL 20XX versions.


Is there a psql equivalent of bash's reverse-search-history? I am very fond of bash's reverse-search-history (C-r) (command-line feature):

  Search backward starting at the current line and moving ‘up’ through the history as necessary. This is an incremental search.

When I hit control-r in bash, I get the following:

(reverse-i-search)`':

And I am able to type characters of previously executed commands, honing in on what I am looking for. If I make a mistake, I can enter control-h to erase a character (equivalent of backspace).

This command-line functionality makes it extremely easy to refer to previously executed commands. I am wondering if such a capability exists for psql. I find it extremely inefficient to rely on just control-p and control-n.

For what it's worth, I am using PostgreSQL 9.3.1 on x86_64-apple-darwin13.0.0, compiled by Apple LLVM version 5.0 (clang-500.2.79) (based on LLVM 3.3svn), 64-bit - obtained via Postgres.app.

A conversation about this on GitHub led to an interim "quick fix" solution:

  open ~/.editrc and add:

  bind "^R" em-inc-search-prev

Apparently the bigger issue is that PostgresApp uses libedit instead of libreadline. And supposedly libedit lacks some command-line features of libreadline, so until PostgresApp is compiled against libreadline, just use the above quick fix.


Best index strategies for read-only table? I have a table in SQL Server which has following characteristics:

will contain about 1.2M records.
first is empty, will be inserted in batch many times (precisely, 64 times), 20-100k records/each
the data will not be updated or deleted, just be queried
the number of concurrent query is high, each query return only one record
there's nothing to do with the ID (because of the records are not updated or deleted)
the key of queries is two field, PROVINCE_ID and CANDIDATE_NUMBER (unique across the table)

I'm thinking about creating clustered index on the ID column, and non-clustered index in (PROVINCE_ID,CANDIDATE_NUMBER). will it the best choice? Can you give me another suggestion? please explain why should I do that?

Thank you so much!

If the ID column is not used for data retrieval, I suggest you place the clustered index on the PROVINCE_ID, CANDIDATE_NUMBER columns instead.

If some columns are read very often compared to others, consider adding an index with PROVINCE_ID, CANDIDATE_NUMBER plus that column. That way the queries can get everything they need from the index itself and don't need to look at the table at all.


User and Schema Recreated during Stored Procedure Creation As mentioned in the subject, a new user and schema are being created when a developer I'm working with creates a stored procedure. Originally I made a user and login for that developer individually but since deleted both. However, whenever they run a script to create a SP a user and schema named after their Active Directory name are recreated and the SP is placed in that "new" schema.

Even when I delete the SP, the user, and the schema, if they create the SP again the user and schema come back.

Does anyone have any insight into this?

This behavior is noted in the "Remarks" section of the CREATE SCHEMA documentation, under "Implicit Schema and User Creation" (emphasis added to highlight what you are experiencing):

  In some cases a user can use a database without having a database user account (a database principal in the database). This can happen in the following situations:

  A login has CONTROL SERVER privileges.
  A Windows user does not have an individual database user account (a database principal in the database), but accesses a database as a member of a Windows group which has a database user account (a database principal for the Windows group).

  When a user without a database user account creates an object without specifying an existing schema, a database principal and default schema will be automatically created in the database for that user. The created database principal and schema will have the same name as the name that user used when connecting to SQL Server (the SQL Server authentication login name or the Windows user name).

  This behavior is necessary to allow users that are based on Windows groups to create and own objects. However it can result in the unintentional creation of schemas and users. To avoid implicitly creating users and schemas, whenever possible explicitly create database principals and assign a default schema. Or explicitly state an existing schema when creating objects in a database, using two or three-part object names.


Running scheduled SSIS pkg with SQL Server 2012 Web Edition I have a client with SQL Server 2012 Web Edition. I believe it has SQL Server Agent, but I see on the following Microsoft page that many integration services are not supported:

https://msdn.microsoft.com/en-us/library/cc645993(v=SQL.110).aspx

My guess is that I could develop an SSIS solution and perhaps run it scheduled by calling it from DTEXEC and scheduling that as a job in the Server Agent.

I agree that getting the client on Standard Edition would be best, but if that is not do-able, can they still run a scheduled SSIS solution scheduled? (Solution is an incremental load from flat files)

When you attempt to run the package from dtexec, you can test this yourself, you're going to get an error that basically says you failed the licensing check and it's not going to run the packages outside the context of Visual Studio (BIDS/SSDT-BI).

Once you have a licensed for a version that supports SSIS, then you'll need to install the Integration Services Service otherwise, you'll get an error in the form of "To run a ssis package outside of sql server data tools you must install {TASK NAME} of Integration Services or higher" See also Error: "To run a ssis package outside of sql server data tools you must install [send successful email] of Integration Services or higher."


Do SSDs reduce the usefulness of Databases I only heard about Robert Martin today, and it seems like he's a notable figure in the software world, so I don't mean for my title to appear as if it's a click bait or me putting words in his mouth, but this is simply how I interpreted what I heard from him with my limited experience and understanding.

I was watching a video today (on software architecture), on a talk by Robert C. Martin, and in the latter half of the video, the topic of databases was the main focus.

From my understanding of what he said, it seemed like he was saying that SSDs will reduce the usefulness of databases (considerably).

To explain how I came to this interpretation:

He discussed how with HDDs/spinning disks, retrieving data is slow. However, these days we use SSDs, he noted. He starts off with "RAM is coming" and then continues by mentioning RAM disks, but then says he can't call it RAM disk, so resorts to just saying RAM. So with RAM, we don't need the indexes, because every byte takes the same time to get. (this paragraph is paraphrased by me)

So, him suggesting RAM (as in computer memory) as a replacement for DBs (as that's what I interpreted his statement as) doesn't make sense because that's like saying all the records are in-memory processed in the lifetime of an application (unless you pull from a disk file on demand)

So, I resorted to thinking by RAM, he means SSD. So, in that case, he's saying SSDs reduce the usefulness of databases. He even says "If I was Oracle, I'd be scared. The very foundation of why I exist is evaporating."

From my little understanding of SSDs, unlike HDDs, which are O(n) seek time (I'd think), SSDs are near O(1), or almost random. So, his suggestion was interesting to me, because I've never thought about it like that.
The first time I was introduced to databases a few years ago, when a professor was describing the benefits over regular filesystem,
I concluded the primary role of a database is essentially being a very indexed filesystem (as well as optimizations, caching, concurrent access, etc), thus, if indexes aren't needed in SSD, this kind of does make databases less useful.

Regardless of that though, prefacing that I'm a newb, I find it hard to believe that they become less useful, as everyone still uses DBs as the primary point of their application, instead of pure filesystem, and felt as if he was oversimplifying the role of databases.

Note: I did watch till the end to make sure he didn't say something different.

For reference:
42:22 is when the whole database topic comes up,
43:52 is when he starts off with "Why do we even have databases"

This answer does say SSDs speed DBs up considerably.
This question asks about how optimization is changed.

To TL;DR my question, does the advent of widespread SSD use in the server market (whether it's upcoming or has happened already) reduce the usefulness of databases?

It seemed like what the presenter was trying to convey was that with SSDs, one can store the data on disk, and not have to worry about how slow it would be to retrieve it as with older HDDs, as with SSDs, seek times are near O(1) (I think). So, in the event of that being true, that would hypothetically lose one of the advantages it had: indexing, because the advantage of having indexes for faster seek times is gone.

There are some things in a database that should be tweaked when you use SSDs. For instance, speaking for PostgreSQL you can adjust effective_io_concurrency, and random_page_cost. However, faster reads and faster random access isn't what a database does. It ensures

ACID (Atomicity, Consistency, Isolation, Durability)
Some form of concurrency control, MVCC (Multiversion concurrency control)
Standardized access for libraries (XQuery, or SQL)

He's just wrong about indexes. If the whole table can be read into ram, an index is still useful. Don't believe me? Let's do a thought experiment,

Imagine you have a table with one indexed column.

CREATE TABLE foobar ( id text PRIMARY KEY );

Imagine that there are 500 million rows in that table.
Imagine all 500 million rows are concatenated together into a file.

What's faster,

grep 'keyword' file
SELECT * FROM foobar WHERE id = 'keyword'

It's not just about where data is at, it's about how you order it and what operations you can do it. PostgreSQL supports B-tree, Hash, GiST, SP-GiST, GIN and BRIN indexes (and Bloom through an extension). You'd be foolish to think that all of that math and functionality goes away because you have faster random access.


Does our application work with sql-server AlwaysOn? This is the question a client of ours is asking. For now I have given a generic response that we will support this however they should provide us with a test environment.

Our application is using .net and we allow clients to configure our server service with the database instance name and username/password (we use sql-server authentication).

From what I understand all they will need to do is configure our application with the cluster instance name instead of the hostname of the database server. Reading the Prerequisites that Microsoft provide it seems like it will be completely transparent for us.

Is this correct? Are there other changes we need to make to our connection string? Any other gotchas?

First, let's clarify terms. AlwaysOn is a Microsoft marketing term which includes both SQL Server Availability Groups and SQL Server Failover Cluster Instances. You should stop using AlwaysOn and be specific for all future communications.

If you are referring to FCIs: the configuration should be completely transparent to your application. They should configure the connection to the virtual network name of the FCI and your application will not notice any difference.

If you are referring to AGs: With AGs, they have the option of configuring an AG Listener or not.

If they are not using a listener then all is well and your application won't notice any difference.

If they are using a listener, in a single subnet, then it is transparent.

If they are using a listener, with multiple subnets (multiple IPs for the listener), then your application will need to support the MultiSubnetFailover=TRUE connection parameter or they will need to modify the RegisterAllProvidersIP Windows property. If one of those aren't used then they will have issues with DNS caching the wrong IP address and might blame the application for failing to connect.

See this article regarding the MultiSubnetFailover parameter.

See this article regarding the RegisterAllProvidersIP property.


Store regular MySQL query results in a timeseries DB I'm looking into installing a timeseries database like InfluxDB or Prometheus to handle data for our Grafana monitoring system.

One of the things I like to do is run a couple of MySQL queries every few minutes to gather business metrics (like number of subscribers or application usage metrics).

Documentation for these timeseries databases feature plenty of examples of how to get MySQL performance metrics into their stores but I can't find any that demonstrates how to store data from queries.

Google search results seem to be completely drowned out by the more typical use case of gathering MySQL performance metrics.

Is this at all possible with either InfluxDB or Prometheus and where can I find information on how to set this up?

You can write a simple script, which would periodically execute SQL query and push its results to a TSDB via one of the supported data ingestion protocols. Various TSDBs support various data ingestion protocols: Influx line protocol, Graphite plaintext protocol, OpenTSDB telnet put protocol, JSON, CSV, etc. For example, all these protocols are supported by VictoriaMetrics - see these docs for details.


MySQL Auto_increment going 2 by 2 I installed MySQL Workbench the other day, accessed my company's database and made myself a table to work with. So far so good. The problem is, I noticed my auto_increment is incrementing 2 by 2. For example:

ID    NAME
1     Paul
3     Jack
5     Louis
7     John
...

When I do SHOW VARIABLES LIKE 'auto_inc%' I get this:

'auto_increment_increment', '2'
'auto_increment_offset', '1'

So I tried setting auto_increment_increment to 1 with:

SET @@auto_increment_increment=1

And after verifying again with SHOW VARIABLES LIKE 'auto_inc%' I confirmed it "worked" with the result:

'auto_increment_increment', '1'
'auto_increment_offset', '1'

But my ID's are still being incremented in 2 by 2.

The first time I did it, it worked well and then I closed MySQL Workbench to realize that when I opened it again, auto_increment_increment was set to 2 again. Now I'm trying to do it again, but it doesn't even seem to work anymore.

Can anyone help me with this, please?

Thanks guys.

The prefix that was used:

SET @@auto_increment_increment=1;

Is the same as:

SET @@SESSION.auto_increment_increment=1;

When modifying this setting, it becomes relevant only in your current session.

To make a more permanent fix try:

SET GLOBAL auto_increment_increment=1;

Is the same as:

SET @@GLOBAL.auto_increment_increment=1;


use fulltext formsof inflectional to generate terms Is there a way to use the FullText query components to generate the list of words in the FullText dictionary that are the inflectional formsof a word?

This query would give me matches based on the query.

select *
from keywords
WHERE CONTAINS(keyword, 'FORMSOF(INFLECTIONAL, migrate)')

I am interested in words that it's using as matches.
I want a list of words like

migrate
migrates
migration
migrations

Essentially the word's inflectional / conjugated forms.

You can find the words that will be used in the match with the sys.dm_fts_parser function in SQL Server.

The Syntax

sys.dm_fts_parser('query_string', lcid, stoplist_id, accent_sensitivity)

I won't go in details for the parameters values, but the "lcid" is important.  In the example below I'm using 1033 which is for English - US.  I've tried with 1036, French - Standard, and for sure I didn't get any results beside the word itself, since the word doesn't exist in the French dictionary.

Example

SELECT display_term
FROM SYS.DM_FTS_PARSER('FORMSOF(INFLECTIONAL, welcome)', 1033, 0, 0)

Result

welcome's
welcomed
welcomes
welcomes'
welcoming
welcome


Resize smallfile tablespace - ORA-01144 I have only 2% free on tablespace UNDOTBS1.
It consist from 2 datafiles.

When I launch

alter database datafile '+DATADF/dbname/datafile/undotbs1.555.23454732' resize 32G;

I got :

  ORA-01144: File size (4194304 blocks) exceeds maximum of 4194303 blocks

How can I extend this tablespace?

You have two options.

Your current limit with smallfiles is 2G.  As you can see there the limit is 4194303 blocks, which is 4194303 x 512 byte block size = 2,147,483,136 bytes = 2G.

  Option 1:

Mount your file systems with the -largefiles option, which will allow you to create 32G and higher.

  Option 2:

You can only add 2G datafiles.

alter tablespace UNDOTBS1 add datafile 'path/filename' size 2G;

The -largefiles option has been the default for many years now, so there shouldn't be a problem for your unix admin to change this.


Extreme storage beasts Intro
I have derived this question from my previous question Single Drive vs Multiple Drives

After getting a very detailed answer from a very helpful member RolandoMySQLDBA and learning the fact that multi drives can help a lot in some cases. I started to look into multi drives for my setup which is just a single bit compared to what I found.

More Info
I found quite huge storages like

http://www.newegg.com/Product/Product.aspx?Item=N82E16811152212

While some videos show even GBs of write speed of these kind of equipment

http://www.youtube.com/watch?v=Xjgw2ENYF6w
http://www.youtube.com/watch?v=PniGlSI900k
http://www.youtube.com/watch?v=I8ToJ4Blw74

Real Question
Providing that there is enough RAM to hold indexes, Can MySQL or other open source RDBMS (not enterprise graded RDBMS like Oracle because its running on SAN is quite common) can run on these beasts easily?

BECAUSE they contain true multi drives setup and dozens of heads are available.

I am sure it would not be that simple because if it would have that easy then why most enterprise setups use commodity servers. So, what problems are faced using these extreme storage solutions?

All one has to do is run these queries:

MyISAM

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1))
recommended_key_buffer_size FROM
(SELECT LEAST(POWER(2,32),KBS1) KBS
FROM (SELECT SUM(index_length) KBS1
FROM information_schema.tables
WHERE engine='MyISAM' AND
table_schema NOT IN ('information_schema','mysql')) AA ) A,
(SELECT 2 PowerOf1024) B;

InnoDB

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,
(SELECT 2 PowerOf1024) B;

These will recommend the right sizes for the MyISAM Key Cache for existing MyISAM and the InnoDB Buffer Pool for InnoDB data and indexes.

While running these SQL queries recommend the buffer sizes, the human factor must still kick in. We must plan data usage based on hardware and frequently accessed data. We must also ask: Of the amount of buffer recommended, how much of my working set will actually reside in memory?

If you configure MySQL to use 75% of RAM for InnoDB, even on a dedicated MySQL server, the OS will get a busy paging to disk. Just because MySQL can use huge numbers for buffer sizes does not mean it's OK to push the limits. Two have two basic storage engines to consider.

Use of commodity hardware merely demonstrates MySQL configurability with noticeable results. Unless you are using 32-bit RHEL (if you are, please stop using it right now, get plenty of coffee, go upgrade), raising buffer sizes in storage beasts can be well trusted.

For example, my employer has a client with 3 DB servers dedicated to MySQL only. Each has 192GB of RAM of which 162GB is the InnoDB Buffer Pool. The working set is actually 170GB. While there is a little swapping and paging going on, the DB performance is astounding. OS has about 30GB of RAM for itself (16GB of RAM disk for temp tables). I am sure that these servers are just little lizards compared to the komodo dragon-sized DB servers out there. MySQL can be up to the task if configured properly.

Keep in mind that InnoDB puts its own checks and balances in place because the current source code limits InnoDB log files to 4GB. This is the case because InnoDB was originally designed with commodity hardware in mind. In light of this, ACID transaction throughput could possibly bottleneck there regardless of the storage beast's hardware setup and the version of MySQL you choose to run.

CONCLUSION

It's OK to push limits on Big Beefy DB Servers. You should always do so with consideration to the OS in terms of Memory, Swap Space, Working Dataset, and number of transactions expected.


How to find out which host / IP address / program performed a delete? We are using SQL Server 2005. In our database some rows are deleted; how can I find the system (host name / IP address), program and date & time of deletion?

You will not be able to find this information after the fact. You'll need to add a trigger and a logging table (or set up an expensive server-side trace, or add manual logging to your data access methods).

Here is a very quick example of how to implement a logging table and a trigger:

CREATE TABLE dbo.TableNameDeleteLog
(
  PK_From_TableName INT,
  Program NVARCHAR(128),
  Host NVARCHAR(128),
  IP VARCHAR(48),
  When DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
GO

CREATE TRIGGER dbo.LogDelete_TableName
ON dbo.TableName
FOR DELETE
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @p NVARCHAR(128), @h NVARCHAR(128), @i VARCHAR(48);

  SELECT @p = s.host_name, @h = host_name, @i = c.client_net_address
    FROM sys.dm_exec_sessions AS s
    INNER JOIN sys.dm_exec_connections AS c
    ON s.session_id = c.session_id
    WHERE s.session_id = @@SPID;

  INSERT dbo.TableNameDeleteLog(PK_From_TableName, Program, Host, IP)
    SELECT PK_Column, @p, @h, @i
    FROM deleted;
END
GO


Having performance issue for an extract date without time to compare with sysdate, I need to get all the rows where its 'time' field is equal to sysdate. The thing is I just want to compare dd/mm/yyyy and as my fields are in the date format I'm using

select *  from myTable where trunc(timefield) = trunc(sysdate)

If there's only one field which is truncated everything works fine, but when I want to execute that request it takes too many times (> 10 minutes and still running).

Is there a way to bypass that?

If you want an index to be used, you should remove the function from the column. You can rewrite the condition (which removes the time part from the two datetimes):

where trunc(timefield) = trunc(sysdate)

to the equivalent (where the function is applied only to sysdate and not the column):

where (timefield >= trunc(sysdate) and timefield < trunc(sysdate) + 1)

This way, the condition is "sargable" and an index on timefield can be used. With the previous condition, a table scan (or a full index scan) was required.


How to change definition of a Postgres 12 GENERATED column How to change definition of a Postgres 12 GENERATED column?

CREATE TABLE test (
    id serial PRIMARY KEY,
    val_a int4 NOT NULL DEFAULT 0,
    val_b int4 NOT NULL DEFAULT 0,
    val_sum int4 generated always AS (val_a + val_b) stored
);

I tried

ALTER TABLE test ALTER COLUMN val_sum TYPE int4 generated always AS (val_a + val_b + 1) stored;
ALTER TABLE test ALTER COLUMN val_sum SET generated always AS (val_a + val_b + 1) stored;

but both give syntax errors.

The alternative is to drop & add column, which works, but I wonder how to simply change it like any other column.

I'm not sure this is the correct answer, but reading the docs about ALTER TABLE I would say: No, you can't.

  where action is one of:

  ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type [ COLLATE
  collation ] [ column_constraint [ ... ] ]

and column_constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  CHECK ( expression ) [ NO INHERIT ] |
  DEFAULT default_expr |
  GENERATED ALWAYS AS ( generation_expr ) STORED |
  GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |
  UNIQUE index_parameters |
  PRIMARY KEY index_parameters |
  REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
    [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

It seems that the only command that allows column_constraint is ADD COLUMN

In fact you can add a constraint name to the computed column:

val_sum int4 CONSTRAINT GA_test generated always AS (val_a + val_b) stored


Get and Put performance testing on Google BigTables (and other integrated DBs) What are some effective ways to perform programmatic performance testing on database operations, especially in environments where the databases themselves do not offer dedicated tools?

For example, in Google App Engine, entire page-loads are evaluated as one operation which may include specific database operations. This problem is also likely present in SQLite and other integrated DBs. As it is difficult to completely abstract the (equivalent of) selects and inserts that need to be tested, are there any recommended database tools to perform more thorough diagnostics on these sorts of queries?

It seems to me your problem is that you are trying to test performance metrics that are not well supported in the underlying db.  This makes it very difficult to compare performance across systems because the underlying approaches are very different.  I don't think it is possible to do apples to apples comparisons just as I don't think you can do an apples to apples comparison of ORDBMS type approaches to RDBMS type approaches.  The performance concerns are just too different and if Stonebraker is right that optimizing an ORDBMS for TPC-C tests misses the point, then for systems that are even further apart it is going to be impossible. (I think he is right there, however, only where ORDBMS functionality comes into play.)

I think what you need to be honest is to look at how you would use each system and build a benchmark tool based on the approach you'd take with each.  Then you can say at least for that workflow that the benchmark shows something specific.  I don't see how you can generalize however.  You could further run it in a profiler in order to get additional information about where the time is being spent in the test run on various engines.

However db benchmarking is very difficult to make meaningful in the best of circumstances and when you are comparing dissimilar systems it becomes impossible to generalize.


Create table on a Master - Master config avoiding replication I have a Master-Master configuration.

What I want is to create an auxiliary table only for a report project so it's not necessary to replicate it.

Is there a specific way to make this in the CREATE TABLE line?

The safest approach is to stay far, far away from trying to filter replication... and tweaking sql_log_bin on and off is a recipe for replication problems, too.  In a replicated environment, the general idea is that both servers have identical data sets, and to whatever extent they don't, your setup becomes exponentially more complex.

There is an easier and safer approach.

First, create the table, with the storage engine you want, on the server where you want it to store data.

Then, on the other server (where you don't want the table to store data), verify that the definition has replicated and that the table exists, and then:

mysql> SET sql_log_bin = 0;
mysql> ALTER TABLE t1 ENGINE=BLACKHOLE;
mysql> SET sql_log_bin = 1;

This will change the table to a Blackhole table locally, without replicating that change back to the other master server.  This is something you only need to do once, when you set up the table.  After that, us it normally.

The Blackhole Storage Engine...

  acts as a “black hole” that accepts data but throws it away and does not store it.

The server with the blackhole table will still churn through the replicated queries involving that table, but with substantially less overhead (writes to the table will be discarded), and SELECT * FROM table will always return... nothing.

You can write to the real table on one server without taking up space or having the data in the table be selectable on the other server.

caveat: if you are manipulating data in other tables based on what you have in this table (INSERT ... SELECT ... UPDATE ... JOIN ...etc.) then you need to be using binlog_format = ROW or abandon this idea altogether, because those queries will not work without this table's data on both masters, if your binlog_format is STATEMENT, and will not necessarily work reliably with MIXED mode either.


What does pg_stat_activity.xact_start mean for autovacuum threads? If I see something like:

db=> SELECT datname,pid,state, (now() - query_start)::interval, query FROM pg_stat_activity where state != 'idle' and query like 'autovacuum%';
 datname |  pid  | state  |    interval     |                        query
---------+-------+--------+-----------------+-----------------------------------------------------
 theseus | 21936 | active | 00:00:00.940774 | autovacuum: VACUUM ANALYZE pg_catalog.pg_constraint
 theseus | 16775 | active | 00:33:57.896069 | autovacuum: VACUUM pg_toast.pg_toast_1234

How should I interpret xact_start vis a vis the vacuum_cost_limit and autovacuum_vacuum_cost_delay? In other words is xact_start the time at which the thread woke up again to do another chunk of vacuum_cost_limit work, or does it represent many chunks?

And a follow-up: my vacuum_cost_limit setting is the default of 200 (very conservative); if we're seeing a very long "transaction" interval and that interval should be measuring a very small amount of work, what could explain the long runtime? It's my understanding that autovacuum doesn't block on other activity.

The xact_start field should be before or equal to query_start, covering the entire duration of the vacuuming of the table.

Your vacuum is slow because your setting of vacuum_cost_limit is very conservative.  It is throttling itself, not blocking on other things.


Use CTE to fetch most recent row for each ID I just recently started learning about the wonders of CTEs and how they can be used, instead of WHILE loops, to optimize performance.
For context, I am automating the process of updating firmware on devices in my company. The devices send UDP data to the server and store the messages into a single table, so the table is quite large
There are 2 modules on each device, high and low frequency and the messages are very similar
Previously, I had temp tables, counters, and a while loop to get the most recent firmware message for each device on each module. I have since used the following CTE to extract the same information (only high frequency code shown, table and column names changed for confidentiality)
;WITH HFCTE (DIdentifier, CachedHFFWVersion, CurrentHFFWVersion, HFFWMessage, HFFWMessageDate) AS
(
    --Get all HF Firmware Messages for each device
    SELECT  udp.DIdentifier,
            LEFT(RIGHT(UDPMessage,7),3),
            RIGHT(UDPMessage, 3),
            UDPMessage,
            DateRecorded
        FROM UDPTable udp WITH (NOLOCK)
            INNER JOIN FirmwareInfoTable fw ON udp.DIdentifier = fw.DIdentifier
        WHERE UDPMessage LIKE '%FWMessage:%'
            AND UDPMessage NOT LIKE '%FWMessage:LF%'
            AND fw.Status = 'HF Pending'
)

-- Get the most recent HF Firmware Message for each device
SELECT cte.DIdentifier,
cte.CachedHFFWVersion,
cte.CurrentHFFWVersion,
cte.HFFWMessage,
cte.HFFWMessageDate
FROM HFCTE cte
    INNER JOIN (
                SELECT DIdentifier,
                    MAX(HFFWMessageDate) AS MaxDate
                FROM HFCTE
                GROUP BY DIdentifier
               ) AS gcte
        ON cte.DIdentifier= gcte.DIdentifierAND cte.HFFWMessageDate = gcte.MaxDate
ORDER BY cte.DIdentifier DESC

The main problem is that before picking out the most recent firmware message using the last SELECT statement, the CTE has to get all FWMessage records for each device, which could be very bad for performance during peak hours.
Is there any way to only pull the one record I need for each device in my initial WITH CTE query? Would it even optimize the overall performance of the query? Should I be using a different method entirely?
P.S. DIdentifier is a PRIMARY_KEY in the FirmwareInfo table and a FOREIGN_KEY (Not to the Firmware table but to the table that holds devices) in the UDP table. The UDP table also has 2 relevant indexes, both non-clustered & non-unique. One includes DIdentifier and UDPMessage, the other includes DIdentifier and DateRecorded. I cannot create a new index for UDP table due to performance constraints

You can try using a window function like ROW_NUMBER() or DENSE_RANK() to generate an ID that represents the latest rows per your sort criteria. Here's an example:
;WITH HFCTE (DIdentifier, CachedHFFWVersion, CurrentHFFWVersionn, HFFWMessage, HFFWMessageDate, SortId) AS
(
    --Get all HF Firmware Messages for each device
    SELECT  udp.DIdentifier,
        LEFT(RIGHT(UDPMessage,7),3),
         RIGHT(UDPMessage, 3),
         UDPMessage,
         DateRecorded,
         DENSE_RANK() OVER (PARTITION BY udp.DIdentifier ORDER BY DateRecorded DESC) AS SortId
    FROM UDPTable udp WITH (NOLOCK)
        INNER JOIN FirmwareInfoTable fw ON udp.DIdentifier = fw.DIdentifier
    WHERE UDPMessage LIKE '%FWMessage:%'
        AND UDPMessage NOT LIKE '%FWMessage:LF%'
        AND fw.Status = 'HF Pending'
)

SELECT cte.DIdentifier,
    cte.CachedHFFWVersion,
    cte.CurrentHFFWVersion,
    cte.HFFWMessage,
    cte.HFFWMessageDate
FROM HFCTE cte
WHERE cte.SortId = 1
ORDER BY cte.DIdentifier DESC

This eliminates the need to join your CTE to itself in a subquery and further upstreams the logic for determining the latest records by date. Also the window functions tend to be rather performant for this kind of problem.


Migrating PostgreSQL 9.1 database to Ubuntu 14.04 I want to migrate a database from PostgreSQL 9.1 installed on an old Debian system to Ubuntu 14.04. The database is making use of triggers and it is a requirement that the triggers be migrated as well, but without having the triggers executed on every row as it is migrated to the new database causing the new database to contain a garbled version of the original data.

The PostgreSQL version in the Ubuntu 14.04 repository is 9.3.

Is there any way I can convert the data from /var/lib/postgresql/9.1 on the old system to be usable on PostgreSQL 9.3?

What I have tried so far

I found an older answer on how to migrate from 9.1 to 9.3. But I did not have much luck using it.

Using pg_dump will as far as I understand require the old database to be running as the data is being dumped, which could lead to updates written during the dump to be missing from the new database.

Using pg_upgrade requires both PostgreSQL versions to be installed simultaneously. But 9.1 is not available in the Ubuntu 14.04 repositories. I considered replicating the old system into a chroot on the new machine, but I don't know if that will play well with how pg_upgrade will create hardlinks.

There is another hurdle which so far prevented me from testing pg_upgrade. When I try to run pg_upgrade I am told that the command is in the postgres-xc package which is not installed.

If I try to install postgres-xc it tells me that postgres-xc depends on postgres-xc-client.

If I try installing postgres-xc-client I am told that doing so will uninstall posgres-9.3. So it appears it is not even possible to have pg_upgrade and PostgreSQL 9.3 installed at the same time.

The message from the command-not-found package telling me I needed to install postgres-xc turned out to be misleading. In fact pg_upgrade is part of the default install but it is not in the default PATH rather it is located in /usr/lib/postgresql/9.3/bin/pg_upgrade.

Because pg_upgrade was not in PATH a search through available packages was performed and the postgres-xc package happens to have a /usr/bin/pg_upgrade executable. That was however not the pg_upgrade I was looking for.

Alas pg_upgrade would not work unless I was able to bring up a PostgreSQL instance serving the files in /var/lib/postgresql/9.1 which I wasn't able to do with the packages available in Ubuntu 14.04 repositories.

So I concluded that the answer to my question most likely was: No, converting the files in /var/lib/postgresql/9.1 is not possible using software from the Ubuntu 14.04 repository.

What I did in the end was to use pg_dump on the Debian system where the database originated. My concern about changes being made while pg_dump was running were addressed by setting default_transaction_read_only in /etc/postgresql/9.1/main/postgresql.conf. To ensure that no currently running transaction would commit after I started pg_dump, I restarted PostgreSQL between enabling default_transaction_read_only and starting pg_dump.

This approach had the added benefit of at least having the system keep running in read-only mode while the migration was ongoing, thus minimising the user visible downtime.

Before I started this manoeuvre I verified the process using the output of a pg_dump command performed on the running database.

In particular I verified that:

Database triggers were migrated correctly.
Unusual unicode characters in the data were migrated correctly.

I decided to use the textual output from pg_dump to be imported by using psql rather than the binary output format, because that allowed me to easily perform a few manual sanity checks on the file being migrated between the two databases.


How to Increment duplicate strings in a column I have a table similar to what is below.  I have listed only one column since that's what I'm interested in.

| column
---------
| foo
| foo
| foo
| bar
| bar
| bar

Below is the output that I desire but I can't seem to figure out how to make it happen.  This column is not a primary key or serial key.

| column
---------
| foo 1
| foo 2
| foo 3
| bar 1
| bar 2
| bar 3

I want to increment the string if there are duplicates.

You can use the row number window function for this (http://www.postgresql.org/docs/9.2/static/functions-window.html)

SELECT col || ' ' || row_number() OVER (PARTITION BY col)
FROM test_a;

Output will be:

+------------+
| ?column?   |
|------------|
| bar 1      |
| bar 2      |
| bar 3      |
| bar 4      |
| foo 1      |
| foo 2      |
| foo 3      |
| foo 4      |
+------------+