Postgres - Return default value if regex match fails I'd like to attempt a regex match, and return null if it fails.

The following query attempts to find the first number in a string. The result ignores the entry with text 'blah'. I'd prefer it to return a null value instead.

This question is probably less about regex and more about set algebra. My hunch is that there is an elegant way to do it, without needing to left join anything, though googling is proving futile.

with test_data as (
  select 'abc 123' as txt
  union
  select 'abc 456' as txt
  union
  select 'blah' as txt
)

select
  txt,
  (regexp_matches(txt, '\d+'))[1] as first_num
from
  test_data

While your answer solves the problem with regexp_matches(), the much better solution is to avoid the problem with the more appropriate function substring() to begin with:

WITH test_data(txt) AS (
   VALUES
      (text 'abc 123')
    , ('abc 456')
    , ('blah')
   )
SELECT txt, substring(txt FROM '\d+') AS first_num
FROM   test_data;

Does exactly what you need: returns the first match in the string or NULL for no match.

substring(string FROM pattern) is standard SQL syntax.
Don't confuse it with substring(string [FROM int] [FOR int]) or substring(string FROM pattern FOR escape) doing something else.

You can also use the shorter, equivalent, currently undocumented Postgres implementation substring(text, text) (with comma (,) instead of FROM).

Use regexp_matches() to return all matches as set. (Note that a single regexp can result in multiple matches, hence a set of text arrays). You still only get the first row unless you add the flag 'g' to get all rows (additional matches - if any - for the same regexp). Related:

Reverse Byte-Order of a postgres bytea field

To avoid complications move set-returning functions to the FROM list in versions before Postgres 10:

What is the expected behaviour for multiple set-returning functions in select clause?

Or use regexp_match() in Postgres 10 or later to only return the first row. This would also not exhibit the original problem:

WITH test_data(txt) AS (
   VALUES
      (text 'abc 123')
    , ('abc 456')
    , ('blah')
   )
SELECT txt, (regexp_match(txt, '\d+'))[1] AS first_num
from   test_data;

But just use substring() for your purpose.

db<>fiddle here


Unable to connect SQL Server 2008 from SQL Server 2005 DB I have SQL Server 2005 on my work station and on my Server, we have SQL Server 2008. When I tried to connect to SQL Server 2008 from my SQL Server 2005 edition, I am receiving the following error:

  This version of Microsoft SQL Server Management Studio can only be
  used to connect to SQL Server 2000 and SQL Server 2005 servers.

Without installing SQL Server 2008 on my workstation, is there any way for me to connect to SQL Server 2008 from SQL Server 2005 ?

This is a known issue.  Please see the following KB article for a resolution: FIX: You may experience problems when you use SQL Server Management Studio in SQL Server 2005 to connect to an instance of SQL Server 2008


Custom personal monitor sql by writing the .psqlrc file I want to custom my personaly monitor sql  by writing the  .psqlrc file ,  but I encounter some problems .

--tablespace

skypcsuit=> \db
                          List of tablespaces
       Name        |  Owner   |                Location
-------------------+----------+-----------------------------------------
 pg_default        | postgres |
 pg_global         | postgres |
 tbs_db_francs     | postgres | /database/pg92/pg_tbs/tbs_db_francs
 tbs_skypcsuit     | postgres | /database/pg92/pg_tbs/tbs_skypcsuit
 tbs_skypcsuit_idx | postgres | /database/pg92/pg_tbs/tbs_skypcsuit_idx

--.psqlrc

\set top10_ts_table 'select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner from pg_class a, pg_tablespace tb  where a.relkind in (\'r\', \'i\') and a.reltablespace=tb.oid and tb.spcname=\':v_spcname\' order by a.relpages desc limit 10;'

--call the sql but return null value

skypcsuit=> \set v_spcname tbs_skypcsuit_idx

skypcsuit=> \echo :v_spcname
tbs_skypcsuit_idx

skypcsuit=> :top10_ts_table
 relname | relkind | relpages | pg_size_pretty | reltablespace | relowner
---------+---------+----------+----------------+---------------+----------
(0 rows)

I set the the value of variable v_spcname to 'tbs_skypcsuit_idx'。

-- csvlog

2014-07-24 01:10:00.091 PDT,"skypcsuit","skypcsuit",2123,"[local]",53d0bf4c.84b,3,"idle",2014-07-24 01:09:48 PDT,2/94,0,LOG,00000,"statement: select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner from pg_class a, pg_tablespace tb  where a.relkind in ('r', 'i') and a.reltablespace=tb.oid and tb.spcname=':v_spcname' order by a.relpages desc limit 10;",,,,,,,,,"psql"

From the log we can see that the values of variable v_spcname did not pass to the sql.   any one know that ?

Using psql variables with quotes is a bit tricky.  As you noticed, they don't get substituted in certain positions, among other, between quotes.

My only solution to this so far has been including the quote in the variable itself.  This is also not very straightforward:

test=# \set a 'parrot'
test=# \echo :a
parrot
test=# \set a ''parrot''
test=# \echo :a
parrot
test=# \set a '''parrot'''
test=# \echo :a
'parrot'
test=# \set a ''''parrot''''
test=# \echo :a
'parrot'

and so on.  This is, to my eyes, is basically the same as the quote duplication magic elsewhere (mentioned more than once in the documentation).

The solution I've posted in the comment is basically an ugly version of the three-quote version.

Note that dollar quoting does not work here:

test=# \set a $$'parrot'$$
test=# \echo :a
$$parrot$$
test=# \set a '$$parrot$$'
test=# \echo :a
$$parrot$$


Changing column type - view doesn't change I noticed that the view column data type doesn't change when I change the datatype of the underlying table. Are there any extra steps I need to take?

Here is an example:

CREATE TABLE test ( amount NUMBER(10,2) );
CREATE VIEW test_vw AS SELECT * FROM test;

SELECT table_name, column_name, data_type, data_precision, data_scale
FROM user_tab_columns
WHERE table_name = 'TEST_VW';

ALTER TABLE test MODIFY ( amount  NUMBER(19,2) );

SELECT table_name, column_name, data_type, data_precision, data_scale
FROM user_tab_columns
WHERE table_name = 'TEST_VW';

Both queries return the same result:

TABLE_NAME COLUMN_NAME DATA_TYPE DATA_PRECISION DATA_SCALE
---------- ----------- --------- -------------- ----------
TEST_VW    AMOUNT      NUMBER                10          2

Dependent objects are invalidated when their parent changes:

select status from user_objects where object_name = 'TEST_VW';
-------
INVALID

Invalid objects are automatically validated when they are next used:

select * from test_vw;

SELECT table_name, column_name, data_type, data_precision, data_scale
FROM user_tab_columns
WHERE table_name = 'TEST_VW';

TABLE_NAME COLUMN_NAME DATA_TYPE DATA_PRECISION DATA_SCALE
---------- ----------- --------- -------------- ----------
TEST_VW    AMOUNT      NUMBER                19          2

Alternatively, you can recompile the object yourself:

alter view test_vw compile;

It is worth noting that no data or precision is lost after modifying the table, but before re-validation:

SELECT table_name, column_name, data_type, data_precision, data_scale
FROM user_tab_columns
WHERE table_name = 'TEST_VW';

TABLE_NAME COLUMN_NAME DATA_TYPE DATA_PRECISION DATA_SCALE
---------- ----------- --------- -------------- ----------
TEST_VW    AMOUNT      NUMBER                10          2

insert into test (amount) values (123456789012345);

select status from user_objects where object_name = 'TEST_VW';
-------
INVALID

select * from test_vw;
---------------
123456789012345

select status from user_objects where object_name = 'TEST_VW';
-------
VALID


MySQL - Autoincrement does not increment sequentally if last row was deleted I have a table which contains an auto incremented primary key id. If I delete the last row (highest id, for exampe id = 6) and insert a new row, the new id starts at 7. Which paramater I have to change that the primary key starts at 6?

CREATE TABLE animals (
 id MEDIUMINT NOT NULL AUTO_INCREMENT,
 name CHAR(30) NOT NULL,
 PRIMARY KEY (id)
) ENGINE=MyISAM;

INSERT INTO animals (name) VALUES
('dog'),('cat'),('penguin'),
('lax'),('whale'),('ostrich');

Result:
id  name
1   dog
2   cat
3   penguin
4   lax
5   whale
6   ostrich

DELETE FROM animals WHERE id = 6;
INSERT INTO animals (name) VALUES
('x');

Result:
id  name
1   dog
2   cat
3   penguin
4   lax
5   whale
7   x

Thanks for advice.

This is by design - all DBMS act this was with auto-increment columns.

If they did not external referential integrity could be damaged. For a simple example of this, imagine you are storing URLs for a shortening service using an auto-increment column as the key. You don't know if the shortened URL has been given out to anyone yet, and the database certainly doesn't, so reusing ID 1234 could result in someone's poor granny visiting somenastypornsite.xxx instead of loverlyknitting.org when she clicks http://shortthi.ng/1234 in an old email, instead of getting a "sorry but this link no longer exists in our records" message.

Also if you reset the increment after deleting the last item, will you also be going through all the work (or expecting the database to) of renumbering everything after the 5th item of 5 million when the 5th item is removed? complete with changes to other tables where their are foreign key constraints pointing at the increment column? Such extra work could get very expensive IO-wise.

If you do reset the increment point after deleting the last item, do be very very careful of your transaction isolation levels: You might reset it just as another transaction makes use of the value, leading to errors (or worse, silent failures), unless you make sure that your action is fully 100% isolated.

I generally recommend people working with databases read "SQL Antipatterns" which has a chapter on this issue named "Psuedo-Key Neat Freaks" (which covers the matter in a friendlier manner than the chapter title may imply to some!). Essentially, if the value has meaning beyond being a key (or at most carrying insert order information) then it probably should not be an auto-incrementing column, and if it does have no meaning beyond being a key (or at most carrying insert order information) then gaps should not matter.


SSIS: Move data between servers - will data travel through my workstation? I have just installed the Microsoft SQL Server Data Tools for Visual Studio 2017 on my workstation and created a simple SSIS package (.dtsx) which basically copies data from one MSSQL server to another.

Now I am wondering if the data will travel through my workstation or if there is direct connection established between the two servers. Thanks in advance!

The data will travel through where the SSIS package is running. In dev this will be your workstation, once deployed this will be where ever your package(s)/project were deployed to. SSIS will not produce a direct connection as you describe between two data sources of any type

This is true even if there is no other processing involved beyond "read from here, write to there", and there are practical and security reasons for this. Practical reasons include mainly the fact that most data sources/sinks don't support remote connections to other resources like that, and the security concern would be that even if SSIS could instruct a data sink to directly talk to a data source (or vice versa) it would have to hand over security credentials in order for it to know how to.

Of course if your SSIS packages are deployed on one of the servers in question then there is effectively a direct connection, but the data will still go through the SSIS process rather than being sent directly to the SQL Server instance. Furthermore, even if you are moving data between tables in the same database (so the same database on the same SQL instance is both the source and destination of an ETL process) the data will still leave SQL Server, be processed by SSIS (however minimally) then sent back to SQL server.

If you need direct communication between SQL Servers then you can use the linked server feature to read/write data directly, but this would not allow SSIS's other processing features. Also this will not work on Azure SQL. There is a per table/view option that will work on Azure SQL (and newer on-prem SQL instances) in external data sources. Both these options require at least one of the SQL instances to have credentials for accessing the other, which may be undesirable depending on your situation and security model.


efficient way to poll database? I have a Linux CentOS server hosting an Oracle 11.2 database, which communicates with an application server, which communicates with a client (a Flex application). I'll try to describe the pseudo-code I want to implement as a process as follows:

The application server has a Java program that acts like a database front end for clients. Basically, a client wishes to do some number crunching, submits some data to the Java tier, and the Java tier saves this data to the database, along with a flag used to instruct a C program that data is waiting.

The application server also has this C program on it. The C program polls the database for this flag every 1 second or so, and when the C program detects this flag to be set, the C program downloads the data using OCILIB (e.g. OCI) from the database, crunches the math, and saves a result back into the database. The C program then sets a different flag to instruct the Java tier this processing is done.

Another program in the Java tier is watching this flag that the C program will set. When it detects the flag is set, the Java tier sends a message to the client telling the client that the data is available in the database.

The client then downloads the data from the database by sending an appropriate request to the first Java program discussed above.

[A simpler approach may be to have the java tier call the C program directly (to avoid all of this polling the database, etc.), but the required JNI programming along with java queuing and multi-threading is beyond my capability as a programmer.]

QUESTIONS:

Is having 2 processes poll the database fairly common in the industry (and nothing to worry about), or is it a bad practice?

Is there a way, for example (in SQL or PL/SQL), to avoid polling by having the database call a function (e.g. C or Java) when the contents of a cell change to become a certain value (e.g. 0 = wait, 1 = call the function)?

Is the simplistic approach above reasonable or flawed somehow? Is there a better, although still straightforward, way of achieving the same result? Anything to watch out for?

If you must poll, see this article by a former colleague of mine.

  Most places I’ve worked at have had some sort of requirement for
  picking up new or changed rows from a table in the database. It’s only
  recently that I’ve found, what I would consider to be, a clean way of
  doing this.

He is particularly interested in capturing a stream of changes, but your use case sounds like you want to do a form of cross-language IPC via the database. I personally do not like polling; it's inelegant. There are a few approaches for this:

AQ. I link to the 9i docs so you can see that this is a well-established feature of Oracle. This is my own favourite technique for this kind of application. One interesting thing about AQ is that you might not even need to tell your app to wake up and fetch the data; you could send the data in the payload!
DCN. This is very straightforward to use from OCI, but I have never tried it with Java so can't comment.
DBMS_ALERT, as Leigh mentions
DBMS_PIPE a similar mechanism
Direct calls from PL/SQL into C using EXTPROC. From C you can call any other language, including Java. From that link there is some sample code on Github for doing this; it's actually quite straightforward.
If you want to get really exotic, you could do this with Oracle Coherence - I do this via C in my example, there is also a Java binding.


Is DBCC SHOWFILESTATS an accurate way to see unused space? We have a few tables each with near 100 million rows of data.  A co-worker has these tables setup with partitions based on a date field.

Our disk space is starting to run low and knowing that partitions have their own file group, I wanted to see if some of the partitions were larger than they needed to be and were chewing up disk.

I ran the poorly documented command:

dbcc showfilestats

and here is a snippet of the output:

TotalExtents   UsedExtents   Name         FileName
363200         200701        LogJun2013   F:\SQL\DATA\logs_phyJun2013.ndf
812800         432127        LogJul2013   F:\SQL\DATA\logs_phyJul2013.ndf
718400         401500        LogAug2013   F:\SQL\DATA\logs_phyAug2013.ndf
1016983        555565        LogSep2013   F:\SQL\DATA\logs_phySep2013.ndf

If I am understanding this correctly (IANADBA), there is a lot of unused space in these files given the high number of unused extents.  In addition, we only collect new data so we will never have a need to grow these old partitions from 2013.

Is my understanding of this correct?  If so, how can I go about freeing up the space from these files?

You can use sys.dm_db_index_physical_stats to identify the size of your partitions and how much space is being used.  Use the page_count field to see the size of your partition.  If you perform a SAMPLED or DETAILED scan, you will also get a value for avg_page_space_used_in_percent, which will allow you to estimate how much of the space in the partition is actually being used.

SELECT
    object_name(di.object_id) [table_name]
    ,i.name
    ,di.index_id
    ,di.partition_number
    ,di.record_count
    ,di.page_count/128.0 [partition_size_MB]
    ,di.avg_page_space_used_in_percent
    ,(di.page_count/128.0) * di.avg_page_space_used_in_percent * .01 [space_used_MB]
FROM
    sys.indexes i
    join sys.dm_db_index_physical_stats(DB_ID(),object_id('<Table Name'>), NULL, NULL, 'SAMPLED') di
      on i.object_id = di.object_id and i.index_id = di.index_id

If you want to check the free space in the actual files themselves, I recommend using a query from Glenn Berry's DMV script.

SELECT f.name AS [File Name] , f.physical_name AS [Physical Name],
CAST((f.size/128.0) AS DECIMAL(15,2)) AS [Total Size in MB],
CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS DECIMAL(15,2))
AS [Available Space In MB], [file_id], fg.name AS [Filegroup Name]
FROM sys.database_files AS f WITH (NOLOCK)
LEFT OUTER JOIN sys.data_spaces AS fg WITH (NOLOCK)
ON f.data_space_id = fg.data_space_id OPTION (RECOMPILE);

This should give you a rough idea of whether or not you have any space to reclaim.  As for actually reclaiming it, it depends on what you need to do.  If your files are simply over provisioned, you can shrink them using DBCC SHRINKFILE (this is not something that should be done regularly).  If, however, the files are mostly full but you have free space within the partitions, try rebuilding the indexes to reclaim some of the space (likely lost due to fragmentation).  If the disk is full and there isn't space in the partitions, consider archiving data off to another disk system or to offline storage.

That all being said, 100 million rows really isn't that much data and I'm curious as to why partitioning was even chosen as a solution.  It's a lot of overhead without a lot of benefit to you.


Update trigger fired before insert trigger upon insert of new row I have a table FOO and 2 triggers defined as follows

CREATE TRIGGER [dbo].[FOO_UPDATED]
   ON [dbo].[FOO]
   AFTER UPDATE
AS
BEGIN
   ...
END

and

CREATE TRIGGER [dbo].[FOO_INSERTED]
   ON [dbo].[FOO]
   AFTER INSERT
AS
BEGIN
   ...
END

The triggers were working fine on SQL Server 2012 - FOO_INSERTED was firing upon insert and FOO_UPDATED was firing upon update.

But now I migrated the database on SQL Server 2016 (no change in the structure and data, simple backup and restore). Upon insert of 1 new row in FOO, both triggers fire in order: first FOO_UPDATED, then FOO_INSERTED.

I've looked around for similar problems and found only that one topic: SQL Server: update trigger fires before insert trigger

Does anyone have an idea what could be the reason for this strange behavior?

Solved: I have update statements for the same table inside the body of the INSERT trigger. Nested triggers are disabled on my database on SQL Server 2012 but after the restore on SQL Server 2016, the setting was somehow enabled.

Edit: It appears that the 'nested triggers' setting of the SQL Server instance was set to 'False' on SQL12 and to 'True' on SQL16.


How to filter hstore where values are not null in a trigger? The easiest way to explain the question is in the usage of an audit trigger.

However, on insert it saves all values, including null values. I want to filter out the null values in the hstore(NEW.*). What is the simplest / fastest way to do this?

Source: https://github.com/2ndQuadrant/audit-trigger/blob/master/audit.sql#L134

audit_row.row_data = hstore(NEW.*) - excluded_cols;

This obviously doesn't work but explains, hopefully, what the answer will look like.

audit_row.row_data = hstore(hstore(NEW.*) FILTER (WHERE value IS NOT NULL)) - excluded_cols;

Preference will go to answer that doesn't use an user created function, e.g. use only Postgres functions / operators.

This is for >= PostgreSQL 11.

I am looking for a solution with hstore, not json or jsonb. Alternative ways with json or jsonb may be included, but hstore is preferred.

With only built-in tools of Postgres and the additional module hstore, without involving jsonb (as requested):
audit_row.row_data = hstore(
   ARRAY (
      SELECT ARRAY[key, value]
      FROM   each(hstore(NEW.*) - excluded_cols)
      WHERE  value IS NOT NULL
      )
   );

One of the overloaded variants of the hstore() functions takes a two-dimensional array.
Alternatively, you could eliminate key names listed in excluded_cols in the WHERE clause (while processing unnested keys anyway):
audit_row.row_data = hstore(
   ARRAY (
      SELECT ARRAY[key, value]
      FROM   each(hstore(NEW.*))
      WHERE  value IS NOT NULL
      AND    key <> ANY (excluded_cols)
      )
   );

Might be a bit faster, not sure. Probably not much difference.
Also not sure how jsonbandjsonb_strip_nulls()` (suggested in comments) perform in comparison. Can you report back with a performance comparison?


Audit rows deleted using SSMS GUI? I found the following query from this question which appears to show row deletions from a specific table:
DECLARE @TableName sysname
SET @TableName = 'dbo.ObjectInstances'

SELECT
    u.[name] AS UserName
    , l.[Begin Time] AS TransactionStartTime
FROM
    fn_dblog(NULL, NULL) l
INNER JOIN
    (
    SELECT
        [Transaction ID]
    FROM
        fn_dblog(NULL, NULL)
    WHERE
        AllocUnitName LIKE @TableName + '%'
    AND
        Operation = 'LOP_DELETE_ROWS'
    ) deletes
ON  deletes.[Transaction ID] = l.[Transaction ID]
INNER JOIN
    sysusers u
ON  u.[sid] = l.[Transaction SID]

The results all show the same username, which is the username we use from our app to connect to the database. However, I had just deleted a row using SSMS, while logged in using Windows Authentication.  This record is not shown in the results from the query above.
How can I view an audit of rows/records deleted using SSMS (right-click, delete)?

In general you should use sys.database_principals instead of sysusers, which was deprecated in 2005 (just look at the big warning in the docs). But in this case, you can do this without joining to anything - SUSER_SNAME() against the sid retrieved the Windows username for me both writing a proper DELETE query and also when using the GUI as a crutch:
DECLARE @TableName sysname = N'dbo.ObjectInstances';

SELECT UserName = SUSER_SNAME(l.[Transaction SID]), l.[Begin Time]
FROM sys.fn_dblog(NULL, NULL) AS l
INNER JOIN
(
    SELECT [Transaction ID]
    FROM sys.fn_dblog(NULL, NULL)
    WHERE AllocUnitName LIKE @TableName + N'%'
    AND Operation = N'LOP_DELETE_ROWS'
) AS deletes
ON  deletes.[Transaction ID] = l.[Transaction ID]
AND l.Operation = N'LOP_BEGIN_XACT';

You may also want to consider joining a 3rd time to be sure the sequence was finalized with a LOP_COMMIT_XACT (in other words, maybe you don't need to audit transactions that are still active or were rolled back, and this would also give you a better idea of when the change was actually committed vs. when they started thinking about making a change).
And just as an aside:

Ladies and gentlemen, start your semi-colons!


how to get back the modified INOUT parameter in postgres I have two functions with a similar signature, lets say:

CREATE FUNCTION func1(INOUT new "My_Table") AS $$ ... $$ LANGUAGE plpgsql;
CREATE FUNCTION func2(INOUT new "My_Table") AS $$ ... $$ LANGUAGE plpgsql;

I call both of these functions from a BEFORE UPDATE TRIGGER where the parameter is the row being updated. I want to give the row to the function as an input but subsequently modify it inside the function (therefore the INOUT).

I call the first function as:

SELECT * FROM func1(NEW) INTO NEW;

Which works just fine.

However, I can't make it work with the second function because the type of NEW is no longer of "My_Table" but it is "record" and I am unable to cast it.

SELECT * FROM func1(NEW) INTO NEW;

or

SELECT * FROM func1(NEW::"My_Table") INTO NEW;

or

SELECT * FROM func1(CAST(NEW AS "My_Table")) INTO NEW;

gives

ERROR:  42846: cannot cast type record to "My_Table"

How can I do this please?

These are the two working solutions:

NEW := func1(NEW);

or first cast the record to text:

SELECT * FROM func1(NEW::text::"My_Table") INTO NEW;


How to optimize a fetch into a crosstab I have a schema like:

[ad_id] .  [name] . [valueofname]
 1 .        name .    "brian"
 1 .        age  .    "23"
 2 .        job  .    "IT"
 2 .        name .    "Jack"

The row name contains multiple values: age, name, birthday, job, and I'd like to convert it into this:

[ad_id] .      [name]  .       [age] .              [birthday] .    [job]
[valueofad_id][valueofname] [valueofnameofage] [valueofnameofbirth] [valueofnameofjob]

Here is the query that I made; it's working but it's very slow because it is trying to make for each column its own table and JOIN it.

Do you have any suggestion or insights please? I am dealing with huge data volumes and trying to optimize fetching.

select users.ad_id,name.valueofname as name,
        age.valueofname as age,
        birthday.valueofname as birthday,
        job.valueofname as job
from users
left join users_details as name on (users.ad_id=name.ad_id)
left join users_details as age on (users.ad_id=age.ad_id)
left join users_details as job on (users.ad_id=job.ad_id)
left join users_details as birthday on (users.ad_id=birthday.ad_id)
group by ad_id,name.valueofname,age.valueofname,birthday.valueofname;

SELECT *
FROM   crosstab(
       'SELECT ad_id, d.name, d.valueofname
        FROM   users
        LEFT   JOIN users_details d USING (ad_id)
        ORDER  BY 1'

    , $$VALUES ('name'), ('age'), ('birthday'), ('job')$$
   ) AS ct (ad_id int, name text, age text, birthday text, job text);

The LEFT JOIN to keep users without any data in the result. If that never occurs or is not desirable, remove the table users from the query completely - it has no other use here.

Detailed explanation for crosstab():

PostgreSQL Crosstab Query

The major advantage over alternative solutions is top performance, which seems to be your prime objective.


Can't create read-write Postgresql user I'm trying to create two users in a Postgres database—one with read-write access to all tables in two schemas, and one with read-write-create (i.e., able to make DDL changes) to the same schemas.

I currently have these statements.

CREATE SCHEMA
    schema_a;

CREATE SCHEMA
    schema_b;

CREATE ROLE read;
CREATE ROLE read_write;
CREATE ROLE read_write_create;

GRANT USAGE ON SCHEMA schema_a, schema_b TO read;
GRANT SELECT ON ALL TABLES IN SCHEMA schema_a, schema_b TO read;

GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA schema_a, schema_b TO read_write;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA schema_a, schema_b TO read_write;

GRANT ALL ON SCHEMA schema_a, schema_b TO read_write_create;

GRANT read to read_write;
GRANT read_write to read_write_create;

ALTER DEFAULT PRIVILEGES IN SCHEMA schema_a, schema_b GRANT SELECT ON TABLES TO read;
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_a, schema_b GRANT SELECT ON SEQUENCES TO read;
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_a, schema_b GRANT INSERT, UPDATE, DELETE ON TABLES TO read_write;
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_a, schema_b GRANT SELECT, UPDATE ON SEQUENCES TO read_write;

CREATE USER
    read_write_user
WITH PASSWORD
    'a_password';

GRANT read_write TO read_write_user;

CREATE USER
    read_write_create_user
WITH PASSWORD
    'another_password';

GRANT read_write_create TO read_write_create_user;

After running these, read_write_create_user can create tables, read from, and write to all tables. But read_write_user can't read or write to any tables created by read_write_create_user.

What am I doing wrong?

This is because the tables in either schema are created by a different role (I assume read_write_create_user) than the one the default privileges were defined for (which is you, or the role that run the above set of statements).

You'll find a bit more explanation in my older answer.


What are the minimum requirements for running a dtsx package on a PC? One of my clients has some very specific needs to get a spreadsheet run through some ETL processes I have created.

I have developed an SSIS .dtsx package which imports an Excel spreadsheet into SQL Server and then performs some transformation processes and then exports the results to another Excel spreadsheet. I am wanting to hand off the tool to the client so they can kick off the ETL process ad-hoc. In order to allow them to run the DTSX package, what are the minimum requirements that are needed to get this running on their system? Can this be done with SQL Express or do they need to have Integration Services running to execute the package?

You have delivered a solution that uses SSIS. SSIS requires an installation of SQL Server Standard, BI, or Enterprise Edition (depending on components used).

At this point, your client will need to spend ~ 8k per core (SE) to 23k per core (EE) for a license of SQL Server for their desktop. That's probably not what they're expecting.

What can you do?

Assuming they have SQL Server installed in their enterprise, it should be a matter of deploying to somewhere that instance can access and educating the client on where they need to copy the source spreadsheet to and where they can pick up the output. I'm fond of my ETL file processing pattern but go with whatever works best for you and your client.

That addresses getting the package so it can run with the right input. Now you need to figure out how the client can call it. I would create a SQL Agent job with no predefined schedule and then the world's tiniest application that simply calls EXECUTE msdb.dbo.sp_start_job 'ExcelProcessor' That'll be an asynchronous process so you'd then want to do something like poll the output folder until a file appears or monitor the job table to see whether the job completes successfully before signalling the user that processing is complete.


Does it matter to convert integer primary key into equivalent string? In a PostgreSQL DB, I have a sortable bigint primary key, which is a bit verbose in terms of readability.
I want to encode it as base36 or base64, like using char(n). In theory the number of bytes should be the same as before, so it does not take more space. Is that correct?
Are there other concerns? For example sorting, collation?

There are potential performance differences and sorting differences (collation dependent) per this DBA.StackExchange answer and it's linked answers such as this one. I highly recommend reading through all the information referenced in that answer.
These sections of the above linked articles are specifically relevant:

There is no performance difference among these three types [char,
varchar, text]...

Short answer: integer is faster than varchar or text in every aspect.

Therefore integer (and BIGINT) is more performant of a data type, even of equal data length.


What sort of SPN / AD Computer Delegation do you need for delegation of credentials to a linked server that connects to a file share? The idea here is to perform a double hop and delegate the credentials of the client (leeand00@example.org), that have been sent to the SQL2.example.org server to SQL3.example.org (admittedly this is an SMB file share and not an SQL Server)

My belief is that I need to delegate the credentials in order to access the file share, since the error message that I get when trying to access a VIEW on the SQL2 server which pulls from a Linked Server using the VFPOLEDB Provider which points to the SMB Share on SQL3 when connecting with a client using a KERBEROS auth_scheme without the SPNs setup is:

OLE DB provider "VFPOLEDB" for linked server "LINKEDSERVERNAME" returned message "Invalid path or file name.".
Msg 7303, Level 16, State 1, Procedure hatsum, Line 5
Cannot initialize the data source object of OLE DB provider "VFPOLEDB" for linked server "LINKEDSERVERNAME".

So this leads me to believe that an SPN on the SQL2 and SQL3 computers on the correct port will delegate the client credentials to the SMB Share.  (Using Constrained Delegation of course...)

Any idea what sort of SPN / AD Computer Delegation settings I need to pull this off?

Please note that when connecting using any other sort of auth_scheme, be that NTLM or SQL, I am able to query the Linked Server without issue; but when using the KERBEROS auth_scheme it yields the error message above.

I also know I can list the SPNs that are already set using setspn -l <server-name>

There is only a single kind of Service Principal Name you need for SQL Server.

MSSQLSvc/<servername>:<portname>

It is typically necessary to implement this twice, one for the short name, and one for the long name.   If, for instance, the SQL Server is named "SQLServer1.somedomain.com" and was running on port 1433, then you'd need:

MSSQLSvc/SQLServer1.somedomain.com:1433
MSSQLSvc/SQLServer1:1433

The computer account for SQLServer1.somedomain.com in Active Directory should be granted the ability to delegate security.  Once that is working correctly, you can confirm Kerberos authentication is working by looking at:

SELECT InstanceName = @@SERVERNAME
    , InstancePortNumber = dec.local_tcp_port
    , dec.auth_scheme
FROM sys.dm_exec_connections dec
WHERE dec.session_id = @@SPID;

If this shows Kerberos in the auth_scheme column, then Kerberos authentication is working.  If you have a second SQL Server at the other end of a linked server, you can run that query over the linked server:

DECLARE @cmd NVARCHAR(MAX);
SET @cmd = 'SELECT InstanceName = @@SERVERNAME
    , InstancePortNumber = dec.local_tcp_port
    , dec.auth_scheme
FROM sys.dm_exec_connections dec
WHERE dec.session_id = @@SPID;
';
EXEC [linked_server_name].tempdb.sys.sp_executesql @cmd;

The error message makes me think you're seeing a problem with file share permissions - when using Kerberos SQL Server will impersonate you when accessing the SMB share -  therefor your account needs access to it.


SQL Split Row Data Separated by Spaces I am looking for a query
to find nth value in a list.  The separator is anything greater than or equal to 2 spaces. (it can be 3, or 5 spaces).
Trying to avoid scalar value functions, since performance may be slower. The sentences can have any number of words, from 5-20.

CREATE TABLE dbo.TestWrite (TestWriteId int primary key identity(1,1),
                            TextRow varchar(255))
INSERT INTO dbo.TestWrite (TextRow)
SELECT 'I am writing SQL Code.'
UNION ALL
SELECT 'SQL keywords include join, except, where.'

+-----+----------+---------+---------------+---------+----------+
| SQL | keywords | include |     join,     | except, |   where. |
+-----+----------+---------+---------------+---------+----------+
| I   | am       | writing |    SQL  Code. |         |          |
+-----+----------+---------+---------------+---------+----------+

Would like in individual rows with columns, see comments above.

This may be one solution trying to utilize.
https://stackoverflow.com/questions/19449492/using-t-sql-return-nth-delimited-element-from-a-string

DECLARE @dlmt NVARCHAR(10)=N' ';
DECLARE @pos INT = 2;
SELECT CAST(N'<x>' + REPLACE(@input,@dlmt,N'</x><x>') + N'</x>' AS XML).value('/x[sql:variable("@pos")][1]','nvarchar(max)')

Splitting to separate fields of a result set is a bit tricky if you have a varying number of elements per row. There is a SQLCLR stored procedure in the SQL# library (that I wrote) that, while not in the Free version, does break a delimited string of varying elements into result set fields based on a Regular Expression delimiter. The delimiter being RegEx makes it quite easy to treat "one or more spaces" as the delimiter: \s+. For example:

EXEC SQL#.String_SplitResultIntoFields N'
SELECT [TextRow] FROM #TestWrite;',
N'\s+', NULL, NULL;
/*
Field1    Field2     Field3     Field4    Field5
I         am         writing    SQL       Code.
SQL       keywords   include    join,     except,
*/

As you can see, it determines the number of fields for the result set based on the first row, which is why there is no "Field6" to contain the final word in the second row.

Of course, you could always seed the first row with dashes separated by spaces to force a certain number of fields, but there is no way to then filter out that initial row:

EXEC SQL#.String_SplitResultIntoFields N'
SELECT N''- - - - - - - -''
UNION ALL
SELECT [TextRow] FROM #TestWrite;',
N'\s+', NULL, NULL;
/*
Field1    Field2     Field3     Field4    Field5    Field6   Field7    Field8
-         -          -          -         -         -        -         -
I         am         writing    SQL       Code.
SQL       keywords   include    join,     except,   where.
*/

I suppose I can add an optional input parameter for @ForceResultSetFieldCount easily enough, but it's not there as of today.

If this request was only what is initially stated in the question (i.e. "I am looking for a query to find nth value in a list"), then that is not only trivial (even with the "one or more spaces" delimiter requirement), but that requires a regular expression function, RegEx_CaptureGroupCapture, and that is in the Free version of SQL#. For example:

SETUP

CREATE TABLE #TestWrite
(
  TestWriteId INT PRIMARY KEY IDENTITY(1, 1),
  TextRow VARCHAR(255)
);

INSERT INTO #TestWrite (TextRow)
  SELECT 'I am     writing SQL                   Code.'
  UNION ALL
  SELECT 'SQL  keywords include   join,    except, where.';

TESTS

As you can see below, you can either use a pattern of "one or more word characters", which will exclude both whitespace and punctuation (first example), or you can use a pattern of "one or more non-whitespace", which will include punctuation, etc (second example).

-- only get "word" characters:
SELECT SQL#.RegEx_CaptureGroupCapture(t.[TextRow], N'(\w+)', 1, 4, 1, NULL, 1, -1, NULL)
FROM   #TestWrite t;
/*
SQL
join
*/

-- get non-whitespace:
SELECT SQL#.RegEx_CaptureGroupCapture(t.[TextRow], N'([^\s]+)', 1, 4, 1, NULL, 1, -1, NULL)
FROM   #TestWrite t;
/*
SQL
join,
*/


How to check the consistency of tables in mysql I have few questions regarding consistency in Mysql in two scenarios

1st scenario:
How can I verify a restored backup matches the original DB data on all the tables?

We have two hosts with the same database A and B (the same data, restored to different DBs) if there any way to check if the data is really the same. Maybe kind of md5 hash on table would help. If there are existing solutions? Of course it's not complicated to do something by myself, kind of validator.

2nd scenario
Let's say we have very strict logical schema for database. I need to check that our DB works according to schema. Let's say every Order has ProductId and etc. It sounds much more complicated, if you have faced these problem, how you deal with them.

Thanks!

Run CHECKSUM TABLE command against a table.

There is a caveat for this. According to the MySQL Documentation on CHECKSUM TABLE:

  If the checksums for two tables are different, then it is almost
  certain that the tables are different in some way. However, because
  the hashing function used by CHECKSUM TABLE is not guaranteed to be
  collision-free, there is a slight chance that two tables which are not
  identical can produce the same checksum.

OK, CHECKSUM TABLE is 99.99%, not 100%.

How can you aggressively check all the items of a table for matches and mismatches? You can actually run a great tool from Percona Tools called pt-table-sync. It will check for differences in tables between Master and Slave, as long as the table structure is identical.

For example, to sync the table db.tb1 on a Slave in relation to its Master, run pt-table-sync as follows:

pt-table-sync --print --sync-to-master h=SlaveIP,D=db,t=tbl > SQLtoFixDifferences.sql

When you run this, the table db.tb1 on the Slave is compared to that of its Master using the Primary Key (or Unique Key is there is no Primary Key) of the Slave's db.tb1. The output will be a series of SQL commands, usually REPLACE INTO and DELETE FROM. The script SQLtoFixDifferences.sql is to be executed on the Slave. When done, the data in db.tb1 should be identical to its counterpart on the Master.

To test it then, run these commands:

CHECKSUM TABLE db.tb1;
SELECT COUNT(1) FROM db.tb1;

The results should be identical on Master and Slave.

CAVEAT

I have never used pt-table-sync. I have been using mk-table-sync (MAATKIT tool from Percona) for years and I know it has always worked for me. I fully trust Percona's pt-table-sync is at the least the same, if not superior, quality.


What's better - setting up Always On fresh and migrating data, or using existing server with data and adding Always On? I have an existing SQL Server instance with three databases and a small amount of data.

I've been asked to create an Always On Availability Group now. The existing server would need to have the drives re-sized and only has 3 vCPUs.

So if you had a choice, what would be easiest/safest? Spin up the new servers, setup Always On, then migrate the data from the existing server?

Or should I keep the existing server and instance and add Always On Availability Groups to the existing SQL Server instance?

To migrate or not to migrate, that is the question: Whether 'tis nobler in the mind to suffer the slings and arrows of past setup sins and mistakes, or to take arms against that sea of troubles and by migrating end them.

Or not. If you were asking "Should I upgrade in place or migrate?" I'd have directed you to an older (and longer) answer I gave which, in short, says MIGRATE - ALWAYS MIGRATE!

But you didn't ask about an upgrade. You asked about an Availability Group. I commend you to that post on in-place vs migration upgrade decisions and contemplate where you are. If you are on a virtual and like the version of SQL you are on and only have to reconfigure "stuff" - I see no pressing reason to do a migration. You simply can add windows clustering, setup the AG feature, and start building your AG. You can use this as an excuse to cover for past sins because most config changes, etc. are "online" settings you can make as you go. On a VM you can play with your CPU config and RAM, etc.

If you need a new version of SQL Server or made some crucial installation mistake (hard to think of any, perhaps 64kB drive allocation size, but that's not the end of the world and you need new drives anyway), then sure install afresh and migrate. Or if you are on a physical, have the licensing budget, and can demonstrate the need for more cores, then you have no choice but to build new and migrate.

BUT! You are building an AG. You now have a magical THIRD OPTION. You could build your new server just the way you want it. You could add that as a node into your AG. You could build the AG with this instance participating as a replica in the AG with two instances you like the config and sizing of better. And then just failover to one of those and evict your current node and kick it goodbye. So you could use your AG to sail on over to the new hardware with minimal user frustration and look like a hero.


SQL Server database schema (and likely, some data changes) - how to auto-distribute over many database instances Our development involves a SQL Server database (also might be Oracle or Postgres later) and we would sometimes make database schema changes or some other interventions in database.

What solutions exist to create a "patch" or "script" to distribute these changes on other installations of same database (we do not have direct access to these)? It needs to alter database schema and execute SQL and/or other complex, pre-programmed database data alterations as defined by person who initializes/designs change. On other instances, a system admin should be able just run some program/press button, so these changes would be applied automatically.

In addition, it is  plus if such solution can take database snapshot and derive "difference" on contents of particular table that would be then distributed.

The solution can be commercial.

Thanks in advance!

Rather than trying Opensource or free tools, I would highly recommend using Redgate tools for schema comparison. (note: I am not working for or affiliated to Redgate, but have and is using the schema and data compare and trust me they are life saving !)

For sql server : SQL Compare.

Useful resource for automating using Powershell or command line.

for Oracle : Schema Compare for Oracle

Note: Just to mention, there is Data compare to sync data as well.


Changing foreign key to ON DELETE CASCADE with least amount of impact I have an existing foreign key that has ON DELETE NO ACTION defined. I need to change this foreign key to ON DELETE CASCADE. I can do this within a transaction:

begin;
alter table posts drop constraint posts_blog_id_fkey;
alter table posts add constraint posts_blog_id_fkey foreign key (blog_id) references blogs (id) on update no action on delete cascade;
commit;

The problem is that the posts table is large (4 million rows) which means that validating the foreign key can take a non-trivial amount of time (I've tested this with a copy of the database). Dropping/adding the foreign key acquires an ACCESS EXCLUSIVE lock on posts. So, adding the foreign key blocks all access to the posts table for a decent amount of time because the lock is held while constraint validation occurs. I need to perform an online migration (I don't have a dedicated downtime window).

I know that I can perform 2 transactions to help with the check taking a long time:

begin;
alter table posts drop constraint posts_blog_id_fkey;
alter table posts add constraint posts_blog_id_fkey foreign key (blog_id) references blogs (id) on update no action on delete cascade not valid;
commit;

begin;
alter table posts validate constraint posts;
commit;

The advantage of this approach is that the ACCESS EXCLUSIVE lock is held for a very short time for dropping/adding the constraint and then for validating the constraint only a SHARE UPDATE EXCLUSIVE on posts and ROW SHARE lock on blogs since I'm on Postgres 9.5.

Are there downsides to this? I know that adding NOT VALID to the constraints means that existing data is not validated, but any rows inserted/updated before the VALIDATE CONSTRAINT will be checked. Because the foreign key is dropped/added in the same transaction, is there any possibility of creating inconsistent data?

The docs say this about NOT VALID

ADD table_constraint [ NOT VALID ]

This form adds a new constraint to a table using the same syntax as CREATE TABLE, plus the option NOT VALID, which is currently only allowed for foreign key and CHECK constraints. If the constraint is marked NOT VALID, the potentially-lengthy initial check to verify that all rows in the table satisfy the constraint is skipped. The constraint will still be enforced against subsequent inserts or updates (that is, they'll fail unless there is a matching row in the referenced table, in the case of foreign keys; and they'll fail unless the new row matches the specified check constraints). But the database will not assume that the constraint holds for all rows in the table, until it is validated by using the VALIDATE CONSTRAINT option.

Your concern,

I know that adding NOT VALID to the constraints means that existing data is not validated, but any rows inserted/updated before the VALIDATE CONSTRAINT will be checked.

They'll only be checked during validation AFTER you tell it to validate, so you can delay that until you have scheduled downtime.

Because the foreign key is dropped/added in the same transaction, is there any possibility of creating inconsistent data?

No, because the second you add the NOT VALID it applies to all rows inserted AFTER the statement as if they were always there. VALIDATION is for the rejection of creating an FOREIGN KEY when the referenced rows do not exist. It has nothing to do with cascading, observe
CREATE TABLE foo
AS
  SELECT 1 AS a;

CREATE TABLE bar
AS
  SELECT a
  FROM ( VALUES (1),(2) )
    AS t(a);

ALTER TABLE foo
  ADD PRIMARY KEY (a);

ALTER TABLE bar
  ADD FOREIGN KEY (a)
  REFERENCES foo
  ON DELETE CASCADE
  NOT VALID;

DELETE FROM foo;

TABLE foo;
 a
---
(0 rows)

test=# TABLE bar;
 a
---
 2
(1 row)

At this point you can see

the deletion from bar cascaded to bar
the lack of validation on bar means it still has a row that is invalid

The constraint still can't be validated (as shown below), but for the purposes of cascaded deletion all things are good.
ALTER TABLE bar VALIDATE CONSTRAINT bar_a_fkey  ;
ERROR:  insert or update on table "bar" violates foreign key constraint "bar_a_fkey"
DETAIL:  Key (a)=(2) is not present in table "foo".

Btw you can write this
begin;
alter table posts drop constraint posts_blog_id_fkey;
alter table posts add constraint posts_blog_id_fkey foreign key (blog_id) references blogs (id) on update no action on delete cascade not valid;
commit;

Like this
alter table posts
  drop constraint posts_blog_id_fkey,
  add constraint posts_blog_id_fkey
    foreign key (blog_id)
    references blogs (id)
    on update no action
    on delete cascade
    not valid;

You don't have to wrap it in a txn. You also don't have to wrap any single statement in a txn -- PostgreSQL isn't MySQL. Everything is already transactional.


IF condition in connect command in SQL*Plus I want to fire SQL queries only when the user is connected to SQL*Plus. In my case even if the connection is not there, the rest of the commands are running in my SQL script.

if "connect &username/&password@&SID"
...

How to proceed with the queries if only the connect statement is successful?

Use whenever sqlerror in your script before connecting.

$ cat 1.sql
whenever sqlerror exit sql.sqlcode
connect bp/bp
select * from dual;

$ cat 2.sql
whenever sqlerror exit sql.sqlcode
connect bp/123
select * from dual;

$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 27 10:07:12 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL> @1
Connected.

D
-
X

SQL> @2
ERROR:
ORA-01017: invalid username/password; logon denied

$


Can default object names be changed? Sooner or later, a db developer will come across, and possibly use, a naming scheme for many objects like foreign keyes, default constraints etc.

Let's consider this example: Name all foreign keys as:
FK__[name of source table]__[name of source col]__ref__[name of master table]__[name of master column]

What kind of parametrization/external programs could be used to achieve having such a name as default, when for example using a create table statements which mentions the fk but does not explicitly provide a constraint name?

What kind of parametrization/external programs could be used to
  achieve having such a name as default, when for example using a create
  table statements which mentions the fk but does not explicitly provide
  a constraint name?

There's no way to change the default name generated when the DDL doesn't contain a name.  The only thing you can do is to run a batch process to identify non-compliant objects and perhaps drop and recreate them.


How can I obtain "Month By Month" data? I want to show a sum for each month that falls between the variables @startdate and @enddate - I think my issue is stemming from the Group By I have to add at the end so SSMS does not bark at me.

Current output:

Year Month Cash
1900  01   300

I want to see:

Year    Month   Cash
2016    01      150
2016    02      50
2016    03      100

Here is query I attempt:

Declare @startdate date = '20160101', @enddate date = '20160331'

SELECT
YEAR(satDate) [Year], MONTH(satDate) [Month],
SUM(ISNULL(A.truckrv,0)-ISNULL(A.DC,0)) As CashAmt
FROM
(
    SELECT
    ZT1.empName,
    SUM(ISNULL(ZT1.[truckrv],0)) AS truckrv,
    drivedate As TDate,
    0 As satDate,
    0 As DC
    FROM truck AS ZT1
    WHERE ZT1.[drivedate] >= @startdate
    AND ZT1.[drivedate] <= @enddate
    GROUP BY ZT1.empName, ZT1.drivedate
    UNION ALL
    Select
    t1.empName,
    0 As Tdate,
    satDate
    ,0 As truckrv
    ,SUM(ISNULL(t1.DC,0)) As DC
    FROM car t1
    WHERE t1.[satDate] >= @startdate
    AND t1.[satDate] <= @enddate
    Group By t1.empName, t1.satDate
) AS A
GROUP BY A.satDate

How should this query be altered in order to provide a "Month by Month" result set?

Likely what's happening here is that your second set of data (after the UNION ALL does not return any data, and so only the first part is returning anything. Because you are passing a 0 for the date, this is 1900-01-01. All of the results are going to get grouped for that date.

Here's a similar mockup, but this one returns data after the UNION ALL

DECLARE @t TABLE
    (
      C1 INT ,
      C2 DATETIME ,
      C3 INT
    );

INSERT  INTO @t
VALUES  ( 1, '2016-01-01', 100 )
,       ( 1, '2016-01-01', 250 )
,       ( 1, '2016-03-05', 200 )
,       ( 2, '2016-02-01', 200 )
,       ( 2, '2016-02-01', 500 )
,       ( 2, '2016-06-01', 200 );

SELECT  YEAR(C2) [Year] ,
        MONTH(C2) [Month] ,
        SUM(C3)
FROM    ( SELECT    0 AS C2 ,
                    SUM(C3) AS C3
          FROM      @t
          WHERE     C1 = 1
          GROUP BY  C2
          UNION ALL
          SELECT    C2 ,
                    SUM(C3) AS C3
          FROM      @t
          WHERE     C1 = 2
          GROUP BY  C2
        ) AS A
GROUP BY C2;


Get limited data from $unwind mongodb I have a chats collection which given down below

{
    "_id" : ObjectId("5a44e6545818041cde24aac9"),
    "messages" : [
        {
            "createdAt" : ISODate("2017-12-23T07:46:29.201Z"),
            "message" : "Hii",
            "userId" : "000000"
        },
        {
            "createdAt" : ISODate("2017-12-23T12:46:29.201Z"),
            "message" : "Bye gtg ttyl",
            "userId" : "111111"
        },
        {
            "createdAt" : ISODate("2017-12-24T07:46:29.201Z"),
            "message" : "Bye Take Care",
            "userId" : "000000"
        }
    ],
    "createdAt" : ISODate("2017-10-23T07:46:29.201Z"),
    "users" : [
        {
            "facebookId" : "0000000",
            "unread" : 0
        },
        {
            "facebookId" : "111111",
            "unread" : 0
        }
    ]
}

When I do this following query in nodejs

chats.aggregate( [ { $unwind : "$messages" },{ $unwind : "$users" } ])

I get an array which is something like this in which unwind opens my array something like this

[
    {
        "_id": "5a44e6545818041cde24aac9",
        "messages": {
            "createdAt": "2017-12-23T07:46:29.201Z",
            "message": "Hii",
            "userId": "000000"
        },
        "createdAt": "2017-10-23T07:46:29.201Z",
        "users": {
            "facebookId": "0000000",
            "unread": 0
        }
    },
    {
        "_id": "5a44e6545818041cde24aac9",
        "messages": {
            "createdAt": "2017-12-23T07:46:29.201Z",
            "message": "Hii",
            "userId": "000000"
        },
        "createdAt": "2017-10-23T07:46:29.201Z",
        "users": {
            "facebookId": "111111",
            "unread": 0
        },
    }, //And so on.
]

But can I only get the objects after unwind where messages.userId !== users.facebookId

something like the following object directly from MongoDB when i fetch data

[
    {
        "_id": "5a44e6545818041cde24aac9",
        "messages": {
            "createdAt": "2017-12-23T07:46:29.201Z",
            "message": "Hii",
            "userId": "000000"
        },
        "createdAt": "2017-10-23T07:46:29.201Z",
        "users": {
            "facebookId": "111111",
            "unread": 0
        },
    },{
        "_id": "5a44e6545818041cde24aac9",
        "messages": {
            "createdAt": "2017-12-23T12:46:29.201Z",
            "message": "Bye gtg ttyl",
            "userId": "111111"
        },
        "createdAt": "2017-10-23T07:46:29.201Z",
        "users": {
            "facebookId": "0000000",
            "unread": 0
        }
    }, {
        "_id": "5a44e6545818041cde24aac9",
        "messages": {
            "createdAt": "2017-12-24T07:46:29.201Z",
            "message": "Bye Take Care",
            "userId": "000000"
        },
        "createdAt": "2017-10-23T07:46:29.201Z",
        "users": {
            "facebookId": "111111",
            "unread": 0
        }
    }
]

I tried this but the output is same :/

chats.aggregate( [ { $unwind : "$messages" },{ $unwind : "$users" },{$match:{ 'messages.userId' :{ $ne: '$users.facebookId'}}}]).exec(function(err, data) { res.send(data); });

As per MongoDB BOL $unwind  Deconstructs an array field from the input documents to output a document for each element. Each output document is the input document with the value of the array field replaced by the element.

The $unwind stage has the following prototype form:

{ $unwind: <field path> }

To specify a field path, prefix the field name with a dollar sign $ and enclose in quotes.

Behaviors

$unwind has the following behaviors:

If a value in the field specified by the field path is not an array,
db.collection.aggregate() generates an error.
If you specify a path
    for a field that does not exist in an input document, the pipeline
    ignores the input document and will not output documents for that
    input document.
If the array holds an empty array ([]) in an input
        document, the pipeline ignores the input document and will not
        output documents for that input document.

For example

Consider an inventory with the following document:

> db.inventoty.insert({"_id" : 1, "item" : "ABC1", sizes: [ "S", "M", "L"] })
WriteResult({ "nInserted" : 1 })
> db.inventory.find().pretty()
> db.inventory.insert({"_id" : 1, "item" : "ABC1", sizes: [ "S", "M", "L"] })
WriteResult({ "nInserted" : 1 })
> db.inventory.find().pretty()
{ "_id" : 1, "item" : "ABC1", "sizes" : [ "S", "M", "L" ] }
> db.inventory.aggregate( [ { $unwind : "$sizes" } ] )
{ "_id" : 1, "item" : "ABC1", "sizes" : "S" }
{ "_id" : 1, "item" : "ABC1", "sizes" : "M" }
{ "_id" : 1, "item" : "ABC1", "sizes" : "L" }

Each document is identical to the input document except for the value of the sizes field which now holds a value from the original sizes array.


Foreign key in main table or supplementary table? I have two tables with one to one relationship b/w student and Student_detail(kind of supplementary table) table

Design 1

Student
 student_id
 name
 ....

Student_detail

 student_detail_id
  student_id(foreign key to student_id in student table)
  ...

Design 2

Student
 student_id
 name
 student_detail_id (foreign key to student_detail_id in Student_detail table)
 ....

Student_detail

 student_detail_id
   ...

My question is which approach is better ? From design perspective is it better to keep the foreign key in main table(in this case student table) or
in supplementary table (student_detail table ) ?

Per my understanding approach 2 is better as if we add more supplementary tables we don't have to modify main table ?

Suggested that previous comment be posted as 'answer'.  Here 'tis.

Even if a justifiable one-to-one, I'd still treat the "primary" table as if it were a parent, and the "detail" as if it were one of  potentially multiple children. Thus the FK would go in the 'details' table. More appropriately named 'additional_student_details'.


Is there a way to replicate Postgres databases in Amazon RDS between regions? We have a Postgres RDS instance in the AWS Oregon Region.  For some supposed legal reasons, we have to host our client's European data in the Ireland AWS Region, but we need that data synchronized to the Oregon Region.  We will probably have to have an instance in the Singapore Region eventually, too.

We would like the databases to all be synchronized across regions.

Is this something that can even be done with Postgres RDS instances, or is MySQL the only database in RDS that supports this concept of replication between regions?

Any input on this matter would help.  We really enjoy how RDS makes administering the databases so easy, but if RDS can't allow us to sync databases between regions with Postgres, then we'll have to switch back to EC2 Linux instances with Postgres.

AWS offers cross region data replication for MySQL RDS with a few clicks on aws console cross-region replication for MySQL, but not for postgreSQL yet, maybe in the future they will provide such functionality.

However, you might be able to implement such replication across regions with ec2 instances and avoiding RDS instances.


You will have to look at this from different perspectives

Data / Storage Engine

The InnoDB Storage Engine is constructed to do as little writing as possible. As an ACID-complaint storage engine, InnoDB has mechanisms in place for concurrent manipulation of data. The structures used by the InnoDB Infrastrcuture are the following:

Buffer Pool : Changes to Data and Index Pages that are Cached in RAM
Double-Write Buffer : Backup buffer to prevent data corruption
Insert Buffer : Changes to secondary non-unique indexes
Redo Logs : Playback instructions in the event of Crash Recovery
Undo Logs : Reverse instructions in the event of Crash Recovery/Rollback
See Pictorial Representation

Master

In all likelihood, there are concurrent operations from multiple DB Connections performing

INSERTs
UPDATEs
DELETEs
SELECTs

on the Master. These changes and lookups are cached and managed with the InnoDB Infrastructure. Flushing all recorded changes is serially managed but has no immediate effect on data concurrency. The only discernible bottleneck would be the time an SQL transaction decides to write the SQL Commands into its Binary Logs. The OS is responsible for flushing changes to binary logs to disk.

Slave

Here is where things can get interesting. While the Master handles everything thrown at it, it has to write the SQL Commands into its Binary Logs. The Slave has the responsibility to process what the Master gives it.

Here is the MySQL Replication Paradigm

IO Thread on Slave Connects to Master
The Master Does the Following on a Continuous Basis:

Completes SQL Command
Records SQL Command in its Binary Logs

The Slave Does the Following on a Continuous Basis:

IO Thread from Slave Reads incoming SQL Commands from the Master's Binary Logs
IO Thread Records the SQL into its Relay Logs
SQL Thread from Slave Reads Next SQL Command to Be Executed
SQL Thread Executes the Latest SQL Command From the Relay Logs
If Binary Logging is enabled on the Slave, Slave Records SQL Command in its Binary Logs

If you carefully follow the flow of control from this Paradigm description, you can easily see that all the SQL sent from the Master is serialized. In other words, the SQL commands from the Relay Logs are executed one at a time. This can manifest itself on the Slave as follows:

When you run SHOW SLAVE STATUS\G, you see Seconds_Behind_Master increasing
When you run SHOW PROCESSLIST;, the SQL thread (whose user is 'system user') is executing an SQL statement that is taking a long time on its own.

This can easily happen when a Master is handling hundreds of SQL commands in parallel. It is when the SQL statement are lined up single-file and recorded as a FIFO queue on the Master, that the Slave must then process each command first-come, first serve only without any benefits from parallelism.

Conclusions Drawn

From the roles of Master and Slave given an all-InnoDB database, a Master to could handle multiple SQL transactions and be done with all commands. By design a Slave has no choice but to process commands in a serialized fashion. The background processing performed by a Master would easily get finished a stay quiet for a long time while a rather busy Slave would have process SQL one-by-one and have resources handle processed changes in a similar manner.

Troubleshooting / Resolution

Once you can see this funneling of SQL from Master to Slave, your only recourse would be to consider some of the following options to apply to the Slave and Master

Query Optimization
Create the needed indexes to support Query Optimization
Make adjustments in the networking setup between Master and Slave

Install a 2nd NIC on Master and Slave
Assign IP address to the 2nd NIC (NIC_IP)
Run CHANGE MASTER TO command to assign (NIC_UP) to MASTER_HOST

Upgrade to MySQL 5.5 (if not using MySQL 5.5)
Configure MySQL 5.5

configure InnoDB for multiple core engagement
configure SemiSynch Replication

Upgrade RAM
Change to Individual Tablespaces


How to find the current value for 'log_history' of PURGE_LOG job in Oracle I was trying to find what is the value of 'log_history' of PURGE_LOG job in Oracle.
I found that you can set the value using following command.

DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE
('log_history',<NUMBER_OF_DAYS>);

But I was not able to find any way to get what is the current value of this property.
Could you please let me know if there is a way to do this?

Thanks in Advanced.

dbms_scheduler.get_scheduler_attribute('log_history',x);

do a desc on dbms_scheduler to see the possibilities...


How to effectively filter between several tables in SQL I have a main table with another 7 tables attached to it by a key.
I have 10 different filter types in the secondary tables
I will not always have all types of filters in one QUERY, some of them may be equal to NULL.
The problem is that the secondary tables are very large and if they do not need to be accessed by the filter then it is best not to include them in the INNER JOIN,
How to make it the most effective?

You can use dynamic SQL to generate a query based on varied requirements. This way, you can exclude joins if you don't need them, like so:

DECLARE @sql NVARCHAR(MAX);
    SET @sql = 'SELECT * FROM myTable AS t ';
IF @param1 IS NOT NULL
    SET @sql = @sql + 'JOIN mySecondTable AS s ON s.Id = t.mySecondTableId ';
IF @param2 IS NOT NULL
    SET @sql = @sql + 'JOIN myThirdTable AS x ON x.Id = t.myThirdTableId';
-- repeat as required to build a valid query

DECLARE @filters NVARCHAR(MAX);
DECLARE @params NVARCHAR(MAX) = '@filterParam1 INT, @filterParam2 NVARCHAR(128);'

IF @filterParam1 IS NOT NULL
BEGIN
   SET @filters = '[column] = @filterParam1';
END

IF @filterParam2 IS NOT NULL
BEGIN
  SET @filters = CASE WHEN @filters IS NULL
                      THEN ''
                      ELSE ','
                  END + '[column2] = @filterParam2';
END

DECLARE @where NVARCHAR(MAX);
IF @filters IS NOT NULL
BEGIN
   SET @where = 'WHERE ' + @filters;
   SET @sql = @sql + @where;
END

EXEC sp_ExecuteSql @stmt = @sql
                 , @params = @params
                 , @filterParam1 = @filterParam1
                 , @filterParam2 = @filterParam2;

Assuming that @param1 is null and @filterParam1 is also null, it would generate the following query:

SELECT *
  FROM myTable AS t
  JOIN myThirdTable AS x on x.Id = t.myThirdTableId
 WHERE [column2] = @filterParam2;

Notice how the join to mySecondTable has not been included and the unnecessary filter has not been included either. You can find more examples of how to use sp_executeSql on Microsoft Docs.


Using Oracle client to connect to remote database Although I can do a telnet to remote oracle port I am unable to connect.
I have a following tnsname.ora connecting to oracle database using sql plus:

remoteOracle =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.66.1.61)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = PROD)
    )
  )

My sqlnet.ora

# sqlnet.ora Network Configuration File: C:\ORACLE\product\11.2.0\client_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

When I try to connect using this I always get following error

ORA-12504: TNS listener was not given service_name in connect data.

And toad shows following error:

ORA-12170 TNS connection Time out

There are different ways to connect oracle. One of them is easy connect .

You may use

sqlplus username/password@server/SID

According to comments. You can connect oracle using easy connect but hr (human resources) account does not exists.
Therefore try to use

sqlplus username/password@10.66.1.61:1521/OPTPROD

You should be able to connect your oracle system.


SSDT schema compare, removing and then renaming stored procedure result is incorrect The initial setup contains two scripts:

stored_procedure.sql
stored_procedureV2.sql

What I did is:

Remove stored_procedure.sql
Rename stored_procedureV2.sql to stored_procedure.sql

When I perform a schema compare in SSDT between a .dacpac not containing this change and my project the action it proposes is to just remove stored_procedure.sql and do nothing else, whilst it should remove stored_procedureV2.sql and update stored_procedure.sql to its new content.

As I assume this is where schema compare gets its information:

The .sqlproj of the source:

...
<Build Include="Scripts\stored_procedure.sql">
  <AnsiNulls>On</AnsiNulls>
  <QuotedIdentifier>On</QuotedIdentifier>
</Build>
<Build Include="Scripts\stored_procedureV2.sql">
  <AnsiNulls>On</AnsiNulls>
  <QuotedIdentifier>On</QuotedIdentifier>
</Build>
...

The .sqlproj of the target:

...
<Build Include="Scripts\stored_procedure.sql">
  <AnsiNulls>On</AnsiNulls>
  <QuotedIdentifier>On</QuotedIdentifier>
</Build>
...

Is this a known issue? Do I need to perform this in two steps? Is there a way to make SSDT behave the way I want?

The issue was that stored_procedure.sql still had stored_procedureV2 as procedure name. Then SSDT actually does a correct compare by saying: stored_procedureV2.sql is unchanged and stored_procedure.sql is removed.


MySQL synchronization I have a cloud-based MySQL instance (slave) and am planning a solution where a remote instance (master) will be based on a ship.  The ship will go in and out of international waters and therefore will have an intermittent terrestrial connection.  When there is no connection, data will be stored locally.  When the vessel arrives in an area with a terrestrial connection, I want the vessel database to synchronize with the cloud-based instance.

This scenario will be duplicated with multiple vessels, with their own instances, and each vessel will need to send local changes to the cloud-based instance.

We have looked at 3'rd party open-source tools like symmetric DS and wanted to 'ask-the-experts' the preferred approach.  Is there a best-in-breed 3'rd party tool, or is the out-of-the-box MySQL solution sufficient?  Thank you in advance.

Classical MySQL replication replication should work:

Assumptions made:

your cloud instance is read only.

The master won't be able to trigger the replication however by default a slave will retry every 60 seconds (configurable CHANGE MASTER .. MASTER_CONNECT_RETRY=).

Ensure your expire_binlog_days is large on the ship based master. Account for the space required carefully on your master and cloud instance.

I'd recommend the ship based master connect via a VPN and the slave connects to the VPN address.

For multiple vessels, you can use use a multisource replication available in MySQL-5.7+ or MariaDB-10.0+. Keep each vessel in its own database for simplicity.

Choose a binlog_format that minimises your traffic volume.


MySQL: Access denied messages I have just installed Ubuntu on my server and MySQL.

I can't make any change in MySQL (seeting password, creating database, etc.) as I ALWAYS get an Access denied error message. I have followed the MySQL manuals re passwords, but to no avail.

The user name I use is the account name created when Ubuntu was installed.

At some point, I am told I need SUPER privileges to do this or that but I can't change the privileges since I get the same error message. I can't even see the GRANTS as I also get the same access denied message.
mysql version 14.14 Dist. 5.5.32, for debian-linux-gnu

Any idea on how to solve this?

To Arka:

Editing my own question is the only option I have to post this:

mysql> status

mysql Ver 14.14 Distrib 5.5.32, for debian-linux-gnu (i686) using readline 6.2
Connection id: 63
Current database:
Current user: charles@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.5.32-0ubuntu0.12.04.1 (Ubuntu)
Protocol version: 10
Connection: localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/run/mysqld/mysqld.sock
Uptime: 6 hours 58 min 20 sec
Threads: 1
Questions: 165
Slow queries: 0
Opens: 211
Flush tables: 1
Open tables: 51
Queries per second avg: 0.006

Now, I have not been able to create a single database since the installation of the server this morning. I hope that helps. Thanks. :)

When you're installing MySQL you're asked to enter the root password that you want to set for the database. In the status code you've listed above you're logging in as charles@localhost and not root. That's the reason why it's not asking for the password you set during installation.

If you're logging in to MySQL using the root account then you can do all the things you want.

Simply type:

mysql -p -u root

and it'll ask you for the root password. Now you're "in" the system with full access. You can then create databases using:

CREATE DATABASE MyNewDatabase;

If you have a user that you want to give access to this new database you can type:

GRANT ALL ON MyNewDatabase.* TO 'charles'@'localhost';

When this is done you should be able to login as charles and also have full access to the newly created database.


AdonisJS: is something wrong with belongsTo() relationship design? In AdonisJs framework, suppose we have a Profile model in which we define belongsTo() relationship to theUser model. The documentation says this will lead us to define our databases this way:

The column id in users table is a foreign key referencing profiles tables instead of being the primary key of users table. Can this 'bad DB design' lead to some inconsistencies?

Billal Begueradj

I think that the only problem with the linked documentation are the typos in the model image:

The users column id (FK) was meant to say id (PK).
The profiles column user_id (PK) was meant to say user_id (FK).
The link should have reverse direction (in all other diagrams, links start from the PK/referenced table and point to the Fk/referencing table).

The diagram under the belongsTo() example seems to be the correct one (and should be used in hasOne(), too).

The design shows a 1-1 relationship, or more accurately a 1 - 0..1 relationship, between users and profiles.

A user can have one (hasOne) profile. A profile belongs to (belongsTo) one user.

What seems to be missing from the diagrams is that profiles (user_id) should have a UNIQUE constraint, in order for the relationship to be true 1-1 and not 1-to-many. Perhaps the ORM creates such a constraint and it was missed when drawing the diagrams.


Three questions about Database design I'm new to mysql,and I'm designing a  database for my social network app, I got some questions about it.

user_table

comment_table

1.Should I use unique key as much as I could in the database when the value of the field is unique?

2.Should I add some backup field in case I need to add other field in the future? If not,how to add other field in the future?

3.In the comment_table,I use user_id for foreign key,but I heard using too much foreign key will make the database slow,especially in social network.So,should I use comment_author_id instead,and if I need someone's
comment,I have to use

"select * from comment_table,user_table where comment_author_id = user_id"

Here is an graph I found on the internet,it use a lot of foreign key.Is it correct or out of date? Thanks for your time.

Generally, your top priority when designing a relational database is data integrity. Performance is a distant second at best. If you sacrifice data integrity, you may one day defending the charge, "This query returns the wrong answer!" with the rationale, "Yes, but it does so very quickly."

Quite lame as far as excuses go, yes?

However, the overhead of data integrity is small and localized. By localized, I mean that the overhead is generally on the DML (Insert, Update, Delete) operations. The queries will usually perform better.

1.Should I use unique key as much as I could in the database when the value of the field is unique? Absolutely. Always. Never fail. Defining a field as unique creates an index on the field. Odds are good that any unique field will be used heavily in the filtering criteria: where uniquefield = value. Now you have data integrity and fast access.

2.Should I add some backup field in case I need to add other field in the future? If not,how to add other field in the future? No. If you don't know enough to create a field, you don't know the data type, the uniqueness, the nullability, anything needed to properly define a field, including a proper name. It's easy enough to add a field to an existing table once you know all the particulars. There is no need to plan ahead on this.

3.In the comment_table, I use user_id for foreign key, but I heard using too [many] foreign key[s] will make the database slow, ... As I said before, data integrity constraints, such as foreign keys, can make DML a little slower, but foreign keys are completely irrelevant to queries. They are ignored, they play no role at all. So go ahead and define foreign keys, unique constraints and whatever other constraints make sense to keep a tight reign on data integrity.

It makes no sense at all to have excellent response time in a database filled with anomalous data. Fortunately, "response time" in most OLTP databases refers to query time. Proper data integrity checks will, at worst, have no effect on query response time. At best, they will actually make it better.

This is where good analysis comes in. In SQL tuning, you typically speed up query time by sacrificing DML time -- and vice versa. Typically, response time concerns for OLTP databases lean towards queries. However, there are instances where the DML is the primary concern. So which hits your tables most often: DML or queries? Generally, it's not even close -- like 100 to 1 in one direction or the other. If you can speed up the 100 side by moving a little overhead to the 1 side, so much the better.

But never sacrifice data integrity. It's never worth it.


What is a staging table and how do you protect data loss when transferring the data? What is a staging table? What is it used for? Are there best practices that should be applied? I'm working in Oracle right now. Are staging tables platform dependent? I'm performing a bulk copy with Devart's OracleLoader C# class. To my knowledge you can't perform this type of operation on a table with enabled triggers(used to add a sequenced primary key).

Another question related to staging tables. How do you protect data loss when transferring data from a staging table to it's permanent home(permanent table?)

I believe I have a basic understanding of what a staging table is. From my understanding it is just a location to temporarily store data before moving that data to its permanent home.

I believe I have a basic understanding of what a staging table is.
  From my understanding it is just a location to temporarily store data
  before moving that data to its permanent home.

Yes, it is.

  To my knowledge you can't perform this type of operation on a table
  with enabled triggers(used to add a sequenced primary key).

This is a restriction of direct path load, not staging tables.

Direct Path Loads, Integrity Constraints, and Triggers

Triggers fire during conventional path loads.

  How do you protect data loss when transferring data from a staging
  table to it's permanent home(permanent table?)

You don't. Direct path loads do not generate the redo that would make possible recovering the data affected by it in case of a recovery situation (this is to improve performance). Since it is just a temporary location, if it is damaged or lost, you can just start over and repeat the loading process.

If you want it to be protected, then use conventional path load.


T-SQL: Convert XML to string without self-closing tags We have a weirdo system we're integrating with which consumes XML, but it does not understand self-closing tags, so

<BOOKING>
  <BOOKINGID>56812</BOOKINGID>
  <PERSONCODE />
</BOOKING>

is beyond its capabilities.

If what I have is declare @x xml, how do I convert that @x to string without having self-closing tags:

<BOOKING>
  <BOOKINGID>56812</BOOKINGID>
  <PERSONCODE></PERSONCODE>
</BOOKING>

A workaround would be to add an unlikely value to your empty nodes using insert (XML DML) and then replace the value on your string representation.

declare @S nvarchar(max)

declare @XML xml = '
<BOOKING>
  <BOOKINGID>56812</BOOKINGID>
  <PERSONCODE />
</BOOKING>'

-- Add _ as value to all empty nodes
while @XML.exist('//*[.="" and count(*)=0]') = 1
  set @XML.modify('insert text{"_"} into (//*[.="" and count(*)= 0])[1]')

-- Cast to string and remove _ as value
set @S = replace(cast(@XML as nvarchar(max)), '>_<', '><')

select @S


How to allow users to run a function, but not access objects the function is accessing I have a PL/PgSQL stored procedure that performs various operations on tables (SELECT, UPDATE, INSERT, TRUNCATE).

I want users to be able to run the stored procedure, but not themselves perform those same operations on the tables.

Is that possible?

Yes that's possible. Create the function with the user owning all those tables and use the SECURITY DEFINER modifier.

  SECURITY DEFINER specifies that the function is to be executed with the privileges of the user that created it.

create or replace function foo()
  returns void
as
$body$
...
$body$
language plpgsql
security definer;

Also see the example in the manual:
http://www.postgresql.org/docs/current/static/sql-createfunction.html#SQL-CREATEFUNCTION-SECURITY


Automating Redshift data dump to a PostgreSQL DB every hour GIS analyst with minimal experience of this back-end stuff but a big task at hand. I have a redshift database that changes multiple times a day. I want to use this "live" data to make web maps and I have a Postgres DB where all my web-mapping data is stored. I want to continuously dump data from the ever-changing Redshift DB to my maps. What is the simplest way to do this? I have experience with SQL so if that's the route it would be helpful.

Your best bet is probably to use the dblink feature of PostgreSQL to create a connection to Redshift and query the necessary data from Redshift to add to your PostgreSQL instance.

Here is an example using RDS PostgreSQL, but I think you would probably be able to get this to work with an EC2 hosted PostgreSQL instance as well:

https://aws.amazon.com/blogs/big-data/join-amazon-redshift-and-amazon-rds-postgresql-with-dblink/

Note the use of pgbouncer-rr in the example is optional, you will probably not need this.


restarting database while shrink [db] is still running I ran a shrink Database command on an SQL Server database to free up about 200GB of space, then mistakenly ran a large query select * from tbl where tbl is large, then tried to cancel that query through SSMS STOP button instead of kill.

SQL Server is now stuck, CPU at max and I can't even log in.

Is it safe to restart an SQL Server that is going through shrink db? Is there a chance I lose data? Or corrupt existing data? I don't think the select is an issue during restarts...

Restarting SQL Server is rarelly a good way to fix issue.

You can try to log as a dedicated admin (DAC connection) and then, you will see what is running and you may be able to kill some session.

Be aware that by killing a session, SQL will have to roll it back (which is single threaded) and there is no way to escape from this rollback. Sometime, the best solution may be to wait for it to complete. (You can kill the select as the rollback should be pretty quick)


Problem with mysqldump and view When using mysqldump to backup MySQL, I got following error.

mysqldump --all-databases --routines >> all.sql
mysqldump: Couldn't execute 'show table status like 'hdkien'': SELECT command denied to user 'tungbt'@'192.168.12.197' for column 'id' in table 'hdcn_hd' (1143)

hdkien is a view

CREATE ALGORITHM=UNDEFINED DEFINER=`tungbt`@`192.168.12.197` SQL SECURITY DEFINER VIEW `hdcn`.`hdkien` AS (...striped...)

User tungbt@192.168.12.197 has already had the privilege to select on table hdcn_hd and I can select from view hdkien without problem.

mysql> select * from hdkien limit 1;
+------+-----------+
| id   | shd       |
+------+-----------+
|  876 | ADFADFA1  |
+------+-----------+

More info:

MySQL version: mysql-community-server-5.5.37-4.el6.x86_64
OS: CentOS 6.5

Why I got the error when running mysqldump and how can I resolve it?

Update 1 (2014/04/17)

I run mysqldump with user 'root'@'localhost'.

mysql> show grants for 'root'@'localhost';
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '...striped...' WITH GRANT OPTION                             |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+

The user 'tungbt'@'192.168.12.197' is the definer of view hdcn.hdkien (and the SQL SECURITY is DEFINER.

+------------------------------------------------------------------------------------------------------------------+
| Grants for tungbt@192.168.12.197                                                                                |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tungbt'@'192.168.12.197' IDENTIFIED BY PASSWORD '...striped...'                             |
| GRANT ALL PRIVILEGES ON `hdcn`.* TO 'tungbt'@'192.168.12.197'                                                      |
+------------------------------------------------------------------------------------------------------------------+

Update 2

$ mysql -ANe"SELECT USER(),CURRENT_USER()"
+----------------+----------------+
| root@localhost | root@localhost |
+----------------+----------------+

Update 3

mysql> SELECT COUNT(1) MySQLGrantsCount,VERSION() MySQLVersion FROM information_schema.columns WHERE table_schema='mysql' AND table_name='user';
+------------------+--------------+
| MySQLGrantsCount | MySQLVersion |
+------------------+--------------+
|               42 | 5.5.37-log   |
+------------------+--------------+

You need to have the SHOW VIEW privilege. I wrote about this Dec 2013 : Which are the minimum privileges required to get a backup of a MySQL database schema?

In that post I show these minimum privileges for a mysqldump

SELECT
SHOW VIEW (If any database has Views)
TRIGGER (If any table has one or more triggers)
LOCK TABLES (If you use an explicit --lock-tables)

You should run this command:

SHOW GRANTS FOR tungbt@192.168.12.197;

If SHOW VIEW is not there, that's the reason why.

UPDATE 2014-04-16 23:06 EDT

When you did this

mysqldump --all-databases --routines >> all.sql

I see you did not specify the user and password. That being the case, you were not logged in as root@localhost. You will have to be explicit in specifying the root user

mysqldump -uroot -p --all-databases --routines >> all.sql

You will see the password prompt. Enter the root@localhost password and you are off and running.

You could also specify the password too

mysqldump -uroot -ppassword --all-databases --routines >> all.sql

Give it a Try !!!

WILD SUGGESTIONS

If you are using .~/my.cnf and still getting an error, you might be hitting this situation in Bug #70907 mysqldump: Couldn't execute 'show table status': SELECT command denied to user '

If the config file is .~/my.cnf is really /root/.my.cnf, perhaps you are not logged in as Linux root. You may have to run sudo.

Please run this command

mysql -ANe"SELECT USER(),CURRENT_USER()"

If you do not see root@localhost twice, then you are not authenticating correctly.

In .my.cnf you need to make sure that user and password are under the [client] section

[client]
user=root
password=rootpassword

not under the [mysql] section.

UPDATE 2014-04-17 13:53 EDT

I cannot help look at that bug report and wonder the following: Since you have DEFINER=tungbt@192.168.12.197, it is possible that root@localhost is behaving like tungbt@192.168.12.197 ? I say this because according to the MySQL Documentation on CREATE VIEW: At view definition time, the view creator must have the privileges needed to use the top-level objects accessed by the view. For example, if the view definition refers to table columns, the creator must have some privilege for each column in the select list of the definition, and the SELECT privilege for each column used elsewhere in the definition.

You could change the definer of the view to root@localhost and try the mysqldump again


Ordering with custom paging when using pivot to convert a query This is a follow up to my question on changing a flat result set into a different column set using Pivot.

Scripts for test DB can be found in that link

Ordering works just fine like this:

SELECT TOP 100 PERCENT *, ROW_NUMBER() OVER(ORDER BY ID) Corr
        FROM
        (
            select p.ID, firstname,[Date], e.Title as stringvalue
    from
            dbo.DayPart dp
         LEFT outer JOIN
            dbo.PersonDayPart idp ON dp.ID = idp.DayPartID
        left outer JOIN
            dbo.Person p ON idp.PersonID = p.ID
              LEFT outer JOIN
        dbo.Event e ON e.ID = idp.DayPartID

        ) as d
        pivot
        (
            min([stringvalue])
            for [Date] in
          ([01/01/12],[02/01/12],[03/01/12],[04/01/12],[05/01/12],[06/01/12],[07/01/12])
        )
        as p
        ORDER BY firstname desc

I have added custom paging to the query and that works fine.

I then wanted to add ordering to the query and I'm coming against the well known issue "The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified."

I am aware there are issues with using TOP, however I have gave it a bash anyway.
When I try to do the order within my CTE, the order does not affect the query at all.

EDIT: having issue uploading the declare statements in bold, please remove the dot in each to get the script to run properly. Every time I try to edit those, my connection crashes.... totally bizarre

DECLARE @StartDate DATE = '20120101';
**D.ECLARE** @SortBy varchar(10) = 'FirstName'
        **D.ECLARE** @sortDir varchar(10) = 'desc'
    DECLARE @size int = 2
    DECLARE @page int = 1
SELECT TOP (7)
    @sql += N',[' + CONVERT(CHAR(10), DATEADD(DAY, ROW_NUMBER() OVER
      (ORDER BY [object_id])-1, @StartDate), 120) + ']'
    FROM sys.all_objects ORDER BY [object_id];

    SELECT @sql = N'
    WITH CTE AS
    (
        SELECT TOP 100 PERCENT *, ROW_NUMBER() OVER(ORDER BY ID) Corr
        FROM
        (
            select p.ID, firstname,[Date], e.Title as stringvalue
    from
            dbo.DayPart dp
         LEFT outer JOIN
            dbo.PersonDayPart idp ON dp.ID = idp.DayPartID
        left outer JOIN
            dbo.Person p ON idp.PersonID = p.ID
              LEFT outer JOIN
        dbo.Event e ON e.ID = idp.DayPartID

        ) as d
        pivot
        (
            min([stringvalue])
            for [Date] in
          (' + STUFF(@sql, 1, 1, '') + ')
        )
        as p
        ORDER BY ' + @sortBy + ' ' + @sortDir + '
    )
    SELECT *
    FROM CTE
    WHERE Corr BETWEEN ('+ CONVERT(varchar(2), @size) + ' * (' + CONVERT(varchar(3), @page) + ' - 1))+1 AND ('+ CONVERT(varchar(2), @size) + '*' + CONVERT(varchar(3), @page) + ')
    '

    print @sql
    EXEC sp_executesql @sql

Mikael's question helped me immensely.

I was not paying attention to the OVER clause which has an OrderBy.

Altering that got the ordering working correctly with the custom paging.

So I replace the line:

SELECT TOP 100 PERCENT *, ROW_NUMBER() OVER(ORDER BY ID) Corr

with the line:

SELECT *, ROW_NUMBER() OVER(ORDER BY ' + @sortBy + ' ' + @sortDir + ') Corr


Hosting Multiple MongoDB Databases on the same MongoDB Instance/Cluster In MongoDB there are the following conecpts:

A MongoDB database exists as a logical unit on a MongoDB Instance which is either a singleton or a cluster running within some number of mongod processes on some number of servers.

There is lots of Q&A around answering the question: Should one run multiple production mongod processes on the same server? to which the answer is: In general no.

However, what about the question of having multiple production databases on the same instance?  I.e.:

Set up a production grade mongodb cluster,
Create a number of databases by doing something like

use someDatabase;
db.someDatabase.insert({});

3.Creating a User/Role that is limited in access to someDatabase

The above described configuration could obviously suffer from the noisy neighbor problem (& related DoS attacks), but otherwise is the above considered a normal and standard way of providing a logical & security barrier between tenants? What are the pros/cons of doing so? If I needed to migrate one of these logical DBs to its own MongoDB instance, would that be possible?

Q1. what about the question of having multiple production databases on the same instance?

A:  It is very normal to have multiple databases on same instance.

Q2. Creating a User/Role that is limited in access to someDatabase
the above considered a normal and standard way of providing a logical & security barrier between tenants?

A: Database level authorization, works very effectively for limiting the user access rights on databases, it is safe to use.

Q3. If I needed to migrate one of these logical DBs to its own MongoDB instance, would that be possible?

A. You cannot have two databases with same name on same instance or cluster.

(If more details required, do ask)


How to index this table (a_level, b_level, item_id) I have a table of items, each of it has an a_level, b_level, and an item_id. Any b_level is dedicated to only one a_level (example: b_level 14 is "child" of a_level 2 only)

Lets say we have million of items all of them are INSERTed once and then only SELECTs are requested.

If i SELECT an item based on item_id, then i need to index the item_id column. This will make the MySQL to look all millions of items, which is bad, since i already have the a_level and b_level information. So i guess if i SELECT an item based on a specific level and i have an index on that column, the MySQL will not have to look all millions of items, just the items with that particular level.

If i INDEX both on a_level, b_level (and of course item_id) and SELECT WHERE a_level= b_level= item_id= will it be bad? I guess only INDEX on b_level and item_id and SELECT WHERE b_level= AND item_id= will be enough/the best solution?

So, since i have a_level and b_level (which any b_level as i said is "child" of only one a_level) what will be the most efficient SELECT and INDEXes created for picking up an item?

Since your table only three columns, this setup can be straightforward.

Here is an ideal table layout for items

CREATE TABLE items
(
    item_id int not null auto_increment,
    a_level int not null,
    b_level int not null,
    KEY  ai  (a_level,item_id),
    KEY  bi  (b_level,item_id),
    KEY  abi (a_level,b_level,item_id),
    KEY  bai (b_level,a_level,item_id),
    PRIMARY KEY     (item_id)
);

Here are the realm of possible queries

SELECT * FROM items WHERE id = 100; (uses the PRIMARY KEY)
SELECT * FROM items WHERE a_level = 200; (uses KEY ai)
SELECT * FROM items WHERE b_level = 300; (uses KEY bi)
SELECT * FROM items WHERE a_level = 400 order by b_level (uses KEY abi ordering all b_level values under a_level 400 and orders item_id under each b_level)

YYou may try out other combinations of SELECT...GROUP BY...ORDER BY.... You should also run EXPLAIN on every SELECT query and see which indexes are summoned for query execution. Indexes that do not appear as a chosen index can be eliminated (dropped) over the course of time.

Here were questions you asked?

  If i INDEX both on a_level, b_level (and of course item_id) and SELECT
  WHERE a_level= b_level= item_id= will it be bad? I guess only INDEX on
  b_level and item_id and SELECT WHERE b_level= AND item_id= will be
  enough/the best solution?

Those queries are not bad given the indexes I just presented.

The only reason why the item_id appears in all the indexes is to make those indexes covering indexes. That way, the retrieval of data is done using the index only, bypassing the table.