Generate & Email report from Oracle Forms 11g

You can download a file from filesystem like this:

FUNCTION GetFile(FolderName IN VARCHAR2, FileName IN VARCHAR2) RETURN BLOB IS

    xbfile   BFILE;
    xblob    BLOB;
    destOffset INTEGER := 1;
    srcOffset INTEGER := 1;

BEGIN
    xbfile := BFILENAME (FolderName, FileName);
    DBMS_LOB.OPEN (xbfile);
    DBMS_LOB.CREATETEMPORARY(xblob, TRUE, DBMS_LOB.SESSION);
    DBMS_LOB.LOADBLOBFROMFILE(
        dest_lob => xblob,
        src_bfile => xbfile,
        amount => DBMS_LOB.GETLENGTH(xbfile),
        dest_offset => destOffset,
        src_offset => srcOffset);

    DBMS_LOB.CLOSE(xbfile);

    RETURN xblob;

END GetFile;

In order to access filesystem you must create a DIRECTORY (if not already exists):

CREATE OR REPLACE DIRECTORY PDF_REPORTS AS 'C:\Downloads';

In my application I use this generic package to send mails:

CREATE OR REPLACE TYPE VARCHAR_TABLE_TYPE AS TABLE OF VARCHAR2(1000);
/

CREATE OR REPLACE PACKAGE Mailing AS

PRIORITY_HIGH           CONSTANT INTEGER := 1;
PRIORITY_NORMAL         CONSTANT INTEGER := 3;
PRIORITY_LOW            CONSTANT INTEGER := 5;

MIME_MIXED              CONSTANT VARCHAR2(50) := 'multipart/mixed;';
MIME_TEXT               CONSTANT VARCHAR2(50) := 'text/plain;';
MIME_HTML               CONSTANT VARCHAR2(50) := 'text/html;';

PROCEDURE SendMail(
    Subject IN VARCHAR2,
    Message IN OUT CLOB,
    ToMail IN VARCHAR_TABLE_TYPE,
    FromMail IN VARCHAR2, FromName IN VARCHAR2,
    PRIORITY IN INTEGER DEFAULT PRIORITY_NORMAL,
    FileName IN VARCHAR2 DEFAULT NULL,
    MimeType IN VARCHAR2 DEFAULT MIME_TEXT, --> determines the MIME-Type of binary attachment "BinAttachment"
    TxtAttachment IN CLOB DEFAULT NULL,
    BinAttachment IN BLOB DEFAULT NULL);

END Mailing;
/

CREATE OR REPLACE PACKAGE BODY Mailing AS

PROCEDURE SendMail(
    Subject IN VARCHAR2,
    Message IN OUT CLOB,
    ToMail IN VARCHAR_TABLE_TYPE,
    FromMail IN VARCHAR2, FromName IN VARCHAR2,
    PRIORITY IN T_MAIL_PRIORITY DEFAULT PRIORITY_NORMAL,
    FileName IN VARCHAR2 DEFAULT NULL,
    MimeType IN VARCHAR2 DEFAULT MIME_TEXT,
    TxtAttachment IN CLOB DEFAULT NULL,
    BinAttachment IN BLOB DEFAULT NULL) IS

    MIME_BOUNDARY           CONSTANT VARCHAR2(50) := '====Multipart.Boundary.689464861147414354====';

    con UTL_SMTP.CONNECTION;
    ret UTL_SMTP.REPLY;
    Charset VARCHAR2(20);
    Footer VARCHAR2(1000);
    Recipients VARCHAR2(1000);

    LobLen PLS_INTEGER;
    amount PLS_INTEGER := 8192;
    BUFFER VARCHAR2(16384);
    OFFSET PLS_INTEGER := 1;
    isHTML BOOLEAN := REGEXP_LIKE(DBMS_LOB.SUBSTR(Message, 1000, 1), '(< *html)|(< *body)', 'i');

BEGIN

    SELECT UTL_I18N.MAP_CHARSET(VALUE)
    INTO Charset
    FROM NLS_DATABASE_PARAMETERS
    WHERE parameter = 'NLS_CHARACTERSET';

    -- setup mail header
    con := UTL_SMTP.OPEN_CONNECTION(SMTP_SERVER, SMTP_PORT);
    ret := UTL_SMTP.HELO(con, SYS_CONTEXT('USERENV', 'DB_DOMAIN'));
    ret := UTL_SMTP.MAIL(con, FromMail);
    FOR i IN ToMail.FIRST..ToMail.LAST LOOP
        Recipients := Recipients ||ToMail(i)||',';
        ret := UTL_SMTP.RCPT(con, ToMail(i));
    END LOOP;
    ret := UTL_SMTP.OPEN_DATA(con);

    IF REGEXP_LIKE(FromName, '^[ -~]+$') THEN
        UTL_SMTP.WRITE_DATA(con, 'From: "'||FromName||'" <'||FromMail||'>'||UTL_TCP.CRLF);
    ELSE
        UTL_SMTP.WRITE_DATA(con, 'From: =?UTF-8?B?'|| UTL_ENCODE.TEXT_ENCODE(FromName, NULL, UTL_ENCODE.BASE64) ||'?= <'||FromMail||'>'||UTL_TCP.CRLF);
    END IF;
    UTL_SMTP.WRITE_DATA(con, 'To: '||REGEXP_REPLACE(Recipients, ',$')||UTL_TCP.CRLF);
    IF REGEXP_LIKE(Subject, '^[ -~]+$') THEN
        UTL_SMTP.WRITE_DATA(con, 'Subject: '||Subject||UTL_TCP.CRLF);
    ELSE
        UTL_SMTP.WRITE_DATA(con, 'Subject: =?UTF-8?B?'|| REPLACE(REPLACE(UTL_ENCODE.TEXT_ENCODE(Subject, NULL, UTL_ENCODE.BASE64), CHR(13), NULL), CHR(10), NULL) ||'?='||UTL_TCP.CRLF);
    END IF;
    UTL_SMTP.WRITE_DATA(con, 'Date: '||TO_CHAR(CURRENT_TIMESTAMP, 'Dy, DD Mon YYYY hh24:mi:ss TZHTZM', 'NLS_DATE_LANGUAGE = American')||UTL_TCP.CRLF);
    UTL_SMTP.WRITE_DATA(con, 'X-Priority: '||PRIORITY||UTL_TCP.CRLF);

    IF FileName IS NOT NULL THEN
        UTL_SMTP.WRITE_DATA(con, 'Mime-Version: 1.0' || UTL_TCP.CRLF);
        UTL_SMTP.WRITE_DATA(con, 'Content-Type: '||MIME_MIXED||' boundary="'||MIME_BOUNDARY||'"' || UTL_TCP.CRLF);
        UTL_SMTP.WRITE_DATA(con, 'This is a multipart message in MIME format.' || UTL_TCP.CRLF);
        UTL_SMTP.WRITE_DATA(con, '--'||MIME_BOUNDARY || UTL_TCP.CRLF);
    END IF;

    -- Append common footer to mail
    Footer := 'Message from '||SYS_CONTEXT('USERENV', 'DB_NAME')||' sent at '||TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss');
    IF isHTML THEN
        UTL_SMTP.WRITE_DATA(con, 'Content-type: '||MIME_HTML||' charset='||Charset || UTL_TCP.CRLF);
        Message := REPLACE(message, '', '
'||Footer||'
');
    ELSE
        UTL_SMTP.WRITE_DATA(con, 'Content-type: '||MIME_TEXT||' charset='||Charset || UTL_TCP.CRLF);
    END IF;

    -- Mail Body
    UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF);
    LobLen := DBMS_LOB.GETLENGTH(Message);
    LOOP
        EXIT WHEN OFFSET > LobLen;
        DBMS_LOB.READ(Message, amount, OFFSET, BUFFER);
        UTL_SMTP.WRITE_RAW_DATA(con, UTL_RAW.CAST_TO_RAW(BUFFER));
        OFFSET := OFFSET + amount;
    END LOOP;
    UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF);
    IF NOT isHTML THEN
        UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF || UTL_TCP.CRLF);
        UTL_SMTP.WRITE_DATA(con, Footer);
        UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF);
    END IF;

    IF FileName IS NOT NULL THEN
        -- Mail Attachment
        UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF);
        UTL_SMTP.WRITE_DATA(con, '--'||MIME_BOUNDARY || UTL_TCP.CRLF);
        UTL_SMTP.WRITE_DATA(con, 'Content-Type: '||MimeType||' name="'||Filename||'"'|| UTL_TCP.CRLF);
        UTL_SMTP.WRITE_DATA(con, 'Content-Disposition: attachment; filename="'||Filename||'"'|| UTL_TCP.CRLF);

        OFFSET := 1;
        IF TxtAttachment IS NOT NULL THEN
            UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF);
            LobLen := DBMS_LOB.GETLENGTH(TxtAttachment);
            LOOP
                EXIT WHEN OFFSET > LobLen;
                DBMS_LOB.READ(TxtAttachment, amount, OFFSET, BUFFER);
                UTL_SMTP.WRITE_RAW_DATA(con, UTL_RAW.CAST_TO_RAW(BUFFER));
                OFFSET := OFFSET + amount;
            END LOOP;
        ELSIF BinAttachment IS NOT NULL THEN
            amount := 48; -- must be a whole multiple of 3
            UTL_SMTP.write_data(con, 'Content-Transfer-Encoding: base64' || UTL_TCP.crlf);
            UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF);
            LobLen := DBMS_LOB.GETLENGTH(BinAttachment);
            LOOP
                EXIT WHEN OFFSET > LobLen;
                DBMS_LOB.READ(BinAttachment, amount, OFFSET, BUFFER);
                UTL_SMTP.WRITE_RAW_DATA(con, UTL_ENCODE.BASE64_ENCODE(BUFFER));
                OFFSET := OFFSET + amount;
            END LOOP;
        END IF;
        UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF);
        UTL_SMTP.WRITE_DATA(con, '--'||MIME_BOUNDARY||'--' || UTL_TCP.CRLF);
    END IF;

    -- finish mail
    ret := UTL_SMTP.CLOSE_DATA(con);
    ret := UTL_SMTP.QUIT(con);

EXCEPTION
    WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
        UTL_SMTP.QUIT(con);
END SendMail;

END Mailing;
/

It provides some extras:

You can optionally attach a file (txt or binary, but only one file is supported)
From and Subject may contain Non-ASCII characters like öäü (of course the mail Body may also)
Multiple recipients
Automatically use correct character set from database
Automatically detects plain-text or HTML mail body
Common footer to indicate mail

Then you can call the function like

DECLARE
   pdf BLOB;
   Message CLOB := 'Some message text';
BEGIN
   pdf := GetFile('PDF_REPORTS','abc.pdf');

Mailing.SendMail(
    Subject => 'The Subject',
    Message => Message,
    ToMail => VARCHAR_TABLE_TYPE('name@domain.net'),
    FromMail => 'oracle@domain.net',
    FromName => 'DB User: '||USER,
    FileName => 'abc.pdf',
    BinAttachment => pdf,
    MimeType => 'application/pdf' );

END;


Why doesn't MySQL coerce the collation to the column-specified, when comparing to a literal?

Why does the above comparison succeed?

Because MySQL is being nice and auto-correcting a user error, but only because it can.

You are not wrong that:

  the column CCV has higher priority

and:

  the utf8_bin collation can't handle utf8mb4 character sets.

However, you are not seeing what you think you are seeing in your test because you are not testing with data that requires the "mb4" version of the "utf8" collations. You are testing with a "safe" value that fits into the non-supplementary character-capable utf8 charset. In this situation, MySQL simply uses the charset of the column — utf8 — and the utf8_bin collation is just fine.

The world looks a little different when you use a 4 byte character (i.e. a supplementary character). In this case, the character cannot exist in the utf8 charset (because the utf8 charset can only handle BMP characters / the first 65,536 code points / U+0000 through U+FFFF / characters that are 1 to 3 bytes each). And so, since the charset of the string literal cannot be changed to the one that does work with the column's collation — utf8_bin — you would get an error stating:

  Illegal mix of collations (utf8_bin,IMPLICIT) and (utf8mb4_0900_ai_ci,COERCIBLE) for operation '<'

I tested with code point U+1F369 (i.e. the donut emoji: "🍩"):

CREATE TABLE test_table (
  utf8_col VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin
);

INSERT INTO test_table VALUES('ã'); # any utf8 character from U+0000 to U+FFFF

# Test against any utf8 character from U+0000 to U+FFFF in _utf8mb4
SELECT utf8_col,
       CHARSET(utf8_col) AS 'charset',
       COLLATION(utf8_col) AS 'collation',
       CONCAT(utf8_col, _utf8mb4'ñ') AS 'concat1',
       CHARSET(CONCAT(utf8_col, _utf8mb4'ñ')) AS 'charset_concat1'
FROM test_table;

# Test against any utf8 character from U+10000 to U+10FFFF in _utf8mb4
SELECT utf8_col,
       CHARSET(utf8_col) AS 'charset',
       COLLATION(utf8_col) AS 'collation',
       CONCAT(utf8_col, _utf8mb4'') AS 'concat2',
       CHARSET(CONCAT(utf8_col, _utf8mb4'')) AS 'charset_concat2'
FROM test_table;
# FAIL

SELECT utf8_col < _utf8mb4'ñ' FROM test_table; # Success

SELECT utf8_col < _utf8mb4'' FROM test_table; # FAIL

See the above example code in action on dbfiddle.uk

The result set from the first SELECT is:

utf8_col    charset    collation    concat1    charset_concat1
ã           utf8       utf8_bin     ãñ         utf8

which shows that the utf8mb4 string was coerced into utf8, and everyone was happy. Was happy, until Mr Donut showed up in the second SELECT statement (you would think that donuts would make everything better, though, wouldn't you? I generally do 😸).


How to Install/Configure Oracle Instant Client on Ubuntu

Updated Per Feedback
I never really found a good answer to this that didn't involve a lot of manual steps, so I made a solution.

You can download a script from my oracle_instant_client_for_ubuntu GitHub repo which will convert Oracle rpms into Ubuntu compatible packages and then install them for you.

It now supports multiple versions of the instant client.  Feel free to log issues via GitHub.

Currently, this project only supports 64-bit systems.


Why is adding a NOT NULL column with a default constraint instantaneous?

Yes, adding a column with NOT NULL and a default doesn't actually write the values to all the rows at the time of the alter, so it is no longer a size-of-data operation. When you select from the table, the columns are actually materialized from sys.system_internals_partition_columns, which prevents all the values from having to be written (until they are changed). Note that this doesn't work for all data types and requires Enterprise Edition.

Remus Rusanu explains this in more detail here:

Online non-NULL with values column add in SQL Server 2012

Also, for an ALTER at least, we still can't show you a plan because SQL Server doesn't produce one, but to see I/O, you can use SQL Sentry Plan Explorer.* This screen shot shows adding a column, c5, "online" as described above, and then another column, c6, "offline" because LOB types are not supported. You can see the I/O is mostly expressed as reads rather than writes, but what's more telling is the (invalid!) UPDATE associated with the offline alter.

If you don't have Enterprise Edition, both statements will have the secondary UPDATE attached (and the associated reads). (And if you use the free version of Plan Explorer, which doesn't get the full query call stack, you won't see the above - you will just see an empty statement tree. A paid version is required to see the full query call stack.)

Note that SQL Server will produce an estimated plan, but it's not very useful. At all. And the estimated plan for an online alter is identical to the estimated plan for an offline alter.

*Disclaimer: I work for SQL Sentry.


Change mysql storage engine from Innodb to MyIsam: Max locks exceeded error

The solution is simply to edit my.cnf, and add or edit the following line:

innodb_buffer_pool_size = 16M

To make it work, I set the size in bytes, so if I wanted 256 megabytes for my buffer pool, I would write 268435456. It would look like like this thus:

innodb_buffer_pool_size = 268435456


Should I Denormalize Tables To Avoid Joins?

I would say that having FirstName and LastName in the User table is still normalized. Having the data in different tables seems excessive, and in practice you'll have a 1-1 relation between the two.

If you expect a lot of read traffic on the User table, add appropriate indexes to that table instead.


Granting access to the Job Activity Monitor

In order to give some privileged users access to xp_sqlagent_enum_jobs we did the following.

Create a Database Role in master named something like SQLAgentJobManager.
Granted that role EXECUTE rights on xp_sqlagent_enum_jobs.

Then via Logins or a Domain group, granted users a membership in the SQLAgentJobManager role in master.

EDIT: The way we implement this permission is through Domain Group login(s) such as (SQLAgentJobManagerGroup). Then add the group login to master's SQLAgentJobManager role (for running xp_sqlagent_enum_jobs) and to msdb's SQLAgentReaderRole or SQLAgentUserRole.

After that we always add or remove logins from the SQLAgentJobManagerGroup to control the group membership for those rights.


Postgresql as docker container not starting with initial data from mapped volume

Instead of moving the actual data folder around I used pg_dump and pg_restore within the docker containers per suggestion on the docker forums. This did the trick


Copying table subset from one database to another with same schema

It's not very clear what your goal is.
 If you want to copy data and ensure its' referential integrity, then you'll need to modify your SELECT so it picks up all dependent rows (check recursive CTE for details).

If want to just copy data , and don't care about referential integrity, you can either drop or disable FK constraints during import.  Since FK in Postgres enforced with triggers, you can ALTER table disable trigger all , import, and enable them .
Another way to disable all triggers would be SET session_replication_role = replica;, import data, then reset to default SET session_replication_role = DEFAULT;


After an index is created on a table, it can be used immediately by queries. But that doesn't mean the index is going to be used or that the index is going to improve performance immediately. Validate that the index is being used by the query(ies) you were targeting and compare performance differences.

The first time the index is used, the database may have to read the index pages from disk into memory, so it might take a little longer than on subsequent runs when the index may already be in memory.


Database stalls on innodb_buffer_pool_wait_free

what is the relationship between this LRU parameter and the database stalls I am experiencing?

The relationship is quite straightforward. The background flush thread every second will scan innodb_lru_scan_depth * innodb_buffer_pool_instances LRU list entries in an attempt to find dirty pages that it can write out to disk. Flushed pages become free and add to innodb_buffer_pool_pages_free.

If the DML thrown at your database produces dirty pages faster than the flush thread can write them out, at some point innodb_buffer_pool_pages_free will go down to zero, and the next attempt to read a page to the buffer pool will cause another innodb_buffer_pool_wait_free while the server flushes dirty pages to disk synchronously.

Ideally you would want the background flushing thread to write dirty pages to disk at the same rate (on average) as they are dirtied by the DML activities. You do that by increasing innodb_lru_scan_depth, which increases the probability of dirty pages being written to disk before the server needs more free pages.

An alternative, as mentioned in another answer, is to enable adaptive flushing and set the appropriate innodb_max_dirty_pages_pct and/or innodb_max_dirty_pages_pct_lwm values.

It should be noted that overly aggressive background flushing could saturate the I/O bandwidth on its own, which would be just as bad as excessive innodb_buffer_pool_wait_free events. At some point you might find that you will have to allocate more memory for buffer pools to satisfy your workload demands, or live with occasional free page waits.


What you are looking for is a concept called dynamic sql. To do this, you are going to have to use prepared statements. Lucky for you, MySQL permits prepared statements in stored procedures, but not stored functions or triggers.

Also, lucky for you, MySQL permits prepared statements on CREATE TABLE commands.

So you are going to have to prepare the CREATE TABLE something like this:

SET @SQL = CONCAT('CREATE TABLE ',CONCAT('sabretooth_archive.',table_to_create),' LIKE ',CONCAT(database_name,'.',table_to_create));

PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


{1} Partitioned Outer Join: Definition

... "Such a join extends the conventional outer join syntax by applying the outer join to each logical partition defined in a query. Oracle logically partitions the rows in your query based on the expression you specify in the PARTITION BY clause. The result of a partitioned outer join is a UNION of the outer joins of each of the partitions in the logically partitioned table with the table on the other side of the join." (documentation)

{2} Simple Example

"Data is normally stored in sparse form. That is, if no value exists for a given combination of dimension values, no row exists in the fact table. However, you may want to view the data in dense form, with rows for all combination of dimension values displayed even when no fact data exist for them."

... "For example, if a product did not sell during a particular time period, you may still want to see the product for that time period with zero sales value next to it." (quotes from the documentation)

Test tables and data (INSERTs)

-- Oracle 12c
create table sales (
  date_ date
, location_ varchar2( 16 )
, qty_ number
);

create table locations (
  name varchar2( 16 )
);

-- dates for locations are "gappy":
-- none of the locations has entries for all 3 dates
-- ( date range: 2019-01-15 - 2019-01-17 )
insert into sales ( date_, location_, qty_ )
  values ( date '2019-01-17', 'London', 11 ) ;
insert into sales ( date_, location_, qty_ )
  values ( date '2019-01-15', 'London', 10 ) ;
insert into sales ( date_, location_, qty_ )
  values ( date '2019-01-16', 'Paris', 20 ) ;
insert into sales ( date_, location_, qty_ )
  values ( date '2019-01-17', 'Boston', 31 ) ;
insert into sales ( date_, location_, qty_ )
  values ( date '2019-01-16', 'Boston', 30 ) ;

-- locations
insert into locations ( name ) values ( 'London' );
insert into locations ( name ) values ( 'Paris' );
insert into locations ( name ) values ( 'Boston' );

Required output

date_       location_  qty_
2019-01-15  London     10
2019-01-15  Paris       0    -- not INSERTed!
2019-01-15  Boston      0    -- not INSERTed!
2019-01-16  London      0    -- not INSERTed!
2019-01-16  Paris      20
2019-01-16  Boston     30
2019-01-17  London     11
2019-01-17  Paris       0    -- not INSERTed!
2019-01-17  Boston     31

Query (partitioned outer join)

select S.date_, S.qty_, L.name
from sales S partition by ( date_ )
  right join locations L on S.location_ = L.name
;

-- result
DATE_      QTY_  NAME
15-JAN-19  NULL  Boston
15-JAN-19  10    London
15-JAN-19  NULL  Paris
16-JAN-19  30    Boston
16-JAN-19  NULL  London
16-JAN-19  20    Paris
17-JAN-19  31    Boston
17-JAN-19  11    London
17-JAN-19  NULL  Paris

Query (version 2, same join)

-- same as above, using NVL(), column aliases, and ORDER BY ...
select S.date_, nvl( S.qty_, 0 ) as sold, L.name as location
from sales S partition by ( date_ )
  right join locations L on S.location_ = L.name
order by S.date_, L.name
;

DATE_           SOLD LOCATION
--------- ---------- ----------------
15-JAN-19          0 Boston
15-JAN-19         10 London
15-JAN-19          0 Paris
16-JAN-19         30 Boston
16-JAN-19          0 London
16-JAN-19         20 Paris
17-JAN-19         31 Boston
17-JAN-19         11 London
17-JAN-19          0 Paris

Dbfiddle here (Oracle 18c)

{3} Equivalence

The following query does roughly the same job as the PARTITION BY ( date_ ) outer join.  We are using a combination of a CROSS JOIN (inner SELECT) and LEFT OUTER JOIN.
(Conversion of NULLs to 0s omitted)

Oracle

select SL.*, S.qty_
from
(
  select *
  from (
    select unique date_ from sales
  ) , (
    select unique name from locations
  )
) SL left join (
  select date_, location_, qty_ from sales
) S on SL.name = S.location_ and SL.date_ = S.date_
order by SL.date_, SL.name
;

DATE_      NAME    QTY_
15-JAN-19  Boston  NULL
15-JAN-19  London  10
15-JAN-19  Paris   NULL
16-JAN-19  Boston  30
16-JAN-19  London  NULL
16-JAN-19  Paris   20
17-JAN-19  Boston  31
17-JAN-19  London  11
17-JAN-19  Paris   NULL

PostgreSQL 10 ( dbfiddle )

-- DDL and INSERTs
create table sales (
  date_ date
, location_ varchar( 16 )
, qty_ number
);

create table locations (
  name varchar( 16 )
);

insert into sales ( date_, location_, qty_ ) values
  ( '2019-01-17', 'London', 11 )
, ( '2019-01-15', 'London', 10 )
, ( '2019-01-16', 'Paris', 20 )
, ( '2019-01-17', 'Boston', 31 )
, ( '2019-01-16', 'Boston', 30 )

insert into locations ( name ) values
( 'London' ), ( 'Paris' ), ( 'Boston' );

Query (Postgres)

-- SL: all date_ <-> location combinations
-- S: all location_ and qty_ values of table sales
select SL.*, S.qty_
from
(
  select *
  from (
    select distinct date_ from sales
  ) S_ cross join (
    select distinct name from locations
  ) L_
) SL left join (
  select date_, location_, qty_ from sales
) S on SL.name = S.location_ and SL.date_ = S.date_
order by SL.date_, SL.name
;

date_        name    qty_
2019-01-15   Boston
2019-01-15   London  10
2019-01-15   Paris
2019-01-16   Boston  30
2019-01-16   London
2019-01-16   Paris   20
2019-01-17   Boston  31
2019-01-17   London  11
2019-01-17   Paris


how to retrieve MAX ID from inner join SQL Query?

I adjusted your current logic to the following, since it was broken in multiple ways (-60 meant 61 rows, not 60, and your subqueries with MAX in them don't work):

ALTER Procedure [dbo].[searchLog]
@searchValue varchar(150),
@StartLoc INT = NULL,
@EndLoc INT = NULL
AS
Declare @myquote varchar(10)
Declare @wildcard varchar(5)
set @myquote ='"'
set @wildcard ='*'
set @searchValue = @myquote + @searchValue + @wildcard +@myquote

SET NOCOUNT ON;

     IF @StartLoc IS NULL BEGIN
          SET @StartLoc = (SELECT MAX(o.ID)
                           FROM dbo.output AS o
                           LEFT OUTER JOIN dbo.jobs AS j
                                ON o.Job = j.ID
                           LEFT OUTER JOIN dbo.scripts AS s
                                ON j.Script = s.ID
                           WHERE CONTAINS ((o.Name, o.Logs),  @searchValue)
                                 AND (o.DateRan < GETDATE())) - 59;
     END;

     IF @Endloc IS NULL BEGIN
           SELECT @EndLoc = (SELECT MAX(o.ID)
                           FROM dbo.output AS o
                           LEFT OUTER JOIN dbo.jobs AS j
                                ON o.Job = j.ID
                           LEFT OUTER JOIN dbo.scripts AS s
                                ON j.Script = s.ID
                           WHERE CONTAINS ((o.Name, o.Logs),  @searchValue)
                                 AND (o.DateRan < GETDATE()));
     END;
BEGIN

SET NOCOUNT ON
SELECT   o.ID, o.Name, o.DateRan, s.Name AS Job, o.Start, o.[End], o.Status, o.Logs
FROM            dbo.output AS o LEFT OUTER JOIN
                         dbo.jobs AS j ON o.Job = j.ID LEFT OUTER JOIN
                         dbo.scripts AS s ON j.Script = s.ID
WHERE CONTAINS ((o.Name, o.Logs),  @searchValue)
AND
(o.DateRan < GETDATE())
AND
(o.ID Between @StartLoc and @EndLoc)
     ORDER BY o.ID DESC
END
RETURN

I did not adjust your logic that assumes all matching IDs will be contiguous.

Then I rewrote this logic using OFFSET/FETCH, as I suggested in a comment:

CREATE PROCEDURE dbo.searchLog2
  @searchValue varchar(150),
  @startLoc    int = NULL,
  @endLoc      int = NULL
AS
BEGIN
  SET NOCOUNT ON;

  SET @searchValue = '"' + @searchValue + '*"';

  ;WITH results AS
  (
    SELECT o.ID, o.Name, o.DateRan, s.Name AS Job,
           o.Start, o.[End], o.Status, o.Logs
    FROM dbo.output AS o
    LEFT OUTER JOIN dbo.jobs AS j
      ON o.Job = j.ID
    LEFT OUTER JOIN dbo.scripts AS s
      ON j.Script = s.ID
    WHERE CONTAINS ((o.Name, o.Logs), @searchValue)
    AND o.DateRan < GETDATE()
    AND o.ID >= COALESCE(@startLoc, 0)          -- assuming unsigned
    AND o.ID <= COALESCE(@endLoc,   2147483647) -- assuming int
 )
  SELECT * FROM results
  ORDER BY ID DESC
  OFFSET 0 ROWS FETCH NEXT 60 ROWS ONLY;
END
GO

If you compare the plans when all three parameters are supplied, they're pretty close:

EXEC dbo.searchLog  @searchValue = 'name', @startLoc = 100, @endLoc = 1000;
EXEC dbo.searchLog2 @searchValue = 'name', @startLoc = 100, @endLoc = 1000;

Yours:

Mine:

Comparison (don't take too much stock in estimated cost %, but it's a hint):

So, pretty similar, how acceptable the tiny hit of the TOP operator reflects in the estimated cost depends on how often you call it with both parameters and how much value you place on readability.

But once you remove either or both Loc parameters, your extra IF conditional queries add up by also having to perform this work, either once or twice:

EXEC dbo.searchLog  @searchValue = 'name', @startLoc = 100;
EXEC dbo.searchLog2 @searchValue = 'name', @startLoc = 100;

EXEC dbo.searchLog  @searchValue = 'name';
EXEC dbo.searchLog2 @searchValue = 'name';

You can see the additional work reflected in statistics I/O and number of rows read/returned by the entire batch (even when you return the wrong number of rows if your matching IDs are too far apart because subtracting 60 from an ID only works if there are no gaps in your IDs and there are no gaps between matching rows).


Can I tell Postgres that a new constraint is valid without actually running the check?

You can safely leave the check constraint invalid, if that doesn't bother you. I cannot think of any place where a check constraint is used for query planning, and it will be enforced for data modifications.

It should be safe to set convalidated to TRUE in pg_constraint in this case, but since you get no actual benefit from it, why do it?


I found a workaround with

select distinct orders.client_po, orders.order_code, to_char(orders.created_at, 'DD-Mon-YYYY') , to_char(carts.checkout_at, 'DD-Mon-YYYY') from orders
left outer join carts on carts.code = orders.cart_id


How to get the nearest date to a record date in sql?

Inner sub-query returns the nearest date.

SELECT
    ta.ID,
    ta.RequestNumber,
    tb.Serial,
    tb.dt
FROM
    ta
JOIN
    tb
    ON tb.RequestNumber = ta.RequestNumber
    AND tb.dt = (SELECT TOP 1
                     dt
                 FROM
                     tb
                 WHERE
                     RequestNumber = ta.RequestNumber
                     AND dt >= ta.dt
                 ORDER BY
                     RequestNumber,
                     dt ASC);

Or you cans use a DENSE_RANK to flag rows by RequestNumber + Date and get the rank = 1.

SELECT
    ta.ID,
    ta.RequestNumber,
    tb.Serial,
    tb.dt
FROM
    ta
CROSS APPLY (
             SELECT RequestNumber, Serial, dt,
                    DENSE_RANK() OVER (PARTITION BY RequestNumber
                                 ORDER BY RequestNumber, dt) rn
             FROM tb
             WHERE tb.RequestNumber = ta.RequestNumber
                   AND tb.dt >= ta.dt
            ) tb
WHERE rn = 1;

Both queries return the same result:

ID | RequestNumber | Serial | dt
-: | ------------: | -----: | :------------------
 1 |             1 |      1 | 27/09/2017 00:00:00
 1 |             1 |      2 | 27/09/2017 00:00:00
 2 |             1 |      6 | 03/06/2018 00:00:00
 2 |             1 |      7 | 03/06/2018 00:00:00
 2 |             1 |      8 | 03/06/2018 00:00:00

db<>fiddle here


Query to send mail to multiple users with their corresponding tickets

Below is how you do it ...

Create table and add processed column

    create table dbo.Tickets (Ticket bigint, [Owner] varchar(50))
go
insert into dbo.Tickets
select 14675, 'John'
union all
select 38759, 'John'
union all
select 45879,'Catheline'
union all
select 23980, 'Mark'
union all
select 16754, 'John'

--- add a processed column so that next time the db mail job runs, it wont send out dupe emails
alter table dbo.Tickets add processed bit not null default(0)
go

Use below query

-- query to get comma seperated values
select [Owner], Tickets = stuff ((select  N', '+ convert(nvarchar(max),innerT.Ticket)
                                    from dbo.Tickets innerT
                                    where innerT.[Owner] = outerT.[Owner]
                                    group by innerT.Ticket -- get rid of dupes
                                    order by innerT.Ticket -- just in case you want order
                                FOR XML PATH, TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 2, N'')
from dbo.Tickets outerT
group by [Owner]

Use above query to send HTML email using database mail.
Update the dbo.Tickets table and set processed = 1 for the owners that you emailed the tickets.

Note: you can use case statements based on the owners and email them seperately.

This is a pseudo code that will help you 80% in what you want to achieve.


Getting records for yesterday, but per hour

Use date_part to get the hour part of your timestamp, then add it to your GROUP BY clause.

 select
    camera_id,
    count(*) as snapshot_count,
    date_part('hour', created_at) as hr
 from snapshots
 where created_at >= TIMESTAMP 'yesterday' AND created_at < TIMESTAMP 'today'
 group by camera_id, hr

I generated an example table:

CREATE TABLE ex.snapshots
(
  camera_id integer NOT NULL,
  created_at timestamp with time zone NOT NULL,
)

and populated it with some test data:

INSERT INTO ex.snapshots (camera_id,created_at) (
    SELECT
        (random() * 6)::integer,
        now() - (((random() * 2000)::integer)::text || ' minutes')::interval
        FROM generate_series(1,20000)
    );

After adding an ordering clause, here's the first few rows of output:

  camera_id | snapshot_count | hr
 -----------+----------------+----
          0 |             39 |  0
          0 |             46 |  1
          0 |             59 |  2
          0 |             46 |  3
          0 |             49 |  4
          0 |             48 |  5
          0 |             56 |  6
          0 |             43 |  7


MySQL 8.021 = 100% CPU when many UPDATE queries to 1 table

As a rule, Databases do not run slowly.  Queries do.

update set value=0 in client_table;

Look at the actual query (not the paraphrasing above).
Look at the "where clause" on that query.
Is it supported by indexes or is your database having to scan through the entire table to find the record you want to update?  That would easily account for the excessive CPU load (you may well find that the entire table is sitting in memory).
Running updates in a loop is also questionable.
Have you retrieved a list of record 'ID's and are now looping through them, making a change to each?
Relational DBMSs like to work on "sets" of rows that can extend to millions of rows.  "Sniping" at individual rows where you could process them all in a single, set-based, query will be slower.


how to dump a case-sensitive table within a specific schema

When you specifies the schema name to unload it does not mean that it will be default schema to search objects:
nd@postgres=# create schema foobar;
CREATE SCHEMA
nd@postgres=# create table foobar."Foo"();
CREATE TABLE
nd@postgres=# \! pg_dump --schema=foobar -t "Foo"
pg_dump: no matching tables were found

So you should to fully qualify the table name:
nd@postgres=# \! pg_dump --schema=foobar -t 'foobar."Foo"'
--
-- PostgreSQL database dump
--
... etc ...

Excerpt from the documentation (cursive and bold is mine):

The -n and -N (--schema) switches have no effect when -t is used, because tables selected by -t will be dumped regardless of those switches, and non-table objects will not be dumped.
Formerly, writing -t tab would dump all tables named tab, but now it just dumps whichever one is visible in your default search path.


You looking for Uniform Resource Identifier (URI) scheme, and the syntax is:
mysql://username:password@host:port/database/tablename

See the MySQL documentation


Installing SQL Server 2016 for Azure development

If you're not planning on deploying to a local SQL Server database engine, you can skip that section from the installer, yes.


Split field values using specific character

Using regular expressions is an option:

create table capacity (inputdata varchar2(30));

insert into capacity(inputdata) values('10uF');
insert into capacity(inputdata) values('2K');
insert into capacity(inputdata) values('1.0uF');
insert into capacity(inputdata) values('200UF');

commit;

select regexp_substr(inputdata,'^[0123456789.]*') value,
regexp_replace(inputdata,'^[0123456789.]*','') unit
from capacity
;

here is this example on sqlfiddle
Maybe you should improve the regular expression pattern of the number format

Regular expression functions are described here in the manual   and on the following pages


The command you wish to run does take an ACCESS EXCLUSIVE lock on the table, preventing all other access to that table.  But the duration of this lock should be just a few milliseconds, as adding a column like the one you want to add does not require the table to be re-written, it just requires metadata to be updated.

Where the problem can come in, and I bet you dollars to donuts that it is the problem you are seeing, is in lock priorities.  Someone has a weak lock, like ACCESS SHARE lock, on that table, and they are camping on it indefinitely (maybe an idle-in-transaction connection which has been leaked?  Someone who opened psql, started a query in a repeatable read mode, and then went on vacation?).

The ADD COLUMN tries to take the ACCESS EXCLUSIVE it needs, and it queues up behind the first lock.

Now all future lock requests queue up behind the waiting ACCESS EXCLUSIVE request.

Conceptually, incoming lock requests which are compatible with the already-granted lock could jump over the waiting ACCESS EXCLUSIVE and be granted out of turn, but that is not how PostgreSQL does it.

You need to find the process which is holding the long-lived weak lock.

You can do this by querying the pg_locks table.

select * from pg_locks where
    granted and relation = 'cliente'::regclass \x\g\x

If you do this while everything is locked up, you should get only one answer (unless there are multiple long-lived culprits).  If you do this after you already killed the ADD COLUMN, then you might see lots of granted locks, but if you repeat it a few times there should one or a few which are staying around each time.

You can then take the PID that you got from pg_lock, and query with that into pg_stat_activity to see what the offender is doing:

select * from pg_stat_activity where pid=28731 \x\g\x

...

backend_start    | 2016-03-22 13:08:30.849405-07
xact_start       | 2016-03-22 13:08:36.797703-07
query_start      | 2016-03-22 13:08:36.799021-07
state_change     | 2016-03-22 13:08:36.824369-07
waiting          | f
state            | idle in transaction
backend_xid      |
backend_xmin     |
query            | select * from cliente limit 4;

So, it ran a query, inside a transaction, and then went idle without ever closing the transaction.  It is now 13:13, so they have been idle for 5 minutes.


According to the AutoMySQLBackup documention you can supply an alternate configuration file:

From https://github.com/rcruz/AutoMySQLBackup/blob/master/README

  Thee backup can be run from the command line simply by running the
  following command.

  automysqlbackup /etc/automysqlbackup/myserver.conf

  If you don't supply an argument for automysqlbackup, the default
  configuration in the program automysqlbackup will be used unless a
  global file

  CONFIG_configfile="/etc/automysqlbackup/automysqlbackup.conf"

  exists

So that means you can simply use a different config file, and schedule two different runs in crontab.

That... or you can always roll your own, which I find more flexible.

#!/bin/bash

OUTPUT="/mnt/backups/"

rm -f "$OUTPUTDIR/*.bz2" > /dev/null 2>&1

databases=`mysql -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`

for db in $databases
do
    if [[ "$db" != "information_schema" ]] && [[ "$db" != "performance_schema" ]] && [[ "$db" != "mysql" ]] && [[ "$db" != _* ]] ; then
        echo "Dumping database: $db"
        mysqldump --quick --set-gtid-purged=OFF --databases $db | bzip2 > $OUTPUT/$db.sql.bz2
    fi
done


I think:

this code:

       (SELECT *
          FROM OPENQUERY (
                  MMSHO,
                  'SELECT MS0001077TRP.MW070K01.MW070P01_REKE@MS0001077TRP(
ART_NR, VAR, GEBI_NR) AS ARTICLE_NNBP
FROM MS0001077TRP.MW_MA_VAR_GEBI_SNAP@MS0001077TRP
WHERE SUBSYS_ART_NR = 245511') AS ARTICLE_NNBP,

should be:

       (SELECT *
          FROM OPENQUERY (
                  MMSHO,
                  'SELECT MS0001077TRP.MW070K01.MW070P01_REKE@MS0001077TRP(
ART_NR, VAR, GEBI_NR) AS ARTICLE_NNBP
FROM MS0001077TRP.MW_MA_VAR_GEBI_SNAP@MS0001077TRP
WHERE SUBSYS_ART_NR = 245511') AS ARTICLE_NNBP),

there is a ")" missing at the end


Separate letters from digits in alphanumeric string

SQL Server doesn't support replacing patterns of multiple characters - so to do it via REPLACE would take potentially 10 operations.

With that in mind one way of doing it would be a recursive CTE to process the digits 0-9 sequentially.

It does the replacement and then checks the length of the before and after strings to know how many characters of that number there were and what needs to be added on to the total.

DECLARE @Input VARCHAR(8000) = 'GR35hc7vdH35';

WITH R(Level,Input,Accumulator,StringLength)
     AS (SELECT 0,
                Input,
                0,
                DATALENGTH(Input)
         FROM   (SELECT REPLACE(@Input, '0', '')) D(Input)
         UNION ALL
         SELECT NewLevel,
                NewInput,
                Accumulator + NewLevel * ( StringLength - NewStringLength ),
                NewStringLength
         FROM   R
                CROSS APPLY (SELECT Level + 1) C(NewLevel)
                CROSS APPLY (SELECT REPLACE(Input, NewLevel, '')) C2(NewInput)
                CROSS APPLY (SELECT DATALENGTH(NewInput)) C3(NewStringLength)
         WHERE  NewLevel <= 9)
SELECT Input       AS Col1,
       Accumulator AS Col2
FROM   R
WHERE  Level = 9;

Or you could use CLR and regular expressions (SQL Server 2012 compatible version).

   using System;
using System.Data.SqlTypes;
using System.Collections;
using System.Text.RegularExpressions;

public partial class UserDefinedFunctions
{
    private static readonly Regex digitRegex = new Regex(@"[\d]", RegexOptions.Compiled);

    [Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "FillRow",
                                            TableDefinition = @"Stripped NVARCHAR(MAX),
                                                                Total INT")]

    public static IEnumerable ReplaceAndTotalise(SqlString input)
    {
        if (!input.IsNull)
        {
            int total = 0;
            string stripped = digitRegex.Replace((string)input, match =>
            {
                total += int.Parse(match.Value);
                return string.Empty;
            });

            yield return new Tuple<string, int>(stripped, total);
        }
    }

    public static void FillRow(object resultObject, out SqlString stripped, out SqlInt32 total)
    {
        var result = (Tuple<string, int>)resultObject;
        stripped = result.Item1;
        total = result.Item2;

    }
}

Example Usage

SELECT Stripped,
       Total
FROM   [dbo].[ReplaceAndTotalise]('GR35hc7vdH35')


How to create a Postgres trigger to ensure field is non-updateable

Create a BEFORE UPDATE on your specific column original_id.
NOTE since the application which is using the DB should be aware of its constraints, therefore it is better to raise an exception when the column is getting updated, in order to notify the application layer of a failure, instead of just ignoring the update.

CREATE OR REPLACE FUNCTION check_id_change()
  RETURNS TRIGGER AS
$BODY$
BEGIN
  IF NEW."original_id" IS DISTINCT FROM OLD."original_id"
  THEN
    RAISE EXCEPTION '"original_id" column cannot get updated';
  END IF;

  RETURN NEW;
END;
$BODY$ LANGUAGE PLPGSQL;

CREATE TRIGGER client_update_trigger
BEFORE UPDATE OF "original_id"
  ON "client"
FOR EACH ROW
EXECUTE PROCEDURE check_id_change();

Or using WHEN condition

CREATE OR REPLACE FUNCTION check_id_change()
  RETURNS TRIGGER AS
$BODY$
BEGIN
  RAISE EXCEPTION '"original_id" column cannot get updated';
END;
$BODY$ LANGUAGE PLPGSQL;

CREATE TRIGGER client_update_trigger
BEFORE UPDATE OF "original_id"
  ON "client"
FOR EACH ROW
WHEN (NEW."original_id" IS DISTINCT FROM OLD."original_id")
EXECUTE PROCEDURE check_id_change();

NOTE use IS DISTINCT FROM comparison instead of <> or != unless the column has NOT NULL or you're sure that it's value will never be NULL.


Mysql Join to get multiple row value from

Perhaps something like this:

SELECT e.employee_id, e.first_name, e.last_name,
CONCAT(addr.address_one,',',addr.address_two,',',state) fulladdr,
CONCAT(ph.mobile',',ph.mobile_one,',',ph.phone) phonenumbers,
e.dob, e.maritial_id, e.sex_id, e.photo, s.sex_label
FROM employee e
INNER JOIN sex s ON e.sex_id = s.id
INNER JOIN address addr ON e.address_id = addr.id
INNER JOIN phone ph ON e.phone_id = ph.id;


How to generate a self-signed SSL certificate for MS SQL server 2008 R2 using OpenSSL?

I managed to generate a self-signed certificate that works for encryption of connections to MS SQL Server by using the New-SelfSignedCertificate command for PowerShell.
Example:
New-SelfSignedCertificate -Type SSLServerAuthentication -Subject "CN=$env:COMPUTERNAME" -DnsName "$env:COMPUTERNAME",'localhost.' -KeyAlgorithm "RSA" -KeyLength 2048 -Hash "SHA256" -TextExtension "2.5.29.37={text}1.3.6.1.5.5.7.3.1" -NotAfter (Get-Date).AddMonths(36) -KeySpec KeyExchange -Provider "Microsoft RSA SChannel Cryptographic Provider"

Running it:
C:\mssql>notepad create-ss.ps1

C:\mssql>powershell
Windows PowerShell
Copyright (C) Microsoft Corporation. All rights reserved.

PS C:\mssql> .\create-ss.ps1

   PSParentPath: Microsoft.PowerShell.Security\Certificate::LocalMachine\MY

Thumbprint                                Subject
----------                                -------
14151D156A16557AAA67A4C9465631520CC18626  CN=WIN-9U7N81SI03H

Use of the certificate:

Giving access to the certificate private key is required as well.
Find the account used to run MS SQL Server:

Now from Management Console, Certificates Add-on, Select Manage Private Keys

Add the user running MS SQL

Restart MS SQL Server and you should be done.


Is Circular replication recommended among 4 MySQL servers

I do not know why trying to fit circular replication in such a setup- a circular replication has multiple single point of failures (one per server, as if any server fails, the "circle" is broken) plus with non-gtid replication solving the failover is not easy at all (what is the binary coordinates equivalent to the binary coordinates of the next server?). While there are some very specific and contained cases where it could make sense, I would say yours is not one of them.

What you described, 1 read/write server and 3 read-only ones is the paradigmatic usage of traditional replication where the master is r/w and it has 3 slaves depending on it. So, a tree-like topology, not a circle.

Of course, replication, up to the most recent versions has its issues (traditionally being asynchronous, no possibility of checking for drift, no paralelization, no automatic resolution of conflicts), so in some cases you may want to check alternative options, like Galera or Tungsten replication. However, many of the shortcomings have been solved in the most recent versions with semisync replication, GTID, parallel apply of logs, and in the upcoming 5.7, multi-master replication.

My recommendation would be to stick with standard replication with one master and 3 slaves, if possible using 5.6 GTID, as it makes the failover easier.

However, replication does not provide HA by itself. There are different approaches, depending on the technology used, here you have some suggestions:

MySQL failover is a small first party utility that can monitor replication and help you change the topology of your setup. If you want a more all-in-one solution, check MySQL Fabric
MHA allows you to check and change the topology of your setup, maybe combined with virtual IPs to guarantee the service
HAProxy allows you to setup a couple of virtual read/write and read-only services that are backed by several MySQL servers, so it can transparently check for failure and switch the active server, or load balance between several servers

Here you have a presentation with some thoughts about different methods of doing HA with MySQL.


Is it possible to disable all mysql_connect(): Too many connections emails, but not other errors?

The answer is that, disable your mailer on the script that checking for this error. Look for the script and comment out the mailer.


Truncate temporary table commits transaction?

You can use DROP TEMPORARY TABLE instead of TRUNCATE, then CREATE TEMPORARY TABLE(...) again, it will not commit the transaction but you gain the speed of TRUNCATE statement.

"CREATE TABLE and DROP TABLE statements do not commit a transaction if the TEMPORARY keyword is used"
Statements That Cause an Implicit Commit


Normalizing data for joins, dedicated columns, a view or.. something else?

There is no generic best solution, normalization is a trade-off between time, space, redundancy and its consequences.

Given your use case is to see a user, his current job and his current house, the original model is fine. And I think the SEQ column is unnecessary.

SEQ and LAST_UPDATE columns are redundant, they both contain information about a change. SEQ simply represents a change number, but LAST_UPDATE even contains the date of change. The number of changes can be simply found by counting rows for a specific USER_ID, so unless you have multiple changes in a second (the smallest unit a DATE type can store), the SEQ column is not mandatory, and USER_ID, LAST_UPDATE could be the PK. The query you write is very real, I have seen queries like that a lot of times, but you can do even better with analytic functions:

SELECT U.USER_ID,
       U.DESCRIPTION,
       UJ.JOB_ID
  FROM USERS U,
  ( SELECT JOB_ID
    FROM (SELECT JOB_ID, ROW_NUMBER() OVER (PARTITION BY USER_ID ORDER BY LAST_UPDATE DESC) as R FROM USER_JOBS)
    WHERE R = 1
  ) UJ
 WHERE U.USER_ID = UJ.USER_ID AND U.USER_ID = :B1;


Your conditions are searching for ranges. Now imagine you're looking in a dictionary and looking up all words where the first letter is "greater" than A. Of how much use is the index? You want to narrow the search range down as much as possible. MySQL most of the time can only use one index per table. Combine those begin and end indexes.

CREATE TABLE `geo_ip_city` (
    `id` INT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
    `begin` BIGINT(20) NOT NULL DEFAULT '0',
    `end` BIGINT(20) NOT NULL DEFAULT '0',
    `code` VARCHAR(2) NOT NULL DEFAULT '',
    `city` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    INDEX `idx_begin_end` (`begin`, `end`)
);

I also removed the index on code because it's also pretty much useless. It consists of two letters. Assuming there are no special characters/symbols included, this leaves you with 26*26=676 possible values for this column. That's a selectivity of 676 / 4.500.000 = 0.0001. You want to have the selectivity as near to 1 as possible.


column_name ordered same as describe table

SELECT column_name FROM information_schema.columns
WHERE table_name='assetType'
ORDER BY ORDINAL_POSITION;

The field ORDINAL_POSITION is there to help

mysql> desc information_schema.columns;
+--------------------------+----------------------------+------+-----+---------+-------+
| Field                    | Type                       | Null | Key | Default | Extra |
+--------------------------+----------------------------+------+-----+---------+-------+
| TABLE_CATALOG            | varchar(64)                | YES  |     | NULL    |       |
| TABLE_SCHEMA             | varchar(64)                | YES  |     | NULL    |       |
| TABLE_NAME               | varchar(64)                | YES  |     | NULL    |       |
| COLUMN_NAME              | varchar(64)                | YES  |     | NULL    |       |
| ORDINAL_POSITION         | int(10) unsigned           | NO   |     | NULL    |       |
| COLUMN_DEFAULT           | text                       | YES  |     | NULL    |       |
| IS_NULLABLE              | varchar(3)                 | NO   |     |         |       |
| DATA_TYPE                | longtext                   | YES  |     | NULL    |       |
| CHARACTER_MAXIMUM_LENGTH | bigint(21)                 | YES  |     | NULL    |       |
| CHARACTER_OCTET_LENGTH   | bigint(21)                 | YES  |     | NULL    |       |
| NUMERIC_PRECISION        | bigint(10) unsigned        | YES  |     | NULL    |       |
| NUMERIC_SCALE            | bigint(10) unsigned        | YES  |     | NULL    |       |
| DATETIME_PRECISION       | int(10) unsigned           | YES  |     | NULL    |       |
| CHARACTER_SET_NAME       | varchar(64)                | YES  |     | NULL    |       |
| COLLATION_NAME           | varchar(64)                | YES  |     | NULL    |       |
| COLUMN_TYPE              | mediumtext                 | NO   |     | NULL    |       |
| COLUMN_KEY               | enum('','PRI','UNI','MUL') | NO   |     | NULL    |       |
| EXTRA                    | varchar(57)                | YES  |     | NULL    |       |
| PRIVILEGES               | varchar(154)               | YES  |     | NULL    |       |
| COLUMN_COMMENT           | text                       | NO   |     | NULL    |       |
| GENERATION_EXPRESSION    | longtext                   | NO   |     | NULL    |       |
| SRS_ID                   | int(10) unsigned           | YES  |     | NULL    |       |
+--------------------------+----------------------------+------+-----+---------+-------+
22 rows in set (0.02 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.12    |
+-----------+
1 row in set (0.00 sec)

mysql>


So? Just copy a dumpfile to a location accessible to database2.

ora is not a directory, it is a directory object defined in the database, that serves as an "alias" to the real path in the filesystem.

Overview of Oracle Data Pump

File Allocation

  Data Pump is server-based rather than client-based, dump files, log files, and SQL files are accessed relative to server-based directory paths. Data Pump requires that directory paths be specified as directory objects. A directory object maps a name to a directory path on the file system.

  The following example shows a SQL statement that creates a directory
  object named dpump_dir1 that is mapped to a directory located at
  /usr/apps/datafiles.

SQL> CREATE DIRECTORY dpump_dir1 AS '/usr/apps/datafiles';

Or you can import the table directly from database1 to database2 through a database link.

Using Network Link Import to Move Data

  When the Import NETWORK_LINK parameter is used to specify a network
  link for an import operation, SQL is directly used to move the data
  using an INSERT SELECT statement. The SELECT clause retrieves the data
  from the remote database over the network link. The INSERT clause uses
  SQL to insert the data into the target database. There are no dump
  files involved.


In Oracle shared server mode, what is a virtual circuit?

Its a shared memory(in SGA) used by dispatcher for client connection requests and responses.

  In a shared server configuration, client user processes connect to a dispatcher. The dispatcher can support multiple client connections concurrently. Each client connection is bound to a virtual circuit, which is a piece of shared memory used by the dispatcher for client database connection requests and replies. The dispatcher places a virtual circuit on a common queue when a request arrives.

Reference

In other words, its a memory where the actual request is stored and shared server can know what it has to serve by reading this memory. For example if a client sends a query lets say select * from emp, the dispatcher has to store it somewhere so that an idle shared server process can pick it up and process further. The somewhere is the virtual circuit.


SQL Server DB table data size query result not increasing if data count increasing

SQL Server stores data on 8kb pages. You can't have a table that's smaller than than two pages. So when you add one row to the table, you have a minimum space consumption of 2 * 8kb = 16kb. But those pages aren't full, which means adding 15 more rows doesn't increase table size by (15 * initial size), it just means that the data page holding the initial row had enough room for more data. If you add 1000 more rows, I bet your size will increase, but this will vary depending on what's in a row and how many rows can fit on an 8kb page.

I don't know that it matters, really, how much data is in a table if you want to only look at the space on a page that's used, and ignore the space on a page that isn't used. You're taking 8 kb on disk and 8 kb in memory whether the page has 10 bytes of data or 7,997 bytes. So you will see that most space calculation scripts just take (number of pages) * 8,192 because the unused space on a page is very rarely relevant.


Correct way to join row in parent table to row in one of several children tables

I think your whole schema is not fully thought through.

A product is a product which, presumably, you are going to sell?

Have one product table - no need to split the products up into phone, tablet or smartwatch. There's no problem having millions of records in a table these days.

Have a table product_category (or type as you suggest) which will have phone, tablet &c. which will be a FOREIGN KEY (pointing back to an INTEGER PRIMARY KEY) in the product table. Then you can have your classic supplier, client, billing tables &c.

You might want to take a look at databaseanswers.org and also you could download Open Source products which correspond to your particular system - check out their schemas and see what they've done.

You asked about the canonical way of designing these sorts of schemas - this is (probably) it. Plus, you won't be writing queries with 100's of tables. You mentioned DRY and SPOT. Don't forget YAGNI (You Ain't Going To Need It) - also espoused by Eric Raymond in the excellent "The Art of Unix Programming".

Don't try and be all things to all men from day one! Start easy and design incrementally - don't expect to get everything right the first time (hint: you won't :-)) - best of luck with your project.


Copy database fails while trying to copy login

Do yourself a favor and stay away from the copy database functionality of SSMS.  Just do a full database backup and then restore on the instance you are looking for.  You can fix orphaned users on the destination instance through the use of sp_change_users_login.

On your Source Instance:

backup database YourDatabase
to disk = 'C:\YourBackupDir\YourDatabase.bak'
go

On your Destination Instance:

restore database YourDatabase
from disk = 'C:\YourBackupDir\YourDatabase.bak'
go

You may have to move that backup onto a share or port it to the destination server, but you get the idea.


Error log folder location and OLA's SQL maintenance solution

All the code is on github
There is quite a lot of it now
github link
IF @Version >= 11
  BEGIN
    SELECT @LogDirectory = [path]
    FROM sys.dm_os_server_diagnostics_log_configurations
  END
ELSE
  BEGIN
    SELECT @LogDirectory = LEFT(CAST(SERVERPROPERTY('ErrorLogFileName') AS nvarchar(max)),LEN(CAST(SERVERPROPERTY('ErrorLogFileName') AS nvarchar(max))) - CHARINDEX('\',REVERSE(CAST(SERVERPROPERTY('ErrorLogFileName') AS nvarchar(max)))))
END


How to restore some percentage of backup file?

You probably have an option of:

Create several filegroups and data files in these filegroups
Move some part of your data to new files using rebuild index command
When restoring your database from a backup specify remote location for some data files, and the other files restore locally.

Some useful links to begin with:

Move an Existing Index to a Different Filegroup (Microsoft Docs)
SQL Server Can Run Databases from Network Shares & NAS by Brent Ozar


I accidentally set password to blank for a PostgreSQL database, how do I change it?

This blogpost should answer. Full disclosure: it's my blogpost.

Summary:

  If you have access to shell account on the machine PostgreSQL is running, and your shell works as the same user as Postgres itself, or root – solution is easy.

  Edit your pg_hba.conf file, and at the beginning of it put:

  local all all trust

  or (depending on your paranoia):

  local all postgres ident

  And restart your PostgreSQL (usually something like /etc/init.d/postgres restart).

  Afterwards you should be able to connect to Postgres as postgres user without password.

  You should note, that if you have choosen the option with “ident” you will be able to connect without password only from shell account named “postgres”.

  don't forget to reverse the pg_hba.conf settings when you have reset the password.


Postgres 13 - Grant to Active Directory Groups?

No. All roles have to be created with CREATE ROLE in the database.
The existence of the role in the database is necessary, because it is associated with permissions in the database (authorization). Authorization is not managed through an external authority. What is managed externally is authentication.
You could write or find some software that synchronizes PostgreSQL roles with what is in your Active Directory.


How to execute multiple statement in PgAdmin 4 like we do in Sql Server/Oracle?

pgAdmin (both III and 4) execute both statements. For the first one, it just discards the output, unless there is an error.

You can actually chain as many statements as you want:

-- We create one table
CREATE TABLE digit_names
(
    digit integer PRIMARY KEY,
    digit_name text,
    UNIQUE (digit_name)
) ;

-- Fill it with values
INSERT INTO digit_names
VALUES
    (0, 'zero'),
    (1, 'one'),
    (2, 'two'),
    (3, 'three'),
    (4, 'four'),
    (5, 'five'),
    (6, 'six'),
    (7, 'seven'),
    (8, 'eight'),
    (9, 'nine') ;

-- And perform a SELECT query
SELECT
    digit_name
FROM
    digit_names
    JOIN (VALUES (1), (2), (3) ) AS three_digits(digit) USING (digit) ;

-- All in just one statement.

But you'll get only the output from the last one.

If you're using pgAdmin 4, you just type everything on the Query Tool pane, and then press the button with a [lightning bolt] (or press F5).


Your situation looks most like a powerfailure. This kind can hurt the database badly and in this case it looks like the controlfile or the online logfile is damaged. If you have a backup, start with restoring the controlfile from the backup and recover the database using the backup controlfile option.

With a little luck you can open the database afterwards. With a little bad luck, the damage is in the online redolog in which case you can not open the database in a consistent state. This is because your undo segments are active and they lack to recovery information from the redolog file to recover from that.

Still it is possible to open the database using some tricks but it does require extra work, setting hidden parameters etc. After that type of 'recovery' only export -> create new db -> import is a valid option.


Try to define appropiate memory size for this postgres database

So long as the DB fits in RAM used for disk cache and/or shared_buffers there is very little benefit to adding more RAM.

Focus your performance efforts elsewhere - eliminate unnecessary queries, cache frequently read results that don't need to be perfectly fresh, replace repeated tiny queries with more efficient joins over sets, etc.


Using PIVOT to convert multiple rows and columns

Well, this solution is not particularly sophisticated (it doesn't use PIVOT), but it would solve your problem.  There are probably smarter ways to address the problem, but here is one method.

The solution selects from a derived unpivoted table (note comment about unpivot original data).  It groups by MyId and customer and uses CASE expressions to derive the individual column names and values.  My example only show two of the servicetype columns broken out into separate columns, but hopefully you'll see the pattern.  (I'm not sure how you handle the rows with blank servicetype).

--demo setup
DROP TABLE IF EXISTS supercharge
GO
CREATE TABLE supercharge (
MyID int
 , customer int
 , Servicetype varchar(10)
 , servicerate varchar(10)
 , serviceNumber int
 , consumption int
 , daysofservice int
 , unitofmeasure varchar(10)
 , chargeAmount decimal(11,2)
 , chargeDate datetime
)

INSERT INTO supercharge (MyID, customer, Servicetype, servicerate, serviceNumber,consumption,daysofservice,unitofmeasure,chargeAmount, chargeDate)

VALUES
(10016, 112992, 'RELC', '110W', 100, 753, 31, 'KWH',99.92, '2019-05-08'),
(10016, 112992, 'RGAS', '120', 200, 0, 31, 'CCF',5.31, '2019-05-08'),
(10016, 112992, 'RWTR', '130', 300, 3, 31, 'TGAL',11.85, '2019-05-08'),
(10016, 112992, 'RSWR', '388', 400, 3, 31, 'NONE',10.12, '2019-05-08'),
(10016, 112992, 'RGAR', '', 500, null, null, '',32.12, '2019-05-08'),
(10016, 112992, 'STRM', '', 700, null, null, '',2.38, '2019-05-08'),
(10016, 112992, '', 'LATE', null, null, null, '',15, '2019-05-13'),
(10016, 112992, '', 'CUTO', null, null, null, '',30, '2019-05-08');

--solution
SELECT MyID
    ,customer
--RELC columns
    ,max(CASE
            WHEN Servicetype = 'RELC'
                AND col = 'rate'
                THEN value
            END) AS Relc_Rate
    ,max(CASE
            WHEN Servicetype = 'RELC'
                AND col = 'consumption'
                THEN value
            END) AS Relc_Consumption
    ,max(CASE
            WHEN Servicetype = 'RELC'
                AND col = 'daysofservice'
                THEN value
            END) AS Relc_DaysOfService
    ,max(CASE
            WHEN Servicetype = 'RELC'
                AND col = 'unitofmeasure'
                THEN value
            END) AS Relc_UnitOfMeasure
    ,max(CASE
            WHEN Servicetype = 'RELC'
                AND col = 'chargeamount'
                THEN value
            END) AS Relc_Charge
    ,max(CASE
            WHEN Servicetype = 'RELC'
                AND col = 'chargedate'
                THEN value
            END) AS Relc_ChargeDate
--RGAS columns
    ,max(CASE
            WHEN Servicetype = 'RGAS'
                AND col = 'rate'
                THEN value
            END) AS Rgas_Rate
    ,max(CASE
            WHEN Servicetype = 'RGAS'
                AND col = 'consumption'
                THEN value
            END) AS Rgas_Consumption
    ,max(CASE
            WHEN Servicetype = 'RGAS'
                AND col = 'daysofservice'
                THEN value
            END) AS Rgas_DaysOfService
    ,max(CASE
            WHEN Servicetype = 'RGAS'
                AND col = 'unitofmeasure'
                THEN value
            END) AS Rgas_UnitOfMeasure
    ,max(CASE
            WHEN Servicetype = 'RGAS'
                AND col = 'chargeamount'
                THEN value
            END) AS Rgas_Charge
    ,max(CASE
            WHEN Servicetype = 'RGAS'
                AND col = 'chargedate'
                THEN value
            END) AS Rgas_ChargeDate

FROM
--unpivot original data
(
select MyID,customer,Servicetype, col, value
from supercharge
cross apply
(
  select 'rate', cast(servicerate as varchar(10)) union all
  select 'consumption', cast(consumption as varchar(10)) union all
  select 'daysofservice', cast(daysofservice as varchar(10)) union all
  select 'unitofmeasure', cast(unitofmeasure as varchar(10)) union all
  select 'chargeamount', cast(chargeAmount as varchar(10)) union all
  select 'chargeDate', convert(varchar(10), chargeDate,121)
) c(col, value)
) d
GROUP BY MyID
    ,customer;

| MyID  | customer | Relc_Rate | Relc_Consumption | Relc_DaysOfService | Relc_UnitOfMeasure | Relc_Charge | Relc_ChargeDate | Rgas_Rate | Rgas_Consumption | Rgas_DaysOfService | Rgas_UnitOfMeasure | Rgas_Charge | Rgas_ChargeDate |
|-------|----------|-----------|------------------|--------------------|--------------------|-------------|-----------------|-----------|------------------|--------------------|--------------------|-------------|-----------------|
| 10016 | 112992   | 110W      | 753              | 31                 | KWH                | 99.92       | 2019-05-08      | 120       | 0                | 31                 | CCF                | 5.31        | 2019-05-08      |


Optimising a Database Shrink (in terms of speed and reducing fragmentation)

A note first:

When you rebuild indexes, you need roughly free space of roughly 1.2 times the largest table. So a 15 GB table in that 20 GB used requires 18 GB free space. This sets a minimum database file size.

Personally, for OLTP databases, anything over 50%-60% used is a bit of red flag. So anything less then 35-40GB for for 20GB data would be worrying.

To decide whether you need downtime and filegroups and all the hassle, have you:

rebuilt all indexes (ALTER INDEX ALL .. REBUILD) to see where you are?
run DBCC SHRINKFILE with TRUNCATEONLY to free space only with no data movements?
checked file size after these steps?

One thing to look at would be NTFS fragmentation too after all this. YOu can stop SQL Server and defrag the volumes to get the MDFs and LDFs contiguous again. Of course, when they grow again you'll get fragmentation: which merits having generous file sizes to start with (50-60% used maximum).

Otherwise, your idea is sound to use different spindles to manage IO. Note the file size and NTFS stuff I mentioned above too.


Execute SQL conditionally based on database Compatibility Level

Final Solution
I went for the dynamic SQL solution, as it "just works", and it's the most obvious:
DECLARE @compatibility_level TINYINT = 0;
SELECT @compatibility_level = compatibility_level FROM sys.databases WHERE name = 'MyDatabase';
PRINT CONCAT('Database compatibility level is: ', @compatibility_level);

-- Data Classification is only supported from SQL Server 2019 onwards, so we skip it if not supported
IF @compatibility_level >= 150
BEGIN
    DECLARE @sql NVARCHAR(MAX) = '';

    SELECT @sql += 'ADD SENSITIVITY CLASSIFICATION TO
        MySchema.MyTable1.Column1,
        MySchema.MyTable1.Column2
        WITH (LABEL=''Confidential - GDPR'', INFORMATION_TYPE=''Name'', RANK=MEDIUM);' + CHAR(10);

    SELECT @sql += 'ADD SENSITIVITY CLASSIFICATION TO
        MySchema.MyTable2.Column1
        WITH (LABEL=''Confidential'', INFORMATION_TYPE=''Contact Info'', RANK=MEDIUM);' + CHAR(10);
        PRINT @sql;

    -- ...more classifications here, elided

    PRINT @sql;
    EXEC sp_executesql @sql;
END
ELSE
BEGIN
    RAISERROR('Data Classification will not be performed, as database compatibility level %d is not high enough', 10, 1, @compatibility_level);
END;

Other Potential Solutions

I thought to use NOEXEC, to prevent executing SQL if the database compatibility level was too low:

DECLARE @compatibility_level TINYINT = 0;
SELECT @compatibility_level = compatibility_level FROM sys.databases WHERE name = 'MyDatabase';
PRINT CONCAT('Database compatibility level is: ', @compatibility_level);

IF @compatibility_level < 150
BEGIN
    RAISERROR('Data Classification will not be performed, as database compatibility level %d is not high enough', 10, 1, @compatibility_level);
      SET NOEXEC ON;
END;
GO

-- This will only be executed if database compatibility is >= 150
ADD SENSITIVITY CLASSIFICATION TO
    MyTable.Forename,
    MyTable.Surname
    WITH (LABEL='Confidential - GDPR', INFORMATION_TYPE='Name', RANK=MEDIUM);

SET NOEXEC OFF;
GO

However, even with NOEXEC enabled, it still actually interprets the SQL - so this doesn't work if the server compatiblity level is too low (beforew SQL2019), regardless of the database compatiblity level.

@martin-cairney came up with another solution, using the sp_addextendedproperty function, and you can find an example here. I personally find having to mess around with "magic string" GUIDs for sys_sensitivity_label_id to be a PITA.

A further option would be to write directly to the sys.extended_properties table (this is exactly what sp_addextendedproperty does) - obviously this method isn't recommended.


SQL Server 2008 - SIMPLE database log file is HUGE relative to the actual DB?

If either of the comment-helpers post an answer, I'll re-mark the answer for them.  I keep checking and will eventually forget to come back to check so I'm going to answer in case someone else runs into this:

The problem was exactly what Kenneth Fisher, 8bit, and Kin thought, the Transaction log was gigantic on my small database (60GB/2GB, respectively) because of failed replication.

Replication had been configured on the database but disabled for about 7 months.  I assume SQL was queuing up all of the changed-data for replication once the the repl-configuration became re-enabled.

This was absolutely a case of misconfiguration.  The customer had disabled replication in testing, moved away from using replication, but never went back and deleted the config, thereby, over time, creating the problem.

After going into SQL Replication in SSMS and deleting the configuration, ~3 mins later, the log file went from 60GB to 43MB.

Now, I'm not sure if it would've done that on it's own.  I ended up running 'Checkpoint' on the database 2x as was previously suggested with no immediate effect.  Immediate checks after those operations yielded no results.  Spot-checking the log file size a few minutes later saw the dramatic difference so again, I'm not sure if I needed to run the checkpoints but ultimately, the transaction log effectively disappeared as a result.


Difference between " ANY(…)" and "NOT = ANY(…)"?

NOT sid = ANY (SELECT ...)

is equivalent to:

sid <> ALL (SELECT ...)

So, your two conditions are not the same.

ALL and ANY operators are hard for me (and many people) to use. I think that's how many prefer to use IN, NOT IN, EXISTS and NOT EXISTS which result in more self-explanatory code.

If you do want to work with them, think that sid = ANY (SELECT ...) means "check if sid is equal to any (some) of the (select...) values".

Then the NOT sid = ANY (SELECT ...) is the opposite of that. But the opposite of "equal to any of them" is "different to all of them" (and not "different to some of them").


PreparedStatement caching size on Oracle and SQL Server

There is no difference in treatment in Oracle between a java PreparedStatement and a standard Statement: both are treated as cursors. They are both parsed (="compiled"), executed and (for queries only) fetched.

The difference between the two kinds of statement is that PreparedStatement:

can be executed multiple times without re-parsing
can use binds

Both actions (binding and re-executing) are client behaviours. To Oracle a client that uses a cursor only once or another client that uses a cursor a thousand times are treated exactly the same way:

The statement is initially parsed.
If the statement has already been parsed by the database before (and is still in the share pool), the previous execution plan will be re-used. This is called a soft parse.
If the statement is new or too much dissimilar to previous statements, a new plan is computed. This is called a hard parse.
The statement is then executed (once the client asks for it).
A fetchable result set is returned for queries.
The client can re-use the cursor to run the same query, possibly with different variable values (jump to 4.)

Note that standard statements, even though they are destined to be used only once, will occupy a space in the shared pool (until they are aged away according to an LRU algorithm).

Prepared statements are therefore a client optimization. They allow the client to limit the number of hard parses by using bind variables and reduce further the number of soft parses by reusing the same cursor multiple times when possible.

Not using prepared statements usually leads to a shared pool full of slightly different statements that are used only once each. The number of parses increases dramatically, which can be a source of contention and therefore bad database performance.

You shouldn't worry too much about the size of the shared pool: use prepared statements and Oracle will keep the statements that are used the most at the top, they shouldn't age away and their plans should be available at all time.


The command seems right.

You can try to debug using PTDEBUG:

PTDEBUG=1 (your complete pt-kill command with daemonize and log ) 2>/var/log/pt-kill.err

Be careful to watch disk space; don't let it run in debug mode indefinitely.


merging many jsonb objects using aggregate functions in PostgreSQL?

I had the same problem and this post solved it:
https://blog.faraday.io/how-to-aggregate-jsonb-in-postgres/
The idea is to create an aggregate:

CREATE AGGREGATE jsonb_object_agg(jsonb) (
  SFUNC = 'jsonb_concat',
  STYPE = jsonb,
  INITCOND = '{}'
);

See the linked article for more details.


Semantic version type on PG 11.5 deployed on RDS. ltree?

You could use an array of integers e.g. version 1.2.3 would be array[1,2,3].

Such an array compares (and sorts) correctly. To format it as a string, you could write some simple functions.

Parsing is simple as well: string_to_array('1.2.3', '.')::int[]


how do I execute a void-returning procedure in pgsql 9.1?

There is no problem with how you call your function.  The error you get comes from executing it if and only if cnt is greater than zero.  The error you get is

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function "waitris" line 9 at SQL statement

and it clearly states that the problem lies in the SELECT pg_sleep(1); line.  That is the place you should replace SELECT with PERFORM.

Otherwise your loop is futile without a CONTINUE.  And even if you add that, behaviour of the loop is hard to follow.  You'd better use a WHILE loop instead.  And still one more: as you see, I changed the variable name count to cnt for better readability.


Replacing SQL Server TDE soon expiring certificate

If the certificate expires TDE will continue to work so don't panic there but you should still replace it to keep with your security policy. Because the certificate is used to encrypt the Database Encryption Key (DEK) and not the data, it is quite easy to change the certificate.

Create or import a new certificate and then use it to encrypt the DEK.

ALTER DATABASE ENCRYPTION KEY
REGENERATE WITH ALGORITHM = AES_256 --Your choice of algorithm may be different
ENCRYPTION BY SERVER CERTIFICATE 'YourNewCertificate';

UPDATE

I made a mistake in my code above. It is not possible to alter the encryption algorithm and the certificate used in one statement. This was not required anyway so the following code is sufficient:

ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER CERTIFICATE YourNewCertificate;
GO


Define the attribute/column being summed as type money or as type numeric.  If defining numeric you'll need to specify the percision.

http://www.postgresql.org/docs/9.4/static/datatype-money.html#DATATYPE-MONEY-TABLE


SSIS AND XLSM load time issue Visual Studio 2010

I have resolved this by using an sql command in the excel properties, and only import where ID IS NOT NULL x

thank team for ideas x


The restore speed will depend on the hardware you use for DEV environment.

I would suggest to turn on Instant File Initialization. When backups are restored the space has to be pre-allocated before a restore can occur and IFI is a boon for such scenarios. Instant file initialization only affects MDF and NDF files, not LDF files. In other words, transaction log files can’t take advantage of instant file initialization.

Also, you can play with BufferCount  and MaxTransferSize. Read up Optimizing Database Restores.


data warehouse automation tool: benefit or bane?

I'll weigh in from the user perspective.
I have worked extensively (15 or so projects) with one of the automation tools on a SQL Server backend, and results were mixed.

Did the DWA tool greatly reduce the time to get a data warehouse up and running, or did the time it took to ramp up on learning the tool eat the time that otherwise would have been gained?

The answer is a great big "it depends". I have found it to largely depend on the complexity of the datawarehouse you are building, and the skills you have using the native tools.
We could easily use the tool to allow customers to design their own datawarehouse and olap solutions with little training and knowledge of the underlying technology provided the requirements were simple.
For an experienced datawarehouse and OLAP developer in the native underlying tools the gains were much less impressive and I haven't seen automation tools supporting each and every feature of SSIS and SSAS. Most provide hooks where you can plug in SQL or XMLA scripts, but as complexity of a project increases you end up scripting more than you are using the automation tool.
Furthermore, as datawarehouse automation tools support multiple versions of the underlying technology I have seen new features in SQL appear later or not appear at all in the automation tools.

If the DWA tool resulted in the failure of your data warehouse project, what were the reasons?

In big complex projects: performance and the lack of flexibility of the automation tool. If you do everything by hand you can configure parallel processing perfectly the way you like it, you can use tricks in your queries to make them faster, you get to think about which operators are blocking in SSIS, what the lineage of your data flow is etc.

If you have done data warehousing in the past without a DWA tool, and you have also used a DWA tool recently, would you use a DWA tool again for your next data warehouse project?

The same "it depends" applies here. It depends on the project. If I expect a lot of data or a lot of complexity, no I wouldn't but I might look at BIML to generate SSIS Packages to avoid repeating tasks.
If it's a simple project, a small source system or I need to have key users at the customer work on the project and take over support after I left, maybe yes.

When would you see a DWA tool as being overkill?

Overkill would when the price exceeds the benefit obviously. If it's a fit for the project, and especially if a less experienced user or  power user can take over the project instead of paying consultant fees, the cost isn't necessarily prohibitive.

What did you like best about using a DWA tool? What did you like least?

What I like best is also the reason to dislike it sometimes. The rigidness and decisions that are made in your place makes sure that projects are "clean" and follow a certain methodology. Especially working with multiple developers it can be a benefit that everybody is forced to work a certain way, you just open a project and know what you will find.
The fact that you are forced to work a certain way can be troublesome if you run into an issue that hasn't been foreseen by the tool.

Some other points I'd like to make

Support is better for the underlying technology. If you have a question on how you have to do something in SSIS or SSAS you could just google or ask here. As the DWA tools abstract that layer you need to turn to the vendor support (which may not be free)
Every piece of software has bugs. SQL Server's bugs are better documented online. I remember a situation where after a long discussion with our vendor's support I just ended up decompiling the DWA tool to figure out what was going on in their code and why it behaved the way it did.
Every DWA tool lacks the flexibility and the community SQL Server has and sooner or later you may run into the limitations the tool is bound to have. Take for example version control. DWA tools may be rolling their own, and that may work more or less, but if you have a simple visual studio project you can add it in your TFS where the rest of your code lives, use BIDShelper to get readable diff's etc. If you need stored procedures in SSAS you'll likely be out of luck too.


How to get value from range datetime from date format to bigint in postgresql

You do not convert datetime to bigint, you use timestamp with time zone. To convert a string to a timestamp with time zone, use the to_timestamp function.


I'm pretty sure leaf expression means something like "a?b:c". A discrete value from a conditional.

But basically the overall message is saying you need to avoid null values. They can't partition on a null.


For SQL 2008 you can run either Enterprise or Standard for mirroring: http://msdn.microsoft.com/en-us/library/ms188712.aspx

The witness server itself can be Standard, Enterprise, Workgroup, or Express.

The witness server is needed for automatic failover: http://technet.microsoft.com/en-us/library/ms175191.aspx


mysql match against with same score on different input

Ah, you want a higher score for exact match?  Instead of

ORDER BY `score` DESC

do

ORDER BY `name` = 'Superocean Héritage 42mm' DESC,
         `score` DESC

The = test will be 1 for an exact match, else 0.  So, effectively, it overrules whatever the score returns.

(Alternative ways to phrase it involve IF or CASE).


instead of dblink, I recommend you to use potgres_fdw.
When you map the foreign table, you can use a simple SELECT INSERT to solve your problem. E.g.:
INSERT INTO remote.employee
SELECT local.* FROM public.employee AS local
WHERE NOT EXISTS (
     SELECT 1 FROM remote.employe AS remote
     WHERE local.id = remote.id
)

Remember to fix the WHERE filter to fit your table's primary keys.

EDIT: I make a small sample how to compare the rows of both tables as a gist: https://gist.github.com/sebastianwebber/7b4ab352733e8aab8b03f94d8aa0c7a9

The easy way to compare the rows is:
SELECT
    e1.id as e1_pk,
    e2.id as e2_pk,
    COALESCE(e1.* = e2.*, false) as equal
FROM employee1 as e1
RIGHT JOIN employee2 as e2 USING (id);

Hope it helps.


Migrate from SQL Server 2000 to 2012 without a 2005 or 2008 instance

No, there is no workaround to upgrading a database directly from 2000 to 2012.

Since you don't have a ton of data, you can do all kinds of things to move the data (but not the database as a whole), including:

Import/Export wizard
SSIS
BCP
Manual queries using a linked server from 2012 or an application

However these will not necessarily bring over other things like users, permissions, roles, etc. And not everything will create your objects in the right dependency order. So they are more work and error-prone. In my experience it would be worthwhile to just put up an intermediate instance temporarily and then do two backup+restore operations - which will be easier, faster, and less error-prone than any of the above methods.

If your databases are smaller than 10GB, you can install a copy of Express (free) in a few minutes, and use that. You can download 2008 R2 Express here. I don't think there are any Enterprise features in 2000 around database structure etc. that would block that upgrade (a bigger concern for more modern versions).

If your database is too large for Express, you should be able to find Developer Edition for $49 or so (or even cheaper - I found 2005 on eBay for $37), or if you have an MSDN subscription, you should be able to get any SKU from there.

Or you can get away with using an Evaluation Edition, which is amazingly still being offered for download for older versions (e.g. I found 2008 here), however I'm on a phone so was not able to confirm the download for that version still works.


Problem with foreign key between 3 tables. (PgAdminIII)

It would have been good if you provided the foreign keys that failed. The following should work:

ALTER TABLE public."OrarioToLocale"
    ADD CONSTRAINT FK_... FOREIGN KEY ("IDorarioT")
        REFERENCES public."Orario" ("IDorario");

and a similar constraint for Locale. Why do the attributes in OrarioToLocale end with a T?


How do I extend the character limit of a string in management studio

change  PRINT (@viewsql) to  SELECT (@viewsql)


SQL Server Auditing is not available in Standard editions?

The auditing option, along with the resource governor and other enterprise only features  are always shown in SSMS no matter which edition you are running. Part of it is probably for familiarity in UX design and part marketing. ;)

Yes, you can use extended events to capture some of this information. Setting up a server side trace to capture this is also very simple and well documented.


Do I have to write SQL for that manually?

Yes, but it's not that hard:

create table original (id integer, url text);
insert into original
values
(1,'VeryLongURLText'),
(2,'VeryLongURLText'),
(3,'LoooongURLText'),
(4,'LoooongURLText'),
(5,'LoooongURLText');

create the dictionary

create table dictionary (id serial, url text);
insert into dictionary (url)
select distinct url
from original;

This creates the table with the following content:

id | data
---+----------------
 1 | LoooongURLText
 2 | VeryLongURLText

Now create a new table based on the dictionary:

create table compressed
as
select o.id, o.some_column, o.other_column, d.id as dictionary_id
from original o
  join dictionary d on o.url = d.url;

As your goal is to reduce the space overhead it's better to create  new table with the dictionary id rather then altering the existing one. This will also be a lot faster then updating all rows from the existing table (with a billion rows this will however still take some time)


Yes you should use spatial indexes, but you should also consider there are limitations that simple sargability concerns can resolve.

A spatial index is good for finding locations in a particular area. It's not good for working out which distance is shortest.

So... when trying to find the closest item, start with a small distance and find items in that area. Something like:

select id
from dbo.locations
where location.STIntersects(@point.STBuffer(@somedistance)) = 1;

...and slowly increase the value of @somedistance until you find what you're looking for. This will allow Seeks to be done, quickly eliminating locations that are not of interest.


How can I prevent users from overwriting databases when restoring?

If what you're asking is how to prevent a login from restoring over an existing database, then take a look at this reference on BOL for RESTORE:

  Permissions

  If the database being restored does not exist, the user must have
  CREATE DATABASE permissions to be able to execute RESTORE. If the
   database exists, RESTORE permissions default to members of the
   sysadmin and dbcreator fixed server roles and the owner (dbo) of the
   database (for the FROM DATABASE_SNAPSHOT option, the database always
  exists).

  RESTORE permissions are given to roles in which membership information
  is always readily available to the server. Because fixed database role
  membership can be checked only when the database is accessible and
  undamaged, which is not always the case when RESTORE is executed,
  members of the db_owner fixed database role do not have RESTORE
  permissions.

In your case, it sounds like the database exists and users are restoring over it.  In that case, just reiterating what permissions are required, you will need to ensure that your particular logins aren't sysadmin or dbcreator members along with the owner of the database.

What it boils down to is, if you don't want people to be able to carry out certain operations, don't give them the permissions to do such.


Query execution time is random, need to identify the root cause

In general, A primary key constraint creates a clustered index in the table. It's important has at least one index in the table to avoid fullscans.

As you said the table doesn't have any kind of index, nevertheless you're searching in the table with a predicate which has two columns. In my experience, I'll create a non-clustered index to support this query. There's another essential thing, you should include all columns that you're using in the statement select. Include columns are not key columns in the index, soo be careful.

CREATE INDEX idx ON MyTable (column2 ,column3) INCLUDE (Name, Address)

I've already read a lot of pages of this book and I recommend it for you.

Expert Performance Indexing in SQL Server

This another one here is good too.

SQL Server 2017 Query Performance Tuning: Troubleshoot and Optimize Query


The documentation suggests to disable constraint checks:

If the target is a PostgreSQL-compatible database, then you can see foreign key violation errors during the CDC phase. To resolve this error, set the session_replication_role parameter to replica.

If you really want to specify the table order manually, use any of the queries from this question to get a list; for example:
SELECT
    tc.table_schema,
    tc.table_name,
    ccu.table_name AS foreign_table_name
FROM
    information_schema.table_constraints AS tc
    JOIN information_schema.constraint_column_usage AS ccu
    USING (constraint_name, table_schema)
WHERE tc.constraint_type = 'FOREIGN KEY';


What tools are there to generate test data for SQL Server?


you're wrong.

There's nothing 'flat' in the SQLite database file; the file is internally split as pages, with trees and references between them.  A table doesn't' store records sequentially.

Check the file format docs


Finding all sql id, sql text, number of executions, execution time between two AWR snap ids in oracle

You can select from the below views.

Historical SQL statistics: DBA_HIST_SQLSTAT
All SQL text: DBA_HIST_SQLTEXT
Map snap_id to actual time: DBA_HIST_SNAPSHOT


Adding parameters to ORACLE sql case statement

When I run the initial query without the Sample.Status NOT IN ('X','A') I get:

What I trying to get is the same view but without a Sample.Status being 'X' or 'A'.

I'm not sure where in the initial code to put the condition to exclude those sample.status lines.


Finding the last record within three years of the first record with subqueries

My solution makes the following assumptions (please let me know if I have misunderstood your requirements)

SequenceId is like an Identity value and represents the chronological order of student changes.
You want to find the enrollment row for each student where the SequenceId is the MAX(SequenceId) for each student where the SchoolYear is within 3 years of the initial enrollment year for that student.

SET NOCOUNT ON
DROP TABLE IF EXISTS [dbo].[Enrollments];
CREATE TABLE [dbo].[Enrollments](
    [SequenceID] [bigint] NULL,
    [SchoolYear] [int] NULL,
    [StudentID] [varchar](8) NULL,
    [ProgramID] [varchar](10) NULL,
    [DepartureCode] [varchar](2) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (29735, 2012, N'9800384', N'5331', N'10')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (33183, 2013, N'9800384', N'5331', N'01')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (33549, 2013, N'9800384', N'5331', N'01')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (37105, 2014, N'9800384', N'5331', N'01')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (37810, 2015, N'9800384', N'5331', N'22')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (29544, 2012, N'9100926', N'5729', N'01')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (37320, 2014, N'9100926', N'5729', N'01')
GO

;

WITH YearFirstEnrolled
AS (
    SELECT StudentID
        ,min(SchoolYear) AS YearFirstEnrolled
    FROM Enrollments
    GROUP BY StudentID
    )
SELECT *
FROM YearFirstEnrolled fe
JOIN Enrollments e
    ON e.StudentID = fe.StudentID
        AND e.SequenceID = (
            SELECT max(SequenceID)
            FROM Enrollments
            WHERE StudentID = e.StudentID
                AND SchoolYear < fe.YearFirstEnrolled + 3
            )
order by e.SequenceID

| StudentID | YearFirstEnrolled | SequenceID | SchoolYear | StudentID | ProgramID | DepartureCode |
|-----------|-------------------|------------|------------|-----------|-----------|---------------|
| 9800384   | 2012              | 37105      | 2014       | 9800384   | 5331      | 01            |
| 9100926   | 2012              | 37320      | 2014       | 9100926   | 5729      | 01            |

Select your desired columns from the result.


SQL column goes from first to last position in output

In the Columns page on the OLE DB Source, you can use the check boxes beside the columns to change the order.

The columns are added to the output in the order in which they get checked, so what you need to do is uncheck all the columns, and then check them again in the correct order.

Downstream data flow tasks will want to update themselves because the column mappings "broke", but this shouldn't be a big issue to fix.


SSAS Calculated Member Conditional Count

Assuming you already have designed

a measure Total that counts the accidents
a dimension Severity that describes the severity of the accident

you can define a calculated member such as:

([Measures].[Total],[Severity].[Severity].&[Fatal])


memory errors on startup in postgresql 9.6 log - MAP_HUGETLB failed

Own experience suggests that this is due to PostgreSQL running out-of-memory.

For instance, I ran into this error when attempting to bump max_connections up to a very high value (say, 2000) on a "small" server.


What changes SQL Server Compatibility Level changes?

A compatibility upgrade changes the way the SQL Engine works and has the database level improvements/changes from the other versions.  This doesn't mean you can't use features outside of that though.  For example having a DB in a older compatibilty mode doesn't mean you can't use mirroring or AGs.

It does mean the query optimizer, newer system provided functions, and other features will not work the same way or be available in some cases.  You will want to ensure your code operates the same way but your DR/HA and operational solutions shouldn't have changed.

Run your code through the Upgrade Advisor and also read up on the breaking changes.  Read up on best practices for upgrades as well, Thomas LaRock has a good article on it as an example.


Does MSSQL Server need more space than the size of the data itself for importing? How much more?

The error messages are indicating that the log file is full. SQL Server has two files (at least) for every database; one is the data file and one is the log file. On your particular server, it is possible that the log file is on a disk that doesn't have enough space for the log file while the data file may be on a disk where it has plenty of space.
If the database is in Simple recovery mode, simply execute a full backup and the log file will clear. However, you may need to find out where the log file is located and make sure the disk has enough space to house the log file while performing the BCP. You can do this by right-clicking on the database and going to Properties. Then click on Files on the left side. This will show you the physical path for the log file (.ldf).


According to Books Online, a media set is an:

  [O]rdered collection of backup media, tapes or disk files, to which
  one or more backup operations have written using a fixed type and
  number of backup devices.

This comprises an ordered set of backup media, which can be tape, disk files, Azure Blob Storage, etc., but (and this is important) not a combination of two or more of these.

So if you create a media set, your backup media in that media set must be of the same kind.

On to your question about the name of the media set. According to the same article (emphasis added):

  Every volume of backup media (disk file or tape) contains a media
  header that is created when [sic] by the first backup operation that uses
  the tape (or disk). That header remains intact until the media is
  reformatted.

The media set name is assigned by the WITH MEDIANAME keyword, as you surmise. It is not possible to change the name once you have created the first backup media in the set, because it is set by the first backup operation.

You will need to explicitly format the media set if you wish to change its name.