self-joins in recursive CTE: `recursive reference must not appear more than once`? This is tested with PostgreSQL 9.6, but it's a general SQL question.

Is it possible to use self-joins of the recursive table in a recursive CTE (rCTE)?

I tried the following rCTE containing a self-join of the recursive term x,

WITH RECURSIVE
x (id) AS (
  SELECT 1 id UNION ALL SELECT x1.id+x2.id FROM x x1, x x2
  WHERE x1.id < 5 AND x2.id < 5 AND x1.id = x2.id
  )
SELECT * FROM x;

, which hopefully should be equivalent to:

WITH RECURSIVE
x (id) AS (
  SELECT 1 id UNION ALL SELECT id+id FROM x WHERE id < 5
  )
SELECT * FROM x;

But the former rCTE generates an error:

ERROR:  recursive reference to query "x" must not appear more than once
LINE 3:   SELECT 1 id UNION ALL SELECT x1.id+x2.id FROM x x1, x x2

Is there a fundamental reason why the recursive reference must not appear more than once? Or is this just a limitation of the PostgreSQL implementation?

Also, is there a work-around?

Indeed error like recursive reference to query "x" must not appear more than once is some strange restriction applied in postgres. And I made assumption it is because their parser just simple distinguish recursive and non-recursive part of query by present of that table.
Meantime for that present nice workaround - you may use nested CTE (WITH statement), and give another name for such table. For your initial example it will look like:

WITH RECURSIVE
x (id) AS (
  SELECT 1 id
  UNION ALL
  SELECT * FROM (
    WITH x_inner AS ( -- Workaround of error: recursive reference to query "x" must not appear more than once
      SELECT * FROM x
    )
    SELECT x1.id+x2.id
    FROM x_inner x1, x_inner x2
    WHERE x1.id < 5 AND x2.id < 5 AND x1.id = x2.id
  )t
)
SELECT * FROM x;

You could try it in SQL fiddle.


DELETE statement not working I'm running my database on a Sybase ASE 15.7 server.
A simple delete statement fails without printing any error , I presume there is no permissions problem as I used to delete rows by the past.
Here is what I did from an isql console.

1> select count(*) from LOGIN where login="336959686@login.com"
2> go

 -----------
       1

(1 row affected)
1> delete from LOGIN where login="336959686@login.com"
2> go
(0 rows affected)

The row wasn't deleted, am I missing something?

LOGIN table definition :

1> sp_help LOGIN
2> go
 Name  Owner Object_type Object_status Create_date
 ----- ----- ----------- ------------- -------------------
 LOGIN xxx   user table   -- none --   Dec  7 2010  2:13PM

(1 row affected)
 Column_name            Type     Length Prec Scale Nulls Not_compressed Default_name Rule_name Access_Rule_name Computed_Column_object Identity
 ---------------------- -------- ------ ---- ----- ----- -------------- ------------ --------- ---------------- ---------------------- ----------
 login_oidval           numeric       8   16     0     0              0 NULL         NULL      NULL             NULL                            0
 login                  varchar     165 NULL  NULL     0              0 NULL         NULL      NULL             NULL                            0
 creation_date          datetime      8 NULL  NULL     0              0 NULL         NULL      NULL             NULL                            0
 last_modification_date datetime      8 NULL  NULL     0              0 NULL         NULL      NULL             NULL                            0
 idp_oidval             smallint      2 NULL  NULL     0              0 NULL         NULL      NULL             NULL                            0
Object has the following indexes

 index_name index_keys         index_description    index_max_rows_per_page index_fillfactor index_reservepagegap index_created       index_local
 ---------- ------------------ -------------------- ----------------------- ---------------- -------------------- ------------------- ------------
 PK_LOGIN    login_oidval      nonclustered, unique                       0                0                    0 Dec 10 2010  7:10PM Global Index
 login_idx1  login, idp_oidval nonclustered, unique                       0                0                    0 Jun 10 2011  2:55PM Global Index

(2 rows affected)
 index_ptn_name       index_ptn_seg
 -------------------- -------------
 PK_LOGIN_448001596   default
 login_idx1_448001596 default

(2 rows affected)
No defined keys for this object.
 name  type       partition_type partitions partition_keys
 ----- ---------- -------------- ---------- --------------
 LOGIN base table roundrobin              1 NULL

(1 row affected)

 partition_name  partition_id compression_level pages row_count segment create_date
 --------------- ------------ ----------------- ----- --------- ------- -------------------
 LOGIN_448001596    448001596 none                863     25497 default Feb 12 2014  3:01PM

 Partition_Conditions
 --------------------
 NULL

 Avg_pages   Max_pages   Min_pages   Ratio(Max/Avg)              Ratio(Min/Avg)
 ----------- ----------- ----------- --------------------------- ---------------------------
         863         863         863                    1.000000                    1.000000
Table LOB compression level
Lock scheme Datarows
The 'ascinserts' attribute is not applicable to tables with datarow or datapage lock schemes.

 exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap ascinserts
 ------------ -------------- ---------- ----------------- ------------ -----------
            0              0          0                 0            0           0

(1 row affected)
 concurrency_opt_threshold optimistic_index_lock dealloc_first_txtpg
 ------------------------- --------------------- -------------------
                        15                     0                   0
(return status = 0)

Instead of using double-quotes, try using single-quotes:

DELETE
FROM LOGIN
WHERE login = '336959686@login.com';

If the delete statement produces no error but doesn't appear to delete the row affected, that means one of the following:

The criteria (the WHERE clause) is not matching the desired row.
You have a transaction that is rolling back the delete, or has not yet committed the delete.  Check the transaction chaining mode using SELECT @@tranchained;  Perhaps try this:

SELECT *
FROM LOGIN
WHERE login = '336959686@login.com';
BEGIN TRANSACTION
DELETE
FROM LOGIN
WHERE login = '336959686@login.com';
COMMIT TRANSACTION
SELECT *
FROM LOGIN
WHERE login = '336959686@login.com';

Undetected database corruption - this is extremely rare.  Have you performed DBCC CHECKDB lately?  Make certain you have a reliable backup. Test the backup by restoring it onto another instance of Sybase ASE.  If you have done that, running DBCC CHECKDB should be performed. It may heavily impact I/O, so I would run it during non-peak hours.


problems when importing csv files into SQL Server using SSIS when the columns are not consistent from quarter to quarter I am using SSIS to upload a bunch of csv tables into SQL Server Express.  The csv files are quarterly data files.  They are largely consistent but every couple of years a column may be added or an existing column deleted.
Every time SSIS encounters a change in the columns present it throws one or both of these errors:

Error: 0xC0202055 at Data Flow Task, Flat File Source [2]: The column delimiter for column "XYZ" was not found.
Error: 0xC0202091 at Data Flow Task, Flat File Source [2]: An error occurred while skipping data rows.

When a new column is added, both errors appear.  When an existing column is excluded, only the 2091 error appears.  Note that the columns are not just null when they are deleted, they no longer in the data.
Is there setting or something I can use so that SSIS accommodates these changes in columns?  I've tried setting up the target table with the complete set of columns but that hasn't helped.
Thanks,

You can use a script transformation to create the actual column mapping. For your flat file source, make every output line become a single large column.

In your script transformation, create column definitions for each column you need mapped / already know about.

This assumes your first line contains headers:

Then, in your script transformation , split each row by the delimiter you need. You can create a boolean value indicating if the first line has been read yet. If not, parse the line and store the column mapping positions in a Dictionary<string,int> such as ColumnMappings and do not write to OutputBuffer. If your row reader sees that the column mappings have been read, then you can set your output to the correct column with something like:

 OutputBuffer.MyMappedColumn = SplitRow[ColumnMappings["MyMappedColumn"]];

An example of an entire transformation with a column "Day" is below

using System;
using System.Data;
using System.Text;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent {
    UTF8Encoding enc = new UTF8Encoding();
    char[] separator = { ',' };
    bool _isColumnMappingComplete = false;
    System.Collections.Generic.Dictionary<string, int> _columnMapping;
    bool isWritable = false;

    bool isColumnMappingComplete { //Set true after first row processed
        get{ return _isColumnMappingComplete; }
        set{ _isColumnMappingComplete = value;}   }

    System.Collections.Generic.Dictionary<string, int> columnMapping{ //Object to hold column mapping indexes
        get {
            if (_columnMapping == null) { _columnMapping = new System.Collections.Generic.Dictionary<string, int>(); }
            return _columnMapping; }
        set {
            _columnMapping = value;
            isColumnMappingComplete = true; } }

    public override void PreExecute() {
        base.PreExecute(); /* Add your code here for preprocessing or remove if not needed */ }

    public override void PostExecute()    {
        isWritable = true;
        base.PostExecute();
        /* Add your code here for postprocessing or remove if not needed
          You can set read/write variables here, for example: Variables.MyIntVar = 100 */  }

    public override void Input0_ProcessInputRow(Input0Buffer Row)    {
        bool isEmptyRow = false;
        //byte[] rawRowBytes = Row.Column0.GetBlobData(0,(int)Row.Column0.Length); //uncomment if using Text input datatype
        //string rawRow = enc.GetString(rawRowBytes); //uncomment if using Text input datatype
        string rawRow = Row.Column0; //comment out if using Text input datatype
        if (rawRow.Replace("\"", "").Replace(",", "").Replace(" ", "").Length == 0)
        { isEmptyRow = true; }
        if (!isEmptyRow) {
            string[] rowData = rawRow.Split(separator);

            if (!isColumnMappingComplete) {
                System.Collections.Generic.Dictionary<string, int> firstColMap = new System.Collections.Generic.Dictionary<string, int>();
                for (int i = 0; i < rowData.Length; i++)
                { firstColMap.Add(rowData[i], i); }
                columnMapping = firstColMap;    }
            else {
                Output0Buffer.AddRow();
                bool _rowError = false;
                //Day
                if (columnMapping.ContainsKey("Day"))
                { Output0Buffer.Day = rowData[columnMapping["Day"]]; }
                else
                { //Output0Buffer.Day = "-";
                    _rowError = true; }   }      }    }

    public override void CreateNewOutputRows()
    {   /* Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
         * For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput". */ }     }


Query with INNER JOIN takes long time than expected? I have the following query, which takes longer than expected.
Anybody help me find a faster alternative?

SET SESSION MAX_JOIN_SIZE=18446744073709551615;

SELECT
    claims.claim_id,
    claims.lease_id,
    claims.accident_date,
    claims.insurer_reference,
    claims.status,
    claims.loss,
    claims.responsibility,
    claims.age,
    claims.collected_date,
    motor.make,
    motor.model,
    motor.YOM,
    estimations.workshop,
    estimations.labour_charges AS EstLabour,
    estimations.spare_parts AS EstSpares,
    estimations.lump_sum AS EstLsum,
    estimations.towing_charges AS EstTowing,
    estimations.total AS EstTotal,
    MIN(approvalspl.approval_date) AS PLAppDate,
    SUM(approvalspl.labour_charges) AS PLAppLabour,
    SUM(approvalspl.spare_parts) AS PLAppSpares,
    SUM(approvalspl.lump_sum) AS PLAppLsum,
    SUM(approvalspl.total_amount) AS PLAppTotal,
    approvalstl.approval_date AS TLAppDate,
    SUM(approvalspl.towing_charges) AS PLAppTowing,
    approvalstl.sum_insured AS sum_insured,
    approvalstl.total_amount AS TLAppTotal,
    approvalspl.estimation_id,
    SUM(approvalspl.deductible) AS PLDeductible,
    approvalstl.deductible AS TLDeductible,
    approvalstl.towing_charges AS TLAppTowing,
    claims.reason,
    motor.SI,
    policies.policy_key,
    claims.city,
    claimsnotes.notes,
    motor.chasis_number,
    claims.tp_payment,
    claims.tp_recovery
FROM claims
LEFT OUTER JOIN motor ON claims.lease_id = motor.lease_id
LEFT OUTER JOIN estimations ON claims.claim_id = estimations.claim_id
LEFT OUTER JOIN approvalspl ON estimations.estimations_id = approvalspl.estimation_id
LEFT OUTER JOIN approvalstl ON estimations.estimations_id = approvalstl.estimation_id
INNER JOIN policies ON motor.policy_id = policies.policy_id
LEFT OUTER JOIN claimsnotes ON claims.claim_id = claimsnotes.claim_id
GROUP BY claims.claim_id

Try adding all columns that are not in a MIN, MAX or SUM to the GROUP BY


Fact Table with blank dimensions Can I use blank dimensions in a fact table?

For instance, total sales in Florida are 200.

Sales    City    State
100      Miami
100      Tampa
200              Florida

Reason why is the total for the city may have metrics, like number of salespeople only available in city records. Total for state may have metrics, like expenses, only available in state, and no real way to break it down to cities.

This is probably a bad idea, and definitely not standard good practice.

In a typical fact/dimension schema for a data warehouse, based on Ralph Kimball's methodology, the fact tables should always store data at the most granular level possible. Your example fact table is storing data at two different levels of granularity. This is bad, if only because it's repeating the same fact twice. Fact tables are intended to get very deep (many rows) and duplication by design will not scale well. I'm going to answer your question, but also show how you can avoid that duplication.

Another principle of fact table design is that they should be as narrow as possible (few columns), and the columns there are should be as compact as possible (least storage space). This minimises the storage impact of tables with so many rows.

So storing strings in the fact table should be avoided: instead, store an integer foreign key to a dimension table, that holds the larger string values. Each string is thus only stored once (in the shallow, few rows, dimension table), with the smaller int being repeated many times in the deep fact table.

Once you decide that, it follows that actually, you only need one foreign key dimension ID column to represent both your City and State columns - because if you know the city, you inherently know the state too, each city is only in one state. These descriptors (and Country) should be columns in the Location (or Geography, or Territory, or whatever) dimension. The metrics you refer to also belong in columns in the dimension table. The dimension has one primary key per City.

Fact table

LocationId    Sales
1             100
2             100

Location dimension table

LocationId    City        State        Country        SalesPeopleInCity    StateExpenses
1             Miami       Florida      USA
2             Tampa       Florida      USA

Your example fact table now needs only the first two rows - duplication problem gone! If you want to find total sales for Florida, you join the fact table to the dimension table, restrict on the State column, and group up to find the sum of sales.

(Aside: with a good indexing strategy on a decent database platform, that probably won't take long at all, even if your fact table gets really big (millions/billions of rows). If lots of people need to find lots of different totals lots of times...? That's why pre-aggregated OLAP cubing technologies exist.)

It's much more acceptable for the dimension table to contain blanks (or nulls) in some columns if need be, and also to contain repeated values. The rationale for this is that the dimension table never has that many rows relative to the fact table: storage is not a problem. Because this is not a transactional system database, consistency is also not a problem.

So your metrics that apply only to State level can be repeated for every applicable City row in the dimension table. You don't need any blanks at all in this simple example. By repeating the values on every applicable row, you can always get the State-level value instantly no matter which City is being analysed.

Location dimension table

LocationId    City        State        Country        SalesPeopleInCity    StateExpenses
1             Miami       Florida      USA            6                    $2500
2             Tampa       Florida      USA            2                    $750

This is all assuming you wish to use a simple "star schema" data model. An alternative way to model this would be using a "snowflake schema", treating the location dimension as a hierarchy and sorting data about each level of the hierarchy (City, State, Country) in a different table. Metrics would then go in the appropriate level table, or perhaps in some generic attribute (key/value) table.

There are pros and cons to each approach and it depends mainly on how you will use the data.


What does “conn” mean in mongostat utility? I'm troubleshooting an application issue that uses Mongo. I've just started to use the mongostat utility. Does the "conn" column show the number of established, authenticated TCP connections to the database, or does it show the number of database connections that are actually running an operation against the Mongo database?

Ref: https://docs.mongodb.com/manual/reference/program/mongostat/

The total number of open connections, meaning all authenticated connections.  They can be running,  runnable, waiting or in sleeping state.


mariadb server optimization for a db of 6GB size I have a database I am trying to configure but I am struggling to get the settings right. I keep changing the settings but I always get the same slow performance.

the mysql database has about 50 tables
2 of the tables have about 10.000.000 rows each.
total size of the database is 6GB. Of those 6GB about 3GB are indexes (because of those 2 tables mainly where most operations occur)
This db is expected to get about 1 million rows more per month added. (there is an import procedure of new records every morning)
Other than that is mostly reading and manipulating data to present to the users.
As this is for a web app, there are only 4 clients using it.

on our new server i have a VM with 64GB of RAM and 16 cores.
on our previous server we had 16GB RAM and 8 cores.

on the previous server i had those settings only :

max_binlog_size   = 100M
binlog_do_db            = equidata
relay-log = /data/mysql/mysql-relay-bin

innodb-buffer-pool-size        = 8G
innodb_log_buffer_size         = 512M
innodb_buffer_pool_instances = 8
join_buffer_size = 2G
thread_cache_size = 50
query_cache_size = 4G
query_cache_limit = 4G

Performance is ok, but until the tables are cached is a bit slow, I believe it could be lot faster.
on the new server with the same settings I get about the same performance. using mysqltuner or the percona online advisor and following the advices, it does not get any better whatever i change.

there are many queries that are not using indexes properly, and have optimized most of them.
when the queries that run to present data are appearing to be slow, I cannot see any spikes on the new server, it is like it is lagging, no activity on cpu and ram..

whenever i use suggestions from mysqltuner I end up with the warning that mysql maximum memory usage is dangerously high etc (because it suggest me to increase the join buffers so for example : Total buffers: 10.6G global + 2.0G per thread (151 max threads)

I am stuck with this, and I ve been trying to fix it for sometime but I always leave it for later as I have huge workload programming the application, but now I am at the point where it affects the application and we got this new big server.

I am open to suggestions for settings. I understand that the queries with no indexes is a major thing, but I am looking at the best optimal configuration for my case on this instance.

This is really bad:

query_cache_size = 4G

It should not be more than about 50M

How much RAM do you have; this should not be more than about 1%:

join_buffer_size = 2G

This is not bad, but most people do just fine with 8M; let's compromise at 16M...

innodb_log_buffer_size         = 512M

The main setting that you should tune is innodb_buffer_pool_size; it should be up to about 70% of RAM (so, about 40G), but does not need to be much bigger than the 6GB that you have in data+index (so 8G is fine for now).

If this VM is dedicated to MySQL, you are paying for more RAM than you need.

If you are chewing up the CPU(s), then look to the queries, indexes, and schema.  Let's see the slowest query and SHOW CREATE TABLE.


MySQL Query Failed: Expression #6 of SELECT list is not in GROUP BY clause and contains non aggregated column fixed bug reappear every restart MySQL Query Failed: Expression #6 of SELECT list is not in GROUP BY clause and contains nonaggregated column

This bug is fixed using below query. by logging into MySQL console as root user

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

But the problem is that this bug reappears every time i restarts the system.
How to fix this?

That's not a bug. That's 'move to standard'.

Do not fix this problem by change server's SQL Mode. Fix it by altering the queries which uses partial GROUP BY expressions.

Details can be read in MySQL Handling of GROUP BY.


"Warning: The join order has been enforced because a local join hint is used" returned with no Join hints I am running the below (homemade) analysis query, looking at wait states, memory usage, and other info for queries running on the current server.

Select GetDate() as RunDate,
    spid,
    blocked,
    waittype,
    lastwaittype,
    waittime,
    sp.dbid,
    uid,
    status,
    hostname,
    program_name,
    cmd,
    nt_domain,
    nt_username,
    loginame,
    sp.sql_handle,
    stmt_start,
    stmt_end,
    session_id,
    dop,
    request_time,
    grant_time,
    requested_memory_kb,
    granted_memory_kb,
    required_memory_kb,
    used_memory_kb,
    max_used_memory_kb,
    query_cost,
    ideal_memory_kb
    ,SUBSTRING (qt.text, stmt_start/2+1, (
        CASE
            WHEN stmt_end = -1
                THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 + 1
            ELSE stmt_end
        END - stmt_start)/2) AS [Individual Query]
    ,[Parent Query] = qt.text

FROM sys.dm_exec_query_memory_grants mg
INNER JOIN sys.sysprocesses sp
    ON mg.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) as qt
WHERE sp.spid != @@SPID
ORDER BY requested_memory_kb

When I run this and there are no currently running queries, I receive the warning message:
    Warning: The join order has been enforced because a local join hint is used
As you can see, there are no join hints in the above code. This is only annoying in my context, but is this an annoying SQL Server bug, is this a known thing, or is it something else?

SQL version: Microsoft SQL Server 2012 (SP4) (KB4018073) - 11.0.7001.0 (X64)
Running on VMWare. Running in SSMS 2016 (Version 13.0.16106.4)

You can get the definition of that DMV by running the following T-SQL:

EXEC sp_helptext 'sys.dm_exec_query_memory_grants'

Which shows that a join hint is being used in the underlying view definition:

CREATE VIEW sys.dm_exec_query_memory_grants AS
 SELECT A.session_id, A.request_id, A.scheduler_id, A.dop, A.request_time, A.grant_time,
 A.requested_memory_kb, A.granted_memory_kb, A.required_memory_kb, A.used_memory_kb, A.max_used_memory_kb,
 A.query_cost, A.timeout_sec,
 convert(smallint, A.is_small) as resource_semaphore_id,
 B.queue_id, B.wait_order, B.is_next_candidate, B.wait_time_ms,
 A.plan_handle, A.sql_handle,
 A.group_id, A.pool_id, A.is_small, A.ideal_memory_kb
 FROM OpenRowset(TABLE DM_EXEC_QE_GRANTSINFO) A
 LEFT OUTER LOOP JOIN OpenRowset(TABLE DM_EXEC_QE_GRANTWAITERS) B
 ON A.session_id=B.session_id and A.request_id=B.request_id and A.sql_handle=B.sql_handle and A.plan_handle=B.plan_handle

Specifically it's the "LOOP JOIN" hint that is triggering the join order warning.  Check out these Microsoft docs on join hints:

  If a join hint is specified for any two tables, the query optimizer automatically enforces the join order for all joined tables in the query...

So even though the view just has a hint to enforce a particular join strategy (loop), a join order hint is automatically applied.


Consistent Prefix Reads In the book Designing Data-Intensive Applications, the author suggests that replication lag can lead to an anomaly were causality is violated. In the example that the author uses, the system is both replicated and partitioned. I was wonder if the violation of causality - what the author terms "consistent prefix reads" - is a problem in non-partitioned systems as well.
P.S. I want to expand on this. In a single leader replicated system, the single leader acts as a synchronization point. All follower replicas will see the writes in the correct order, so I do not see how a third-party observer would see the writes in the wrong order. I am starting to arrive at the conclusion that causality can be violated in a multi-leader replicate system because the lag between L2 and F1 can be larger than L1 and F1.

Yes, attaining consistent prefix reads is still a problem in replicated non-partitioned systems (or for reads within a single partition of a partitioned replicated system which gets the same treatment as a non-partitioned replicated system for this issue) IF replication is asynchronous or semi-synchronous.
A single client could read from follower1 and then make a subsequent read to follower2. If follower2 is lagging, the second read of data from that partition may reflect an older state of your data model than the first read of data served by follower1. So any business logic that requires the causality sequence could become erroneous.
Now, this leads to the next point: if you achieve the monotonic reads guarantee for the client, a concept usually discussed in the context of reading from a single partition or unpartitioned system, then you also get the consistent prefix reads guarantee under the single-partition assumption. The way to do this is to force the same clientID to read from the same replica, or only have a single replica (the leader), or force all the replication to be synchronous. If you have multiple partitions, on the other hand, achieving monotonic reads per partition still don't guarantee you consistent prefix reads, which is the point Martin makes in the book.
To correct what you said:

In a single leader replicated system, the single leader acts as a synchronization point.

No, the single leader is only a synchronization point for writes, but could be a synchronization point for both reads+writes if those reads are forced to go through the leader. When doing transactions involving both read/writes, you want to go through the leader. When doing reads where stale data is fine, you would want to make use of your replicas.

All follower replicas will see the writes in the correct order, so I do not see how a third-party observer would see the writes in the wrong order.

No, as per my example, even though replicas eventually see all writes in the correct order, the client can read from different replicas and see the writes out of order unless you constrain otherwise to achieve monotonic reads.

I am starting to arrive at the conclusion that causality can be violated in a multi-leader replicate system because the lag between L2 and F1 can be larger than L1 and F1.

Yes but also in a single-leader replicated system as I hope I have explained properly above.


Sum Interval of Dates within same column How do you best sum the differences of a range of dates in the same column between interleaving rows? I have a Datetime column and want to calculate the difference between Rows. I want the difference in Seconds. This questions is not about how to get a difference between 2 timestamps, but is more focused on how to most efficiently calculate between rows on the same table. In my case each row has a datetime eventype that links 2 rows together logically.

Details Related to how to group the eventtypes of start and end. ( Andriy M's Question)
Starts and Ends "should" be consecutive. If a Start does not have a subsequent end it should be left out of the sum. Moving to the next Start to see if it has a end. Only consecutive Start - End Pairs should be added to the sum of the total seconds.

Working in postgresql 9.x...

Example data in table;

eventtype, eventdate
START, 2015-01-01 14:00
END, 2015-01-01 14:25
START, 2015-01-01 14:30
END, 2015-01-01 14:43
START, 2015-01-01 14:45
END, 2015-01-01 14:49
START, 2015-01-01 14:52
END, 2015-01-01 14:55

Note,
All Start and End Dates will be sequential.

Here's my first attempt. Seems to be working.

SELECT
-- starts.*
SUM(EXTRACT(EPOCH FROM (eventdate_next - eventdate))) AS duration_seconds
FROM
(
    WITH x AS (
        SELECT *, dense_rank() OVER (ORDER BY eventdate) AS rnk
        FROM   table
        AND eventdate > '2015-01-01 00:00:00.00'
        AND eventdate < '2016-01-01 23:59:59.59'
        )
    SELECT x.eventdate, x.eventtype, y.eventdate AS eventdate_next,  y.eventtype AS eventtype_next
    FROM   x
    LEFT   JOIN (SELECT DISTINCT eventdate, eventtype, rnk FROM x) y ON y.rnk = (x.rnk + 1)
    ORDER  BY x.eventdate
) starts
WHERE
eventtype = 'START'
GROUP BY eventtype

My first attempt is based on a great example from stackoverflow
Postgres 9.1 - Getting the next value

Note; You can comment the GROUP BY and the SUM and un-comment the starts.* to get a record for each individual duration going into the sum.

You can use the LEAD analytic function to obtain the next row's eventtype and eventdate alongside the current row's data:

SELECT
  eventtype,
  eventdate,
  LEAD(eventtype) OVER (ORDER BY eventdate) AS nexttype,
  LEAD(eventdate) OVER (ORDER BY eventdate) AS nextdate
FROM
  atable
WHERE
      eventdate >= '2015-01-01 00:00:00.00'
  AND eventdate <  '2016-01-01 23:59:59.59'

Using the above query as a derived table, you can filter the output further on eventtype = 'START' AND nexttype = 'END' and get the difference total:

SELECT
  SUM(EXTRACT(EPOCH FROM (nextdate - eventdate))) AS duration_seconds
FROM
  (
    SELECT
      eventtype,
      eventdate,
      LEAD(eventtype) OVER (ORDER BY eventdate) AS nexttype,
      LEAD(eventdate) OVER (ORDER BY eventdate) AS nextdate
    FROM
      atable
    WHERE
          eventdate >= '2015-01-01 00:00:00.00'
      AND eventdate <  '2016-01-01 23:59:59.59'
  ) AS s
WHERE
      eventtype = 'START'
  AND nexttype  = 'END'
;

As a slight variation, you can implement the subquery as a CTE:

WITH cte AS
  (
    SELECT
      eventtype,
      eventdate,
      LEAD(eventtype) OVER (ORDER BY eventdate) AS nexttype,
      LEAD(eventdate) OVER (ORDER BY eventdate) AS nextdate
    FROM
      atable
    WHERE
          eventdate >= '2015-01-01 00:00:00.00'
      AND eventdate <  '2016-01-01 23:59:59.59'
  )
SELECT
  SUM(EXTRACT(EPOCH FROM (nextdate - eventdate))) AS duration_seconds
FROM
  cte
WHERE
      eventtype = 'START'
  AND nexttype  = 'END'
;

This rewrite can have implications for performance, because unlike a derived table, a CTE is materialised in PostgreSQL. Testing should reveal if there is a difference and, if so, which option is better for you.


Multi-Tenant Design with Single DB & Single Schema approach using Sql Server How would you design the tables which should isolate the data using TenantId in a single table? I came up with two approaches, written down the has pros and cons with my little experience, but I would like to to ask opinion who have successfully implemented.

Tenant details are stored in Tenant table.
Customer is unique to each tenant and gets stored in Customer table.
Sales Header & Item details are tightly associated to Customer.

Common tables

CREATE TABLE Tenant(
TenantId SMALLINT IDENTITY(1,1) NOT NULL,
TenantName VARCHAR(32) NOT NULL
)

CREATE TABLE Customer(
CustomerId INT IDENTITY(1,1),
TenantId SMALLINT NOT NULL,
CustomerName VARCHAR(32) NOT NULL
)

Approach 1:

Pros:

Save 2 bytes for each record from SalesOrder and SalesOrderItem table by eliminating the TenantId column.

Cons:

To query the Sales Order must always join the Customer table to identify the TenantId.

CREATE TABLE SalesOrder (
SalesOrderId INT IDENTITY(1,1),
CustomerId INT NOT NULL
)
CREATE TABLE SalesOrderItem (
SalesOrderItemId INT IDENTITY(1,1),
SalesOrderId INT NOT NULL
)

Approach 2:

Pros:

Data is nicely segregated by TenantId column in SalesOrder and SalesOrderItem.

Cons:

Waste extra 2 bytes for each record on both tables.

CREATE TABLE SalesOrder (
SalesOrderId INT IDENTITY(1,1),
TenantId SMALLINT NOT NULL,
CustomerId INT NOT NULL
)

CREATE TABLE SalesOrderItem (
SalesOrderItemId INT IDENTITY(1,1),
TenantId SMALLINT NOT NULL,
SalesOrderId INT NOT NULL
)

As you may notice the pros and cons are just reversed for each approach, however I may not be the experienced person to write down the pros and cons in details for each approach. It would be nice, if someone with a similar experience can highlight some things which am totally missing here.

I hope, as the Tenants increase in the future it would be good to create Partitions based on TenantId and move their data into other drives using FileGroup approach in Sql Server.

I would go with including Tenant_ID in all tables. Yes, this is denormalized, but with good reason.

In cases like this, it can be critical to ensure that one tenant cannot see another's data. If every query includes the Tenant_ID, that helps ensure this.

Your note on future partitions is another reason (though not critical at this point).

I can think of one other. If one of your tenants required some sort of direct SQL access to the database, you could create views for them for all tables, with WHERE Tenant_ID = 12 (or whatever the appropriate ID is, of course). By granting access to those views, and denying access to the actual tables, you would be able to provide the same security as you do with your application.


LOAD txt file DATA INFILE issues I have this project I am working on, below is the sample of the text file I need to load into my database.

routes.txt:

"route_id","origin","destination","distance"
1,"ABE","ATL",692.000
2,"ABE","DTW",425.000
3,"ABE","ORD",655.000

flights.txt:

"carrier","flight_number","route_id","departure_time","arrival_time"
"AA",43,1051,"1100","1438"
"AA",43,1182,"1523","1730"
"AA",44,3477,"0710","1527"
"AA",45,1921,"1830","2152"

Here are the tables I created:

create table flights(
  route_id INT NOT NULL,
  carrier  char(2)  NOT NULL,
  flight_number int  NOT NULL,
  departure_time date NOT NULL,
  arrival_time date NOT NULL,
  primary key (route_id),
  index(route_id) )

create table routes(
  route_id INT NOT NULL,
  origin   char(3)  NOT NULL,
  destination  char(3)  NOT NULL,
  distance  decimal(8,3) NOT NULL,
  primary key (route_id),
  index(route_id) )

Here is the the commands I executed to load the data:

LOAD DATA infile '/var/lib/mysql-files/routes.txt'  INTO TABLE routes
  fields terminated by ','   lines terminated BY '\n\r' IGNORE 1 LINES
  (route_id,origin,destination,distance);

Query OK, 0 rows affected (0.00 sec)
Records: 0  Deleted: 0  Skipped: 0  Warnings: 0

from the result above you see the data are not loaded.

What could be the reason?

I am using MySQL 5.7

Use LINES TERMINATED BY '\n' or just leave it out altogether.  '\n' is the linefeed character on Linux, and with '\n\r' it will never see a linefeed, so the entire file looks like one long line, and you told it to ignore 1 line, so it sees nothing to insert.

You also need  "OPTIONALLY ENCLOSED BY '"'.  Try something like this:

mysql> LOAD DATA LOCAL INFILE 'routes.txt'
    INTO TABLE routes FIELDS TERMINATED BY ','
      OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM routes;
+----------+--------+-------------+----------+
| route_id | origin | destination | distance |
+----------+--------+-------------+----------+
|        1 | ABE    | ATL         |  692.000 |
|        2 | ABE    | DTW         |  425.000 |
|        3 | ABE    | ORD         |  655.000 |
+----------+--------+-------------+----------+
3 rows in set (0.00 sec)

Obviously, leave off LOCAL if using a file on a remote server and be sure the file is readable by MySQL.


PostgreSQL: Can the archive command for master and standby point to the same directory? I use streaming replication and PITR (WAL files) in my cluster, currently I have different versions of postgresql.conf for the master and the standby server.

The only difference in the files is the archive command, that points to a different directory.

for example, on master I have:

archive_command = 'cp %p /dbckp/server1master/%f'

And on the standby:

archive_command = 'cp %p /dbckp/server2master/%f'

I figure that if the standby is not generating any archives while on 'standby mode' I could use the same directory and have a single postgresql.conf.

In theory this should work.  After all, the files should be identical.  In practice I think it is scary and I wouldn't do it.  I would worry about file locking contention, and also the fact that if I am archiving twice to two locations I get a full backup of the WAL's esp, if I send to different file servers.  If one goes belly up at a bad time, I can still get logs off the other for PITR.

Keep in mind that if something goes wrong with this setup, you can lose PITR ability to roll forward beyond a certain point.  Additionally if you set things up how they are now, you may be able to use file permissions and attributes to prevent overwriting existing parts of existing files which can be very helpful in backup assurance.


best way to create multiple similar tables - CTAS vs INSERT I am creating a few dozen tables as CREATE TABLE AS SELECT (CTAS).  The only difference in the SELECT statements is specifying a different value in the WHERE clause for TEAM_ID.  I'm an Oracle newbie and thought I could use bind variables to speed up the process, but those are not allowed in DDL statements.

My data is quite large - 500+ million rows.

What is my best option for speed?  Currently we are using nologging on the CREATE TABLE statement.  Would it be better to explicitly create the table and INSERT with the /*+ append */ hint and bind variables?  Or do the gains of nologging outweigh the gains of bind variables?

(I am sure this is an "it depends" scenario, but I don't even know what it depends on...)

Use execute immediate to make creation of many very similar tables easier.

A little PL/SQL demo:

SQL>
declare
table_number number;
ctas_sql varchar(4000);
begin
  ctas_sql:='select sysdate as foo from dual';

  for table_number in 1..10
  loop
    execute immediate('create table foo'||table_number ||' as ('||ctas_sql||')');
  end loop;
end;
/
SQL>
PL/SQL procedure successfully completed.

SQL>
SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
FOO1
FOO2
FOO3
FOO4
FOO5
FOO6
FOO7
FOO8
FOO9
FOO10

10 rows selected.

SQL>

The create table statement can contain anything you want - the above is just showing that the statement can be dynamic. So, in your case you'd have simple logic to alter the TEAM_ID and table name for each statement.

Bind variables will offer no gain unless you're executing the same single query multiple times (extremely simplified for the sake of this answer) - in this case you're executing many different queries that are performing large bulk inserts.

Creating tables with NOLOGGING is not advisable if this is a production system. Also be aware that not logging and /*+APPEND*/ can have backup/restore implications. This Ask Tom thread may be of particular interest.

To clarify, using CTAS is the way to go. You only have to parse the CTAS statement once to insert your millions of rows. If you use individual INSERT statements you have to parse each statement over & over again, which adds a massive overhead!


Why didn't MySQL keep the innodb_file_format after loading a mysqldump I was loading a mysql dump today when I ran into

ERROR 1118 (42000) at line 279: Row size too large (> 8126)

Which I thought was weird because I know I had turned on row compression and was using Barracuda on that server. I also knew I wasn't running into that problem on the production database.

I checked on the production database and sure enough

show variables like "%innodb_file%";
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Barracuda |
| innodb_file_per_table    | ON        |
+--------------------------+-----------+

But on the server that loads the dump the file_format variable is set to:

mysql> show variables like "%innodb_file%";
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Antelope  |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Antelope  |
| innodb_file_per_table    | ON        |
+--------------------------+-----------+

Here's what my MySQL dump looks like

sudo mysqldump -u mysqlbackups --databases limbo | gzip > /local_backup_directory'+%m-%d-%Y'.sql.gz

Why wasn't the innodb_file_format and format_max variables dumped? How can I tell mysqldump to dump the file_format and format_max variables?

According to the MySQL Documentation on

innodb_file_format

  The file format to use for new InnoDB tables. Currently, Antelope and Barracuda are supported. This applies only for tables that have their own tablespace, so for it to have an effect, innodb_file_per_table must be enabled. The Barracuda file format is required for certain InnoDB features such as table compression.

innodb_file_format_max

  At server startup, InnoDB sets the value of this variable to the file format tag in the system tablespace (for example, Antelope or Barracuda). If the server creates or opens a table with a “higher” file format, it sets the value of innodb_file_format_max to that format.

Since each of these belong to my.cnf and not the data, the format of InnoDB is agnostic as far as the mysqldump goes. If you want to shift everything to Barracuda on the fly, there are two ways (TEST WHAT I AM ABOUT TO SUGGEST ON A DEV AND STAGING SERVER, PLEASE)

TECHNIQUE #1 : Prepend the Barracuda Option to the mysqldump

Suppose the mysqldump file is called mydump.sql. Do this:

echo "SET GLOBAL innodb_file_format = 'Barracuda';" > mynewdump.sql
cat mydump.sql >> mynewdump.sql

Now, just load the dump

mysql -uroot -p < mynewdump.sql

TECHNIQUE #2 : Set the Barracuda Option Before Loading the Dump

Login to mysql as root@localhost and run this from the mysql prompt

mysql> SET GLOBAL innodb_file_format = 'Barracuda';
mysql> source mynewdump.sql

WARNING

TEST THE SUGGESTIONS ON A DEV AND STAGING SERVER, PLEASE

Give it a Try !!!


How can I pass parameters to an "SQL Script" from isql I am wondering if it is possible to pass parameters to an SQL script from within sybase's isql utility.

For example, I'd like to store a select statement in the file the_script.sql that would look something like

select
   col_1,
   col_2,
   etc
from
   table
where
   cond1 > $param1 and
   cond2 < $param2

Then, with that file, I'd like to "execute" it from isql with

:r the_script.sql 900 20

with the expectation that $param1 is repleaced with 900 and $param2 with 20.

Is there a possibility to achieve what I want?

Looking at your use case, it looks as if you want a stored procedure, so here's one I wrote for you:

CREATE PROC the_script
(
   @param1 int = null,
   @param2 int = null
)
AS
BEGIN
   select
      col_1,
      col_2,
      etc
   from
      table
   where
      cond1 > @param1 and
      cond2 < @param2
END

Now, being in isql, you can run it:

exec the_script 900, 20

Regards.


Building an Inventory Database I am building a small inventory database for copier supplies at my place of work. In the end this database will be automated to subtract taken from the shelves and add any incoming orders. Right now I am having a hard time with creating the tables. I don't have much experience with SQL except for a class I took a while back. I can't think of tables that I would need for this project. Currently we use a manual system with the following information.

Manufacturer
Model
Part #
Reorder Level
Reorder Quantity
On Hand
Quantity on Order

I don't need to know price or anything like that. I think all of that could go on one or two tables. But as stated earlier, I haven't had any experience with SQL in a while and would like some feedback.

I'd consider 3 tables to describe the items, and a table to describe the transactions against those items.  Something like:

CREATE TABLE dbo.Manufacturers
(
    ManufacturerName NVARCHAR(100) NOT NULL
        CONSTRAINT PK_Manufacturers
        PRIMARY KEY CLUSTERED
    , ManufacturerWebsite NVARCHAR(100) NULL
);

CREATE TABLE dbo.Models
(
    ManufacturerName NVARCHAR(100) NOT NULL
    , ModelName NVARCHAR(100) NOT NULL
    , ModelDescription NVARCHAR(1000) NOT NULL
    , CONSTRAINT PK_Models
        PRIMARY KEY CLUSTERED
        (ManufacturerName, ModelName)
    , CONSTRAINT FK_Models_Manufacturer
        FOREIGN KEY (ManufacturerName)
        REFERENCES dbo.Manufacturers(ManufacturerName)
);

CREATE TABLE dbo.Parts
(
    ManufacturerName NVARCHAR(100) NOT NULL
    , ModelName NVARCHAR(100) NOT NULL
    , PartNumber NVARCHAR(100) NOT NULL
    , ReorderLevel INT NOT NULL
    , ReorderQuantity INT NOT NULL
    , CONSTRAINT PK_PartNumbers
        PRIMARY KEY CLUSTERED
        (ManufacturerName, ModelName, PartNumber)
    , CONSTRAINT FK_PartNumbers_Model
        FOREIGN KEY (ManufacturerName, ModelName)
        REFERENCES dbo.Models(ManufacturerName, ModelName)
);

The above tables can contain whatever extra columns you need that make sense for each table.  Perhaps the manufacturer's phone number, or perhaps where you order parts from, etc.

Here is the table where you keep track of when items were consumed or received:

CREATE TABLE dbo.Transactions
(
    TransactionID INT NOT NULL
        CONSTRAINT PK_Transactions
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , ManufacturerName NVARCHAR(100) NOT NULL
    , ModelName NVARCHAR(100) NOT NULL
    , PartNumber NVARCHAR(100) NOT NULL
    , TransactionDate DATETIME NOT NULL
    , TransactionDescription NVARCHAR(1000) NOT NULL
    , Quantity INT NOT NULL
    , CONSTRAINT FK_Transactions_Parts
        FOREIGN KEY (ManufacturerName, ModelName, PartNumber)
        REFERENCES dbo.Parts(ManufacturerName, ModelName, PartNumber)
);

To show this design, we'll insert some sample data:

INSERT INTO dbo.Manufacturers (ManufacturerName, ManufacturerWebsite)
VALUES ('HP', 'www.hp.com');

INSERT INTO dbo.Models (ManufacturerName, ModelName, ModelDescription)
VALUES ('HP', 'LaserJet II', 'Laser printer');

INSERT INTO dbo.Parts (ManufacturerName, ModelName, PartNumber, ReorderLevel, ReorderQuantity)
VALUES ('HP', 'LaserJet II', 'Letter Paper', 10, 5);

INSERT INTO dbo.Transactions (ManufacturerName, ModelName, PartNumber, TransactionDate, TransactionDescription, Quantity)
VALUES ('HP', 'LaserJet II', 'Letter Paper', GETDATE(), 'Received Paper', 5);

INSERT INTO dbo.Transactions (ManufacturerName, ModelName, PartNumber, TransactionDate, TransactionDescription, Quantity)
VALUES ('HP', 'LaserJet II', 'Letter Paper', GETDATE(), 'Received Paper', 15);

INSERT INTO dbo.Transactions (ManufacturerName, ModelName, PartNumber, TransactionDate, TransactionDescription, Quantity)
VALUES ('HP', 'LaserJet II', 'Letter Paper', GETDATE(), 'Consumed Paper', -6);

INSERT INTO dbo.Transactions (ManufacturerName, ModelName, PartNumber, TransactionDate, TransactionDescription, Quantity)
VALUES ('HP', 'LaserJet II', 'Letter Paper', GETDATE(), 'Consumed Paper', -8);

INSERT INTO dbo.Transactions (ManufacturerName, ModelName, PartNumber, TransactionDate, TransactionDescription, Quantity)
VALUES ('HP', 'LaserJet II', 'Letter Paper', GETDATE(), 'Consumed Paper', -5);

This shows how you can see a running total and when you need to reorder:

SELECT t.ManufacturerName, t.ModelName, t.PartNumber
    , t.TransactionDate
    , t.TransactionDescription
    , t.Quantity
    , Balance = SUM(t.Quantity) OVER (PARTITION BY t.ManufacturerName, t.ModelName, t.PartNumber ORDER BY t.TransactionDate ROWS UNBOUNDED PRECEDING)
    , NeedToReorder = CASE WHEN SUM(t.Quantity) OVER (PARTITION BY t.ManufacturerName, t.ModelName, t.PartNumber ORDER BY t.TransactionDate ROWS UNBOUNDED PRECEDING) < p.ReorderLevel THEN 'X' ELSE '' END
    , QuantityToOrder = CASE WHEN SUM(t.Quantity) OVER (PARTITION BY t.ManufacturerName, t.ModelName, t.PartNumber ORDER BY t.TransactionDate ROWS UNBOUNDED PRECEDING) < p.ReorderLevel THEN p.ReorderQuantity ELSE 0 END
FROM dbo.Transactions t
    INNER JOIN dbo.Parts p ON t.ManufacturerName = p.ManufacturerName
        AND t.ModelName = p.ModelName
        AND t.PartNumber = p.PartNumber
ORDER BY t.TransactionID;

The output from the above query:


PostgreSQL: Performance: ORDER BY an indexed column is slowing down the query performance SELECT s.id, s.name, s.updated_at, COUNT(*) OVER ()
FROM screens s
INNER JOIN theatres t ON s.theatre_id = t.id
LEFT JOIN company_screen_mappings AS csm ON csm.screen_id = s.id AND csm.is_deleted = FALSE AND csm.company_id = 'f5003aa3-3621-498a-ab0f-526706fda88f'
WHERE 1=1 ORDER BY s.name asc  LIMIT 50 OFFSET 0

Here is the query plan:
https://explain.depesz.com/s/UC9B
Here are the indexes in the associated tables
CREATE INDEX idx_screens_name ON public.screens USING btree (name);
CREATE INDEX idx_screens_number ON public.screens USING btree (((data ->> 'number'::text)));
CREATE INDEX idx_screens_tags ON public.screens USING gin (tags);
CREATE INDEX idx_screens_theatre_id ON public.screens USING btree (theatre_id);

CREATE INDEX idx_theatre_companies ON public.theatres USING gin (company_ids);
CREATE INDEX idx_theatres_chain_name ON public.theatres USING btree ((((data -> 'chain'::text) ->> 'name'::text)));
CREATE INDEX idx_theatres_city_name ON public.theatres USING btree (((((data -> 'address'::text) -> 'city'::text) ->> 'name'::text)));
CREATE INDEX idx_theatres_country_name ON public.theatres USING btree (((((data -> 'address'::text) -> 'country'::text) ->> 'name'::text)));
CREATE INDEX idx_theatres_name ON public.theatres USING btree (name);
CREATE INDEX idx_theatres_province_name ON public.theatres USING btree (((((data -> 'address'::text) -> 'province'::text) ->> 'name'::text)));
CREATE INDEX idx_theatres_qwc_is_active ON public.theatres USING btree (qwc_is_active);
CREATE INDEX idx_theatres_tags ON public.theatres USING gin (tags);

CREATE UNIQUE INDEX company_screen_mappings_key ON public.company_screen_mappings USING btree (company_id, screen_id) WHERE (is_deleted = false);

The moment I remove ORDER BY s.name the query is considerably fast.
However, s.name is indexed, why is this happening?
Is there a way I could optimize the query?
Please suggest.
I need the result ordered by name.
I could notice that removing COUNT(*) OVER() greatly improves the query execution time. But I need the COUNT as well.

It is normal that retrieving a sorted result is more effort.
If you want the total count, all rows have to be scanned, even if you only return the first 50.
You could try order by name || '' to see if a sort would be faster.
My recommendation is not to retrieve the total count together with the query.
The optimizer's estimates are good in this case, so you should use an approximate result count by running
EXPLAIN (FORMAT JSON) SELECT ...

and retrieving the Plan Rows.


Auto increment field on update I have a field called: update_count, which I need to reflect the number of times a row has been updated.
I have been looking into the SERIAL type for the column which sounds promising, but it has one problem - it is not transaction safe, which means that it may get out of sync. This may not happen.
My question is then: "How to handle an auto incrementing field on each column while supporting multiple concurrent client?"
Environment: Spark.

Auto-increment (aka "identity") columns are something completely different than "a column indicating the number of updates to a row".
Even if a sequence (the "magic" behind identity columns) did work without gaps, you still have a single sequence for the entire table, not for each row.
If you want to track how often a row was updated, create a trigger:
create or replace function increment_update_count()
  returns trigger
as
$$
begin
  new.update_count := new.update_count + 1;
  return new;
end;
$$
language plpgsql;

And create the corresponding trigger:
create trigger trigger_track_upate_count
  BEFORE update on the_table --<< change your table name here
  for each row
  execute procedure increment_update_count();


What is fragmenting my index on a table with stable traffic? I am using Ola Hallengren's solution for optimizing indexes. I run it on Sunday every week. The index has had low fragmentation for the last 6 months and it hasn't needed reorganization. The way it's being used hasn't changed either.

For the last three weeks every time it runs it reports that the clustered index on the database's biggest table is getting between 5-12% fragmented. Strangely enough this is not occurring during weekdays, so it must be there sometime before the maintenance job runs.

My table has a timestamp on every row so I know that the traffic has stayed on the same level for months. Usually it's under 1% fragmentation per month.

I have two questions:

What else could be fragmenting my index?
Is there an automated solution I can set up to track changes? Ideally something not disruptive since this is a production box.

The table has 134 columns contains 14gb worth of data, and the primary key is not an identity (sigh)

The index in question looks like this:

Average row size: 1413
Depth: 4
Leaf-level rows: 9884500
Maximum row size: 2303
Minimum row size: 746
Pages: 1904907
Partition ID: 1

CREATE UNIQUE CLUSTERED INDEX [FOOINDEX] ON [dbo].[FOOTABLE]
(
   [FOONO] ASC,

   [ID] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,  IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,

ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

In General

Just to be clear at the start: fragmentation refers to when the next logical data page (i.e. the values in the fields for 1 or more rows), is not the next physical data page (the location of page in the data file).

Non-fragmented pages (in Physical Page order):

Logical Page 1: A1, A2, A3, A4
Logical Page 2: B1, B2, B3, B4
Logical Page 3: C1, C2, C3, C4

Fragmented pages (in Physical Page order):

Logical Page 1: A1, A2, A3, A4
Logical Page 3: C1, C2, C3, C4
Logical Page 2: B1, B2, B3, B4

In a general sense, fragmentation is caused by:

Data inserted out of order (the order of the index in question).

Using ever-increasing values (e.g. an IDENTITY field) is one way to mitigate this as rows will always be created at the end of the index (assuming an ASC sort order).
FILLFACTOR is another means of reducing fragmentation when not using an ever-increasing value. But unlike an ever-increasing value, FILLFACTOR is only a certain amount of space per data page and depending on what data comes in, it might not be enough space, in which case page splits will occur. And depending on the max size of the row, it might be that no amount of reserved space will help (if the rows are over 4030 bytes).

Updates of variable-length fields (e.g. VARCHAR, NVARCHAR) and NULLable fix-length columns marked as SPARSE that change from NULL to a non-NULL value. If the combination of changes of these types of fields increase the size of the row and there is no more room left on the data page, a page split will occur.

FILLFACTOR again helps here as it reserves some amount of space (assuming the row size is small enough to allow for it) for update operations to increase the size of the row without needing to do a page split.

Deleting enough rows as to remove one, or more, data pages

Additional resources on index fragmentation:

SQL Server Index Fragmentation, Types and Solutions
Index fragmentation with insert/updates, measuring it and fixing it
Visualizing Index Fragmentation

In Particular

For this particular index, the average row size is 1413 so you will only get 5 rows (again, on average) per page. No FILLFACTOR is specified so it would take the system default value (which the default "default" value is 0, meaning to fill the data page). It seems most likely that the values are not ever-increasing given that FOONO is VARCHAR(12). This would give you some amount of fragmentation quite often as new rows come in. Of course, there will be a slight amount of natural reserved space, even without specifying a FILLFACTOR, since the row size is variable. Meaning, after a REINDEX there might be 2000 bytes left on the page as the next row in order was larger than 2000 bytes and could not fit on the page. If a new row comes in that logically fits between those existing rows and is <= 2000 bytes, it should go there. Hence, for these cases, in order to get a sense of the relationship between the values coming in and the increase in fragmentation, you need to identify the new rows, which seems doable given the timestamp field.

It has been confirmed that this table is INSERT-only. That narrows down the source of fragmentation being the new rows coming in. The variance in fragmentation should be a function of a) the distribution of the values of the FOONO field, and b) the width of the new rows. Given that there is a DATETIME field indicating when the rows are inserted, you should be able to query on a per-week basis for the past 6 weeks:

The FOONO field
The ID field
The total size of the variable-length fields in each row (based on the data there; using COALESCE(DATALENGTH(ColumnName), 0) for each of those fields). You are not concerned about the fixed-length fields since they aren't a factor in the increase in fragmentation (unless they are marked as SPARSE and are now coming in as non-NULL values whereas in prior weeks they were typically NULL).

What you are looking for is (comparing the most recent 3, or now 4, weeks with when fragmentation was lower; and be sure to ORDER BY the DATETIME field) at least one of the following:

a more varied distribution of FOONO values
ID values:

a distribution that is interspersed with existing ID values, AND / OR
non-sequential / out-of-order values with respect to chronological INSERT order as denoted by DATETIME timestamp field (hence why the need to do the ORDER BY TimestampField)

a greater overall row size

Any combination of those things happening should account for an increase in fragmentation.

Where the Rubber Meets the Road

The common theme among these 4 possibilities is that they all have the effect of increasing the rate of page splits. Page splits occur when a new row (or, updated row that is larger than it was before, but for now we are only concerned with new rows) should logically be placed on a data page that does not have enough free space to fit the new row. When this happens, SQL Server will (typically) take some amount of the latter portion of the existing data and move it to a new page and add the new row to whichever of the two pages it logically falls into. The end result is that the new page will have at least half, if not slightly more, of the rows in it (I tested several times with a table that holds 10 rows per page and each page split left the existing page with 5 rows and the new page with 6 rows).

The typical page split is just half of the story, though. The other half is what happens when data is added sequentially to these new pages created by the split operations. As new rows are added and the new pages fill up, new pages are created without moving any rows. When this happens the new page is not just in logical order, but also in physical order as compared to the prior page. This is not considered a fragmented page because from the prior page to this new page, they are in both logical and physical order.

Meaning, if we had full pages A0, B0, and C0 and added some rows that would logically go into B0, a page split will occur giving us a new page B1, with some amount of rows on both B0 and B1. But now, if we fill up B1 and keep adding rows in sequential order that logically come before the data on C0, it will (or will often) create a new page B2 for the new rows without moving anything from B1 onto B2. Physically the pages will be ordered as either:

B1, B2, A0, B0, C0

or

A0, B0, C0, B1, B2

The jumps from B0 to B1, and from B2 back to C0, are considered fragmentation as the logical order (A0, B0, B1, B2, C0) does not match the physical order. BUT, the jump from B1 to B2 is not considered fragmentation because the logical order does match the physical order.

Let's put all of this together now. Keeping in mind that the data pages for this particular index are packed fairly tightly (which is fine), most insert operations will have a high probability of being into an already filled page and hence requiring a split. If there are 4 new rows to add if they are spread out (either in terms of the value of FOONO and/or in terms of ID--if new values might be between already existing values) such that they should go into 4 separate pages, that is 4 page splits representing 4 new pages and 8 occurrences of fragmentation (the jump out of order and back into order for each of the splits). BUT, if the 4 new rows are in order, then it is possible that only one page split occurs, representing 1 new page and 2 occurrences of fragmentation. If more rows are added sequentially to the new page created by the split such that a new page is needed, they can roll onto a new next page that will obviously increase the page count by 1 but will not register as an occurrence of fragmentation.

The other scenario--data that is sequential in terms of the new records for the week but were not added in sequential order as determined by sorting by the "timestamp" DATETIME field--is mostly the same as the main scenario just described. The difference here is that a) without ordering by the "timestamp" field it might appear as though the data is sequential and will falsely get ruled out as the source of fragmentation, and b) page splits will happen more frequently within the set of created pages that were just created due to the initial page splits.

Hopefully the following example will help illustrate the issue. Assume we are staring with logical pages A0, B0, and C0 and they are also physically in order. Page B0 is filled and contains rows Ba, Bf, and Bn. New rows come in that are Bo, Bp, Bq, Br, and Bt. But they don't come in that order. The following maps out what should be happening to the data pages as each row comes in. The page IDs (e.g. A0) indicate the logical page while the order indicates their physical placement.

Start:

  A0
  B0 = Ba, Bf, and Bn (page is full)
  C0

Add Br:

  B1 = Bn and Br (physically before A0, but logically after B0 = fragmentation)
  A0
  B0 = Ba and Bf (Split: Bn moved to new page B1)
  C0

Add Bt:

  B1 = Bn, Br, and Bt (page filled)
  A0
  B0 = Ba and Bf
  C0

Add Bp:

  B1 = Bn and Bp (Split: Br and Bt moved to new page B2)
  B2 = Br and Bt (physically AND logically after B1 = NOT fragmentation)
  A0
  B0 = Ba and Bf
  C0

Add Bq:

  B1 = Bn, Bp, and Bq (page filled)
  B2 = Br and Bt
  A0
  B0 = Ba and Bf
  C0

Add Bo:

  B1 = Bn and Bo (Split: Bp and Bq moved to new page B2)
  B3 = Br and Bt (still physically after B1, but now logically after B2 = fragmentation)
  B2 = Bp and Bq (physically after B3, but logically after B1 = fragmentation)
  A0
  B0 = Ba and Bf
  C0

Result: 3 page splits, 3 new pages, 4 counts of fragmentation, 4 pages partially filled

Had those same rows been inserted in order, it would end up looking like:

Start:

  A0
  B0 = Ba, Bf, and Bn (page is full)
  C0

Final:

  B1 = Bn, Bo, and Bp
  B2 = Bq, Br, and Bt
  A0
  B0 = Ba and Bf (Split: Bn moved to new page B1)
  C0

Result: 1 page split, 2 new pages, 2 counts of fragmentation, 1 page partially filled

Misc.

Just to have it noted for those who have a similar setup but do allow UPDATE operations, if it were that UPATEs did occur then this question would contain a false premise and would ultimately be unanswerable. The issue at hand is that a timestamp (whether using a TIMESTAMP / ROWVERSION datatype or a DATETIME / DATETIME2 datatype) only tells you when the last change occurred; it does not tell you a) how many changes have happened, nor b) what were the specific changes that happened (i.e. did the row get smaller? Did 5 fields get much larger but 20 fields got slightly smaller so the row size actually stayed the same? etc). If updates do occur on this table, then the only way to see what caused the increase in fragmentation is to collect the audit data of the changes (whether custom, trigger-based solution or Change Data Capture). But in the current setup, there is no way to reliably correlate the number of rows changed--as indicated by the timestamp field--with any amount of fragmentation (again, IF updates are happening).

Also, regarding this statement:

  The table has 134 columns contains 14gb worth of data, and the primary key is not an identity (sigh)

If this were an OLTP table then I would find the 134 columns more problematic than the primary key not being based on an IDENTITY field. Since this is a history table then it is probably ok to have 134 columns. But even being a history table, it is not necessarily best that the PK be based on an IDENTITY field, or that the table even has a PK (I am assuming that PK and Clustered Index are not the same since the index in the question is UNIQUE CLUSTERED and not a PK). And even if we are talking about the Clustered Index (whether or not the PK), then still not necessarily always best as the leading column. It all comes down to how the data is accessed. If the leading field is an IDENTITY but that value is never used in JOINs or UPDATE or DELETE operations, then you will have sacrificed performance on all queries, even when there is no fragmentation (not fixable), just to avoid some amount of fragmentation (fixable via REBUILD or REORGANIZE).


Compare running / cumulative total with static target I have some daily data and monthly data.

I want to compare these two datasets, with Hive and perform a payout when the sum of actual for a month passes that respective months target. In the above dataset, every id has a monthly target of 6000. The payout should be 0 unless an id accumulates more than 6000 for that respective month from the daily data.

Example: Comp1 has a target of 6000 for month 1. When we look at the daily data, we can see Comp1 hits this target on 2020-01-06 by having an accumulated actual of 6600. At this point, the payout should be: (6600-6000)/100*1. Additionally, Comp1 should see a payout for every day following until the end of the month as they've passed their monthly target.

This is what I have tried:

select
    d.yyyy_mm_dd,
    d.name,
    d.id,
    d.actual,
    case when d.actual > t.target
        then (d.actual - t.target) / 100.0
        else 0
    end payout
from monthly_targets t
inner join (
    select yyyy_mm_dd, name, id, sum(cast(actual as int)) actual
    from daily_data
    group by yyyy_mm_dd, name, id
) d
    on month(d.yyyy_mm_dd) = t.month
    and d.name = t.name
where
    d.yyyy_mm_dd >= '2020-01-01'
    and d.name in ('Comp1', 'Comp2')

However, this is only comparing the daily actual value to the monthly target. There will never be a payout this way as a monthly target will never be met on a single day. How can I amend the above so it calculates a cumulative actual for the month and compares on that instead?

I'm expecting output like this:

+------------+----+-------+--------+----------------+--------+
| yyyy_mm_dd | id | name  | actual | actual_to_date | payout |
+------------+----+-------+--------+----------------+--------+
| 2020-01-01 |  1 | Comp1 |   1100 |           1100 |      0 |
| 2020-01-02 |  1 | Comp1 |   1100 |           2200 |      0 |
| 2020-01-03 |  1 | Comp1 |   1100 |           3300 |      0 |
| 2020-01-04 |  1 | Comp1 |   1100 |           4400 |      0 |
| 2020-01-05 |  1 | Comp1 |   1100 |           5500 |      0 |
| 2020-01-06 |  1 | Comp1 |   1100 |           6600 |      6 |
| 2020-01-07 |  1 | Comp1 |   1100 |           7700 |     17 |
| 2020-01-08 |  1 | Comp1 |   1100 |           8800 |     28 |
| ...        |    |       |        |                |        |
| ...        |    |       |        |                |        |
| ...        |    |       |        |                |        |
| 2020-01-31 |  1 | Comp1 |   1100 |          34100 |    281 |
| 2020-02-01 |  1 | Comp1 |   1100 |           1100 |      0 |
| 2020-02-02 |  1 | Comp1 |   1100 |           2200 |      0 |
| 2020-02-03 |  1 | Comp1 |   1100 |           3300 |      0 |
+------------+----+-------+--------+----------------+--------+

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

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


Why am I getting ERROR: column "GLOBE-BE-154" does not exist I create a Table in a Database and looks as following
test1=# SELECT * FROM stunden_Plan;
 stunde | montag | dienstag | mittwoch | donnerstag | freitag
--------+--------+----------+----------+------------+---------
      1 |        |          |          |            |
(1 row)

From this distinction, I wanted to insert some values into the table below...
INSERT INTO stunden_Plan (stunde, montag, dienstag, mittwoch, donnerstag, freitag)
VALUES (2, "GLOBE-BE-154", "CH-SCH-156", "D-KR-477", "M-HA-477", "E-ZE-477" );

But I got an error of which I have no understanding!
ERROR:  column "GLOBE-BE-154" does not exist

I am grateful for any help. Thanks!

As documented in the manual string constants need to be enclosed in single quotes in SQL
INSERT INTO stunden_Plan (stunde, montag, dienstag, mittwoch, donnerstag, freitag)
VALUES (2, 'GLOBE-BE-154', 'CH-SCH-156', 'D-KR-477', 'M-HA-477', 'E-ZE-477' );

Double quotes are for identifiers


Uniqueness constraint with date range Consider a prices table with these columns:

id         integer primary key
product_id integer -- foreign key
start_date date not null
end_date   date not null
quantity   integer
price      numeric

I'd like the database to enforce the rule that a product can only have one price at a specific quantity in a date range (via where <date> BETWEEN start_date AND end_date).

Is this kind of range-based constraint doable?

Yes, you can use an EXCLUDE constraint, which is a generalization of UNIQUE constraints:

ALTER TABLE prices
  ADD CONSTRAINT unique_price_per_product_quantity_daterange
    EXCLUDE  USING gist
    ( product_id WITH =,
      quantity WITH =,
      daterange(start_date, end_date, '[]') WITH &&   -- this is the crucial
    );

The constraint can be interpreted as saying:

  Don't allow two rows that have same product_id, same quantity and overlapping (&&) date ranges.

The '[]' is for the wanted all-inclusive date range (the default is [) for range types).

See the documentation on constraints on range types. You probably also need to add the extension by running (once, for each database where you want this installed):

CREATE EXTENSION btree_gist;


Managing constants changes to database Here's the deal, I'm in charge of creating a website that will host small web apps. This apps will be photo contests, creatives sentences and other similar gibberish. The deal is, since apps we'll be constantly changing and some new ones will come up what's the best way to save the config specs for each one? Should I create new tables and fields as I need? Or should I serialize this data and put it on one field?

In this case instead of having a table apps, I was thinking having a table for each app, this way I could have a more modular way of dealing with apps, making changes more easy, or more painful (in case of the changes apply to all the apps)

Or if I serialize everything, then I would only have one table and I won't have the need to make changes to the table, but this option seems a bit lame to me.

Ever heard of the windows registry? Put something like that into your database. A hierarchy of folders for key/value pairs. Simple to do with some changes. Do NOT serialize anything more complex than primitive types - that will come back to haunt you - or hand crafted XML structures (i.e. classes that output XML that is clear to read).


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


Unable to shutdown immediate database - Oracle whenever I want to shutdown the database, I get an error saying datafile header is media corrupt:
SQL> shutdown immediate
ORA-01122: database file 13 failed verification check
ORA-01110: data file 13: '/oradata/obvs/entry_data02.dbf'
ORA-01210: data file header is media corrupt

I do not mind losing those files, just need to be able to shut down the database.
I tried reseaching online, but they all say in order for me to fix them I need to shutdown the database.

Since you do not mind losing these files, you can just simply drop the whole tablespace after making the datafile(s) in it offline as below.
Steps to reproduce:
SQL> create tablespace test datafile '/oradata/RYMIN19/test.dbf' size 100M;

Tablespace created.

SQL> create table bp.t1 tablespace test as select * from dba_objects;

Table created.

SQL> !dd if=/dev/urandom of=/oradata/RYMIN19/test.dbf conv=notrunc bs=8192 count=2 oflag=sync
2+0 records in
2+0 records out
16384 bytes (16 kB, 16 KiB) copied, 0.00262083 s, 6.3 MB/s

SQL> shu immediate
ORA-01122: database file 5 failed verification check
ORA-01110: data file 5: '/oradata/RYMIN19/test.dbf'
ORA-01210: data file header is media corrupt

One way to drop:
SQL> shu abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 2147481656 bytes
Fixed Size                  8898616 bytes
Variable Size             956301312 bytes
Database Buffers         1174405120 bytes
Redo Buffers                7876608 bytes
Database mounted.
SQL> alter database datafile '/oradata/RYMIN19/test.dbf' offline drop;

Database altered.

SQL> alter database open;

Database altered.

SQL> drop tablespace test including contents and datafiles;

Tablespace dropped.

If you have more files in this tablespace, execute the above offline drop for all before dropping the tablespace.
You will need to do some manual cleanup in the filesystem because the database is unable to delete such a file, as shown in the alert log:
2020-12-30T23:33:33.458679+01:00
drop tablespace test including contents and datafiles
2020-12-30T23:33:34.322107+01:00
Setting Resource Manager plan SCHEDULER[0x4D5B]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
2020-12-30T23:33:36.724601+01:00
Errors in file /u01/app/oracle/diag/rdbms/rymin19/RYMIN19/trace/RYMIN19_ora_513540.trc:
ORA-01122: database file 5 failed verification check
ORA-01110: data file 5: '/oradata/RYMIN19/test.dbf'
ORA-01565: error in identifying file '/oradata/RYMIN19/test.dbf'
ORA-27048: skgfifi: file header information is invalid
Additional information: 2
2020-12-30T23:33:36.724680+01:00
Errors in file /u01/app/oracle/diag/rdbms/rymin19/RYMIN19/trace/RYMIN19_ora_513540.trc:
ORA-01259: unable to delete datafile /oradata/RYMIN19/test.dbf
Completed: drop tablespace test including contents and datafiles

Cleanup:
SQL> !ls -l /oradata/RYMIN19/test.dbf
-rw-r-----. 1 oracle oinstall 104865792 Dec 30 23:32 /oradata/RYMIN19/test.dbf

SQL> !rm /oradata/RYMIN19/test.dbf

Shutdown immediate works as expected:
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>


MySQL Performance Gains By Splitting Tables? I am designing a database schema to be used with MySQL. The database needs to keep track of a users basket items and purchased items. I've thought about two ways I could do this but not sure which would be better.

1.Have one table

user_product

id (PK) | user_id (FK) | product_id (FK) | sales_order_id (FK)

The user_product table stores details of any items that a user has in their basket and items the user has purchased. For purchased products a foreign key to a sales_order_id is added, for basket items no sales_order_id is added

2.Have two tables

user_basket

id (PK) | user_id (FK) | product_id (FK)

user_purchased

id (PK) | user_id (FK) | product_id (FK) | sales_order_id (FK)

Fairly self explanatory. Items that are pending in a users basket in the first, items they have purchased in the second with a foreign key to a sales order.

The first question I have is are both solutions 3NF? Im 99% sure they both are but if one isn't then I can eliminate it.

The second question is does one solution perform better than the other? I would prefer to go with solution 1, but worried that a lot of queries will be made to that one table. Would it be better to separate out to two tables like solution 2 or does it not really make a difference as the same number of queries will be made to the overall database irrespective of whether its two tables or one.

I'd vote for #2. Will you not need a item_price and a quantity in the user_purchased table? (for which order_details looks a better name, by the way.) Or other, extra columns for the user_basket table? I can easily see how the table will get wide, with a lot of nullable columns. MySQL has no CHECK constraint so how do you plan to enforce integrity?

And why have both kind of queries, the ones that search the Basket and the ones that search the Purchased, all trying to access one table, when you can avoid it?

Other advantages:

Narrower tables, which is usually good for indexing.
One of the 2 tables (the user_purchased) will have only INSERT operations and rarely DELETE or UPDATE and will be growing, so you will not have fragmentation there. With one table for all, which will be affected by all kind of operations, it will be hard to avoid fragmentation.


Getting Postgres CREATE TABLE statements I created some tables (9, to be exact) with a Django (1.9.6) migration and now I'm trying to get simple CREATE TABLE statements for them. I tried this answer, but using pg_dump in this way gives me over 800 lines of output for the 9 tables. For example, part of the output creating the first table is

--
-- Name: popresearch_question; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE popresearch_question (
    id integer NOT NULL,
    created_date timestamp with time zone NOT NULL,
    modified_date timestamp with time zone NOT NULL,
    question_text character varying(500) NOT NULL,
    question_template_id integer,
    question_type_id integer,
    user_id integer
);

ALTER TABLE popresearch_question OWNER TO postgres;

--
-- Name: popresearch_question_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE popresearch_question_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE popresearch_question_id_seq OWNER TO postgres;

--
-- Name: popresearch_question_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE popresearch_question_id_seq OWNED BY popresearch_question.id;

and then later on are more ALTER statements:

--
-- Name: popresearch_question id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY popresearch_question ALTER COLUMN id SET DEFAULT nextval('popresearch_question_id_seq'::regclass);

and then later:

--
-- Name: popresearch_question popresearch_question_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY popresearch_question
    ADD CONSTRAINT popresearch_question_pkey PRIMARY KEY (id);

--
-- Name: popresearch_question popresearch_question_question_text_key; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY popresearch_question
    ADD CONSTRAINT popresearch_question_question_text_key UNIQUE (question_text);

and after that there are at least a dozen more ALTER TABLE statements just for this one table scattered in the pg_dump output. Is there a way to get a simple, condensed CREATE TABLE statement that includes all the keys, constraints, etc.?

I don't know of any Postgres-specific tool that can do that, maybe you should approach this from the Django perspective: https://docs.djangoproject.com/en/1.8/ref/django-admin/#django-admin-sql


MySQL Cluster - Table is full [Error 1114] while still creating tables I am trying to forward engineer a MySQL Workbench model with about 100 tables to a development single-node cluster and, while creating the 51th table, I end up getting a "1114 table is full" error, which is somewhat curious because there isn't any data inside those tables yet.

I suspected the problem had to be related to the memory allocation of hash indexes, so I have tried to create that table without any constraints or indexes and adding them later, not surprisingly finding that the error came out after trying to add any of the indexes, but getting this incoherent memory usage report:

~$ /usr/local/mysql/bin/ndb_mgm -e "ALL REPORT MEMORYUSAGE"
Connected to Management Server at: localhost:1186
Node 1: Data usage is 0%(13 32K pages of total 16000)
Node 1: Index usage is 0%(71 8K pages of total 32032)

I assume I must be missing something but I still can't figure out what it is. This is my config.ini file:

[NDB_MGMD DEFAULT]
Portnumber=1186

[NDB_MGMD]
NodeId=49
HostName=192.168.122.1
DataDir=/var/lib/MySQL_Cluster/49/
Portnumber=1186

[TCP DEFAULT]
SendBufferMemory=2M
ReceiveBufferMemory=2M

[NDBD DEFAULT]
BackupMaxWriteSize=1M
BackupDataBufferSize=16M
BackupLogBufferSize=4M
BackupMemory=20M
BackupReportFrequency=10
MemReportFrequency=30
LogLevelStartup=15
LogLevelShutdown=15
LogLevelCheckpoint=8
LogLevelNodeRestart=15
DataMemory=500M
IndexMemory=250M
MaxNoOfTables=4096
MaxNoOfTriggers=3500
NoOfReplicas=1
StringMemory=25
DiskPageBufferMemory=64M
SharedGlobalMemory=20M
LongMessageBuffer=32M
MaxNoOfConcurrentTransactions=16384
BatchSizePerLocalScan=512
FragmentLogFileSize=64M
NoOfFragmentLogFiles=16
RedoBuffer=32M
MaxNoOfExecutionThreads=2
StopOnError=false
LockPagesInMainMemory=1
TimeBetweenEpochsTimeout=32000
TimeBetweenWatchdogCheckInitial=60000
TransactionInactiveTimeout=60000
HeartbeatIntervalDbDb=15000
HeartbeatIntervalDbApi=15000

[NDBD]
NodeId=1
HostName=192.168.122.1
DataDir=/var/lib/MySQL_Cluster/1/

[MYSQLD DEFAULT]

[MYSQLD]
NodeId=53
HostName=192.168.122.1

[API]
NodeId=50
HostName=192.168.122.1

Any ideas?

After reading deeper the manual, storing the model in an InnoDB engine and running ndb_size.pl I have been able to find that these parameters (which had their default values) were in fact too low:

    MaxNoOfOrderedIndexes
    MaxNoOfUniqueHashIndexes
    MaxNoOfAttributes


Why isn't my Postgres index being used? I’m using Postgres 9.5.  I want to create indexes that will make queries like

select * FROM my_object_times where name like ‘Dave %’;

and

select * FROM my_object_times where name like '% LastName';

run quickly.  So I created …

CREATE INDEX my_object_times_name_idx ON my_object_times (name text_pattern_ops);
CREATE INDEX my_object_times_rev_name_idx ON my_object_times (reverse(name) text_pattern_ops);

This works fine for the first case, in which I can see my index being used, but in the second case, “Explain” does not show my index being utilized …

my_db=> explain select * FROM my_object_times where name like '% LastName';
                            QUERY PLAN
------------------------------------------------------------------
 Seq Scan on my_object_times  (cost=0.00..136594.69 rows=51 width=221)
   Filter: ((name)::text ~~ '% LastName'::text)

How do I get my index to be in used in the second case?

Answer, because you're not reversing it in the query, and that's not implicit.

SELECT * FROM my_object_times where reverse(name) like reverse('% LastName');

However, that's a horrible idea. Instead delete both of those indexes. If you have to have non-anchored matching on something like "LastName" try the pgtrgm extension which will just work on LIKE and %. Tell us how it goes.

Install pg_trgm,

CREATE extention pg_trgm;

Create either a gist or gin index.

CREATE index test_trgm_gin ON test USING gin (some_text gin_trgm_ops);
CREATE index test_trgm_gist ON test USING gist (some_text gist_trgm_ops);

See this blog post on depesz for more information.

Why is two text_pattern_ops an inferior idea,

Two text_pattern_ops will never support '%foo%'
Not likely any library even supports this manual reverse() of all conditions.

What do I mean? Most ORM's support assembling a where clause from a hash. Here is an example for Sequalize,. {LastName: { $like: '% LastName' } } It's unlikely that this is going to ever generate, reverse(LastName) LIKE reverse('% LastName'). So the index simply won't be used unless you write it all manually: at best, that will obfuscate your use of a third party tool, at worst it'll be impossible or very difficult to make work. Where as the pg_trgm option just works.

Requires the user to know something of the index-implementation.
I'm not sure it's more space-efficient than GIST, or faster than either.

Also, as another note, if it just comes down to size you can always get another hard drive, set it up as a TABLESPACE and store the GIST index there. That doesn't solve the /space/ problem, but it should mitigate it.


Create Calculated Fields from Distinct Values in Second Table I'm trying to create a report in Microsoft SQL Server Report Builder 3.0 that has a date field populated with dates for a specified year and alongside, calculated fields for each distinct error code from the 'ERRCODE' field in 'Errors'. For each of the error codes, a count will be done for that error code from the 'Errors' table.

I already have a table function called 'CreateCalendar' that generates a date field with dates populated for the year specified. What I'm struggling to do is use the unique error codes from table 'Errors' and then create the calculated fields from those values.

The 'CreateCalendar' table function requires a 4 digit integer which is set as a parameter. This table function works fine alone and with another query I've used it with.

At the moment I have the below query but I have a feeling I'm going about this the wrong way. I'm still very new to SQL and have no formal training which I hope won't hinder me too much or annoy yourselves.

;WIth ErrorList As
(
 SELECT DISTINCT
  Errors.ERRCODE
 FROM
  Errors
 WHERE
  Errors.ERRTYPE = 'BATCH'
)

DECLARE @cnt int = 0;
DECLARE @name nvarchar(4);

SELECT
 CreateCalendar.cal_date,
 WHILE @cnt < (SELECT Count(ERRCODE) FROM ErrorList)
 BEGIN
  SET @name = (SELECT (ErrorList.ERRCODE) FROM ErrorList WHERE ROW_NUMBER() = @cnt);
  (SELECT Count(ErrorList.ERRCODE) FROM Errors WHERE Errors.ERRDATE = CreateCalendar.cal_date) As @name
  SET @cnt = @cnt + 1;
 END;
FROM
 CreateCalendar(@p_year)

If this is a workable approach, I believe a lot of where I'm going wrong is with my syntax and also picking the error code based on its row number. I could be way off the mark completely.

Any help is appriciated.

EDIT

EXAMPLE EXPECTED OUTPUT

cal_date   | 0001 | 0002 | 0003
01/01/2016 |   20 |   54 |    0
02/01/2016 |   15 |   22 |    2
03/01/2016 |   18 |   38 |    5
04/01/2016 |    0 |    0 |    0
05/01/2016 |   10 |    2 |    7

This looks lika a PIVOT operation, where you want an aggregate (the count) of your error codes, grouped by date (on rows) and error code (on columns).

First off, pivot reports are probably easier to design in your reporting tool, Report Builder, so I would try this query first and try to format the results into a pivot table in the GUI. I don't know Report Builder very well, but the query should look something like this:

SELECT cal.cal_date,
       ec.ERRCODE,
       COUNT(*) AS [count]
FROM dbo.CreateCalendar(@p_year) AS cal
LEFT JOIN Errors.ERRCODE AS ec ON
    cal.cal_date=ec.ERRDATE AND
    ec.ERRTYPE='BATCH'
GROUP BY cal.cal_date, ec.ERRCODE;

If that doesn't work, you can make SQL Server do the pivoting for you, but the downside is that you have to explicitly specify each column (error code) manually in the query, which is repetitive and any future error code won't show up without editing the query later on.

SELECT cal.cal_date,
       SUM((CASE WHEN ec.ERRCODE='0001' THEN 1 ELSE 0 END)) AS [0001],
       SUM((CASE WHEN ec.ERRCODE='0002' THEN 1 ELSE 0 END)) AS [0002],
       SUM((CASE WHEN ec.ERRCODE='0003' THEN 1 ELSE 0 END)) AS [0003],
       SUM((CASE WHEN ec.ERRCODE='0004' THEN 1 ELSE 0 END)) AS [0004]
FROM dbo.CreateCalendar(@p_year) AS cal
LEFT JOIN Errors.ERRCODE AS ec ON
    cal.cal_date=ec.ERRDATE AND
    ec.ERRTYPE='BATCH'
GROUP BY cal.cal_date
ORDER BY cal.cal_date;

For more reading on this type of operation, take a look at GROUP BY, aggregate functions (like COUNT() and SUM()) and PIVOT in T-SQL (although I used a "manual" construct to build a pivot table instead of the built-in PIVOT syntax).


MySQL: Try to Port a Trigger from SQL Server Here, there's a sample of a trigger with "INSTEAD OF UPDATE" clause:

CREATE TRIGGER [dbo].[Test_PTA_Table_Update_trg]
-- ALTER TRIGGER [dbo].[Test_PTA_Table_Update_trg]
   ON  [dbo].[Test_PTA_Table]
   INSTEAD OF UPDATE
AS
   SET NOCOUNT ON
   DECLARE @key int
   SET @key = (SELECT TestTablePK FROM Inserted)

   UPDATE Test_PTA_Table
      SET DateEnd = GetDate(), DateReplaced = GetDate()
      WHERE TestTablePK = @key

   INSERT INTO dbo.Test_PTA_Table
   (TestTableText, DateCreated, DateEffective, OperatorCode, DateReplaced)
   (SELECT TestTableText, GetDate(), GetDate(), OperatorCode, NULL
FROM Inserted)

I'm trying to port the principle to MySQL:

DELIMITER ;;

DROP TRIGGER IF EXISTS attribut_trigger_update_before;

CREATE TRIGGER attribut_trigger_update_before
BEFORE UPDATE ON attribut
  FOR EACH ROW BEGIN
    INSERT INTO attribut SET
      id_origine      = OLD.id_origine,
      date_v_creation = OLD.date_v_creation,
      date_v_debut    = OLD.date_v_debut,
      date_v_fin      = OLD(),
      importance      = OLD.importance,
      description     = OLD.description,
      valeur          = OLD.valeur,
      abbreviation    = OLD.abbreviation;
  END;;

DELIMITER ;;

But it doesn't work.

mysql> update produit set importance=3;
ERROR 1442 (HY000): Can't update table 'produit' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
mysql>

Any idea how to do this?

This has been discussed before by others

Making a column immutable in MySQL
What are some good methods of structuring a MySQL audit trail to be both immutable, and perform well for usage?

Your question is a little different in this instance. You may have to experiment with INSERT IGNORE or REPLACE for the desired effect.

You may need to also experiment with disabling the trigger midstream : Disable trigger for just one table


Validate a column to accept only certain age limit from birthdate given using check constraint A business rule required my table to only registrater  certain dob from bithDate column to be inserted or allow. I created the check constraint but i kind of having logical error i not able to spot out. pls advise

ALTER TABLE Personalinfo
ADD CONSTRAINT chk_ValidDOB   CHECK
(
CASE
    WHEN (convert(int,DATEDIFF(M, BirthDate, getdate())/12) BETWEEN 30 AND 33) THEN 1
    ELSE BirthDate
END = 1
);
Error message:  `Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the CHECK constraint "chk_ValidDOB"`

Most likely the issue is that the ELSE is returning BirthDate instead of 0.

If there is already data in the table that conflicts, then add the WITH NOCHECK clause if you want to keep that data, else first delete any rows that conflict with the new constraint.

Also, best to use MONTH instead of M for the date part, for easier readability.


Migrate existing data when altering the column type from string to json I have a table with an existing column type varchar. I need to convert this column type to new type jsonb. The problem is if the column has already data 'str1:str2' then after converting the type to jsonb column has invalid JSON string 'str1:str2'.
I want to migrate the existing string 'str1:str2' into the proper JSON string with keys as '{"key1":"str1","key2":"str2"}' before/while converting the column type.
Is it possible to do it using ALTER TABLE ALTER COLUMN....Using function() clause or in some other way.

Rather than making up any keys, why not convert that to a json array?
alter table the_table
  alter column the_column type jsonb
  using to_jsonb(string_to_array(the_column, ':'));

That would turn str1:str2 into ["str1", "str2"]
If you really need to make up some (numbered keys), you will have to write a helper function:
create function make_json_keys(p_input text)
returns jsonb
as
$$
  select jsonb_object_agg('key'||idx, val)
  from unnest(string_to_array(p_input, ':')) with ordinality as t(val, idx);
$$
language sql
immutable;

That can be used in the USING expression:
alter table the_table
  alter column the_column type jsonb
  using make_json_keys(the_column);


How do I do an UPSERT in SQL Server, returning the pre-updated values? All of the syntax is valid PostgreSQL as an example of an UPSERT that returns the old and new values for the field. Let's say I have a table foo with (1,A)...(5,E).

CREATE TEMP TABLE foo
AS
  SELECT id, chr(id+64)
  FROM generate_series(1,5) AS t(id);
CREATE UNIQUE INDEX ON foo(id);

 id | chr
----+-----
 1  | A
 2  | B
 3  | C
 4  | D
 5  | E
(5 rows)

Now, let's say I want to UPSERT 6 rows. A few colliding, a few new rows.

SELECT id, chr(id+74)
FROM generate_series(3,8) AS t(id);
 id | chr
----+-----
  3 | M
  4 | N
  5 | O
  6 | P
  7 | Q
  8 | R

How would I get,

id
The old value of chr
The new value of chr

In PostgreSQL, I would do..

WITH t1 AS (
  SELECT id, foo.chr AS oldchr, chr(id+74)
  FROM generate_series(3,8) AS t(id)
  LEFT OUTER JOIN foo USING (id)
),
tupdate AS (
  UPDATE foo
    SET chr = t1.chr
  FROM t1
  WHERE foo.id = t1.id
  RETURNING foo.id, t1.chr, t1.oldchr
),
tinsert AS (
  INSERT INTO foo (id, chr)
  SELECT id, chr
  FROM t1
  WHERE t1.oldchr IS NULL
  RETURNING id, chr, null::text
)
SELECT * FROM tupdate
UNION ALL
SELECT * FROM tinsert;

 id | chr | oldchr
----+-----+--------
  3 | M   | C
  4 | N   | D
  5 | O   | E
  6 | P   |
  7 | Q   |
  8 | R   |
(6 rows)

That table above is returned by those CTEs. And, that's the output I'm looking for.

You can use merge with the output clause.

Temp table:

create table #foo
(
  id int unique,
  chr char(1)
);

insert into #foo(id, chr) values
(1, 'A'),
(2, 'B'),
(3, 'C'),
(4, 'D'),
(5, 'E');

Merge:

with t1 as
(
  select T.id,
         T.chr
  from (values (3, 'M'),
               (4, 'N'),
               (5, 'O'),
               (6, 'P'),
               (7, 'Q'),
               (8, 'R')) as T(id, chr)
)
merge #foo as T
using t1 as S
on T.id = S.id
when not matched then
  insert (id, chr) values(S.id, S.chr)
when matched then
  update set chr = S.chr
output inserted.id,
       inserted.chr,
       deleted.chr as oldchr;

Result:

id          chr  oldchr
----------- ---- ------
3           M    C
4           N    D
5           O    E
6           P    NULL
7           Q    NULL
8           R    NULL