Extract all of the keys, including nested, from postgres JSONB column I have a table, my_table, in postgres with a column, d, that is of a jsonb type.  An example of the data in that column is:

{
"logo": "a",
"name": "b",
"status": "c",
"address": {
    "city": "d",
    "state": "e",
    "street1": "f",
    "street2": "",
    "postal_code": "g",
    "country_code": "h"
},
"industry": "i",
"logo_url": "j",
"followers": 2,
"twitter_id": "",
"description": "k",
"status_code": "l",
"website_url": "m",
"headquarters": {},
"employees_count": 5,
"employees_range": "1-10",
"current_jobs_posted": 0

}

and I would like to have the sql return:

logo
name
status
address
city
state
street1
street2
postal_code
country_code
industry
logo_url
followers
twitter_id
description
status_code
website_url
headquarters
employees_count
employees_range
current_jobs_posted

The current SQL

SELECT    jsonb_object_keys(data) as key_name
FROM      profiles
WHERE     jsonb_typeof(data) = 'object'
and       id = 5;

returns

logo
name
status
address
industry
logo_url
followers
twitter_id
description
status_code
website_url
headquarters
employees_count
employees_range
current_jobs_posted

leaving out the keys nested under address.  I need to have those broken out as well.  It can either return them as rows, or if necessary, it can be flattened and returned as columns.  I just need to be able to return all of the keys, including any nested keys.  There may be other keys nested in other entries in the table.

Thank you

You can use a recursive query for that:

with recursive all_keys (ky, nested) as (
  select t.ky,
         case jsonb_typeof(p.data -> t.ky)
           when 'object' then p.data -> t.ky
           else null
         end
  from profiles p
    cross join jsonb_object_keys(p.data) as t(ky)
  where id = 5
  union all
  select t.ky,
         case jsonb_typeof(ak.nested -> t.ky)
           when 'object' then ak.nested -> t.ky
           else null
         end
  from all_keys ak
    cross join jsonb_object_keys(ak.nested) as t(ky)
  where nested is not null
)
select ky
from all_keys;

Online example: https://rextester.com/YVNF32970


How to search in blob binary wise, and how to export data binary wise I've a table with a blob field for each row.
The blob contains binary data, which can be represented as bytes or text ( using mysql workbench - open value in viewer)

Does anybody know how can I search specific sequence of bytes in a given bytes chunk(blob field),
for example search for the sequence of bytes: 00 38 a7 in blob's binary representation
Is there any possibility to export all table values to csv with blob values in binary representation When I tried to export using mysql workbench I got for each blob ... as a value in csv.

One way...

SELECT ... FROM ... WHERE HEX(blob_col) REGEXP '(..)*0038A7';

The (..)* skips over any number of pairs of the hex digits.

For dumping, see mysqldump --hex-blob (not good for csv)

See SELECT ... INTO OUTFILE


What is the key to pg_restore? We have been working on a large-ish PostgreSQL database migration to a Google Cloud VM instance (Linux 14.04) for several weeks now, and are having issues with the final step: pg_restore.

We are trying to restore from a 17GB .backup file. We have tried several permutations of the pg_restore command, including:

No existing target database, include the --create flag.
Connect to existing target database, include --clean and --create flags (per these Stackexchange answers).
Specifying --host=localhost (per 68 here).

Several attempts resulted in the rows of the restore being written to the nohup.out file.

A few permutations we've tried:

nohup pg_restore --host=localhost --username=postgres --password --role=postgres --dbname=dbname_to_restore path/to/data.backup > nohup.out 2> err.out

pg_restore --username "postgres" --password --role "postgres" --create --clean "/path/to/data.backup"

When we nohup we are getting a few issues in our err.out file, such as

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 187; 1259 17762 TABLE ping arcgis
pg_restore: [archiver (db)] could not execute query: ERROR:  type "geography" does not exist
pg_restore: [archiver (db)] could not execute query: ERROR:  schema "arcgis" does not exist

What are we missing? What are the bare minimum flags etc. to load the data into the PostgreSQL database? Are there some environmental considerations we need to address? Why isn't the data being loaded into the db?

In cases like this and the size of the DB (>17 Gb) I suggest based on the official documentation for version 9.2 the following:

do a pgdump_all

pg_dumpall [connection-option...] [option...]

pg_dumpall > databackup.out

Restore with psql. I have found out a better execution and stability

psql -f databackup.out postgres

Take extreme precaution with the file encoding. Specially if you are doing migrations among different OS and specially if you are uploading to a cloud RDBMS provider.


MySQL Performance Planning I am considering moving my existing script which is downloaded to be a hosted script.  One of the things it does is to keep track of some stats.  My concern is with heavy hitters (100,000 pageviews/month on up), it might tax my mysql process.

I'm currently using a VPS, which would be just to start with.  My question is...

How do I best plan ahead for an easy/smooth transition to another mysql server if load gets too high?

Thanks for any advice, it's appreciated.

You need to find out first what makes the load get too high so that you are able to plan ahead.

If its IO bound then you need faster disks. If its memory bound then you need to add more memory or find ways to reduce its usage.

The best way to find out what the problem is, is to track your system during a peak time. Get a graphing application and look for:

Hard disk usage
CPU usage
Memory usage
Swap usage
Raid card, if you have one

Also keep track of your SHOW STATUS info and see what are the differences. Try to notice patterns, find the bottlenecks and predict scalability issues.

For example, if you system is currently doing 100,000 transactions per minute and your bottlenecks are at 75% capacity, then if your TPM increase it to 140,000 your system might crash/stall/lagg/die-a-horrible-death.


How can I enforce that values in a table column match the Regular Expression "[axyto0-9\s]{0,2}[\s0-9]{0,10}"? I have a table as below

CREATE TABLE dbo.DemoTable
(
Value VARCHAR(12)
)

I would like to constrain it to only contain rows where Value matches the following pattern

[axyto0-9\s]{0,2}[\s0-9]{0,10}

Start of string
A single character present in the list below between
zero and 2 times, as many times as possible

A single character from the list “axyto” (case sensitive)
A character in the range between “0” and “9”
A “whitespace character”

Match a single character present in the list below between zero and 10 times, as many times as possible

A “whitespace character”
A character in the range between “0” and “9”

End of string

How can I do this in SQL Server? (inspired by this question on SO)

String handling in TSQL is poor.

PATINDEX supports a limited set of wild cards but has no support for character classes such as \s to match white space or quantifiers. It isn't impossible to do the validation for this relatively simple regex with a native TSQL expression. The max length of 12 is enforced by the datatype and use of varchar means there are only 6 white space characters in my code page.

So the following approach should work

First 2 characters must match NOT LIKE CONCAT('%[^aotxy0123456789',char(9),char(10),char(11),char(12),char(13),char(32),char(160),']%')
From 3rd character on must match NOT LIKE CONCAT('%[^0123456789',char(9),char(10),char(11),char(12),char(13),char(32),char(160),']%')

This is already fairly messy (I'm not even certain that it is currently correct) and it becomes completely unviable for more complex patterns.

Regular Expression support is available by using CLR and is also called out as one of the possibilities enabled by the Java language extension.

  This extends the TSQL surface area to better handle use cases
  involving regular expressions, string handling, and NLP support.

Examples of using regular expressions with these technologies are already available on the Microsoft Site (CLR, Java)

This answer gives a solution for the cases where these are not an option (perhaps as disabled by policy or using Azure SQL database where these are not available).

This leverages XML Schema Regular Expressions.

Create an XML Schema with a pattern constraint containing the regex

CREATE XML SCHEMA COLLECTION dbo.PatternValidatorSchema
AS '<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified"
           elementFormDefault="qualified"
           xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="test">
        <xs:simpleType>
            <xs:restriction base="xs:string">
                <xs:pattern value="[axyto0-9\s]{0,2}[\s0-9]{0,10}"/>
            </xs:restriction>
        </xs:simpleType>
    </xs:element>
</xs:schema>';

Add a check constraint so inserts/updates are validated

ALTER TABLE dbo.DemoTable
 ADD CONSTRAINT CK_ValidateValueMatchesPattern
 CHECK (CAST(ISNULL('<test>' + REPLACE(REPLACE(REPLACE(Value, '&','&amp;'), '<','&lt;'), '>','&gt;') + '</test>','') AS XML(dbo.PatternValidatorSchema)) IS NOT NULL)

This approach is not very flexible for general validation as any failure will throw an error so it can't be used to determine good and bad rows in a set based way, but for the purposes of aborting the insert if a single bad value is found it works fine.

The check constraint isn't really checking anything worthwhile. It is there to cause the conversion to typed XML to be invoked as any failures will cause an error to be thrown.

Insert some rows

INSERT INTO dbo.DemoTable
VALUES      (''),
            ('ax1234567890'),
            ('to123456'),
            ('AX1234567890');

Error

  Msg 6926, Level 16, State 1, Line 37

  XML Validation: Invalid simple type value: 'AX1234567890'. Location: /*:test[1]

The final value attempted violated the expression as it is upper case so the insert was blocked. The offending value is helpfully shown in the error (though the rest of the message may cause confusion)

Performance

Unfortunately there is a potentially significant performance penalty to pay though. When inserting 10 million valid values into the table with a TABLOCK hint on my machine it took about 2.6 seconds with no check constraint.

Calling the below in a check constraint increased this to 12 seconds with the CLR function

using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.Text.RegularExpressions;

public partial class UserDefinedFunctions
{
    private static readonly Regex regexObj = new Regex(@"\A[axyto0-9\s]{0,2}[\s0-9]{0,10}\z", RegexOptions.Compiled);

    [SqlFunction(DataAccess = DataAccessKind.None,
                 IsDeterministic = true,
                 IsPrecise = true,
                 SystemDataAccess = SystemDataAccessKind.None)]
    public static SqlBoolean PatternValidator([SqlFacet(MaxSize = 12)]SqlString valueToCheck)
    {
        return new SqlBoolean(regexObj.IsMatch(valueToCheck.Value));
    }
}

With the below check constraint in place the native TSQL attempt took 17 seconds

ALTER TABLE [dbo].[DemoTable]  WITH CHECK ADD  CONSTRAINT [CK_ValidateValueMatchesPattern] CHECK  (
LEFT(Value,2)  COLLATE Latin1_General_100_BIN2 NOT LIKE CONCAT('%[^aotxy0123456789',char(9),char(10),char(11),char(12),char(13),char(32),char(160),']%')
      AND SUBSTRING(Value,3,10) COLLATE Latin1_General_100_BIN2 NOT LIKE CONCAT('%[^aotxy0123456789',char(9),char(10),char(11),char(12),char(13),char(32),char(160),']%')
)

Using the typed XML approach took 4 minutes.

This is a penalty in the order of 24 microseconds per row vs no constraint so it would need to be evaluated whether this is a price worth paying or not taking into account expected volume of inserts/updates.

Just by way of comparison after altering the check constraint to produce untyped XML it took 1 min 25 seconds to insert that quantity of rows so the typed XML does add some significant overhead on top of that.


PostgreSQL 11 error: column p.proisagg does not exist Using phpPgAdmin v5.6 and PostgreSQL v11.2 on CentOS v7, when I try to access the Functions tab within the public schema, I get the following error:

ERROR:  column p.proisagg does not exist
LINE 18:    WHERE NOT p.proisagg
                      ^
HINT:  Perhaps you meant to reference the column "p.prolang".
Dans l'instruction :

            SELECT
                p.oid AS prooid,
                p.proname,
                p.proretset,
                pg_catalog.format_type(p.prorettype, NULL) AS proresult,
                pg_catalog.oidvectortypes(p.proargtypes) AS proarguments,
                pl.lanname AS prolanguage,
                pg_catalog.obj_description(p.oid, 'pg_proc') AS procomment,
                p.proname || ' (' || pg_catalog.oidvectortypes(p.proargtypes) || ')' AS proproto,
                CASE WHEN p.proretset THEN 'setof ' ELSE '' END || pg_catalog.format_type(p.prorettype, NULL) AS proreturns,
                u.usename AS proowner
            FROM pg_catalog.pg_proc p
                INNER JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
                INNER JOIN pg_catalog.pg_language pl ON pl.oid = p.prolang
                LEFT JOIN pg_catalog.pg_user u ON u.usesysid = p.proowner
            WHERE NOT p.proisagg
                AND n.nspname = 'public'
            ORDER BY p.proname, proresult

Fatal error: Call to a member function recordCount() on integer in /mnt/webdata/websites/applications/pga/classes/Misc.php on line 1949

I get the same error using the \df meta-command in psql (version 10.1 as it turns out):

ts_d=> \df
ERROR:  column p.proisagg does not exist
LIGNE 6 :   WHEN p.proisagg THEN 'agg'
                 ^
ASTUCE : Perhaps you meant to reference the column "p.prolang".
ts_d=>

The error in those queries seems to be due to a reference to proisagg, a column originally from the pg_proc table in the pg_catalog schema, but which no longer exists in PostgreSQL 11.
See: https://www.postgresql.org/docs/11/release-11.html

Any way to resolve this?

In Postgres 11 proisagg was replaced with prokind in the system catalog pg_proc:

  prokind | char | f for a normal function, p for a procedure, a for an aggregate function, or w for a window function

The query needs to be adapted. Like:

SELECT ...
FROM pg_catalog.pg_proc p
 ...
WHERE p.prokind = 'f'   -- to only get plain functions
...

Related:

How to drop all of my functions in PostgreSQL?
PostgreSQL: How to list all stored functions that access specific table

The error you observed from the \df meta-command in psql is most likely due to using an outdated version of psql. psql 11 and up are updated to deal with  this change, of course.


Service Master Key inaccessible, SSRS key only one backed up I’m working with a SQL Server 2012 instance that had a service master key, other secondary keys, and a SSRS key. The instance was uninstalled and reinstalled with new service accounts, and the master database was restored. No encryption keys were backed up.   The original master key, now inaccessible, was left as is and the SSRS key was regenerated and backed up.

Now we need to open or regenerate the master key for SSIS use.  If the SQL engine service is reset to the original login account, can the master key be opened?  If so, via backup with a newly created password?  If not, and the master key is regenerated, can the SSRS key be restored using its backup?

My google-fu is failing me and I want to accomplish this with the least amount of loss possible.  Any pointers or assistance is greatly appreciated.

For anyone who comes across this and has a similar issue...

I was able to successfully change the SQL Server Service login to the original account used when the service master key was created, then successfully back up the key.  Therefore I did not need to regenerate the service master key and restore the SSRS key.  I still don't have an answer - if the saved SSRS key could have been restored following the service master key regeneration.

If anyone learns the answer to that - when you have backups of secondary keys, but not the service master key - will you have to regenerate the secondary keys also, or be able to use your backups.... please post the answer here for future reference.


linked server operation I am trying to insert rows from server1.table1 to server2.table2. I already create a linked server in order to query tables in both servers and it works.

However because server2.table2 has an identity column,and though I put the hint set identity_insert table2 on,
  the insert operation failed and I got the following errors in french:

L'objet "192.168.0.36.wt_delivery.dbo.table2" est introuvable, car il n'existe pas ou vous ne disposez pas des autorisations requises.

which mean that the object 192.168.0.36.wt_delivery.dbo.table2 is not found or we don't have the required permission to do the operation.

when I create the same table without the identity column the insert operation succeed!!

The query was executed from server1

Is there a specific permission that allow the operation to succeed or the problem is not at permission level?

Thank you

Microsoft documentation indicates that you can only use a 3-part naming convention for the SET IDENTITY_INSERT command.

  SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON | OFF }

Somebody else had this same issue, and provided a solution using a temp table, and remote stored procedure to do the work.


What is the impact of MAX_DISPATCH_LATENCY while configuring EXTENDED EVENT? By default, MAX_DISPATCH_LATENCY is set to 30 seconds. I chose FILE as a target and write all captures there. I thought that if I write all captures into file, why would then I lower down MAX_DISPATCH_LATENCY to 1 second and by this way prevent captures from staying in the memory for the max duration of 30 seconds. What is the impact of lowering down MAX_DISPATCH_LATENCY?

From my research it doesn't sound like anything terrible can really happen from adjusting that property, but it also generally doesn't need to really be adjusted because 30 seconds is typically a good value for an Extended Events session.
You can read more about the property here but basically its purpose is to set a threshold for the max amount of time the captured events stay in the buffer in memory. Often times the captured events are written to their destination before hitting that threshold, so it's more of a catch all limit. Aside from that threshold, the buffer also gets flushed to the destination once the buffer is full.
Ultimately the reasoning behind having the buffer work this way rather than instantly writing to the destination every time is because I/O is less performant to write to then memory.  Constant writes from the buffer to disk (e.g. a file in your case) could be bottlenecked by and/or cause bottlenecks to the disk. Doing batch writes to disk is most performant when there's a lot of data, and when there's not a lot of data then the MAX_DISPATCH_LATENCY threshold will handle writing your events to the file.


ERROR 1297: Got temporary error 4010 'Node failure caused abort of transaction' from NDBCLUSTER when importing data I have the cluster built by one MGM Node and two data/sql nodes

When importing data to an NDB node the importation crashes returning the following:

ndb_2# mysql -u root -p db < data.sql
Enter password:
ERROR 1297 (HY000) at line 214: Got temporary error 4010 'Node failure caused abort of transaction' from NDBCLUSTER

Monitoring the other NDB node, I could see the data is being well inserted, but at 'n' records, the first node return the ERROR.

The ndb_2_out.log & ndb_3_out.log write:

WARNING  -- ACK wo/ gcp record (gci: 60138/0) ref: 0fa20004 from: 0fa20004

The ndb_2_error.log & ndb_3_error.log don't write anything.

Before the crash, both nodes have the same records.

I google it but I can't find anything related.

By reading some forums, the variables that avoids this issue are:

MaxBufferedEpochs
TimeBetweenEpochsTimeout

This must be configured in MGMT Node

/var/lib/mysql-cluster/config.ini

As we can see in MySQL Documentation, the default for MaxBufferedEpochs is 100, the default for TimeBetweenEpochsTimeout is 4000, change this for the needed value.


PostgreSQL multiple range best practice I'm using tsrange in my project, but I don't know how to represent something.

I have a product that can be displayed on my website at some times, and can be hidden at other times. But tsrange has only 2 bounds (lower, upper). How can I represent an suite of ranges which can be indexed (by gist) and selected with the same operators of tsrange? (like this: [(,"2019-01-01"],["2019-02-15","2019-03-16"],["2019-10-11","2019-12-20"]])

I tested tsrange[] but I can't index this and I can't use tsrange operators. I found that I can create them.

For this solution, I don't want a table product_range with two column's.

What is the best practice?

EDIT

This is an example what I want :

CREATE TEMPORARY TABLE product (
  id BIGSERIAL,
  name VARCHAR(15),
  selling_dates TSRANGE[]
);
INSERT INTO product (name, selling_dates) VALUES ('Puppet', ARRAY[tsrange(NULL, '2019-01-01'), tsrange('2019-05-03', '2019-08-04')]);
CREATE INDEX product_selling_dates ON product USING GIST (selling_dates);

SELECT * FROM product WHERE selling_dates <@ '2019-06-10'::TIMESTAMP

The best practice - and I'm afraid, the only approach that will make an indexed lookup possible - is the two-table approach; there is no index type that will help with a "timestamp falls within any item in a tsrange[] array" clause. The tsrange data must be in it's own column to benefit from a GIST index, and the index types that apply to arrays can only support exact-match for their "item-is-in-array" operators.


Is IDENTITY continuous in new versions of PostgreSQL? Now I am use serial to generate table primary key in PostgreSQL 13, but today I found the ID jumps and has big gaps in the ID space. It will waste many IDs. I read the docs and found that PostgreSQL has identity as a SQL standard generated primary key. I found a recommendation to use identity in new version of PostgreSQL but I don't know whether ID is continuous.
What should I do get a primary key id without gaps in PostgreSQL 13?

Solution 1: I tried to change serial to identity like this:
BEGIN;
ALTER TABLE public.article ALTER id DROP DEFAULT; -- drop default

DROP SEQUENCE public.article_id_seq;              -- drop owned sequence

ALTER TABLE public.article
-- ALTER clientid SET DATA TYPE int,              -- not needed: already int
   ALTER id ADD GENERATED ALWAYS AS IDENTITY (RESTART 2270886);
COMMIT;

Still gaps.
Solution 2: I tried to add a redis distribution lock about my apps insert article like this:
def save_single(guid, pub_time, title, author, content, source, link):
    if content is not None and len(content) > 0:
        article = Article()
        article_content = ArticleContent()
        beta_parser = CnbetaParser()
        second_parsed_content = beta_parser.parse_cnbeta_content(content, link)
        article_content.article_content = second_parsed_content

        cover_image = None
        lock_identifier = None
        try:

            # cover_image = Utils.get_cover_image_url(self, content, link)
            lock_identifier = acquire_lock("article-save", 5, 5)
            article.save(title, guid, author, pub_time, article_content, link, source, cover_image)
        except Exception as e:
            logger.error("save article data error,rss:" + source.sub_url, e)
        finally:
            if lock_identifier is not None:
                release_lock("article-save", lock_identifier)
    else:
        logger.error("article content is null,title:" + title)

Still gaps.

The term is IDENTITY, not IDENTIFY. (I fixed it in your question.)
IDENTITY columns (GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY) are based on a SEQUENCE just like the older serial columns. You have to expect gaps in the serial numbers with either, that's in the their nature, and nothing to worry about.
Differences are addressed in my answer on stackexchange (where you obviously found the code to convert your serial to an IDENTITY column):

How to change a table ID from serial to identity?

But nowhere did I suggest that the conversion would remove gaps in the ID space. Or that this would be desirable. If you positively think you need that, consider:

Compacting a sequence in PostgreSQL

It starts with a similar disclaimer like you already received in the comments (and some alternatives):

First off, gaps in a sequence are to be expected. Ask yourself if you really need to remove them. [...]


Avoid circular reference with configuration tables I've got a few tables that define configuration information.  This defines what combination are allowed in the system.

However, I've got a circular reference on ProjectName which can cause potential invalid relationships.  I'm wondering how I can enforce this better in the database.  I don't want to trust the code to enforce it.

Table Schema (Table / Columns)

Project
    ProjectName

Category
    CategoryName

ProjectCategory
    ProjectCategoryID
    ProjectName
    CategoryName

Inventory
    ProjectName
    ProjectCategoryID

Populated Tables (Table/Column/Data)

Project
    ProjectName
        A
        B

Category
    CategoryName
        X
        Y

ProjectCategory
    ProjectCategoryID, ProjectName, ProjectCategory
        1, A, X
        2, A, Y
        3, B, Y

Inventory
    ProjectName, ProjectCategoryID
        A, 1
        A, 3

Inventory record (A, 3) is invalid.  The inventory belongs to project 'A' yet the configuration belongs to project 'B'.  Because of the circular reference, its possible to associate a record incorrectly.

You can solve this problem by either removing ProjectName from Inventory (since it is redundant because it is already specified in ProjectCategory), or enforce the relationship via a foreign key:

CREATE UNIQUE INDEX ON ProjectCategory (ProjectCategoryID, ProjectName);

ALTER TABLE Inventory ADD
  FOREIGN KEY (ProjectCategoryID, ProjectName) REFERENCES (ProjectCategoryID, ProjectName);


Counting repeated occurences for a group of fields I am trying to count the continuous repeated value for a field in order to obtain the maximum repeated occurrences for each person in the table. eg:

Time         person        Result
08:12:36     Dave          Heads
08:12:37     Dave          Heads
08:12:39     Sue           Tails
08:12 41     Dave          Tails
08:12:42     Dave          Heads
08:12:44     Dave          Heads
08:12:45     Sue           Heads
08:12:46     Sue           Heads
08:12:47     Sue           Tails
08:12:48     Dave          Heads
08:12:49     Dave          Heads
08:12:50     Dave          Tails
08:12:51     John          Heads

Result should look like this

Dave     4
Sue      2
John     1

The count for Dave would be 4 as between 08:12:42 and 08:12:49, 4 Heads options occur for Dave before Dave gets a Tails option.

SELECT person, max(ct) AS max_ct
FROM  (
   SELECT person, count(*) AS ct
   FROM  (
      SELECT person, result
           , row_number() OVER (PARTITION BY person ORDER BY time)
           - row_number() OVER (PARTITION BY person, result ORDER BY time) AS grp
      FROM   tablex
      ) sub1
   GROUP BY person, result, grp
   ) sub2
GROUP  BY person
ORDER  BY max(ct) DESC, person;

SQL Fiddle.

Result exactly as desired.

After forming groups (grp) of consecutive pairs (person, result), the count has to be per pair  and group (person, result, grp), not just per person and group (person, grp). There can (and will) be one group with the same group number per person and result, those have to be counted separately.

Also, it's not efficient to use CTEs here. In Postgres, performance is generally superior for subqueries, since CTEs are optimization fences that always materialize the result. Use CTEs only where they are actually necessary.


Should SQL Server Always-On Availability groups have storage deduplication enabled? We are using EMC and storage engineers want to save on storage and provide one big storage array for implementing synchronous AG replicas. I am not a storage expert but isn't it best practice to have two separate storage arrays when implementing AG replicas?
Otherwise, I will be introducing single point of failure by having one big de-duped storage. Am I right?

storage engineers want to save on storage and provide one big storage array for
  implementing synchronous AG replicas.

Dump. The whole idea behind using an AOAG - instead of simple failover - is to have 2 storage backends. Because those ALSO SOMETIMES FAIL (and yes, even a big HP EVA can fail). The whole concept is that:

You can use local cheap high performance storage, not slow storage arrays. And yes, I mean slow - I can get a 2HE server these days that has literally hundred gigabyte / second IO locally on quite a lot of PCIe connected NVM drives. For a lot less cost.
You have redundant storage so you rule out any storage backend failure, because regardless what you do, that one SOMETIMES also fails. Been there, seen that. Rare but it happens.

Puttig an AOAG on an XIO already is eliminating a lot of the benefit (cost), but then using it as shared storage.... pretty much totally kills the benefit. Not EXACTLY totally because I can come up with some rare edge cases where it still is better (corruption wise), but really... not a lot left compared to normal clustering.


Why do I get incorrect characters when decoding a Base64 string to NVARCHAR in SQL Server? I've been looking at how to decode Base64 with SQL Server and after searching around many solutions online (some from here) seem to be based upon this sort of method.

SELECT CAST(CAST('Base64StringHere' as XML ).value('.','varbinary(max)') AS VARCHAR(250))

When I have ASCII text this works perfectly. However when I have the following French text it gets corrupted (presumably due to the restrictions of VARCHAR).

Où est le café le plus proche?
T8O5IGVzdCBsZSBjYWbDqSBsZSBwbHVzIHByb2NoZT8=

And provides the following output.

OÃ¹ est le cafÃ© le plus proche?

I thought the relatively simple fix would be to change the CAST to NVARCHAR but this results in corruption again.

SELECT CAST(CAST('T8O5IGVzdCBsZSBjYWbDqSBsZSBwbHVzIHByb2NoZT8=' as XML ).value('.','varbinary(max)') AS NVARCHAR(250) )

썏₹獥⁴敬挠晡꧃氠汰獵瀠潲档㽥

My search engine skills may be failing me, but I cannot seem to find someone else who has my problem.

Any thoughts?

The problem is that you encoded a UTF-8 encoded string into Base64. So, decoding the Base64 gives you back the original UTF-8 sequence of bytes. SQL Server uses UTF-16 Little Endian only for NVARCHAR data, and even for XML. Hence, Ã¹ is the 8-bit version of the two-byte UTF-8 sequence for ù (0xC3 and  0xB9).

Fortunately, it is possible to convert a UTF-8 encoded string into UTF-16, or even into a non-Unicode Code Page (IF the Code Page supports all characters being converted). The trick is to convert the Base64 decoded bytes, in their text representation (even with incorrectly converted characters) into XML. The trick to this trick is that you need to add the <?xml ...> declaration (typically omitted) and specify the source encoding:

DECLARE @Base64Value NVARCHAR(500) = N'T8O5IGVzdCBsZSBjYWbDqSBsZSBwbHVzIHByb2NoZT8=';

DECLARE @BinaryValue VARBINARY(500) =
    CONVERT(XML, @Base64Value).value('.','varbinary(max)');

DECLARE @IntermediaryValue VARCHAR(500) = CONVERT(VARCHAR(500), @BinaryValue);

SELECT @BinaryValue, @IntermediaryValue;
-- 0x4FC3B920657374206C6520636166C3A9206C6520706C75732070726F6368653F
-- Oֳ¹ est le cafֳ© le plus proche?

-- This is to NVARCHAR, which will always work:
SELECT CONVERT(NVARCHAR(500),
    CONVERT(XML, '<?xml version="1.0" encoding="UTF-8"?>' +  @IntermediaryValue)
              );
-- Où est le café le plus proche?

-- This is to VARCHAR, but "success" will depend on the Code Page
-- specified by the default Collation of the current Database:
SELECT CONVERT(VARCHAR(500),
    CONVERT(XML, '<?xml version="1.0" encoding="UTF-8"?>' +  @IntermediaryValue)
              );

 -- In a DB with a Latin1_General Collation it works:
 -- Où est le café le plus proche?

 -- In a DB with a Hebrew Collation, it gets the following error:
 /*
   Msg 6355, Level 16, State 1, Line XXXXX
   Conversion of one or more characters from XML to target collation impossible
 */

Please note that this trick only works for converting from various source encodings into UTF-16 Little Endian (as that is how the XML datatype in SQL Server stores strings internally). This method cannot be used to convert UTF-16 into UTF-8 or some other non-SQL Server-supported encoding.

Below is an Inline-TVF encapsulating the steps shown above:

GO
CREATE FUNCTION dbo.ConvertBase64EncodedUTF8ToUTF16LE
(
  @Base64EncodedUTF8String VARCHAR(8000)
)
RETURNS TABLE
AS RETURN

    SELECT
        CONVERT(NVARCHAR(500),
                CONVERT(XML,
                        '<?xml version="1.0" encoding="UTF-8"?>' +
                        CONVERT(VARCHAR(500),
                                CONVERT(XML, @Base64EncodedUTF8String)
                                  .value('.','varbinary(max)')
                               )
                       )
               ) AS [DecodedValue];

GO

And then test:

SELECT *
FROM   dbo.ConvertBase64EncodedUTF8ToUTF16LE(
          'T8O5IGVzdCBsZSBjYWbDqSBsZSBwbHVzIHByb2NoZT8=');
-- Où est le café le plus proche?

Just use with CROSS APPLY if doing a set-based operation.


Dynamic except query PostgreSQL I know this has been asked about multiple times in the past, sorry for the repeat I'm just trying to brainstorm a solution.

I'm looking for a way to query a table while excluding a small minority of the columns without having to query information_schema manually every time (since a lot of my tables consist of 50 or more columns only 2-3 of which are used for things that are irrelevant in the desired use case.)

I went about this like so:

query 1 returns the list of all the columns that exist on a table minus the ones you don't want.

        CREATE OR REPLACE FUNCTION public.inverse_columns(
        _table_name text DEFAULT NULL::text,
        _col_list text[] DEFAULT NULL::text[])
      RETURNS text[] AS
    $BODY$
    DECLARE
    col_arr text[];
    BEGIN
    EXECUTE  'SELECT array_agg(column_name::text) FROM information_schema.columns
    WHERE table_name = ' ||quote_literal(_table_name) ||' AND
    column_name NOT IN
    ('||chr(39)|| array_to_string(_col_list, quote_literal(chr(44)))||chr(39)||');'
    INTO col_arr;

    RETURN col_arr;
    END;
    $BODY$
      LANGUAGE plpgsql IMMUTABLE
      COST 100;
    ALTER FUNCTION public.inverse_columns(text, text[])
      OWNER TO postgres;

while query two uses the output array in another dynamic query in order to return the result set.

CREATE OR REPLACE FUNCTION public.except_select(
    _table_name text DEFAULT NULL::text,
    _col_list text[] DEFAULT NULL::text[])
  RETURNS SETOF record AS
$BODY$
DECLARE
ret_cols text[] := inverse_columns(_table_name, _col_list);
ret_vals record;
BEGIN
RETURN QUERY
EXECUTE 'SELECT '|| quote_literal(array_to_string(ret_cols, chr(44)||chr(32))) ||
    'FROM ' || _table_name||';';
END;
$BODY$
  LANGUAGE plpgsql IMMUTABLE
  COST 100;
ALTER FUNCTION public.except_select(text, text[])
  OWNER TO postgres;

the error I am receiving is this:

ERROR:  a column definition list is required for functions returning "record"
LINE 1: SELECT * FROM except_select.....

and while I understand it is that I am misusing record, I can't think of a good way to return an undefined set of rows short of returning a refcursor and using that to populate a result set(which is what I am working on right now with slow progress unless I can get a better idea of how to do this.)

    EDIT:
few days of playing around with it and I gave up.

In SQL, it's not possible to have a function that simultaneously figures out what columns it should return and return them.

It's a consequence of the fundamental rule that the output structure of a SQL query must be known when parsing a query, before executing it.

If the client-side cooperates, that's not a problem. Just implement a two-step process:

step #1: call inverse_columns() that returns the SELECT statement as an string (or just the list of columns and your client code builds the rest of the statement).

step #2: run the SELECT statement.

There's no manual process involved if you can do client-side automation.

For instance newer versions of psql (9.6) have a \gexec function that executes a query and reinject the result as an SQL query.

Reworking a bit your except_select function to return a query instead of a function:

CREATE OR REPLACE FUNCTION public.except_select(_table_name text DEFAULT NULL::text, _col_list text[] DEFAULT NULL::text[])
 RETURNS text  LANGUAGE plpgsql IMMUTABLE
AS $function$
DECLARE
ret_cols text[] := inverse_columns(_table_name, _col_list);
ret_vals record;
BEGIN
RETURN 'SELECT '|| array_to_string(ret_cols, chr(44)||chr(32)) ||
    ' FROM ' || _table_name;
END;
$function$

Say we want to query all of pg_stat_activity except the query field, this could be written in psql as:

select except_select('pg_stat_activity', '{"query"}') \gexec

Result with expanded display (\x):

-[ RECORD 1 ]----+------------------------------
datid            | 12303
datname          | postgres
pid              | 15175
usesysid         | 10
usename          | daniel
application_name | psql
client_addr      |
client_hostname  |
client_port      | -1
backend_start    | 2016-12-17 16:10:07.200232+01
xact_start       | 2016-12-17 16:21:10.069285+01
query_start      | 2016-12-17 16:21:10.069285+01
state_change     | 2016-12-17 16:21:10.069288+01
wait_event_type  |
wait_event       |
state            | active
backend_xid      |
backend_xmin     | 622


Maintenance plan is backing up database transaction log but it is not selected I have a strange problem, the transaction log for my database is being backed up however it is not selected.

I change the names of the server and databases in the screenshots to protect my information from the bad guys. ;)

Here is the issue that server "XYZ" is having, the screen shot below is showing the sub-plan for the "Transaction Log Backup" maintenance job. As you can see here "Database2" is showing up in the plan task.

However in the properties plan task "Database2" is not selected. In order for the transaction log to be backed up the database has to be selected, like "Database1", right?

Despite the fact that "Database2" is not selected the transaction log is being backed up. I verified the properties of the database when the last transaction log was done.

So, I am a little confused about this and would like to know if anyone else here came across this and how you resolved it.

Server specs: SQL Server 2008 SP2 running on Windows Server 2008 R2 Standard

Your radio button is on All user databases and NOT on These Databases


Open source and commercial tools to build dynamic queries of fact tables in star schema What user interfaces or tools (commercial and/or open source) can query a star schema model (Ralph Kimball dimensional model)?  So for example, if we have X fact tables, and Y dimension tables, and Z cross reference tables, we'd like to query a random fact table.  If you are aware of such tools, can you provide a reference of each.

Mondrian OLAP server
http://mondrian.pentaho.com/

Pentaho Schema Workbench (for creating the cube schema)
http://mondrian.pentaho.com/documentation/workbench.php

Pentaho Aggregation Designer (for finding useful aggregations and building them)
http://infocenter.pentaho.com/help/index.jsp?topic=%2Faggregation_designer_guide%2Fconcept_pad_overview.html

Saiku AJAX pivot tables
http://analytical-labs.com/

Pentaho Report Designer for designing reports.

Pentaho Data Integration (Kettle) for ETL into data warehouse
http://kettle.pentaho.com/

DataCleaner for quality control


If a select statement returns no result, change the where condition I have two queries. I want to run the first select statement and only if it does not return any record then I want to run the second statement. How can I achieve this. These are my two statements:

select * from drscan.oem_program p
WHERE upper(OEM_PROGRAMID) LIKE upper('%Subvented%')
  and oem_code = 'NCI'
  and rownum=1
  and p.ISACTIVE='Y'

select *
from drscan.oem_program p
where oem_code ='NCI'
  and p.PROGRAMCATEGORY='NEW'
  and p.ISACTIVE='Y'
  and trunc(p.STARTDATE)<=trunc(sysdate)
  and trunc(p.ENDDATE)>= trunc(sysdate)
  and rownum=1

In SQL (not PL/SQL) you can do it like this:

with p as (
  select * from drscan.oem_program p
  WHERE upper(OEM_PROGRAMID) LIKE upper('%Subvented%')
    and oem_code = 'NCI'
    and rownum=1
    and p.ISACTIVE='Y'
)
, p2 as (
    select *
  from drscan.oem_program p
  where oem_code ='NCI'
    and p.PROGRAMCATEGORY='NEW'
    and p.ISACTIVE='Y'
    and trunc(p.STARTDATE)<=trunc(sysdate)
    and trunc(p.ENDDATE)>= trunc(sysdate)
    and rownum=1
)
select * from p
union all
select * from p2 where not exists(select 1 from p)


Dynamic SQL query - how do I add an int to the code? Consider:

Declare @stringsvar varchar(1000)
Declare @Emp_id int
DECLARE  @strvar SYSNAME = 'Employee_test'

SET @stringsvar = ('select * from' + ' ' + @strvar + ' where emp_id' + ' =' + @Emp_id)
Print @stringsvar

The above query is giving an error as mentioned below:

  Msg 245, Level 16, State 1, Line 17 Conversion failed when converting
  the nvarchar value 'select * from Employee_test where emp_id =' to
  data type int.

What needs to be done in this case?

George's answer does solve your problem, but it leaves you wide open to SQL injection attacks.

While converting an INT to a VARCHAR 11 is likely not going to cause any issues, sysname is the equivalent of an NVARCHAR 128, and you can jam a lot of extra code in there.

To make your code totally safe, you'd want to do this:

DECLARE @stringsvar NVARCHAR(1000) = '';
DECLARE @Emp_id INT = 1;
DECLARE @strvar sysname = N'Employee_test';

SET @stringsvar = ( N'select * from '
                    + QUOTENAME(@strvar)
                    + N' where emp_id'
                    + N' = @iEmp_id' );

PRINT @stringsvar;
EXEC sys.sp_executesql @stringsvar,
                       N'@iEmp_id INT',
                       @iEmp_id = @Emp_id;

Using sp_executesql to issue parameterized dynamic SQL, and quotename to make the table name non-executable code is a much safer choice.

For a little more reference, I'd suggest heading over here: The Curse and Blessings of Dynamic SQL


MySQL Authentication Error After Re-applying the mysqldump after an upgrade After upgrading from a lower version of MySQL 5.5.* to MySQL 5.5.52, after reapplying the mysqldump that I took before the upgrade, I am receiving the following error:

Authentication to host 'localhost' for user '[user]' using method 'mysql_native_password' failed with message: Access denied for user '[user]'@'localhost' (using password: YES) ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Access denied for user '[user]'@'localhost' (using password: YES)

I needed to run the mysql_upgrade command after re-applying the mysqldump after the update.

mysql_upgrade -u root -p --force


How do I check the connection timeout in Oracle? I need to check Oracle 12c timeout for an active connection. I don't have access to the database itself but I do have some creds. Is there a table to query to get this information?

Thank you!

These are properties of user profiles. Query DBA_PROFILES.

CREATE PROFILE

  resource_parameters

  ...

  CONNECT_TIME

  Specify the total elapsed time limit for a session, expressed in
  minutes.

  IDLE_TIME

  Specify the permitted periods of continuous inactive time during a
  session, expressed in minutes. Long-running queries and other
  operations are not subject to this limit.

  ...


Why does mysql allow me to enter password for non existing mysql user My mysql server has 2 users -

root
robb

But when I try to login with another user it let's me enter password -

hussain@magic-laptop:~$ mysql -u jon -p
Enter password:

Shouldn't it give an error saying user: jon donesn't exist?

I'm sure someone else will be able to provide a more informed answer than this, but a common-sense guess would be that this is a security feature.

If someone is attempting a brute-force attack, they will need the username and password using the system described above. If MySQL reported invalid users before prompting for a password, half of the work is already done for the attacker.

This is purely theoretical, as I haven't tested anything or been informed on the subject by anyone else - just an educated guess based on other systems.


Optimitze Table, MyIsam I was reading a post on Percona (https://www.percona.com/blog/2010/12/09/mysql-optimize-tables-innodb-stop/) that said dropping and adding an index on a innodb table was almost 20* faster than using the table optimize syntax. Is this also true for myisam tables?

I have a 50 million row table and am deleting about 40k rows a day from it (while also adding in 50k new rows).

(Some of this is off-topic.)

Fast ALTER

The handling of ADD/DROP INDEX is different for MyISAM and InnoDB.
Oracle is focusing only on InnoDB.
InnoDB can allow writes while an index is being built -- by keeping track of what needs to be added to the index.  It already has a "Change Buffer" to optimize such.  MyISAM would have to invent a similar mechanism, and has no 'transaction' hooks to help.

Therefore, I conclude that all(?) benefits from ALTER TABLE ... INLINE/INPLACE are handled inside the InnoDB engine, leaving MyISAM out in the cold.

Long ago, one flavor of online ALTER TABLE, namely adding a item to an ENUM (with restrictions), was implemented.  But that only required modifying the .frm file.

OPTIMIZE

In one situation in a hundred, OPTIMIZE TABLE is useful in MyISAM.  The need for OPTIMIZE in InnoDB is much less frequent.

One example (applies to MyISAM only):  The table has a lot of churn -- either from UPDATEs that change the lengths of rows or from DELETE+INSERT.  MyISAM's INSERT gives preference to filling in holes in the .MYD, which can lead to individual rows scattered across multiple disk blocks.  This slows down SELECT, even for 'point queries'.

Time Series

Are you are deleting "old" rows and adding "new" rows?  If so,

Your MyISAM may need OPTIMIZE, as explained above.  But, with InnoDB, OPTIMIZE would be mostly a waste.
DELETEing 40M rows locks a MyISAM table for a long time.
There are other techniques to deal with big deletes.

Partitioning for time series

PARTITIONing, even with MyISAM (before MySQL 8.0), is possible.  It might let you turn the time consuming DELETE into an instantaneous DROP PARTITION.  More
Using PARTITIONing would probably obviate the need for OPTIMIZE, even for MyISAM.
Caveat:  Usually when adding partitioning, all the indexes need rethinking.


Script to automate converting a clustered index on primary key (with foreign keys to it) column to non clustered? What's a good way to easily convert a clustered index on a primary key column to a nonclustered index? I know how to do this manually, but it's time consuming if there are several tables which have foreign keys to the primary key as they need to be dropped and recreated.

Looking for a script or free tool to automate this.

I am using SQL Server 2012

Well, luckily for you, I did this very thing a few months back... I just never actually published it to my blog... Guess I should get to that at some point, but until then, here you go:

/***************************************************************************************************************
 *
 *  This Script will perform the following operations:
 *    1) Drop All RI
 *    2) Drop All PKEYS
 *    3) Recreate PKEYS as NONCLUSTERED
 *    4) Recreate ALL RI
 *
 *  To do this it will create a work table and populate it with the commands to execute then iterate through
 *  the work table to execute pregenerated commands.
 *
 ***************************************************************************************************************/

CREATE TABLE #workTable
(
    CommandID   INT IDENTITY(1,1),
    Command     VARCHAR(MAX)
)

SET NOCOUNT ON;

/***************************************************************************************************************
 *
 *  DROP FOREIGN KEYS
 *
 *  http://stackoverflow.com/questions/159038/can-foreign-key-constraints-be-temporarily-disabled-using-t-sql
 *
 ***************************************************************************************************************/

INSERT INTO #workTable (Command) VALUES ('PRINT ''-- DROP FOREIGN KEY CONSTRAINTS --''');

INSERT INTO #workTable (Command)
SELECT 'ALTER TABLE [' + sch.NAME + '].[' + OBJECT_NAME(fk.parent_object_id) + ']' + ' DROP  CONSTRAINT ' + '[' + fk.NAME + ']'
FROM sys.foreign_keys AS fk
INNER JOIN sys.schemas AS sch ON sch.schema_id = fk.schema_id
ORDER BY fk.NAME

INSERT INTO #workTable (Command) VALUES ('PRINT ''-- DROP FOREIGN KEY CONSTRAINTS -- COMPLETE'' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)');

/***************************************************************************************************************
 *
 *  DROP CLUSTERED PKEYS
 *
 *  http://social.technet.microsoft.com/wiki/contents/articles/2321.script-to-create-or-drop-all-primary-keys.aspx
 *
 ***************************************************************************************************************/

INSERT INTO #workTable (Command) VALUES ('PRINT ''-- DROP CLUSTERED KEYS --''');

DECLARE @object_id int;
DECLARE @parent_object_id int;
DECLARE @TSQL NVARCHAR(4000);
DECLARE @COLUMN_NAME SYSNAME;
DECLARE @is_descending_key bit;
DECLARE @col1 BIT;
DECLARE @action CHAR(6);

SET @action = 'DROP';
--SET @action = 'CREATE';

DECLARE PKcursor CURSOR FOR
    select kc.object_id, kc.parent_object_id
    from sys.key_constraints kc
    inner join sys.objects o
    on kc.parent_object_id = o.object_id
    where kc.type = 'PK' and o.type = 'U'
    and o.name not in ('dtproperties','sysdiagrams')  -- not true user tables
    order by QUOTENAME(OBJECT_SCHEMA_NAME(kc.parent_object_id))
            ,QUOTENAME(OBJECT_NAME(kc.parent_object_id));

OPEN PKcursor;
FETCH NEXT FROM PKcursor INTO @object_id, @parent_object_id;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @action = 'DROP'
        SET @TSQL = 'ALTER TABLE '
                  + QUOTENAME(OBJECT_SCHEMA_NAME(@parent_object_id))
                  + '.' + QUOTENAME(OBJECT_NAME(@parent_object_id))
                  + ' DROP CONSTRAINT ' + QUOTENAME(OBJECT_NAME(@object_id))
    ELSE
        BEGIN
        SET @TSQL = 'ALTER TABLE '
                  + QUOTENAME(OBJECT_SCHEMA_NAME(@parent_object_id))
                  + '.' + QUOTENAME(OBJECT_NAME(@parent_object_id))
                  + ' ADD CONSTRAINT ' + QUOTENAME(OBJECT_NAME(@object_id))
                  + ' PRIMARY KEY'
                  + CASE INDEXPROPERTY(@parent_object_id
                                      ,OBJECT_NAME(@object_id),'IsClustered')
                        WHEN 1 THEN ' CLUSTERED'
                        ELSE ' NONCLUSTERED'
                    END
                  + ' (';

        DECLARE ColumnCursor CURSOR FOR
            select COL_NAME(@parent_object_id,ic.column_id), ic.is_descending_key
            from sys.indexes i
            inner join sys.index_columns ic
            on i.object_id = ic.object_id and i.index_id = ic.index_id
            where i.object_id = @parent_object_id
            and i.name = OBJECT_NAME(@object_id)
            order by ic.key_ordinal;

        OPEN ColumnCursor;

        SET @col1 = 1;

        FETCH NEXT FROM ColumnCursor INTO @COLUMN_NAME, @is_descending_key;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            IF (@col1 = 1)
                SET @col1 = 0
            ELSE
                SET @TSQL = @TSQL + ',';

            SET @TSQL = @TSQL + QUOTENAME(@COLUMN_NAME)
                      + ' '
                      + CASE @is_descending_key
                            WHEN 0 THEN 'ASC'
                            ELSE 'DESC'
                        END;

            FETCH NEXT FROM ColumnCursor INTO @COLUMN_NAME, @is_descending_key;
        END;

        CLOSE ColumnCursor;
        DEALLOCATE ColumnCursor;

        SET @TSQL = @TSQL + ');';

        END;

    INSERT INTO #workTable (Command)
    SELECT @TSQL;

    FETCH NEXT FROM PKcursor INTO @object_id, @parent_object_id;
END;

CLOSE PKcursor;
DEALLOCATE PKcursor;

INSERT INTO #workTable (Command) VALUES ('PRINT ''-- DROP CLUSTERED KEYS -- COMPLETE'' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)');

/***************************************************************************************************************
 *
 *  CREATE NONCLUSTERED PKEYS
 *
 *  http://social.technet.microsoft.com/wiki/contents/articles/2321.script-to-create-or-drop-all-primary-keys.aspx
 *
 * NOTE: Subsection where script determines if PKEY or not was modified to only create NONCLUSTERED pkeys
 *
 ***************************************************************************************************************/

INSERT INTO #workTable (Command) VALUES ('PRINT ''-- CREATE NONCLUSTERED PKEYS --''');

--SET @action = 'DROP';
SET @action = 'CREATE';

DECLARE PKcursor CURSOR FOR
    select kc.object_id, kc.parent_object_id
    from sys.key_constraints kc
    inner join sys.objects o
    on kc.parent_object_id = o.object_id
    where kc.type = 'PK' and o.type = 'U'
    and o.name not in ('dtproperties','sysdiagrams')  -- not true user tables
    order by QUOTENAME(OBJECT_SCHEMA_NAME(kc.parent_object_id))
            ,QUOTENAME(OBJECT_NAME(kc.parent_object_id));

OPEN PKcursor;
FETCH NEXT FROM PKcursor INTO @object_id, @parent_object_id;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @action = 'DROP'
        SET @TSQL = 'ALTER TABLE '
                  + QUOTENAME(OBJECT_SCHEMA_NAME(@parent_object_id))
                  + '.' + QUOTENAME(OBJECT_NAME(@parent_object_id))
                  + ' DROP CONSTRAINT ' + QUOTENAME(OBJECT_NAME(@object_id))
    ELSE
        BEGIN
        SET @TSQL = 'ALTER TABLE '
                  + QUOTENAME(OBJECT_SCHEMA_NAME(@parent_object_id))
                  + '.' + QUOTENAME(OBJECT_NAME(@parent_object_id))
                  + ' ADD CONSTRAINT ' + QUOTENAME(OBJECT_NAME(@object_id))
                  + ' PRIMARY KEY'
                  + ' NONCLUSTERED'
                  + ' (';

        DECLARE ColumnCursor CURSOR FOR
            select COL_NAME(@parent_object_id,ic.column_id), ic.is_descending_key
            from sys.indexes i
            inner join sys.index_columns ic
            on i.object_id = ic.object_id and i.index_id = ic.index_id
            where i.object_id = @parent_object_id
            and i.name = OBJECT_NAME(@object_id)
            order by ic.key_ordinal;

        OPEN ColumnCursor;

        SET @col1 = 1;

        FETCH NEXT FROM ColumnCursor INTO @COLUMN_NAME, @is_descending_key;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            IF (@col1 = 1)
                SET @col1 = 0
            ELSE
                SET @TSQL = @TSQL + ',';

            SET @TSQL = @TSQL + QUOTENAME(@COLUMN_NAME)
                      + ' '
                      + CASE @is_descending_key
                            WHEN 0 THEN 'ASC'
                            ELSE 'DESC'
                        END;

            FETCH NEXT FROM ColumnCursor INTO @COLUMN_NAME, @is_descending_key;
        END;

        CLOSE ColumnCursor;
        DEALLOCATE ColumnCursor;

        SET @TSQL = @TSQL + ');';

        END;

    INSERT INTO #workTable (Command)
    SELECT @TSQL;

    FETCH NEXT FROM PKcursor INTO @object_id, @parent_object_id;
END;

CLOSE PKcursor;
DEALLOCATE PKcursor;

INSERT INTO #workTable (Command) VALUES ('PRINT ''-- CREATE NONCLUSTERED PKEYS -- COMPLETE'' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)');

/***************************************************************************************************************
 *
 *  CREATE FOREIGN KEYS
 *
 *  http://stackoverflow.com/questions/159038/can-foreign-key-constraints-be-temporarily-disabled-using-t-sql
 *
 ***************************************************************************************************************/

INSERT INTO #workTable (Command) VALUES ('PRINT ''-- CREATE FOREIGN KEY CONSTRAINTS --''');

INSERT INTO #workTable (Command)
SELECT 'ALTER TABLE ' + const.parent_obj + '
    ADD CONSTRAINT ' + const.const_name + ' FOREIGN KEY (
            ' + const.parent_col_csv + '
            ) REFERENCES ' + const.ref_obj + '(' + const.ref_col_csv + ')'
FROM (
    SELECT QUOTENAME(fk.NAME) AS [const_name]
        ,QUOTENAME(schParent.NAME) + '.' + QUOTENAME(OBJECT_name(fkc.parent_object_id)) AS [parent_obj]
        ,STUFF((
                SELECT ',' + QUOTENAME(COL_NAME(fcP.parent_object_id, fcp.parent_column_id))
                FROM sys.foreign_key_columns AS fcP
                WHERE fcp.constraint_object_id = fk.object_id
                FOR XML path('')
                ), 1, 1, '') AS [parent_col_csv]
        ,QUOTENAME(schRef.NAME) + '.' + QUOTENAME(OBJECT_NAME(fkc.referenced_object_id)) AS [ref_obj]
        ,STUFF((
                SELECT ',' + QUOTENAME(COL_NAME(fcR.referenced_object_id, fcR.referenced_column_id))
                FROM sys.foreign_key_columns AS fcR
                WHERE fcR.constraint_object_id = fk.object_id
                FOR XML path('')
                ), 1, 1, '') AS [ref_col_csv]
    FROM sys.foreign_key_columns AS fkc
    INNER JOIN sys.foreign_keys AS fk ON fk.object_id = fkc.constraint_object_id
    INNER JOIN sys.objects AS oParent ON oParent.object_id = fkc.parent_object_id
    INNER JOIN sys.schemas AS schParent ON schParent.schema_id = oParent.schema_id
    INNER JOIN sys.objects AS oRef ON oRef.object_id = fkc.referenced_object_id
    INNER JOIN sys.schemas AS schRef ON schRef.schema_id = oRef.schema_id
    GROUP BY fkc.parent_object_id
        ,fkc.referenced_object_id
        ,fk.NAME
        ,fk.object_id
        ,schParent.NAME
        ,schRef.NAME
    ) AS const
ORDER BY const.const_name

INSERT INTO #workTable (Command) VALUES ('PRINT ''-- CREATE FOREIGN KEY CONSTRAINTS -- COMPLETE'' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)');

SET NOCOUNT OFF;

/***************************************************************************************************************
 *
 *  Iterate through Work Table on the database
 *
 ***************************************************************************************************************/

--SELECT * FROM #workTable
DECLARE @Command VARCHAR(MAX)

DECLARE WorkTableCursor CURSOR
FOR
    SELECT Command
    FROM #workTable

OPEN WorkTableCursor

FETCH NEXT FROM WorkTableCursor
INTO @Command

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @Command
    EXECUTE (@Command)

    FETCH NEXT FROM WorkTableCursor
    INTO @Command
END

CLOSE WorkTableCursor
DEALLOCATE WorkTableCursor

DROP TABLE #workTable


Why do those Update statements fail using a datetime format? Given:

Query #1:

begin tran
UPDATE [dbo].[t] SET [mydatetime]='2011-12-25 07:00:00.000';
rollback

Query #2: (changing month/day)

begin tran
UPDATE [dbo].[t] SET [mydatetime]='2011-25-12 07:00:00.000';
rollback

Why does the QUERY #1 fail to run on a new installed SQL Server while query #2 runs succesfully?

  Msg 242, Level 16, State 3, Line 6 Bei der Konvertierung eines
  varchar-Datentyps in einen datetime-Datentyp liegt der Wert außerhalb
  des gültigen Bereichs.

In comparison why does Query #1 run on the old server while query #2 fails on the new server?

  Msg 242, Level 16, State 3, Line 7 The conversion of a varchar data
  type to a datetime data type resulted in an out-of-range value.

The servers have different versions (2008 / 2016). And as you can see from error messages there are two different languages installed. Please don't tell me just because the language of the Sql-Server installation is different it is not able to handle different date formats?

Is this configurable?
Will I get away with uniform formats like 20150129 08:00:00.000 ?

You could deal with the issue by specifying the style and doing an explicit CONVERT() from string to datetime rather than an implicit conversion. https://msdn.microsoft.com/en-us/library/ms187928.aspx

UPDATE dbo.t SET mydatetime = CONVERT('2011-12-25 07:00:00.000', 121)


Cannot connect to mysql server error for master replication Am trying to connect to a mysql master which is a server running LAMP. It replies with an error "cannot connect to mysql server". Have also tried to connect using mysql -h XXX -u -p from the slave server but still replies the same error. On the LAMP server have also checked the iptables and allowed mysql port to allow incoming connection but still have the same error. Have also allowed my slave public ip but the error is just the same.

Using this mysql -h XXX -u -p you cannot connect to mysql remotely using root, if you want to connect with root remotely you have to configure it. For replication you need to create replication user on master and give it replication priviliges.

example;

mysql> CREATE USER 'repl'@'%.example.com' IDENTIFIED BY 'password';

mysql> GRANT REPLICATION SLAVE ON . TO 'repl'@'%.example.com';

start your slave and check what is the error on IO thread also check slave log for any errors.


How to predict database size? One of requirments for database which I am building is to predict database size to prepare hardware for production environment. Aapplication has 2 main tables wchich are partitioned. Tables are in 8 Filegroups (16 files, 32 partitions). Both tables store data for last month (after one month data will be deleted). We have to be prepared for around 12 milions rows daily in one table and around 36 milions rows in second table. I made a workload test for these tables and:

For 1 milion rows data size of filegroups which cointains data was around 13 GB
For 12 milion rows data size of filegroups which contains data was around 48,5 GB

Log size has increased only by 40mb.
According to this data have two questions:

My idea was to make daily workload and multiple it by 30. But according to above data it is not working like that. 13*12 != 48,5
Why log has increased only by 40 mb of data ?
Is there any difference in size If we store data in AlwaysOn solution ?

To measure file size I have been used below query:
SELECT [sizing].[DbName],
       [sizing].[FileName],
       [sizing].[type_desc],
       [sizing].[CurrentSizeMB],
       [sizing].[FreeSpaceMB],
       [CurrentSizeMB] - [FreeSpaceMB] AS [SizeStored]
FROM
(
    SELECT DB_NAME() AS [DbName],
           [name] AS [FileName],
           [type_desc],
           [size] / 128.0 AS [CurrentSizeMB],
           [size] / 128.0 - CAST(FILEPROPERTY([name], 'SpaceUsed') AS int) / 128.0 AS [FreeSpaceMB]
    FROM [sys].[database_files]
    WHERE [type] IN ( 0, 1 )
) [sizing];

It sounds like you have highly varying data, for example a few VARCHAR(MAX) columns that are sometimes filled in heavily and other times not at all. That is why 12,000,000 rows doesn't necessarily result in 12 times the size of 1 million rows. You need to use a larger sample size to more accurately determine what your data growth will be. For example if you wanted to know what the smaller table will look like in a month then you need to measure against at least a months worth of data (360,000,000 rows). And honestly probably should measure against a few months for a more accurate estimation if possible (though I'm assuming it's not since you're trying to do initial provisioning).

Without more information it's hard to tell why your log file is so relatively slow. What Recovery Model is your database set to? Is it possible someone ran a SHRINK operation against it?

The AlwaysOn solution won't materially affect the size on your Primary Replica. Though keep in mind that AlwaysOn results in a literal copy of your database in a Secondary Replica on a separate server, so you'll be storing two copies of the same data.

To answer your root question, the advice I gave in #1 in regards to using as big of a data sample for your calculations as possible and adding in a worst case estimate buffer is your best bet. E.g. if you calculate a months worth of data to be 1 TB, provision for 1.25 or 1.5 TB to be safe for the first month and adjust as needed over time. This will be a continuous task for you to revisit periodically, re-calculate, re-evaluate, and re-provision in the beginning until you become more intimate with your data. It's better to be safe over provisioning in the beginning than under provisioning.


Combine multiple queries into 1 query I have a message table in Postgres 9.4 that contains a words field of array type, with random words of a message. Currently I have millions of messages:

\d messages
                        Table "public.messages"
            Column            |            Type             | Modifiers
------------------------------+-----------------------------+-----------
 id_str                       | character varying(255)      | not null
 feed_id                      | integer                     |
 message                      | character varying(255)      |
 posted_at                    | timestamp without time zone |
 words                        | character varying(255)[]    |
 Indexes:
    "messages_pkey" PRIMARY KEY, btree (id_str)
    "index_messages_on_feed_id" btree (feed_id)
    "index_messages_on_feed_id_posted_at" btree (feed_id, posted_at DESC NULLS LAST)
    "index_messages_on_words" gin (words)

Now I have an array of words [A, B, C].
I need to find the number of messages that the words contains each of the combinations of the list, i.e. [[A,B], [A,C], [B,C]].

Also I have this combination array created outside of the Postgres query already.

I can do this by creating 3 queries:

select count(*) from messages where words @> ARRAY['A','B']::varchar[];
select count(*) from messages where words @> ARRAY['A','C']::varchar[];
select count(*) from messages where words @> ARRAY['B','C']::varchar[];

Is there a way to do this in one query? And output the result in the following format?

A, B, count or [A, B], count
A, C, count
B, C, count

Edit:

Here's the query I run currently:

http://sqlfiddle.com/#!15/7c907/2/0

http://explain.depesz.com/s/Dot

And this one is using Erwin Brandstetter 's method:

http://sqlfiddle.com/#!15/7c907/1/0

http://explain.depesz.com/s/pr2

Now assume the messages table has more than 1 Million records, the CTE scan or seq scan in each of the query plan will become very slow.

To get all unique pairs of elements from an array of arbitrary length:

WITH a(a) AS (SELECT '{A,B,C,D}'::text[])  -- provide array here
,    i(i) AS (SELECT i FROM a, generate_series(1, array_upper(a.a,1)) i)
SELECT ARRAY[a[i1.i], a[i2.i]] AS pair
FROM   i i1
JOIN   i i2 ON i2 > i1
,      a;

You can then join to the message table.

Without knowing any details of your setup, my educated guess is that a LATERAL join will be fastest as it can use the GIN index on messages.words - create it if you don't have one yet.

WITH a(a) AS (SELECT '{A,B,C,D}'::text[])
,    i(i) AS (SELECT i FROM a, generate_series(1, array_upper(a.a,1)) i)
SELECT p.pair, c.ct
FROM  (
   SELECT ARRAY[a[i1.i], a[i2.i]] AS pair
        , i1.i AS i1, i2.i AS i2
   FROM   i i1
   JOIN   i i2 ON i2 > i1
   ,      a
   ) p
, LATERAL (
   SELECT count(*) AS ct
   FROM   message
   WHERE  words @> p.pair
   ) c
ORDER BY p.i1, p.i2;

SQL Fiddle.


What standard SQL isolation levels do the SQLite isolation levels correspond to? There are four standard SQL isolation levels:  Read Uncommitted,  Read Committed, Repeated Read, and Serializable. (c.f. Database System Concepts 6ed.)

In SQLite, there are three isolation levels: DEFERRED, IMMEDIATE, or EXCLUSIVE. They control how the required read/write locks are acquired. See https://www.oreilly.com/library/view/using-sqlite/9781449394592/ch04s07.html

  By default, all transactions (including autocommit transactions) use the DEFERRED mode.
  Under this mode, none of the database locks are acquired until they are required. This
  is the most “neighborly” mode and allows other clients to continue accessing and using
  the database until the transaction has no other choice but to lock them out. This allows
  Transaction Control Language | 53other clients to continue using the database, but if the locks are not available when the
  transaction requires them, the transaction will fail and may need to be rolled back and
  restarted.

  IMMEDIATE attempts to acquire a reserved lock immediately. If it succeeds, it
  guarantees the write locks will be available to the transaction when they are needed,
  but still allows other clients to continue to access the database for read-only operations.

  The EXCLUSIVE mode attempts to lock out all other clients, including read-only clients.
  Although the IMMEDIATE and EXCLUSIVE modes are more restrictive to other clients, the
  advantage is that they will fail immediately if the required locks are not available, rather
  than after you’ve issued your DDL or DML commands.

I was wondering if it is possible to make some correspondence between the four standard SQL isolation levels and the three SQLite isolation levels?

Does DEFERRED mean exactly serializability isolation level, but only applies to transactions which consist of only one statement not more than one?
Does IMMEDIATE mean exactly read uncommitted isolation level, which prevents dirty write but not dirty read?
Does EXCLUSIVE mean exactly serializability isolation level?

Thanks.

DEFERRED, IMMEDIATE, and EXCLUSIVE are not isolation levels. All three modes ensure the serializable isolation level. (SQLite's transcations are always serializable because it actually serializes them; there is a single, database-wide lock.)

IMMEDIATE takes the equivalent of a write lock at the beginning of the transaction; this prevents a deadlock when two concurrent read-only transactions try to update to a write lock. EXCLUSIVE is useful when you want to lock the database without actually writing.


How can I change the timezone in MySQL? In a MySQL DB, I have two varchar fields. One is for date and one is for time. I need to change the time from CST to GMT. In MySQL how do we convert the combination of these date and time fields from CST to GMT time? It could be helpful if I get some sample queries for retrieve the same.

You would use the CONVERT_TZ function like this:

SELECT CONVERT_TZ('2012-01-01 12:00:00','-06:00','+00:00')

Example SQL Fiddle

But, why are you using varchar for your date/time fields? It would be better to use the proper data types DATE and TIME.


Need some clarification of another post's method for restoring database with frm and ibd files I want to ask a question in this one post at Restoring MySQL Tables from .ibd, .frm and mysqllogbin files but unfortunately my reputation isn't at 50 yet, so the system won't let me ask in that post.

So I ask here:

She figured out how to get her database restored through some trial and error. Her step #2 said:

  Go into your command/terminal to open the MySQL utility, mysqlfrm, and
  use it to find the structure of your table that you need to restore.
  How I did that, was I cd'ed into the file location of mysqlfrm, then
  entered "mysqlfrm --server=user:pwd@localhost --port=3307
  "path_to_.frm_file" > table_name.txt". The .txt file should be saved
  in the same folder as where your utilities are saved if you didn't
  specify where you wanted it to go.

My question is this: I assume that I put the path to my frm file in that area she puts "path_to_.frm_file" so I'd do something like this: C:\MySQL_5.6.12\data\wordpress\mysqlfrm  but I'm not understanding the "table_name.txt" part. Will I need to put the name of my table in the "table_name" part and am I correct in understanding that this will create a .txt file with my information and it will be saved where I have mysqlfrm and the other MySQL Utilities?

Other question is: she attempted this using WampServer 2.4 and MySQL 5.6.12 on Windows 2003 Server. I'm doing this in WAMP on MySQL 5.6.12 on a laptop running Windows 8. Is her method okay to do on Windows 8 or am I not in the same ballpark. I'd like to know before I would try her method.

table_name.txt is just the name of the file you're creating to save the CREATE statements in.  By default, it should go into the same folder as the utilities, but if you'd rather it go somewhere else, you can do path_to_where\you_want_it_saved\table_name.txt and it should go there instead.


Run Built-In Function on linked server We have two SQL 2014 servers, and I have created a Linked Server on one of them to the other. As part of a task I'm trying to run I need to convert a SQL UserID to a username on both the local and the linked server.

On the local server I can just call

SELECT SUSER_NAME(1)

which will return the string sa

Is there an easy way to run the function SUSER_NAME on the linked server to get a value back from there?

The other alternative I think I've found is to do a join to sys.server_principals and pull out the name from there, but SUSER_NAME seems like a cleaner approach.

You can execute Dynamic SQL on that Linked Server by executing sp_executesql on that remote server using the 4-part name. Then you just assign that to a variable marked as OUTPUT:

DECLARE @RemoteName sysname,
        @LoginID INT;

SET @LoginID = 267;

EXEC [LinkedServerName].master.dbo.sp_executesql
    N'SET @tmpRemoteName = SUSER_NAME(@tmpLoginID);',
    N'@tmpLoginID INT, @tmpRemoteName sysname OUTPUT',
    @tmpLoginID = @LoginID,
    @tmpRemoteName = @RemoteName OUTPUT;

SELECT @RemoteName;

Of course, it is debatable as to whether this is "cleaner" than JOINing to that remote table as there are pros and cons to each approach.


Is there any particular reason LIMIT 0 is allowed? I've just tried running SELECT * FROM tbl LIMIT 0, which unsurprisingly, returns 0 results.

I could see an argument for making it be an error, because it will never return any result, but then again it isn't invalid like LIMIT 'HelloWorld'.

Are there any actual real-world use cases for LIMIT 0, or is it just allowed because there's no real reason to not to allow it?

Perhaps a matter of convenience

I once answered a post Table JOIN and CREATE new table extremely slow, mentioning how CREATE TABLE AS SELECT will create a table and then perform INSERT INTO SELECT to populate the table

There are ways to use LIMIT 0 to create a temp table without indexes

CREATE TABLE mytable SELECT * FROM othertable LIMIT 0;

In another post, MySQL Locks while CREATE TABLE AS SELECT, I mentioned under OPTION #2 using WHERE 1=2 as a way to create a blank temp table with no indexes

CREATE TABLE 1_temp_foo SELECT * FROM crm_companies WHERE 1=2;
ALTER TABLE 1_temp_foo ENGINE=InnoDB;

In terms of end result, using LIMIT 0 and WHERE 1=2 and even WHERE 0 are synonymous.

I agree with your assumption that it was allowed for no particular reason (because there's no real reason to not allow it, as you say), but it sure comes in handy for such occasions.


Performance issue using view vs. direct statement I have a table with structure similar to this:

table values
(
id1 int,
id2 int,
value real,
value_at timestamp,
constraint primary key (id1,id2,value_at)
)

I need to get the "current value" for a given combination (id1,id2) --- that is, the record with highest value_at among the records with a given combination id1,id2.

The "normal" SQL query would be (example for id1,id2 = 100,200):

select ... from values
where (id1,id2) = (100,200)
      and value_at = (select max(value_at) from values
                      where (id1,id2) = (100,200))

However, I want to have a view that hides this complexity:

select value from vw_current_value where (id1,id2) = (100,200)

My attempt is:

create view vw_current_value as
select id1, id2, value from values as outer_v
where value_at = (select max(value_at) from values as inner_v
                  where outer_v.id1 = inner_v.id1 and outer_v.id2 = inner_v.id2)

It works, but getting a current value for a given (id1,id2) is 12x slower than what I get using the "direct" SQL statement (without using the view)
as reported by EXPLAIN ANALYZE (0.04 ms for the non-view, 0.48 ms for the view, with a table that has 250k records).

My question is: is there a more efficient way to write the view?  Perhaps using some other SQL construct that I'm either overlooking or unfamiliar with?

Notice that I would like to:

Avoid a Pl/PgSQL procedure for this.
Use a view and not the "direct" select statement.
Stick to version 9.2.4  (however, if this is known to have much better performance in later versions, I would certainly like to know about it).

EDIT:  As requested, here's the output of the two explain analyze for each of the queries (first one for the direct query, second for the query using the view):

Index Scan using pk_values on values  (cost=0.09..8.43 rows=1 width=24) (actual time=0.018..0.019 rows=1 loops=1)
  Index Cond: ((id1 = 100) AND (id2 = 200) AND (value_at = $1))
  InitPlan 2 (returns $1)
    ->  Result  (cost=0.08..0.09 rows=1 width=0) (actual time=0.013..0.013 rows=1 loops=1)
          InitPlan 1 (returns $0)
            ->  Limit  (cost=0.00..0.08 rows=1 width=8) (actual time=0.012..0.012 rows=1 loops=1)
                  ->  Index Only Scan Backward using pk_values on values  (cost=0.00..5.98 rows=74 width=8) (actual time=0.011..0.011 rows=1 loops=1)
                        Index Cond: ((id1 = 100) AND (id2 = 200) AND (value_at IS NOT NULL))
                        Heap Fetches: 0
Total runtime: 0.036 ms

Bitmap Heap Scan on values outer_v  (cost=5.05..262.46 rows=1 width=24) (actual time=0.457..0.458 rows=1 loops=1)
  Recheck Cond: ((id1 = 100) AND (id2 = 200))
  Filter: (value_at = (SubPlan 2))
  Rows Removed by Filter: 75
  ->  Bitmap Index Scan on pk_values  (cost=0.00..5.05 rows=74 width=0) (actual time=0.017..0.017 rows=76 loops=1)
        Index Cond: ((id1 = 100) AND (id2 = 200))
  SubPlan 2
    ->  Result  (cost=0.08..0.09 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=76)
          InitPlan 1 (returns $2)
            ->  Limit  (cost=0.00..0.08 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=76)
                  ->  Index Only Scan Backward using pk_values on values inner_v  (cost=0.00..6.02 rows=76 width=8) (actual time=0.003..0.003 rows=1 loops=76)
                        Index Cond: ((id1 = outer_v.id1) AND (id2 = outer_v.id2) AND (value_at IS NOT NULL))
                        Heap Fetches: 0
Total runtime: 0.478 ms

You might want to try using a window function for that:

create view vw_current_value as
select id1, id2, value from (
  select id1, id2, value,
      row_number() over (partition by id1, id2 order by value_at desc) rn
  from your_values_table
) t where t.rn = 1;

sqlfiddle

EDIT:

The above has an advantage of being standard SQL. However (thanks to @a_horse_with_no_name) Postgres also offers a proprietary alternative: DISTINCT ON(something), which reportedly performs better:

create view vw_current_value as
select distinct on (id1, id2) id1, id2, value
from your_values_table
order by id1, id2, value_at desc;

sqlfiddle 2

Comparison of the execution plans of the alternatives is left as an excercise for the reader.


SQL Server Express Setup I'm definitely a Database newbie and I'm trying to get SQL Sever Express set up so I can create and play with a database. I managed to install SQL Server Express 2012 before and It allready had the Server name populated. I was able to go in and use commands to create a database and then play with queries.

My laptop (which had it set up correctly) has since died and I need to set it up on my desktop.

Before, the server name was Name-PC/SQLEXRESS. When I try to use that this time though it acts like that is not correct.

I also installed LocalDB because I think that's what I need...

I know this might be a bit vague but I'm at a loss to how to get this set up so I can create the database.

Thanks in advance.

Open SQL Server Configuration Manager (Start > Programs > Microsoft SQL Server 2012 > Configuration Tools). Look at the SQL Server Services node; you should see something like this:

If that right pane is empty, or if you can't find Configuration Manager installed at all, then you don't have SQL Server installed. Go download the latest version (SQL Server 2014) from here:

http://www.microsoft.com/en-us/download/details.aspx?id=42299

You want ExpressAndTools 64BIT\SQLEXPRWT_x64_ENU.exe, unless you're on a 32-bit system for some reason, then you want ExpressAndTools 32BIT\SQLEXPRWT_x86_ENU.exe.

Download that, and try the setup again. You may not be able to name this instance SQLEXPRESS; that's okay, some people have many instances installed and they can't all have the same name.


What is the difference between PostgreSQL 9.0 Replication and Slony-I? Is there still any use for Slony-I, and if so, what is it? For clarification, from 9.0 Postgresql supports built in streaming replication.

This article covers it well: http://scanningpages.wordpress.com/2010/10/09/9-0-streaming-replication-vs-slony/


SQL Server : Tracking a slow running query on completion Am trying to work on script for slow-running queries. I understand that we can get the list of slow running queries using sys.dm_exec_requests. Say, I have a query Q1 which is running very slow, say, 1 hour. Till Q1 is running i.e. executing, I can track it using sys.dm_exec_requests DMV. If I understood it right, once Q1 is completed, its statistics will be no more available in this DMV. Instead, we need to look at sys.dm_exec_query_stats. But how do I get the statistics of that specific query Q1 which had been slow-running ? Meaning how do I relate the data I earlier got in sys.dm_exec_requests and sys.dm_exec_query_stats?

A quick-and-dirty way to get this information is to run something like this query while the query you wish to observe is still running.

select
    st.text,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    qs.creation_time,
    qs.*
from sys.dm_exec_query_stats qs
join sys.dm_exec_requests r
    on r.sql_handle = qs.sql_handle
cross apply sys.dm_exec_sql_text(qs.sql_handle) st

Note: this will only capture queries that are running at that moment. Additionally, the query stats DMV does not persist across reboots and may also be flushed when under pressure. Joining on sql_handle gets you query stats for that exact statement. You can find query stats for "similar" queries (similar logic with difference occurring only in literal values) by joining on query_hash.


Why can't I persist binary(4) computed column? I'm playing with storing and indexing IP addresses.  I'm starting with a simple, stupid table:
CREATE TABLE [dbo].[IP_addresses](
    [IP_as_text] [char](16) NOT NULL,
    [IP]  AS ([dbo].[fnBinaryIPv4]([IP_as_text]))
) ON [PRIMARY]

Where fnBinaryIPv4 is from https://stackoverflow.com/questions/1385552.
CREATE FUNCTION dbo.fnBinaryIPv4(@ip AS VARCHAR(15)) RETURNS BINARY(4)
AS
BEGIN
    DECLARE @bin AS BINARY(4)

    SELECT @bin = CAST( CAST( PARSENAME( @ip, 4 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ip, 3 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ip, 2 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ip, 1 ) AS INTEGER) AS BINARY(1))

    RETURN @bin
END

However, when I try to add PERSISTED to the IP column or use it in an index, I get a message that it is not deterministic.  I've Googled around a bit and that usually has to do with the style passed to CONVERT() for a date but that doesn't seem to apply here.  http://www.sql-server-helper.com/functions/system-functions/index.aspx says CAST() and PARSENAME() are deterministic so I don't see why fnBinaryIPv4() is nondeterministic.  But it turns out that PARSENAME() used to be but is no longer deterministic.  So I rewrote that function:
CREATE FUNCTION [dbo].[fnBinaryIPv4](@ip AS VARCHAR(15)) RETURNS BINARY(4)
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @int_addr AS bigint = 0;
    DECLARE @b CHAR(3);

    DECLARE bCursor CURSOR FOR (
        SELECT value FROM STRING_SPLIT(@ip, '.')
    )

    OPEN bCursor

    FETCH NEXT FROM bCursor INTO @b
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT @int_addr = (@int_addr * 256) + CAST(@b AS INTEGER)
        FETCH NEXT FROM bCursor INTO @b
    END

    CLOSE bCursor
    DEALLOCATE bCursor

    RETURN CAST(@int_addr AS BINARY(4))
END

But this version is still nondeterministic.

I'm not sure what "http://www.sql-server-helper.com/" is, but this is from the current, official documentation:

The following built-in functions from other categories are always nondeterministic.
...
PARSENAME

Side note: it looks like PARSENAME was deterministic, at least on SQL Server 2005 (thanks for that link, jpa).  So perhaps that other site just has outdated information on it.
You would need to re-implement this function using only deterministic functions (for instance, SUBSTRING and CHARINDEX).
Just to show that it can be deterministic (note that this implementation doesn't deal with general IP addresses, it's just an example):
CREATE OR ALTER FUNCTION dbo.fnBinaryIPv4(@ip AS VARCHAR(15)) RETURNS BINARY(4)
AS
BEGIN
    DECLARE @bin AS BINARY(4)

    SELECT @bin = CAST( CAST( PARSENAME( @ip, 4 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ip, 3 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ip, 2 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ip, 1 ) AS INTEGER) AS BINARY(1))

    RETURN @bin
END
GO

SELECT OBJECTPROPERTY(OBJECT_ID('dbo.fnBinaryIPv4'), 'IsDeterministic') AS IsDeterministic;
GO

CREATE OR ALTER FUNCTION dbo.fnBinaryIPv4(@ip AS VARCHAR(15)) RETURNS BINARY(4)
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @bin AS BINARY(4)

    SELECT @bin = CAST( CAST( SUBSTRING( @ip, 0, 3 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( SUBSTRING( @ip, 4, 3 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( SUBSTRING( @ip, 8, 3 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( SUBSTRING( @ip, 12, 3 ) AS INTEGER) AS BINARY(1))

    RETURN @bin
END
GO

SELECT OBJECTPROPERTY(OBJECT_ID('dbo.fnBinaryIPv4'), 'IsDeterministic') AS IsDeterministic;
GO


Question on datatypes for photo app I am working on a relational database design for a photo organising app, and as I have mentioned before, am very new to databases. I need to include exposure, aperture value and focal length. The values I have been provided are:
Exposure: typically represented in seconds, using, e.g. 1/1000 for fast exposure and 20 for very slow;
Aperture: ranging from e.g. 1.1 up to values such as 45;
Focal Length (mm): ranges from about 8 to 1200.
Would you recommend representing these as enums with a range of values, or rather, for example varchar for exposure, text for aperture and int for focal length (I am using MySQL)?
I still am not 100% clear on why you would use which datatypes.
Thanks again for your insight and advice.

you can use double/float for both exposure and aperture because they carry floating points and int/smallint for focal length. you can use varchar for all this but if you need to do any calculation on these that's not good and when you sort you can't get correct out put you wanted because sever treat these values as strings (if use varchar). in my case i always use numeric data types for numbers.


How to set bytea_output to hex? I cannot seem to change bytea_output to hex.

I have tried this method:

ALTER DATABASE dbname SET bytea_output TO 'hex';

I have tried to edit postgresql.conf

bytea_output = 'hex'

and restarted the server.

I have tried

SET bytea_output = 'hex'

and it works per transaction, but that is very tedious.

However, whenever I browse my bytea columns in phpPgAdmin, the columns are outputted escaped.

This

SELECT setting FROM pg_settings WHERE name = 'bytea_output'

returns escape.

How can bytea_output be set permanently to hex?

Sounds like PhpPgAdmin is probably doing a session-level SET bytea_output = 'escape'.

If you open a new psql session and SHOW bytea_output; it'll be hex there.

PhpPgAdmin may offer a way to control this behaviour, or it might be hard-coded to work around compatibility issues (if it doesn't understand the hex format for bytea).


What's the fastest way to replicate data? I have a mongodb database data on my computer. I want to upload that into amazon.com server.

The bottle neck is the internet connection.

One way is to zip the file and then upload it to the server. But what about if after that
I want to synchronize the server data with current data?

Another is to set the mongo in server as slave of the data in my computer. However, my computer is a home computer that connect to the internet via router. So how do the amazon server knows the IP of my computer? The IP is fe80::6df8:c5f9:bdb5:b9e2%15

It seems that it's the IPv6 IP. I do not know the IP4 IP.

So how do I turn a server in my computer to become master? How do I set the router to pass on connection?

Then there is mongodump. Is it fast?

Which one do you usually use?

First, let me say that I would not recommend doing this, ever.  I would recommend running your database on EC2 and having it live there, only.  Have a test database on your local machine for convenience, but keeping the two in sync is going to be painful, as you will see from the answers below.

  One way is to zip the file and then upload it to the server. But what
  about if after that I want to synchronize the server data with current
  data?

You either need to add that EC2 based server as a secondary, so that it syncs from your home machine (likely to be unreliable) and would require that you have an appropriate set up to route traffic), or you need to replay the oplog manually from your home machine on the EC2 machine (basically how replication functions).  You can write your own program to do so, or in 2.2 you have the option of the mongooplog tool

  So how do the amazon server knows the IP of my computer?
  So how do I turn a server in my computer to become master? How do I
  set the router to pass on connection?

It may not be able to route to your computer, you may have only an IPV6 address, but I doubt  it.  Go here:

http://www.whatsmyip.org/

That will tell you what your publicly viewable IP address is, and then you can try connecting to it from EC2 to see if it works.  It may not - ISPs will often block listening ports and have other firewalls in effect to prevent this.  As for how to pass on connections through a rotuer etc. that topic is large and beyond the scope of the DBA site realistically.  You should try SuperUser for a detailed answer on that front, and be sure to include your router model when asking.

  Then there is mongodump. Is it fast?

Mongodump is as fast as your database basically - it has to walk an index (usually _id), write out each document it finds as BSON.  So it will be limited by how quickly your machine can do that.  It is not likely to be as fast as a simple file based copy.

  Which one do you usually use?

I doubt anyone runs in this manner for very long - it will be too prone to failure.  I would imagine that most will do one of the above methods once (or at least very infrequently), to move their DB into EC2 or elsewhere on the cloud, then just use it there, rather than trying to keep the two in sync as part of a replica set.


How can I override the MySQL DELETE command to update a field rather than to remove the row? I wonder whether it's possible to override the functionality of MySQL's DELETE command to update a field on the selected row rather than to delete it completely.

This would make it possible to set a flag, for example deleted, from 0 / null to 1, without using UPDATE.

Is it possible to do, and if so how?

No, this isn't possible.  You can block deletions with triggers or (where appropriate) foreign key constraints, but you can't make DELETE do anything other than delete.

It isn't more semantically correct -- you are updating a row, not deleting a row.  DELETE means remove 0 or more rows.

If you really want to use the word "delete," create a stored procedure with an appropriate name that accepts the primary key as its argument.

To prevent deletions entirely, use a simple trigger like this:

CREATE TRIGGER t1_bd
BEFORE DELETE ON t1 -- table name
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'table t1 does not support deletion';

This will immediately terminate the query and prevent the deletion.

The customary DELIMITER statements and BEGIN/END block are not required when a trigger contains a single non-complex statement in its body, like the one above.

SQLSTATE 45000 means unhandled user-defined exception.