Find the destination owner (schema) for replication on SQL Server We have a table replicating to another server with a different Schema at the destination.  When I set the replication up it shows as destination owner in the GUI.  I am trying to create some reports and need to look at the publication and look for what the destination schema would be.

Unfortunately when I query the table [distribution].[dbo].[MSarticles] the destination owner column is null.  Is there somewhere else I can find this info?

Thanks

Instead of using [distribution].[dbo].[MSarticles] use [publicationDatabase].[dbo].[sysarticles]. Use dest_owner column.

Detail here:
https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/sysarticles-transact-sql


MySQL Query to get rank of a student I have a table with the following structure.

CREATE TABLE `score` (
    `sid` INT NULL,
    `name` INT NULL,
    `rollno` INT NULL,
    `examid` INT NULL,
    `phone` INT NULL,
    `dob` INT NULL,
    `district` INT NULL,
    `score` INT NULL,
    `active` INT NULL
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;

I need a query that will assign rank to a student based on their score and age. If two students have same mark, student with highest date of birth is given priority.

This is the sample data I have:

--------------------------------------------------------------------------------
sid | name | examid | phone | dob       | district | score | date       | active
--------------------------------------------------------------------------------
1     User1    1      12345  10/11/1983   209         10     10/11/2017     1

2     User2    1      34567  11/10/1983   209         10     10/11/2017     1

3     User3    1      34567  11/10/1985   209         20     10/11/2017     1
------------------------------------------------------------------------------

The result I am expecting is like this:

+----+------------------+----------------+
| id | name             |           rank |
+----+------------------+----------------+
|  2 |           User2  |              1 |
|  3 |           User3  |              2 |
|  2 |           User1  |              3 |

My question is similar to this question (Get the rank of a user in a score table)  but is there any other solution.

This is the current query I have which is working but how to display rank with the same query.

SELECT name, rollno, score
FROM score
WHERE examid = '1'
ORDER BY score DESC, dob ASC

MySQL 8+

SELECT id, name, ROW_NUMBER() OVER (ORDER BY score ASC, dob DESC) rank
FROM score

MySQL 5+

SELECT id, name, @rank := @rank + 1 rank
FROM score, (SELECT @rank := 0) init
ORDER BY score ASC, dob DESC

The students with equal both score and birth will be ranked randomly.


Maintenance plan backup and reload the same database I want in my maintenance plan every night to copy the production database to a backup, and reload the newly-created backup into a test database. By reading here:

Restore database - maintenance plan task

I see that it is possible to do it using first the Back Up Database Task and then the Execute T-SQL Statement Task.
The problem is, the backup file has every time a different name, like

  SIAM_backup_2014_08_14_221501_2611766.bak

If I don't find a better solution, I will have to save the database backup always on the same filename, overwriting it every time.
Is it possible to pass a parameter between the two tasks, with the name of the backup file, or is there a T-SQL statement that means "Reload the latest saved full backup", or do you have other ideas?

Carlo,

  do you have other ideas?

You jus took a backup, so when you write the execute SQL Statement part of your job reference the MSDB backup history information tables to pull out the latest full and any other diffs/logs/etc that you would like to apply. The data can be found in msdb under the dbo schema and will give you the file names, etc, so there will be no need to pass anything and you can make it work should you want to do it from the last full or from the last full with the rest of the backup chain.

http://msdn.microsoft.com/en-us/library/ms188062.aspx


cannot generate sspi - None of Solution worked! I am receiving "cannot generate sspi" error when I try to connect a sql instance remotely. I suspected the issue might be with spn and verified that spn was registered properly on the service account(confirmed the same with AD team as well). Also tried to get the server removed from domain and rejoined. The issue still persists and issue started occurring all of sudden and there were no changes on the server (patching, reboot). Would appreciate if anyone can provide some lead to resolve this issue?

Having no SPN does not cause this error. Having an invalid SPN does. It's not enough to make sure an SPN exists, you have to make sure it is owned by the SQL Server service account and that it is configured correctly.

Follow the advice I blogged here: http://sqlsoldier.net/wp/sqlserver/advanced-troubleshooting-week-at-sql-university-lesson-1


Check if two tables have the same structure (are compatible) I was wondering if there is an established way to test if two PostgreSQL tables/relations have the same structure. In other words, how to test whether they are compatible with each other in the sense that I can perform set operations such as UNION ALL and EXCEPT on them?

I searched around on DBA.SE and elsewhere, and can only find questions about finding whether the contents of two tables are different, (e.g. Checking whether two tables have identical content in PostgreSQL), or when the compatibility is known (e.g. Compare two tables with same structure, but differing membership numbers). But I'm interested in checking the compatibility of table structure.

I'm using PostgreSQL 10.3, but standard compliant ways are certainly preferable.

Having example schema like:

create table table1 (
  id integer,
  txt text,
  col1 integer,
  col2 integer);

create table table2 (
  id integer,
  txt text,
  col1 text,
  colx integer);

and assuming you don't care about column names you can check potential conflicts in union-ed columns with query:

(select data_type, ordinal_position
from information_schema.columns
where table_name = 'table1'
except
select data_type, ordinal_position
from information_schema.columns
where table_name = 'table2')
union all
(select data_type, ordinal_position
from information_schema.columns
where table_name = 'table2'
except
select data_type, ordinal_position
from information_schema.columns
where table_name = 'table1'
)
order by 2;

The result will show offending column's position in tables' structure and data type.

data_type   ordinal_position
integer     3
text    3

If you have this shortened list you can check details in information_schema.columns disctionary to check details about each column.


How to optimize query for text array in PostgreSQL using `@>` operator I have a column of type text[] and I want to search over this column using  this SQL operator @>

Note: @> operator, it filters the data as such ['a','b'] in ['a','b','c']

    The returned objects will be those where the values passed are a subset of the data

The problem though is that integer comparison is better than string comparison.
I'm thinking of another way maybe if Postgres hashes the values and only then it compares.

Note, I cannot make use of indexes because there is not only one column, moreover, the query will first filter on some id and then it needs to filter those multi-valued columns.

My question, Is there is some feature in Postgresql that supports comparing integers instead of strings.

If the strings are from a restricted set, you can define an ENUM datatype.  This translates the strings to integers behind the scenes.

create type alph as enum ( 'a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z');

create table j as select floor(random()*100)::int, array_agg(substring('abcdefghijklmnopqrstuvwxyz',floor(random()*26)::int+1,1)) from generate_series(1,10000000) f(x) group by x%1000000;
create table j2 as select floor, array_agg::alph[] from j;

I get about a 2 fold speed improvement by doing:

select * from j2 where  array_agg @> '{a,b}';

rather than

select * from j where  array_agg @> '{a,b}';

If I include the condition and floor=7 (after creating an index on "floor"), then both queries are so fast that any difference in speed can not be reliably detected.

This seems like the essence of premature optimization to me.


Sync two MySQL databases in two different locations I have two identical MySQL databases one in an internal server and the other in a web hosting server. I want to update the database on the web host each day with the database on the internal server. Is there a way to automate this process also how can I do this manually? If I'm to do it manually, does it require me to get a SQL dump of the Database on the internal server and then import it on the database on the web host? Can someone advice please.

You have a few options:

Set up MySQL replication between the servers. Your internal server can act as the Master, and the web host server as the slave. Any updates performed on the Master will be replicated to the slave immediately (assuming a working connection). This will most likely be the easiest and most effective option to choose. To use replication, your internal database would need to be accessible over the network from the web host.
You can read more about replication here.

Each day, you can perform a mysqldump on the internal server, upload the dump file to the web host, and import the data. Since this is a full dump, if you have a very large database, this might not be feasible. If you like, this procedure could potentially be scripted to avoid having to do it manually.

You can set up binary logging on the internal server. You can then ship the binary logs to the web host and apply them to the database, effectively playing all transactions that occurred that day to the web server. In effect, this is what happens with replication anyway, so you would nearly always go with the replication set up instead of this option.

If there is no connection between the two databases, taking mysqldumps each day will be the easiest path to take.


Sync Data Without Replication I have a database server I am using an old dump from to restore due to the need of older records that must be brought back. I also need to sync to this database the new entries from the current database server. How would I accomplish this?

First step - take a backup of both databases (and other files my.cnf &c.)

Second step - you have your mysqldump of your old database.

Third step - edit the old mysqldump file and change the line

CREATE DATABASE old_database_name (may have backticks around name - also I'm assuming that old_database_name is the same as the current_database_name).

to

CREATE DATABASE staging (or name of your choice, but not one that
already exists in the current server schemas - SHOW SCHEMAS; will show you this information.

Fourth step - Using the MySQL client (or MySQL Workbench), create an empty schema on the current database also called staging (CREATE SCHEMA staging;)

Fifth step - "restore" the staging database to the current server, using the instructions from here.

In this case, it will be

mysql -u root -p[root_password] staging < old_dumpfilename.sql

So, you now have the current and old databases on the current server, BUT in different schemas.

Sixth step - Your main worry now is how to resolve any conflicts of PRIMARY KEYs.

You can check this by running

SELECT staging.old_table_PK from staging.old_staging_table_name
WHERE staging.old_table_PK
IN (SELECT current.current_table_PK FROM current.curent_table_name);

You need to specify the schema name because you are running cross schema queries. The above query will give you any records in both tables which have the same PRIMARY KEY. It will be up to you to resolve any conflicts using your system's logic.

Perform this query for all tables in your current database.

Seventh step - If there are no conflicts, then perform

INSERT INTO current.current_table_name SELECT * from staging.staging_table_name;

This should get you most of the way there. There may be typos in my answer, since I didn't have suitable data to perform a large scale check - but you can do any necessary testing yourself.

REMEMBER - BACKUP before you MESSUP!


SQL Server Agent maintains thousands of sleeping connections In a recent monitoring session I noticed that one of our main MSSQL 2014 databases had an increasing number of open connections.

It started with more than 1000 open connections, and now after two weeks it has increased to more than 2600. One would think that this would be improper connection handling in one of our applications, but out of the 2610 open connections, 2600 belong to SQLSERVERAGENT. When inspecting the result of sp_who2, almost all SQL Server Agent connections are program name 'SQLAgent - Temporary Worker', sleeping and 'awaiting command'.

I've searched for threads on improper behavior on the SQL Server Agent side, but almost all results talk about applications not closing their db connections properly. It seems to me that the jobs performed by the SQL Server Agent are not correctly being closed?

KB 3034297 - Cumulative Update 6 for SQL Server 2014 describes the issue that you are encountering and is fixed in SQL Server 2014 CU6

  When you turn on SQL Server Managed Backup in Microsoft SQL Server 2014. When you let SQL Server and SQL Server Agent run for several days, there are many connections with the program name SQLAgent - Temporary Worker. They are created by "sqlagent.exe," and cannot be closed.


Database Design Regarding a Relationship for Patient Visit I am designing a database to store records for research purposes, and I am having trouble picturing how I can create a relational database based on the information I have.

Busines rules

There are a total of 6 different studies, and patients can be categorized under more than one study if they fit the criteria.
Once they are under a study, they are required to pay routine visits for various type of exams (a total of 10 different kinds), i.e. clinical exams, physical exams, and other examinations to keep track of information.
Each of these various exams must be time stamped to allow back tracing.
In addition, patients are required to complete these exams up to three times while in the study, and each examinations has 10+ parameters that needs to be recorded as well.

So lets say there are 100 patients, these patients are categorized into studies A, B, C, D, E, F, with the possibility of being in more than one studies at a time (i.e. patient_25 can be in studies A,B, and E). Each study requires 3-8 different patient visits to collect information from examination, and some studies require the examinations to be performed up to a total of 3 times over a period of time.

What would be the best way to create tables that fit this kind of structure?
Here's my thoughts so far, let me know if there is a better way.

My representation with tables so far

Tier 1.

Studies Table: study_id(primary key), study_name, study_patient_code, patient_id(foreign key)

Tier 2.

Patient Demographics: patient_id(primary key), patient_name, patient_gender, patient_dob...etc.

Tier 3.

Visits Table: visit_id (primary key), patient_id (foreign key), visit_date, visit_type

Tier 4.

Physical Exam: physicalexam_id (primary key), visit_id (foreign key), pe_height, pe_weight, pe_score...etc.
Clinical Exam: clinicalexam_id (primary key), visit_id (foreign key), parameters....etc
Examination 3: exam3_id (primary key), visit_id (foreign key), parameters....etc
Examination 4: exam4_id (primary key), visit_id (foreign key), parameters....etc

.....

Examination 10: exam10_id (primary key), visit_id (foreign key), parameters....etc

So basically, the study table will be linked to patient demographics table. the patients table will be linked to visits, which will then be linked to various examination tables with columns consisting of the parameters recorded on the date of visit. Is this a viable method of approaching this problem? Or are there a more elegant way of doing so?

I hope this makes sense, if not, let me know and I'll be sure to elaborate more.

Additional information

I apologize for not doing a good job of explaining myself. So there are various type of exams that returns on average about 15-20 fields of data that needs to be stored. Each exam has its own purpose so the parameters will be different. For example, one exam may be just for Lumbar Puncture and contains fields with specific information with regards to that, physical exam will be for height, weight, blood pressure, BMI...etc. Patients may have to comeback for an exam multiple times during a study (for example, need to do physical exam on 1st, 2nd, 3rd visit.)

I guess what I'm trying to ask is where should the parameters of each exam be stored? and where should the time stamp be stored? How should I organize the tables so that we normalize the database for each visit without excessive repetition of information for different exams and the 1st, 2nd, 3rd visits?

In the long run, thinking in terms of tiers and of ID numbers is more likely to hurt you rather than help you.  At the start, use ID numbers only for those things that don't carry their identity with themselves. Studies should carry their identity with themselves; that is, their names should almost certainly be unique. Patients (people) don't carry their identity with themselves; there are many people named "John Smith".

My guess at primary keys is in parens. Foreign keys should be obvious.

Studies (study_name)
Patients (patient_id)
StudyPatients (study_name, patient_id)
Visits (study_name, patient_id, visit_timestamp)
PhysicalExam (study_name, patient_id, visit_timestamp)
ClinicalExam (study_name, patient_id, visit_timestamp)

It's not clear whether you need any additional columns to identify rows in PhysicalExam and in ClinicalExam. It doesn't look like it on first glance, though. Later, if there's sufficient interest and if I have enough time, I'll write this in SQL.


How are Postgres partitions counted for the table size limit? Postgres has a limit of 32T in a single table, but what if the table is partitioned - is that 32T per partition now?

So just to be clear, a table with two partitions would effectively be a maximum 64T table? This aligns with my understanding of the underlying mechanism (same as Oracle's) but I wondered if there was something I missed. – Gaius Jun 18 '16 at 17:08

Yes, that's true. Just to be clear. As @a_horse_with_no_name said partitions are implemented as different tables.

Though if you have more than 32T in a single table, even if partitioned, I would think something is funky.


Aggregation of duplicate data We have an interesting problem around duplicate data.  Our source files can contain multiple rows of data for a single employee benefit.  As an example, a health care benefit could be split up and reported over 3 rows (3 is just an example - it could be many more).

The data is loaded into staging with no problems and will contain a mix of single and multiple benefit rows.  What I have done thus far is to perform aggregation to find identify with multiple benefits (aggregate on employeeid and benefitid) and if it is > 1, move it to a separate staging table.

What I now need to do is analyse the staging table with multiple benefits and aggregate the data based on employeeid and benefitid.  Let's assume a benefit has 3 rows.

The employeeid and benefitid will be the key to identify those rows.  Other rows may include the benefit provider, yearly cost, eligible & benefit name (and many more but these cover the 4 types of aggregation required to output a single row).

if there are multiple providers in the list of 3, we should set the value to "Multiple" otherwise it should be the distinct value
yearly cost is simple...SUM of all 3 rows
if any of the active flags are true, set the value to true (1 could be true and 2 could be false)
if there are multiple benefit names, we just select the 1st non null value.

After the data is aggregated (3 rows into 1, applying the rules above), it is then merged with the single row benefits table.

Since you're using 2012+, you have windowing functions available to you which I think make this easier to solve.

Here's my set up.

IF NOT EXISTS
(
    SELECT *
    FROM
        sys.tables AS T
        INNER JOIN
            sys.schemas AS S
            ON s.schema_id = t.schema_id
    WHERE
        S.name ='dbo' AND T.name = 'StagingBenefit'
)
BEGIN
    CREATE TABLE
        dbo.StagingBenefit
    (
        StagingBenefitSK int identity(1,1) NOT NULL

    ,   EmployeeID int NOT NULL
    ,   BenefitID int NOT NULL

    ,   BenefitProvider char(1) NOT NULL
    ,   Cost int NOT NULL
    ,   FlagA bit NOT NULL
    ,   FlagB bit NOT NULL
    ,   FlagC bit NOT NULL
    ,   BenefitName varchar(30) NULL
    ,   Scenario varchar(30) NOT NULL
    );
END

TRUNCATE TABLE dbo.StagingBenefit;

INSERT INTO
    dbo.StagingBenefit
SELECT
*
FROM
(
    VALUES
        (1,1, 'A', 100, 0, 0, 0, 'Teeth', 'SingleRow')
    ,   (2,2, 'A', 200, 0, 0, 0, 'Teeth', 'DoubleRow, Samesies')
    ,   (2,2, 'A', 222, 0, 0, 0, 'Teeth', 'DoubleRow, Samesies')
    ,   (3,3, 'A', 300, 0, 0, 0, 'Teeth', 'ThreeRow,DoubleFlagged')
    ,   (3,3, 'A', 330, 1, 0, 0, 'Elbow', 'ThreeRow,DoubleFlagged')
    ,   (3,3, 'A', 333, 0, 1, 0, 'Elbow', 'ThreeRow,DoubleFlagged')
    ,   (4,4, 'A', 400, 0, 0, 0, 'Teeth', 'MultipleProviders')
    ,   (4,4, 'A', 440, 0, 0, 0, 'First', 'MultipleProviders')
    ,   (4,4, 'B', 444, 0, 0, 1,    NULL, 'MultipleProviders')
)D
(
    EmployeeID
,   BenefitID

,   BenefitProvider
,   Cost
,   FlagA
,   FlagB
,   FlagC
,   BenefitName
,   Scenario
)
;

I would then look at a query something like this

SELECT DISTINCT
    SB.EmployeeID
,   SB.BenefitID
,
    CASE
        WHEN
            MIN(SB.BenefitProvider) OVER (PARTITION BY SB.EmployeeID, SB.BenefitID)
            <> MAX(SB.BenefitProvider) OVER (PARTITION BY SB.EmployeeID, SB.BenefitID)
            THEN 'Multiple'
        ELSE
            MIN(SB.BenefitProvider) OVER (PARTITION BY SB.EmployeeID, SB.BenefitID)
        END AS BenefitProvider
    -- SUM this
,   SUM(SB.Cost) OVER (PARTITION BY SB.EmployeeID, SB.BenefitID) As TotalCost
,   CAST(MAX(CAST(SB.FlagA AS int)) OVER (PARTITION BY SB.EmployeeID, SB.BenefitID) AS bit) AS FlagA
,   CAST(MAX(CAST(SB.FlagB AS int)) OVER (PARTITION BY SB.EmployeeID, SB.BenefitID) AS bit) AS FlagB
,   CAST(MAX(CAST(SB.FlagC AS int)) OVER (PARTITION BY SB.EmployeeID, SB.BenefitID) AS bit) AS FlagC
,   MIN(SB.BenefitName) OVER
    (
        PARTITION BY SB.EmployeeID, SB.BenefitID
    ) AS FirstBenefit
,   SB.Scenario
FROM
    dbo.StagingBenefit AS SB

Picking through some of the tricks of the query

DISTINCT I feel this is the dirtiest part of it but it solves the problem
MIN/MAX BenefitProvider  - I find the first and last for my window (employee and benefit ids) and if they are different, which I determine via the CASE expression, I use the text Multiple. Otherwise, I just grabbed the minimum but max would work just as well. Heck, I probably could have just skipped the second aggregation call and just made it column name.
MAX Flag - I made this as hard as I could by using a bit data type which you cannot aggregate on so I explicitly convert to an integer and then cast the results of that back to a bit. Same windowing though. Net result is if anywhere my flag is set to true, it'll get preserved across rows
MIN BenefitName - the rule here is to pick the first non-null benefit name. The aggregate function eliminates the NULL and returns the text "First"


Can't create SSIS package from Import and Export Wizard Problem

I can't create a SSIS package from SQL Server Import and Export Wizard.

I get to the very last screen "Run Package" and only have the option to "Run Immediately" . At the bottom of said window I get a paragraph stating:

  In SQL Server Express, Web, or Workgroup, you can run the package that the Import and Export Wizard creates, but cannot save it. To save packages that the wizard creates, you must upgrade to SQL Server Standard, Enterprise, Developer or Evaluation.

Context

I have several CSV files located on a client system that I want to load into a remote system hosting the SQL Server instance.

The Wizard is being run on the client system.

"Run Package " of the wizard DOES work and data loading is possible.

System permissions are such that the file system of client and host are invisible to each other. I would have BULK INSERT'ed otherwise.

Technical Details

Client System:

Microsoft SQL Server Management Studio      10.50.4000.0
Microsoft Data Access Components (MDAC)     6.1.7601.17514
Microsoft  MSXML                            3.0 4.0 5.0 6.0
Microsoft Internet Explorer                 9.11.9600.18665
Microsoft .NET Framework                    2.0.50727.8669
Operating System                            6.1.7601

Host System:

[Microsoft SQL Server 2008 R2 (SP1) - 10.50.2811.0 (X64)   Apr  6 2012
01:59:29   Copyright (c) Microsoft Corporation  Enterprise Edition
(64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

We are using SQL Server 2012 SP3 [11.0.6020.0] Enterprise Edition and SSMS 2014 [12.0.4100.1] and have the same issue.

The pictures below are:

SSMS version
Import/Export Wizard complaining about edition
Result of @@version showing Enterprise Edition

Since at work I had SQL Server 2008 R2 SP1 Enterprise Edition [10.50.2500.0] + SSMS RTM [10.50.1600.1] (they just applied SP1 on server only, all clients remained RTM) I know 100% this couple was working fine.
I patched my Studio so it then became 10.50.6000.34 and this couple: SQL Server 2008 SP3 + SSMS SP3 was still working fine.
At home I use both the server and the studio patched, i.e. both of them [10.50.6000.34] and this configuration also has no issue.

Then the Server at work migrated: all the dbs were moved onto SQL Server 2012 SP3 that is shown on the picture [11.0.6020.0], for sure Enterprise Edition, we are using partitioning...And here came a surprise: our clients have the new SSMS 2014 [12.0.4100.1] installed, and the Wizard does not save the packages anymore!
That was the first time I see the bizard complaining about Edition.
So I was wondered if it's a known issue or no and I googled it up. I found no workarounds or explanaitions but I did found the Connect Item

Using Enterprise Edition; Can't Save SSIS Package from IMPORT WIZARD

Status :

Closed
  as Not Reproducible  Not Reproducible

So I just concluded it's a bug that won't be fixed  (ok. because we cannot give any reproducible scenario), but it's just there

I had no chance to test your version [10.50.4000.0] and tis is the first time I read that the issue persisted on 2008 R2. I thought it was SSMS 2014's bug


Documenting a giant web of inter-related stored procedures in an MS SQL database: What tool or format? I hope this is a question with a shorter answer than "Read a 1000 page book", but then, if that's the real situation, then hit me with it.

I am not a real DBA, I'm a software developer who is realizing we need a DBA, and yet the shop I work in has zero DBAs. However our MS SQL database design including several core stored procedures, is a giant mess. The stored procedures are slow, we suspect they have bugs, but we don't even know how they are expected to work, so we don't know how to fix them.

As a start I've decided we'll document how it's supposed to all work, then we'll start unit testing, and building up a set of unit tests that help prove that the stored procedures actually do work. The logic that they perform is a key part of our application, you could say, it's the "crown jewels" of our company's main product, and the way it works is completely undocumented.

I'm looking for the specific technical documentation that a professional DBA might expect to have existing, or might write themselves, if they had to, to understand a giant web of stored procedures that call each other.

What is the usual format for documenting a large stored procedure? Description of expected values for each In Parameter  (ie "preconditions", "postconditions", ie, for boolean parameters what changes when you turn it on or off, etc?)
How does one usually document it? SQL comments only? External tooling that is specific to the purpose? External "documentation"? We have no SQL tools, other than MS SQL Management studio, but we are wondering if there is a tool that would make understanding, documenting, and testing our environment better.  Maybe that is a better way to ask my question; What tool do I need to solve our mess?

Our goal is to be able to:

A. Use the documentation we generate, or whatever tools we add to our environment, to help understand how the procedures are supposed to work, so we can then go on to create unit test coverage for the stored procedures.

B. Show the client-app developers how to properly call each of these complex stored procedures.

C. Unit test our stored procedures.

The most important thing about documentation is that it makes sense to you.  There's no really standard way of doing this.

If you've got lots of stored procedures that all connect to each other starting with a Visio diagram with one object for each procedure, then links between them so you can track how things go from procedure to procedure is probably a pretty good start.


Table to create Ids for one table to many table relationships I have several tables that will represent an object that can be commented on. I would like to store all the comments in one table. To relate the other tables to the comments, I was thinking of adding a CommentParentId column to each one. That column would need to be unique across all the tables that are related to the comments table, so I was thinking of creating a table, CommentParents, that would simply have an Id column in order to have the DB handle generating the Ids. For instance:

Comments

Id
Title
Body
User
Date
CommentParentId

Posts

Id
Title
Body
User
Date
CommentParentId

Images

Id
Title
User
File
Date
CommentParentId

Users

Id
Name
Email
DateJoined
CommentParentId

CommentParents

Id

So that table would simply be used to create new Ids. Is there another way to do this? What are the advantages?

I'm using SQL Server 2014

You could utilize a sequence object to facilitate the mechanism of generating the IDs if that is the sole purpose of the CommentParents table.  Take a look at the TechNet documentation for the TechNet SQL Server 2014 Sequence Documentation for more detailed information.  What this sequence object will do that your current table can't do is allow you to grab a value and assign it to a variable ahead of time without worrying about doing an INSERT/SELECT SCOPE_IDENTITY() process.  The advantage here is at great scale the current design you have would break down due to the need to maintain metadata overhead where SQL Server has caching for Sequence objects.


What is causing BETWEEN to be replaced with =? We have a Visual Studio solution for our SQL Server 2019 database, and it is stored in a git repo. One table has a couple of check constraints using the BETWEEN operator, like this.
CONSTRAINT chk_FileTbl_MonthlyDate CHECK (MonthlyDate BETWEEN 1 AND 31)

When I used Redgate SQL Compare 14 to compare the repo to the database, it displayed the constraint reformatted with less than and greater than operators, like this.
CONSTRAINT chk_FileTbl_MonthlyDate CHECK (MonthlyDate >= 1 AND MonthlyDate <= 31)

I thought it was a bug/feature in Redgate's software and submitted a ticket for which I am awaiting a response. However, my colleague then used the comparison tool in Visual Studio 2019 and it did the same thing. If we look at the file defining the table, it uses BETWEEN, but both comparison tools show and deploy a change script with the less than/greater than format.
What is causing this behavior? We somewhat recently moved to SQL Server 2019, is it some kind of built-in optimization?

If you write a query and generate an execution plan, you will see that it, too, translates BETWEEN to a pair of >= and <= predicates. These mean the same thing. Redgate is just showing you what SQL Server stored when you created the constraint, not what you typed. And it has nothing to do with SQL Server 2019.
That said, I prefer the two-predicate pattern, and talk about why BETWEEN is bad for date ranges here and here. My opinion is to avoid exceptions, especially when it is just convenient shorthand / syntactic sugar, and especially when it contradicts how SQL Server stores it internally. In other words, if you don't like that scripting yields a different result than your code, change your code and embrace >= AND <=.


Mongodb: Document size of 29344731 is larger than maximum of 16777216 When we are upgrading AEM 6.1 instance with mongo (Mongo version: 2.6 ) to AEM 6.3  with mongo (Mongo version: 3.2) we are facing the exception.

  org.apache.jackrabbit.oak.plugins.document.DocumentNodeStore
  Background operation failed: org.bson.BsonSerializationException:
  Document size of 29344731 is larger than maximum of 16777216.

How DocumentNodestore exceeds its limit?
Is there anyway to resolve the issue?
Is revision garbage collection will reduce the DocumentNodeStore size?

As per MongoDB BOL here The maximum BSON document size is 16 megabytes.

The maximum document size helps ensure that a single document cannot use excessive amount of RAM or, during transmission, excessive amount of bandwidth. To store documents larger than the maximum size, MongoDB provides the GridFS API.

GridFS is a specification for storing and retrieving files that exceed the BSON-document size limit of 16 MB.

For your further ref here and here


Identifying rows which don't match a master row I'm comparing a bunch of tables from different databases on different servers to a Master record. I need to know which servers, identified by locationID, have the non-matching rows because they might need maintenance.

I've got a simple EXCEPT query where I compare a table where each row is the configuration from each server; table1 has one row per server with all configuration plus locationID which is a column that tells me which server it is. I compare these all to a table1_master table which has the right settings, but I exclude the locationID since it won't match.

Simple query below:

SELECT everything, but, locationID
FROM table1
EXCEPT
SELECT everything, but, locationID
FROM table1_master

There's only one master row I compare all servers to, and I don't select it's locationID here.

This is an example of the rows I'm comparing. Each has a primary key, a single column varchar and a giant list of that's dozens of columns. I want to compare all columns except LocationID, but I need LocationID to identify the rows.

LocationID             setting    setting    setting     setting
CS02      C            Y           Y         Y           Y
CS03      C            Y           Y         Y           Y
CS06      C            Y           N         Y           Y

In this example say CS02 is my Master record, so since all settings are the same in CS02 and CS03, those rows don't show up, but CS06's does. But in my EXCEPT query, I'm not actually catching LocationID so I don't actually know which row was returned.

This returns the rows I need but NOT the locationID, so I don't know which rows are wrong. Is there any way I can include locationID in the results set while kicking out the matching rows?

The solution I thought of was to make a row for each server in the table1_master table, so each locationID is represented, but they all have the same data other than that. My EXCLUDE query should then return the locationID and my info, but is that the best way to do it?

You can also do this with dynamic SQL without having to manually build out all the column names.

DECLARE @sql NVARCHAR(MAX), @c1 NVARCHAR(MAX), @c2 NVARCHAR(MAX);

SELECT @c1 = N'', @c2 = N'';

SELECT
  @c1 = @c1 + ',' + QUOTENAME(name),
  @c2 = @c2 + ' AND m.' + QUOTENAME(name) + ' = s.' + QUOTENAME(name)
 FROM sys.columns
 WHERE name <> 'LocationID'
 AND [object_id] = OBJECT_ID('dbo.table1');

SET @sql = ';WITH s AS (
       SELECT ' + STUFF(@c1, 1, 1, '') + ' FROM dbo.table1
       EXCEPT
       SELECT ' + STUFF(@c1, 1, 1, '') + ' FROM dbo.table1_master
     )
     SELECT m.LocationID
 FROM s INNER JOIN dbo.table1 AS m ON 1 = 1
 ' + @c2;

SELECT @sql;
--EXEC sp_executesql @sql;

You can take the output of this query as is and store the query somewhere, or you can comment out the SELECT and uncomment the EXEC and leave it as permanent dynamic SQL - in this case it will automatically adapt to column changes in the two tables.

Another idea (assuming LocationID is unique) - and it occurred to me you may want to include the master row so you can quickly spot the columns that are different:

  ;WITH c AS
  (
    SELECT t.LocationID, m.setting1, m.setting2, ...
      FROM dbo.table1 AS t CROSS JOIN dbo.table1_master AS m
  )
  SELECT DISTINCT src = '> master', setting1, setting2, ...
    FROM c
  UNION ALL
  (
    SELECT RTRIM(LocationID), setting1, setting2, ...
      FROM dbo.table1
    EXCEPT
    SELECT RTRIM(LocationID), setting1, setting2, ...
      FROM c
  )
  ORDER BY src;

This version is a little cheaper (mostly by avoiding the DISTINCT against the master table, at the cost of needing to specify all of the columns one more time - which again you can automate as per above):

  ;WITH m AS
  (
    SELECT setting1, setting2, ...
      FROM dbo.table1_master
  ),
  c AS
  (
    SELECT src = RTRIM(t.LocationID), m.setting1, m.setting2, ...
      FROM dbo.table1 AS t CROSS JOIN m
  )
  SELECT src = '> master', setting1, setting2, ...
    FROM m
  UNION ALL
  (
    SELECT RTRIM(LocationID), setting1, setting2, ...
      FROM dbo.table1
    EXCEPT
    SELECT src, setting1, setting2, ...
      FROM c
  )
  ORDER BY src;

However all of these options are poorer performers with worse plans than Rachel's simple LEFT JOIN. I tried to stick to the theme of using EXCEPT even though it is more about syntax than performance.

The key takeaway is that if the column count is too high to deal with manually, you can use the dynamic SQL approach above to construct whatever query you want to use - and you can do that one time and store the result, or have the code generated every time. To generate Rachel's query using dynamic SQL, not much needs to change:

DECLARE @sql NVARCHAR(MAX), @and NVARCHAR(MAX), @anycol NVARCHAR(128);
SELECT @sql = N'', @and = N'';

SELECT @and = @and + ' AND t.' + QUOTENAME(name) + ' = m.' + QUOTENAME(name)
  FROM sys.columns
  WHERE [object_id] = OBJECT_ID('dbo.table1_master');

SELECT TOP (1) @anycol = QUOTENAME(name)
  FROM sys.columns
  WHERE [object_id] = OBJECT_ID('dbo.table1_master')
  ORDER BY name;

SET @sql = 'SELECT locationID
FROM dbo.table1 AS t
LEFT OUTER JOIN dbo.table1_master AS m ON 1 = 1'
  + @and + ' WHERE m.' + @anycol + ' IS NULL;';

SELECT @sql;
--EXEC sp_executesql @sql;


Ubuntu: upgrading postgresql 8.4 database cluster for use with 9.2 server I have installed PG 9.2 on my machine. I have a database cluster that was created about a year ago, using initdb, using postgreSQL version 8.4.

The (8.4) data cluster consumes several Gb and so its on its own separate hard disk. I instructed the old 8.4 server to use the correct data location by passing it a -D /path/to/data/folder when the server was started.

My question is this:

I have a (v8.4) database cluster (containing about 10 databases) located at /some/path/to/datadir

I have a freshly installed postgreSQL 9.2 server.

How do I upgrade the cluster (and its databases), so that the new server can use the cluster - bearing in mind that I want the data to remain in the current data location (a physically separate device mapped to '/').

I tried running the server against the old cluster file by modifying the configuration file, and I (unsurprisingly), got the following error:

 * Starting PostgreSQL 9.2 database server                                                                                * The PostgreSQL server failed to start. Please check the log output:
2012-11-18 23:50:31 GMT FATAL:  database files are incompatible with server
2012-11-18 23:50:31 GMT DETAIL:  The data directory was initialized by PostgreSQL version 8.4, which is not compatible with this version 9.2.1.

So how can I upgrade an 8.4 cluster (in a specified data directory) to be used by a 9.2 server?

Don't know about 2) and 3) (I don't use Ubuntu).

But to migrate the data from 8.4 to 9.1 pg_dump.

Assuming that that 8.4 runs on port 5432 and 9.1 runs on port 5433 something like the following will copy all the data from 8.4 to 9.1:

/usr/lib/postgresql/9.1/bin/pg_dump -p 5432 -U -c -C postgres my_db | /usr/lib/postgresql/9.1/bin/psql -p 5433

This assumes that your current user can log on to both databases.

The full path to pg_dump is necessary to ensure that the dump is taken using the target version.

Of course you can also use an intermediate file if you prefer that. More details are in the manual: PostgreSQL: Documentation: 9.1: Backup and Restore

Using the pg_dump binary from the new version is intentional.

From the manual: 17.6. Upgrading a PostgreSQL Cluster - 17.6.1. Upgrading Data via pg_dump

  It is recommended that you use the pg_dump and pg_dumpall programs from the newer version of PostgreSQL


how to repair suspect db I am trying to repair a suspect database.
The problem is that when I run:

EXEC sp_resetstatus property;

it gives me this response:

Warning: You must recover this database prior to access.

when I try this:

ALTER DATABASE property SET EMERGENCY;

it also fails:

Msg 5011, Level 14, State 7, Line 1
User does not have permission to alter database 'property', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

what do I do next?
Thanks.

Its good to restore from an earlier backup copy.If you dont have one..below are the steps that can be done to repair suspect database..this has been explained with more examples by Paul Randal here :Creating, detaching, re-attaching, and fixing a SUSPECT database

Overview of steps

set suspect database offline
Copy those mdf ldf files to some location
drop the old suspect database
create a new database with same name and same file layout
set this new database offline
delete the newly created mdf,ldf files and copy old corrupted files
now try bringing the database online and try repairing with DBCC

below are the scripts

CREATE DATABASE DEMOSUSPECT

ALTER DATABASE DEMOSUSPECT SET OFFLINE

--- you have to copy and drop the old database files prior to this
----NOW DELETE THE mdf and ldf files created for new database

--copy those corrupted files into new location
alter database demosuspect set online

 --check status once
SELECT DATABASEPROPERTYEX (N'DemoSuspect', N'STATUS')

--tyr putting the database into emergency mode and use DBCC
ALTER DATABASE [DemoSuspect] SET EMERGENCY;
GO
ALTER DATABASE [DemoSuspect] SET SINGLE_USER;
GO
DBCC CHECKDB (N'DemoSuspect', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO

-- Check the state
SELECT DATABASEPROPERTYEX (N'DemoSuspect', N'STATUS') AS N'Status';
GO


Accessing computed column in a nested query I am using nested sets to represent tree-type date in SQL-Server and basing what I'm doing on this article:

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/  by Mike Hillyer.

In the article Hillyer uses the following SQL to return the depth of each node:

SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

I would like to return a list of all nodes that have a depth of, say, 2.  My attempts have been around using a sub-query but I have been unable so far to figure out where to add the check for "depth = 2".  This is what I have so far:

select * from nested_category nc
  where exists
      (select node.category_id, (COUNT(parent.name) - 1) AS depth
       from
          nested_category AS node,
          nested_category AS parent
        where
          node.lft between parent.lft and parent.rgt AND
          nc.category_id = category_id
        group by
          node.category_id, node.lft
       ) as ???

How can this be done?

You just have your nesting a little wrong (and introduced an unnecessary EXISTS clause):

SELECT * FROM
(
   SELECT node.category_id, ...
) AS nested
WHERE depth = 2;

You could also try (I think):

SELECT node.category_id, (COUNT(parent.name) - 1) AS depth
...
    group by
      node.category_id, node.lft
HAVING (COUNT(parent.name)-1) = 2;

But please be mindful of:

Schema prefix
Old-style JOINs


Convert UTC time to local time with specific format in Redshift I have a column called created_at with data stored in UTC without timezone like : 2017-02-05 15:43:27.151

I would like to convert to an other timezone like CET in specific format like :

2017-02-08 16:43:27 +01:00

I succeed converting the time to specific timezone but needed to specify the timezone of the input. Query looks like :

SELECT created_at,
       (created_at AT TIME ZONE 'UTC') AT TIME ZONE 'CET' AS cet
FROM my_table;

[
  {
    "created_at" : "2017-02-08 15:43:27.151",
    "cet"        : "2017-02-08 16:43:27.151"
  }
]

But I failed to extract or generate from timezone code the timezone offset.

I tried

SELECT EXTRACT(TIMEZONE_HOUR
               FROM
                 (SELECT '2017-02-08 15:43:27.151' AT TIME ZONE 'CET'));
[
  {
    "date_part" : 0
  }
]

How to get the 2017-02-08 16:43:27 +01:00 ?

EDIT 1:

SHOW timezone; --utc
SELECT current_timestamp; -- 2017-02-09 11:00:20.225039+00
SELECT to_char(current_timestamp AT TIME ZONE 'CET', 'YYYY-MM-DD HH24:MI:SS OF'); -- 2017-02-09 12:00:35 +00

Why the offset is 00? (I'm using OF because I'm on Redshift)?

  OF |  Offset from UTC; valid for TIMESTAMPTZ only

From doc

EDIT 2: I thought AT TIME ZONE 'CET' turn the the date to timestamptz but I was wrong. You need to cast the date as ::timestamptz then OF will display the UTC offset.

SELECT to_char((current_timestamp AT TIME ZONE 'CET')::timestamptz, 'YYYY-MM-DD HH24:MI:SS OF');
--2017-02-09 14:48:48 +01

Now I need to add the :00.


Unable to create replica set for MongoDB in Linux When I run the following command: rs.initiate(),

I get the following error:

"info2" : "no configuration explicitly specified -- making one",
        "me" : "ip-10-0-2-113:27017",
        "ok" : 0,
        "errmsg" : "No host described in new configuration 1 for replica set s-1-rs maps to this node",
        "code" : 93

I'm just running on the local host. I'm following this guide: http://docs.mongodb.org/manual/tutorial/convert-standalone-to-replica-set/

I just skipped the part where they named the replica set.

In any case, how can I create a replica set and what am I doing wrong?

Thanks

Your hostname must be an alias for 127.0.0.1 in your /etc/hosts file to make it work.

aldwin@myhostname~$ cat /etc/hostname
myhostname
aldwin@myhostname:~$ hostname
myhostname
aldwin@myhostname:~$ cat /etc/hosts
127.0.0.1   localhost
127.0.1.1   myhostname


Run job once a minute, every day, except during a certain time I have a Sql server 2012 agent job which currently runs every minute, every day, etc.

I'd like to modify it so that it will run every minute, every day except on Saturdays between 7:00 pm and 11:30 pm. Is this possible with the job scheduler?

I tried using a daily frequency and I can change the time on a per day basis that way, but not for a specific day.

Current schedule:

I don't think you can work with that single job to get that exact schedule.  You might want to consider having another job that disables/enables the job for the period of time that you don't want it to run.  Another solution would be to add another conditional logic to your existing job and do a check on the current day of the week and what time it is, and decide whether or not to execute your job's logic.

A few alternatives here, but I think you'll have to get creative with your relatively complex schedule.


I'm building up a large MyISAM table from a few dumps. The first 21GB took less than 10 hrs total. The next 5GB is forecasted to take two months I had a couple machines building up identical MyISAM tables.  I've dumped all of those tables to a few files, and am now trying to restore them all into a single table on my own machine.

The first four restores, done by pv'ing the dumpfile into mysql, took no more than 10 hrs in total (may have been as little as 5).  Those files were around 5-13 GB each.

The table is now about 60 million rows and 21 GB.  It is also hash-partitioned into 100 partitions.  However, I partitioned it when it was 20 million rows and got it up to 60 million without much problem.

Now I'm trying to dump another 5GB (maybe about 15 million rows) into the table, and pv is predicting it will take 50+ days. This doesn't seem to be a problem with pv, however, because the db's data directory is growing very slowly when I do the restore.

Additionally, I've looked at gnome-system-monitor, and the CPU and memory usage seems to be quite low.  Tried rebooting and attempting to restore again, no change.

This is actually my second attempt at building up the big table.  I tried once before over a longer period (waiting for the data to come in on the other machines), and suddenly I was getting this same problem.

This is a table with a single (natural) BIGINT primary key.  I don't know a whole lot about database internals, but I tried a query suggested in this other post: slow load speed of data from mysqldump

to get the size of the index.  Here are the results (reformatted for readability)

Storage Engine: MyISAM
Data Size: 19.706 GB
Index Size: 0.954 GB
Table Size: 20.660 GB

I would make sense that inserts would get slower as the table gets larger.  However... it doesn't make sense that going from 60GB to 80 or less should take 100 times as long as going from 0 to 60.

So, does anyone have any guess what might be causing this?

Thanks

After a bit more research, I ran "OPTIMIZE table_name", and now the restore that would have taken two months is taking half an hour.  According to the docs, the following is a good time to do run OPTIMIZE:

  After deleting a large part of a MyISAM or ARCHIVE table, or making
  many changes to a MyISAM or ARCHIVE table with variable-length rows
  (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns). Deleted
  rows are maintained in a linked list and subsequent INSERT operations
  reuse old row positions. You can use OPTIMIZE TABLE to reclaim the
  unused space and to defragment the data file. After extensive changes
  to a table, this statement may also improve performance of statements
  that use the table, sometimes significantly.

I didn't do any deletions (does "changes" include insertions?), but I do have big VARCHAR fields.

from here: http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html


Using IN with LIMIT in MySql Here is my old query which was working fine :

SELECT tag.tag
FROM tag, v_bookmark_tag_assoc
WHERE tag.tag_id = v_bookmark_tag_assoc.tag_id
AND v_bookmark_tag_assoc.bookmark_id = '123'
ORDER BY RAND() LIMIT 10

Now I need to update the query to feed in all the "v_bookmark_tag_assoc.bookmark_id" as in IN clause of MySql, so that I can have all the result up in one output array for all input ids.

Problem is if if modify query like so :

SELECT tag.tag
FROM tag, v_bookmark_tag_assoc
WHERE tag.tag_id = v_bookmark_tag_assoc.tag_id
AND v_bookmark_tag_assoc.bookmark_id IN(123,456,678,890)
ORDER BY RAND() LIMIT 10

It doesn't make sense at all using LIMIT of 10 for each input id is illegitimate here this way.

Please help me out to modify this query in such a way that I can have 10 tags for each input bookmark id.

There are several different ways to approach to problem.  One is simply to group by the bookmarks you care about then obtain a list of tags for each - the substring_index function is being used to limit the number of tags to ten which are in ascending order.  Order and the separation character can be changed to fit your purpose.

select bm.bookmark_id
     , substring_index(group_concat(tag.tag order by tag.tag asc separator ','),',',10) as tag_list
  from v_bookmark_tag_assoc bm
     , tag
 where bm.bookmark_id in (123,456,678,890)
   and tag.tag_id=bm.tag_id
 group by bm.bookmark_id


Mysql: Create a view with multiple self joins without duplicates in result Just to clarify i can't change the tables structure, so please leave out the "you should change your tables to this and that" answers, thank you.

So i have a table entities_attributes_values where an entity has a lot of attributes and the value of that attribute, basically imagine 3 fields:

entity_id
entity_attributes_id
value

Because every entities attribute and its value is on row getting more values is not so easy i was thinking of multiple self joins, and because this query will be very common i created a view, which is built with this query:

SELECT `L1`.`entity_id`,
       `L1`.`value` as 'company_id',
       `L2`.`value` as 'entity_name',
       `P`.`value` as 'person_name',
       `L4`.`value` as 'establishment_id',
       `L5`.`value` as 'department_id'
FROM `entities_attributes_values` `L1`
LEFT JOIN `entities_attributes_values` `L2` ON `L1`.`entity_id` = `L2`.`entity_id` AND `L2`.`entity_attributes_id` = 1
LEFT JOIN `entities_attributes_values` `L3` ON `L1`.`entity_id` = `L3`.`entity_id` AND `L3`.`entity_attributes_id` = 3
LEFT JOIN `persons_attributes_values` `P` ON `L3`.`value` = `P`.`core_persons_id` AND `P`.`core_persons_attributes_id` = 4
LEFT JOIN `entities_attributes_values` `L4` ON `L1`.`entity_id` = `L4`.`entity_id` AND `L4`.`entity_attributes_id` = 12
LEFT JOIN `entities_attributes_values` `L5` ON `L1`.`entity_id` = `L5`.`entity_id` AND `L5`.`entity_attributes_id` = 13
WHERE `L1`.`entity_attributes_id` = 2

So this works but i have one problem i get "duplicate" values and its not really duplicate but the point is that in my view i want every entity to be only one row with all its attributes values but instead i get this:

So as you can see the first three result are not good for me, i only need the fourth one, where i have all my data about one entity.

Thank you in advance for any help!

Inner Join:

An inner join produces a result set that is limited to the rows where there is a match in both tables for what we're looking for.  If you don't know which kind of join you need, this will usually be your best bet.

Left Outer Join:

Add to Where

... and establishment_id is not null and department_id is not null

And sometimes just to make it complete i do also

... and establishment_id<>'' and department_id<>''


Insert if names do not exist and return ids if exist Suppose I have an array of string "a","b","c","d","e".

CREATE TABLE foo (
  id   int   GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name text  UNIQUE
);
INSERT INTO foo (name) VALUES ('a'), ('d');  -- assigned IDs 1 and 2

What will be the query to pass an array of strings and insert each if that name doesn't exist and return all IDs for the given array? In this example, it should return 1,2,3,4,5 as an output of SQL execution.

It's the recurring problem of INSERT or SELECT, related to the common UPSERT, but not the same. INSERT ... ON CONFLICT ... DO ... (commonly called UPSERT), introduced with Postgres 9.5, is instrumental in any case.

Assuming the simple case without concurrent write load on the table:

CREATE TABLE foo (
  id   int   GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name text  UNIQUE
);
INSERT INTO foo (name) VALUES ('a'), ('d');  -- assigned IDs 1 and 2

WITH input(name) AS (SELECT unnest('{a,b,c,d,e}'::text[]))  -- input array once
, ins AS (
   INSERT INTO foo(name)
   TABLE  input
   ON CONFLICT (name) DO NOTHING
   RETURNING id
   )
SELECT f.id
FROM   input i
JOIN   foo   f USING (name)
UNION  ALL
TABLE  ins;

| id |
| -: |
|  1 |
|  2 |
|  4 |
|  5 |
|  7 |

db<>fiddle here

Note the missing serial IDs 3 and 6. A side effect of UPSERT is that conflicting rows burn a serial number since default values are fetched before checking for conflicts, and sequences are never set back. That should be irrelevant, since gaps in serial numbers are to be expected at all times. Meaning, you cannot rely on a result 1,2,3,4,5 for the given example (and assumed setup).

Detailed explanation and alternative solutions - especially for concurrent write load:

Is SELECT or INSERT in a function prone to race conditions?
How to use RETURNING with ON CONFLICT in PostgreSQL?


How to specify the primary key number space for a postgres table? I have a postgres table that contains data where the id has been set by the code that injected the data. So lets say the id is 0..49.

How can I instruct postgres to start with 50 on the id column for the next insert?

If you want to happen it automatically (I mean, without the application doing that), you have to create a sequence:

CREATE SEQUENCE some_table_pk_seq START 50;

Then change the column to use this as a default:

ALTER TABLE some_table
ALTER COLUMN id SET DEFAULT nextval('some_table_pk_seq'::regclass);

And that's it.  Remember that the default will be used only if you don't pass a value to the given column in your INSERT statements.


MS Access: Capture ListBox Scroll Event? An Access 2016 ListBox control has 10 items, only 1 showing at a time. My Goal: when the user scrolls up/down using the scrollbar arrows, I would like the item displayed to be automatically selected without the user directly clicking on the item (via an event handler).

The scrollbar action does not appear to be captured by any ListBox Event. While the On Got Focus event will capture the first mouse click on the scroll buttons, subsequent clicks on the buttons do not trigger any control event.

I do understand that I can create custom up/down buttons to simulate the scrollbar actions, but I prefer to use built in routines.

Am I correct to conclude that the scrollbar events are handled by the Windows event handler, not Access?

There is no such event handler available in Access.  Access controls are very limited compared to, for example, the .Net counterparts or other development environments.

I can't disagree with using "built in routines", but even if the scrollbar did expose more events, you'd still be trying to force the listbox to behave in a non-standard way.  Especially in Access you'll have better luck implementing something with specialized buttons as you've already mentioned.


Reference multiple rows of one table from another this is a programming challenge I've undertaken to learn some MySQL fundamentals and I can't figure out how to implement a one-to-many relationship in the manner I need. See the below code:

CREATE TABLE Product (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    price FLOAT(7,7) NOT NULL,
    needed_for_bundle INT(2) NOT NULL
)

CREATE TABLE Bundle (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    price FLOAT(7,7)
)

I can't find anywhere how to do this or I'm just not understanding the examples I've found that were similar. If these were python objects, I would have a list of product IDs in the bundle table/object that reference the 3-4 items in the bundle. I know this is a one-to-many relationship but I'm not understanding lexically and theoretically how it would work in MySQL.

Help would be much appreciated. Thank you all.

Setting the column "needed_for_bundle" as a Foreign Key that references the Primary Key of the table Bundle would mean that there's a one-to-many relationship between the two tables. Each product has to belong to one bundle, and one bundle can be composed of one or * product(s).

So first, needed_for_bundle should be the same type as the Primary key of Bundle table which is INT(6).

After changing that you can apply this code, for the foreign key constraint + index:

ALTER TABLE `Product`
ADD FOREIGN KEY (`needed_for_bundle`)
    REFERENCES `Bundle` (`id`)
    ON UPDATE CASCADE
    ON DELETE CASCADE;
CREATE INDEX `fk-id_bundle-Product-Bundle`
    ON `Product` (`needed_for_bundle`) USING BTREE;


Take individual MySQL database offline MySQL does not have any SQL commands or internal mechanisms for

making an individual database unavailable / offline
moving an individual database

Thtis being the case, how can you take an individual database offline?

This may not be a suitable answer, but would achieve your goal...

What about, for the time you want it offline, renaming the database? This way, whatever application you have accessing the data would fail to connect to the database.

Moving the database can be achieved by

mysqldump -u user -p database -c > whatever.sql

Then import whatever.sql into another server, finally dropping the original DB.


Oracle Backup/Recovery architecture When we write the following:

allocate channel ch1_tape type sbt;
set controlfile autobackup format for device type sbt to '%F';
restore controlfile  from autobackup;
RELEASE CHANNEL ch1_tape;

By what variables does RMAN search the backups? I think just by DBID, am I wrong?

Thank you very much!

With a modern release (>= 10g) and the usage of a Recovery Area (DB_RECOVERY_FILE_DEST is set), the initialization parameter DB_UNIQUE_NAME (derived from DB_NAME if not specified explicitly) tells RMAN in what subdirectory of the Recovery Area the controlfile autobackup must be searched for.
Without usage of Recovery Area, and in status NOMOUNT (no controlfile is opened), you must set DBID for RMAN in order to enable it to retrieve the right controlfile autobackup.


Advanced job flow control I want to create a job with two steps:
 1. Success - go to step two, failure - exit(f).
 2. Success - exit(s), failure - go to step one, but if this step fails more than once - exit(f).
Initial thought was to query some kind of job run id in step one, and if there are four (or more) rows for this id exit with failure, but there is no such thing as run id.
What is the best way to implement this logic?

You can create duplicates of steps 1 and 2 as steps 3 and 4, and set the following flow:

Success - go to step two, failure - exit(f).
Success - exit(s), failure - go to step three
Success - go to step four, failure - exit(f).
Success - exit(s), failure - exit(f).

But if you can instead wrap steps 1 and 2 in a stored procedure or ad hoc query that handles the retry logic without duplication, then your solution will be much more understandable and maintenance-safe.


How to update commands in Ola Hallengren's SQL Server jobs? I am looking for an easy way to update the jobs that get created from Ola's MaintenanceSolution.sql script without having to completely delete and re-create them or having to modify each job individually by going into the job's properties, then into the step and modifying the command.

Specifically, I'm trying to update the @CleanupTime argument, which is used to tell the job when to delete old backups. According to his FAQ, whenever a newer version of his script is available and you want to update to that newer version, you just need to download the MaintenanceSolution.sql script and run it again, with the option of specifying SET @CreateJobs = 'N' if you don't want to create the jobs. That tells me that if you leave @CreateJobs = 'Y', it would create the jobs anew, but this doesn't seem to be the case.

For example, if the "DatabaseBackup - USER_DATABASES - FULL" job already exists, with the command in the step showing @CleanupTime = 48, then I re-run the MaintenanceSolution.sql script with @CleanupTime = 168, and then go back into the job's step, it still shows @CleanupTime = 48.

You can update steps without completely recreating everything and without pointing and clicking yourself to death in the UI. You can just write manual queries directly against msdb.dbo.sysjobsteps (start with SELECTs that issue the REPLACE() calls, then when you're happy, change it to UPDATE).

SELECT REPLACE(js.command, N'foo', N'bar')
  FROM msdb.dbo.sysjobsteps AS js
  INNER JOIN msdb.dbo.sysjobs AS j
  ON js.job_id = j.job_id
  WHERE j.name = N'name of the job';

When you're satisfied it's correct, only the first couple of lines change:

UPDATE js
  SET command = REPLACE(js.command, N'foo', N'bar')
  FROM msdb.dbo.sysjobsteps AS js
  INNER JOIN msdb.dbo.sysjobs AS j
  ON js.job_id = j.job_id
  WHERE j.name = N'name of the job';

It's tough to get more automated than that (e.g. update more than one job at once), since the string you want to replace might not be exactly the same in all job steps, and you might get false positives, too.


What does repair and optimize tables in PHP MyAdmin actually do? Sometimes I repair or optimize (normally both) my tables in PHP MyAdmin  Often it fixes a problem I'm having.  My question is what am I actually doing when I undertake these actions?

Is there any danger?  Is any data changed or moved?

Someone told me Optimize is...

Do you remember when we used to defrag HDDs? Table optimization is something similar to that. It restructures a table so that data pages are next to each other and releases unused pages. If you ever delete a large part of the table data, optimization shrinks the table file down to the proper size.


Is there any special meaning for numbers in braces like {0} or {1} in LDIF files of OpenLDAP? I'm playing a bit with OpenLDAP.

In the configuration LDIF files there are often numbers in braces like {0} or {1}, e.g:

dn: olcDatabase={0}config,cn=config

or

olcAttributeTypes: {0}( 1.3.6.1.1.1.1.2 NAME 'gecos' DESC 'The GECOS field;
 the common name' EQUALITY caseIgnoreMatch SUBSTR caseIgnoreSubstringsMatch
 SYNTAX 1.3.6.1.4.1.1466.115.121.1.15 SINGLE-VALUE )
olcAttributeTypes: {1}( 1.3.6.1.1.1.1.3 NAME 'homeDirectory' DESC 'The absol
 ute path to the home directory' EQUALITY caseExactIA5Match SYNTAX 1.3.6.1.4
 .1.1466.115.121.1.26 SINGLE-VALUE )
olcAttributeTypes: {2}( 1.3.6.1.1.1.1.4 NAME 'loginShell' DESC 'The path to
 the login shell' EQUALITY caseExactIA5Match SYNTAX 1.3.6.1.4.1.1466.115.121
 .1.26 SINGLE-VALUE )

Do the {0}, {1} and so on have any special meaning (like parameters) or are they just strings?

It's for list ordering: Attributes or entities with the same key are sorted using the number in the braces. Especial need for access control lists, as the ordering of the rules matters, e.g.

olcAccess: {0}to attrs=userPassword,shadowLastChange
    by self write
    by dn="cn=admin,dc=mobikat,dc=net" write
    by anonymous auth
    by * none
olcAccess: {1}to *
    by self read
    by dn="cn=admin,dc=mydomain,dc=net" write
    by dn="cn=readonly,dc=mydomain,dc=net" read
    by * none


What's the impact of running sp_updatestats in production server? Is it safe to run sp_updatestats on an SQL Server in the production environment?

Or rather, what is the impact of updating all statistics on a sql server? Can it "choke" the sql server while it runs and cause timeouts or other issues for the users?

That's a big 'it depends.'  Depending on how your statistics have been maintained and the options you specify you could end up running full table/index scans and thrashing your I/O and buffer pool. Depending on the characteristics of your hardware and databases that could be very bad.

Also, rebuilding statistics invalidates execution plans, which means you could see a CPU spike and slower performance while SQL Server re-compiles queries.

Best practices dictate updating statistics during off-peak hours to minimize impact.  Otherwise, take due precautions to minimize load on the system such as rebuilding statistics on only the tables that require it over a period of time.

Check books online for more information:

http://technet.microsoft.com/en-us/library/ms173804(v=sql.110).aspx

http://technet.microsoft.com/en-us/library/ms187348.aspx


Combine column from multiple rows into one row in SQLITE I have a sqlite-database with the schema

Articles
 Id
 Name

Tags
 Id
 Name

ArticlesTags
 Id
 ArticleId
 TagId

I´m trying to query for Articles.Name and a LIST of tags. Basically what I would want is:

Name1, (tag1,tag2,tag3)
Name2, (tag3,tag2)

Is this possible?
I´m lacking the language to explain what I actually want to do, what is it called?

I found Combine column from multiple rows into single row but it seems limited to SQLServer and the usage of builtin xml-features of that.

You can use the GROUP_CONCAT() function:

SELECT a.Name               AS ArticleName
     , GROUP_CONCAT(t.Name) AS TagList
FROM Articles AS a
  LEFT JOIN ArticlesTags AS at
    ON at.ArticleId = a.Id
  LEFT JOIN Tags AS t
    ON t.Id = at.TagId
GROUP BY a.Id, a.Name ;


Filter records that have a certain status I am using mysql and I have two tables, product and revision.

Joining them gives me the following table:

SELECT *
FROM product AS p
INNER JOIN revisions AS r ON p.revision_id = r.id
ORDER BY p.id;

| id | name     | prices | revision_id | id | revision_status |
|----|----------|--------|-------------|----|-----------------|
| 1  | Produkt1 | 10     | 1           | 1  | 1               |
| 2  | Produkt1 | 4      | 2           | 2  | 0               |
| 3  | Produkt1 | 2      | 3           | 3  | null            |
| 4  | Product2 | 42     | 4           | 4  | 1               |
| 5  | Produkt2 | 43     | 5           | 5  | null            |
| 6  | Produkt2 | 78     | 6           | 6  | 0               |
| 7  | Produkt3 | 1      | 7           | 7  | 1               |
| 8  | Produkt3 | 3      | 8           | 8  | null            |
| 9  | Produkt3 | 4      | 9           | 9  | null            |
| 10 | Produkt4 | 33     | 10          | 10 | 1               |

A Product has a unique ID that identifies a product. Due to price changes each product can have several different prices. Each product has a revision id.

The revision table has a revision_status and can have three different values: 1 for approved price, 0 for closed revision and null for to be revised.

As a final result I would like to get all records that have revision_status = 1 and also a revision_status = null.

Final result

| id | name     | prices | revision_id | id | revision_status |
|----|----------|--------|-------------|----|-----------------|
| 1  | Produkt1 | 10     | 1           | 1  | 1               |
| 3  | Produkt1 | 2      | 3           | 3  | null            |
| 4  | Product2 | 42     | 4           | 4  | 1               |
| 5  | Produkt2 | 43     | 5           | 5  | null            |
| 7  | Produkt3 | 1      | 7           | 7  | 1               |
| 8  | Produkt3 | 3      | 8           | 8  | null            |
| 9  | Produkt3 | 4      | 9           | 9  | null            |

I tried the following:

SELECT *
FROM product AS p
INNER JOIN revisions AS r ON p.revision_id = r.id
WHERE p.name IN (
  SELECT p.name
  FROM product AS p
  INNER JOIN revisions AS r ON p.revision_id = r.id
  GROUP BY p.name
  HAVING COUNT(r.revision_status) > 1)
ORDER BY p.id;

However, this also gives me revisions back that have a revision_status = 0

I created this sqlfiddle as a minimum viable example.

1 Product can have 1 Revision. Initially a product has revision_status of null. After it gets checked the status is set either to 1 - approved -  or to 0 - not approved. In the application I have a logic, that only 1 product - with the same name -  can have revision_status 1. I am looking for a way to show all revisions that have revision_status 1 and null and display the 1ns always on top of the nulls. (Like shown in the result table).

Try the following query (for starters):

select
  P.id
, P.name
, P.prices
, R.revision_status
, R.id
from Product P
  join Revisions R on P.revision_id = R.id
where R.revision_status <> '0'
  and (
    select count(*)
    from Product
      join Revisions on Revisions.id= Product.revision_id
    group by Product.name
    having Product.name = P.name
  ) > 1
order by P.id, R.revision_status ;

--
-- result
-- "name" of the Product with the ID 4 changed to: "Produkt2"
-- before executing the query
--
+------+----------+--------+-----------------+------+
| id   | name     | prices | revision_status | id   |
+------+----------+--------+-----------------+------+
| 1    | Produkt1 | 10     | 1               | 1    |
| 3    | Produkt1 | 2      | null            | 3    |
| 4    | Produkt2 | 42     | 1               | 4    |
| 5    | Produkt2 | 43     | null            | 5    |
| 7    | Produkt3 | 1      | 1               | 7    |
| 8    | Produkt3 | 3      | null            | 8    |
| 9    | Produkt3 | 4      | null            | 9    |
+------+----------+--------+-----------------+------+
7 rows in set (0.01 sec)

-- MySQL 5.7

If this gives you the required result, maybe we should cover the (revision_status) cases '1' and 'null' explicitly, by refactoring the WHERE clause as follows:

select ... from ... -- same as above
where R.revision_status <> '0'
  and (
    select count(*)
    from Product
      join Revisions on Revisions.id= Product.revision_id
    group by Product.name, Revisions.revision_status
    having Product.name = P.name and Revisions.revision_status = '1'
  ) >= 1
  and (
    select count(*)
    from Product
      join Revisions on Revisions.id= Product.revision_id
    group by Product.name, Revisions.revision_status
    having Product.name = P.name and Revisions.revision_status = 'null'
  ) >= 1
order by P.id, R.revision_status ;

-- ... = 'null' due to your test data, maybe this needs IS NULL in reality.


Attaching a MDF with a missing LDF Possible Duplicate:
  How do I attach a database in SQL Server?

I was given an MDF for a database, no backups and not LDF that I was asked to attach the database to a SQL server. I get the following error:

File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER2K8\MSSQL\DATA\something.LDF" may be incorrect.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

I tried Paul Randall's method of creating a database of the same size, putting it in emergency, single user mode and attempting to set it online that way. Is there any other option or do I tell the person that gave me this file that the data is corrupt?

When a database is detached, it should shut down cleanly. This file has been shadow copied or has been taken from a crashed SQL Server Instance or such. That is, the MDF is not from a cleanly detached, shutdown database.

So, possible solutions..

I don't think this will work thought but please check. Try SP_ATTACH_SINGLE_FILE_DB from a query window. It is clearer than the SSMS GUI attach pop-up. Note, this is deprecated in favour of CREATE DATABASE .. FOR ATTACH_REBUILD_LOG which states as a requirement: A clean shutdown of the database.
But I'm sure that you'll have to go back and state you have a corrupted file.
Better still, ask for a BACKUP which will be smaller and consistent.
If your source says they can attach it, they'll be lying or losing data.
Looking at SO and SF, there appears to one approach that will cause data loss. You can find that for yourself: I don't endorse it.


Does MySQL or MariaDB have a master option to strictly require TLS for ALL connections, no matter what? When setting up new MySQL or MariaDB servers, I am much more concerned about security than performance.  The MySQL and MariaDB documentation use the words "allow" and "permit" around all the SSL options, where I would much rather see "require".

I am looking for something like, "mysqld --ssl-mode=REQUIRE" but that apparently doesn't work [anymore?] because the logs say that's a bad option.  I am not looking to do this per-user, I'm looking to do it for the whole system, no matter what.

I should point out that all of my certificates are working fine, and I've verified that all of that works.  It's just that now, I would like to REQUIRE it be used, always, by everyone, period, strictly, no exceptions, no, not even that one, yes really.  Remote?  Encrypt.  Localhost?  Encrypt. Root?  Encrypt.  Somebody else?  Encrypt.  In short: connected to anything at all for any reason?  Encrypt.

Does anybody know of a master switch to require this all the time, period?

Again, I am not looking for per-user options-- that's exactly what I'm hoping to avoid.  This is for an embedded device that may be in service for an extended period of time, and the less branching logic, the better.  "Always" tends to have fewer edge cases.

Thanks!

As of MySQL 5.7, it does not seem to be possible to force SSL from the server side, without specifying it for each user.

https://dev.mysql.com/doc/refman/5.7/en/encrypted-connection-options.html#option_general_ssl-mode

  --ssl-mode=mode

  This option is available only for client programs, not the server. It specifies the security state of the connection to the server. These option values are permitted:

BOO.  You can only force SSL from the client side.  Opposite of what you want to do.  This is likely why you're getting "bad option" trying to use it as a server setting.

Also:

  To require use of encrypted connections by a MySQL account, use CREATE USER to create the account with a REQUIRE SSL clause, or use ALTER USER for an existing account to add a REQUIRE SSL clause. Connection attempts by clients that use the account will be rejected unless MySQL supports encrypted connections and an encrypted connection can be established.

Enforcing users to Require SSL by routinely checking the users table for REQUIRE SSL seems hacky, but it might be your best option.


Upgrade from Postgres 9.3 to 9.5 I’m attempting my first upgrade of Postgres from 9.3 to 9.5. I’ve run into two roadblocks but not sure how to get around them. I have my 9.3 PGDATA set to /data/postgres, which is a separate filesystem on my RHEL 6 server. After installing 9.5 and running initdb, I attempted to run pg_upgrade using this command line:

/usr/pgsql-9.5/bin/pg_upgrade -b /usr/pgsql-9.3/bin -B /usr/pgsql-9.5/bin
  -d /data/postgres -D /var/lib/pgsql/9.5/data --link -p 5432 -P 5433 -c

This command failed the link parameter test, because the 9.5 PGDATA value is not on the same filesystem as 9.3. Roadblock #1.

I then tried to re-initialize 9.5 using this command:

initdb -D /data/postgres95

It errored saying "Data directory is not empty!". Roadblock #2.

How do I proceed with this upgrade? I’d rather not move to a new server.

Self-answer from Kevin Struckhoff originally left as a comment:

Finally got back to this task and ran the upgrade successfully.

Turns out, since I use tablespaces in specific directories, the new 9.5 are right next to the old 9.3 dirs:

drwx------ 4 postgres postgres  4096 Oct  4  2014 PG_9.3_201306121
drwx------ 3 postgres postgres  4096 Apr 18 11:49 PG_9.5_201510051


Hide table structure to users in firebird I have been searching for a way to hide tables structures for some user but without success. I need that some users can only browse two views, but they don't be able to browse tables structures.
Is there any way?

You should not need to hide things from Users.

You should only grant them access to the things they actually need to work with.


Remove duplicate rows and add count I'm trying add a count column to my table. I want to remove duplicate rows and add an additional column with the count of rows removed.

My Table looks like this:

start             institution_code  year
Indianapolis, IN    IUBLA           2004
Indianapolis, IN    IUBLA           2004
Indianapolis, IN    IUINA           2004
Bloomington,  IN    IUINA           2002
Bloomington,  IN    IUINA           2002

And I'd like the final version to look like this:

start             institution_code  year    Count
Indianapolis, IN    IUBLA           2004    2
Indianapolis, IN    IUINA           2004    1
Bloomington,  IN    IUINA           2002    2

I thought come combination of Count and Group By or even Over but I haven't been able to come up with a working example.

                                              version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 10.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-23), 64-bit

It should be a pretty straight forward group by and count:

select start, institution_code  , year, count(*) as Count
from yourtable
group by start, institution_code  , year;


The materialized view is already optimal and cannot be tuned any further I have tried DBMS_ADVISOR.TUNE_MVIEW to verify some materialized views created locally. One of the result returns an error:

Error report:
ORA-13600: error encountered in Advisor
QSM-03116: The materialized view is already optimal and cannot be tuned any further
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.PRVT_ACCESS_ADVISOR", line 205
ORA-06512: at "SYS.PRVT_TUNE_MVIEW", line 951
ORA-06512: at "SYS.DBMS_ADVISOR", line 739
ORA-06512: at line 4

Does this mean the materialized view script is in the best shape, or DBMS_ADVISOR.TUNE_MVIEW is not capable to find a better solution?

If a materialized view is already fast refreshable, the DBMS_ADVISOR.TUNE_MVIEW procedure will return a message saying "the materialized view is already optimal and cannot be further tuned"

So there is nothing to do.


Speeding up GROUPING SETS queries I have a query that fetches a number of aggregations over a table. It's very similar to the following adapted example from the Postgres documentation, just with more entries in the grouping sets clause:

SELECT
    brand,
    size,
    count(*)
FROM items_sold
GROUP BY GROUPING SETS (
    brand,
    size,
    ()
);

If there is a WHERE clause that restricts this query to a small subset of the table, it is pretty obvious how to speed that up with an index. But what options do I have for cases where I run this kind of query against the whole table, or a large enough fraction that an index on the WHERE clause condition isn't useful?

Now, I personally can't really think of a way of speeding this kind of query up, in the end it has to scan the entire table to perform these aggregations. But just because I can't think of any way doesn't mean there isn't one. From what I read, dedicated search engines like Elasticsearch or Solr can perform similar aggregations over very large amounts of data, and I assume they're reasonably fast at this.

What options do I have to speed up this kind of query that uses a GROUP BY GROUPING SET over an entire table?

Now, I personally can't really think of a way of speeding this kind of query up, in the end it has to scan the entire table to perform these aggregations. But just because I can't think of any way doesn't mean there isn't one.

That's basically right, and isn't unique to GROUPING SETS. If you are scanning a lot of blocks then IO will likely dominate execution time so you have only two strategies:

Make IO faster
Reduce IO

Regarding faster IO, you need to look at hardware or configuration changes. Parallel query was mentioned in the comments, but this could be faster or slower and isn't a magic bullet for IO.

Regarding reducing IO, note that your query does not necessarily need to look at the table at all — it could use an appropriate covering index instead:

  Covering indexes are indexes creating for the express purpose of being used in index-only scans. They typically "cover" more columns than would otherwise make sense for an index, typically columns that are known to be part of particular expensive, frequently executed query's selectlist.

So an index including brand and size (in either order) is all that is needed.

test data:

create table items_sold(brand integer, size integer, other text);

insert into items_sold(brand, size, other)
select g%5, random() * 5 + 1, lpad('',1000)
from generate_series(1,100000) g;

vacuum analyze; -- you wouldn't normally run this manually

without covering index:

explain (analyze,buffers)
select brand, size, count(*)
from items_sold
group by grouping sets (brand,size,());

/*
|QUERY PLAN                                                                                |
|:----------------------------------------------------------------------------------------------------------------------------|
|GroupAggregate  (cost=23590.82..33395.88 rows=24 width=16) (actual time=117.246..206.585 rows=12 loops=1)                    |
|  Group Key: brand                                                                                |
|  Group Key: ()                                                                                |
|  Sort Key: size                                                                                |
|    Group Key: size                                                                                |
|  Buffers: shared hit=885 read=13401, temp read=441 written=441                                                              |
|  ->  Sort  (cost=23590.82..23840.82 rows=100000 width=8) (actual time=108.069..129.823 rows=100000 loops=1)                 |
|        Sort Key: brand                                                                                |
|        Sort Method: external merge  Disk: 1752kB                                                                            |
|        Buffers: shared hit=885 read=13401, temp read=220 written=220                                                        |
|        ->  Seq Scan on items_sold  (cost=0.00..15286.00 rows=100000 width=8) (actual time=0.010..49.793 rows=100000 loops=1)|
|              Buffers: shared hit=885 read=13401                                                                             |
|Planning time: 0.034 ms                                                                                |
|Execution time: 207.241 ms                                                                                |
*/

with covering index:

create index on items_sold(brand,size);

explain (analyze,buffers)
select brand, size, count(*)
from items_sold
group by grouping sets (brand,size,());

/*
|QUERY PLAN                                                                                |
|:------------------------------------------------------------------------------------------------------------------------------------------------------------|
|GroupAggregate  (cost=0.29..12159.35 rows=24 width=16) (actual time=10.478..95.557 rows=12 loops=1)                                                          |
|  Group Key: brand                                                                                |
|  Group Key: ()                                                                                |
|  Sort Key: size                                                                                |
|    Group Key: size                                                                                |
|  Buffers: shared hit=1 read=275, temp read=221 written=221                                                                                |
|  ->  Index Only Scan using items_sold_brand_size_idx on items_sold  (cost=0.29..2604.29 rows=100000 width=8) (actual time=0.023..21.911 rows=100000 loops=1)|
|        Heap Fetches: 0                                                                                |
|        Buffers: shared hit=1 read=275                                                                                |
|Planning time: 0.108 ms                                                                                |
|Execution time: 95.953 ms                                                                                |
*/

dbfiddle here

Note the "Buffers" data — the covering index does much less IO here.


Postgresql Date Function Returning Range In Readable Format I am wondering if it is possible to use postgresql's date-time , concat and case functions to give one of the following results:

June 4th to 11th 2019 (where the 1st and 2nd date are within the same month)
July 27th to July 3rd 2019 (where the 1st and 2nd date are in different calendar months but the same calendar year)
December 29th 2019 to January 4th 2020 (where the 1st and 2nd date are in different calendar months and different years)

I am setting out to create a VIEW that will be used for posting status updates (such as to Twitter) when the weekly tip is changed.  In my programming I endeavor to use the databases native functions to the most possible.  In an ideal world I wouldn't use source code to accomplish this provided postgresql is able to do so itself.

The SELECT query for VIEW that I've created so far is:

SELECT 'chronic pain'::text AS section,
    'https://rons-home.net/en/living-life-lab/tips/living-with-chronic-pain'::text AS link,
    tips_chronic_pain.reference,
    tips_chronic_pain.tip,
    tips_chronic_pain_weekly_selection.start_date,
    tips_chronic_pain_weekly_selection.end_date
FROM tips_chronic_pain
JOIN tips_chronic_pain_weekly_selection ON tips_chronic_pain.reference = tips_chronic_pain_weekly_selection.tips_chronic_pain_reference
ORDER BY tips_chronic_pain_weekly_selection.start_date DESC
LIMIT 1

I have created the following 2 tables:

Table #1 contains all the tips:

CREATE TABLE public.tips_chronic_pain
(
  reference bigint NOT NULL DEFAULT nextval('tips_chronic_pain_reference_seq'::regclass),
  tip text,
  add_date timestamp with time zone DEFAULT now(), -- UTC
  membership_reference bigint,
  CONSTRAINT tips_chronic_pain_pkey PRIMARY KEY (reference)
)

Table #2 determines which tip is currently being displayed:

CREATE TABLE public.tips_chronic_pain_weekly_selection
(
  reference bigint NOT NULL DEFAULT nextval('tips_chronic_pain_weekly_selection_reference_seq'::regclass),
  tips_chronic_pain_reference bigint,
  start_date timestamp with time zone DEFAULT now(), -- UTC
  end_date timestamp with time zone DEFAULT now(), -- UTC
  CONSTRAINT tips_chronic_pain_weekly_selection_pkey PRIMARY KEY (reference)
)

Column tips_chronic_pain_weekly_selection.start_date provides the first date
Column tips_chronic_pain_weekly_selection.end_date provides the second date

CREATE OR REPLACE FUNCTION readable_date_range(DATERANGE)
RETURNS TEXT AS $$

  SELECT CASE WHEN to_char(LOWER($1), 'YYYY-MM') = to_char(UPPER($1), 'YYYY-MM')
              THEN to_char(LOWER($1), 'FMMonth FMDDth to ') || to_char(UPPER($1), 'FMDDth YYYY')
              WHEN EXTRACT(year FROM LOWER($1)) = EXTRACT(year FROM UPPER($1))
              THEN to_char(LOWER($1), 'FMMonth FMDDth to ') || to_char(UPPER($1), 'FMMonth FMDDth YYYY')
              ELSE to_char(LOWER($1), 'FMMonth FMDDth YYYY to ') || to_char(UPPER($1), 'FMMonth FMDDth YYYY')
              END;

$$ LANGUAGE SQL STRICT IMMUTABLE;

Let's look at some output:

[[local]:5432] user =# SELECT readable_date_range('[2019-01-01,2019-05-03)');
     readable_date_range
─────────────────────────────
 January 1st to May 3rd 2019
(1 row)

Time: 63.280 ms
[[local]:5432] user =# SELECT readable_date_range('[2019-01-01,2019-01-03)');
   readable_date_range
─────────────────────────
 January 1st to 3rd 2019
(1 row)

Time: 0.668 ms
[[local]:5432] user =# SELECT readable_date_range('[2019-01-01,2022-01-03)');
         readable_date_range
──────────────────────────────────────
 January 1st 2019 to January 3rd 2022
(1 row)

Time: 4.563 ms

You could quite easily modify that to take two values instead.


Why do I get this error "Statement failed, SQLSTATE = 08001"? When ever I try to connect with Firebird SQL I get this error,

firebird$ isql-fb 'db.FDB' -u SYSDBA -p <PASS>

Statement failed, SQLSTATE = 08001
I/O error during "open" operation for file "CAFW.FDB"
-Error while trying to open file
-Permission denied
Use CONNECT or CREATE DATABASE to specify a database

What can I do to resolve it, I've already ensured that my current user has read/write access to the file.

$ sudo chown firebird:firebird ./db.FDB
$ sudo chmod 770 ./db.FDB # should only need 660

$ ls -lah
-rw-rw---- 1 firebird firebird 53M May  2 17:13 CAFW.FDB

You need to add yourself in the Firebird group

sudo adduser `id -un` firebird

or always use localhost address in front of db if Server is Classic or SuperClassic

See the long thread here
http://firebird.1100200.n4.nabble.com/Fwd-Have-you-tried-firebird-2-5-SuperClassic-from-the-packages-td3053790.html


MySQL database design I could do with some help designing a database for a website I'm working on. I should mention that I am a beginner and I have never had to design a database in the past.

Ok, my implementation needs to hold data on auctions based on 5 world regions (EU, US, KR, CN, TW). Each auction is identified with an ID that is not unique to a region but is unique to a 'realm' in that region. The final requirements are:

Able to track historical market prices of individual items up for auction over the past 4-6 months.
Able to view the current items up for auction.
Able to track sellers of items and their history.
Able to create a profile on the site.
It is not required to be able to compare regions.

Some stats:

Each region can be made up of 240+ realms.
Each realm has around 20,000 auctions up for sale at any one time.

My current plan is to work with a database for each region so 5 in total. Each one of those databases would have the following tables:

Realms - Holds information about all realms in a region
Scan - Holds a snap shot of the current items up for sale in a region. Indexed by realm.
Historical - Holds 4-6 months of history for each region. This will include every auction that was seen.
Sellers - Holds data on all sellers seen in the last 4-6 months.

I am debating whether it would be better to have just two tables and merging the Scan, Historical and Sellers tables together, then if a user wanted to view just the current scan data or information for a seller it would be calculated from the combined table on the fly.

With me being new at this, I could do with someone with more experience to take a look and let me know if I'm overlooking anything. Keep in mind that I don't know anything about performance and whether the design I have would be acceptable or not.

Anyone interested can see the documentation for the API I am working with here http://blizzard.github.com/api-wow-docs/ I'll have scheduled tasks to import the data and insert it into the database.

Feel free to ask me anything if I wasn't clear enough.

I'll take a crack at this.  Note, that I've just listed fields that are necessary.  If you have additional data you want to store for in those tables, feel free to add them (so long as they are normalized).

While you did state that you would use a different database for each region, I designed this so that they could all work in one database.  Right now you may have only 5 regions, but this offers the flexibility to easily add more:

table: region
regionID        varchar(2)
regionName      varchar(50)

Defines data for each realm.  I went with a surrogate key (realmID) because it's possible that realmName might not be unique.  This assumes that each realm can only belong to one region (regionID needs to be a foreign key to regionID on the region table):

table: realm
realmID         int
regionID        varchar(2)
realmName       varchar(50)

Defines data for each user (seller/buyer).  I decided that it would be better if it had a surrogate key (sellerID) because seller/buyers first/lastname combinations may not be totally unique.  Note, you may also want an address table to store multiple:

table: user
userID          int
userFirstName   varchar(20)
userLastName    varchar(20)
userEmail       varchar(100)

Data for each auction.  AuctionID would be a auto-generated ID and the Primary Key.  You would also want foreign keys on realmID (realmID on realm table) and sellerID (userID on user table):

table: auction
auctionID       int
realmID         int
sellerID        int
auctionPrice    decimal
auctionStart    datetime

Data for each auction that has been completed with a foreign key on buyerID (userID on user table):

table: auctionHistory
auctionID       int
buyerID         int
finalPrice      decimal
auctionFinished datetime

I don't think that you'd need a seperate table for "Scan".  That would be a simple query of rows in the auction table that do not have matching rows in the auctionHistory table.  Hopefully, this will help you get started.  If I missed/misread anything, please let me know.


Transaction Log Backups on SQL Server Running SQL2008 R2 SP2, database is in FULL Mode

Is it Possible to shrink the transaction log without putting it into SIMPLE mode? Temporarily cannot take log backus and don't want it to grow until I can take the log backups.

I put it in Simple mode but was curious if could shrink in FULL?  Think it is; or at least was possible

thanks in advance

Thanks for comments as stated; it is in Simple.

My question is; Can a transaction log be shrunk while it is in FULL mode?

One can attempt to shrink a log file at any time, regardless of recovery model.

The amount it shrinks will only go back to just after the last in-use virtual log file (VLF). VLFs are logical regions of a physical log file. You can use the DBCC LOGINFO command to dump a list of all the VLFs for the database-in-context. In the Status column, 0 means unused, and 2 means in-use. A shrink operation will only reclaim contiguous VLFs at the end of the physical file which have status 0.

The difference between recovery models is the mechanism that maintains the status of each VLF. In FULL recovery, VLFs can't be overwritten at least until they've been backed up in a log backup.

Just a note that switching to SIMPLE and then back to FULL usually isn't a good idea, and should only be used as a very last resort in exceptional circumstances. Doing this will break the log chain (which can be restarted with a full or differential backup) and lose the ability to recover to a point-in-time within the log records that were dropped on the floor.