execution plan TABLE ACCESS BY INDEX ROWID cardinality

An ancient "issue" that is still with us, even in 18.4

SQL> select banner_full from v$version;

BANNER_FULL
------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 -
Production
Version 18.4.0.0.0

SQL> create table t1 as select * from dba_objects where object_id is not null;

Table created.

SQL> alter table t1 add constraint t1_pk primary key (object_id);

Table altered.

SQL> exec dbms_stats.gather_table_stats(user, 'T1', cascade=>true);

PL/SQL procedure successfully completed.

SQL> explain plan for select * from (select * from t1 order by object_id) where rownum <= 5;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3766500789

---------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |     5 |  2405 |     3   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                |       |       |       |            |          |
|   2 |   VIEW                        |       |     5 |  2405 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1    | 62395 |  7616K|     3   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | T1_PK |     5 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=5)

16 rows selected.

The above is kind of explainable, but this is where the numbers get strange (3670 comes from num_rows/num_distinct):

SQL> explain plan for select * from (select * from t1 where owner = 'SYS' order by object_id) where rownum <= 5;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3766500789

---------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |     5 |  2405 |     5   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                |       |       |       |            |          |
|   2 |   VIEW                        |       |     6 |  2886 |     5   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T1    |  3670 |   447K|     5   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | T1_PK |   102 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=5)
   3 - filter("OWNER"='SYS')

17 rows selected.

rownum implicitly triggers some first_rows_N optimization that messes up the cardinality (this was discussed on Oracle-l, 12 years ago: https://www.freelists.org/post/oracle-l/ORDER-BY-and-first-rows-10-madness,4). One workaround is to run it with all_rows hint (yes, even if it is the current setting in the session), also I had to place the hint for the index, otherwise the database would have chosen FTS in this example:

SQL> explain plan for select /*+ all_rows */ * from (select /*+ index(t1 t1_pk) */ * from t1 order by object_id) where rownum <= 5;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3766500789

---------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |     5 |  2405 |  1459   (1)| 00:00:01 |
|*  1 |  COUNT STOPKEY                |       |       |       |            |          |
|   2 |   VIEW                        |       | 62395 |    28M|  1459   (1)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1    | 62395 |  7616K|  1459   (1)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | T1_PK | 62395 |       |   131   (1)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=5)

16 rows selected.

Much better. Another reason to use analytic functions or the 12c+ syntax instead of rownum. Analytic function:

SQL> explain plan for select * from (select t1.*, row_number() over (order by object_id) as rn from t1) where rn <= 5;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 3641836603

---------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |     5 |  2470 |  1459   (1)| 00:00:01 |
|*  1 |  VIEW                         |       |     5 |  2470 |  1459   (1)| 00:00:01 |
|*  2 |   WINDOW NOSORT STOPKEY       |       | 62395 |  7616K|  1459   (1)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1    | 62395 |  7616K|  1459   (1)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | T1_PK | 62395 |       |   131   (1)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RN"<=5)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "OBJECT_ID")<=5)

17 rows selected.

Or 12c+ syntax:

SQL> explain plan for select * from t1 order by object_id fetch first 5 rows only;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 3641836603

---------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |     5 |  2535 |  1459   (1)| 00:00:01 |
|*  1 |  VIEW                         |       |     5 |  2535 |  1459   (1)| 00:00:01 |
|*  2 |   WINDOW NOSORT STOPKEY       |       | 62395 |  7616K|  1459   (1)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1    | 62395 |  7616K|  1459   (1)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | T1_PK | 62395 |       |   131   (1)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=5)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "T1"."OBJECT_ID")<=5)

17 rows selected.


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

String handling in TSQL is poor.

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

So the following approach should work

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

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

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

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

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

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

This leverages XML Schema Regular Expressions.

Create an XML Schema with a pattern constraint containing the regex

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

Add a check constraint so inserts/updates are validated

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

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

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

Insert some rows

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

Error

  Msg 6926, Level 16, State 1, Line 37

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

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

Performance

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

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

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

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

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

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

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

Using the typed XML approach took 4 minutes.

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

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


you can get this message in different error/ issues on during the backup/restore process. To identify and fix this problem.

You might using different backup version than what your current database. This is the most probable cause of what I suspect from the error message displayed. You might have a backup file from higher version and now your current server is lower version. you can always restore from lower to higher but can not backward.
The second issue could be, you might have a corrupted backup file. So first make sure you have restore able backup file.
Your backup file might be encrypted. Make sure your backup is not encrypted and if so you might need to find the decryption key.

Hopefully this will resolve your issue.


PostgreSQL 9.3: Primary key violated by a trigger INSERT

Why is t_history.id auto-incremented in the first place? If "both t and t_history have the same schema", and t.id is a serial PK, you can just copy whole rows.

I would also suggest you only copy rows you actually delete from t to t_history - in a data-modifying CTE. This way you do not have overlapping rows (which might be part of the problem).

CREATE FUNCTION update_t_history()
  RETURNS trigger AS
$func$
BEGIN
   -- Keep the newest 10, move older rows to t_history
   WITH del AS (
      DELETE FROM t
      USING (
         SELECT id
         FROM   t
         WHERE  user_id = NEW.user_id
         ORDER  BY id DESC
         OFFSET 10      -- to keep 10 (not 9)
         FOR UPDATE     -- avoid race condition
         ) d
      WHERE t.id = d.id
      RETURNING t.*
      )
   INSERT INTO t_history
   SELECT * FROM del;   -- copy whole row

   RETURN NULL;         -- irrelevant in AFTER trigger
END
$func$  LANGUAGE plpgsql;

The new row is already visible in an AFTER trigger.


Is it imperative that I backup my index files?

The first point to consider that a clustered index is actual table data.  So in that alone, I'd say yes.  Your whole database should be backed up and able to restore (there are a few exceptions to that, like read only filegroups that are subject to no DML).

The second point is that restoring a database does not rebuild or reorganize any indexes.

So yes, you should be backing up all of your data, indexes included.


How does postgres use the schema search path when making indexes?

Given the following setup:

regress=> CREATE SCHEMA A;
CREATE SCHEMA
regress=> CREATE SCHEMA B;
CREATE SCHEMA
regress=> SET search_path = B, public;
SET
regress=> CREATE TABLE bar(email text);
CREATE TABLE
CREATE UNIQUE INDEX index_bar_on_email ON bar USING btree (email);
CREATE INDEX

I cannot reproduce the problem you report in PostgreSQL 9.2:

regress=> SET search_path = A, B;
SET
regress=> CREATE TABLE bar(email text);
CREATE TABLE
regress=> CREATE UNIQUE INDEX index_bar_on_email ON bar USING btree (email);
CREATE INDEX

However, rather than using the search_path, it's safer to use explicit schema-qualification. For example, I'd re-write the above as:

regress=> RESET search_path;
RESET
regress=> SHOW search_path ;
  search_path
----------------
 "$user",public
(1 row)

CREATE TABLE B.bar(email text);
CREATE UNIQUE INDEX b.index_bar_on_email ON b.bar USING btree (email);
CREATE TABLE A.bar(email text);
CREATE UNIQUE INDEX index_bar_on_email ON A.bar USING btree (email);

The indexes are automatically created in the schema of their associated table; see:

regress=> \di B.
                  List of relations
 Schema |        Name        | Type  | Owner | Table
--------+--------------------+-------+-------+-------
 b      | index_bar_on_email | index | craig | bar
(1 row)

regress=> \di A.
                  List of relations
 Schema |        Name        | Type  | Owner | Table
--------+--------------------+-------+-------+-------
 a      | index_bar_on_email | index | craig | bar
(1 row)

Update based on question change:

Yes, what you've shown does look like a Rails adapter issue. It's checking to see whether the index exists in any schema. It should be checking to see whether the first table of the given name in the search_path has the named index.

I would write the query differently. I'd leave off the join on pg_class entirely, instead using a cast to regclass to handle search_path resolution for me. I'd use the resulting oid to search for the index. Compare original, then updated, below. Note that the updated query does require search_path to be set first.

regress=> SELECT DISTINCT i.relname, d.indisunique, d.indkey, t.oid, am.amname
 FROM pg_class t, pg_class i, pg_index d, pg_am am
 WHERE i.relkind = 'i'
 AND d.indexrelid = i.oid
 AND d.indisprimary = 'f'
 AND t.oid = d.indrelid
 AND t.relname = 'bar'
 AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname IN ('b','a') )
 AND i.relam = am.oid
 ORDER BY i.relname
;
      relname       | indisunique | indkey |  oid  | amname
--------------------+-------------+--------+-------+--------
 index_bar_on_email | t           | 1      | 28585 | btree
 index_bar_on_email | t           | 1      | 28592 | btree
(2 rows)

regress=> SELECT DISTINCT i.relname, d.indisunique, d.indkey, 'bar'::regclass::oid, am.amname
 FROM pg_class i, pg_index d, pg_am am
 WHERE i.relkind = 'i'
 AND d.indexrelid = i.oid
 AND d.indisprimary = 'f'
 AND 'bar'::regclass = d.indrelid
 AND i.relam = am.oid
 ORDER BY i.relname
;
      relname       | indisunique | indkey | regclass | amname
--------------------+-------------+--------+----------+--------
 index_bar_on_email | t           | 1      | 28585    | btree
(1 row)


Error loading example PostgreSQL database: "Could not find header for file ..."

restore.sql is a plain SQL statement file in your "dvdrental.tar" file. First create your db with the statement below;

CREATE DATABASE db_name ENCODING = 'UTF8';

After that, instead of using pg_restore you can use psql.

psql --echo-all --file=restore.sql db_name


Why in Oracle 11gR2 I can't drop the materialized view with the same user that created it?

Connect as sysdba and check if there is any entires in dba_summaries for the MV. If there is run,

drop summary <OWNER>.<MV_NAME>;`


SQL Server Management Studio login prompt will never forget users other than 'sa'

Management Studio remembers each server that you connect to and the logins used to connect to them.

The logins are remembered in from the last new login used.  For example if you connect to a server for the first time as 'sa' and then connect as 'bob', 'bob' will appear at the top of the login drop down.  Then if you connect as 'tony' then 'tony' will appear at the top of the drop down.

Reconnecting to the different users will not change their order in the list and 'tony' will remain as the default login until a new user is used.

This list can removed by removing the Server from the Server Name drop down.  This is done by expanding the server name list, hovering over the server name and pressing delete as detailed here: Deleting Old server names from "Connect to Server" dialog in SSMS


You can use Vertica's vbr utility with copycluster task to copy an entire database to another cluster. This copies everything, including the data.
See Copying the Database to Another Cluster

Alternately, you can use the EXPORT_CATALOG function. But it's limited to physical schema only, it doesn't export configurations like users, resource pools, LDAP etc.


MySQL Return Column Types from Select Statement

Why is that Questions come in pairs?  This recent one discussed the solution you want:

MySQL data precision confusion


A securable is anything that can have permissions granted, denied, or revoked on in SQL Server.  It's basically "something you can secure".

Think about it like this:  Your car is a securable.  You would have to have been given keys (permissions) to access your car (securable).

A securable is anything that can be accessible or inaccessible.

Take a look at this BOL reference on Securables.  Under the three scopes of securables (server, database, schema) you can see a listing of the contained ones.  Here is the list of securables (taken directly from BOL, but for reference):

  Server Scope Securables

  Endpoint
  Login
  Database

  Database Scope Securables

  User
  Role
  Application role
  Assembly
  Message Type
  Route
  Service
  Remote Service Binding
  Fulltext Catalog
  Certificate
  Asymmetric Key
  Symmetric Key
  Contract
  Schema

  Schema Scope Securables

  Type
  XML Schema Collection
  Object

  Aggregate
  Function
  Procedure
  Queue
  Synonym
  Table
  View


Memory Utilization

I would also run the following statement to see how SQL Server memory is being used:

SELECT
  physical_memory_in_use_kb/1024            AS sql_physical_memory_in_use_MB,
    large_page_allocations_kb/1024          AS sql_large_page_allocations_MB,
    locked_page_allocations_kb/1024         AS sql_locked_page_allocations_MB,
    virtual_address_space_reserved_kb/1024  AS sql_VAS_reserved_MB,
    virtual_address_space_committed_kb/1024 AS sql_VAS_committed_MB,
    virtual_address_space_available_kb/1024 AS sql_VAS_available_MB,
    page_fault_count                        AS sql_page_fault_count,
    memory_utilization_percentage           AS sql_memory_utilization_percentage,
    process_physical_memory_low             AS sql_process_physical_memory_low,
    process_virtual_memory_low              AS sql_process_virtual_memory_low
FROM sys.dm_os_process_memory;

At some point when you keep on increasing memory, the sql_memory_utilization_percentage column will start moving away from 100%. This is because SQL Server may not require more memory.

SQL Server Edition Features

Depending on your SQL Server Edition there are some features that are available or not. Two being the Read-Ahead and Advanced Scanning capabilities which are described in the article Reading Pages (Microsoft | SQL Docs).

SQL Server 2016 Scalability and Performamnce

Feature           | Enterprise | Standard
------------------+------------+----------
Read-Ahead        | Yes        | Yes (possible limitations; see Correction)
Advanced Scanning | Yes        | No

SQL Server 2017 Scalability and Performamnce

Feature           | Enterprise | Standard
------------------+------------+----------
Read-Ahead        | Yes        | Yes (possible limitations; see Correction)
Advanced Scanning | Yes        | No

SQL Server 2019 Scalability and Performamnce

Feature           | Enterprise | Standard
------------------+------------+----------
Read-Ahead        | Yes        | Yes (possible limitations; see Correction)
Advanced Scanning | Yes        | No

Read Ahead

  The Database Engine supports a performance optimization mechanism called read-ahead. Read-ahead anticipates the data and index pages needed to fulfill a query execution plan and brings the pages into the buffer cache before they are actually used by the query. This allows computation and I/O to overlap, taking full advantage of both the CPU and the disk.

Correction Apparently the Read Ahead feature is available in all editions of SQL Server. I found the article When are read ahead reads issued (Microsoft Social MSDN - Paul White) which answers this question. However, there can still be internal limitations depending on the edition that would affect the amount of read-aheads performed in the Standard Edition.

  i.e. Enterprise can read further ahead, if the circumstances are
  right.  The exact numbers might change between versions (that second
  quote is taken from Bob Dorr's SQL Server 2000 I/O Basics paper -
  http://technet.microsoft.com/en-us/library/cc966500.aspx).  It might
  be 2,048 pages these days - who can keep up? ;c)

Advanced Scanning

  In SQL Server Enterprise, the advanced scan feature allows multiple tasks to share full table scans. If the execution plan of a Transact-SQL statement requires a scan of the data pages in a table and the Database Engine detects that the table is already being scanned for another execution plan, the Database Engine joins the second scan to the first, at the current location of the second scan. The Database Engine reads each page one time and passes the rows from each page to both execution plans. This continues until the end of the table is reached.

(Taken from the linked Microsoft article Reading Pages)

Because you are testing on "Enterprise Edition" you may be observing a performance boost just because of these additional features.

  Caution
  There are features which may or may not be available in the version of your SQL Server Standard Edition. Some features became available within the same version but with different Service Packs!

Please ensure you are comparing Apples (Standard) with Apples (Standard) and not Apples (Standard) with Pears (Enterprise).

Rare Cases & Trace Flags

In some rare cases having too much memory can be counter-productive. This situation is described in the Knowledge Base article regarding the Trace Flag 2335.

  When you configure max server memory sp_configure option in Microsoft SQL Server to a large value, you may notice that a particular query may run slowly. But if you lower the value for this option, the same query may run much faster.

Answering Your Question

  What steps would you do to be sure that STANDARD edition with its 128GB RAM limit is enough for your production?

Compare Standard Edition with Standard Edition
Keep an eye on your baseline counters

If your counters show a decrease in performance and users are complaining, consider switching to Enterprise Edition if only for the benefits of the various features available in Enterprise Edition. Please test thoroughly before switching.
If your counters don't show a decrease in performance and users are happy, then 128 GB might be just right.


Questions on Availability Group Readable Secondary

"What is the behaviour of Read-intent only? Why the agent job failed?" You're making a promise to the server that this connection won't try to write any data to the database in the availability group. If you do try to write, that write will fail as the database cannot be written to. I believe that you need to go through the availability group listener to get this behavior, though. You've configured your secondary to accept connections only from clients that a) go through the listener and b) specify the ReadOnly application intent. Since your error says it's trying to connect to (local), I suspect that it's connecting to the instance directly.

I believe this also relates to how you've configured your Agent job. What are you specifying for the connection string? If it hasn't specified ApplicationIntent=ReadOnly (or something similar), it will get directed to the primary.
If it is all reads, your connection string likely doesn't specify the database. It has to include the database name so that SQL Server can tell which routing list to use (your server could have multiple Availability Groups running on it).  See: AlwaysOn Secondary readable - can't connect with applicationintent=readonly

"What are the difference between read intent only and option yes on readable secondary?" The difference between "read-intent only" and "any" for a database in the secondary role determines what kinds of connections it will accept. The former says that it will only accept connections from clients that pinky swear that they're not going to write anything (i.e. specify ApplicationIntent=ReadOnly). "Any" says it'll take anything (writes will still fail, but the requirement of the client to say that they're not going to write anything is relaxed).

You asked in a comment:
Can I specify the connection string AppicationIntent=ReadOnly in SQL Agent Job T-SQL script? Also, I am still a bit confused by that behaviours, given that my insert statement indeed is targeting the MySQL platform instead of SQL Server. Why the "Read-Intent Only" settings cannot let me go, especially I am not trying to connect to (local)?

It depends on what kind of SQL Agent job you have. For example, I've used ApplicationIntent=ReadOnly in SSIS packages, but I don't know how to specify that for things like a standard Transact SQL job. As to why it failed, if you have set up your secondary to only accept connections that explicitly set their ApplicationIntent to ReadOnly and your job didn't do that, it will be rejected (even though you know that it isn't going to do any writes).


Indexes all Exist in non Primary Filegroup - Advantages?

Not sure if I can list any benefits but that was fashionable about 15 to 20 years ago.
Well actually the db is accessible once the Primary Filegroup is online (if you have enterprise edition) - so you might be back online faster (albeit without any access to your data).


Discovered what the problem was.

sqlplus SYS/guardium111@XEPDB1 AS SYSDBA
sqlplus paul/guardium111@XEPDB1

OR

conn paul/guardium111@XEPDB1


MySQL - Slow InnoDB Write Speeds on Windows 10

RECOMMENDATION #1
You have innodb_thread_concurrency at 17. It really needs to be zero(0). Why ?
Rather than rehash stuff I wrote in the past, please read the 7 posts I wrote about innodb_thread_concurrency being the best choice at 0.
RECOMMENDATION #2
Using a small log buffer can be a bottleneck when writing to the redo logs.
Please read my 3-yr-old post MySQL transaction size - how big is too big? why it should be bigger
I recommend innodb_log_buffer_size being set to 128M
RECOMMENDATION #3
The redo log needs to larger as well. You have
You have innodb_log_file_size at the default size for MySQL 5.6 : 48M
I recommend setting innodb_log_file_size = 1G.
RECOMMENDATION #4
You have flush_time set to 0. IMHO For any Windows OS, that's a big NO-NO.
The default is 1800 for Windows. I don't trust MySQL flushing to disk every 30 min in Windows.
I recommend flush_time to 300 (every five(5) minutes)
RECOMMENDATION #5
You should increase the IO threads
The defaults are
innodb_read_io_threads = 4
innodb_write_io_threads = 4

Percona Server's defaults are
innodb_read_io_threads = 8
innodb_write_io_threads = 8

You should start with 8 for both innodb_read_io_threads and innodb_write_io_threads.
GIVE IT A TRY !!!
I have so much more to recommend in terms of per-connection buffers : How costly is opening and closing of a DB connection?


Are you running this test between RDS and MySQL workbench on your own machine?

The MySQL client (and MySQL workbench) are including the time it takes for RDS to return the value of "1" to your workstation (from my workstation on the other side of the world, the response from RDS US West takes 280ms).

If you try enabling profiling on the RDS service, you can see how long the query is really taking - it's completing on your RDS instance in fractions of a millisecond.

mysql> set profiling = 1;

Query OK, 0 rows affected (0.00 sec)

mysql> select 1;

1
1

1 row in set (0.27 sec)

mysql> show profiles;

Query_ID   Duration      Query
       1   0.00016200    select 1

mysql> show profile for query 1;

Status                  Duration
starting                0.000052
checking permissions    0.000008
Opening tables          0.000012
...
executing               0.000013
end                     0.000008
...
cleaning up             0.000003

12 rows in set (0.28 sec)

set profiling = 0;

Query OK, 0 rows affected (0.28 sec)


Keep in mind that the backup does not necessarily reflect the size of the database (including the log) on disk - even if compression is not used.

You are likely trying to expand a log file that matches the size on the original database, and there is not enough space on the disk. You should check the size that the restore operation is trying to restore to looking at the size column for the files in the following query:

RESTORE FILELISTONLY FROM DISK = 'path:\to\file.bak';

This may or may not match the current size of the original source database, depending on when the backup was taken and what type of operations have been performed against that database in the meantime.

If you don't have that much space to spare on the disk you're trying to restore to, you have multiple options:

free space on the drive;
use WITH MOVE to place the data and/or log files on a different drive with sufficient space; or,
shrink the log file on the original database (this may require performing a log backup or a checkpoint to be effective, depending on recovery model) and then take a new, COPY_ONLY backup and restore that.


Why shouldn't INFORMATION_SCHEMA views be used to determine the schema of an object?

Yes, the statement there is inaccurate. I talk about this in this blog post (and complained about it in this ancient Connect item), where I implore people to use the sys catalog views over INFORMATION_SCHEMA:

  The warning really should state that the INFORMATION_SCHEMA views are incomplete; the wording above implies that they might be incorrect, which in itself is incorrect.

In 2011, they said:

  Hi Aaron,
  That particular wording has been in the topic since 2005. But, I see your point. We'll revist the wording and try to make it more clear.

Now that we can submit pull requests for suggested changes to the documentation, it might be time for someone to make them pay attention to it again. 13 years is long enough.


SQL Server 2012 extended event session to expose the network protocol

Found the answer.

The login event is correct and the network protocol is exposed via the options_text action. options_text was always blank for me previously and this needs to be set on via SET collect_options_text=(1). An example session might be: -

CREATE EVENT SESSION [Login] ON SERVER
ADD EVENT sqlserver.login(SET collect_options_text=(1)
    ACTION(
    sqlserver.session_id
    ,sqlserver.username
    ))
ADD TARGET package0.ring_buffer
WITH (
    MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=1 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=ON,
    STARTUP_STATE=ON
)
GO

And this would then populate options_text with something like: -

  network protocol: TCP/IP  set quoted_identifier on  set arithabort off
  set numeric_roundabort off  set ansi_warnings on  set ansi_padding on
  set ansi_nulls on  set concat_null_yields_null on  set
  cursor_close_on_commit off  set implicit_transactions off  set
  language us_english  set dateformat mdy  set datefirst 7  set
  transaction isolation level read committed

I found this out be chance by creating a session from the "Connection Tracking" Microsoft shipped extended events template.

SSMS | Management | Extended Events | Sessions | Right click | New session | General page | Template = Connection Tracking.

That template includes "SET collect_options_text=(1)" and I wasn't previously aware such an option existed.

An example is also given in this sql-server-performance article. The last screenshot shows options_text populated with "network protocol: LPC.." LPC is shared memory.


Is it possible to delete cache in Mongo?

As per MongoDB documentation here PlanCache.clear() removes all cached query plans for a collection.

The method is only available from the plan cache object of a specific collection; i.e.

db.collection.getPlanCache().clear()

Required Access

On systems running with authorization, a user must have access that includes the planCacheWrite action.

For your further refernce Manage Users and Roles in MongoDB and Collection-Level Access Control in MongoDB


SQL Server 2017: How does Interleaved Execution work?

The Devil Is In The Table Variable

Interleaved execution is aimed at correct cardinality misestimated in Multi-Statement Table Valued Functions.

In prior versions of SQL Server, these functions would always produced rather shoddy estimates:

2014, 2016: 100 rows
2005 - 2012: 1 row

Needless to say, this could cause a lot of problems when joining to other tables.

While selecting data from a table variable does not inhibit parallelism on its own, the low row estimates would often contribute to low query costs, where parallelism wouldn't be considered.

With Interleaved Execution, cardinality estimation is paused, the subtree for the MSTVF is executed, and optimzation is resumed with a more accurate cardinality estimate.

How do I know if my MSTVF receives interleaved execution.

Like with Adaptive Joins, Interleaved Execution is noted in the query plan. Unlike Adaptive Joins, it is not noted in estimated plans, at least as of this writing.

The plan shape for a MSTVF with Interleaved Execution is a bit different from a typical plan that has a MSTVF in it.

You'll see the Table Valued Function operator at the top of the plan, and the scan of the Table Variable where the TVF operator would normally be in the graphical plan.

When hovering over the TVF operator, you'll see the attribute IsInterleavedExecuted set to True, as well as a an estimated number of rows that may very nearly reflect reality. Hurrah.

Are there any Extended Events to troubleshoot when Interleaved Execution doesn't occur?

Yes, a whole bunch:

Note that some of these are in the Debug channel, which isn't selected by default when searching for Events to Extend.

Does Interleaved Exection Require ColumnStore Indexes?

No, they'll work either way. Here's an example:

SELECT u.Id, mj.*
FROM   dbo.Users_cx AS u --ColumnStore
JOIN   dbo.MultiStatementTVF_Join(0) AS mj
ON mj.UserId = u.Id
WHERE  u.LastAccessDate >= '2016-12-01';

SELECT u.Id, mj.*
FROM   dbo.Users AS u --RowStore
JOIN   dbo.MultiStatementTVF_Join(0) AS mj
ON mj.UserId = u.Id
WHERE  u.LastAccessDate >= '2016-12-01';

These queries each join to different tables. One ColumnStore, one not. They both get Interleaved Execution plans.

When does Interleaved Execution work?

Right now, it only works with MSTVFs where the correlation is done outside of the function.

Here are a couple examples:

This function has no inner correlation, meaning there's no WHERE clause predicated on a table column and a passed in variable.

CREATE OR ALTER FUNCTION dbo.MultiStatementTVF_Join
(
    @h BIGINT
)
RETURNS @Out TABLE
(
    UserId INT,
    BadgeCount BIGINT
)
AS
    BEGIN
        INSERT INTO @Out ( UserId, BadgeCount )
        SELECT   b.UserId, COUNT_BIG(*) AS BadgeCount
        FROM     dbo.Badges AS b
        GROUP BY b.UserId
        HAVING   COUNT_BIG(*) > @h;
        RETURN;
    END;
GO

This function is the opposite, with a predicate on the UserId column with a passed in variable.

CREATE OR ALTER FUNCTION dbo.MultiStatementTVF_CrossApply
(
    @h BIGINT,
    @id INT
)
RETURNS @Out TABLE
(
    UserId INT,
    BadgeCount BIGINT
)
AS
    BEGIN
        INSERT INTO @Out ( UserId, BadgeCount )
        SELECT   b.UserId, COUNT_BIG(*) AS BadgeCount
        FROM     dbo.Badges AS b
        WHERE    b.UserId = @id
        GROUP BY b.UserId
        HAVING   COUNT_BIG(*) > @h;
        RETURN;
    END;
GO

It's a common misconception that CROSS APPLY won't work. The real limitation is noted previously. The inner-function correlation is the deal breaker.

SELECT u.Id, mj.*
FROM   dbo.Users AS u --RowStore
CROSS APPLY dbo.MultiStatementTVF_Join(0) AS mj
WHERE  mj.UserId = u.Id
       AND u.LastAccessDate >= '2016-12-01';

SELECT   TOP 1 u.Id, mj.*
FROM     dbo.Users AS u --RowStore
CROSS APPLY dbo.MultiStatementTVF_CrossApply(2147483647, u.Id) AS mj
WHERE    u.LastAccessDate >= '2016-12-01'
ORDER BY u.Id;


Is there a problem creating only differential backups every hour?

Storage is not why you take log backups.  You take log backups when the database is in full recovery model, and you need point-in-time recovery between full or incremental backups.

If your business can afford only 1 hour of lost data, then I'd typically setup nightly full backups for smaller databases, with log backups every 30 minutes during business hours (or even 24-hours per day).

If you had the database in simple recovery model, and each database was only a couple of hundred megabytes, you could simple take full backups every hour, or half hour.

Essentially, the decision comes down to answering these questions:

What is my recovery point objective?
What is my recovery time objective?

See Wikipedia for great details about what those two things actually mean.

If you're storing high-value, business-critical data in those databases, you should understand log backups, and the transaction log and recovery in general (link to my blog), to avoid being in a situation where you're unexpectedly missing data, or down for an extended period of time.

In your case, I would also do a log backup after every diff backup just to ensure the log doesn't grow out of control.


Well, no, not exactly. My fault for not making the wording on that a little better.

You're using sp_BlitzFirst, and here's something similar to what you're seeing:

It's reporting CPU usage and where I found it in the DMVs - the ring buffer. The ring buffer isn't CAUSING the high CPU use.

To find queries doing high CPU use, use sp_BlitzCache @SortOrder = 'cpu'.

For more details, check out this post from Microsoft's Premier Field Engineering team on using ring buffers to track CPU usage inside SQL Server.


Slow access to table in postgresql despite vacuum

1)  I would have tried cluster followed by analyze.  My only hesitation is I am not 100% sure what happened here.  Is it possible there was some index corruption as well?  Reindex might have helped? Given that the stats entries were way off, is it possible something was corrupt elsewhere regarding the relation's OID?

2)  I have no idea.  I have never seen anything like that before.  I would need a lot more diagnostic information even to hazard a guess.  If it happens again it would be worth knowing the physical size of the table, for example.

Edit:  Remembering that 8.4 still had max_fsm_pages, if that was set too low between vacuums that could cause this sort of table bloat.

Edit2:  Vacuum full, however should have been able to handle bloat due to max_fsm_pages, at least it does in my experience, suggesting again to me that something deeper was wrong.


keep in mind the following: generally SAN in sthe best option for clustered environments. In non clustered Environment you can opt for locl disks.
Moving from SAN/Local Disk to SMB Windows share can hide some potential problems.

Using SMB network connectivity becomes a very very important piece of your architecture. Probalby you will have to use dedicated nics on database server. You have to consider who/what is sharing disks through SMB. Especially if the "sharing" server is a cheap NAS, you have to pay attention about performance and to the number/weight of all other clients.

The best uses for SMB share are:

build low cost clustered environments, specially when you have lightweight
databases
Test/Disaster recovery/developement environments;
Storing historical data;
Emergecy/temporary storage;
During database migration/upgrade processes.

If you want to use SMB in production environment I think you have to build a strong network infrastructure:

Nics redundancy;
Dedicated nics;
impement traffic isolation (VLAN implementation);
Buy robust NAS solution;
COnsider involving 10GB network interfaces;
Keep under control network performance, check periodically network interfaces counter. Tune parameter like MTU to obtain the best performance for your physical infratructure.


table size seems high for the amount of records in db

It's hard to say anything definitive without knowing how the table was defined or what you did to "blank out" the columns, but NULLs still take up space and you still have an NVARCHAR(MAX) MatchedKeywords column which could be taking up plenty of space.

Sometimes tables can change in size after a REBUILD. If you feel that the table is taking up too much space right now I suggest trying that.


SELECT group_concat(tag) AS tags
  FROM table
 WHERE tag IN (123,124,234)
 GROUP BY video
 ORDER BY video
;


Would creating an alias be any use to you?


Which mechanism is best for previous data archived in oracle?

If you used the optional feature table partitioning. You could easily drop the previous years partitions without affecting the current year. If it were me I would partition by month and sub partition by hour. You might want to keep more than the current year online. Hence partition by month would allow you to keep a rolling 11 months if you wanted and drop the oldest partition every month.


you are missing a key based on what you can update the data ... obviously there is always the last value in table1.my_col because what you are doing is looping through all values and updating the column in the table1..

The database design is not correct ..  Can you share your table structure / definition ?

Then, we can come up with some solution for you

EDIT:
if you wanna just move those records from one table to another ...

try this:

insert into t1 (my_col) as select acct_num from t2 where rownum not in ('1', '10','38', '39', '46', '77');


The approach I take is broadly as follows (and this is by no means complete):

Requirements
You need to know the goals of the to be situation.

specifics of platforms (hardware, software), versions of these. There may be dependencies in terms of feature availability
features used in "from" situation and needed in "to" situation. Think of

queueing
clustering
high availability
backup and recovery

Analyse current situation for

requirements missing from above
DBMS or platform specific features being used

Design for an entirely automated and repeatable approach. This makes it

testable
able to run overnight/weekend in case of a long process
allows for an iterative solution where the database becomes more and more complete during the life of the project

Determine dependencies between systems both on the "from" side and on the "to" side. This will affect the "flow" of the migration.
Decide how the data will be migrated. Amongst others, choices are:

dump and reload, probably via some database agnostic format

if reloading, it may be easier to go via "temporary" tables and then populate the real target tables from the temporary ones. I've had good experience with this approach.
this also makes it easier to allow for "fallout" which can be manually corrected after cutover (but you need to keep this under control too).

"suck" the data from the "from" database into the "to" database using some database connection technology (eg Oracle Gateway, PostgreSQL foreign data wrappers). This approach is usually also best when wanting to perform a migration where the target DBMS is the same as the source.
decide whether to use scripting (SQL scripts mainly, but possibly also Perl/awk etc) or a tool (eg Oracle used to have a product called Migration Workbench or an ETL tool can be used for this purpose) or even "programmed" solutions in Java/C# etc. This decision will largely be driven by the skillset at hand. My preference is to use SQL wherever possible.

Build it.

If the target data model is different, determine mappings. An ETL tool can be handy in these cases, but well written SQL can be just as clear. Like all code, try to keep the documentation on the mapping with the code rather than in a separate document.


Should order of column in where clause be same as the index order?

So, my question is does the order of columns in Where clause matters

No. The order of columns in the WHERE clause does not matter at all.


Query to determine percentage composition on a specific grouping of records

Assuming your table is called [t], and that the Value column has been called Val (Value is a reserved word and complicates things), you can create a view (query) with the following SQL definition:

SELECT
    t0.CPTY, t0.Industry, t0.Val * 100.0 /
    (SELECT sum(t1.Val) FROM t AS t1
      WHERE t1.CPTY = t0.CPTY) AS PerCentIndustryPerCpty
FROM
    t AS t0;

You can make it using the "SQL view" instead of the graphical designer for views (querys).

You get this result:

If you don't feel comfortable with the SQL view, this can also be done within the GUI, in two steps:

Create a Query named TotalsPerCPTY with a column named TotalValPerCPTY that has the SUMs of Val GROUPed BY CPTY, as shown in the following image:

Create the ViePerCents Query by joining the t table together with the TotalsPerCPTY view, using the CPTY column, with one computed column that computes Val / TotalValPerCPTY * 100.0:


How to find dropped or ghosted columns in Azure SQL DB?

Since Azure database is MSSQL server then you can execute the following command to find how many ghost process per database

SELECT sum(ghost_record_count) total_ghost_records, db_name(database_id)
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL)
group by database_id
order by total_ghost_records desc

And if you are looking for more detailed report then you can execute the following command

SELECT
db_name(database_id),
object_name(object_id),
ghost_record_count,
version_ghost_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(N'db_name'), OBJECT_ID(N'table_name'), NULL, NULL , 'DETAILED');

Looking for long term solution to start recording this data then you can check this post. This will create table that store ghost records in the long term, but I recommend you to create it on test environment first to see if this is what you actually want.


Should I include as much information as possbile to help the optimiser?

The best way to determine this is to simply look at the execution plans. Typically the optimizer will "condense" a lot of the operations in a query down. For example, if you include "WHERE 1=0" in your query it will see that, know it to be false, and do as little work as possible to return an empty set. In fact, in this case, it does 0 I/O operations as far as STATISTICS IO is concerned.

Since, by default, the optimizer is going to shuffle your JOINs around into an order that it thinks makes the most sense, I would be very surprised if it didn't also get rid of the redundant conditions in the WHERE clause.

EDIT: This is all assuming the WHERE clause is just going to be repeating what was already established in the JOIN clause. In your example you are including the columns used in the JOIN clause in the WHERE clause, so that equality has already been evaluated once.

I did a quick test on my end and the execution plans were identical, but I still encourage you to give it a try. Also, check out Benjamin Nevarez's book on the optimizer: https://www.red-gate.com/products/sql-development/sql-prompt/entrypage/sql-query-optimizer-ebook

I forgot to mention the amazing tool that is SQL Sentry Plan Explorer: http://www.sqlsentry.com/products/plan-explorer/

Great tool to look at plans, and the free version is VERY capable.


Accessing Encrypted SQL Server database through a web-based application

I see this is an old thread so I don't know if you still monitor it. I'm from NetLib Security. Can I suggest you open up a support incident here?
https://netlibsecurity.com and then click Support.


How to run DB Admin Tool (GUI) in IBM DB2 Express-C?

DB2 Express-C version 10 and later no longer include a full suite of administration tools as part of the installer (the "Administration Tools" you installed might only include the ones you're looking at...).

Like MySQL and SQL Server, the administration tools are now available as a separate download.

The official tool you're looking for is IBM Data Studio, which is available free of charge.

Older versions of DB2 Express-C used to include the legacy Control Center apps, but IBM has deprecated those and is really pushing the Eclipse-based Data Studio.


Should a DBA be able to write basic T-SQL?

I have been lucky to have had the opportunity to work with many great people in my career and I used to think the same as you do now.

I've met DBA's who were better at writing T-SQL than me, and I've worked with DBA's that don't know a single line of T-SQL. While I personally find the latter frustrating, I try to keep an understanding of the task they are doing in mind when I judge their skill set.

To be more specific, some of the DBA's I have known who could not write T-SQL have been amazing at PowerShell, SMO or were a whiz with SSMS or another GUI and so never needed to write a query.

More importantly, the question comes down to can they do the job they were hired to do? If they can, and they are good at it, then so be it. You aren't asking them to do yours so don't stress about it.

If they need to run some T-SQL, walk them through it if they are interested, guide them to some help documentation. Some people may want to learn and have never had the opportunity to do so. Others are not interested and just want to clock in and out each day. Each to their own.

As long as you get what you need from the team, life is good.


"can't return a result set in the given context" error when calling stored procedure - differences between mysql 5.0 and 5.5?

Older versions of phpMyAdmin have issues with MySQL. This has been asked 8 times in StackOverflow with the tag mysql-error-1312.

This bug was seen and addressed in MySQL 5.0.09 beta about 6 weeks after MySQL 5.0.26 was released Oct 09, 2006. Still, you got this error.

According to http://qasimbadami.wordpress.com/tag/mysql-error-1312/, you should make sure you are using mysqli / PDO.


What's best practices for changing a user's email in an application?

It sounds like you are using the email address as a key, which is not good practise. Anything that can change is not a good candidate to be a key value which includes email addresses and phone numbers. Other reasons it is not a good idea include:

It could be NULL: maybe there are people in your target audience who don't use email so won't have an address?
Someone could have more than one address/number
People might share an address/number so it is not unique

If you do not have a non-changing, unique, never-NULL value that is therefore a candidate to be the key value, it is usual to have a generated surrogate key: most commonly an integer value or UUID that is decided when the record is created and does not depend on any properties of the record. You appear to have such a key already in your announcement_contacthistory table, the ID column.

This is particularly important if the key value is referenced by foreign key values in other tables as appears to be the case here. While SQL Server and Azure SQL DB have ON UPDATE CASCADE which makes changing such referred values possible and easy from the coders point of view (just update the primary value, the engine will go away and fix up all the references) this can be very inefficient resulting in a lot of related changes being made. If announcement_contacthistory had a reference to announcement_contact.id instead of announcement_contact.email you could change the email of a user without needing to update all the references in announcement_contacthistory and elsewhere.

Of course you may want to keep a history of email addresses so you know what was used at the point in time the messages was sent, either by still recording the address in the message record or by keeping a history of the values in the contact table (perhaps using a system versioned temporal table).

To find info on the relevant theory and practise, search around the following terms:

Key
Candidate Key
Surrogate Key
Primary Key
Unique Key


How to identify which schema calls a function in another database without schema?

You can get the current schema by using the System Information Functions

current_schema[()] returns text: name of current schema
current_schemas(boolean) returns text[]: names of schemas in search path, optionally including implicit schemas

I don't think dblink passes the context of the functions executed from the original database. So you'll have to wrap those functions and explicitly pass the context, perhaps by making the function variadic

CREATE OR REPLACE FUNCTION public.guardarTransaccion("tipo" text, "idRemito" int4, "idPicking" int4,
  context DEFAULT '{}'::text[] )

Then call it like the context explicitly. I would probably just think of a better way to engineer the system.


Detect multiple valid date periods for same id, interleaved with non-valid periods

Without CTEs and window functions:
SELECT colony, MIN(date_in) date_in, date_out
FROM ( SELECT t1.colony, t1.date_check date_in, MAX(t2.date_check) date_out
       FROM test t1
       JOIN test t2 ON t1.colony = t2.colony
                   AND t1.date_check < t2.date_check
                   AND t1.protection = 'Y'
                   AND t2.protection = 'Y'
                   AND NOT EXISTS ( SELECT NULL
                                    FROM test t3
                                    WHERE t1.colony = t3.colony
                                      AND t1.date_check < t3.date_check
                                      AND t3.date_check < t2.date_check
                                      AND t3.protection != 'Y' )
       GROUP BY t1.colony, t1.date_check ) subquery
GROUP BY colony, date_out
ORDER BY colony, date_in;

With CTE and window functions:
WITH
cte1 AS (SELECT *,
                CASE WHEN protection = 'N' OR LAG(protection) OVER (PARTITION BY colony ORDER BY date_check) = 'Y'
                     THEN 0
                     ELSE 1 END AS row_in,
                CASE WHEN protection = 'Y' OR LEAD(protection) OVER (PARTITION BY colony ORDER BY date_check) = 'N'
                     THEN 0
                     ELSE 1 END AS row_out
         FROM test),
cte2 AS (SELECT *, SUM(row_in) OVER (PARTITION BY colony ORDER BY date_check) group_no
         FROM cte1)
SELECT colony, MIN(date_check) date_in, MAX(date_check) date_out
FROM cte2
WHERE protection = 'Y'
GROUP BY colony, group_no
ORDER BY colony, date_in;

https://dbfiddle.uk/?rdbms=postgres_9.5&fiddle=bf769987d7211b73e89c0564bc6902a5


SQL Server Log Shipping restore fail - The Log Is too recent to apply

I use the following query to get a good overview of running Backups / LOG Shipping Status.

SELECT  [b].[database_name] ,
            [b].[backup_start_date] ,
            [b].[backup_finish_date] ,
            [b].[type] ,
            [b].[first_lsn] ,
            [b].[last_lsn] ,
            [b].[checkpoint_lsn] ,
            [b].[database_backup_lsn]
            --,f.media_set_id
            ,f.physical_device_name,
            script='RESTORE LOG DBNAME FROM DISK=N''' + f.physical_device_name + ''' WITH NORECOVERY; '
    FROM    [msdb].[dbo].[backupset] AS [b]
    LEFT JOIN  msdb.dbo.backupmediafamily f ON b.media_set_id = f.media_set_id
    WHERE   [b].[database_name] = 'DBNAME'
        AND [b].[backup_start_date] >= '2017-05-10 13:00:00.000' ORDER BY [b].[backup_start_date] asc;

It gives a picture of the Backups taken and so you will See which Backups you need to RESTORE them on secondary DB.

Sample :
The Backups Marked yellow need to be restored on 2ndary, only those.

Regarding your NUL backup, have you already scripted All of the stored procedures in Place and Looked for NUL in there?

Have you observed any Logins the time this NUL Backup happens? If a login FROM outside starts this Backup you could cut the login off the Server.

Hope it helps.


Job is Storing History But 'Include Step Output in History' is not Checked

That check box does not prevent job step history from being recorded/stored. SQL Server is going to do all this by default and that I know of you cannot turn it off.

The check box referenced is for telling SQL Agent to store additional or more detailed job step output. It would store this information to msdb.dbo.sysjobstepslogs. Which from my testing just checking that box alone does not do anything. You would still find the table empty if you queried it. Once I also checked Log to table, ran the job again, and then it showed a record for the job step.

This would enabled on a job that runs DBCC CHECKFILEGROUP command for a database:


Can I restrict user's access on a certain IP?

When you create a user then you can specify Authentication Restrictions
Would be like this:
use admin
db.createUser(
   {
     user: "dbuser",
     pwd: "password"
     roles: [ { role: "readWrite", db: "reporting" } ],
     authenticationRestrictions: [ {
        clientSource: ["192.0.2.0"],
        serverAddress: ["198.51.100.0"]
     } ]
   }
)

But for me it is not clear, if you are looking for this.


Is there a bug with PL/Scope in combination with associative arrays?

It is a bug, because the documentation states:

  USAGE_CONTEXT_ID is a reflexive foreign key to USAGE_ID

UPDATE: A bit of troubleshooting...

all_identifiers.usage_id comes from sys.plscope_action$.action#

Given:

SQL> /

IDENTIFIER   USAGE       TYPE                 USAGE_ID USAGE_CONTEXT_ID LINE COL
------------ ----------- ------------------ ---------- ---------------- ---- ---
TQ84_PKG_C   DECLARATION PACKAGE                     1                0    1   9
PROC_1       DECLARATION PROCEDURE                   2                1    2  15
TQ84_PKG_C   DEFINITION  PACKAGE                     1                0    1  14
NUM_T        DECLARATION ASSOCIATIVE ARRAY           2                1    3  10
NUMBER       REFERENCE   NUMBER DATATYPE             3                2    3  28
VARCHAR2     REFERENCE   CHARACTER DATATYPE          4                3    3  44
PROC_2       DECLARATION PROCEDURE                   5                1    5  15
PROC_2       DEFINITION  PROCEDURE                   6                5    5  15
PROC_1       DEFINITION  PROCEDURE                   7                1    9  15
V_NUM        DECLARATION VARIABLE                    8                7   10   9
NUM_T        REFERENCE   ASSOCIATIVE ARRAY           9                8   10  15
V_NUM        REFERENCE   VARIABLE                   11               10   12  12
PROC_2       CALL        PROCEDURE                  12               10   13  12

13 rows selected.

SQL>

... we're looking for the elusive sys.plscope_action$.action# = 10 for this object:

SQL> select a.obj#, a.action#, a.signature, a.action, a.line, a.col, a.context#, o.name, o.type#
from sys.plscope_action$ a, sys.obj$ o
where a.obj# = o.obj#
and name = 'TQ84_PKG_C'
and action# = 10;

      OBJ#    ACTION# SIGNATURE                            ACTION LINE COL   CONTEXT# NAME                                TYPE#
---------- ---------- -------------------------------- ---------- ---- --- ---------- ------------------------------ ----------
     83504         10 0C679E5FFBC26144A7F875495F68C2E1          3   12  18          7 TQ84_PKG_C                             11

SQL>

That's the missing row.

The ALL_IDENTIFIERS view joins sys.plscope_identifier$ with sys.plscope_action$ on the signature column - there's no matching row in sys.plscope_identifier$, which I suspect is causing the problem.


what columns to include in the index to improve the performance of the following update?

I fail to see how included columns can be anything but overhead for an UPDATE. If you think about it... Included columns are there to eliminate the lookup for each row. But you still have to get to the row (do the lookup) in order to modify it for an update. And every time a column is in an index (included or key) this value have to be updated (in addition to the actual data) when a modification occurs.


transaction log backup format sector size

Correct. You can't restore backups to storage with a different sector size (this is something to do with the way the log is structured internally where it would be unsafe to do so - there's a big article on it which is as clear as mud).

Are you using Storage Spaces?

  The problem with all of this is that the when creating a virtual disk
  with Storage Spaces, if you do not specify a LogicalSectorSize via the
  Powershell cmdlet, the system will create a virtual disk with a
  LogicalSectorSize equal to the greatest PhysicalSectorSize of any disk
  in the pool. This means if you have SSD’s in your pool and you created
  the virtual disk using the GUI, your virtual disk will have a 4k
  LogicalSectorSize.

If so then you're out of luck, you can reformat the Azure disks as described to use 512 byte sectors but you'll then get a massive and permanent performance hit.

There's no way to backup/restore that database onto the new storage. You'd have to export the database schema and data from your old storage and import it to a new database on the new storage. Crazy, I know.


Get WAL files from AWS RDS PostgreSQL instance

Update: I've posted about this to the AWS forums - please go chime in and ask for it there.

At time of writing, Amazon RDS does not support physical replication outside RDS. You can GRANT users the REPLICATION right using an rds_superuser login, but you can't configure replication entries for outside IPs in pg_hba.conf.

Furthermore, when you create a DB parameter group in RDS, some key parameters are shown but locked, e.g. archive_command, which is locked to /etc/rds/dbbin/pgscripts/rds_wal_archive %p. AWS RDS for PostgreSQL does not appear to expose these WALs for external access (say, via S3) as it would need to if you were to use WAL-shipping replication for external PITR.

So at this point, if you want wal-shipping, don't use RDS. It's a canned easy-to-use database, but easy-to-use often means that it's also limited, and that's certainly the case here. As Joe Love points out in the comments, it provides WAL shipping and PITR within RDS, but you can't get access to the WAL to that from outside RDS.

So you need to use RDS's own backup facilities - dumps, snapshots and its own WAL-based PITR.

Even if RDS did let you make replication connections (for pg_basebackup or streaming replication) and allowed you to access archived WAL, you might not be able to actually consume that WAL. RDS runs a patched PostgreSQL, though nobody knows how heavily patched or whether it significantly alters the on-disk format. It also runs on the architecture selected by Amazon, which is probably x64 Linux, but not easily determined. Since PostgreSQL's on disk format and replication are architecture dependent, you could only replicate to hosts with the same architecture as that used by Amazon RDS, and only if your PostgreSQL build was compatible with theirs.

Among other things this means that you don't have any easy way to migrate away from RDS. You'd have to stop all writes to the database for long enough to take a pg_dump, restore it, and get the new DB running. The usual tricks with replication and failover, with rsync, etc, won't work because you don't have direct access to the DB host.

Even if RDS ran an unpatched PostgreSQL Amazon probably wouldn't want to permit you to do WAL streaming into RDS or import into RDS using pg_basebackup for security reasons. PostgreSQL treats the data directory as trusted content, and if you've crafted any clever 'LANGUAGE c' functions that hook internal functionality or done anything else tricky you might be able to exploit the server to get greater access than you're supposed to have. So Amazon aren't going to permit inbound WAL anytime soon.

They could support outbound WAL sending, but the above issues with format compatibility, freedom to make changes, etc still apply.

Instead you should use a tool like Londiste or Bucardo.


Check out Paul Randal's recommendations: Set the database to EMERGENCY mode, the database will be accessible to sysadmins only. It'll be read-only, so nothing can be written to the transaction log

Using EMERGENCY mode to access a RECOVERY PENDING or SUSPECT database


Postgresql function equivalent to Excel's NORMSINV function?


Why are some write requests throttled whereas the average consumed write capacity is below the provisioned write capacity?

It doesn't have to be a hot key or partitioning issue where the total provisioned write capacity is spread across the partitions.

You have a 5 minute average chart there. Technically you could have a case where you do have zero write activity for a 4:50 minute period and then have 10 seconds of ridiculous amounts of writes. These will most likely exceed you provisioned writes while the average still is below the limit.


Are sql tuning ways same for different DB engine？

The "tricks" are to convince the DBMS to behave the way you want it to. These are large, complex pieces of software. No two behave exactly the same. So tricks that affect one may or may not work with another.

Good practice, such as only retrieving columns that a actually needed, is applicable everywhere.


How we can enable `index_condition_pushdown`?

index_condition_pushdown is used for Queries with the range, ref, eq_ref, and ref_or_null access methods, i.e when there is a need to access full table rows.

Where as Index Condition Pushdown is enabled by default; it can be controlled with the optimizer_switch system variable by setting the index_condition_pushdown flag.

To Switch:

 SET [GLOBAL|SESSION] optimizer_switch='command[,command]...';

for more you could read here:
http://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html

and http://dev.mysql.com/doc/refman/5.6/en/switchable-optimizations.html


Postgres replication/replication to server outside firewall

You can do this with the log-shipping standby functions, which is really an extension of continuous archiving.

You first configure PostgreSQL to write all* changes to the Write-Ahead Log by saying:

wal_level = hot_standby
archive_mode = on

Once a log file is full (16 MB of writes) or a configurable timeout occurs (archive_timeout), PostgreSQL starts writing to the next log file, and it calls your archive_command with the filename of the log it just finished writing. Your archive_command would be a script that uses some other appropriate technology to push this log file off the master -- maybe you want to upload it to an object store like Amazon S3, or maybe you want to use SCP to push it straight onto the slave, or maybe you want to do something else entirely.

You'd set up the slave similarly to the master, except its data directory would include a recovery.conf saying standby_mode = on. If the master copies logs straight into the slaves' pg_xlog directory, you're done; if not, the slave will need a restore_command script to act as an inverse of archive_command and retrieve the indicated archive log from wherever you put it.

Lastly, copy over a base backup (as described in the continuous archiving documentation), start PostgreSQL, and your slave will be following the master without needing any direct communication.


How to make column data in upper case by default in SQL Server 2008?

You could use a trigger to accomplish this but why not deal with this in the front-end of the application? Or, if you're displaying the data somewhere else (such as a report), set the case there?

Why do you want the data stored in a particular case?


You also need one of these:

log_output = FILE

log_output = TABLE

And put them in this section:

[mysqld]

('d' as in 'daemon' as in the 'server')


Yes. IBM i, formerly known as AS/400, is a platform with the operating system that has a relational DBMS as its integral part. Indeed, Db2 for i is in a way an alternative access method to the IBM i file system.


How to retrieve or reset the password on postgresql on linux

If you want to reset your password you need to go to the pg_hba.conf file. You can do this by giving in the following command into Linux.

sudo subl /etc/postgresql/12/main/pg_hba.conf

After gaining access to the file you must change all local locations from Method md5 to trust so you can enter Postgresql without permission or without being asked for a password.
Then place the following command into Linux:

sudo /etc/init.d/postgresql restart

Subsequently, run the succeeding command to get into PostgreSQL:

sudo -u postgres psql

And last but not least run the common SQL Command to reset the password for User:

ALTER USER postgres with PASSWORD 'anewremembrablepassword';

Of course, you could also use another user than postgres if you considerably created one. Make sure you go back to the pg_hba.conf file to change the methods back to md5 so you always give in a password as for better security or leave it and never give in a password.
I hope I could be helpful in solving this matter.
Thanks!


Trigger: Insert Into For Update IF Update - but with custom insert based on what type of update?

Use a CASE to evaluate it value:

CREATE TRIGGER [dbo].[Logging]
ON [dbo].[TriggerTest]
For UPDATE
AS
BEGIN
    IF UPDATE (ColumnName)
    Begin
    INSERT INTO [dbo].Logging(
        CorrelationID,
        DateTimeStamp,
        Operation
        )
    SELECT
        i.ID,
        GETDATE(),
        CASE
            WHEN i.ColumnName is not null and d.ColumnName is not null
                 THEN 'Column value has been altered'
            WHEN i.ColumnName is null and d.ColumnName is not null
                 THEN 'Column value has been deleted.'
            WHEN i.ColumnName is not null and d.ColumnName is null
                 THEN 'Column value has been added.'
        END
    FROM inserted i
    LEFT JOIN deleted d
        ON i.ID = d.ID
    WHERE i.ColumnName != ISNULL(d.ColumnName, -1)
    End
END;
GO


Select COUNT of days between two dates except weekends

Assuming that by "weekend" you mean Saturday and Sunday, this can be even simpler:

SELECT count(*) AS count_days_no_weekend
FROM   generate_series(timestamp '2014-01-01'
                     , timestamp '2014-01-10'
                     , interval  '1 day') the_day
WHERE  extract('ISODOW' FROM the_day) < 6;

You don't need an extra subquery level for generate_series(). SRF (set returning functions), also referred to as "table-functions", can be used just like tables in the FROM clause.
Note in particular that generate_series() includes the upper bound in the output, as long as a full interval (3rd parameter) fits. The upper bound is only excluded if the last interval would be truncated, which is not the case with full days.
With the pattern ISODOW for EXTRACT(), Sundays are reported as 7, according to the ISO standard. Allows for a simpler WHERE condition.
Rather call generate_series() with timestamp input. Here is why:

Generating time series between two dates in PostgreSQL

count(*) is slightly shorter and faster than count(the_day), doing the same in this case.

To exclude lower and / or upper bound, add / subtract 1 day accordingly. Typically, you might include the lower and exclude the upper bound:

SELECT count(*) AS count_days_no_weekend
FROM   generate_series(timestamp '2014-01-01'
                     , timestamp '2014-01-10' - interval '1 day'
                     , interval '1 day') the_day
WHERE  extract('ISODOW' FROM the_day) < 6;


How to conditionally stop a psql script (based on a variable value)?

There is an option in psql which stops executing commands on error, this is ON_ERROR_STOP.  If we could raise an error somehow, this would do what we want.

The problem is that we have to test the variable and produce an error somehow.  Since one can't use control structures in psql (because there are none)*, my only idea was to use SQL for testing.  Well, producing an error conditionally is something which pl/pgsql is quite good at, so I wrote a function which would generate an error. I can now call this function from a simple CASE structure.  A simple example:

-- let's assume for clarity that there is no function with this name in the database
CREATE OR REPLACE FUNCTION error_generator()
RETURNS boolean AS
$body$
BEGIN
    RAISE 'Meaningful error message here';
    RETURN FALSE; -- just for aesthetical purposes
END;
$body$
LANGUAGE plpgsql;

\set ON_ERROR_STOP on

BEGIN;

-- test for the variable value
-- notice that if :var is not set, it fails as well (with a syntax error)
SELECT CASE WHEN 1 = :var THEN error_generator() ELSE TRUE END;

INSERT INTO test_table (integer_value, text_value)
VALUES (:var, 'something');

COMMIT;

*: You can use any shell commands after \! and conditionals of the shell, but since \! opens a new shell, executing anything there does not have any effect for the current psql script.


ora 20001 error in oracle dbms scheduler

Your question contain 2 items that must have been looked at before question itself arose:

Have you looked at API of DBMS_SCHEDULER (any programmer should do that before knowing how to use built-in [package]) ; another words have you RTFM?
Oracle (most of the time) is in-fact very good with "responses" - error signaling back to "client". Have you googled ora - 20001 ?

To your question:

Your job action is NULL; you need to have some procedure that does something AND also is not named as Oracle keyword or PLSQl block that must have at bare minimum begin and end
- docs
Oracle error 20001 is custom error and if you want to see the code you need to contact DBA's.
Based on the message , seems that your schema also missing some grants (you obviously logged in to the database , so message itself is deceiving , but the exact error can only be identified by going thru the code or checking with DBA's directly - see above )
why do you need job id? your job name is providing your with information already! check API's!!


How to restart PostgreSQL server under CentOS 7

I found out that you need to specify the exact name for the PostgreSQL service, which you can find under the list of services, using systemctl (also see this post):

systemctl list-units|grep postgresql
postgresql-10.service                                                                     loaded active running   PostgreSQL 10 database server

Then you can use service:

service postgresql-10.service reload

or

service postgresql-10.service restart

Alternatively, you can use the systemctl command:

/bin/systemctl reload postgresql-10.service

or

/bin/systemctl restart postgresql-10.service


As I understand payment_id is unique (maybe primary). If so:

SELECT payment_id, SUM(amount), payment_date
FROM payment
WHERE payment_date >= '2018-01-01'
AND payment_date <= NOW())
GROUP BY payment_id WITH ROLLUP;

If it is not unique, or if ONLY_FULL_GROUP_BY enabled:

SELECT payment_id, SUM(amount), payment_date
FROM payment
WHERE payment_date >= '2018-01-01'
AND payment_date <= NOW())
GROUP BY payment_id, payment_date WITH ROLLUP
HAVING payment_date IS NOT NULL or payment_id IS NULL;

Last query will give wrong result if payment_date can be NULL.


Revised Question: How do I prevent Oracle 12c Database from starting on machine boot

You'll want to prevent the OracleService<<SID>> from starting automatically.  Assuming you used the default SID of ORCL, that would be OracleServiceORCL.

You'll likely also see a number of other services that start with Oracle that are for various other components (i.e. your listener, your MTS recovery service, etc.)  You can set all those to not start automatically if you want.  Given that those services tend to use many fewer resources, it's generally easiest to let them run to make it easier to start the database later on.


MySQL-How to find that value in a column is subset with all other values in same column multiple time

As DBNull pointed out, you are experiencing this challenge because the Team column is breaking First Normal Form. The values in that column should be atomic.

You have two options here. The first is to follow DBNull's recommendation of having the teams registered in their own Team table and having a separate TeamGroup table to store Team Groups. While you're at it you could throw in the third table to store the Tasks that the Team Groups are bound to (trusting they are bound to be changed from time to time and you'd like to know that history).

The second option is to write a script that does pretty much what you'd be doing in the first option except it reflects the change back to the table in the de-normalized format.

This isn't trivial. You'll need to loop over each row, split the column into the atomic team names and then query the table using a FIND_IN_SET() for each team name plus ensuring not to match the row your cursor is pointing to. Every time the table changes you'll need to re-run the query.

Here is some SQL that demonstrates normalizing the data. It has foreign keys for data consistency. The task IDs are not stored in the team_group table in this example instead a separate table exists which allows for multiple tasks.

CREATE TABLE `team` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(256) NOT NULL COMMENT 'Name of Team',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `team_group` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(256) NOT NULL DEFAULT '' COMMENT 'Optional Name of Team Group',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='a team group can be one or more teams';

CREATE TABLE `team_group_map` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `team_id` int(10) unsigned NOT NULL,
  `group_id` int(10) unsigned NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unq_team_group` (`team_id`,`group_id`),
  KEY `group_id` (`group_id`),
  CONSTRAINT `team_group_map_ibfk_1` FOREIGN KEY (`team_id`) REFERENCES `team` (`id`) ON DELETE CASCADE,
  CONSTRAINT `team_group_map_ibfk_2` FOREIGN KEY (`group_id`) REFERENCES `team_group` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Binds teams to a team group';

CREATE TABLE `task` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(256) NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `task_group_map` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `task_id` mediumint(8) unsigned NOT NULL,
  `group_id` int(10) unsigned NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unq_group_task` (`group_id`,`task_id`),
  KEY `task_id` (`task_id`),
  CONSTRAINT `task_group_map_ibfk_1` FOREIGN KEY (`task_id`) REFERENCES `task` (`id`) ON DELETE CASCADE,
  CONSTRAINT `task_group_map_ibfk_2` FOREIGN KEY (`group_id`) REFERENCES `team_group` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Binds tasks to a team group';

-- Add a new team
INSERT INTO team (name) VALUES ('Team1');

-- Add a new group and add an existing team to it
INSERT INTO team_group (name) VALUES ('Euro east group');
-- Get ID for this group
SELECT id FROM team_group WHERE name = 'Euro east group';
-- Get team's ID
SELECT id FROM team WHERE name = 'Team1';
-- Add team to the group (team_id = 1, group_id = 1)
INSERT INTO team_group_map (team_id, group_id) VALUES (1, 1);

-- Add a task and set it for a group
INSERT INTO task (name) VALUES ('Win Finals');
-- Get ID for this task
SELECT id FROM task WHERE name = 'Win Finals';
-- Get team's ID
SELECT id FROM team WHERE name = 'Team1';
-- Add task to the group (task_id = 1, group_id = 1)
INSERT INTO task_group_map (task_id, group_id) VALUES (1, 1);

-- Select the teams and group IDs
SELECT team.name, group_id
FROM team_group_map
INNER JOIN team ON (team.id = team_id);

-- Select the teams and group names
SELECT team.name, group.name
FROM team_group_map
INNER JOIN team ON (team.id = team_id)
INNER JOIN team_group AS `group` ON (group.id = group_id);

-- Select the groups, their task IDs and when the tasks were added to the group
SELECT group.name, task_id, map.updated_at
FROM task_group_map AS map
INNER JOIN team_group AS `group` ON (group.id = group_id);

-- Select the groups and their task Names + when tasks were added to group
SELECT group.name, task.name, map.updated_at
FROM task_group_map AS map
INNER JOIN task ON (task.id = task_id)
INNER JOIN team_group AS `group` ON (group.id = group_id);

-- Select the most recent task for a group
SELECT task.name, map.updated_at
FROM task_group_map AS map
INNER JOIN task ON (task.id = task_id)
WHERE group_id = 1
ORDER BY map.updated_at DESC
LIMIT 1;


Find tables with modified data after a specified datetime

You can use something like

    SELECT
        [name]
       ,create_date
       ,modify_date
FROM
        sys.tables

you can add a WHERE clause to limit the results to only those changes that have occurred in the past week, month, year, whatever. Something like

SELECT
        [name]
       ,create_date
       ,modify_date
FROM
        sys.views
WHERE
        modify_date > DATEADD(day,-30,GETDATE())

Although this is no substitute for true database auditing that captures who modified the table, what was changed, and what the table looked like prior to the change.

To get the former, you'll need to invest some time in DDL triggers or some money into a third party tool.


Percona XtraDB Cluster and MySQL Replication

it seems the binlog doesn't include the new inserts

I'm not sure whether you're saying the binlog actually doesn't include them, and you have confirmed this with mysqlbinlog, or that it "seems" like it doesn't, because they don't replicate.

PXC needs log_slave_updates turned on at the node serving as master to the asynchronous slave, otherwise, not everything will be written to the master's binary log.  This is very different than an ordinary MySQL server as master, where log_slave_updates will do nothing at all (unless the master is actually a slave to another master).

If that's not it, remove replicate_do_db and binlog_do_db and all of their related options from your configuration and then remove them from your brain.  They should never be added unless you know exactly how they work, in your sleep.  The simplest and by far most reliable replication configuration is, and will always be, replicate everything, which is the default.

Forget about binlog_format on the slave.  It makes absolutely no difference unless the slave, itself, has other, subtended slaves... and if the master is using ROW format, the slave will still log in ROW format if you do indeed have it configured with subtended slaves.  Also, the slave's binlogs (not to be confused with the relay logs) will not log statements received from an upstream master unless log_slave_updates is enabled on the slave.

The same thing goes for innodb_flush_log_at_trx_commit.  It does not impact actual replication.  It's a setting the determines a tradeoff between ACID compliance and performance.


Is it possible to update a field value in one table based on data inserted into various other tables?

Consider switching the problem around...

Have a TIMESTAMP with suitable DEFAULT in each table.  Have it automatically set on UPDATE or INSERT.

Then, during the SELECT, JOIN the 4 tables and get GREATEST(t1.ts, t2.ts, ...) to find out the latest TIMESTAMP of the 4.

(I have been bitten by TIMESTAMP DEFAULTs -- they get updated when I am not expecting it.  I would rather take control and change/set the ts in the code.)


Can Postgres use an index-only scan for this query with joined tables?

If additional preconditions for an index-only scan are met, it makes perfect sense to append the column id as trailing column to the index (not as leading column):

CREATE INDEX ON thing_types(first_lvl_type, second_lvl_type, id);

Postgres 11 introduces actual covering indexes with the INCLUDE keyword.

CREATE INDEX ON thing_types(first_lvl_type, second_lvl_type) INCLUDE (id);

Only a small benefit for your case, but it's a great option to add columns to a UNIQUE or PK index or constraint.

About index-only scans:

The Postgres Wiki
The manual

The most important precondition: The visibility map of table thing_types has to show most or all pages as "visible" to all transactions. I.e. the table is either read-only, or your autovacuum settings are aggressive enough to continuously clean up after writes to the table.

Every additional index adds costs. Mostly to write performance. But also side effects, like exhausted cache capacities. (Multiple queries using the same indexes have a better chance for them to reside in cache.) So it's also a question of size. id is typically a very small column integer or bigint. Makes it a good candidate for the use case.

In particular, adding a column to an index disables the option for H.O.T. updates involving the column. But since id is indexed anyway and typically not updated (being the PK) this is not a problem in this case. Related:

Redundant data in update statements

If you actually get index-only scans out of these indexes most of the time, it typically makes sense to use them. Test with EXPLAIN.

There were limitations for partial indexes in older versions. Quoting the release notes of Postgres 9.6:

  Allow use of an index-only scan on a partial index when the index's WHERE clause references columns that are not indexed (Tomas
  Vondra, Kyotaro Horiguchi)

  For example, an index defined by CREATE INDEX tidx_partial ON t(b) WHERE a > 0 can now be used for an index-only scan by a query that
  specifies WHERE a > 0 and does not otherwise use a. Previously
  this was disallowed because a is not listed as an index column.


What you are missing is a row that can be grouped by. Row_NUMBER is a good solution

CREATE TABLE OCCUPATIONS
    (Name varchar(255), Occupation varchar(255))
;

INSERT INTO OCCUPATIONS
    (Name, Occupation)
VALUES
    ('Samantha', 'Doctor'),
    ('Julia', 'Actor'),
    ('Maria', 'Actor'),
    ('Meera', 'Singer'),
    ('Ashely', 'Professor'),
    ('Ketty', 'Professor'),
    ('Christeen', 'Professor'),
    ('Jane', 'Actor'),
    ('Jenny', 'Doctor'),
    ('Priya', 'Singer')
  ;
GO

SELECT Row#,Doctor, Actor, Singer, Professor
from
(
  select Name, Occupation,
ROW_NUMBER() OVER(PARTITION BY Occupation ORDER BY name ASC) AS Row#
  from OCCUPATIONS
) d
pivot
(
  max(Name)
  for Occupation in (Doctor, Actor, Singer, Professor)
 ) piv;
GO

Row# | Doctor   | Actor | Singer | Professor
---: | :------- | :---- | :----- | :--------
   1 | Jenny    | Jane  | Meera  | Ashely
   2 | Samantha | Julia | Priya  | Christeen
   3 | null     | Maria | null   | Ketty

WITH CTE as (SELECT
Name, Occupation,
ROW_NUMBER() OVER(PARTITION BY Occupation ORDER BY name ASC) AS Row#
FROM OCCUPATIONS)
SELECT Row#,Doctor, Actor, Singer, Professor
from
(
  select Name, Occupation,Row#
  from CTE
) d
pivot
(
  max(Name)
  for Occupation in (Doctor, Actor, Singer, Professor)
 ) piv;
GO

Row# | Doctor   | Actor | Singer | Professor
---: | :------- | :---- | :----- | :--------
   1 | Jenny    | Jane  | Meera  | Ashely
   2 | Samantha | Julia | Priya  | Christeen
   3 | null     | Maria | null   | Ketty

db<>fiddle here


Combine Results in Same Column to get Average

You can use a with clause and get so the average from every team per month and then simply add the data as you see fit

CREATE TABLE sportstable
    ("Average Score" int, "Team" varchar(13), "Month" varchar(4))
;

INSERT INTO sportstable
    ("Average Score", "Team", "Month")
VALUES
    (2, 'New York Team', 'May'),
    (3, 'San Fran Team', 'May'),
    (2, 'Chi Team', 'May'),
    (3, 'Texas Team', 'May'),
    (2, 'New York Team', 'June'),
    (3, 'San Fran Team', 'June'),
    (2, 'Chi Team', 'June'),
    (3, 'Texas Team', 'June')
;

✓

8 rows affected

WITH average_month AS (SELECT "Month",
MAX(CASE WHEN "Team" = 'New York Team' THEN "Average Score"
            ELSE 0
       END) AS "New York Team",
       MAX(CASE WHEN "Team" = 'San Fran Team' THEN "Average Score"
            ELSE 0
       END) AS "San Fran Team",
       MAX(CASE WHEN "Team" = 'Chi Team' THEN "Average Score"
            ELSE 0
       END) AS "Chi Team",
       MAX(CASE WHEN "Team" = 'Texas Team' THEN "Average Score"
            ELSE 0
       END) AS "Texas Team"
FROM
   sportstable
GROUP BY "Month")
SELECT
    "Month"
    ,("New York Team" + "San Fran Team" ) AS Tier1
    ,("Chi Team" + "Texas Team") AS Tier2
FROM
    average_month

Month | tier1 | tier2
:---- | ----: | ----:
May   |     5 |     5
June  |     5 |     5

db<>fiddle here


No index is useful.  There is only one value of usergroupID (as you have written the query), so there is no use for it to be indexed.

So, the Optimizer decides to simply read the data without using any index.

One thing you can do is to get rid of the extra layer of SELECTs.

Another thing is to get rid of assignmentCount, since it is not used.

To achieve the same result set, simply do (without a tmp table):

SELECT  156825 AS usergroupID,
        GROUP_CONCAT(MVStoreID) as Inspects
    FROM  MVMasterEntity_AttachedStores

With this formulation, INDEX(MVStoreID) would probably help.

(I suspect you watered down the actual code too much, and I have not answered your real question.)


I take it that your main concern is tracking the changing rates and charging the prevailing rate when work was performed. That's not really a problem.

There is a pattern I call Temporal Normal Form. It's a way to track changing data using normalization -- a pattern we all know and love.

So the Rates table will be normalized according to static data:

create table Rates_S(
    ID        int identity,
    AgentID   int not null,
    ProjectID int not null,
    constraint PK_Rates_S primary key( ID )
);

and varying data:

create table Rates_V(
    RateID    int not null,
    Effective date not null,
    Rate      number( 8, 2 ) not null,
    constraint PK_Rates_V primary key( RateID, Effective ),
    constraint FK_Rates_V_Rate_S foreign key( RateID )
        references Rates_S( ID )
);

Notice there is no EndDate field. The rate, once started, continues until changed. This eliminates Row Spanning Dependencies, where different fields in different rows must remain in synch.

So now let's see how much a billing entry will cost the project. We're looking for the rate that was in effect on the date the hours are logged:

select  b.ProjectID, b.AgentID, b.Date, b.Hours * v.Rate as Charge
from    Billing   b
join    Rates_S   s
    on  s.ProjectID = b.ProjectID
    and s.AgentID   = b.AgentID
join    Rates_V   v
    on  v.RateID    = s.ID
    and v.Effective =(
        select  Max( Effective )
        from    Rates_V
        where   RateID = v.RateID
            and Effective <= v.Effective );

Notice I've added how much time is being billed to the Billing table.

This looks like a lot to get one rate, but it is a pattern and once you write it a coupla hundred times, it will sink in. It is also quite efficient.


Enable SQL Server FILESTREAM - Windows Based Docker Container (not linux)

Not sure if this is the problem but the link you gave in the question says that the image was last updated 3 years ago:

Plus it says that:
"This image is compatible with Windows Server 2016 (Core, with Desktop)...
I'm concerned about that "with Desktop" but I cannot tell if this is the problem.
I would rather suggest you to use this one by running:
docker pull mcr.microsoft.com/mssql/server:2017-latest

Let us know if it works.


Create unique sequence per set of rows sharing the same foreign key value

This has been asked many times. The long and the short of it: Don't try it. You'd run into all kinds of problems with concurrency, gaps introduced by deleting rows, etc. Make id a plain serial column and create a VIEW to display a running number per user_id.

CREATE VIEW v_user_article AS
SELECT *, row_number() OVER (PARTITION BY user_id ORDER BY id) AS per_user_id
FROM   user_article;

A column article with 'some notes' hardly makes sense for your setup. And ordering by it makes even less sense, since the numbers would change with every new article that does not happen to sort last. To get a stable sort order and stable numbers (except for deletes!) use the serial column to determine the sort order.

Details:

Serial numbers per group of rows for compound key


How do I set and get custom database variables?

You have to add the variable to the end of your postgresql.conf like

#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------

custom_variable_classes = 'general'     # list of custom variable class names

general.application_version = 'v1.0'

When restarted, you can add general.application_version manually in PgAdmin (it will not show up in the drop-down - at least not for me).  Otherwise, you can use it just like any other PostgreSQL variable.

Update for version 9.2 and newer

From PostgreSQL 9.2 onwards, we don't have to set custom_variable_classes anymore.  One can set whatever variable they want - the only limitation seems to be it still has to have two parts:

SET something TO 'bla';
ERROR:  unrecognized configuration parameter "something"

SET thing.something TO 'bla';
SET

I guess this is so to avoid collision with the builtin parameters.