Normalization for three somehow identical tables

The question one has to ask is: Will I ever need the address id and the subscriber id more than once? If the answere to this is yes, go with the second approach. If the answere is no you could still use the second approach, but most of the time it is more convenient to use the first one.

If the triple ELECTRIC_SUBSCRIBER_NUMBER, GAS_SUBSCRIBER_NUMBER, WATER_SUBSCRIBER_NUMBER will always be the same for an owner, then I would make a separate table out of it, because it may happen, that one owner has multiple possessions.

Since the address of a posession and the space does uniquily belong to one possession there is no reason to split it out.

My approach would look like this:

POSSESSIONS
  ID
  [TYPE (1-APARTMENT, 2-GARDEN, 3-PLOT_OF_LAND)]
  CITY_ID
  VILLAGE_ID
  STREET_NAME
  BUILDING
  APARTMENT (nullable)
  SPACE
  SUBSCRIBER_ID (nullable)

SUBSCRIBERS
  ID
  ELECTRIC_SUBSCRIBER_NUMBER
  GAS_SUBSCRIBER_NUMBER
  WATER_SUBSCRIBER_NUMBER

You may skip the TYPE field, because the information is already encoded in the Nulls of APARTMENT and SUBSCRIBER_ID.

Normalization is all about eliminating redundacies. A good approach to work this out is, to take your scheme with good sample data and then try to change some information by hand. If you have to change data in multiple places for one bit of "atomic information", you should probabbly rethink your scheme. If you need to think about where this information was, because you have multiple tables with similar information schemes, then again, you should probabbly rethink your scheme.


My script may give minor syntax error or minor logic error because you haven't thrown sample data.

I am sure it cover your requirement,

--Write sql to get all user_id from helpline_requests as per your requirement
-- I am getting latest helpline date of each user
-- you can use your own logic
declare @Hr int=24
declare @CurDate datetime=Getdate()

declare @Period int= 7 --days
declare @FromDate datetime=datediff(day,-@Period,@CurDate)
;with CTE as
(
select [user_id],[date]
,ROW_NUMBER()over(partition by t.[user_id] order by t.[date] desc) rownum
from helpline_requests
where [date]>=@FromDate
)
,CTE1 as
(
select t.[user_id]
,ROW_NUMBER()over(partition by t.[user_id] order by t.[date] desc) rownum1
from all_transactions as T
cross apply(select [user_id] from CTE C
where c.user_id=t.user_id
and c.rownum=1
and datediff(hour,t.[date],c.[date])<=@Hr)ca
and t.[date]>=FromDate and t.[date]<=@CurDate
)
-- more than once in last 7 days
select * from CTE1 where rownum1>1


How to determine high availability configuration is working correctly

how does SQL know when to fail over

There is a Flexible Failover Policy which you can use to adjust the sensitivity to the criteria that will trigger a failover. The default is FAILURE_CONDITION_LEVEL = 3 which means "critical error, server unresponsive, or server down." You can see more details here and here.

  how would my application ... know where it is pointing

If your availability group listener is properly configured, the connection string should not need any further knowledge to always connect (even if after a retry) to the primary replica on a specific server - it does not need to know those implementation details at all. All it needs to do is point at the listener, and the listener determines if the primary replica is currently on DB01 or DB02. This is pretty powerful since it lets you adjust your AG configuration and move replicas all over the place without ever having to touch the applications (except in cases where you (a) add a replica with read-only intent or (b) have to move the listener). You can see more details here.


Is it a normal practice when there're 2 columns and if one is true the other must false, and vise versa?

This is called transitive functional dependency and should be avoided to have a 3th normal form design.
To solve it you should move it to the type of product to another table.
And then you can have another table linked with specific attributes of coffee entity and another one with specific attributes of the tea entity.
Don't be scared of the number of the tables, be scared of the consequences of not be in normal form.
(i.e. lack of performance, space, scalability)


Does adding a postgres index concurrently take up disk space gradually or all at once?

When running CREATE INDEX in PostgreSQL, disk space will be allocated gradually as needed. It should be easy to observe with a simple command like watch du -s $PGDATA.

Same applies to any data-generating command (INSERT, COPY, CREATE TABLE AS, etc etc).

It's hard (and in most cases, pointless) to pre-calculate and pre-allocate all required space before object is created. If the transaction breaks, data can be simply discarded.


Should I explicitly DENY UPDATE to columns that should not be updated?

The argument doesn't make sense. I always want the controls and constraints as close to the data as possible. Putting it in the application layer means it only affects the people using the application layer, and also assumes that the code will be bug-free and the security around those code paths will be bulletproof. Those are big assumptions.

If they absolutely need to be updated, then that can be done by a person unaffected by the explicit DENY, or the person can be temporarily moved into a role that is unaffected, or the DENY can be temporarily removed. These are things that are easy for you, as the DBA, to set up auditing around. In the app? Not so much.


Large discrepancy between information_schema database size vs mysqldump database size

A BIGINT takes 8 bytes (plus some overhead) on disk, but after going through mysqldump, a value of '1' will take only 2 bytes: INSERT ... VALUES(...,1,...).  That's a 4:1 shrinkage.  (Pro tip: 90+% of BIGINT columns don't need to be 'big'.)

On the other hand, a 1-byte TINYINT with 100 in it expands 1:4.

BLOBs tend to incur a 1:2 expansion, maybe more, due to escaping the bytes to encode in the dump.

INDEXes are not dumped.

"Free" space (and other fragmentation) is not dumped.  This might account for 2:1 shrinkage in the typical dump.

But the dump spells out the column names at least twice -- once for the CREATE TABLE, and at least once for the INSERTs.  On disk, the column names occur only once -- in the .frm file.  (That is, not included in the SELECT you did.)

And there are many other cases of expansion/shrinkages.

Since you are seeing 23 : 6.5 shrinkage, I would guess that you have some over-sized integer columns and a lot of indexes.  (Pro tip: It is folly to "index every column.")


Is there any way to perform atomic updates of stored procedures in MySQL?

There is no way to solve this problem. It's one of many issues that arise over MySQL not having real transactions: the transactions do not cover DDL.

It's not just stored procedures either. That's a tremendous understatement. MySQL has fundamental problems with concurrency. It's MVCC model is non-existent with DDL: transactions are a function of Inno not MySQL. And, even at that the transaction model is broken with DML too.

I suggest migrating databases. I know that's steep. But shy of that nothing can done.


Naturally, if you have a backup before the change, you could simply restore that backup to another database and get the previous value from that.  Otherwise, you 'might' be able to take advantage of the free trial of this
http://www.apexsql.com/sql_tools_log.aspx


Execution plans (actual, not estimated) need to be added to the Q for a definitive answer but...

  How Can the Same Query in Two Nearly Identical Instances Generate Two
  Different Execution Plans?

Because, by your admission, they are not identical. Most likely explanation for the different execution plans is a variance in statistics.

  Table rows counts are slightly larger on Server A... Statistics are
  updated nightly at the same time on both servers.

Row counts are different and the statistics update was probably a default sample, rather than FULLSCAN. I've witnessed some comically unfortunate stats histograms as the result of sampling, each of which has been corrected by a FULLSCAN update.


Moving a SQL Cluster to Another Data Center w/No Downtime

In SQL 2008 you're looking for minimum downtime vs no downtime in these cases unless you're willing to shell out a lot of money/time for a few seconds.  This sounds like hopefully it's not too complex of an environment so you have several options.

How about SQL Mirroring?  If your data isn't coming in too fast, it'll sync up and you can 'failover mirror' option.  Also, it's a very easy rollback, you just hit failover mirror again and it'll fail right back to the old cluster.  If you go with High Safety mode, all writes have to be hardened on the other side before committing, thus you will have some transactional delay while the mirror is up.  The upside is that it's super fast to just fail over as the data is certainly up to date.  High performance mode is available in Enterprise.  Mirroring is much more simple than replication and requires a lot less configuration items/requirements.  For example, in Replication, every replicated table must have a PK, this isn't required in Mirroring.  You also have the option of a witness server which will help with it being transparent to the application.

Worst case, you can do log shipping with automated scripts to restore the tail of the log.  You can take a log backup, restore, put the DB in read only mode, take another log backup, and restore again.  Be sure to check your VLFS (DBCC LOGINFO;) because if you have hundreds upon hundreds, your log restores will be slow complicating things.

Ensure security matches on both sides.  Are they different domains?  Then you'll have different users you'll need to add to your DB as soon as they go live.  SQL user?  Copy the user and SID together.  There's scripts to do this.

Prior to the failover, run a trace or profiler to see what apps are connecting from where.  Make sure to account for them on the new cluster.  This includes user security.  Also run a trace or profiler looking for logins to the old server.  Let the app owner know they are connecting to the wrong DB.

Whatever you do, make sure to test and stage the process multiple times and automate it to where it's a couple of key strokes.  Test failure scenarios and go into this confident.


Your error output is strange, it should be:

ERROR 1064 (42000): You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version
for the right syntax to use near 'where cname='Sophore'' at line 1

Which tell you exactly where the error is- you forgot the FROM clause on DELETE:

DELETE FROM company WHERE cname='Sophore';


Prevent mysql process being killed by OOM killer

RHEL7 uses systemd.
Using the OOMScoreAdjust as follows:

sudo systemctl edit mysqld.service

Note: I'm not sure of the exact service name

Add the following text:

[Service]
OOMScoreAdjust=-1000

restart with:

systemctl restart mysqld.service

Check by looking at the score:

cat /proc/$(pidof mysqld)/oom_score_adj


SQL Server 2016 Security - Restrict users from viewing a single database

Contained Databases are your option.
Your server version supports them.

No one of your user will require server access, all of them can be authenticated by corresponding database.
As consequence, none of them will "see" databases other than they are mapped too.

The problem of "seeing" all the databases derives from VIEW ANY DATABASE server level permission granted to public, if you revoke it from public or deny to someone, that login will not "see" even the database it is mapped to, except for the database owners (not to be confused with members of db_owner database role, the owner is only one server principal that owns the database).

So when we have no logins, we have no problem of databases visible to everyone


Saddam has a smart solution, but it carries some weaknesses. Imagine a source named 'Fresno, CA' (with comma in the string). split_part() would be fooled by the separator character in the string ...

To avoid such corner case problems and preserve original data types, use a (well-defined!) row type instead. You can create a composite type permanently with CREATE TYPE or register a temporary one with CREATE TEMP TABLE:

CREATE TEMP TABLE defso (def numeric, so varchar);  -- once per session!

SELECT country_code
     , country_name
     , (d14).def AS deflator_2014  -- note the parentheses!
     , (d14).so  AS source_2014
     , (d15).def AS deflator_2015
     , (d15).so  AS source_2015
     , (d16).def AS deflator_2016
     , (d16).so  AS source_2016
FROM   crosstab (
    'SELECT country_code, country_name, year, (deflator, source)::defso
     FROM   deflator
     ORDER  BY 1'
  , 'SELECT generate_series(2014, 2016)::int2'
   ) AS ct (country_code int2
          , country_name text
          , d14 defso
          , d15 defso
          , d16 defso
   );

I also removed the unnecessary CTE and simplified a bit.

While dealing with only a hand full of years, you can do without crosstab() and use self-joins:

SELECT country_code, country_name
     , d14.deflator AS deflator_2014
     , d14.source   AS source_2014
     , d15.deflator AS deflator_2015
     , d15.source   AS source_2015
     , d16.deflator AS deflator_2016
     , d16.source   AS source_2016
FROM        (SELECT * FROM deflator WHERE year = int2 '2014') d14
FULL   JOIN (SELECT * FROM deflator WHERE year = int2 '2015') d15 USING (country_code, country_name)
FULL   JOIN (SELECT * FROM deflator WHERE year = int2 '2016') d16 USING (country_code, country_name)
ORDER  BY country_code;

Using FULL [OUTER] JOIN since we can't assume a row for every combination of (country_code, year). This way we get the same result as with the crosstab query above.

Including country_name in the join condition seems redundant, but if we don't, we have to use COALESCE(d14.country_name, d15.country_name, d16.country_name) AS country_name to defend against missing rows. This functionally dependent value shouldn't be in the table to begin with. Should be in a country table in a properly normalized schema.


Using aggregate function in custom function

You would not use another aggregate function.  You would define your aggregate "from scratch".  I am surprised there is not an existing aggregate that does this, but I have not found one.


Optimize postgresql query with multiple subqueries

You can avoid the second scan of the large table using window functions and a subquery:

SELECT "time",
       metric,
       notional_usd / max(notional_usd) OVER () * 100 AS notional_usd_percent
FROM (SELECT extract (hour FROM to_timestamp(timestamp/1000)) AS "time",
             extract (hour FROM to_timestamp(timestamp/1000))::text AS metric,
             SUM(notional_usd) AS notional_usd
      FROM candles
      GROUP BY "time") AS q;

You will still have to perform the GROUP BY, so raising work_mem for this query is a good idea.


Installing Oracle XE 11gR2 on Fedora 15 - linux group permission deadlock

You've got the distinction between users and groups mixed up. Putting the dba user in the oinstall group will not mean that the dba group will also have permission to write to /u01/app/oracle when the owning group is oinstall - this is because the dba user and the dba group are completely separate entities.

The Oracle user should be in the oinstall and dba groups. There shouldn't be a dba user, only an Oracle user.

chown oracle:dba /u01/app/oracle

... will fix it.


If you're using InnoDB and have a reasonably default configuration, tablespace data is in the ibdata files, you should have at least ibdata1 by default.

You may want to read up about its configuration:

https://dev.mysql.com/doc/refman/5.7/en/innodb-configuration.html

Specifically splitting it so that each table gets its own ibdata file:

https://dev.mysql.com/doc/refman/5.7/en/innodb-multiple-tablespaces.html

And there are some related questions:

Ibdata usage and Recommendations?

If you're not using InnoDB, disregard that :)


Compare running / cumulative total with static target

I think I've now got a working solution. The below is giving the output as expected. It could probably be optimized a bit as it isn't the quickest (takes a couple minutes for only two companies). In my real dataset I have 65~ companies so if this could be improved, I'd love to see it!

SELECT
    x.yyyy_mm_dd,
    x.id,
    x.name,
    x.actual,
    x.target,
    x.actual_to_date,
    CASE WHEN x.actual_to_date > x.target THEN ((x.actual_to_date - x.target) /100) * 1 ELSE 0 END AS payout
FROM(
    SELECT
        daily.yyyy_mm_dd,
        daily.id,
        daily.name,
        daily.actual,
        t.target,
        SUM(daily.actual) OVER (PARTITION BY MONTH(daily.yyyy_mm_dd), daily.id ORDER BY daily.yyyy_mm_dd RANGE UNBOUNDED PRECEDING) AS actual_to_date
    FROM(
        SELECT
            yyyy_mm_dd,
            id,
            name,
            sum(cast(actual as int)) as actual
        FROM
            daily_data_table
        WHERE
            yyyy_mm_dd >= '2020-01-01'
        GROUP BY
            1,2,3
    ) daily
    INNER JOIN
        monthly_target_table t
        ON t.id = daily.id AND t.month = month(daily.yyyy_mm_dd)
    WHERE
        daily.name = 'Comp1'
) x


How to get all months in a query where one month has no matches?

I think you just had your tables backwards (the first table is all the rows you want, the outer table is all the rows that might exist). You also need the outer table clauses to be part of the join criteria (ON), rather than filter criteria (WHERE), otherwise your outer join turns into an inner join.

SELECT m.id, COUNT(t.fechaIngreso) AS ingreso
FROM dbo.tblMes AS m
LEFT OUTER JOIN dbo.tblTicket AS t
ON MONTH(t.fechaIngreso) = m.id
AND year(t.fechaIngreso) = 2017
GROUP BY m.id
ORDER BY m.id;

This is assuming tblMes is just a table with 12 rows, with one row for each of the 12 months. You can also do this without that table pretty easily:

;WITH Months(id) AS
(
  SELECT 1 UNION ALL SELECT id+1 FROM Months WHERE id <= 11
)
SELECT m.id, COUNT(t.fechaIngreso) AS ingreso
FROM Months AS m
LEFT OUTER JOIN dbo.tblTicket AS t
ON m.id = MONTH(t.fechaIngreso)
AND YEAR(t.fechaIngreso) = 2017
GROUP BY m.id
ORDER BY m.id;

On modern enough versions of SQL Server, and with an index on fechaIngreso, this may perform better:

ON t.fechaIngreso >= DATEFROMPARTS(2017, m.id, 1)
  AND t.fechaIngreso < DATEADD(MONTH, 1, DATEFROMPARTS(2017, m.id, 1))

The reason is that MONTH() and YEAR() functions will force a scan, because the result of that computation will have to be evaluated for every single row. Avoiding this work is preferable, IMHO, even if you don't have a supporting index on that column now.


Dynamic column value to columns name mySQL

You can solve it on this way:

Select all distinct StudentId, and join its with two subqueries, one with all answers about Gender and another with all answers about Age, both grouped by StudentId.

select    distinct q.StudentId,
          g.Answer as YourGender,
          a.Answer as YourAge
from      questions q
left join (
           select   StudentId, Answer
           from     questions
           where    View = 'Gender'
           group by StudentId
          ) g
on         g.StudentId = q.StudentId
left join (
           select   StudentId, Answer
           from     questions
           where    View = 'Age'
           group by StudentId
          ) a
on         a.StudentId = q.StudentId
;

This is the result:

| StudentId | YourGender | YourAge |
|-----------|------------|---------|
| 1         | Male       | 23      |
| 2         | Female     | 21      |

Can check it here: http://rextester.com/XPNAT23300


Best Practices for Creating and Deleting Records in MYSQL

There are at least two drawback when using a query to check if URL exist:

Sending an extra query to database, will include additional network delay to your overall response time and also more network traffic.
no matter what value you insert, database will still check for duplicate key. therefore, the search on primary key is performed twice when two query is used.

Moreover, @mrdenny is right, there is 1% chance of collisions (with as few as 93,000 values). in order to  avoid that, keep a copy of the URL in your table and write query as below:

SELECT * FROM myTable WHERE md5Val = MD5('https://www.mysql.com') and url = 'https://www.mysql.com';

This will avoid collisions but your md5 value cannot be the primary key anymore. Therefore, I suggest to create table as below:

CREATE TABLE Urls(
id int unsigned NOT NULL auto_increment,
url varchar(255) NOT NULL,
url_crc int unsigned NOT NULL DEFAULT 0,
PRIMARY KEY(id), INDEX index_url_crc(url_crc)
);

and you may also use a trigger to maintain the crc value

DELIMITER //
CREATE TRIGGER Urls_crc_ins BEFORE INSERT ON Urls FOR EACH ROW BEGIN
SET NEW.url_crc=crc32(NEW.url);
END;
//
CREATE TRIGGER Urls_crc_upd BEFORE UPDATE ON Urls FOR EACH ROW BEGIN
SET NEW.url_crc=crc32(NEW.url);
END;
//
DELIMITER ;

and now query your table like this

SELECT * FROM Urls WHERE url_crc = CRC32('https://www.mysql.com') and url = 'https://www.mysql.com';

hope this is helpful.


MySQL trigger if new value is not equal to varchar

Sure you get an error. If for example sifra is S then the sifra <> 'P' will cause the error to appear. Change the OR into AND.


SHOW CREATE TABLE -- need to see the PRIMARY KEY and the "index".

100-1000 is optimal (10K might be ok) for "batching" INSERTs -- That is, a single INSERT statement with 100 to 1000 rows in it.

You have shown a single row being inserted.  Will it work change to

values ('a', 'b', 3), ('c', 'd', 4), ...

That might give you a 10-fold speedup.

That is also pretty good for the size of a transaction.  You could also set innodb_flush_log_at_trx_commit = 2.

Parallel threads might help.  In that case, transactions are vital, plus code to replay a transaction if it fails.  And you might be better off with 100 rows at a time -- to minimize deadlocks or delays due to other locking.

innodb_buffer_pool_size should be 70% of available RAM (assuming you are using ENGINE=InnoDB).


Relationships are used for describing how the tables are well, related and for enforcing this such as satisfying a foreign key from one table requires a matching entry in another. A join is used to realize those relationships. In a join you are telling the database what fields to match and how when it is executed. In a relationship you are telling the database to enforce this for future queries. Also joins are used in gathering data, whereas relationships are meant to maintain data integrity from update anomalies as well as used for conceptual reasons for developers and DBAs to know how the tables are meant to be used.


Tuning dedicated Percona Server with XtraDB for OLTP

When I head the word 'Percona Server', the first thing that jumps at me is : "Does the DB Server have multiple CPUs?" These days, that is taken for granted. What is unfortunate is that most people forget to configure MySQL or Percona Server to summon those multiple CPUs.

MySQL 5.1 with InnoDB 5.1 Plugin, MySQL 5.5 and Percona Server have options to increase threading. Here are those options along with other InnoDB features you may wish to tune:

innodb_thread_concurrency sets the upper bound on number of concurrent threads that InnoDB can hold open. Best round number to set for this is (2 X Number of CPUs) + Number of Disks. As I learned firsthand from the Percona NYC Conference back in May 2011, you should set this to 0 in order to alert InnoDB Storage Engine to find the best number of threads for the environment it is running in.

innodb_concurrency_tickets sets the number of threads that can bypass concurrency checking with impunity. After that limit is reached, thread concurrency checking becomes the norm again.

innodb_commit_concurrency sets the number of concurrent transactions that can be committed. Since the default is 0, not setting this allows any number of transactions to commit simultaneously.

innodb_thread_sleep_delay sets the number of milliseconds an InnoDB thread can be dormant before reentering the InnoDB queue. Default is 10000 (10 sec).

innodb_read_io_threads and innodb_write_io_threads (both since MySQL 5.1.38) allocate the specified number of threads for reads and writes. Default is 4 and maximum is 64.

innodb_replication_delay imposes thread delay on a slave is innodb_thread_concurrency is reached.

innodb_read_ahead_threshold allows linear readings of the set number of extents (64 pages [page = 16K]) before switching to asynchronous reading.

Time would escape me if I named more options. You can read about them in MySQL's Documentation.

Switching gears to RAID: Please stick with your RAID10. It is easier to maintain other redundant setups, especially RAID5.. Also, make sure you have the latest firmware for your RAID card. Otherwise, RAID firmware may conflict with the OS kernel resulting either in disk crashes or lower overall throughput.

As for Is ext4 ok or should I create a separate xfs volume for the data?, keep in mind that ext3 places a max size limit of 2TB per file. If you are not dealing with TBs of data, don't sweat using ext3 or ext4.

A separate data volume would increase disk performance since mysqld will not compete with the OS.


The default algorithm to locate a particular record in an index is a binary search using the row offset array:

Enterprise Edition may also use interpolation based on linear regression information:

I wrote about the details and the performance implications in SQL Server, Seeks, and Binary Search.


SQL Server 2012 Transactional Replication - twin subscriptions on same machine

Giova's query brought into sharper focus the name of the publication; Pub 1 vs. Pub_1, for example.

The difference between the two subscriptions was the under-bar, '_', character in the name of the publication.

The subscription referencing the non-existent publication was deleted.


cross database ownership - The server principal is not able to access the database under the current security context

The server principal "My_Company\sqldbslaveowner" is not able to
  access the database "JUNOCORE" under the current security context.

You should not need to EXECUTE AS OWNER in this scenario, and it will break as you can't cross databases when impersonating a database principal.  Impersonation and Ownership Chains are two separate mechanisms for delegation.

And the normal thing that people forget is that the end user must have database access to the target database.  Cross Database Ownership chains don't give a user access to a database, they only suppress permissions checks on objects in that database.

Here's a simple repro:

/*
revert
use master
drop database a
drop database b
drop login appuser
*/
go
create database a
create database b

ALTER DATABASE a SET DB_CHAINING ON;
ALTER DATABASE b SET DB_CHAINING ON;
go

use b
go
create procedure foo
as
begin
  select 1 a;
end

go

use a
go
create procedure foo
as
begin
  exec b.dbo.foo;
end

go

create login appuser with password='P@Jhs9d!$12'
create user appuser for login appuser
grant exec on foo to appuser

go
execute as login='appuser'
  exec foo;
--fails
--Msg 916, Level 14, State 1, Procedure foo, Line 4 [Batch Start Line 37]
--The server principal "appuser" is not able to access the database "b" under the current security context.

go
revert

go
use b
create user appuser for login appuser
deny select,insert,update,execute to appuser --not strictly necessary
go
use a
execute as login='appuser'
  exec foo;
revert
--works

go

--`execute as owner` is incompatible with cross database ownership chains
create or alter procedure foo
with execute as owner
as
begin
  exec b.dbo.foo;
end
go

use a
execute as login='appuser'
  exec foo;
revert
--fails
--Msg 916, Level 14, State 1, Procedure foo, Line 5 [Batch Start Line 69]
--The server principal "NORTHAMERICA\dbrowne" is not able to access the database "b" under the current security context.


Does PG somehow support the ability to set a column to "variable type"?

AFAIK know there is no such union type. I would consider this a last resort, but here is a sketch if you truly need such type:
CREATE TABLE T
( attribute_key text not null
, attribute_value text not null
, attribute_type smallint not null -- example 1=int, 2=...
);

You can add a type check like:
attribute_type = 1 => attribute_value ~ '^[0-9]+'

and so forth. The above rule would be:
CHECK( attribute_type <> 1 or attribute_value ~ '^[0-9]+' );

The other option I could think of is to use XML to store the value. You can create a schema for the data types you are supporting, and validate against that schema during insert.


Has anything been written to the Master in the last 72K seconds (about a day)?

Sometimes this helps:

STOP SLAVE;
START SLAVE;

I have seen the need for this on and off over the last decade; never figured out why it might be needed.

Another mystery is that the Seconds_behind_master sometimes mysteriously decides to flap between zero and some large value.  Eventually that problem clears itself up.


"Skipping the default startup of database" SQL notification for AlwaysOn Cluster

Same kind of message appeared in our environment as well.

What I understand is, as the DB was in AAG, and when the node (secondary AAG group) goes under restart, the DB start-up will be taken care of by AlwaysOn instead of the regular SQL Server 3-phase process!

If the message appears without any secondary node restart, we may have to consider troubleshooting, but in our condition we can ignore that as the message states it is just informational.


Alternative to MakeValid() for spatial data in SQL Server 2016

Let me caveat that I am playing with spatial data in SQL server for the first time (so you probably already know this first part), but it took me a while to figure out that SQL Server isn't treating (x y z) coordinates as true 3D values, it is treating them as (latitude longitude) with an optional "elevation" value, Z, which is ignored by validation and other functions.

Evidence:

select geography::STGeomFromText('LINESTRING (0 0 1, 0 1 2, 0 -1 3)', 4326)
    .IsValidDetailed()

24413: Not valid because of two overlapping edges in curve (1).

Your first example seemed weird to me because (0 0 1), (0 1 2), and (0 -1 3) are not collinear in 3D space (I'm a mathematician, so I was thinking in those terms). IsValidDetailed (and MakeValid) is treating these as (0 0), (0 1), and (0, -1), which does make an overlapping line.

To prove it, just swap the X and Z, and it validates:

select geography::STGeomFromText('LINESTRING (1 0 0, 2 1 0, 3 -1 0)', 4326)
    .IsValidDetailed()

24400: Valid

This actually makes sense if we think of these as regions or paths traced on the surface of our globe, instead of points in mathematical 3D space.

The second part of your issue is that Z (and M) point values are not preserved by SQL through functions:

  Z-coordinates are not used in any calculations made by the library and are not carried through any library calculations.

This is unfortunately by design. This was reported to Microsoft in 2010, the request was closed as "Won't Fix". You might find that discussion relevant, their reasoning is:

  Assigning Z and M is ambiguous, because MakeValid splits and merges spatial elements. Points often gets created, removed or moved during this process. Therefore MakeValid (and other constructions) drops Z and M values.

  For example:

DECLARE @a geometry = geometry::Parse('POINT(0 0 2 2)');
DECLARE @b geometry = geometry::Parse('POINT(0 0 1 1)');
SELECT @a.STUnion(@b).AsTextZM()

  Values Z and M are ambiguous for point (0 0). We decided to drop Z and M completely instead of returning half-correct result.

  You can assign them later on if you know exactly how. Alternatively you can change the way you generate your objects to be valid on input, or keep two versions of your objects, one that is valid and another one that preserves all your features. If you explain your scenario better and what you do with the objects maybe we could be able to give you additional workarounds.

In addition, as you've already seen, MakeValid can also do other unexpected things, like change the order of points, return a MULTILINESTRING, or even return a POINT object.

One idea I came across was to store them as a MULTIPOINT object instead:

  The problem is when your linestring actually retraces a continuous section of line between two points that was previously traced by the line. By definition, if you're retracing existing points, then the linestring is no longer the simplest geometry that can represent this pointset, and MakeValid() will give you a multilinestring instead (and lose your Z/M values).

  Unfortunately, if you're working with GPS data or similar then it's quite likely that you might have retraced your path at some point in the route, so linestrings are not always that useful in these scenarios :( Arguably, such data should be stored as a multipoint anyway since your data represents the discrete location of an object sampled at regular points in time.

In your case it validates just fine:

select geometry::STGeomFromText('MULTIPOINT (0 0 1, 0 1 2, 0 -1 3)',4326)
    .IsValidDetailed()

24400: Valid

If you absolutely need to maintain these as LINESTRINGS, then you will have to write your own version of MakeValid that slightly adjusts some of the source X or Y points by some tiny value, while still preserving Z (and doesn't do other crazy things like convert it into other object types).

I'm still working on some code, but give a look at some of the starting ideas here:

Converting linestring to points
Rob Farley Blog: Converting points to a path
Create geography polyline from points in T-SQL

EDIT Ok, a few things I found while testing:

If the geometry object is invalid, you just can't do much with it. You can't read the STGeometryType, you can't get the STNumPoints or use STPointN to iterate through them. If you can't use MakeValid, you're basically stuck with operating on the text representation of the geographic object.
Using STAsText() will return the text representation of even an invalid object, but doesn't return Z or M values. Instead, we want AsTextZM() or ToString().
You can't create a function that calls RAND() (functions need to be deterministic), so I just made it nudge by successively larger and larger values. I really have no idea what the precision of your data is, or how tolerant it is of small changes, so use or modify this function at your own discretion.

I have no idea if there are possible inputs that will cause this loop to go on forever. You have been warned.

CREATE FUNCTION dbo.FixBadLineString (@input geography) RETURNS geography
AS BEGIN
DECLARE @output geography

IF @input.STIsValid() = 1   --send valid objects back as-is
  SET @output = @input;
ELSE IF LEFT(@input.IsValidDetailed(),6) = '24413:'
--"Not valid because of two overlapping edges in curve"
BEGIN
  --make a new MultiPoint object from the LineString text
  DECLARE @mp geography = geography::STGeomFromText(
      REPLACE(@input.AsTextZM(), 'LINESTRING', 'MULTIPOINT'), 4326);
  DECLARE @newText nvarchar(max); --to build output
  DECLARE @point int
  DECLARE @tinynum float = 0;

  SET @output = @input;
  --keep going until it validates
  WHILE @output.STIsValid() = 0
  BEGIN
    SET @newText = 'LINESTRING (';
    SET @point = 1
    SET @tinynum = @tinynum + 0.00000001

    --Loop through the points, add a bit and append to the new string
    WHILE @point <= @mp.STNumPoints()
    BEGIN
      SET @newText = @newText + convert(varchar(50),
               @mp.STPointN(@point).Long + @tinynum) + ' ';
      SET @newText = @newText + convert(varchar(50),
               @mp.STPointN(@point).Lat - @tinynum) + ' ';
      SET @newText = @newText + convert(varchar(50),
               @mp.STPointN(@point).Z) + ', ';
      SET @tinynum = @tinynum * -2
      SET @point = @point + 1
    END

    --close the parens and make the new LineString object
    SET @newText = LEFT(@newText, LEN(@newText) - 1) + ')'
    SET @output = geography::STGeomFromText(@newText, 4326);
  END; --this will loop if it is still invalid
  RETURN @output;
END;
--Any other unhandled error, just send back NULL
ELSE SET @output = NULL;

RETURN @output;
END

Instead of parsing the string, I chose to create a new MultiPoint object using the same set of points, so I could iterate through them and nudge them, then reassemble a new LineString. Here's some code to test it, 3 of these values (including your sample) start invalid but are fixed:

declare @geostuff table (baddata geography)

INSERT INTO @geostuff (baddata)
          SELECT geography::STGeomFromText('LINESTRING (0 0 1, 0 1 2, 0 -1 3)',4326)
UNION ALL SELECT geography::STGeomFromText('LINESTRING (0 2 0, 0 1 0.5, 0 -1 -14)',4326)
UNION ALL SELECT geography::STGeomFromText('LINESTRING (0 0 4, 1 1 40, -1 -1 23)',4326)
UNION ALL SELECT geography::STGeomFromText('LINESTRING (1 1 9, 0 1 -.5, 0 -1 3)',4326)
UNION ALL SELECT geography::STGeomFromText('LINESTRING (6 6 26.5, 4 4 42, 12 12 86)',4326)
UNION ALL SELECT geography::STGeomFromText('LINESTRING (0 0 2, -4 4 -2, 4 -4 0)',4326)

SELECT baddata.AsTextZM() as before, baddata.IsValidDetailed() as pretest,
 dbo.FixBadLineString(baddata).AsTextZM() as after,
 dbo.FixBadLineString(baddata).IsValidDetailed() as posttest
FROM @geostuff


It was a case sensitivity issue. Using inputAttrColumns N(\"Cat_Zones\") solved the problem.


SQL Server Basic Availability group: one or zero listener for multiple AG

The listener has to be unique. When you attempt to create the listener you will get an error message that is difficult to understand, but basically, the listener setup program detects that it is in use.
Multiple AGs on the same nodes can share a listener.


Foreign key set to cascading but fails to update - why?

It appears as if this is not permitted in innodb which I assume is the engine you are using. From  http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html:

  If ON UPDATE CASCADE or ON UPDATE SET NULL recurses to update the same table it has previously updated during the cascade, it acts like RESTRICT. This means that you cannot use self-referential ON UPDATE CASCADE or ON UPDATE SET NULL operations. This is to prevent infinite loops resulting from cascaded updates. A self-referential ON DELETE SET NULL, on the other hand, is possible, as is a self-referential ON DELETE CASCADE. Cascading operations may not be nested more than 15 levels deep.


Grouping Sessions using a DATETIME stamp which can Span across a DAY

CREATE TABLE TEST
(
DTTM DATETIME,
SESSION_ID INT
)

INSERT INTO TEST (DTTM, SESSION_ID)
select '2016-08-29 14:24:28.450', 297692378 union
select '2017-04-13 23:54:53.760', 297692378 union
select '2017-04-13 23:59:53.477', 297692378 union
select '2017-04-14 00:04:52.897', 297692378 union
select '2017-04-14 00:04:53.790', 297692378 union
select '2017-04-14 00:44:53.790', 297692378
GO

First I've added a new record just to check it runs for a more than 20 min.
select '2017-04-14 00:44:53.790', 297692378

Then I've added a new column called RANK to store the final result.

ALTER TABLE TEST ADD [RANK] int;

GO

I've used LAG() window function to calculate DATEDIFF between current and next row.

SELECT DTTM, SESSION_ID,
       DATEDIFF(minute, COALESCE(LAG(DTTM) OVER (ORDER BY DTTM, SESSION_ID), DTTM), DTTM) DIF_MIN
FROM   TEST
GO

DTTM                | SESSION_ID | DIF_MIN
:------------------ | ---------: | ------:
29/08/2016 14:24:28 |  297692378 |       0
13/04/2017 23:54:53 |  297692378 |  327450
13/04/2017 23:59:53 |  297692378 |       5
14/04/2017 00:04:52 |  297692378 |       5
14/04/2017 00:04:53 |  297692378 |       0
14/04/2017 00:44:53 |  297692378 |      40

Then I've used a CURSOR just to calculate RANK field. Basically it acumulates minutes between records, until it hits 20 or more minutes.

DECLARE @dttm datetime,
        @session_id int,
        @diff_min int,
        @acm_diff int,
        @rank int,
        @last_dttm datetime;

SET @diff_min = 0;
SET @acm_diff = 0;
SET @rank = 0;
SET @last_dttm = NULL;

DECLARE curMin CURSOR FAST_FORWARD  FOR
SELECT DTTM, SESSION_ID,
       DATEDIFF(minute, COALESCE(LAG(DTTM) OVER (ORDER BY DTTM, SESSION_ID), DTTM), DTTM) DIF_MIN
FROM   TEST

OPEN curMin;
FETCH NEXT FROM curMin INTO @dttm, @session_id, @diff_min;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @last_dttm IS NULL OR @acm_diff + @diff_min > 20
    BEGIN
        SET @rank = @rank + 1;
        SET @acm_diff = 0;
    END
    ELSE
    BEGIN
        SET @acm_diff = @acm_diff + @diff_min;
    END

    UPDATE TEST
    SET    [RANK] = @rank
    WHERE  DTTM = @dttm
    AND    SESSION_ID = @session_id;

    SET @last_dttm = @dttm;

    FETCH NEXT FROM curMin INTO @dttm, @session_id, @diff_min;
END

CLOSE curMin;

SELECT   DTTM, SESSION_ID,
         DATEDIFF(minute, DTTM, COALESCE(LEAD(DTTM) OVER (ORDER BY DTTM, SESSION_ID), DTTM)) DIF_MIN,
         [RANK]
FROM     TEST
ORDER BY DTTM, SESSION_ID;
GO

DTTM                | SESSION_ID | DIF_MIN | RANK
:------------------ | ---------: | ------: | ---:
29/08/2016 14:24:28 |  297692378 |  327450 |    1
13/04/2017 23:54:53 |  297692378 |       5 |    2
13/04/2017 23:59:53 |  297692378 |       5 |    2
14/04/2017 00:04:52 |  297692378 |       0 |    2
14/04/2017 00:04:53 |  297692378 |      40 |    2
14/04/2017 00:44:53 |  297692378 |       0 |    3

dbfiddle here


How to improve performance of a query matching on the same table

You can split your query into two different queries allowing for two different covering indexes to help you find the rows faster.

One query that checks FirstName and another that checks BirthDate.

select C1.CustomerId,
       C2.CustomerId
from dbo.Customer as C1
  inner join dbo.Customer as C2
    on C1.CustomerId <> C2.CustomerId and
       C1.FirstName = C2.FirstName and
            (
              C1.EmailAddress = C2.EmailAddress or
              C1.MobilePhoneNumber = C2.MobilePhoneNumber or
              (
                 C1.HomeAddressLine1 = C2.HomeAddressLine1 and
                (C1.HomePostCode = C2.HomePostCode or C1.HomeSuburb = C2.HomeSuburb)
              )
            );

select C1.CustomerId,
       C2.CustomerId
from dbo.Customer as C1
  inner join dbo.Customer as C2
    on C1.CustomerId <> C2.CustomerId and
       C1.BirthDate = C2.BirthDate and
            (
              C1.EmailAddress = C2.EmailAddress or
              C1.MobilePhoneNumber = C2.MobilePhoneNumber or
              (
                 C1.HomeAddressLine1 = C2.HomeAddressLine1 and
                (C1.HomePostCode = C2.HomePostCode or C1.HomeSuburb = C2.HomeSuburb)
              )
            );

The index you need to support those queries:

create nonclustered index IX_FirstName on dbo.Customer(FirstName)
  include(EmailAddress, MobilePhoneNumber, HomeAddressLine1, HomePostCode, HomeSuburb);

create nonclustered index IX_BirthDate on dbo.Customer(BirthDate)
  include(EmailAddress, MobilePhoneNumber, HomeAddressLine1, HomePostCode, HomeSuburb);

In my rather limited test case I saw an improvement from more time than I care to wait down to 4 seconds*.

The query plans I got for the two queries.

*I actually started the original query and forgot about it. Scratched my head trying to figure out why my computer was sluggish and found that the query had been running for 40 minutes.


How often to backup the master database?

Your master DB is generally very small. Just back it up with the rest of your databases, at least daily. Does it matter?

Personally, when SHTF I'd like a master db backup that is a few hours old, even if I have 400 other identical ones going back 400 days. I don't want to think too much in the case I have to restore it...


Grant dbcreator only for databases matching prefix

You can use a server level trigger like this:

CREATE OR ALTER TRIGGER [database_name_check] ON ALL SERVER
      FOR CREATE_DATABASE
AS
      BEGIN
            SET NOCOUNT ON;

            DECLARE @event_data XML;
            SET @event_data = EVENTDATA();
            IF ((SELECT @event_data.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(256)') ) =  'NADABRUTO\edarl'
                 AND (SELECT @event_data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(256)') ) NOT LIKE  '%Stack%')
                        BEGIN
                            RAISERROR('NO CAN DO, BUCKAROO', 0, 1) WITH NOWAIT;
                            ROLLBACK;
                        END

      END;

GO
ENABLE TRIGGER [database_name_check] ON ALL SERVER;
GO

Or at the database level like this:

USE StackOverflow2013
GO

CREATE OR ALTER TRIGGER [database_name_check] ON DATABASE
      FOR CREATE_DATABASE
AS
      BEGIN
            SET NOCOUNT ON;

            DECLARE @event_data XML;
            SET @event_data = EVENTDATA();
            IF ((SELECT @event_data.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(255)') ) =  'NADABRUTO\edarl'
                 AND (SELECT @event_data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(255)') ) NOT LIKE  '%Stack%')
                        BEGIN
                            RAISERROR('NO CAN DO, BUCKAROO', 0, 1) WITH NOWAIT;
                            ROLLBACK;
                        END

      END;

GO

ENABLE TRIGGER [database_name_check] ON DATABASE;
GO


SSMS uses port 1434 and the UDP protocol.  This has to be allowed on the firewall, too.

Quote:

  When SQL Server clients request SQL Server resources, the client
  network library sends a UDP message to the server using port 1434. SQL
  Server Browser responds with the TCP/IP port or named pipe of the
  requested instance. The network library on the client application then
  completes the connection by sending a request to the server using the
  port or named pipe of the desired instance.

From here.


How to fix data discrepancies between columns from two tables

With extensive experience consulting in the automotive sector, eBay is likely using real models as they come from OEM. You're conflating models and trims in your own system.

F150 Crew Cab            -- Crew cab is a trim level.
Silverado 2500/3500      -- Likely two models here.
Ram Pickup 2500-5500 HD  -- likely two models here too, with HD as a trim.

You need to figure out what is a model and what is a trim. Or, use a third party data provider like Chromedata which provides styles that often determined by model and trim. If eBay provides you a list of models, you need to map those into your local data source. Usually you'll have like 60%-direct match, 20%-inferrable with accuracy, 20%-requires human Q/A.

This is just part of how to integrate with other systems.

Looking at the new eBay guide I can give you an idea of the workload if that's not an option (which I may try first because it's probably all in a day's work).

From the eBay motors listing:

This listing of category ids needs to be loaded into a hierarchical database., alternatively and better use the api
Each entry needs to be weighed with the parents. and flattened.
Collapse it into a full text search vector.

Local database

In your own database, select all of the distinct "models" and make sure that they all have the same make.
Create a tsquery of the make, model
Run this against the eBay motors database above.


single node stuck in WSREP has not yet prepared node for application use

I met this issue too, and solved it by re-sync that node through SST. I use RSYNC as SST method to make sure the consistence between nodes.


What determines the time waited for Postgres Transaction Timeout?

idle_in_transaction_session_timeout (integer)

Releases locks on open transactions that have become idle.

idle_in_transaction_session_timeout parameter


Use Visual Studio Sql Server Data Tools. It has a schema compare tool that you can use to view differences between any two databases. I have used this tool to do things very similar to what you are asking.

Instructions for Using the Schema Compare Tool
General Instructions are

Create Database Project
Connect Databases to Project
Click "Schema Compare" in SQL Menu
Select Source and Target Database
Hit Compare Button
Select What you want to keep/get rid off
Click on Generate Script


Changing the mysql.proc table directly after upgrade

The mysql_upgrade utility has an --upgrade-system-tables (only) option.

I tested it on a local machine that had already been done, but judging from the output and the documentation, it seems to be what you're looking for.

Of course, be sure you use the version of mysql_upgrade that exactly matches whatever version they have upgraded you to (e.g., 5.5.30).

sqlbot@dev:/usr/local/mysql$ bin/mysql_upgrade --upgrade-system-tables
Looking for 'mysql' as: bin/mysql
The --upgrade-system-tables option was used, databases won't be touched.
This installation of MySQL is already upgraded to 5.5.30, use --force if you still need to run mysql_upgrade
sqlbot@dev:/usr/local/mysql$


Job Activity Monitor - Log File Viewer is showing Duration for running jobs

It seems that, if jobhistory run_duration is null (meaning the job is still running), you could simply calculate the current run_duration using DATEDIFF between jobactivity start_execution_date and sysdatetime().

Since the normal Run_Duration from jobhistory is an int in HHMMSS format, there are some calculations to achieve that.

Something like:

SELECT ja.job_id
    ,j.NAME AS job_name
    ,ja.start_execution_date
    ,ja.stop_execution_date
    ,ISNULL(last_executed_step_id, 0) + 1 AS current_executed_step_id
    ,Js.step_name
    ,CASE
        WHEN jh.run_duration IS NULL
            THEN
                convert(int,RIGHT('0' + CAST(datediff(second, ja.start_execution_date, sysdatetime()) / 3600 AS VARCHAR),2)  +
                RIGHT('0' + CAST((datediff(second, ja.start_execution_date, sysdatetime()) / 60) % 60 AS VARCHAR),2)  +
                RIGHT('0' + CAST(datediff(second, ja.start_execution_date, sysdatetime()) % 60 AS VARCHAR),2))
        ELSE jh.run_duration
        END AS run_duration
FROM msdb.dbo.sysjobactivity ja
LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id
JOIN msdb.dbo.sysjobsteps js ON ja.job_id = js.job_id
    AND ISNULL(ja.last_executed_step_id, 0) + 1 = js.step_id
WHERE ja.session_id = (
        SELECT TOP 1 session_id
        FROM msdb.dbo.syssessions
        ORDER BY agent_start_date DESC
        )
    AND start_execution_date IS NOT NULL
    AND stop_execution_date IS NULL;


Table creation along with dynamic partitioning (interval partitioning) in Oracle 10g EE

I'm afraid that interval partitioning was introduced on Oracle 11g.


Postgres strategy for UPDATE SET x = NULL

PostgreSQL never performs an UPDATE by modifying the existing data in place.  If you set columns to NULL, a new row version will be created just as with any other UPDATE, and the previous row versions will remain until VACUUM reclaims them.
But be warned that

VACUUM will only delete the old row version if there is no long running transaction that still might need old data.
VACUUM will not overwrite the data, so the old value will still be on disk until the space is reused.

Concerning the GDPR, the wording is:

The data subject shall have the right to obtain from the controller the erasure of personal data concerning him or her without undue delay and the controller shall have the obligation to erase personal data without undue delay

The term “erasure” is nowhere defined in that law, so it is subject to interpretation. My bet is that few enough people understand the inner workings of PostgreSQL well enough to contest that DELETE is erasure. And it would take a data forensics expert with advanced PostgreSQL knowledge to retrieve such data. Once VACUUM has run, it is nigh impossible to do that. If I were called to court as an expert witness, I would say that anybody who has run DELETE in the database has taken all possible steps to erase the data.
If you feel paranoid, schedule a regular VACUUM on the table in question, and make sure that you have no long running transactions. Any worry beyond that is silly.


Unknown sticky Problem after MySQL upgrade

PROBLEM
I think your problem stems from a change in sql_mode.

sql_mode is blank in MySQL 5.5
It is not blank in MySQL 5.6

SOLUTION
Run this command
SET GLOBAL sql_mode = '';

Then, add this to my.cnf to keep sql_mode blank should you restart mysqld
[mysqld]
sql_mode = ''

You do not need to restart mysqld since you set it globally
GIVE IT A TRY !!!


Setting up a Postgresql Partition with foreign key

First, I would suggest reading this: http://ledgersmbdev.blogspot.com/2012/08/postgresql-or-modelling-part-3-table.html because although it doesn't cover partitioning, you are doing partitioning using table inheritance and this will cover a lot of details.

In general there are three ways you can handle this.  The first is to use specialized key tables (managed with triggers) and reference these tables in your fkey constraints.  This is often the simplest approach but it has significant costs in some cases.

The second possibility is you can partition your joining table by the foreign key.  This can lead to major complexity problems though and so aside from rare cases, it is my least-favored approach.

A final possibility is you can write your own constraint triggers to manage foreign key enforcement.

Which approach you choose will be dependent on the complexity of joining information, particularly transitively joining information.  The second approach allows you to do things entirely declaratively at a significant (possibly major) complexity cost, while the first and third move away from declarative approaches, meaning you may also want to use pgTAP to run test cases on your triggers.....

None of these solutions are without significant complexity.  You may want to rethink whether partitioning is the best way to go here and whether partial indexes and other approaches will get you where you need to go.


Converting rows to columns using crosstab in PostgreSQL not working (relation "table" does not exist)

The CTE is not visible inside the crosstab() function. You have to pass the complete query string including the CTE.

Possible alternative: create a temporary table instead of the CTE.

However, there are more problems in your query. all_correct seems undefined. Etc.

Basic instructions:

PostgreSQL Crosstab Query


SQL Server 2012: Monitoring SPID on unpredictable app

I had the exact same situation and I created a SQL Agent job with the following script as the only step to monitor a job that occasionally runs long.

if exists(
        SELECT *
        FROM msdb.dbo.sysjobactivity AS sja
        INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id
        WHERE sja.start_execution_date IS NOT NULL
           AND sja.stop_execution_date IS NULL and
           sj.name ='Your job name'
        )
-- send an email
exec msdb.dbo.sp_send_dbmail
    @recipients ='email recipients',
    @subject='The job is running long',
    @body='Check the job on the server to see why it is running long'

In your case you would schedule this for 15 minutes after each execution of the job being monitored.


How do MariaDB and MySQL differ in character set handling?

MySQL and MariaDB handle character sets the same.  However...  Different versions of MySQL have different defaults.  Ditto for MariaDB.

SET NAMES declares what encoding is in the client.  This is independent of the CHARACTER SET on a column or table.  (There are, of course, characters that are not representable in latin1, so there can be conversion problems.)  As you say, the conversion occurs on INSERT or SELECT.

To further pursue this, please tell us what client you are using, what connection parameters were used, any SETs performed, and what happened.

For diagnosing what is stored, use SELECT col, HEX(col)....  More details.


Does pure MongoDB use schemas and models like Mongoose?

Does pure MongoDB use schemas and models like Mongoose ODM?

No, the MongoDB server (as at 4.0) does not have a concept of models or data relationships. Data is persisted in Documents which are stored on the server in a binary JSON-like serialization format called BSON.

  Does pure MongoDB have any validators?

The MongoDB server does support per-collection validation rules for inserting & updating documents. In MongoDB 3.6 validation rules are defined using JSON Schema; earlier versions used MongoDB query operators to express validation rules.

Mongoose vs MongoDB schema validation

Typically the goal is to validate data as early as possible in order to provide timely feedback to the end user and avoid unnecessary round trips and processing. For example, validation can happen in the frontend UI, in your application layer, and at the database layer. Frontend UI often has basic validation based on data types and field masks, while custom validation, relationships, and business logic is generally better suited to your application layer. Database validation is a final check to ensure only data fitting an expected (but flexible) schema is persisted.

Using a proposed standard like JSON Schema potentially allows validation rules to be shared by multiple layers of your stack. There are JSON Schema Implementations available for most popular programming languages and browsers. Note: MongoDB's implementation of JSON Schema has an Extension which is the inclusion of a bsonType keyword to allow use of all MongoDB BSON field types.

A current limitation of MongoDB's server-side JSON Schema validation is that it does not provide a detailed report of errors: documents either succeed or fail validation. You may want to watch/upvote SERVER-20547:  Expose the reason an operation fails document validation in the MongoDB Jira issue tracker. Depending on your validation rules and options, failure will either result in an error (the insert/update is rejected) or a warning (the insert/update succeeds but results in a log message). Several of the JSON Schema implementations for application code have more detailed validation reporting suitable for end users.


It may not be the data size, but rather the number of tables and schemas.  Database dumps and restores are known to run into problems in those cases.

For large backups the preferred backup approach is to use pg_basebackup which creates a snapshot of the database files at a specific time.  These are restored much faster.  Unfortunately this does not necessarily work for certain sorts of things, like restoring across major versions (but see pg_upgrade).

the raw data size is not a problem.  But in your case, pg_dump may not be very useful and you may want to look at other ways of making a backup.


InnoDB uses "optimistic" MVCC - when you do some changes, it applies them to the real tables and may even write them to disk. Commit only ensures that all the writes were finished and makes the changes visible and is otherwise almost instantaneous. That means that the rollback has to undo all these changes, possibly rewriting the pages on disk again and it has to do some more work in finding the previous versions in the undo logs etc. And the code is imho more optimized for the "primary" writes than for rollbacks too.

So the rollback may take at least the same and possibly more time than the inserts/updates/deletes took. I've met cases where someone tried to delete million of rows, killed the transaction after few hours (because it kept most of the table locked and they wanted to get rid of it and batch it properly next time) and the rollback took few more hours - in the worst cases they decided to restart the server (by force, because it does not respond until done) and found out that it continued the rollback during startup, effectively becoming inacessible for few more hours (before the kill it at least worked for rest of the data).

But as I wrote - the rollback time depends directly on the number of writes the transaction did and indirectly on how long it was active (more precisely how many changes other concurrent transactions did - the size of undo logs, which are "global" and not per-transaction). So if most of your transactions are select-only or not write heavy, the possible rollbacks possibly do not have to do too much work.

Contrast with other implementations, for example Firebird DB where when I executed a big delete, it finished surprisingly fast, but then when I tried to commit that, it took much longer than the delete itself.


Remove punctuation marks, special chars and space and compare cols

Since you only have 644 rows, you could probably get away (performance-wise) with creating a user defined function.

Create Function [dbo].[RemoveSpecialCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin

    Declare @KeepValues as varchar(50)
    Set @KeepValues = '%[^A-Za-z0-9]%'
    While PatIndex(@KeepValues, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

    Return @Temp
End

Then, assuming your sample data (I purposely changed a value in the last row.)

DROP TABLE IF EXISTS DESCR_COMP;
go
CREATE TABLE DESCR_COMP (
    HCPCS CHAR(5) NOT NULL
    ,SEQNUM CHAR(4) NOT NULL
    ,NEW_LONG_DESC CHAR(1000) NOT NULL
    ,OLD_LONG_DESC CHAR(1000) NOT NULL
    ,SHORT_DESC CHAR(250) NOT NULL
    ,ADD_DT DATE
    ,ACT_EFF_DT DATE
    ,TERM_DT DATE
    ,CONSTRAINT PK_HCPCS PRIMARY KEY CLUSTERED (HCPCS ASC)
    )
insert into DESCR_COMP select 'A0120', '0010', 'Non-emergency transportation: mini-bus, mountain area transports, or other transportation systems', 'Non-emergency transportation: mini-bus, mountain area transports, or other  transportation systems', 'Noner transport mini-bus', '19820101', '20030101', ''
insert into DESCR_COMP select 'A0130', '0010', 'Non-emergency transportation: wheelchair van', 'Non-emergency transportation: wheel-chair van', 'Noner transport wheelch van', '19820101', '', ''
insert into DESCR_COMP select 'A0170', '0010', 'Transportation ancillary: parking fees, tolls, other', 'Transportation ancillary:  parking fees, tolls, other', 'Transport parking fees/tolls', '19820101', '20030101', ''
insert into DESCR_COMP select 'A0180', '0010', 'Non-emergency transportation: ancillary: lodging-recipient', 'Non-emergency transportation: ancillary:  lodging-recipient', 'Noner transport lodgng recip', '19820101', '', ''
insert into DESCR_COMP select 'A0190', '0010', 'Non-emergency transportation: ancillary: meals-recipient', 'Non-emergency transportation: ancillary:  meals-recipient', 'Noner transport meals recip', '19840101', '', ''
insert into DESCR_COMP select 'A0200', '0010', 'Non-emergency transportation: ancillary: lodging escort', 'Non-emergency transportation: ancillar:  lodging escort', 'Noner transport lodgng escrt', '19820101', '', ''
go

SELECT *
FROM DESCR_COMP
WHERE dbo.RemoveSpecialCharacters(OLD_LONG_DESC) <> dbo.RemoveSpecialCharacters(NEW_LONG_DESC)

Results:

| HCPCS | SEQNUM | NEW_LONG_DESC                                           | OLD_LONG_DESC                                           | SHORT_DESC                   | ADD_DT     | ACT_EFF_DT | TERM_DT    |
|-------|--------|---------------------------------------------------------|---------------------------------------------------------|------------------------------|------------|------------|------------|
| A0200 | 0010   | Non-emergency transportation: ancillary: lodging escort | Non-emergency transportation: ancillar:  lodging escort | Noner transport lodgng escrt | 1982-01-01 | 1900-01-01 | 1900-01-01 |


Copy whole database from SQL Server to SQL Server using linked server

Backup/restore, the Copy Database Wizard, and Log Shipping are all potentially better choices than linked server copies.

See the following documentation:

Back Up and Restore of SQL Server Databases
Use the Copy Database Wizard
About Log Shipping (SQL Server)


Transaction log changes not synchronizing with AlwaysOn

Can anyone shed some light on this behavior?

The behavior you're describing is not working as described. The end result is the same but the inner workings are different than what you're thinking.

The Availability Group for this specific DB is synchronized and healthy.
I had to add a second transaction log file (logfile2) to the primary. The file did not replicate until I restarted the instance (was doing other maintenance). After the instance was restarted the transaction log appeared at the primary and secondary. 12 hours have passed since the instance was restarted and the log files show the same time/datestamp AND file size.

Restarting the instance didn't actually fix anything. If the databases were synchronized then the log blocks were hardened on the secondary. This doesn't mean there were redone yet, jus that they were hardened to disk.
By restarting the instance you've effectively forced redo to complete to a point and thus creating the second log file. If it is taking this much time to redo log blocks, there may be an issue with how much load the secondary could handle and this is one of the ways you'll see it manifest.
The last item that is indicative of this behavior is a blocked redo thread, which is most likely the cause.

It appears however that transactions hitting logfile2 on the primary are not being synchronized to the secondary for over 20 minutes.

Then the AG wouldn't be synchronized, it'd change to synchronizing. This is more likely what I descried above, where the log blocks were hardened and weren't yet redone (blocked redo thread).
Assuming the log blocks were sent, the AG is synchronized, and the redo thread was not blocked... this could be due to the transaction still being open. Until the transactions is committed (trancount = 0) your queries on the secondary won't see it as it isn't a yet committed transaction.
Assuming the transaction was committed (trancount = 0), the default isolation level of read committed is mapped to the snapshot isolation level on the secondary. This could also be due to isolation level changes and the query running.

In addition to the normal transaction log backup that runs every 2 hours, which includes a CHECKPOINT command, I had to install a SQL agent job that performs a CHECKPOINT on the DB every 15 minutes. The reasoning is that transactions going to logfile2 are languishing there and not getting applied to the database; so, I force the flush of the log files to the database, which then synchronizes to the secondary.

This is not how the process works. Checkpoint itself doesn't flush log records, they may need to be flushed from the log buffer because of WAL (Write Ahead Logging) enforcement for the data pages being flushed but it will do nothing to the log file. The setup done with the agent job and checkpoint is not helping one bit.
The unit of transport is a log block, not individual records. When log blocks end and are flushed to disk, if the database is part of AlwaysOn Availability Groups it is copied into (depending on version) another buffer and eventually sent through the transport when the secondary checks in. This is a gross over simplification but shows how it works.
What to do next?

Checked for blocked redo threads on the secondary.
Make sure the AG is synchronized and not synchronizing
Make sure the transactions have @@TRANCOUNT = 0
Make sure the secondary has enough disk IOPs to handle the load without growing the redo queue size


Understanding statistics, execution plans, and 'ascending key problem'

Am I correct in saying that statistics are only used when creating the execution plan

No, out-of-date statistics may cause an optimality-related recompilation of the affected statement.

  We have an ascending date/time column in one of our largest tables that we query regularly

Sub-optimal execution plans caused by predicate values being outside (specifically above) the range of values stored in the corresponding statistics histogram is known as the Ascending Key Problem. Rebuilding statistics is one possible solution, but it can be quite resource-intensive. Alternatives include:

Trace flags 2389 and 2390. This requires that an index exists with the problematic column as the leading key. It does not work with partitioned tables, and is only effective in SQL Server 2014 if the original cardinality estimator is used. Trace flag 4139 may also be required if the statistics object is branded stationary.
Upgrade to SQL Server 2014. The new cardinality estimator includes logic to estimate beyond the histogram using average density information. This can be less accurate than the 2389/2390 trace flags in some important circumstances.
Enable more frequent automatic statistics updates for large tables with trace flag 2371. With this trace flag, instead of updating after 20% + 500 changes, only SQRT(1000 * Table rows) modifications are required. This is not as complete a solution as those mentioned previously, as updates may still not be triggered often enough.

If the source of your problem is not so much frequent plan compilations based on beyond-histogram predicate values, but more about the effects of occasionally caching such a bad plan as a result of parameter sniffing, you could also consider:

Disabling parameter sniffing using trace flag 4136
Using OPTIMIZE FOR (@parameter = value) to compile a plan for a known representative value
Using OPTIMIZE FOR (@parameter UNKNOWN) to optimize using the average distribution
Using OPTIMIZE FOR UNKNOWN (same as 4136, but per-query)
Using OPTION (RECOMPILE) to compile every time, sniffing the particular value. If the vast majority of runtime values are within the histogram, this may be effective.

For more information about parameter sniffing, embedding and the recompile options, see my article on SQLperformance.com.


Mysql Server Login and Permission Deny Error

First of all before performing an upgrade you should have taken a backup of all important databases (or simply all databases) using the mysqldump command.

Anyhow, to fix this please run:

shell> mysql_upgrade

To understand more what mysql_upgrade does see here.

If you get permissions errors:

mysql_upgrade -u root -p (give the root password here)

If still it fails, rename /etc/my.cnf to /etc/my.cnfbku and then rerun the command.

This will fix all the issues regarding the upgrade.


add column without metadata lock in mysql 5.6

I have executed the very same query on 5.6.14 and got a positive result:

alter table candidate_candidate add column responsible_user_id int, algorithm=INPLACE, lock=NONE;
Query OK, 0 rows affected (0.48 sec)
Records: 0  Duplicates: 0  Warnings: 0

So I am sure that this kind of operation can be done online. You are probably experiencing some of the exceptions shown at: http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-limitations.html

My bet would be on having outdated temporal datatypes, foreign keys or fulltext indexes. Any of those on the table? Recently migrated from 5.5? If I am wrong, can you show the full CREATE TABLE?


How do I join these two tables, showing results from the second table?

select A.cid, A.time, IF(A.cid = B.CID2,C.cpu,NULL)
 from A
 LEFT JOIN B on A.cid = B.CID2
 where A.time ="2018-12-17 14:00:00";


How to group items by path (graph structure) and leave only those with highest score using t-sql?

I have completely redone my answer (based on the additional information you provided in your question).

You indicated that you tried recursion and it was too slow on a large set of data.  I've created a cursor based solution that works on your test data, but I'm wondering if it gives you the correct answer on your production data.  With such a small set of test data, it's difficult to spot flaws in my logic.

Can you give it a try and let me know if I'm any closer to a proper solution?

DECLARE @OfferId INT
DECLARE @OffersToDelete TABLE (id INT)

--Declare cursor for all Offers
DECLARE _CURSOR CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT [o].offerid
FROM Offers [o]
ORDER BY [o].OfferId

OPEN _CURSOR

FETCH NEXT
FROM _CURSOR
INTO @OfferId

DECLARE @Related TABLE (OfferId INT)

WHILE @@FETCH_STATUS = 0
BEGIN
    --For each Offer, initialize @Related table
    DELETE
    FROM @Related

    --Insert into @Related the Offerid that we are currently processing
    INSERT INTO @Related
    SELECT [or].OfferId2
    FROM OffersRelation [or]
    WHERE [or].OfferId1 = @OfferId

    --Now, loop through all offers related to the one we're processing
    --When @@ROWCOUNT = 0, you have processed all related rows and need
    --to break out of the while loop
    WHILE 1 = 1
    BEGIN
        INSERT INTO @Related
        SELECT [or].OfferId2
        FROM OffersRelation [or]
        JOIN @Related [r]
            ON [r].OfferId = [or].OfferId1
        WHERE NOT EXISTS (
                SELECT *
                FROM @Related
                WHERE OfferId = [or].OfferId2
                )
            AND [r].OfferId <> @OfferId

        IF @@ROWCOUNT = 0
            BREAK;
    END;

    --With all related rows, including the offer we're processing,
    --add a row number ordered by OfferScore desc and insert all rows
    --not = 1 into the @OffersToDelete table
    WITH OfferAndRelated
    AS (
        SELECT Offerid
            ,offerscore
        FROM offers
        WHERE offerid = @OfferId

        UNION

        SELECT [o].offerid
            ,[o].offerscore
        FROM @Related [r]
        JOIN Offers [o]
            ON [o].OfferId = [r].OfferId
        )
        ,OfferAndRelatedWithRowNumber
    AS (
        SELECT *
            ,ROW_NUMBER() OVER (
                ORDER BY OfferScore DESC
                ) AS rn
        FROM OfferAndRelated
        )
    INSERT INTO @OffersToDelete
    SELECT OfferId
    FROM OfferAndRelatedWithRowNumber
    WHERE rn <> 1

    --  SELECT *
    --  FROM @Related
    FETCH NEXT
    FROM _CURSOR
    INTO @OfferId
END

CLOSE _CURSOR

DEALLOCATE _CURSOR

--Delete from Offers where the OfferId is on the @OffersToDelete table
DELETE [o]
FROM Offers [o]
JOIN @OffersToDelete otd
    ON otd.id = [o].OfferId

SELECT *
FROM Offers

| OfferId | OfferScore |
|---------|------------|
| 2       | 20         |
| 6       | 6          |
| 8       | 8          |


MySQL 5.6 Slaves Fail to Reconnect to older MySQL server when it goes down for backups

While replicating from a MySQL 5.0 Master to MySQL 5.6 Slave is allowed, it is not good to keep that scenario indefinitely. Why ? The way MySQL 5.6 reconnects to an older Master may be more difficult with older Masters.
To illustrate problems of this nature, consider how MySQL Replication can break between versions because of Binary Logs changing between versions of MySQL. I have discussed this before in my answer to the Jan 02, 2015 question mysql replication master 5.5 slave 5.1 error on create database.
In that answer, I mentioned how binary log events have been added with each new major release of MySQL. This may call for MySQL 5.6 to ignore or bypass events from the binary logs it does not understand. In some rare cases, the SQL will break while trying to unravel and binary log event that may have changed between versions. In that instance, the SQL thread for MySQL Replication WILL NEVER START BACK UP.
While I am talking about the SQL thread in my scenario, your situation involves the I/O thread. There may some issues (bug or untested feature) causing reconnects not work. In light of this, a Slave reconnecting to a Master of a previous version of MySQL is no longer a trivial matter.
There is a current bug report for MySQL 5.6.20 that has been triaged since August 2014 (Bug #73494 : Slave can't reconnect after 2003 error when sha256_password is used). This is not directly related to connecting to a older Master but the problem exists with MySQL 5.6 itself.
You should either consider one of the following

Increase max_allowed_packet to 1G on the Master and all Slaves
refrain from using MySQL 5.6 as a Slave to an older Master
upgrade to MySQL 5.6 across the board

UPDATE 2015-05-21 18:06 EDT
If Master and Slave have max_allowed_packet set at 16M, then there is no need to change it.
If you can live without changing skip-networking, try the following

Step 01 : STOP SLAVE IO_THREAD;
Step 02 : Run mysqldump --single-transaction ...
Step 03 : START SLAVE IO_THREAD;

This way, the I/O Thread stays connected 24 / 7 and you never have to restart mysql.
GIVE IT A TRY !!!


keeping database columns consistent with documentation

If the only reason not to add columns is "but that would make the database design, in documentation, inconsistent with the columns present in the database" then you need to look at your procedures.

If there is a business reason to add the fields and your systems support it then you need to have your documentation updated at the same time as the table. This should not be much more than adding a description fo the field to the documentation, perhaps having a place in documentation just for these user added fields.

Note that the business wants the system and the documentation is only a tool to help and not the end result.


When you say "a list of tables which reference this table" do you mean a list of tables that have a foreign key that reference your primary key? If so
you can get a list of foreign keys that reference your table 'User' with the following query:

SELECT name as Foreign_Key
,schema_name(schema_id) as Schema_Name
,object_name(parent_object_id) as Table_Name
FROM sys.foreign_keys
WHERE Referenced_object_id = object_id('dbo.user','U');

if your table belongs to a different schema other than dbo then replace the schema name.

This query will give you all the referenced foreign keys to your table User. You will now know what joins you need to make in your update statement to maintain referential integrity.

Edit: woahhhhhh screw my answer,
The best answer by db2 gives more detail in his query.
How to Find the Foreign Key Associated with a Given Primary Key

Add a WHERE clause to his query before the ORDER BY clause for a specific table.

ex. WHERE o2.name = 'User'


Clarification on steps creating Database Replication Definition

step 1 not needed and not normally recommended as it can lead to contention issues on system tables

setp 3 is needed if you want to replicate DDL and system procs (which coincides with what you've got in step 4 - replicate DDL/sysprocs)

create sub should be step 5

check sub should be step 6

step 7 would be sending a rs_ticket from the PDB and making sure it arrives in RDB's rs_ticket_history table

make sure your maintuser (in RDB) is aliased to dbo to get around permissions issues


Nullable One To Many w/Join Table - Indexing

If a session can only belong to a single user at any given time, the more appropriate model would be without a join table (user_sessions). What's the problem with column user_id being NULL?

If you need it to be NOT NULL and you don't always know the user at creation time, just insert a user "unknown" (with user_id 0 in my example). Can serve as DEFAULT value, too ...

CREATE TABLE sessions
  session_id   serial PRIMARY KEY
 ,user_id      int NOT NULL DEFAULT 0 REFERENCES users(user_id)
 ,session_date timestamp
 ...
)

Note on indexing strategy

Postgres can combine multiple indices with excellent speed. Typically, in your original scenario, with a nested loop:

  nested loop join: The right relation is scanned once for every row found in the left relation. This strategy is easy to implement but can
  be very time consuming. (However, if the right relation can be scanned
  with an index scan, this can be a good strategy. It is possible to use
  values from the current row of the left relation as keys for the index
  scan of the right.)

Say, you have these indices:

sesssions(session_id)
user_sessions(session_id, user_id)

Both should be covered by the pk constraints in your original model automatically. However, the second one could go either way (user_id, session_id) or (session_id, user_id) and that matters:
Is a composite index also good for queries on the first field?

For best performance in a query like:

SELECT u.user_name, s.session_date
FROM   users         u
JOIN   user_sessions us USING (user_id)
JOIN   sessions      s  USING (session_id)
WHERE  u.user_name = 'foo'

You would need the index (or pk constraint) to be

user_sessions(user_id, session_id)

Or have an additional index with user_id as first column.


Tracking of deleted records who delete the transaction in SQL server 2008r2

You'd have to do forensics on the transaction logs using 3rd party tools

SQL Log Rescue
ApexSQL Log
?

Of course, the log entries may not be there due to backups or simple recovery.

If you want auditing, build it in. And restrict permissions of course.


Faulty oracle11g regexp_replace on removing duplicates

In your 2nd example the values are unique, but you're searching for any repeated match and there's multiple matches:

'106RONBADB0W2562701,106RONCRT00W2562701,106RPB0130350001,106RONBADB0W2562701'

It's not concatenating, it's removing some characters :-)

Usually adding a word boundary \b should work, but not in Oracle.

I found this is working '(.+?,)\1+', but you need to add a final comma to the string (fiddle)

/* CORRECT */
select '308RONBADB046782001,308RONBADB046782001,308RONCRT0046782001,308RONCRT0046782001,308RONMFRT046782001,308RONMFRT046782001' as str,
       regexp_replace('308RONBADB046782001,308RONBADB046782001,308RONCRT0046782001,308RONCRT0046782001,308RONCRT0046782001,308RONMFRT046782001,308RONMFRT046782001' || ',',
                  --'(\b[^,]+)(,*\1)+', --should work, doesn't in Oracle
                  '(.+?,)\1+',
                  '\1') as str_uq
from dual
union all
/* FAULTY */
select '106RONBADB0W2562701,106RONCRT00W2562701,106RPB0130350001,106RONBADB0W2562701' as str,
       regexp_replace('106RONBADB0W2562701,106RONCRT00W2562701,106RPB0130350001,106RONBADB0W2562701' || ',',
                  --'(\b[^,]+)(,*\1)+', --should work, doesn't in Oracle
                  '(.+?,)\1+',
                  '\1') as str_uq
from dual


The scripting answers above will obviously work.

But, assuming you have to, have to, have it executed from within the DB (let us know why :) ), the code below (minus the loop, etc.) should get you on your way.

You were very close btw.

CREATE or REPLACE FUNCTION function_1(rows text)
RETURNS void AS $$
begin
  execute 'COPY (
    SELECT *
      FROM backup_table
     WHERE id = ' || rows || '
       AND status = 1
  ) TO ''/var/lib/pgsql/'||rows||'.dat''';
end
$$ language 'plpgsql';

EDIT 1: function loop example and formatting

CREATE or REPLACE FUNCTION function_loop(rows_arr text[])
RETURNS void AS $$
declare
  crtRow text;
begin
  ForEach crtRow in array rows_arr
  LOOP
     perform function_1(crtRow);
  end loop;
end
$$ language 'plpgsql';

Executed as

select function_loop('{1,2,3}');

Please note there are no checks being performed here.

E.g. This will create a zero byte 3.dat even if there is no id=3 in the table

EDIT 2: A more extensible option for thousands of id would be

CREATE or REPLACE FUNCTION function_loop1(startId int, endId int)
RETURNS void AS $$
declare
  allIds text[];
  crtRow text;
begin
  select array_agg(id::text) into allIds
    from backup_table
   where status=1
     and id between startId and endId;
  ForEach crtRow in array allIds
  LOOP
    perform function_1(crtRow);
  end loop;
end
$$ language 'plpgsql';


sql server 2014 unable to connect with Microsoft account or sa

The following steps should allow you to create a new sysadmin account, you can then connect using the new account and update the sa password as required.

Log in to Windows as a local administrator (must be local admin, not just domain admin, on the machine hosting SQL Server).
Stop the SQL Server Windows service.
Open command prompt (cmd.exe) as an administrator and run the SQL Server exe with the -m flag (minimal config). Leave this running. (For named instances use -s {instancename}, see https://technet.microsoft.com/en-us/library/aa178031)

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlservr.exe -m
Open another command prompt window as an administrator.
Run SqlCmd.exe and connect to the instance.

sqlcmd -s localhost\sqlexpress
Create a user and assign to the sysadmin role.

create login new_admin with password='new_password';
go

sp_addsrvrolemember new_admin, 'SYSADMIN';
go
Exit sqlcmd using ctrl + c and close the command prompt.
On the other command prompt (where you started sqlservr.exe) press ctrl + c and then Y to stop SQL Server. Restart the SQL Server Windows service.

You should be able to log in using the new username and password.

Edit: If you have difficulties connecting once the server is started in minimal config mode you may need to stop other services to prevent anything opening a connection, particularly SSRS/SSIS etc. if they're running as local administrators.


help understanding galera consistency (certification-based replication)

But what happens in the case of losing a node that certified the commit but still fetching the data, for example when writing a big blob?

This is no different to losing any other node. If the node crashes, it will try to re-join the cluster next time it comes up. A donor node will be found, and preferably IST (incremental state transfer) or alternatively SST (state snapshot transfer) will bring the node to the right state.

  From my understanding the certification process is synchronous but the data transfer is asynchronous, meaning that the client/user may think his data is stored on all the nodes after running successfully a COMMIT but while in reality is happening in the backend is that data may be still synchronizing among the nodes and in case of a network outage and depending on the type of transaction the node could be in a conflict, correct?

You are right that the data transfer is asynchronous and there is a short time while the node state is not identical. This isn't exactly a conflict, just that the other nodes are behind. They will catch up soon enough.

  If this is the case, how could the cluster be configured to self-heal from possible conflicts or wondering if there is a way to achieve strong consistency even if it would impact the return time for every COMMIT since it should only be successful after the data been full transmitted on all nodes.

Yes, it does self-heal through ISTs and SSTs.

There are two ways to be sure that you're reading the most up-to-date version of the data: If you always write to only one node (e.g. because of using a DB proxy with a read-write splitter), then you can make sure to also read from that same node. The other option is to set the session variable wsrep_sync_wait=1 - see Achieving Read-After-Write Semantics With Galera.


why in explain in mysql row count is very high

Is the table InnoDB? For that the index statistics are gathered by "index dives" and are by definition imprecise.

EXPLAIN is not actually executing the query, but uses the estimates to plan it so it shows those imprecise numbers.

The important thing is that if it keeps numbers in the same order as reality (which, being a double in your case, it is), it works well enough for most cases.

Keeping exact numbers is too cumbersome and not even really possible in transactional environment.


Does the system process slow down my Database access?

One of the common disk usage process on a windows 7 machine under the System process is Superfetch.
You might want to look into disabling it disabling superfetch.

The reason why it's likely Superfetch is because you're seeing

Only reads
It's not your OS disk
You're running windows 7, not a server version
more slowdown as the amount of files increases

If that doesn't help Sysintenals' Process Explorer might help you get a better idea of what's going on. sysinternals


Insert column from other table / merge tables

If date & time are identical and work as a unique identifier in both tables:

SELECT
    t1.date,
    t1.time,
    COALESCE(t1.windspeed,t2.windspeed) as windspeed,
    COALESCE(t1.winddir,t2.winddir) as winder
FROM t1 INNER JOIN t2 ON t1.date = t2.date AND t1.time = t2.time

This is a query for create table:

CREATE TABLE tblName AS

SELECT
      t1.date,
      t1.time,
      COALESCE(t1.windspeed,t2.windspeed) as windspeed,
      COALESCE(t1.winddir,t2.winddir) as winder
FROM t1 INNER JOIN t2 ON t1.date = t2.date AND t1.time = t2.time

To work properly (fast), create combined indexes on both tables on (date, time).


Does MySQL still handle indexes in this way?

This is how MySQL 4.x did this and it used to severely aggravate me.
In fact, there was a formula I computed on how many such index maneuvers were needed
Table with 0 indexes and adding 1 index tooks 1 temp table
Table with 1 index   and adding 1 index tooks 3 temp tables
Table with 2 indexes and adding 1 index tooks 6 temp tables
Table with 3 indexes and adding 1 index tooks 10 temp tables (I had eyewitnessed this !!!)
.
.
.
Table with n indexes and adding 1 index took (n + 1) X (n + 2) / 2 temp tables
.
.
.
Table with 16 indexes and adding 1 index took 153 temp tables

Good News, MySQL 5.x does not do that !!!
If MySQL 5.x did this, I would be a PostgreSQL DBA today (No offense to PostgreSQL, it is an excellent RDBMS in its own right).
UPDATE
Oh my goodness, I read the post !!! That post came from me !!!
I never thought someone would dig this post up.
Please leave stuff like this dead and buried. Now I am having flashbacks !!!


What happens if you lose the connection during a huge insert?

For SQL databases that are ACID - A is for Atomic meaning all or none of a transaction will occur. When partial data is loaded and the connection stops, there is no choice in a ACID constraint but to drop all of the data.

For MySQL Innodb (default) is ACID but there is a MyISAM engine which isn't.


Check If Record Was Inserted Or Updated In Table

As many have commented, you want to isolate your trigger to fire and modify only when necessary.  This means your going to have to filter on just the record that was inserted.  You can do this with the inserted view.  As you will see from the sample code below, I've slightly modified your code to more easily see what's going on and isolate the update statement to only affect the inserted row.

--create the table
create table TwoNumericNames (rowID int IDENTITY, name nvarchar(500))

--insert a row
INSERT TwoNumericNames
VALUES (N'AA')

--note the row
select * from TwoNumericNames;
go

--create trigger
CREATE TRIGGER trgUpdateName
ON TwoNumericNames
FOR INSERT
AS
Begin

    UPDATE T
    SET T.[name] = CASE
                    WHEN I.[name] LIKE '%AA%' THEN 'Alpha Awe'
                    WHEN I.[name] LIKE '%RR%' THEN 'Raw Red'
                    WHEN I.[name] LIKE '%CC%' THEN 'Capt Cree'
                    WHEN I.[name] LIKE '%MM%' THEN 'My More'
                    WHEN I.[name] LIKE '%EE%' THEN 'Ed Ewe'
                    ELSE I.[name]
                END
    FROM TwoNumericNames T
    JOIN inserted I ON I.rowID = T.rowID

END
go

--insert another row
INSERT TwoNumericNames
VALUES (N'RR')

--note only the one record is updated
select * from TwoNumericNames


autovacuum challenges after upgrade to postgresql 9.6

I'd suggest you change your strategy.

  Specifically, one of our processes updates several columns one-at-a-time in such a table, and in the code log, I can see where it pauses and hangs, for up to 30 minutes, and then picks up later as if nothing happened, and updates many consecutive columns in the same with no such hang, within seconds.

One such update can easily double the size of your table, since each UPDATE is basically equivalent to one DELETE one INSERT, which leaves each deleted row occupying useless space until the table it is VACUUMed and that space can be reused for the next round of UPDATEs.

You can do:

Change the strategy and update as many columns as you can at once. Your process will be much quicker because the data will be accessed many less times.
Issue a VACUUM from your code after each UPDATE round, so that the unused spaced is available for the next UPDATE round.
Under some circumstances, doing the UPDATEs in batches of (let's say) 1.000, and committing after each batch might ease the sitution.

Any combination of the three techniques will probably ease the updates.


You can simplify to:

CREATE OR REPLACE FUNCTION pg_temp.insert_stuff(_a1 text, _a2 text, _a3 int)
  RETURNS TABLE (id_i int, id_j int) AS
$func$
BEGIN
   IF EXISTS (SELECT FROM table_1 WHERE a12 = _a1||a2) THEN  -- see below
      RETURN QUERY
      SELECT -1, NULL;
      -- RAISE EXCEPTION 'Some informative msg here'  -- alternative?
   ELSE
      RETURN QUERY
      SELECT insert_here(_a1, _a2)
           , insert_there(_a3, _a2);
   END IF;
END
$func$  LANGUAGE plpgsql;

Note the clearer (and faster) IF EXISTS. See:

PL/pgSQL checking if a row exists

Either variant might fail "unexpectedly" if either input parameter or the column a12 can be NULL! You would have to define desired behavior.

Subtle difference: Since I replaced your CTE construct with a simple SELECT, the function always returns at least one now, even if either nested function (insert_here() or insert_there()) returns no row. Your version would return nothing in this case, which may be a sneaky bug if either of the nested functions can return no row.

I am assuming Postgres 10 or later (you didn't declare). Else, depending on the undeclared nature of the nested functions, you might need to consider:

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


PL/pgsql: use cursor to update next record based on a condition from current record

What you want is something this this with lag() and lead()

UPDATE mytble
SET ownby_ctc = false
FROM (
    SELECT
      id_y_q,
      lead(ownby_ctc) OVER () AS lead_ownby_ctc,
      lead(ownby_ftc) OVER () AS lead_ownby_ctc,
    FROM mytbl
    ORDER BY id, year, qrtr;
) AS t2
WHERE t2.id_y_q = id_y_q
  AND t2.id = id
  AND ownby_ctc = 1
  AND ownby_ftc = 0

I'm not sure your copy works and you don't provide an environment but something like this is what you're after.


Using RANK function, how can I return the rank of one record vs the entire table?

Put the window function in a derived table and move the filter outside of the derived table. That way the RANK() function will be calculated before the filter is applied instead of after. Here is one implementation:

CREATE TABLE #btcProfile (
profile_name VARCHAR(20) NOT NULL,
TRUST INT NOT NULL
);

INSERT INTO #btcProfile
VALUES
('DeaDTerra', 390),
('OgNasty', 322),
('Blazed', 303),
('monbux', 260),
('Tomatocage',230),
('Stunna', 220),
('philipma1957', 216),
('John (John K.)', 180),
('CanaryInTheMine', 173),
('Mitchell', 165);

SELECT profile_name, [Rank]
FROM
(
    select profile_name, trust, RANK() over (order by trust desc) AS [Rank]
    from #btcProfile
) t
where profile_name='Blazed';

Be sure that the RANK() function handles ties how you want it to. It wasn't clear how the TOP 25 related to your question. If you let me know I can integrate that into my answer.


How can I get my linked server working using Windows authentication?

Every machine in the chain from your desktop to the server you are calling has to be Kerberos enabled for the trust to advance past the first hop.  So, yes the server needs to trust the user for delegation.

The "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'" almost always indicates a delegation problem.

Your Windows Account must have access to both ServerA and ServerB.
You must not have the setting "Account is sensitive and cannot be delegated."
Both ServerA and ServerB must have their own SPN registered.
The servers must be TCP/IP or named pipes connected.

The SQL Server Books Online article that offers some more detail is "Configuring Linked Servers for Delegation":
http://msdn.microsoft.com/en-us/library/ms189580(v=sql.105).aspx


Informix DB export to Microsoft SQL Server 2016

You might try and check if you can get data out of the Informix database via the linked server.
If the direct query fails, you might have some luck with the use of OPENQUERY

SELECT * FROM OPENQUERY ([name of linked server], 'Query text')

If this works, maybe a select into gets the job done.


Simple Syntax Error - CASE WHEN DISTINCT(Column)

Answer looks like this.  Thanks Randolph!

SELECT DISTINCT
    CASE WHEN Response_Master_Incident.Battalion = '' THEN 'Unknown'
    ELSE Response_Master_Incident.Battalion END AS 'Zone'