After renaming postgres user, getting "role 'postgres' does not exist" in logs on PostgreSQL startup For security reasons, we've been asked to rename our postgres user in our database. We've made the change in our staging environment and bounced PostgreSQL. We're now seeing an error in the log on server start up. We thought it was related to auto-vacuuming but we've since turned that off and we are still seeing the error:

< 2016-01-19 13:42:55.910 EST    >LOG:  database system was shut down at 2016-01-19 13:42:43 EST
< 2016-01-19 13:42:55.917 EST    >LOG:  database system is ready to accept connections
< 2016-01-19 13:42:56.869 EST [unknown] [unknown] [unknown] >LOG:  connection received: host=[local]
< 2016-01-19 13:42:56.871 EST postgres postgres [unknown] >LOG:  connection authorized: user=postgres database=postgres
< 2016-01-19 13:42:56.871 EST postgres postgres [unknown] >FATAL:  role "postgres" does not exist

I'm still able to login using my user account and our app is also able to login.

I'd like to be able to be sure nothing else is broken (and eliminate this error message) before pushing this change to our production environment.

FWIW, I see the same error in the log after su - postgres and then running psql. It looks like there may be some process that's assuming that peer logins to the database from localhost are allowed. How would I identify this process and fix it?

This is on Cent OS 7 and PostgreSQL 9.3. PostgreSQL is being started via a service using the default postgresql-9.3.service file.

Here's the output from systemctl status postgresql-9.3:

postgresql-9.3.service - PostgreSQL 9.3 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-9.3.service; enabled)
   Active: active (running) since Tue 2016-01-19 13:42:56 EST; 5min ago
  Process: 23847 ExecStop=/usr/pgsql-9.3/bin/pg_ctl stop -D ${PGDATA} -s -m fast (code=exited, status=0/SUCCESS)
  Process: 10944 ExecReload=/usr/pgsql-9.3/bin/pg_ctl reload -D ${PGDATA} -s (code=exited, status=0/SUCCESS)
  Process: 23861 ExecStart=/usr/pgsql-9.3/bin/pg_ctl start -D ${PGDATA} -s -w -t 300 (code=exited, status=0/SUCCESS)
  Process: 23856 ExecStartPre=/usr/pgsql-9.3/bin/postgresql93-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 23866 (postgres)
   CGroup: /system.slice/postgresql-9.3.service
           ├─23866 /usr/pgsql-9.3/bin/postgres -D /var/lib/pgsql/9.3/data
           ├─23867 postgres: logger process
           ├─23869 postgres: checkpointer process
           ├─23870 postgres: writer process
           ├─23871 postgres: wal writer process
           ├─23872 postgres: archiver process
           └─23873 postgres: stats collector process

Jan 19 13:42:55 snapp-db1.mobdev.local systemd[1]: Starting PostgreSQL 9.3 database server...
Jan 19 13:42:55 snapp-db1.mobdev.local pg_ctl[23861]: < 2016-01-19 13:42:55.907 EST    >LOG:  redirecting log output to logging c...rocess
Jan 19 13:42:55 snapp-db1.mobdev.local pg_ctl[23861]: < 2016-01-19 13:42:55.907 EST    >HINT:  Future log output will appear in d...gres".
Jan 19 13:42:56 snapp-db1.mobdev.local systemd[1]: Started PostgreSQL 9.3 database server.
Hint: Some lines were ellipsized, use -l to show in full.

So after much more research and help from users here, it sounds like renaming your postgres DB user isn't a good idea. You'll need to backup, re-initdb, restore, and some other stuff.


Why running queries one by one yields correct results, but running lots of them in one chunk doesn't? Let's say I create a simple table and a stored function to populate it:

CREATE TABLE t
(
    id INT PRIMARY KEY,
    val VARCHAR(255)
)//

CREATE FUNCTION populateIfNotAlreadyIn (idArg INT, valArg VARCHAR(255))
RETURNS BOOLEAN NOT DETERMINISTIC
BEGIN
    DECLARE alreadyIn INT;
    SELECT COUNT(id) INTO alreadyIn FROM t WHERE id = idArg;
    IF alreadyIn <> 0 THEN
        RETURN FALSE;
    ELSE
        INSERT INTO t (id, val) VALUES (idArg, valArg);
        RETURN TRUE;
    END IF;
END//

Of course, the intended behaviour is that the function returns 0 if a row with the given ID already exists in the table, and 1 if it doesn't (in the latter case, the function also inserts such a row).

So now testing comes.

If I run testing queries in phpmyadmin one by one like that:

SELECT populateIfNotAlreadyIn(1, 'val1');

//Go//

SELECT populateIfNotAlreadyIn(2, 'val2');

//Go//

… etc

Then apparently, the results are correct: the function returns 1 and inserts a row if and only if there is no row with such an id in the table, otherwise it returns 0.

So far, so good. But instead of running queries one by one, let's feed phpmyadmin with all of them at once. To be more specific, let's insert into phpmyadmin sth like this:

SELECT populateIfNotAlreadyIn(1, 'val1');
SELECT populateIfNotAlreadyIn(2, 'val2');
SELECT populateIfNotAlreadyIn(3, 'val3');
SELECT populateIfNotAlreadyIn(4, 'val4');
SELECT populateIfNotAlreadyIn(5, 'val5');

And then click Go, to run all of them.

Result? The rows are inserted correctly into table t. But the stored function returns incorrect values. Each call except the last one returns a 0.

Why does this happen?

SELECT populateIfNotAlreadyIn(1, 'val1'),
       populateIfNotAlreadyIn(2, 'val2'),
       populateIfNotAlreadyIn(3, 'val3'),
       populateIfNotAlreadyIn(4, 'val4'),
       populateIfNotAlreadyIn(5, 'val5') ;

Meanwhile, you could shorten the function to just

RETURN EXISTS(SELECT * FROM t WHERE id = idArg);


Physical co-clustering of two or more logical relations/tables in modern RDBMS One possible approach to optimize search queries is (a) to store the records that retain data that corresponds to different relations/tables in (b) the same file → on the same pages. That way, a join can be performed much faster.

I've googled "co-clustering" and surprisingly few results showed up. I found nothing on MySQL for example. There is some indication that Oracle offered it 10 years ago. Is co-clustering still a valid option for optimization?

For example, you have two relations/tables:

Employee (id, name, age, did)
Department (did, location)

A typical query for which you optimize maybe looks something like this:

SELECT E.name,
       E.age
  FROM Employee E,
       Department D
 WHERE E.age = 25
   AND E.did = D.did;

If you had 1,000,000 employees and they are all between 25 and 27, the best join method is probably sort-merge join or hash join - both require multiple scans.

Now, if you store tuples/rows of multiple relations/tables on the same page, you can use a physical structure that stores a department with a certain did together with employees with the same did. Notice that such join requires much less IOs.

Is co-clustering still a valid option for optimization?

Sure, it is a valid option for optimization, if your DBMS offers it. As David Browne has mentioned in a comment, only Oracle does (which, in a way, tells you how practical this feature is).

As you have noted, it is useful in a very limited set of scenarios, while being detrimental to a wider range of queries. In the cases that might benefit from table co-clustering you can employ alternative optimization techniques, such as materialized (indexed) views or column-organized tables, which offer similar performance benefits while being more widely available.

Consider also that today common use of SSD storage, abundance of cheap RAM on database servers, in combination with better query optimizers, decrease the value of marginal reduction in physical I/O at the cost of possible negative side effects and additional database maintenance overhead.

TLDR: don't bother.


is it possible to do a reverse pattern search in a database? I have 2 database tables with rows containing strings. If I combine two strings, one from each table, as input I need to be able to find where the parts of the strings are located in each table.

For example:

table 1 has "AB" at row 3
table 2 has "ED" at row 4

If I give the input as "ABED" I need the two rows to be returned.

Can this be done using an SQL command?

Edit: We do not know the sub-strings. We only know the string ABED.So we don't know if the first table has "ABE" in it or "AB" in it and which part of the string is in the second table.

This is what I can come up with (untested).

You have to be able to tell the server that you're searching for "AB" and "ED" separately - otherwise you will get a very large number of possible combinations very quickly.

SELECT t1.*, t2.*
FROM table1 t1, table2 t2
WHERE t1.t1_field LIKE '%AB%'
OR t1.t2_field LIKE '%ED%'.

If you require different substring sets per substring, then you will have to use your programming environment (PHP, Java, &c.) to split the main input search string ("ADED") into its components.

[EDIT - in reponse to the OP]

Are these random values or do you have a (limited) specific list? If you have more than ~ 25, then I suggest you go with your programming environment, otherwise the SQL will become very messy.

Just a thought, if you have a limited number of these substrings (at a specific part of the field - i.e. 3 characters in? Or similar), you could create a joining table with the substring and the PRIMARY KEY of the main table to get your lookups done faster.

Are these codes you are decomposing? We really need to see the entire scope of your data to get an idea of what would be the best solution.


Is there a way of securing ProxySQL's own SQLite3 database? As I understand ProxySQL has support for SSL encryption for inbound and outbound traffic (front/backend) and supports password hashing. However back in 2017, one had direct database access:
How to easily bring 80 MySQL users into ProxySQL?
According to Icarus's answer, it would be rather easy for someone who broke into the hosting server to gain access to all endpoints listed on this database by making a select call directly to it.
Is there any way of encrypting the SQLite3 DB? Or perhaps of encrypting the data stored in the database (similar to how SSL is used for in-transit data)? Basically looking for data-at-rest securing strategies.
Perhaps this is already implemented in recent versions? I could not find any documentation supporting it, but it did seem that may be the case:
https://github.com/sysown/proxysql/blob/v2.0.13/lib/ProxySQL_Admin.cpp
Which, if I understood correctly implements SHA1 encryption:
https://stackoverflow.com/questions/3179021/sha1-hashing-in-sqlite-how
If this is correct, is this restricted to password hashing? Or is it part of the SSL encryption implementation? Or is there actually a way of enabling encrypted "DISK" read/writes? Is this by any chance implemented by default on v2+ builds?
Ultimately, I plan on encrypting the volume this is installed in, and have a second proxy layer so that I don't expose endpoint information; this would be an additional layer of protection.
Thanks, I appreciate any insights you may have!

I have yet to find a better answer for now other than the SQLite3 DB ProxySQL uses is unencrypted and there are no parameters to change this. It is strange since you can encrypt many other things, but data at rest seems unaddressed.
However one may use HDD encryption to reduce the chances of it being breached. Also, keep user passwords hashed in case your server security is broken, and your encryption keys encrypted.
Will update this answer if I find additional information.


How to use HTTP Connection Manager in Script Component? (Not Script Task) I'm trying to perform the same scenario as in the following link; Create a SSIS Script Component as a Data Source that uses a pre-existing HTTP Connection Manager to retreive a page with GET and emit rows into the Data Flow pipeline.

http://www.sqlis.com/sqlis/post/Downloading-a-file-over-HTTP-the-SSIS-way.aspx

My target platform is SQL Server 2008 and therefore C#. The MSDN documentation gives examples of File and SQL Connection Managers but not HTTP ones.

https://msdn.microsoft.com/en-us/library/ms136060%28v=sql.100%29.aspx

The specific problem is that I can NOT figure out why there's no HttpClientConnection constructor in my current context. The MSDN documentation of that class does not seem to apply in the case of Script Components and translating this to something useful is apparently beyond me.

https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.httpclientconnection.httpclientconnection%28v=sql.100%29.aspx

My non-working code looks like this -

using System;
using System.Data;
using System.Text;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    public override void AcquireConnections()
    {
        IDTSConnectionManager100 connMgr = this.Connections.MyWebServer;
    }
    public override void PreExecute()
    {
       base.PreExecute();

       HttpClientConnection clientConn = new HttpClientConnection(connMgr));

       Byte[] buffer = clientConn.DownloadData();
       String Document = Encoding.ASCII.GetString(buffer);
    }

What am I missing?

i know it's been a while since the question but i found a possible solution.

This link provides an explanation and examples of how to use the connections available in the connection manager enabled for the Script Component in the SSIS https://msdn.microsoft.com/en-us/library/ms136060.aspx

The portion of code that we are interested is this:

IDTSConnectionManager100 connMgr;
HttpClientConnection100 hcc;

IDTSComponentMetaData100 compMetadata = this.ComponentMetaData; //Just for output purposes.

connMgr = this.Connections.MyWebServer;

hcc = (HttpClientConnection100)cm.AcquireConnection(null);
compMetadata.FireInformation(1, "Message: ", "URL: " + hcc.ServerURL + " User: " + hcc.ServerUserName + " Pwd: " + hcc.GetServerPassword(), "", 0, ref fail);


Database design problem: where to put UNIQUE constraint I have the following three tables:

Form
-----------
formId INTEGER       PK
name   NVARCHAR(50)  NOT NULL

Block
-----------
blockId     INTEGER        PK
name        NVARCHAR(30)   NOT NULL
orderInForm INTEGER        NOT NULL

FormBlock
-----------
blockId  INTEGER  PK
formId   INTEGER  PK

There is a many-to-many relationship. A block could appear on multiples forms. orderInForm is for a particular form.

On Block table I want to avoid that two blocks have the same orderInForm in a Form.

I think I have to add an UNIQUE constraint, but if orderInForm is on Block table, I don't know how to do it.

May I need to move orderInForm to FormBlock table?

Since orderInForm is the position of a particular block on a particular form, it should be a column in the FormBlock table, not a column in the Block table.  If orderInForm is in the Block table, a block would need to have the same position in every form it was on.

Once you move the column to FormBlock, you can then create a unique constraint on the combination of (form_id,orderInForm) on the FormBlock table.


atomic insert select operation So i have two tables:
Project:
----------
project_id (PK)
project_prefix

Issues:
----------
issue_id
project_id (FK)

for every project with some prefix ex P1 i want all issues to be like P1-1 P1-2... P3-1 P3-2, whenver an issues is created in the issue table.
The way that i can think of doing this is maintaining a thrid table with Project id & issue_id_max mapping.
project_issues_max:
-----------------
project_id issue_id
P1            1
P2            1
...
P3            2

& then having something like:
prefix_id -> fetch_project_prefix(p1)

BEGIN
    Insert case values(
        concat(
            prefix , select(
                        coalesce(
                             max (project_issues_max.case_id) + 1 ,0)
                             from project_case_max
                        )
                    )
            )
        )
    update project_case_max set case_id = case_id + 1 where project_case_max.project_id = "some project id (p1)"
COMMIT

I am sure this doing max()+1 will have concurrency issues with multiple simultaneous session reads of case_id value.
What is the best way to do this?, This question here is almost similar and seems to be accepted.what makes doing max()+1 safe by adding SKIP LOCKED. It also handles ON CONFLICT, so, what i can infer is conflict can happen only when multiple session read the same value, then how come its race condition safe.

I think requirements like that stem from a fundamental misconception on how relational databases work and what a primary key should do.
Anyhow, in Postgres I would do it like this:
You indeed need a separate table to store the last generated id for each project prefix. To make that a unique value, this means that the project prefix also has to be unique.
The two base tables:
create table project
(
  project_id     integer primary key,
  project_prefix varchar(20) not null unique --<< unique is important!
);

create table issue
(
  issue_id   varchar(50) primary key, -- this needs to be longer than the prefix!
  project_id integer not null references project,
  severity   integer not null,
  title      text not null,
  constraint check_severity check (severity between 1 and 10)
);

The "generator" table could look like this:
create table id_generator
(
   prefix    varchar(20) not null primary key,
   issue_id  integer     not null default 1
   foreign key (prefix) references project (project_prefix)
);

Then we an create a function that generates the next issue id based on the prefix:
create or replace function generate_issue_id(p_prefix text)
  returns text
as
$$
  insert into id_generator(prefix)
  values (p_prefix)
  on conflict (prefix) do update
    set issue_id = id_generator.issue_id + 1
  returning concat(prefix, '-', issue_id);
$$
language sql;

Then we need to create a trigger for the issue table to automatically assign the issue_id. That way we don't have to provide the project prefix during insert.
create function assign_issue_id()
  returns trigger
as
$$
begin
  select generate_issue_id(project_prefix)
    into new.issue_id
  from project
  where project_id = new.project_id;
  return new;
end;
$$
language plpgsql;

create trigger issue_trigger
  before insert on issue
  for each row
  execute function assign_issue_id();

This works because the function will lock the "generator row" for the project for which the issue ID was generated. The lock will be held until the transaction is committed.
This will effectively serialize inserts into the issue table for a one project, i.e. if a second insert happens before the first one is committed (or rolled back) the second insert has to wait. As the increment of the project specific ID is committed together with the insert into the issue table, a rollback will also rollback the issue id increment, effectively making this a "gapless sequence".
The insert into the issue table then should not provide a issue_id as it is generated automatically:
insert into project (project_id, project_prefix) values (1, 'p1');
insert into issue (project_id, severity, title)
values
(1, 1, 'Not working'),
(1, 10, 'Wrong translation');

Online example


Replicating tables from LAN database to web server? I have an in-house database (that's actually part of a third-party product) running on 2008R2 Express. We'd like to display some of the data on a new website we're working on (running 2008R2). I'm more developer than administrator, so I'm wondering the best method for getting data from one to the other. Data doesn't have to be available real-time, daily replication is acceptable.

Again, this is display only, so two-way replication is not necessary.

I know of replication as a concept and have heard of Sync Framework, but have no experience with either and don't know where to start. I also don't have access to install anything on either server, so to my understanding this rules out Sync Framework.

I've been reading up on replication, but when I looked into creating a publication on the LAN database, I didn't have the option to create one (I'm assuming this is because it's Express). I've also looked into mirroring and snapshots, but I'm not sure where to start.

I'm able to create a linked server from the website database to the LAN database, but for security and performance reasons I can't query directly, which is why I need to replicate the data.

There are a variety of ways you can do this.

If you only need a portion of the database, you can have copies of the tables which you refresh on the destinations periodically. The idea is that you have your users reading one copy of the data, then you have an identical table in a different schema, and you reload that copy while the users are reading. Then in a very quick transaction, you switch the copies of the table between schemas, and it's essentially a metadata operation. I describe this in detail in the following two posts:

Schema Switch-A-Roo
Schema Switch-A-Roo : Part 2

If you need most or all of the database, then it is going to be much simpler to use log shipping to facilitate readable secondaries. It just so happens that today I published a very thorough walk-through of creating readable secondaries with Express:

Readable Secondaries on a Budget

Essentially, you play round-robin with which instance gets read from at any one time; since you want multiple active simultaneously, it would only be a little bit more complex. You might have two or four instances of Express on each web server, and every 15 minutes you switch the standby on all of the servers to have the most recent data.

Since you're on Express for both source and destination, in either case, you will need an agent of some kind to schedule the daily (or more frequent) pushes.


Can you trigger an action when a view is created? Is it possible to create a trigger that fires when a view is created on a user schema?

I want to create a meta data table which will hold all the views and a listing containing what they are supposed to do.  In this system  (Oracle 9.2.0.8) there is only one user who has the create view privilege so that may make it easier.

The sequence of events should be something like
view is created   trigger fires and inserts the name of the view in the table of meta data table

I could write up some package to create views using dynamic sql but that seems a bit of overkill

You can use DDL triggers in Oracle.

I haven't actually done this myself, so here are some links:

http://www.dba-oracle.com/t_ddl_triggers.htm
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7004.htm#i2153503
http://justoracle.blogspot.com/2006/10/ddl-triggers.html


Taking a backup of a streaming WAL slave I have an environment with pair of postgresql servers set up to do WAL streaming replication.

I also have a test environment and periodically I want to snapshot the production DBMS and restore that onto the test server.

Right now I do:

ID=db-$(date +%F_%T)
pg_basebackup --format=tar --pgdata=- --xlog --gzip --label=$ID

and then restore that archive on the test server.

Unfortunately that makes things really slow down on the production server so I'd like to switch that to pulling the clone from the replication slave.

But I can't - the error I get is:

pg_basebackup: could not connect to server: FATAL:  recovery is still in progress, can't accept WAL streaming connections

What do I need to do to accomplish my goal of not killing performance on the master when I need to clone it?

Running on Ubuntu 12.04 x86_64, postgresql server 9.1.9-0ubuntu12.04

Chained replication is supported on PostgreSQL 9.2.

9.1 does not support chained replication. You will need to either take the base backup from the master, or:

Stop the replica
Copy the replica manually
Start the replica back up


How to optimize query with having and group My Task is to optimize a query, which should check for data inconsistency: Return all leads ids with more than one entries and multiple is_first flag.

| id | lead_id | is_first| (other data) |
|----|---------|---------|-------- ...
|   1|       20|        1|...
|   2|       20|        0|
|   3|       21|        1|
|   4|       21|        0|
|   5|       21|        1|
|   6|       22|        1|

So this dataset should return 21. (20 ok, because is_first is only true/1 once)

Old Query:

SELECT DISTINCT s1.lead_id as lead_id
FROM history as s1
INNER JOIN history as s2 ON
  s1.is_first = 1 AND
  s2.is_first = 1 AND
    s1.id != s2.id AND
    s1.lead_id = s2.lead_id;

New Query: I already increased performance somewhat with this query

SELECT history.lead_id AS lead_id
FROM history
WHERE is_first = 1 GROUP BY lead_id
HAVING count(lead_id) != 1;

But I feel it's not end of the line. Any recommendations?

If you dont have it already, you could create a index on both fields: is_first and lead_id;

If it's a table with few record you won't see much difference, so I always recommend to check the statistics from the query.


SQL Management Studio 2016 account disabled I have 2 instances, sqlexpress and mssqlserver. I made fool mistake, I disabled the login account that I used it all of the time to login in mssqlserver using windows authentication. And I don't remember the 'sa' password because never use it to log in.

I have read and practice some tips about this problem, and Connect to SQL Server When System Administrators Are Locked Out but my problem still isn't solved.

So, my questions. Does uninstall then reinstall can give me access again? If yes, which should I uninstall-reinstall? My sql server or ssms?
I'm not worried about databases, I just want the access to this instance again for learning.

So, my questions. Does uninstall then reinstall can give me access again?

Yes, uninstall and reinstall will give you access again

  If yes, which should I uninstall-reinstall? My sql server or ssms?

SQL Server


Relational-JSON overlapping with association I have a composed class called sensor that is linked to a main class Node such that:

class node is linked with another node to make a communication that produce an event
class sensor can as well detect events

So I thought of putting the sensor data inside of the table node as a JSON, but the  association that allows me to detect makes doesn't apply to any schema I have seen before.

Is it a good idea to make the change that way or will that be more tricky to get the needed data?

See the full UML below; but my point concerns only the sensor.

Do you think I can convert that in R-JSON?

If you spot any place other than position that it would be suited to use R-JSON, I would like to think of the option.

If you want simple and efficient SQL statements, don't model an entity as a JSON column. Create a regular normalized table that expresses the functional dependency between the sensor's attributes explicitly.

JSON in the database is a good thing and has its applications, but modeling foreign key relationships like the one to event is not among them.


Naming LOGSEGMENT in Oracle I'm new to Oracle DBs and I'm creating a table that contains a CLOB field for very large JSON Objects.

I'm requested to name the LOGSEGMENT object of the CLOB column but I haven't found how to do it. For now, I'm just creating a table that has a CLOB column and a constraint to check that the CLOB field is actually a json. How do I name the LOGSEGMENT object?

SQL> create table t1 (c1 clob) lob (c1) store as securefile c1_clob;

Table created.

SQL> select table_name, column_name, segment_name from user_lobs;

TABLE_NAME           COLUMN_NAME          SEGMENT_NAME
-------------------- -------------------- --------------------
T1                   C1                   C1_CLOB

SQL>

More details at: LOB_storage_clause


How to delete all but the latest entry per customer in the fastest way? I have a table containing around 4 million unique customers (with customerId each), but each customer can have multiple entries. There is no primary key. Each row has a creation dateTime saved as decimal number. customerId is indexed.

Now I want to find the solution to delete all but the latest row per customer that has the best performance. The thing is, this table will be constantly filled and the cleanup should be done once a day.

What I have tried:

Use a cursor of "select customerId from customers" and iterate over it. Fetch the max creation time per customer. Delete all entries with the id and with a different creation time. -> Sadly, this is incredibly slow.
"Smart" DELETE queries with subselects aren't possible, because MySQL does not allow calling select in a subquery.
The client does not want to user triggers.

Any other ideas?

Draft code:

CREATE PROCEDURE clear_table() AS
BEGIN
    CREATE TABLE temptable LIKE datatable;
    INSERT INTO temptable (customerID, creationDatetime)
        SELECT customerID, MAX(creationDatetime)
        FROM datatable
        GROUP BY customerId;
/* if there exists another fields in tables, then: */
/*  UPDATE temptable, datatable
        SET temptable.field1 = datatable.field1,
            ... ,
            temptable.fieldN = datatable.fieldN
        WHERE temptable.customerID = datatable.customerID
          AND temptable.creationDatetime = datatable.creationDatetime;
*/
    DROP TABLE datatable;
    ALTER TABLE temptable RENAME TO datatable;
END;

To increase performance - create index by (customerID, creationDatetime).


How can I design a table for quick versioning of textual data? Version i am talking about here is not of version control systems. Like in SO i got some textual data(compare them to questions) that could be versioned(in the sense edited, saved, rollback). Content of Each edit, update, save is to be saved as new version based on option by end-user. How should i design a table for this kind of task?
Current Implementation
id pk auto increment
content
parentid defaults to 0
version  defaults to 0

Sample records
id     content        parentid        version
1      Hello World        0              0
2      Hello Doggy        1              1
3      Hello Kitty        1              2

as you can see each record with a parent id equal to existing id will be saved in incremental version(which is actually calculated in server side code). Which is displayed in the UI like below
1.Hello World
    -Hello Doggy(Version1)
    -Hello Kitty(Version2)

where each is displayed as link so that further editing can be done. Things works like expected no problem but we want to improve on this & i think it would be best to start from the table designs.

You already have one option: keep it in one table
This is a "Slowly Changing Dimension".

The other options is to split "Current" and "Previous" versions into 2 tables. When you update the current table, a trigger can populate the "Previous" table. Or an UPDATE..OUTPUT.

You can query this data in 2 ways:

combined into one recordset, client code splits it up
two record sets, one current + one previous. This can be done in a stored procedure too.

I've implemented both: it depends on access patterns and usage.

If I'm reading all versions in one go or JOINING onto it, then one table. If "previous" data is only on demand then tables.

In your case, can you defer the previous versions until a user clicks to load it? Is it rare to actually need previous versions? If yes to either, then I'd suggest 2 tables. This way, you only shift and render less data only for current versions.


Derived classes with same base class and class specific ids I have three classes in my project. One base class and two derived classes.

public class Animal
{
    public long Id { get; set; }
    public DateTime Created { get; set; }
    public string Name { get; set; }
}

public class Cattle : Animal
{
    public int HornsCount { get; set; }
}

public class Fish : Animal
{
    public int FlippersCount { get; set; }
}

I want to store instances of these classes in database and since they have some same values (from base class) I thought that the best idea is to create Three tables: Animal (with PK Id), Cattle (with PK&FK to Animal.Id), Fish (with PK&FK to Animal.Id). But the problem occurs when I found out that each derived class has its own Id counter. So you can have Cattle.Id = 1 and at the same time Fish.Id = 1. Now I'm not sure how to proceed. Is one of these approaches correct?

Use just two tables (Cattle and Fish) with duplicate columns.
Use design specified above and add special column to Animal table which will be used for all Animals?
a) This new Id is string code that is defined eg derived class in first letter and then is original Id of derived class.
b) This new Id is auto increment database value. And move original Id column from Animal table to tables for derived classes.

1 downside - many duplicate columns -> have to change all tables for derived classes if I change Animal class.
2a downside - string as PK which actually doesn't mean anything in application.
2b downside - I have to insert into Animal table, then get auto Id of that record and then insert row to derived class table. Also there are identical original Id column in all tables for derived classes.

Or is there some better approach?

EDIT:
Question was put on hold as unclear so maybe some example data will help me explain my problem better. Here are five instances which I should save into database (I am not able to affect how these instances are created):

var c1 = new Cattle {Id = 1, Created = 2015-11-05 10:10, Name = "Cow1", HornsCount = 1}
var c2 = new Cattle {Id = 2, Created = 2015-11-05 12:12, Name = "Bull1", HornsCount = 2}
var f1 = new Fish {Id = 1, Created = 2015-11-04 05:05, Name = "Salmon1", FlippersCount = 5}
var f2 = new Fish {Id = 2, Created = 2015-11-05 06:06, Name = "Carp1", FlippersCount = 3}
var f3 = new Fish {Id = 3, Created = 2015-11-05 05:05, Name = "Trout1", FlippersCount = 2}

As you can see one Id property is unique only for all Cattle instances and different one is unique only for Fish instances. I should be able to return the right result when the app wants for example: "Fish with Id = 2."

You can simply inherit the primary key from the Animals table in the Fish and Cattle table.

This example uses syntax that is SQL Server specific, however you should be able to convert this into SQLLite syntax:

CREATE TABLE dbo.Animals
(
    AnimalID INT NOT NULL
        CONSTRAINT PK_Animals
        PRIMARY KEY CLUSTERED
    , DateCreated DATETIME
);

CREATE TABLE dbo.Fish
(
    AnimalID INT NOT NULL
        CONSTRAINT FK_Fish_AnimalID
        FOREIGN KEY
        REFERENCES dbo.Animals(AnimalID)
    , FinCount INT NOT NULL
    , CONSTRAINT UQ_FISH
        UNIQUE (AnimalID)
);

This design prevents the need for surrogate key in the Fish table, while still ensuring uniqueness for the rows in that table by way of the unique constraint UQ_Fish.

If you want to see all the details about Fish, you'd use a query similar to:

SELECT Animals.AnimalID
    , Animals.DateCreated
    , Fish.FinCount
FROM dbo.Animals
    INNER JOIN dbo.Fish ON Animals.AnimalID = Fish.AnimalID;

Alternatively, if you are constrained by the classes built by the app, you can simply create the following structure:

CREATE TABLE dbo.Animals
(
    AnimalID INT NOT NULL
        CONSTRAINT PK_Animals
        PRIMARY KEY CLUSTERED
    , DateCreated DATETIME
);

CREATE TABLE dbo.Fish
(
    FishID INT NOT NULL
        CONSTRAINT PK_Fish
        PRIMARY KEY CLUSTERED
    , AnimalID INT NOT NULL
        CONSTRAINT FK_Fish_AnimalID
        FOREIGN KEY
        REFERENCES dbo.Animals(AnimalID)
    , FinCount INT NOT NULL
    , CONSTRAINT UQ_FISH
        UNIQUE (AnimalID)
);


Mysql Connector C++ statement values question I just want to know, if a value, like an int for example, is actually converted into a string before being sent as part of the command.

pstmt = con->prepareStatement("INSERT INTO test(id) VALUES (?)");

Usually, the ? is replaced with:

pstmt->setInt(1,155);

What I'm curious about is: could I simply make the command:

pstmt = con->prepareStatement("INSERT INTO test(id) VALUES (155)");

And execute it? Would it be the same as setInt? (basically that would mean that the int value is converted to a string before being set as part of the command)

While both methods are perfectly legitimate, there is one major difference.

When you prepare a SQL statement, you precompile it and substitute values using setInt().
When you hardcode the actually values, you have to precompile the SQL every time.

Please note when it comes to Com_stmt_prepare and Com_stmt_execute

only Com_stmt_execute would increment
both increment in the MySQL Server Status when you hardcode

Prepared statements are only to your advantage if you maintain persistent or pooled connections. This is the case because certain data structures must be allocated on the server-side. If you connect and disconnect all the time, you have to construct and destroy the prepared statement along with a host of other connection-based resources (See my old post How costly is opening and closing of a DB connection?)


SQL Server could not find a login matching the name provided I have SQL Serer 2012 up and running. I created a new login (SQL Server authentication with password expiration, requires change, etc. turned off).

I can start SQL Server Management Studio as the user. But when I try to connect in code I get the error 'could not find a login matching the name provided'. I know the code works because it can connect to another instance on the same machine.

But the name is there, and I'm actually logged in as that user.

Note, I cannot use integrated security either, just the same error message.

What's wrong? Many thanks.

I had the wrong port number. There was a third database instance with this port....

so it seems when you connect using 'SERVER\INSTANCE,port' it completely ignores INSTANCE if you specify port and instead of saying you have the wrong instance you get user not found.

thanks.


Oracle11g - Convert alpha characters to numeric in select I am only able to convert alpha characters to numeric by nesting REPLACE. Is there a better way to handle this?

Desired conversion:

a - 1
A - 1
b - 2
B - 2
c - 3
C - 3
...
z - 26
Z - 26

Example:

select  '1a2b3c4d5e' as "before",
        REPLACE(REPLACE('1a2b3c4d5e','a','1'),'b','2') as "after"
  from dual

Output:

before      after
1a2b3c4d5e  11223c4d5e

I'm hoping there is an alternative, as none seemed to exist here. Maybe there is a clever way to use REGEXP_REPLACE?

Well, it won't win any code golf awards .. O.o ... but this should work .. the trick is you trying to convert higher letters into double digits . (ie z ==> 26)  if it was 1 to 1 .. TRANSLATE would work quite nicely .. however, I'm not sure to do it more elegantly .. O.o  (not sure if it's better than the REPLACE option or not .. lol)  I suppose with this logic as a base, you could easily turn it into a table driven approach .. using the w_sub query as the join to your mapping tables) ;)

  col after format a50

  with w_input as ( select '1a2b3c4zd5e' c from dual ),
     w_asc as ( select c, level i, ascii(substr(upper(c),level,1))  aa,
                    substr(upper(c),level,1)  cc
                 from w_input  connect by level <= length(c)
               ),
     w_sub as (
        select c, i, aa, cc,
                 case when aa between 65 and 90  -- A-Z
                       then aa-64
                    when aa between 48 and 57  -- 0-9
                       then to_number(cc)
                    else 0     -- non number, non alpha ... could be punctuation, etc.
                 end  zz
          from w_asc
        )
  select c  before,
        listagg(zz,'') within group (order by i)   after
    from w_sub
  group by c
  /

  BEFORE      AFTER
  ----------- --------------------------------------------------
  1a2b3c4zd5e 112233426455

Explanation:

1) w_input is just your input .. put whatever tests you want here.

2) w_asc: is taking the string, and turning each digit into a row ...
   (it also yanks the digit, and converts it to ASCII code )

3) w_sub: this is your conversion/mapping logic - numbers 0-9 ==> 0-9 .. letters: a==>1  z==>26 ... case insenstive.

4) the final query uses listagg to bring the rows back together using the original column "i" to remember the order.

Cheers! :)


Can PostgreSQL databases be attached/detached on the fly? Is it possible to attach/detach databases to PostgreSQL on the fly, i.e. while the server is running without having to restart the service? I can't find anything related to this in the documentation.

What I am trying to achieve is to have the user be able to select different data directories from an archive location and then have them loaded into PostgreSQL, and later removed when the user is finished.

No, short answer is no. This has been asked at the PostgreSQL development mailing list at 2011: Detach/attach database
 and the many replies in summary said "yes, this is feasible to implement but quite complicated to do so.":

  You would have to do quite some surgery because of oids from shared tables. I don't think thats easily doable.

  Andres Freund

  The main reason this doesn't work is XID management.
  It's barely possible you could make it work if you first locked all
  other sessions out of the DB and then froze every XID in the database,
  but that's a sufficiently heavyweight operation to make it of dubious
  value.
  ...

  Tom Lane

  I have to admit I've thought about this from time to time, and it
  would be pretty cool.  I was initially thinking that it wouldn't be
  that difficult to do this on a per-database level, because if you
  slurp up a whole database then by definition you're also including the
  system catalogs, which means that you have the pg_class, pg_attribute,
  and pg_type entries that are necessary to interpret the table
  contents.  If you do anything more fine-grained (per-tablespace,
  per-table, or whatever) then things get much more complex, but at the
  database level you only need to worry about interactions with other
  globals: tablespace and role definitions.  And we could probably write
  code to grovel through the system catalogs for a newly "mounted"
  database and do search and replace on the appropriate columns, to map
  from the old OIDs to the new ones.  It wouldn't be simple, but I think
  it could be done.

  But Tom's point about XIDs and LSNs seems like it kind of puts a
  bullet through the heart of the whole idea.  Now, before you can move
  the database (or table, or whatever) between clusters, you've got to
  rewrite all the data files to freeze XIDs and, I don't know, zero out
  LSNs, or something.  And if you're going to rewrite all the data, then
  you've pretty much lost all the benefit of doing this in the first
  place. In fact, it might end up being slower than a dump and
  restore; even an uncompressed dump will be smaller than the on-disk
  footprint of the original database, and many dumps compress quite
  well.

  Robert Haas

  What about having database-level XIDs rather than cluster-level?  Is that
  remotely feasible?

  Thom Brown

  Maybe.  You'd need a set separate set for shared catalogs, too.  It
  seems like a heck of a lot of work, though, especially since (IME,
  anyway) most people only really one run one database per cluster.

  Robert Haas

Leaving aside all the internal implementation details / problems that would make this possible, the last comment has something that may be helpful to you:

Run a different cluster per database. You would probably need to implement
something of an infrastructure so when a user wants to "attach" a database, it would start up the related cluster and stop it when the user wants to "detach" it.


Restoring Job failed with error 'Could not find a log backup file that could be applied' on Log Shipping in SQL Server (Secondary)? Recently I configured Log shipping between 2 different servers (all SQL Server 2008 R2), from primary to secondary (2 instances). Log shipping configured successfully and databases on secondary servers are Standby/Read only mode. I mean from primary to 2 secondary servers I added for Log shipping.

Today I observed Log backup is happening on primary but it's not restoring into secondary. Is the Full Backup on primary breaking Log shipping, or are there any other reasons for breaking Log shipping?

@ Kris Gruttemeyer, Transactional Log shipping status on secondary server seems like below:

Error Message for Restoring data in View Job History while Running Restore Job on Log shipping server (Secondary): "Could not find a log backup file that could be applied to secondary database" and “The restore operation completed with errors. Secondary ID: '807202d2-18a6-4236-85ad-ae3a51fba2eb'”

There is no answer for previous question as well: What does 'Could not find a log backup file that could be applied' means on log shipping?. Please help me on this?

FULL backups do not break log shipping.  FULL backups simply reset your differential base for all subsequent DIFF backups until the next FULL occurs.

Log shipping occurs in 3 phases:

1- Log backup on the primary

2- Copy log file to destination server (or network share)

3- Restore Log to secondary

If using default settings, these each occur in 15 minute intervals.  This means that it can take up to 45 minutes for a log file taken on the primary to be successfully applied to the secondary.

An easy way to verify that things are in sync is use the 'Transaction Log Shipping Status Report' on the secondary server by right clicking the server name in SSMS and navigating to the following:

EDIT: Here's where to look:


How to join by most recent preceding date? Imagine I have a database with historic price data and a second table with dates

Item | Price | Date               Customer | Item | date
-------------------------         ---------------------------
   A | 2.49$ | 2014-09-01                1 |    A | 2014-08-27
   B | 1.29$ | 2014-09-01                2 |    A | 2014-09-02
   A | 2.99$ | 2014-08-25
   B | 1.39$ | 2014-08-26

A new row was entered into the price history table every time the price changed.

How can I (efficiently) join the two tables so that I can get the price each customer had to pay on that day?

SQLFiddle

select *
from dbo.Sales s
  cross apply (
  select top (1) *
  from dbo.PriceHistory ph where ph.itemId = s.ItemId and ph.Date <= s.Date
  order by ph.Date desc
) ca;


How to create pg_trgm compound indexes with date columns SELECT col1, max(date1) as max_date
FROM table
WHERE col1 ILIKE 'name'
GROUP BY col1

TYPES:Here col1 is varchar and date1 is timestamp with time zone data type. So created extension CREATE EXTENSION pg_trgm

Then tried the following indexes and got the errors:

1: Issue: ERROR:  operator class "gin_trgm_ops" does not accept data type timestamp with time zone

CREATE INDEX CONCURRENTLY trgm_table_col1_date_index
ON table
USING gin (provider_id, date1 gin_trgm_ops);

2: Issue: ERROR:  operator class "text_pattern_ops" does not exist for access method "gin"

 CREATE INDEX CONCURRENTLY trgm_table_col1_date_index
    ON table
    USING gin (provider_id, date1 text_pattern_ops);

How can I create an index for the above query for faster execution? Any help will be really appreciated!

EDIT:

Please pick identifiers for your example queries which are not reserved words.

The pg_trgm module only supports text-like columns.  Your date column is presumably a date type (or a timestamp, or something like that), not text-like.

To do what you want, you first need to allow date-like data to be indexed in a gin index.  For example, by create extension btree_gin.

Then you can create your index:

create index on bar using gin (col1 gin_trgm_ops, date_column);

You don't need to provide the operator class for the date_column, because btree_gin provides default classes.  The operator class you do provide needs to be next to the column it applies to.  They are per-column, not per-index.

Note that while this answers your question, it probably doesn't solve your problem.  The above index is legal and can be built, but it will not speed up your query (beyond what a pg_trgm indx on just col1 does).  If you want help making the query run faster, you should show us the EXPLAIN (ANALYZE) of the existing query, as well as other information.


Alternative to Having and Group By I'm in the DB classes, and I have to understand and show that it is possible to get the results of HAVING and GROUP BY without using themselves. I searched for alternatives for those 2 commands, but I didn't understand anything.

Can someone give me examples?

Assuming a query like:

SELECT category, COUNT(*)
FROM T
GROUP BY category
HAVING COUNT(*) > 3

You can mimic that with:

SELECT category, cnt
FROM (
    SELECT x.category
       , ( SELECT COUNT(*) from T where T.category = x.category ) as cnt
    FROM (
        SELECT distinct category from T -- GROUP BY
    ) as x
) as y
WHERE y.cnt > 3 -- HAVING


Checkdb giving error for master database When i run dbcc checkdb on master database i get error like

Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.

When i searched for it i got lot of links stating that it happens when database has read-only file group or file is in FAT32 file system. But when i checked the master database was not in readonly file group. But mdf file was in FAT32 file system. So solution was to use TABLOCK with checkdb command. So i tried dbcc checkdb With Tablock . But i am hetting same error message. So what is the reason for this error. I am using sql server 2008 R2 express(service pack 2).

The FAT32 is the reason here. And you can't do a TABLOCK CHECKDB on the Master database. WITH TABLOCK puts the database in read only mode - you can't do that to the Master database because of system operations that required to run and run on the Master database.

You can verify this easily enough by creating a new user Database on the same file system and then trying a CHECKDB, seeing the same error and then doing a CHECKDB on that on with the TABLOCK option. My guess is you get the same error without the option and you do not get the error when specifying the option (as long as you don't have a connection open to the database by accident)

Unfortunately you are sort of stuck from being able to do regular CHECKDBs on Master until you resolve this.

In the meantime you can do this process ( a bit of a pain but it works)

Ensure all services that connect to SQL Server are off/stoped (this includes services like SSIS, SQL VSS writer, etc)
Restart SQL Server in single user mode using the -m startup parameter (described here)
Connect as the single user and then do your CHECKDB with the TABLOCK specified.

I just tried this on my SQL Server 2012 instance and it worked. Only way you can do this.

To me the big question here is - why is your file system FAT32? What OS is this on? Are all of your databases on FAT32? It is possible to be on FAT32, but the recommendation is to be on NTFS volumes. On FAT32 you don't get certain permission benefits that you get on NTFS - leaving some files open for manipulation and resulting problems, and obviously this CHECKDB issue.


Postgres Procedure Returns Table We have a table structure returned by this function:

RETURNS TABLE (ticker VARCHAR,
           current_recommendation INTEGER,
           previous_approved_recommendation INTEGER,
           date_appended TEXT,
           suggested_recommendation INTEGER,
           date_suggested TEXT,
           suggested_by VARCHAR,
           approved_recommendation INTEGER,
           date_accepted TEXT,
           approved_by VARCHAR,
           recommendation_status INTEGER
           )

On certain conditions, rather than returning values, we want to return just the first & last values, and everything else is NULL.

v_select := 'SELECT ''' || p_ticker || ''',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,' || v_status;

But it is not working:

ERROR:  structure of query does not match function result type
DETAIL:  Returned type text does not match expected type character varying in column 1.

Any help would be great.  We are all kind of new at Postgres, after migrating some thing from ORacle.

I suppose you can solve it by using an explicit cast.

CREATE FUNCTION test(tic text)
RETURNS TABLE (foo VARCHAR, bar int)
AS $$
DECLARE
  sql text;
BEGIN
  sql := 'SELECT ''' || tic || ''', NULL';
  RETURN QUERY EXECUTE sql;
END
$$
LANGUAGE plpgsql;

SELECT test('A');

  ERROR:  structure of query does not match function result type DETAIL:
  Returned type text does not match expected type character varying in
  column 1.

CREATE FUNCTION test1(tic text)
RETURNS TABLE (foo VARCHAR, bar int)
AS $$
DECLARE
  sql text;
BEGIN
  sql := 'SELECT ''' || tic || '''::VARCHAR, NULL::int';
  RETURN QUERY EXECUTE sql;
END
$$
LANGUAGE plpgsql;

SELECT test1('A');

| test1 |
| :---- |
| (A,)  |

db<>fiddle here


Additional fields or intersection table I have a domains table and each domain belongs to a contact. Additionally you can assign 3 extra contacts with different roles admin, tech and billing ,those are not mandatory.

My first thought was to add three extra fields in my domains table.

but then I thought it was better to create an intersection table.

Any suggestions?

Using an intersection table is a cleaner and more flexible design.

With extra columns you are limited to the number of instances you can include (one per foreign key column).  Also, the types of instances are limited to those given by your FK columns.  Making any changes requires a table design change.  Also, since your roles are optional, you may have a lot of nulls in your FKs.  Having sparsely populated columns rubs some people the wrong way.  Whether this is a real design problem or just a "code smell" is debatable and depends on how you actually populate your table.

With an intersection table, you can either use constraints to enforce a single instance per type or you can allow multiple instances per type.  That might prove useful in the future if you start having contacts share a role.

Also, with an intersection table, you can create a new role without having to make a table design change.  This could save you a lot of work in the future.


What tool do I use to administer MySql? I have a phpBB installation on a shared hosting site, and I need to be able to run Sql queries on the underlying database from my remote (home) workstation.  I am checking the MySQL website for tools and I see that there are a few, but which one does what I have just described?

I am very familiar with MS Sql Management Studio, which I use daily on my day job to work with Sql Server databases, and something that allows me to edit Sql queries and run them against the remote MySQL database where my phpBB resides is what I need.

Which tool provided by MySQL does what I need?

There are three "official" Oracle tools for managing MySQL in the sense you describe, two of which are currently supported.

MySQL Workbench I assume is the administration tool that's most analogous to SSMS.

The mysql command line client is a console utility that allows you to issue queries and get results displayed in "ascii line drawing" tables.

MySQL Query Browser is no longer officially supported, but still available for download and still compatible with newer versions of MySQL Server.  It's minimalistic in functionality compared to Workbench, but it's also substantially more lightweight.  It's essentially a GUI that's well-suited to a power user who doesn't want "help," just an interface that's more usable for running queries than the command line.  (As a DBA all day every day, I use Query Browser and the command line client almost exclusively.)

They're all free, of course.

There's nothing proprietary about MySQL Community Edition, so there's really nothing you can to do, administratively, to a MySQL server that specifically requires one of the official clients.  There are others out there, third party graphical tools that can provide, for all practical purposes, any functionality that "official" clients can provide.  They all have their respective strengths and weaknesses.

One thing to keep in mind, however, is that if you encounter any unusual behavior in any client, is that you should verify that behavior using the mysql command line client, to confirm whether it's the client you're using or the server behaving unexpectedly but consistently among different clients.


Why are my statistics getting out of date so quickly? I've got a large table (300 million rows), with about 1 million rows added per day. This is time-series data, so I have the following setup:

CREATE TABLE Readings (
    Id INT PRIMARY KEY,
    TrackerId INT,
    DateProcessed DATETIME,
    -- About 10 other columns of data
)

CREATE INDEX IX_TrackerId_DateProcessed
ON Readings (
    TrackerId,
    DateProcessed
) INCLUDE (
    -- all the other columns
) WHERE (
    TrackerId IS NOT NULL
)

When we view the data, I usually run something like the following query:

SELECT Latest.*
FROM Trackers
CROSS APPLY (
    SELECT TOP 1 *
    FROM Readings
    WHERE TrackerId = Trackers.Id
    ORDER BY DateProcessed DESC
) Latest

This used to work fine. Now, it takes forever to generate the query plan, even when I explicitly tell it to use the index. The only solution I have found so far is to update the statistics for the table with the following command:

UPDATE STATISTICS Readings

This takes about ten minutes to run, and then the select query runs fine, but only for a day. Then I have to update the statistics again. Why are the statistics getting out of date so quickly, and what can I do to prevent this?

Points worth mentioning:

I only ever append to the table, I never update or delete rows
There are some old rows with a null value for tracker id, but no more will be added
The database is running at compatibility level 14
I can't enable trace flag 2371 in Azure SQL

Additional answers to questions in the comments

The estimated query plan before updating the statistics

The estimated query plan after updating the statistics

The plans before and after updating the statistics look identical to me. The issue is not that I am getting a bad query plan, but how long it takes to get the plan. Before updating the statistics, I had to wait two minutes for the plan. After updating the statistics, it got the plan instantly.

While writing this, I just realized that it would actually be quicker to wait for the query, and let the cache do the work, rather than update the statistics, but that's still not solving the problem. This query is executed from within Entity Framework, with a timeout of 30 seconds, so two minutes is still too long.

Time spent automatically updating statistics is included in compile time.  The stats update takes 10 minutes, and the compile time for a query that triggers an auto stats update includes that time - which is why the query takes over 10 minutes every now and then.

The modification threshold for an automatic stats update, per the documentation, is

sqrt(table cardinality * 1000)

With your numbers (300,000,000 rows), you hit that threshold at 547,722 modifications.

Since 1,000,000 rows are being added every day, you're bound to hit an auto stats update around twice a day.

Since your query's estimates are good before and after the stats update, one possible solution would be to enable the database level AUTO_UPDATE_STATISTICS_ASYNC setting.  This still let's SQL Server update stats automatically, but it's done asynchronously (so queries don't have to wait on it to happen):

ALTER DATABASE [Live]
SET AUTO_UPDATE_STATISTICS_ASYNC ON;

Note that this affects the entire database, not just that table / statistic.


Get matching part from conditional pattern I'm having a table like this:

|id|key    |value      |
------------------------
| 0|name   |Homer      |
| 1|surname|Simpson    |
| 2|town   |Springfield|

I'm running this query:

SELECT *
FROM Search
WHERE [value] LIKE '%m%'
OR [value] like '%p%'

Is there any chance to know, which of both conditions did match?

Within one statement (so not running each of condition separately).

So for example a result like this:

|id|key    |value      |match  |
--------------------------------
| 0|name   |Homer      |%m%    |
| 1|surname|Simpson    |%m%,%p%|
| 2|town   |Springfield|%p%    |

http://sqlfiddle.com/#!18/bc0b1/3/0

EDIT:
The result may also be like this:

|id|key    |value      |match  |
--------------------------------
| 0|name   |Homer      |%m%    |
| 1|surname|Simpson    |%m%    |
| 1|surname|Simpson    |%p%    |
| 2|town   |Springfield|%p%    |

EDIT 2:
Conditions might be more than two so a generic approach (if possible) would be great

WITH
cte AS ( SELECT value AS pattern            -- or TRIM(value)
         FROM STRING_SPLIT('%m%,%p%', ',')  -- comma is NOT allowed in a pattern
                                            -- or alter your delimiter
       )
SELECT Search.[id],
       Search.[key],
       Search.[value],
       STRING_AGG(cte.pattern, ',') [match]
FROM Search,
JOIN cte ON Search.[value] LIKE cte.pattern -- or LEFT JOIN
GROUP BY Search.[id],
         Search.[key],
         Search.[value]

fiddle

Possibe CTE replace usable on previous server versions (CHAR(9) is used as a delimiter, @pattern declared before cte must be query parameter transferred from client as literal):

DECLARE @pattern VARCHAR(64) = '%m%'+CHAR(9)+'%p%';

WITH cte AS ( SELECT LEFT(@pattern,CHARINDEX(CHAR(9),@pattern)-1) pattern,
                     SUBSTRING(@pattern, CHARINDEX(CHAR(9),@pattern)+1, LEN(@pattern)) slack
              UNION ALL
              SELECT LEFT(slack,CHARINDEX(CHAR(9),slack)-1),
                     SUBSTRING(slack, CHARINDEX(CHAR(9),slack)+1, LEN(slack))
              FROM cte
              WHERE CHARINDEX(CHAR(9),slack) > 0
              UNION ALL
              SELECT slack, ''
              FROM cte
              WHERE CHARINDEX(CHAR(9),slack) = 0
                AND slack != ''
            )

fiddle

STRING_AGG() must be replaced too...


SQL 2005 query all databases and output to one table Need a little help with the below:

I've created a query which basically will give me all the stored procedures in all the table for every database and I want to try and amend it so that the output queries are output to one table.

Any suggestions on how to improve this?

DECLARE @command varchar(1000)
SELECT @command =

'USE [?] select *,  DB_NAME() as DatabaseName
from sys.all_objects where type=''p'' and is_ms_shipped=0 '

EXEC sp_MSforeachdb @command

select  *,DB_NAME() as DB_NAME
into    mytable
from    sys.all_objects
where   1=2

DECLARE @command varchar(1000)
SELECT @command =

'USE [?] select *,  DB_NAME() as DatabaseName
from sys.all_objects where type=''p'' and is_ms_shipped=0 '

insert into mytable  EXEC sp_MSforeachdb @command

Using table variable

declare  @mytable table (
    [name] [sysname] NOT NULL,
    [object_id] [int] NOT NULL,
    [principal_id] [int] NULL,
    [schema_id] [int] NOT NULL,
    [parent_object_id] [int] NOT NULL,
    [type] [char](2) NULL,
    [type_desc] [nvarchar](60) NULL,
    [create_date] [datetime] NOT NULL,
    [modify_date] [datetime] NOT NULL,
    [is_ms_shipped] [bit] NULL,
    [is_published] [bit] NULL,
    [is_schema_published] [bit] NULL,
    [DB_NAME] [nvarchar](128) NULL
    )

DECLARE @command varchar(1000)
SELECT @command =

'USE [?] select *,  DB_NAME() as DatabaseName
from sys.all_objects where type=''p'' and is_ms_shipped=0 '

insert into @mytable  EXEC sp_MSforeachdb @command

select * from @mytable


Microsoft SQL Server CAL - What is it? I have trawled through masses of documentation both on Microsoft website and Stack Exchange, yet I have failed to find answers to some basic questions:

What is a CAL? A license code that I need to enter? Or a legal document allowing me to use the software in a certain way?
Let's say someone simply installs SQLServer 2016, restores a DB and points 200 clients to it via TCP/IP protocol on port 1433. Will the clients be able to connect and use the DB server? If not, what software exactly and on what side (DB server/client) will stop it from working?

If you feel that I haven't searched well enough, would you be so kind and provide a link per each point above, leading to an exact answer (as opposed to several paragraphs of vague text that is usually found on MS website)

CAL is a legal CONSTRUCT. I say construct because you may not even get a dedicated document, depending on what papers you sign and agree on with Microsoft.
CAL's are not technically enforced. That said, if you ever get audited you will realize that you are still - possibly criminally - liable. It is legal construct and legal protection to software use applies, whether technically enforced or not.


importing MySQL dump to SQL Server 2012 I have a database schema from MySQL which I want to import to SQL SERVER, I have tried dumping from MySQL with compatibility for SQL Server, but with no success. Tried dumping as Excel still no dice. Does anyone know another way to import this database with no records from MySQL to SQL SERVER 2012? Thanks for your time.

I recently had to do essentially the same thing, except that I wanted to migrate MySQL tables and data to SQL Server. I tried:

using phpMyAdmin to Export the table definitions with "MSSQL" compatibility: The resulting SQL script would not execute in SQL Server Management Studio (SSMS) due to syntax errors.
using HeidiSQL to export the MySQL database directly to SQL Server: HeidiSQL also generated SQL statements that SQL Server would not accept.
using MySQL Workbench: I could not find a way to migrate a database from MySQL to SQL Server.
using MySQL ODBC and the SQL Server Import and Export Wizard: The wizard crashed (i.e., the Windows "[Application name] has stopped working..." error) when trying to access the MySQL database.

The only method that didn't fail outright was to pull the tables from MySQL into a Microsoft Access .mdb file (!) via MySQL ODBC, then import that into SQL Server.

However, even that was an incomplete solution because the transfer from MySQL to Access omitted all of the primary keys, indexes, and AUTO_INCREMENT attributes. Still, it did transfer the tables and data, which was my main objective. I just had to write some T-SQL to re-create the indexes and whatnot.

Since you don't need to transfer any data you might want to consider just hacking the mysqldump SQL code (good old Find & Replace...) to the point where SQL Server will execute it.


Query taking non-consistent time to execute I have a query which takes large time to execute sometimes.When i checked the speed it was taking 15 seconds.But actually it should run faster than that.When i again checked the query it again executed with in 11 sec.Then i tried removing cast used in query and when executed it ran just in 8 seconds.But when i again the original query it takes less time.I checked in several computers running the query.But in some cases i get output just in 1 or 2 seconds also.So there is no consistency with the time taken to execute.So i am not able to find why this happens?

Below is the query i used to test

SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
FROM [company].dbo.[customer]
WHERE ( Charindex('9000413237',CAST([company].dbo.[customer].[Phone no] AS VARCHAR(MAX)))>0 )

Here customer table has about 30 columns.In that 'Customer name' is of datatype varchar(255),'Sl_No' is of int,'Id' is of int and 'Phone no' is of varchar(255).

when i execute the same query(with cast) time required to execute is different even in same PC for different trials.It varies from 1 second to 14 second(i tried number of trials executing query). The variation in time may be due to network lateny,cache,table in high usage by other user etc.
But is there any tool or method by which i can find the reason for the time taken(may be slow or fast) to execute?

update
I just tried to check IO stalls by using the query

SELECT
    mf.physical_name,
    ( io_stall_read_ms / ( 1.0 + num_of_reads )) as [avg read wait],
    ( io_stall_write_ms / ( 1.0 + num_of_writes )) as [avg write wait],
    i.name,
    fs.io_stall
    FROM    sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
    INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id AND fs.[file_id] = mf.[file_id]
    INNER JOIN sys.databases as i on fs.database_id = i.database_id
order by i.name desc

In the output my database 'company' was taking high io stall.below is the values i got for that database.(The original output had iostall of all database files)

avg read wait-37.17162364    avg write wait -20.66666667  io_stall-4813081
So the database i used in query had high iostall .What is the reason for it?

I added Phone no as non clustered index and  just rewrite the query as

SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
 FROM [company].dbo.[customer]
  WHERE [company].dbo.[customer].[Phone no]  LIKE '%9000413237%'

Now the query executes within 1 second. I cant use LIKE '9000413237%' as i require phone number to be searched at any part of the column.So i hav to use LIKE '%9000413237%'.But in several articles it is mentioned that using like '%abc%' will not benefit indexing only 'abc%' can benefit from indexing. But in my case using LIKE '%9000413237%' helped me a lot with indexing.So i have doubt that why people say using LIKE '%9000413237%' does not benifit

One possible explanation:

The first time you run your query it's probably not in cache, which means it has to be read from disk and stored in memory.  If your query remains the same and execution plan doesn't change, the time it takes to execute the same query will be less.

If the SQL Server you are using is on your own computer and/or others will not be affected, you can clear the plan cache with the DBCC FREEPROCCACHE to see if the execution times remain constant.

You can check to see which queries are in cache by running the following command

SELECT text,objtype, refcounts, usecounts, size_in_bytes,
    disk_ios_count, context_switches_count, pages_allocated_count, original_cost,
    current_cost
FROM sys.dm_exec_cached_plans p
    CROSS APPLY sys.dm_exec_sql_text(plan_handle)
    JOIN sys.dm_os_memory_cache_entries e
    ON p.memory_object_address = e.memory_object_address
WHERE cacheobjtype = 'Compiled Plan'
ORDER BY objtype desc, usecounts DESC

If you see the query

SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
FROM [company].dbo.[customer]
WHERE ( Charindex('9000413237',CAST([company].dbo.[customer].[Phone no] AS VARCHAR(MAX)))>0 )

Then you know its in cache.

If clearing out the cache doesn't make the execution times more consistent, please include the actual execution plan, by selecting CTRL+M before executing your query.

Another possible explanation:
Since you are testing from different computers, there could be additional network latency involved when SQL Server sends the data back to the computer you are testing from. You can verify this by using task manager and opening up the Networking Tab when running your query from another server.

Update 15:26

If bandwith was an issue , one solution would be to find out:
1. How much bandwith exists between your clients and server
2. How much bandwith you are actually using

Your network administrator or sysadmin can tell you how much bandwith exists between you and other machines within your domaine. Using Process Explorer or perfmon, you can easily determine how much of that bandwith is being used by SQL Server and other processes. Just keep in mind that when you are executing a query on the server, the results don't have to travel across a network cable as they would with a remote client, which would change execution times.

Your inital question though is why you are seeing inconsistent execution times for the same query. So a discussion about networks and bandwith would be off topic.

Update 09:27

Here are two screenshots of Process Explorer that will help you find the graphs that will allow you to analyze, CPU,IO,Memory and Network traffic.

Main Screen

Graphs


What exactly is "pinning" in relation to indexes? When one talks about "pinning" in indexes, what exactly is this? Is there some other word/term I can search for, as google has not provided any solutions.

It is part of a test question, where students were asked to define and describe pinning in relation to indexes.

Does anyone have an idea?

Search for Keep Pool.  You're probably better off not using it unless you know its purpose and limitations very well and it fits the problem you are trying to solve.


I'm getting the below error while trying to ADD CONSTRAINT in Azure DataWarehouse Msg 104467, Level 16, State 1, Line 1
Enforced unique constraints are not supported in Azure SQL Data Warehouse. To create an unenforced unique constraint you must include the NOT ENFORCED syntax as part of your statement.

If you look at the list of Unsupported Table Features for Azure SQL Data Warehouse, unique constraints is still on that list as at today:

It may be that they get supported at some point in the future but I wouldn't hold your breath for this MPP system.  As a workaround, get your ELT/ETL process to ensure records are unique during ingest for example, or make an assumption where the data has been imported from an upstream system which does have unqiue constraints, that it is valid.  Do some validation via reports / queries to check.


It is possible to export a MySQL database without BLOB fields? If I have a MySQL database with some tables where one have a BLOB column, It is possible to export the whole database excluding the BLOB fields? If not, could you suggest a solution to work-around it?

mysqldbexport utility does this, I have myself not tried though,
It has --skip-blobs which prevents exporting of blob data.
For more information refer this:

http://dev.mysql.com/doc/workbench/en/mysqldbexport.html


How do I format the output from OBJECT_DEFINITION so that it looks the same as sp_helptext? object_definition and sp_helptext both return the source code of a stored procedure - but how can I use object_definition and get the source code including the line breaks as the sp_helptext currently does?

for example in the code below I create a stored procedure:

if OBJECT_ID('usp_radhe') is not null
   drop procedure usp_radhe
go
-- this procedure is just a test
-- it just returns a date in the past
-- how will I get its source code?
create procedure usp_radhe as
begin
select dateadd(dd,-31,GETDATE())
end

using sp_helptext I get a nice view of the source code:

sp_helptext 'usp_radhe'

using object_definition I get the source code in a single line and that is not good for me:

select OBJECT_DEFINITION(object_id('usp_radhe'))

I had similar problem. First turn on this option of SSMS - it is turned off by default:

SSMS/Tools/Options/Query results/SQL Server/Results to grid/Retain CR/LF on copy or save

Close SSMS, and run it again to get it work.

I use this query to get the full text of object definitions:

create view v_get_text_of_objects as

select top 100000
  [name]
, definition = replace(replace(object_definition(object_id),'create view','alter view'),'create proc','alter proc')
, len = len(object_definition(object_id))
, type
, type_desc
, create_date
, modify_date
from  sys.objects
where object_definition(object_id) like '%your text to find in sp%'
order by type, create_date desc

I like to display LEN of the sp text, so that I am sure it is not cropped.


Need to create a "summary grid" query? I have multiple transaction records in table A like this:

TXN#  DATE        CUSTOMER
1001  10/05/2015  Cust 1
1002  02/03/2016  Cust 2
1003  02/06/2016  Cust 1
1004  03/25/2016  Cust 3
1005  03/26/2016  Cust 1
1006  03/26/2016  Cust 2
1007  04/06/2016  Cust 3

I would like to create a query or a new table that contains the total number (count) of transactions for each month by CUSTOMER like this:

CUSTOMER  Jan   Feb   Mar   Apr   May ... etc.
Cust 1      0     1     1     0     0 ... etc.
Cust 2      0     1     1     0     0 ... etc.
Cust 3      0     0     2     0     0 ... etc.

I understand a query that gives me counts for a specific time range, but I don't know how to do it for multiple months in one nice result grid.

Is this possible?

I was thinking of just updating the total counts into a table, and manipulating the second table, but I need to bone up on that.

This is the code I'm using to do the current search:

/* Declarations */
DECLARE @SearchStartDate varchar(10);
DECLARE @SearchEndDate   varchar(10);
SET @SearchStartDate = '2016-01-01';
SET @SearchEndDate   = '2016-12-31';

/* Count by customer */
select e.custaccountid as "Company", count (e.custaccountid) as "Orders"
from oriediorder as e  WITH (NOLOCK)
join salestable as s  WITH (NOLOCK)
on (e.dataareaid = s.dataareaid) and (e.salesid = s.salesid) and (e.PONUMBER = s.PONUMBER)
group by e.custaccountid
order by Orders desc;

It requires joins and groupings.

Suggestions?

You should be able to turn your top set of data into the tabular set of data below it by doing something like this:

SELECT
    Customer,
    Count(CASE Month(Date_Column) WHEN 1 THEN [TXN#] ELSE Null END) AS Jan,
    Count(CASE Month(Date_Column) WHEN 2 THEN [TXN#] ELSE Null END) AS Feb,
    Count(CASE Month(Date_Column) WHEN 3 THEN [TXN#] ELSE Null END) AS Mar,
    Count(CASE Month(Date_Column) WHEN 4 THEN [TXN#] ELSE Null END) AS Apr,
    Count(CASE Month(Date_Column) WHEN 5 THEN [TXN#] ELSE Null END) AS May,
    Count(CASE Month(Date_Column) WHEN 6 THEN [TXN#] ELSE Null END) AS Jun,
    Count(CASE Month(Date_Column) WHEN 7 THEN [TXN#] ELSE Null END) AS Jul,
    Count(CASE Month(Date_Column) WHEN 8 THEN [TXN#] ELSE Null END) AS Aug,
    Count(CASE Month(Date_Column) WHEN 9 THEN [TXN#] ELSE Null END) AS Sep,
    Count(CASE Month(Date_Column) WHEN 10 THEN [TXN#] ELSE Null END) AS Oct,
    Count(CASE Month(Date_Column) WHEN 11 THEN [TXN#] ELSE Null END) AS Nov,
    Count(CASE Month(Date_Column) WHEN 12 THEN [TXN#] ELSE Null END) AS Dec
FROM
    Table_name
WHERE
    Date_Column BETWEEN @SearchStartDate AND @SearchEndDate
GROUP BY
    Customer;

This works because aggregate functions like SUM or COUNT will not include Null values.  The case statement for each month outputs Null if the data is not for the particular month, so the data left being aggregated is only for the one specific month.


Percona server Master-Slave version query I have a master with percona server (5.6.25) running in RHEL host and a slave of the same version. I'm facing some corruption issues in replication slave and I'm thinking of rebuilding the slave from scratch.

Just a query, can I have the slave of version 5.6.31 instead of the original master version? What could be the issues that could occur?

yes, it will work. generally it is recommended that slaves be the same or of higher version than its master.


Disadvantages of reorganizing all tables nightly I consider scheduling a SQL Server job that reorganizes all tables nightly. We don't have enterprise, so we cannot rebuild online (this means we need to reorganize). We also don't want to invest too much time in fragmentation and space maintenance so we are seeking a solution that can be implemented quickly and does not require further time investments in the future.

Testing on a restored production backup showed that reorganize is almost as good as rebuild for reducing fragmentation and regaining space.

Is this a reasonable idea? Does this scheme create problems for availability or maintainability? Is this a reasonable long-term solution?

There are better ways - just reorganizing all indexes nightly can be quite wasteful. Why even bother reorganizing an index that is 12% fragmented? Why reorganize a 10GB index every night if it takes 30 minutes and you only reduce fragmentation by 2% or 3%? How much effort should you spend reorganizing an index that is largely or completely in memory anyway - sure you save some disk space, but you have to pull the index out of buffer to do so - is the space savings worth the performance hit that will cause?

There are free scripts out there that help make some of these decisions for you, and it's easy to override the defaults:

Ola Hallengren: http://ola.hallengren.com/
Michelle Ufford (@sqlfool): http://sqlfool.com/2011/06/index-defrag-script-v4-1/

SentryOne also has a tool (not free) called SQL Sentry which goes quite a bit further than these by offering full historical reporting, broad to granular rules and schedules on which indexes to reorganize/rebuild and when (from server-wide to individual index and even partition), support for concurrent operations (should your maintenance window be tight), drag-and-drop calendar for scheduling, and the ability to assess exactly how the work you're doing affects performance.


How to change pgAdmin 4 to display rows retrieved and runtime? I've just switched from pgAdmin III to pgAdmin 4, and while overall it seems a lot slicker in terms of UI and presentation, I'm missing a few things from the previous version. In particular, I would show the execution time and number of rows retrieved at the bottom right on the results for selections. In 4, I see a small green popup that contains that information, but disappears after a few seconds. I've found the setting in File->Preferences->SQL Editor->Display called "Query info notifier timeout" that lets me keep the popup there indefinitely until clicked. I also don't see any kind of ongoing runtime display while a query is running. Is there any way to display any of this information more permanently?

It's kind of a shame that there is no good solution to this. I found that adding EXPLAIN ANALYZE to the beginning of my query will display results. Some documentation can be found here.

Example:

EXPLAIN ANALYZE
select * from schema.my_table limit 1000

Yields:

"Limit  (cost=0.00..168.48 rows=1000 width=2644) (actual time=0.048..5.214 rows=1000 loops=1)"
"Append  (cost=0.00..13935338.46 rows=82710326 width=2644) (actual time=0.045..4.880 rows=1000 loops=1)"
"Seq Scan on event_analysis_vw  (cost=0.00..459263.46 rows=3764446 width=3186) (actual time=0.040..4.579 rows=1000 loops=1)"
"Seq Scan on dva_event_analysis_field  (cost=0.00..6640119.59 rows=43856549 width=2623) (never executed)"
"Filter: (is_deleted IS NULL)"
"Seq Scan on dva_event_analysis_field_archived  (cost=0.00..6046496.61 rows=35089331 width=2612) (never executed)"
"Filter: (is_deleted IS NULL)"
"Total runtime: 5.977 ms"


Structured .TXT Data To A Proper DBMS I'm presented with multiple .txt documents (each with its own set of columns) containing rows separated by some type of delimiter (which is different across the files). Each .txt file has different amount of columns/names and they are huge in size, some are split into 100 MB chunks. The data may contain malformed rows.

My job is to import them to an appropriate DBMS that will fulfil these requirements:

Importing speed should not be too bad. Few hours is acceptable for 700m rows
Importing should support ignoring malformed rows
Querying the data should support pattern matching such as %m% in SQL
Read operations are more important than write. It's required that once everything is imported, the read should be within milliseconds
There will be many tables. It's required to be easy to query all tables

I have already tried to use relational DBMS (MySQL, PostgreSQL), but they don't support skipping malformed rows (which is a huge issue). I've tried mongoDB but some data contains double quotes and I'm unsure if the import speed is good enough.

I recently even tried using Cassandra, but it's pretty limited on pattern matching and multiple table querying.

This task is not easy, and I've spent a lot of time reading and trying to find a solution by my own. I'd very much appreciate if someone could help me out to solve this.

The data is already extracted and given, cannot modify that process. Transform is not possible as the data size is beyond the limit of manual editing. Loading is the step I'm struggling with. I'm trying to find a DBMS that can do such thing.

I've thought a lot about NoSQL DBMS but they do not fulfil all requirements. I think SQL such as PostgreSQL would do it, but I have trouble inserting the data as it does not support skipping malformed rows.

Now that I think about it, it's more structured data than unstructured. So it would fit perfectly for SQL DBMS, but the only issue is that the data contains some malformed rows and I need quite good import speed.

Two Rows from file of format (username:email):

Valid row: admin:email@hotmail.com
Malformed row (contains extra column): admin:my:email@hotmail.com

When previously faced with a similar issue I ended up

writing a pre import parser (c)which ran through the extracted data and created a file of valid entries and a file of malformed entries
Import the valid entries Into
Sql server using the import wizard which was used to create a schedule job for each of the import file types
Once imported there were several jobs that further manipulated the data and copied to the final end user tables.

The main issue is identifying the malformed data
And handling it and ensuring that you use the same pattern where possible for all files.


Selecting parent and child data I have an application front end which speaks to a PostgreSQL database. I'm trying to find the most efficient way to extract a structure similar to the below.

CREATE TABLE people (
  person_id SERIAL PRIMARY KEY,
  fname     VARCHAR
);

CREATE TABLE items (
  person_id INTEGER REFERENCES people (person_id),
  item_id   SERIAL PRIMARY KEY,
  title     VARCHAR
);

INSERT INTO people (fname) VALUES
  ('Bob'),
  ('Jim'),
  ('Geoff');

INSERT INTO items (person_id, title) VALUES
  (1, 'Cat'),
  (1, 'Dog'),
  (1, 'Monkey'),
  (2, 'Elephant');

My current approach would be to list all of the items, then (within the application) iterate over each and run a SELECT. Something similar to this Pseudo code:

items = db.Query(SELECT * FROM people);
for item in items
   itemsub = db.Query(SELECT * FROM items WHERE id = item.person_id)

There are a number of reasons why I don't like this.

It requires a minimum of 1 + N(People) queries to extract a simple data structure. For a page containing 1000 items, this would produce a ton of network traffic.
The application has to manually iterate over the list atleast twice more after the database, one to build the better structure and a second to render it.
It seems like something a database should be able to do

One other alternative would be to perform a JOIN, but this would result in extra processing to remove duplicates in the application

The resulting structure should be relatively simple:

peopleItems{
   array people{
             array items{}
         }
}

However, I can't for the life of me work out the best approach.

This is just an idea.

test=# CREATE INDEX items_idx ON items (person_id);
CREATE INDEX

test=# SELECT person_id, fname,
    (SELECT array_to_string(ARRAY(SELECT title FROM items WHERE items.person_id = people.person_id ), ',')) AS titles
 FROM people;
 person_id | fname |     titles
-----------+-------+----------------
         1 | Bob   | Cat,Dog,Monkey
         2 | Jim   | Elephant
         3 | Geoff |
(3 rows)

I don't know what application language  you use (PHP, Ruby, or others), but all languages can easily parse this result.

I made dummy data and done EXPLAIN. The result is shown below:

test=# EXPLAIN
SELECT person_id,
fname,
(SELECT array_to_string(ARRAY(SELECT title FROM items WHERE items.person_id = people.person_id ), ',')) AS titles
 FROM people;
                                       QUERY PLAN

--------------------------------------------------------------------------------------
---
                                        QUERY PLAN

--------------------------------------------------------------------------------------
-----
 Seq Scan on people  (cost=0.00..984256.31 rows=9990 width=8)
   SubPlan 2
     ->  Result  (cost=98.50..98.51 rows=1 width=0)
           InitPlan 1 (returns $1)
             ->  Bitmap Heap Scan on items  (cost=19.81..98.50 rows=455 width=16)
                   Recheck Cond: (person_id = people.person_id)
                   ->  Bitmap Index Scan on items_idx  (cost=0.00..19.70 rows=455 widt
h=0)
                         Index Cond: (person_id = people.person_id)
(8 rows)

This query uses Bitmap Index scan, so it can be run very fast.

Please don't forget to create index of person_id in the items table.


Stored Procedure With an Optional Parameter Limiting Rows Returned if Specified How would one go about having a sproc that allows a consumer to optionally specify the number of rows returned?

If no row count is specified, then all rows returned.

The "easiest" way is to parameterize input to TOP. The number I'm using is the maximum value for BIGINT. You'll probably never have more rows than this returned by a query.

USE master
GO

CREATE PROCEDURE dbo.Limiter (@Top BIGINT = 9223372036854775807)
AS
BEGIN

SELECT TOP (@Top) *
FROM sys.databases

END

EXEC dbo.Limiter @Top = 1

EXEC dbo.Limiter

Just be careful, because you can introduce parameter sniffing symptoms here.

If you review the plans for both of these, the TOP operator for both is expecting one row.

Whichever input to top executes first will be cached, unless you recompile, or add an OPTIMIZE FOR hint.

You should also be aware that adding a TOP operator may introduce row goals.


Oracle: using "as of" clause with table aliases? I can run this flashback query with no problem:

select x from a as of timestamp sysdate;

But if I use a table alias I get an error.

select foo.x from a foo as of timestamp sysdate;
ORA-00933: SQL command not properly ended

How can I use "as of" with table aliases?

The table alias follows the "as of" clause.

select x from a as of timestamp sysdate foo;


How can I determine what's a better configuration for my app? I'm currently using Postgres 12 on AWS RDS and am using their default configuration for the database.
I use a gem called PgHero that advises me to use PGTune to improve the efficiency of the database.  After inserting my inputs on https://pgtune.leopard.in.ua/
# DB Version: 12
# OS Type: linux
# DB Type: web
# Total Memory (RAM): 7 GB
# CPUs num: 2
# Data Storage: ssd

It gives me the advice to update my DB configuration to:
max_connections = 200  # AWS has: LEAST({DBInstanceClassMemory/9531392},5000)
shared_buffers = 1792MB  # AWS has {DBInstanceClassMemory/32768}
effective_cache_size = 5376MB # AWS has {DBInstanceClassMemory/16384}
maintenance_work_mem = 448MB  # AWS has GREATEST({DBInstanceClassMemory*1024/63963136},65536)
checkpoint_completion_target = 0.7  # AWS has 0.9
wal_buffers = 16MB # AWS is not set
default_statistics_target = 100  # AWS is not set
random_page_cost = 1.1  # AWS is not set
effective_io_concurrency = 200 # AWS is not set
work_mem = 9175kB # AWS is not set
min_wal_size = 1GB  # AWS is 192MB
max_wal_size = 4GB  # AWS is 2048MB
max_worker_processes = 2 # AWS is 8
max_parallel_workers_per_gather = 1 # AWS is not set
max_parallel_workers = 2 # AWS is not set
max_parallel_maintenance_workers = 1 # AWS is not set

I'm not sure whether to use PGTune or just leave RDS defaults?  How does one typically determine if there is performance updates? Am I in over my head?

The correct configuration depends on your hardware, your database size and your workload, none of which we know.
Tune conservatively at first, setting shared_buffers and work_mem to some reasonable values based on database size and workload, and setting maintenance_work_mem, effective_cache_size, effective_io_concurrency and random_page_cost according to your hardware. Finally,  configure logging.
Adjust these and other parameters based on the findings from a realistic load test.


Adding index - There is insufficient system memory in resource pool 'default' to run this query Here's the query I'm trying to run in order to add an index to my new memory optimized table

ALTER TABLE [s_Dim_Response_ram]
ADD INDEX response_ram_clientsurveydid NONCLUSTERED ([Clientid],[SurveyDefID])

And here's the error that I get:

There is insufficient system memory in resource pool 'default' to run this query.

The table has 23 million rows in it, and both columns in the index I'm trying to add are ints.

The server has 32GB of ram on it, and SQL Server is set to use a max of 26GB of ram.

It was upgraded from SQL 2008 R2 to SQL Server 2017 over the weekend, so I could try to take advantage of Memory Optimized tables. I made a copy of an existing regular table schema but as a memory optimized, and then tried to add a index as shown above.

How do I fix this error? Do I need more memory for SQL Server? Or more free system memory? Are there settings I could change to help?

edit: I tried deleting all the rows from the table first, and then adding the index. I still get the same error with an empty table.

Here are my memory performance counters:

counter_name                                                    MB
Stolen Server Memory                                        15682.015625
Free Memory                                                 4106.617187
Lock Memory                                                 8.093750
Log Pool Memory                                             3.140625
Connection Memory                                           2.070312
Optimizer Memory                                            1.554687
Cursor memory usage                                         0.031250
Cursor memory usage                                         .031250
Cursor memory usage                                         0.000000
Granted Workspace Memory                                    0.000000

Here are my top 20 Clerks

    type                mb
Total               22085.913770
MEMORYCLERK_XTP         14733.703125
MEMORYCLERK_SQLBUFFERPOOL   6877.562500
CACHESTORE_SQLCP        104.601562
CACHESTORE_OBJCP        94.734375
MEMORYCLERK_SOSNODE     54.804687
MEMORYCLERK_SQLSTORENG      54.156250
CACHESTORE_PHDR         31.093750
USERSTORE_SCHEMAMGR     27.031250
MEMORYCLERK_SQLCLR      21.296875
MEMORYCLERK_SQLTRACE        17.179687
USERSTORE_DBMETADATA        13.648434
CACHESTORE_SYSTEMROWSET     13.539053
MEMORYCLERK_SQLGENERAL      8.132812
OBJECTSTORE_LOCK_MANAGER    8.093749
USERSTORE_TOKENPERM     5.077892
MEMORYCLERK_SQLLOGPOOL      3.140625
USERSTORE_OBJPERM       2.257797
MEMORYCLERK_XE          2.195312
MEMORYCLERK_SQLCONNECTIONPOOL   2.000000
MEMORYCLERK_SQLOPTIMIZER    1.593750

My SQL version is Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)   Aug 22 2017 17:04:49   Copyright (C) 2017 Microsoft Corporation  Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3  (Build 9600: ) (Hypervisor)

The problem is that the amount of memory available for memory-optimized data is not a fixed size. It depends on system needs.

Please see my post here:
http://nedotter.com/archive/2018/01/in-memory-oltp-resources-part-4-oom-the-most-feared-acronym-in-all-of-in-memory-oltp/

Bottom line is that you need more memory.


Applying Transaction Logs to an existing Database I am wondering if this possible, I can not seem to apply another transaction log.

I have restored the Full database backup, the differential backup and my last Transaction Log WITH RECOVERY. If later on I need to apply the next Transaction Log in sequence to it, what do I need to do?

I tried applying the Transaction Log and it states:

  The log or differential backup cannot be restored because no files are ready to rollforward.

For each time I need to add a new Transaction Log, do I have to go through the steps of restoring the backup database, restoring the differential database and all transaction logs again?

You have to start over and restore full and diffs with no recovery and then the tlogs  with stand by option. This will allow you to restore tlogs later as well.

You can configure logshipping with option of stand by, so you can read from the secondary database.

There will be a .tuf (transaction undo file) file created when you restore tlogs with standby option.

For manually restoring tlogs use restore gene or you can just use logshipping for automated restores.


What is the cost of changing the search path in postgres in a schema-per tenant model I am working on a multi-tenant storage gateway.

Each tenant has his own schema -- the schema for each tenant is a meta-schema so each tenant schema is identical. The DB interactions only occur through a stored procedure API. The stored procedure layer is on the public schema. The stored procedures rely on the search_path being set to <TENANT_SCHEMA>;public.

The application layer prepares statements against the stored procedures in the public schema.

I have a stored procedure for switching the tenant that looks as follows:

CREATE OR REPLACE FUNCTION domains_switch(domain DOMAIN_NAME) RETURNS VOID AS $$ BEGIN
  IF EXISTS (SELECT NULL FROM public.domains WHERE domain_name = domain) THEN
    EXECUTE format('set search_path=%s,public;', domain);
  ELSE
    RAISE 'domain "%" does not exist',domain USING ERRCODE = 'AX001';
  END IF;
  RETURN; END;
$$ LANGUAGE plpgsql;

plpgsql stored procedures manage plans themselves which means I only need to prepare the api statements once per backend in the gateway. I suspect the stored procedure's internal cached plans are invalidated when I switch the schema.

I am planning to add a layer to the gateway's connection pool management to minimise search_path switches. Is my intuition correct? Is this the right thing to do?

I would say your intuition is correct. Changing the search_path causes Postgres to start from scratch with prepared statements. The manual on PREPARE:

  Also, if the value of search_path changes from one use to the next,
  the statement will be re-parsed using the new search_path. (This
  latter behavior is new as of PostgreSQL 9.3.)

And SQL statements inside PL/pgSQL functions are treated like prepared statements.

Especially while working with connection poolers (same connection stays open), it will help performance if you keep switching the search_path to a minimum.

The extra cost is not too high for simple statements, but it may be a noticeable effect for more complex queries.