texts: Can a trigger procedure in PostgreSQL learn its own name?

texts:
A trigger procedure in PostgreSQL can learn the name of the trigger that fired it from the special variable TG_NAME. But can it also learn its own name?

I would like to keep additional configuration information that applies per trigger procedure in a new table and need the trigger procedures's name (preferrably not retyped manually) to select values from that table.


texts: PostgreSQL invalid frontend message type 74

texts:
Error

invalid frontend message type 74

I'm getting this error and I have no idea where it's coming from or what it means. I can't find any documentation on frontend message types in the PostgreSQL documentation.

I tried enabling the log of every statement, but this error isn't occurring after any specific statement in the log. The statements are different each time, and when run via pgsql, the statements run without error.

Is there anyway I can print out what statement or message is causing this error? I really don't know how to proceed without knowing the source.

PostgreSQL Version

PostgreSQL 9.4.5 on amd64-portbld-freebsd10.2, compiled by FreeBSD clang version 3.4.1 (tags/RELEASE_34/dot1-final 208032) 20140512, 64-bit


texts: Optimize user segmentation by bytea column

texts:
First i would too explain my problem:
I have around 10 millions of customers in the table. Based on customer attributes, we are creating segments.
My table what joins customer with segments looks like

customer_segmet(customer_id int, segment_id int)

Every 3 hours we have a cronjob, what refreshing segments and reinsert them again to this table.

Each refresh means, delete all customers from segment, and insert again.
Deleting customers are fast enough, but i have problem with insert. Inserting 10mln of rows to this table takes 20 minutes. It is too much and im trying to resolve this problem.

Idea:

I'm thinking to optimize inserts. My idea is remove customer_segment table and instead of this, create "customers" column in segments table. This new column will be bytea type.
To store information if user is in segment, i will generate each time binary file where:

Bit position = customer_id
Bit value = is customer in segment or not.

For my calculation looks like for 10mln of customers i need 10mln of bits what is 1.25MB only. So default for this column will be 1.25mb data of nulls.

After that if i want to add only one customer to the segment, i will execute query:

UPDATE segments SET customers = set_bit(customers, 555, 1);

where 555 is my customer ID.

If i want to check if customer is in the segment, i can do it by query:

SELECT COUNT(1) FROM segment WHERE get_bit(customers, 5555) = 1 AND id = 1

Also to simplify the joins between Customer-Segment i created function:

CREATE OR REPLACE FUNCTION get_segment_customers(segment BYTEA) RETURNS SETOF integer AS
$$
for (let i = 0; i < segment.length; i++) {
    if(0 === segment[i]){
        continue;
    }
    for(let bit = 0; bit < 8; bit++){
        if(!(segment[i] & ( 1 << bit))){
            continue;
        }

        plv8.return_next((i * 8) + bit);
    }
}
$$
LANGUAGE plv8;

And my query to select customers:

select
_c.* from
    get_segment_customers((SELECT data FROM segments where id = 1)) _t
JOIN
    customer _c ON _c.id = _t;

I made test and is fast.

Only about what im scared is query:

UPDATE segments SET customers = set_bit(customers, 555, 1);

I'm not sure, if PostgreSQL each time of this update will overwrite full file or only one bit in the hard disk?

If someone can answer question above and give the feedback about idea and potential problems, i will be happy.

Thank you


texts: How does TIMESTAMP WITH LOCAL TIME ZONE behave when exporting data?

texts:
Consider the following table:

CREATE TABLE MY_DATA (
    MY_DATA_ID NUMBER(38,0) PRIMARY KEY,
    THE_DATA VARCHAR(10) NOT NULL,
    DATA_TIMESTAMP TIMESTAMP (6) WITH LOCAL TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL
);

(Note that TIMESTAMP without further qualification defaults to the data type used above.)

What happens if I export this data using Data Pump and then import it into another database that has a different time zone? Will the stored time be adjusted for the new time zone, or will it behave as though the off set was changed without changing the rest of the data?

For example, if

The original database is US Central time
A row contains 2017-10-25 12:14:38
We import the data into a database using US Eastern time

will the time be adjusted to 2017-10-25 12:14:38 -04:00 (as in replacing the offset without changing the date/time) or 2017-10-25 13:14:38 -04:00 (= 2017-10-25 12:14:38 -05:00)?

My apologies for not testing this myself. I don't have access to another database with a different time zone, but I wish to know to inform my choice of data type.


texts: Conditional relationships? One-to-many relationship using a pivot table for only a type of entity

texts:
I have the following tables:

users:
  - id
  - name
  - user_type_id (references id on user_types)

neighborhoods:
  - id
  - name

homes:
  - id
  - house_number
  - neighborhood_id (references id on neighborhoods)

user_types:
  - id
  - label

The available user_types are resident, neighborhood_admin, super_admin. Each type of user has access to different parts of the system. Only residents must have a relationship with the homes table.

I was thinking adding a pivot table like:

residents:
  - user_id (references id on users)
  - home_id (references id on home)

but I'm not sure if this approach is correct. I've used pivot tables for many-to-many relationships only. Also, using code I would have to check the user_type before assigning a home, but I could just as easily assign a home to a super_admin if I'm not careful.

How can I make the database enforce this restriction so that it doesn't have to be done through code?

UPDATE: I forgot to add, neighborhood_admins must be assigned to a neighborhood. I could add the following table:

neighborhood_admins:
   - user_id (references id on users)
   - neighborhood_id (references id on neighborhoods)

But this creates a problem. The users table now has two different relationships to the neighborhoods table; if the user is a neighborhood_admin, then the relationship is obtained from the pivot table neighborhood_admin but if the user is a resident, then we must first obtain the home relationship and then the home's neighborhood.

I'm thinking I might as well just add a neighborhood_id column to the users table and be done with it, but I don't like those relationship triangles. Also, that column would be null for super_admins so it couldn't be a foreign key.


texts:
Is it good practice and or recommended to reference 2 documents to each other using ref? Like this:

const FirstDocumentId = mongoose.model(
    'FirstDocumentId',
    new mongoose.Schema({
        coolField: String,
        secondDocumentId: {type: Mongoose.Schema.Types.ObjectId, ref: 'secondDocumentId'}
    })
);

const SecondDocumentId = mongoose.model(
    'SecondDocumentId',
    new mongoose.Schema({
        someField: String,
        firstDocumentId: {type: Mongoose.Schema.Types.ObjectId, ref: 'firstDocumentId'}
    })
);


texts: "Error creating foreign key (check data types)…" but they do match…

texts:
I'm getting the following error trying to create a constraint:

  Error creating foreign key (check data types)

Problem is… those column data types do match.

The target is an int(10) unsigned NOT NULL and is the PRIMARY column.
The source, in the same table, is an int(10) unsigned DEFAULT NULL, and the constraint I'm trying to create is a simple ON DELETE RESTRICT, ON UPDATE CASCADE.

Now, of course the target is NOT NULL and the source is NULL, but that's entirely intentional and what I need, and I can't see how could this be a problem. I could understand if it was the other way around…

Is this really the problem? Why? How do I solve it, then? I don't want to be forced to insert a value there even when I don't need one, neither I want to enable NULL on the PRIMARY column, which doesn't make much sense.

What else could be the problem?


texts:
Here is my situation: restored a known good backup (works fine on other machines) that originated on Sql Server 2012 onto a local 2012 install. Created DB as part of the restore. No errors reported.

I am accessing it through a Node.js app, using the Node-SqlServer driver (if that makes a difference). The first time that the app tries to access the db after restore, the db goes into Suspect mode.

I tried deleting the Db restoring from scratch. Same thing.

I was able to "fix" the db by running these commands. After that, the db runs fine (app can access and query the db witout issue). However, this seems like a less than ideal scenario.

Any ideas about what could be causing this and how I might be able to prevent it?

Computer is a laptop with a single SSD. I ran chkdsk and returned a clean bill of health. Note: this has happened with more than one db restore, specifically to this machine. So the circumstantial evidence points to the machine as being at fault here.


texts:
I'm using MYSQL V5.6.24 GPL

I have a database with 200K rows and growing and I'm trying to get the nearest, based on points. I have all the database in InooDB, and to use Spatial data, I create one table with the Points with Mysiam.

everything works great, the problem is that when I sort based on ST_Distance, I'm not using any index, I create a SPATIAL index with the coordinates, and still using file_sort to sort. And Mysql doesn't allow me to use RTREE INDEX

Any idea?


texts: Backup and restore Transparent Data Encrypted database

texts:
Doing my first backup and restore of someone else's sql server database. I have already done a successful backup and restore of one of the databases, but the other two are locked behind Transparent Data Encryption.

Is there a simple way of finding the certificates' files and just copy/pasting them to the new server's filepath and then restore? What's the proper direction for restoration from backup here?

If I do:

select * FROM [master].[sys].[certificates]

I get a bunch of rows that have ## before them. Are these significant? There's only one cert from that output that doesnt have ## and I'm not sure if that applied to a db that I'm not interested in...


texts:
I am working on a db optimization (planing for future project growth) and need some help.

Currently, every table is using uniqueidentifier as PK (clustered index) and we have high index fragmentation (99%). For the new tables we started using bigint as pk but I don't want a nightmare when bidirectional replication phase comes.

I did my research and uniqueidentifier is not a huge problem (except memory), problem is clusters index on that column (http://www.sqlskills.com/blogs/kimberly/guids-as-primary-keys-andor-the-clustering-key/).

Can we do this to solve our fragmentation problem and any replication nightmare:

Add ROWGUIDCOL to PK column
Add another identity column in every table and move that cluster
index (not pk) to that column

Would this new identity column cause the same replication problems as if that was bigint PK?
I know SharePoint db primary keys are GUIDs but I am not happy with its performances and probably security has some part in that decision.
We could reduce fragmentation by using seq. GUIDs but we can't create it on application side or return it with SCOPE_IDENTITY(). OUTPUT inserted.ID hack would be time consuming because we need to rewrite complete application DAL.

Finally, is there any valid solution for this problem? Can we use bigint without any replication problem.


texts:
This is my data set-up, I am needing a way to return only itemsnames and itemonhand where the count of itemonhand is >= 1

I tried simply adding an OR statement to the query, but that is returning items that have a 0 count.

  Declare @TableTest Table (dealerID int, itemname varchar(100), itemonhand int, itemname2 varchar(100), itemonhand2 int, itemname3 varchar(100), itemonhand3 int)
  INSERT INTO @TableTest (dealerID, itemname, itemonhand, itemname2, itemonhand2, itemname3, itemonhand3) VALUES
  (1, 'ball', 0, 'hat', 1, 'sock', 0)
  ,(2, 'ball', 0, 'hat', 0, 'sock', 1)

  Select * FROM @TableTest
  where itemonhand2 = '1'
  OR itemonhand3 = '1'

My desired result set is

1  hat  1
2  sock 1

I just want one row for each dealerID with all items returned on that one row.


texts: Compilation of T-SQL oddities and pitfalls

texts:
Like all programming languages, T-SQL has its oddities when compared to a generic programming language.  Some of these have tripped us up.

It would be nice if there were an already-compiled list so that we could be forewarned of these pitfalls before stepping into them.  What are some T-SQL oddities that we need to be aware of?

We don't mean an exhaustive study of all the specific structures, functions, and constructions unique to the SQL language.  We mean a concise list of the places where SQL diverges consequentially from other standard programming languages or from the usual and expected conventions of programming.  Things that a reasonably skilled programmer coming from another language could be expected to stumble over.

Please contribute to the wiki answer.


texts: Best way to store, query and update 300M rows of data

texts:
I'm struggling to find a solution (preferably DBaaS) that I can rely on for storing and querying some 300M rows of data (roughly 100GB).

The data in question is pretty much numeric. There is also one "description" column that I would want to perform full-text search on. There are couple of "category" columns used for filtering as well. I also want to filter/order search results in many ways (10+ different indexes).

There is no need for doing complex joins since the data is pretty much denormalized. The data is updated heavily: some 50M of records are being replaced every day.

I've first tried with DynamoDB, but it can support only up to 5 indexes, and is not capable of doing full-text search at reasonable speed. I've also considered google's BigQuery, but it is designed for "append-only" data. I'm now considering Redshift, but I'm not sure how it will be able to handle such large number of daily updates.

Any advice would be appreciated!


texts: Is Ola Hallengrens Stored Procedure what we need and safe to use

texts:
I stumbled across a stored procedure and wanted to clarify a few things first as I’ve been told it needs “rigorous review before being executed on any server”, and “testing before we run it on a production server”.

We need to know and understand what the SP does at each stage to ensure no security risks are being introduced and that there is no potential for a data breach or destabilisation of the environment.

Below is our current SP which runs on a Sunday night but within the day the gains are lost and fragmentation is up to 99% on a large number of tables

Additional NONE of our tables have a fill factor at all and all set to zero (equivalent to 100), wondered if this matters

Forgive the email I literally am very new to the Developer world with no prior experience of any DBA related issues.

Our current rebuild / reorganise code :

/****** Object:  StoredProcedure [dbo].[IndexMaintenance_sp]    Script Date: 25/05/2018 13:05:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[IndexMaintenance_sp] @log_only BIT = 1
AS
BEGIN
    DECLARE @tableName VARCHAR(800)
        ,@indexName VARCHAR(800)
        ,@fragmentation FLOAT
        ,@indexId INT = 0
        ,@prevTempIndexId INT = 0
        ,@pageCount INT = 0
        ,@tableId INT
        ,@tempIndexId INT = 0;

    CREATE TABLE #tempIndexes (
        id INT IDENTITY
        ,objectId INT
        ,name VARCHAR(max)
        ,indexId INT
        );

    INSERT INTO #tempIndexes (
        objectId
        ,name
        ,indexId
        )
    SELECT i.object_id
        ,i.name AS Name
        ,i.index_id
    FROM [sys].indexes AS i
    WHERE i.name IS NOT NULL
        AND i.object_id IS NOT NULL;

    WHILE 1 = 1
    BEGIN
        SELECT @tempIndexId = id
            ,@indexId = indexId
            ,@tableId = objectId
        FROM #tempIndexes
        WHERE id = (
                SELECT min(id)
                FROM #tempIndexes
                WHERE id > @prevTempIndexId
                );

        IF @tempIndexId = @prevTempIndexId
            BREAK;

        SET @prevTempIndexId = @tempIndexId;
        SET @indexName = NULL;

        SELECT TOP 1 @tableId = s.object_id
            ,@tableName = OBJECT_NAME(s.object_id)
            ,@indexName = i.name
            ,/*s.index_id,*/ @pageCount = s.page_count /*, s.index_type_desc*/
            ,@fragmentation = s.avg_fragmentation_in_percent /*, s.fragment_count */
        FROM [sys].[dm_db_index_physical_stats](DB_ID(), @tableId, @indexId, NULL, NULL) AS s
        INNER JOIN [sys].indexes AS i ON i.object_id = s.object_id
            AND i.index_id = s.index_id
        WHERE avg_fragmentation_in_percent > 5.0
            AND s.object_id IS NOT NULL
            AND i.name IS NOT NULL;

        IF @indexName IS NULL
            CONTINUE;

        INSERT INTO [ServerHealth].[dbo].[__tblIndexFragmentation] (
            checkDate
            ,databaseId
            ,databaseName
            ,objectId
            ,objectName
            ,indexid
            ,indexName
            ,indexPages
            ,indexFragmentation
            )
        VALUES (
            GetDate()
            ,DB_ID()
            ,DB_NAME(DB_ID())
            ,@tableId
            ,@tableName
            ,@indexId
            ,@indexName
            ,@pageCount
            ,@fragmentation
            );

        IF @pageCount < 1000
            CONTINUE;

        DECLARE @sql NVARCHAR(2000);

        SET @sql = 'ALTER INDEX [' + @indexName + '] ON [dbo].[' + @tableName;

        IF @fragmentation < 30
        BEGIN
            SET @sql = @sql + '] REORGANIZE;';
        END
        ELSE
        BEGIN
            SET @sql = @sql + '] REBUILD;';
        END

        IF @log_only = 0
        BEGIN
            EXEC (@sql);
        END
    END
END
GO


texts:
If we have 2 tables

**user**
+----+------+------+-----------+
| id | user | pass | gender_id |
+----+------+------+-----------+
| 1  | lisa | good |     11    |
| 2  | jack | pass |     10    |
| 3  | paul | 5555 |     10    |
| 4  | rich | none |     10    |
+----+------+------+-----------+

**gender**
+----+--------+
| id | gender |
+----+--------+
| 11 | female |
| 10 | male   |
+----+--------+

If we join  them like so

SELECT user.id, user.user, user.pass, user.gender_id, gender.gender FROM `user` JOIN `gender` on user.gender_id

The result of query

array(4) {
  [0]=>
  array(4) {
    ["id"]=>
    string(3) "2"
    ["type"]=>
    string(6) "jack"
    ["pass"]=>
    string(11) "pass"
    ["gender"]=>
    string(1) "male"
    }
  [1]=>
  array(4) {
    ["id"]=>
    string(3) "3"
    ["type"]=>
    string(6) "paul"
    ["pass"]=>
    string(11) "5555"
    ["gender"]=>
    string(1) "male"
    }
  [2]=>
  array(4) {
    ["id"]=>
    string(3) "4"
    ["type"]=>
    string(6) "rich"
    ["pass"]=>
    string(11) "none"
    ["gender"]=>
    string(1) "male"
    }
  [3]=>
  array(4) {
    ["id"]=>
    string(3) "1"
    ["type"]=>
    string(6) "lisa"
    ["pass"]=>
    string(11) "good"
    ["gender"]=>
    string(1) "female"
    }

The order has now changed even though we did not specify this.(user lisa is last)
If we change lisa gender_id to 11, and run the same query, the result will be

array(4) {
  [0]=>
  array(4) {
    ["id"]=>
    string(3) "1"
    ["type"]=>
    string(6) "lisa"
    ["pass"]=>
    string(11) "good"
    ["gender"]=>
    string(1) "male"
    }
  [1]=>
  array(4) {
    ["id"]=>
    string(3) "2"
    ["type"]=>
    string(6) "jack"
    ["pass"]=>
    string(11) "pass"
    ["gender"]=>
    string(1) "male"
    }
  [2]=>
  array(4) {
    ["id"]=>
    string(3) "3"
    ["type"]=>
    string(6) "paul"
    ["pass"]=>
    string(11) "5555"
    ["gender"]=>
    string(1) "male"
    }
  [3]=>
  array(4) {
    ["id"]=>
    string(3) "4"
    ["type"]=>
    string(6) "rich"
    ["pass"]=>
    string(11) "none"
    ["gender"]=>
    string(1) "male"
    }

This result is how the rows are positioned in the table(user lisa first), which is represented correctly in the results as we did not specify any order.

Why is the first result set being ordered when we are not specifying this. It does not seem correct, I want the results to be pulled exactly how they are positioned in the table.

Thanks


texts: Is query efficiency affected by data not relevant to the query?

texts:
Say I have roughly 200 million records and have an indexed column user_id on a product table. If I select back 1 million records by doing the following query :

select * from product where user_id = 1 (Assuming my user_id is indexed.)

Would the other 199 million rows' column data affect the efficiency of my query ?

Example :

Consider 199 million rows were completely populated (containing blobs / varchars)
Consider 199 million rows were only partially populated, and carried a lot of null values

I've always understood this as the query would perform the same in either scenario - since user_id is indexed and could bypass traversing through all the data - but I'd like to get confirmation on this.

If there's another question / resource that answers this - feel free to redirect me elsewhere. Just looking for some confirmation (and an explanation if my assumption incorrect).

Thanks.


texts:
I have my "customer" data in a normalized sql server database.

Getting out the customer data in my app is taking too long.  This is because I have to go to 10+ tables to get all the data I need.

My company has an installation of SOLR that I thought about storing a Json object that contains all the data I need for a single "customer" already put together.

I think that this would give me some significant speed improvements.

However, it got me to wondering what the difference would be to me putting this data in a single table with a varchar(max) column that has the Json in it.  I could index my 10ish searchable columns on the same table as the json.

I know that document databases are very popular.  So I imagine there has to be benefits over just rolling my own using denormalized data in sql server.  Can someone tell me what they are?


texts: Slow running query using date range on non-clustered index column

texts:
I have a very slow running query, and I've isolated the problem down to the following SQL.

SELECT
  ...
FROM
    TableA a(nolock)
    inner join TableB b with(nolock) on (b.Id = a.Id AND b.Date>= isnull(@timestamp_start, '17530101') AND b.Date < isnull(@timestamp_end, '99991231'))

a = 2million+ rows
b = 2million+ rows
b.Id => Index, unique, non-clustered
a.Id => Index, unique, non-clustered
b.Date  => Index, non-unique, non-clustered

This takes several minutes without returning a result even for a single day date range.

UPDATE:

The estimated execution plan on the production server is showing me this, which shows that a complete scan of a huge table (TableA) is occurring near the beginning of the query, rather than the TableB.date range being used to filter in the WHERE. Why is this happening and how can I force the query to filter first on my date range?

The plan relates to this query, which in detail is:

SELECT
  ...
FROM
    TableA a(nolock)
    inner join TableB b with(nolock) on (b.Id = a.Id AND b.Date>= isnull(@timestamp_start, '17530101') AND b.Date < isnull(@timestamp_end, '99991231'))
    inner join TableC c with(nolock) on c.Id2 = s.Id2 and c.Id3 = a.Id3 and c.Id = b.Id
    inner join TableD d with(nolock) on (a.DealId = d.Id and d.Id3 = s.Id3 AND (@myparam is null OR d.ProviderName = @myparam))
    inner join TableE e with(nolock) on (e.Id = d.Id AND (@myparam2 is null OR e.Id = @myparam2))


texts: Availability Group - Deleting old backup files

texts:
I'm using Azure VMs with a SQL Server Availability Group, and Ola Hallengren' scripts. Backups are working without issues, however I've noticed that old backup files are not being removed from the server.

To remove the files, I'm running this command;

DECLARE @BackupDir NVARCHAR(4000) = (SELECT BackupDir = dbo.fn_SQLServerBackupDir())
DECLARE @Date DATETIME = DATEADD(DAY, -4, GETDATE())

EXECUTE [master].[dbo].xp_delete_file 0, @BackupDir, 'bak', @Date, 1;

Note: The function gets the default backup location for the SQL Instance.

Whenever I run this command, it completes successfully, but no files are removed. The Cleanup process that is included in the Ola Hallengren script is also unable to remove files.

In other environments, that are not AGs, the above command, plus the Cleanup in Ola Hallengren' script, work without issues.

This leads me to think the issue is either environment related or has something to do with the fact the backups where performed on databases in an AG.

Has anyone else encountered something like this before and might be able to offer some insight?

Backups are stored on the F:\ drive, which is a local disk on the server. I have also confirmed that the correct permissions have been set. At the moment, I've set full control for Everyone. (Environment is not live yet).

I tried modifying the function to add the \ at the end of the backup path, but this did not fix the issue. I've confirmed that there are backups which meet the date requirement, too.

EDIT 1:
Here is the backup command that's running, which includes the @Cleanup parameter, that doesn't remove the files;

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@BackupType = 'Log',
@Verify = 'Y',
@Checksum = 'Y',
@Compress = 'Y',
@LogToTable = 'Y',
@CleanupTime = 0,
@AvailabilityGroupDirectoryStructure = '{DatabaseName}',
@AvailabilityGroupFileName = '{DatabaseName}_{BackupType}_{Partial}_{CopyOnly}_{Year}_{Month}_{Day}_{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}',
@Encrypt = 'Y',
@EncryptionAlgorithm = 'AES_256'
, @ServerCertificate = 'Cert_Name'


texts: Can an Oracle 19c database dblink to an Oracle 10.2.3 database?

texts:
I have an Oracle 12.1.0.2 database that dblinks to an Oracle 10.2.0.3.0 database.
I am upgrading the Oracle 12.1.0.2 database to 19c (12.2.0.3).
Will my dblinks to the Oracle 10.2.0.3.0 database continue to work?


texts: Need a solution on dataset as given below

texts:
Data Set:
col1,col2,col3,col4.col5
(A,b,c,d,e)
(A,z,p,q,r)
(F,s,t,y,v)
(F,w,x,y,z)
(F,p,x,r,t)
(G,m,n,o,p)
(G,m,i,q,s)

Actually I need result set that has all the columns and only single row for each capital letter values among the all same capital letter values.

Result set should be like:
(A,b,c,d,e)
(F,p,q,r,t)
(G,k,i,q,s)

or
(A,z,p,q,r)
(F,w,x,y,z)
(G,m,n,o,p)

any of three rows.

  Actually I have detail records of so many companies in which some
  where company name are redundant and I have to select single records
  for each company on the basis of their name. Here I use Capital
  letters for company name.

  I hope you realize the question well.

  Please provide a select statement I will be thankful to you all.


texts: Using a subquery in place of a SELECT fieldname ...or how to achieve the same result?

texts:
My goal is to get a collection of ID's from Table 1 based on a query, a collection of ID's from Table 2 based on a query, and insert a new record into Table 3 for each permutation.

Basically, this:

INSERT INTO `TABLE3` (Field1,Field2,Field3)
SELECT null,`UID`, (SELECT `UID` FROM `TABLE2` WHERE `IsDefault` = 1)
FROM `TABLE1`
WHERE `Group` LIKE 'V';

So if the subquery:

(SELECT `UID` FROM `TABLE2` WHERE `IsDefault` = 1)

returns a list of IDs:

1, 2, 3

And the main select query:

SELECT null,`UID`, $i
FROM `TABLE1`
WHERE `Group` LIKE 'V';

returns another list of IDs:

200, 300, 400

Then TABLE3 after the query runs should be:

1 - 200
1 - 300
1 - 400
2 - 200
2 - 300
2 - 400
3 - 200
3 - 300
3 - 400

When I tried the above query, I get:

#2014 - Commands out of sync; you can't run this command now

So I'm not sure what the proper syntax is to do an INSERT...SELECT  where one of the "hard values" should be treated as a "foreach" variable from the subquery results?


texts: PostgreSQL 9.4 : server closed the connection unexpectedly

texts:
I have a website in production with low traffic (10-20 rpm). The application servers runs on the same box as the database, connection to the DB is done via localhost

For a few months everything has been running fine.

About 5 days ago Postgres started to randomly returns this error, about 1 error every 100 reqs :

  server closed the connection unexpectedly
  This probably means the server terminated abnormally before or while processing the request.

No major modification was done to the app, database or system setup.

If i understand the error message correctly, it means the database process was killed ?

I'm trying to understand what can cause this error :

Postgres logs don't show anything (no log around error time)
Kernel logs don't show anything either (no log around error time)
Syslog only shows that a cron jobs started the requests that failed with this error

Memory usage on the server seems ok (70%), i have plenty of free disk space.

When i manually connect to Postgres through psql or the ruby PG client i can execute select and update queries.

I already had a look at this question :
PostgreSQL Error : server closed the connection unexpectedly

I tried to re-index the tables. I also performed a pgdump which worked fine (i didn't restore data)

Environment :

Ubuntu 14.04
Postgres 9.4
Database size is about 6.5GB

App connecting to the DB :

Ruby on Rails 4.2
PG gem 0.18.2


texts:
I am making a database at no cost for a local dance school. I'm still very new this would like to be sure that I designed it appropriately.

From the front end, the parent will: fill out their personal info, create a student for each child and, an account number will be assigned to the parent. Each student will have an annual fee(s) attached to their name. Student will be enrolled in classes by the parent and, I plan to total the costs by adding together each unique enrolment for a student.

I think the diagram can explain the rest. Have I designed done this properly?


texts: mysql trigger to insert/update date column from datetime column

texts:
probably simple question but can't get it to work:

I have a table with two datetime columns. In order to set up a date index and have simpler queries, i have added two date columns next to them.

Now, i would like to, whenever a row is inserted or updated, to extract the date part from the datetime columns, and insert or update the date columns.

Not very well versed with triggers, so pointers would be much appreciated.

server version: 5.1.49-3


texts: Attempting to setup a new named instance but can't get past the Feature Selection Screen

texts:
I have been going through and looking at all of the options listed via google for the better part of the day. No matter how I try to run this, whether initially from the straight GUI interface, using a command line option with QS or Q set or from command line without the Q/QS so it goes through the gui I always get to the same spot but can go no further - would be pulling my hair out if any was left!

The latest Command line used is:

I have one complete instance installed. It was setup using all the default values. I am simply attempting to add a new instance (SQL, AS, IS, RS) as named instance with name of SQL2014..
This is all getting installed locally on my PC


texts:
My Postgres was working fine on my ubuntu 12.10 before i installed postgres-xc and execute a command initdb for changing encoding. After that it started showing error like:

  psql: could not connect to server: Too many levels of symbolic links
  Is the server running locally and acceptingconnections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

I have tried most of the the things I found on internet but nothing worked for me


texts: Oracle Data Pump Import imports no records

texts:
When we do an Oracle Data Pump import of our database, it runs without significant errors.  However, on one table Oracle reports that 0 records have been imported.

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "SCHEMA1"."TABLE_NAME"                   34.26 GB       0 out of 402776 rows

The import does create the table.  It just does not import any rows.  There are other miscellaneous errors during the import -- none of which apply to this table.

If we drop the table and do a table-level import, it again creates the table but imports 0 rows.

We are using a basic data pump command line.

userid="sys/********@INSTANCE AS SYSDBA" SCHEMAS=(SCHEMA1, SCHEMA2, ...) DIRECTORY=DPUMP_DIR LOGFILE=LOGFILE.IMP.LOG DUMPFILE=DUMPFILE_%U_DATE.dmpdp

We are trying to figure out why the rows are not being loaded and to get them loaded.


texts: Can spatial index be a part of a multiple columns index?

texts:
This manual over here is extremely unclear and doesn't even provide some sample SQL statements:
http://dev.mysql.com/doc/refman/5.6/en/column-indexes.html

Another way to rephrase the question is the following:

We know we can have an index with multiple columns. What about if the indexes of those columns are of different type? Say the first column is spatial, the other is fulltextsearch, etc. Can we do so in mysql? (Bonus: can we do so in mongodb, if you happen to know)

Say you have a myisam table

It has a LATLONG column that contains points

It has a FULLTEXT column that contains words in the "business"

You want to query by LATLONG first, and then within the matching LATLONG you want to filter based on FULLTEXT column.

I suppose you will need multiple column index.

But what is the SQL command?

As we know, mysql will always use fulltextsearch index first if possible.

This query:

SELECT BusinessID as ID ,
  111151.29341326*SQRT(pow(-6.186751-X(LatLong),2)+pow(106.772835-Y(LatLong),2)*0.98838574205337) AS Distance from tableauxiliary
use index (LatLong_2)
WHERE
    MBRContains(
    GeomFromText (
        'MULTIPOINT(-6.1934985598076 106.76604791159,-6.1800034401924 106.77962208841)'
        ),
        Latlong)=1
    AND Prominent >15
    AND MATCH FullTextSearch AGAINST ('sea*' IN BOOLEAN MODE)
    ORDER BY
  Distance
LIMIT
  0, 45

Takes a long time, while this query:

SELECT BusinessID as ID ,
  111151.29341326*SQRT(pow(-6.186751-X(LatLong),2)+pow(106.772835-Y(LatLong),2)*0.98838574205337) AS Distance from tableauxiliary
use index (LatLong_2)
WHERE
    MBRContains(
    GeomFromText (
        'MULTIPOINT(-6.1934985598076 106.76604791159,-6.1800034401924 106.77962208841)'
        ),
        Latlong)=1
    AND Prominent >15
    AND MATCH FullTextSearch AGAINST ('sea*' IN BOOLEAN MODE)
    ORDER BY
  Distance
LIMIT
  0, 45

is faster because I tell mysql to use latlong_2 index instead, which is a spatial query.

Well, say I want to have a multiple column index. Latlong_2 and FULLTEXTSEARCH. They are off different type. LatLong_2 is spatial and FULLTEXTSEARCH is a fulltext search index. What SQL command I should run?


texts:
In my current work position I've been given a desktop with SQL2014 tools installed including SQL Profiler. For some time we are still however supporting production SQL2000 machines and when using Profiler against these the following error is received:

  Client unable to establish connection
  SQL Server Native Client 11.0 does not support connections to SQL
  Server 2000 or earlier versions.

What is a simple way to fix this issue?


texts: MySQL Replication not working properly on Ubuntu Lucid Lynx Server

texts:
I have two Linux boxes. On the master following is the configuration file (my.cnf)

log-bin       = master-bin
log-bin-index = master-bin.index
binlog-do-db  = test_db
server-id     = 1

master status is as follows:

mysql> show master status;
+-------------------+----------+----------------+------------------+
| File              | Position | Binlog_Do_DB   | Binlog_Ignore_DB |
+-------------------+----------+----------------+------------------+
| master-bin.000026 |      106 | test_db        |                  |
+-------------------+----------+----------------+------------------+
1 row in set (0.00 sec)

The configuration file on slave is:

server-id = 2
relay-log-index = slave-relay-bin.index
relay-log   = slave-relay-bin
replicate-do-db=test_db

The slave status is:

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 207.216.74.213
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000027
          Read_Master_Log_Pos: 276
               Relay_Log_File: slave-relay-bin.000141
                Relay_Log_Pos: 422
        Relay_Master_Log_File: master-bin.000027
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test_db
                   Last_Errno: 0
          Exec_Master_Log_Pos: 276
              Relay_Log_Space: 723
              Until_Condition: None
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
               Last_SQL_Errno: 0
1 row in set (0.00 sec)

I've removed some empty entries from status above

Slave does connect and is showing correct master log file, but I am not getting any data from master to slave.

The only thing I can found is that master has version 5.1.41-3ubuntu12.10-log or MySQL but slave has 5.1.41-3ubuntu12.10.

Is the version or something other is creating problem?


texts:
i'm a programmer, not a dba. i need a mechanism to quickly make a snapshot of a database (oracle 11g XE) before i run migration. migration changes data, grants, DDL (tables, triggers, lexers, views etc). in case migration fails, i want to quickly revert to the saved state. all this will be done on non-production environment. it's just to speed up the development process

i've heard about full backups, flashbacks, snapshots but i have no idea what's the difference and which one is best for my task


texts: Does my table need a primary key and clustered index change?

texts:
I have a table holding 1.7 Milion rows.

Definition:

CREATE TABLE T(
[ID] [uniqueidentifier] NOT NULL,
[AID] [uniqueidentifier] NOT NULL,
[BID] [uniqueidentifier] NOT NULL,
[iType] [int] NOT NULL,
[MT] [ntext] NOT NULL,
[isM] [tinyint] NOT NULL,
[CDate] [datetime] NOT NULL,
[CBy] [nvarchar](50) NOT NULL,
[xi] [tinyint] NOT NULL

There is no Primary Key and no clustered index. There is one non clustered index on a uniqueidentifier column (that is not the PK candidate).

Looking at the usage statistics I see the following:

           Seeks Scans Lookups Updates TotalKB  UsedKB
HEAP       0     2     1500    65000   1810736  1757128
Non Cl.IX  1500  0     0       65000   56280    56240

I am thinking about adding a primary key on the (obviously previously for this purpose implemented) "ID" column - just to follow the normalization requirements and to have it.
I am also thinking if a clustered index would be helpful or not... There is not a really good candidate to fullfil the Clustered index requirements. Maybe CDate is the least bad one...?

Unfortunately I do not have insight in regulary running queries that I could use to identify if there are range queries and to compare before / after performance with.

Questions:

After adding a primary key - can I expect any performance changes / improvements?
Is it even possible to add a primary key WITHOUT adding a clustered index?
Would you suggest adding a clustered index in this scenario to improve performance? I have read tons of opinitons, discussions and benchmark tests about this - Brent Ozar says "create it as long you can't prove it's better without" - others say (sorry, it is in German) performance usually is not getting better but ofthen worse having a clustered index in place because it requires more reads to get the required data, a benchmark on SE resulted in "Ci are good fur updates only" => so right now I am completely confused what to do with my several HEAP tables (like this one).


texts: Need help mariadb optimization for performance configuration

texts:
I have server with 20cpu, 40 core th, 96GB ram, 2x3.8TB Nvme disk. This server for shared hosting, and about 1000 account hosted.

Current my configuration on my.cnf

[client-server]
!includedir /etc/my.cnf.d

[mysqld]
max_connections=150
log-error = /var/lib/mysql/myerrnet.err
performance-schema = 0
default-storage-engine = MyISAM
innodb_file_per_table = 1
query_cache_size =0
query_cache_type =0
max_allowed_packet=268435456
max_heap_table_size=20M
tmp_table_size =20M
open_files_limit=50000
local-infile=0
wait_timeout = 50
interactive_timeout=50
connect_timeout = 50
#max_allowed_packet = 8M
max_connect_errors = 50
join_buffer_size=256K

####Disable Strick Mode ####
sql_mode=NO_ENGINE_SUBSTITUTION

[mysqldump]
quick
max_allowed_packet = 512M

And after run mysqltunner i got this:

Variables to adjust:
    join_buffer_size (> 256.0K, or always use indexes with JOINs)
    tmp_table_size (> 20M)
    max_heap_table_size (> 20M)
    table_open_cache (> 2000)
    table_definition_cache(400) > 91182 or -1 (autosizing if supported)
    performance_schema = ON enable PFS
    innodb_buffer_pool_size (>= 19.2G) if possible.
    innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

What best configuration should i put on my.cnf ?


texts: Database-agnostic stored procedure call passing array of name/value pairs

texts:
Background

Looking to pass a set of name/value pairs into a stored procedure in a database-agnostic way, using JDBC. A database structure is defined as follows:

CREATE TYPE array_parameters AS (
  v_name VARCHAR2(255),
  v_value CLOB
);

This structure, which can have equivalent definitions in most modern relational databases, is being proposed as a way to pass an arbitrary number of name/value pairs into a stored procedure. The stored procedure call resembles:

SELECT rxm( '...map...', array_parameters );

Where the ...map... can include any number of variable references, taking the following form:

account.id = $id &&
person.last_name = $surname && ...

The array_parameters, in theory, could be populated as:

array_parameters[0].v_name = "$id";
array_parameters[0].v_value = "123456789";
array_parameters[1].v_name = "$surname";
array_parameters[1].v_value = "O'Malley, The \"Great\"";

Problem

JDBC4 defines a method called createArrayOf, which is the New South China Mall of APIs:

Unsupported by Oracle
Unsupported by MySQL
Unsupported by Microsoft SQL Server
Unsupported by Apache Derby
Unsupported by Sybase

Without the ability to create the name/value pair array, I can see no obvious way to pass in the values without resorting to database-specific implementations (such as using Oracle's ARRAY, or obtuse contortions to support MySQL).

Question

How would you define and then call a stored procedure that can take an arbitrary number of name/value pairs in a database-agnostic fashion?

Idea #1

One idea would be to define two string arrays, rather than an object array structure, and call the stored procedure as follows:

SELECT rxm( '...map...', array_names, array_values );

The two arrays would be index-linked, but this likely depends on createArrayOf(), as well.

Idea #2

It might be possible to pass the pairings as comma-separated strings. However, the values could contain commas themselves, which makes parameter encoding using comma-separated strings tricky. (Generally speaking, any separator can appear as a character somewhere in the values, which includes escaped separators as well, such as \,.)

This seems like the most database-agnostic solution, but implementing a CSV decoding routine across multiple databases in PL/SQL is neither trivial nor efficient.

Idea #3

Use Hibernate as an abstraction layer, then implement a JPQL routine that passes in the array of name/value pairs. For example, calling query.setParameterList, which might only work for IN clauses, rather than stored procedure parameters.


texts:
Hi I am new to Oracle triggers. Your help will be much appreciated!
I have the following 2 tables:
Product:

CREATE TABLE Product
  (prodId varchar(10) NOT NULL PRIMARY KEY,
   prodName varchar(30) NOT NULL,
  storeCode varchar(10) NOT NULL,
  CONSTRAINT prodStore FOREIGN KEY (storeCode) REFERENCES Store(stCode)
  );

and the Store table:

CREATE TABLE Store
(stCode varchar(10) NOT NULL PRIMARY KEY,
storeName varchar(20) NOT NULL
);

I want to create an Oracle delete trigger so that if I delete a store, all the products that are related to it will be deleted as well, in a cascade.
note: I don't want to use an ON DELETE CASCADE CONSTRAINT.
Here's my code:

CREATE OR REPLACE TRIGGER trigg_suppression
BEFORE DELETE ON Store
FOR EACH ROW
BEGIN
DELETE FROM Product
WHERE Product.storeCode= :OLD.stCode;
END;
/

I get a mutating table error.

I tried to use a statement level trigger:

CREATE OR REPLACE TRIGGER trigg_suppression
BEFORE DELETE ON Store
BEGIN
DELETE FROM Product
WHERE Product.storeCode= Store.stCode;
END;
/

This won't even compile.

Thank you!


texts: How do I reraise an exception in a PL/pgSQL EXCEPTION block?

texts:
Consider the following (incomplete) block of PL/pgSQL inside a function:

CREATE OR REPLACE FUNCTION my_calc(myvar1 NUMERIC, myvar2 NUMERIC)
    RETURNS NUMERIC
    RETURNS NULL ON NULL INPUT
    IMMUTABLE
    LANGUAGE plpgsql
    AS $$
    BEGIN
        RETURN some_third_party_function(myvar1, myvar2);
    EXCEPTION WHEN internal_error THEN
        IF SQLERRM LIKE 'KnownErrorPrefix:%' THEN
            RETURN 0;
        ELSE
            -- Reraise the original exception here
            RAISE EXCEPTION '%', SQLERRM;
        END IF;
    END
    $$

When an unanticipated error occurs, this code will throw a new exception with the same message. However, it won't preserve the original type or context.

How can I reraise or rethrow the original exception unmodified?


texts: What is preventing ORA-00376 errors appearing in the alert log?

texts:
An overnight hard drive failure had various side-effects, including leaving a single datafile needing recovery:

SQL> select count(*) from v$datafile where status='RECOVER';

  COUNT(*)
----------
         1

Everything is now back to normal but one mystery remains: before recovery, the client application logged an ORA-00376 to a logging table when a batch job tried to access a segment in the datafile. However this error did not appear in the alert log, how can this happen?

At the time of the ORA-00376 error, the following did appear in the alert log:

Errors in file /app/oracle/admin/live33/bdump/live33_j002_938224.trc:
ORA-12012: error on auto execute of job 24760001
ORA-20001: Error during batch run
ORA-06512: at "*******.***_*****", line 266
ORA-06512: at "*******.***_***_***_*****", line 355
ORA-06512: at line 1

And at the time of the original issue:

KCF: write/open error block=0x2fb23 online=1
     file=177 /data/ora9i/live/db3b/nsbme.dbf
     error=27072 txt: 'IBM AIX RISC System/6000 Error: 5: I/O error
Additional information: 195363'
Automatic datafile offline due to write error on
file 177: /data/ora9i/live/db3b/nsbme.dbf


texts: Don't use a transaction for Stored Procedure

texts:
I have a stored procedure that runs a few commands. I don't want these commands to be wrapped in the transaction of the stored procedure. If the 4th command fails, I want the 1st, 2nd and 3rd ones to stay and not rollback.

Is it possible to write the stored procedure in such a way that it doesn't all execute as one big transaction?


texts: Table(s) indexing strategy for separable data

texts:
Scenario: The data needs to be indexed by location, all of the rows include location info and also city info. I want to index by location, but there are millions of rows. The idea is that I can create other tables, for each city and I can index again by location.
I know that there are options such as Sharding and Partitioning however, I'd like to learn more before going further.
SELECT *
FROM
    t1
INNER JOIN
    t2
ON
    t1.locationId = t2.locationId
 WHERE
    t1.locationId = '.....';

..~ (1-2K result)
After indexing by locationId (in both two tables), the first query returns data after ~900ms,subsequent queries takes ~20ms. However, since there's a lot of locationId, I think it's not possible to cache all of them, and this will not work.
Any other idea is appreciated.


texts:
I have read and try lots of things about MySQL optimization and tuning. I tried most of them one by one according to our needs but nearly I got nothing.

I am doing my test using some simulation program with 800 concurrent user. However, system should be able to handle about 1500 concurrent user.

System specs:

OS: OpenSUSE 13.1 x86_64
MySQL: 5.6.27-log openSUSE package
CPU: Intel(R) Xeon(R) CPU E5-2660 v2 @ 2.20GHz
Memory: 64GB
DB size: ~6GB
Tables: 870
Storage: Raid 6 // I know 10 is most powerful for DBs but it is not my choice

All tables are InnoDB and here is my config file and why I decide them:

general_log_file = /var/log/mysql/mysql.log
//I need logs
general_log = 1
slow_query_log = 1
//to optimize queries
//Actualy I havent any optimized query
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2

skip-name-resolve // to increase DNS lookup performance
query_cache_type = 1
query_cache_limit = 200M
query_cache_min_res_unit = 2k
query_cache_size = 500M
// I tried with cache but nothing.
Last setting is query_cache_size = 0
innodb_buffer_pool_size = 52000M
//80% actual memory
innodb_buffer_pool_instances = 50
//at least 1GB for each pool
innodb_doublewrite = OFF
//for improve io performance
innodb_flush_method =  O_DIRECT
//for improve io performance with innodb_doublewrite off
innodb_log_buffer_size=8M
//lots of little innodb write
#innodb_thread_concurrency=8
//I try this but I can not get anything so it is now innodb decide it
innodb_flush_log_at_trx_commit=2;
//to increase io performance. I do not need ACID
#log-bin
// It can be dangerous but I turn it off for improve performance

OS top stats under load with 800 users:
    innodb_file_format=Barracuda
    innodb_file_per_table=ON

CPU usage: 40%-80%
IO write: 3MB-7MB
IO read: nearly 0
Ram Usage: 37GB
No swap usage and also swappines is 0.

Workbench stats under load with 800 users:

Traffic: 40MB-80MB
Connection: 820
Key Efficiency: 93,5%
Select per sec: 2000-4000
InnoDB Buffer Usage: 20.2%
InnoDB reads per sec: 0
InnoDB writes per sec 500-1500

I got stuck and cant go further to improve my db performance for 1500 concurrent user. What am I missing?


texts: SQL Server 2008 DB Performance on single disk

texts:
I have a database in SQL Server 2008 with around 20 GB in size. And it is increasing rapidly.

Somehow I can not add multiple independent harddisks to increase IO performance.

If a add large table in separate file group, will it help to improve performance on single disk?

Or any one has tips to improve performance?

Thank you


texts:
What's wrong with this Dynamic Pivot table query? I'm staring myself blind on this.

mysql> SET @sql = NULL;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    ->   GROUP_CONCAT(DISTINCT
    ->     CONCAT(
    ->       'IF(q.label = ''',
    ->       label,
    ->       ''', 1, 2) AS ',
    ->       label
    ->    )
    ->   ) INTO @sql
    -> FROM question;
Query OK, 1 row affected (0.00 sec)

mysql> SET @sql = CONCAT('SELECT a.id, ', @sql, ', q.id
    '>       FROM answer a
    '>       INNER JOIN question q ON questionId = q.id
    '>       GROUP BY a.id');
Query OK, 0 rows affected (0.01 sec)

mysql> PREPARE stmt FROM @sql;
ERROR 1064 (42000): You have an error in your SQL syntax ... near ' q.id

Why does the first one pass but the second one blows up?

Adding PREPARE stmt FROM @sql; on the GROUP_CONCAT query gives me this:

You have an error ... near 'IF(q.label = 'Q1', 1, 2) AS Q1,IF(q.label = 'Q2', 1, 2) AS Q2,IF(q.label = '', 1' at line 1

I'm not expecting the label to be empty on the last if, but I don't see how that would blow up the last query.

The query without the dynamic part returns this:

+----+-------+----+
| id | label | id |
+----+-------+----+
|  1 | Q1    |  1 |
|  2 | Q2    |  1 |
|  3 | Q1    |  1 |
|  4 | Q2    |  1 |
+----+-------+----+


texts: Another - ERROR: column "da2.dependency_device_name" must appear in the GROUP BY clause or be used in an aggregate function

texts:
I am attempting to aggregate dependency_device_name into one string, "Dependency List". Here is the error:

  ERROR:  column "da2.dependency_device_name" must appear in the GROUP
  BY clause or be used in an aggregate function.

Here is the sql. I have added the field to the group by and still have the same error message:

Select
xy1."Dependency Count",
xy1."Dependency List",
ag2.report_type_name,
xy1.dependent_device_name
from
view_affinitygroup_v2 ag2
left join
 (
   Select
   da2.dependency_device_name,
   da2.effective_to,
   da2.dependent_device_name,
   da2.dependent_device_fk,
   sum(1) "Dependency Count",
  string_agg(da2.dependency_device_name,', ') "Dependency List"
  from
    view_deviceaffinity_v2 da2
     ) as  xy1
   on xy1.dependent_device_fk = ag2.primary_device_fk and xy1.effective_to is null
   where ag2.report_type_id = 1
   group by
     da2.dependency_device_name,
     ag2.report_type_name,
     xy1.dependent_device_name,
     xy1.effective_to


texts:
I was redirected from SE's root site to this one.

I'm not a DBA, so I shouldn't be doing this, but I have to, so I apologize beforehand to all of you DBAs that will read this post:

I'm migrating an Oracle database from 10.2.0.4.0 to 11.2.0.1.0 from win 2003 to win 2008 both 64-bit.

I planned, for starters, to export/import and see what happened, and I found that while most of it went well, I had an issue with different charsets: On 10 I have WE8MSWIN1252 and on the new 11 it's using AL32UTF8.

I get "value is too long" for a few records on a few tables. Everything else seems to be in reasonably good shape.

I read somewhere that the length issue is related to a few mono-byte characters getting translated to multi-byte hence I need to increase the size of my columns with problems.

http://docs.oracle.com/cd/B28359_01/server.111/b28298/ch11charsetmig.htm#g1011430

I'll be doing just that:
1) Letting the import/export service do the charset translation.
2) I'll run an import for the metadata only,
3) then alter the tables with the issues,
4) and afterwards import the data.

I'll be using the EM console to do that because, well, my PL/SQL skills suck. :)

Anything extremely wrong with that approach? Any tips/advice you might think of?

Thanks in advance.


texts: How to properly add IPv6 data of GeoLite2 into existing table?

texts:
I'm building an application which will use MaxMind's GeoLite2 City database.

By following a 2016 tutorial, I imported the data of GeoLite2-City-Blocks-IPv4.csv and GeoLite2-City-Locations-en.csv and also added the helper procedure. Now I can successfully query the location information of IP address which are IPv4.

But I still need to be able to query the location information of IP address which are IPv6. Can I use the same code from the tutorial (shown below) to add the data of IPv6 into the existing table without messing things up? (I will change the file path to GeoLite2-City-Blocks-IPv6.csv)

LOAD DATA LOCAL INFILE '/root/geoip/GeoLite2-City-CSV_20160503/GeoLite2-City-Blocks-IPv4.csv' INTO TABLE ip_blocks COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES (
@network,
geoname_id,
registered_country_geoname_id,
represented_country_geoname_id,
is_anonymous_proxy,
is_satellite_provider,
postal_code,
latitude,
longitude,
accuracy_radius) SET
ip_from = INET_ATON(SUBSTRING(@network, 1, LOCATE('/', @network) - 1)),
ip_to = (INET_ATON(SUBSTRING(@network, 1, LOCATE('/', @network) - 1)) + (pow(2, (32-CONVERT(SUBSTRING(@network, LOCATE('/', @network) + 1), UNSIGNED INTEGER)))-1));


texts: How to test SQL Server Standard Basic Availability Group?

texts:
I'm planning to set up a new SQL Server 2017 Standard Edition instance, with Basic Availability Groups. How can I test that in a dev or qa environment?

In my QA environment now, I have Developer edition installed. When I run the T-SQL to create a Basic Availability Group, I get the following error:

  'BASIC' failed because it is not supported in the edition of this SQL Server instance

I've learned that Developer and Evalualtion have same feature set as Enterprise, so looks like I can't use those? Do I need a fully licensed Standard edition in a dev/qa environment to test Basic Availability Groups?


texts: What exactly is a "thread" in thread_cache_size, Thread_connected and thread_created?

texts:
In the MySQL 5.7 manual, I read that the thread cache has a size determined by the thread_cache_size system variable.

The default value is 0 (no caching), which causes a thread to be set up for each new connection and disposed of when the connection terminates.

But what exactly is a "thread"?

A connection thread becomes inactive when the client connection with which it was associated terminates.

Again, I can't understand what is a "thread" that I'm reading about.

Threads_created: The number of threads created to handle connections.

What the word "thread" refers to in this case?

Threads_cached: The number of threads in the thread cache.

Again this word "thread".

Threads_connected: The number of currently open connections.

I can't understand what is a thread. It is not connection I guess...

But what is it? Maybe it's only a matter of translation in my language, but I can't make up my mind to understand what is a "thread"


texts: Restore backup in order to write datafile sequentially in disk

texts:
I have just come to a new company and we have one database which was created with standard initial size and autogrowth parameters for data and log files (1 MB autogrowth for data / 10% for log).

The database is 70 GB right now and I guess the datafile is spread all over the disk in 1 MB chunks, so sequential reads get penalized. There is just one raid for OS and other RAID 5 for database files (data and logs together, unfortunately).

Will a backup and restore help to store the datafile in a better way, physically speaking? Will there be improvement for long sequential reads? Otherwise, what can I do?

Thanks.

EDIT: Actually, I have just discovered another database in the same server, with the same problem but a size of 500 GB.


texts: SQL AlwaysON Turkish Collation Problem During Setup

texts:
I have 2 servers with SQL Server 2014 SP2 and windows 2012 R2 installed on them.
When I tried to create availability group it failed with

  the primary replica is not active

But when I tried to create availability group with different collation other than turkish_ci_as it worked.

What am I missing here?


texts:
So I am attempting to setup a query that can track actions that are automated within our web system.
The first thing I am attempting to check is the reminders we are sending out to customers.
I have got a query that should be returning what I am expecting but it's overcounting and I have no idea what it's doing. The query is as follows (I'm sure its terribly written this isn't normally job):
    SELECT  COUNT(DISTINCT CASE WHEN O.`Application date` LIKE '2020-05-13%'
                      THEN O.`Order ID` END
                 ) AS 'Expected 35 day Emails sent',
            COUNT(CASE WHEN C.`view` LIKE '%evidence-reminder-35-days'
                  AND  C.created_at LIKE '2020-06-19%' THEN C.`view` END
                 ) AS 'Actual 35 day Emails Sent',
            COUNT(DISTINCT CASE WHEN O.`Application date` LIKE '2020-06-04%'
                  AND  E.type IS NULL THEN O.`Order ID` END
                 ) AS 'Expected 14 day Emails sent',
            COUNT(CASE WHEN C.`view` LIKE '%evidence-reminder-14-days'
                  AND  C.created_at LIKE '2020-06-19%' THEN C.`view` END
                 ) AS 'Actual 14 day Emails Sent'
        FROM
        (
            SELECT  `permit_id` AS 'Order ID', `created_at` AS 'Application Date',
                    `user_id`, `status` AS 'Order Status', type AS 'Order type'
                FROM  `user_permit`
                WHERE  `status` = 'Awaiting Approval'
                UNION
             SELECT  `voucher_id` AS 'Order ID', `created_at` AS 'Application Date',
                    `user_id`, `status` AS 'Order Status', `type` AS 'Order type'
                FROM  `user_voucher`
                WHERE  `status` = 'Awaiting Approval'
        ) AS O
        LEFT JOIN
        (
            SELECT  `view`, ref, receiver_id AS 'user_id', created_at
                FROM  communication
                WHERE  `view` LIKE '%evidence%') AS C  ON (O.`Order ID` = C.`ref`
        )
        LEFT JOIN  user_evidence E  ON (O.user_id = E.user_id);

Now the results I am given are 2, 16, 14, and 26. Having been through the communication table and Counted the entries I'm looking for I got 3 and 11 for the second and fourth result (I don't actually know how many results there should've been for the first and third results)
One of the main problems with the way that the communication table is setup is that as the orders may receive many reminders / other emails there are many entries with the same ref (Order ID) associated with them which many of the other examples of queries I've found online have in common.
The communication table has the format:
+--+----+---+------------------+-----------+----+----------+----------+
|ID|type|ref|communication_type|receiver_id|view|created_at|updated_at|

the view is the email template that is going out and is what is easiest to check.
If any more information is needed please ask.


texts:
I've been reading about different recovery modes you can set on sql databases, for example here: https://www.mssqltips.com/sqlservertutorial/2/sql-server-recovery-models/

So I've been trying to find the option to set this recovery mode or check what recovery mode I am using in mysql but I cannot find it anywhere. I've been googling around as well, and I also cannot find an answer anywhere.

Is this related only to certain database platforms that use sql (Microsoft only?) that's not mysql or can I use this or a similar function in mysql and postgresql?


texts: I have to create tables where both have each as the foreign key . Which one should be created first

texts:
DDL QUERY FOR CREATING MAINTENANCE_PROBLEM

CREATE TABLE MAINTENANCE_PROBLEM (
MP_ID int primary key,
MP_Description varchar2(250),
Standard_Min_Labor_Cost number(4,2),
Standard_Min_Labor_Hours int,
PART_ID int,
Constraint MP_PART_ID_FK foreign key (PART_ID) references Vehicle_Part (PART_ID)
);

DDL QUERY FOR CREATING VEHICLE_PART

CREATE TABLE VEHICLE_PART (
 PART_ID int primary key,
PR_Description varchar2(250),
PR_Cost number(4, 2),
MP_ID int
Constraint VP_MP_ID_FK foreign key (MP_ID) references MAINTENANCE_PROBLEM (MP_ID)
);


texts: Display last date grouped by warehouse and product


texts: Deadlock scenario, where is this S lock coming from?

texts:
I am encountering the below deadlock scenario. Please see the attached object definitions and execution plans. The plans were pulled out of cache from a system where this deadlock does occur.

I understand what all of this data is telling me but what I am confused about is why the spUpdateUserAccount stored procedure is taking an S lock on IX_Person_TaxPreparer_rowCreated. This lock appears unnecessary and there is no reference to IX_Person_TaxPreparer_rowCreated in the execution plan for spUpdateUserAccount.

...

Full question, with attachments, posted on SQLPerformance


texts: How can I query names using regular expressions?

texts:
How could I search for a specific pattern in a column?

For example, I like to get all user names that start with either letter A or B.

(btw: this is tagged oracle, but it might be interesting in other RDBMS as well).


texts: PostgreSQL 9.0 live migration/replication with native/bucardo between multiple architectures

texts:
I'm writing here because I'm in desperate need of a stable and good solution for resolving a issue we are facing.
We have an old PostgreSQL server running on RHEL 4 i386 with PostgreSQL 9.0 (postgresql-server-9.0.13-5.1.el4) with about 135 GB of raw data in the data folder. One of the big issues with this machine is that I can't install any new software and any attempts at installing yum fails so I have ruled this out of the picture. I have been able to compile a newer version of rsync on the machine which possibly brings slightly better performance but it's questionable.

I need to migrate this database to a CentOS 6 x86_64 running the same PostgreSQL versionpost but for 64 bit architecture (postgresql-server-9.0.13-5.1.el6.x86_64) with as minimal downtime as possible, this is an important live databas for a lot of customers (one of our billing backends).

This is what I have been testing:

Stopping database, pgsql_start_backup() and using rsync on the data folder which corrupts the data
Dumping database using pg_dump then importing but it takes toooooo much time to import
I was looking into pg_basebackup and running that over the network but since this feature was introduced in 9.1 I don't have it

What I think could be possible solutions but I don't know if they are possible:

Running a third party software like Bucardo on the new server and configure it as a slave so that it can start replicating the live server and that way sync all data and then switch, can I run Bucardo on only one server (the new one) and start the replication to the same server? Bucardo replication or native replication?
Should I try to use pg_upgrade and upgrade my database to 9.1 to get pg_basebackup? However this could introduce a second issue since our third party application specificly installs postgresql-server-9.0.13-5.1.el6.x86_64

Do you have any recommendations how I would move this database to a new server on a different architecture with as minimal downtime as possible (preferably replication until it catches up) with a very big database of 135 GB raw data? (About 60-80 GB data when dumped and compressed with gzip).

Thankful for any help.
Best regards


texts: Maintenance Plan on Replication Subscriber

texts:
I have seven subscribers to a replicated publication using transactional replication in SQL Server 2012.

Do I treat the subscribers just like a normal database and perform maintenance via a maintenance plan on them - or should they be left alone / treated any differently?

I know that Microsoft recommend backing up subscriber databases as usual, so I presume this would be part of a normal maintenance plan?


texts: Better ways to remove duplicate values of columns when using pl-sql "listagg" function in oracle

texts:
I have a table as you can see below :
c_id  |  Name1 |  Name2 |  Name3
--------------------------------
  1   |   a    |   b    |   c
  1   |   c    |   d    |   a
  2   |   d    |   e    |   a
  2   |   c    |   d    |   b

I want to write a query to have this output (Want to remove duplicates):
 c_id    Names
--------------------
  1      a,b,c,d
  2      a,b,c,d,e

I used listagg function and unpivot to do this :
select c_id, listagg(val, ',') within group(order by c_id) Names
  from (select distinct c_id, val
          from (select 1 c_id , 'a' name1, 'b' name2, 'c' name3
                  from dual
                union
                select 1 c_id , 'c' name1, 'd' name2, 'a' name3
                  from dual
                union
                select 2 c_id , 'd' name1, 'e' name2, 'a' name3
                  from dual
                union
                select 2 c_id , 'c' name1, 'd' name2, 'b' name3
                  from dual) v unpivot (val for namee in (name1, name2, name3)))
 group by c_id

I want to know If there are better ways to have the output mentioned above.
Thanks in advance


texts: Multi-Statement Table-Valued Function with a Cursor: How to Check If The Cursor is Still Open On Error?

texts:
I have a stored procedure preparing a dataset using an employee listing as base query and performing a CROSS APPLY to a MSTVF to build a small resultset per employee. In the MSTVF, a cursor is used to build the interim result set (performing complex math using running totals and case logic). MSTVFs don't allow TRY...CATCH blocks so I can't trap an error directly and use CURSOR_STATUS() to close and deallocate the cursor, or can I? What can I do in the declaration of the cursor or in the CATCH block of the stored procedure to check for an open cursor in the MSTVF?
Additional Context after Earlier Comments (Functional Logic)
The MSTVF code in question builds a set of instructions to submit to a timekeeping app's API engine to open a timecard, adjust one or more paycode balances with offsetting transactions and close a timecard. Basic workflow below...

MSFTVF takes an employee ID and pay period date ranges as params and returns a 10-column table variable

A variable is set with a sum of specific paycode balances. This sum is to be washed via debits and credits via the API. This is the pending balance.

A cursor of qualifying transactions to wash is declared in priority order.

While there is a pending balance...
3a) A debit matching credit transaction is inserted into a table variable (for the API call records)
3b) The amount of the debit/credit is the smaller of the current transaction or pending balance (handled via IF statement)
3c) If the transaction is more than one pay period in the past, another API call record is inserted that would flag the transaction for a historical correction.

When no balance remains, the while loop exits out
4a) If there are no xactions in the cursor left and still there is a pending balance, a final set of API calls are inserted to wash this balance.

The API table variable is then queried to insert API call records for final transactions at the timecard level and housekeeping calls (unlock, retotal, lock). If no records are present by the end of 4), nothing is generated.

The API table variable is aggegrated on paycode and transaction type and inserted into the MSTVF table variable and returned back to the calling procedure.

The use of a MSTVF was to encapsulate the logic generating all the API calls. To scale to an ILTVF, I figure after calculating the pending balances in 1) in the calling stored procedure, the ILTVF would run a query with a windowing function for SUM() for the running totals. On first blush, maybe the windowing query could be used in a CTE to reproduce steps 3) and 4) with a multi-section UNION statement. I figure the ILTVF result would be stored in a temp table in the calling procedure and steps 5) and 6) run to build in the housekeeping and store the aggregated result for the next ETL step (API execution).


texts: Are RANK() and DENSE_RANK() deterministic or non-deterministic?

texts:
According to official Microsoft BOL DENSE_RANK is nondeterministic (RANK()). But according to Ranking Functions by Itzik Ben-Gan  "... the RANK() and DENSE_RANK() functions are always deterministic". Who is right?

What I have found so far:
Microsoft's Definition "Deterministic functions always return the same result any time they are called with a specific set of input values and given the same state of the database."

So in Set theory tables
Employees

Employee            Salary
Sue Right            1.00
Robin Page           1.00
Phil Factor          1.00

and
Employees2

Employee            Salary
Phil Factor          1.00
Sue Right            1.00
Robin Page           1.00

are the same. But Ranking functions return different values:

    CREATE TABLE [dbo].[Employees](
    --[ID] [int] IDENTITY(1,1) NOT NULL,
    [Employee] [varchar](150) NOT NULL,
    [Salary] [smallmoney] NULL,
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[Employees2](
    --[ID] [int] IDENTITY(1,1) NOT NULL,
    [Employee] [varchar](150) NOT NULL,
    [Salary] [smallmoney] NULL,
) ON [PRIMARY]

INSERT INTO [dbo].[Employees]
([Employee] ,[Salary])
VALUES
('Sue Right', 1)
, ('Robin Page', 1)
,('Phil Factor', 1 )
GO
INSERT INTO [dbo].[Employees2]
([Employee] ,[Salary])
VALUES
('Phil Factor', 1 )
,('Sue Right', 1)
,('Robin Page', 1)
GO
SELECT RANK() OVER ( ORDER BY Salary) AS [Rank]
, DENSE_RANK() OVER (ORDER BY Salary ) AS [Dense_rank]
, [Employee]
FROM
dbo.Employees

SELECT RANK() OVER ( ORDER BY Salary) AS [Rank]
, DENSE_RANK() OVER (ORDER BY Salary ) AS [Dense_rank]
, [Employee]
FROM
dbo.Employees2

SELECT NTILE(3) OVER ( ORDER BY SALARY )
, [Employee]
FROM
dbo.Employees

SELECT NTILE(3) OVER ( ORDER BY SALARY )
, [Employee]
FROM
dbo.Employees2


texts:
Is there a way in pgAdmin to organize Postgres databases into "folders" or groups? Each Django project I'm creating has it's own Postgres database, and I would like to find a way to organize them.

I asked this question on StackOverflow (https://stackoverflow.com/questions/22727629/organizing-postgres-databases), and my question was "put on hold" because it was off topic.

On StackOverflow, Erwin Brandstetter suggested I create a new Server Group and set the Group dialog box:

My question is: how to create new options for the "Group" dropdown box? When I use pgAdmin3 command "Add Server" on my MacBook Pro, I only see one option under "Group" called "Servers".

Do I need to create a new "Database Cluster"? I found this command in the Postgres Documentation in Section 17.2:

$ initdb -D /usr/local/pgsql/data

On my MacBook Pro, my default databases are located at /usr/local/var/postgres/base and /usr/local/var/postgres/global.

Should I create a new directory, such as /usr/local/var/postgres2, and then execute a command like this?

$ initdb -D /usr/local/var/postgres2


texts:
I've successfully started some server-side tracing on a customer's SQL Server instance thanks to a few great posts out there, but since that's really my first ever in a "real-life" situation, I was wondering about a few things.

1) I've defined a trace file's "base" name in the script that creates the trace. I'm starting the trace at 6am and turning it off again at 7pm everyday. What I'd really love to do is set the trace file's base name to include the date, e.g. I would love to make it be

 (some directory)\MyTrace-2012-Apr-19

and so forth - so that each day's trace files would be easily recognizable. Is there a way to achieve this?? Would I have to actually create and drop the trace every day to get this??

2) If I turn on and off the trace on a daily basis, will my trace file from yesterday (MyTrace.trc) be reused today, up to its defined max size? Or will SQL Server create a new trace file (MyTrace_1.trc, MyTrace_2.trc) every day when it starts up?

3) Also, I had trouble trying to go get that trace file, even after 7pm - it seems as if it was still locked by some process and I couldn't just copy it elsewhere... I was under the assumption that once the tracing is stopped, the file would be "freely" available - am I wrong?


texts: Insert into table from a query to a linked database hangs

texts:
I have a schema with some database links to other schemas that we use to pull data from.  We pull the data into a staging table, massage it, and then do some merges into our schema/database.

The data in the linked schema is a little much to pull into test systems, so I want to only get the first 10000 records, which is easy enough.

select * from
(
    SELECT TRIM(r.R_ID), 1, TRIM(r.R_CODE), TRIM(r.R_MISC4), trim(rc.DESCR)
    FROM sradmin.RACEWAY@sr25 r
    LEFT JOIN sradmin.RWAYCODE@sr25 rc ON r.R_CODE = rc.R_CODE
)
Where Rownum <= 10000;

This returns very quickly as in under 2 seconds.

However when I try to insert this into my staging table, it causes SQL Developer to hang.  Not a windows hang, the Script Output window just shows "ScriptRunner Task" with the moving bar back and forth showing that it's doing something.

INSERT INTO STG_TRANSIT(TRANSIT_ID, SHIP, TRANSIT_TYPE, COMPARTMENT_ID, DESCRIPTION)
select * from
(
  SELECT TRIM(r.R_ID), 1, TRIM(r.R_CODE), TRIM(r.R_MISC4), trim(rc.DESCR)
  FROM sradmin.RACEWAY@sr25 r
  LEFT JOIN sradmin.RWAYCODE@sr25 rc ON r.R_CODE = rc.R_CODE
)
Where Rownum <= 10000;

When it does this I can see that there are DW locks, but nothing else is using the linked databases that I'm trying to query.

Any help would be greatly appreciated as I am very much a novice with Oracle.


texts: Designing a tracking system for ordered workouts

texts:
I've taken a handful of introductory database courses, so I thought I'd design a relational database to help me keep track of my workout routines. It turned out to be a more difficult task than I'd anticipated.

So say I record my workout session with a pen and paper something like this:

Monday

 1. jogging | 1.5 miles
 2. plank   | 3 minutes
 3. lift_A  | 5 pounds  | 15 reps
 4. lift_A  | 5 pounds  | 15 reps
 5. lift_A  | 6 pounds  | 17 reps

Tuesday

 1. plank    | 3 minutes | 3 pounds
 2. wall sit | 2 minutes
 3. jogging  | 1.6 miles
 4. lift_B   | 10 pounds | 2 reps

I need to keep track of the date of each series of exercises as well as the order I do them in. I had trouble figuring out how best to store the three "types" of activities (distance, duration w/ optional weight, weight+reps) and how to manage their recorded order in a day's workout.

How can I design a database (with or without an entity-relationship diagram, which would be nice) to help me record such information? What is the most logical/intuitive approach? Is a relational database even an appropriate approach at all?


texts: Is there a way to see how SQL Server 2012 defines new identities?

texts:
On Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64), is there a way to see what mechanism SQL Server uses to calculate the new identity values for tables created by SELECT INTO?

SAMPLE DATA

-- Create our base table
CREATE TABLE dbo.A
(A_ID INT IDENTITY(1, 1),
x1 INT,
noise1 int DEFAULT 1,
noise2 char(1) DEFAULT 'S',
noise3 date DEFAULT GETUTCDATE(),
noise4 bit DEFAULT 0);

-- Create random data between the range of [0-3]
INSERT INTO dbo.A(x1)
SELECT s1000.n FROM
( SELECT TOP (10) n = 1 FROM sys.columns) AS s10 -- 10
CROSS JOIN
( SELECT TOP (10) n = 1 FROM sys.columns) AS s100 -- 10 * 10
CROSS JOIN
( SELECT TOP (10) n = ABS(CHECKSUM(NEWID())) % 4 FROM sys.columns) AS s1000; -- 100 * 10

SELECT * FROM dbo.A ORDER BY A_ID DESC;

In my case, the results are:

And running

DBCC CHECKIDENT('A')

returns

  Checking identity information: current identity value '1000', current
  column value '1000'.

Create new table

When we select a subset into a new table, it creates a new current identity value:

SELECT * INTO #temp FROM dbo.A WHERE x1 = 0;

The highest value in the identity column of this new table is 998 and when we check it's next identity:

DBCC CHECKIDENT('#temp')

  Checking identity information: current identity value '998', current
  column value '998'.

How?

How are these identity values inserted into the new table with the highest value correctly set as the current identity value of the new table?


texts: Continuously arriving data with fast filtering on date in PostgreSQL

texts:
I have a huge amount of data, which arrives continously. (About 10 million rows/day, ~3000 rows/10 seconds in peak intervals of the day.)

I want to store them in a PostgreSQL database (I use PostGis to process the geographical data, that's why PostgreSQL).

I usually filter these data by timestamp first, and only after this I want to select data based on other properties (so it's rare when I need to work with all of the data).

My first approach was to create a table which has an index on the timestamp (to enable fast filtering on timestamp), and I insert the freshly arrived rows to the table every 10 seconds (so about 500-3000 rows).
This approach worked for the first few days, but after that inserting the data into the DB took more time than the rate in which the data arrived (so I wanted to insert new data every 10 seconds, but inserting took more than 10 seconds). (It's reason is obviously updating the index. I use COPY to insert the data, as recommended here.)

The other ideas I considered were the following, but they make the handling of the data difficult:

Putting the continuously arriving data into a different table, and copy it into the full table e.g. every night. But the time to copy the data will be longer every day, so it's not sustainable after a while.
Putting every day's data into a different table. But in this case it's difficult to create queries which have overlapping intervals, or contain multiple days. (I have to UNION the tables.) But still this solution seems to be the best yet...
Dropping the index before every insert (as written in the documentation), but the index recreation takes more and more time as the table grows, so it's also not a sustainable solution.

Because the data arrives continuously (timestamp is monotonically increasing), if I don't use DB, I could store these data into files (e.g. 1 file/hour), and use binary search on these files according to the timestamps, and that would be much faster than using the DB to store the data (no need to index), and it also scales well (the time I need to insert and query the same amount of data is the same, it doesn't depend on the total data size). So I think it's not impossible to create a solution which meets my requirements.

But obviously, it would result in a much less usable database and it would be much worse in other aspects without PostgreSQL's functions (e.g. further filtering data, PostGIS features).

Storing the data in these files and only loading them to a temporary table for the duration of processing seems to be another possible solution to solve the performance problem, but I think it would be much slower to query (because I have to physically copy the data on the disk) and it seems to be an ugly solution.

What other options do I have to handle this amount of data in a sustainable way (so the complexity of selecting and inserting the same amount of data doesn't depend on the size of the total data amount), but still make querying the data reasonably simple?


texts:
Is SSIS subject to the maximum memory limitations for SQL Server 2012 Standard edition?

If I have a server with 128GB RAM and I allocate 64GB of RAM to SQL Server does SSIS utilise this memory or does it utilise the memory that is available to the OS?

I am assuming it utilises the remaining memory left to the OS since there is no settings for memory consumption and it runs as a separate service.

If anyone has any information on the available memory that should be left for SSIS to consume I would appreciate the help.


texts: JOIN 3 Tables without Union ALL nor nested queries with multiple IDs

texts:
Not sure if this is possible, but I would like to join multiple tables with multiple IDs without UNION ALL or nested queries. You can see the related question here which shows everything in table view. I would like to create a table view but am not allowed to use what was mentioned above. I've already figured out how to do it with UNION ALL.

So, I have the Main table which reference a table called Fact. The Fact table contains data that can be referenced by LanguageID, ClientID, or StatusID (revision). The Main and Fact tables are related by an intermediary table called FactLink (not shown for simplicity's sake).

The end result MainView indexable table I would like to get looks like:

MainView

MainID | StatusOrder | LanguageID | ClientID | Description   | Disclaimer  | Other
-------+-------------+------------+----------+---------------+-------------+------
50     | 10          | 1          | 1        | Some text     | Disclaimer1 | Blah
50     | 10          | 2          | 1        | Otro texto    | NULL        | Blah
50     | 20          | 1          | 2        | Modified text | NULL        | Blah
55     | 10          | 1          | 1        | Some text 2   | Disclaimer2 | Blah Blah
55     | 10          | 1          | 2        | NULL          | Disclaimer3 | Blah Blah

DROP TABLE IF EXISTS #main
CREATE TABLE #main
(
    ID INT NOT NULL,
    DescriptionID INT NOT NULL,
    DisclaimerID INT NOT NULL,
    Other NVARCHAR(500)
)

Drop table if exists #fact
CREATE TABLE #fact
(
    FactID INT NOT NULL,
    LanguageID INT NOT NULL,
    StatusID INT NOT NULL,
    ClientID INT NOT NULL,
    Description NVARCHAR(MAX)
)

DROP TABLE IF EXISTS #status
CREATE TABLE #status
(
    StatusID INT NOT NULL,
    [Order] INT NOT NULL
)

INSERT INTO #main
    (ID, DescriptionID, DisclaimerID, Other)
    VALUES
    (50, 1, 2, 'Blah'),
    (55, 4, 3, 'Blah Blah')

INSERT INTO #fact
    (FactID, LanguageID, StatusID, ClientID, Description)
    VALUES
    (1, 1, 1, 1, N'Some text'),
    (1, 2, 1, 1, N'Otro texto'),
    (1, 1, 3, 2, N'Modified text'),
    (2, 1, 1, 1, N'Disclaimer1'),
    (3, 1, 1, 1, N'Disclaimer2'),
    (3, 1, 2, 1, N'Disclaimer3'),
    (4, 1, 1, 1, N'Some text 2')

INSERT INTO #status
    (StatusID, [Order])
    VALUES
    (1, 10),
    (2, 100),
    (3, 20)

Here's what I have so far. But it doesn't include the last entry and hasn't accounted for the StatusOrder. I'm not sure where to go from here.

SELECT
    t.ID, t.Other,
    fDescription.Description,
    fDisclaimer.Description Disclaimer,
    COALESCE(fDescription.LanguageID, fDisclaimer.LanguageID) LanguageID,
    COALESCE(fDescription.ClientID, fDisclaimer.ClientID) ClientID,
    COALESCE(fDescription.StatusID, fDisclaimer.StatusID) StatusID
FROM #main t
JOIN #fact fDescription
    ON fDescription.FactID = t.DescriptionID
LEFT OUTER JOIN #fact fDisclaimer
    ON fDisclaimer.FactID = t.DisclaimerID
    AND fDisclaimer.ClientID = fDescription.ClientID
    AND fDisclaimer.LanguageID = fDescription.LanguageID
    AND fDisclaimer.StatusID = fDescription.StatusID


texts: Surrogate foreign keys allow inconsistent data - which normal form does this violate?

texts:
We have inherited a system which has surrogate keys on every table (that is, an auto-increment primary key, id). All of the foreign key constraints are on individual surrogate keys, and this seems to allow inconsistent data to exist in the database. Consider the following tables:

// Global list of available IO
io (id, type, name, etc...)

// Configuration of devices attached to the system
devices (id, name, etc...)

// Sensors connected to a particular device
device_io (
    id PRIMARY KEY,
    device_id FOREIGN KEY TO devices(id),
    io_id FOREIGN KEY TO io(id),
    [io parameters]...
)

// Control loop parameters connecting input sensors to output sensors
device_control_loops(
    id PRIMARY KEY,
    device_id FOREIGN KEY TO devices(id),
    input_id FOREIGN KEY TO device_io(id),
    output_id FOREIGN KEY TO device_io(id),
    [control loop parameters]...
)

Because the input_id and output_id refer only to device_io(id), I can insert invalid data like this:

INSERT INTO io (1, 'input', 'Temperature'), (2, 'output', 'Heater');
INSERT INTO devices (1, 'Device 1'), (2, 'Device 2');
INSERT INTO device_io (1, 1, 1), (2, 2, 2);
INSERT INTO device_control_loops (1, 1, 1, 2);

The control loop entry is invalid. It's for device_id 1, but it refers to IO from device 2. In this system you are not supposed to be able to do this.

I am sure this must be a violation of normal form, but I'm not sure which.

Perhaps the violation is with what the foreign key should be? To prevent this inconsistency in the current design the foreign key would have to be:

(device_id, input_id) FOREIGN KEY TO device_io(device_id, id)

This seems very wrong to me. Is this where we would violate a normal form?

The way I would do this is have input_id and output_id refer to io(id) rather than device_io(id). Then the foreign keys would be:

(device_id, input_id) FOREIGN KEY TO device_io(device_id, io_id),
(device_id, output_id) FOREIGN KEY TO device_io(device_id, io_id)

Which seems much more sensible.

In summary, I would like to understand exactly what the issue is with the current design so I can provide justification for changing it.


texts: How does a security group sid differ from a per service Sid?

texts:
I am learning SQL and trying to understand the concept of a SID.

At the link below it states:

  Microsoft SQL Server uses a security group to set resource access
  control lists (ACLs) instead of using the service account directly.
  Therefore, changing the service account can be done without having to
  repeat the resource ACL process. The security group can be a local
  security group, a domain security group, or a service security
  identifier (SID)

and then goes on to say

  For stand-alone instances of SQL Server on Windows Vista and on
  Windows Server 2008 operating systems, service SIDs are added to the
  service group, and the service SID for SQL Server Engine and SQL
  Server Agent is added as a login to the sysadmin server role.

It uses service group and security group interchangeably. Are they the same? ...and linked to this, if a service SID can be added to a security group (which can also be a SID), these 2 SIDs are different right?

I think so, but struggling with the Microsoft documentation.

thanks.

https://support.microsoft.com/en-us/kb/2620201


texts: Slow query on large table with GROUP BY and ORDER BY

texts:
I have a table with 7.2 million tuples which looks like this:

                               table public.methods
 column |          type         |                      attributes
--------+-----------------------+----------------------------------------------------
 id     | integer               | not null DEFAULT nextval('methodkey'::regclass)
 hash   | character varying(32) | not null
 string | character varying     | not null
 method | character varying     | not null
 file   | character varying     | not null
 type   | character varying     | not null
Indexes:
    "methods_pkey" PRIMARY KEY, btree (id)
    "methodhash" btree (hash)

Now I want to select some values but the query is incredibly slow:

db=# explain
    select hash, string, count(method)
    from methods
    where hash not in
          (select hash from nostring)
    group by hash, string
    order by count(method) desc;
                                            QUERY PLAN
----------------------------------------------------------------------------------------
 Sort  (cost=160245190041.10..160245190962.07 rows=368391 width=182)
   Sort Key: (count(methods.method))
   ->  GroupAggregate  (cost=160245017241.77..160245057764.73 rows=368391 width=182)
       ->  Sort  (cost=160245017241.77..160245026451.53 rows=3683905 width=182)
             Sort Key: methods.hash, methods.string
             ->  Seq Scan on methods  (cost=0.00..160243305942.27 rows=3683905 width=182)
                   Filter: (NOT (SubPlan 1))
                   SubPlan 1
                   ->  Materialize  (cost=0.00..41071.54 rows=970636 width=33)
                     ->  Seq Scan on nostring  (cost=0.00..28634.36 rows=970636 width=33)

The hash column is the md5 hash of string and has an index. So I think my problem is that the whole table is sorted by id and not by hash, so it takes a while to sort it first and then group it?

The table nostring contains only a list of hashes I don't want to have. But I need both tables to have all values. So it's not an option to delete these.

additional info: none of the columns can be null (fixed that in the table definition) and i'm using postgresql 9.2.


texts: designing a database with type and sub type

texts:
I am trying to build a publications database, and i want to develop a genre relation that has various (maybe look up tables) sub-genres attached. as an example, in the genre relation there would be a row for 'POETRY' but somehow i want to divide that into sub genres like 'PROSE', 'HAIKU' or 'FREE_VERSE'.

what is the best way to design such a thing? would i have a separate look-up table for each row in the genre relation for the various sub-genres? that doesn't seem very flexible. any input would be greatly appreciated.


texts:
Assuming that you have a table like

CREATE TABLE `t1` (
    `search_id` int(11) NOT NULL,
    `provider_id` bigint(20) unsigned NOT NULL,
    `status` text NOT NULL,
    `imported_at` int(11) NOT NULL,
    `created_at` int(11) NOT NULL,
    `latitude` double DEFAULT NULL,
    `longitude` double DEFAULT NULL,
    `moderation` tinyint(3) NOT NULL DEFAULT '0',
    PRIMARY KEY (`search_id`,`provider_id`),
    KEY `k_search_imported` (`search_id`,`imported_at`),
    KEY `k_scm` (`search_id`,`created_at`,`moderation`),
    KEY `k_sc` (`search_id`,`created_at`)
) ENGINE=InnoDB;

What I'm unsure about is why

SELECT COUNT(1) FROM t1;

Takes 19 minutes (I've populated the table with 1.2 million rows).

MariaDB [test] 10:53:58> show indexes from t1;
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1    |          0 | PRIMARY           |            1 | search_id   | A         |       13478 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          0 | PRIMARY           |            2 | provider_id | A         |      646982 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_search_imported |            1 | search_id   | A         |          30 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_search_imported |            2 | imported_at | A         |       46213 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_scm             |            1 | search_id   | A         |           8 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_scm             |            2 | created_at  | A         |      646982 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_scm             |            3 | moderation  | A         |      646982 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_sc              |            1 | search_id   | A         |          10 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_sc              |            2 | created_at  | A         |      646982 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
9 rows in set (0.00 sec)

I wonder if the problem here is the cardinality. My question is should InnoDB indexes be ordered by the higher cardinality columns closer to the the start of the index, i.e. PRIMARY KEY (provider_id,search_id); or does it even matter?


texts: Retrieve list of matched words in PostgreSQL

texts:
I'm new to PostgreSQL and really more than your more basic queries/inserts.

I've created a TSVector column to my table that I'm searching and have set the column to look at the specific column in the table.

Now what I'm trying to do is find out what words matched in a query.

If I had the query

SELECT ts_headline('the big red ball' , 'big & ball');

It would obviously return:

  "the big red ball"

Is there a way I can get it to return a list instead so I just see big and red in seperate rows?


texts: Another Dynamically alias column question

texts:
I might be asking the same question as this, but perhaps if I ask it my way, a different solution may become obvious. I have this:

DECLARE @Table1 AS TABLE(
Table1ID Int Primary Key
,ColumnName varchar(max)
)
INSERT INTO @Table1(Table1ID,ColumnName) VALUES(1000,'A')
INSERT INTO @Table1(Table1ID,ColumnName) VALUES(2000,'B')
DECLARE @Table2 AS TABLE(
Table2ID Int Identity Primary Key
,Table1ID Int
,FieldData varchar(max)
)
INSERT INTO @Table2(Table1ID,FieldData) VALUES(1000,'A DATA')
INSERT INTO @Table2(Table1ID,FieldData) VALUES(2000,'B DATA')
SELECT * FROM @Table1
SELECT * FROM @Table2
SELECT FieldData AS A
from @Table2
WHERE Table1ID = 1000
SELECT FieldData AS B
from @Table2
WHERE Table1ID = 2000

And I want to do this:

SELECT FieldData AS [Table1.ColumnName]
from @Table2
WHERE Table1ID = 2000


texts: Vertical Elastic Query is not identifying Object

texts:
I did create an External Datasource, identical to the guide described here https://www.sqlservercentral.com/articles/cross-database-queries-in-azure-sql-database .The process is pretty simple, so just for illustration.
CREATE MASTER KEY ENCRYPTION ...
CREATE DATABASE SCOPED CREDENTIAL ...

CREATE LOGIN <externaldbname> WITH PASSWORD = '<somepassword1>';
CREATE USER externaldbname FOR LOGIN externaldbname;

CREATE USER externaldbname FOR LOGIN externaldbname;

CREATE EXTERNAL DATA SOURCE ...
CREATE EXTERNAL TABLE ..

All executed successfully. Now when I try to select something from the external database, this error is raised

Msg 46823, Level 16, State 1, Line 3
Error retrieving data from sar*****.database.windows.net.carDB.  The underlying error message received was: 'Invalid object name 'SalesLT.Addresstest'.'.

I didn't get the error msg as the method I followed is used/shown in multiple website and tutorials.


texts:
I have several tables: company, customer, company_has_customer, and transaction.

company_has_customer is a many-to-many junction table using the PKs of company and customer as its PK.

In almost all situations throughout the database, both the company and customer are used to identify a record (e.g. in the transaction table).

My question is whether I should create the foreign key in the transaction table to point to company_has_customer or to the two tables (company, customer) individually?

I believe it would be best for referential integrity to send those FKs through company_has_customer to ensure that transaction records are only inserted if there is a relationship between the company and the customer.  Are there any standards or conventions to support my hunch?

My second question is doing the lookup from transaction to the customer table if the FK is actually going through the company_has_customer table.  There would still be an index, but should I always have to join through the company_has_customer table?